R JSON to tibble

Question

下面的数据是从API中传回的，我不能更改它的结构。我想将下面的JSON转换为tibble。

data <- '{    "ids":{
      "00000012664":{
         "state":"Indiana",
         "version":"10",
         "external_ids":[
            {
               "db":"POL",
               "db_id":"18935"
            },
            {
               "db":"CIT",
               "db_id":"1100882"
            }
         ],
         "id":"00000012520",
         "name":"Joe Smith",
         "aliases":[
            "John Smith",
            "Bill Smith"
         ]
      },
      "00000103162":{
         "state":"Kentucky",
         "external_ids":[
            {
               "db":"POL",
               "db_id":"69131"
            },
            {
               "db":"CIT",
               "db_id":"1098802"
            }
         ],
         "id":"00000003119",
         "name":"Sue Smith",
         "WIP":98203059
      } ,
     "0000019223":{
        "state":"Ohio",
        "external_ids":[
           {
              "db":"POL",
              "db_id":"69134"
           },
           {
              "db":"JT",
              "db_id":"615234"
           }
        ],
        "id":"0000019223",
        "name":"Larry Smith",
        "WIP":76532172,
        "aliases":[
           "Test 1",
           "Test 2",
           "Test 3",
           "Test 4"
        ],
        "insured":1
  }   } }'

请注意:这是数据的一小部分，可能有数千个"ids".

我尝试过jsonlite和tidyjson结合了purrr。

下面的内容给了我一个提示，但我不知道如何找回别名。

obj <- jsonlite::fromJSON(data, simplifyDataFrame = T, flatten = F)
obj$ids %>% { 
    data_frame(id=purrr::map_chr(., 'id'), 
           state=purrr::map_chr(., 'state', ''), 
           WIP=purrr::map_chr(., 'WIP', .default=''), 
           #aliases=purrr::map(purrr::map_chr(., 'aliases', .default=''), my_fun)
           ) 
}

我也不知道如何使用tidyjson：

data %>% enter_object(ids) %>% gather_object %>% spread_all

我想要返回的是带有以下字段的tibble (不管它们是否在JSON中)。

id
name
state
version
aliases -> as a string comma separated
WIP

奖金：-)

我也可以把external_ids作为字符串吗？

Answer 1

与使用map提取多个调用的每个元素不同，一个选项是将感兴趣的列转换为tibble with (as_tibble)和select，按'id‘分组，将’别名‘折叠成一个字符串，然后按'id’获取distinct行。

library(tibble)
library(purrr)
library(stringr)
map_dfr(obj$ids,  ~ as_tibble(.x) %>% 
             select(id, one_of("name", "state", "version", "aliases", "WIP")))  %>% 
    group_by(id) %>% 
    mutate(aliases = toString(unique(aliases))) %>% 
    distinct(id, .keep_all = TRUE)
# A tibble: 2 x 6
# Groups:   id [2]
#  id          name      state    version aliases                     WIP
#  <chr>       <chr>     <chr>    <chr>   <chr>                     <int>
#1 00000012520 Joe Smith Indiana  10      John Smith, Bill Smith       NA
#2 00000003119 Sue Smith Kentucky <NA>    NA                     98203059

如果我们还需要“external_ids”(这是一个data.frame)

map_dfr(obj$ids,  ~ as_tibble(.x) %>%
         mutate(external_ids = reduce(external_ids, str_c, sep = " "))) %>%
   group_by(id) %>%
   mutate_at(vars(aliases, external_ids), ~ toString(unique(.))) %>%
   ungroup %>% 
   distinct(id, .keep_all= TRUE)
# A tibble: 2 x 7
#  state    version external_ids           id          name      aliases                     WIP
#  <chr>    <chr>   <chr>                  <chr>       <chr>     <chr>                     <int>
#1 Indiana  10      POL 18935, CIT 1100882 00000012520 Joe Smith John Smith, Bill Smith       NA
#2 Kentucky <NA>    POL 69131, CIT 1098802 00000003119 Sue Smith NA                     98203059

更新

对于新的数据，我们可以使用

obj$ids %>%
    map_dfr(~ map_df(.x, reduce, str_c, collapse = ", ", sep= " ") )
# A tibble: 3 x 8
#  state    version external_ids           id          name        aliases                          WIP insured
#  <chr>    <chr>   <chr>                  <chr>       <chr>       <chr>                          <int>   <int>
#1 Indiana  10      POL 18935, CIT 1100882 00000012520 Joe Smith   John Smith Bill Smith             NA      NA
#2 Kentucky <NA>    POL 69131, CIT 1098802 00000003119 Sue Smith   <NA>                        98203059      NA
#3 Ohio     <NA>    POL 69134, JT 615234   0000019223  Larry Smith Test 1 Test 2 Test 3 Test 4 76532172       1