<jupyter_start><jupyter_text>There are bunch of columns but we will choose only few data points.<jupyter_code>#select few rows
columns = ['BorrowerAPR', 'BorrowerRate', 'ListingCreationDate',
           'ProsperRating (Alpha)', 'ProsperScore',
           'ListingCategory (numeric)', 'EmploymentStatus', 'CurrentlyInGroup',
           'Term', 'CreditScoreRangeLower', 'CreditScoreRangeUpper', 'CurrentCreditLines',
           'OpenCreditLines', 'InquiriesLast6Months', 'TotalInquiries',
           'OpenRevolvingAccounts', 'OpenRevolvingMonthlyPayment', 'CurrentDelinquencies', 'AmountDelinquent',
           'RevolvingCreditBalance', 'BankcardUtilization', 'TotalTrades', 'TradesOpenedLast6Months',
           'DebtToIncomeRatio', 'TotalProsperLoans', 'LoanCurrentDaysDelinquent',
           'LoanOriginalAmount', 'MonthlyLoanPayment', 'PercentFunded', 'LoanStatus', 'ClosedDate', 'LoanOriginationDate']
data = df[columns]<jupyter_output><empty_output><jupyter_text>To simplify the columns name we will clean columns names.<jupyter_code>#rename the columns
update_columns_name = {"ProsperRating (Alpha)" : "ProsperRating", "ListingCategory (numeric)" : "ListingCategory"}
data.rename(columns=update_columns_name, inplace=True)
data.info()
data.head()<jupyter_output><empty_output><jupyter_text>Cleaning- Null values in BorrowerAPR, ProsperRating, ProsperScore, EmploymentStatus, CreditScoreRangeLower, CreditScoreRangeUpper, CurrentCreditLines, OpenCreditLines, InquiriesLast6Months, TotalInquiries, CurrentDelinquencies, AmountDelinquent, RevolvingCreditBalance, BankcardUtilization, TotalTrades, TradesOpenedLast6Months, DebtToIncomeRatio, TotalProsperLoans and ClosedDate.- ListingCreationDate, ClosedDate and LoanOriginationDate is in string format.- ListingCategory is in number but it should be in string.  Null values cleaning - Filling the null value because few columns have some default value if no value available.    - CurrentCreditLines, OpenCreditLines, InquiriesLast6Months, TotalInquiries, CurrentDelinquencies, AmountDelinquent, RevolvingCreditBalance, BankcardUtilization, TotalTrades, TradesOpenedLast6Months and TotalProsperLoans.<jupyter_code>columns = ['CurrentCreditLines', 'OpenCreditLines', 'InquiriesLast6Months', 'TotalInquiries', 'CurrentDelinquencies',
           'AmountDelinquent', 'RevolvingCreditBalance', 'BankcardUtilization',
           'TotalTrades', 'TradesOpenedLast6Months', 'TotalProsperLoans']

for column in columns:
    data.loc[data[column].isna(), column] = 0<jupyter_output>C:\Users\Naveen\Anaconda3\lib\site-packages\pandas\core\indexing.py:494: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self.obj[item] = s<jupyter_text>- Remove the null values columns    - BorrowerAPR, ProsperRating, ProsperScore, EmploymentStatus, CreditScoreRangeLower and CreditScoreRangeUpper<jupyter_code>columns = ['BorrowerAPR', 'ProsperRating', 'ProsperScore', 'DebtToIncomeRatio',
           'EmploymentStatus', 'CreditScoreRangeLower', 'CreditScoreRangeUpper']

data = data.dropna(subset=columns)
data.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 77557 entries, 1 to 113936
Data columns (total 32 columns):
BorrowerAPR                    77557 non-null float64
BorrowerRate                   77557 non-null float64
ListingCreationDate            77557 non-null object
ProsperRating                  77557 non-null object
ProsperScore                   77557 non-null float64
ListingCategory                77557 non-null int64
EmploymentStatus               77557 non-null object
CurrentlyInGroup               77557 non-null bool
Term                           77557 non-null int64
CreditScoreRangeLower          77557 non-null float64
CreditScoreRangeUpper          77557 non-null float64
CurrentCreditLines             77557 non-null float64
OpenCreditLines                77557 non-null float64
InquiriesLast6Months           77557 non-null float64
TotalInquiries                 77557 non-null float64
OpenRevolvingAccounts          77557 non-null int64
OpenRevolvingMonthlyPayment    77557 n[...]<jupyter_text>- Skip the ClosedDate null values because every loan is not in closed status - Convert the Listing Category numarical value to actual name.<jupyter_code>def change_list(x):
    ListingCategory = {0 : 'Not Available', 1 : 'Debt Consolidation', 2 : 'Home Loan', 3 : 'Business',
                       4 : 'Personal Loan', 5 : 'Student Use', 6 : 'Auto', 7 : 'Personal Loan',
                       8 : 'Personal Loan', 9 : 'Auto', 10 : 'Personal Loan', 11 : 'Wedding Loans',
                       12 : 'Green Loans', 13 : 'Home Loan', 14 : 'Personal Loan', 15 : 'Medical or Dental',
                       16 : 'Auto', 17 : 'Auto', 18 : 'Taxes',
                       19 : 'Vacation', 20 : 'Wedding Loans'}
    return ListingCategory[int(x)]

