如何用gt或flextable灵活分组展示分年龄组X/Y统计数据

现有两组分年龄组的计数数据（X和Y），原始数据框如下：

# 原始数据框
dataX <- data.frame(
  "ID" = 1:6,
  "X_0_to_2" = c(58, 58, 46, 98, 43, 39),
  "X_3_to_5" = c(47, 34, 51, 95, 29, 66),
  "X_6_to_8" = c(81, 95, 97, 79, 49, 63),
  "X_9_to_11" = c(34, 71, 39, 70, 41, 13),
  "X_12_to_14" = c(50, 75, 35, 67, 92, 61)
)

dataY <- data.frame(
  "ID" = 1:6,
  "Y_0_to_2" = c(85, 20, 58, 77, 30, 92),
  "Y_3_to_5" = c(42, 96, 63, 10, 58, 77),
  "Y_6_to_8" = c(88, 40, 17, 82, 58, 10),
  "Y_9_to_11" = c(55, 69, 38, 53, 15, 49),
  "Y_12_to_14" = c(22, 73, 92, 78, 41, 20)
)

已整理为tidy格式的数据：

tidy_data <- structure(list(ID = c(1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L, 5L, 5L, 
6L, 6L), X_Y = c("X", "Y", "X", "Y", "X", "Y", "X", "Y", "X", 
"Y", "X", "Y"), `0_to_2` = c(58, 85, 58, 20, 46, 58, 98, 77, 
43, 30, 39, 92), `3_to_5` = c(47, 42, 34, 96, 51, 63, 95, 10, 
29, 58, 66, 77), `6_to_8` = c(81, 88, 95, 40, 97, 17, 79, 82, 
49, 58, 63, 10), `9_to_11` = c(34, 55, 71, 69, 39, 38, 70, 53, 
41, 15, 13, 49), `12_to_14` = c(50, 22, 75, 73, 35, 92, 67, 78, 
92, 41, 61, 20), Total = c(270, 292, 333, 298, 268, 268, 409, 
300, 254, 202, 242, 248)), row.names = c(NA, -12L), class = c("tbl_df", 
"tbl", "data.frame"))

希望将数据展示为如下层级表头样式：

Age 0-2     Age 3-5  ...    Total
      X     Y  |  X     Y  ...   X     Y
ID |           |           ...
1  | 58    85  | 47    42  ... 105   127
2  | 58    20  | 34    96  ...  92   116
3  | 46    58  | 51    63  ...  97   121

手动合并排序后用gt::tab_spanner()的方案灵活性不足，寻求基于gt或flextable的简便实现方法。

方法一：使用gt包 #

先将tidy数据转换为宽格式，再通过层级表头配置实现需求：

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

# 转换为宽格式：每个年龄组和Total拆分出X/Y两列
wide_data <- tidy_data %>%
  pivot_wider(
    id_cols = ID,
    names_from = X_Y,
    values_from = c(`0_to_2`, `3_to_5`, `6_to_8`, `9_to_11`, `12_to_14`, Total)
  )

# 构建gt表格
gt_table <- wide_data %>%
  gt() %>%
  # 配置顶层表头（年龄组/Total）
  tab_spanner(label = "Age 0-2", columns = c(`0_to_2_X`, `0_to_2_Y`)) %>%
  tab_spanner(label = "Age 3-5", columns = c(`3_to_5_X`, `3_to_5_Y`)) %>%
  tab_spanner(label = "Age 6-8", columns = c(`6_to_8_X`, `6_to_8_Y`)) %>%
  tab_spanner(label = "Age 9-11", columns = c(`9_to_11_X`, `9_to_11_Y`)) %>%
  tab_spanner(label = "Age 12-14", columns = c(`12_to_14_X`, `12_to_14_Y`)) %>%
  tab_spanner(label = "Total", columns = c(Total_X, Total_Y)) %>%
  # 修改第二层表头为X/Y
  cols_label(
    `0_to_2_X` = "X", `0_to_2_Y` = "Y",
    `3_to_5_X` = "X", `3_to_5_Y` = "Y",
    `6_to_8_X` = "X", `6_to_8_Y` = "Y",
    `9_to_11_X` = "X", `9_to_11_Y` = "Y",
    `12_to_14_X` = "X", `12_to_14_Y` = "Y",
    Total_X = "X", Total_Y = "Y"
  ) %>%
  # 可选：添加垂直分割线区分年龄组
  tab_style(
    style = list(
      cell_borders(sides = "right", color = "black", weight = px(2))
    ),
    locations = cells_body(columns = c(`0_to_2_Y`, `3_to_5_Y`, `6_to_8_Y`, `9_to_11_Y`))
  )

gt_table

方法二：使用flextable包 #

flextable支持更灵活的表头合并，步骤如下：

library(flextable)
library(dplyr)
library(tidyr)

# 转换为宽格式
wide_data <- tidy_data %>%
  pivot_wider(
    id_cols = ID,
    names_from = X_Y,
    values_from = c(`0_to_2`, `3_to_5`, `6_to_8`, `9_to_11`, `12_to_14`, Total)
  )

# 构建flextable
ft_table <- flextable(wide_data) %>%
  # 定义第二层表头为X/Y
  set_header_labels(
    ID = "ID",
    `0_to_2_X` = "X", `0_to_2_Y` = "Y",
    `3_to_5_X` = "X", `3_to_5_Y` = "Y",
    `6_to_8_X` = "X", `6_to_8_Y` = "Y",
    `9_to_11_X` = "X", `9_to_11_Y` = "Y",
    `12_to_14_X` = "X", `12_to_14_Y` = "Y",
    Total_X = "X", Total_Y = "Y"
  ) %>%
  # 合并表头单元格，设置顶层标签
  merge_at(i = 1, j = 2:3, part = "header", value = as_paragraph("Age 0-2")) %>%
  merge_at(i = 1, j = 4:5, part = "header", value = as_paragraph("Age 3-5")) %>%
  merge_at(i = 1, j = 6:7, part = "header", value = as_paragraph("Age 6-8")) %>%
  merge_at(i = 1, j = 8:9, part = "header", value = as_paragraph("Age 9-11")) %>%
  merge_at(i = 1, j = 10:11, part = "header", value = as_paragraph("Age 12-14")) %>%
  merge_at(i = 1, j = 12:13, part = "header", value = as_paragraph("Total")) %>%
  # 可选：添加垂直分割线
  hline_right(
    j = c(3,5,7,9,11), part = "all",
    border = fp_border(color = "black", width = 2)
  ) %>%
  # 应用预设主题优化样式
  theme_booktabs()

ft_table

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