如何使用gt按分组（年份）添加分组总计？

问题描述 #

我有一段可复现的R代码，想按年份分组添加总计行。目前我能用janitor包的adorn_totals()添加全局总计，但不知道怎么给2012、2013每个年份单独加分组总计，求指点，谢谢。

原始数据与代码 #

library(dplyr)
library(tidyr)
library(janitor)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1)), class = "data.frame", row.names = c(NA, 
-16L))

# 原始处理代码（仅添加全局总计）
a %>% 
  group_by(year,SampleDate) %>% 
  summarise(W = sum(W), F = sum(F), LF = sum(LF), S = sum(S)) %>% 
  adorn_totals() %>% 
  gt(groupname_col = "year") %>% 
  tab_header(title = md("Fish Observations"), subtitle = md("Total number of fish by year"))

解决方案1：用dplyr+janitor生成带分组总计的数据框 #

通过group_modify()对每个年份分组单独应用adorn_totals()，生成包含分组总计行的数据框，后续可灵活用于各种输出：

a_processed <- a %>%
  # 按年份和日期聚合
  group_by(year, SampleDate) %>%
  summarise(W = sum(W), F = sum(F), LF = sum(LF), S = sum(S), .groups = "drop_last") %>%
  # 对每个年份分组添加总计行
  group_modify(~ adorn_totals(.x, where = "row", name = "年度总计")) %>%
  ungroup()

# 生成gt表格
a_processed %>%
  gt(groupname_col = "year") %>%
  tab_header(title = md("鱼类观测数据"), subtitle = md("按年份统计的鱼类总数"))

解决方案2：直接在gt表格中添加分组总计 #

如果只需要可视化输出，gt包的summary_rows()可以直接在表格层面为每个分组添加总计，无需修改原始聚合后的数据框：

a %>%
  group_by(year, SampleDate) %>%
  summarise(W = sum(W), F = sum(F), LF = sum(LF), S = sum(S)) %>%
  gt(groupname_col = "year") %>%
  tab_header(title = md("鱼类观测数据"), subtitle = md("按年份统计的鱼类总数")) %>%
  # 为每个年份分组添加总计行
  summary_rows(
    groups = TRUE,  # 针对每个分组生效
    fns = list(总计 = ~sum(.)),  # 求和函数
    formatter = fmt_number,  # 格式化数字
    decimals = 0  # 保留0位小数
  )

两种方法都能实现按年份添加分组总计的需求，你可以根据后续是否需要复用数据框来选择。

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