{"cells":[{"metadata":{"collapsed":true,"_uuid":"219c78594e2c0fef794f8f908732e07d10fa260b","_cell_guid":"a4e2babd-ff0d-4827-a746-d3707ac3b9cd"},"cell_type":"markdown","source":"# 1. Load data and library"},{"metadata":{"_uuid":"270be78f486599979774f90ef7afdf9ea4ac6097","_cell_guid":"79db8d3e-392b-4e56-b087-57a7e8f1bbc7"},"cell_type":"markdown","source":"## 1.1 Load library"},{"metadata":{"_kg_hide-output":true,"_uuid":"e62bee026983a801d0560d4dec52bbd5368cb036","trusted":false,"_cell_guid":"a793328c-4084-4dce-82d4-a2da370ca663"},"cell_type":"code","source":"# general visualisation\nlibrary('ggplot2') # visualisation\nlibrary('scales') # visualisation\nlibrary('grid') # visualisation\nlibrary('gridExtra') # visualisation\nlibrary('RColorBrewer') # visualisation\nlibrary('corrplot') # visualisation\n\n# general data manipulation\nlibrary('dplyr') # data manipulation\nlibrary('readr') # input/output\nlibrary('data.table') # data manipulation\nlibrary('tibble') # data wrangling\nlibrary('tidyr') # data wrangling\nlibrary('stringr') # string manipulation\nlibrary('forcats') # factor manipulation\nlibrary('Matrix')\nlibrary('xgboost')\nlibrary('caret')\n\n# Dates\nlibrary('lubridate') # date and time\n\n# Extra vis\nlibrary('ggforce') # visualisation\nlibrary('ggridges') # visualisation","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7c928eb1927412fdda49c03136dab25d0596e046","_cell_guid":"97abab8a-70e6-4d95-b03c-5761c913ae53"},"cell_type":"markdown","source":"## 1.2 Helper functions"},{"metadata":{"_kg_hide-output":false,"_uuid":"e573b4d2c5c7f872c7ddf2b9180af45d818d0f6a","_kg_hide-input":true,"trusted":false,"_cell_guid":"f76485f5-6bd2-4a13-9c56-526fda5e2b81"},"cell_type":"code","source":"get_binCI <- function(x,n) as.list(setNames(binom.test(x,n)$conf.int, c(\"lwr\", \"upr\")))\n# Define multiple plot function\n\n# ggplot objects can be passed in ..., or to plotlist (as a list of ggplot objects)\n# - cols:   Number of columns in layout\n# - layout: A matrix specifying the layout. If present, 'cols' is ignored.\n#\n# If the layout is something like matrix(c(1,2,3,3), nrow=2, byrow=TRUE),\n# then plot 1 will go in the upper left, 2 will go in the upper right, and\n# 3 will go all the way across the bottom.\n#\nmultiplot <- function(..., plotlist=NULL, file, cols=1, layout=NULL) {\n\n  # Make a list from the ... arguments and plotlist\n  plots <- c(list(...), plotlist)\n\n  numPlots = length(plots)\n\n  # If layout is NULL, then use 'cols' to determine layout\n  if (is.null(layout)) {\n    # Make the panel\n    # ncol: Number of columns of plots\n    # nrow: Number of rows needed, calculated from # of cols\n    layout <- matrix(seq(1, cols * ceiling(numPlots/cols)),\n                    ncol = cols, nrow = ceiling(numPlots/cols))\n  }\n\n if (numPlots==1) {\n    print(plots[[1]])\n\n  } else {\n    # Set up the page\n    grid.newpage()\n    pushViewport(viewport(layout = grid.layout(nrow(layout), ncol(layout))))\n\n    # Make each plot, in the correct location\n    for (i in 1:numPlots) {\n      # Get the i,j matrix positions of the regions that contain this subplot\n      matchidx <- as.data.frame(which(layout == i, arr.ind = TRUE))\n\n      print(plots[[i]], vp = viewport(layout.pos.row = matchidx$row,\n                                      layout.pos.col = matchidx$col))\n    }\n  }\n}","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"149e40c5f0d9656459487c0c70899102a7ebedfa","_cell_guid":"fd439c1a-f5b1-40ec-8f7f-de55119e619e"},"cell_type":"markdown","source":"## 1.3 load data"},{"metadata":{"_uuid":"d5883f408077fe1690cb13a9ee747c4cb87d4c19","trusted":false,"_cell_guid":"c5f57be3-74a2-4b03-b256-0210ee898d35"},"cell_type":"code","source":"train <- as.tibble(fread('../input/train.csv'))\ntest <- as.tibble(fread('../input/sample_submission_zero.csv'))\nmembers <- as.tibble(fread('../input/members_v3.csv', nrows = 1e6))\ntrans <- as.tibble(fread('../input/transactions.csv', nrows = 1e6))\nlogs <- as.tibble(fread('../input/user_logs.csv', nrows = 5e6))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2cda18beb4ab2ee54510b90de53552207f3615c7","_cell_guid":"256f2f00-4e61-4b5a-b2a2-f83682d23a20"},"cell_type":"markdown","source":"## 1.4 File structure"},{"metadata":{"scrolled":true,"_uuid":"8be05d8923d425fcab35d60df46107fc02358bff","trusted":false,"_cell_guid":"3d3a91bc-60ee-4ae1-a3a4-42cca423e610"},"cell_type":"code","source":"glimpse(train)\nprint(\"==============members==========\")\nglimpse(members)\nprint(\"==============trans============\")\nglimpse(trans)\nprint(\"==============logs============\")\nglimpse(logs)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"34fa5e00a19dab34d1554a1a2f834090a0a450c1","_cell_guid":"be22cdaf-e56a-4d28-bf07-91425ee3c0dd"},"cell_type":"markdown","source":"## 1.5 Reformating features"},{"metadata":{"_uuid":"f715f142c714cfcb5032d1aa7a56837dc05e0ede","trusted":false,"_cell_guid":"ae1490f4-955f-445a-b59f-3dfb38d79a82"},"cell_type":"code","source":"train = train%>%\n        mutate(is_churn=as.factor(is_churn))\nmembers = members%>%\n            mutate(city=as.factor(city),\n                   gender = as.factor(gender),\n                  reg_via = as.factor(registered_via),\n                  reg_init=ymd(registration_init_time))\ntrans = trans%>%\n            mutate(pay_met = as.factor(payment_method_id),\n                  auto_renew = as.factor(is_auto_renew),\n                  is_cancel = as.factor(is_cancel),\n                  trans_date=ymd(transaction_date),\n                  exp_date=ymd(membership_expire_date))\nlogs = logs%>%\n        mutate(date=ymd(date))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c18006ceafa446e3af73ab390a3b634ff819abda","_cell_guid":"ee33f914-be60-4aa3-a392-9dc0d583c259"},"cell_type":"markdown","source":"# 2. Invidual feature visualization\n"},{"metadata":{"_uuid":"32bc293e7b812bb9e310fea082a48e9a06849d91","_cell_guid":"1f6ab8f4-df3d-42ed-8845-f859a3d12fd0"},"cell_type":"markdown","source":"## 2.1 Train and members data"},{"metadata":{"scrolled":true,"_uuid":"550a8ef5c9e92e97e8150811b4cda3a87ee79f27","trusted":false,"_cell_guid":"b53b92c6-aace-4a6f-aacb-8be8b23e89d9"},"cell_type":"code","source":"p1 = train %>% \n        ggplot()+geom_bar(mapping=aes(is_churn,fill=is_churn))\np2 = members %>% \n        ggplot()+geom_bar(aes(x=city,fill=city)) + theme(legend.position = \"none\")\np3 = members %>% \n        ggplot()+geom_bar(aes(x=gender,fill=gender)) + theme(legend.position = \"none\")\np4 = members %>% \n        filter(bd>0 & bd<100) %>% \n        ggplot() + geom_density(aes(x=bd),fill=\"blue\",alpha=0.6)\np5 = members%>% \n        ggplot() + geom_bar(aes(reg_via,fill=reg_via)) + theme(legend.position = \"none\")\np6 = members %>% \n        ggplot() + geom_freqpoly(aes(x=reg_init),col=\"red\",binwidth=1) + labs(x=\"registration time\")\nlayout <- matrix(c(1,2,3,4,5,6),2,3,byrow=TRUE)\nmultiplot(p1,p2,p3,p4,p5,p6)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"37fdbabdfbbf76d5ca107fd3c5c2e351e4643562","_cell_guid":"143e2205-9867-4460-8f1f-ebc4c6137ad7"},"cell_type":"markdown","source":"we find\n- only 6% customers churn\n- most users from city 1\n- many users have unknown gender\n- age range from 15-50\n- increasing registration from 2014"},{"metadata":{"_uuid":"4dddaf8342813759a3cb47fb1c7ed29961ada580","_cell_guid":"96b04131-788b-45a1-87b4-5fb87cddcb9b"},"cell_type":"markdown","source":"## 2.2 transaction data"},{"metadata":{"_uuid":"6dcdec154ede119065dfd89e3bfa24209f1dfdf4","trusted":false,"_cell_guid":"494be414-bf51-41a0-b627-defb6b5185de"},"cell_type":"code","source":"trans = trans %>% \n            select(msno,payment_plan_days,plan_list_price,is_cancel,pay_met,auto_renew,trans_date,exp_date)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e60696a575fd81dffe0c8ec096b6815af476bcd4","trusted":false,"_cell_guid":"9fc9e2c9-4cb2-4acf-92da-9e821de355bb"},"cell_type":"code","source":"p1 = trans %>% \n        mutate(payment_plan_days = factor(payment_plan_days)) %>%\n        ggplot() + \n        geom_bar(aes(x=payment_plan_days,fill=payment_plan_days)) + \n        scale_y_sqrt() + \n        theme(legend.position = \"none\")\np2 = trans %>% \n        ggplot() + \n        geom_bar(aes(auto_renew,fill=auto_renew))+ \n        theme(legend.position = \"none\")\np3 = trans %>% \n        ggplot() + \n        geom_bar(aes(pay_met,fill=pay_met)) + \n        scale_y_sqrt()+ \n        theme(legend.position = \"none\")\np4 = trans %>%\n        ggplot() + \n        geom_freqpoly(aes(trans_date),col=\"red\",binwidth=1) + \n        facet_zoom(x=trans_date > ymd(\"20160101\") & trans_date<ymd(\"20170101\"))\nlayout = matrix(c(1,2,3,4),4,1,byrow = TRUE)\nmultiplot(p1,p2,p3,p4,layout = layout)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ab0e3ffd41d3e44137d8d14576b2b8a7f2e5c61b","_cell_guid":"fcf0c345-704d-44be-93a8-11031a1a5998"},"cell_type":"markdown","source":"we find:\n- most of the users choose 30 days to auto_pay\n- most of the users choose auto renew\n- spike appears in the initial of next month, possibly due to renew\n"},{"metadata":{"_uuid":"0733ce9c2c95fc08332fba5e9d5ecd5a74219bf1","_cell_guid":"fded5a5c-edc5-4f8f-9a8f-34f1f51f6997"},"cell_type":"markdown","source":"## 2.3 Logs data"},{"metadata":{"_uuid":"a81ac1616b1a071f3a151e7618b8fcc42804ae49","trusted":false,"_cell_guid":"dc2a6912-1439-48c4-b6cd-e2db42235392"},"cell_type":"code","source":"p1 = logs %>%\n        count(msno) %>%\n        ggplot() + \n        geom_bar(aes(x=n,fill=\"blue\"))+\n        labs(x=\"entries per user\")\np2 = logs %>% \n        filter(abs(total_secs)<1e5) %>%\n        ggplot() +\n        geom_density(aes(total_secs),fill=\"blue\",alpha=0.5)\np3 = logs %>% \n        ggplot() + \n        geom_bar(aes(num_unq),fill=\"red\",alpha=0.5) +\n        scale_x_log10()\np4 = logs %>% \n        gather(num_25,num_50,num_75,num_985,num_100,key=\"slen\",value = \"cases\") %>%\n        mutate(slen = fct_relevel(factor(slen),\"num_100\", after = Inf)) %>%\n        ggplot() + \n        geom_density(aes(x=cases,fill=slen),position = \"stack\",bw=0.1) +\n        scale_x_log10(lim = c(1,800)) +\n        labs(x = \"Number of songs\", fill = \"% played\")\nlayout = matrix(c(1,2,3,4),2,2,byrow=TRUE)\nmultiplot(p1,p2,p3,p4)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b022a8f2129ee92882c9a326817f9fe7a5ff547e","_cell_guid":"a5192a6b-7a27-4607-bb5a-8f150df92d0a"},"cell_type":"markdown","source":"# 3. Dataset Relation"},{"metadata":{"_uuid":"f30d208211e1dddf1cd77815e6559b6f187b0cb8","_cell_guid":"9be21b83-23b7-47dd-89f7-c53dfb4cfd02"},"cell_type":"markdown","source":"## 3.1 members churn rate"},{"metadata":{"_uuid":"c596cb28c259fb2e374b0063a3d4eac24978e60a","trusted":false,"_cell_guid":"64eecb36-6fbb-488b-9b9f-0a9b0cbc1a69"},"cell_type":"code","source":"head(members)\nhead(train)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eef35006ffa96d2bab5f1ab4f059cfbbc8eb579d","trusted":false,"_cell_guid":"e491d22c-29b3-4294-8c31-f3e8174fb96a"},"cell_type":"code","source":"p1 = members %>% \n        select(msno,gender) %>%\n        filter(gender!=\"\") %>%\n        left_join(train,by = \"msno\") %>%\n        filter(!is.na(is_churn)) %>% \n        group_by(gender,is_churn) %>%\n        count() %>%        \n        spread(is_churn,n) %>%\n        mutate(frac_churn = `1`/(`1`+`0`)*100,\n               lwr = get_binCI(`1`,(`1`+`0`))[[1]]*100,\n               upr = get_binCI(`1`,(`1`+`0`))[[2]]*100) %>%\n        ggplot(aes(gender, frac_churn, fill = gender)) +\n        geom_col()+\n        geom_errorbar(aes(ymin = lwr, ymax = upr), width = 0.5, size = 0.7, color = \"gray30\") +\n        theme(legend.position = \"none\") +\n        labs(x = \"Gender\", y = \"Churn [%]\")\np2 <- members %>%\n  select(reg_via, msno) %>%\n  left_join(train, by = \"msno\") %>%\n  filter(!is.na(is_churn)) %>%\n  group_by(reg_via, is_churn) %>%\n  count() %>%\n  spread(is_churn, n) %>%\n  mutate(frac_churn = `1`/(`1`+`0`)*100,\n         lwr = get_binCI(`1`,(`1`+`0`))[[1]]*100,\n         upr = get_binCI(`1`,(`1`+`0`))[[2]]*100\n         ) %>%\n  ggplot(aes(reorder(reg_via, -frac_churn, FUN = max), frac_churn, fill = reg_via)) +\n  geom_col() +\n  geom_errorbar(aes(ymin = lwr, ymax = upr), width = 0.5, size = 0.7, color = \"gray30\") +\n  theme(legend.position = \"none\") +\n  labs(x = \"Registration method\", y = \"Churn [%]\")\np3 = members %>% \n     select(msno,bd) %>%\n     filter(bd>0 & bd<100) %>%\n     left_join(train,by=\"msno\") %>%\n     filter(!is.na(is_churn)) %>%\n     ggplot() +\n     geom_density(aes(x=bd,fill=is_churn),bw=1,alpha=0.5) +\n     labs(x=\"age-churn\")\nlayout=matrix(c(1,2,3),3,1,byrow=TRUE)\nmultiplot(p1,p2,p3)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bd7b4c080bcf26dd6b8700c20f2c2fc27a8b86ae","_cell_guid":"03b309d5-9dd3-4990-9338-e690dd1da998"},"cell_type":"markdown","source":"# 4. Data processing"},{"metadata":{"_uuid":"a8c87f2ebe21ff560fbb68f8f2119b8afe5a59ea","trusted":false,"_cell_guid":"fa3da29a-0b11-46fe-9350-85b9c3fcfe4e"},"cell_type":"code","source":"PATH <- \"../input/\"\ntrain <- fread(paste0(PATH,\"train.csv\"), sep=\",\", na.strings = \"\", stringsAsFactors=T)\ntransactions <- fread(paste0(PATH,\"transactions.csv\"), sep=\",\", na.strings = \"\", stringsAsFactors=T)\nmembers <- fread(paste0(PATH,\"members_v3.csv\"), sep=\",\", na.strings = \"\", stringsAsFactors=T)\nsample_submission_zero <- fread(paste0(PATH,\"sample_submission_v2.csv\"), sep=\",\", na.strings = \"\", stringsAsFactors=T)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e35f174f0499d7a2ac4b2e1972f208470e47ca31","_cell_guid":"df6ce1f6-279d-4efc-8b22-cb60dbf88391"},"cell_type":"markdown","source":"### 4.1 Combine train and test dataset"},{"metadata":{"_uuid":"aa1e34eb57b25ea20713d558219920e1f3d81309","trusted":false,"_cell_guid":"d065bd0e-e5c3-4a3b-aac9-639a8f1f304d"},"cell_type":"code","source":"# combine train and test\nsample_submission_zero$is_churn <- NA\ndata <- rbind(train, sample_submission_zero)\n#data[,is_duplicate := as.numeric(duplicated(as.character(data$msno)) | duplicated(as.character(data$msno),fromLast=T))]\nrm(train);gc()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2b2cb74417759595657fecfd938f6655a1724a2e","_cell_guid":"d466f496-fcd9-49aa-9984-64c2e161e1c2"},"cell_type":"markdown","source":"### 4.2 processing members\n- Format members and remove NAs\n- Format dates and do some feature engineering"},{"metadata":{"_uuid":"79f3992c788694d7c6909533b5f1b8d8fda6cdf7","trusted":false,"_cell_guid":"0f587fd0-64c9-49ea-82a6-a41f613fab7e"},"cell_type":"code","source":"members = as.data.table(members)\nmembers[,gender := as.numeric(gender)]\nmembers$gender[is.na(members$gender)] = 0\n\nmembers[,\":=\"(reg_fulldate = members$registration_init_time\n             ,registration_init_time = as.Date(as.character(registration_init_time), '%Y%m%d'))]\nmembers[,\":=\"(reg_year = year(registration_init_time)\n             ,reg_month = month(registration_init_time)\n             ,reg_mday = mday(registration_init_time)\n             ,reg_wday = wday(registration_init_time)\n             ,reg_days = as.numeric(difftime(ymd(\"2017/02/28\"),ymd(reg_fulldate),units=\"days\")))]\nmembers <- subset(members, select = -c(registration_init_time))\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e92baf6aefef09b583c490cb94f04e5c7a861a60","trusted":false,"_cell_guid":"9a71034d-b381-42da-aa80-50d33257099c"},"cell_type":"code","source":"head(members)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e87aa22eccd8d26606450d0c1f14570e2e42ce41","_cell_guid":"948e72c1-7782-4e37-aa1c-cf2ad489b6e3"},"cell_type":"markdown","source":"### 4.3 merge data and members"},{"metadata":{"_uuid":"67dd3c7d81ef10f99cd07d34d1360c709157afc3","trusted":false,"_cell_guid":"2c75c1f9-d47b-47df-983f-a85874ef0264"},"cell_type":"code","source":"data <- merge(data, members, by = \"msno\", all.x = TRUE)\nrm(members);gc()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5cde51c8d4573dfe28673a162ae484c1cded3c01","trusted":false,"_cell_guid":"29809765-c2c9-464b-af9e-41dd4f0e57b6"},"cell_type":"code","source":"transactions <- transactions[transactions$msno %in% levels(data$msno),]\ntransactions[,n_transactions := .N, by = msno]\ntransactions[,payment_price_diff := plan_list_price - actual_amount_paid]\n# transactions <- transactions[,lapply(.SD,mean,na.rm=T), by = msno, .SDcols = names(transactions)[c(2:6,9:11)]] # remove transaction dates\n### calculate transaction ratio\ntransactions = transactions %>% \n                mutate(trans_1week = paste(year(ymd(transaction_date)),week(ymd(transaction_date)),sep = \"-\"),\n                       trans_4week = paste(year(ymd(transaction_date)),floor(as.numeric(substr(trans_1week,regexpr(\"-\",trans_1week)+1,nchar(trans_1week)))/5),sep=\"-\"),\n                       trans_8week =paste(year(ymd(transaction_date)),floor(as.numeric(substr(trans_1week,regexpr(\"-\",trans_1week)+1,nchar(trans_1week)))/9),sep=\"-\"),\n                      )\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a38827de5af1601783aa72a88fe661f6927ef6e5","_cell_guid":"e0470e8e-fc71-4125-a7d0-390bc4bc0963"},"cell_type":"markdown","source":"#### create auto renew and cancel ratio features"},{"metadata":{"_uuid":"2c1765a345d93712f3319828538996f530bbcb1e","trusted":false,"_cell_guid":"560f837a-62d8-4436-88f4-3b507a32459d"},"cell_type":"code","source":"# frac_cancel_1week =\n#     transactions %>% \n#     select(trans_1week,is_cancel) %>%\n#     group_by(trans_1week,is_cancel) %>% \n#     summarise(number = n()) %>% \n#     spread(is_cancel,number) %>%\n#     mutate(frac_cancel_1week = `1`/(`1`+`0`)) %>%\n#     select(trans_1week,frac_cancel_1week)\n# frac_cancel_4week =\n#     transactions %>% \n#     select(trans_4week,is_cancel) %>%\n#     group_by(trans_4week,is_cancel) %>% \n#     summarise(number = n()) %>% \n#     spread(is_cancel,number) %>%\n#     mutate(frac_cancel_4week = `1`/(`1`+`0`)) %>%\n#     select(trans_4week,frac_cancel_4week)\n# frac_cancel_8week =\n#     transactions %>% \n#     select(trans_8week,is_cancel) %>%\n#     group_by(trans_8week,is_cancel) %>% \n#     summarise(number = n()) %>% \n#     spread(is_cancel,number) %>%\n#     mutate(frac_cancel_8week = `1`/(`1`+`0`)) %>%\n#     select(trans_8week,frac_cancel_8week)\n\nfrac_auto_1week = \n    transactions %>% \n    select(trans_1week,is_auto_renew) %>%\n    group_by(trans_1week,is_auto_renew) %>% \n    summarise(number = n()) %>% \n    spread(is_auto_renew,number) %>%\n    mutate(frac_auto_1week = `1`/(`1`+`0`)) %>%\n    select(trans_1week,frac_auto_1week)\n\nfrac_auto_4week = \n    transactions %>% \n    select(trans_4week,is_auto_renew) %>%\n    group_by(trans_4week,is_auto_renew) %>% \n    summarise(number = n()) %>% \n    spread(is_auto_renew,number) %>%\n    mutate(frac_auto_4week = `1`/(`1`+`0`)) %>%\n    select(trans_4week,frac_auto_4week)\n\nfrac_auto_8week = \n    transactions %>% \n    select(trans_8week,is_auto_renew) %>%\n    group_by(trans_8week,is_auto_renew) %>% \n    summarise(number = n()) %>% \n    spread(is_auto_renew,number) %>%\n    mutate(frac_auto_8week = `1`/(`1`+`0`)) %>%\n    select(trans_8week,frac_auto_8week)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a907f8618901dbb93620d331155cf005daf3523d","trusted":false,"_cell_guid":"93712b02-daf4-4452-acb1-f5c0cbdcb3be"},"cell_type":"code","source":"transactions = transactions %>%\n#                 left_join(frac_cancel_1week,by='trans_1week') %>%\n#                 left_join(frac_cancel_4week,by='trans_4week') %>%\n#                 left_join(frac_cancel_8week,by='trans_8week') %>%\n                left_join(frac_auto_1week,by='trans_1week') %>%\n                left_join(frac_auto_4week,by='trans_4week') %>%\n                left_join(frac_auto_8week,by='trans_8week')\ntransactions = as.data.table(transactions)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b4ff7d8ad459ee4879d226b45a7dacdd3b84dbf3","trusted":false,"_cell_guid":"9fa68cb4-62d8-4a89-aee7-308094887abe"},"cell_type":"code","source":"most_common_pay_via = function(lis){\n    as.numeric(sort(table(lis),decreasing=TRUE)[1])\n}  \nhead(transactions)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e3acb20acd9602cea624675782c3c84766b6a0eb","trusted":true,"_cell_guid":"3dcd66db-2b5b-49d6-983f-701f92ef0dea"},"cell_type":"code","source":"transactions1 = transactions %>%\n                group_by(msno) %>%\n                summarise(payment_method_id = most_common_pay_via(payment_method_id),\n                         num_pay_via = n_distinct(payment_method_id),\n                         payment_plan_days = mean(payment_plan_days),\n                         plan_list_price = mean(plan_list_price),\n                         actual_amount_paid = mean(actual_amount_paid),\n                         is_auto_renew = mean(is_auto_renew),\n#                          is_cancel = mean(is_auto_cancel),\n                         n_transactions = n())\ntransactions2 = transactions %>%\n                select(msno,transaction_date,frac_auto_1week,frac_auto_4week,frac_auto_8week) %>%\n                group_by(msno) %>%\n                top_n(1,wt=ymd(transaction_date))\ntransactions_final = merge(transactions1,transactions2,by=\"msno\")","execution_count":1,"outputs":[]},{"metadata":{"_uuid":"9730ae18efa820fba963711c2a900e8670e04f9c","trusted":false,"_cell_guid":"b087faf7-6b6e-47c9-bde7-0ab2bc0f7768"},"cell_type":"code","source":"head(transactions_final)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"91b12dc23e8d9c510e0376192f36a4d89b4924ca","_cell_guid":"e93b4fb2-b2d0-4132-a880-7218660c62ee"},"cell_type":"markdown","source":"### 4.4 combine transaction and data"},{"metadata":{"_uuid":"0bb4afd3055fa18c5d354d2d413e131ec50df4bd","trusted":false,"_cell_guid":"f45c6b0d-5a56-4297-8787-81fd205e2ec0"},"cell_type":"code","source":"data <- merge(data, transactions_final, by = \"msno\", all.x = TRUE)\nrm(transactions_final);gc()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fe5b2b11a5031a246ad6c7d0ad5eb0c591b6ab91","trusted":false,"_cell_guid":"d7540273-681b-4a26-83ae-5a34e9329294"},"cell_type":"code","source":"head(data)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"892ad69b2819fe139cb8e3ff56e88fbfa835fefc","_cell_guid":"5953f740-d967-4ad7-a1d1-4e7ab04b8807"},"cell_type":"markdown","source":"## 4.5 logs data"},{"metadata":{"scrolled":true,"_uuid":"be4f7500a7f5b17e39e4cb463ac963ade4748da6","trusted":false,"_cell_guid":"4d54052c-95c7-40fb-a062-4c051e47a879"},"cell_type":"code","source":"# logs <- as.tibble(fread('../input/user_logs.csv', nrows = 5e6))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8c13fdbcea89bf9df31298d635e5e6717bfc8395","trusted":false,"_cell_guid":"23a6160d-0a2a-42de-971a-c8fb31651a3f"},"cell_type":"code","source":"varnames <- setdiff(colnames(data), c(\"msno\", \"is_churn\"))\n# define the test sample data to get score\ntest_sparse <- Matrix(as.matrix(data[is.na(is_churn), varnames, with=F]), sparse=TRUE)\ntest_ids <- data[is.na(is_churn),msno]\n# remove test data and split our own training-test dataset to see the score\ndata = data[!is.na(is_churn),]\nset.seed(123)\n\n# idx = sample(dim(data)[1],dim(data)[1]*0.7)\ntrain_sparse <- Matrix(as.matrix(data[, varnames, with=F]), sparse=TRUE)\ny_train <- data$is_churn\ndtrain <- xgb.DMatrix(data=train_sparse, label=y_train)\ndtest <- xgb.DMatrix(data=test_sparse)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e2a8ee2e64e2c64d220ce85f34f02898b3569ba4","_cell_guid":"5a652cbb-06ae-4434-8152-a4a6a3474c8b"},"cell_type":"markdown","source":"## 5 Modeling"},{"metadata":{"_uuid":"6f58c93a6c0ed99e0bff72855b4fb288a60f5371","trusted":false,"_cell_guid":"04c5b234-058e-452d-9ef7-380136538fdc"},"cell_type":"code","source":"param <- list(booster=\"gbtree\",\n              objective=\"binary:logistic\",\n              eval_metric=\"logloss\",\n              eval_metric=\"error\",\n              eval_metric=\"auc\",\n              eta = .02,\n              gamma = 1,\n              max_depth = 6,\n              min_child_weight = 1,\n              subsample = .8,\n              colsample_bytree = .8)\n\nxgb_model <- xgb.cv(data = dtrain,\n                    params = param,\n                    nrounds = 500,\n                    verbose = 1,\n                    prediction=TRUE,\n                    print_every_n = 1,\n                   nfold=3)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7198cf792d905e42174bacf94325538d7d6720f5","_cell_guid":"35490532-b477-4974-974d-c2ada0daa1ea"},"cell_type":"markdown","source":"## 5.1 feature importance"},{"metadata":{"_uuid":"14ebf6977abcf9e2e1d4de2fc7512a70cb36cd1a","trusted":false,"_cell_guid":"fd16855e-b9c9-4973-af0e-457eddb217a4"},"cell_type":"code","source":"bst = xgboost(params = param,data=dtrain,nrounds=500)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6db436adbb0aa5d02f86044240280da377efd6d5","trusted":false,"_cell_guid":"e384c4a1-e6ae-4dcb-ab25-1fea182f1c9b"},"cell_type":"code","source":"impor = xgb.importance(colnames(train_sparse),model = bst)\nxgb.plot.importance(impor)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9c3411961e5fb4af2522acea55919ea12e25f952","_cell_guid":"83fd6a55-93b1-4756-a96f-5325607ee610"},"cell_type":"markdown","source":"### 5.2 predict and output csv"},{"metadata":{"_kg_hide-output":false,"_uuid":"7f1f9309614545520862cba3d8ebe6a48433483c","trusted":false,"_cell_guid":"34a7f4a6-eb7d-40f9-adb9-d46e1f56e430"},"cell_type":"code","source":"preds <- data.table(msno=test_ids, is_churn=predict(bst,dtest))\npreds <- merge(sample_submission_zero[,1], preds, by=\"msno\", all.x=T, sort=F)\nwrite.table(preds, \"submission.csv\", sep=\",\", dec=\".\", quote=FALSE, row.names=FALSE)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"251f9662ee0ba74a3de55cb0c6f6f0824c18585b","trusted":false,"_cell_guid":"08e56294-d9a5-4f30-9ab2-f601d78d52cb"},"cell_type":"code","source":"nrow(preds)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"57d9318077b4129e4ed3cd92d8ec95d2f8c848ec","_cell_guid":"1c19eb22-01ee-4623-aff8-d9e09c3b106f"},"cell_type":"markdown","source":""}],"metadata":{"kernelspec":{"display_name":"R","language":"R","name":"ir"},"language_info":{"mimetype":"text/x-r-source","name":"R","pygments_lexer":"r","version":"3.4.2","file_extension":".r","codemirror_mode":"r"}},"nbformat":4,"nbformat_minor":1}