<jupyter_start><jupyter_text>Data Preparation  Import Libraries<jupyter_code>import numpy as np
import pandas as pd<jupyter_output><empty_output><jupyter_text>Import DataThe dataset contains all available data for more than 800,000 consumer loans issued from 2007 to 2015 by Lending Club: a large US peer-to-peer lending company. There are several different versions of this dataset. We have used a version available on kaggle.com. You can find it here: https://www.kaggle.com/wendykan/lending-club-loan-data/version/1We divided the data into two periods because we assume that some data are available at the moment when we need to build Expected Loss models, and some data comes from applications after. Later, we investigate whether the applications we have after we built the Probability of Default (PD) model have similar characteristics with the applications we used to build the PD model.<jupyter_code>loan_data_backup = pd.read_csv('../Dataset/loan_data_2007_2014.csv')
loan_data = loan_data_backup.copy()<jupyter_output><empty_output><jupyter_text>Explore Data<jupyter_code>loan_data
pd.options.display.max_columns = None
#pd.options.display.max_rows = None
# Sets the pandas dataframe options to display all columns/ rows.
loan_data
loan_data.head()
loan_data.tail()
loan_data.columns.values
# Displays all column names.
loan_data.info()
# Displays column names, complete (non-missing) cases per column, and datatype per column.<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 466285 entries, 0 to 466284
Data columns (total 75 columns):
Unnamed: 0                     466285 non-null int64
id                             466285 non-null int64
member_id                      466285 non-null int64
loan_amnt                      466285 non-null int64
funded_amnt                    466285 non-null int64
funded_amnt_inv                466285 non-null float64
term                           466285 non-null object
int_rate                       466285 non-null float64
installment                    466285 non-null float64
grade                          466285 non-null object
sub_grade                      466285 non-null object
emp_title                      438697 non-null object
emp_length                     445277 non-null object
home_ownership                 466285 non-null object
annual_inc                     466281 non-null float64
verification_status            466285 non-null object
issue_d                   [...]<jupyter_text>General Preprocessing  Preprocessing few continuous variables<jupyter_code>loan_data['emp_length'].unique()
loan_data['emp_length_int'] = loan_data['emp_length'].str.replace('\+ years','')
loan_data['emp_length_int'] = loan_data['emp_length_int'].str.replace('< 1 year',str(0))
loan_data['emp_length_int'] = loan_data['emp_length_int'].str.replace('n/a',str(0))
loan_data['emp_length_int'] = loan_data['emp_length_int'].str.replace(' years','')
loan_data['emp_length_int'] = loan_data['emp_length_int'].str.replace(' year','')
loan_data['emp_length_int'].describe()
loan_data.emp_length_int = pd.to_numeric(loan_data.emp_length_int)
loan_data.emp_length_int.describe()
loan_data['term_int'] = loan_data['term'].str.replace(' months','')
loan_data.term_int.value_counts()
loan_data.term_int = pd.to_numeric(loan_data.term_int)
loan_data.term_int[0]
loan_data.earliest_cr_line
loan_data['earliest_cr_line_date'] = pd.to_datetime(loan_data.earliest_cr_line,format='%b-%y')
loan_data.earliest_cr_line_date
pd.to_datetime('2017-12-01') - loan_data.earliest_cr_line_date
loan_data['mths_since_earliest_cr_line'] = round(pd.to_numeric((pd.to_datetime('2017-12-01') - loan_data.earliest_cr_line_date)/np.timedelta64(1,'M')))
loan_data['mths_since_earliest_cr_line'].describe()
# observe the minimum value
loan_data.mths_since_earliest_cr_line.max()
loan_data.mths_since_earliest_cr_line[loan_data.mths_since_earliest_cr_line < 0] = loan_data.mths_since_earliest_cr_line.max()
min(loan_data['mths_since_earliest_cr_line'])
loan_data['mths_since_issue_d']=round((pd.to_datetime('2017-12-01') - pd.to_datetime(loan_data['issue_d'],format = '%b-%y'))/np.timedelta64(1,'M'))<jupyter_output><empty_output><jupyter_text>Preprocessing few discrete variablesgradesub_gradehome_ownershipverification_statusloan_statuspurposeaddr_stateinitial_list_status<jupyter_code>loan_data.info()
pd.get_dummies(loan_data['grade'],prefix = 'grade', prefix_sep = ":")
loan_data_dummies = [pd.get_dummies(loan_data['grade'],prefix = 'grade', prefix_sep = ":"),
                    pd.get_dummies(loan_data['sub_grade'],prefix = 'sub_grade', prefix_sep = ":"),
                    pd.get_dummies(loan_data['home_ownership'],prefix = 'home_ownership', prefix_sep = ":"),
                    pd.get_dummies(loan_data['verification_status'],prefix = 'verification_status', prefix_sep = ":"),
                    pd.get_dummies(loan_data['loan_status'],prefix = 'loan_status', prefix_sep = ":"),
                    pd.get_dummies(loan_data['purpose'],prefix = 'purpose', prefix_sep = ":"),
                    pd.get_dummies(loan_data['addr_state'],prefix = 'addr_state', prefix_sep = ":"),
                    pd.get_dummies(loan_data['initial_list_status'],prefix = 'initial_list_status', prefix_sep = ":")]
