Collapse list of JSON with nested structures into single data.frame

Question

I'm trying to parse some results from an API. The API returns a JSON result for a given value. The API can only handle one value at a time so I lapply to get all the results. <- If this is a bad start point happy to here a better way!

Each JSON response has some nested info. I'm trying to get the data into one single data.frame with the nested structure resolved.

I've been playing around with jsonlite and purrr for much of the afternoon trying to get this working and ideally resilient.

How does one successfully convert a nested json structure into a data.frame for multiple json documents/records using R?

MRE

library(jsonlite)
a <- LETTERS[1:5]
b <- letters[1:5]
c <- rep(data.frame(d=LETTERS[1:5]),5)

strSetup <- list(a, b, c)
dfSetup  <- data.frame(a, b, c)

jsonStr <- toJSON(rep(strSetup,3))

finalStr <- rbind(dfSetup,dfSetup,dfSetup)

I'm trying to go from jsonStr to finalStr.

Did you try `as.data.frame(fromJSON(jsonStr))`? It show 5 rows, 21 columns which I think is what you want. — fishtank, Feb 16 '16 at 19:11
When you say "The API returns a JSON result for a given value.", do you mean that it returns `toJSON(strSetup)` or `toJSON(dfSetup)`? — Adam Hoelscher, Feb 16 '16 at 19:25
@parfait, jsonStr is intended to be representative of the Json I end up with in a list. Actual contents would be too long. — Steph Locke, Feb 16 '16 at 19:43
@fishtail, unfortunately that puts everything as columns, not converting each Json entry into 5 rows and stacking them — Steph Locke, Feb 16 '16 at 19:45
So should line 9 of your example code be `jsonStr <- rep(toJSON(strSetup),3)` instead? — Adam Hoelscher, Feb 16 '16 at 19:54

score 0 · Accepted Answer · answered Feb 16 '16 at 20:04

If I understand correctly, you are storing all the JSON strings returned by the API into a character vector. That means that line 9 of your example is a little off and this should work.

library(jsonlite)
a <- LETTERS[1:5]
b <- letters[1:5]
c <- rep(data.frame(d=LETTERS[1:5]),5)

strSetup <- list(a, b, c)
dfSetup  <- data.frame(a, b, c)

jsonStr <- rep(toJSON(strSetup),3)

finalStr <- rbind(dfSetup,dfSetup,dfSetup)

finalStr2 <- do.call(
  what = rbind,
  args = lapply(
    X = jsonStr,
    FUN = function(x){
      data.frame(fromJSON(x))
      }
    )
  )

print(all(finalStr == finalStr2))

The caveat there is that names(finalStr) != names(finalStr2). However, I don't think that's avoidable, since you indicated that the JSON string you're receiving from the API does not contain names.

If I misunderstood, please let me know.

It's coming in as a list due to the lapply - each individual call returns a json object and I was looping through the input items to get the responses. I'm giving your code a whirl now though - I should be able to loop to a vector and if that makes life easier I'm all for it. — Steph Locke, Feb 16 '16 at 20:13
Ok. So you're using `lapply` with `FUN` equal to the function that pulls the JSON from the API? Can you wrap `unlist` around the `lapply` to convert it back to a vector? — Adam Hoelscher, Feb 16 '16 at 20:59

Collapse list of JSON with nested structures into single data.frame

MRE

1 Answers1