CODEWITHSUNDEEP
rjsonjsonlite
Eric Green
Eric Green

Reputation: 7735

parse json with embedded lists into semi-long dataframe

I have a json file with several layers of nesting, and I am struggling to get it into a workable dataframe. I created a toy example of mock data based on a real structure: here is the gist.

And here is my desired output. The output could be "longer" or have additional variables from the original json, but I'm showing the core ask.

enter image description here

This is the part of the json that shows the deepest level of nesting that I want to get into a semi-long format as shown above in white (a fully wide format would be fine).

enter image description here

I've tried lots of things with this object:

myList <- jsonlite::fromJSON("example.json", flatten=TRUE)$results

from trying to subset [][[]] and cbind(), to other efforts trying to unnest the embedded lists. Nothing quite right. I'd benefit greatly from advice on the best approach.

Upvotes: 0

Views: 58

Answers (1)

hrbrmstr
hrbrmstr

Reputation: 78832

Does this get you any further along? (This is a gnarly structure):

library(tidyverse)

x <- (jsonlite::fromJSON("/Users/hrbrmstr/r7/gh/labs-research/2018-11-portland-ciso-event/example.json"))

jsonlite::stream_out(x$results, con = gzfile("ex-res.json.gz"))

y <- ndjson::stream_in("ex-res.json.gz", "tbl")

gather(y, path, path_val, starts_with("path")) %>%
  gather(flow, flow_val, starts_with("flow")) %>%
  gather(name, name_val, starts_with("values.pdep")) %>%
  gather(intervention, interv_val, starts_with("values.inter")) %>%
  glimpse()
## Observations: 87,696
## Variables: 18
## $ contact.name <chr> "Person 1", "Person 2", "Person 1", "Person 2", "Person 1", "Person 2", "Person 1", "Person 2"...
## $ contact.uuid <chr> "k0dcjs", "rd3jfui", "k0dcjs", "rd3jfui", "k0dcjs", "rd3jfui", "k0dcjs", "rd3jfui", "k0dcjs", ...
## $ created_on   <chr> "2016-02-08T07:00:15.093813Z", "2016-02-08T07:00:15.093813Z", "2016-02-08T07:00:15.093813Z", "...
## $ id           <dbl> 1234, 1235, 1234, 1235, 1234, 1235, 1234, 1235, 1234, 1235, 1234, 1235, 1234, 1235, 1234, 1235...
## $ modified_on  <chr> "2016-02-09T04:42:54.812323Z", "2016-02-08T08:09:51.545160Z", "2016-02-09T04:42:54.812323Z", "...
## $ responded    <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE...
## $ start.uuid   <chr> "dnxh4g", "kfj4dsi", "dnxh4g", "kfj4dsi", "dnxh4g", "kfj4dsi", "dnxh4g", "kfj4dsi", "dnxh4g", ...
## $ uuid         <chr> "esn4dk", "qask9dj", "esn4dk", "qask9dj", "esn4dk", "qask9dj", "esn4dk", "qask9dj", "esn4dk", ...
## $ exit_type    <chr> NA, "completed", NA, "completed", NA, "completed", NA, "completed", NA, "completed", NA, "comp...
## $ exited_on    <chr> NA, "2016-02-08T08:09:51.544998Z", NA, "2016-02-08T08:09:51.544998Z", NA, "2016-02-08T08:09:51...
## $ path         <chr> "path.0.node", "path.0.node", "path.0.time", "path.0.time", "path.1.node", "path.1.node", "pat...
## $ path_val     <chr> "ecb4cb11-6cca-4791-a950-c448e9300846", "ecb4cb11-6cca-4791-a950-c448e9300846", "2016-02-08T07...
## $ flow         <chr> "flow.name", "flow.name", "flow.name", "flow.name", "flow.name", "flow.name", "flow.name", "fl...
## $ flow_val     <chr> "weeklyratings", "weeklyratings", "weeklyratings", "weeklyratings", "weeklyratings", "weeklyra...
## $ name         <chr> "values.pdeps1.category", "values.pdeps1.category", "values.pdeps1.category", "values.pdeps1.c...
## $ name_val     <chr> "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 - 7", "0 -...
## $ intervention <chr> "values.intervention", "values.intervention", "values.intervention", "values.intervention", "v...
## $ interv_val   <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA...

Full approach:

gather(y, path, path_val, starts_with("path")) %>%
  gather(flow, flow_val, starts_with("flow")) %>%
  gather(name, name_val, starts_with("values.pdep")) %>%
  gather(intervention, interv_val, starts_with("values.inter")) %>%
  filter(grepl(".value", name)) %>% 
  filter(grepl("node", path)) %>%
  mutate(variable = gsub("values.", "", name)) %>% 
  mutate(variable = gsub(".value", "", variable)) %>% 
  distinct(contact.name, uuid, name, .keep_all = TRUE) %>% 
  select(id, uuid, contact.uuid, variable, name_val, created_on, modified_on) %>% 
  arrange(id, created_on) # optional wide %>% spread(variable, name_val)

Upvotes: 2

Related Questions