合并含同名变量且NA多互补的DataFrame并处理数据冲突

问题背景 #

现有三个DataFrame：

• df：包含id变量及其他附加变量
• df1：通过id与df匹配，变量均为varX_J格式（X为00-19的字符，J为变量描述，前缀统一为var）
• df2：结构与df1完全一致，但数据不同

理论上df1和df2的有效值应当互补，但存在脏数据导致同一位置同时有有效值的冲突情况。需要将df1、df2合并到df中，合并规则：

• 同名变量合并时，若同位置均有值，保留df1的数据
• 生成冲突标记列，标记出存在冲突的行

模拟数据代码 #

library('dplyr')

df <- data.frame(id = c(1:20),
                 og_var1 = sample(c(1:50), 20, replace=TRUE),
                 state = sample(c(1:52), 20, replace=TRUE),
                 race = sample(c(1:5), 20, replace=TRUE)
                 )

df1 <- left_join(data.frame(id = (1:20)), data.frame(
                  id = c(3,6,9,12),
                  var09_married = c(1,NA,2,1),
                  var09_happiness = c(1,NA,3,2),
                  var10_married = c(NA,1,2,2),
                  var10_happiness = c(NA,5,2,5)), by=c("id"))

df2 <- left_join(data.frame(id = (1:20)), data.frame(
                  id = c(3,6,11,15),
                  var09_married = c(NA,1,1,1),
                  var09_happiness = c(NA,3,3,2),
                  var10_married = c(1,NA,2,1),
                  var10_happiness = c(2,NA,4,4)), by=c("id"))


df <- left_join(df, df1, by=c("id"))
df <- left_join(df, df2, by=c("id"))

期望最终结构 #

dput(df)
structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
13, 14, 15, 16, 17, 18, 19, 20), og_var1 = c(6L, 4L, 33L, 7L, 
37L, 16L, 34L, 42L, 37L, 37L, 39L, 41L, 24L, 33L, 30L, 2L, 20L, 
29L, 33L, 47L), state = c(2L, 35L, 11L, 14L, 16L, 16L, 40L, 39L, 
28L, 13L, 5L, 26L, 28L, 15L, 13L, 31L, 43L, 25L, 16L, 28L), race = c(5L, 
4L, 2L, 1L, 1L, 2L, 3L, 2L, 2L, 4L, 2L, 3L, 5L, 2L, 3L, 2L, 5L, 
1L, 5L, 5L), var09_married = c(NA, NA, 1, NA, NA, 1, NA, NA, 
2, NA, 1, 1, NA, NA, 1, NA, NA, NA, NA, NA), var09_happiness = c(NA, 
NA, 1, NA, NA, 3, NA, NA, 3, NA, 3, 2, NA, NA, 2, NA, NA, NA, 
NA, NA), var10_married = c(NA, NA, 1, NA, NA, 1, NA, NA, 2, NA, 
2, 2, NA, NA, 1, NA, NA, NA, NA, NA), var10_happiness = c(NA, 
NA, 2, NA, NA, 5, NA, NA, 2, NA, 4, 5, NA, NA, 4, NA, NA, NA, 
NA, NA), flag = c(0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0)), row.names = c(NA, -20L), class = "data.frame")

解决方案代码 #

library(dplyr)
library(tidyr)

# 提取所有var开头的目标变量名
var_names <- grep("^var", names(df1), value = TRUE)

# 合并df1和df2，处理冲突并生成标记
combined_vars <- df1 %>%
  inner_join(df2, by = "id", suffix = c("_df1", "_df2")) %>%
  # 合并同名变量：优先保留df1的值，无值则用df2的
  mutate(across(all_of(paste0(var_names, "_df1")),
                .names = "{gsub('_df1', '', .col)}",
                ~ ifelse(!is.na(.) & !is.na(get(gsub("_df1", "_df2", cur_column()))),
                         .,
                         coalesce(., get(gsub("_df1", "_df2", cur_column()))))),
         # 生成冲突标记：只要有一个变量存在同位置双值冲突，标记为1
         flag = as.integer(rowSums(across(all_of(paste0(var_names, "_df1")),
                                          ~ !is.na(.) & !is.na(get(gsub("_df1", "_df2", cur_column()))))) > 0)) %>%
  # 保留需要的列
  select(id, all_of(var_names), flag)

# 将处理后的变量合并到原始df中
final_df <- df %>%
  # 移除之前重复合并的var列
  select(-all_of(grep("^var", names(df), value = TRUE))) %>%
  left_join(combined_vars, by = "id") %>%
  # 对无冲突的行填充0
  mutate(flag = replace_na(flag, 0))

# 输出结果
dput(final_df)

代码说明 #

1. 提取目标变量：通过正则匹配筛选出所有var开头的变量，确保仅处理目标列
2. 合并冲突处理：将df1和df2按id合并，用后缀区分同名列；遍历每个变量，优先保留df1的值，同时判断是否存在双值冲突
3. 生成冲突标记：统计每行中冲突变量的数量，大于0则标记为1，否则为0
4. 合并到主表：移除主表中重复的var列，将处理后的变量和标记列合并，对未匹配到冲突的行填充0

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