如何使用summarise(across())同时计算求和与均值

需求说明 #

需要对数据集的W、F、LF、S列按year和SampleDate分组求和，对temp和turb列分组求均值，最终用gt生成带汇总行的表格。原代码对所有列统一求和，无法实现预期效果。

修改后的代码 #

library(dplyr)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1), temp = c(45L, 46L, 47L, 48L, 45L, 
46L, 47L, 48L, 45L, 46L, 47L, 48L, 45L, 46L, 47L, 48L), turb = c(14L, 
15L, 16L, 17L, 14L, 15L, 16L, 17L, 14L, 15L, 16L, 17L, 14L, 15L, 
16L, 17L)), row.names = c(NA, -16L), class = "data.frame")

a |>
  mutate(SampleDate = as.character(SampleDate)) |>
  group_by(year, SampleDate) |>
  summarise(
    # 对指定列求和
    across(c(W, F, LF, S), sum),
    # 对指定列求均值
    across(c(temp, turb), mean),
    .groups = "drop"
  ) |>
  gt() |>
  summary_rows(
    columns = -c(SampleDate, temp, turb),
    fns = list(label = "总计", fn = "sum"),
    side = "bottom"
  ) |>
  summary_rows(
    columns = c(temp, turb),
    fns = list(label = "均值", fn = "mean"),
    side = "bottom"
  )

关键修改点 #

1. 拆分across()调用：在summarise中使用两个独立的across()，分别给目标列指定sum和mean函数，解决原代码统一求和的问题。
2. 添加.groups = "drop"：分组聚合后取消分组，确保数据结构符合gt的处理要求，避免潜在的分组残留问题。
3. 汇总行逻辑保留：原有的summary_rows部分可以直接沿用，分别对计数列和均值列生成对应的汇总行。

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