gibbz00 - 1 year ago 133
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)
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]
``````

We can do double `cumsum` and wrap it with `ceiling`
``````dt[, NewSharesN := ceiling(cumsum(cumsum(NumShares)*Interest + NumShares)) , by = Client]