I have data grouped by an id variable with multiple, unique observations per quarter and with different group sizes per id:
library(dplyr)
library(data.table)
library(lubridate)
v2 <- sample(1:100, 15)
df <- data.frame(qy = c(rep('2016-01-01', 5), rep('2016-04-01', 5), rep('2016-10-01', 5)),
id = c(rep(c('a','a','b','b','c'), 3)),
value_t = c(0,0,1,1,0,1,1,0,0,0,0,0,1,1,1),
value2_t = c(v2))
df$qy <- ymd(df$qy)
df <- df %>% arrange(id, qy)
> df
qy id value_t value2_t
1 2016-01-01 a 0 49
2 2016-01-01 a 0 4
3 2016-01-01 b 1 5
4 2016-01-01 b 1 48
5 2016-01-01 c 0 32
6 2016-04-01 a 1 81
7 2016-04-01 a 1 6
8 2016-04-01 b 0 71
9 2016-04-01 b 0 47
10 2016-04-01 c 0 78
11 2016-10-01 a 0 31
12 2016-10-01 a 0 10
13 2016-10-01 b 1 37
14 2016-10-01 b 1 63
15 2016-10-01 c 1 36
I attempt to create two lag variables grouped by id with lags of t-1 and t-2, respectively:
setDT(df)[order(qy), paste0('value_t', 1:2) := shift(value_t, 1:2) , by = id]
Although I've grouped by id, the lags don't follow the grouping assignment - the lag variables are just rolling lags within the group:
> df
qy id value_t value2_t value_t1 value_t2
1: 2016-01-01 a 0 49 NA NA
2: 2016-01-01 a 0 4 0 NA
3: 2016-04-01 a 1 81 0 0
4: 2016-04-01 a 1 6 1 0
5: 2016-10-01 a 0 31 1 1
6: 2016-10-01 a 0 10 0 1
7: 2016-01-01 b 1 5 NA NA
8: 2016-01-01 b 1 48 1 NA
9: 2016-04-01 b 0 71 1 1
10: 2016-04-01 b 0 47 0 1
11: 2016-10-01 b 1 37 0 0
12: 2016-10-01 b 1 63 1 0
13: 2016-01-01 c 0 32 NA NA
14: 2016-04-01 c 0 78 0 NA
15: 2016-10-01 c 1 36 0 0
I would like the lag variables to respect the grouping despite there being multiple observations per quarter as follows:
> df
qy id value_t value2_t value_t1 value_t2
1 2016-01-01 a 0 49 NA NA
2 2016-01-01 a 0 4 NA NA
3 2016-04-01 a 1 81 0 NA
4 2016-04-01 a 1 6 0 NA
5 2016-10-01 a 0 31 1 0
6 2016-10-01 a 0 10 1 0
7 2016-01-01 b 1 5 NA NA
8 2016-01-01 b 1 48 NA NA
9 2016-04-01 b 0 71 1 NA
10 2016-04-01 b 0 47 1 NA
11 2016-10-01 b 1 37 0 1
12 2016-10-01 b 1 63 0 1
13 2016-01-01 c 0 32 NA NA
14 2016-04-01 c 0 78 0 NA
15 2016-10-01 c 1 36 0 0
Any suggestions in data.table or dplyr in particular would be greatly appreciated!
Update: Thanks all for your comments. I believe David A. is correct in that the main issue is the varied id group size, and I've updated the question to highlight this.
We can create a subset of data frame based on unique qy and id, create the lag columns value_t1 and value_t2, and then merge back to the original data frame.
library(dplyr)
library(data.table)
library(lubridate)
# Create example data frame
set.seed(123)
v2 <- sample(1:100, 15)
df <- data.frame(qy = c(rep('2016-01-01', 5), rep('2016-04-01', 5), rep('2016-10-01', 5)),
id = c(rep(c('a','a','b','b','c'), 3)),
value_t = c(0,0,1,1,0,1,1,0,0,0,0,0,1,1,1),
value2_t = c(v2))
df$qy <- ymd(df$qy)
df <- df %>% arrange(id, qy)
# Process the data
df2 <- df %>%
distinct(id, qy, .keep_all = TRUE) %>%
group_by(id) %>%
mutate(value_t1 = lag(value_t, n = 1L),
value_t2 = lag(value_t, n = 2L)) %>%
select(-value_t, -value2_t) %>%
ungroup() %>%
left_join(df, ., by = c("qy", "id"))
df2
# qy id value_t value2_t value_t1 value_t2
# 1 2016-01-01 a 0 29 NA NA
# 2 2016-01-01 a 0 79 NA NA
# 3 2016-04-01 a 1 5 0 NA
# 4 2016-04-01 a 1 50 0 NA
# 5 2016-10-01 a 0 87 1 0
# 6 2016-10-01 a 0 98 1 0
# 7 2016-01-01 b 1 41 NA NA
# 8 2016-01-01 b 1 86 NA NA
# 9 2016-04-01 b 0 83 1 NA
# 10 2016-04-01 b 0 51 1 NA
# 11 2016-10-01 b 1 60 0 1
# 12 2016-10-01 b 1 94 0 1
# 13 2016-01-01 c 0 91 NA NA
# 14 2016-04-01 c 0 42 0 NA
# 15 2016-10-01 c 1 9 0 0