loan_data_dummies = pd.concat(loan_data_dummies,axis = 1)
type(loan_data_dummies)
loan_data = pd.concat([loan_data,loan_data_dummies],axis = 1)
loan_data.columns.values<jupyter_output><empty_output><jupyter_text>Check for missing values and clean<jupyter_code>loan_data.isnull()
pd.options.display.max_rows = None
loan_data.isnull().sum()<jupyter_output><empty_output><jupyter_text>Columns of our interestannual_inc,delinq_2yrs,inq_last_6mths,open_acc,pub_rec,total_acc,acc_now_delinq,total_rev_hi_lim,emp_length_int,mths_since_earliest_cr_line Ways to deal with missing values1. To remove the observations with missing values2. Impute the values with other relevant ways Dictionary Definition of the column we are preprocessing ``total_rev_hi_lim --> Total revolving high credit/credit limit``If it is missing we can impute it with the loan disbursed/funded amount!<jupyter_code>loan_data['total_rev_hi_lim'].head()
loan_data['total_rev_hi_lim'].fillna(loan_data['funded_amnt'],inplace = True)
loan_data['total_rev_hi_lim'].isnull().sum()<jupyter_output><empty_output><jupyter_text>Homework<jupyter_code>loan_data['annual_inc'].head()
loan_data.annual_inc.isnull().sum()
loan_data.annual_inc.fillna(loan_data.annual_inc.mean(),inplace=True)<jupyter_output><empty_output><jupyter_text>For the rest of the columns‘mths_since_earliest_cr_line’‘acc_now_delinq’‘total_acc’‘pub_rec’‘open_acc’‘inq_last_6mths’‘delinq_2yrs’‘emp_length_int’we will fill the values with zeroes<jupyter_code>for i in list(['acc_now_delinq','total_acc','pub_rec','open_acc','inq_last_6mths','delinq_2yrs','emp_length_int']):
    loan_data[i].fillna(0,inplace = True)
