如何在R数据框中按行标签分组计算列均值及标准差

原始数据 #

我有如下结构的R dataframe：

需求 #

需要计算：

• 2021、2022年所有水果销量的均值和标准差
• 每种水果（Apples、Bananas、Oranges）各年份的均值和标准差

最终输出如下格式的统计表格：

解决方案 #

方法1：使用dplyr + tidyr包 #

library(dplyr)
library(tidyr)

# 构造示例数据框（已有df可跳过）
df <- data.frame(
  水果 = c("Apples", "Bananas", "Apples", "Oranges", "Oranges", "Bananas"),
  2021 = c(12, 11, 44, 30, 19, 24),
  2022 = c(29, 31, 55, 73, 82, 78)
)

# 计算整体统计量
overall_stats <- df %>%
  summarise(across(c(`2021`, `2022`), list(mean = mean, sd = sd))) %>%
  pivot_longer(everything(), names_sep = "_", names_to = c("年份", "统计量")) %>%
  pivot_wider(names_from = 年份, values_from = value)

# 计算分组统计量
grouped_stats <- df %>%
  group_by(水果) %>%
  summarise(across(c(`2021`, `2022`), list(mean = mean, sd = sd))) %>%
  pivot_longer(-水果, names_sep = "_", names_to = c("年份", "统计量")) %>%
  pivot_wider(names_from = c(年份, 水果), values_from = value)

# 合并并整理成目标格式
final_stats <- bind_cols(overall_stats, grouped_stats %>% select(-统计量)) %>%
  rename(统计量 = 统计量) %>%
  select(统计量, `2021`, `2021_Apples` = `2021_Apples`, `2021_Bananas` = `2021_Bananas`, `2021_Oranges` = `2021_Oranges`,
         `2022`, `2022_Apples` = `2022_Apples`, `2022_Bananas` = `2022_Bananas`, `2022_Oranges` = `2022_Oranges`) %>%
  mutate(统计量 = ifelse(统计量 == "mean", "均值", "标准差"))

# 查看结果
print(final_stats)

方法2：使用data.table包 #

library(data.table)

# 构造示例数据框（已有df可跳过）
df <- data.frame(
  水果 = c("Apples", "Bananas", "Apples", "Oranges", "Oranges", "Bananas"),
  2021 = c(12, 11, 44, 30, 19, 24),
  2022 = c(29, 31, 55, 73, 82, 78)
)
setDT(df)

# 计算整体统计量
overall_dt <- df[, lapply(.SD, function(x) list(mean = mean(x), sd = sd(x))), .SDcols = c("2021", "2022")]
overall_dt <- melt(overall_dt, measure.vars = c("2021", "2022"), variable.name = "年份", value.name = "value")
overall_dt <- dcast(overall_dt, L1 ~ 年份, value.var = "value")
setnames(overall_dt, "L1", "统计量")

# 计算分组统计量
grouped_dt <- df[, lapply(.SD, function(x) list(mean = mean(x), sd = sd(x))), by = 水果, .SDcols = c("2021", "2022")]
grouped_dt <- melt(grouped_dt, id.vars = "水果", measure.vars = c("2021", "2022"), variable.name = "年份", value.name = "value")
grouped_dt <- dcast(grouped_dt, L1 ~ 年份 + 水果, value.var = "value")

# 合并并整理格式
final_dt <- cbind(overall_dt, grouped_dt[, -"L1"])
final_dt[, 统计量 := fifelse(统计量 == "mean", "均值", "标准差")]

# 查看结果
print(final_dt)

最终统计结果 #

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