#I created this kernel as a resource for those looking to parse the nested tables in the `hits` column. #The approach I took was to extract the nested tables from the `hits` column and convert them into dataframes with keys linking the contents back to train.csv. #I also parse all remaining columns so the training dataset is fully ready for EDA, feature engineering and model training. #I'm a coder in training so please excuse any obvious inefficiencies. In fact, I would greatly appreciate any tips, suggestions, or code improvements- especially re. vectorization. #One thing I would greatly appreciate are suggestions regarding alternative functions/approaches to reduce CPU time. I use for loops which have significant time CPU for even a 20k sample of the train.csv. #Importing necessary libaries library(jsonlite) library(plyr) library(stringr) #Due to the size of the training dataset, I'll only bring in enough rows to demonstrate the code. train <- read.csv("../input/train_v2.csv", header = T, stringsAsFactors = F, colClasses = c(fullVisitorId = "character"), nrow = 20000) #Taking a peek at the data. head(train) #Lots of text in JSON format and some that can be connvered to JSON format (customDimensions). str(train) #Parsing JSON the quicker way. This was taken from https://www.kaggle.com/mrlong/r-flatten-json-columns-to-make-single-data-frame. #This creates a large character object which can be parsed by fromJSON. #paste("[",paste(train$`anyJSONcolumn`, collapse=","),"]") #Prepare customDimensions table. This table will be merged with training later. train$customDimensions <- gsub("\'", "\"", train$customDimensions) #Using gsub function to replace single quotes with double quotes- necessary for JSON parsing with fromJSON. train$customDimensions <- gsub("\\[\\]", "[{}]", train$customDimensions) #Replacing empty lists with {}, required for JSON parsing. train$customDimensions <- gsub("\\[|\\]", "", train$customDimensions) #Removing [] from each object, also required for fromJSON to work. customDimensions <- lapply(paste("[",train$customDimensions,"]"),function(x) fromJSON(x, flatten=T)) #Glorious flattening using fromJSON and converting to dataframe. customDimensions <- do.call("rbind.fill", customDimensions) #Include system.time wrapper to keep an eye on efficiencies. Also apending dataframes created from flatten. head(customDimensions) #Check dataframe for issues. dim(customDimensions) #Checking to ensure size is consistent with training dataset. train$customDimensions <- NULL #Prepare to parse the gargantuan hits column. hitNumberlist <- str_extract_all(train$hits, "(?<='hitNumber': ')([0-9]*)") #I created a separate nested list containing the hits numbers for each visit. There's definitely a better way to do this. #The `hits` column contains seven nested tables, but only two are not empty (based on the first 20k rows). The first is the product table. train$hits <- gsub("(\'product\': \\[\\])", "\'product\': \\[\\{\\}\\]", perl = T, train$hits) #Fill empty dict with {}. for (i in 1:length(train$hits)) { #This code places what I think are four attributes from the train dataset into the new product table which form a unique key for each row in the train dataset. This way, any analysis/aggregation on the nested tables can be linked back to the source row. train$hits[i] <- gsub("\'productSKU\'", paste0("\'visitStartTime\': \'",train$visitStartTime[i], "\', \'productSKU\'"), train$hits[i]) train$hits[i] <- gsub("\'visitStartTime\'", paste0("\'visitNumber\': \'",train$visitNumber[i], "\', \'visitStartTime\'"), train$hits[i]) train$hits[i] <- gsub("\'visitNumber\'", paste0("\'visitId\': \'",train$visitId[i], "\', \'visitNumber\'"), train$hits[i]) train$hits[i] <- gsub("\'visitId\'", paste0("\'fullVisitorId\': \'",train$fullVisitorId[i], "\', \'visitId\'"), train$hits[i]) } product <- str_extract_all(train$hits, "(\\{\'fullVisitorId\': )(.*?(\\}\\]))") #Extracting the `product` table from `hits`, `productSKU` is a unique identifier for each item in the dataframe. for (i in 1:length(product)) { #Each element in train$hits can contain multiple "hits" (blobs of data denoted by `hitNumber`). This is also a unique identifier and we want to make sure it stays with each dataframe we create. for(j in 1:length(product[[i]])) { product[[i]][j] <- gsub("\'productSKU\'", paste0("\'hitNumber4SKU\': \'",hitNumberlist[[i]][j],"\', \'productSKU\'"), product[[i]][j]) } } #Extracting each row from the `product` table. product <- rapply(product, function(x) gsub("(\'v2ProductCategory\': \'\\$\\{)(.*?\\})", "\'v2ProductCategory\': \'NA", x), how = "list") #Replacing dubious values in `v2ProductCategory` which conflict with extraction code. productsku <- str_extract_all(product,"(\\{\'fullVisitorId\')(.*?\\})") #Extracting each productSKU row into a nested list element. rm(product) #No longer needed. #A series of gsub operations to prep data for parsing. Credit goes to Alexandros Papageorgiou, the author of this notebook https://www.kaggle.com/alpapag/approach-for-working-with-the-hits-column-in-r. Some of this may be irrelevant as I've coded this in stages to address issues I ecountered with each approach. productsku <- rapply(productsku, function(x) gsub("\\", "", fixed=T, x), how = "list") #Removing slashes. productsku <- rapply(productsku, function(x) gsub("\"", "\'", x), how = "list") #Double quotes to single quotes before converting all single quotes to double qoutes. productsku <- rapply(productsku, function(x) gsub("\'(?![a-z])|(?<=\\{|\\s)\'", "\"", perl = T, x), how = "list") #All single quotes except apostrophes to double quotes. productsku <- rapply(productsku, function(x) gsub("True", "\"True\"", x), how = "list") #True to logical. #productsku <- gsub("\" : \"", "\":\"", productsku) #Remove extra spaces. DIdn't end up using this. productsku <- rapply(productsku, function(x) gsub("(?