0

I have a data frame below. I wondered how to calculate the mean for column 'value_t' by expanding window starting from '2014-1-5'. e.g. val(1)=mean(1:5), value(2)=mean(1:6), value(3)=mean(1:7). I hope the algorithm is efficient (w/o loop).

df<-data.frame(date_t=paste('2014-01-',1:15,sep=""),value_t=1:15)
> df
   date_t        value_t
1   2014-01-1       1
2   2014-01-2       2
3   2014-01-3       3
4   2014-01-4       4
5   2014-01-5       5
6   2014-01-6       6
7   2014-01-7       7
8   2014-01-8       8
9   2014-01-9       9
10 2014-01-10      10
11 2014-01-11      11
12 2014-01-12      12
13 2014-01-13      13
14 2014-01-14      14
15 2014-01-15      15
Cœur
  • 37,241
  • 25
  • 195
  • 267
YYY
  • 605
  • 3
  • 8
  • 16

3 Answers3

2

How about sapply(5:NROW(df), function(.) mean(df$value_t[1:.]))? It involves kind of a loop (sapply) but it should be reasonable fast.

thothal
  • 16,690
  • 3
  • 36
  • 71
  • Thank you for your help. Since I have a pretty large data.frame(100,000*20), it looks very slow. – YYY Oct 10 '14 at 18:38
1

Have a look at this

df$val <- cumsum(df$value_t) / 1:nrow(df)
df$val[1:4] <- NA
#     date_t value_t val
#  2014-01-1       1  NA
#  2014-01-2       2  NA
#  2014-01-3       3  NA
#  2014-01-4       4  NA
#  2014-01-5       5 3.0
#  2014-01-6       6 3.5
#  2014-01-7       7 4.0
#  2014-01-8       8 4.5
#  2014-01-9       9 5.0
# 2014-01-10      10 5.5
# 2014-01-11      11 6.0
# 2014-01-12      12 6.5
# 2014-01-13      13 7.0
# 2014-01-14      14 7.5
# 2014-01-15      15 8.0

If you just want the vector, and you don't want to tamper with df, do

val <- (cumsum(df$value_t) / 1:nrow(df))[-(1:4)]
# 3.0 3.5 4.0 4.5 5.0 5.5 6.0 6.5 7.0 7.5 8.0
blakeoft
  • 2,370
  • 1
  • 14
  • 15
  • I appreciate your solution. Is there any way to do it more general? The read case is not the average, it might be a self-defined function, such as cumulative percentile ranking. – YYY Oct 09 '14 at 16:43
  • I suppose one of the apply functions would be best for the more general cases. – blakeoft Oct 10 '14 at 12:33
1

The sapply(...) solution is faster than the for(...) loop, but only just (about 2% - well within the margin of error). It turns out that extracting the column from the data frame at every step slows things down considerably. If you grab that column as a vector first, you get a ~25% improvement.

df <- data.frame(value=1:1e4)
f.sapply <- function() sapply(5:nrow(df), function(.) mean(df$value[1:.]))
f.loop   <- function() {result <- numeric(nrow(df)-4)
                        for (i in 5:nrow(df)) result[i-4] <- mean(df$value[1:i])
                        result
}
f.vec    <- function() {vec<-df$value
                        sapply(5:nrow(df), function(.) mean(vec[1:.]))
}

# do they produce identical results?
identical(f.sapply(),f.loop())
# [1] TRUE
identical(f.sapply(),f.vec())
# [1] TRUE
# which is faster?
library(microbenchmark)
microbenchmark(f.sapply(),f.loop(),f.vec())
# Unit: milliseconds
#        expr      min       lq   median        uq      max neval
#  f.sapply() 904.2934 929.7361 947.7621  978.8775 1496.455   100
#    f.loop() 927.5288 950.3632 963.5926 1012.2407 1347.889   100
#     f.vec() 669.5615 697.3639 711.1498  751.2634 1060.056   100
jlhoward
  • 58,004
  • 7
  • 97
  • 140
  • Thanks for the benchmarking results and the pointer to `microbenchmark` a library which I did not know yet :) – thothal Oct 14 '14 at 09:23