tucson tucson - 3 months ago 15
R Question

Calculate average value over last hundred rows of a column

Here is a quick hack to calculate the average of x over the last 3 rows:

s <- data.table(x=c(.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,0.1))
s$avgx = NA
for(i in (4:10)) s$avgx[i] <- mean(s$x[(i-1):(i-3)])
s
x avgx
1: 0.1 NA
2: 0.2 NA
3: 0.3 NA
4: 0.4 0.2 # 0.2 is average of x in previous 3 rows
5: 0.5 0.3 # etc.
6: 0.6 0.4
7: 0.7 0.5
8: 0.8 0.6
9: 0.9 0.7
10: 0.1 0.8


This code works, but gets very slow for large data (100k rows, average over last 100 or 1000 rows, for example).

Is there a quick way to do this, either with data table, or data frame?

Answer

zoo::rollmean() does most of what you're after, it's just a case of lining up the rows as you want:

> rollmean(s$x, 3, na.pad=TRUE, align="right")
 [1]  NA  NA 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.6

> means <- rollmean(s$x, 3, na.pad=TRUE, align="right")
> c(NA, means[-length(means)])
 [1]  NA  NA  NA 0.2 0.3 0.4 0.5 0.6 0.7 0.8