loan_data.loc[:,['acc_now_delinq','total_acc','pub_rec','open_acc','inq_last_6mths','delinq_2yrs','emp_length_int']].isnull().sum()<jupyter_output><empty_output><jupyter_text>PD Model: Data PreparationDependent Variable: ```loan_status``` columnDefault Definition:Common Default Definition: 90 days past due!Fraudulent Loans are also default loans  Logistic RegressionEstimates the relationship between two things, \begin{equation*}ln(odds) =  \sum_{j=1}^m \beta_j X_j \end{equation*}in our case we have\begin{equation*}ln(\frac{Non-Defaults}{Defaults}) =  \sum_{j=1}^m \beta_j X_j \end{equation*}  InterpretabilityModel should be made in such a way that even the people who have never worked with Statistical modelling should be able to interpret the modelSo, the independent variables that are going to be used should be **Dummy Variables** i.e. binary categorical variables  Dependent Variable: Good/Bad (Default) definition.Default and Non-Default accounts<jupyter_code>loan_data['loan_status'].unique()
loan_data['loan_status'].value_counts()
loan_data['loan_status'].value_counts() / loan_data['loan_status'].count()<jupyter_output><empty_output><jupyter_text>We will now create a variable which will facilitate us whether the loan is good or badgood --> 1bad --> 0<jupyter_code>loan_data['good_bad'] = np.where(loan_data['loan_status'].isin(['Charged Off','Default',
                                                                'Does not meet the credit policy. Status:Charged Off',
                                                                'Late (31-120 days)']), 0 , 1)<jupyter_output><empty_output><jupyter_text>peeping into the Independent Variables  Fine ClassingIf we have values ranging from 0-100If we make equidistant classes and turn the continuous/discrete data into categories, we call it fine classing Weight of EvidenceWoE shows to what extent an independent variable would predict a dependent variable.How much evidence does the independent variable have with respect to the differences in the dependent variable.In more clear sense, we can understand it this way,WoE shows the extent to which each of the different categories of an independent variable will explain the dependent variable.The formula for calculating WoE in our case would be \begin{equation*}WoE_i= ln\left(\frac{percentage-of-good_i}{percentage-of-bad_i}\right) \end{equation*} Coarse ClassingWhen we categorize the continuous or discrete values into unequal distance categories, this process if called Coarse Classing  Information Value (We are not using this)How much information the original independent variable brings with respect to explaining the dependent variable\begin{equation*}Information Value =   \sum_{i=1}^k \left(PercentageOfGood - PercentageOfBad \right) x \left(ln\left(\frac{percentage-of-good_i}{percentage-of-bad_i}\right) \right)\end{equation*} or\begin{equation*}Information Value =   \sum_{i=1}^k \left(PercentageOfGood  - PercentageOfBad \right) x \left(WoE \right)\end{equation*}Information Value is always between 0-1Greater the information value means the original value yields more information with respect to the outcomeValues of IV have significance in the below manner0 0.02 0.1 0.3 0.5 < IV < 1 - Suspiciously high, too good to be true Predictive Power  Splitting the data into train and test<jupyter_code>from sklearn.model_selection import train_test_split
# Imports the libraries we need.
loan_data_inputs_train, loan_data_inputs_test, loan_data_targets_train, loan_data_targets_test = train_test_split(loan_data.drop('good_bad', axis = 1), loan_data['good_bad'])
# We split two dataframes with inputs and targets, each into a train and test dataframe, and store them in variables.
loan_data_inputs_test.shape
loan_data_targets_test.shape
loan_data_inputs_train.shape
loan_data_targets_train.shape
loan_data_inputs_train, loan_data_inputs_test, loan_data_targets_train, loan_data_targets_test = train_test_split(loan_data.drop('good_bad', axis = 1), loan_data['good_bad'], test_size = 0.2, random_state = 42)
# We split two dataframes with inputs and targets, each into a train and test dataframe, and store them in variables.
# This time we set the size of the test dataset to be 20%.
# Respectively, the size of the train dataset becomes 80%.
# We also set a specific random state.
# This would allow us to perform the exact same split multimple times.
# This means, to assign the exact same observations to the train and test datasets.
loan_data_inputs_train.shape
# Displays the size of the dataframe.
loan_data_targets_train.shape
# Displays the size of the dataframe.
loan_data_inputs_test.shape
# Displays the size of the dataframe.
loan_data_targets_test.shape
# Displays the size of the dataframe.<jupyter_output><empty_output><jupyter_text>Data Preparation: An Example<jupyter_code>#####
df_inputs_prepr = loan_data_inputs_train
df_targets_prepr = loan_data_targets_train
#####
#df_inputs_prepr = loan_data_inputs_test
#df_targets_prepr = loan_data_targets_test
df_inputs_prepr['grade'].unique()
# Displays unique values of a column.
df1 = pd.concat([df_inputs_prepr['grade'], df_targets_prepr], axis = 1)
# Concatenates two dataframes along the columns.
df1.head()
df1.groupby(df1.columns.values[0], as_index = False)[df1.columns.values[1]].count()
# Groups the data according to a criterion contained in one column.
# Does not turn the names of the values of the criterion as indexes.
# Aggregates the data in another column, using a selected function.
# In this specific case, we group by the column with index 0 and we aggregate the values of the column with index 1.
# More specifically, we count them.
# In other words, we count the values in the column with index 1 for each value of the column with index 0.
df1.groupby(df1.columns.values[0], as_index = False)[df1.columns.values[1]].mean()
# Groups the data according to a criterion contained in one column.
# Does not turn the names of the values of the criterion as indexes.
# Aggregates the data in another column, using a selected function.
# Here we calculate the mean of the values in the column with index 1 for each value of the column with index 0.
df1 = pd.concat([df1.groupby(df1.columns.values[0], as_index = False)[df1.columns.values[1]].count(),
                df1.groupby(df1.columns.values[0], as_index = False)[df1.columns.values[1]].mean()], axis = 1)
