合并含不同变量的两个DataFrame问题求助

问题分析 #

你的两个DataFrame中，仅df1的第1行与df2的第1行在x1、x2、x4三列完全匹配，可合并为一行；其余行因x4值不同，需保留各自的行并补充缺失列（用NA填充），最终得到13行5列的结果。

解决方案 #

方法1：使用dplyr的full_join（推荐）

通过指定x1、x2、x4为连接键，仅当这三列完全匹配时合并行，否则保留两个表的所有行：

# 加载dplyr包
library(dplyr)

# 定义原始DataFrame
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,2,2,2,2),
  x2 = c("a","a","b","b","c","c","c"),
  x4 = c("apple","banana","banana","melon","melon","melon","melon"),
  x5 = c("t","u","v","w","x","y","z")
)

# 执行全外连接
merged_df <- full_join(df1, df2, by = c("x1", "x2", "x4"))

# 验证结果
nrow(merged_df) # 输出13
ncol(merged_df) # 输出5

方法2：使用base R的merge函数

无需加载额外包，设置all=TRUE实现全外连接：

# 定义原始DataFrame
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,2,2,2,2),
  x2 = c("a","a","b","b","c","c","c"),
  x4 = c("apple","banana","banana","melon","melon","melon","melon"),
  x5 = c("t","u","v","w","x","y","z")
)

# 执行全外连接
merged_df <- merge(df1, df2, by = c("x1", "x2", "x4"), all = TRUE)

为什么之前的方法无效？ #

• cbind：仅按位置拼接列，要求两个DataFrame行数一致，无法处理行的匹配逻辑；
• rbind：要求列名完全一致，会保留所有14行（含重复的那一行），不符合13行的需求；
• 未指定正确连接键的merge/连接函数：若仅按x1、x2连接，会产生过多匹配行，导致行数超出预期；
• 左/右连接：仅保留左表或右表的所有行，无法同时保留两个表的全部非匹配行。

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