parse json with embedded lists into semi-long dataframe

Question

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.

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

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.

score 2 · Accepted Answer · edited Nov 09 '18 at 20:59

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)

Awesome, @hrbmstr! I was able to take this and get it the rest of the way. Shall I make an edit to your answer to suggest the method? — Eric Green, Nov 09 '18 at 20:01
messy as a comment, but replace `glimpse()` with: `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)` — Eric Green, Nov 09 '18 at 20:30
definitely go ahead and edit it. just glad it helped. i've had my share of gnarly json in the past. — hrbrmstr, Nov 09 '18 at 20:37

parse json with embedded lists into semi-long dataframe

1 Answers1