在R语言中对重复行的单列数值求和的实现方法

在R语言中处理数据集时，需要实现：当两行或多行除Num列外的其他所有列值完全相同时，将这些行的Num列数值求和聚合。已掌握两列数据的处理方式，但不清楚多列场景的操作方法。

示例数据 #

原始数据 #

User    Date    Sex    Age  Ethic   Med    Num  Diag   Dis  Doc   Month
27168   1/1/2002    1   49  NULL    42506   1   2000    0   13545   1
27168   1/1/2002    1   49  NULL    42506   9   2000    0   13545   1
27168   1/1/2002    1   49  NULL    42506   5   2000    0   13545   1
27168   1/1/2002    1   49  NULL    34533   1   2000    0   13545   1
12335   2/1/2002    0   87  2       42506   2   8654    2   34568   1
12335   2/1/2002    0   87  2       65873   4   8654    2   34568   1
12335   2/1/2002    0   87  2       65873   14  8654    2   34568   1
12335   2/1/2002    0   87  2       65825   6   8654    2   34568   1
12335   2/1/2002    0   87  2       65873   4   8654    2   34568   1 

目标效果 #

User    Date    Sex    Age  Ethic   Med    Num  Diag   Dis  Doc   Month
27168   1/1/2002    1   49  NULL    42506   15  2000    0   13545   1
27168   1/1/2002    1   49  NULL    34533   1   2000    0   13545   1
12335   2/1/2002    0   87  2       42506   2   8654    2   34568   1
12335   2/1/2002    0   87  2       65873   18  8654    2   34568   1
12335   2/1/2002    0   87  2       65825   6   8654    2   34568   1

真实数据集 #

structure(list(Adm.Code = c(2716882L, 2716882L, 2716884L, 2716884L, 
2716884L, 2716885L, 2716885L, 2716885L, 2716885L, 2716886L, 2716886L, 
2716886L, 2716886L, 2716889L, 2716889L, 2716889L, 2716889L, 2716889L, 
2716889L, 2716889L, 2716896L, 2716896L, 2716896L, 2716896L, 2716896L, 
2716896L, 2716896L, 2716896L, 2716896L, 2716899L), Date = c("1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01", 
"1397-01-01", "1397-01-01", "1397-01-01", "1397-01-01"), Sex = c(1L, 
1L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L), Old = c(0L, 
0L, 23L, 23L, 23L, 49L, 49L, 49L, 49L, 24L, 24L, 24L, 24L, 22L, 
22L, 22L, 22L, 22L, 22L, 22L, 35L, 35L, 35L, 35L, 35L, 35L, 35L, 
35L, 35L, 29L), BloodGroup = c("0", "0", "NULL", "NULL", "NULL", 
"NULL", "NULL", "NULL", "NULL", "0", "0", "0", "0", "0", "0", 
"0", "0", "0", "0", "0", "NULL", "NULL", "NULL", "NULL", "NULL", 
"NULL", "NULL", "NULL", "NULL", "0"), Service.Int.Code = c(32603L, 
32602L, 42506L, 32603L, 32602L, 42506L, 32603L, 32602L, 34533L, 
32603L, 32602L, 42506L, 34533L, 42506L, 32603L, 32602L, 42506L, 
42506L, 32603L, 32602L, 34533L, 42506L, 32603L, 32602L, 34533L, 
32603L, 32602L, 32603L, 32602L, 42506L), Num = c(1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 2, 5, 1, 1, 1, 1, 1, 1, 1, 4, 3, 1, 1, 8, 
1, 1, 1, 1, 3), DiagCode = c(2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L), DiseaseTag = c(0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), Doctor.Code = c(13545L, 
13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 
13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 
13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 13545L, 
13545L, 13545L, 13545L, 13545L, 13545L), month = c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L)), row.names = c(2L, 
3L, 6L, 11L, 12L, 23L, 28L, 29L, 44L, 48L, 49L, 53L, 61L, 73L, 
80L, 81L, 82L, 84L, 88L, 89L, 94L, 96L, 107L, 108L, 117L, 128L, 
129L, 135L, 136L, 139L), class = "data.frame")

解决方案 #

方法1：Base R 原生函数 aggregate #

使用aggregate函数，通过~ .指定除Num列外的所有列作为分组依据，对Num列求和：

# 假设数据集名为df
agg_df <- aggregate(Num ~ ., data = df, FUN = sum)

• 原理：~ .表示将公式左侧（Num）以外的所有列作为分组变量，对每个分组内的Num值执行求和操作。

方法2：tidyverse 框架（dplyr包） #

如果习惯使用tidyverse语法，可通过group_by结合summarise实现，用across(-Num)选中除Num外的所有列作为分组键：

library(dplyr)

agg_df <- df %>%
  group_by(across(-Num)) %>%
  summarise(Num = sum(Num), .groups = "drop")

• 说明：.groups = "drop"用于聚合后取消分组状态，返回普通数据框；若使用旧版dplyr，可替换为group_by_at(vars(-Num))。

验证效果 #

运行上述任一代码后，即可得到和示例目标效果一致的聚合结果：相同非Num列组合的行被合并，Num列值为对应分组的求和值。

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