R How to use next row for same column calculation

Question

I've been trying to replicate some basic Excel calculations in R but I can't find the right solution for this one.

I want to create a new column of a data.frame but the values being calculated should be based on the next values of the same column, so it is kind of being calculated from down-up.

I am able to do it using values of the same column (previous ones) but doesn't work for the next ones.

First part of my approach works fine (when using next values of another column), but not in the same column.

In excel it is simple but can't make it work.

Also, I don't know if a for loop is the best option, I have to do it with 1 million rows data.frame so it may be slow.

Thank you in advance.

p <- c(1,2,3,4,5,6,7,6,8,9,10)
g <- c(4,4,3,4,5,6,6,6,8,0,0)
data <- data.frame(p,g)
a <- 2

My approach

data$f2<- 0
for (i in 1:nrow(data)){
  data$f2[i] = data$g[i]*a+data$g[i+1]*a+data$g[i+2]*a+data$f2[i+1]*a+data$f2[i+2]*a
}


#Output
   g f2

1  4 22
2  4 22
3  3 24
4  4 30
5  5 34
6  6 36
7  6 40
8  6 28
9  8 16
10 0 NA
11 0 NA
> 

#Desired Correct Output

    g    f2
   
1     4 11232
2     4 11232
3     3 11232
4     4  4106
5     5  1498
6     6   540
7     6   192
8     6    60
9     8    16

Answer 1

base R solution

Code:

data$f2 <- 0

for ( i in rev(seq_len(nrow(data)-2))) {
  data$f2[i] <-  with(data, sum( na.omit( c( g[c(i + 0:2)], f2[c(i + 1:2)])) * a                               
                               ) 
                     )
}

Output

 data
 #    p g    f2
 #1:  1 4 83882
 #2:  2 4 30698
 #3:  3 3 11232
 #4:  4 4  4106
 #5:  5 5  1498
 #6:  6 6   540
 #7:  7 6   192
 #8:  6 6    60
 #9:  8 8    16
 #10:  9 0     0
 #11: 10 0     0

Answer 2

This looks like an Arma calculation. You could use the filter function from signal package :

library(signal)

a <- 2
f <- signal::Arma(b=rep(a,3),a=c(1,-rep(a,3)))

signal::filter(f,sort(g,decreasing=T))
# [1]     16     60    192    540   1498   4106  11234  30704  83898 229218 626238

data$f2 <- sort(signal::filter(f,sort(g,decreasing=T)),decreasing=T)

# A tibble: 11 x 3
       p     g     f2
   <dbl> <dbl>  <dbl>
 1     1     4 626238
 2     2     4 229218
 3     3     3  83898
 4     4     4  30704
 5     5     5  11234
 6     6     6   4106
 7     7     6   1498
 8     6     6    540
 9     8     8    192
10     9     0     60
11    10     0     16

Some remarks :

• Not sure why your calculation doesn't go beyond 11234
• The coefficients of this Arma filter diverge as shown by zplane below : the x (poles of the filter) should all be within unit circle for a stable filter.
  This is not the case => results will be Inf before reaching the million rows

zplane(f)