CODEWITHSUNDEEP
rjsonjsonlite
ezra
ezra

Reputation: 23

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

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

Upvotes: 2

Views: 329

Answers (1)

Hugh
Hugh

Reputation: 16099

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

Upvotes: 1

Related Questions