将预测合并到R中的数据框中，然后导出到excel中 [英] Combining forecasts into a data frame in R and then exporting into excel

问题描述

我目前正在R中运行多个 auto.arima()预测，以生成一系列具有置信区间的点预测，我希望能够直接将它们引入excel.下面显示了我目前正在使用的脚本的一部分数据.

I'm currently running multiple auto.arima() forecasts in R to generate a series of point forecasts with confidence intervals that I'd like to be able to pull directly into excel. A sample of the script I've currently been using below is shown for a portion of my data.

require(forecast)


# Customer GM ARIMA Forecasts (1 Quarter Ahead)

F1 <- read.csv("C:/datapath/Desktop/dataname.csv")

F1 <- ts(F1, frequency = 12, start = c(2014, 1), end = c(2015, 12))

Coonan <- F1[,3]
Gallo  <- F1[,4]
Kempton<- F1[,5]
Moore  <- F1[,6]
Nekic  <- F1[,7]


fit.Coonan  <- auto.arima(Coonan, stepwise = FALSE)
fc.Coonan   <- forecast(fit.Coonan, h=3, level = c(20, 40, 80))

fit.Gallo   <- auto.arima(Gallo, stepwise = FALSE)
fc.Gallo    <- forecast(fit.Gallo, h=3, level = c(20, 40, 80))

fit.Kempton <- auto.arima(Kempton, stepwise = FALSE)
fc.Kempton  <- forecast(fit.Kempton, h=3, level = c(20, 40, 80))

fit.Kempton <- auto.arima(Kempton, stepwise = FALSE)
fc.Kempton  <- forecast(fit.Kempton, h=3, level = c(20, 40, 80))

fit.Moore   <- auto.arima(Moore, stepwise = FALSE)
fc.Moore    <- forecast(fit.Moore, h=3, level = c(20, 40, 80))

fit.Nekic   <- auto.arima(Nekic, stepwise = FALSE)
fc.Nekic    <- forecast(fit.Nekic, h=3, level = c(20, 40, 80))



# Save to clipboard to copy and paste into excel

write.excel <- function(x,row.names=TRUE,col.names=TRUE,...) {
  write.table(x,"clipboard",sep="\t",row.names=row.names,col.names=col.names,...)
}

write.excel(fc.Coonan)   # Then can paste Coonan Forecasts Directly into excel

将结果粘贴到excel中之后，我得到一个看起来像这样的表(我想移列名，但是现在这不是大问题).

After pasting my result into excel I get a table that looks like this this (I'd like to shift over column names, but that's not a big problem right now).

按照当前的编写，我需要在底部函数中手动更改模型的名称，运行该函数(以便将结果保存到剪贴板)，然后将结果复制并粘贴到excel中.这个过程变得非常耗时，我想知道是否有一种简单的方法可以将我的一系列点预测和置信区间组合到一个数据框中，然后可以一次将其全部导出到excel中.

As currently written I need to manually change the name of the model in the bottom function, run the function (in order to save the results to the clipboard), and then copy and paste the results into excel. This process has become very time consuming and I'm wondering if there's a simple way to combine my series of point forecasts and confidence intervals into one data frame that I can then export all at once into excel.

谢谢您的帮助.

推荐答案

这里是使用 openxlsx 的一个，我发现它比其他任何软件包都更可取，因为它使用C ++代替了Java，而Java通常会运行内存不足，甚至无法写小纸.

Here's one using openxlsx which I find to be preferable to any of the other packages because it uses C++ instead of Java which often runs out of memory writing even small sheets.

尽管出现错误，您可能仍需要将路径设置为 zip :

You may need to set the path to zip though if you get the error:

错误:压缩工作簿失败.请确保已安装Rtools或zip应用程序可用于R.在Windows上尝试installr :: install.rtools().

Error: zipping up workbook failed. Please make sure Rtools is installed or a zip application is available to R. Try installr::install.rtools() on Windows.

library(forecast)
library(openxlsx)
Sys.setenv(R_ZIPCMD = "C:/RBuildTools/3.1/bin/zip")

# create dummy data
library(data.table)
set.seed(1)
build <- data.table()
F1 <- build[, lapply(seq(7), function(x) runif(24))]

F1 <- ts(F1, frequency = 12, start = c(2014, 1), end = c(2015, 12))

Coonan <- F1[,3]
Gallo  <- F1[,4]
Kempton<- F1[,5]
Moore  <- F1[,6]
Nekic  <- F1[,7]

results <- list()

fit.Coonan  <- auto.arima(Coonan, stepwise = FALSE)
results[["Coonan"]] <- forecast(fit.Coonan, h=3, level = c(20, 40, 80))

fit.Gallo   <- auto.arima(Gallo, stepwise = FALSE)
results[["Gallo"]] <- forecast(fit.Gallo, h=3, level = c(20, 40, 80))

fit.Kempton <- auto.arima(Kempton, stepwise = FALSE)
results[["Kempton"]] <- forecast(fit.Kempton, h=3, level = c(20, 40, 80))

fit.Moore   <- auto.arima(Moore, stepwise = FALSE)
results[["Moore"]] <- forecast(fit.Moore, h=3, level = c(20, 40, 80))

fit.Nekic   <- auto.arima(Nekic, stepwise = FALSE)
results[["Nekic"]] <- forecast(fit.Nekic, h=3, level = c(20, 40, 80))

results_together <- do.call(rbind,lapply(names(results),function(x){
  transform(as.data.frame(results[[x]]), Name = x)
}))

wb <- createWorkbook()

addWorksheet(wb, "Forecasts")
writeData(wb, "Forecasts", results_together, rowNames = TRUE)
saveWorkbook(wb, "Forcasts.xlsx", overwrite = TRUE)

结果:

您还可以将每个结果放在自己的标签上(添加或不添加 Name 列):

You can also put each result on its own tab (with or without adding the Name column):

wb <- createWorkbook()

for (nm in names(results)){
  addWorksheet(wb, nm)
  writeData(wb, nm, results[[nm]], rowNames = TRUE)
}

saveWorkbook(wb, "Forcasts.xlsx", overwrite = TRUE)  

结果:

这篇关于将预测合并到R中的数据框中，然后导出到excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！