在R语言中从嵌套列表提取并合并值至单列

问题描述 #

我有一个包含深度嵌套列表的数据框，需要将owners列中嵌套的commissioner和name字段提取出来，放到与division、id同一层级的列中。要求将name重命名为owner_name，若某行存在多个所有者，用&连接名字（例如id为"1"的行，owner_name应为"Fred R & Cam E"）。

示例数据框：

df <- tibble::tribble(
     ~division,  ~id,                                                                                                                                                                                                                                                           ~owners,
  "Division 2",  "8",                                                                                                                                   list(list(commissioner = 0, name = "Jesse H", id = "475A2F18-B6AF-11E6-977B-8468E8194D12", playoff_add_drops_disabled = 0)),
  "Division 1", "15",                                                                                                                                list(list(commissioner = 0, name = "Jon O", id = "F37E4A84-673C-11EC-812D-82AF546C6EE5", playoff_add_drops_disabled = 0)),
  "Division 2", "10", list(list(commissioner = 0, name = "Jim K", id = "1F19EADA-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0), list(commissioner = 0, name = "Kevin P", id = "E48FCF2A-ACA5-11E3-854D-2A588E5A61DF", playoff_add_drops_disabled = 0)),
  "Division 1", "14",       list(list(commissioner = 0, name = "Alan C", id = "D97B1226-E9C6-11E8-8119-3A141D7BE3A7", playoff_add_drops_disabled = 0), list(commissioner = 0, name = "Sam L", id = "8CC10D94-E7DF-11EC-9CA5-51CD4E0A8531", playoff_add_drops_disabled = 0)),
  "Division 1",  "9",                                                                                                                                  list(list(commissioner = 0, name = "Don R", id = "4686110A-D916-11EF-8988-87BD04866ADA", playoff_add_drops_disabled = 0)),
  "Division 1", "17",                                                                                                           list(list(logged_in_owner = 1, commissioner = 0, name = "Jason S", id = "2E84EA94-D98A-11EF-A9AB-4A2105866ADA", playoff_add_drops_disabled = 0)),
  "Division 2", "16",                                                                                                                                   list(list(commissioner = 0, name = "Eric W", id = "E0D804E0-9B59-11ED-BA80-FCF8D3963504", playoff_add_drops_disabled = 0)),
  "Division 1",  "3",                                                                                                                                 list(list(commissioner = 0, name = "James C", id = "1EF701E6-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0)),
  "Division 2", "13",                                                                                                                                 list(list(commissioner = 0, name = "Enrique D", id = "1F2EBB68-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0)),
  "Division 1",  "5",                                                                                                                                list(list(commissioner = 0, name = "Mitch T", id = "1F00CD8E-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0)),
  "Division 2",  "1",           list(list(commissioner = 1, name = "Fred R", id = "1E34F182-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0), list(commissioner = 0, name = "Cam E", id = "E42646B8-ACA5-11E3-854D-2A588E5A61DF", playoff_add_drops_disabled = 0)),
  "Division 2", "12",                                                                                                                         list(list(commissioner = 0, name = "Nick D", id = "1F29D4C2-AC9D-11E3-8EB7-031CFC5F1C7D", playoff_add_drops_disabled = 0)),
  "Division 1",  "4",                                                                                                                                    list(list(commissioner = 0, name = "Liam S", id = "389BDCA0-21EC-11EA-AD4E-B4851D7BE3A7", playoff_add_drops_disabled = 0)),
  "Division 2",  "7",                                                                                                                              list(list(commissioner = 0, name = "David E", id = "3D994500-AE62-11E5-AB74-A57DE140860F", playoff_add_drops_disabled = 0))
  )

解决方案 #

方法一：使用purrr直接提取并合并（无需展开数据） #

这种方法不需要将嵌套列表展开为多行，适合处理较大数据集：

library(dplyr)
library(purrr)
library(stringr)

df_processed <- df %>%
  mutate(
    # 提取所有所有者名字，用&连接
    owner_name = map_chr(owners, ~str_c(map_chr(., "name"), collapse = " & ")),
    # 提取commissioner值：这里取最大值（1代表存在专员，0代表无），若需保留所有值可改用str_c连接
    commissioner = map_int(owners, ~max(map_int(., "commissioner")))
  ) %>%
  select(-owners) # 移除原嵌套列

方法二：先展开再分组汇总（直观易懂） #

先将嵌套列表展开为多行，再按division和id分组合并：

library(tidyr)
library(dplyr)
library(stringr)

df_processed <- df %>%
  unnest(owners) %>% # 展开嵌套列表为多行
  rename(owner_name = name) %>% # 重命名name列
  group_by(division, id) %>% # 按分组键聚合
  summarise(
    owner_name = str_c(owner_name, collapse = " & "),
    commissioner = max(commissioner), # 同样，如需保留所有值用str_c(commissioner, collapse = ", ")
    .groups = "drop" # 取消分组
  )

两种方法处理后，id为"1"的行owner_name都会变成"Fred R & Cam E"，commissioner为1，符合需求。

内容的提问来源于stack exchange，提问作者Jazzmatazz