BANK LOAN CASE STUDY

By

Uttkarsh Mathur

DESCRIPTION

• The Project contains the predictive analysis for lending credit to potential users and predict whether the person getting credit is reliable or not.

​

• For the provided data set we are going to perform data preprocessing using jupyter notebook and perform visualization using seaborn in it and for some complex visualizations we have used MS Excel somewhere too.

​

• We will drag out the necessary insights such as which group of society is most likely to take credit and repay it and vice versa so that the organization can take right decisions and prevent from making wrong investments.

​

JUPYTER NOTEBOOK AND EXCEL FILE LINK

• All the charts and how we made them is present in following links:
• Excel file:https://docs.google.com/spreadsheets/d/1eeDwagXIRK2R_G6yOnCB4D65o9iDE2aM/edit?usp=sharing&ouid=115553774201524595434&rtpof=true&sd=true
• Jupyter Notebook file:https://drive.google.com/file/d/1zPXIMyvZbmGE0YnfwrJNkhk1ThfmCWrY/view?usp=share_link
• You should go through jupyter notebook file first then excel file.

APPROACH

​

For the project, I approached it by first analyzing the data provided and understanding the different columns and their significance. I then proceeded to clean the data by removing null values, dropping unnecessary columns. Finally, I visualized the data using appropriate charts and graphs to better understand the trends and patterns. Overall, my approach was to take a step-by-step approach to thoroughly understand the data and perform the necessary cleaning and analysis to gain insights and answer the research questions

​

TECH STACK USED

• For Preprocessing and visualization we have used jupyter notebook with the help of Pandas , Numpy and Seaborn .

​

• At some places we have used MS Excel to make some charts .

​

• To understand the description of table we have used both Excel and Jupyter notebook.

INSIGHTS

• We have used Application_data.csv file for all the analysis and used previous_application.csv to consider and understand the criteria of the organization which helped us to do predictive analysis.
• For data preprocessing we have inspected and read the application data file and checked for null values too, if the null values are more than 35% we removed them.

​

INSIGHTS

• We did not removed or made any changes in Occupation_Type column as it may contain useful information in further analysis, also imputing it with mean or median values is not sensible as it’s a non numerical column.

​

• While analyzing data set we saw that days columns contains negative value which is not valid so we changed the negative values to positive values .

​

• We also noticed some columns containing Y/N columns so we replaced the values with 0/1 accordingly.

​

• While checking for Categorical columns we found some XNA values which stands for NULL values .

​

INSIGHTS

• We looked for any patterns in XNA values columns and observed that for such values:
• NAME_INCOME_TYPE is Pensioner in ORGANIZATION_TYPE column
• Found that the value is missing at random and don’t have any hidden patterns.
• Now, we imputed the null values for occupation_type as Pensioner because income_type column suggested it.
• Pensioner is most frequent value of occupation_type.

​

INSIGHTS

• We have found some nominal columns such as AMT_INCOME and AMT_CREDIT thorough which we have created two categorical columns with values such as MEDIUM , LOW , VERY LOW & HIGH.

​

• Such columns will help us in further visualization.

INSIGHTS

• We created a new column named AGE_group by converting the DAYS_BIRTH to years.
• Converting it provided us with following values:
• VERY_YOUNG
• YOUNG
• MIDDLE_AGE
• SENIOR_CITIZEN

​

• Most frequent age group found was MIDDLE_AGE

INSIGHTS

• We again checked the datatypes for all columns and changed them according to their values.
• After this we observed the data set and found that we have some columns that do not help us in any form of analysis so we dropped them to make the data set clear and easy to find insights.

INSIGHTS

• OUTLIER ANALYSIS: For detecting any outliers present still in the data set we made a list of all numerical columns and putted the data into box plots to understand outliers.
• Observations:
• CNT_CHILDREN have outliers where count is 5 children
• IQR for AMT_INCOME_TOTAL is very less with large number of outliers
• AMT_CREDIT third quartile is larger as compared to the first quartile which tells us that most credit amount of loan of customers are in third quartile with huge number of outliers
• Third quartile of AMT_ANNUITY is larger than First quartile by a bit with large numbers of outliers.

​

​

INSIGHTS

​

​

• From CNT_FAM_MEMBERS we found that most of the applicants have 4 family members with some outliers ofcource.

​

• DAYS_BIRTH,DAYS_ID_PUBLISH and EXT_SOURCE_2&3 don’t have any outliers.

​

INSIGHTS

DATA IMBALANCE: The imbalance ratio we got is 11:38 because defaulted population is 8.1% and non defaulted population is 91.9%.

Now we will separately analyze for target variables for better analysis.

INSIGHTS�

It Seems like female clients applied higher than

male clients for loan.

• 66.6% female clients are non defaulters while

33.4% males are non defaulters

• 57% females are defaulters while 42% male

clients are defaulters.

• Middle Age(35-60) this group has applied for credit

More than any other age group for both defaulter and

non defaulter cases.

• The middle age group is facing difficulties in paying

More than any other group.

​

INSIGHTS

