lag and cumulative sum by group

Question

For a matrix with three columns .

     ID     t      res
     1      1     -1.5
     1      2     -1.5
     1      3      0.5
     1      4      0.5
     2      1     -0.5
     2      2     -0.5
     2      3     -2.0
     2      4     -1.5
     2      5      1.5

My goal is to sum the values of column res by ID like this.

   (-1.5*(-1.5+0.5+0.5)     - 1.5*(0.5+0.5)      + 0.5*(0.5))/(4-1) +
   (-0.5*(-0.5-2.0-1.5+1.5) - 0.5*(-2.0-1.5+1.5) - 2.0*(-1.5+1.5)  -1.5*(1.5))/(5-1) 
   =  -0.167 

Any suggestions on how to sum and divide this by groups is much appreciated.

Answer 1

Here is a base R solution.

df1 <- "ID     t      res
     1      1     -1.5
     1      2     -1.5
     1      3      0.5
     1      4      0.5
     2      1     -0.5
     2      2     -0.5
     2      3     -2.0
     2      4     -1.5
     2      5      1.5"
df1 <- read.table(text = df1, header = TRUE)


out <- tapply(df1$res, df1$ID, FUN = \(x) {
  sum(sapply(seq_along(x), \(i) x[i]*sum(x[-(1:i)]))) / (length(x) - 1)
})
out <- sum(out)
out
#> [1] -0.1666667

^{Created on 2023-02-25 with reprex v2.0.2}