data['ListingCategory'] = data['ListingCategory'].apply(change_list)<jupyter_output><empty_output><jupyter_text>- Change the LoanOriginationDate and Closed date to datetime format from string.<jupyter_code>data.LoanOriginationDate = pd.to_datetime(data.LoanOriginationDate)
data.ClosedDate= pd.to_datetime(data.ClosedDate)
data.to_csv("clean_file.csv")<jupyter_output><empty_output><jupyter_text>Exploration<jupyter_code>#BorrowerAPR rate
sns.distplot(data['BorrowerAPR'])
plt.title("BorrowerAPR distribution");
#BorrowerAPR rate
sns.distplot(data['BorrowerRate'])
plt.title("BorrowerRate distribution");<jupyter_output><empty_output><jupyter_text>Most of our BorrowerAPR lies within 0.1 to 0.3 and Borrower Rate in 0.1 to 0.2.<jupyter_code>#ProsperRating plot
ProsperRating_order = data.ProsperRating.value_counts().index
sns.countplot(data=data, x="ProsperRating", color = base_color, order = ProsperRating_order);
#ProsperScore plot
sns.countplot(data=data, x='ProsperScore', color=base_color);<jupyter_output><empty_output><jupyter_text>- The highest ProsperRating is belong ti C and then B, A, D and so on.- Most of the customer lies in 4-8 ProsperScore which mean this dataset only contain most of the customer which are lies between the bad and good score.<jupyter_code>#Listing Category Plot
ListingCategory_order = data.ListingCategory.value_counts().index
sns.countplot(data=data, y='ListingCategory', color=base_color, order=ListingCategory_order);<jupyter_output><empty_output><jupyter_text>Most of the debtis Debt Consilidation and then Persoal Loan<jupyter_code>#Employment Status plot
sns.countplot(data=data, y='EmploymentStatus', color = base_color);<jupyter_output><empty_output><jupyter_text>Most of the customer who works as employe or full time.<jupyter_code>#In Group count plot
sns.countplot(data=data, x='CurrentlyInGroup', color = base_color);<jupyter_output><empty_output><jupyter_text>This shows that most of the money is lend outside the group.<jupyter_code>#Total Trades distribution plot
sns.distplot(data.TotalTrades);<jupyter_output><empty_output><jupyter_text>This fig shows that we only lend the money to those customer who have the Trade histroy and most of then is between 0-40.<jupyter_code>#Total Inquiries distribution plot
sns.distplot(data['TotalInquiries']);<jupyter_output><empty_output><jupyter_text>Also this shows that customer should have the less numbers of Inquiries to them. Mort inquiries shows that customer might be rejected with various lender or in a debt loop.<jupyter_code>#Monthly Loan Payments distribution plot
sns.distplot(data['MonthlyLoanPayment']);<jupyter_output><empty_output><jupyter_text>This shows that most of the monthly payement is less then 500 per month.<jupyter_code>#Loan status count plot
sns.countplot(data=data, y='LoanStatus', color=base_color);<jupyter_output><empty_output><jupyter_text>This show that we have a number of open loan but the bed debt rate is also very low.<jupyter_code>#Term count plot
sns.countplot(data=data, x='Term', color = base_color);<jupyter_output><empty_output><jupyter_text>Most of the loan is 36 month loan. Which mean customer prefrence or this club only let most of the loan with 36 month of span time.<jupyter_code>#Current Credit Lines distribution plot
sns.distplot(data['CurrentCreditLines']);<jupyter_output><empty_output><jupyter_text>Most of the customer have the current Credit lines between 0-20. _Adding loan duration since loan is opend._<jupyter_code>data.ClosedDate.max(), data.LoanOriginationDate.max()
data['loan_duartaion'] = data.ClosedDate - data.LoanOriginationDate
data.loc[data.ClosedDate.isna(), 'loan_duartaion'] = data.LoanOriginationDate.max() - data.loc[data.ClosedDate.isna(), 'LoanOriginationDate']
data['loan_duartaion'] = data['loan_duartaion'].apply(lambda x : int(x.days))
#loan duration distribution plot
sns.distplot(data['loan_duartaion'], kde=False);<jupyter_output><empty_output><jupyter_text>Most of our loans is new which is created before 200 days.<jupyter_code>#BorrowerApr vs BorrowerRate heatmap plot
bins_x = np.arange(0, 0.5, 0.1)
bins_y = np.arange(0, 0.5, 0.1)
plt.hist2d(data = data, x = 'BorrowerAPR', y = 'BorrowerRate',
           bins = [bins_x, bins_y], cmap = 'viridis_r', cmin = 0.5)
