How to create a loop that runs an ARIMA model for 88 variables in a table

[C.Rhone]

I am fairly new at R . I have a data set with 88 rows for the period 2011-2020 that I am trying to use an ARIMA model to forecast for the period 2021-2030. I am having some difficulties with the codes. I am including a snap shop of the data table and codes as well a a link to the r file and excel file.

Snap shot of Table:

Group Model_Type 2011 2012 2013 2014 .....
Annotto1 A 6,532 5,392 5,873 5,728
Annotto2 B 5,998 5,604 5,616 5,831
Blackst C 3,911 3,895 3,816 3,680

library(readxl)
library(forecast)
library(seasonal)
library(tidyverse)
library(lubridate)
library(tidyselect)
library(TSdist)

LT_Models <- read_excel("D:/Practice/LT_Models.xlsx") %>% # location of file
pivot_longer(cols = !c("Substation","Group","Model_Type"), names_to = "Year", values_to = "kWh") %>%
group_by(Substation,Group,Model_Type)%>%
nest() %>%
ungroup() %>% # undoing the group
mutate(
TSobjects = map(data,
~ pull(.x) %>% ts(start = 2011, frequency = 1) ))%>% #creating a column called TS objects to store time seriesdata
select(!data) %>% #removing column from data
select(!Model_Type)

#----------------- Attempting Loop for ARIMA model

GroupedArimav2 = LT_Models %>%
mutate(
Model = map(TSobjects,
~ ts([start:end],start = 2011, frequency = 1) %>% auto.arima(test = "kpss",approximation = F)),

Forecast = map(Model,~ forecast(.x, level = c(95,97))),
  
AIC = map_dbl(Model, AIC),

)

#to change forecast length
GroupedArimav2 %<>%
mutate(Forecast = map(Model,~ forecast(.x, level = c(95,97), h=11)))

See link to excel file and R codes at link below. I am having issues with the codes:
https://drive.google.com/drive/folders/1NqBfHlAXPB_Aowkk6Q3Rh86TN4Ej7Iaf?usp=sharing

Please assist.

[Hayward]

I can't reproduce your example because the data in your excel file is missing 'Group' and 'Model_Type'. However, if I make a few assumptions--like no grouping and working from your 'variables' column as if it were the completed groups--I think what you're looking for is something like this:

GroupedArimav2 <-
  LT_Models %>% 
  mutate(Model =  map(TSobjects, ~ auto.arima(. , test = "kpss",approximation = F)))

No need to pull and redefine the timeseries with "pull(.x) %>% ts(start = 2011, frequency = 1) ))" since that's already done in 'TSobjects'.

This example page might help you:
https://cran.r-project.org/web/packages/broom/vignettes/broom_and_dplyr.html

Complete adjusted prep code to work with your example Excel file:

LT_Models <- read_excel("/Downloads/LT_Models.xlsx") %>%
  select(-Substation) %>% 
  pivot_longer(cols = !c("variables"), names_to = "Year", values_to = "kWh") %>%
  group_by(variables)%>%
  nest() %>%
  ungroup() %>%
  mutate(
    TSobjects = map(data,~ pull(.x) %>% ts(start = 2011, frequency = 1) )) %>% 
  select(!data)


GroupedArimav2 <-
  LT_Models %>% 
  mutate(Model =  map(TSobjects, ~ auto.arima(. , test = "kpss",approximation = F)))

[C.Rhone]

Hi Hayward,

Firstly, I apologize for modifying the excel sheet from its original format.
Secondly, thank you so much for the assistance!
The suggested adjustments to the codes worked and it has saved me hours of manual work.

Thank you.

[Hayward]

Your response made me smile You're welcome.