GeorgeMeSa GeorgeMeSa - 3 months ago 31
R Question

how to create a data.frame in R from a complex JSON file and loop of files with this

I am kind of new to the loops in R and also to transform from Json to data.frame thing.

I have a data in json file like this:

{
"players": {
"111111": {
"tLastEvent": 1432523322.429,
"tActive": 30793.195000172,
"tSocial": 1915.0230002403,
"nBlockBreak": 2315,
"nBlockPlace": 3051,
"sMove": 141554.58326606,
"loc": {
"w": "111111",
"x": 222222,
"y": 222222,
"z": 2222222
},
"social": {
"22222": 42.579999923706,
"333333": 2318.8910028934,
"44444": 440.19000005722,
"55555": 5417.4040002823,
"66666": 913.04000043869,
"7777": 1737.2639997005,
"88888": 419.68400025368,
"6666666": 972.16700005531,
"999999": 241.88300061226
}


}

Currently I got some good ideas from here and I got this far

origininfo<-fromJSON("stats_100_players.json")
socialpl<-origininfo$players[[1]]$tSocial
socialinfo <- as.data.frame(lapply(socialpl,rbind))
colnames(socialinfo, prefix = "social")


As a result I get a small one that for that row:

structure.1915.0230002403...Dim...c.1L..1L..
1
1915.023


Of course I have more players and the structure is the same, but how should I collapse all?

So Here is my other questions, What can I change to add the other variables? How can I do this for 30 files without having to write the code each time (all have the same structure?

Thanks in advance.

Answer

Consider a defined function with lapply() on the list of json files pulled from a directory. In building the list for dataframe conversion, you would need to unlist() the nested items: loc and social:

library(jsonlite)

mypath = "path/to/json/files"
jsonfiles <- list.files(path = mypath, pattern=".json", full.names=TRUE)

jsonparse <- function(file){
  origininfo <- fromJSON(file) 

  temp <- c(list(player = names(origininfo[[1]])),
            origininfo$players[[1]][1:6], 
            lapply(origininfo$players[[1]]$loc, unlist),
            lapply(origininfo$players[[1]]$social, unlist))
  df <- data.frame(temp, stringsAsFactors = FALSE)

}

dfList <- lapply(jsonfiles, jsonparse)

finaldf <- do.call(rbind, dfList)

colnames(df) <- colnames(df, prefix="social")

Output (using example posted)

str(finaldf)

#  'data.frame' : 1 obs. of  20 variables:
#  $ player     : chr "111111"
#  $ tLastEvent : num 1.43e+09
#  $ tActive    : num 30793
#  $ tSocial    : num 1915
#  $ nBlockBreak: int 2315
#  $ nBlockPlace: int 3051
#  $ sMove      : num 141555
#  $ w          : chr "111111"
#  $ x          : int 222222
#  $ y          : int 222222
#  $ z          : int 2222222
#  $ X22222     : num 42.6
#  $ X333333    : num 2319
#  $ X44444     : num 440
#  $ X55555     : num 5417
#  $ X66666     : num 913
#  $ X7777      : num 1737
#  $ X88888     : num 420
#  $ X6666666   : num 972
#  $ X999999    : num 242