<jupyter_start><jupyter_text>Data Exploration and Cleaning w/ Jupyter Notebook  Packages versions<jupyter_code>import sys
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt

%matplotlib inline

print('The Python version is: {}\n'.format(sys.version))
print('The Numpy version is: {}\n'.format(np.__version__))
print('The Pandas version is: {}\n'.format(pd.__version__))
print('The Matplotlib version is: {}\n'.format(mpl.__version__))<jupyter_output>The Python version is: 3.8.5 (default, Jul 28 2020, 12:59:40) 
[GCC 9.3.0]

The Numpy version is: 1.19.1

The Pandas version is: 1.1.0

The Matplotlib version is: 3.3.3<jupyter_text>Loading data into Jupyter w/ Pandas<jupyter_code>df = pd.read_excel('data/default_of_credit_card_clients__courseware_version_1_21_19.xls')<jupyter_output><empty_output><jupyter_text>Number of rows and columns (rows, columns):<jupyter_code>df.shape<jupyter_output><empty_output><jupyter_text>Getting familiar with the data Examining the column names:<jupyter_code>df.columns<jupyter_output><empty_output><jupyter_text>Formating the column names to lowercase:<jupyter_code>df.columns = [column.lower() for column in df.columns]
df.columns = [column.replace(' ', '_') for column in df.columns]
df.columns<jupyter_output><empty_output><jupyter_text>The metadata of this dataset is as follows:**education**:Education (1 = graduate school; 2 = university; 3 = high scholl; 4 = others)**marriage**:Marital status (1 = married; 2 = single; 3 = others)**age**:Age (in years)**pay_1 to pay_6**:A record of past payments (pay_1 = repayment status September; ...; pay_6 = repayment status April). The payment value means different status (-1 = pay duly; 1 = payment delay for one month; ...; 9 = payment delay for nine months and above).**bill_amt1 to bill_amt6**:Bill statement amount (NT dollar)**pay_amt1 to pay_amt6**:Represents the amount of previous payments (pay_amt1 = amount paid in September; ...; pay_amt6 = amount paid in April)**default payment next month**:Whether or not a customer defaulted the next month payment Observing the first few records:<jupyter_code>df.head()<jupyter_output><empty_output><jupyter_text>Certify that the ids are unique:<jupyter_code>df.id.nunique()<jupyter_output><empty_output><jupyter_text>Verifying the the duplicate ids:<jupyter_code>id_counts = df['id'].value_counts()
id_counts.head()<jupyter_output><empty_output><jupyter_text>Veirifying the number of duplicates:<jupyter_code>id_counts.value_counts()<jupyter_output><empty_output><jupyter_text>Boolean Masks<jupyter_code>dup_mask = id_counts == 2
dup_mask[0:5]<jupyter_output><empty_output><jupyter_text>Extracting the duplication ids:<jupyter_code>id_counts.index[0:5]
dup_ids = id_counts.index[dup_mask]
dup_ids = list(dup_ids)
len(dup_ids)
dup_ids[0:5]<jupyter_output><empty_output><jupyter_text>Displaying df records that have some of these ids:<jupyter_code>df.loc[df.id.isin(dup_ids[0:3]), :].head(10)<jupyter_output><empty_output><jupyter_text>Selecting df records with all zeroes:<jupyter_code>df_zeroes = df == 0
feature_zeroes_mask = df_zeroes.iloc[:, 1:].all(axis=1)
sum(feature_zeroes_mask)<jupyter_output><empty_output><jupyter_text>Droping all zeroes df records:<jupyter_code>df_clean = df.loc[~feature_zeroes_mask, :].copy()
df_clean.shape<jupyter_output><empty_output><jupyter_text>Checking the uniqueness of the ids:<jupyter_code>df_clean.id.nunique() == df_clean.shape[0]<jupyter_output><empty_output><jupyter_text>Column data types<jupyter_code>df_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 29685 entries, 0 to 29999
Data columns (total 25 columns):
 #   Column                      Non-Null Count  Dtype 
---  ------                      --------------  ----- 
 0   id                          29685 non-null  object
 1   limit_bal                   29685 non-null  int64 
 2   sex                         29685 non-null  int64 
 3   education                   29685 non-null  int64 
 4   marriage                    29685 non-null  int64 
 5   age                         29685 non-null  int64 
 6   pay_1                       29685 non-null  object
 7   pay_2                       29685 non-null  int64 
 8   pay_3                       29685 non-null  int64 
 9   pay_4                       29685 non-null  int64 
 10  pay_5                       29685 non-null  int64 
 11  pay_6                       29685 non-null  int64 
 12  bill_amt1                   29685 non-null  int64 
 13  bill_amt2                   29685 non-null  in[...]<jupyter_text>Checking pay_1 column in detail:<jupyter_code>df_clean.pay_1.head()
