Wsrtjtyk

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.

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)