gibbz00 - 7 months ago 29
R Question

# R Cumulative calculation based on prior values in the same column

This is what my dataframe looks like.

`V3`
is my desired Column.
`V3`
is not available to me.

``````library(data.table)

Level  V1  V2
0    10  2
1     0  3
1     0  2
1     0  2 ')
``````

I am trying to calculate V3 based on prior values of V3. The V3 formula is:

``````New Value of V3 =((Prior Value of V3+ Prior Value of V3*V2)*Level)+V1
1st Row V3 = (NA+NA*3)*1 + 10 = 10
2nd Row V3 = (10+10*3)*1 + 0 =40
3rd Row V3 = (40+40*2)*1 + 0 =120
4th Row V3  = (120+120*2)*1 + 0 = 360
``````

The output should look like this.

``````Level  V1  V2  V3
0    10  2   10
1     0  3   40
1     0  2   120
1     0  2   360
``````

I was trying:

``````dt[,V3:= (cumsum(V3+V3*V2)*Level)+V1]
``````

I reworked your efforts in the comments to get the desired result:

`````` dt[,V3:=cumprod( c(V1[1] ,(Level*(1 + V2))[-1]) ) ]
dt

Level V1 V2  V3
1:     0 10  2  10
2:     1  0  3  40
3:     1  0  2 120
4:     1  0  2 360
``````

I didn't actually get an error (only a warning) with dt[,V3:= V1[1] * cumprod((Level*(1 + V2))[-1])]. Using the [-1] shortened the cumprod with no extension, and resulted in recycling.