In [1]:
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.linear_model import LinearRegression
%matplotlib inline
import seaborn as sns
sns.set_style('whitegrid')
import warnings
warnings.filterwarnings('ignore')
from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"
In [2]:
df=pd.read_csv('~/dm/pbl1/LoansTrainingSet.csv',low_memory=False)
In [3]:
df.info()
In [4]:
df[df['Credit Score'].isnull()]['Loan ID'].value_counts()
Out[4]:
In [5]:
df[df['Loan ID']=='9cd8febc-48ff-4f54-8475-e57a4e5b1c9b']
Out[5]:
Monthly Debt¶
In [6]:
df['Monthly Debt']=df['Monthly Debt'].str.replace('$','')
In [7]:
df['Monthly Debt']=df['Monthly Debt'].replace('[ ]','',regex=True)
In [8]:
df['Monthly Debt']=df['Monthly Debt'].replace(',','',regex=True)
In [9]:
df['Monthly Debt']=df['Monthly Debt'].astype(float)
In [10]:
col=df.columns
print col
for i in col:
print i,' ',df[i].isnull().sum()
# print i,' ', df[i].nunique()
In [11]:
cat_df=df.select_dtypes(include=['object'])
Maximum open credit¶
In [12]:
df['Maximum Open Credit'].replace('#VALUE!',np.nan,regex=True,inplace=True)
In [13]:
df['Maximum Open Credit'].isnull().sum()
Out[13]:
In [14]:
df['Maximum Open Credit'].iloc[187512]
Out[14]:
In [15]:
#length=len(df['Maximum Open Credit'])
In [16]:
'''
for i in range(0,length):
#v=df['Maximum Open Credit'].iloc[i]
try:
v=float(df['Maximum Open Credit'].iloc[i])
except ValueError,e:
print 'line i is bad ',i
'''
Out[16]:
In [17]:
df['Maximum Open Credit']=df['Maximum Open Credit'].astype(float)
In [18]:
df.dropna(subset=['Maximum Open Credit'],inplace=True)
In [19]:
#df['Maximum Open Credit']=df['Maximum Open Credit'].str.replace(r"[a-zA-Z]",'')#extract('(\d+)', expand=False)
In [20]:
#df['Maximum Open Credit']=df['Maximum Open Credit'].str.replace(r"[a-zA-Z]",'')#extract('(\d+)', expand=False)
In [21]:
#df['Maximum Open Credit']=df['Maximum Open Credit'].replace('\D','')
Years in Current Job¶
In [22]:
df['Years in current job'].value_counts()
Out[22]:
In [23]:
11476.0/250000 * 100.0
Out[23]:
In [24]:
tmp2_df=df[df['Years in current job']=='n/a']
j=pd.DataFrame()
df['Years in current job'].replace('< 1 year','1',inplace=True)
df['Years in current job'].replace(' 1 year','1',inplace=True)
df['Years in current job'].replace('1 year','1',inplace=True)
df['Years in current job'].replace('2 years','1',inplace=True)
df['Years in current job'].replace('3 years','1',inplace=True)
df['Years in current job'].replace('4 years','1',inplace=True)
df['Years in current job'].replace('5 years','1',inplace=True)
df['Years in current job'].replace('6 years','2',inplace=True)
df['Years in current job'].replace('7 years','2',inplace=True)
df['Years in current job'].replace('8 years','2',inplace=True)
df['Years in current job'].replace('9 years','2',inplace=True)
df['Years in current job'].replace('10+ years','2',inplace=True)
df['Years in current job'].replace('n/a','1',inplace=True)
tmp3_df=df[df['Years in current job']!='n/a']
df['Years in current job'].value_counts()
#df['Years in current job']=df['Years in current job'].astype('category')
Out[24]:
In [25]:
df.head(2)
Out[25]:
In [127]:
df['Purpose'].value_counts()
Out[127]:
In [26]:
df['Years in current job']=df['Years in current job'].astype(int)
In [27]:
#df['Years in current job'].plot(kind='barh')
In [130]:
df['Years in current job']=df['Years in current job'].astype('category')
df['Years in current job'].value_counts()
Out[130]:
In [28]:
#dont del df['Years in current job']
++++++++++++++++++++++++++++++++++++++++++++++++++++=¶
In [29]:
num_df=df.select_dtypes(include=['float64','int64'])
In [30]:
num_df.info()
In [31]:
num_df['Loan ID']=df['Loan ID']
In [32]:
num_df.isnull().sum()
Out[32]:
In [33]:
num_df=num_df.groupby(by='Loan ID',as_index=False).max()
num_df2=num_df.groupby(by='Loan ID',as_index=False).min()
In [131]:
num_df.info()
In [34]:
num_df[num_df['Loan ID']=='9cd8febc-48ff-4f54-8475-e57a4e5b1c9b']
num_df2[num_df['Loan ID']=='9cd8febc-48ff-4f54-8475-e57a4e5b1c9b']
Out[34]:
Out[34]:
In [35]:
cat_df=df.select_dtypes(include=['object'])
In [36]:
del cat_df['Customer ID']
In [37]:
cat_df.info()
In [38]:
cat_df.drop_duplicates(['Loan ID'],inplace=True)
In [39]:
cat_df.info()
In [40]:
cat_df.shape,num_df.shape
Out[40]:
++++++++++++++++++++++++++++++++++++++++++++++++++++=¶
In [41]:
df=pd.merge(cat_df,num_df,on='Loan ID')
In [42]:
df.shape
Out[42]:
In [43]:
df.nunique()
Out[43]:
Loan Status:¶
In [44]:
df['Loan Status'].isnull().sum()
Out[44]:
In [45]:
df['Loan Status'].value_counts()
Out[45]:
Current Loan Amount¶
In [46]:
df['Current Loan Amount'].isnull().sum()
Out[46]:
In [47]:
df['Current Loan Amount'].value_counts().sum()
Out[47]:
In [48]:
df['Current Loan Amount'].hist(bins=50)
Out[48]:
In [49]:
tmp_df=df[df['Current Loan Amount']<99999]
In [50]:
tmp_df['Current Loan Amount'].hist()
print tmp_df.shape,df.shape, df.shape[0]/tmp_df.shape[0]
Out[50]:
In [51]:
tmp_df['Current Loan Amount'].mean(),tmp_df['Current Loan Amount'].median(),tmp_df['Current Loan Amount'].mode()
Out[51]:
Term¶
In [52]:
df['Term'].isnull().sum(), df['Term'].nunique(),df['Term'].value_counts()
Out[52]:
Credit Score¶
In [53]:
df['Credit Score'].isnull().sum(),df['Credit Score'].nunique(),df['Credit Score'].value_counts().sum()
Out[53]:
In [54]:
df2=df[df['Credit Score']<800]
In [133]:
df2['Credit Score'].hist()
Out[133]:
In [56]:
df['Credit Score']=df['Credit Score'].apply(lambda x: x/10 if x > 800 else x)
In [132]:
df['Credit Score'].hist()
Out[132]:
In [58]:
#def replaceCreditScore(score):
# if score > 800:
# a=score/10
# return a
# else:
# return score
Home Ownership¶
In [59]:
df['Home Ownership'].value_counts()
Out[59]:
In [60]:
del df['Home Ownership']
Annual Income¶
In [61]:
test_df=df[df['Annual Income']!= np.nan]
In [62]:
h=test_df[test_df['Annual Income']<0.4e7]
h['Annual Income'].hist(bins=50)
print h['Annual Income'].mean(),h['Annual Income'].median()
Out[62]:
Purpose¶
In [63]:
#del df['Purpose']
df['Purpose'].value_counts()
Out[63]:
In [64]:
#del df['Purpose']
#Dont Delete
Months since last¶
In [65]:
#df['Months since last delinquent'].fillna('3',inplace=True)
In [66]:
df['Months since last delinquent'].median()
Out[66]:
In [67]:
df['Months since last delinquent'].hist()
Out[67]:
In [68]:
df['Months since last delinquent'].fillna(df['Months since last delinquent'].median(),inplace=True)
In [69]:
df['Months since last delinquent']
Out[69]:
Number of open accounts¶
In [70]:
df['Number of Open Accounts'].value_counts()
Out[70]:
number of credit problems¶
In [71]:
df['Number of Credit Problems'].hist(log=True)
Out[71]:
In [148]:
df[df['Tax Liens']==0]['Tax Liens'].unique()
Out[148]:
Current Credit Balance¶
In [72]:
df['Current Credit Balance']
Out[72]:
Bankruptcies¶
In [73]:
df.Bankruptcies.value_counts(dropna=False)
Out[73]:
In [74]:
bank_df=df[df.Bankruptcies.isnull()]
In [75]:
k=bank_df[bank_df['Credit Score']<800]
In [76]:
k['Credit Score'].hist(bins=50)
Out[76]:
In [77]:
df.dropna(subset=['Bankruptcies'],inplace=True)
In [78]:
bank_df['Months since last delinquent']
Out[78]:
Tax Liens¶
In [79]:
df['Tax Liens'].shape
Out[79]:
In [80]:
df.isnull().sum()
Out[80]:
In [81]:
df.dropna(axis=0,subset=['Tax Liens'], inplace=True)
In [82]:
df['Tax Liens'].shape
Out[82]:
In [83]:
#for index,row in df.iterrows():
#if row['Maximum Open Credit']==9999:
# row['Maximum Open Credit'].astype(float)
In [84]:
#df['Maximum Open Credit'].astype(float,inplace=True)
#df['Maximum Open Credit'].hist()
In [85]:
#tmp3_df['Years in current job'].astype(int)
#tmp3_df['Years in current job'].hist()
Linear Regression¶
In [86]:
df.isnull().sum()
Out[86]:
In [87]:
train_data=df[df['Credit Score'].notnull()]
test_data=df[df['Credit Score'].isnull()]
to_predict1=df['Annual Income']
to_predict2=df['Credit Score']
In [88]:
train_data.shape,test_data.shape,df.shape
Out[88]:
In [89]:
#def multiple_regression_model(train,test,input_features,to_predict):
# regr = LinearRegression()
# regr.fit(train.as_matrix(columns = input_features), train.as_matrix(columns =to_predict))
# return regr
In [90]:
input_features=df[['Years of Credit History','Monthly Debt']]
to_predict1=df[['Annual Income']]
In [149]:
#rg=multiple_regression_model(train_data,test_data,input_features,['Annual Income'])
regr1 = LinearRegression(fit_intercept=False)
regr2 = LinearRegression()
#regr.fit(train_data.as_matrix(columns = ['Years of Credit History','Current Loan Amount','Monthly Debt']), train_data.as_matrix(columns =to_predict1))
#regr.fit(train_data.as_matrix(columns = ['Years of Credit History','Current Loan Amount','Monthly Debt']), train_data.as_matrix(columns =to_predict1))
In [92]:
regr1.fit(train_data[['Years of Credit History','Monthly Debt','Years in current job']],train_data['Annual Income'])
Out[92]:
In [93]:
filled_in_annual_income=regr1.predict(test_data[['Years of Credit History','Monthly Debt','Years in current job']])
In [94]:
from sklearn.metrics import mean_squared_error
In [95]:
mean_squared_error(train_data['Annual Income'],regr1.predict(train_data[['Years of Credit History','Monthly Debt','Years in current job']]))**0.5
Out[95]:
In [96]:
test_data['Annual Income']=filled_in_annual_income
In [97]:
test_data['Annual Income'].isnull().sum()
Out[97]:
In [98]:
train_data['Annual Income'].hist(log=True,bins=50)
Out[98]:
In [99]:
df=train_data.append(test_data)
In [100]:
df['Annual Income'].hist(bins=50,log=True)
Out[100]:
Filling Credit Score¶
In [101]:
regr2.fit(train_data[['Years of Credit History','Monthly Debt','Years in current job','Annual Income']],train_data['Credit Score'])
Out[101]:
In [102]:
filled_in_credit_score=regr2.predict(test_data[['Years of Credit History','Monthly Debt','Years in current job','Annual Income']])
In [103]:
test_data['Credit Score']=filled_in_credit_score
In [104]:
test_data['Credit Score'].isnull().sum()
Out[104]:
In [105]:
result=pd.concat([train_data,test_data])
In [106]:
result.sort_index(ascending=True,inplace=True)
result.to_csv('loan_predictions_output.csv')
In [107]:
result.head(3)
Out[107]:
OLD:¶
In [113]:
#new_df=df.duplicated(subset='Loan ID')
In [114]:
'''newdf=groupby(df['Loan ID']).max()
#agg({'Loan Status':'max', 'height':sum, 'weight':sum})[['age','height','weight']]
'''
Out[114]:
In [115]:
#newdf=df.groupby(df['Loan ID'])[''].max()
In [116]:
#newdf.shape
In [117]:
#newdf[newdf['Loan ID']=='b624940f-da82-410b-b885-77d92c2e4f0c']
In [118]:
#df2['Credit Score'].dropna(inplace=True)
In [119]:
#df2['Annual Income'].dropna(inplace=True)
In [120]:
#df2['Annual Income'].isnull().sum()
In [121]:
df2.dropna(inplace=True)
In [122]:
h=df2[ (df2['Current Loan Amount'] < 9999999 ) & (df2['Credit Score']>700 )& (df2['Credit Score'] <800)]
#plt.hist(h['Current Loan Amount'])
print h['Current Loan Amount'].mean()
print h['Current Loan Amount'].median() ############# This one
print h['Current Loan Amount'].mode()
df2['Current Loan Amount'].replace(99999999,h['Current Loan Amount'].median(),inplace=True)
df2['Credit Score']=df2['Credit Score'].apply(lambda x: x/10 if x > 800 else x)
df3=df2[df2['Credit Score'] < 800]
high_score=df3[df3['Credit Score']>700]
low_score=df3[df3['Credit Score']<600]
#df3['Maximum Open Credit']=df3['Maximum Open Credit'].astype(float)
#plt.scatter( df3['Maximum Open Credit'],df3['Credit Score'])