1 Using a step by step approach

Before starting the Berka Analysis, a few important steps were taken in order to prepare the source data files. These steps are listed below:

2 Create Functions (step 1)

This step create functions to be used in the next steps. Following, all functions created are described.

2.1 Specific data ingestion functions

2.1.1 GetGenderFromBirthnumber

The birth_number column is given in the form of YYMMDD for men, and YYMM+50DD for women. The objective of this function is to return the gender of the client via the birth_number.

2.2 Metrics auxiliary functions

2.3 Data preparation functions

2.3.0.1 SplitTestTrainDataset

See topic “Splitting dataset into Train and Test data” for further details.

2.4 Plot auxiliary functions

Functions used in the evaluation step to compare the models.

2.4.4 Plot_ROC

Function used to plot the combined ROC curves of each model.

Needs to receive a dataset with actual and predicted scores of each model.

Plot_ROC <- function(dataset, smooth_opt = FALSE) {
  roc_logistic      <- roc(logistic.actual ~ logistic.predicted,
                           dataset,
                           smooth = smooth_opt,
                           quiet = TRUE)
  
  roc_decision.tree <- roc(decision.tree.actual ~ decision.tree.predicted,
                           dataset,
                           smooth = smooth_opt,
                           quiet = TRUE)
  
  roc_boosting      <- roc(boosting.actual ~ boosting.predicted,
                           dataset,
                           smooth = smooth_opt,
                           quiet = TRUE)
  
  roc_random.forest <- roc(random.forest.actual ~ random.forest.predicted,
                           dataset,
                           smooth = smooth_opt,
                           quiet = TRUE)
  
  p <- ggplot() +
    geom_line(aes(x = 1 - roc_logistic$specificities, 
                  y = roc_logistic$sensitivities, 
                  colour = 'Logistic Regression'), # red
              size = 1,
              linetype = 1,
              alpha = 0.7) +
    geom_line(aes(x = 1 - roc_decision.tree$specificities, 
                  y = roc_decision.tree$sensitivities,
                  colour = 'Decision Tree'), # blue
              size = 1,
              linetype = 1,
              alpha = 0.7) +
    geom_line(aes(x = 1 - roc_boosting$specificities, 
                  y = roc_boosting$sensitivities,
                  colour = 'Boosting'), # green
              size = 1,
              linetype = 1,
              alpha = 0.7) +
    geom_line(aes(x = 1 - roc_random.forest$specificities, 
                  y = roc_random.forest$sensitivities,
                  colour = 'Random Forest'), # purple
              size = 2,
              linetype = 1,
              alpha = 1) +
    geom_abline(aes(intercept = 0, slope = 1),
                linetype = 2,
                size = 1) +
    scale_colour_manual(name = NULL,
                        breaks = c('Logistic Regression', 
                                   'Decision Tree',
                                   'Boosting', 
                                   'Random Forest'),
                        labels = c('Logistic Regression', 
                                   'Decision Tree',
                                   'Boosting', 
                                   'Random Forest'),
                        values = c('#C0392B', 
                                   '#3498DB', 
                                   '#28B463', 
                                   '#9B59B6')) +
    labs(y = 'True Positive Rate',
         x = 'False Positive Rate',
         title = 'Receiver Oerating Characteristic Curve - ROC',
         subtitle = 'Random Forest and Boosting are the models that best discriminate Defaulters and Non-Defaulters') +
    theme_economist() +
    theme(panel.grid = element_blank())
  
  return (p)
}

3 Data Ingestion (step 2)

The process of data ingestion — preparing data for analysis — usually includes steps called extract (taking the data from its current location), transform (cleansing and normalizing the data), and load (placing the data in a database where it can be analyzed).

During this step, in addition to the loading data processes, it was performed data casting, column renaming and small touch-ups. The list below describe each table adjustment taken:

4 Data Cleaning (step 3)

The objective of this step is analysing missing values and other strange conditions. In order to accomplish this task, a few R functions were used to quickly discover missing values, like NA and empty fields.

First thing done, was fixing observations in k_symbol transaction table with ’ ’ (one space) to empty string (’’), using the following command.

Then, the command below was used to find out any NA values in each table.

Solely the transaction table has NA values, in the following columns:

x
trans_id 0
account_id 0
date 0
type 0
operation 0
amount 0
balance 0
k_symbol 0
bank 0
account 760931

Finally, the following command was used in each table to find out where empty values was hidden.

Again, only the transaction table had empty values, according to the table below:

x
trans_id.NA NA
account_id.NA NA
date.NA NA
type.NA NA
operation.TRUE 183114
amount.NA NA
balance.NA NA
k_symbol.TRUE 535314
bank.TRUE 782812
account.NA NA

For the exploration analysis report, we did not take any additional action, since the missing values was not relevant.

5 Label Translation (step 4)

In order to make the data information more understandable, it was translated some relevant labels and domains from Czech to English.

6 Data Enhancement (step 5)

This step aims to improve the analysis by adding auxiliary information. Data enhancement is all about making sure any data that is coming into the business is being looked at with a critical eye and is being filtered down to maximize its value.

The code below get gender, birthday and age from birth_number column using GetGenderFromBirthnumber and GetBirthdateFromBirthnumber functions.

The code below improved loan data by having a classification regarding its payment status.

The code below improved client data by having its age group.

The code below calculate an additional table with current and average account balance for each account.