plt.colorbar();<jupyter_output><empty_output><jupyter_text>If we see then we could see that borrowerARP is incrasing with Borrower Rate. But more intresting is that club's majority of loans is between 0.10-0.20 BorrowerRate with 0.10-0.20 BorroweAPR.<jupyter_code>#BorrowerRate count distribut among term
sns.violinplot(data=data, x="Term", y='BorrowerRate', inner = None, color = base_color);<jupyter_output><empty_output><jupyter_text>This show us that club benfites with the loan with term of 36 months.<jupyter_code>#BorrowerRate count distribut among Prosper Rating
sns.violinplot(data=data, x="ProsperRating", y='BorrowerRate', inner = None, color = base_color);
sns.violinplot(data=data, x="ProsperScore", y='BorrowerRate', inner = None, color = base_color);<jupyter_output><empty_output><jupyter_text>This show that the higher the risk higer the BorrowerRate and low the risk lower the BorrowerRate.<jupyter_code>#BorrowerRate count distributed amount Employment Status
sns.violinplot(data=data, x="EmploymentStatus", y='BorrowerRate', inner = None, color = base_color)
plt.xticks(rotation = 15);
#Credit Score distrbution in term of borrower Apr
sns.regplot(data=data, x='BorrowerAPR', y='CreditScoreRangeLower', fit_reg = False, scatter_kws={'alpha' : 1/10});<jupyter_output><empty_output><jupyter_text>Above fig show us that the lower the credit score lower higer the BorrowerAPR because those are the customer with higer risk.<jupyter_code>#Prosper Score count distrbuted among Prosper Rating
plt.figure(figsize=[14.70, 8.27])
rating_order = ['AA', 'A', 'B',  'C', 'D',  'E',   'HR']
data['ProsperRating'] = pd.Categorical(data['ProsperRating'],
                                       categories=rating_order,
                                       ordered = True)
ax = sns.countplot(data = data, x = 'ProsperRating', hue = 'ProsperScore', palette="RdYlBu_r")
ax.legend(loc = 8, ncol = 3, framealpha = 1, title = 'ProsperScore');
ax.legend(loc='center left', bbox_to_anchor=(1, 0.5),
         framealpha=1, title="ProsperScore");
#Prosper Rating count in term of Credit Score
data.groupby(["ProsperRating"]).CreditScoreRangeLower.value_counts().unstack("ProsperRating").plot.line(figsize=[14.70, 6]);<jupyter_output><empty_output><jupyter_text>This show that the higer credit scroe customer is belong to AA or A catorgry and he C is thos customer who have the worst credit score.<jupyter_code>#Prosper Rating count in term of Open Credit Line
data.groupby(["ProsperRating"]).OpenCreditLines.value_counts().unstack("ProsperRating").plot.line(figsize=[14.70, 6]);<jupyter_output><empty_output><jupyter_text>This shows that the all the rating have the open credit within 10-20 but few catorgry is have the higher number of credit line.<jupyter_code>#Prosper Rating varation among Total Trade
data.groupby(["ProsperRating"]).TotalTrades.value_counts().unstack("ProsperRating").plot.line(figsize=[14.70, 6]);
#Total Proseper Loans variation in term of Proser Rating
data.groupby(["ProsperRating"]).TotalProsperLoans.value_counts().unstack("ProsperRating").plot.line(figsize=[14.70, 6]);
#Loan Status count vary among Total Trades
data.groupby(["LoanStatus"]).TotalTrades.value_counts().unstack("LoanStatus").plot.line(figsize=[14.70, 6]);
#Delinquent scatter amont total Inquiries based on Proser Rating
g = sns.FacetGrid(data = data, col='ProsperRating', size = 2.5,
                margin_titles = True)
g.map(plt.scatter, 'TotalInquiries', 'LoanCurrentDaysDelinquent');<jupyter_output>C:\Users\Naveen\Anaconda3\lib\site-packages\seaborn\axisgrid.py:230: UserWarning: The `size` paramter has been renamed to `height`; please update your code.
  warnings.warn(msg, UserWarning)<jupyter_text>Above Facet Grid show that the A and AA catory have the less nuber of Inquiries bit have some delinquent history. There might be some other reason for that except debt loop. But HR, E and D is those which might be in a debt loop.<jupyter_code>#Debt to income ration scatter amont total Inquiries based on Proser Rating
g = sns.FacetGrid(data = data, col='ProsperRating', size = 2.5,
                margin_titles = True)
g.map(plt.scatter, 'TotalInquiries', 'DebtToIncomeRatio');<jupyter_output><empty_output><jupyter_text>Like last facet. This alos show the relaction ship between Inquiries and Debt TO Income Ratio. Which show us the D, E and HR is in a debt loop becase they have high debt to income ration and mative Inquiries.<jupyter_code>#Debt To income ratio scatter amont Delinquencies based on Proser Rating
g = sns.FacetGrid(data = data, col='ProsperRating', size = 2.5,
                margin_titles = True)
g.map(plt.scatter, 'CurrentDelinquencies', 'DebtToIncomeRatio');<jupyter_output><empty_output><jupyter_text>This facet grid shows that some customer might have less delinquencies details but they have hight Debt To Income Ratio. So, HR, E and D is in a debt loop.<jupyter_code>#Debt To Income ratio scatter amont total Inquiries based on Current In Group
g = sns.FacetGrid(data = data, col='CurrentlyInGroup', size = 5,
                margin_titles = True)
g.map(plt.scatter, 'CurrentDelinquencies', 'DebtToIncomeRatio');<jupyter_output><empty_output><jupyter_text>This show that outside the group customeris in a debt loop but inside the grop customer do not have much debt and might have some delinquencies history.<jupyter_code>#Debt to income ratio scatter amont total Inquiries based on Employment Status
g = sns.FacetGrid(data = data, col='EmploymentStatus', size = 2.5,
                margin_titles = True)
g.map(plt.scatter, 'CurrentDelinquencies', 'DebtToIncomeRatio');<jupyter_output><empty_output><jupyter_text>This show that those salaried customer have high debt to income ratio. Create a closed data set and divide the status of closed loan.<jupyter_code>closed_loan = data[data.ClosedDate.notnull()]
closed_loan.loc[closed_loan.loan_duartaion//30.417 < closed_loan.Term, 'status'] = 'early'
closed_loan.loc[closed_loan.loan_duartaion//30.417 == closed_loan.Term, 'status'] = 'time'
closed_loan.loc[closed_loan.loan_duartaion//30.417 > closed_loan.Term, 'status'] = 'late'
#Debt to ratio scatter amont total Inquiries based on Status
g = sns.FacetGrid(data = closed_loan, col='status', size = 5,
                margin_titles = True)
g.map(plt.scatter, 'TotalInquiries', 'DebtToIncomeRatio');<jupyter_output><empty_output><jupyter_text>This show that those customer how clear there loan before time they are higly in debt to ratio or we can say that those custoemr how have numbers of loans will close there loan before the time. This shows the debt loop.<jupyter_code>#Debt to Ratio scatter amont total Inquiries based on Status
g = sns.FacetGrid(data = closed_loan, col='status', size = 4,
                margin_titles = True)
g.map(plt.scatter, 'DebtToIncomeRatio', 'TotalTrades');
#Percent Funded scatter amont Debt to income ratio based on Status
g = sns.FacetGrid(data = closed_loan, col='status', size = 4,
                margin_titles = True)
g.map(plt.scatter, 'DebtToIncomeRatio', 'PercentFunded');<jupyter_output><empty_output><jupyter_text>The customer who is in debt loop will take the loan on any condition event they will not receive the entire the principle.<jupyter_code>#Credit score amont total Inquiries based on Status
g = sns.FacetGrid(data = closed_loan, col='status', size = 5,
                margin_titles = True)
g.map(plt.scatter, 'TotalInquiries', 'CreditScoreRangeLower');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10047675804529202, 'fraction_numerical': 0.018116805721096543, 'mean_word_length': 5.390327494287891, 'pattern_counts': {'":': 0, '<': 128, '<?xml version=': 0, '>': 128, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '32', 'dir': 'jupyter-structured-clean-dedup', 'id': '370096', 'license': 'MIT', 'n_tokens_mistral': 5684, 'n_tokens_neox': 5212, 'n_words': 1457, 'path': 'exploration_template.ipynb', 'repo_name': 'naveenmaan/Loan-Data-Analysis', 'size': '16780'}