如何用Tidyverse与Pivot_Wider构建双层聚合汇总表

解决方案 #

使用 tidyverse 工具链可以高效实现你需要的双层聚合汇总表，以下是完整代码：

library(tidyverse)
library(kableExtra)

# 示例数据
df <- structure(list(Year = c(2019L, 2019L, 2019L, 2019L, 2019L, 2019L, 
2019L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2020L, 2021L, 
2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 2021L, 2022L, 2022L, 
2022L, 2022L, 2022L, 2022L, 2022L, 2022L), ExamType = c("A", 
"B", "A", "A", "B", "A", "B", "B", "B", "A", "B", "B", "A", "B", 
"B", "B", "A", "A", "A", "B", "B", "A", "B", "B", "A", "A", "A", 
"B", "A", "B"), ExamScore = c(1L, 2L, 2L, 3L, 1L, 4L, 4L, 5L, 
2L, 1L, 4L, 3L, 2L, 5L, 1L, 4L, 3L, 2L, 1L, 2L, 5L, 4L, 4L, 3L, 
1L, 2L, 5L, 4L, 3L, 1L), Region = c("North", "South", "East", 
"East", "North", "South", "West", "East", "South", "South", "West", 
"East", "North", "South", "West", "East", "North", "South", "West", 
"East", "North", "West", "West", "East", "North", "South", "West", 
"East", "West", "North"), Gender = c("M", "M", "F", "F", "M", 
"F", "F", "F", "M", "M", "M", "M", "M", "M", "F", "F", "M", "F", 
"M", "F", "F", "M", "F", "F", "F", "M", "M", "F", "M", "M"), 
    Race = c("White", "Black", "Other", "Other", "Latinx", "White", 
    "Latinx", "Black", "Other", "White", "Latinx", "Black", "Other", 
    "White", "Latinx", "Black", "Other", "White", "Latinx", "Latinx", 
    "Black", "Other", "White", "White", "Black", "Latinx", "White", 
    "Black", "Latinx", "Other")), class = "data.frame", row.names = c(NA, 
-30L))

# 1. 将多协变量转为长格式，统一处理
df_long <- df %>%
  pivot_longer(cols = c(Region, Gender, Race),
               names_to = "Covariate",
               values_to = "Category")

# 2. 双层分组计算统计量
summary_data <- df_long %>%
  group_by(ExamType, Year, Category) %>%
  summarize(
    均值 = round(mean(ExamScore), 2),
    标准差 = round(sd(ExamScore), 2),
    样本量 = n(),
    .groups = "drop"
  )

# 3. 重塑为宽格式，按协变量类别分组排列列
summary_wide <- summary_data %>%
  pivot_longer(cols = c(均值, 标准差, 样本量),
               names_to = "统计量",
               values_to = "数值") %>%
  unite(col = "类别_统计量", Category, 统计量, sep = "_") %>%
  pivot_wider(names_from = "类别_统计量", values_from = "数值") %>%
  arrange(ExamType, Year)

# 4. 格式化为目标样式的表格
summary_wide %>%
  mutate(ExamType = ifelse(duplicated(ExamType), "", ExamType)) %>%
  kable(col.names = gsub("_(均值|标准差|样本量)", "", colnames(.)),
        align = "c") %>%
  kable_styling(full_width = FALSE) %>%
  add_header_above(c(" " = 2, 
                     "Region" = 12,  # 4类别×3统计量
                     "Gender" = 6,   # 2类别×3统计量
                     "Race" = 12)) %>% # 4类别×3统计量
  collapse_rows(columns = 1, valign = "top")

代码说明 #

1. 统一协变量格式：用pivot_longer把Region、Gender、Race合并成一列，方便后续统一分组计算。
2. 双层聚合统计：按ExamType和Year分组，对每个类别计算均值、标准差和样本量，保留两位小数提升可读性。
3. 重塑宽格式：先把统计量拆成长格式，再合并类别与统计量的名称，最后转成宽格式，确保每个类别对应的三个统计量连续排列。
4. 表格美化：用kableExtra实现合并单元格、添加表头分组，完全匹配你想要的层级展示效果。

输出效果 #

生成的表格会自动把同一ExamType下的年份行合并表头，每个协变量类别下依次展示均值、标准差、样本量，和你提供的示例样式一致。

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