df_clean.pay_1.value_counts()
valid_pay_1_mask = df_clean.pay_1 != 'Not available'
valid_pay_1_mask[0:5]
sum(valid_pay_1_mask)<jupyter_output><empty_output><jupyter_text>Dropping df records that don't have 'pay_1' information:<jupyter_code>df_clean = df_clean.loc[valid_pay_1_mask, :].copy()
df_clean.shape
df_clean.pay_1.value_counts()<jupyter_output><empty_output><jupyter_text>New values found in **pay_1** column: The value **-2** account started that month with a zero balance, and never used any credit card.The value **0** means that at least the mimimum payment was made, but the entire balance wasn't paid.<jupyter_code>df_clean.pay_1 = df_clean.pay_1.astype('int64')
df_clean.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 26664 entries, 0 to 29999
Data columns (total 25 columns):
 #   Column                      Non-Null Count  Dtype 
---  ------                      --------------  ----- 
 0   id                          26664 non-null  object
 1   limit_bal                   26664 non-null  int64 
 2   sex                         26664 non-null  int64 
 3   education                   26664 non-null  int64 
 4   marriage                    26664 non-null  int64 
 5   age                         26664 non-null  int64 
 6   pay_1                       26664 non-null  int64 
 7   pay_2                       26664 non-null  int64 
 8   pay_3                       26664 non-null  int64 
 9   pay_4                       26664 non-null  int64 
 10  pay_5                       26664 non-null  int64 
 11  pay_6                       26664 non-null  int64 
 12  bill_amt1                   26664 non-null  int64 
 13  bill_amt2                   26664 non-null  in[...]<jupyter_text>Data Quality Assurance and Exploration Let's start looking at the 'limit_bal' and 'age' distributions using histograms:<jupyter_code>df_clean[['limit_bal', 'age']].hist(figsize=(10,4))
df_clean[['limit_bal', 'age']].describe()<jupyter_output><empty_output><jupyter_text>**limit_bal**, the credit limit, seems ok. It has a minimum of 10,000 (NT dollar - Taiwan currency). Being above zero makes sense when analyzing minimum limits.**age** feature also looks reasonably ok, with mimium age of 21 and the maximum age of 79 years old. Let's look ate the 'education' distribution:<jupyter_code>df_clean.education.value_counts()<jupyter_output><empty_output><jupyter_text>New values found in **education** column: The unknown values will be included in the 'others' category (value of **4**)<jupyter_code>df_clean.education.replace(to_replace=[0, 6, 5], value=4, inplace=True)
df_clean.education.value_counts()<jupyter_output><empty_output><jupyter_text>Let's look ate the 'marriage' distribution:<jupyter_code>df_clean.marriage.value_counts()<jupyter_output><empty_output><jupyter_text>New values found in **marriage** column: The unknown values will be included in the 'others' category (value of **3**)<jupyter_code>df_clean.marriage.replace(to_replace=0, value=3, inplace=True)
df_clean.marriage.value_counts()<jupyter_output><empty_output><jupyter_text>Exploring some relationshipsLet's look at the 'education' distribution:<jupyter_code>df_clean.groupby('education').agg({'default_payment_next_month': 'mean'}).plot.bar(legend=False)
plt.ylabel('Default rate')
plt.xlabel('Education level: ordinal encoding')
plt.xticks(rotation=0)<jupyter_output><empty_output><jupyter_text>Let's 'reverse engineer' the education categorical values to one hot enconding (OHE):<jupyter_code>cat_map = {
    1: 'graduate_school',
    2: 'university',
    3: 'high_school',
    4: 'others'
}

df_clean['education_cat'] = 'none'
df_clean['education_cat'] = df_clean.education.map(cat_map)
df_clean[['education', 'education_cat']].head(10)
edu_ohe = pd.get_dummies(df_clean.education_cat)
edu_ohe.head(10)
df_with_ohe = pd.concat([df_clean, edu_ohe], axis=1)
df_with_ohe[['education_cat', 'graduate_school', 'high_school', 'university', 'others']].head(10)<jupyter_output><empty_output><jupyter_text>Saving the resultant dataframe for posterior analyses:<jupyter_code>df_with_ohe.to_csv('data/default_of_credit_card_clients_cleaned.csv', index=False)<jupyter_output><empty_output><jupyter_text>Further dataset Analysis - Exploring the Financial FeaturesLet's read the previous saved csv file:<jupyter_code>df = pd.read_csv('data/default_of_credit_card_clients_cleaned.csv')
df.head()
df.columns
df.shape<jupyter_output><empty_output><jupyter_text>Let's look only at the financial features:<jupyter_code>pay_feats = ['pay_{}'.format(i+1) for i in range(6)]

