convert xlsx to json using R
The split function of data.table is highly useful here.
dd <- data.frame( state = c("Alabama", "Alabama", "Alabama", "Alsaka"), city = c("Hoover", "Hoover", "Dothan", "Chugiak"), x = c(1, 2, 3, 4), y = c(5, 6, 7, 8), stringsAsFactors=FALSE)library(data.table)dt <- as.data.table(dd)dt_split <- split(dt, by=c("state", "city"), keep.by=FALSE, flatten=FALSE)You get:
> dt_split$Alabama$Alabama$Hoover x y1: 1 52: 2 6$Alabama$Dothan x y1: 3 7$Alsaka$Alsaka$Chugiak x y1: 4 8Now use jsonlite:
> library(jsonlite)> toJSON(dt_split, dataframe = "columns", pretty=TRUE){ "Alabama": { "Hoover": { "x": [1, 2], "y": [5, 6] }, "Dothan": { "x": [3], "y": [7] } }, "Alsaka": { "Chugiak": { "x": [4], "y": [8] } }}