CODEWITHSUNDEEP
rtime-seriesforecasting
Roman
Roman

Reputation: 249

Time series forecasting in R

My aim is to get forecast sales figures for a number seasonal sold products.

I have managed to get a forecasting with the help of the forecast package and the arima method for one single product. Now I got access to historical sales data which contain the number of articles sold per month in different regions. The historical data go back several years. To give you an overview how the data is organised I have prepared an excerpt below:

History <- read.table(sep = ";",text = "
Region;Part.number;Qty;Period;Year;Month;Plant;Material.ID;Subgroup.ID;Group.ID;Product.Subgroup;Product.Group;Not.Consider
AA;1.234;5;01/01/2008;2008;1;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;8;01/01/2008;2008;1;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;16;01/01/2008;2008;1;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;5;01/01/2008;2008;1;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;14;01/01/2008;2008;1;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;3;01/01/2008;2008;1;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;9;01/01/2008;2008;1;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;4;01/01/2008;2008;1;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;6;01/01/2008;2008;1;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;3;01/01/2008;2008;1;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;27;01/02/2008;2008;2;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;42;01/02/2008;2008;2;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;80;01/02/2008;2008;2;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;27;01/02/2008;2008;2;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;72;01/02/2008;2008;2;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;18;01/02/2008;2008;2;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;46;01/02/2008;2008;2;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;20;01/02/2008;2008;2;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;33;01/02/2008;2008;2;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;16;01/02/2008;2008;2;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;81;01/03/2008;2008;3;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;126;01/03/2008;2008;3;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;244;01/03/2008;2008;3;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;82;01/03/2008;2008;3;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;219;01/03/2008;2008;3;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;56;01/03/2008;2008;3;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;138;01/03/2008;2008;3;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;60;01/03/2008;2008;3;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;101;01/03/2008;2008;3;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;52;01/03/2008;2008;3;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;165;01/04/2008;2008;4;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;255;01/04/2008;2008;4;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;490;01/04/2008;2008;4;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;167;01/04/2008;2008;4;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;440;01/04/2008;2008;4;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;115;01/04/2008;2008;4;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;277;01/04/2008;2008;4;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;120;01/04/2008;2008;4;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;204;01/04/2008;2008;4;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;106;01/04/2008;2008;4;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;234;01/05/2008;2008;5;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;357;01/05/2008;2008;5;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;687;01/05/2008;2008;5;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;235;01/05/2008;2008;5;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;619;01/05/2008;2008;5;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;161;01/05/2008;2008;5;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;389;01/05/2008;2008;5;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;172;01/05/2008;2008;5;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;289;01/05/2008;2008;5;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;150;01/05/2008;2008;5;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;237;01/06/2008;2008;6;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;361;01/06/2008;2008;6;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;690;01/06/2008;2008;6;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;237;01/06/2008;2008;6;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;620;01/06/2008;2008;6;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;164;01/06/2008;2008;6;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;393;01/06/2008;2008;6;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;174;01/06/2008;2008;6;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;293;01/06/2008;2008;6;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;153;01/06/2008;2008;6;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;173;01/07/2008;2008;7;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;260;01/07/2008;2008;7;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;496;01/07/2008;2008;7;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;172;01/07/2008;2008;7;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;443;01/07/2008;2008;7;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;119;01/07/2008;2008;7;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;281;01/07/2008;2008;7;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;125;01/07/2008;2008;7;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;212;01/07/2008;2008;7;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;112;01/07/2008;2008;7;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;88;01/08/2008;2008;8;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;131;01/08/2008;2008;8;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;252;01/08/2008;2008;8;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;88;01/08/2008;2008;8;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;225;01/08/2008;2008;8;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;63;01/08/2008;2008;8;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;141;01/08/2008;2008;8;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;63;01/08/2008;2008;8;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;106;01/08/2008;2008;8;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;58;01/08/2008;2008;8;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;31;01/09/2008;2008;9;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;47;01/09/2008;2008;9;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;88;01/09/2008;2008;9;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;31;01/09/2008;2008;9;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;79;01/09/2008;2008;9;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;24;01/09/2008;2008;9;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;48;01/09/2008;2008;9;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;24;01/09/2008;2008;9;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;38;01/09/2008;2008;9;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;23;01/09/2008;2008;9;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;7;01/10/2008;2008;10;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;11;01/10/2008;2008;10;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;20;01/10/2008;2008;10;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;8;01/10/2008;2008;10;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;17;01/10/2008;2008;10;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;5;01/10/2008;2008;10;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;11;01/10/2008;2008;10;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;6;01/10/2008;2008;10;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;10;01/10/2008;2008;10;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;5;01/10/2008;2008;10;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;2;01/11/2008;2008;11;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;2;01/11/2008;2008;11;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;3;01/11/2008;2008;11;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;2;01/11/2008;2008;11;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;3;01/11/2008;2008;11;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;4;01/11/2008;2008;11;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;3;01/11/2008;2008;11;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;2;01/11/2008;2008;11;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;5;01/11/2008;2008;11;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;3;01/11/2008;2008;11;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0
AA;1.234;1;01/12/2008;2008;12;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;0;01/12/2008;2008;12;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;1.234;2;01/12/2008;2008;12;555;1.234_AA_555;Product A_AA_555;Group1_AA_555;Product A;Group1;0
AA;2.345;1;01/12/2008;2008;12;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
AA;2.345;2;01/12/2008;2008;12;555;2.345_AA_555;Product B_AA_555;Group1_AA_555;Product B;Group1;0
BB;2.345;3;01/12/2008;2008;12;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;3;01/12/2008;2008;12;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;2.345;3;01/12/2008;2008;12;444;2.345_BB_444;Product B_BB_444;Group1_BB_444;Product B;Group1;0
BB;3.456;3;01/12/2008;2008;12;444;3.456_BB_444;Product C_BB_444;Group2_BB_444;Product C;Group2;0
BB;3.456;4;01/12/2008;2008;12;00X;3.456_BB_00X;Product C_BB_00X;Group2_BB_00X;Product C;Group2;0

",
header=TRUE)

