使用R合并两个DataFrame并将重复值替换为NA的需求

需求说明 #

合并df1和df2，同时对df2中x1、x2、x5字段组合重复的行，将其x6字段替换为NA（仅保留每组重复组合首次出现的x6值）。

示例数据 #

df1 <- data.frame(
  x1 = c(1,1,1,2,2,2,2),
  x2 = c("a","a","b","b","c","c","c"),
  x3 = c("t","u","v","w","x","y","z"),
  x4 = c("apple","apple","mango","mango","mango","mango","mango")
)

df2 <- data.frame(
  x1 = c(1,1,1,1,2,2,2,2),
  x2 = c("a","a","a","b","b","b","c","c"),
  x3 = c("t","u","u","v","w","x","y","z"),
  x5 = c("apple A","apple A","apple B","mango A","mango B","mango A","mango A","mango A"),
  x6 = c(10,10,20,10,10,30,30,30)
)

解决方案（dplyr版） #

使用dplyr包可以简洁实现需求，步骤清晰：

library(dplyr)

df3 <- df2 %>%
  # 合并df1的x4字段，匹配键为x1、x2、x3
  left_join(df1, by = c("x1", "x2", "x3")) %>%
  # 按x1、x2、x5分组
  group_by(x1, x2, x5) %>%
  # 仅保留每组第一行的x6值，其余设为NA
  mutate(x6 = ifelse(row_number() == 1, x6, NA)) %>%
  ungroup() %>%
  # 调整列顺序与期望输出一致
  select(x1, x2, x3, x4, x5, x6)

验证结果 #

执行上述代码后，df3的输出与期望一致：

print(df3)
#> # A tibble: 8 × 6
#>      x1 x2    x3    x4    x5       x6
#>   <dbl> <chr> <chr> <chr> <chr> <dbl>
#> 1     1 a     t     apple apple A    10
#> 2     1 a     u     apple apple A    NA
#> 3     1 a     u     apple apple B    20
#> 4     1 b     v     mango mango A    10
#> 5     2 b     w     mango mango B    10
#> 6     2 b     x     mango mango A    30
#> 7     2 c     y     mango mango A    30
#> 8     2 c     z     mango mango A    NA

解决方案（Base R版） #

如果不想加载第三方包，也可以用Base R实现：

# 合并数据，保留df2所有行
merged_df <- merge(df2, df1, by = c("x1", "x2", "x3"), all.x = TRUE)

# 对每组x1/x2/x5，仅保留第一行的x6值
merged_df$x6 <- ave(merged_df$x6, merged_df[c("x1", "x2", "x5")], FUN = function(x) {
  x[-1] <- NA
  x
})

# 调整列顺序
df3 <- merged_df[, c("x1", "x2", "x3", "x4", "x5", "x6")]

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