Converting JSON to data frame with proper column names

Question

I am trying to convert the json below into a data frame:

data_toy.json = 
{
    "department": {
        "BME": [
            {
                "course_name": "BMD_ENG_250-0_20 Thermodynamics",
                "instructor": "Neha Kamat",
                "instructor_gender": "F"
            }
        ]
    }
}

However, when I run

library(jsonlite)
dat <- as.data.frame(fromJSON('data_toy.json'))

I get:

> str(dat)
'data.frame':   1 obs. of  3 variables:
 $ department.BME.course_name      : chr "BMD_ENG_250-0_20 Thermodynamics"
 $ department.BME.instructor       : chr "Neha Kamat"
 $ department.BME.instructor_gender: chr "F"

How do I:

1) Add a column for department?

2) Make the columns: department, course_name, instructor, instructor_gender?

maybe `library(tidyverse); jsonlite::fromJSON(data_toy.json) %>% pluck(1) %>% imap_dfr(~mutate(.x, department = .y))`, but a reprex with more than one row of data would be better — alistaire, Dec 01 '18 at 18:09
That seems to work for the toy example. But when I use the full json, I get `Error in UseMethod("mutate_") : no applicable method for 'mutate_' applied to an object of class "character"` — Adam_G, Dec 01 '18 at 18:21
Like I said, I don't think your example is big enough to be representative of your problem. — alistaire, Dec 01 '18 at 19:07

score 1 · Accepted Answer · answered Dec 01 '18 at 22:09

I think you need to post more of your data but:

data_toy.json = '{"department": {"BME": [{"course_name": "BMD_ENG_250-0_20 Thermodynamics","instructor": "Neha Kamat","instructor_gender": "F"}]}}'

dat <- jsonlite::fromJSON(data_toy.json, simplifyVector = FALSE, simplifyDataFrame = FALSE, flatten=FALSE)

str(dat)
## List of 1
##  $ department:List of 1
##   ..$ BME:List of 1
##   .. ..$ :List of 3
##   .. .. ..$ course_name      : chr "BMD_ENG_250-0_20 Thermodynamics"
##   .. .. ..$ instructor       : chr "Neha Kamat"
##   .. .. ..$ instructor_gender: chr "F"

Base R:

do.call(
  rbind.data.frame,
  unname(lapply(dat, function(x) {

    tdf <- as.data.frame(x[[1]], stringsAsFactors=FALSE)
    tdf$department <- names(x)[1]
    tdf

  }))
)
##                       course_name instructor instructor_gender department
## 1 BMD_ENG_250-0_20 Thermodynamics Neha Kamat                 F        BME

If you're stuck in the tidyverse:

library(purrr)

map_df(dat, ~{
  flatten_df(.x[[1]]) %>%
    dplyr::mutate(department = names(.x)[1])
})
## # A tibble: 1 x 4
##   course_name                     instructor instructor_gender department
##   <chr>                           <chr>      <chr>             <chr>     
## 1 BMD_ENG_250-0_20 Thermodynamics Neha Kamat F                 BME

Sorry, I had to step out for a few hours. I'll add a much better MWE. — Adam_G, Dec 02 '18 at 00:14
I take that back. I'm getting a new error now, and will just post a new question. — Adam_G, Dec 02 '18 at 00:15
Here's the new question: https://stackoverflow.com/questions/53576269/converting-nested-json-to-data-frame — Adam_G, Dec 02 '18 at 00:25

Converting JSON to data frame with proper column names

1 Answers1