{"cells":[{"metadata":{"_uuid":"6cb94db85d38910dd673138acd2ed065c7cf1fad","_execution_state":"idle","trusted":true},"cell_type":"code","source":"## Importing packages\n\n# This R environment comes with all of CRAN and many other helpful packages preinstalled.\n# You can see which packages are installed by checking out the kaggle/rstats docker image: \n# https://github.com/kaggle/docker-rstats\n\nlibrary(tidyverse) # metapackage with lots of helpful functions\n\n## Running code\n\n# In a notebook, you can run a single code cell by clicking in the cell and then hitting \n# the blue arrow to the left, or by clicking in the cell and pressing Shift+Enter. In a script, \n# you can run code by highlighting the code you want to run and then clicking the blue arrow\n# at the bottom of this window.\n\n## Reading in files\n\n# You can access files from datasets you've added to this kernel in the \"../input/\" directory.\n# You can see the files added to this kernel by running the code below. \n\nlist.files(path = \"../input\")\n\n## Saving data\n\n# If you save any files or images, these will be put in the \"output\" directory. You \n# can see the output directory by committing and running your kernel (using the \n# Commit & Run button) and then checking out the compiled version of your kernel.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ae76201f656cd5e46f917ab3009be3d839589164"},"cell_type":"code","source":"library(data.table)\ntrain = fread(\"../input/train_v2.csv\", header = TRUE,\n           colClasses=c(fullVisitorId='character'),\n           drop=c('hits'))\ntest = fread(\"../input/test_v2.csv\", header = TRUE,\n           colClasses=c(fullVisitorId='character'),\n           drop=c('hits'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d621dc54809aa7b309835ef330dea631927eb3fe"},"cell_type":"code","source":"print('train:'); dim(train)\nprint('test:'); dim(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"84de90845282fb2cc60de11389ad0916a8f73bf9","_kg_hide-output":true},"cell_type":"code","source":"str(train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d7a293bd99313f767ff008c3ac12b9a76e5ae6a4","_kg_hide-output":true},"cell_type":"code","source":"str(test)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"a48cd5d0c7fb9a64212ba29c265aecd857cfba85"},"cell_type":"markdown","source":"* fullVisitorId- A unique identifier for each user of the Google Merchandise Store.\n* channelGrouping - The channel via which the user came to the Store.\n* date - The date on which the user visited the Store.\n* device - The specifications for the device used to access the Store.\n* geoNetwork - This section contains information about the geography of the user.\n* socialEngagementType - Engagement type, either \"Socially Engaged\" or \"Not Socially Engaged\".\n* totals - This section contains aggregate values across the session.\n* trafficSource - This section contains information about the Traffic Source from which the session originated.\n* visitId - An identifier for this session. This is part of the value usually stored as the _utmb cookie. This is only unique to the user. For a completely unique ID, you should use a combination of fullVisitorId and visitId.\n* visitNumber - The session number for this user. If this is the first session, then this is set to 1.\n* visitStartTime - The timestamp (expressed as POSIX time).\n* hits - This row and nested fields are populated for any and all types of hits. Provides a record of all page visits.\n* customDimensions - This section contains any user-level or session-level custom dimensions that are set for a session. This is a repeated field and has an entry for each dimension that is set."},{"metadata":{"_uuid":"81df2ef1c1246ce04fc9c882963739a2f5898b04"},"cell_type":"markdown","source":"Parsing JSONs"},{"metadata":{"trusted":true,"_uuid":"79ccad95d213d19615d96040beb3a65cc0428935","scrolled":false},"cell_type":"code","source":"head(train, n=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"697f935f49c2ce016bf5a5477e91a4b9cf4e0789"},"cell_type":"code","source":"library(jsonlite)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"419110a57f50e73796af0c94f86d136bf37708b5","scrolled":true},"cell_type":"code","source":"train$customDimensions<-gsub(\"[]\",\"[{}]\",train$customDimensions,fixed=TRUE)\ntrain$customDimensions<-gsub(\"'\",\"\\\"\",train$customDimensions,fixed=TRUE)\ntrain$customDimensions<-gsub(\"[\",\"\",train$customDimensions,fixed=TRUE)\ntrain$customDimensions<-gsub(\"]\",\"\",train$customDimensions,fixed=TRUE)\ntrain$customDimensions <- factor(train$customDimensions)\nhead(train$customDimensions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f6aa2075ea8c863104f2b4c3cfba1d7b2a3fe540"},"cell_type":"code","source":"train.customDimensions <- fromJSON(paste('[',paste(train$customDimensions,collapse = ','),']'),flatten = TRUE)\ntrain.customDimensions <- data.frame(apply(train.customDimensions, 2,function(x){as.factor(x)}))\nhead(train.customDimensions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c68575263385e4552c840a35dce0c706e427c3dd","scrolled":true},"cell_type":"code","source":"test$customDimensions<-gsub(\"[]\",\"[{}]\",test$customDimensions,fixed=TRUE)\ntest$customDimensions<-gsub(\"'\",\"\\\"\",test$customDimensions,fixed=TRUE)\ntest$customDimensions<-gsub(\"[\",\"\",test$customDimensions,fixed=TRUE)\ntest$customDimensions<-gsub(\"]\",\"\",test$customDimensions,fixed=TRUE)\ntest$customDimensions <- factor(test$customDimensions)\nhead(test$customDimensions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ba9c180d9797e2e2450d1a8217ea5c1176252071"},"cell_type":"code","source":"test.customDimensions <- fromJSON(paste('[',paste(test$customDimensions,collapse = ','),']'),flatten = TRUE)\ntest.customDimensions <- data.frame(apply(test.customDimensions, 2,function(x){as.factor(x)}))\nhead(test.customDimensions)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ddf6cef2ec620161422a293f2b187ee11543ba33"},"cell_type":"code","source":"train$device<-gsub(\"\\\"\\\"\",\"\\\"\",train$device,fixed=TRUE)\ntrain.device <- fromJSON(paste('[',paste(train$device,collapse = ','),']'),flatten = TRUE)\ntrain.device <- data.frame(apply(train.device, 2,function(x){as.factor(x)}))\nhead(train.device)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"94f133f5bd57d7c65deb2d478fe0e1b1b39326bf"},"cell_type":"code","source":"test$device<-gsub(\"\\\"\\\"\",\"\\\"\",test$device,fixed=TRUE)\ntest.device <- fromJSON(paste('[',paste(test$device,collapse = ','),']'),flatten = TRUE)\ntest.device <- data.frame(apply(test.device, 2,function(x){as.factor(x)}))\nhead(test.device)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"639be99a3613561bf96e32e40381bedbfbfff08c"},"cell_type":"code","source":"train$geoNetwork<-gsub(\"\\\"\\\"\",\"\\\"\",train$geoNetwork,fixed=TRUE)\ntrain.geoNetwork <- fromJSON(paste('[',paste(train$geoNetwork,collapse = ','),']'),flatten = TRUE)\ntrain.geoNetwork <- data.frame(apply(train.geoNetwork, 2,function(x){as.factor(x)}))\nhead(train.geoNetwork)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3cf351a121921460ec6136e1792802473ff083e2"},"cell_type":"code","source":"test$geoNetwork<-gsub(\"\\\"\\\"\",\"\\\"\",test$geoNetwork,fixed=TRUE)\ntest.geoNetwork <- fromJSON(paste('[',paste(test$geoNetwork,collapse = ','),']'),flatten = TRUE)\ntest.geoNetwork <- data.frame(apply(test.geoNetwork, 2,function(x){as.factor(x)}))\nhead(test.geoNetwork)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bf582d9378971b0c0c811ab683a574edc3e426f8"},"cell_type":"code","source":"#train$hits<-gsub(\"(?<=[0-9])\\\"\",\"-inch\",train$hits,perl=TRUE)\n#train$hits<-gsub(\"'(?!\\\\s|[a-zA-Z])\",\"\\\"\",train$hits, perl=TRUE)\n#train$hits<-gsub(\"(?<![a-zA-Z])'\",\"\\\"\",train$hits, perl=TRUE)\n#train$hits<-gsub(\"True\",\"\\\"True\\\"\",train$hits,fixed=TRUE)\n#train$hits<-gsub(\"False\",\"\\\"False\\\"\",train$hits,fixed=TRUE)\n##train$hits<-gsub(\"[]\",\"[{}]\",train$hits,fixed=TRUE)\n#train$hits<-gsub(\"^\\\\[\",\"\",train$hits)\n#train$hits<-gsub(\"\\\\]$\",\"\",train$hits)\n#train$hits<-gsub(\" :\",\":\",train$hits,fixed=TRUE)\n#train$hits<-gsub(\": \",\":\",train$hits,fixed=TRUE)\n#train$hits <- factor(train$hits)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dc7fc02139fa2b524fcf346783b9612974d71aef"},"cell_type":"code","source":"#train.hits <- fromJSON(paste('[',paste(train$hits,collapse = ','),']'),flatten = TRUE)\n#train.hits <- data.frame(apply(train.hits, 2,function(x){as.factor(x)}))\n#head(train.hits,n=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"967d9abcbe898a4000c4f54217ccb5c08e9a7440"},"cell_type":"code","source":"train$totals<-gsub(\"\\\"\\\"\",\"\\\"\",train$totals,fixed=TRUE)\ntrain.totals <- fromJSON(paste('[',paste(train$totals,collapse = ','),']'),flatten = TRUE)\ntrain.totals <- data.frame(apply(train.totals, 2,function(x){as.numeric(x)}))\nhead(train.totals)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3d6ecf5ac039f1f008b90175aba49d4e2803da6d"},"cell_type":"markdown","source":"From Google Analytics Help:\n\ntotals.totalTransactionRevenue: Total transaction revenue, expressed as the value passed to Analytics multiplied by 10^6 (e.g., 2.40 would be given as 2400000).\n\ntotals.transactionRevenue: This field is deprecated. Use \"totals.totalTransactionRevenue\" instead (see above)."},{"metadata":{"trusted":true,"_uuid":"b678689b2e7a570e6127a95c19bc7b5696e617ae"},"cell_type":"code","source":"test$totals<-gsub(\"\\\"\\\"\",\"\\\"\",test$totals,fixed=TRUE)\ntest.totals <- fromJSON(paste('[',paste(test$totals,collapse = ','),']'),flatten = TRUE)\ntest.totals <- data.frame(apply(test.totals, 2,function(x){as.numeric(x)}))\nhead(test.totals)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d4bf3f4660d9bfd50f55e7371dd7fffbdf658077"},"cell_type":"code","source":"train$trafficSource<-gsub(\"\\\"\\\"\",\"\\\"\",train$trafficSource,fixed=TRUE)\ntrain.trafficSource <- fromJSON(paste('[',paste(train$trafficSource,collapse = ','),']'),flatten = TRUE)\ntrain.trafficSource <- data.frame(apply(train.trafficSource, 2,function(x){as.factor(x)}))\nhead(train.trafficSource)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09cf2c90b2a5651b7b56a1b90ec68a89b53caaa7"},"cell_type":"code","source":"test$trafficSource<-gsub(\"\\\"\\\"\",\"\\\"\",test$trafficSource,fixed=TRUE)\ntest.trafficSource <- fromJSON(paste('[',paste(test$trafficSource,collapse = ','),']'),flatten = TRUE)\ntest.trafficSource <- data.frame(apply(test.trafficSource, 2,function(x){as.factor(x)}))\nhead(test.trafficSource)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5130f171ff8990650c0942d2e7a61dfc58ae5592"},"cell_type":"code","source":"train_data <- cbind(train,train.customDimensions,train.device,train.geoNetwork,train.totals,train.trafficSource)\ntrain_data <- train_data[,-c(2,4,6,8,9)]\ntrain_data$date <- as.Date(as.character(train_data$date), format= \"%Y%m%d\")\nclass(train_data$visitStartTime) <- c('POSIXt','POSIXct')\ntrain_data$channelGrouping <- as.factor(train_data$channelGrouping)\ntrain_data$fullVisitorId <- as.factor(train_data$fullVisitorId)\ntrain_data$socialEngagementType <- as.factor(train_data$socialEngagementType)\ntrain_data$visitId <- as.factor(train_data$visitId)\nprint(dim(train_data))\nstr(train_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cc27270704d8e663024b115228747e05a62050f9"},"cell_type":"code","source":"test_data <- cbind(test,test.customDimensions,test.device,test.geoNetwork,test.totals,test.trafficSource)\ntest_data <- test_data[,-c(2,4,6,8,9)]\ntest_data$date <- as.Date(as.character(test_data$date), format= \"%Y%m%d\")\nclass(test_data$visitStartTime) <- c('POSIXt','POSIXct')\ntest_data$channelGrouping <- as.factor(test_data$channelGrouping)\ntest_data$fullVisitorId <- as.factor(test_data$fullVisitorId)\ntest_data$socialEngagementType <- as.factor(test_data$socialEngagementType)\ntest_data$visitId <- as.factor(test_data$visitId)\nprint(dim(test_data))\nstr(test_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b62ef59823f1fdfb61ce33ebb954ad0466688963"},"cell_type":"code","source":"head(train_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"016591fb9f929c356096df87b558343de176d705"},"cell_type":"code","source":"summary(train_data)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"976a3569f54b1f7c584cdb616ce3f0a690cbe63c"},"cell_type":"markdown","source":"1.  socialEngagementType is constant; can be dropped\n2. we can keep either of date or visitStartTime and drop the other\n3. browserVersion is 'not available' for all observations; can be dropped\n4. browserSize is 'not available' for all observations; can be dropped\n5. operatingSystemVersion is 'not available' for all observations; can be dropped\n6. mobileDeviceBranding is 'not available' for all observations; can be dropped\n7. mobileDeviceModel is 'not available' for all observations; can be dropped\n8. mobileInputSelector is 'not available' for all observations; can be dropped\n9. mobileDeviceInfo is 'not available' for all observations; can be dropped\n10. mobileDeviceMarketingName is 'not available' for all observations; can be dropped\n11. flashVersion is 'not available' for all observations; can be dropped\n12. language is 'not available' for all observations; can be dropped\n13. screenColors is 'not available' for all observations; can be dropped\n14. screenResolution is 'not available' for all observations; can be dropped\n15. cityId is 'not available' for all observations; can be dropped\n16. latitude is 'not available' for all observations; can be dropped\n17. longitude is 'not available' for all observations; can be dropped\n18. networkLocation is 'not available' for all observations; can be dropped\n19. campaignCode is all NA except 1; can be dropped\n20. adwordsClickInfo.criteriaParameters is 'not available' for all observations; can be dropped\n21. index is all NA except for 4 observations; can be dropped\n22. visits is constant; can be dropped\n23. bounces is mostly missing and is constant wherever available; can be dropped\n24. newVisits is mostly missing and is constant wherever available; can be dropped\n25. drop the other Id features except fullVisitorId, viz. visitId, adwordsClickInfo.gclId"},{"metadata":{"trusted":true,"_uuid":"f752c02f75b73a154fc81a2043dd2c1ea2019834","scrolled":true},"cell_type":"code","source":"train_data<-select(train_data,-socialEngagementType, -visitStartTime,\n                  -browserVersion, -browserSize, -operatingSystemVersion,\n                  -mobileDeviceBranding, -mobileDeviceModel, -mobileInputSelector,\n                  -mobileDeviceInfo, -mobileDeviceMarketingName, -flashVersion,\n                  -language, -screenColors, -screenResolution, -cityId,\n                  -latitude, -longitude, -networkLocation, -campaignCode, \n                   -adwordsClickInfo.criteriaParameters, -index, -visits, -bounces,\n                  -newVisits, -visitId, -adwordsClickInfo.gclId)\ndim(train_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1ef820b0e4a6f3de0e195c145eeb37e82d4f5492","scrolled":true},"cell_type":"code","source":"test_data<-select(test_data,-socialEngagementType, -visitStartTime,\n                  -browserVersion, -browserSize, -operatingSystemVersion,\n                  -mobileDeviceBranding, -mobileDeviceModel, -mobileInputSelector,\n                  -mobileDeviceInfo, -mobileDeviceMarketingName, -flashVersion,\n                  -language, -screenColors, -screenResolution, -cityId,\n                  -latitude, -longitude, -networkLocation, \n                   -adwordsClickInfo.criteriaParameters, -index, -visits, -bounces,\n                 -newVisits, -visitId, -adwordsClickInfo.gclId)\ndim(test_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2fabc06d3ac3daf0028a046b60e903b0dd3d1112"},"cell_type":"code","source":"### continuous attributes\nnames(train_data)[which(sapply(train_data, is.numeric))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"94de530a2c042e947293af829dc98110f07e3a4d"},"cell_type":"code","source":"### categorical attributes\nnames(train_data)[which(sapply(train_data, is.factor))]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"76c4badbc1a5b2fcf26a8bd21e1d89a6190f94f1"},"cell_type":"code","source":"cbind(sort(colSums(is.na(train_data)),decreasing=TRUE))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"17b689bbc101223a512dfa2a8487880ff5421452"},"cell_type":"code","source":"library(Hmisc)\ntrain_data$transactionRevenue <- impute(train_data$transactionRevenue,0)\ntrain_data$totalTransactionRevenue <- impute(train_data$totalTransactionRevenue,0)\ntrain_data$transactions <- impute(train_data$transactions,0)\ntrain_data$adContent <- impute(train_data$adContent)\ntrain_data$adwordsClickInfo.page <- impute(train_data$adwordsClickInfo.page)\ntrain_data$adwordsClickInfo.slot <- impute(train_data$adwordsClickInfo.slot)\ntrain_data$adwordsClickInfo.adNetworkType <- impute(train_data$adwordsClickInfo.adNetworkType)\ntrain_data$adwordsClickInfo.isVideoAd <- impute(train_data$adwordsClickInfo.isVideoAd)\ntrain_data$isTrueDirect[is.na(train_data$isTrueDirect) & train_data$source == '(direct)'] <- TRUE\ntrain_data$isTrueDirect <- impute(train_data$isTrueDirect,FALSE)\ntrain_data$isTrueDirect <- factor(train_data$isTrueDirect)\ntrain_data$referralPath <- impute(train_data$referralPath)\ntrain_data$keyword <- impute(train_data$keyword)\ntrain_data$timeOnSite <- impute(train_data$timeOnSite,0)\ntrain_data$sessionQualityDim <- impute(train_data$sessionQualityDim,0)\ntrain_data$value <- impute(train_data$value)\ntrain_data$pageviews <- impute(train_data$pageviews,0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1211967e768856fbaf261554849cafeb96b91bba"},"cell_type":"code","source":"cbind(sort(colSums(is.na(test_data)),decreasing=TRUE))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2d69a3eb95361479ca8b75b8465680fbd8ef81dc"},"cell_type":"code","source":"test_data$transactionRevenue <- impute(test_data$transactionRevenue,0)\ntest_data$totalTransactionRevenue <- impute(test_data$totalTransactionRevenue,0)\ntest_data$transactions <- impute(test_data$transactions,0)\ntest_data$adwordsClickInfo.page <- impute(test_data$adwordsClickInfo.page)\ntest_data$adwordsClickInfo.slot <- impute(test_data$adwordsClickInfo.slot)\ntest_data$adwordsClickInfo.adNetworkType <- impute(test_data$adwordsClickInfo.adNetworkType)\ntest_data$adwordsClickInfo.isVideoAd <- impute(test_data$adwordsClickInfo.isVideoAd)\ntest_data$isTrueDirect[is.na(test_data$isTrueDirect) & test_data$source == '(direct)'] <- TRUE\ntest_data$isTrueDirect <- impute(test_data$isTrueDirect,FALSE)\ntest_data$isTrueDirect <- factor(test_data$isTrueDirect)\ntest_data$timeOnSite <- impute(test_data$timeOnSite,0)\ntest_data$value <- impute(test_data$value)\ntest_data$keyword <- impute(test_data$keyword)\ntest_data$pageviews <- impute(test_data$pageviews,0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2c99e0a0453d80a30a7c31a5c383df1bcdf4a738"},"cell_type":"code","source":"# --- write to csv the processed dataset\nwrite.csv(train_data, \"train_v2_clean.csv\",row.names = FALSE)\nwrite.csv(test_data, \"test_v2_clean.csv\",row.names = FALSE)","execution_count":null,"outputs":[]}],"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}