如何一次性生成多科目多时间点的汇总表格（R语言）

我有一个名为df的数据集，想要生成按科目分组，每行展示年份、对应人数和平均分的汇总表格（目标为长表结构，每个科目下对应各年份的统计项）。我目前生成的表格格式不符合需求，不知道怎么把当前的宽表转成目标长表，或者是否需要在汇总前做额外处理，求指导。

原始数据集与当前尝试代码 #

df<-structure(list(Name = c("Jack", "Jack", "Jack", "Jack", "Jack", 
"Jack", "Jack", "Jack", "Jack", "Jack", "Jack", "Jack", "Jack", 
"Jack", "Marry", "Marry", "Marry", "Marry", "Marry", "Marry", 
"Marry", "Marry", "Marry", "Marry", "Marry", "Marry", "Marry", 
"Marry", "Tom", "Tom", "Tom", "Tom", "Tom", "Tom", "Tom", "Tom", 
"Tom", "Tom", "Tom", "Tom", "Tom"), Subject = c("Art", 
"Art", "Art", "Art", "Art", "Math", "Math", "Math", "Math", "Math", 
"Math", "ELA", "ELA", "ELA", "Art", "Art", "Art", "Art", "Art", 
"Math", "Math", "Math", "Math", "Math", "ELA", "ELA", "ELA", 
"ELA", "Art", "Art", "Art", "Art", "Math", "Math", "Math", "Math", 
"Math", "ELA", "ELA", "ELA", "ELA", "ELA"), Year = c(2001, 2002, 
2003, 2004, 2005, 2001, 2002, 2003, 2004, 2005, 2006, 2002, 2003, 
2006, 2001, 2002, 2003, 2004, 2005, 2001, 2002, 2004, 2005, 2006, 
2002, 2003, 2005, 2006, 2001, 2002, 2004, 2005, 2001, 2002, 2003, 
2004, 2006, 2002, 2003, 2004, 2005, 2006), Score = c(94, 94, 
68, 92, 97, 93, 100, 99, 75, 72, 83, 93, 75, 67, 83, 99, 86, 
90, 92, 65, 62, 99, 88, 61, 60, 79, 77, 72, 62, 83, 78, 87, 65, 
85, 62, 68, 79, 96, 84, 90, 69, 79)), class = c("tbl_df", "tbl", 
"data.frame"), row.names = c(NA, -42L))

# 当前尝试的代码
tbl <- df %>% group_by(Subject, Year) %>%
 summarise(n=n(),
           mean=mean(Score)) %>% t(.) %>% as.data.frame()

解决方案 #

无需先转置宽表，直接在汇总后通过tidyverse的函数调整结构即可生成目标样式表格：

完整代码

library(tidyverse)

# 生成目标样式表格
target_tbl <- df %>%
  # 按科目和年份分组统计
  group_by(Subject, Year) %>%
  summarise(
    人数 = n(),
    平均分 = mean(Score),
    .groups = "drop"  # 取消分组状态
  ) %>%
  # 将统计指标转为长表结构
  pivot_longer(
    cols = c(人数, 平均分),
    names_to = "统计指标",
    values_to = "数值"
  ) %>%
  # 将年份转为列，匹配目标格式
  pivot_wider(
    names_from = Year,
    values_from = 数值
  ) %>%
  # 按科目和指标排序，优化展示
  arrange(Subject, `统计指标`)

# 查看结果
print(target_tbl)

代码解释

1. 分组汇总：按Subject和Year分组，统计每个组合下的人数和平均分，.groups = "drop"确保后续处理不受分组状态影响。
2. 转长表：用pivot_longer把"人数""平均分"两个统计列转为"统计指标"和"数值"的长表结构，为后续调整列布局做准备。
3. 转目标宽表：用pivot_wider把Year转为列，每个年份对应指标的数值，得到目标样式——每个科目下分人数、平均分两行，各年份数值对应列。
4. 排序优化：用arrange按科目和指标排序，让表格结构更清晰易读。

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