r - 将嵌套的 JSON 文件转换为 R 数据帧

Question

我正在尝试将具有多层嵌套的 JSON 文件转换为 R 中的数据框。我查看了有关此问题的一些现有问题/答案(例如，Convert JSON to R dataframe 和 Flatten nested JSON to dataframe in R 和 Nested JSON to dataframe in R )，但由于这是我第一次处理 JSON 文件，所以我真的很吃力。

JSON 文件非常大并且包含 Twitter 数据。这是一个显示嵌套结构的示例:

[
{
    "Corpus": "ALM",
    "Tweets": [
        {
            "tweet_id": "521033092132503552",
            "tweet_text": "no tweet text available",
            "date": "no date available",
            "annotations": [
                {
                    "annotator": "annotator00",
                    "annotation": "care"
                },
                {
                    "annotator": "annotator01",
                    "annotation": "care,purity"
                },
                {
                    "annotator": "annotator02",
                    "annotation": "care,purity"
                },
                {
                    "annotator": "annotator03",
                    "annotation": "care"
                }
            ]
        },
        {
            "tweet_id": "537681598989475841",
            "tweet_text": "Wholeheartedly support these protests & acts of civil disobedience & will join when I can! #Ferguson #AllLivesMatter",
            "date": "Wed Nov 26 18:57:37 +0000 2014",
            "annotations": [
                {
                    "annotator": "annotator00",
                    "annotation": "subversion"
                },
                {
                    "annotator": "annotator01",
                    "annotation": "subversion"
                },
                {
                    "annotator": "annotator02",
                    "annotation": "loyalty"
                },
                {
                    "annotator": "annotator03",
                    "annotation": "loyalty,subversion"
                }
            ]
        },

这是同一数据文件的一些输出，但观察结果/推文不同(请注意，由于文件太大，最大的嵌套级别被切断):

list(tweet_id = "500745903054258177", tweet_text = "@MichaelSkolnik Thank you for joining the solidarity effort #DearMikesMom #AllLivesMatter", 
    date = "Sat Aug 16 20:48:21 +0000 2014", annotations = list(
        list(annotator = "annotator01", annotation = "loyalty"), 
        list(annotator = "annotator02", annotation = "loyalty"), 
        list(annotator = "annotator03", annotation = "loyalty"))), 
list(tweet_id = "621859689270120448", tweet_text = "no tweet text available", 
    date = "no date available", annotations = list(list(annotator = "annotator01", 
        annotation = "betrayal"), list(annotator = "annotator02", 
        annotation = "non-moral"), list(annotator = "annotator03", 
        annotation = "fairness"))), list(tweet_id = "551227029874438145", 

我想要的输出是这样的:

   corpus tweet_id               tweet_text              date    annotator           annotation  
1     ALM  5210...  no tweet text available  no date available          00                 care
2     ALM  5210...  no tweet text available  no date available          01         care, purity
3     ALM  5210...  no tweet text available  no date available          02         care, purity
4     ALM  5210...  no tweet text available  no date available          03                 care
5     ALM  5376...  Wholeheartedly suppo...  Wed Nov 26 18:...          00           subversion
6     ALM  5376...  Wholeheartedly suppo...  Wed Nov 26 18:...          01           subversion 
7     ALM  5376...  Wholeheartedly suppo...  Wed Nov 26 18:...          02              loyalty           
8     ALM  5376...  Wholeheartedly suppo...  Wed Nov 26 18:...          03  loyalty, subversion           
...      

如何将 JSON 文件转换为我想要的数据帧输出？

---

我已经导入了 JSON 文件并尝试将其展平:

myData <- fromJSON(file = "my_json_file.json")
myData_flat <- as.data.frame(myData)

但这显然还不够:

str(myData_flat)
'data.frame':   1 obs. of  352130 variables:
 $ Corpus                              : Factor w/ 1 level "ALM": 1
 $ Tweets.tweet_id                     : Factor w/ 1 level "521033092132503552": 1
 $ Tweets.tweet_text                   : Factor w/ 1 level "no tweet text available": 1
 $ Tweets.date                         : Factor w/ 1 level "no date available": 1
 $ Tweets.annotations.annotator        : Factor w/ 1 level "annotator00": 1
 $ Tweets.annotations.annotation       : Factor w/ 1 level "care": 1
 $ Tweets.annotations.annotator.1      : Factor w/ 1 level "annotator01": 1
 $ Tweets.annotations.annotation.1     : Factor w/ 1 level "care,purity": 1
 $ Tweets.annotations.annotator.2      : Factor w/ 1 level "annotator02": 1
 $ Tweets.annotations.annotation.2     : Factor w/ 1 level "care,purity": 1
 $ Tweets.annotations.annotator.3      : Factor w/ 1 level "annotator03": 1
 $ Tweets.annotations.annotation.3     : Factor w/ 1 level "care": 1
 $ Tweets.tweet_id.1                   : Factor w/ 1 level "537681598989475841": 1
 $ Tweets.tweet_text.1                 : Factor w/ 1 level "Wholeheartedly support these protests & acts of civil disobedience & will join when I can! #Ferguson #A"| __truncated__: 1
 $ Tweets.date.1                       : Factor w/ 1 level "Wed Nov 26 18:57:37 +0000 2014": 1
 $ Tweets.annotations.annotator.4      : Factor w/ 1 level "annotator00": 1
 $ Tweets.annotations.annotation.4     : Factor w/ 1 level "subversion": 1
 $ Tweets.annotations.annotator.5      : Factor w/ 1 level "annotator01": 1
 $ Tweets.annotations.annotation.5     : Factor w/ 1 level "subversion": 1
 $ Tweets.annotations.annotator.6      : Factor w/ 1 level "annotator02": 1
 $ Tweets.annotations.annotation.6     : Factor w/ 1 level "loyalty": 1
 $ Tweets.annotations.annotator.7      : Factor w/ 1 level "annotator03": 1
 $ Tweets.annotations.annotation.7     : Factor w/ 1 level "loyalty,subversion": 1
...

Answer 1

R 中有几个包可以读取 JSON 数据并具有 fromJSON() 函数。 RJSONOIO、jsonlite 和 rjson 是我所知道的。您似乎在代码中使用了 rjson::fromJSON()。

由于 json 格式在如何存储数据方面非常灵活，并且能够存储复杂的嵌套结构，当我们将其转换为相对不太复杂的 数据格式时。 frame，我们必须非常清楚数据的结构。您的案例相当简单，但在转换为 data.frame 的矩形形状时捕获 json 文件中的全部数据表示形式可能非常乏味。

rjson::fromJSON() 生成一个嵌套列表，类似于 JSON 文件的结构。

myData list 的结构如下所示:

myData[[1]]:
  - $Corpus
  - $Tweets
      - $[[1]]
          - tweet data
      - $[[2]]
          - tweet data

为了提取您要查找的数据，您想循环遍历 mydata[[1]]$tweets 子列表的条目，将它们转换为 data .frame，然后将所有这些 data.frame 绑定(bind)到一个大的 data.frame。您可以使用 for 循环或类似 lapply() 的方法来实现。我建议使用 purrr::map_dfr()，因为它会自动将每个嵌套操作的结果绑定(bind)到一个 data.frame 中。

我发现以下内容应该适用于您的数据。幸运的是 tibble::as.tibble() 适用于您的数据。您将其应用于每个推文条目一次，然后将其应用于每组 annotator、annotation，您将获得所需的结果。

library(rjson)
myData <- fromJSON(file = "my_json_file.json")

library(purrr)
library(dplyr)

myData_df <- map_dfr(myData[[1]]$Tweets, as.tibble)

annotations_df <- map_dfr(myData_df$annotations, as.tibble)

myData_df %>% 
  select(-annotations) %>% 
  bind_cols(annotations_df)

># A tibble: 8 x 5
>  tweet_id      tweet_text                                               date              annotator annotation   
  <chr>         <chr>                                                    <chr>             <chr>     <chr>        
1 521033092132… no tweet text available                                  no date available annotato… care         
2 521033092132… no tweet text available                                  no date available annotato… care,purity  
3 521033092132… no tweet text available                                  no date available annotato… care,purity  
4 521033092132… no tweet text available                                  no date available annotato… care         
5 537681598989… Wholeheartedly support these protests &amp; acts of civ… Wed Nov 26 18:57… annotato… subversion   
6 537681598989… Wholeheartedly support these protests &amp; acts of civ… Wed Nov 26 18:57… annotato… subversion   
7 537681598989… Wholeheartedly support these protests &amp; acts of civ… Wed Nov 26 18:57… annotato… loyalty      
8 537681598989… Wholeheartedly support these protests &amp; acts of civ… Wed Nov 26 18:57… annotato… loyalty,subv…