Change row values ​​by lag value in another column

Question

I have a fairly large dataset, and I'm interested in "marching" values ​​forward in time based on values ​​from another column. For example, if I have Value = 3at Time = 0and DesiredShift = 2I want 3 to change two lines to Time = 2. Here is a reproducible example.

Create reproducible fake data

library(data.table)
set.seed(1)
rowsPerID <- 8
dat <- CJ(1:2, 1:rowsPerID)
setnames(dat, c("ID","Time"))
dat[, Value := rpois(.N, 4)]
dat[, Shift := sample(0:2, size=.N, replace=TRUE)]

Fake Data

#     ID Time Value Shift
#  1:  1    1     3     2
#  2:  1    2     3     2
#  3:  1    3     4     1
#  4:  1    4     7     2
#  5:  1    5     2     2
#  6:  1    6     7     0
#  7:  1    7     7     1
#  8:  1    8     5     0
#  9:  2    1     5     0
# 10:  2    2     1     1
# 11:  2    3     2     0
# 12:  2    4     2     1
# 13:  2    5     5     2
# 14:  2    6     3     1
# 15:  2    7     5     1
# 16:  2    8     4     1

I want everyone to Valuemove forward according to the column Shift. So the column DesiredOutputfor row 3 will be 3, since the value is Time=1equal to Value = 3and Shift = 2.

Line 4 shows 3 + 4 = 7, as 3 moves down by 2 and 4 go down.

ID , , data.table, .

#     ID Time Value Shift DesiredOutput
#  1:  1    1     3     2       NA
#  2:  1    2     3     2       NA
#  3:  1    3     4     1       3
#  4:  1    4     7     2       3+4 = 7
#  5:  1    5     2     2       NA
#  6:  1    6     7     0       7+7 = 14
#  7:  1    7     7     1       2
#  8:  1    8     5     0       7+5 = 12
#  9:  2    1     5     0       5
# 10:  2    2     1     1       NA
# 11:  2    3     2     0       1+2 = 3
# 12:  2    4     2     1       NA
# 13:  2    5     5     2       2
# 14:  2    6     3     1       NA
# 15:  2    7     5     1       3+5=8
# 16:  2    8     4     1       5

data.table::shift, , .

Answer 1

:

dat[, TargetIndex:= .I + Shift]

toMerge = dat[, list(Out = sum(Value)), by='TargetIndex']

dat[, TargetIndex:= .I]

# dat = merge(dat, toMerge, by='TargetIndex', all=TRUE)
dat[toMerge, on='TargetIndex', DesiredOutput:= i.Out]

> dat
#     ID Time Value Shift TargetIndex DesiredOutput
#  1:  1    1     3     2           1            NA
#  2:  1    2     3     2           2            NA
#  3:  1    3     4     1           3             3
#  4:  1    4     7     2           4             7
#  5:  1    5     2     2           5            NA
#  6:  1    6     7     0           6            14
#  7:  1    7     7     1           7             2
#  8:  1    8     5     0           8            12
#  9:  2    1     5     0           9             5
# 10:  2    2     1     1          10            NA
# 11:  2    3     2     0          11             3
# 12:  2    4     2     1          12            NA
# 13:  2    5     5     2          13             2
# 14:  2    6     3     1          14            NA
# 15:  2    7     5     1          15             8
# 16:  2    8     4     1          16             5