按日期与Type汇总大型DataFrame值时求和异常的问题排查

问题描述 #

现有如下结构的DataFrame：

structure(list(Date = structure(c(18220, 18220, 18220, 
18955, 19110, 19110, 18514, 18514, 18892, 18647, 18647, 18528, 
18822, 18822, 18822, 18822, 18745, 18745, 18745), class = "Date"), 
    Type = c("Main", "Main", "Main", 
    "Main", "Secondary", "Tri-Annual", "Main", 
    "Main", "Tri-Annual", "Main", "Syndication", 
    "Main", "Tri-Annual", "Syndication", "Secondary", 
    "Main", "Main", "Tri-Annual", "Secondary"
    ), Value = c(4000, 2250, 2250, 1100, 1800, 12000, 
    8000, 9000, 10000, 6500, 7000, 6500, 7000, 4250, 5500, 2500, 
    6000, 6000, 4500), buckets = c("Long", "Long", "Long", 
    "Long", "Long", "Medium", "Medium", "Long", "Medium", "Long", 
    "Long", "Long", "Long", "Long", "Long", "Long", "Long", "Long", 
    "Long")), row.names = c(NA, -19L), class = c("tbl_df", "tbl", 
"data.frame"))

尝试通过以下代码按Date和Type分组对Value求和：

df <- df %>% group_by(Date, Type) %>% dplyr::summarise(Aggregated = sum(df$Value))

但结果未实现按每日每种Type的Value求和，而是将所有Value的总和填入Aggregated列。

错误原因与修正方案 #

错误原因 #

在dplyr::summarise中使用df$Value会直接引用原始未分组的整个DataFrame的Value列，完全忽略group_by创建的分组上下文，因此计算的是全局总和而非分组内的总和。

修正代码 #

去掉数据框前缀，直接使用列名Value即可，dplyr会自动识别当前分组的子集：

df <- df %>% 
  group_by(Date, Type) %>% 
  dplyr::summarise(Aggregated = sum(Value), .groups = "drop")

添加.groups = "drop"可在聚合后自动取消分组（可选，根据后续操作需求调整）。

验证结果 #

运行修正后的代码，会得到正确的分组求和结果，例如：

# 部分结果示例
# Date       Type          Aggregated
# <date>     <chr>              <dbl>
# 2020-01-06 Main                8500
# 2020-08-17 Main               17000
# 2020-08-31 Main                 6500
# ...

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