How to update row by group in sequence

Question

I have a dt:

library(data.table)

DT <- data.table(a = c(1,2,3,4,5), b = c(4,5,6,7,8), c = c("X","X","X","Y","Y") ) 

I want to add one column d, within each group of column C:

• the first row value should be the same as b[i],
• the second to last row within each group should be d[i-1] + 2*b[i]

Intended results:

   a b c d
1: 1 4 X 4
2: 2 5 X 14
3: 3 6 X 26
4: 4 7 Y 7
5: 5 8 Y 23

I tried to use functions such as shift but I struggle to update rows dynamically (so to speak) here, wonder if there is any elegant data.table style solution?

Answer 1

We can use cumsum and subtract the first row using [1]:

DT[, d := cumsum(2 * b) - b[1], .(c)][]

#>    a b c  d
#> 1: 1 4 X  4
#> 2: 2 5 X 14
#> 3: 3 6 X 26
#> 4: 4 7 Y  7
#> 5: 5 8 Y 23