1
$\begingroup$

I have this kind of data:

dat
       year month shop item sales
1  2013     1    1    1     1
2  2013     1    1    1     3
3  2013     1    1    2     5
4  2013     1    1    2     7
5  2013     1    2    1     9
6  2013     1    2    1    11
7  2013     1    2    2    13
8  2013     1    2    2    15
9  2013     2    1    1    17
10 2013     2    1    1    19
11 2013     2    1    2    21
12 2013     2    1    2    23
13 2013     2    2    1    25
14 2013     2    2    1    27
15 2013     2    2    2    29
16 2013     2    2    2    31
17 2013     3    1    1     0
18 2013     3    1    1     0
19 2013     3    1    2     0
20 2013     3    1    2     0
21 2013     3    2    1     0
22 2013     3    2    1     0
23 2013     3    2    2     0
24 2013     3    2    2     0

The goal is to predict the sales for the next month (3), for each shop and each item. In other words, how high will be the sales for item 1 and 2 in shop 1 and 2 in the third month? Because the sales take place on different days (which I don´t have) there is more than one sale per item and shop for each month.

My idea was to create a one month lag. The lag has to be created for each shop and item because each item and each shop has different developments. In this simple case, it would look like this:

library(dplyr)
dat %>%
  mutate(lag_sales = lag(sales, 8))

   year month shop item sales lag_sales
1  2013     1    1    1     1        NA
2  2013     1    1    1     3        NA
3  2013     1    1    2     5        NA
4  2013     1    1    2     7        NA
5  2013     1    2    1     9        NA
6  2013     1    2    1    11        NA
7  2013     1    2    2    13        NA
8  2013     1    2    2    15        NA
9  2013     2    1    1    17         1
10 2013     2    1    1    19         3
11 2013     2    1    2    21         5
12 2013     2    1    2    23         7
13 2013     2    2    1    25         9
14 2013     2    2    1    27        11
15 2013     2    2    2    29        13
16 2013     2    2    2    31        15
17 2013     3    1    1     0        17
18 2013     3    1    1     0        19
19 2013     3    1    2     0        21
20 2013     3    1    2     0        23
21 2013     3    2    1     0        25
22 2013     3    2    1     0        27
23 2013     3    2    2     0        29
24 2013     3    2    2     0        31

Is this a proper way to deal with this problem? If so, in the original data, the cases of each month are not sorted like this (i.e. the number of shops and items per month are changing). So I can´t simply "move" the sales column like in this simple example. This is the second issue.

With this data, I could calculate some other measure like the rolling mean. For this measure year, month, shop and item has to be considered.

dat %>%
  group_by(year, month) %>%
  mutate(rm2 = roll_meanr(lag_sales, 2))
# A tibble: 24 x 7
    year month  shop  item sales lag_sales avg_3
   <dbl> <int> <int> <int> <dbl>     <dbl> <dbl>
 1  2013     1     1     1     1        NA    NA
 2  2013     1     1     1     3        NA    NA
 3  2013     1     1     2     5        NA    NA
 4  2013     1     1     2     7        NA    NA
 5  2013     1     2     1     9        NA    NA
 6  2013     1     2     1    11        NA    NA
 7  2013     1     2     2    13        NA    NA
 8  2013     1     2     2    15        NA    NA
 9  2013     2     1     1    17         1    NA
10  2013     2     1     1    19         3     2
11  2013     2     1     2    21         5     4
12  2013     2     1     2    23         7     6
13  2013     2     2     1    25         9     8
14  2013     2     2     1    27        11    10
15  2013     2     2     2    29        13    12
16  2013     2     2     2    31        15    14
17  2013     3     1     1     0        17    NA
18  2013     3     1     1     0        19    18
19  2013     3     1     2     0        21    20
20  2013     3     1     2     0        23    22
21  2013     3     2     1     0        25    24
22  2013     3     2     1     0        27    26
23  2013     3     2     2     0        29    28
24  2013     3     2     2     0        31    30

Is this a proper way to deal with this problem?

I know that this is more than one question and it´s not just a conceptual question but also a coding question. Sorry, it´s hard for me to separate them.

dput(dat)
structure(list(year = c(2013, 2013, 2013, 2013, 2013, 2013, 2013, 
2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 2013, 
2013, 2013, 2013, 2013, 2013, 2013), month = c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L), shop = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L), 
    item = c(1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 
    1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L), sales = c(1, 
    3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 0, 
    0, 0, 0, 0, 0, 0, 0)), class = "data.frame", row.names = c(NA, 
-24L))
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.