Bank Loan Default Prediction--Feature Engineering (Part 2)

import pandas as pd 
import numpy as np 

import matplotlib.pyplot as plt
import seaborn as sns 
sns.set(font_scale = 1.2)
%matplotlib inline

loan_account = pd.merge(loan,
                        account,
                        how = 'left',
                        on = 'account_id',
                        suffixes = ('_loan','_acc'))
loan_account.head()

len(loan_account)

loan_account_trans = pd.merge(loan_account,
                              trans,
                              how = 'left',
                              on = 'account_id',
                              suffixes = ('_loan','_trans')
                             )

# display into two seperate parts just for the tight layout of the blog
display(loan_account_trans.iloc[:2, :11])
display(loan_account_trans.iloc[:2, 11:])

loan_account_trans['loan_trans_diff'] = ((loan_account_trans['date'] - loan_account_trans['trans_date']) / np.timedelta64(1, 'D')).astype(int)
loan_account_trans = loan_account_trans[loan_account_trans['loan_trans_diff'] >= 0]
display(loan_account_trans.iloc[:2, :11])
display(loan_account_trans.iloc[:2, 11:])

two_week = loan_account_trans[loan_account_trans['loan_trans_diff'] <= 14]
two_week = two_week.groupby(['loan_id'], as_index = None).mean()
two_week['balance_before_loan_2w'] = two_week['balance']
two_week = two_week.loc[:, ['loan_id', 'balance_before_loan_2w']]
display(two_week.head(5))

one_month = loan_account_trans[loan_account_trans['loan_trans_diff'] <= 30]
one_month = one_month.groupby(['loan_id'],as_index = None).mean()
one_month['balance_before_loan_1m'] = one_month['balance']
one_month = one_month.loc[:,['loan_id','balance_before_loan_1m']]
display(one_month.head(5))

three_month = loan_account_trans[loan_account_trans['loan_trans_diff'] <= 90]
three_month = three_month.groupby(['loan_id'],as_index = None).mean()
three_month['balance_before_loan_3m'] = three_month['balance']
three_month = three_month.loc[:,['loan_id','balance_before_loan_3m']]
display(three_month.head(5))

before = loan_account_trans
before['trans_year'] = before['trans_date'].dt.year # extract the year from datetime column
before.head(2)

trans_freq = before.loc[:, ['loan_id','trans_year','balance']].groupby(['loan_id','trans_year']).count()
trans_freq.rename(columns={'balance':'trans_freq'}, inplace = True)
trans_freq.head()

f = {'trans_freq': ['sum', 'mean'], 
     'trans_year': ['count']}

trans_freq = trans_freq.reset_index().groupby('loan_id').agg(f)
trans_freq.columns = [s1 + '_' + s2 for (s1,s2) in trans_freq.columns.to_list()]
trans_freq.reset_index(inplace = True)

large_amount = before[before['large_amount'] == 3]
large_amount_freq = large_amount.loc[:,['loan_id','large_amount']].groupby(['loan_id']).count()
large_amount_freq.rename(columns = {'large_amount': 'large_amount_freq'}, inplace = True)
display(large_amount_freq.head())

low_5k = before[before['balance'] <= 5000] 
low_5k_freq = low_5k.loc[:,['loan_id', 'balance']].groupby(['loan_id']).count()
low_5k_freq.rename(columns = {'balance': 'low_5k_freq'},inplace = True)
display(low_5k_freq.head())

loan_info = loan.merge(three_month,
                       how = 'left',
                       on = 'loan_id')

loan_info = loan_info.merge(two_week,
                            how = 'left',
                            on = 'loan_id')

loan_info = loan_info.merge(one_month,
                            how = 'left',
                            on = 'loan_id')

loan_info = loan_info.merge(trans_freq,
                            how = 'left',
                            on = 'loan_id')

loan_info = loan_info.merge(large_amount_freq,
                            how = 'left',
                            on = 'loan_id')

loan_info = loan_info.merge(low_5k_freq,
                            how = 'left',
                            on = 'loan_id')
loan_info.head()

loan_info[loan_info['status'] == 0]['large_amount_freq'].describe()

loan_info[loan_info['status'] == 1]['large_amount_freq'].describe()

# making subplots to compare the payment habbits of these two types of clients
plt.figure(figsize = (14, 5))
plt.subplot(211)
sns.histplot(loan_info[loan_info['status'] == 0]['large_amount_freq'], 
             stat = 'density', kde = True, 
             label = 'Success in making payment')
plt.legend()
plt.ylim(0, 0.03)
plt.xlim(0, 105)

plt.subplot(212)
sns.histplot(loan_info[loan_info['status'] == 1]['large_amount_freq'], 
             stat = 'density', kde = True,
             label = 'Fail in making payment')
plt.ylim(0, 0.03)
plt.xlim(0, 105)
plt.legend()

data = pd.merge(loan_info,
                account,
                how = 'left',
                on = 'account_id'
               )

data = pd.merge(data,
                disp,
                how = 'left',
                on = 'account_id'
               )

data = pd.merge(data,
                card,
                how = 'left',
                on = 'disp_id'
               )

data = pd.merge(data,
                order,
                how = 'left',
                on = 'account_id'
               )

data = pd.merge(data,
                client,
                how = 'left',
                on = 'client_id'
               )

data = pd.merge(data,
                district,
                how = 'left',
                left_on = 'district_id_x',
                right_on = 'A1'
               )
display(data.iloc[:3, :10])
display(data.iloc[:3, 10: 21])
display(data.iloc[:3, 21: 30])
display(data.iloc[:3, 30:])

data['year'] = data['date'].dt.year
data['year_loan'] = 1999 - (data['year']).astype(int)

data.drop(['date','year', 'account_id', 'birth_number', 'card_id',
           'client_id', 'disp_id', 'district_id_x', 'district_id_y','issued',
           'loan_id', 'A12', 'A15'], axis = 1,  inplace = True)

data.columns.unique()

data['type'].fillna(-1, inplace = True)
data.fillna(0, inplace = True)
data.info()

from sklearn.model_selection import train_test_split

X = data.loc[:, data.columns != 'status']
y = data['status']

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = 1/3)
len(X_train), len(X_test)