<jupyter_start><jupyter_text>Analyzing borrower's risk of defaultingYour project is to prepare a report for a bank’s loan division. You’ll need to find out if a customer’s marital status and number of children has an impact on whether they will default on a loan. The bank already has some data on customer's credit worthiness.Your report will be considered when building a "credit scoring" of a potential customer. A "credit scoring" is used to evaluate the ability of a potential borrower to repay their loan.**Description of the data:*** children : the number of children in the family * days_employed : how long the customer has been working* dob_years : the customer’s age* education : the customer’s education level* education_id : identifier for the customer’s education* family_status : the customer’s marital status* family_status_id : identifier for the customer’s marital status* gender : the customer’s gender* income_type : the customer’s income type* debt : whether the customer has ever defaulted on a loan* total_income : monthly income* purpose : reason for taking out a loan  Step 1. Open the data file and have a look at the general information.<jupyter_code>import pandas as pd
import numpy as np
# Read the games data
credit_scoring_eng = pd.read_csv('/Users/fanibhushan/Downloads/credit_scoring_eng.csv')    
credit_scoring_eng.head()

#Number of rows & columns
'Total Rows & Columns:',credit_scoring_eng.shape
# variable types
credit_scoring_eng.info()
credit_scoring_eng.describe()
# count of family_status having 20 children
credit_scoring_eng.loc[credit_scoring_eng["children"] == 20, "family_status"].value_counts()
#count of family_status having 0 children
credit_scoring_eng.loc[credit_scoring_eng["children"] == 0, "family_status"].value_counts()
#Convert negative to positive
credit_scoring_eng['days_employed'] = abs(credit_scoring_eng['days_employed'])
credit_scoring_eng['children'] = abs(credit_scoring_eng['children'])
credit_scoring_eng['total_income'] = abs(credit_scoring_eng['total_income'])
credit_scoring_eng.describe(include=np.object)
#count of gender column
credit_scoring_eng['gender'].value_counts()
#Detecting problem entries
problem_entries = []

for row in credit_scoring_eng['gender']:
    if (row != 'F') & (row != 'M'):
        problem_entries.append(row)
print(problem_entries)<jupyter_output>['XNA']<jupyter_text>ConclusionThere are total 21525 rows and 12 columns. Each columns consist of: int64,float64 & object datatype.Descriptive statistics: shows "number of children in a family" max count is 20 & min count is -1 which is outlier. Family_status: Consist of 5 categories among them 57% is under married categories.Income_type: Consist of 8 categories among them 51% is under emplyeed categories.Also we get to know that gender column had 'XNA' value apart from male and female.In 'credit_scoring_eng' dataset. How long the customers has been working & total income column both are in negative. That is not possible as number of days people working and their income cannot be in negative.So converted into positive. As negative numbers will impact in arithmatic calcultion.  Step 2. Data preprocessing  Preprocessing Missing Value<jupyter_code># col 'days_employed' & total_income round the decimal place to 2
credit_scoring_eng['days_employed'] = round(credit_scoring_eng['days_employed'],2)
credit_scoring_eng['total_income'] = round(credit_scoring_eng['total_income'],2)

#number of missing values per column
credit_scoring_eng.isna().sum()
#creating a copy of orignal data
credit_scoring_eng_copy = credit_scoring_eng.copy()
#group data by gender and print mean & median of num_colums
num_columns = ['days_employed','total_income']
print('These are the means:')
print(credit_scoring_eng_copy.groupby('gender')[num_columns].mean())
print()
print('These are the medians:')
print(credit_scoring_eng_copy.groupby('gender')[num_columns].median())
#group data & see the count of missing values in the two column
print('Count of missing days_employed by Gender')
print(credit_scoring_eng_copy[credit_scoring_eng_copy['days_employed'].isnull()]['gender'].value_counts())

print('Count of missing total_income by Gender')
print(credit_scoring_eng_copy[credit_scoring_eng_copy['total_income'].isnull()]['gender'].value_counts())
#calculate & fill missing values for days_employed & total_income with mean & meadian
# Calculate values
Mediandaysemployed = credit_scoring_eng_copy['days_employed'].median()
Meantotalincome = credit_scoring_eng_copy['total_income'].mean()
Meandobyears = credit_scoring_eng_copy['dob_years'].mean()

credit_scoring_eng_copy.fillna(value= {'days_employed':Mediandaysemployed,'total_income':Meantotalincome,'dob_years':Meandobyears }, inplace = True)
credit_scoring_eng_copy.head(2)
# number of children in a family 20 replacing it with 2
credit_scoring_eng_copy.loc[credit_scoring_eng_copy['children'] == 20] = 2
#Check if there is any missing value
credit_scoring_eng_copy.isnull().sum()<jupyter_output><empty_output><jupyter_text>Conclusion"days_employed" & "total_income" column was float type so round the decimal place to 2.Two Column has missing value i.e. 'days_imployed' & 'total_income'.  10% of row in "total_income" column has missing value and that is replaced by mean. In case of days_employed also have 10% of row missing and that is replaced by median.'dob_years' had age 0 which is not possible and so this was replaced by mean age.'no of children in family is' 20 & -1 which is also not possible, so converted -1 to 1 & 20 to 2Reason for 'children' column: this can be typo error (instead of 1 it was -1 & instead of 2 it was 20) that could be the possible reason.Reason for 'days_imployed', 'total_income' & 'dob_years' column: the vaues were missing because peple sometimes forget to mention or don't want to specify thier income, age & no of days employed. The means and medians are roughly equal for total_income & dob_years that means there are no outliers in this two columns so filled the missing value by mean.For days_employed the mean is higher than the median that implies that it has outliers in this column so filled the missing vaue by median.  Data type replacement<jupyter_code># checking the datatype
credit_scoring_eng_copy.info()
#transform from float to int type
credit_scoring_eng_copy['total_income']=credit_scoring_eng_copy['total_income'].astype('int64')
credit_scoring_eng_copy['days_employed'] = credit_scoring_eng_copy['days_employed'].astype('int64')

credit_scoring_eng_copy.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 21525 entries, 0 to 21524
Data columns (total 12 columns):
 #   Column            Non-Null Count  Dtype 
---  ------            --------------  ----- 
 0   children          21525 non-null  int64 
 1   days_employed     21525 non-null  int64 
 2   dob_years         21525 non-null  int64 
 3   education         21525 non-null  object
 4   education_id      21525 non-null  int64 
 5   family_status     21525 non-null  object
 6   family_status_id  21525 non-null  int64 
 7   gender            21525 non-null  object
 8   income_type       21525 non-null  object
 9   debt              21525 non-null  int64 
 10  total_income      21525 non-null  int64 
 11  purpose           21525 non-null  object
dtypes: int64(7), object(5)
memory usage: 2.0+ MB<jupyter_text>ConclusionConverted  'total_income' & 'days_employed' to integer type. These columns were converted because float-point could be much slower than integer arithmetic.   Processing duplicates<jupyter_code>#No of duplicate entries in dataset
'Duplicate entries in row:', credit_scoring_eng_copy.duplicated().sum()
credit_scoring_removed_duplicated = credit_scoring_eng.drop_duplicates()
len(credit_scoring_removed_duplicated)
# drop XNA from gender column
credit_scoring_removed_duplicated = credit_scoring_removed_duplicated[(credit_scoring_removed_duplicated['gender'] == 'M') | (credit_scoring_removed_duplicated['gender'] == 'F')]
credit_scoring_removed_duplicated['gender'].value_counts()<jupyter_output><empty_output><jupyter_text>ConclusionThere were 129 rows duplicated. That means 0.59% of a variable is duplicated, delete the variable entirely.Used drop_duplicates methods to repmove the duplicate as this method will return a new dataframe without the duplicates and then saved this into a new dataframe called 'credit_scoring_removed_duplicated'.Possible reason for the appearence of duplicate data: Effective matching customers require multiple data points but if records does't have enough data points to match multiple customers may be associated with same records.Dropped the row contaning 'XNA' in gender column.  Categorizing Data<jupyter_code>#Returns the category of income_level for the total_income column
def income_level(total_income):
    if (total_income > credit_scoring_removed_duplicated['total_income'].quantile(0.25)) & (total_income <= credit_scoring_eng['total_income'].quantile(0.50)):
        return 'medium'
    if total_income <= credit_scoring_removed_duplicated['total_income'].quantile(0.25):
        return 'low'
    if total_income <= credit_scoring_removed_duplicated['total_income'].quantile(0.75):
        return 'high'
    return 'very_high'

credit_scoring_removed_duplicated['income_level'] = credit_scoring_removed_duplicated['total_income'].apply(income_level)
credit_scoring_removed_duplicated.head()
#Returns purpose_category for the purpose column
purpose= credit_scoring_removed_duplicated['purpose']


def get_purpose_category(purpose):
    if 'wedding' in purpose:
        return 'wedding'
    if 'house' in purpose:
        return 'house'
    if 'real estate' in purpose:
        return 'real estate'
    if 'property' in purpose:
        return 'property'
    if 'car' in purpose:
        return 'car'
    if 'cars' in purpose:
        return 'car'
    if 'education' in purpose:
        return 'education'
    if 'university' in purpose:
        return 'education'
    if 'educated' in purpose:
        return 'education'
    if 'housing' in purpose:
        return 'house'
    
    #return 'default'
    
credit_scoring_removed_duplicated['purpose_category'] = credit_scoring_removed_duplicated['purpose'].apply(get_purpose_category)
credit_scoring_removed_duplicated.head()
# mean of total_income categoried as per family_status & children
data_pivot = credit_scoring_removed_duplicated.pivot_table(index=['family_status','children'],columns='debt', values='total_income', aggfunc='mean')
data_pivot_reset_index = data_pivot.reset_index()
data_pivot_reset_index.head()
# Convert the pivot table to dataframe for calculating avg_income_per_person
data_pivot_dataframe = pd.DataFrame(data_pivot_reset_index)

data_pivot_dataframe.loc[(data_pivot_dataframe['family_status'] == 'civil partnership') | (data_pivot_dataframe['family_status'] == 'married'),'avg_income_per_person'] = data_pivot_dataframe[1]/(data_pivot_dataframe['children'] +2)
data_pivot_dataframe.loc[(data_pivot_dataframe['family_status'] == 'divorced') | (data_pivot_dataframe['family_status'] == 'widow / widower')|(data_pivot_dataframe['family_status'] == 'unmarried'),'avg_income_per_person'] = data_pivot_dataframe[1]/(data_pivot_dataframe['children'] +1)
data_pivot_dataframe.head()<jupyter_output><empty_output><jupyter_text>ConclusionCategorised 'tota_income' column into low, medium , high & ver high income group.This was done on the basis of median and saved this into another column named 'income_level'.Categorised 'purpose' column into different level of categories based on similar words from the purpose column.This data was saved into another column named 'purpose_category'.By categorising the data created two columns named: 'income_level' & 'purpose_category'.This was help us in futher analysis based on catagory.Using pivot table: Aggregated the data & summarised it by grouping the 'family_status' with thier 'children'.'debt' provides an dditional way to segment the actual value we care. The column 'total_income' we see the average total income group wise.From pivot table calculating average income per person. Using this average income per person will check the relationship if that particular category and repaying a loan has some relationship or not.  Step 3. Answer these questions  Is there a relation between having kids and repaying a loan on time?<jupyter_code>credit_scoring_removed_duplicated['children'].value_counts()
data_pivot_children = credit_scoring_removed_duplicated.pivot_table(index=['children','family_status'],columns='debt', values='total_income', aggfunc='mean')
data_pivot_with_reset_index_children = data_pivot_children.reset_index()
data_pivot_with_reset_index_children.tail(5)
data_pivot_dataframe_per_children= data_pivot_dataframe.pivot_table(index= ['children'] ,values ='avg_income_per_person', aggfunc='mean')
data_pivot_dataframe_per_children['avg_income_per_person'].sort_values(ascending= False)
def children_impact(children):
    if children <= data_pivot_dataframe_per_children['avg_income_per_person'].median():
        return 'high_risk'
    else:
        return 'low_risk'
    
data_pivot_dataframe_per_children['children_impact'] = data_pivot_dataframe_per_children['avg_income_per_person'].apply(children_impact) 
data_pivot_dataframe_per_children<jupyter_output><empty_output><jupyter_text>ConclusionNumber of Children family has is: 0,1,2,3,4From pivot table we can see that on the basis children & their family_status for both when person is under debt & no debt what is the average total income.As we are looking for repayment of loan on time: So we will consider person under debt that means where debt==1 & what is the average income per person categorised by family_status & children.Further when we see on the basis of number of children family has and average income per person. Categorised the impact children can have based on average income per person by taking the median and classified under high & low risk.From the above table we can say based: Greater the average income per person with less number of children there is higher chances of repayment of loan on time.As we can see family having more than 2 children their aberage imcome per person decreases and hence, repayment of loan timely is under high risk. Thus, we can conclude that yes there is relationship between having kids and repayment of loan on time.  Is there a relation between marital status and repaying a loan on time?<jupyter_code>credit_scoring_removed_duplicated['family_status'].value_counts()
data_pivot_family_status = credit_scoring_removed_duplicated.pivot_table(index=['family_status','children'],columns='debt', values='total_income', aggfunc='mean')
data_pivot_with_reset_index_family_status = data_pivot_family_status.reset_index()
data_pivot_dataframe_per_family_status= data_pivot_dataframe.pivot_table(index= ['family_status'] ,values ='avg_income_per_person', aggfunc='mean')
data_pivot_dataframe_per_family_status['avg_income_per_person'].sort_values(ascending= False)
def family_status_impact(family_status):
    if family_status < data_pivot_dataframe_per_family_status['avg_income_per_person'].median():
        return 'high_risk'
    else:
        return 'low_risk'
    
data_pivot_dataframe_per_family_status['family_status_impact'] = data_pivot_dataframe_per_family_status['avg_income_per_person'].apply(family_status_impact) 
data_pivot_dataframe_per_family_status<jupyter_output><empty_output><jupyter_text>ConclusionFamily Status is divided into 4 categories: married,civil partnership,unmarried,divorced,widow / widowerFrom the above table we can say: Divorced, unmarried and widow there is higher chances of repayment of loan on time.As we can see family status for civil partner, married imcome per person is lower and hence, repayment of loan timely is under high risk. Thus, we can conclude that there is relationship between family_status and repayment of loan on time.  Is there a relation between income level and repaying a loan on time?<jupyter_code>credit_scoring_removed_duplicated['income_level'].value_counts()
data_pivot_income_level = credit_scoring_removed_duplicated.pivot_table(index=['income_level','family_status','children'],columns='debt', values='total_income', aggfunc='mean')
data_pivot_reset_index_income_level = data_pivot_income_level.reset_index()
data_pivot_reset_index_income_level.tail(5)
data_pivot_dataframe_income_level = pd.DataFrame(data_pivot_reset_index_income_level)

data_pivot_dataframe_income_level.loc[(data_pivot_dataframe_income_level['family_status'] == 'civil partnership') | (data_pivot_dataframe_income_level['family_status'] == 'married'),'avg_income_per_person'] = data_pivot_dataframe_income_level[1]/(data_pivot_dataframe_income_level['children'] +2)
data_pivot_dataframe_income_level.loc[(data_pivot_dataframe_income_level['family_status'] == 'divorced') | (data_pivot_dataframe_income_level['family_status'] == 'widow / widower')|(data_pivot_dataframe_income_level['family_status'] == 'unmarried'),'avg_income_per_person'] = data_pivot_dataframe_income_level[1]/(data_pivot_dataframe_income_level['children'] +1)

data_pivot_dataframe_per_income_level= data_pivot_dataframe_income_level.pivot_table(index= ['income_level'] ,values ='avg_income_per_person', aggfunc='mean')
data_pivot_dataframe_per_income_level['avg_income_per_person'].sort_values(ascending= False)
def income_level_impact(income_level):
    if income_level <= data_pivot_dataframe_per_income_level['avg_income_per_person'].median():
        return 'high_risk'
    else:
        return 'low_risk'
    
data_pivot_dataframe_per_income_level['income_level_impact'] = data_pivot_dataframe_per_income_level['avg_income_per_person'].apply(income_level_impact) 
data_pivot_dataframe_per_income_level<jupyter_output><empty_output><jupyter_text>ConclusionIncome level is divided into 4 categories: low,meduim ,high,very highFrom the above table we can say: those who are under high and very high income group there is higher chances of repayment of loan on time.As we can see income level for low and medium imcome group has lower income per person and hence, repayment of loan timely is under high risk. Thus, we can conclude that there is relationship between income_level and repayment of loan on time.  How do different loan purposes affect on-time repayment of the loan?<jupyter_code>credit_scoring_removed_duplicated['purpose_category'].value_counts()
data_pivot_purpose_category = credit_scoring_removed_duplicated.pivot_table(index=['purpose_category','family_status','children'],columns='debt', values='total_income', aggfunc='mean')
data_pivot_reset_index_purpose_category = data_pivot_purpose_category.reset_index()
data_pivot_dataframe_purpose_category = pd.DataFrame(data_pivot_reset_index_purpose_category)


data_pivot_reset_index_purpose_category.loc[(data_pivot_reset_index_purpose_category['family_status'] == 'civil partnership') | (data_pivot_reset_index_purpose_category['family_status'] == 'married'),'avg_income_per_person'] = data_pivot_reset_index_purpose_category[1]/(data_pivot_reset_index_purpose_category['children'] +2)
data_pivot_reset_index_purpose_category.loc[(data_pivot_reset_index_purpose_category['family_status'] == 'divorced') | (data_pivot_reset_index_purpose_category['family_status'] == 'widow / widower')|(data_pivot_reset_index_purpose_category['family_status'] == 'unmarried'),'avg_income_per_person'] = data_pivot_reset_index_purpose_category[1]/(data_pivot_reset_index_purpose_category['children'] +1)

data_pivot_reset_index_purpose_category.head(5)
data_pivot_dataframe_per_purpose_category= data_pivot_dataframe_purpose_category.pivot_table(index= ['purpose_category'] ,values ='avg_income_per_person', aggfunc='mean')
data_pivot_dataframe_per_purpose_category['avg_income_per_person'].sort_values(ascending= False)
def purpose_category_impact(purpose_category):
    if purpose_category <= data_pivot_dataframe_per_family_status['avg_income_per_person'].median():
        return 'high_risk'
    else:
        return 'low_risk'
    
data_pivot_dataframe_per_purpose_category['purpose_category_impact'] = data_pivot_dataframe_per_purpose_category['avg_income_per_person'].apply(purpose_category_impact) 
data_pivot_dataframe_per_purpose_category<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0811357026667324, 'fraction_numerical': 0.010400749248287081, 'mean_word_length': 6.857474825716499, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '241191', 'license': 'MIT', 'n_tokens_mistral': 6396, 'n_tokens_neox': 5926, 'n_words': 1709, 'path': 'credit_scoring_dataset.ipynb', 'repo_name': 'Sinha0810/credit_scoring', 'size': '20287'}