<jupyter_start><jupyter_text>Load Dataset The raw data are stored in separate files, ranging from $2007$ until $2018$. Possibly some years have been separated into $4$ quarters.<jupyter_code># read raw data
data_2011 = pd.read_csv('./data/2011.csv')
data_2013 = pd.read_csv('./data/2013.csv')
data_2014 = pd.read_csv('./data/2014.csv')
data_2015 = pd.read_csv('./data/2015.csv')
data_2016_1 = pd.read_csv('./data/2016(1).csv')
data_2016_2 = pd.read_csv('./data/2016(2).csv')
data_2016_3 = pd.read_csv('./data/2016(3).csv')
data_2016_4 = pd.read_csv('./data/2016(4).csv')
data_2017_1 = pd.read_csv('./data/2017(1).csv')
data_2017_2 = pd.read_csv('./data/2017(2).csv')
data_2017_3 = pd.read_csv('./data/2017(3).csv')
data_2017_4 = pd.read_csv('./data/2017(4).csv')
data_2018 = pd.read_csv('./data/2018(1).csv')

# merge data from 2016 and 2017
data_2016 = pd.concat(objs=[data_2016_1, data_2016_2, data_2016_3, data_2016_4], axis=0)
data_2017 = pd.concat(objs=[data_2017_1, data_2017_2, data_2017_3, data_2017_4], axis=0)
# basic information
print('Year 2011:\t', data_2011.shape)
print('Year 2013:\t', data_2013.shape)
print('Year 2014:\t', data_2014.shape)
print('Year 2015:\t', data_2015.shape)
print('Year 2016:\t', data_2016.shape)
print('Year 2017:\t', data_2017.shape)
print('Year 2018:\t', data_2018.shape)
data_2011.info()
data_2013.info()
data_2014.info()
data_2015.info()
data_2016.info()
data_2017.info()
data_2018.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 107864 entries, 0 to 107863
Columns: 143 entries, loan_amnt to settlement_term
dtypes: float64(59), int64(54), object(30)
memory usage: 117.7+ MB<jupyter_text>Data Concatenation After observation in depth, we notice that early date, especially the data before $2011$, have some difference with the most recent data, such as the data in $2017$ and $2018$. Since the data before $2014$ is only a small part of all data (~$12\%$), in this project, we will only use the data from $2014$ until $2018$.<jupyter_code># add year information 
data_2014['year'] = 2014
data_2015['year'] = 2015
data_2016_1['year'] = 2016
data_2016_2['year'] = 2016
data_2016_3['year'] = 2016
data_2016_4['year'] = 2016
data_2017_1['year'] = 2017
data_2017_2['year'] = 2017
data_2017_3['year'] = 2017
data_2017_4['year'] = 2017
data_2018['year'] = 2018
# concate data from 2014 until 2018
objs = [data_2014, data_2015, data_2016_1, data_2016_2, data_2016_3, data_2016_4, 
        data_2017_1, data_2017_2, data_2017_3, data_2017_4, data_2018]
data = pd.concat(objs=objs, axis=0)
data.head()
data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1642574 entries, 0 to 107863
Columns: 144 entries, loan_amnt to year
dtypes: float64(67), int64(39), object(38)
memory usage: 1.8+ GB<jupyter_text>Data Cleaning In this notebook, we only do the very basic data cleaning steps: to remove the features with large amount of missing values.<jupyter_code># missing values
pd.options.display.max_rows = 150
missing = data.isnull().sum()
missing_ratio = missing / len(data)
missing_ratio = missing_ratio.reset_index()

# check the feature that has missing ration >= 0.2
missing_ratio = missing_ratio.rename(columns={'index': 'feature', 0: 'missing ratio'})
missing_ratio = missing_ratio.sort_values(by='missing ratio', ascending=False)
missing_ratio[missing_ratio['missing ratio'] >= 0.2]<jupyter_output><empty_output><jupyter_text>Based on the missing ratio and business sense, all the above features are removed.<jupyter_code># it's safe to remove the features with missing ratio >= 0.3
columns = ['url', 'orig_projected_additional_accrued_interest', 'hardship_start_date', 
           'hardship_end_date', 'payment_plan_start_date', 'hardship_length', 
           'hardship_dpd', 'hardship_loan_status', 'hardship_payoff_balance_amount', 
           'hardship_last_payment_amount', 'hardship_amount', 'deferral_term', 
           'hardship_status', 'hardship_reason', 'hardship_type', 'desc', 
           'sec_app_mths_since_last_major_derog', 'settlement_term', 
           'debt_settlement_flag_date', 'settlement_date', 'settlement_amount', 
           'settlement_percentage', 'settlement_status', 'sec_app_revol_util', 
           'revol_bal_joint', 'sec_app_open_act_il', 'sec_app_earliest_cr_line', 
           'sec_app_inq_last_6mths', 'sec_app_open_acc', 'sec_app_mort_acc', 
           'sec_app_num_rev_accts', 'sec_app_chargeoff_within_12_mths', 
           'sec_app_collections_12_mths_ex_med', 'verification_status_joint',
           'dti_joint', 'annual_inc_joint', 'mths_since_last_record', 
           'mths_since_recent_bc_dlq', 'mths_since_last_major_derog', 
           'mths_since_recent_revol_delinq', 'mths_since_last_delinq', 'next_pymnt_d', 
           'il_util', 'mths_since_rcnt_il', 'all_util', 'open_acc_6m', 'total_cu_tl', 
           'inq_last_12m', 'open_act_il', 'open_il_12m', 'max_bal_bc', 'open_rv_24m', 
           'open_rv_12m', 'total_bal_il', 'open_il_24m', 'inq_fi']
# drop features with too many missing values
data = data.drop(labels=columns, axis=1)

# save to local disk
data.to_csv('./data/data_2014_2018.csv', index=False)

data.head()
# basic information
data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 1642574 entries, 0 to 107863
Data columns (total 88 columns):
loan_amnt                     1642574 non-null int64
funded_amnt                   1642574 non-null int64
funded_amnt_inv               1642574 non-null float64
term                          1642574 non-null object
int_rate                      1642574 non-null object
installment                   1642574 non-null float64
grade                         1642574 non-null object
sub_grade                     1642574 non-null object
emp_title                     1535014 non-null object
emp_length                    1537225 non-null object
home_ownership                1642574 non-null object
annual_inc                    1642574 non-null float64
verification_status           1642574 non-null object
issue_d                       1642574 non-null object
loan_status                   1642574 non-null object
pymnt_plan                    1642574 non-null object
purpose                [...]
{'fraction_non_alphanumeric': 0.11953024453024454, 'fraction_numerical': 0.11116473616473617, 'mean_word_length': 4.133773740710157, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 20, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '483554', 'license': 'MIT', 'n_tokens_mistral': 2633, 'n_tokens_neox': 2097, 'n_words': 481, 'path': '1. Data Collection and Concatenation.ipynb', 'repo_name': 'JifuZhao/Lending-Club-Loan-Analysis', 'size': '6216'}