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
– IRTFM, Commented Oct 21, 2014 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
– Hamy, Commented Oct 21, 2014 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
– Rich Scriven, Commented Oct 22, 2014 at 2: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
– geotheory, Commented Jul 4, 2017 at 9:13

Mahdi Jadaliha · Accepted Answer · 2018-06-05 19:41:29Z

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 5, 2018 at 19:41

Mahdi Jadaliha

1,9871 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5359531 Over a year ago

There is no such function. > flatten() Error in flatten() : could not find function "flatten"

Mahdi Jadaliha Over a year ago

probably you did not read the title, check jsonlite::flatten(). this function exists in jsonlite package.

IRTFM · Accepted Answer · 2014-10-21 23:55:55Z

2

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) )

answered Oct 21, 2014 at 23:55

IRTFM

264k22 gold badges381 silver badges503 bronze badges

Comments

Ramnath · Accepted Answer · 2014-10-22 15:41:04Z

1

Another oneliner

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

answered Oct 22, 2014 at 15:41

Ramnath

55.9k16 gold badges129 silver badges155 bronze badges

Collectives™ on Stack Overflow

How to flatten nested data frames returned from jsonlite

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related