<jupyter_start><jupyter_text>Here's what we're going to do on missing value treatment: 1) Take a first look at the data 2) See how many missing data points we have 3) Figure out why the data is missing 4) Drop missing values 5) Filling in missing values  Take a first look at the data We will use a python library called pandas which is a datastructure and data analysis tool. More on pandas in the A11 course Few other libraries we will use are seaborn, matplotlib for visualisation<jupyter_code>import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import plotly.offline as py
py.init_notebook_mode(connected=True)
import plotly.graph_objs as go
import plotly.tools as tls<jupyter_output><empty_output><jupyter_text>load the data<jupyter_code>data = pd.read_csv("data/scraped_data.csv")
data.dtypes
data.head()<jupyter_output><empty_output><jupyter_text>Cleaning<jupyter_code>dataCopy = data.copy()
dataCopy = dataCopy.replace('-', np.NaN)
dataCopy.head()
#missingno is a python library for nice visulaisation of missing number in the data
import missingno as msno

# Nullity or missing values by columns
msno.matrix(df=dataCopy.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))<jupyter_output><empty_output><jupyter_text>So how many missing datapoints we have<jupyter_code># get the number of missing data points per column
missing_values_count = dataCopy.isnull().sum()

# look at the # of missing points in the first ten columns
missing_values_count[0:17]<jupyter_output><empty_output><jupyter_text>Remove incomplete rows<jupyter_code>#Dropping all rows with any NA values is easy

dataCopyDropAll = dataCopy.copy()
dataCopyDropAll.dropna(inplace=True)

msno.matrix(df=dataCopyDropAll.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))
# This might drop a lot of rows which might hamper our analysis

# We can drop rows with all NULLs, thats not the case here
dataCopyDropRowWithAllNA = dataCopy.copy()
dataCopyDropRowWithAllNA.dropna(how='all',inplace=True)

msno.matrix(df=dataCopyDropRowWithAllNA.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))
#We also can remove rows with a threshold value

dataCopyDropRowWithThres = dataCopy.copy()
dataCopyDropRowWithThres.dropna(thresh=10,inplace=True)

msno.matrix(df=dataCopyDropRowWithThres.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))
#Sometimes it makes sense to drop 
# Drop the columns with that are all NA values
dataCopyDropColAllNA = dataCopy.copy()
dataCopyDropColAllNA.dropna(axis=1, how='all', inplace=True)

msno.matrix(df=dataCopyDropColAllNA.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))
# Drop all columns with any NA values:
dataCopyDropColAnyNA = dataCopy.copy()
dataCopyDropColAnyNA.dropna(axis=1, how='any', inplace=True)

msno.matrix(df=dataCopyDropColAnyNA.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))
#The threshold thing also can be applied here

dataCopyDropColWithThres = dataCopy.copy()
dataCopyDropColWithThres.dropna(axis=1, thresh=5, inplace=True)

msno.matrix(df=dataCopyDropColWithThres.iloc[:,2:39], figsize=(20, 14), color=(0.42, 0.1, 0.05))<jupyter_output><empty_output><jupyter_text>Imputation of missing values  This is the point at which we get into the part of data science that I like to call "data intution", by which I mean "really looking at your data and trying to figure out why it is the way it is and how that will affect your analysis". It can be a frustrating part of data science, especially if you're newer to the field and don't have a lot of experience. For dealing with missing values, you'll need to use your intution to figure out why the value is missing. One of the most important question you can ask yourself to help figure this out is this:Is this value missing becuase it wasn't recorded or becuase it dosen't exist?If a value is missing becuase it doens't exist (like the height of the oldest child of someone who doesn't have any children) then it doesn't make sense to try and guess what it might be. These values you probalby do want to keep as NaN. On the other hand, if a value is missing becuase it wasn't recorded, then you can try to guess what it might have been based on the other values in that column and row. (This is called "imputation" and we'll learn how to do it next! :)  Mean, median, mode  Frequent values<jupyter_code># A simple imputation to make the nan values 0, but not a good idea always
dataCopyFillNAZero = dataCopy.copy()
dataCopyFillNAZero = dataCopyFillNAZero.fillna(0)
dataCopyFillNAZero.head()
# The values can also be replace with mean, media, mode or frquent values in the column
dataCopyFillNAMean = dataCopy.copy()
dataCopyFillNAMean["first_class.average"] = pd.to_numeric(dataCopyFillNAMean["first_class.average"])
dataCopyFillNAMean = dataCopyFillNAMean.fillna(dataCopyFillNAMean.mean())
dataCopyFillNAMean.head()<jupyter_output><empty_output><jupyter_text>A simple imputer from sklearn cane be used, Detailed sklearn will be covered in the next module class sklearn.impute.SimpleImputer(missing_values=nan, strategy=’mean’, fill_value=None, verbose=0, copy=True, add_indicator=False)missing_values : number, string, np.nan (default) or NoneThe placeholder for the missing values. All occurrences of missing_values will be imputed.strategy : string, optional (default=”mean”)The imputation strategy.If “mean”, then replace missing values using the mean along each column. Can only be used with numeric data.If “median”, then replace missing values using the median along each column. Can only be used with numeric data.If “most_frequent”, then replace missing using the most frequent value along each column. Can be used with strings or numeric data.If “constant”, then replace missing values with fill_value. Can be used with strings or numeric data.New in version 0.20: strategy=”constant” for fixed value imputation.fill_value : string or numerical value, optional (default=None)When strategy == “constant”, fill_value is used to replace all occurrences of missing_values. If left to the default, fill_value will be 0 when imputing numerical data and “missing_value” for strings or object data types.erbose : integer, optional (default=0)Controls the verbosity of the imputer.copy : boolean, optional (default=True)If True, a copy of X will be created. If False, imputation will be done in-place whenever possible. Note that, in the following cases, a new copy will always be made, even if copy=False:If X is not an array of floating values;If X is encoded as a CSR matrix;If add_indicator=True.add_indicator : boolean, optional (default=False)If True, a MissingIndicator transform will stack onto output of the imputer’s transform. This allows a predictive estimator to account for missingness despite imputation. If a feature has no missing values at fit/train time, the feature won’t appear on the missing indicator even if there are missing values at transform/test time.<jupyter_code>from sklearn.impute import SimpleImputer
dataCopyImputation = dataCopy.copy()
my_imputer = SimpleImputer()
dataCopyImputation['first_class.average'] = dataCopyImputation['first_class.average'].astype('float')
dataCopyImputation['first_class.strike_rate'] = dataCopyImputation['first_class.strike_rate'].astype('float')
data_with_imputed_values = my_imputer.fit_transform(dataCopyImputation[['first_class.average','first_class.strike_rate']])<jupyter_output><empty_output><jupyter_text>Assignment: Predicting Missing values KNN is an algorithm that is useful for matching a point with its closest k neighbors in a multi-dimensional space. It can be used for data that are continuous, discrete, ordinal and categorical which makes it particularly useful for dealing with all kind of missing data.The assumption behind using KNN for missing values is that a point value can be approximated by the values of the points that are closest to it, based on other variables.https://towardsdatascience.com/the-use-of-knn-for-missing-values-cf33d935c637<jupyter_code># Assignment: Use KNN Impute library to fill up the missing data from data/scrapped_data_large.csv