The code below calculate an additional table with the proportion of each transaction type (k_symbol) on total transaction amount of each account. That data will be used to fit various different predictive models.

7 Data Preparation for Predictive Modeling (step 6)

7.1 Selecting the target dataset

The below function was created to be used in the modeling exercises to be performed, the idea is to have a standard way to get the prepared data set already prepared with correct data types and dummies.

# dataset preparation ---------------------------------------------------------

# The objective of this step is to return a DataFrame to be used in predictive 
# modeling. Therefore, it will join loan, client, district, creditcard, 
# account_balance, account_balance_pattern. Finally, it will rename the variables 
# and create the appropriate dummy variables to be used in the modeling process.

# joining datasets
source_dataset <- left_join(loan, disposition, by = c('account_id', 'type')) %>% 
  left_join(client, by = 'client_id') %>%
  left_join(district, by = 'district_id') %>% 
  left_join(creditcard, by = 'disp_id') %>% 
  left_join(account_balance, by = 'account_id') %>% 
  left_join(account_transaction_pattern, by = 'account_id') %>% 
  mutate(card_age_month = (issued %--% 
                             make_date(1998, 12, 31)) / months(1), 
         last_transaction_age_days = ((last_transaction_date.y %--% 
                                         make_date(1998, 12, 31)) / days(1))) %>% 
  dplyr::select(c("amount.x", "duration", "payments", "status", "defaulter", 
                  "contract_status", "gender", "age", "district_name", 
                  "region", "no_of_inhabitants", 
                  "no_of_municip_inhabitants_less_499", 
                  "no_of_municip_500_to_1999", "no_of_municip_2000_to_9999", 
                  "no_of_municip_greater_10000", "no_of_cities", 
                  "ratio_of_urban_inhabitants", 
                  "average_salary", "unemploymant_rate_1995", 
                  "unemploymant_rate_1996", 
                  "no_of_enterpreneurs_per_1000_inhabitants", 
                  "no_of_commited_crimes_1995", 
                  "no_of_commited_crimes_1996", "type.y", 
                  "card_age_month","account_balance", 
                  "avg_balance","transaction_count", "amount.y", 
                  "last_transaction_age_days", "prop_old_age_pension", 
                  "prop_insurance_payment", 
                  "prop_sanction_interest","prop_household", 
                  "prop_statement", "prop_interest_credited", 
                  "prop_loan_payment", "prop_other"))

# renaming variables
colnames(source_dataset) <- c("x_loan_amount", "x_loan_duration", "x_loan_payments", 
                       "x_loan_status", "y_loan_defaulter", "x_loan_contract_status",
                       "x_client_gender", "x_client_age", 
                       "x_district_name", "x_region", 
                       "x_no_of_inhabitants", "x_no_of_municip_inhabitants_less_499", 
                       "x_no_of_municip_500_to_1999", "x_no_of_municip_2000_to_9999", 
                       "x_no_of_municip_greater_10000", "x_no_of_cities", 
                       "x_ratio_of_urban_inhabitants", 
                       "x_average_salary", "x_unemploymant_rate_1995", 
                       "x_unemploymant_rate_1996", 
                       "x_no_of_enterpreneurs_per_1000_inhabitants", 
                       "x_no_of_commited_crimes_1995", 
                       "x_no_of_commited_crimes_1996", "x_card_type", 
                       "x_card_age_month","x_account_balance", 
                       "x_avg_account_balance","x_transaction_count", 
                       "x_transaction_amount", "x_last_transaction_age_days", 
                       "x_prop_old_age_pension", "x_prop_insurance_payment", 
                       "x_prop_sanction_interest","x_prop_household","x_prop_statement",
                       "x_prop_interest_credited", "x_prop_loan_payment", "x_prop_other")

# excluding redundant variables
source_dataset <- dplyr::select(source_dataset, -c("x_loan_status", "x_loan_contract_status", 
                                     'x_prop_sanction_interest'))

# coercing variable domains and data types
source_dataset$x_card_type = ifelse(is.na(source_dataset$x_card_type), 'no card', 
                             as.character(source_dataset$x_card_type))

source_dataset$x_card_age_month = ifelse(is.na(source_dataset$x_card_age_month), 0, 
                                  source_dataset$x_card_age_month)

source_dataset$y_loan_defaulter = as.integer(source_dataset$y_loan_defaulter)

# creating dummies
source_dataset <- fastDummies::dummy_cols(source_dataset,
                                   remove_first_dummy = TRUE,
                                   select_columns = c("x_client_gender", "x_district_name", 
                                                      "x_region", "x_card_type"))

source_dataset <- dplyr::select(source_dataset, -c("x_client_gender", "x_district_name", "x_region", 
                                     "x_card_type"))

# reordering variables
source_dataset <- source_dataset[ , order(names(source_dataset))]

source_dataset <- dplyr::select(source_dataset, y_loan_defaulter, everything())

# excluding non desirable characters in variable names
colnames(source_dataset) <- stringr::str_replace_all(names(source_dataset), ' ', '_')
colnames(source_dataset) <- stringr::str_replace_all(names(source_dataset), '_-_', '_')
colnames(source_dataset) <- trimws(names(source_dataset))

7.2 Splitting dataset into Train and Test data

The below function was created to be used in the modeling exercises to be split the source_dataset into train and test datasets.

To make sure all the models uses the same datasets for Train and Testing we are saving the initial sampling to be reused across the models.

This will ensure consistency when comparing the models against each other.