## Importing packages library(tidyverse) library(caret) library(broom) train <- read_csv('../input/train.csv') test <- read_csv('../input/test.csv') glimpse(train) #How many different visitors unique_customer <-train$fullVisitorId length(unique_customer) unique_customer_te <- test$fullVisitorId length(unique_customer_te) #make combined training set citation:(https://www.kaggle.com/mrlong/r-flatten-json-columns-to-make-single-data-frame) #JSON columns are "device", "geoNetwork", "totals", "trafficSource" library(jsonlite) tr_device <- paste("[", paste(train$device, collapse = ","), "]") %>% fromJSON(flatten = T) tr_geoNetwork <- paste("[", paste(train$geoNetwork, collapse = ","), "]") %>% fromJSON(flatten = T) tr_totals <- paste("[", paste(train$totals, collapse = ","), "]") %>% fromJSON(flatten = T) tr_trafficSource <- paste("[", paste(train$trafficSource, collapse = ","), "]") %>% fromJSON(flatten = T) te_device <- paste("[", paste(test$device, collapse = ","), "]") %>% fromJSON(flatten = T) te_geoNetwork <- paste("[", paste(test$geoNetwork, collapse = ","), "]") %>% fromJSON(flatten = T) te_totals <- paste("[", paste(test$totals, collapse = ","), "]") %>% fromJSON(flatten = T) te_trafficSource <- paste("[", paste(test$trafficSource, collapse = ","), "]") %>% fromJSON(flatten = T) #Check to see if the training and test sets have the same column names setequal(names(tr_device), names(te_device)) setequal(names(tr_geoNetwork), names(te_geoNetwork)) setequal(names(tr_totals), names(te_totals)) setequal(names(tr_trafficSource), names(te_trafficSource)) #As expected, tr_totals and te_totals are different as the train set includes the target, transactionRevenue names(tr_totals) names(te_totals) #Apparently tr_trafficSource contains an extra column as well - campaignCode #It actually has only one non-NA value, so this column can safely be dropped later table(tr_trafficSource$campaignCode, exclude = NULL) names(tr_trafficSource) names(te_trafficSource) #Combine to make the full training and test sets train <- train %>% cbind(tr_device, tr_geoNetwork, tr_totals, tr_trafficSource) %>% select(-device, -geoNetwork, -totals, -trafficSource) test <- test %>% cbind(te_device, te_geoNetwork, te_totals, te_trafficSource) %>% select(-device, -geoNetwork, -totals, -trafficSource) #Number of columns in the new training and test sets. ncol(train) ncol(test) #Remove temporary tr_ and te_ sets rm(tr_device); rm(tr_geoNetwork); rm(tr_totals); rm(tr_trafficSource) rm(te_device); rm(te_geoNetwork); rm(te_totals); rm(te_trafficSource) #change visitor ID to character and date to date format train$date <- as.Date(as.character(train$date), format = '%Y%m%d') test$date <- as.Date(as.character(test$date), format = '%Y%m%d') range(train$date) range(test$date) glimpse(train) # get number of unique values in each column citation("https://www.kaggle.com/captcalculator/a-very-extensive-gstore-exploratory-analysis") unique <- sapply(train, function(x) { length(unique(x)) }) unique #Drop single unique value columns: citation: https://www.kaggle.com/captcalculator/a-very-extensive-gstore-exploratory-analysis # subset to ==1 one_val <- names(unique[unique == 1]) # drop columns from dtrain train[, (one_val)] <- NULL glimpse(train) #change some of the columns to different types numeric_cols <- c('hits', 'pageviews', 'bounces', 'transactionRevenue', 'newVisits') train[,numeric_cols] <- apply(train[,numeric_cols], 2, function(x) as.numeric(as.character(x))) glimpse(train) #count null values sapply(train, function(x) sum(length(which(is.na(x))))) #thought I dropped campaign code but that didn't happen train$campaignCode <- NULL sapply(train, function(x) sum(length(which(is.na(x))))) #lets look at unique values for clickinfo unique_adwords <- unique(train$adwordsClickInfo.adNetworkType) length(unique_adwords) unique_click <- unique(train$adwordsClickInfo.page) length(unique_click) unique_isvideo <- unique(train$adwordsClickInfo.isVideoAd) length(unique_isvideo) unique_slot <- unique(train$adwordsClickInfo.slot) length(unique_slot) unique_gc <- unique(train$adwordsClickInfo.gclId) unique_adcontent <- unique(train$adContent) length(unique_adcontent) unique_referral_path <- unique(train$referralPath) length(unique_referral_path) unique_istrue <- unique(train$isTrueDirect) length(unique_istrue) unique_newvisits <- unique(train$newVisits) length(unique_newvisits) unique_bounce <- unique(train$bounces) length(unique_bounce) unique_keyword <- unique(train$keyword) length(unique_keyword) #interesting...let's see what the small values are unique_adwords unique_click unique_slot unique_adcontent #look at adContent table(train$adContent) #ok....I'm not sure what to do witht that one for now..... #add a "uknown_"" to NA character values glimpse(train) #col_character <- c("adwordsClickinfo.page", "" ) train$adwordsClickInfo.adNetworkType[is.na(train$adwordsClickInfo.adNetworkType)] <- "uk_type" train$adwordsClickInfo.page[is.na(train$adwordsClickInfo.page)] <- "uk_page" train$adwordsClickInfo.slot[is.na(train$adwordsClickInfo.slot)] <- "uk_slot" train$adContent[is.na(train$adContent)] <- "uk_content" train$adwordsClickInfo.gclId[is.na(train$adwordsClickInfo.gclId)] <- "uk_id" #check nulls again sapply(train, function(x) sum(length(which(is.na(x))))) #get the ones I missed train$transactionRevenue[is.na(train$transactionRevenue)] <- 0 glimpse(train) #turn logicals into doubles train$isTrueDirect <- as.character(train$isTrueDirect) train$isTrueDirect[is.na(train$isTrueDirect)] <- 0 train$isTrueDirect[train$isTrueDirect == "TRUE"] <- 1 train$isTrueDirect <- as.numeric(train$isTrueDirect) glimpse(train) #next logicals train$isMobile <- as.character(train$isMobile) train$isMobile[train$isMobile == "TRUE"] <- 1 train$isMobile[train$isMobile == "FALSE"] <- 0 train$isMobile <- as.numeric(train$isMobile) glimpse(train) #final logical....this one has given me some trouble train$adwordsClickInfo.isVideoAd.c <- as.character(train$adwordsClickInfo.isVideoAd) train$adwordsClickInfo.isVideoAd.c[is.na(train$adwordsClickInfo.isVideoAd.c)] <- 1 train$adwordsClickInfo.isVideoAd.c[train$adwordsClickInfo.isVideoAd.c == "FALSE"] <- 0 #the above and below lines is a long way to do this but for some reason, I was messing this up so I decided to go the long wroute train$adwordsClickInfo.isVideoAd <- train$adwordsClickInfo.isVideoAd.c train$adwordsClickInfo.isVideoAd.c <- NULL #finish out null values train$newVisits[is.na(train$newVisits)] <- 0 train$pageviews[is.na(train$pageviews)] <- 0 train$bounces[is.na(train$bounces)] <- 0 train$keyword[is.na(train$keyword)] <- "0" train$referralPath[is.na(train$referralPath)] <- "uk_path" sapply(train, function(x) sum(length(which(is.na(x)))))