Before start our lesson please download the datasets.
Give me some credit data
We will try to understand the data exploration, validation and data cleaning using a case study on loans data
Give me some credit data. It is loans data. Historical data are provided on 150,000 borrowers.
The final objective is to build a model that borrowers can use to help make the best financial decisions.
We generally get the data and data dictionary from the data team.
Steps in Data Exploration and Cleaning
Step-1: Basic details of the data
Step-2: Categorical variables exploration
Step-3: Continuous variables exploration
Step-4: Missing Values and Outlier Treatment
Step-1: Basic details of the data
In [ ]:
#Lab: Basic contents of the data
# Import “Give me some Credit\cs-training.csv”
# What are number of rows and columns
# Are there any suspicious variables?
# Are all the variable names correct?
# Display the variable formats
# Print the first 10 observations
# Do we have any unique identifier?
# Do the text and numeric variables have meaningful data?
# Are there some coded values in the data?
# Do all the variables appear to have data
In [ ]:
# Code: Basic contents of the data
In [47]:
import pandas as pd
loans=pd.read_csv("C:\\Users\\jyothi\\Google Drive\\Datasets\\Give me some Credit\\cs-training.csv")
loans
Out[47]:
In [48]:
#What are number of rows and columns
loans.shape
Out[48]:
In [49]:
#Are there any suspicious variables?
loans.columns.values
Out[49]:
In [50]:
#Display the variable formats
loans.dtypes
Out[50]:
In [51]:
#Print the first 10 observations
loans.head(10)
Out[51]:
In [52]:
#Do we have any unique identifier?
loans.columns.values
Out[52]:
Step-2: Categorical and Descrete variables exploration
In [ ]:
# Code: Frequencies
# What are the categorical and discrete variables? What are the continues variables.
# Find the frequencies of all class variables in the data
# Are there any variables with missing values?
# Are there any default values?
# Can you identify the variables with outliers?
# Are there any variables with other issues?
In [57]:
#What are the categorical and discrete variables? What are the continues variables.
loans.dtypes
Out[57]:
In [58]:
loans.head()
Out[58]:
In [59]:
#Find the frequencies of all class variables in the data
loans.columns.values
Out[59]:
In [60]:
#Find the frequencies of all class variables in the data
loans['SeriousDlqin2yrs'].value_counts()
Out[60]:
In [62]:
loans['age'].value_counts(sort=False)
Out[62]:
In [63]:
loans['NumberOfTime30-59DaysPastDueNotWorse'].value_counts(sort=False)
Out[63]:
In [64]:
loans['NumberOfOpenCreditLinesAndLoans'].value_counts(sort=False)
Out[64]:
In [65]:
loans['NumberOfTimes90DaysLate'].value_counts(sort=False)
Out[65]:
In [66]:
loans['NumberRealEstateLoansOrLines'].value_counts(sort=False)
Out[66]:
In [67]:
loans['NumberOfTime60-89DaysPastDueNotWorse'].value_counts(sort=False)
Out[67]:
In [68]:
loans['NumberOfDependents'].value_counts(sort=False)
Out[68]:
In [69]:
#Are there any variables with missing values?
loans.isnull().sum()
Out[69]:
Step-3: Continuous variables exploration
In [ ]:
#Code: Continuous variables summary
# List down the continuous variables
# Find summary statistics for each variable. Min, Max, Median, Mean, sd, Var
# Find Quartiles for each of the variables
# Create Box plots and identify outliers
# Find the percentage of missing values
# Find Percentiles and find percentage of outliers, if any P1, p5,p10,q1(p25),q3(p75), p90,p99
In [70]:
#List down the continuous variables
loans.dtypes
Out[70]:
In [79]:
loans.head()
Out[79]:
In [80]:
#Find summary statistics for each variable. Min, Max, Median, Mean, sd, Var
loans['RevolvingUtilizationOfUnsecuredLines'].describe()
Out[80]:
In [81]:
loans['MonthlyIncome'].describe()
Out[81]:
In [82]:
import numpy as np
variance = np.var(loans['RevolvingUtilizationOfUnsecuredLines'])
variance
Out[82]:
In [86]:
import numpy as np
np.var(loans['MonthlyIncome'])
Out[86]:
In [87]:
import numpy as np
np.std(loans['RevolvingUtilizationOfUnsecuredLines'])
Out[87]:
In [88]:
import numpy as np
np.std(loans['MonthlyIncome'])
Out[88]:
In [89]:
#Find Quartiles for each of the variables
loans['RevolvingUtilizationOfUnsecuredLines'].describe()
Out[89]:
In [90]:
loans['MonthlyIncome'].describe()
Out[90]:
This is an integer variable. It has missing values represented by ‘NA’. Its minimum value is 0, which is practically impossible. Mean is 6670 and median is 5400 without considering NA values.
In [92]:
#Create Box plots and identify outliers
import matplotlib.pyplot as plt
%matplotlib inline
loans.boxplot(column="RevolvingUtilizationOfUnsecuredLines")
Out[92]:
In [94]:
import matplotlib.pyplot as plt
%matplotlib inline
loans.boxplot(column="MonthlyIncome")
Out[94]:
In [98]:
#Find the missing values in MonthlyIncome variable
loans['MonthlyIncome'].isnull().sum()
Out[98]:
29731 values are missing in MonthlyIncome variable
In [99]:
#Find the percentage of missing values
loans['MonthlyIncome'].isnull().sum()/len(loans)
Out[99]:
In [100]:
#Find Percentiles and find percentage of outliers, if any P1, p5,p10,q1(p25),q3(p75), p90,p99
util_percentiles=loans['RevolvingUtilizationOfUnsecuredLines'].quantile([0.05, 0.1, 0.25, 0.5, 0.75, 0.80, 0.9,0.91,0.95,0.96,0.97,0.975,0.98,0.99,1])
round(util_percentiles,2)
Out[100]:
Code: Data Cleaning Scenario-1
In [118]:
# What percent are missing values in RevolvingUtilizationOfUnsecuredLines?
# Get the detailed percentile distribution
# Clean the variable, and create a new variable by removing all the issues
In [101]:
# # LAB: Data Cleaning Scenario-1
#What percent are missing values in RevolvingUtilizationOfUnsecuredLines?
#Get the detailed percentile distribution
util_percentiles=loans['RevolvingUtilizationOfUnsecuredLines'].quantile([0.05, 0.1, 0.25, 0.5, 0.75, 0.80, 0.9,0.91,0.95,0.96,0.97,0.975,0.98,0.99,1])
round(util_percentiles,2)
Out[101]:
In [102]:
#Clean the variable, and create a new variable by removing all the issues
#If utilization is more than 1 then it can be replaced by median
median_util=loans['RevolvingUtilizationOfUnsecuredLines'].median()
median_util
Out[102]:
In [103]:
loans['util_new']=loans['RevolvingUtilizationOfUnsecuredLines']
loans['util_new'][loans['util_new']>1]=median_util
loans['util_new']
Out[103]:
In [104]:
# percentile distribution for new variable
util_percentiles1=loans['util_new'].quantile([0.05, 0.1, 0.25, 0.5, 0.75, 0.80, 0.9,0.91,0.95,0.96,0.97,0.975,0.98,0.99,1])
round(util_percentiles1,2)
Out[104]:
Code: Data Cleaning Scenario-2
In [ ]:
# What is the issue with NumberOfTime30_59DaysPastDueNotWorse
# Draw a frequency table
# What percent of the values are erroneous?
# Clean the variable- Look at the cross tab of variable vs target. Impute based on target .
# Create frequency table for cleaned variable
In [105]:
# # LAB: Data Cleaning Scenario-2
#What is the issue with NumberOfTime30_59DaysPastDueNotWorse
#Draw a frequency table
freq_table_30dpd=loans['NumberOfTime30-59DaysPastDueNotWorse'].value_counts(sort=False)
freq_table_30dpd
Out[105]:
In [106]:
#One month defaults frequency can't be beyond 24 in last 24 months
#What percent of the values are erroneous?
freq_table_30dpd[13:len(freq_table_30dpd)]
freq_table_30dpd[13:len(freq_table_30dpd)].sum()/freq_table_30dpd.sum()
Out[106]:
In [107]:
#Clean the variable- Look at the cross tab of variable vs target. Impute based on target .
#Cross tab with target
import pandas as pd
cross_tab_30dpd_target=pd.crosstab(loans['NumberOfTime30-59DaysPastDueNotWorse'],loans['SeriousDlqin2yrs'])
cross_tab_30dpd_target
Out[107]:
In [108]:
#Cross tab row Percentages
cross_tab_30dpd_target_percent=cross_tab_30dpd_target.astype(float).div(cross_tab_30dpd_target.sum(axis=1), axis=0)
round(cross_tab_30dpd_target_percent,2)
Out[108]:
In [109]:
loans['num_30_59_dpd_new']=loans['NumberOfTime30-59DaysPastDueNotWorse']
loans['num_30_59_dpd_new'][loans['num_30_59_dpd_new']>12]=6
loans['num_30_59_dpd_new']
Out[109]:
In [110]:
loans['num_30_59_dpd_new'].value_counts(sort=False)
Out[110]:
LAB: Monthly Income
In [ ]:
# Find the missing value percentage in monthly income
# Create an indicator variable for missing and non-missing
# Replace the missing values with median
In [111]:
# # Data Cleaning Scenario-3
#Find the missing value percentage in monthly income
loans['MonthlyIncome'].isnull().sum()
loans['MonthlyIncome'].isnull().sum()/len(loans)
Out[111]:
In [115]:
#Once identified where missing values exist, the next task usually is to fill them (data imputation). Depending upon the context,
#in this case, I am assigning median value to all those positions where missing value is present:
loans['MonthlyIncome_ind']=1
loans['MonthlyIncome_ind'][loans['MonthlyIncome'].isnull()]=0
loans['MonthlyIncome_ind'].value_counts(sort=False)
Out[115]:
In [116]:
loans['MonthlyIncome_new']=loans['MonthlyIncome']
loans['MonthlyIncome_new'][loans['MonthlyIncome'].isnull()]=loans['MonthlyIncome'].median()
round(loans['MonthlyIncome_new'].describe())
Out[116]:
In [119]:
#Remaining Variables Imputation
# Debt Ratio: Imputation
# NumberOfOpenCreditLinesAndLoans : No issues in this variable
# NumberOfTimes90DaysLate: Imputation similar to NumberOfTime30_59DaysPastDueNotW
# NumberRealEstateLoansOrLines: : No issues in this variable
# NumberOfTime60_89DaysPastDueNotW: Imputation similar to NumberOfTime30_59DaysPastDueNotW
# ConclusionNumberOfDependents: Impute based on target variable
In [120]:
#Conclusion
#Data cleaning needs patience, we need to clean for each individual variable
