在R中合并data.frame：用test_2覆盖test_1对应值
2026-8-15
在R中用test_2的值覆盖test_1对应值的实现方法
问题需求
合并两个data.frame，当test_2中存在对应rn和日期列的值时，覆盖test_1的对应值；test_2没有的列或值则保留test_1的原始值，同时保持test_1的行顺序和所有列结构。
原始数据
test_1 = structure(list(rn = c("Red", "Blue", "Green", "Yellow", "Pink", "Gold" ), X2022.08.01 = c(0, 0, 0, 0, 0, 0), X2022.08.02 = c(0, 0, 0, 0, 0, 0), X2022.08.03 = c(0, 0, 0, 0, 0, 0), X2022.08.04 = c(0, 0, 0, 0, 0, 0), X2022.08.05 = c(0, 0, 0, 0, 0, 0), X2022.08.08 = c(0, 0, 0, 0, 0, 0), X2022.08.09 = c(0, 0, 0, 0, 0, 0), X2022.08.10 = c(0, 0, 0, 0, 0, 0), X2022.08.11 = c(0, 0, 0, 0, 0, 0), X2022.08.12 = c(0, 0, 0, 0, 0, 0), X2022.08.15 = c(0, 0, 0, 0, 0, 0), X2022.08.16 = c(0, 0, 0, 0, 0, 0), X2022.08.17 = c(0, 0, 0, 0, 0, 0), X2022.08.18 = c(0, 0, 0, 0, 0, 0), X2022.08.19 = c(0, 0, 0, 0, 0, 0), X2022.08.22 = c(0, 0, 0, 0, 0, 0), X2022.08.23 = c(0, 0, 0, 0, 0, 0), X2022.08.24 = c(0, 0, 0, 0, 0, 0), X2022.08.25 = c(0, 0, 0, 0, 0, 0), X2022.08.26 = c(0, 0, 0, 0, 0, 0), X2022.08.29 = c(0, 0, 0, 0, 0, 0), X2022.08.30 = c(0, 0, 0, 0, 0, 0), X2022.08.31 = c(0, 0, 0, 0, 0, 0)), row.names = c(NA, 6L), class = "data.frame") test_2 = structure(list(rn = c("Blue", "Pink", "Red", "Yellow", "Green", "Gold" ), X2022.08.01 = c(10, 10, 10, 10, 10, 10), X2022.08.03 = c(10, 10, 10, 10, 10, 10), X2022.08.04 = c(10, 10, 10, 10, 10, 10), X2022.08.05 = c(10, 10, 10, 10, 10, 10), X2022.08.26 = c(10, 10, 10, 10, 10, 10)), row.names = c(NA, 6L), class = "data.frame")
解决方案
方法1：使用dplyr + tidyr（灵活易读）
通过转置数据格式实现精准匹配，最后恢复原结构：
library(dplyr) library(tidyr) # 将宽格式转为长格式，便于按rn和日期匹配 test_1_long <- test_1 %>% pivot_longer(-rn, names_to = "date", values_to = "val1") test_2_long <- test_2 %>% pivot_longer(-rn, names_to = "date", values_to = "val2") # 合并数据，优先取test_2的值，无值则保留test_1 merged_data <- test_1_long %>% left_join(test_2_long, by = c("rn", "date")) %>% mutate(final_val = coalesce(val2, val1)) %>% select(rn, date, final_val) # 转回宽格式，恢复test_1的行顺序和列顺序 test_output <- merged_data %>% pivot_wider(names_from = date, values_from = final_val) %>% arrange(match(rn, test_1$rn)) %>% select(names(test_1))
方法2：Base R实现（无需额外包）
直接对齐行顺序后遍历列替换：
# 按test_1的rn顺序重新排序test_2 test_2_aligned <- test_2[match(test_1$rn, test_2$rn), ] # 初始化输出为test_1 test_output <- test_1 # 遍历所有共同列（排除rn），替换值 common_cols <- intersect(names(test_1), names(test_2)) common_cols <- common_cols[common_cols != "rn"] for(col in common_cols) { test_output[[col]] <- ifelse(!is.na(test_2_aligned[[col]]), test_2_aligned[[col]], test_output[[col]]) }
验证结果
用以下代码检查输出是否符合预期：
# 期望的输出数据 test_expected = structure(list(rn = c("Red", "Blue", "Green", "Yellow", "Pink", "Gold" ), X2022.08.01 = c(10, 10, 10, 10, 10, 10), X2022.08.02 = c(0, 0, 0, 0, 0, 0), X2022.08.03 = c(10, 10, 10, 10, 10, 10), X2022.08.04 = c(10, 10, 10, 10, 10, 10), X2022.08.05 = c(10, 10, 10, 10, 10, 10), X2022.08.08 = c(0, 0, 0, 0, 0, 0), X2022.08.09 = c(0, 0, 0, 0, 0, 0), X2022.08.10 = c(0, 0, 0, 0, 0, 0), X2022.08.11 = c(0, 0, 0, 0, 0, 0), X2022.08.12 = c(0, 0, 0, 0, 0, 0), X2022.08.15 = c(0, 0, 0, 0, 0, 0), X2022.08.16 = c(0, 0, 0, 0, 0, 0), X2022.08.17 = c(0, 0, 0, 0, 0, 0), X2022.08.18 = c(0, 0, 0, 0, 0, 0), X2022.08.19 = c(0, 0, 0, 0, 0, 0), X2022.08.22 = c(0, 0, 0, 0, 0, 0), X2022.08.23 = c(0, 0, 0, 0, 0, 0), X2022.08.24 = c(0, 0, 0, 0, 0, 0), X2022.08.25 = c(0, 0, 0, 0, 0, 0), X2022.08.26 = c(10, 10, 10, 10, 10, 10), X2022.08.29 = c(0, 0, 0, 0, 0, 0), X2022.08.30 = c(0, 0, 0, 0, 0, 0), X2022.08.31 = c(0, 0, 0, 0, 0, 0)), row.names = c(NA, 6L), class = "data.frame") # 检查是否一致 all.equal(test_output, test_expected)
内容的提问来源于stack exchange，提问作者nicshah
相关产品推荐
相关产品推荐
