Calculate value using row above in data.frame and another column

Question

Hi I want to do something in R that seems like it should be simple but I seem to be having a brain fade.

For each row in a data.frame I want to get the value of Vol in row above, add value of In for that row and minus a value dependent on this value.

Here is my attempt but lag is just looking one row back for the values at the start, not continuing to look back once the next value has been calculated

library(dplyr)
df <- data.frame(In = c(1,4,0,0,1,2,3,0,0), Vol = c(1,rep(NA,8)))

df %>% mutate(Vol = (lag(Vol) + In) -  (lag(Vol) + In)*0.01)

desired output =

  In     Vol
1  1  1.00
2  4  4.95
3  0  4.90
4  0  4.85
5  1  5.79
6  2  7.72
7  3 10.61
8  0 10.50
9  0 10.40

Answer 1

Here is a solution using accumulate from the purrr package. The accumulate function can apply a function with two arguments, such as x and y, to a sequence of vector. The return value would become the input value of the next round.

In the following example, I asked the accumulate function to begin at the second number of the In column to the end. I also provided 1 to the .init argument, which will be the first x to the function.

library(dplyr)
library(purrr)

df <- data.frame(In = c(1,4,0,0,1,2,3,0,0), Vol = c(1,rep(NA,8)))

df %>% 
  mutate(Vol = accumulate(In[2:n()], function(x, y) (x + y) * 0.99, .init = 1))
#   In       Vol
# 1  1  1.000000
# 2  4  4.950000
# 3  0  4.900500
# 4  0  4.851495
# 5  1  5.792980
# 6  2  7.715050
# 7  3 10.607900
# 8  0 10.501821
# 9  0 10.396803

In addition, it seems like the first value from the Vol column is the same as the first value of the In column. If what you are trying to do is accumulate the process simply on the In column, the following code would be more concise and you don't even need to copy the first value to the Vol column.

df %>% 
  mutate(Vol = accumulate(In, function(x, y) (x + y) * 0.99))
#   In       Vol
# 1  1  1.000000
# 2  4  4.950000
# 3  0  4.900500
# 4  0  4.851495
# 5  1  5.792980
# 6  2  7.715050
# 7  3 10.607900
# 8  0 10.501821
# 9  0 10.396803