在R中使用gt()实现表格上方的分类小计与总计

需求说明 #

众所周知，R对列的处理较为便捷，但行处理难度较高；而Excel在行处理（尤其是小计功能）上表现出色。需要在R中实现Excel风格的表格，核心需求：

• 分类小计置于对应分组的上方
• 总计置于第一个分组的上方
• 替换总计行的标签为ALL，并格式化所有汇总行

修改后的实现代码 #

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

countrypops |>
  filter(country_code_2 %in% c("BR", "RU", "IN", "CN", "FR", "DE", "IT", "GB")) |>
  filter(year %% 10 == 0) |>
  select(country_name, year, population) |>
  pivot_wider(names_from = year, values_from = population) |>
  gt(rowname_col = "country_name") |>
  # 定义分组
  tab_row_group(
    label = md("*BRIC*"),
    rows = c("Brazil", "Russian Federation", "India", "China"),
    id = "bric"
  ) |>
  tab_row_group(
    label = md("*Big Four*"),
    rows = c("France", "Germany", "Italy", "United Kingdom"),
    id = "big4"
  ) |>
  row_group_order(groups = c("bric", "big4")) |>
  tab_spanner(columns = everything(), label = "Year") |>
  fmt_number(n_sigfig = 3, suffixing = TRUE) |>
  # 总计行：置于最顶部，标签为ALL
  grand_summary_rows(
    fns = list(label = md("**ALL**"), fn = "sum"),
    fmt = ~ fmt_number(., n_sigfig = 3, suffixing = TRUE),
    side = "top"
  ) |>
  # BRIC分组小计：置于分组上方
  summary_rows(
    groups = "bric",
    fns = list(label = md("**BRIC小计**"), fn = "sum"),
    fmt = ~ fmt_number(., n_sigfig = 3, suffixing = TRUE),
    side = "top"
  ) |>
  # Big Four分组小计：置于分组上方
  summary_rows(
    groups = "big4",
    fns = list(label = md("**Big Four小计**"), fn = "sum"),
    fmt = ~ fmt_number(., n_sigfig = 3, suffixing = TRUE),
    side = "top"
  ) |>
  # 统一格式化所有汇总行样式
  tab_style(
    locations = cells_summary(),
    style = cell_fill(color = adjust_luminance("lightblue", steps = +1))
  ) |>
  tab_style(
    locations = cells_grand_summary(),
    style = cell_fill(color = adjust_luminance("lightblue", steps = +1))
  )

关键改动说明 #

1. 总计行实现：使用grand_summary_rows替代原有的summary_rows，通过side="top"将总计行放在所有分组最上方，同时将标签设置为**ALL**
2. 分组小计实现：针对每个分组ID（bric/big4）单独调用summary_rows，指定groups参数和side="top"，让小计行显示在对应分组的上方，并自定义小计标签
3. 汇总行格式化：分别对分组小计行（cells_summary()）和总计行（cells_grand_summary()）设置统一的浅蓝填充样式，实现汇总行的视觉区分
4. 保留原有格式：延续原代码的数字格式化逻辑，确保数据展示的一致性

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