Iterating through JSON elements to fix missing values using R and jsonlite

Question

I'm working with a JSON file and trying to flatten it into a data.frame using the jsonlite package.

The main set of objects in the array don't have a key, and so instead of flattening into rows, it's creating a column for each object (and its key-value pairs).

How can I iterate through the JSON object to add a key to each object value so that I can view this data as a readable data.frame?

This is the code I'm using:

jsonfile <- jsonlite::fromJSON("filename"), simplifyDataFrame=TRUE)

And so this:

{
  "SUCCESS ACADEMY": {
    "districtName": "SUCCESS SD",
    "isPublic": true
  },
  "FAILURE ACADEMY": {
    "districtName": "FAIL SD",
    "isPublic": true
  }
}

Turns into this:


+------------------------------+--------------------------+------------------------------+--------------------------+
| SUCCESS ACADEMY.districtName | SUCCESS ACADEMY.isPublic | Failure Academy.districtName | Failure Academy.isPublic |
+------------------------------+--------------------------+------------------------------+--------------------------+

When I want this instead:

+-----------------+--------------+----------+
|   School Name   | districtName | isPublic |
+-----------------+--------------+----------+
| SUCCESS ACADEMY | SUCCESS SD   | true     |
| FAILURE ACADEMY | FAIL SD      | true     |
+-----------------+--------------+----------+

score 1 · Accepted Answer · answered Nov 14 '20 at 14:44

1

There may be a direct way but treating it as a list of data frames thne binding them together seems adequate:

library(data.table)
library(magrittr)

jsonlite::fromJSON(txt= '{
  "SUCCESS ACADEMY": {
    "districtName": "SUCCESS SD",
    "isPublic": true
  },
  "FAILURE ACADEMY": {
    "districtName": "FAIL SD",
    "isPublic": true
  }
}') %>% 
  lapply(as.data.table) %>%
  rbindlist(idcol = "School_name")

answered Nov 14 '20 at 14:44

Hugh

15,521
12
57
100

This was perfect for me, thanks Hugh. Is it `lapply` that treats each item as a data frame, and then `rbindlist` that binds them together? – ezra Nov 14 '20 at 16:51

Iterating through JSON elements to fix missing values using R and jsonlite

1 Answers1