# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Notes on Pandas Data Cleaning
#
# #### Author: <NAME>
# #### 07/10/2020

# https://www.kaggle.com/learn/data-cleaning

# +
import datetime

import chardet

from fuzzywuzzy import fuzz, process
import matplotlib.pyplot as plt
from mlxtend.preprocessing import minmax_scaling
import numpy as np
import pandas as pd
from scipy import stats
import seaborn as sns

np.random.seed(0) 
# -

# # Handling Missing Values

# ## Initial look at the data

# https://www.kaggle.com/agirlcoding/all-space-missions-from-1957
# Detail: Rocket Name
# Rocket column: Cost of the mission: in $ million
df = pd.read_csv("data/space-missions/Space_Corrected.csv")

# Initial visualisation of the data

df.head()

# Count how many missing data points 

missing_values = df.isnull().sum()
missing_values

# See what percentage of the values in our dataset are missing to give us a better sense of the scale of this problem

# +
# how many total missing values do we have?
total_cells = np.product(df.shape)
total_missing = missing_values.sum()
print("total cells = {}\ntotal missing = {}".format(total_cells, total_missing))

# percent of data that is missing
print("% missing = {}%".format(round((total_missing / total_cells) * 100, 2)))
# -

# **Is this value missing because it wasn't recorded or because it doesn't exist?**:
#
# * if a value is missing becuase it doesn't exist then it doesn't make sense to try and guess what it might be (keep as NaN)
#
# * if a value is missing because it wasn't recorded, then can try to guess what it might have been based on the other values in that column and row (imputation)

# ## Option 1: drop missing values

# If in a hurry or don't have a reason to figure out why values are missing, can remove any rows or columns that contain missing values. 
#
# *Note: not recommend to follow this approch for important projects! It's usually worth it to take the time to go through data and really look at all the columns with missing values one-by-one to really get to know the dataset.)*

# remove all the rows that contain a missing value
print("dataframe size before dropping values={}".format(df.shape))
df.dropna()
print("dataframe size after dropping values={}".format(df.shape))

# Danger: might delete all data (if each row contains at least 1 missing value)

# remove all columns with at least one missing value
columns_with_na_dropped = df.dropna(axis=1)
columns_with_na_dropped.head()
print("Columns in original dataset: {}".format(df.shape[1]))
print("Columns with na's dropped: {}".format(columns_with_na_dropped.shape[1]))

# --> Lost 1 column, but no more NaN in data

# ## Option 2: Filling in missing values automatically

# Replace all the NaN values with 0.

# replace all NA's with 0
df.fillna(0)

# Replace all values with whatever value comes directly after it in the same column. 
# *(This makes a lot of sense for datasets where the observations have some sort of logical order to them.)*

df = pd.read_csv("data/space-missions/Space_Corrected.csv")
# replace all NA's the value that comes directly after it in the same column, 
# then replace all the remaining na's with 0
df.fillna(method="bfill", axis=0).fillna(0)

# # Scaling and Normalisation

# * scaling = changing the range of your data
# * normalisation = changing the shape of the distribution of your data.

# ## Scaling

# ==> transforming your data so that it fits within a specific scale
#
# Useful when using methods based on measures of how far apart data points are, like SVM or kNN --> can help compare different variables on equal footing.

# +
# generate 1000 data points randomly drawn from an exponential distribution
data = np.random.exponential(size=1000)

# df[" Rocket"].to_numpy()
# data
# -

# mix-max scale the data between 0 and 1
scaled_data = minmax_scaling(data, columns=[0])

# plot both together to compare
fig, ax = plt.subplots(1,2)
sns.distplot(data, ax=ax[0])
ax[0].set_title("Original Data")
sns.distplot(scaled_data, ax=ax[1])
ax[1].set_title("Scaled data")

# ## Normalisation

# The point of normalisation is to change observations so that they can be described as a normal distribution != scaling!
#
# **Normalisation** = specific statistical distribution where 
# * roughly equal observations fall above and below the mean
# * the mean and the median are the same
# * there are more observations closer to the mean
# * aka *Gaussian distribution* and *bell curve*.
#
# Normalise your data if going to be using a machine learning or statistics technique that assumes that the data is normally distributed e.g. linear discriminant analysis (LDA) and Gaussian naive Bayes (any method with "Gaussian" in the name probably assumes normality.)

