<jupyter_start><jupyter_text>Part One: Handling Missing Values<jupyter_code>from IPython.display import HTML
from IPython.display import Image
Image(url= "https://www.topbots.com/wp-content/uploads/2020/06/1_8d3YIjRdvh92ZGprwF24gQedited.jpg")
# Image(url = "https://datascienceai365.com/wp-content/uploads/2020/03/Do-Meditation-3.png")
# Image(url = "https://www.digitalvidya.com/wp-content/uploads/2018/08/data-cleaning-techniques.jpg")
# Image(url = "https://research.aimultiple.com/wp-content/uploads/2018/06/data-cleaning-new.png")

# # <p style="text-align: center;"> Data Cleaning</p>
# ![title](Mini_Project_One\Images\Data_Cleaning.jpg)
HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
The raw code for this IPython notebook is by default hidden for easier reading.
To toggle on/off the raw code, click <a href="javascript:code_toggle()">here</a>.''')<jupyter_output><empty_output><jupyter_text>Table of Contents -  1. [Introduction](Introduction)   -  1.1 [Abstract](abstract)   -  1.2 [Importing Libraries](importing_libraries)   -  1.3 [Importing Dataset](importing_dataset)   -  1.4 [Dataset Summary](dataset_summary)       -  1.4.1 [Head](head)       -  1.4.2 [Describe](describe)       -  1.4.3 [Info](info)       -  1.4.4 [Finding Missing Values](finding_missing_values)       -  1.4.5 [Figure out Why the Data is Missing](why_data_missing)       -  1.4.6 [Handling Missing Values](handling_missing_values)           -  1.4.6.1 [Drop missing values (Most Dirty Approach)](drop_missing)           -  1.4.6.2 [Drop Columns with Missing Values](drop_missing_columns)           -  1.4.6.3 [Filling Missing Values (automatically)](fill_missing_automatic)           -  1.4.6.4 [Better Option: Imputation](imputation)           -  1.4.6.5 [Extension To Imputation](extension_imputation)   -  1.5 [Implementation](implementation)-  5. [Conclusion](Conclusion)-  6. [Contribution](Contribution)-  7. [Citation](Citation)-  8. [License](License)   1.0 Introduction     1.1 Abstract  Welcome to the Data Cleaning[Back to top](Introduction)    1.2 Importing Libraries  This is the official start to any Data Science or Machine Learning Project. A Python library is a reusable chunk of code that you may want to include in your programs/ projects. In this step we import a few libraries that are required in our program. Some major libraries that are used are Numpy, Pandas, MatplotLib, Seaborn, Sklearn etc.[Back to top](Introduction)<jupyter_code>import numpy as np
import pandas as pd

import matplotlib.pyplot as plt
import plotly.express as px
import seaborn as sns
import time
import datetime

import string

from sklearn import ensemble, metrics, model_selection, naive_bayes
from sklearn.metrics import confusion_matrix,roc_auc_score,log_loss, mean_absolute_error
from sklearn.preprocessing import LabelEncoder, MinMaxScaler, StandardScaler
from sklearn.model_selection import cross_val_score,train_test_split,KFold
from sklearn.pipeline import Pipeline
from sklearn.ensemble import RandomForestRegressor

from xgboost import XGBRegressor

from astropy.table import Table, Column

seed = 2176

import xgboost as xgb 


import warnings; warnings.simplefilter('ignore')<jupyter_output><empty_output><jupyter_text>1.3 Importing the dataset  We begin the journey of a Data Science project by Importing the dataset into our Python Notebook. A very popular option in importing data in Python is via the use of Pandas, especially when the data size is big (like several hundred MBs). [Back to top](Introduction)<jupyter_code>start_time = time.time()

df_1 = pd.read_csv("Datasets/Melbourne_Housing_Data.csv")

end = time.time()

print("Time taken in reading the input files is {}.".format(end - start_time))<jupyter_output>Time taken in reading the input files is 0.1466062068939209.<jupyter_text>**PS** - We also take a note of the time taken to read the input files, it is not always necessary, but as the size of the datasets keeps getting bigger and bigger, the time taken to read the input file may also rise. We just keep a note of it, incase it might come in handy later.<jupyter_code># start_time = time.time()<jupyter_output><empty_output><jupyter_text>1.4 Dataset Summary  We start any data science project like we start dating. During the first few dates, it’s critical to get a feel for your partner (your data). Are there any deal breakers that might creep up later on? These deal breakers are the ones you want to catch first. These deal breakers will bias your data. We use head(), describe() and info() functions of the pandas library to view the dataset with 3 different angles. It is like viewing your dataset with different lenses, each give you a different view. [Back to top](Introduction)    1.4.1 Head()  First we use the head to take a plane look at the dataset. Here we look at  top 5 rows of the dataset to view, what type of dataset it is. We also look at the columns which show the various attributes in the dataset. This is a key step in identifying what type of data we're dealing with.[Back to top](Introduction)<jupyter_code>df_1.head()<jupyter_output><empty_output><jupyter_text>1.4.2 Describe()  Second, we're going to view the mathematical properties of our dataset. Here we see the basic mathematical properties of each column in our dataset. These include count, mean, standard deviation, minimum, maximum and different quantile values. Just viewing this can sometimes help detect inconsistencies in our dataset.[Back to top](Introduction)<jupyter_code>df_1.describe()<jupyter_output><empty_output><jupyter_text>As we can see here already, there are a few values which are 0 in min like Bathroom and BuildingArea which clearly proves that there are a few errors in the dataset.    1.4.3 Info()  Third, this seems very trivial, but this is quite an important insight. This method prints information about a DataFrame including the index dtype and columns, non-null values and memory usage. Here is a list of Python Data types. We generally do not care about the data types unless something goes wrong, so it is always a good idea to have it ready to check.![title](Images\Pandas_dtype.png)[Back to top](Introduction)<jupyter_code>df_1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 13580 entries, 0 to 13579
Data columns (total 21 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   Suburb         13580 non-null  object 
 1   Address        13580 non-null  object 
 2   Rooms          13580 non-null  int64  
 3   Type           13580 non-null  object 
 4   Price          13580 non-null  float64
 5   Method         13580 non-null  object 
 6   SellerG        13580 non-null  object 
 7   Date           13580 non-null  object 
 8   Distance       13580 non-null  float64
 9   Postcode       13580 non-null  float64
 10  Bedroom2       13580 non-null  float64
 11  Bathroom       13580 non-null  float64
 12  Car            13518 non-null  float64
 13  Landsize       13580 non-null  float64
 14  BuildingArea   7130 non-null   float64
 15  YearBuilt      8205 non-null   float64
 16  CouncilArea    12211 non-null  object 
 17  Lattitude      13580 non-null  float64
 18  Longti[...]<jupyter_text>Here we see a few columns with a few null values here and there, and we see the size of the dataset which is 2.2 MB (Thankfully it's not very huge). And we also see the data types of the columns.    1.4.4 Finding Missing Values Missing Data can occur when no information is provided for one or more items or for a whole unit. Missing Data is a very big problem in real life scenario. Missing Data can also refer to as NA(Not Available) or Null values in pandas. In DataFrame sometimes many datasets simply arrive with missing data, either because it exists and was not collected or it never existed. For Example, Suppose different user being surveyed may choose not to share their income, some user may choose not to share the address in this way many datasets went missing.It is very important to check for these missing values and find their location.[Back to top](Introduction)<jupyter_code>print("Are there Null Values in the dataset? ")
df_1.isnull().values.any()<jupyter_output>Are there Null Values in the dataset?<jupyter_text>As suspected, there are null values in the dataset, next we find the total percentage of these null values in our dataset.<jupyter_code>missing_values_count = df_1.isnull().sum()
# how many total missing values do we have?
total_cells = np.product(df_1.shape)
total_missing = missing_values_count.sum()

# percent of data that is missing
percent_missing = (total_missing/total_cells) * 100

print("Total percent of missing data: {} %".format (percent_missing))<jupyter_output>Total percent of missing data: 4.648292306613367 %<jupyter_text>Now that we know that 4.64% of our dataset is null, it doesn't seem like a lot, but that might not be true. 4% in the whole dataset might be a relatively small value, but we also need to check the distribution of these null values in the different columns, which we do below<jupyter_code># df_1.isnull().sum()
total = df_1.isnull().sum()[df_1.isnull().sum() != 0].sort_values(ascending = False)
percent = pd.Series(round(total/len(df_1)*100,2))
pd.concat([total, percent], axis=1, keys=['Total Missing', 'Percent'])<jupyter_output><empty_output><jupyter_text>As suspected, couple of columns like BuildingArea and YearBuilt have as much as 47% & 39% of data missing, which is quite significant.  1.4.5 Figure out Why the Data is Missing 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 questions you can ask yourself to help figure this out is this:   >**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 (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 probably do want to keep as NaN. On the other hand, if a value is missing because 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! :)> **Tip:** This is a great place to read over the dataset documentation if you haven't already! If you're working with a dataset that you've gotten from another person, you can also try reaching out to them to get more information.If you're doing very careful data analysis, this is the point at which you'd look at each column individually to figure out the best strategy for filling those missing values. Next, we'll cover some "quick and dirty" techniques that can help you with missing values but will probably also end up removing some useful information or adding some noise to your data.[Back to top](Introduction)   1.4.6 Handling the Missing Values  Next we will try to handle these missing values in our dataset. As the missing data might just be the key to a perfect ML algorithm.There are many ways data can end up with missing values. For example- A 2 bedroom house wouldn't include an answer for how large is the third bedroom- Someone being surveyed may choose not to share their income- Python libraries represent missing numbers as nan which is short for "not a number". Most machine learning libraries (including scikit-learn) give an error if you try to build a model using data with missing values. So you'll need to choose a strategy to handle these missing values.![title](Images\Missing_Values.jpg)[Back to top](Introduction)<jupyter_code>df_1_NN=df_1
df_1_NN=df_1_NN.fillna(0)
fig, axes = plt.subplots(nrows=2, ncols=2, figsize=(23,20))
plt.subplots_adjust(hspace=0.4)

z = pd.Series()
for col in df_1_NN.columns.values[2:]:
    if ((col!='Type')&(col!='Method')&(col!='SellerG')&(col!='Date')&(col!='CouncilArea')&(col!='Regionname')&(col!='Rooms')&(col!='Price')&(col!='Distance')&(col!='Postcode')&(col!='Bedroom2')&(col!='Bathroom')):
        colums=np.array(df_1_NN[col])
        z[col]=colums
for i in range(2):
    for j in range(2):
        
        x=z[i*2+j]
        y=z.index[i*2+j]
        sns.distplot(x,axlabel=y,ax=axes[i,j])
        
fig.suptitle('Distplot of all the numeric columns of the dataset',fontsize=30)        
plt.show()<jupyter_output><empty_output><jupyter_text>We can see from the plot that different numerical variables are of different types, some are categorical and some are continuous. We need to know the difefrent types of variables before we do data imputation. We can use one of the three modes of central tendency to fill in the missing values.**Which mode of central tendency we are using to fill in missing values?**Reasoning:- By looking at both plot we can say that Car is a categorical variable, Building Area and LandSize are continuous variables with exponential distributions(right skew with 0 being set where value is unknown) and Year Built is a bi-modal distribution (because the rows where they don't have data about the year it was built, it is set to 0)- For the distributions that are generally skewed and not normal we use **median** as a measure of central tendency.- **Mean** is not used because it is greatly affected by outliers which would may result in mean being skewed between outliers but median retains its position and is not as strongly influenced by the skewed values. - And as for **mode**, it is generally used for categorical data . Also in mode's case , two or more datapoints have same frequency, as in mode may have more than one value. So, we use median to fill in our missing values.  1.4.6.1 Drop missing values (Most Dirty Approach) If you're in a hurry or don't have a reason to figure out why your values are missing, one option you have is to just remove any rows or columns that contain missing values. (Note: It is not generally recommend to use this approch for important projects! It's usually worth it to take the time to go through your data and really look at all the columns with missing values one-by-one to really get to know your dataset.)If you're sure you want to drop rows with missing values, pandas does have a handy function, dropna() to help you do this[Back to top](Introduction)<jupyter_code>df_1_Drop=df_1.dropna()
df_1_Drop.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 6196 entries, 1 to 12212
Data columns (total 21 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   Suburb         6196 non-null   object 
 1   Address        6196 non-null   object 
 2   Rooms          6196 non-null   int64  
 3   Type           6196 non-null   object 
 4   Price          6196 non-null   float64
 5   Method         6196 non-null   object 
 6   SellerG        6196 non-null   object 
 7   Date           6196 non-null   object 
 8   Distance       6196 non-null   float64
 9   Postcode       6196 non-null   float64
 10  Bedroom2       6196 non-null   float64
 11  Bathroom       6196 non-null   float64
 12  Car            6196 non-null   float64
 13  Landsize       6196 non-null   float64
 14  BuildingArea   6196 non-null   float64
 15  YearBuilt      6196 non-null   float64
 16  CouncilArea    6196 non-null   object 
 17  Lattitude      6196 non-null   float64
 18  Longtit[...]<jupyter_text>Looks like we've deleted half of our dataset while trying to drop null values. This is because over half of the rows in our dataset had at least one missing value. Clearly this is not a very good technique as we are always looking to add to our sample size, not reduce it! We might have better luck removing all the columns that have at least one missing value instead.  1.4.6.2 Drop Columns with Missing Values The second simplest option is to drop columns with missing values.![title](Images\Data_Imp_1.png)Unless most values in the dropped columns are missing, the model loses access to a lot of (potentially useful!) information with this approach. As an extreme example, consider a dataset with 10,000 rows, where one important column is missing a single entry. This approach would drop the column entirely![Back to top](Introduction)<jupyter_code>df_1_Drop_Columns = df_1.dropna(axis=1)
df_1_Drop_Columns.head()
print("Columns in original dataset: %d \n" % df_1.shape[1])
print("Columns with na's dropped: %d" % df_1_Drop_Columns.shape[1])<jupyter_output>Columns in original dataset: 21 

Columns with na's dropped: 17<jupyter_text>We've lost quite a bit of data, but at this point we have successfully removed all the NaN's from our data.  1.4.6.3 Filling Missing Values (automatically) Another option is to try and fill in the missing values. We can use the Panda's fillna() function to fill in missing values in a dataframe for us. One option we have is to specify what we want the NaN values to be replaced with. Here, I'm saying that I would like to replace all the NaN values with 0.[Back to top](Introduction)<jupyter_code># replace all NA's with 0
df_1_FillNA=df_1.fillna(0)
df_1_FillNA.head()<jupyter_output><empty_output><jupyter_text>I could also be a bit more savvy and replace missing 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.)<jupyter_code># 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_1Bfill=df_1.fillna(method='bfill', axis=0).fillna(0)
df_1Bfill.head()<jupyter_output><empty_output><jupyter_text>Clearly this doesn't make sense here as it just takes value from the column below and replaces the null, there is no logical reasoning why this would work in this dataset, this is just to show how to use this technique.  1.4.6.4 Better Option: Imputation Imputation fills in the missing values with some number. For instance, we can fill in the mean value along each column.![title](Images\Data_Imp_2.png)The imputed value won't be exactly right in most cases, but it usually leads to more accurate models than you would get from dropping the column entirely.The default behavior fills in the mean value for imputation. Statisticians have researched more complex strategies, but those complex strategies typically give no benefit once you plug the results into sophisticated machine learning models.One (of many) nice things about Imputation is that it can be included in a scikit-learn Pipeline. Pipelines simplify model building, model validation and model deployment.[Back to top](Introduction)<jupyter_code>from sklearn.impute import SimpleImputer
my_imputer = SimpleImputer()
data_with_imputed_values = my_imputer.fit_transform(df_1.values)<jupyter_output><empty_output><jupyter_text>Uh-OH!, looks like one of the variables with null is a categorical variable, and obviuously we can't use a mean or median strategy there. For this we have to use encoding (Covered in part 4). Here we use a cleaver technique where we just impute the data with numerical values<jupyter_code>def split_num_categ(df_1):
    categorical_columns = []
    numeric_columns = []
    for c in df_1.columns:
        if df_1[c].map(type).eq(str).any(): #check if there are any strings in column
            categorical_columns.append(c)
        else:
            numeric_columns.append(c)

    #create two DataFrames, one for each data type
    data_numeric = df_1[numeric_columns]
    data_categorical = pd.DataFrame(df_1[categorical_columns])
    return data_numeric,data_categorical

data_numeric,data_categorical=split_num_categ(df_1)

from sklearn.preprocessing import Imputer
imp = Imputer(missing_values=np.nan, strategy='mean', axis=0)
data_numeric = pd.DataFrame(imp.fit_transform(data_numeric), columns = data_numeric.columns) #only apply imputer to numeric columns


#you could do something like one-hot-encoding of data_categorical here

#join the two masked dataframes back together
data_joined = pd.concat([data_numeric, data_categorical], axis = 1)
data_joined.head()<jupyter_output><empty_output><jupyter_text>1.4.6.5 Extension To Imputation Imputation is the standard approach, and it usually works well. However, imputed values may be systematically above or below their actual values (which weren't collected in the dataset). Or rows with missing values may be unique in some other way. In that case, your model would make better predictions by considering which values were originally missing.![title](Images\Data_Imp_3.png)In this approach, we impute the missing values, as before. And, additionally, for each column with missing entries in the original dataset, we add a new column that shows the location of the imputed entries.In some cases, this will meaningfully improve results. In other cases, it doesn't help at all.[Back to top](Introduction)<jupyter_code># make copy to avoid changing original data (when Imputing) and selecting only numeric columns to make our lives a little bit easy

melb_predictors_1 = df_1.drop(['Price'], axis=1)
X_1 = melb_predictors_1.select_dtypes(exclude=['object'])


# Get names of columns with missing values

cols_with_missing_1 = (col for col in X_1.columns 
                                 if X_1[col].isnull().any())
# make new columns indicating what will be imputed
for col in cols_with_missing_1:
    X_1[col + '_was_missing'] = X_1[col].isnull()

# Imputation

my_imputer = Imputer(missing_values=np.nan, strategy='mean', axis=0)
X_1 = pd.DataFrame(my_imputer.fit_transform(X_1), columns = X_1.columns)
X_1.head()<jupyter_output><empty_output><jupyter_text>1.5 Implementation [Back to top](Introduction)<jupyter_code># Copy the oroginal dataset
data = df_1

# Select target
y = data.Price

# To keep things simple, we'll use only numerical predictors
melb_predictors = data.drop(['Price'], axis=1)
X = melb_predictors.select_dtypes(exclude=['object'])

# Divide data into training and validation subsets
X_train, X_valid, y_train, y_valid = train_test_split(X, y, train_size=0.8, test_size=0.2,
                                                      random_state=0)

# Function for comparing different approaches
def score_dataset(X_train, X_valid, y_train, y_valid):
    model = RandomForestRegressor(n_estimators=10, random_state=0)
    model.fit(X_train, y_train)
    preds = model.predict(X_valid)
    return mean_absolute_error(y_valid, preds)
# Get names of columns with missing values
cols_with_missing = [col for col in X_train.columns
                     if X_train[col].isnull().any()]

# Drop columns in training and validation data
reduced_X_train = X_train.drop(cols_with_missing, axis=1)
reduced_X_valid = X_valid.drop(cols_with_missing, axis=1)

print("MAE from Dropping columns with missing values:")
print(score_dataset(reduced_X_train, reduced_X_valid, y_train, y_valid))
# Imputation
my_imputer_mean = SimpleImputer(missing_values=np.nan, strategy='mean')
my_imputer_median = SimpleImputer(missing_values=np.nan, strategy='median')

#Mean
imputed_X_train_Mean = pd.DataFrame(my_imputer_mean.fit_transform(X_train))
imputed_X_valid_Mean = pd.DataFrame(my_imputer_mean.transform(X_valid))

#Median
imputed_X_train_Median = pd.DataFrame(my_imputer_median.fit_transform(X_train))
imputed_X_valid_Median = pd.DataFrame(my_imputer_median.transform(X_valid))

# Imputation removed column names; put them back
imputed_X_train_Median.columns = X_train.columns
imputed_X_valid_Median.columns = X_valid.columns

imputed_X_train_Mean.columns = X_train.columns
imputed_X_valid_Mean.columns = X_valid.columns

print("MAE from Imputation-Mean):")
print(score_dataset(imputed_X_train_Mean, imputed_X_valid_Mean, y_train, y_valid))

print("MAE from Imputation-Median):")
print(score_dataset(imputed_X_train_Median, imputed_X_valid_Median, y_train, y_valid))
# Make copy to avoid changing original data (when imputing)
X_train_plus = X_train.copy()
X_valid_plus = X_valid.copy()

# Make new columns indicating what will be imputed
for col in cols_with_missing:
    X_train_plus[col + '_was_missing'] = X_train_plus[col].isnull()
    X_valid_plus[col + '_was_missing'] = X_valid_plus[col].isnull()

# Imputation
my_imputer_mean = SimpleImputer(missing_values=np.nan, strategy='mean')
my_imputer_median = SimpleImputer(missing_values=np.nan, strategy='median')

imputed_X_train_plus_mean = pd.DataFrame(my_imputer_mean.fit_transform(X_train_plus))
imputed_X_train_plus_median = pd.DataFrame(my_imputer_median.fit_transform(X_train_plus))

imputed_X_valid_plus_mean = pd.DataFrame(my_imputer_mean.transform(X_valid_plus))
imputed_X_valid_plus_median = pd.DataFrame(my_imputer_median.transform(X_valid_plus))

# Imputation removed column names; put them back
#Mean
imputed_X_train_plus_mean.columns = X_train_plus.columns

#Median
imputed_X_valid_plus_median.columns = X_valid_plus.columns

print("MAE from An Extension to Imputation-Mean):")
print(score_dataset(imputed_X_train_plus_mean, imputed_X_valid_plus_mean, y_train, y_valid))

print("MAE from An Extension to Imputation-Median):")
print(score_dataset(imputed_X_train_plus_median, imputed_X_valid_plus_median, y_train, y_valid))<jupyter_output>MAE from An Extension to Imputation-Mean):
178927.503183954
MAE from An Extension to Imputation-Median):
179669.92839960728
{'fraction_non_alphanumeric': 0.07595083937502423, 'fraction_numerical': 0.02434769123405575, 'mean_word_length': 4.46367295064605, 'pattern_counts': {'":': 0, '<': 78, '<?xml version=': 0, '>': 80, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 10, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '204838', 'license': 'Unlicense', 'n_tokens_mistral': 8003, 'n_tokens_neox': 7430, 'n_words': 3074, 'path': 'Data Science/Data_Preprocessing/Notebooks/INFO7390_Assignment_3_Mini_Project_One_Part_One.ipynb', 'repo_name': 'RushabhNisher/Data', 'size': '25793'}