如何优雅聚合两个DataFrame的字符与数值数据生成新DataFrame？

我需要合并两个结构一致的DataFrame，实现以下效果：

• 保留Date、Name等相同列的内容
• 将Result1列的内容用+拼接
• 对Value列的数值求和
  目前用left_join的方式步骤繁琐，想找更简洁的实现方法。

输入数据 #

input1 #

input1 = structure(list(Date = structure(c(1677502800, 1677502800,
1677502800, 1677502800, 1677502800, 1677502800), class = c("POSIXct",
"POSIXt"), tzone = ""), Name = c("Rome_Italy", "Paris_France",
"Beijing_China", "Boston_USA", "Moscow_Russia",
"Sydney_Australia"), ReportType = c("SALES",
"SALES", "SALES", "SALES", "SALES", "SALES"), TestType = c("Internal",
"Internal", "Internal", "Internal", "Internal", "Internal"), Code1 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"), Code2 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"
), Result1 = c("XMAS_DOWN", "XMAS_DOWN", "XMAS_DOWN", "XMAS_DOWN",
"XMAS_DOWN", "XMAS_DOWN"), Result2 = c("N/A", "N/A", "N/A", "N/A",
"N/A", "N/A"), Value = c(24, 6, 0, 9,
-7, -13)), row.names = c(NA, 6L), class = "data.frame")

input2 #

input2 = structure(list(Date = structure(c(1677502800, 1677502800,
1677502800, 1677502800, 1677502800, 1677502800), class = c("POSIXct",
"POSIXt"), tzone = ""), Name = c("Rome_Italy", "Paris_France",
"Beijing_China", "Boston_USA", "Moscow_Russia",
"Sydney_Australia"), ReportType = c("SALES",
"SALES", "SALES", "SALES", "SALES", "SALES"), TestType = c("Internal",
"Internal", "Internal", "Internal", "Internal", "Internal"), Code1 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"), Code2 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"
), Result1 = c("EAST_DOWN", "EAST_DOWN", "EAST_DOWN", "EAST_DOWN", "EAST_DOWN", "EAST_DOWN" ), Result2 = c("N/A", "N/A", "N/A", "N/A",
"N/A", "N/A"), Value = c(22, 2, 3, 2,
9, 16)), row.names = c(NA, 6L), class = "data.frame")

期望输出 #

output = structure(list(Date = structure(c(1677502800, 1677502800,
1677502800, 1677502800, 1677502800, 1677502800), class = c("POSIXct",
"POSIXt"), tzone = ""), Name = c("Rome_Italy", "Paris_France",
"Beijing_China", "Boston_USA", "Moscow_Russia",
"Sydney_Australia"), ReportType = c("SALES",
"SALES", "SALES", "SALES", "SALES", "SALES"), TestType = c("Internal",
"Internal", "Internal", "Internal", "Internal", "Internal"), Code1 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"), Code2 = c("N/A",
"N/A", "N/A", "N/A", "N/A", "N/A"
), Result1 = c("XMAS_DOWN + EAST_DOWN", "XMAS_DOWN + EAST_DOWN", "XMAS_DOWN + EAST_DOWN", "XMAS_DOWN + EAST_DOWN", "XMAS_DOWN + EAST_DOWN", "XMAS_DOWN + EAST_DOWN" ), Result2 = c("N/A", "N/A", "N/A", "N/A",
"N/A", "N/A"), Value = c(46, 8, 3, 11,
2, 3)), row.names = c(NA, 6L), class = "data.frame")

现有实现（繁琐） #

test = left_join(input1, input2, by = c('Date', 'Name', 'ReportType', 'TestType', 'Code1', 'Code2', 'Result2'))
test$Value = test$Value.x + test$Value.y
test$Result1 = paste(test$Result1.x, "+", test$Result1.y)
test_1 = select(test, c('Date', 'Name', 'ReportType', 'TestType', 'Code1', 'Code2', 'Result1', 'Result2', 'Value'))

更优实现方案 #

方案1：使用dplyr的bind_rows + group_by聚合 #

这种方式无需手动指定连接键，适用于所有分组列内容一致的场景，代码更简洁：

library(dplyr)

output = bind_rows(input1, input2) %>%
  group_by(Date, Name, ReportType, TestType, Code1, Code2, Result2) %>%
  summarise(
    Result1 = paste(Result1, collapse = " + "),
    Value = sum(Value),
    .groups = "drop"
  )

方案2：简化left_join的链式操作 #

如果确定两个表的连接键完全匹配，也可以用管道简化原逻辑，避免生成中间变量：

library(dplyr)

output = input1 %>%
  left_join(input2, by = c('Date', 'Name', 'ReportType', 'TestType', 'Code1', 'Code2', 'Result2')) %>%
  mutate(
    Result1 = paste(Result1.x, "+", Result1.y),
    Value = Value.x + Value.y
  ) %>%
  select(Date, Name, ReportType, TestType, Code1, Code2, Result1, Result2, Value)

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