如何在R的gt包汇总行中实现列合并？

我用R的gt包制作展示级表格，希望将计数及其占总计的百分比以「计数(百分比)」的紧凑格式显示。普通行通过cols_merge()已实现该效果，但小计和总计行无法应用该合并，导致汇总行仅显示计数缺失百分比。

数据与已尝试操作 #

1. 数据构造代码：

library(tidyverse)
library(gt)

df <- tibble(country_lane = c("CA-US", "MX-CA", "MX-US", "US-CA", 
                            "US-MX", "US-US"), 
                 `Above Market` = c(2L, 3L, 33L, 3L, 3L, 54L), 
                 `Below Market` = c(18L, 0L, 2L, 14L, 3L, 370L), 
                 `In Market` = c(15L, 0L, 25L, 15L, 3L, 240L),
             BU = c("US", "CAN", "US", "CAN", "US", "US"))

2. 已完成操作：生成百分比列、通过summary_rows()和grand_summary_rows()添加小计与总计行并格式化百分比，但执行cols_merge()后汇总行未合并对应百分比列。

解决方案 #

cols_merge()仅作用于原始数据行，对summary_rows()/grand_summary_rows()生成的汇总行无效。我们可以直接在生成汇总行时构造「计数(百分比)」格式的内容，替代先分列再合并的思路。

完整代码实现

library(tidyverse)
library(gt)
library(glue)

df <- tibble(country_lane = c("CA-US", "MX-CA", "MX-US", "US-CA", 
                            "US-MX", "US-US"), 
                 `Above Market` = c(2L, 3L, 33L, 3L, 3L, 54L), 
                 `Below Market` = c(18L, 0L, 2L, 14L, 3L, 370L), 
                 `In Market` = c(15L, 0L, 25L, 15L, 3L, 240L),
             BU = c("US", "CAN", "US", "CAN", "US", "US"))

# 预计算各列总计，用于百分比计算
col_totals <- df %>% 
  select(`Above Market`, `Below Market`, `In Market`) %>% 
  colSums()

# 自定义函数：生成「计数(百分比)」格式文本
format_count_pct <- function(x, total) {
  count <- sum(x, na.rm = TRUE)
  pct <- ifelse(total == 0, 0, count / total * 100)
  glue("{count}({sprintf('%.1f%%', pct)})")
}

# 构建gt表格
gt_table <- df %>%
  gt(groupname_col = "BU") %>%
  # 处理原始行：生成合并格式列并替换原列
  mutate(
    across(
      c(`Above Market`, `Below Market`, `In Market`),
      ~ glue("{.x}({sprintf('%.1f%%', .x / col_totals[[cur_column()]] * 100)})"),
      .names = "{.col}_formatted"
    )
  ) %>%
  cols_merge(`Above Market`, `Above Market_formatted`, pattern = "{2}") %>%
  cols_merge(`Below Market`, `Below Market_formatted`, pattern = "{2}") %>%
  cols_merge(`In Market`, `In Market_formatted`, pattern = "{2}") %>%
  # 处理分组小计行：直接生成合并格式内容
  summary_rows(
    groups = TRUE,
    columns = c(`Above Market`, `Below Market`, `In Market`),
    fns = list(
      `Above Market` = ~ format_count_pct(.x, col_totals[["Above Market"]]),
      `Below Market` = ~ format_count_pct(.x, col_totals[["Below Market"]]),
      `In Market` = ~ format_count_pct(.x, col_totals[["In Market"]])
    ),
    formatter = fmt_markdown
  ) %>%
  # 处理总计行：同样生成合并格式内容
  grand_summary_rows(
    columns = c(`Above Market`, `Below Market`, `In Market`),
    fns = list(
      `Above Market` = ~ format_count_pct(.x, col_totals[["Above Market"]]),
      `Below Market` = ~ format_count_pct(.x, col_totals[["Below Market"]]),
      `In Market` = ~ format_count_pct(.x, col_totals[["In Market"]])
    ),
    formatter = fmt_markdown
  ) %>%
  # 可选：添加样式区分汇总行
  tab_style(
    style = cell_text(weight = "bold"),
    locations = cells_summary()
  ) %>%
  tab_style(
    style = cell_text(weight = "bold", color = "#c0392b"),
    locations = cells_grand_summary()
  )

gt_table

关键要点

1. 预计算列总计：先算出每列的总计数，确保百分比计算的一致性
2. 自定义格式化函数：format_count_pct()同时计算计数和占比，直接拼接成目标格式
3. 汇总行直接生成合并内容：绕过cols_merge()的限制，在summary_rows()和grand_summary_rows()中直接传入格式化后的文本，用fmt_markdown确保格式正确解析
4. 保留原始行合并逻辑：继续用cols_merge()处理普通行，保持原有实现逻辑

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