• Clients with Business Entity Type 3, self-employed,

Other , Medicine , Government ,Business Entity Type 2

Applied more for the loans in comparison to others for both Defaulters and Non Defaulters.

• Clients with Industry: type 13 & 8,Trade:type 4&5 applied lower for loans in both defaulters and non defaulters as compared to others.

​

INSIGHTS

• For categorical columns it is difficult and time consuming to plot charts so we used pandas in Jupyter Notebook for iterating each categorical column.
• Following are the insights:
• NAME_TYPE_SUIT: For both Defaulters and Non Defaulters there were a family member present while applying for credit.
• NAME_INCOME_TYPE: Clients applied have income source from Working, Commercial associate and Pensioner .
• Working Category have more risk of having defaulters
• State Servant have least chance of being a defaulter.

3. NAME_EDUCATION_TYPE:

• Secondary or Secondary Special are more likely to apply.
• Secondary or Secondary Special have more risk of being a defaulter

as compared to others.

INSIGHTS

4. NAME_CONTRACT_TYPE:

• Most Clients have applied for Cash Loans and small amount of people have applied for Revolving Loan for both Defaulter and Non Defaulter category.

5. NAME_FAMILIY_STATUS:

• Married clients are the most frequent applicants for both category
• Clients having relationship as single are less risky
• Widows are also least risky

6. NAME_HOUSING_TYPE: Most of applicants own a house or apartment for both category.

​

INSIGHTS

​

7. OCCUPATION_TYPE:

• Pensioner are most frequent in applying for credit for both categories
• Pensioners and Laborers are most likely to default.

​

8. AMT_INCOME_TYPE: Clients with low and Medium income are more risky.

​

9. AMT_CREDIT_TYPE: Applicants for high and low credit are most risky.

​

​

INSIGHTS

• UNIVARATE ANALYSIS OF NUMERICAL COLUMNS:

People with target 1 has largely staggered income as compared to target 0

• Plots highlights that people having difficulties to pay back with respect to their income ,loan amount and cost of goods against which loan and annuity is prepared.

INSIGHTS

• BIVARIATE ANALYSIS: NUMERICAL & CATEGORICAL TARGET VARIABLES:
• Widow client with academic degree have few outliers and don’t have first and third quartile for target 0.
• For AMT_INCOME_TOTAL & TARGET 0,1: Income of clients with all types of family status having rest of the education type lie below 25%.

​

1.Clients who haven’t completed their degree are more likely to

have higher income.

2. Some clients with Secondary and Secondary special tend to

have higher incomes.

INSIGHTS

• BIVARIATE ANALYSIS: NUMERICAL & CATEGORICAL TARGET VARIABLES:
• Married applicant with academic degree is having much lesser income than others.
• Defaulter clients have relatively less income as compared to others.

INSIGHTS

• BIVARIATE ANALYSIS: NUMERICAL & CATEGORICAL TARGET VARIABLES:
• Most client Credit amount lie below 25%
• Applicants with academic degree and is a widow tend to take higher credit loan.
• Except Academic Degrees other education types are having less outliers.
• Married client with academic degree applied for higher credit.
• Single clients having academic degree have small loans with

no outliers.

​

INSIGHTS

• BIVARIATE ANALYSIS OF CATEGORICAL COLUMN(finding maximum clients with payment difficulties):
• Being a long process for this we have used jupyter notebook again .
• From the graph below we can easily see that people from medium income range have applied the most and payment difficulties are faced by Low Income client followed by very low and medium.

​

INSIGHTS

We can clearly see from the chart that Working people

have applied for loan the most and payment difficulties

are faced mostly by unemployed.

It is clearly seen that cash loans are applied the most

and payment difficulties are faced by cash loan

Clients more as compared to Revolving Loans.

INSIGHTS

Secondary and secondary special have applied the most while lower secondary Education type faced most difficulties in paying back the loan.

Clients with house/apartments have applied most and payment

Difficulties are faced mostly by rented apartment clients and clients

Living with parents.

INSIGHTS

Pensioners and laborers have applied most for loans but payment difficulties mostly faced by

Low skill laborers and waiters/barmen staff , drivers , security staff, cooking staff and laborers.

INSIGHTS

• DISTRIBUTION OF CODE GENDER WITH RESPECT TO AMT_INCOME_RANGE(To find maximum % loan payment difficulties):
• Below table shows that females with academic degree and high income have a higher risk of default
• Male clients with secondary and secondary special education with all types of salary have high risk of default.
• Male clients with incomplete education

having very low salary have a high risk to

default.

• Male clients with lower secondary

education with very low or medium

income are more risky

INSIGHTS

Pair Plots for visualization and better understanding of Numerical Variables for Target 0:

INSIGHTS

Pair Plots for visualization and better understanding of Numerical Variables for Target 1:

INSIGHTS

• In the pair plots present in previous slides we have find the following insights:

1. AMT_CREDIT & AMT_ANNUITY are highly correlated for both defaulters and non defaulters, so as loan price increases the emi amount also increases.

​

2. AMT_CREDIT & AMT_GOODS_PRICE are highly correlated as well as goods price increases it elevates the loan amount too.

