在R中按多变量分组聚合表格数据的实现方法

首先还原你提供的示例数据：

df <- data.frame(
  Species = c("A", "A", "B", "A"),
  Round = c(1, 1, 1, 2),
  `# of individuals` = c(3, 2, 2, 2)
)

一、按Species分组、以Round为列的个体总数汇总表 #

Base R 实现 #

先用aggregate按Species和Round分组求和，再用reshape转换成宽表：

# 分组求和
sum_agg <- aggregate(`# of individuals` ~ Species + Round, data = df, FUN = sum)
# 转宽表
sum_wide <- reshape(sum_agg, idvar = "Species", timevar = "Round", direction = "wide")
# 优化列名（可选）
colnames(sum_wide) <- gsub("# of individuals\\.", "Round_", colnames(sum_wide))
print(sum_wide)

tidyverse 实现 #

用dplyr分组聚合，搭配tidyr的pivot_wider转宽表，代码更直观：

library(dplyr)
library(tidyr)

sum_wide_tidy <- df %>%
  group_by(Species, Round) %>%
  summarize(total_individuals = sum(`# of individuals`), .groups = "drop") %>%
  pivot_wider(names_from = Round, values_from = total_individuals, 
              names_prefix = "Round_", values_fill = 0)

print(sum_wide_tidy)

二、按Species分组、以Round为列的出现次数汇总表 #

这里的“出现次数”指每个Species在对应Round下的记录行数。

Base R 实现 #

# 分组计数
count_agg <- aggregate(Species ~ Species + Round, data = df, FUN = length)
colnames(count_agg)[3] <- "occurrence_count"
# 转宽表
count_wide <- reshape(count_agg, idvar = "Species", timevar = "Round", direction = "wide")
colnames(count_wide) <- gsub("occurrence_count\\.", "Round_", colnames(count_wide))
print(count_wide)

tidyverse 实现 #

count_wide_tidy <- df %>%
  group_by(Species, Round) %>%
  summarize(occurrence_count = n(), .groups = "drop") %>%
  pivot_wider(names_from = Round, values_from = occurrence_count, 
              names_prefix = "Round_", values_fill = 0)

print(count_wide_tidy)

说明：你提到的AGGREGATE对应Base R里的aggregate函数，GROUP BY逻辑在tidyverse的dplyr::group_by中实现，核心是先完成分组聚合，再通过转宽表将Round转为列名，满足输出需求。

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