df[pay_feats].describe()
df.pay_1.value_counts().sort_index()<jupyter_output><empty_output><jupyter_text>As the number of finacial history columns is reasonable low (18 in total) it is possible to look at every one. Let's start with 'pay_1' feature:<jupyter_code>df.pay_1.hist()
plt.ylabel('Number of accounts')
plt.xlabel('pay_1')<jupyter_output><empty_output><jupyter_text>Setting the bin argument manually to better visualize the data:<jupyter_code>pay_1_bins = np.array(range(-2, 10)) - 0.5

df.pay_1.hist(bins=pay_1_bins)
plt.ylabel('Number of accounts')
plt.xlabel('pay_1')<jupyter_output><empty_output><jupyter_text>Let's plot multiple histograms together:<jupyter_code>df[pay_feats].hist(bins=pay_1_bins, figsize=(12, 8), layout=(2, 3))<jupyter_output><empty_output><jupyter_text>Some strange behaviour can be seen in the plots above. Considering 'pay_3' (July) and 'pay_2' (August), we can see that in July there are very few accounts that had a 1-month payment delay and in August there are almost the same number of accounts with a 2-month payment delay as was in July. This is strange because the expected behaviour is that the number of accounts with a 2-month delay in a given month should be less or equal to the number of accounts with a 1-month delay in the previous month.Let's look in detail to these records:<jupyter_code>df.loc[(df.pay_2 == 2), ['pay_2', 'pay_3']].head()<jupyter_output><empty_output><jupyter_text>Yes, the data is faulty.Let's look at the bill and payment amounts now:<jupyter_code>bill_feats = ['bill_amt{}'.format(i+1) for i in range(6)]

df[bill_feats].describe()<jupyter_output><empty_output><jupyter_text>The average monthly bill amount is in the range of 40,000 to 50,000 NT dollars. It is also possible to see some negative values of bill amount and that is reasonable due to overpayment of previous months or other factors.<jupyter_code>df[bill_feats].hist(bins=20, figsize=(12, 8), layout=(2, 3), xrot=30)<jupyter_output><empty_output><jupyter_text>The histogram plots make sense in several aspects too. Most of the accounts have relatively small bills and as the amount increases, the account number steadily decreases. The distribution of payments is similar month-by-month so any data inconsistency is noticed between them.<jupyter_code>pay_amt_feats = ['pay_amt{}'.format(i+1) for i in range(6)]

df[pay_amt_feats].describe()<jupyter_output><empty_output><jupyter_text>The average payment is an order of magnitude (power of 10) lower than the average bill amounts. This supports the data that most of the payments have status 0 (that they honored the credit payment). There are no negative payment values and this is good.<jupyter_code>df[pay_amt_feats].hist(bins=40, figsize=(12, 8), layout=(2,3), xrot=30)<jupyter_output><empty_output><jupyter_text>Looking at the plots above it is hard to grasp any information. Only that the bin that include 0 is the most frequent value. Let's see how many values are zero indeed:<jupyter_code>pay_zero_mask = df[pay_amt_feats] == 0
pay_zero_mask.sum()<jupyter_output><empty_output><jupyter_text>Only a portion of accounts have bill payments equal to 0 in any given month - roughly around 20-25%. So the histrogram plots are in some way misleading information. This happens because the way bins are grouped together.Let's ignore the payments with zero values and only consider the other values:<jupyter_code>df[pay_amt_feats][~pay_zero_mask].apply(np.log10).hist(figsize=(12,8), layout=(2,3))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10587957868259708, 'fraction_numerical': 0.036865272755856, 'mean_word_length': 4.5149544863459035, 'pattern_counts': {'":': 0, '<': 156, '<?xml version=': 0, '>': 156, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '39', 'dir': 'jupyter-structured-clean-dedup', 'id': '597954', 'license': 'MIT', 'n_tokens_mistral': 4564, 'n_tokens_neox': 4161, 'n_words': 1109, 'path': 'loading-and-examining-data.ipynb', 'repo_name': 'jeantardelli/data-science', 'size': '12722'}