将嵌套的JSON转换为数据框 [英] Converting nested JSON to data frame

问题描述

我正在尝试将大量嵌套的json转换为适当的数据帧(按整洁的标准进行处理).在问题的末尾复制了json的MWE，因为我想确保它捕获了json的每个元素.

I am trying to convert a heavily nested json into a proper data frame (proper by tidy standards). An MWE of the json is copied at the end of the question, as I wanted to make sure it captured every element of the json.

我尝试过:

library(jsonlite)
library(tidyverse)
dat <- jsonlite::fromJSON('data_toy.json') %>% pluck(1) %>% imap_dfr(~mutate(.x, department = .y))

但这返回:

Error: Columns `time_spent`, `school_breakdown`, `reason_for_taking_course`, `student_years`, `interest_before` must be 1d atomic vectors or lists

我也尝试过:

dat <- jsonlite::fromJSON('data_toy.json', simplifyVector = FALSE, 
                          simplifyDataFrame = FALSE, flatten=FALSE)
dat.df <- map_df(dat, ~{
  flatten_df(.x[[1]]) %>%
    dplyr::mutate(department = names(.x)[1])
})

但这返回:

Error in bind_rows_(x, .id) : Argument 3 must be length 1, not 0

如何将其转换为数据框?

How can I convert this to a data frame?

数据文件(data_toy.json):

{
    "department": {
        "BME": [
            {
                "course_name": "BMD_ENG_250-0_20: Thermodynamics",
                "instructor": "Neha Kamat",
                "time_spent": {},
                "school_breakdown": {
                    "Education & SP": 0,
                    "Communication": 0,
                    "Graduate School": 0,
                    "KGSM": 0
                },
                "reason_for_taking_course": {
                    "Distribution requirement": 0,
                    "Major/Minor requirement": 53
                },
                "student_years": {
                    "Freshman": 5,
                    "Sophomore": 37
                },
                "interest_before": {
                    "1-Not interested at all": 1,
                    "2": 5
                },
                "comments": [
                    "is amazing and you will love her!",
                    "Prof. is so nice"
                ],
                "instructor_gender": "F"
            },
            {
                "course_name": "BMD_ENG_250-0_20: Thermodynamics",
                "instructor": "Neha Kamat",
                "time_spent": {},
                "school_breakdown": {
                    "Education & SP": 0,
                    "Communication": 0,
                    "Graduate School": 0,
                    "KGSM": 0
                },
                "reason_for_taking_course": {
                    "Distribution requirement": 0,
                    "Major/Minor requirement": 53
                },
                "student_years": {
                    "Freshman": 5,
                    "Sophomore": 37
                },
                "interest_before": {
                    "1-Not interested at all": 1,
                    "2": 5
                },
                "comments": [
                    "is amazing and you will love her!",
                    "Prof. is so nice"
                ],
                "instructor_gender": "F"
            }
        ],
        "LING": [
            {
                "course_name": "BMD_ENG_250-0_20: Thermodynamics",
                "instructor": "Neha Kamat",
                "time_spent": {},
                "school_breakdown": {
                    "Education & SP": 0,
                    "Communication": 0,
                    "Graduate School": 0,
                    "KGSM": 0
                },
                "reason_for_taking_course": {
                    "Distribution requirement": 0,
                    "Major/Minor requirement": 53
                },
                "student_years": {
                    "Freshman": 5,
                    "Sophomore": 37
                },
                "interest_before": {
                    "1-Not interested at all": 1,
                    "2": 5
                },
                "comments": [
                    "is amazing and you will love her!",
                    "Prof. is so nice"
                ],
                "instructor_gender": "F"
            },
            {
                "course_name": "BMD_ENG_250-0_20: Thermodynamics",
                "instructor": "Neha Kamat",
                "time_spent": {},
                "school_breakdown": {
                    "Education & SP": 0,
                    "Communication": 0,
                    "Graduate School": 0,
                    "KGSM": 0
                },
                "reason_for_taking_course": {
                    "Distribution requirement": 0,
                    "Major/Minor requirement": 53
                },
                "student_years": {
                    "Freshman": 5,
                    "Sophomore": 37
                },
                "interest_before": {
                    "1-Not interested at all": 1,
                    "2": 5
                },
                "comments": [
                    "is amazing and you will love her!",
                    "Prof. is so nice"
                ],
                "instructor_gender": "F"
            }
        ]
    }
}

推荐答案

在这里使用flatten = TRUE似乎是关键:

Using flatten = TRUE seems to be the key here:

dat <- jsonlite::fromJSON('data_toy.json', flatten = TRUE)[[1]]
dat %>% bind_rows() %>% mutate(department = rep(names(dat), map_dbl(dat, nrow)))

这篇关于将嵌套的JSON转换为数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！