嵌套数据框元数据匹配：将指标数据按组集转宽格式

从API拉取指标数据后，indicators数据框包含嵌套数据框存储元数据，尝试规整但效果不佳。需求为：给indicators新增以组集名称为列名的列，列值为指标对应的分组名称，无对应分组则填充NA。

API返回的数据如下：

library(tibble)
indicators <- tibble::tibble(
  name = c("Number of lettuces", "Number of oranges"),
  groups = list(
    data.frame(
      name = c("Number"), 
      id = c("uid001")),
    data.frame(
      name = c("Oranges", "Citrus", "Number"),
      id = c("uid003", "uid004", "uid001")
    )
  )
)
indicator_groups <- tibble::tibble(
  name = c("Number", "Oranges", "Citrus"),
  id = c("uid001", "uid003", "uid004"),
  sets = list(
    data.frame(name = c("Unit_of_measure"), id = c("gid003")),
    data.frame(name = c("Fruit"), id = c("gid001")),
    data.frame(name = c("Fruit", "Type"), id = c("gid001", "gid002"))
  )
)
indicator_group_sets <- tibble::tibble(
  name = c("Fruit", "Type", "Unit_of_measure"),
  id = c("gid001", "gid002", "gid003"),
  groups = list(
    data.frame(id = c("uid003", "uid004")),
    data.frame(id = c("uid003", "uid004")),
    data.frame(id = c("uid001"))
  )
)

期望结果：

indicators
#>                 name   Fruit   Type Unit_of_measure
#> 1 Number of lettuces    <NA>   <NA>          Number
#> 2  Number of oranges Oranges Citrus          Number

以下提供两种基于indicator_groups和indicator_group_sets的实现方式，均使用dplyr+tidyr完成：

方法一：基于indicator_groups处理 #

library(dplyr)
library(tidyr)

# 1. 建立分组ID与组集名称的映射关系
group_set_mapping <- indicator_groups %>%
  unnest(sets) %>%
  select(group_id = id, group_name = name, set_name = sets.name)

# 2. 展开indicators的嵌套分组，关联组集信息
indicators_expanded <- indicators %>%
  unnest(groups) %>%
  left_join(group_set_mapping, by = c("groups.id" = "group_id")) %>%
  select(indicator_name = name, set_name, group_name)

# 3. 转换为宽表并与原indicators合并
final_result <- indicators %>%
  select(name) %>%
  left_join(
    indicators_expanded %>%
      pivot_wider(names_from = set_name, values_from = group_name),
    by = c("name" = "indicator_name")
  )

print(final_result)

运行输出：

# A tibble: 2 × 4
  name                 Fruit   Type  Unit_of_measure
  <chr>                <chr>   <chr> <chr>          
1 Number of lettuces   NA      NA    Number         
2 Number of oranges    Oranges Citrus Number         

方法二：基于indicator_group_sets处理 #

library(dplyr)
library(tidyr)

# 1. 建立组集名称与分组ID的映射关系
set_group_mapping <- indicator_group_sets %>%
  unnest(groups) %>%
  select(set_name = name, group_id = groups.id)

# 2. 展开indicators的嵌套分组，关联组集信息
indicators_expanded <- indicators %>%
  unnest(groups) %>%
  left_join(set_group_mapping, by = c("groups.id" = "group_id")) %>%
  select(indicator_name = name, set_name, group_name = groups.name)

# 3. 转换为宽表并与原indicators合并
final_result <- indicators %>%
  select(name) %>%
  left_join(
    indicators_expanded %>%
      pivot_wider(names_from = set_name, values_from = group_name),
    by = c("name" = "indicator_name")
  )

print(final_result)

此方法同样能得到与需求一致的结果。

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