Romy Romy - 2 months ago 10
R Question

How to convert complex JSON data into a dataset in R

I don't know how to properly convert my JSON data into a useful R dataset. This is some example data that shows the structure of my data:

{
"data":[
{"track":[
{"time":"2015","midpoint":{"x":6,"y":8},"realworld":{"x":1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":6,"y":8},"realworld":{"x":1,"y":3},"coordinate":{"x":16,"y":37}},
{"time":"2016","midpoint":{"x":6,"y":9},"realworld":{"x":2,"y":3},"coordinate":{"x":16,"y":38}}
]},
{"track":[
{"time":"2015","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2016","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":3,"y":15},"realworld":{"x":-9,"y":2},"coordinate":{"x":17,"y":38}}
]},
{"track":[
{"time":"2015","midpoint":{"x":6,"y":7},"realworld":{"x":-2,"y":3},"coordinate":{"x":16,"y":39}}
]}]}


I have a lot of tracks and I'd like the dataset to look like this:


track time midpoint realworld coordinate
1
1
1
2
2
2
2
3


So far i have this:

json_file <- "testdata.json"
data <- fromJSON(json_file)
data2 <- list.stack(data, fill=TRUE)


Right now it comes out like this:
data output

How can I get this in the proper format?

Answer

Add the flatten = TRUE parameter when reading with fromJSON. This will give you a nested list with in it's deepest level a list of three dataframes. Using:

library(jsonlite)
# read the json
jsondata <- fromJSON(txt, flatten = TRUE)

# bind the dataframes in the nested 'track' list together    
dat <- do.call(rbind, jsondata$data$track)

# add a track variable
dat$track <- rep(1:length(jsondata$data$track), sapply(jsondata$data$track, nrow))

gives:

> dat
  time midpoint.x midpoint.y realworld.x realworld.y coordinate.x coordinate.y track
1 2015          6          8           1           3           16           38     1
2 2015          6          8           1           3           16           37     1
3 2016          6          9           2           3           16           38     1
4 2015          5          9          -1           3           16           38     2
5 2015          5          9          -1           3           16           38     2
6 2016          5          9          -1           3           16           38     2
7 2015          3         15          -9           2           17           38     2
8 2015          6          7          -2           3           16           39     3

Another, shorter, approach is using jsonlite in combination with rbindlist from the data.table package:

library(jsonlite)
library(data.table)
# read the json
jsondata <- fromJSON(txt, flatten = TRUE)
# bind the dataframes in the nested 'track' list together 
# and include an id-column at the same time   
dat <- rbindlist(jsondata$data$track, idcol = 'track')

or with bind_rows from the dplyr package in a similar way:

library(dplyr)
dat <- bind_rows(jsondata$data$track, .id = 'track')

Used data:

txt <- '{
"data":[
{"track":[
{"time":"2015","midpoint":{"x":6,"y":8},"realworld":{"x":1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":6,"y":8},"realworld":{"x":1,"y":3},"coordinate":{"x":16,"y":37}},
{"time":"2016","midpoint":{"x":6,"y":9},"realworld":{"x":2,"y":3},"coordinate":{"x":16,"y":38}}
]},
{"track":[
{"time":"2015","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2016","midpoint":{"x":5,"y":9},"realworld":{"x":-1,"y":3},"coordinate":{"x":16,"y":38}},
{"time":"2015","midpoint":{"x":3,"y":15},"realworld":{"x":-9,"y":2},"coordinate":{"x":17,"y":38}}
]},
{"track":[
{"time":"2015","midpoint":{"x":6,"y":7},"realworld":{"x":-2,"y":3},"coordinate":{"x":16,"y":39}}
]}]}'
Comments