R中如何按指定条件将多个数据框的值匹配到目标数据框

代码错误原因 #

之前的代码运行失败核心有三点：

• dplyr的left_join()无name参数，用该参数自定义列名的逻辑不生效
• 每次管道调用left_join()时重复传入原始df1作为左表，而非上一步关联后的结果，导致之前的匹配结果被覆盖
• 匹配键拼写错误：df1中的年份字段为year1/year2，代码中写为year_1/year_2，字段不匹配导致关联失败

实现方案 #

方案1：多次左连接（适合年份少的场景） #

逻辑直观，直接通过重命名匹配表的字段实现自定义列名，无需转换表结构：

library(tidyverse)

df_final <- df1 %>%
  # 匹配year1对应的polity得分
  left_join(df2 %>% rename(polity_year1 = score),
            by = c("country" = "country", "year1" = "year")) %>%
  # 匹配year2对应的polity得分
  left_join(df2 %>% rename(polity_year2 = score),
            by = c("country" = "country", "year2" = "year")) %>%
  # 匹配year1对应的GDP
  left_join(df3 %>% rename(gdp_year1 = gdp),
            by = c("country" = "country", "year1" = "year")) %>%
  # 匹配year2对应的GDP
  left_join(df3 %>% rename(gdp_year2 = gdp),
            by = c("country" = "country", "year2" = "year"))

运行后直接得到要求的df_final结构，和预期输出完全一致。

方案2：长宽表转换（适合年份多的扩展场景） #

如果后续需要新增year3/year4等更多年份字段，用该方案无需重复写join逻辑，扩展性更强：

library(tidyverse)

df_final <- df1 %>%
  # 把年份列转为长格式
  pivot_longer(cols = starts_with("year"), 
               names_to = "year_type", 
               values_to = "year_val") %>%
  # 批量匹配polity得分和GDP
  left_join(df2, by = c("country", "year_val" = "year")) %>%
  rename(polity = score) %>%
  left_join(df3, by = c("country", "year_val" = "year")) %>%
  # 转回宽格式，自动生成指定格式的列名
  pivot_wider(names_from = year_type,
              values_from = c(polity, gdp),
              names_glue = "{.value}_{year_type}") %>%
  # 调整列顺序符合要求
  select(name, country, year1, year2, polity_year1, gdp_year1, polity_year2, gdp_year2)

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