如何在R中实现Excel的AVERAGEIFS函数功能？

问题描述 #

需要按产品类型分组，计算每行订单日期±15天范围内同类型产品的价格平均值，再求当前价格与该平均值的平方差。但现有代码生成的Avg_Price列全组为同一值，无法匹配预期结果。

示例数据集 #

先定义一个匹配需求的示例数据集，包含预期平均值用于验证：

library(dplyr)

df <- tibble(
  Product_Type = c("A", "A", "A", "B", "B", "B"),
  Order_Date = as.Date(c("2023-01-01", "2023-01-10", "2023-01-25", "2023-01-05", "2023-01-20", "2023-02-05")),
  Price = c(10, 12, 15, 20, 22, 25),
  Expected_Mean = c(11, 11, 15, 21, 21, 25) # 预期的日期范围内平均值
)

错误代码分析 #

常见错误是分组后未针对每行的日期做精准筛选，而是直接计算全组平均值，导致Avg_Price全组一致：

# 错误示例代码
df %>%
  group_by(Product_Type) %>%
  mutate(Avg_Price = mean(Price)) %>% # 计算全组所有价格的平均值，而非日期范围的平均值
  ungroup()

解决方案 #

方法1：使用dplyr的rowwise逐行处理 #

通过rowwise()让mutate针对每行执行筛选逻辑，精准匹配当前行的产品类型和日期范围：

df_result <- df %>%
  rowwise() %>%
  mutate(
    # 筛选同产品类型且日期在当前行±15天内的价格，计算平均值
    Avg_Price = mean(
      df$Price[
        df$Product_Type == Product_Type & 
        df$Order_Date >= (Order_Date - 15) & 
        df$Order_Date <= (Order_Date + 15)
      ],
      na.rm = TRUE
    ),
    # 计算价格与平均值的平方差
    Squared_Difference = (Price - Avg_Price)^2
  ) %>%
  ungroup()

print(df_result)

方法2：使用purrr提高大数据集效率 #

如果数据量较大，rowwise效率偏低，可使用purrr::map2_dbl逐行映射计算：

library(purrr)

df_result <- df %>%
  mutate(
    Avg_Price = map2_dbl(Product_Type, Order_Date, function(type, date) {
      mean(
        df$Price[df$Product_Type == type & df$Order_Date >= date -15 & df$Order_Date <= date +15],
        na.rm = TRUE
      )
    }),
    Squared_Difference = (Price - Avg_Price)^2
  )

方法3：使用data.table优化性能（超大数据集） #

对于百万级以上的数据集，data.table的效率优势明显：

library(data.table)

setDT(df)

df[, Avg_Price := sapply(.I, function(i) {
  mean(Price[Product_Type == Product_Type[i] & Order_Date >= (Order_Date[i] -15) & Order_Date <= (Order_Date[i] +15)], na.rm = TRUE)
}), by = Product_Type]

df[, Squared_Difference := (Price - Avg_Price)^2]

验证结果 #

执行上述代码后，Avg_Price会和Expected_Mean完全匹配，Squared_Difference也会正确计算出每行的平方差。

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