How to calculate the cumulative mean of a block of the data frame up to each row in R

user_14857062 0
Viewed 60

I have a simplified dataframe that looks like this:

df
volume ask1 ask2 bid1 bid2
0      38   NA   38   37.9
100    38.1 38.2 37.8 38.2
0      38.4 38.5 38.2 38.3
0      38.4 38.5 38.2 NA
200    38.3 38.1 38   38.4
250    38.4 38.2 NA   38.6

I want to have another column, which contains the mean of df[1:i, 2:5] on the ith row.

I can do this with a for loop:

df[, "midpoint"] <- NA
for (i in 1:nrow(df)) {
   df$midpoint[i] <- mean(as.matrix(df[c(1:i), c(2:5)]), na.rm = TRUE)
}

But as my dataframe is actually large, the for loop takes a long time.

I have tried sapply but failed:

df[, "midpoint"] <- sapply(df, function(i) mean(as.matrix(df[c(1:i), c(2:5)]), na.rm = TRUE))

Could anyone give me some suggestions?

dataframe r
3 Answers

With sapply you can do :

mat <- as.matrix(df[, 2:5])
df$midpoint <- sapply(seq(nrow(df)), function(i) mean(mat[1:i, ], na.rm = TRUE))

You can also take mean of means which would be faster but introduce a small error.

library(dplyr)
df %>%
  mutate(res = rowMeans(select(., 2:5), na.rm = TRUE), 
         res = cummean(res))

#  volume ask1 ask2 bid1 bid2 midpoint      res
#1      0 38.0   NA 38.0 37.9 37.96667 37.96667
#2    100 38.1 38.2 37.8 38.2 38.02857 38.02083
#3      0 38.4 38.5 38.2 38.3 38.14545 38.13056
#4      0 38.4 38.5 38.2   NA 38.19286 38.18958
#5    200 38.3 38.1 38.0 38.4 38.19444 38.19167
#6    250 38.4 38.2   NA 38.6 38.22381 38.22639

Here midpoint is the actual answer from the for loop or sapply code and res is the answer from the above calculation.

user_39629140

You were close with your sapply command, but you need to iterate over the number of rows.

Try

sapply(1:nrow(df), function(x) mean(as.matrix(df)[x, 2:5], na.rm = TRUE))
user_75518160 
rowSums(cumsum(df[2:5]), na.rm=T) / cumsum(rowSums(!is.na(df[2:5])))
user_96019600
Related