如何基于两个DataFrame创建按区域分组的DataFrame列表（R语言）

我有两个DataFrame：

• 第一个DataFrame（df1）包含数千列，列名格式为城市_年份（如London_2001、London_2002），行存储测量数据。
• 第二个DataFrame（df2）包含两列：TerritorialRegion（地域区域）和Cities（城市列表，多个城市用逗号分隔）。示例数据如下：

df1 <- data.frame(London_2021 = c(3,6,83,25,4),
                  London_2022 = c(3,6,83,25,4),
                  Berlin_2021 = c(3,6,83,25,4),
                  Berlin_2022 = c(3,6,83,25,4),
                  BerlinSuburbs_2021 = c(3,6,83,25,4),
                  BerlinSuburbs_2022 = c(3,6,83,25,4),
                  Tokyo_2021 = c(3,6,83,25,4),
                  Tokyo_2022 = c(3,6,83,25,4))

df2 <- data.frame(TerritorialRegion = c("Pacific", "Europe"),
                  Cities = c("Tokyo", "London, Berlin"))

需求是：创建一个列表，每个元素是对应地域区域的DataFrame，仅包含该区域内城市的所有年度列（严格匹配城市名，比如BerlinSuburbs不会被纳入）。例如列表中的Europe元素应包含London_2021、London_2022、Berlin_2021、Berlin_2022列。

我尝试了以下代码但未成功：

library(dplyr)
library(tidyr)

# step 1
df2 <- df2 %>% 
  separate_rows(Cities, sep = ",\\s*")

# step 2
df1_long <- df1 %>% 
  pivot_longer(cols = everything(), 
               names_to = c("City", "Year"), 
               names_sep = "_")

# step 3
df_merged <- df2 %>% 
  left_join(df1_long, by = c("Cities" = "City"))

# step 4
df_list <- df_merged %>% 
  split(.$TerritorialRegion)

你之前的代码将df1转为长格式后，最终得到的列表元素也是长格式，不符合保留原宽格式列结构的需求。以下是两种可行的修正方案：

方法一：直接基于列名匹配筛选 #

先拆分df2的城市列表，按地域分组后直接从原df1中筛选对应列，效率更高：

library(dplyr)
library(tidyr)

# 拆分df2的城市列表，得到区域-城市的对应关系
region_city_map <- df2 %>%
  separate_rows(Cities, sep = ",\\s*")

# 按地域分组，提取对应城市的所有年度列
result_list <- region_city_map %>%
  group_by(TerritorialRegion) %>%
  group_map(function(.x, .y) {
    # 构造严格匹配的正则：城市名开头，后跟_和年份数字
    city_pattern <- paste0("^", .x$Cities, "_\\d+$", collapse = "|")
    # 从df1中筛选符合条件的列
    df1 %>% select(matches(city_pattern))
  }, .keep = TRUE)

# 给列表元素命名为对应的地域名称
names(result_list) <- unique(region_city_map$TerritorialRegion)

方法二：长格式转换后转回宽格式 #

基于你原有的思路调整，合并后再转回宽格式：

library(dplyr)
library(tidyr)

# step1 拆分城市列表
df2_split <- df2 %>% 
  separate_rows(Cities, sep = ",\\s*")

# step2 转长格式并合并（用inner_join确保只保留匹配的城市）
df_merged <- df1 %>%
  pivot_longer(cols = everything(), 
               names_to = c("City", "Year"), 
               names_sep = "_") %>%
  inner_join(df2_split, by = c("City" = "Cities"))

# step3 转回宽格式并按区域拆分
result_list <- df_merged %>%
  pivot_wider(names_from = c(City, Year), values_from = value) %>%
  split(.$TerritorialRegion)

# 移除每个DataFrame中的TerritorialRegion列
result_list <- lapply(result_list, function(df) df %>% select(-TerritorialRegion))

验证：访问result_list[["Europe"]]即可得到包含London和Berlin所有年度列的宽格式DataFrame，且不会包含BerlinSuburbs相关列。

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