如何在R中按City匹配并基于District/Area列合并数据框

需求说明 #

需要合并两个数据框df1和df2，合并规则如下：

• City列必须在两个数据框中完全匹配
• df1的District列需匹配df2的District列 或 Area列

由于原始数据存在人为失误，df2中部分本该在District列的值被错误存入Area列，且无法直接清理原始数据集。用户期望的理想合并逻辑用伪代码表示如下：

df_merged <- merge(df1, df2, 
                   by.x = c("City", "District"),
                   by.y = c("City", "District" | "Area"), 
                   all.x = TRUE)

示例数据 #

# 输入数据框
df1 <- data.frame(City = c("A", "B"), District = c("cc", "dd"))
df2 <- data.frame(City = c("A", "A", "B", "B"), Code = c("1a","2a","3a","4a"), District = c("cc", "Apple", "Pear", "Orange"), Area = c("e", "a", "dd", "f"))

# 期望得到的结果
df3 <- data.frame(City = c("A", "B"), District = c("cc","dd"), Code = c("1a", "3a"))

数据预览：

> df1
  City District
1    A       cc
2    B       dd

> df2
  City Code District Area
1    A   1a       cc    e
2    A   2a    Apple    a
3    B   3a     Pear   dd
4    B   4a   Orange    f

> df3
  City District Code
1    A       cc   1a
2    B       dd   3a

解决方案 #

可以通过两种方式实现需求：

方法1：预处理df2后合并

先给df2新增一个匹配列，整合District和Area中符合匹配条件的值，再与df1合并：

library(dplyr)

# 预处理df2：新增match_col用于匹配df1的District
df2_processed <- df2 %>%
  mutate(match_col = ifelse(District %in% df1$District, District, Area))

# 合并数据
df_merged <- df1 %>%
  left_join(df2_processed, by = c("City", "District" = "match_col")) %>%
  select(City, District, Code)

方法2：直接通过条件筛选合并

利用left_join结合逻辑条件筛选，一步完成匹配：

library(dplyr)

df_merged <- df1 %>%
  left_join(df2, by = "City") %>%
  filter(District.x == District.y | District.x == Area) %>%
  select(City, District = District.x, Code)

如果需要保留all.x = TRUE的逻辑（即保留df1中所有行，即使无匹配项），可调整方法2为：

df_merged <- df1 %>%
  left_join(df2, by = "City") %>%
  filter(District.x == District.y | District.x == Area | is.na(District.y)) %>%
  group_by(City, District.x) %>%
  slice(1) %>% # 去重，保留第一个匹配项
  select(City, District = District.x, Code)

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