r looping through different columns from excel file to do Holt Winter forecast & create results (Forecast, Plots) in separate excel sheets

Question

I have this excel file (Refer leftmost image) which has two columns – column A has period values from Jan 2005 – Dec 2014, Column B contains weightage values for AA15. I want to do Holt Winters forecast (next 24 months) for AA15 column and create an excel file as an output (Refer middle image) which will have the forecast values and the plots in an excel sheet.

The R code lets the user to select the .csv excel file, which has the input data to do the Holt Winters forecast.

R code which is working for AA15 Holt Winter forecast :

# Install the packages : tseries, forecast, WriteXLS, ggplot2, reshape2


library(tseries)
library(forecast)
library(WriteXLS)

# Let user select the excel (.csv) file for importing the input data

AA15file <- read.csv(file.choose(), header = T)

# convert to time series
AA15 <- ts(AA15file[,2], start=c(2005,1), end= c(2014,12),frequency = 12)
AA15

# convert to Holt Winter
AA15HW <- HoltWinters(AA15, seasonal = "multiplicative",optim.start =    c(alpha=0.3, beta=0.3, gamma =0.3))
AA15HW
AA15HW$fitted
summary(AA15HW)

##########################################################################################



# Combine Holt Winter Forecast plots

library(ggplot2)
library(reshape2)


HWplot<-function(AA15,  n.ahead=12,  CI=.95,  error.ribbon='green',  line.size=1){

  hw_object<-HoltWinters(AA15)

  forecast<-predict(hw_object,  n.ahead=24,  prediction.interval=T,   level=0.95)


  for_values<-data.frame(time=round(time(forecast),  3),   value_forecast=as.data.frame(forecast)$fit,  dev=as.data.frame(forecast)$upr- as.data.frame(forecast)$fit)

  fitted_values<-data.frame(time=round(time(hw_object$fitted),  3),   value_fitted=as.data.frame(hw_object$fitted)$xhat)

  actual_values<-data.frame(time=round(time(hw_object$x),  3),  Actual=c(hw_object$x))


  graphset<-merge(actual_values,  fitted_values,  by='time',  all=TRUE)
  graphset<-merge(graphset,  for_values,  all=TRUE,  by='time')
  graphset[is.na(graphset$dev),  ]$dev<-0

  graphset$Fitted<-c(rep(NA,  NROW(graphset)-(NROW(for_values) +  NROW(fitted_values))),  fitted_values$value_fitted,  for_values$value_forecast)


  graphset.melt<-melt(graphset[, c('time', 'Actual', 'Fitted')], id='time')

  p<-ggplot(graphset.melt,  aes(x=time,  y=value)) +     geom_ribbon(data=graphset, aes(x=time, y=Fitted, ymin=Fitted-dev,  ymax=Fitted +    dev),  alpha=.2,  fill=error.ribbon) + geom_line(aes(colour=variable),    size=line.size) + geom_vline(x=max(actual_values$time),  lty=2) + xlab('Time') +     ylab('Value') + theme(legend.position='bottom') + scale_colour_hue('')
  return(p)

}

# Calculate the Holt Winter Forecast values

HoltWinters(AA15)
forecast<-forecast.HoltWinters(AA15HW)
forecast$mean

forecastvalues<-data.frame(forecast$mean)

forecastvalues


# Save the plot

ggsave(filename = "zipggplotAA15.png")
png(filename = "zipggplotAA15.png", units = "px", width = 600, height = 600)
HWplot(AA15, n.ahead=12, CI=.95, error.ribbon='blue',line.size=1)
dev.off()


library(xlsx)

wb<-createWorkbook(type="xlsx")

# Create a new sheet to contain the plot
sheet <-createSheet(wb, sheetName = "ggplotFORECASTAA15")

# Add the plot created previously
addPicture("zipggplotAA15.png", sheet, scale = 1, startRow = 4,
           startColumn = 5)

# Add title
xlsx.addTitle(sheet, rowIndex=1, title="ForecastPlotsggplot2AA15",
          titleStyle = TITLE_STYLE)

# remove the plot from the disk
res<-file.remove("zipggplotAA15.png")

# Save the workbook to a file...

saveWorkbook(wb, "ggplotforecastplotAA15.xlsx")

#Add forecast data to excel sheet

addDataFrame(forecastvalues, sheet, startRow = 1, startColumn = 1)
saveWorkbook(wb, "ggplotforecastplotAA15.xlsx")

# The excel file and the sheet will be created in the working directory

getwd()

Now I want to apply the same code and call all the functions in a loop, so that I can calculate the Holt Winter forecast for different columns of the input file (Refer rightmost image) which has weightage values for AA15, AA16, AA17 etc. and produce the same output like above in separate excel sheets corresponding to each column values – AA15, AA16 etc which would be ggplotFORECASTAA15, ggplotFORECASTAA16, ggplotFORECASTAA17 etc in the same excel workbook created in the working directory.

