如何在R中按优先级匹配多ID列实现数据框右连接？

现有两个R数据框，需要执行带优先级规则的右连接：

df1 <- structure(list(ID_1 = c("NE1", "NE2", "LR2", "NE4", "WE5"), ID_2 = c("WW1", 
"NE3", "LR2", "NE5", "WE5"), Country = c("Togo", "USA", "Iceland", 
"Chad", "UK")), class = "data.frame", row.names = c(NA, -5L))


df2 <- structure(list(ID_1 = c("NE1", "NE3", "LR2", "NE6"), Sex = c("M", 
"F", "M", "M")), class = "data.frame", row.names = c(NA, -4L))

匹配规则 #

• 优先用df1$ID_1与df2$ID_1匹配
• 若上述匹配失败，再用df1$ID_2与df2$ID_1匹配
• 禁止使用c(ID_1, ID_2)这类合并列的方式
• 最终结果需保留单个ID列，期望输出如下：

#     ID_1    Country   Sex
# 1   NE1     Togo      M     --> 基于df1的ID_1匹配
# 2   NE3     USA       F     --> 基于df1的ID_2匹配
# 3   LR2     Iceland   M     --> df1的ID_1和ID_2均匹配
# 4   NE6     <NA>      M     --> 无匹配但保留行（右连接特性）

解决方案（使用dplyr包） #

library(dplyr)

# 第一步：提取df1中通过ID_1直接匹配df2的行
match_by_id1 <- df1 %>%
  inner_join(df2, by = "ID_1")

# 第二步：提取df1中ID_1未匹配，通过ID_2匹配df2的行
match_by_id2 <- df1 %>%
  filter(!ID_1 %in% df2$ID_1) %>%
  inner_join(df2, by = c("ID_2" = "ID_1")) %>%
  rename(ID_1 = ID_2)  # 统一ID列名

# 第三步：合并匹配结果，右连接补充df2中无匹配的行
final_result <- bind_rows(match_by_id1, match_by_id2) %>%
  right_join(df2, by = "ID_1") %>%
  select(ID_1, Country, Sex) %>%
  arrange(ID_1)

print(final_result)

输出结果 #

ID_1 Country Sex
1  NE1    Togo   M
2  NE3     USA   F
3  LR2 Iceland   M
4  NE6    <NA>   M

逻辑说明 #

1. 优先处理df1中ID_1能直接匹配df2的行，保证优先级
2. 对df1中ID_1无法匹配的行，尝试用ID_2匹配并统一列名
3. 合并有效匹配结果后，通过右连接保留df2中所有行（包括无匹配的NE6）
4. 最后整理列顺序和排序，得到符合要求的输出

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