R语言循环计算DataFrame连续24列求和异常的解决咨询

你的代码存在两个核心问题，导致只计算出单月销售额而非24个月总和：

1. 变量名错误：循环里误用了mean_df，但你初始化的结果数据框是sum_df，循环逻辑完全错位。
2. 索引优先级错误：R中:的优先级高于+，j:j+23会先执行j:j（得到单个元素j），再加上23，最终只取了metric_alert_sales的第j+23列，求和自然是单月数值。正确写法应为j:(j+23)，强制先计算j+23再生成连续列索引。

修正后的循环代码 #

# 初始化sum_df
sum_df <- as.data.frame(matrix(nrow=25, ncol=13))
colnames(sum_df) <- c("branch","oct-21","nov-21","dec-21","jan-22","feb-22","mar-22","apr-22","may-22","jun-22","jul-22","aug-22","sep-22")
sum_df$branch <- metric_alert_sales$branch
sum_df <- sum_df %>% relocate(branch, .before = 1) %>% replace(is.na(.), 0)

# 修正后的循环
for (i in 1:nrow(sum_df)) {
  for (j in 2:ncol(sum_df)) {
    # 修正索引优先级，用sum替代rowSums更直接
    sum_df[i,j] <- sum(metric_alert_sales[i, j:(j+23)])
  }
}

更高效的非循环实现（推荐） #

R中循环效率偏低，推荐用dplyr结合slider包的滑动窗口函数实现，代码更简洁且性能更优：

library(dplyr)
library(slider)

# 转换为长格式，方便滑动计算
long_data <- metric_alert_sales %>%
  pivot_longer(-branch, names_to = "month", values_to = "sales") %>%
  arrange(branch, month)  # 确保每个门店的月份按时间顺序排列

# 计算连续24个月滚动求和并转换为目标宽格式
sum_df <- long_data %>%
  group_by(branch) %>%
  mutate(rolling_24sum = slide_dbl(sales, sum, .before = 23, .complete = TRUE)) %>%
  # 筛选对应sum_df列的结束月份（2021-09到2022-09）
  filter(month %in% c("sep-21", "oct-21", "nov-21", "dec-21", "jan-22", "feb-22", "mar-22", "apr-22", "may-22", "jun-22", "jul-22", "aug-22", "sep-22")) %>%
  pivot_wider(names_from = month, values_from = rolling_24sum) %>%
  # 重命名列匹配需求：sep-21的求和对应oct-21列（覆盖2019-10到2021-09）
  rename(
    "oct-21" = "sep-21", "nov-21" = "oct-21", "dec-21" = "nov-21",
    "jan-22" = "dec-21", "feb-22" = "jan-22", "mar-22" = "feb-22",
    "apr-22" = "mar-22", "may-22" = "apr-22", "jun-22" = "may-22",
    "jul-22" = "jun-22", "aug-22" = "jul-22", "sep-22" = "aug-22"
  ) %>%
  replace(is.na(.), 0)

注：使用非循环方法前需确保metric_alert_sales的月份列名按时间顺序排列，且先安装slider包（install.packages("slider")）。

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