Table of Contents
- 1 Load the data
- 2 Memory Reduction
- 3 data type: datetetime
- 4 data type: object
- 5 Create New Columns
- 6 Missing Values
- 7 Dummy variables
- 8 Split train test data
- 9 Prepare the data for train and test separately
- 10 Weight of Evidence WoE and Information Value IV
- 10.1 woe dummies: grade
- 10.2 woe dummies: home_ownership
- 10.3 woe dummies: addr_state
- 10.4 woe dummies: verification_status
- 10.5 woe dummies: purpose
- 10.6 woe dummies: initial_list_status
- 10.7 woe dummies: term_int
- 10.8 woe dummies continuous: emp_length_int
- 10.9 woe dummies continuous: mths_since_issue_d
- 10.10 woe dummies continuous: int_rate
- 10.11 woe dummies continuous: mths_since_earliest_cr_line
- 10.12 woe dummies continuous: delinq_2yrs **
- 10.13 woe dummies continuous: inq_last_6mths
- 10.14 woe dummies continuous: open_acc
- 10.15 woe dummies continuous: pub_rec
- 10.16 woe dummies continuous: total_acc
- 10.17 woe dummies continuous: acc_now_delinq
- 10.18 woe dummies continuous: total_rev_hi_lim
- 10.19 woe dummies continuous: installment
- 10.20 woe dummies continuous: annual_inc
- 10.21 woe dummies continuous: mths_since_last_delinq **
- 10.22 woe dummies continuous: dti **
- 10.23 woe dummies continuous: mths_since_last_record ** missing
- 11 Save the train data and repeat for test
In [1]:
import numpy as np
import pandas as pd
import seaborn as sns
pd.plotting.register_matplotlib_converters()
from tqdm import tqdm_notebook as tqdm
import matplotlib.pyplot as plt
%matplotlib inline
%config InlineBackend.figure_format = 'retina'
plt.style.use('ggplot')
# random state
SEED=100
np.random.seed(SEED)
[(x.__name__,x.__version__) for x in [np,pd,sns]]
Out[1]:
In [2]:
pd.options.display.max_columns = None
In [3]:
%%javascript
IPython.OutputArea.auto_scroll_threshold = 9999;
In [4]:
import datetime
import functools
In [5]:
from sklearn.model_selection import train_test_split
In [6]:
import plotly
import plotly.offline as py
import plotly.graph_objs as go
import plotly.figure_factory as ff
import plotly.tools as tls
from plotly.subplots import make_subplots
from plotly.offline import plot, iplot, init_notebook_mode
init_notebook_mode(connected=False)
[(x.__name__,x.__version__) for x in [plotly]]
Out[6]:
Load the data¶
In [7]:
!ls ../data/raw
In [8]:
!du -sh ../data/raw/loan_data_2007_2014.csv
In [222]:
dat_raw = '../data/raw/'
dat_pro = '../data/processed/'
In [9]:
df = pd.read_csv(dat_raw + 'loan_data_2007_2014.csv',low_memory=False)
print(df.shape)
df.head(2).append(df.tail(2))
Out[9]:
In [10]:
df_copy = df.copy(deep=True)
In [11]:
# df = df_copy.copy()
Memory Reduction¶
In [12]:
col_floats = df.select_dtypes(np.float64).columns
for col in col_floats:
df[col] = df[col].astype(np.float32)
In [13]:
col_ints = df.select_dtypes(np.int64).columns
for col in col_ints:
df[col] = df[col].astype(np.int32)
data type: datetetime¶
In [14]:
df.select_dtypes('object').head(1).T
Out[14]:
In [15]:
cols_date = ['issue_d',
'earliest_cr_line',
'last_pymnt_d',
'last_credit_pull_d']
for col in cols_date:
df[col+'_date'] = pd.to_datetime(df[col],
format='%b-%y',
errors='coerce')
In [16]:
df[cols_date].dtypes
Out[16]:
In [17]:
df[cols_date].head(2)
Out[17]:
In [18]:
today = datetime.datetime.today()
today
Out[18]:
In [19]:
today = datetime.datetime(2020, 3, 30)
today
Out[19]:
In [20]:
df['earliest_cr_line_date'].head(2)
Out[20]:
In [21]:
df['earliest_cr_line_date'].isnull().sum()
Out[21]:
In [22]:
for col in cols_date:
df['mths_since_'+col] = \
round((today - df[col+'_date']) /
np.timedelta64(1,'M'))
In [23]:
cols_months_since = ['mths_since_' + i for i in cols_date]
df[cols_months_since].describe().round(2).T
Out[23]:
data type: object¶
In [24]:
df.select_dtypes('object').nunique().sort_values()
Out[24]:
In [25]:
cols_cat_small = df.select_dtypes('object').nunique()[lambda x: x<12].index
In [26]:
for col in cols_cat_small:
print(col)
print(df[col].unique())
print()
Hidden Continuous features¶
In [27]:
df['term'].value_counts(dropna=False)
Out[27]:
In [28]:
df['term_int'] = df['term'].str.extract('(\d+)').astype(int)
df['term_int'].unique()
Out[28]:
In [29]:
df['emp_length'].value_counts(dropna=False)
Out[29]:
In [30]:
df['emp_length_int'] = df['emp_length']\
.str.replace('< 1years','0')\
.str.extract(r'(\d+)').astype(np.float32)
df['emp_length_int'].value_counts(dropna=False)
Out[30]:
Create New Columns¶
In [31]:
df['loan_income_ratio']= df['loan_amnt']/df['annual_inc']
df.iloc[:2,-2:]
Out[31]:
In [32]:
df['loan_status'].value_counts()
Out[32]:
In [33]:
df['loan_status'].unique()
Out[33]:
In [34]:
cols_bad = ['Charged Off',
'Default',
'Does not meet the credit policy. Status:Charged Off',
'Late (31-120 days)']
df['good_bad'] = np.where(df['loan_status'].isin(cols_bad),0,1)
df['good_bad'].value_counts()
Out[34]:
Missing Values¶
In [35]:
pd.options.display.max_rows = 200
In [36]:
df.head(2)
Out[36]:
In [37]:
missing_cols = df.isnull().sum()[lambda x: x>0].sort_values()\
.index.tolist()
df[missing_cols].head(2)
Out[37]:
In [38]:
# df[missing_cols].isnull().sum()
In [39]:
df.shape
Out[39]:
In [40]:
# drop columns if all of them are nans
df = df.dropna(how='all',axis=1)
df.isnull().sum()[lambda x: x>0]
Out[40]:
In [41]:
missing_cols = df.isnull().sum()[lambda x: x>0].sort_values()\
.index.tolist()
df[missing_cols].head(2)
Out[41]:
In [42]:
columns = ['feature','n_missing','top_value',
'proportion','nunique']
df_missing = pd.DataFrame(columns=columns)
for col in missing_cols:
x = df[col].value_counts(normalize=True).round(2)
unq = df[col].nunique()
n_missing = df[col].isnull().sum()
row = [col,n_missing, x.index[0],x.iloc[0],unq]
df_missing.loc[len(df_missing)] = row
df_missing = df_missing.set_index('feature')
df_missing
Out[42]:
fill nans with mean¶
In [43]:
df['annual_inc'].isna().sum()
Out[43]:
In [44]:
df['annual_inc'] = df['annual_inc'].fillna(df['annual_inc'].mean())
fill nans with another column¶
In [45]:
df['total_rev_hi_lim'] = df['total_rev_hi_lim'].fillna(df['funded_amnt'])
fill nans with zero¶
In [46]:
cols_zero = ['mths_since_earliest_cr_line','acc_now_delinq',
'total_acc','pub_rec','open_acc','inq_last_6mths',
'delinq_2yrs','emp_length_int']
for col in cols_zero:
df[col] = df[col].fillna(0)
Dummy variables¶
In [47]:
df.select_dtypes('object').nunique().sort_values()
Out[47]:
In [48]:
pd.get_dummies(df['grade'],prefix='grade',prefix_sep=':').head(2)
Out[48]:
In [49]:
cols_dummy = ['grade','sub_grade','home_ownership',
'verification_status','loan_status',
'purpose','addr_state','initial_list_status']
df_dummies = [pd.get_dummies(df[col],
prefix=col,prefix_sep = ':')
for col in cols_dummy]
df_dummies = pd.concat(df_dummies,axis=1)
print(df_dummies.shape)
df_dummies.head(2)
Out[49]:
In [50]:
df = pd.concat([df,df_dummies],axis=1)
print(df.shape)
df.head(2)
Out[50]:
In [51]:
df.isnull().sum().sum()
Out[51]:
Split train test data¶
In [52]:
from sklearn.model_selection import train_test_split
In [53]:
df_Xtrain, df_Xtest, ser_ytrain, ser_ytest = train_test_split(
df.drop('good_bad', axis = 1),
df['good_bad'],
train_size=0.8,
random_state=SEED
)
In [54]:
df_Xtrain.shape, df_Xtest.shape, ser_ytrain.shape, ser_ytest.shape
Out[54]:
Prepare the data for train and test separately¶
In [224]:
X_prep = df_Xtrain
y_prep = ser_ytrain
In [225]:
# In second round, we will comment train part and
# uncomment this test part.
#
# Note: after doing test part, comment test part and
# and keep train part uncommented, since we need to
# create features based on train not test.
#
# X_prep = df_Xtest
# y_prep = ser_ytest
Weight of Evidence WoE and Information Value IV¶
In [226]:
def get_woe_iv(df1,
ser_target,
feature_cat,
target_name='good_bad',
sort='woe',
return_all=False):
"""Calculate Weight of Evidence and Infomation Value.
Parameters
-----------
df1: pandas.DataFrame
Input dataframe having categorical feature.
ser_target: pandas.Series
Pandas Series of binary values. e.g y_prep
feature_cat: str
Name of categorical column
target_name: str
Name of target column.
sort: str
Sort the output dataframe by this column.
Default is 'woe'. We use 'woe' for discrete
columns.
If the column values are continuous, then
use the actual column name for sorting.
return_all: boolean, optional
Whether or not return all columns
Returns:
--------
Returns dataframe with WOE, IV and other quantities.
Example:
--------
df_woe = get_woe_iv(X_prep,'grade',y_prep)
"""
df1 = pd.DataFrame({
feature_cat: df1[feature_cat],
target_name: ser_target
})
df1 = df1.groupby(feature_cat).agg(
n_obs=(target_name,'count'),
prop_good=(target_name,'mean'))
df1['prop_n_obs'] = df1['n_obs'] / df1['n_obs'].sum()
df1['n_good'] = df1['prop_good'] * df1['n_obs']
df1['n_bad'] = (1-df1['prop_good']) * df1['n_obs']
df1['prop_n_good'] = df1['n_good'] / df1['n_good'].sum()
df1['prop_n_bad'] = df1['n_bad'] / df1['n_bad'].sum()
df1['woe'] = np.log(df1['prop_n_good']/df1['prop_n_bad'])
df1['diff_prop_good'] = df1['prop_good'].diff().abs()
df1['diff_woe'] = df1['woe'].diff().abs()
df1['diff_n_obs'] = df1['n_obs'].diff(-1).abs()
df1['iv'] = ((df1['prop_n_good']-df1['prop_n_bad']
) * df1['woe']
).sum()
# reorder columns
if not return_all:
df1 = df1[['n_obs', 'woe', 'diff_n_obs' ]]
if return_all:
df1 = df1[['n_obs', 'woe', 'diff_n_obs',
'prop_n_obs','iv',
'n_good', 'n_bad',
'prop_n_good','prop_n_bad',
'prop_good',
'diff_prop_good', 'diff_woe' ]]
# add one more column
n_obs1 = df1['n_obs'].apply(lambda x: "{:,d}".format(x)).astype(str)
df1['text_plotly'] = (
'n_obs: ' + n_obs1 + '<br>' +
'WoE: ' + df1['woe'].round(4).astype(str) + '<br>'
)
# sort values
df1 = df1.sort_values(sort)
df1 = df1.reset_index()
return df1
df_woe = get_woe_iv(X_prep,y_prep,'grade')
df_woe.head().style\
.background_gradient(subset=['n_obs'],cmap='Blues')\
.background_gradient(subset=['woe'],cmap='Reds')\
.format({'n_obs':"{:,d}", 'diff_n_obs':"{:,.0f}"})
Out[226]:
In [227]:
def plot_woe(df_woe,xrot=0,figsize=(18,12),fontsize=20,color='red'):
x = df_woe.iloc[:,0].to_numpy().astype(str)
y = df_woe['woe']
fig,ax = plt.subplots(1,1,figsize=figsize)
plt.plot(x,y,marker='o',linestyle='--',color='k')
plt.xlabel(df_woe.columns[0],fontsize=fontsize)
plt.ylabel('Weight of Evidence',fontsize=fontsize)
plt.xticks(rotation=xrot,fontsize=fontsize,color=color)
plt.yticks(fontsize=fontsize)
plt.grid(color='k',linestyle='-.')
for i,txt in enumerate(df_woe['n_obs']):
txt = ' {:,.0f}'.format(txt)
try: # sometimes I get error in y[i]
plt.annotate(txt, (x[i],y[i]),
rotation=90,fontsize=18,color='blue')
except:
pass
In [228]:
def plot_woe_plotly(df_woe):
col = df_woe.columns.tolist()[0]
x = df_woe.iloc[:,0].astype(str)
y = df_woe['woe']
z = df_woe['text_plotly']
data = go.Scatter(x=x,y=y,text=z,mode='lines+markers')
fig = go.Figure(data=data)
fig.update_layout(
title=f"WoE Curve for **{col}**",
xaxis_title=f"{col}",
yaxis_title="Weight of Evidence",
)
# also display dataframe
df_style = df_woe.style\
.background_gradient(subset=['n_obs'],
cmap='Blues')\
.background_gradient(subset=['woe'],
cmap='Reds')\
.format({'n_obs':"{:,d}",
'diff_n_obs':"{:,.0f}"})
display(df_style)
fig.show()
In [229]:
def create_bins_dummies_cont(X_prep,col,bins):
"""Creates new dummy columns in-place in given dataframe.
For this continuous bins,
left numbers are inclusive.
Example:
========
bins = [0,9.961,12.025, 15.74, 20.281]
col = 'int_rate'
create_bins_dummies_cont(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Outputs:
========
int_rate:0_9.961
int_rate:9.961_12.025
int_rate:12.025_15.74
int_rate:15.74_20.281
int_rate:>=20.281
"""
nbins = len(bins)
ser = X_prep[col]
for i in range(nbins):
if i < (nbins-1):
a,b = bins[i], bins[i+1]
name = col + f':{a}_{b}'
print(name)
# condition
cond = (ser>=a) & (ser<b)
X_prep[name] = np.where(cond,1,0)
elif i == (nbins-1):
b = bins[i]
name = col + ':>=' + str(b)
print(name)
# condition
cond = ser >= b
X_prep[name] = np.where(cond,1,0)
In [230]:
def create_bins_dummies_int(X_prep,col,bins):
"""Creates new dummy columns in-place in given dataframe.
For this integer bins,
both left and right numbers are included.
Example:
---------
col = 'mths_since_last_delinq'
bins = [0,3,30,56]
create_bins_dummies_integer(X_prep,col,bins)
Outputs:
--------
mths_since_last_delinq:0_3
mths_since_last_delinq:4_30
mths_since_last_delinq:31_56
mths_since_last_delinq:>=57
"""
nbins = len(bins)
ser = X_prep[col]
# assert last number of bin is integer
assert isinstance(bins[-1],int)
for i in range(nbins):
if i == 0:
a,b = bins[i], bins[i+1]
name = col + f':{a}_{b}'
print(name)
# condition
cond = (ser>=a) & (ser<=b)
X_prep[name] = np.where(cond,1,0)
elif i < (nbins-1):
a,b = bins[i]+1, bins[i+1]
name = col + f':{a}_{b}'
print(name)
# condition
cond = (ser>=a) & (ser<=b)
X_prep[name] = np.where(cond,1,0)
elif i == (nbins-1):
b = bins[i]+1
name = col + ':>=' + str(b)
print(name)
# condition
cond = ser >= b
X_prep[name] = np.where(cond,1,0)
In [231]:
def create_dummies_left_inclusive(X_prep,col,bins,labels):
nlabels = len(labels)
for i in range(nlabels):
name = col + ':' + labels[i]
ser = X_prep[col]
if i == 0:
cond = (ser <= bins[i])
X_prep[name] = np.where(cond,1,0)
print(name)
elif (i>0) & (i<(nlabels-1)):
cond = (ser >= bins[i-1]) & (ser < bins[i])
X_prep[name] = np.where(cond,1,0)
print(name)
elif i == (nlabels-1):
cond = (ser > bins[i-1])
X_prep[name] = np.where(cond,1,0)
print(name)
woe dummies: grade¶
In [232]:
col = 'grade'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[232]:
In [233]:
df[col].value_counts().sort_index()
Out[233]:
In [234]:
"""
as we expect, lower the grade, higher is the probabilty of default.
here, we will not group grades into grade:G_F and so on.
we already have one hot encoding of grade, so we are fine.
grade:A
grade:B
grade:C
grade:D
grade:E
grade:F
""";
woe dummies: home_ownership¶
In [235]:
col = 'home_ownership'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[235]:
In [236]:
cols = [i for i in X_prep.columns if 'home_ownership' in i]
X_prep[cols].head(2)
Out[236]:
In [237]:
df_woe = get_woe_iv(X_prep,y_prep,'home_ownership')
plot_woe_plotly(df_woe)
In [238]:
"""
home_ownership:RENT_OTHER_NONE_ANY
home_ownership:OWN
home_ownership:MORTGAGE
""";
In [239]:
X_prep['home_ownership:RENT_OTHER_NONE_ANY'] = \
sum([X_prep['home_ownership:RENT'],
X_prep['home_ownership:OTHER'], X_prep['home_ownership:NONE'],
X_prep['home_ownership:ANY']])
woe dummies: addr_state¶
In [240]:
col = 'addr_state'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[240]:
In [241]:
cols = [i for i in X_prep.columns if 'addr_state' in i]
X_prep[cols].head(2)
Out[241]:
In [242]:
X_prep['addr_state'].unique()
Out[242]:
In [243]:
train_state_names = ['GA', 'PA', 'WI', 'CA', 'FL', 'CT', 'TX', 'MA', 'OR', 'HI', 'MO',
'MS', 'NY', 'SC', 'WV', 'IL', 'NJ', 'AZ', 'MN', 'CO', 'TN', 'AL',
'LA', 'OH', 'VA', 'NV', 'WA', 'NC', 'WY', 'OK', 'MI', 'DC', 'NM',
'AK', 'MD', 'AR', 'KS', 'IN', 'UT', 'DE', 'NH', 'KY', 'RI', 'MT',
'VT', 'SD', 'NE', 'ID', 'ME', 'IA']
In [244]:
X_prep['addr_state'].nunique()
Out[244]:
In [245]:
"""
We have 50 states and one DC, so, we should have 51 values.
one state is missing. and the missing state is ND.
""";
In [246]:
state_names51 = ['AK','AL','AR','AZ','CA','CO','CT',
'DC',
'DE','FL','GA','HI','IA','ID','IL',
'IN','KS','KY','LA','MA','MD','ME',
'MI','MN','MO','MS','MT','NC', 'ND',
'NE','NH','NJ','NM','NV','NY','OH',
'OK','OR','PA','RI','SC','SD','TN',
'TX','UT','VA','VT','WA','WI','WV',
'WY']
len(state_names51)
Out[246]:
In [247]:
missing_states = [i for i in state_names51 if i not in
train_state_names]
missing_states
Out[247]:
In [248]:
for col in missing_states:
name = 'addr_state:' + col
if name in X_prep.columns:
pass
else:
X_prep[name] = 0
In [249]:
# df_woe = get_woe_iv(X_prep,y_prep,'addr_state')
# plot_woe_plotly(df_woe)
In [250]:
"""
** do not forget the missing states.
We create the following categories:
'ND' 'NE' 'IA' NV' 'FL' 'HI' 'AL'
'NM' 'VA'
'NY'
'OK' 'TN' 'MO' 'LA' 'MD' 'NC'
'CA'
'UT' 'KY' 'AZ' 'NJ'
'AR' 'MI' 'PA' 'OH' 'MN'
'RI' 'MA' 'DE' 'SD' 'IN'
'GA' 'WA' 'OR'
'WI' 'MT'
'TX'
'IL' 'CT'
'KS' 'SC' 'CO' 'VT' 'AK' 'MS'
'WV' 'NH' 'WY' 'DC' 'ME' 'ID'
'ND_NE_IA_NV_FL_HI_AL' will be the reference category.
""";
In [251]:
multiple = [
'ND_NE_IA_NV_FL_HI_AL',
'NM_VA',
'OK_TN_MO_LA_MD_NC',
'UT_KY_AZ_NJ',
'AR_MI_PA_OH_MN',
'RI_MA_DE_SD_IN',
'GA_WA_OR',
'WI_MT',
'IL_CT',
'KS_SC_CO_VT_AK_MS',
'WV_NH_WY_DC_ME_ID'
]
dummies = ['addr_state:'+i for i in multiple]
for d in dummies:
print(d)
In [252]:
"""
Store these dummies:
addr_state:ND_NE_IA_NV_FL_HI_AL
addr_state:NM_VA
addr_state:OK_TN_MO_LA_MD_NC
addr_state:UT_KY_AZ_NJ
addr_state:AR_MI_PA_OH_MN
addr_state:RI_MA_DE_SD_IN
addr_state:GA_WA_OR
addr_state:WI_MT
addr_state:IL_CT
addr_state:KS_SC_CO_VT_AK_MS
addr_state:WV_NH_WY_DC_ME_ID
""";
In [253]:
sep = '_'
one = 'ND_NE_IA_NV_FL_HI_AL'
lst_one = one.split(sep)
lst_one
Out[253]:
In [254]:
colname = 'addr_state'
new_cat = colname + ':' + one
new_cat
Out[254]:
In [255]:
lst = [X_prep[colname + ':' + i] for i in lst_one]
In [256]:
sum(lst).head(2)
Out[256]:
In [257]:
colname = 'addr_state'
sep = '_'
for one in multiple:
lst_one = one.split(sep)
new_cat = colname + ':' + one
lst = [X_prep[colname + ':' + i] for i in lst_one]
X_prep[new_cat] = sum(lst)
In [258]:
X_prep.iloc[:2,-2:]
Out[258]:
woe dummies: verification_status¶
In [259]:
col = 'verification_status'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[259]:
In [260]:
X_prep[col].value_counts()
Out[260]:
In [261]:
cols = [i for i in X_prep.columns if 'verification_status' in i]
X_prep[cols].head(2)
Out[261]:
In [262]:
for col in cols:
print(col)
In [263]:
"""
verification_status:Not Verified
verification_status:Source Verified
verification_status:Verified
""";
In [264]:
# they all have significant n_obs.
# no need to group them.
woe dummies: purpose¶
In [265]:
col = 'purpose'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[265]:
In [266]:
cols = [i for i in X_prep.columns if 'purpose' in i]
X_prep[cols].head(2)
Out[266]:
In [267]:
# df_woe = get_woe_iv(X_prep,y_prep,'purpose')
# plot_woe_plotly(df_woe)
In [268]:
dummies = \
"""
small_business__educational__moving__renewable_energy
other
house__medical__wedding__vacation
debt_consolidation
home_improvement__major_purchase__car
credit_card
""".strip().split('\n')
dummies = ['purpose:'+ i for i in dummies]
for d in dummies:
print(d)
In [269]:
multiple =[
'small_business__educational__moving__renewable_energy',
'house__medical__wedding__vacation',
'home_improvement__major_purchase__car'
]
In [270]:
colname = 'purpose'
sep = '__'
multiple =[
'small_business__educational__moving__renewable_energy',
'house__medical__wedding__vacation',
'home_improvement__major_purchase__car'
]
for one in multiple:
lst_one = one.split(sep)
new_cat = colname + ':' + one
lst = [X_prep[colname + ':' + i] for i in lst_one]
X_prep[new_cat] = sum(lst)
In [271]:
X_prep.iloc[:2,-2:]
Out[271]:
woe dummies: initial_list_status¶
In [272]:
col = 'initial_list_status'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[272]:
In [273]:
cols = [i for i in X_prep.columns if 'initial_list_status' in i]
X_prep[cols].head(2)
Out[273]:
In [274]:
# we already have binary encoding.
In [275]:
dummies = """
initial_list_status:f
initial_list_status:w
""";
woe dummies: term_int¶
In [276]:
col = 'term_int'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[276]:
In [277]:
X_prep['term:36'] = np.where((X_prep['term_int'] == 36), 1, 0)
X_prep['term:60'] = np.where((X_prep['term_int'] == 60), 1, 0)
In [278]:
dummies = \
"""
term:36
term:60
""";
woe dummies continuous: emp_length_int¶
In [279]:
col = 'emp_length_int'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[279]:
In [280]:
X_prep[col].value_counts()
Out[280]:
In [281]:
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[281]:
In [282]:
# for continuous variables use sort=colname
df_woe = get_woe_iv(X_prep,y_prep,col,sort=col)
plot_woe_plotly(df_woe)
In [283]:
"""
emp_length:0
emp_length:1
emp_length:2_3
emp_length:4_5
emp_length:6_7
emp_length:8_9
emp_length:10
""";
In [284]:
X_prep['emp_length:0'] = np.where(X_prep['emp_length_int'].isin([0]), 1, 0)
X_prep['emp_length:1'] = np.where(X_prep['emp_length_int'].isin([1]), 1, 0)
X_prep['emp_length:2_3'] = np.where(X_prep['emp_length_int'].isin([2,3]), 1, 0)
X_prep['emp_length:4_5'] = np.where(X_prep['emp_length_int'].isin([4,5]), 1, 0)
X_prep['emp_length:6_7'] = np.where(X_prep['emp_length_int'].isin([6,7]), 1, 0)
X_prep['emp_length:8_9'] = np.where(X_prep['emp_length_int'].isin([8,9]), 1, 0)
X_prep['emp_length:10'] = np.where(X_prep['emp_length_int'].isin([10]), 1, 0)
woe dummies continuous: mths_since_issue_d¶
In [285]:
col = 'mths_since_issue_d'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[285]:
In [286]:
X_prep[colf] = pd.cut(X_prep[col], 50)
In [287]:
X_prep[colf].head(2)
Out[287]:
In [288]:
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
# plot_woe_plotly(df_woe)
In [289]:
bins = [0,66,70,75,82,93,128]
col = 'mths_since_issue_d'
create_bins_dummies_int(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[289]:
In [290]:
# after 93, there are very few obervations
# we can group them all to one or make the two.
"""
mths_since_issue_d:0_66
mths_since_issue_d:67_70
mths_since_issue_d:71_75
mths_since_issue_d:76_82
mths_since_issue_d:83_93
mths_since_issue_d:94_128
mths_since_issue_d:>=129
""";
woe dummies continuous: int_rate¶
In [291]:
col = 'int_rate'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[291]:
In [292]:
X_prep[colf] = pd.cut(X_prep[col], 50)
In [293]:
X_prep[colf].head(2)
Out[293]:
In [294]:
# for continuous variables use sort=colname
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
plot_woe(df_woe,xrot=90,color='k')
In [295]:
bins = [0,9.961,12.025, 15.74, 20.281,27]
# we have monotonically decreasing curve, its easy to partition.
# first few point are zigzag, we suspect low n_obs.
# there is low n_obs, so bundle them up.
#
# look at graph, where woe drops sharply
"""
int_rate:0_9.961
int_rate:9.961_12.025
int_rate:12.025_15.74
int_rate:15.74_20.281
int_rate:>=20.281
""";
In [296]:
bins = [0,9.961,12.025, 15.74, 20.281]
col = 'int_rate'
create_bins_dummies_cont(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[296]:
woe dummies continuous: funded_amnt **¶
In [297]:
col = 'funded_amnt'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[297]:
In [298]:
X_prep[colf] = pd.cut(X_prep[col], 50)
In [299]:
# for continuous variables use sort=colname
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
plot_woe(df_woe,xrot=90,color='k')
In [300]:
# woe is almost constant
# woe does not depend on dependent variable
# we can remove the variable from analysis.
woe dummies continuous: mths_since_earliest_cr_line¶
In [301]:
col = 'mths_since_earliest_cr_line'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[301]:
In [302]:
X_prep[colf] = pd.cut(X_prep[col], 50)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
plot_woe(df_woe,xrot=90,color='k')
In [303]:
bins = [0,207, 350]
col = 'mths_since_earliest_cr_line'
create_bins_dummies_int(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[303]:
In [304]:
"""
Ignore first few low numbers,
we see upward trend
mths_since_earliest_cr_line:0_207
mths_since_earliest_cr_line:208_350
mths_since_earliest_cr_line:>=351
""";
woe dummies continuous: delinq_2yrs **¶
In [305]:
col = 'delinq_2yrs'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[305]:
In [306]:
X_prep[col].value_counts()
Out[306]:
In [307]:
# we do not want factor here.
df_woe = get_woe_iv(X_prep,y_prep,col,sort=col)
plot_woe(df_woe,xrot=90,color='k')
In [308]:
"""
we might be attemped to ignore the feature
but this might be useful feature
we see lots of zeros, make it one feature.
then make 1-3 and greater than 3.
delinq_2yrs:0
delinq_2yrs:1_3
delinq_2yrs:>=3
""";
In [309]:
X_prep['delinq_2yrs:0'] = \
np.where((X_prep['delinq_2yrs'] ==0), 1, 0)
X_prep['delinq_2yrs:1-3'] = \
np.where((X_prep['delinq_2yrs'] >= 1) &
(X_prep['delinq_2yrs'] <= 3), 1, 0)
X_prep['delinq_2yrs:>=3'] = \
np.where((X_prep['delinq_2yrs'] > 3), 1, 0)
woe dummies continuous: inq_last_6mths¶
In [310]:
col = 'inq_last_6mths'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[310]:
In [311]:
X_prep[col].value_counts()
Out[311]:
In [312]:
df_woe = get_woe_iv(X_prep,y_prep,col,sort=col)
plot_woe(df_woe,xrot=90,color='k')
In [313]:
"""
we see lots of zeros, make it one feature.
then make 1-3 and greater than 3.
inq_last_6mths:0
inq_last_6mths:1
inq_last_6mths:2_3
inq_last_6mths:>=3
""";
In [314]:
X_prep['inq_last_6mths:0'] = \
np.where((X_prep['inq_last_6mths'] ==0), 1, 0)
X_prep['inq_last_6mths:1'] = \
np.where((X_prep['inq_last_6mths'] ==1), 1, 0)
X_prep['inq_last_6mths:2_3'] = \
np.where((X_prep['inq_last_6mths'] > 1) &
(X_prep['inq_last_6mths'] <= 3), 1, 0)
X_prep['inq_last_6mths:>3'] = \
np.where((X_prep['inq_last_6mths'] > 3), 1, 0)
woe dummies continuous: open_acc¶
In [315]:
col = 'open_acc'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[315]:
In [316]:
df_woe = get_woe_iv(X_prep,y_prep,col,sort=col)
plot_woe(df_woe,xrot=90,color='k')
In [317]:
X_prep[col].value_counts().sort_index()[lambda x: x>600]
Out[317]:
In [318]:
X_prep[col+':<=3'] = \
np.where((X_prep[col] <=3), 1, 0)
X_prep[col+':4_5'] = \
np.where((X_prep[col] > 3) &
(X_prep[col] <= 5), 1, 0)
X_prep[col+':6_13'] = \
np.where((X_prep[col] > 5) &
(X_prep[col] <= 13), 1, 0)
X_prep[col+':13_17'] = \
np.where((X_prep[col] > 13) &
(X_prep[col] <= 17), 1, 0)
X_prep[col+':>17'] = \
np.where((X_prep[col] > 17), 1, 0)
In [319]:
"""
There are many small number and woe curve is flat.
look at n_obs and group them.
open_acc:<=3
open_acc:4_5
open_acc:6_13
open_acc:13_17
open_acc:>17
""";
In [320]:
# df_woe
woe dummies continuous: pub_rec¶
In [321]:
col = 'pub_rec'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[321]:
In [322]:
df_woe = get_woe_iv(X_prep,y_prep,col,sort=col)
plot_woe(df_woe,xrot=90,color='k')
In [323]:
X_prep[col].value_counts().sort_index()
Out[323]:
In [324]:
X_prep[col+':0'] = \
np.where((X_prep[col] ==0), 1, 0)
X_prep[col+':1'] = \
np.where((X_prep[col] ==1), 1, 0)
X_prep[col+':2'] = \
np.where((X_prep[col] ==2), 1, 0)
X_prep[col+':3'] = \
np.where((X_prep[col] ==3), 1, 0)
X_prep[col+':>3'] = \
np.where((X_prep[col] > 3), 1, 0)
In [325]:
"""
pub_rec:0
pub_rec:1
pub_rec:2
pub_rec:3
pub_rec:>3
""";
woe dummies continuous: total_acc¶
In [326]:
col = 'total_acc'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[326]:
In [327]:
X_prep[col].value_counts()[lambda x: x>9000]
Out[327]:
In [328]:
X_prep[colf] = pd.cut(X_prep[col], 50)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
plot_woe(df_woe,xrot=90,color='k')
In [329]:
bins = [0,6.24,21.84,37.44,40.56]
col = 'total_acc'
create_bins_dummies_cont(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[329]:
In [330]:
"""
total_acc:0_6.24
total_acc:6.24_21.84
total_acc:21.84_37.44
total_acc:37.44_40.56
total_acc:>=40.56
""";
woe dummies continuous: acc_now_delinq¶
In [331]:
col = 'acc_now_delinq'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[331]:
In [332]:
X_prep[col].value_counts()
Out[332]:
In [333]:
X_prep[col+':0'] = \
np.where((X_prep[col] ==0), 1, 0)
X_prep[col+'>=1'] = \
np.where((X_prep[col] > 3), 1, 0)
In [334]:
"""
acc_now_delinq:0
acc_now_delinq:>=1
""";
woe dummies continuous: total_rev_hi_lim¶
In [335]:
col = 'total_rev_hi_lim'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[335]:
In [336]:
X_prep[colf] = pd.cut(X_prep[col], 100)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
df_woe.head()
Out[336]:
In [337]:
df_woe = df_woe[df_woe.n_obs > 1000]
df_woe
Out[337]:
In [338]:
plot_woe(df_woe,xrot=90,color='k')
In [339]:
"""
total_rev_hi_lim:<=5k
total_rev_hi_lim:5k_10k
total_rev_hi_lim:10k_20k
total_rev_hi_lim:20k_30k
total_rev_hi_lim:30k_40k
total_rev_hi_lim:40k_50k
total_rev_hi_lim:50k_65k
total_rev_hi_lim:65k_80k
total_rev_hi_lim:>80k
""";
In [340]:
X_prep['total_rev_hi_lim:<=5k'] = \
np.where((X_prep['total_rev_hi_lim'] <= 5000), 1, 0)
X_prep['total_rev_hi_lim:5k_10k'] = \
np.where((X_prep['total_rev_hi_lim'] > 5000) &
(X_prep['total_rev_hi_lim'] <= 10000), 1, 0)
X_prep['total_rev_hi_lim:10k_20k'] = \
np.where((X_prep['total_rev_hi_lim'] > 10000) &
(X_prep['total_rev_hi_lim'] <= 20000), 1, 0)
X_prep['total_rev_hi_lim:20k_30k'] = \
np.where((X_prep['total_rev_hi_lim'] > 20000) &
(X_prep['total_rev_hi_lim'] <= 30000), 1, 0)
X_prep['total_rev_hi_lim:30k_40k'] = \
np.where((X_prep['total_rev_hi_lim'] > 30000) &
(X_prep['total_rev_hi_lim'] <= 40000), 1, 0)
X_prep['total_rev_hi_lim:40k_50k'] = \
np.where((X_prep['total_rev_hi_lim'] > 40000) &
(X_prep['total_rev_hi_lim'] <= 50000), 1, 0)
X_prep['total_rev_hi_lim:50k_65k'] = \
np.where((X_prep['total_rev_hi_lim'] > 50000) &
(X_prep['total_rev_hi_lim'] <= 65000), 1, 0)
X_prep['total_rev_hi_lim:65k_80k'] = \
np.where((X_prep['total_rev_hi_lim'] > 65000) &
(X_prep['total_rev_hi_lim'] <= 80000), 1, 0)
X_prep['total_rev_hi_lim:>80k'] = np.where((X_prep['total_rev_hi_lim'] > 80000), 1, 0)
woe dummies continuous: installment¶
In [341]:
col = 'installment'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[341]:
In [342]:
X_prep[colf] = pd.cut(X_prep[col], 50)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
df_woe.head()
Out[342]:
In [352]:
df_woe = df_woe[df_woe.n_obs > 500]
df_woe.style\
.background_gradient(subset=['n_obs'],cmap='Blues')\
.background_gradient(subset=['woe'],cmap='Reds')\
.format({'n_obs':"{:,d}", 'diff_n_obs':"{:,.0f}"})
Out[352]:
In [360]:
plot_woe(df_woe,xrot=90,color='k')
In [361]:
bins = [0, 127,210,266,294,322,378,406,433,517,545,
629,712,796,852,880,935,963]
col = 'installment'
create_bins_dummies_cont(X_prep,col,bins)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[361]:
In [362]:
"""
installment:0_127
installment:127_210
installment:210_266
installment:266_294
installment:294_322
installment:322_378
installment:378_406
installment:406_433
installment:433_517
installment:517_545
installment:545_629
installment:629_712
installment:712_796
installment:796_852
installment:852_880
installment:880_935
installment:935_963
installment:>=963
""";
woe dummies continuous: annual_inc¶
In [363]:
col = 'annual_inc'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[363]:
In [364]:
X_prep[col].describe()
Out[364]:
In [365]:
X_prep[colf] = pd.cut(X_prep[col], 50)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
df_woe.head()
Out[365]:
In [366]:
# we see too many obs in first category. increase bins to 100
X_prep[colf] = pd.cut(X_prep[col], 100)
df_woe = get_woe_iv(X_prep,y_prep,colf,sort=colf)
df_woe.head()
Out[366]:
In [367]:
"""
Looking at factors of annual income, we can make two
groups with 150k (or 140k) and analyze the group
earning more than 140k separately.
""";
In [368]:
X_prep_tmp = X_prep[X_prep[col] <= 140000]
X_prep_tmp[colf] = pd.cut(X_prep_tmp[col], 50)
y_prep_tmp = y_prep[X_prep_tmp.index]
df_woe = get_woe_iv(X_prep_tmp,y_prep_tmp,colf,sort=colf)
df_woe.head()
Out[368]:
In [369]:
X_prep_tmp[col].hist()
Out[369]:
In [370]:
plot_woe(df_woe,xrot=90,color='k')
In [371]:
"""
WoE is monotonically increasing.
We can break into 10 equal parts.
""";
In [372]:
col = 'annual_inc'
bins = [20_000,30_000,40_000,50_000,
60_000,70_000,80_000,90_000,
100_000,120_000,140_000]
# we need one more labels than bins
labels = ['<20k',
'20k_30k','30k_40k','40k_50k',
'50k_60k','60k_70k','70k_80k',
'80k_90k','90k_100k','100k_120k','120k_140k',
'>140k']
create_dummies_left_inclusive(X_prep,col,bins,labels)
cols = [i for i in X_prep.columns if col in i]
X_prep[cols].head(2)
Out[372]:
woe dummies continuous: mths_since_last_delinq **¶
In [373]:
col = 'mths_since_last_delinq'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[373]:
In [374]:
X_prep[col].hist()
Out[374]:
In [375]:
# create missing column
vals = np.where((X_prep[col].isnull()), 1, 0)
X_prep[col+':missing'] = vals
print(col+':missing')
In [376]:
"""
Do not forget missing:
mths_since_last_delinq:missing
mths_since_last_delinq:0_3
mths_since_last_delinq:4_30
mths_since_last_delinq:31_56
mths_since_last_delinq:>=57
""";
In [377]:
X_prep_tmp = X_prep[pd.notnull(X_prep[col])]
y_prep_tmp = y_prep[X_prep_tmp.index]
X_prep_tmp[colf] = pd.cut(X_prep_tmp[col], 50)
df_woe = get_woe_iv(X_prep_tmp,y_prep_tmp,colf,sort=colf)
df_woe.head()
Out[377]:
In [378]:
plot_woe(df_woe,xrot=90,color='k')
In [379]:
# month is an integer, make intervals integer.
# NOTE: create missing category
In [380]:
col = 'mths_since_last_delinq'
bins = [0,3,30,56]
create_bins_dummies_int(X_prep,col,bins)
In [381]:
cols = [i for i in X_prep.columns if 'delinq' in i]
X_prep[cols].head(2)
Out[381]:
woe dummies continuous: dti **¶
In [382]:
col = 'dti'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[382]:
In [383]:
X_prep[col].hist()
Out[383]:
In [384]:
# we see very few observation after 35, we make one group
# we can create temp df with range 0 to 35 and bin into 50
# parts
In [385]:
X_prep_tmp = X_prep[X_prep[col]<35]
y_prep_tmp = y_prep[X_prep_tmp.index]
In [386]:
X_prep_tmp[colf] = pd.cut(X_prep_tmp[col], 50)
df_woe = get_woe_iv(X_prep_tmp,y_prep_tmp,colf,sort=colf)
df_woe.head()
Out[386]:
In [387]:
plot_woe(df_woe,xrot=90,color='k')
In [388]:
bins = [0,0.7,1.4,2.1,3.5,4.9,5.6,6.3,7,9,9.8,10.5,
11.2,11.9,12.6,13.3,14,14.7,17.5,18.2,21,21.7,
22.4,23.1,25.2,25.9,29.4,30.1,30.8,31.5,32.2,32.9,
33.6]
create_bins_dummies_cont(X_prep,col,bins)
woe dummies continuous: mths_since_last_record ** missing¶
In [389]:
col = 'mths_since_last_record'
colf = col + '_factor'
print('unique = ', X_prep[col].nunique())
print('nulls = ', X_prep[col].isnull().sum())
X_prep[col].head(2)
Out[389]:
In [390]:
X_prep[col].hist()
Out[390]:
In [391]:
# create missing column
vals = np.where((X_prep[col].isnull()), 1, 0)
X_prep[col+':missing'] = vals
print(col+':missing')
In [392]:
"""
mths_since_last_record:missing
mths_since_last_record:0_2
mths_since_last_record:3_22
mths_since_last_record:23_46
mths_since_last_record:47_68
mths_since_last_record:69_85
mths_since_last_record:>=86
""";
In [393]:
X_prep_tmp = X_prep[pd.notnull(X_prep[col])]
y_prep_tmp = y_prep[X_prep_tmp.index]
X_prep_tmp[colf] = pd.cut(X_prep_tmp[col], 50)
df_woe = get_woe_iv(X_prep_tmp,y_prep_tmp,colf,sort=colf)
df_woe.head()
Out[393]:
In [394]:
plot_woe(df_woe,xrot=90,color='k')
In [395]:
# look at large falling line and create groups
bins = [0,2,22,46,68,85]
create_bins_dummies_int(X_prep,col,bins)
Save the train data and repeat for test¶
In [396]:
print(X_prep.shape)
X_prep.head(2)
Out[396]:
In [397]:
# 1. after saving, comment these two lines
# 2. go up in notebook where we have defined X_prep
# and make it test and run all the cells below that.
X_prep.to_csv(dat_pro + 'Xtrain.csv')
y_prep.to_csv(dat_pro + 'ytrain.csv')
In [398]:
# after running this, comment it and uncomment train part
# we always create from train, not from test.
# X_prep.to_csv(dat_pro + 'Xtest.csv')
# y_prep.to_csv(dat_pro + 'ytest.csv')