JSON parsing and nested lists

General
1

I am trying to import some mildly complex JSON data into R for analysis, and spinning my wheels a bit. The JSON looks something like this:

[
  {
    "name":"joe",
    "scores": [
      {"type":"a","value":1},
      {"type":"b","value":0}
    ]
  },
  {
    "name":"bob",
    "scores": [
      {"type":"a","value":0},
      {"type":"b","value":1}
    ]
  },
  {
    "name":"sue",
    "scores": [
      {"type":"a","value":1},
      {"type":"b","value":0}
    ]
  }
]

The goal is to transform this into a data frame with names, and a column for each score type. Loading the data is trivial:

library(jsonlite)

df <- fromJSON("test.json")

But I cannot wrap my head around how to work with the nested dataframes this generates. I've tried using jsonlite::flatten(), but it doesn't seem to do anything:

> flatten(df)$scores
[[1]]
  type value
1    a     1
2    b     0

[[2]]
  type value
1    a     0
2    b     1

[[3]]
  type value
1    b     0
2    a     1

I've tried just unlist()ing the scores, but reshaping that unlisted data back into something useful is eluding me as well:

> unlist(df$scores)
 type1  type2 value1 value2  type1  type2 value1 value2  type1  type2 value1 value2 
   "a"    "b"    "1"    "0"    "a"    "b"    "0"    "1"    "b"    "a"    "0"    "1"

I'm still fairly new to R, so apologies if I'm missing something obvious here. What's the best way to approach this?

2

Hi, does this do what you want?

res <- jsonlite::fromJSON(txt = '[
  {
    "name":"joe",
    "scores": [
      {"type":"a","value":1},
      {"type":"b","value":0}
    ]
  },
  {
    "name":"bob",
    "scores": [
      {"type":"a","value":0},
      {"type":"b","value":1}
    ]
  },
  {
    "name":"sue",
    "scores": [
      {"type":"a","value":1},
      {"type":"b","value":0}
    ]
  }
]')

tidyr::unnest(res, scores)
#>   name type value
#> 1  joe    a     1
#> 2  joe    b     0
#> 3  bob    a     0
#> 4  bob    b     1
#> 5  sue    a     1
#> 6  sue    b     0

Created on 2018-10-23 by the reprex package (v0.2.1)

4 Likes
3

Yes, that's exactly what I was looking for. I could've sworn I tried it before, but obviously not. Thanks!

1 Like