--- title: 'Elo Merchant - First Look' date: '`r format(Sys.Date() , "%d-%m-%Y")`' output: html_document: toc: true theme: simplex number_sections: true code_folding: hide fig_width: 10 fig_height: 6 --- ```{r setup, include=FALSE, echo=FALSE} knitr::opts_chunk$set(echo=TRUE, error=FALSE, warning = FALSE, comment=FALSE) ``` ```{r, message = FALSE, echo = FALSE, comment=FALSE, warning = FALSE} library(plotly) library(scales) library(data.table) library(DT) library(dplyr) colours <- c('#ff8000', '#851e52', '#1f4287', '#a6cb12', '#851e52') ``` ```{r} train <- data.table(read.csv('../input/train.csv')) test <- data.table(read.csv('../input/test.csv')) sample_table<-sample_n(train, 10) train$no <- 1 test$no <- 1 train$first_active_month2 <- paste(train$first_active_month, '01', sep='-') train$first_active_month2 <- as.Date(train$first_active_month2, "%Y-%m-%d") test$first_active_month2 <- paste(test$first_active_month, '01', sep='-') test$first_active_month2 <- as.Date(test$first_active_month2, "%Y-%m-%d") ``` ```{r, message = FALSE, echo = TRUE, comment=FALSE, warning = FALSE} merchants <- data.table(read.csv('../input/merchants.csv')) merchants$no <- 1 ``` # What you can find here? This kernels is quick look through the merchants and train dataset. We will see some important basic fact (for example that we have some duplicated merchant_id, but it's not big issue or that for cards which first_active month is in far past we have less possible values for features ). ## Train & test dataset summary Train dataset has `r nrow(train) ` rows - `r length(unique(train$card_id)) ` unique card_id. All of them have 3 features, information about the first_active_month and information about loyalty numerical score. * first_active_month - month of first purchase min: `r format(min(train$first_active_month2) , "%Y-%m") ` , max:`r format(max(train$first_active_month2) , "%Y-%m") ` * feature_1 - Anonymized card categorical feature: `r as.character(unique(train$feature_1))[order(as.character(unique(train$feature_1)))] ` * feature_2 - Anonymized card categorical feature: `r as.character(unique(train$feature_2))[order(as.character(unique(train$feature_2)))] ` * feature_3 - Anonymized card categorical feature: `r as.character(unique(train$feature_3))[order(as.character(unique(train$feature_3)))] ` * target - Loyalty numerical score calculated 2 months after historical and evaluation period: min: `r min(train$target) ` , max:`r max(train$target) ` Test dataset has `r nrow(test) ` rows - `r length(unique(test$card_id)) ` unique card_id. All of them have 3 features, information about the first_active_month and information about loyalty numerical score. * first_active_month - month of first purchase min: `r format(min(test$first_active_month2[!is.na(test$first_active_month2)]) , "%Y-%m") ` , max:`r format(max(test$first_active_month2[!is.na(test$first_active_month2)]) , "%Y-%m") ` * feature_1 - Anonymized card categorical feature: `r as.character(unique(test$feature_1))[order(as.character(unique(test$feature_1)))] ` * feature_2 - Anonymized card categorical feature: `r as.character(unique(test$feature_2))[order(as.character(unique(test$feature_2)))] ` * feature_3 - Anonymized card categorical feature: `r as.character(unique(test$feature_3))[order(as.character(unique(test$feature_3)))] ` Of course we don't have data about the target score in test dataset. # Train dataset Let's look at the sample data: ```{r} sample_table ``` ### Features by first active month ```{r} by_f1 = c('first_active_month2', 'feature_1') SD_f1 = c('target', 'no') train_f1 <- train[, lapply(.SD, sum), .SDcols =SD_f1 ,by=by_f1] train_f1$feature_1<- as.factor(train_f1$feature_1) p_f1 <- plot_ly(train_f1)%>%add_trace( x = ~first_active_month2,y = ~no, name = 'Feature1' ,type = 'bar', color = ~feature_1, hoverinfo = 'text',name = 'Feature1',colors= colours[1:5], text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature1: ', feature_1, '
qty: ', no) )%>%layout(yaxis = list(title = 'Card_qty'), barmode = 'stack') p_f1 ``` We can see that before `r format(min(train[feature_1!=3,first_active_month2]),"%Y-%m") ` the only possible value of feature_1 was 3. ```{r} by_f2 = c('first_active_month2', 'feature_2') SD_f2 = c('target', 'no') train_f2 <- train[, lapply(.SD, sum), .SDcols =SD_f2 ,by=by_f2] train_f2$feature_2<- as.factor(train_f2$feature_2) p_f2 <- plot_ly(train_f2)%>%add_trace( x = ~first_active_month2,y = ~no, name = 'Feature2' ,type = 'bar', color = ~feature_2, hoverinfo = 'text',name = 'Feature2',colors= colours[1:5], text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature1: ', feature_2, '
qty: ', no) )%>%layout(yaxis = list(title = 'Card_qty'), barmode = 'stack') p_f2 ``` Feature_3 was equal 0 first time in `r format(min(train[feature_3==0,first_active_month2]),"%Y-%m") `. ```{r} by_f3 = c('first_active_month2', 'feature_3') SD_f3 = c('target', 'no') train_f3 <- train[, lapply(.SD, sum), .SDcols =SD_f3 ,by=by_f3] train_f3$feature_3<- as.factor(train_f3$feature_3) p_f3 <- plot_ly(train_f3)%>%add_trace( x = ~first_active_month2,y = ~no, name = 'Feature3' ,type = 'bar', color = ~feature_3, hoverinfo = 'text',name = 'Feature3',colors= colours[1:5], text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature1: ', feature_3, '
qty: ', no) )%>%layout(yaxis = list(title = 'Card_qty'), barmode = 'stack') p_f3 ``` ### Target by first active month In nearest past we have more data (so the graph is more smooth) and the target has higher values by avarage. ```{r} train_f1$avg_target<-train_f1$target/train_f1$no train_f1$first_active_month2<-as.Date(train_f1$first_active_month2) train_f1 <- train_f1[order(train_f1$first_active_month2)] p_f1 <- plot_ly(train_f1)%>%add_trace( x = ~first_active_month2,y = ~avg_target, name = 'Feature1' ,type = 'scatter',mode = 'lines', color = ~feature_1, hoverinfo = 'text',name = 'Feature1',colors= colours[1:5], text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature1: ', feature_1, '
qty: ', no) )%>%layout(yaxis = list(title = 'Avg target')) p_f1 ``` Feature_2 was equal 1 first time in `r format(min(train[feature_2==3,first_active_month2]),"%Y-%m") ` , and was equal 2 first time in `r format(min(train[feature_2==2,first_active_month2]),"%Y-%m") ` . ```{r} train_f2$avg_target<-train_f2$target/train_f2$no train_f2$first_active_month2<-as.Date(train_f2$first_active_month2) train_f2 <- train_f2[order(train_f2$first_active_month2)] p_f2 <- plot_ly(train_f2)%>%add_trace( x = ~first_active_month2,y = ~avg_target, name = 'Feature2' ,type = 'scatter',mode = 'lines', color = ~feature_2, hoverinfo = 'text',name = 'Feature2',colors= colours, text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature2: ', feature_2, '
qty: ', no) )%>%layout(yaxis = list(title = 'Avg target')) p_f2 ``` ```{r} train_f3$avg_target<-train_f3$target/train_f3$no train_f3$first_active_month2<-as.Date(train_f3$first_active_month2) train_f3 <- train_f3[order(train_f3$first_active_month2)] p_f3 <- plot_ly(train_f3)%>%add_trace( x = ~first_active_month2,y = ~avg_target, name = 'Feature3' ,type = 'scatter',mode = 'lines', color = ~feature_3, hoverinfo = 'text',name = 'Feature3',colors= colours[3:5], text = ~paste('First active month: ', format(first_active_month2 , "%Y-%m") , '
feature3: ', feature_3, '
qty: ', no) )%>%layout(yaxis = list(title = 'Avg target')) p_f3 ``` # Merchants ```{r} merchants_dupli_id <- merchants$merchant_id[duplicated(merchants$merchant_id)] ``` Let's see now what we now about merchants (merchant.csv dataset) We have `r nrow(merchants) ` rows - `r length(unique(merchants$merchant_id)) ` unique merchant_id. We will check more carefully what we know about this `r length(unique(merchants_dupli_id)) ` duplicated merchant_id later. In merchant dataset we can find columns: * merchant_id * merchant_group_id : `r length(unique(merchants$merchant_group_id)) ` unique group_id (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$merchant_group_id))) ` merchant_id in group) * merchant_category_id: `r length(unique(merchants$merchant_category_id)) ` unique category (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$merchant_category_id))) ` merchant_id in category) * subsector_id: `r length(unique(merchants$subsector)) ` unique subsector (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$subsector))) ` merchant_id in subsector) * numerical_1: min `r min(merchants$numerical_1) `, max `r max(merchants$numerical_1) ` * numerical_2: min `r min(merchants$numerical_2) `, max `r max(merchants$numerical_2) ` * category_1: `r length(unique(merchants$category_1)) ` unique category_1 (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$category_1))) ` merchant_id in category_1) * most_recent_sales_range: `r as.character(unique(merchants$most_recent_sales_range))[order(as.character(unique(merchants$most_recent_sales_range)))] ` * most_recent_purchases_range: `r as.character(unique(merchants$most_recent_purchases_range))[order(as.character(unique(merchants$most_recent_purchases_range)))] ` * avg_sales_lag3 (Monthly average of revenue in last 3 months divided by revenue in last active month) : min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_sales_lag3, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_sales_lag3, na.rm=T)) ` * avg_purchases_lag3 (Monthly average of transactions in last 3 months divided by transactions in last active month) : min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_purchases_lag3, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_purchases_lag3, na.rm=T)) ` * active_months_lag3 (Quantity of active months within last 3 months) : min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$active_months_lag3, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$active_months_lag3, na.rm=T)) ` * avg_sales_lag6: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_sales_lag6, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_sales_lag6, na.rm=T)) ` * avg_purchases_lag6: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_purchases_lag6, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_purchases_lag6, na.rm=T)) ` * active_months_lag6: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$active_months_lag6, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$active_months_lag6, na.rm=T)) ` * avg_sales_lag12: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_sales_lag12, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_sales_lag12, na.rm=T)) ` * avg_purchases_lag12: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$avg_purchases_lag12, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$avg_purchases_lag12, na.rm=T)) ` * active_months_lag12: min `r dollar_format(prefix = "", big.mark = " ")(min(merchants$active_months_lag12, na.rm=T)) `, max `r dollar_format(prefix = "", big.mark = " ")(max(merchants$active_months_lag12, na.rm=T)) ` * category_4 `r length(unique(merchants$category_4)) ` unique group_id (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$category_4))) ` merchant_id in category_4) * city_id `r length(unique(merchants$city_id)) ` unique group_id (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$city_id))) ` merchant_id in city_id) * state_id `r length(unique(merchants$state_id)) ` unique group_id (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$state_id))) ` merchant_id in state_id) * category_2 `r length(unique(merchants$category_2)) ` unique group_id (on avarage `r dollar_format(prefix = "", big.mark = " ")(length(unique(merchants$merchant_id))/length(unique(merchants$category_2))) ` merchant_id in category_2) ### State and city ```{r cities} by_city = c('state_id','city_id') sd_city = c('no') merchants_city <- merchants[, lapply(.SD, sum), .SDcols =sd_city ,by=by_city] ``` We have `r length(unique(merchants_city$city_id)) ` unique city_id, but `r nrow(unique(merchants_city)) ` unique pair city_id - state_id. It means, that whenever we need a city, we should use the whole pair city-state, as some city names are not unique. Interesting is also that all city_id expect one are not negative number. Is "-1" used as "unknown"? Quite common solution when we want to evoid nulls and have all data as numbers. ```{r cities2 } merchants_city$city_id <- as.factor(merchants_city$city_id) merchants_city$state_id <- as.factor(merchants_city$state_id) p_mc <- plot_ly(merchants_city)%>%add_trace( x = ~state_id,y = ~no, name = 'Cities' ,type = 'bar', color = ~city_id, hoverinfo = 'text', text = ~paste('State: ', state_id , '
City: ', city_id, '
qty: ', no) )%>%layout(yaxis = list(title = 'cities'),showlegend = FALSE, barmode = 'stack') p_mc ``` ### Category and subcategory ```{r cat} by_cat = c('merchant_category_id','subsector_id') sd_cat = c('no') merchants_cat <- merchants[, lapply(.SD, sum), .SDcols =sd_cat ,by=by_cat] merchants_cat$merchant_category_id <- as.factor(merchants_cat$merchant_category_id) merchants_cat$subsector_id <- as.factor(merchants_cat$subsector_id) p_mcat <- plot_ly(merchants_cat)%>%add_trace( x = ~merchant_category_id,y = ~no, name = 'Cities' ,type = 'bar', color = ~subsector_id, hoverinfo = 'text', text = ~paste('merchant_category_id: ', merchant_category_id , '
subsector_id: ', subsector_id, '
qty: ', no) )%>%layout(yaxis = list(title = 'cities'),showlegend = FALSE, barmode = 'stack') p_mcat ``` ### Duplicated ```{r duplicated} merchants_dupli <- merchants[merchants$merchant_id %in% merchants_dupli_id,] merchants_dupli_cat1 <- merchants_dupli[,c('merchant_id', 'category_1', 'category_4')] length(unique(merchants_dupli$merchant_id)) == nrow(unique(merchants_dupli_cat1)) ``` Duplicated merchants often have different group_id, category_id and subcategory_id. Only category_1, category_4 are unchangeable. It's difficult to tell if one merchant can have more then one possible category, but we can assume that yes. In historical data we have information about the category, city, state etc. so we can correctly connect transaction (and card_id) with the proper merchant. ```{r duplicated2} merchants_dupli ```