R语言循环生成预测结果写入空数据框避免覆盖的方法

问题描述 #

在R语言中通过循环生成HoltWinters时间序列预测后，尝试将各变量的预测结果写入空数据框时，每次生成的预测结果都会被覆盖到同一列，无法写入不同列。尝试使用Output[,i] = as.data.frame(Predictions$mean)也未能解决问题。

原代码如下：

library(dplyr)
library(tidyverse)
library(tidyr)
library(tidymodels)
library(forecast)
library(prophet)
library(readxl)
library(writexl)
library(tibble)

pd <- readxl::read_excel("C:/X/X/X/X/Dummy.xlsx")
colnames(pd)[1]="ds"

colnames(pd)
pd1 <- pd %>% select(`X1`,`X2`,`X3`)

pd2 <- pd %>% select(`X1`)

Output = data.frame()

for(i in 2:ncol(pd))
{
  Yi<- ts(data = pd[,i],
           frequency = 12,
           start = c(2019,1),
           end = c(2022,8))
  #print(Yi)
  
  Model = HoltWinters(x=Yi,
                      seasonal = 'additive')
  
  Predictions = forecast(Model,h=6)
  
  print(Predictions$mean)
  
  Output = as.data.frame(Predictions$mean)
  
  print(Output)
  
}

数据集结构：

structure(list(ds = 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"), X1 = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 5, 
85, 72, 111, 96, 50, 95, 48, 87, 75, 249, 173, 74, 86, 127, 209, 
92, 137, 49, 84, 75, 73, 376, 196, 91, 107, 124, 177, 244, 275, 
100, 176), X2 = 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, 19, 29, 243, 
281, 262, 283, 0, 264, 104, 289, 41, 76), X3 = 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, 157, 171, 377, 409, 375, 314, 253, 322, 
130, 472, 115, 179)), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -44L))

问题原因 #

1. 原代码每次循环执行Output = as.data.frame(Predictions$mean)，直接覆盖整个Output数据框，仅保留最后一次循环结果。
2. 初始Output为空数据框，直接用Output[,i]赋值时，因维度不匹配导致赋值失败。
3. Predictions$mean是时间序列（ts）对象，直接转数据框时会携带额外属性，干扰列赋值逻辑。

解决方案 #

方案1：生成各变量预测结果为不同列的数据框（理想输出）

先初始化包含预测时间索引的空数据框，再循环添加对应变量的预测列：

library(forecast)
library(readxl)

# 读取数据
pd <- readxl::read_excel("C:/X/X/X/X/Dummy.xlsx")
colnames(pd)[1] <- "ds"

# 初始化输出数据框：先获取预测的时间序列索引
test_model <- HoltWinters(ts(pd[,2], frequency=12, start=c(2019,1), end=c(2022,8)), seasonal='additive')
test_pred <- forecast(test_model, h=6)
Output <- data.frame(pred_date = as.Date(test_pred$mean))

# 循环处理每个变量
for(i in 2:ncol(pd)){
  # 提取变量并转为时间序列
  Yi <- ts(data = pd[,i], frequency = 12, start = c(2019,1), end = c(2022,8))
  # 构建模型并预测
  Model <- HoltWinters(x=Yi, seasonal = 'additive')
  Predictions <- forecast(Model, h=6)
  # 将预测结果转为数值向量，添加到Output中，列名为原变量名
  Output[[colnames(pd)[i]]] <- as.numeric(Predictions$mean)
}

print(Output)

方案2：生成按行排列的预测结果数据框

若需要按行展示每个变量的预测值，可整理为长格式：

library(forecast)
library(readxl)
library(dplyr)
library(tidyr)

pd <- readxl::read_excel("C:/X/X/X/X/Dummy.xlsx")
colnames(pd)[1] <- "ds"

# 初始化空列表存储结果
pred_list <- list()

for(i in 2:ncol(pd)){
  Yi <- ts(data = pd[,i], frequency = 12, start = c(2019,1), end = c(2022,8))
  Model <- HoltWinters(x=Yi, seasonal = 'additive')
  Predictions <- forecast(Model, h=6)
  
  # 将当前变量的预测结果整理为数据框
  pred_df <- data.frame(
    variable = colnames(pd)[i],
    pred_date = as.Date(Predictions$mean),
    pred_value = as.numeric(Predictions$mean)
  )
  pred_list[[i-1]] <- pred_df
}

# 合并所有结果为一个数据框
Output <- bind_rows(pred_list)

# 可选：转为宽格式（与方案1一致）
# Output_wide <- Output %>% pivot_wider(names_from = variable, values_from = pred_value)

print(Output)

关键修改点说明 #

• 方案1先初始化带预测日期的Output，避免空数据框赋值的维度问题；用[[colnames(pd)[i]]]按变量名添加列，确保列名清晰。
• 将Predictions$mean转为数值向量（as.numeric()），消除ts对象属性对数据框赋值的干扰。
• 方案2通过列表存储单变量结果再合并，适合需要长格式输出的场景。

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