如何在R中按列合并行数不同的数据框并合并重复行内容

示例数据 #

s1 <- data.frame(id = c(1,2,3,4), sex = c("M","M,","F","F"))
s2 <- data.frame(id = c(1,1,2,2,2,3,4), symp = c("cold","fever","headache","pain","cough","sneeze","cramps"))

期望结果 #

s3 <- data.frame(id = c(1,2,3,4), sex = c("M","M,","F","F"), symp = c("cold,fever","headache,pain,cough","sneeze","cramps"))

解决方案 #

方法1：使用dplyr包（代码更直观）

先分组聚合把s2中同一ID的symp合并成逗号分隔的字符串，再和s1做左连接：

# 未安装dplyr的话先运行：install.packages("dplyr")
library(dplyr)

# 聚合s2的symp列
s2_agg <- s2 %>%
  group_by(id) %>%
  summarise(symp = paste(symp, collapse = ","), .groups = "drop")

# 合并两个数据框
s3 <- s1 %>%
  left_join(s2_agg, by = "id")

方法2：使用Base R（无需额外安装包）

用aggregate完成聚合，再通过merge合并数据框：

# 聚合s2的symp列
s2_agg_base <- aggregate(symp ~ id, data = s2, FUN = function(x) paste(x, collapse = ","))

# 合并数据框，all.x=TRUE确保保留s1的所有行
s3_base <- merge(s1, s2_agg_base, by = "id", all.x = TRUE)

两种方法输出的结果均与你期望的s3一致。

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