Multiple Time Series - Forecasting with different statistical models and exporting into excel file

Question

In R , I have a data set including the demand of 2 products for 38 periods and I need to predict the demand of each product for the next 12 periods.In my code different statistical models (ARIMA,TBATS,Naive,ETS,Holt_Trend,Single Exponential) are added and for each product I want to generate forecast with these models and calculate MAPE results of the generated forecasts. At final step I want to export these forecasts and MAPE results into an excel file. I've added below an image to give more idea about the expected excel file output. Expected_Excel_File

In my code I'm able to generate forecasts and calculate MAPE results for a single product but I couldn't loop it for more than one. Also I was able to export excel file but not in the expected output format and not for both products and not with MAPE results. How should I modify the coding to solve described problem? Thanks,

install.packages('forecast', dependencies = TRUE)
install.packages("writexl")
library(readr)
library(ggplot2)
library(forecast)
library(fpp2)
library(TTR)
library(dplyr)
library(writexl)
library(datasets)  # Load base packages manually

# Installs pacman ("package manager") if needed
if (!require("pacman")) install.packages("pacman")
# Use pacman to load add-on packages as desired
pacman::p_load(pacman, rio) 

dat <- structure(list(Date = c("01.2017", "02.2017", "03.2017", "04.2017", 
                               "05.2017", "06.2017", "07.2017", "08.2017", "09.2017", "10.2017", 
                               "11.2017", "12.2017", "01.2018", "02.2018", "03.2018", "04.2018", 
                               "05.2018", "06.2018", "07.2018", "08.2018", "09.2018", "10.2018", 
                               "11.2018", "12.2018", "01.2019", "02.2019", "03.2019", "04.2019", 
                               "05.2019", "06.2019", "07.2019", "08.2019", "09.2019", "10.2019", 
                               "11.2019", "12.2019", "01.2020", "02.2020"), Class = c("Train", 
                                                                                      "Train", "Train", "Train", "Train", "Train", "Train", "Train", 
                                                                                      "Train", "Train", "Train", "Train", "Train", "Train", "Train", 
                                                                                      "Train", "Train", "Train", "Train", "Train", "Train", "Train", 
                                                                                      "Train", "Train", "Train", "Train", "Test", "Test", "Test", "Test", 
                                                                                      "Test", "Test", "Test", "Test", "Test", "Test", "Test", "Test"
                               ), X1 = c(12150.95, 10517.85, 12902.75, 13515.6, 19094.95, 19644.8, 
                                         26028.8, 25700.5, 25494.133, 16804.41, 12008.78, 13177.843, 14600.887, 
                                         12533.437, 14175.105, 17195.7835, 14365.666, 17857.231, 51370.585, 
                                         47686.779, 17484.005, 12986.561, 21711.739, 26042.345, 14021.21, 
                                         12758.4625, 28185.5105, 35070.8995, 30983.645, 48007.869, 58787.33, 
                                         58861.8555, 44464.1295, 35811.4575, 30472.015, 36567.93, 31427.3365, 
                                         28937.349), X2 = c(25592.35, 26709.2, 25189.5, 21972.3, 14581.343, 
                                                            11392.85, 22533.693, 22030.05, 23642.689, 27861.022, 24451.2555, 
                                                            28566.9125, 20601.581, 26642.938, 29806.2905, 37823.2505, 13444.319, 
                                                            22217.5485, 27572.8775, 27461.532, 25945.178, 36164.338, 19929.6685, 
                                                            38298.582, 32097.618, 27095.11, 27887.356, 19219.0915, 6751.6015, 
                                                            26521.3235, 28729.197, 30128.6335, 26408.907, 29410.584, 26739.8005, 
                                                            35474.4145, 25188.919, 25074.392)), row.names = c(NA, 38L), class = "data.frame")
head(dat)
glimpse(dat)
dat_train = subset(dat, Class == 'Train',select=c(Class,X2))
dat_test = subset(dat, Class == 'Test',select=c(Class,X2))
nrow(dat_train); nrow(dat_test)

dat_ts <- ts(dat[, 4], start = c(2017, 1), end = c(2020, 2), frequency = 12)
autoplot(dat_ts)

#MAPE Calculation Function
mape <- function(actual,pred){
  mape <- mean(abs((actual - pred)/actual))*100
  return (mape)
}

#Naive Forecasting Model Run

naive_mod <- naive(dat_ts, h = 12)
summary(naive_mod)
dat_test$naive = mean(dat_ts)
mape(dat_test$`X2`, dat_test$naive)

#Simple Exponential Smoothing Forecasting Model Run

se_model <- ses(dat_ts, h = 12)
summary(se_model)
df_fc = as.data.frame(se_model)
dat_test$simplexp = df_fc$`Point Forecast`
mape(dat_test$`X2`, dat_test$simplexp) 

#Holt Trend Forecasting Model Run

holt_model <- holt(dat_ts, h = 12)
summary(holt_model)
df_holt = as.data.frame(holt_model)
dat_test$holt = df_holt$`Point Forecast`
mape(dat_test$`X2`, dat_test$holt) 

#Arima Forecasting Model Run

arima_model <- auto.arima(dat_ts)
summary(arima_model)
fore_arima = forecast::forecast(arima_model, h=12)
df_arima = as.data.frame(fore_arima)
dat_test$arima = df_arima$`Point Forecast`
mape(dat_test$`X2`, dat_test$arima)

#TBATS Forecasting Model Run

model_tbats <- tbats(dat_ts)
summary(model_tbats)
for_tbats <- forecast::forecast(model_tbats, h = 12)
df_tbats = as.data.frame(for_tbats)
dat_test$tbats = df_tbats$`Point Forecast`
mape(dat_test$`X2`, dat_test$tbats) 

#Auto-Model Selection ETS function
fit<- ets(dat_ts)
summary(fit)
fore_ETS = forecast::forecast(fit, h=12)
df_fore_ETS = as.data.frame(fore_ETS)
dat_test$ETS = df_fore_ETS$`Point Forecast`
mape(dat_test$`X2`, dat_test$ETS) 

#Output of forecast results and MAPE result

tmp <- writexl::write_xlsx(dat_test, "~/Desktop/Result.xls")
readxl::read_xlsx(tmp)

# CLEAN UP #################################################

# Clear environment
rm(list = ls()) 

# Clear console
cat("\014")  # ctrl+L

Welcome to SO! You should know that [Making a Reproducible example](https://stackoverflow.com/a/5963610/6478701) makes it much easier for people on this site to help you. Also, please, in the name of anything that is holy, ***do not put pictures of tables*** to describe your data. — RoB, Feb 26 '21 at 18:04
Thanks for this highlight. I'm quite new and will keep it in mind. I removed the image for my data and introduced it in the script, added also related packages. I hope this way everything will run smooth. — Efe, Feb 26 '21 at 19:51
I'm not sure what exactly your plan is, but you can call different functions in a loop with something like ```tmp <- do.call(mod, list(dat_ts, h = 12))```. The reason why you won't get MAPE results in the table is that you don't save them to any variable nor do you add them to the table. Since you want the results in the table horizontally anyways, I'd recommend saving *mape* and the *point forecast* values in a vector and maybe later on use *rbind()* to combine those vectors and *cbind()* to add the model names. — Manuel Popp, Feb 26 '21 at 20:56
Thanks Manuel, rbind() helped me to create the table I needed for excel output. — Efe, Mar 01 '21 at 17:07

Multiple Time Series - Forecasting with different statistical models and exporting into excel file

0 Answers0