R语言高效计算多列3/6月移动平均及9期预测的方法

问题背景 #

我是R语言初学者，现有一个包含约7000列的DataFrame，需为每列计算3月或6月移动平均并预测未来9期。当前采用for循环实现，但耗时极长，希望了解并行处理等替代方案及具体代码实现。

数据结构示例 #

structure(list(`Row Labels` = c("2019-01-01", "2019-02-01", "2019-03-01", 
"2019-04-01", "2019-05-01", "2019-06-01", "2019-07-01", "2019-08-01", 
"2019-09-01", "2019-10-01", "2019-11-01", "2019-12-01", "2020-01-01", 
"2020-02-01", "2020-03-01", "2020-04-01", "2020-05-01", "2020-06-01", 
"2020-07-01", "2020-08-01", "2020-09-01", "2020-10-01", "2020-11-01", 
"2020-12-01", "2021-01-01", "2021-02-01", "2021-03-01", "2021-04-01", 
"2021-05-01", "2021-06-01", "2021-07-01", "2021-08-01", "2021-09-01", 
"2021-10-01", "2021-11-01", "2021-12-01", "2022-01-01", "2022-02-01", 
"2022-03-01", "2022-04-01", "2022-05-01", "2022-06-01", "2022-07-01", 
"2022-08-01", "2022-09-01", "2022-10-01"), `XYZ|703` = c(0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1636, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0), `XYZ|851` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 206, 1814, 
2324, 772, 1116, 1636, 1906, 957, 829, 911, 786, 938, 1313, 2384, 
1554, 1777, 1635, 1534, 1015, 827, 982, 685, 767, 511, 239, 5400, 
1301, 426, 261, 201, 33, 0, 0, 0, 0, 0, 0), `XYZ|574` = c(0, 
0, 0, 0, 0, 0, 0, 0, 74, 179, 464, 880, 324, 184, 90, 170, 140, 
96, 78, 83, 83, 121, 245, 9000, 332, 123, 117, 138, 20, 42, 70, 
70, 42, 103, 490, 7500, 488, 245, 142, 95, 63, 343, 57, 113, 
100, 105), `XYZ|300` = c(82, 67, 140, 118, 24, 23, 93, 27, 63, 
62, 76, 50625, 56, 65, 55, 136, 99, 53, 5, 137, 111, 126, 208, 
50625, 61, 42, 225, 206, 69, 116, 82, 59, 51, 30, 63, 50625, 
40, 39, 96, 92, 101, 77, 43, 0, 0, 0), `XYZ|513` = c(74, 33, 
21, 31, 31, 29, 41, 21, 25, 60, 66, 5476, 47, 25, 23, 17, 34, 
0, 0, 0, 0, 0, 0, 5476, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 5476, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `XYZ|204` = c(13, 41, 65, 107, 
42, 32, 79, 79, 44, 84, 79, 23409, 79, 36, 30, 51, 48, 51, 25, 
39, 15, 21, 65, 23409, 48, 40, 130, 94, 90, 92, 41, 59, 35, 4, 
83, 23409, 73, 38, 39, 28, 60, 71, 17, 51, 37, 26), `XYZ|895` = c(2, 
7, 26, 22, 10, 28, 13, 23, 18, 19, 30, 2209, 13, 6, 24, 20, 10, 
14, 23, 6, 21, 16, 32, 2209, 14, 14, 21, 27, 23, 45, 8, 12, 15, 
8, 29, 2209, 6, 1, 8, 10, 7, 2, 9, 8, 6, 13), `XYZ|666` = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 22500, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 150, 22500, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 22500, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0), `XYZ|575` = c(0, 0, 179, 464, 880, 
324, 184, 0, 0, 0, 0, 774400, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
774400, 0, 0, 0, 0, 0, 0, 0, 0, 0, 103, 490, 774400, 488, 245, 
142, 0, 0, 0, 0, 0, 0, 0), `XYZ|345` = c(0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -46L))

现有低效代码 #

Book7 <- read_excel("C:/X/X/X- X/X/Book7.xlsx", 
                   sheet = "Loop")

Output = data.frame(matrix(NA,nrow = 9,ncol = ncol(Book7)))
colnames(Output) <- colnames(Book7)


for(i in 2:ncol(Book7))
{
  Y<- ts(data = Book7[,i],
         frequency = 12,
         start = c(2019,1),
         end = c(2022,10))
  
  Print(Y)
  
  mov_avg <- ma(Y, order = 3, centre = FALSE)
  
  Forecast_Mov_Avg_3M <- forecast(mov_avg,h = 19)
  
  print(Forecast_Mov_Avg_3M$mean)
  
  Output[,i] = as.data.frame(Forecast_Mov_Avg_3M$mean)
  
}

优化方案及代码实现 #

问题分析 #

for循环逐列处理时，每个迭代都要重复初始化时间序列、调用移动平均和预测函数，单线程下7000列的开销极大。并行处理可以把任务拆分到多个CPU核心同时运行，大幅缩短耗时。

方案1：使用furrr（tidyverse风格，适合初学者） #

furrr是purrr的并行版本，语法和purrr一致，容易上手。

步骤1：安装并加载所需包

install.packages(c("furrr", "forecast", "readxl"))
library(furrr)
library(forecast)
library(readxl)

步骤2：初始化并行会话

根据你的CPU核心数设置工作线程，一般设为核心数-1，避免占满资源：

plan(multisession, workers = parallel::detectCores() - 1)

步骤3：定义单列处理函数

把原循环里的逻辑封装成函数，输入列数据，输出预测结果：

process_column <- function(col_data, ma_order = 3, forecast_h = 9) {
  # 转换为时间序列
  y_ts <- ts(data = col_data,
             frequency = 12,
             start = c(2019, 1),
             end = c(2022, 10))
  # 计算移动平均
  mov_avg <- ma(y_ts, order = ma_order, centre = FALSE)
  # 预测未来h期
  forecast_result <- forecast(mov_avg, h = forecast_h)
  # 返回预测值
  as.numeric(forecast_result$mean)
}

步骤4：并行处理所有列

# 读取数据
Book7 <- read_excel("C:/X/X/X- X/X/Book7.xlsx", sheet = "Loop")

# 提取需要处理的列（除了第一列日期）
data_cols <- Book7[, -1]

# 并行处理每列
forecast_list <- future_map(data_cols, process_column, ma_order = 3, forecast_h = 9)

# 转换为输出DataFrame
Output <- as.data.frame(do.call(cbind, forecast_list))
# 添加日期列（未来9期的日期，这里按月度生成）
future_dates <- seq.Date(as.Date("2022-11-01"), by = "month", length.out = 9)
Output <- cbind(`Row Labels` = future_dates, Output)
# 设置列名
colnames(Output) <- colnames(Book7)

方案2：使用foreach+doParallel（传统并行框架） #

如果更习惯传统的并行写法，可以用foreach配合doParallel：

步骤1：安装加载包

install.packages(c("doParallel", "forecast", "readxl"))
library(doParallel)
library(forecast)
library(readxl)

步骤2：注册并行集群

cl <- makeCluster(parallel::detectCores() - 1)
registerDoParallel(cl)

步骤3：并行处理

# 读取数据
Book7 <- read_excel("C:/X/X/X- X/X/Book7.xlsx", sheet = "Loop")

# 并行循环处理列
forecast_list <- foreach(i = 2:ncol(Book7)) %dopar% {
  col_data <- Book7[, i]
  y_ts <- ts(data = col_data,
             frequency = 12,
             start = c(2019, 1),
             end = c(2022, 10))
  mov_avg <- ma(y_ts, order = 3, centre = FALSE)
  forecast_result <- forecast(mov_avg, h = 9)
  as.numeric(forecast_result$mean)
}

# 停止集群
stopCluster(cl)

# 转换为输出DataFrame
Output <- as.data.frame(do.call(cbind, forecast_list))
future_dates <- seq.Date(as.Date("2022-11-01"), by = "month", length.out = 9)
Output <- cbind(`Row Labels` = future_dates, Output)
colnames(Output) <- colnames(Book7)

注意事项 #

• 原代码中h=19是预测19期，根据需求已改为h=9
• 计算6月移动平均时，只需将ma_order参数改为6即可
• 并行处理时尽量避免在函数内做打印操作（原代码的Print(Y)和print(Forecast_Mov_Avg_3M$mean)），会拖慢速度
• 如果内存不足，可以考虑分批次处理列，避免一次性加载所有任务

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