如何在R中合并多列分组统计结果生成汇总表

需要基于给定数据生成出版级表格，计算city、race、gender每列中各组的attend、fail、both占比（即每组中对应变量的均值乘以100），最终将所有分组作为行，attend、fail、both作为列。

之前尝试分别计算各列分组的百分比后用kableExtra合并，但操作混乱且结果错误，初始代码如下：

race_percentages <- d %>%
  group_by(race) %>%
  summarize(
    percent_attend = mean(attend) * 100,
    percent_fail = mean(fail) * 100,
    percent_both = mean(both) * 100)

gender_percentages <- d %>%
  group_by(gender) %>%
  summarize(
    percent_attend = mean(attend) * 100,
    percent_fail = mean(fail) * 100,
    percent_both = mean(both) * 100)

city_percentages <- d %>%
  group_by(city) %>%
  summarize(
    percent_attend = mean(attend) * 100,
    percent_fail = mean(fail) * 100,
    percent_both = mean(both) * 100)

预期表格格式：

给定数据：

d<-structure(list(city = structure(c(9, 6, 9, 12, 12, 6, 6, 12, 
12, 6, 6, 9, 12, 12, 6, 6, 9, 6, 9, 6, 6, 12, 12, 12, 6, 12, 
9, 6, 12, 6), format.stata = "%9.0g"), race = structure(c(3, 
3, 3, 3, 3, 3, 2, 3, 3, 3, 3, 3, 2, 2, 2, 2, 2, 2, 2, 3, 2, 2, 
2, 2, 2, 2, 2, 3, 3, 2), format.stata = "%9.0g", labels = c(White = 1, 
Black = 2, Hispanic = 3, Other = 4), class = c("haven_labelled", 
"vctrs_vctr", "double")), gender = structure(c(0, 1, 0, 1, 0, 
0, 1, 0, 1, 0, 1, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 1, 0, 0, 0, 1, 
1, 0, 0, 0), label = "gender of subject", format.stata = "%12.0g", labels = c(female = 0, 
male = 1), class = c("haven_labelled", "vctrs_vctr", "double"
)), attend = structure(c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), format.stata = "%9.0g"), 
    fail = structure(c(0, 1, 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 1, 
    1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1), format.stata = "%9.0g"), 
    both = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), row.names = c(NA, 
-30L), class = c("tbl_df", "tbl", "data.frame"))

步骤1：处理标签变量 #

先把haven_labelled类型的变量转换为可读文本标签，避免表格显示数字编码：

library(tidyverse)
library(haven)

# 转换标签为文本，给city添加前缀区分分组
d_clean <- d %>%
  mutate(
    race = as_factor(race),
    gender = as_factor(gender),
    city = paste0("City", city)
  )

步骤2：统一计算所有分组占比 #

用pivot_longer将分组变量转为长格式，统一计算占比后整理成目标结构：

# 转换格式并聚合计算
result <- d_clean %>%
  pivot_longer(cols = c(city, race, gender), names_to = "group_type", values_to = "group_value") %>%
  group_by(group_value) %>%
  summarize(
    Attend = mean(attend) * 100,
    Fail = mean(fail) * 100,
    Both = mean(both) * 100,
    .groups = "drop"
  ) %>%
  rename(Group = group_value) %>%
  # 调整行顺序，匹配预期表格
  arrange(
    case_when(
      str_detect(Group, "Black|Hispanic") ~ 1,
      str_detect(Group, "male|female") ~ 2,
      str_detect(Group, "City") ~ 3
    ),
    Group
  )

步骤3：生成出版级表格 #

用kableExtra生成格式化表格：

library(kableExtra)

result %>%
  mutate(across(c(Attend, Fail, Both), ~sprintf("%.1f%%", .x))) %>%
  kbl(caption = "各分组Attend/Fail/Both占比", align = "c") %>%
  kable_styling(
    bootstrap_options = c("striped", "hover", "condensed"),
    full_width = FALSE,
    position = "left"
  )

最终表格 #

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