如何在R语言中按年份计算高于均值的列条件求和？

首先修正测试数据里的语法错误（COMP2列的> 1是无效值，这里替换为1）：

Year <- c(2001, 2001, 2001, 2002, 2002, 2003, 2003, 2004, 2004)
COMP1 <- c(NA, 1, 2, 6, 9, NA, 2, 1, NA)  
COMP2 <- c(2, 3, 3, 3, 6, 4, 1, 0, 1)   
COMP3 <- c(NA, 1, 2, 3, 4, 0, 0, 1, 0)   
COMP4 <- c(25, 29, 16, 17, NA, 20, NA, 21, 12)

DF <- data.frame(Year, COMP1, COMP2, COMP3, COMP4)

方法1：Base R实现 #

核心思路是先按年份计算各列均值，再匹配原数据筛选出高于均值的数值后求和：

# 按年份计算各公司列的均值
mean_by_year <- aggregate(. ~ Year, data = DF, FUN = mean, na.rm = TRUE)

# 将原数据和均值表转为长格式，方便匹配
library(reshape2)
df_long <- melt(DF, id.vars = "Year", variable.name = "Company", value.name = "Value")
mean_long <- melt(mean_by_year, id.vars = "Year", variable.name = "Company", value.name = "YearlyMean")

# 匹配均值并筛选符合条件的数值，最后分组求和
df_merged <- merge(df_long, mean_long, by = c("Year", "Company"))
result <- aggregate(Value ~ Year + Company, data = df_merged[df_merged$Value > df_merged$YearlyMean, ], FUN = sum, na.rm = TRUE)

# 转回宽格式（和原数据结构对应，可选）
result_wide <- dcast(result, Year ~ Company, value.var = "Value", fill = 0)

方法2：dplyr + tidyr 实现（更简洁） #

用tidyverse工具链可以一步完成分组、计算均值、筛选求和：

library(dplyr)
library(tidyr)

result <- DF %>%
  pivot_longer(cols = starts_with("COMP"), names_to = "Company", values_to = "Value") %>%
  group_by(Year, Company) %>%
  mutate(YearlyMean = mean(Value, na.rm = TRUE)) %>%
  filter(Value > YearlyMean) %>%
  summarise(SumAboveMean = sum(Value, na.rm = TRUE)) %>%
  pivot_wider(names_from = "Company", values_from = "SumAboveMean", values_fill = 0)

说明 #

• 两种方法都支持处理大量公司列（比如你的6121列），核心逻辑无需修改
• 过程中自动忽略NA值，若需保留NA可调整na.rm参数
• 最终结果按年份展示各公司列中高于当年均值的数值之和，无符合条件值的位置填充0

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