# normalize the exponential data with a boxcox transformation
normalized_data = stats.boxcox(data)

# plot both together to compare
fig, ax=plt.subplots(1,2)
sns.distplot(data, ax=ax[0])
ax[0].set_title("Original Data")
sns.distplot(normalized_data[0], ax=ax[1])
ax[1].set_title("Normalized data")

# --> the shape of data has changed. Before normalising it was almost L-shaped. But after normalizing it looks more like the outline of a bell.

# # Parsing Dates

# Dates are in `Datum` columns (Strings usually have type object)

df['Datum'].head()

# Convert column to datetime using strftime directives: https://strftime.org/ to point out which parts of the date are where and what punctuation is between them e.g. `%d` for day, `%m` for month, `%y` for a two-digit year and `%Y` for a four digit year:
# * 1/17/07 has the format `%m/%d/%y`
# * 17-1-2007 has the format `%d-%m-%Y`
#
#
#
# (use infer_datetime_format if run into an error with multiple date formats)

df['Datum'] = pd.to_datetime(df['Datum'], utc=True)  # infer_datetime_format=True
df['Datum'].head()

# --> Now dtype is datetime64

# Can now interact with dates e.g. extract information from the day the launch occured

day_of_launch = df['Datum'].dt.day
day_of_launch.head()

# Plotting days of the month because there is a danger with parsing dates: mixing up the months and days.
#
# It is better to double-check that the days of the month make sense (plot a histogram of the days of the month, expect values between 1 and 31):

day_of_launch = day_of_launch.dropna()
sns.distplot(day_of_launch, kde=False, bins=31)

# # Character Encodings

# ## Encodings

# Character encodings are specific sets of rules for mapping from raw binary byte strings (that look like this: 0110100001101001) to characters that make up human-readable text (like "hi"). There are many different encodings, and if anyone tried to read in text with a different encoding than the one it was originally written in, you ended up with:
# * scrambled text called "mojibake" e.g. `æ–‡å—åŒ–ã??`
# * a "unknown" characters. They are what gets printed when there's no mapping between a particular byte and a character in the encoding you're using to read your byte string in and they look like this: `����������`
#
# Character encoding mismatches are less common today than they used to be, but it's definitely still a problem. There are lots of different character encodings. The main one is UTF-8, the standard text encoding. All Python code is in UTF-8 and ideally all the data should be as well. Trouble comes when UTF-8 is not used.
#
# There are two main data types when working with text in Python 3: string and bytes.

# ### String
#
# `string` which is what text is by default

# string with a symbol:
my_str = "This is the euro symbol: €"
type(my_str)

# ### Byte
#
# `bytes` is a sequence of integers (can convert a string into bytes by specifying which encoding it's in)

# encode previous string it to a different encoding, replacing characters that raise errors:
new_str = my_str.encode("utf-8", errors="replace")
type(new_str)

# The bytes object has a `b` in front of it and some text after. That's because bytes are printed out as if they were characters encoded in ASCII (an older character encoding that doesn't work for writing any language other than English.) The euro symbol has been replaced with some mojibake when it's printed as if it were an ASCII string.

new_str

# Can convert bytes back to a string with the correct encoding to get the correct text.

print(new_str.decode("utf-8"))

# Can try decoding in ASCII instead of UTF-8, but will cause errors because the encoding trying to use doesn't know what to do with the bytes trying to pass it --> need to tell Python the encoding that the byte string is actually supposed to be in:

print(new_str.decode("ascii"))

# Can ask for the bytes to be what they would be if the text was in ASCII. Since text isn't in ASCII there will be some characters it can't handle which are  automatically replaced with the unknown character. When the bytes are converted back to a string, the character will be replaced with the unknown character `�`. The dangerous part about this is that there's not way to tell which character it should have been --> just made data unusable!

before = "This is the euro symbol: €"
# Encode string to a different encoding (ASCII instead of UTF-8), replacing characters that raise errors
after = before.encode("ascii", errors = "replace")
# Convert it back to utf-8
print(after.decode("ascii"))

# It's better to convert all text to UTF-8 ASAP and keep it in that encoding. The best time to convert non UTF-8 input into UTF-8 during file reading.

# ## Reading in files with encoding problems

