gibbz00 gibbz00 - 1 month ago 7
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)
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

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
Comments