当前位置: 动力学知识库 > 问答 > 编程问答 >

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.

网友答案:
# 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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: