<jupyter_start><jupyter_text>Give me some Credit  Use CRISP-DM to answer some issues related to GiveMe some credit Kaggle competition  Environment Setup<jupyter_code>import numpy as np               # Array, Vector, matrix calculations
import pandas as pd              # DataFrame handling
import matplotlib.pyplot as plt  # Plotting
pd.options.display.max_columns = 999

%matploblib inline
np.random.seed(42)               # Answer to everything<jupyter_output>UsageError: Line magic function `%matploblib` not found.<jupyter_text>1. Download, Explore and prepare Kaggle competition dataset Give me some credit dataset:    https://www.kaggle.com/c/GiveMeSomeCredit/data    The Give Me Some credit dataset contains 12 variables related to the financial behaviour and position of customers, these are:- **SeriousDlqin2yrs:** Person Experienced 90 days past due delinquency or worse- **RevolvingUtilizationOfUnsecuredLines:** Total balance on credit cards and personal lines of credit, divided by the sum of credit limits- **Age:** Age borrower in years- **NumberOfTime30-59DaysPastDueNotWorse:** Number of times the borrower has been 30-59 days past due but not worse in the last 2 years- **DebtRatio:** Monthly debt payments, alimony, living costs divided by monthly gross income- **MonthlyIncome:** Monthly Income of customer- **NumberOfOpenCreditLinesAndLoans:** Number of Open oans (installments like car loan or mortgage) and Lines of Credit- **NumberOfTimes90DaysLate:** Number of times borrower has been 90 days or more past due.- **NumberRealEstateLoansOrLines:** Number of mortgage and real estate loans including home equity lines of credit- **NumberOfTime60-89DaysPastDueNotWorse:** Number of times borrower has been 60-89 days past due but not worse in the last 2 years- **NumberOfDependents:** Number of dependents in family excluding themselves.These 10 input variables are used to predict the target variable wheter or not the customer will experience any Serious Delinquency event in the next two years. Since we will perform our analysis on a wide range of models, all of these variables will be handled and converted to numeric type.  Import Data and clean The credit card default data is available as an .xls file. Pandas reads .xls files automatically.<jupyter_code># Import file
path = 'D:/Python/Projects/02_GSC/cs-training.csv'
data = pd.read_csv(path,
                  usecols = ['SeriousDlqin2yrs',
                             'RevolvingUtilizationOfUnsecuredLines',
                             'age',
                             'NumberOfTime30-59DaysPastDueNotWorse',
                             'DebtRatio',
                             'MonthlyIncome',
                             'NumberOfOpenCreditLinesAndLoans',
                             'NumberOfTimes90DaysLate',
                             'NumberRealEstateLoansOrLines',
                             'NumberOfTime60-89DaysPastDueNotWorse',
                             'NumberOfDependents'])

data.dtypes<jupyter_output><empty_output><jupyter_text>EDAExploration of a dataset is paramount to a correct framing of our problem. Out objective is to predict an event of serious delinquency in the next 2 years based on 10 variables. In order to really understand our data and the vastness of the problem at hand we will focus on the next steps:* Dimensions of the dataset* Descriptive stats + Probability distribution plots* Nan Detection* Outlier Detection + Box plots* Correlation Matrix* Model Constraints from data related problems* Possible candidates of featuresLets start with the Dimensions of our training matrix<jupyter_code>data.shape<jupyter_output><empty_output><jupyter_text>Thus we have 150.000 records to train our model, and 11 features to predict our target feature  SeriousDlqin2yrs  • Descriptive Stats<jupyter_code>data.describe()
data.columns<jupyter_output><empty_output><jupyter_text>• Probability Distribution plots + Bins  Target: SeriousDlqin2yrsAs you can probably tell, this dataset feature some imbalance on the response target, (Kudos to the risk evaluation team of the dataset). Delinquency events in the last 2 years have been around 6.7%<jupyter_code># Target 
data['SeriousDlqin2yrs'].hist()
print(data['SeriousDlqin2yrs'].value_counts())<jupyter_output>0    139974
1     10026
Name: SeriousDlqin2yrs, dtype: int64<jupyter_text>• RevolvingUtilizationOfUnsecuredLines:In order to properly understand the distribution on our features, we will use the elements of a Box-Plot and its bounds. In this case we find almost 38k records between -0.74 and 0.2, negative utilization rates? this does seem a bit strange,  notice as well the concentration of  high rates of Revolving utilization: 135%!<jupyter_code># RevolvingUtilizationOfUnsecuredLines 
## Get quantiles and define Bins
col = 'RevolvingUtilizationOfUnsecuredLines'
def BinSelection(col): 
    q_u = data[col].quantile(0.75)  # Upper Quantile
    q_l = data[col].quantile(0.25)  # Lower Quantile
    m   = data[col].quantile(0.50)  # Median
    iqr = q_u - q_l                 # InterQuartile Range
    olb = q_l - 3 * iqr             # Outer Lower Bound, Anything less is strongly suspected outlier
    ilb = q_l - 1.5 * iqr           # Inner Lower Bound, Anything within bound is softly considered outlier
    iub = q_u + 1.5 * iqr           # Inner Upper Bound
    oub = q_u + 3 * iqr             # Outer Upper Bound
    df = [float('-inf'), olb, ilb, q_l, m, q_u, iub, oub, float('inf')] 
    return(df);
bp_bins = (BinSelection('RevolvingUtilizationOfUnsecuredLines'))
data['RevolvingUtilizationOfUnsecuredLines_binned']=pd.cut(x = data['RevolvingUtilizationOfUnsecuredLines'],
                        bins = bp_bins 
                        )
data['RevolvingUtilizationOfUnsecuredLines_binned'].value_counts(sort=False).plot.bar()
print(data['RevolvingUtilizationOfUnsecuredLines_binned'].value_counts())
print(bp_bins)
data.groupby(['RevolvingUtilizationOfUnsecuredLines_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What values worry me in this feature: - Negative numbers on utilization are not feasible- Numbers greater than 1 seem troublesome from a financial point perspective.- High Utilization of revolving products does suggest an increase on the target featureLet us test for numbers greater than 1 since we know the min of this feature is 0 we need not to worry about negative numbers<jupyter_code># Numbers greater than 1 on dataset
test_data = data[data['RevolvingUtilizationOfUnsecuredLines']>1]
col_count = test_data.count()
print(col_count)<jupyter_output>SeriousDlqin2yrs                               3321
RevolvingUtilizationOfUnsecuredLines           3321
age                                            3321
NumberOfTime30-59DaysPastDueNotWorse           3321
DebtRatio                                      3321
MonthlyIncome                                  2773
NumberOfOpenCreditLinesAndLoans                3321
NumberOfTimes90DaysLate                        3321
NumberRealEstateLoansOrLines                   3321
NumberOfTime60-89DaysPastDueNotWorse           3321
NumberOfDependents                             3260
RevolvingUtilizationOfUnsecuredLines_binned    3321
dtype: int64<jupyter_text>Age:This feature seems like a straightforward one, Demographic age in years, we expect lower age to affect risk exposure.<jupyter_code>data['age'].value_counts()<jupyter_output><empty_output><jupyter_text>Although 90+ age seems a given on a developed world, being in a loan portfolio at advanced age does not seem that relevant. Bank policies usually avoid customers with life exposure. let us Bin this variable again and try to understand it  a little bit better.<jupyter_code>bp_bins = (BinSelection('age'))

data['age_binned']=pd.cut(x = data['age'],
                        bins = [0, 18, 20, 41, 52, 63, 80, 93, float('Inf')] 
                        )
data['age_binned'].value_counts(sort=False).plot.bar()
print(data['age_binned'].value_counts())
print(bp_bins)
data.groupby(['age_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature?:- People Below legal age- 90+ population- Concentration of target events on first bin, suggests decrease in bin increases.<jupyter_code># Non Legal Age customers on dataset
test_data = data[data['age'] < 18]
col_count = test_data.count()
print(col_count)
# Non Legal Age customers on dataset
test_data = data[data['age'] > 90]
col_count = test_data.count()
print(col_count)<jupyter_output>SeriousDlqin2yrs                               489
RevolvingUtilizationOfUnsecuredLines           489
age                                            489
NumberOfTime30-59DaysPastDueNotWorse           489
DebtRatio                                      489
MonthlyIncome                                  255
NumberOfOpenCreditLinesAndLoans                489
NumberOfTimes90DaysLate                        489
NumberRealEstateLoansOrLines                   489
NumberOfTime60-89DaysPastDueNotWorse           489
NumberOfDependents                             399
RevolvingUtilizationOfUnsecuredLines_binned    489
age_binned                                     489
dtype: int64<jupyter_text>NumberOfTime30-59DaysPastDueNotWorse: Number of times borrower has been 30-59 days past due but no worse in the last 2 years.Interesting feature, to be used in conjuction with other 2 features  - Theoretical min:  0 - Theoretical max: 24<jupyter_code>data['NumberOfTime30-59DaysPastDueNotWorse'].value_counts()<jupyter_output><empty_output><jupyter_text>98 - 96 seems a bit excessive and not a part of our theoretical range values. Lets explore further this variable.<jupyter_code>bins = [0, 0.99, 1, 2, 4, 6, 10, 13, float('Inf')]
data['NumberOfTime30-59DaysPastDueNotWorse_binned']= pd.cut(x = data['NumberOfTime30-59DaysPastDueNotWorse'],
                                                            bins = bins)

data['NumberOfTime30-59DaysPastDueNotWorse_binned'].value_counts(sort=False).plot.bar()
print(data['NumberOfTime30-59DaysPastDueNotWorse_binned'].value_counts())
print(bins)<jupyter_output>(0.99, 1.0]     16033
(1.0, 2.0]       4598
(2.0, 4.0]       2501
(4.0, 6.0]        482
(13.0, inf]       269
(6.0, 10.0]        95
(10.0, 13.0]        4
(0.0, 0.99]         0
Name: NumberOfTime30-59DaysPastDueNotWorse_binned, dtype: int64
[0, 0.99, 1, 2, 4, 6, 10, 13, inf]<jupyter_text>We would like to see wether there can be any sort of relation among our bins and the target feature<jupyter_code>data.groupby(['NumberOfTime30-59DaysPastDueNotWorse_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature?- Extremely unbalanced, 130k+ records display a value of 0 (Again, kudos to the risk evaluation team)- It does seem to suggest that the proportion of events on each bin increase is related to an increase in Delinquency events among the population.- 96, 98 seem to be some sort of code. We need to create new features for these values.  Debt RatioMonthly debt payments, alimony,living costs divided by monthy gross income. Potential pitfalls:- Non accurate income measure- Possible missing income measure (inf ratios)- Debt Payments are mixed with living costs ( Non dynamic feature)Theoretical ratio 0 - 2(?) lets check the portfolio distribution.<jupyter_code>data['DebtRatio'].describe()<jupyter_output><empty_output><jupyter_text>max of 339k? that's just denominator problems. Let us Find the bins for this variable<jupyter_code>bins = BinSelection('DebtRatio')
# [float('-inf'), 0, 0.36, 0.86, 1, 2, 3, float('inf')])

data['DebtRatio_binned']= pd.cut(x = data['DebtRatio'],
                          bins = bins)

data['DebtRatio_binned'].value_counts(sort=False).plot.bar()
print(data['DebtRatio_binned'].value_counts())
print(bins)
data.groupby(['DebtRatio_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature? - Non informative feature, proportion of target does not seem to relate on a linear fashion among bins- Construction problems. By dividing by an unreliable feature we have really high debt to income ratios that are driven mainly by Nan- Feature treatment: bin, normalize, separate non informative by nulls  MonthlyIncome       Monthly income, no currency info, assumed USD. - No time period, Inflation should not play a role on 2y and US data.- Income unless given and collected from employer it's a guess. Even then, dynamic nature of income brings a lot of variance to the data. - Assumed credit origination income to be maintained across credit lifecycle?Potential pitfalls: - Tons of nulls- Traditional unreliable data, be careful.<jupyter_code>data['MonthlyIncome'].describe().apply("{:,.2f}".format)<jupyter_output><empty_output><jupyter_text>Well that avg portfolio income does bring to tears all international students. But Let us focus on the task at hand.We have:- Around 30k Na's- Some possible format problems? 3m monthly usd seems excessive. (Maybe Corporate Loans?)Let's find the bins of this variable<jupyter_code>bp_bins = (BinSelection('MonthlyIncome'))
bp_bins
data['MonthlyIncome_binned']=pd.cut(x = data['MonthlyIncome'],
                        bins = [float('-Inf'), 0, 3400, 5400, 8000, 10000, 15000, 20000, 23000, float('Inf')] 
                        )
data['MonthlyIncome_binned'].value_counts(sort=False, dropna=False).plot.bar()
print(data['MonthlyIncome_binned'].value_counts(dropna= False))
print(bp_bins)
data.groupby(['MonthlyIncome_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature:- Absurd amount of nulls.- Suspected outliers around 15k+ - Strong suspect of Outliers at 22k+- Feature treatment:    - Null treatment    - Stze    - Relate Nan values of debt ratio to median. in case of null income.      NumberOfOpenCreditLinesAndLoansNumber of Open loans (installment like car loan or mortgage) and Lines of credit (e.g. credit cards).- Proxy for income(?)Potential pitfalls:- Distorted by including Lines of credit (2-3 avg)- Non Linear nature with target (?)Let us delve into this feature.<jupyter_code>data['NumberOfOpenCreditLinesAndLoans'].describe()<jupyter_output><empty_output><jupyter_text>Well even among industry standards this seems to involve also savings/checking accounts. A bit high to what was expected. Let us find the bins of this variable.<jupyter_code>bp_bins = (BinSelection('NumberOfOpenCreditLinesAndLoans'))
bp_bins
data['NumberOfOpenCreditLinesAndLoans_binned']=pd.cut(x = data['NumberOfOpenCreditLinesAndLoans'],
                        bins = [float('-inf'), 0, 5, 8, 11, 20, 29, float('inf')] 
                        )
data['NumberOfOpenCreditLinesAndLoans_binned'].value_counts(sort=False, dropna=False).plot.bar()
print(data['NumberOfOpenCreditLinesAndLoans_binned'].value_counts(dropna= False))
print(bp_bins)
data.groupby(['NumberOfOpenCreditLinesAndLoans_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature:- More than 11 open lines? this is just way too much. Is this per household , even then?- Non linear relationship with target. Low Open lines could men less income therefore more delinquency events?- No null events. Possible aggregation across time errors. maybe card renewals?Recommended treatment: - Separate feature space >11. - Treat >29 as outlier- stdize.  NumberOfTimes90DaysLateNumber of times borrower has been 90 days or more past due, this feature is considered a non-performing loan across different portfolio 3 months late payment is considered crossing a line in accounting literature. For House portfolios... maybe not that troublesome.We will observe this feature with great interest.<jupyter_code>data['NumberOfTimes90DaysLate'].describe()<jupyter_output><empty_output><jupyter_text>Again this cheeky 98 trying to pass by, max theoretical of 24times. This reinforces our theory of code-related meaning on the feature, (maybe refinanced status?)<jupyter_code>data['NumberOfTimes90DaysLate'].value_counts(dropna=False)
data['NumberOfTimes90DaysLate_binned']=pd.cut(x = data['NumberOfTimes90DaysLate'],
                        bins = [float('-inf'), 0, 1, 2, 3, 8, 10, float('inf')] 
                        )
data['NumberOfTimes90DaysLate_binned'].value_counts(sort=False, dropna=False).plot.bar()
print(data['NumberOfTimes90DaysLate_binned'].value_counts(dropna= False))
print(bp_bins)
data.groupby(['NumberOfTimes90DaysLate_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature?: - Unbalanced feature- Highly informative > 1.- Leakage? three times of >90 events could be considered serious delinquency events.Treatment:- Encode  NumberRealEstateLoansOrLinesNumber of mortgage and real estate loans including home equity lines of credit.<jupyter_code>data['NumberRealEstateLoansOrLines'].describe()<jupyter_output><empty_output><jupyter_text>Now this **is** some real feature, its range is among what is expected. 1 maybe 2 mortgages. You would expect delinquencies on these to be low. Nonetheless... this was 9 years ago we should be careful at assigning any expected value to our beliefs about this feature.<jupyter_code>data['NumberRealEstateLoansOrLines'].value_counts(dropna= False)
bp_bins = (BinSelection('NumberRealEstateLoansOrLines'))
bp_bins
data['NumberRealEstateLoansOrLines_binned']=pd.cut(x = data['NumberRealEstateLoansOrLines'],
                        bins = bp_bins
                        )
data['NumberRealEstateLoansOrLines_binned'].value_counts(sort=False, dropna=False).plot.bar()
print(data['NumberRealEstateLoansOrLines_binned'].value_counts(dropna= False))
print(bp_bins)
data.groupby(['NumberRealEstateLoansOrLines_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar()<jupyter_output><empty_output><jupyter_text>What worries me about this feature?- That everything seems normal. What am I missing?  NumberOfTime60-89DaysPastDueNotWorseNumber of times borrower has been 60-89 days past due but no worse in the last 2 years.<jupyter_code>data['NumberOfTime60-89DaysPastDueNotWorse'].describe()
data['NumberOfTime60-89DaysPastDueNotWorse'].value_counts(dropna=False)
# Bin Selection
bp_bins = (BinSelection('NumberOfTime60-89DaysPastDueNotWorse'))
bp_bins
data['NumberOfTime60-89DaysPastDueNotWorse_binned']=pd.cut(x = data['NumberOfTime60-89DaysPastDueNotWorse'],
                        bins = [float('-inf'), 0, 1, 2, 3, float('inf')] 
                        )
data['NumberOfTime60-89DaysPastDueNotWorse_binned'].value_counts(sort=False, dropna=False).plot.bar()
print(data['NumberOfTime60-89DaysPastDueNotWorse_binned'].value_counts(dropna= False))
print(bp_bins)
data.groupby(['NumberOfTime60-89DaysPastDueNotWorse_binned',"SeriousDlqin2yrs"]).size().unstack().plot.bar(figsize=(8, 6))<jupyter_output><empty_output><jupyter_text>What worries me about this feature?: - Unbalanced feature- Highly informative > 1.- Leakage? three times of >90 events could be considered serious delinquency events.Treatment:- Encode  Calculate Pearson Correlation<jupyter_code># Displays last column of Pearson correaltion matrix as a Pandas DataFrame
pd.DataFrame(data.corr()['SeriousDlqin2yrs']).iloc[:-1]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08589934714439933, 'fraction_numerical': 0.028633115714799772, 'mean_word_length': 4.162951167728238, 'pattern_counts': {'":': 0, '<': 97, '<?xml version=': 0, '>': 104, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '330317', 'license': 'FTL', 'n_tokens_mistral': 6242, 'n_tokens_neox': 5501, 'n_words': 1764, 'path': 'Credit_EDA.ipynb', 'repo_name': 'MaurovX/Nanodegree-Credit_Default', 'size': '19453'}