import knnimpute<jupyter_output><empty_output><jupyter_text>missForest Nonparametric Missing Value Imputation Using Random Forest'missForest' is used to impute missing values particularly in the case of mixed-type data. It can be used to impute continuous and/or categorical data including complex interactions and nonlinear relations.  Noisy data removal<jupyter_code>import re
def clean_data(s):
    s = s.replace('u', '')
    s = s.replace('\'', '')
    s = s.upper()
    s = s.replace(',', ' ')
    s = s.replace('[', '')
    s = s.replace(']', '')
    # Isolate punctuation
    s = re.sub(r'([\'\"\.\(\)\!\?\-\\\/\,\$%])', r' \1 ', s)
    # Remove some special characters
    s = re.sub(r'([\;\:\|"Â«\n])', ' ', s)
    return s
import re
dataCopyNoisyData = dataCopy.copy()
dataCopyNoisyData['born_new'] = dataCopyNoisyData['born'].apply(clean_data)
dataCopyNoisyData.head()<jupyter_output><empty_output><jupyter_text>Inconsistent data removal  Use fuzzy matching to correct inconsistent data entry I'm going to use the fuzzywuzzy package to help identify which string are closest to each other. This dataset is small enough that we could probably could correct errors by hand, but that approach doesn't scale well. (Would you want to correct a thousand errors by hand? What about ten thousand? Automating things as early as possible is generally a good idea. Plus, it’s fun! :)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"). You won't always be able to rely on fuzzy matching 100%, but it will usually end up saving you at least a little time.Fuzzywuzzy returns a ratio given two strings. The closer the ratio is to 100, the smaller the edit distance between the two strings.<jupyter_code># fuzz is used to compare TWO strings
from fuzzywuzzy import fuzz

# process is used to compare a string to MULTIPLE other strings
from fuzzywuzzy import process

#fuzz.ratio compares the entire string, in order
print(fuzz.ratio("this is a test", "this is a fun"))
#fuzz.partial_ratio compares subsections of the string
print(fuzz.partial_ratio("this is a test", "test a is this"))
#fuzz.token_sort_ratio ignores word order
print(fuzz.token_sort_ratio("fuzzy wuzzy was a bear", "wuzzy fuzzy was a bear"))
print(fuzz.token_sort_ratio("fuzzy was a bear", "fuzzy fuzzy was a bear"))
#fuzz.token_set_ratio ignores duplicate words
print(fuzz.token_set_ratio("fuzzy was a bear", "fuzzy fuzzy was a bear"))

# replace data with one consistent version if match score is higher than a pre-determined threshold<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08413680189194106, 'fraction_numerical': 0.013098053483718392, 'mean_word_length': 5.647521160822249, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '148687', 'license': 'Apache-2.0', 'n_tokens_mistral': 3397, 'n_tokens_neox': 3199, 'n_words': 1337, 'path': 'DataPreperation/1_DataQuality.ipynb', 'repo_name': 'sumandeepb/A10_DataPreperationAndVisualization', 'size': '10994'}