CODEWITHSUNDEEP
rdata.tabledplyrcumsum
gibbz00
gibbz00

Reputation: 1987

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]

Upvotes: 3

Views: 1169

Answers (1)

akrun
akrun

Reputation: 886928

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

Upvotes: 3

Related Questions