gibbz00 - 1 year ago 79
R Question

# Dynamic and Conditional Column Updating in data.table

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

`````` StartingAmount<-10
library(data.table)
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)]
``````

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download