如何在R中使用Group By实现Excel数据透视表的级联分组汇总逻辑

现有代码说明 #

你当前编写的代码已经可以实现userID、year、month、type、category五个维度最细粒度的分组求和，缺少的是Excel透视表自带的各级父维度的小计、合计行，也就是你所说的级联分层汇总效果，以下提供两种常用的实现方案：

方案1：生成带层级汇总的结构化数据集 #

使用dplyr内置的rollup函数可以直接实现层级聚合，自动生成所有上层维度的汇总结果，代码如下：

library(tidyverse)

# 维度按从高到低的层级传入rollup，自动生成各层级汇总行
data_group <- data_merged %>%
  group_by(rollup(userID, year, month, type, category)) %>%
  summarize(balance = sum(price, na.rm = TRUE), .groups = "drop") %>%
  as.data.frame()

输出结果中，上层维度汇总行对应的下层维度会显示NA，例如单用户全时段总消费的行，year、month、type、category字段都会为NA，你可以按需将NA替换为"合计"类文本提升可读性。

方案2：生成可交互的类Excel透视表 #

使用pivottabler包可以直接输出和Excel操作逻辑完全一致的透视表，支持层级折叠、一键导出Excel等功能，和你预期的效果完全匹配：

# 首次使用先安装包
# install.packages("pivottabler")
library(pivottabler)

pt <- PivotTable$new()
pt$addData(data_merged)
# 按层级依次添加行维度，自定义合计文本
pt$addRowDataGroups("userID", addTotal = TRUE, totalCaption = "用户合计")
pt$addRowDataGroups("year", addTotal = TRUE, totalCaption = "年度合计")
pt$addRowDataGroups("month", addTotal = TRUE, totalCaption = "月度合计")
pt$addRowDataGroups("type", addTotal = TRUE, totalCaption = "类型合计")
pt$addRowDataGroups("category", addTotal = TRUE, totalCaption = "品类合计")
# 定义汇总规则：对price求和
pt$defineCalculation(calculationName = "总消费金额", summariseExpression = "sum(price, na.rm = TRUE)")
# 在RStudio Viewer面板渲染可交互透视表，支持点击展开/折叠层级
pt$renderPivot()

# 如需导出到Excel，执行以下代码即可，导出的Excel会保留层级折叠结构
# pt$writeToXLSX("用户消费透视表.xlsx")

注意事项 #

• 若数据中price存在空值，sum函数中加入na.rm = TRUE参数可以避免汇总结果出现NA
• 方案1的结果如果需要替换NA值为合计标识，可以在管道中追加以下代码：

  mutate(across(c(year, month, type, category), ~replace(., is.na(.), "合计")))
  

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