# Concatenates two dataframes along the columns.
df1# 
df1 = df1.iloc[:, [0, 1, 3]]
# Selects only columns with specific indexes.
df1
df1.columns = [df1.columns.values[0], 'n_obs', 'prop_good']
# Changes the names of the columns of a dataframe.
df1

df1['prop_n_obs'] = df1['n_obs'] / df1['n_obs'].sum()
# We divide the values of one column by he values of another column and save the result in a new variable.
df1
df1['n_good'] = df1['prop_good'] * df1['n_obs']
# We multiply the values of one column by he values of another column and save the result in a new variable.
df1['n_bad'] = (1 - df1['prop_good']) * df1['n_obs']
df1

df1['prop_n_good'] = df1['n_good'] / df1['n_good'].sum()
df1['prop_n_bad'] = df1['n_bad'] / df1['n_bad'].sum()
df1
df1['WoE'] = np.log(df1['prop_n_good'] / df1['prop_n_bad'])
# We take the natural logarithm of a variable and save the result in a nex variable.
df1
df1 = df1.sort_values(['WoE'])
# Sorts a dataframe by the values of a given column.
df1 = df1.reset_index(drop = True)
# We reset the index of a dataframe and overwrite it.
df1
df1['diff_prop_good'] = df1['prop_good'].diff().abs()
# We take the difference between two subsequent values of a column. Then, we take the absolute value of the result.
df1['diff_WoE'] = df1['WoE'].diff().abs()
# We take the difference between two subsequent values of a column. Then, we take the absolute value of the result.
df1<jupyter_output><empty_output><jupyter_text>Preprocessing Discrete Variables: Automating Calculaions<jupyter_code># WoE function for discrete unordered variables
def woe_discrete(df, discrete_variabe_name, good_bad_variable_df):
    df = pd.concat([df[discrete_variabe_name], good_bad_variable_df], axis = 1)
    df = pd.concat([df.groupby(df.columns.values[0], as_index = False)[df.columns.values[1]].count(),
                    df.groupby(df.columns.values[0], as_index = False)[df.columns.values[1]].mean()], axis = 1)
    df = df.iloc[:, [0, 1, 3]]
    df.columns = [df.columns.values[0], 'n_obs', 'prop_good']
    df['prop_n_obs'] = df['n_obs'] / df['n_obs'].sum()
    df['n_good'] = df['prop_good'] * df['n_obs']
    df['n_bad'] = (1 - df['prop_good']) * df['n_obs']
    df['prop_n_good'] = df['n_good'] / df['n_good'].sum()
    df['prop_n_bad'] = df['n_bad'] / df['n_bad'].sum()
    df['WoE'] = np.log(df['prop_n_good'] / df['prop_n_bad'])
    df = df.sort_values(['WoE'])
    df = df.reset_index(drop = True)
    df['diff_prop_good'] = df['prop_good'].diff().abs()
    df['diff_WoE'] = df['WoE'].diff().abs()
    df['IV'] = (df['prop_n_good'] - df['prop_n_bad']) * df['WoE']
    df['IV'] = df['IV'].sum()
    return df
# Here we combine all of the operations above in a function.
# The function takes 3 arguments: a dataframe, a string, and a dataframe. The function returns a dataframe as a result.
# 'grade'
df_temp = woe_discrete(df_inputs_prepr, 'grade', df_targets_prepr)
# We execute the function we defined with the necessary arguments: a dataframe, a string, and a dataframe.
# We store the result in a dataframe.
df_temp<jupyter_output><empty_output><jupyter_text>Preprocessing Discrete Variables: Visualizing Results<jupyter_code>import matplotlib.pyplot as plt
import seaborn as sns
# Imports the libraries we need.
sns.set()
# We set the default style of the graphs to the seaborn style. 
# Below we define a function that takes 2 arguments: a dataframe and a number.
# The number parameter has a default value of 0.
# This means that if we call the function and omit the number parameter, it will be executed with it having a value of 0.
# The function displays a graph.
def plot_by_woe(df_WoE, rotation_of_x_axis_labels = 0):
    x = np.array(df_WoE.iloc[:, 0].apply(str))
    # Turns the values of the column with index 0 to strings, makes an array from these strings, and passes it to variable x.
    y = df_WoE['WoE']
    # Selects a column with label 'WoE' and passes it to variable y.
    plt.figure(figsize=(18, 6))
    # Sets the graph size to width 18 x height 6.
    plt.plot(x, y, marker = 'o', linestyle = '--', color = 'k')
    # Plots the datapoints with coordiantes variable x on the x-axis and variable y on the y-axis.
    # Sets the marker for each datapoint to a circle, the style line between the points to dashed, and the color to black.
    plt.xlabel(df_WoE.columns[0])
    # Names the x-axis with the name of the column with index 0.
    plt.ylabel('Weight of Evidence')
    # Names the y-axis 'Weight of Evidence'.
    plt.title(str('Weight of Evidence by ' + df_WoE.columns[0]))
    # Names the grapth 'Weight of Evidence by ' the name of the column with index 0.
    plt.xticks(rotation = rotation_of_x_axis_labels)
    # Rotates the labels of the x-axis a predefined number of degrees.
