如何使用{gt}包的summary_row()返回值计算分组百分比？

解决方案 #

你遇到的问题核心是默认对所有指定列统一用sum()汇总，导致percent_successful列的汇总值为三行百分比的和，而非分组整体的成功率。有两种常用实现方案：

方案1：拆分summary_rows()调用，单独计算百分比列 #

通过两次调用summary_rows()，分别处理需要求和的数值列、需要单独计算的百分比列：

df %>% 
  group_by(division, department) %>% 
  gt() %>% 
  # 汇总需要求和的4列：widthraws到total_enrolled
  summary_rows(
    groups = TRUE,
    columns = widthraws:total_enrolled,
    missing_text = " ",
    fns = list(total = ~sum(.))
  ) %>%
  # 单独计算percent_successful列的汇总值：总成功数/总入学数
  summary_rows(
    groups = TRUE,
    columns = percent_successful,
    missing_text = " ",
    fns = list(
      total = ~ sum(.data$successfull[cur_group_rows()]) / sum(.data$total_enrolled[cur_group_rows()])
    ),
    formatter = fmt_number,
    decimals = 2
  )

注：cur_group_rows()可以获取当前分组在原数据中的行索引，直接调用对应列求和计算即可得到正确的分组整体成功率。

方案2：提前合并汇总行（更稳定可控） #

如果不想依赖gt的分组上下文逻辑，可以提前用dplyr计算好分组汇总行，和原数据合并后再生成表格，适配性更强：

library(dplyr)
library(gt)

# 计算分组汇总行
group_summ <- df %>%
  group_by(division, department) %>%
  summarise(
    course_num = "total",
    widthraws = sum(widthraws),
    unsucessful = sum(unsucessful),
    successfull = sum(successfull),
    total_enrolled = sum(total_enrolled),
    percent_successful = sum(successfull)/sum(total_enrolled),
    .groups = "drop"
  )

# 合并原数据与汇总行后生成gt表格
bind_rows(df, group_summ) %>%
  group_by(division, department) %>%
  gt(rowname_col = "course_num") %>%
  # 给汇总行加粗区分
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_stub(rows = course_num == "total")
  ) %>%
  fmt_number(columns = percent_successful, decimals = 2)

两种方案得到的汇总行percent_successful值均为0.57，符合需求。

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