create lag variable for each unique ID

user_10783090 0
Viewed 166

I have a data set of different IDs, Date, Quantities. I have created a difference variable and then want to create lag variables for each of the unique IDs. 

df <- data.frame(ID = c("1", "1", "1", "1", "1", "1",
                        "2", "2", "2", "2", "2", "2",
                        "3", "3", "3", "3", "3", "3"),
                 Date = c(seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6),
                          seq(as.Date("2000/1/1"), by = "month", length.out = 6)),
                 QTY = c(143, 23, 432, 43, 432, 4645, 3762, 432, 432, 4645, 3762, 432, 122, 42, 43,655, 4645, 3762))

df <- transform(df, diff = ave(QTY, ID, FUN = function(x) c(NA, diff(x))))

I managed to do it however the lag variables are calculated using the other IDs as well

for(i in unique(df$ID)) {
  setDT(df)[, c("lag1","lag2", "lag3",
                "lag4",
                "lag5") := .(shift(diff, 1L, fill = NA, type = "lag"),
                             shift(diff, 2L, fill = NA, type = "lag"),
                             shift(diff, 3L, fill = NA, type = "lag"),
                             shift(diff, 4L, fill = NA, type = "lag"),
                             shift(diff, 5L, fill = NA, type = "lag")
                )]
}

My result comes out like this 


    ID  Date        QTY    diff lag1 lag2 lag3 lag4 lag5
1   1   2000-01-01  143    NA   NA  NA  NA  NA  NA
2   1   2000-02-01  23    -120  NA  NA  NA  NA  NA
3   1   2000-03-01  432   409   -120    NA  NA  NA  NA
4   1   2000-04-01  43    -389  409 -120    NA  NA  NA
5   1   2000-05-01  432   389   -389    409 -120    NA  NA
6   1   2000-06-01  4645  4213  389 -389    409 -120    NA

7   2   2000-01-01  3762  NA    4213    389 -389    409 -120
8   2   2000-02-01  432   -3330 NA  4213    389 -389    409
9   2   2000-03-01  432   0 -3330   NA  4213    389 -389
10  2   2000-04-01  4645  4213  0   -3330   NA  4213    389
11  2   2000-05-01  3762  -883  4213    0   -3330   NA  4213
12  2   2000-06-01  432   -3330 -883    4213    0   -3330   NA

13  3   2000-01-01  122   NA    -3330   -883    4213    0   -3330
14  3   2000-02-01  42   -80    NA  -3330   -883    4213    0
15  3   2000-03-01  43    1 -80 NA  -3330   -883    4213
16  3   2000-04-01  655   612   1   -80 NA  -3330   -883
17  3   2000-05-01  4645  3990  612 1   -80 NA  -3330
18  3   2000-06-01  3762  -883  3990    612 1   -80 NA

so basically the first ID is calculated correctly but not the others because it's taking into consideration the previous IDs. How can I fix this?

data.table r
3 Answers

Using data.table : 

library(data.table)
n <- 1:5

setDT(df)[, paste0("lag", n) := shift(diff, n), ID]
df

#    ID       Date  QTY  diff  lag1  lag2  lag3  lag4 lag5
# 1:  1 2000-01-01  143    NA    NA    NA    NA    NA   NA
# 2:  1 2000-02-01   23  -120    NA    NA    NA    NA   NA
# 3:  1 2000-03-01  432   409  -120    NA    NA    NA   NA
# 4:  1 2000-04-01   43  -389   409  -120    NA    NA   NA
# 5:  1 2000-05-01  432   389  -389   409  -120    NA   NA
# 6:  1 2000-06-01 4645  4213   389  -389   409  -120   NA
# 7:  2 2000-01-01 3762    NA    NA    NA    NA    NA   NA
# 8:  2 2000-02-01  432 -3330    NA    NA    NA    NA   NA
# 9:  2 2000-03-01  432     0 -3330    NA    NA    NA   NA
#10:  2 2000-04-01 4645  4213     0 -3330    NA    NA   NA
#11:  2 2000-05-01 3762  -883  4213     0 -3330    NA   NA
#12:  2 2000-06-01  432 -3330  -883  4213     0 -3330   NA
#13:  3 2000-01-01  122    NA    NA    NA    NA    NA   NA
#14:  3 2000-02-01   42   -80    NA    NA    NA    NA   NA
#15:  3 2000-03-01   43     1   -80    NA    NA    NA   NA
#16:  3 2000-04-01  655   612     1   -80    NA    NA   NA
#17:  3 2000-05-01 4645  3990   612     1   -80    NA   NA
#18:  3 2000-06-01 3762  -883  3990   612     1   -80   NA
user_39629140

One option involving purrr and dplyr could be:

bind_cols(df, map(.x = 1:5, ~ df %>%
                   group_by(ID) %>%
                   mutate(diff = c(NA, diff(QTY)),
                          !!paste0("lag", .x) := lag(diff, n = .x)) %>%
                   ungroup() %>%
                   select(starts_with("lag"))))

   ID       Date  QTY  lag1  lag2  lag3  lag4 lag5
1   1 2000-01-01  143    NA    NA    NA    NA   NA
2   1 2000-02-01   23    NA    NA    NA    NA   NA
3   1 2000-03-01  432  -120    NA    NA    NA   NA
4   1 2000-04-01   43   409  -120    NA    NA   NA
5   1 2000-05-01  432  -389   409  -120    NA   NA
6   1 2000-06-01 4645   389  -389   409  -120   NA
7   2 2000-01-01 3762    NA    NA    NA    NA   NA
8   2 2000-02-01  432    NA    NA    NA    NA   NA
9   2 2000-03-01  432 -3330    NA    NA    NA   NA
10  2 2000-04-01 4645     0 -3330    NA    NA   NA
11  2 2000-05-01 3762  4213     0 -3330    NA   NA
12  2 2000-06-01  432  -883  4213     0 -3330   NA
13  3 2000-01-01  122    NA    NA    NA    NA   NA
14  3 2000-02-01   42    NA    NA    NA    NA   NA
15  3 2000-03-01   43   -80    NA    NA    NA   NA
16  3 2000-04-01  655     1   -80    NA    NA   NA
17  3 2000-05-01 4645   612     1   -80    NA   NA
18  3 2000-06-01 3762  3990   612     1   -80   NA
user_59645570

Can also be done with just dplyr

library(dplyr)

df <- df %>%
  group_by(ID) %>%
  mutate(diff = QTY-lag(QTY),
         lag1 = lag(diff),
         lag2 = lag(lag1),
         lag3 = lag(lag2),
         lag4 = lag(lag3),
         lag5 = lag(lag4))
user_63353720
Related