gibbz00 - 1 year ago 68

R Question

This is what my data.table looks like. The last column New_Amount is my desired column.

`StartingAmount<-10`

library(data.table)

dt <- fread('

Level Interest New_Amount

0 .5 10

1 .5 15

0 .5 15

0 .3 15

1 .2 18

0 .4 18

1 .5 27

')

Lets say I am starting with $10. For each row where Level =1, I want to calculate my new amount after adding in interest. So for the second row where Level =1, my New_Amount is 10+(10*.5) =15. In the 5th row where Level=1, my New_Amount is 15+(15*.2)=18. I am going to ignore rows where Level=0 and just carry the last observation forward.

This is my attempt:

`dt[, NewAmount:= ifelse(Level==1,StartingAmount+StartingAmount*Interest,0)]`

dt[, NewAmount:= ifelse(Level==1, New_Amount + New_Amount*Interest , NA)]

Answer Source

You can use `cumprod`

on the `Interest`

column which you can multiply with the `Level`

column to determine if extra interest should be added to the previous one:

```
library(data.table)
dt[, New_Amount := StartingAmount * cumprod(1 + Level * Interest)][]
# Level Interest New_Amount
#1: 0 0.5 10
#2: 1 0.5 15
#3: 0 0.5 15
#4: 0 0.3 15
#5: 1 0.2 18
#6: 0 0.4 18
#7: 1 0.5 27
```