# If get `UnicodeDecodeError` error (same when  tried to decode UTF-8 bytes as if they were ASCII), then file isn't actually UTF-8. 

df_in = pd.read_csv("data/pakistan-intellectual-capital/Pakistan Intellectual Capital - Computer Science - Ver 1.csv")

# Don't know what encoding it actually is so can use the `chardet` module to try and automatically guess what the right encoding is (not 100% guaranteed to be right, but faster than just trying to guess).

# +
# Look at the first ten thousand bytes to guess the character encoding (usually enough for a good guess)
with open("data/pakistan-intellectual-capital/Pakistan Intellectual Capital - Computer Science - Ver 1.csv", 'rb') as rawdata:
    result = chardet.detect(rawdata.read(10000))

# Check what the character encoding might be
print(result)
# -

df_in = pd.read_csv("data/pakistan-intellectual-capital/Pakistan Intellectual Capital - Computer Science - Ver 1.csv", encoding=result["encoding"])

# ## Saving file in UTF-8 encoding

# Once file is in UTF-8, save it in that encoding. Easily done by saving file because it will be saved as UTF-8 by default.

df_in.to_csv("data/pakistan-intellectual-capital/Pakistan Intellectual Capital - Computer Science - Ver 1 - utf8.csv")

# ## E) Inconsistent Data Entry

df = pd.read_csv("data/pakistan-intellectual-capital/Pakistan Intellectual Capital - Computer Science - Ver 1 - utf8.csv")
df.head(3)

df["Country"].unique()

# Inconsistencies: " New Zealand" and "New Zealand" or "Germany" and " Germany" (leading space).
#
# --> make everything lower case (can change it back later) and remove any white spaces at the beginning and end of cells. Inconsistencies in capitalisations and trailing white spaces are very common in text data.

# Convert to lower case
df["Country"] = df["Country"].str.lower()
# Remove trailing white spaces
df["Country"] = df["Country"].str.strip()
df["Country"].unique()

# Other inconsistency: "south korea" and "southkorea"

# Going to use the `fuzzywuzzy` package to help identify which strings are closest to each other (could correct errors by hand, but that approach doesn't scale well)
#
# **Fuzzy matching** = The process of automatically finding text strings that are very similar to the target string. In general, a string is considered "closer" to another one the fewer characters you'd need to change if you were transforming one string into another. So "apple" and "snapple" are two changes away from each other (add "s" and "n") while "in" and "on" and one change away (rplace "i" with "o"). Can't rely on fuzzy matching 100%, but will save time.
#
# Fuzzywuzzy returns a ratio given two strings. The closer the ratio is to 100, the smaller the edit distance between the two strings. 
#
# Example: get the 10 strings from list of countries that have the closest distance to "south korea".

# Get the top 10 closest matches to "south korea"
matches = process.extract("south korea", df["Country"].unique(), limit=10, scorer=fuzz.token_sort_ratio)
matches


# 1 of the items in the countries is very close to "south korea": "southkorea". 
#
# Now, can replace all rows in "Country" column that have a ratio of > 47 with "south korea":

def replace_matches_in_column(df, column, string_to_match, min_ratio = 47):
    """
    Function to replace rows in the provided column of the provided dataframe that match 
    the provided string above the provided ratio with the provided string.
    """
    # get a list of unique strings
    strings = df[column].unique()
    
    # get the top 10 closest matches to our input string
    matches = process.extract(string_to_match, 
                              strings, 
                              limit=10, 
                              scorer=fuzz.token_sort_ratio)

    # only get matches with a ratio > 90
    close_matches = [matches[0] for matches in matches if matches[1] >= min_ratio]

    # get the rows of all the close matches in our dataframe
    rows_with_matches = df[column].isin(close_matches)

    # replace all rows with close matches with the input matches 
    df.loc[rows_with_matches, column] = string_to_match
    
    # let us know the function's done
    print("Replacements completed")


# use the function we just wrote to replace close matches to "south korea" with "south korea"
replace_matches_in_column(df=df, column='Country', string_to_match="south korea")

df["Country"].unique()

# Only one entry for "south korea" now!

# Can capitalise the countries again
df['Country'] = df['Country'].str.capitalize()
df["Country"].unique()

StarcoderdataJupyterScriptsDedupFiltered