R语言实现带重复列替换的左连接：用精确值覆盖估计值

我有两个存在信息重叠的数据框，一个包含估计值，另一个包含精确值。需要将它们合并为一个数据框，规则是：有精确计数的列用精确值替换估计值，没有精确计数的列保留原估计值。请问怎么实现这种排除重复列的左连接？

示例数据 #

# 估计值数据框
Id = c("1", "2", "3", "4")
Persons = c(300, 400, 200, 5000)
Houses = c(20, 40, 10, 23)
Ages =  c(45, 34, 50, 44)
Races = c(1, 3, 1, 2)
estimates = data.frame(Id, Persons, Houses, Ages, Races)

# 精确值数据框
Id = c("1", "2", "3", "4")
Persons = c(321, 421, 198, 4876)
Houses = c(21, 39, 11, 25)
counts = data.frame(Id, Persons, Houses)

目标结果 #

Id Persons Houses Ages Races
1  1     321     21   45     1
2  2     421     39   34     3
3  3     198     11   50     1
4  4    4876     25   44     2

解决方案 #

方法一：Base R 原生实现 #

1. 用merge做左连接，为重叠列添加后缀区分估计值和精确值
2. 遍历重叠列，用精确值替换对应位置的估计值
3. 删除临时后缀列，整理出最终结构

# 左连接并添加后缀
merged_df <- merge(estimates, counts, by = "Id", all.x = TRUE, suffixes = c("_est", "_act"))

# 获取重叠列名（排除Id）
overlap_cols <- setdiff(names(counts), "Id")

# 替换重叠列的值
for(col in overlap_cols) {
  merged_df[[col]] <- ifelse(!is.na(merged_df[[paste0(col, "_act")]]), 
                             merged_df[[paste0(col, "_act")]], 
                             merged_df[[paste0(col, "_est")]])
}

# 清理临时列得到结果
final_df <- merged_df[, c("Id", names(estimates)[-1])]

方法二：dplyr 包简洁实现 #

借助dplyr的管道语法和coalesce函数，一步完成替换和清理：

library(dplyr)

final_df <- estimates %>%
  left_join(counts, by = "Id", suffix = c("_est", "_act")) %>%
  # 对所有重叠列，用精确值覆盖估计值
  mutate(across(all_of(setdiff(names(counts), "Id")), 
                ~ coalesce(.data[[paste0(cur_column(), "_act")]], .))) %>%
  # 删除带后缀的临时列
  select(-ends_with("_est"), -ends_with("_act"))

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