<jupyter_start><jupyter_text>Applied Data Science Bootcamp  Module 06 - Data Science in Finance Notebook for the lessons and proposed challenges.  Import Libraries<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats

from sklearn.linear_model import LogisticRegression
from sklearn.ensemble import RandomForestClassifier
from sklearn.dummy import DummyClassifier
from sklearn.model_selection import train_test_split
from sklearn.metrics import roc_auc_score, classification_report, plot_confusion_matrix
from sklearn.preprocessing import OneHotEncoder
from sklearn.preprocessing import MinMaxScaler
from sklearn.base import BaseEstimator, TransformerMixin
from sklearn.pipeline import Pipeline

from imblearn.over_sampling import SMOTE
from imblearn.pipeline import Pipeline

sns.set()<jupyter_output><empty_output><jupyter_text>Lesson 01<jupyter_code>approved_clients_raw = pd.read_csv('https://github.com/diascarolina/data-science-bootcamp/blob/main/data/approved_clients.csv?raw=true')
client_records_raw = pd.read_csv('https://github.com/diascarolina/data-science-bootcamp/blob/main/data/client_record.csv?raw=true')
client_records_raw.head()
client_records_raw.shape
client_records_raw.id.value_counts()<jupyter_output><empty_output><jupyter_text>We can see that we have some repeated IDs.<jupyter_code>client_records_raw.duplicated().sum()<jupyter_output><empty_output><jupyter_text>This means that we don't have whole rows duplicated.<jupyter_code>client_records_raw[client_records_raw.id.duplicated(keep = False)].shape<jupyter_output><empty_output><jupyter_text>So we have 94 problematic values (47 repeated ones). Let's save them.<jupyter_code>repeated_ids = client_records_raw[client_records_raw.id.duplicated(keep = False)].id
client_records = client_records_raw.copy()
client_records = client_records.drop(repeated_ids.index)
client_records.shape
client_records.id.value_counts()<jupyter_output><empty_output><jupyter_text>Now we have only one ID per row. Do we have null data?<jupyter_code>print(f'Number of null values in client_records: {client_records.isnull().sum().sum()}')
client_records.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 438463 entries, 0 to 438556
Data columns (total 18 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   id               438463 non-null  int64  
 1   gender           438463 non-null  object 
 2   own_car          438463 non-null  int64  
 3   own_property     438463 non-null  int64  
 4   children_count   438463 non-null  int64  
 5   annual_income    438463 non-null  float64
 6   income_type      438463 non-null  object 
 7   education_type   438463 non-null  object 
 8   marital_status   438463 non-null  object 
 9   housing_type     438463 non-null  object 
 10  age              438463 non-null  float64
 11  own_cellphone    438463 non-null  int64  
 12  own_workphone    438463 non-null  int64  
 13  own_phone        438463 non-null  int64  
 14  own_email        438463 non-null  int64  
 15  occupation_type  438463 non-null  object 
 16  family_size      438463 non-null  floa[...]<jupyter_text>Lesson 02<jupyter_code>quantitative_variables = ['age', 'children_count', 'annual_income', 'years_working', 'family_size']
client_records[quantitative_variables].describe()<jupyter_output><empty_output><jupyter_text>Taking a look at the table above, how can someone have ```years_working``` equal to -1000.7?<jupyter_code>client_records.query("years_working <= 0")[['years_working', 'income_type']].value_counts()<jupyter_output><empty_output><jupyter_text>We see that all ```years_working == -1000.7``` are pensioners.<jupyter_code>client_records.query("income_type == 'pensioner'")['income_type'].value_counts()<jupyter_output><empty_output><jupyter_text>Almost all pensioners have ```years_working == -1000.7```.<jupyter_code>client_records['years_working'] = client_records['years_working'].replace(-1000.7, -1)
client_records[quantitative_variables].describe()
sns.histplot(data = client_records, x = 'annual_income', bins = 10)
plt.show()<jupyter_output><empty_output><jupyter_text>Let's remove tha outliers.<jupyter_code>annual_income_column = client_records['annual_income']
avg_annual_income = annual_income_column.mean()
std_annual_income = annual_income_column.std()

upper_limit = avg_annual_income + (2 * std_annual_income)
lower_limit = avg_annual_income - (2 * std_annual_income)

outlier_index = []
for index, value in annual_income_column.items():
    if value > upper_limit or value < lower_limit:
        outlier_index.append(index)

print(f'We have {len(outlier_index)} outliers in the annual income column.')
client_records = client_records.drop(outlier_index)
client_records.shape
client_records[quantitative_variables].describe()
sns.histplot(data = client_records, x = 'annual_income', bins = 10)
plt.xticks(rotation = 20)
plt.show()
sns.histplot(data = client_records, x = 'age', bins = 10)
plt.xticks(rotation = 20)
plt.show()
sns.histplot(data = client_records.query("years_working > 0"), x = 'years_working')
plt.xticks(rotation = 20)
plt.show()
client_records.info()
categorical_variables = ['gender', 'own_car', 'own_property', 'income_type',
                         'education_type', 'marital_status', 'housing_type',
                         'own_cellphone', 'own_workphone', 'own_phone', 'own_email',
                         'occupation_type']
sns.countplot(data = client_records, x = 'marital_status')
plt.xticks(rotation = 45, ha = 'right')
plt.show()
sns.countplot(data = client_records, x = 'own_cellphone')
plt.xticks(ha = 'right')
plt.show()
sns.countplot(data = client_records, x = 'occupation_type')
plt.xticks(rotation = 45, ha = 'right')
plt.show()
sns.histplot(data = client_records.query("occupation_type == 'others'"), x = 'annual_income', bins = 10)
plt.xticks(rotation = 45)
plt.show()<jupyter_output><empty_output><jupyter_text>We have seen that the variable ``own_cellphone``` has only one alue, so it is not relevant for our future machine learning model.Also, we will drop the gender variable in order to avoid a bias.<jupyter_code>client_records_treatment1 = client_records.drop(['gender', 'own_cellphone'], axis = 1)
client_records_treatment1.shape<jupyter_output><empty_output><jupyter_text>Let's update our categorical variables.<jupyter_code>categorical_variables.remove('gender')
categorical_variables.remove('own_cellphone')<jupyter_output><empty_output><jupyter_text>Lesson 03 Let's now take a look at the approved clients.<jupyter_code>approved_clients = approved_clients_raw.copy()
approved_clients.head()
approved_clients.info()
approved_clients.id.value_counts().shape
client_records_treatment1.shape
# example of a client
approved_clients.query("id == 5001712")
approved_clients.query("id == 5001711")
approved_clients.status.value_counts().index.to_list()<jupyter_output><empty_output><jupyter_text>Creating Auxiliary Variables<jupyter_code># month in which the client's account was opened
approved_clients_by_id = approved_clients.copy()
approved_clients_by_id = approved_clients_by_id.groupby('id')
opening_series = approved_clients_by_id.apply(lambda x: min(x['month']))
opening_series.name = 'opening'
opening_series
approved_clients = approved_clients.merge(opening_series, on = 'id')
approved_clients.head()
last_registered_series = approved_clients_by_id.apply(lambda x: max(x['month']))
last_registered_series.name = 'final'
last_registered_series
approved_clients = approved_clients.merge(last_registered_series, on = 'id')
approved_clients.head()
# window of number of opening months
approved_clients['window'] = approved_clients['final'] - approved_clients['opening']
approved_clients.head()
# month on book (mob)
approved_clients['mob'] = approved_clients['month'] - approved_clients['opening']
approved_clients.head()
approved_clients['status'].unique()
status_dict = {
    'no_loan': 0,
    'paid_loan': 1,
    '1-29days': 2,
    '30-59days': 3,
    '60-89days': 4,
    '90-119days': 5,
    '120-149days': 6,
    '>150days': 7
}

approved_clients['status_int'] = approved_clients['status'].map(status_dict)
approved_clients.head()
# approved_clients['bad'] = approved_clients.apply(lambda x: x['status_int'] > 3, axis = 1)
approved_clients['bad'] = approved_clients.apply(lambda x: 1 if x['status_int'] > 3 else 0, axis = 1)
approved_clients<jupyter_output><empty_output><jupyter_text>Vintage Analysis<jupyter_code>approved_clients_unique_id = approved_clients[['id', 'opening', 'final', 'window']].groupby('id').apply(lambda x: x.iloc[0]).reset_index(drop = True)
approved_clients_unique_id
approved_clients_denominator = approved_clients_unique_id.groupby('opening').apply(lambda x: x['id'].count()).reset_index()
approved_clients_denominator.columns = ['opening', 'number_of_clients']
approved_clients_denominator
vintage = approved_clients.groupby(['opening', 'mob']).apply(lambda x: x['id'].count()).reset_index()
vintage.columns = ['opening', 'mob', 'number_of_clients']
vintage<jupyter_output><empty_output><jupyter_text>To explain the above table, let's look at the index 1886:- opening -2 = 2 months ago- mob 1 = account open for one month- number_of_clients 770So, two months ago we had 770 clients with accounts open for a period of one month.<jupyter_code>vintage = pd.merge(vintage[['opening', 'mob']],
                   approved_clients_denominator,
                   on = ['opening'],
                   how = 'left')
vintage
vintage['number_of_bad_payers'] = np.nan

for j in range(-60, 1):
    ls = []
    for i in range(0, 61):
        due = list(approved_clients.query("bad == 1 and mob == @i and opening == @j")['id'])
        ls.extend(due)
        vintage.loc[(vintage['mob'] == i) & (vintage['opening'] == j), 'number_of_bad_payers'] = len(set(ls))

vintage['bad_payers_rate'] = vintage['number_of_bad_payers'] / vintage['number_of_clients']
vintage
vintage_pivot = vintage.pivot(index = 'opening',
                              columns = 'mob',
                              values = 'bad_payers_rate')

vintage_pivot
lst = [i for i in range(0, 61)]
vintage_pivot[lst].T.iloc[:, :10].plot(legend = True,
                                       grid = True,
                                       title = 'Cumulative Percentage of Bad Clients (late pay > 60 days)',
                                       figsize = (10, 8))

plt.xlabel('MOB')
plt.ylabel('Cumulative Percentage of Bad Clients')
plt.legend(bbox_to_anchor = (1.12, 1), title = 'Opening')
plt.show()
sns.histplot(data = approved_clients_unique_id,
             x = 'window',
             cumulative = True,
             stat = 'density',
             kde = True)
plt.title('Distribution of Clients by Window')
plt.xlabel('Window')
plt.ylabel('Percentage of Clients')
plt.show()
late_interval_dict = {
    'more_than_30_days': 3,
    'more_than_60_days': 4,
    'more_than_90_days': 5,
    'more_than_120_days': 6,
    'more_than_150_days': 7
}
for key, value in late_interval_dict.items():
    approved_clients[f'bad_{key}'] = approved_clients.apply(lambda x: 1 if x['status_int'] >= value else 0, axis = 1) 
approved_clients
bad_rate_dict = {}
id_sum = len(set(approved_clients['id']))

for key in late_interval_dict.keys():
  min_bad = approved_clients.query(f'bad_{key} == 1').groupby('id')['mob'].min().reset_index()
  mob_bad_rate = pd.DataFrame({'mob':range(0,61), 'bad_rate': np.nan})
  lst = []
  for i in range(0,61):
      due = min_bad.query('mob == @i')['id'].to_list()
      lst.extend(due)
      mob_bad_rate.loc[mob_bad_rate['mob'] == i, 'bad_rate'] = len(set(lst)) / id_sum
  bad_rate_dict[key] = mob_bad_rate['bad_rate']

bad_rate = pd.DataFrame(bad_rate_dict)
bad_rate
bad_rate.plot(grid = True,
              title = 'Cumulative Percentage of Bad Clients by Late Window',
              figsize = (10, 6))
plt.xlabel('Mob')
plt.ylabel('Cumulative Percentage of Clients')
plt.show()<jupyter_output><empty_output><jupyter_text>Defining the Target Variable<jupyter_code>len(approved_clients.query('window >= 12').groupby('id').count().index)
approved_clients_treatment1 = approved_clients.query('window >= 12').copy()
approved_clients_treatment1.shape
def verify(registers):
  status_list = registers['status'].to_list()
  if '60-89days' in status_list or '90-119days' in status_list or '120-149days' in status_list or '>150days' in status_list:
    return 1
  else:
    return 0
  
client_records_unique_id = pd.DataFrame(approved_clients_treatment1.groupby('id').apply(verify)).reset_index()
client_records_unique_id.columns = ['id', 'bad']
client_records_unique_id.head()
client_records_unique_id.shape
client_records_treatment1.shape
target_client_records = client_records_treatment1.merge(client_records_unique_id, on = 'id')
target_client_records.head()
target_client_records['bad'].value_counts(normalize = True) * 100<jupyter_output><empty_output><jupyter_text>We can see that our data is pretty unbalanced.  Lesson 04<jupyter_code>!pip install scikit-learn==0.23.2 imbalanced-learn==0.7.0
import six
import sys
sys.modules['sklearn.externals.six'] = six
binary_columns = ['own_car', 'own_property', 'own_workphone',
                  'own_phone', 'own_email']
non_binary_columns = ['income_type', 'education_type', 'marital_status',
                      'housing_type', 'occupation_type']
target_client_records = target_client_records[['id'] + quantitative_variables + binary_columns + non_binary_columns + ['bad']]
target_client_records.head()
SEED = 1561651

def run_model(model, data):

    X = data.drop(['id', 'bad'], axis=1)
    y = data.drop('id', axis = 1)['bad']
    
    X_train, X_test, y_train, y_test = train_test_split(X, y, stratify = y, random_state = SEED)

    model.fit(X_train, y_train)

    prob_predic = model.predict_proba(X_test)
    auc = roc_auc_score(y_test, prob_predic[:,1])
    print(f'AUC score: {auc}')
    
    good_data = np.sort(model.predict_proba(X_test)[:, 1])
    bad_data = np.sort(model.predict_proba(X_test)[:, 0])
    kstest = stats.ks_2samp(good_data, bad_data)
    print(f"KS: {kstest}")

    CM = plot_confusion_matrix(model,
                               X_test,
                               y_test,
                               values_format = '.0f',
                               display_labels = ['Good', 'Bad'])
    plt.grid(False)    
    plt.show(CM)

    prediction = model.predict(X_test)
    print('\nClassification Report')
    print(classification_report(y_test, prediction, zero_division = 0))
class Transformer(BaseEstimator, TransformerMixin):
    def __init__(self, quantitative_variables, categorical_variables):
        self.quantitative_variables = quantitative_variables
        self.categorical_variables = categorical_variables
        self.encoder = OneHotEncoder()
        self.scaler = MinMaxScaler()

    def fit(self, X, y = None):
        self.encoder.fit(X[self.categorical_variables])
        self.scaler.fit(X[self.quantitative_variables])
        return self 

    def transform(self, X, y = None):
      X_categorical = pd.DataFrame(data = self.encoder.transform(X[self.categorical_variables]).toarray(),
                                  columns = self.encoder.get_feature_names(self.categorical_variables))
      
      X_quantitative = pd.DataFrame(data = self.scaler.transform(X[self.quantitative_variables]),
                                  columns = self.quantitative_variables)
      
      X = pd.concat([X_quantitative, X_categorical], axis = 1)

      return X<jupyter_output><empty_output><jupyter_text>Dummy Classifier<jupyter_code>pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Model', DummyClassifier(strategy = 'prior'))])   
run_model(pipeline, target_client_records)<jupyter_output>AUC score: 0.5
KS: Ks_2sampResult(statistic=1.0, pvalue=0.0)<jupyter_text>Logistic Regression<jupyter_code>pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Model', LogisticRegression(random_state = SEED, max_iter = 1000))])   
run_model(pipeline, target_client_records)<jupyter_output>AUC score: 0.5847293657137516
KS: Ks_2sampResult(statistic=1.0, pvalue=0.0)<jupyter_text>Oversampling and Undersampling<jupyter_code>pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Sampling', SMOTE(random_state = SEED)),
                     ('Model', LogisticRegression(random_state = SEED, max_iter = 1000))])   
run_model(pipeline, target_client_records)
pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Model', RandomForestClassifier(random_state = SEED))])   
run_model(pipeline, target_client_records)
pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Sampling', SMOTE(random_state = SEED)),
                     ('Model', RandomForestClassifier(random_state = SEED))])   
run_model(pipeline, target_client_records)<jupyter_output>AUC score: 0.8419941047885691
KS: Ks_2sampResult(statistic=0.9651776292648611, pvalue=0.0)<jupyter_text>Lesson 05<jupyter_code>pipeline = Pipeline([('Transformer', Transformer(quantitative_variables, categorical_variables)),
                     ('Sampling', SMOTE(random_state = SEED)),
                     ('Model', RandomForestClassifier(random_state = SEED))])   
X = target_client_records.drop(['id', 'bad'], axis = 1)
y = target_client_records['bad']

pipeline.fit(X, y)<jupyter_output><empty_output><jupyter_text>Saving the Model<jupyter_code>from joblib import dump
dump(pipeline, 'model.joblib')
features = X.columns
dump(features, 'features.joblib')
categories_list = target_client_records[non_binary_columns].apply(lambda x: x.unique(), axis = 0)
dump(categories_list, 'categories_list.joblib')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12187274643590171, 'fraction_numerical': 0.026460309535696454, 'mean_word_length': 4.75422917331631, 'pattern_counts': {'":': 0, '<': 96, '<?xml version=': 0, '>': 104, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '500704', 'license': 'MIT', 'n_tokens_mistral': 6431, 'n_tokens_neox': 5879, 'n_words': 1134, 'path': 'modulo6/aulas_desafios_modulo6.ipynb', 'repo_name': 'diascarolina/data-science-bootcamp', 'size': '18027'}