CODEWITHSUNDEEP
rdata.table
bumblebee
bumblebee

Reputation: 1116

R: get each entry's previous entries in data.table

The data I have consist of groups and each group has entries along a time dimension.

library(data.table); set.seed(42)
dat <- data.table(group=rep(1:2,each=5), t=rep(1:5,2), x=rnorm(10))

> head(dat)
   group t          x
1:     1 1  1.3709584
2:     1 2 -0.5646982
3:     1 3  0.3631284
4:     1 4  0.6328626
5:     1 5  0.4042683
6:     2 1 -0.1061245

For each group, I would like to get all earlier time points of each time point. That is, the results should look like this:

> head(res)
   group t.cons  t          x
1:     1      2  1  1.3709584
2:     1      3  1  1.3709584
3:     1      3  2 -0.5646982
4:     1      4  1  1.3709584
5:     1      4  2 -0.5646982
6:     1      4  3  0.3631284

where t.cons gives the entry in t that is being considered.

Upvotes: 0

Views: 59

Answers (1)

Roland
Roland

Reputation: 132706

You can use a non-equi self join:

library(data.table)
set.seed(42)
dat <- data.table(group=rep(1:2,each=5), t=rep(1:5,2), x=rnorm(10))
#    group t           x
# 1:     1 1  1.37095845
# 2:     1 2 -0.56469817
# 3:     1 3  0.36312841
# 4:     1 4  0.63286260
# 5:     1 5  0.40426832
# 6:     2 1 -0.10612452
# 7:     2 2  1.51152200
# 8:     2 3 -0.09465904
# 9:     2 4  2.01842371
#10:     2 5 -0.06271410

dat[dat, .(group, t.cons = i.t, t = x.t, x),
    on = c("group", "t < t"), allow.cartesian = TRUE,
    nomatch = NULL]
#    group t.cons t           x
# 1:     1      2 1  1.37095845
# 2:     1      3 1  1.37095845
# 3:     1      3 2 -0.56469817
# 4:     1      4 1  1.37095845
# 5:     1      4 2 -0.56469817
# 6:     1      4 3  0.36312841
# 7:     1      5 1  1.37095845
# 8:     1      5 2 -0.56469817
# 9:     1      5 3  0.36312841
#10:     1      5 4  0.63286260
#11:     2      2 1 -0.10612452
#12:     2      3 1 -0.10612452
#13:     2      3 2  1.51152200
#14:     2      4 1 -0.10612452
#15:     2      4 2  1.51152200
#16:     2      4 3 -0.09465904
#17:     2      5 1 -0.10612452
#18:     2      5 2  1.51152200
#19:     2      5 3 -0.09465904
#20:     2      5 4  2.01842371

Upvotes: 3

Related Questions