​

3. AMT_CREDIT , AMT_AMT_GOODS_PRICE & AMT_ANNUITY are correlated highly which may not be a good indicator for defaulter detection.

​

INSIGHTS

Correlation using heat maps

For target 0:

INSIGHTS

• From previous slide heat map we have following insights:
• AMT_CREDIT is inversely proportional to DAYS_BIRTH , CNT_CHILDREN because people belong to low age group taking high credit amount and vice versa also the credit amount is higher for fewer children count clients and vice versa.
• AMT_INCOME_TOTAL is inversely proportional to CNT_CHILDREN as more income for fewer children the clients have and vice versa.
• AMT_CREDIT & AMT_INCOME_Total both are higher inn a densely populated area.

INSIGHTS

Correlation using

heat maps For target 1:

INSIGHTS

• From previous slide heat map we have found following insights:
• This heat map for target 1 have quite similar observations as target 0 but with minute changes such as:
• Client’s permanent address not matching the contact address having few children.
• Client’s permanent address not matches the work address having few children.

INSIGHTS

• TOP 10 CORRELATION FOR TARGET 0 &1:
• Top 10 correlation for defaulters and non defaulters are same for most columns.

Target 0

Target 1

INSIGHTS

• Now , we started the same data preprocessing of previous_application.csv file.
• We have followed same steps as for application_data.csv
• We looked for the description of table and understood the data.
• Afterwards, we analyzed null values and dropped the columns having null values more than 35%.
• Now, with the null columns with less than 35% null values , we used mode value to fill the nulls.

INSIGHTS

• After dropping null columns and filling the null columns we looked for xna and xap values which were found in following columns:

NAME_CASH_LOAN_PURPOSE

NAME_CASH_LOAN_PURPOSE

NAME_CASH_LOAN_PURPOSE

• Now , we found that DAYS_DECISION column had negative values which is not appropriate, so we turned it to positive values.
• Now , we merged the dataframes application_data.csv and previous_applications.csv to look for insights and patterns in it.

INSIGHTS

After joining the data set we visualized some columns

and found following insights:

• Around 80.7% clients were repeaters applying

for loan.

• 14.5% clients are new applying for the loan.

​

Previous applications percentage are as follows:

• Approved loans are 38.8%
• Refused loans are 58.5%
• Canceled applications are 2.3%
• Unused offers are 0.31%

​

​

INSIGHTS

​

NAME_CONTRACT_STATUS with NAME_CASH_LOAN_PURPOSE:

• Loans which got rejected mostly are from Repairs type
• Education and Medicine have equal approval and rejection rate.
• Buying a new car and paying other loans categories have higher rate of rejections as compared to approvals.

INSIGHTS

Target with Name_Cash_Loan_Purpose:

• Here there’s a high variation for Repairs for both targets
• Medicine loan clients have more difficulties in paying

back loans

• Education loans are as high as Medicine but repayment

made by client is more as compared to Medicine.

• Building and Buying used car loan purposes have same

ratio for both defaulters and non defaulters .

INSIGHTS

• Distribution between AMT_CREDIT and NAME-HOUSING_TYPE:
• Here for Housing type, office apartment is having higher credit of target 0 and co-op apartment is having higher credit of target 1. So, we can conclude that bank should avoid giving loans to the housing type of co-op apartment as they are having difficulties in payment. Bank can focus mostly on housing type with parents or House\apartment or municipal apartment for successful payments

​

CONCLUSION

• There is an increase in payment difficulties for Working section of society and decrease for same for Pensioners.
• We observed that there is increase in payment difficulties for single and civil married clients whereas a decrease in payment difficulties for widows and married clients. Also these categories are applying for loan more as compared to previous times.
• Count of Lower Secondary is very less also it has maximum % of payment difficulties around 11%. Hence, client with education type as Lower Secondary are the driving factors for Loan defaulters.
• The count of Low skilled Laborers is comparatively very less and has maximum % of payment difficulties around 17%. Hence, Low skilled Laborers clients are the driving factors for Loan Defaulters

CONCLUSION

• The banks should minimize their focus from working type income clients as most defaulters fall under this category.
• For non defaulters clients , banks should consider Pensioner , Students and Businessman who don’t have housing type as co-op and office apartments.
• Banks should be careful from Repair loans as unsuccessful payments on due date are high under this purpose.
• Most eligible clients are from housing type as Parent as they have least number of unsuccessful payments compared to any other type.

RESULT

• From this project , I started to learn pandas , numpy and seaborn as the data sets were huge enough that I was not able to work with it using only excel.
• I learned new technology because of this project , took help from internet for learning it , done my research on different libraries of pandas such as numpy and python , although there is so much more to learn .
• I assure you that I have learned the technology also used excel where ever I found it is possible so that the task should be less complex for me to understand.
• I have used examples for plotting graphs in seaborn as I am not fluent enough in it .
• I’m happy to finish this project although it took long for me to submit it as compared to my previous submissions, I believe I will get more to learn in the next project.

THANK YOU