--- title: 'AMEX Default Prediction - EDA' date: '`r Sys.Date()`' output: html_document: number_sections: true toc: true --- # Introduction In this report, we will review the data files of the [AMEX Default Prediction Compeition](https://www.kaggle.com/competitions/amex-default-prediction/overview). ```{r} library(data.table) library(ggplot2) library(magrittr) library(ggrepel) ``` # EDA ```{r} DAT_DIR <- '../input/amex-default-prediction' list.files(DAT_DIR) ``` Let us review the data files. ```{r} print_dt_dim <- function(dt) { cat(paste0(deparse(substitute(dt)), " dimension: (", paste0(dim(dt), collapse=", "), ")\n")) } ``` ```{r} train_dt <- fread(file.path(DAT_DIR, "train_data.csv")) print_dt_dim(train_dt) ``` ```{r} train_layout <- train_dt[, lapply(.SD, function(i) class(i)[[1]])] %>% as.character() table(train_layout) ``` As we can see, most of the columns are numeric. Let's take a quick peek of the non-numeric columns. ```{r} train_cols <- names(train_dt) non_numeric_cols <- names(train_dt)[unlist(train_dt[, lapply(.SD, function(i) {! is.numeric(i)})])] numeric_cols <- setdiff(train_cols, non_numeric_cols) ``` Let us temporarily delete numerical columns from train_dt as we temporarily do not need it. ```{r} train_dt[, c(numeric_cols):=NULL] ``` ```{r} cat( paste0( "There are ", train_dt$customer_ID %>% unique() %>% length(), " unique customers in the training dat\n" ) ) ``` ```{r} train_dt[, S_2_yrmo := lubridate::floor_date(S_2, "month")] train_account <- train_dt[, .( S_2_start = min(S_2_yrmo, na.rm = TRUE), S_2_end = max(S_2_yrmo, na.rm = TRUE), S_2_NA_cnt = sum(is.na(S_2_yrmo)), S_2_cnt = .N ), by = c("customer_ID")] ``` ```{r} train_account[, c("customer_ID", "S_2_start", "S_2_end")] %>% melt(id.vars=c("customer_ID")) %>% `[`(, .N, by=c("variable", "value")) %>% ggplot(aes(x=value, y=N, color=variable)) + geom_point() + geom_text_repel(aes(label=N), color='black', size=3) + geom_line() + scale_y_log10() ``` A total of about 400K customers were in the system at the beginning (March 2017) and each subsequent months see a few thousand new customers enter the system. All of them have last statement in the training data (i.e., April 2018). ```{r} train_account[, sum(S_2_NA_cnt)] ``` In addition, no missing data exists in the S_2. ```{r} train_account[, duration:= ifelse(S_2_cnt > 1, (S_2_end - S_2_start) / (S_2_cnt-1), NA)] train_account %>% ggplot(aes(x=duration)) + geom_histogram(binwidth=30) + stat_bin(binwidth=30, geom="text", aes(label=..count..), size=3, vjust=1.5, color="white") + scale_y_log10() + scale_x_continuous(breaks=seq(from=0, to=max(train_account$duration, na.rm=TRUE), by=30)) ``` The histogram above shows most accounts have every monthly statement between the first and last. However, a few of them miss at least one statements. It is worth researching if this would correlate with the default risk. ```{r} duration_dt <- train_account[, .N, by=c("duration")] cycle_days <- round(duration_dt$duration[which.max(duration_dt$N)],2) train_account[, implied_S_2_cnt := as.numeric(round((S_2_end - S_2_start) / cycle_days + 1))] train_account[, diff_S_2_cnt := S_2_cnt - implied_S_2_cnt] ``` Let us review the distribution of difference in S_2 counts. ```{r} train_account %>% ggplot(aes(x=diff_S_2_cnt)) + geom_histogram(bins=30) + stat_bin(bins=30, geom="text", aes(label=..count..), size=3, color="black", vjust=-1.0) + scale_y_log10() + labs(x="Missing cycles of statements", y="Count") ``` This chart shows the distribution of customers missing statements. ```{r} train_labels_dt <- fread(file.path(DAT_DIR, "train_labels.csv")) print_dt_dim(train_labels_dt) ``` ```{r} train_account <- merge(train_account, train_labels_dt, by=c("customer_ID")) train_account[, target:=factor(target)] train_account ``` ```{r} train_account[diff_S_2_cnt < 0] %>% ggplot(aes(x=diff_S_2_cnt)) + geom_freqpoly() + facet_grid(target ~ ., scales="free_y") + labs(x = "Missing Statements", y="Count", title="Histogram of Account missing statements vs. target") ``` It does not seem that number of mising statements should be a clear indicator of target. ```{r} train_account %>% ggplot(aes(x=S_2_cnt)) + geom_freqpoly() + facet_grid(target ~ ., scales="free_y") ``` It seems the target=1 has somewhat higher distribution of less than maximum statements. Let us exclude the peak on the rightmost side and review again. ```{r} S2_cnt_dt <- train_account[, .N, by=c("S_2_cnt")] mode_S2_cnt <- S2_cnt_dt$S_2_cnt[which.max(S2_cnt_dt$N)] train_account[S_2_cnt < mode_S2_cnt,] %>% ggplot(aes(x=S_2_cnt)) + geom_freqpoly() + facet_grid(target ~ ., scales="free_y") ``` Despite minor difference, it is not apparent that number of statements (or account history) is linked to target. Now, let us move to the next non-numeric variable D_63. ```{r} train_dt[, .N, by=c("D_63")] %>% ggplot(aes(x=reorder(D_63, -N), y=N)) + geom_col() + geom_text(aes(label=N), vjust=1.5, color="white", size=3) + labs(x="Levels of D_63", y="Count", title="Histogram of D_63") ``` The chart shows D_63 has 6 levels which drastic difference of popularity. Let us report the statistics on the customer level and see if they relate to the target. ```{r} train_D63 <- train_dt[, .(cnt = .N), by=c("customer_ID", "D_63")] train_D63[, ind:=ifelse(cnt > 0, 1, 0)] train_D63_wide <- train_D63 %>% dcast(customer_ID ~ D_63, value.var="ind", fun.aggregate = sum) train_D63_wide[, customer_ID:=NULL] target_D63_cor <- cor(as.numeric(train_labels_dt$target), as.matrix(train_D63_wide)) rm(train_D63, train_D63_wide); gc() target_D63_cor ``` It is quite clear that the correlation between D63 and target is weak. Let us move to D_64. ```{r} train_dt[, .N, by=c("D_64")] %>% ggplot(aes(x=reorder(D_64, -N), y=N)) + geom_col() + geom_text(aes(label=N), vjust=1.5, color="white", size=3) + labs(x="Levels of D_64", y="Count", title="Histogram of D_64") ``` Column D_64 has three letter-code levels, O, U and R. In addition, it also includes "" and "-1". Let us impute "" with label "Unk" to facilitate subsequent analysis. ```{r} train_dt[, D_64:=factor(ifelse(is.na(D_64) | D_64 == "", "Unk", D_64))] train_dt[, .N, by=c("D_64")] %>% ggplot(aes(x=reorder(D_64, -N), y=N)) + geom_col() + geom_text(aes(label=N), vjust=1.5, color="white", size=3) + labs(x="Levels of D_64", y="Count", title="Histogram of D_64") ``` ```{r} train_D64 <- train_dt[, .(cnt = .N), by=c("customer_ID", "D_64")] train_D64[, ind:=ifelse(cnt > 0, 1, 0)] train_D64_wide <- train_D64 %>% dcast(customer_ID ~ D_64, value.var="ind", fun.aggregate = sum) train_D64_wide[, customer_ID:=NULL] target_D64_cor <- cor(as.numeric(train_labels_dt$target), as.matrix(train_D64_wide)) rm(train_D64, train_D64_wide); gc() target_D64_cor ``` The correlation coefficient does seem somewhat significant for D_64. ```{r} rm(train_dt); gc() ``` Finally, let us review the correlation of the numeric columns to the target. For simplicity, let us only consider the columns without any NA values. ```{r} train_dt <- fread(file.path(DAT_DIR, "train_data.csv")) train_num_sum_dt <- train_dt[, lapply(.SD, sum), by=c("customer_ID"), .SDcols=numeric_cols] num_cols_cor_sum <- cor(as.numeric(train_labels_dt$target), as.matrix(train_num_sum_dt[, -c("customer_ID")])) rm(train_dt); gc() names(num_cols_cor_sum) <- numeric_cols num_cols_cor_sum[!is.na(num_cols_cor_sum)] num_cols_cor_sum <- sort(round(num_cols_cor_sum,2), decreasing=TRUE) print(num_cols_cor_sum) num_cols_cor_sum_dt <- data.table(corr_value= num_cols_cor_sum, var_name=names(num_cols_cor_sum)) num_cols_cor_sum_dt %>% ggplot(aes(x=reorder(var_name, -corr_value), y=corr_value)) + geom_col() + scale_x_discrete(breaks=NULL, labels=element_blank()) + labs(x="Variable", y="Correlation with Target") ``` We can see quite a few columns have significant correlation, which should be useful for the model development. # Conclusions In this report, we have explored the training data set. Despite significant effort, we have not found significant correlation of the non-numeric variables to the target variable. The numeric variables, which consist over-majority of the content, do seem to possess quite strong relation to the target. As we have left out many numeric columns that include NA value, it becomes an interesting question as how to incorporate them into the model.