如何在R中基于id列连接两个DataFrame并生成目标融合数据集？

要把给定的两个DataFrame按id列合并，保留所有字段并生成预期的组合结果，本质是对两个数据框按id做全匹配连接（即每个id对应的所有行两两组合），以下是两种常用实现方式：

方法1：Base R 原生 merge() 函数 #

不需要额外安装包，直接用R内置的merge()函数即可完成：

# 定义原始数据框
df1 <- data.frame(id = c("id1","id2","id3", "id3"), status = c("open","close","open/close","close"), stock = c("google", "amazon", "yahoo", "amazon"))
df2 <- data.frame(id = c("id1","id2", "id2", "id3"), newspaper = c("times", "newyork", "london", "times"))

# 按id执行全匹配连接
merged_df <- merge(df1, df2, by = "id", all = TRUE)

# 输出结果
print(merged_df)

运行后得到的结果完全符合预期：

id     status  stock newspaper
1 id1       open google     times
2 id2      close amazon   newyork
3 id2      close amazon    london
4 id3 open/close  yahoo     times
5 id3      close amazon     times

方法2：dplyr 包的 full_join() 函数 #

如果日常使用tidyverse工具链，用dplyr的full_join()会更符合tidy风格：

# 先加载dplyr包（若未安装需先运行 install.packages("dplyr")）
library(dplyr)

# 定义原始数据框
df1 <- data.frame(id = c("id1","id2","id3", "id3"), status = c("open","close","open/close","close"), stock = c("google", "amazon", "yahoo", "amazon"))
df2 <- data.frame(id = c("id1","id2", "id2", "id3"), newspaper = c("times", "newyork", "london", "times"))

# 按id执行全连接
merged_df <- full_join(df1, df2, by = "id")

# 输出结果
print(merged_df)

该方法的输出和原生merge()完全一致。

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