How to flatten nested data frames returned from jsonlite

Question

I am loading this JSON data with jsonlite

<snip>  
"rawData": {
    "fortune": {}, 
    "plaintext": {}, 
    "db": {}, 
    "update": {
      "duda": [
        {
          "latencyAvg": "201.40us", 
          "latencyMax": "727.00us", 
          "latencyStdev": "54.85us", 
          "totalRequests": 561810, 
          "startTime": 1413890149, 
          "endTime": 1413890164
        }
      ]
      }, 
    "json": {
      "duda": [
        {
          "latencyAvg": "201.40us", 
          "latencyMax": "727.00us", 
          "latencyStdev": "54.85us", 
          "totalRequests": 561810, 
          "startTime": 1413890149, 
          "endTime": 1413890164
        }
      ]
    }, 
    "query": {}
  }

Which results in a structure with nested data frames

data <- structure(list(fortune = structure(list(), .Names = character(0)), 
    plaintext = structure(list(), .Names = character(0)), db = structure(list(), .Names = character(0)), 
    update = structure(list(duda = structure(list(latencyAvg = "201.40us", 
        latencyMax = "727.00us", latencyStdev = "54.85us", totalRequests = 561810L, 
        startTime = 1413890149L, endTime = 1413890164L), .Names = c("latencyAvg", 
    "latencyMax", "latencyStdev", "totalRequests", "startTime", 
    "endTime"), class = "data.frame", row.names = 1L)), .Names = "duda"), 
    json = structure(list(duda = structure(list(latencyAvg = "201.40us", 
        latencyMax = "727.00us", latencyStdev = "54.85us", totalRequests = 561810L, 
        startTime = 1413890149L, endTime = 1413890164L), .Names = c("latencyAvg", 
    "latencyMax", "latencyStdev", "totalRequests", "startTime", 
    "endTime"), class = "data.frame", row.names = 1L)), .Names = "duda"), 
    query = structure(list(), .Names = character(0))), .Names = c("fortune", 
"plaintext", "db", "update", "json", "query"))

I'd like to create a single data.frame that looks like this:

Type   | Name | latencyAvg | latencyMax | latencyStdev | totalRequests | startTime | endTime
json   | duda | 201.40us   | <etc..>
update | duda | 201.40us   | <etc..>

By flatting the nested data frames. I'm figuring out how to do this manually by removing the items I want and using rbind/cbind to move them into a new data frame, but is there a simple way to do this type of recursive flattening?

You need to provide the possible arrangements and names of what will be coming in. Will there always be two named items 'json' and 'update'? — IRTFM, Oct 21 '14 at 23:44
The JSON example fully covers the data I'd expect to see - the first nested frame will have names json,update,query,etc. The second nested frame will have latencyAvg,latencyStdev,etc. No other arrangements of the data from JSON is expected — Hamy, Oct 21 '14 at 23:46
How did you load this with `jsonlite`? I've been trying to load it several times after writing to file and I can't get it to load without error — Rich Scriven, Oct 22 '14 at 02:28
There;s a tidyverse gap here I think - i.e. a solution that doesn't require manual identification of nested elements, such as `tidyr::unnest` does for nested-list columns. — geotheory, Jul 04 '17 at 09:13

score 3 · Answer 1 · answered Jun 05 '18 at 19:41

3

use flatten function. it gets a data frame and returns a flat data frame. if you need to exclude some columns from source data frame then use df[[-i]] to exclude column i.

answered Jun 05 '18 at 19:41

Mahdi Jadaliha

1,947
1
14
22

There is no such function. `> flatten() Error in flatten() : could not find function "flatten"` – user5359531 Jan 10 '19 at 21:16
2

probably you did not read the title, check `jsonlite::flatten()`. this function exists in `jsonlite` package. – Mahdi Jadaliha Jan 11 '19 at 15:03

score 2 · Answer 2 · answered Oct 21 '14 at 23:55

Assuming that object is named the unfortunate name of "data":

newdat <- rbind.data.frame( Type= c(rep("json",   nrow(data$json$duda)), 
                                    rep("update", nrow(data$update$duda)) ), 
                            rbind( data$json$duda, data$update$duda) )

score 1 · Answer 3 · answered Oct 22 '14 at 15:41

1

Another oneliner

do.call(rbind, lapply(data[c('json', 'update')], '[[', 'duda'))

answered Oct 22 '14 at 15:41

Ramnath

54,439
16
125
152

How to flatten nested data frames returned from jsonlite

3 Answers3