Linked Questions

Popular Questions

R data.table calculations with previous row dependencies

Asked by At

Below is some data which I currently calculate in Excel.

col_A   col _B  col_C col_D col_E   col_F    col_G
-1.5%   0.010   1.00    1   1.00     -       -   
-5.4%   0.024   1.00    1   1.00     0.01   -0.00 
-7.9%   0.036   1.00    1   1.00     0.02   -0.00 
-12.7%  0.052   0.99    1   0.99     0.06   -0.01 
-4.6%   0.049   0.98    1   0.98     0.19   -0.01 
-8.3%   0.051   0.95    1   0.95     0.39   -0.03 
-7.3%   0.052   0.88    1   0.88     1.00   -0.07 
-9.2%   0.055   0.69    1   0.69     2.31   -0.21 
-7.9%   0.055   0.38    1   0.38     5.63   -0.44 
-2.2%   0.051   0.29    1   0.29     11.13  -0.24 

I have been trying to perform the calculations in R using data.table. The problem I have is that data.table performs calculation column-wise. I need the calculations to be performed row-wise, because of dependencies on the results of previous row values. The Excel-formulas for the calculated columns are given below, with "T" indicating "current row" and "T-1" indication "previous row"

col_C: (col_C.T-1) * (1 + col_G.T)

col_D: max (Col_C.T, col_D.T-1)

col_E: (col_C.T / col_D.T)

col_F: max ((1 - (col_C.T-1 / col_D.T-1)) / col B.T-1), 0.01)

col_G: col_A * col_F

Any assistance is greatly appreciated.

Related Questions