Rolling sum of last 12 rows for every column in a data frame in r

Question

I have a data frame looking like this: Data frame

The columns only have data every third month. For all columns, except the first one, i want a running sum for the last 12 rows in a new data frame. I have succesfully done this using a for loop with the code:

for (j in 2:3537){
 for (i in 10:415){
  EBIT_total[i,j]<-EBIT[i-9,j]+EBIT[i-6,j]+EBIT[i-3,j]+EBIT[i,j]
  Sales_total[i,j]<-Sales[i-9,j]+Sales[i-6,j]+Sales[i-3,j]+Sales[i,j]
}}

However, this takes a lot of time because of the number of columns.

I have searched the internet for a solution and have tried using apply and rollapply(from the "zoo" library) but without any luck.

I hope someone is able to help, and thanks in advance!

Best regards Rasmus

Answer 1

Try this:

library(zoo)
DF <- data.frame(a = 1:25, b = 1:25) # test data

rollapplyr(DF, 10, function(x) sum(x[c(1, 4, 7, 10)]), fill = NA)

# or even shorter

rollapplyr(DF, list(c(-9, -6, -3, 0)), sum, fill = NA)