Romy - 1 year ago 74

JSON Question

I don't know how to properly convert my JSON data into a useful R dataframe. 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:

How can I get this in the proper format?

Answer Source

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}}
]}]}'
```