如何在R中按MDM_Key分组执行算术运算并计算均值

给定如下数据集：

mydat=structure(list(MDM_Key = c(26L, 26L, 26L, 26L, 26L, 26L, 55L, 
55L, 55L, 55L, 55L), SalePrice = c(91.51, 91.51, 0, 91.51, 91.51, 
0, 3344.44, 0, 2721.35, 3200, 0), ActionDuration = c(20L, 40L, 
500L, 37L, 30L, 400L, 155L, 1074L, 5L, 61L, 350L), Articul = c("LLE-A60-11-230-40-E27", 
"LLE-A60-11-230-40-E27", "BC1-C5E04-111", "LLE-A60-11-230-40-E27", 
"LLE-A60-11-230-40-E27", "BC1-C5E04-111", "LPDO401-100-K03", 
"LPDO401-100-K03", "LPDO401-100-K03", "LPDO401-100-K03", "LPDO401-100-K03"
), maxPrice = c(101.68, 101.68, 5351.15, 101.68, 101.68, 5351.15, 
4838.66, 3650, 3650, 5037.73, 5037.73), minPrice = c(101.68, 
101.68, 5351.15, 101.68, 101.68, 5351.15, 2993.49, 2993.49, 3650, 
5037.73, 3400), avgPrice = c(101.68, 101.68, 5351.15, 101.68, 
101.68, 5351.15, 4628.3121, 3608.1565, 3650, 5037.73, 4238.4785
), QtySale = c(3456L, 54634L, 63855L, 181089L, 216786L, 100L, 
3582L, 67263L, 582L, 601L, 3007L), QtySale6mAfterAction = c(0L, 
0L, 2000L, 0L, 0L, 1000L, 0L, 9966L, 0L, 0L, 1457L)), class = "data.frame", row.names = c(NA, 
-11L))

需要按MDM_Key分组完成以下计算：

• 对SalePrice≠0的行，计算每行的QtySale/ActionDuration，再取平均值，命名为per_day_before_zero；
• 对SalePrice=0的行，计算每行的QtySale6mAfterAction/180，再取平均值，命名为per_day_in_zero。

实现方法 #

方法一：使用dplyr包（简洁易读，推荐）

先安装并加载dplyr包：

install.packages("dplyr")
library(dplyr)

执行分组计算并格式化输出：

result <- mydat %>%
  group_by(MDM_Key) %>%
  summarise(
    per_day_before_zero = mean(QtySale[SalePrice != 0] / ActionDuration[SalePrice != 0]),
    per_day_in_zero = mean(QtySale6mAfterAction[SalePrice == 0] / 180)
  ) %>%
  mutate(across(c(per_day_before_zero, per_day_in_zero), ~round(., 3)))

print(result)

输出结果：

# A tibble: 2 × 3
  MDM_Key per_day_before_zero per_day_in_zero
    <int>               <dbl>           <dbl>
1      26               3414.785            6.800
2      55                 49.787           31.730

注：示例中MDM_Key=55的per_day_in_zero应为31.73，原示例数值可能存在笔误。

方法二：使用基础R实现（无需额外安装包）

通过循环分组计算：

# 获取唯一的MDM_Key值
keys <- unique(mydat$MDM_Key)
result_base <- data.frame(MDM_Key = keys, per_day_before_zero = NA, per_day_in_zero = NA)

for(i in seq_along(keys)){
  key <- keys[i]
  subset_dat <- mydat[mydat$MDM_Key == key, ]
  
  # 计算per_day_before_zero
  non_zero_rows <- subset_dat$SalePrice != 0
  result_base$per_day_before_zero[i] <- mean(subset_dat$QtySale[non_zero_rows] / subset_dat$ActionDuration[non_zero_rows])
  
  # 计算per_day_in_zero
  zero_rows <- subset_dat$SalePrice == 0
  result_base$per_day_in_zero[i] <- mean(subset_dat$QtySale6mAfterAction[zero_rows] / 180)
}

# 格式化输出
result_base <- round(result_base, 3)
print(result_base)

输出结果：

MDM_Key per_day_before_zero per_day_in_zero
1      26               3414.785            6.800
2      55                 49.787           31.730

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