在R中基于另一个DataFrame的匹配条件替换Location值

我们有两个R语言DataFrame：

• df1：包含存在错误的Location字段数据
• df2：存储了code+Name组合对应的正确Location值

需求是：当df1与df2的code和Name同时匹配时，将df1的Location替换为df2中对应的正确值；未匹配到的行保留df1原Location值。

原始数据定义 #

# 存在错误Location的df1
df1 <- data.frame(
  code = c("A1","A1","A1","A2","A2","A2"),
  Name = c("alpha","beta","charli","alpha","beta","charli"),
  Location = c("A","B","C","B","C","A")
)

# 存储正确Location映射的df2
df2 <- data.frame(
  code = c("A1","A2","A1"),
  Name = c("alpha","beta","charli"),
  Location = c("C","B","A")
)

方法1：基础R原生实现 #

通过生成code+Name的组合匹配键，利用match函数定位对应行，完成值替换：

# 为两个数据框创建唯一匹配键
df1$match_key <- paste(df1$code, df1$Name, sep = "_")
df2$match_key <- paste(df2$code, df2$Name, sep = "_")

# 找到df1每行在df2中的匹配位置
match_indices <- match(df1$match_key, df2$match_key)

# 仅替换匹配到的Location值，未匹配项保留原值
df1$Location[!is.na(match_indices)] <- df2$Location[match_indices[!is.na(match_indices)]]

# 删除临时创建的匹配键列
df1 <- df1[, !names(df1) %in% "match_key"]

# 输出修正后的结果
print(df1)

方法2：tidyverse(dplyr)实现 #

使用left_join关联数据框，再通过coalesce函数优先选取正确的Location值：

library(dplyr)

df1_corrected <- df1 %>%
  # 按code和Name关联两个数据框，区分原Location和正确Location
  left_join(df2, by = c("code", "Name"), suffix = c("_original", "_correct")) %>%
  # 优先用正确值，无匹配则保留原值
  mutate(Location = coalesce(Location_correct, Location_original)) %>%
  # 保留需要的列
  select(code, Name, Location)

# 输出修正后的结果
print(df1_corrected)

最终结果 #

两种方法运行后都会得到符合需求的结果：

code    Name Location
1   A1   alpha        C
2   A1    beta        B
3   A1 charli        A
4   A2   alpha        B
5   A2    beta        B
6   A2 charli        A

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