gibbz00 gibbz00 - 1 month ago 23
R Question

R Cumulative sum based on two variables

This is whay my data.table looks like. The last column NewShares is my desired columns.

library(data.table)
dt <- fread('
Client Level NumShares Interest NewShares
A 0 10 0 10
A 0 0 0 10
A 1 0 .1 11
A 0 9 0 20
A 1 0 .2 24')


I want the cumulative NumShares while taking into account NewShares bought with interest. So as of the 1st row, the cumulative shares are 10. As of the 3rd row,
Level==1
, so I have to add interest. It will be
10+(10*.1)=11
. As of the 4th row, the cumulative shares is
11+9 =20
. As of the last row,
Level==1
, so new shares are
20+(20*.2) = 24


I tried:

dt[,NewShares:= NumShares* cumprod(1+ NumShares*Interest),by=Client]

Answer

We can do double cumsum and wrap it with ceiling

dt[, NewSharesN := ceiling(cumsum(cumsum(NumShares)*Interest + NumShares)) , by = Client]
dt
#   Client Level NumShares Interest NewShares NewSharesN
#1:      A     0        10      0.0        10         10
#2:      A     0         0      0.0        10         10
#3:      A     1         0      0.1        11         11
#4:      A     0         9      0.0        20         20
#5:      A     1         0      0.2        24         24