This is how the head table displays:

  Region Part.number Qty     Period Year Month Plant  Material.ID      Subgroup.ID      Group.ID Product.Subgroup Product.Group Not.Consider
1     AA       1.234   5 01/01/2008 2008     1   555 1.234_AA_555 Product A_AA_555 Group1_AA_555        Product A        Group1            0
2     AA       1.234   8 01/01/2008 2008     1   555 1.234_AA_555 Product A_AA_555 Group1_AA_555        Product A        Group1            0
3     AA       1.234  16 01/01/2008 2008     1   555 1.234_AA_555 Product A_AA_555 Group1_AA_555        Product A        Group1            0
4     AA       2.345   5 01/01/2008 2008     1   555 2.345_AA_555 Product B_AA_555 Group1_AA_555        Product B        Group1            0
5     AA       2.345  14 01/01/2008 2008     1   555 2.345_AA_555 Product B_AA_555 Group1_AA_555        Product B        Group1            0
6     BB       2.345   3 01/01/2008 2008     1   444 2.345_BB_444 Product B_BB_444 Group1_BB_444        Product B        Group1            0

The intermediate goal is to get a table with the forecasted values for 2015 organised like that:

Region  Part.number  January  February  March  April  May  ...  Total
AA            1.234        2         4      9      9    5  ...     29

The parts I'm struggling with is how to feed the data as it is organised right now into forecast::stl and how to do so for all the different Region&Part.number combinations.

Hints are appreciated.

Upvotes: 1

Views: 273

Answers (1)

Miha Trošt
Miha Trošt

Reputation: 2022

# load some useful libraries
library(forecast)
library(dplyr)
library(stringr)
library(lubridate)

# reformat Period column to Date variable
df <-
  History %>% 
  mutate(Period = as.Date(dmy(Period)))

# prepeare a vector of possible Region-Part.number combinations
filtered <- 
  df %>% 
  select(Region, Part.number) %>% 
  unique(.)

# filter your data using above defined object called "filtered"
my_df <- 
  df %>% 
  filter(Region == filtered[1, 1],
         Part.number == filtered[1, 2])

# select only relevant columns, prepeare name for a time series
my_ts_df <-
  my_df %>% 
  select(Region, Part.number, Qty, Period) %>% 
  mutate(reg_part = str_c(Region, Part.number)) %>% 
  select(reg_part, Period, Qty)

my_ts_name <- 
  my_ts_df %>% 
  select(reg_part) %>% 
  unique(.) %>% 
  unlist(.) %>% 
  unname(.)

Using the ts() command like below:

ts(my_ts_df$Qty, start = first(my_ts_df$Period), end = last(my_ts_df$Period), freq = 12)

you should be able to construct a time series. However, your dataset only contains data for 1 day.

After you constructed a time series, forecast it, extract the forecasted values and put them in a list. Loop over all rows of object called "filtered", defining a for loop and replacing filtered[1, 1] with filtered[i, 1]. At the end of looping, plyr::ldply() the list into a data frame.

If you provide data for more dates, I can provide better answer.

Upvotes: 2

Related Questions