plot_by_woe(df_temp)
# We execute the function we defined with the necessary arguments: a dataframe.
# We omit the number argument, which means the function will use its default value, 0.<jupyter_output><empty_output><jupyter_text>Preprocessing Discrete Variables: Creating Dummy Variables, Part 1<jupyter_code># 'home_ownership'
df_temp = woe_discrete(df_inputs_prepr, 'home_ownership', df_targets_prepr)
# We calculate weight of evidence.
df_temp
plot_by_woe(df_temp)
# We plot the weight of evidence values.
# There are many categories with very few observations and many categories with very different "good" %.
# Therefore, we create a new discrete variable where we combine some of the categories.
# 'OTHERS' and 'NONE' are riskiest but are very few. 'RENT' is the next riskiest.
# 'ANY' are least risky but are too few. Conceptually, they belong to the same category. Also, their inclusion would not change anything.
# We combine them in one category, 'RENT_OTHER_NONE_ANY'.
# We end up with 3 categories: 'RENT_OTHER_NONE_ANY', 'OWN', 'MORTGAGE'.
df_inputs_prepr['home_ownership:RENT_OTHER_NONE_ANY'] = sum([df_inputs_prepr['home_ownership:RENT'], df_inputs_prepr['home_ownership:OTHER'],
                                                      df_inputs_prepr['home_ownership:NONE'],df_inputs_prepr['home_ownership:ANY']])
# 'RENT_OTHER_NONE_ANY' will be the reference category.

# Alternatively:
#loan_data.loc['home_ownership' in ['RENT', 'OTHER', 'NONE', 'ANY'], 'home_ownership:RENT_OTHER_NONE_ANY'] = 1
#loan_data.loc['home_ownership' not in ['RENT', 'OTHER', 'NONE', 'ANY'], 'home_ownership:RENT_OTHER_NONE_ANY'] = 0
#loan_data.loc['loan_status' not in ['OWN'], 'home_ownership:OWN'] = 1
#loan_data.loc['loan_status' not in ['OWN'], 'home_ownership:OWN'] = 0
#loan_data.loc['loan_status' not in ['MORTGAGE'], 'home_ownership:MORTGAGE'] = 1
#loan_data.loc['loan_status' not in ['MORTGAGE'], 'home_ownership:MORTGAGE'] = 0<jupyter_output><empty_output><jupyter_text>Preprocessing Discrete Variables: Creating Dummy Variables, Part 2<jupyter_code># 'addr_state'
df_inputs_prepr['addr_state'].unique()
df_temp = woe_discrete(df_inputs_prepr, 'addr_state', df_targets_prepr)
# We calculate weight of evidence.
df_temp
plot_by_woe(df_temp)
# We plot the weight of evidence values.
if ['addr_state:ND'] in df_inputs_prepr.columns.values:
    pass
else:
    df_inputs_prepr['addr_state:ND'] = 0
plot_by_woe(df_temp.iloc[2: -2, : ])
# We plot the weight of evidence values.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10709060762703598, 'fraction_numerical': 0.01750707110114113, 'mean_word_length': 5.013782991202346, 'pattern_counts': {'":': 9, '<': 73, '<?xml version=': 0, '>': 72, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '365470', 'license': 'MIT', 'n_tokens_mistral': 6936, 'n_tokens_neox': 6486, 'n_words': 1869, 'path': 'Credit Risk Modeling Analysis.ipynb', 'repo_name': 'ashupadhyay/Credit-Risk-Modelling-Python', 'size': '20506'}