Also in the output excel sheet for Holt Winter Forecast, I was able to print the forecast values for the next 24 months but was not able to get the dates printed (Jan 2015- Dec 2016), please let me know how to get the dates in the output file.

Any help in creating the loop in R will be highly appreciated. Thank You.

Answer 1

Consider the following which essentially runs a for loop on a list of AA15, AA16, AA17. All libraries are called once at the very beginning.

Quite a bit of assign(), paste0(), and get() functions are being used to pass the list items throughout loop. Hopefully I didn't miss anything. Please adjust as needed!

library(tseries)
library(forecast)
library(WriteXLS)
library(ggplot2)
library(reshape2)
library(xlsx)

forecasts <- c("AA15", "AA16", "AA17")

for (AA in forecasts) {

    # Let user select the excel (.csv) file for importing the input data            
    assign(paste0(AA, "file"), read.csv(file.choose(), header = T))

    # convert to time series
    assign(AA, ts(get(paste0(AA, "file"))[,2], start=c(2005,1), end= c(2014,12),frequency = 12))

    get(AA)

    # convert to Holt Winter
    assign(paste0(AA, "HW"), HoltWinters(get(AA), seasonal = "multiplicative",optim.start =    c(alpha=0.3, beta=0.3, gamma =0.3)))

    get(paste0(AA, "HW"))

    get(past0(AA, "HW"))$fitted
    summary(get(paste0(AA, "HW"))

    ###########################################################################

    # Combine Holt Winter Forecast plots

    HWplot<-function(get(AA),  n.ahead=12,  CI=.95,  error.ribbon='green',  line.size=1){

      hw_object<-HoltWinters(get(AA))

      forecast<-predict(hw_object,  n.ahead=24,  prediction.interval=T,   level=0.95)    

      for_values<-data.frame(time=round(time(forecast),  3), 
                  value_forecast=as.data.frame(forecast)$fit,  dev=as.data.frame(forecast)$upr- as.data.frame(forecast)$fit)

      fitted_values<-data.frame(time=round(time(hw_object$fitted),  3),   value_fitted=as.data.frame(hw_object$fitted)$xhat)

      actual_values<-data.frame(time=round(time(hw_object$x),  3),  Actual=c(hw_object$x))


      graphset<-merge(actual_values,  fitted_values,  by='time',  all=TRUE)
      graphset<-merge(graphset,  for_values,  all=TRUE,  by='time')
      graphset[is.na(graphset$dev),  ]$dev<-0

      graphset$Fitted<-c(rep(NA,  NROW(graphset)-(NROW(for_values) +  NROW(fitted_values))),  
                      fitted_values$value_fitted,  for_values$value_forecast)


      graphset.melt<-melt(graphset[, c('time', 'Actual', 'Fitted')], id='time')

      p<-ggplot(graphset.melt,  aes(x=time,  y=value)) +  
                geom_ribbon(data=graphset, aes(x=time, y=Fitted, ymin=Fitted-dev,  
                ymax=Fitted +    dev),  alpha=.2,  fill=error.ribbon) +
                geom_line(aes(colour=variable),    size=line.size) + 
                geom_vline(x=max(actual_values$time),  lty=2) + xlab('Time') +     
                ylab('Value') + theme(legend.position='bottom') + scale_colour_hue('')
      return(p)

    }

   # Calculate the Holt Winter Forecast values

   HoltWinters(get(AA))
   forecast<-forecast.HoltWinters(get(paste0(AA, "HW")))
   forecast$mean

   forecastvalues<-data.frame(forecast$mean)

   forecastvalues


   # Save the plot

   ggsave(filename = paste0("zipggplot", AA ".png"))
   png(filename = paste0("zipggplot", AA ".png"), units = "px", width = 600, height = 600)
   HWplot(get(AA), n.ahead=12, CI=.95, error.ribbon='blue',line.size=1)
   dev.off()


   wb<-createWorkbook(type="xlsx")

   # Create a new sheet to contain the plot
   sheet <-createSheet(wb, sheetName = paste0("ggplotFORECAST", AA))

   # Add the plot created previously
   addPicture(paste0("zipggplot", AA ".png"), sheet, scale = 1, startRow = 4,
           startColumn = 5)

   # Add title
   xlsx.addTitle(sheet, rowIndex=1, title= paste0("ForecastPlotsggplot2", AA),
          titleStyle = TITLE_STYLE)

   # remove the plot from the disk
   res<-file.remove(paste0("zipggplot", AA, ".png"))

   # Save the workbook to a file...    
   saveWorkbook(wb, paste0("ggplotforecastplot", AA ".xlsx"))

   #Add forecast data to excel sheet    
   addDataFrame(forecastvalues, sheet, startRow = 1, startColumn = 1)
   saveWorkbook(wb, paste0("ggplotforecastplot", AA ".xlsx"))

   # The excel file and the sheet will be created in the working directory    
   getwd()

}