<jupyter_start><jupyter_text>INFO6105 Data Science Methods and Tools  CRN:18687 Topic: Loan Default Prediction Team Members: <NAME> (001388261) <NAME> (001423854) <NAME> (001387983) Lending Club is an online crowdfunding platform for peer to peer lending, facilitating personal loans, business loans, and financing. Borrowers access loans through an online or mobile interface and investors provide capital in exchange for earning interest (peer-to-peer (P2P) lending). Being an online-only operation results in cheaper operating costs and overheads, thus this offers lenders higher returns compared to traditional bank products. Borrowers can borrow money at lower interest rates, even after accounting for platform and credit checking fees. Interest rates are set by lenders who compete for the lowest rate based on a reverse auction model or a fixed rate based on borrower's credit profile.<jupyter_code>#Importing all necessary packages

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.offline as py
py.init_notebook_mode(connected=True)
import plotly.graph_objs as go
import plotly.tools as tls
from collections import Counter

import sys
import warnings

if not sys.warnoptions:
    warnings.simplefilter("ignore")
#Importing the Lending Club Loan Data

df = pd.read_csv('loan.csv', low_memory = False)
df_new_copy = df.copy()
df.shape
df.head()
print('No of Names')
print(len(df.columns))
print('Column Names')
print(list(df.columns))<jupyter_output>Column Names
['id', 'member_id', 'loan_amnt', 'funded_amnt', 'funded_amnt_inv', 'term', 'int_rate', 'installment', 'grade', 'sub_grade', 'emp_title', 'emp_length', 'home_ownership', 'annual_inc', 'verification_status', 'issue_d', 'loan_status', 'pymnt_plan', 'url', 'desc', 'purpose', 'title', 'zip_code', 'addr_state', 'dti', 'delinq_2yrs', 'earliest_cr_line', 'inq_last_6mths', 'mths_since_last_delinq', 'mths_since_last_record', 'open_acc', 'pub_rec', 'revol_bal', 'revol_util', 'total_acc', 'initial_list_status', 'out_prncp', 'out_prncp_inv', 'total_pymnt', 'total_pymnt_inv', 'total_rec_prncp', 'total_rec_int', 'total_rec_late_fee', 'recoveries', 'collection_recovery_fee', 'last_pymnt_d', 'last_pymnt_amnt', 'next_pymnt_d', 'last_credit_pull_d', 'collections_12_mths_ex_med', 'mths_since_last_major_derog', 'policy_code', 'application_type', 'annual_inc_joint', 'dti_joint', 'verification_status_joint', 'acc_now_delinq', 'tot_coll_amt', 'tot_cur_bal', 'open_acc_6m', 'open_act_il', 'open_il_[...]<jupyter_text>Exploratory Data Analysis:Here I will go throught important features and analyse them. We'll use this information while data cleaning as well as training machine learning model. Target Variable - Loan status<jupyter_code>df['loan_status'].value_counts()
#Visualization of Target Variable with the Loan Amount

plt.figure(figsize=(12,6))
sns.boxplot(df['loan_amnt'],df['loan_status'])<jupyter_output><empty_output><jupyter_text>We considered Fully Paid loans and Charged off, Late (31-120 days), Default loans as these categories tells surely about loan repayment<jupyter_code>default=['Charged Off','Late (31-120 days)','Default']
df['loan_status']=df['loan_status'].apply(lambda x: 'Default' if x in default else x)
df['loan_status'].value_counts()<jupyter_output><empty_output><jupyter_text>Creating Target Variable Default and Fully paid<jupyter_code>df=df[(df['loan_status']=='Default') | (df['loan_status']=='Fully Paid')]
df['loan_status'].value_counts()
#Visualization of Target Variable which has only 2 categories:
#1. Fully Paid
#2. Defaulter

plt.figure(figsize=(12,6))
sns.boxplot(df['loan_amnt'],df['loan_status'])
#Finding the amount of missing values in the entire dataset

missing_val_count_by_column = (df.isnull().sum())
total_entries = len(df)*len(df.columns)
percent_missing_entries = sum(missing_val_count_by_column)/total_entries
percent_missing_columns = len(missing_val_count_by_column[missing_val_count_by_column > 0.5*len(df)])/len(df.columns)

print("Total missing entries:")
print(sum(missing_val_count_by_column),'\n')

print("Percentage of missing entries:")
print(percent_missing_entries*100, '\n')

print("Percentage of columns with more than 50 percent missing values:")
print(percent_missing_columns*100, '\n')

print("Number of columns with more than 50 percent missing values:")
print(len(missing_val_count_by_column[missing_val_count_by_column > 0.5*len(df)]))
print("Columns with more than 50 percent missing values:")
print(missing_val_count_by_column[missing_val_count_by_column > 0.5*len(df)])
#Checking different types of datatypes present in the dataset

df.dtypes.sort_values().to_frame('feature_type').groupby(by = 'feature_type').size().to_frame('count').reset_index()
#Visualizing Loan term distribution count for 2 terms: 36 months and 60 months

plt.figure(figsize=(8, 5))
sns.barplot(y=df.term.value_counts(), x=df.term.value_counts().index)
plt.xticks(rotation=0)
plt.title("Loan's Term Distribution")
plt.ylabel("Count")
#Visualizing Loan term distribution count for 2 terms: 36 months and 60 months with respect to Grade and Loan Amount

plt.figure(figsize=(16,8))
sns.boxplot(x=df.grade, y=df.loan_amnt, hue=df.term, palette="inferno")
plt.xlabel("Grade ")
plt.ylabel("Loan Amount")
plt.legend(bbox_to_anchor=(1, 1), loc=2, borderaxespad=0.5)
#Plotting which employment type has taken the maximum amount of loans

trace0 = go.Bar(
    x = df.emp_title.value_counts()[:40].index.values,
    y = df.emp_title.value_counts()[:40].values,
    marker=dict(
        color=df.emp_title.value_counts()[:40].values
    ),
)

data = [trace0]

layout = go.Layout(
    yaxis=dict(
        title='Count'
    ),
    xaxis=dict(
        title='Employment name'
    ),
    title='TOP 40 Employment Title'
)

fig = go.Figure(data=data, layout=layout)

py.iplot(fig, filename='emp-title-bar')
df['issue_year']=df['issue_d'].apply(lambda x:int(x.split('-')[1]))
#Plotting the count of loan status with the year in which it has been issued

year_wise =df.groupby(by= [df.issue_year])[['loan_status']].count()
year_wise.rename(columns={"loan_status": "count"},inplace=True)
ax =year_wise.plot(figsize=(20,8))
year_wise.plot(kind='bar',figsize=(20,8),ax = ax)
plt.show()
#Plotting the Interest Rate vs Grade distribution which tells us which Grade has the maximum Interest Rate

interest_by_grade=df[['int_rate','grade']].groupby('grade').mean()
plt.bar(x=interest_by_grade.index,height=interest_by_grade['int_rate'])
plt.title('Interest Rate Vs. Grade')
#Plotting two bar graphs
#1.Describes the count of people taking Loan in which Grade
#2.Describes the loan purposes with the count

fig, ax = plt.subplots(2, 1, figsize=(12, 12))
plt.subplots_adjust(wspace=1.0, hspace=0.50)
df.grade.value_counts().plot(kind="bar", ax=ax[0])
ax[0].set_title("Grade Count")
df.purpose.value_counts().plot(kind="bar", ax=ax[1])
ax[1].set_title("Loan Purposes")
plt.xticks(rotation=60)
#Plotting the bar graph describing which loan purposes type has the maximum count

plt.figure(figsize=(12,5))
sns.barplot(x=df.title.value_counts()[:10].index, y=df.title.value_counts()[:10], data=df, palette='inferno')
plt.xticks(rotation=30)
def getMonth(x):
    return x.split('-')[0]

df['Month'] = df.issue_d.apply(getMonth)

#Line graph to depict the loan distribution amount by months for every grade

plt.figure(figsize = (14,6))

g = sns.pointplot(x='Month', y='loan_amnt', 
              data=df, hue='grade')
g.set_xticklabels(g.get_xticklabels(),rotation=90)
g.set_xlabel("Duration Distribuition", fontsize=15)
g.set_ylabel("Mean amount", fontsize=15)
g.set_title("Loan Amount by Months", fontsize=20)
plt.legend(bbox_to_anchor=(1, 1), loc=2, borderaxespad=0.5)
plt.show()
#Line graph to depict the loan distribution amount by years for every grade

plt.figure(figsize = (14,6))

g = sns.pointplot(x='issue_year', y='loan_amnt', 
              data=df, hue='grade')
g.set_xticklabels(g.get_xticklabels(),rotation=90)
g.set_xlabel("Duration Distribuition", fontsize=15)
g.set_ylabel("Mean amount", fontsize=15)
g.set_title("Loan Amount by Years", fontsize=20)
plt.legend(bbox_to_anchor=(1, 1), loc=2, borderaxespad=0.5)
plt.show()
#Graph tells us number of loan defaulters in each state

fig = plt.figure(figsize=(18,10))
df[df['loan_status']=='Default'].groupby('addr_state')['loan_status'].count().sort_values().plot(kind='barh')
plt.ylabel('State',fontsize=15)
plt.xlabel('Number of loans',fontsize=15)
plt.title('Number of defaulted loans per state',fontsize=20);<jupyter_output><empty_output><jupyter_text>California, New York and Texas have the maximum number of loan defaulters<jupyter_code>df.shape
df['loan_status'].value_counts()
df['grade'].value_counts()
#0-Fully paid
#1-Default
df['loan_status'].value_counts()
df.shape<jupyter_output><empty_output><jupyter_text>Data Cleaning<jupyter_code>df.shape
df.head()
#Finding the columns with missing values along with the datatype of each column
df_dtypes = pd.merge(df.isnull().sum(axis = 0).sort_values().to_frame('missing_value').reset_index(),
         df.dtypes.to_frame('feature_type').reset_index(),
         on = 'index',
         how = 'inner')
df_dtypes.sort_values(['missing_value', 'feature_type'])<jupyter_output><empty_output><jupyter_text>Dropping columns with more than 50% missing values<jupyter_code>dropping_columns=[]

for i in range(len(df.columns)):
    if df.iloc[:,i].isnull().mean() > 0.5:
        dropping_columns.append(df.iloc[:,i].name)
df = df.drop(dropping_columns,axis=1)
df.shape
def plot_feature(col_name, isContinuous):
    f, (ax1, ax2) = plt.subplots(nrows=1, ncols=2, figsize=(12,3), dpi=90)
    
    # Plot without loan status
    if isContinuous:
        sns.distplot(df.loc[df[col_name].notnull(), col_name], kde=False, ax=ax1)
    else:
        sns.countplot(df[col_name], order=sorted(df[col_name].unique()), color='#5975A4', saturation=1, ax=ax1)
    ax1.set_xlabel(col_name)
    ax1.set_ylabel('Count')
    ax1.set_title(col_name)
    plt.xticks(rotation = 90)

    # Plot with loan status
    if isContinuous:
        sns.boxplot(x=col_name, y='loan_status', data=df, ax=ax2)
        ax2.set_ylabel('')
        ax2.set_title(col_name + ' by Loan Status')
    else:
        data = df.groupby(col_name)['loan_status'].value_counts(normalize=True).to_frame('proportion').reset_index()        
        sns.barplot(x = col_name, y = 'proportion', hue= "loan_status", data = data, saturation=1, ax=ax2)
        ax2.set_ylabel('Loan fraction')
        ax2.set_title('Loan status')
        plt.xticks(rotation = 90)
    ax2.set_xlabel(col_name)
    
    plt.tight_layout()
df.iloc[0:10:,0:10]
plot_feature('loan_amnt', True)
plot_feature('funded_amnt',True)
plot_feature('funded_amnt_inv',True)
plot_feature('installment', True)
features_to_be_removed=[]<jupyter_output><empty_output><jupyter_text>Funded_amnt, funded_amnt_inv and  loan_amnt has similar graph hence removing two features<jupyter_code>features_to_be_removed.extend(['funded_amnt','funded_amnt_inv'])
features_to_be_removed
plot_feature('term',False)
plot_feature('int_rate', True)<jupyter_output><empty_output><jupyter_text>People who were defaulter had a very high interest rate<jupyter_code>df['grade'].value_counts()
plot_feature('grade', False)
plot_feature('grade', False)
plot_feature('sub_grade', False)
plot_feature('sub_grade', False)<jupyter_output><empty_output><jupyter_text>Keeping only grade columnRemoving emp_title columns<jupyter_code>features_to_be_removed.extend(['emp_title','installment','sub_grade'])
features_to_be_removed
df.emp_length.value_counts()
df.emp_length.fillna(value=0,inplace=True)
df['emp_length'].replace(to_replace='[^0-9]+', value='', inplace=True, regex=True)
df['emp_length'] = df['emp_length'].astype(int)
plot_feature('emp_length', False)
df['emp_length'].value_counts()
df.iloc[0:5:,10:20]
df.home_ownership.value_counts()
plot_feature('home_ownership', False)
sns.countplot(x='home_ownership',hue='loan_status',data=df)
df['pymnt_plan'].value_counts()<jupyter_output><empty_output><jupyter_text>Majority is n and we can remove this column<jupyter_code>features_to_be_removed.extend(['pymnt_plan'])
features_to_be_removed
df['verification_status'].value_counts()
df.verification_status = df.verification_status.map(lambda x: 1 if x == 'Not Verified' else 0)
df['verification_status'].value_counts()
plot_feature('verification_status', False)

df['purpose'].value_counts()
plot_feature('purpose',False)
df['title'].value_counts()
features_to_be_removed.extend(['title','zip_code'])
features_to_be_removed
df['addr_state'].value_counts()
plot_feature('addr_state', False)
df['issue_month'] = pd.Series(df.issue_d).str.replace(r'-\d+', '')
plot_feature('issue_month', False)
df['issue_year'] = pd.Series(df.issue_d).str.replace(r'\w+-', '').astype(np.int)
df.issue_year.value_counts()
plot_feature('issue_year', False)
features_to_be_removed.extend(['issue_d'])
features_to_be_removed
df.iloc[0:5:,20:30]
df['earliest_cr_year'] = df.earliest_cr_line.str.replace(r'\w+-', '').astype(np.int)
df['credit_history'] = np.absolute(df['issue_year']- df['earliest_cr_year'])
features_to_be_removed.extend(['earliest_cr_line',])
df.initial_list_status.value_counts()
df.initial_list_status = df.initial_list_status.map(lambda x: 1 if x== 'w' else 0)
df.initial_list_status.value_counts()
plot_feature('delinq_2yrs',True)
df['inq_last_6mths'].value_counts()
df.iloc[0:5:,30:40]
features_to_be_removed.extend(['out_prncp','out_prncp_inv','total_pymnt','total_pymnt_inv','total_rec_prncp','total_rec_late_fee','recoveries','collection_recovery_fee','last_pymnt_d'])
features_to_be_removed
df.iloc[0:5:,40:50]
df['application_type'].value_counts()
df['application_type'] = df.application_type.map(lambda x: 0 if x == 'Individual' else 1)
df['application_type'].value_counts()
features_to_be_removed.extend(['last_pymnt_amnt','last_credit_pull_d','collections_12_mths_ex_med','policy_code','acc_now_delinq','tot_coll_amt','tot_cur_bal','total_rev_hi_lim'])
features_to_be_removed
df.iloc[0:5:,50:60]
features_to_be_removed.extend(['avg_cur_bal','bc_open_to_buy','bc_util','chargeoff_within_12_mths','delinq_amnt','mo_sin_old_il_acct','mo_sin_old_rev_tl_op','mo_sin_rcnt_rev_tl_op','mo_sin_rcnt_tl'])
df.iloc[0:5:,60:70]
features_to_be_removed.extend(['mths_since_recent_bc','mths_since_recent_inq','num_accts_ever_120_pd','num_actv_bc_tl','num_actv_rev_tl','num_bc_sats','num_bc_tl','num_bc_tl','num_il_tl','num_op_rev_tl'])
len(features_to_be_removed)
df.iloc[0:5:,70:80]
features_to_be_removed.extend(['num_rev_tl_bal_gt_0','num_sats','num_tl_120dpd_2m','num_tl_30dpd','num_tl_90g_dpd_24m','num_tl_op_past_12m','pct_tl_nvr_dlq','percent_bc_gt_75','pub_rec_bankruptcies','tax_liens'])
df.iloc[0:5:,80:90]
features_to_be_removed.extend(['tot_hi_cred_lim','total_bal_ex_mort','total_bc_limit','total_il_high_credit_limit','hardship_flag','disbursement_method','debt_settlement_flag','Month'])
len(features_to_be_removed)
df_selected = df.drop(list(set(features_to_be_removed)), axis = 1)
df_selected.shape
df_selected.isnull().sum()<jupyter_output><empty_output><jupyter_text>Imputing the null values by mean<jupyter_code>df_selected['dti'].fillna((df_selected['dti'].mean()), inplace=True)
df_selected['inq_last_6mths'].fillna((df_selected['inq_last_6mths'].mean()), inplace=True)
df_selected['revol_util'].fillna((df_selected['revol_util'].mean()), inplace=True)
df_selected['acc_open_past_24mths'].fillna((df_selected['acc_open_past_24mths'].mean()), inplace=True)
df_selected['mort_acc'].fillna((df_selected['mort_acc'].mean()), inplace=True)
df_selected['num_rev_accts'].fillna((df_selected['num_rev_accts'].mean()), inplace=True)
df_selected.to_csv('./df_selected_columns.csv', index = False)

df_selected.isnull().sum()
df_selected.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1478717348681298, 'fraction_numerical': 0.020365568275251406, 'mean_word_length': 8.092581943645774, 'pattern_counts': {'":': 1, '<': 51, '<?xml version=': 0, '>': 55, 'https://': 0, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '529065', 'license': 'MIT', 'n_tokens_mistral': 6004, 'n_tokens_neox': 5711, 'n_words': 1033, 'path': 'Default_Loan_DataCleaning.ipynb', 'repo_name': 'purvangt25/Loan-Default-Prediction', 'size': '15848'}