如何在R中将嵌套的txt/json转换成“合适的”数据帧

Question

我下载了一个超过200万行的数据集，如下所示(我有10列/变量，从“messageid”开始。我不知道“id”列或变量是什么？？应该是，但我不需要这些来进行分析):

{
    "_id": {
        "$id": "fh37fc3huc3"
    },
    "messageid": "4757724838492485088139042828",
    "attachments": [],
    "usernameid": "47284592942",
    "username": "Alex",
    "server": "475774810304151552",
    "text": "Must watch",
    "type": "462050823720009729",
    "datetime": "2018-08-05T21:20:20.486000+00:00",
    "type": {
        "$numberLong": "0"
    }
}

{
    "_id": {
        "$id": "23453532dwq"
    },
    "messageid": "232534",
    "attachments": [],
    "usernameid": "273342",
    "usernameid": "Alice",
    "server": "475774810304151552",
    "text": "https://www.youtube.com/",
    "type": "4620508237200097wd29",
    "datetime": "2018-08-05T21:20:11.803000+00:00",
    "type": {
        "$numberLong": "0"
    }

在我意识到这是一个“嵌套数据集”之前，我尝试了显而易见的方法：

test <- read.csv(test.csv, row.names=NULL)

这是错误消息:

read.table（文件 = 文件，头 = 头，sep = sep，引号 = 引号，：列数多于列名

如果我不使用“row.names=NULL”

test <- read.csv(test.csv)

这是错误消息:

读取时出错。表（file=file，header=header，sep=sep，quote=quote，：不允许重复的“row.names”

有人知道如何将这个嵌套的数据集读入R中，其中每个变量都是一列，每个观察值都是一行吗？

Answer 1

此文件是json类型而不是csv类型。您需要添加[]以指示它是一个json列表。然后您可以将树装入表中：

文件数据的内容：

[{
  "_id": {
    "$id": "fh37fc3huc3"
  },
  "messageid": "4757724838492485088139042828",
  "attachments": [],
  "usernameid": "47284592942",
  "username": "Alex",
  "server": "475774810304151552",
  "text": "Must watch",
  "type": "462050823720009729",
  "datetime": "2018-08-05T21:20:20.486000+00:00",
  "type": {
    "$numberLong": "0"
  }
},

{
  "_id": {
    "$id": "23453532dwq"
  },
  "messageid": "232534",
  "attachments": [],
  "usernameid": "273342",
  "usernameid": "Alice",
  "server": "475774810304151552",
  "text": "https://www.youtube.com/",
  "type": "4620508237200097wd29",
  "datetime": "2018-08-05T21:20:11.803000+00:00",
  "type": {
    "$numberLong": "0"
  }
}
]

代码：

library(jsonlite)
library(tidyverse)

fromJSON("data.json") %>% as_tibble()
#> # A tibble: 2 x 9
#>   `_id`$`$id` messageid   attachments usernameid  username server  text   type  
#>   <chr>       <chr>       <list>      <chr>       <chr>    <chr>   <chr>  <chr> 
#> 1 fh37fc3huc3 4757724838… <list [0]>  47284592942 Alex     475774… Must … 46205…
#> 2 23453532dwq 232534      <list [0]>  273342      <NA>     475774… https… 46205…
#> # … with 1 more variable: datetime <chr>

< sup >由reprex软件包(2.0.1版)于2021-11-09创建