0

Simple data frame: 

X2 <- runif(20, -2, 2)
X1 <- c(1:20)
data <- cbind(X1, X2)

Would like to calculate a new column of data consisting of the average of five data points from X2, for the whole column; eg, this new column's first point is the average of the first five points from X2, point 2 is the average of the second five points, etc. New column should have four total values in this example. I'm guessing this is done with a function and an apply, but I have no idea how to proceed. Any help would be appreciated!

Molly Westbrook
  • 117
  • 9
  • You can create a new column for the groups using `data$newcol =rep(1:(dim(data)[1]/5),each=5)`. Then you can use the aggregate function to find the four means like this `data$mean <- aggregate(data$X2, by = list(data$newcol), FUN=mean)` – XXavier Feb 06 '20 at 21:13

2 Answers2

1

If you're not after the rolling mean, you could do:

n_splits <- 5

transform(
  data,
  mean_X2 = ave(X2, ceiling(seq_along(X2)/n_splits), FUN = mean)
)

The idea behind is that you get only 4 different values (each for 1 cut of 5 rows):

   X1         X2    mean_X2
1   1  0.2450328  0.4765226
2   2 -0.2485971  0.4765226
3   3  0.1073639  0.4765226
4   4  1.6354394  0.4765226
5   5  0.6433740  0.4765226
6   6  1.6846952  0.4854171
7   7 -1.9208249  0.4854171
8   8  0.9209313  0.4854171
9   9  0.2554107  0.4854171
10 10  1.4868733  0.4854171
11 11  1.4396200 -0.4308499
12 12 -1.4503835 -0.4308499
13 13 -0.1566408 -0.4308499
14 14 -1.5484536 -0.4308499
15 15 -0.4383918 -0.4308499
16 16  0.6936388  1.0445175
17 17  0.1052725  1.0445175
18 18  0.9264836  1.0445175
19 19  1.8322978  1.0445175
20 20  1.6648948  1.0445175

Data:

set.seed(3243)

X2 <- runif(20, -2, 2)
X1 <- c(1:20)
data <- cbind(X1, X2)
arg0naut91
  • 14,574
  • 2
  • 17
  • 38
0

You are looking for rollmean, e.g. found in the zoo package, or in data.table:

library(data.table)
set.seed(1)
data <- data.table(X1=seq_len(20), X2=runif(20, -2, 2))
data[, rm := frollmean(data$X2, 5, align = "left")]
data
#>     X1           X2          rm
#>  1:  1 -0.937965347 -0.14369948
#>  2:  2 -0.511504401  0.36260534
#>  3:  3  0.291413453  0.82064643
#>  4:  4  1.632831160  0.89100197
#>  5:  5 -1.193272276  0.66772698
#>  6:  6  1.593558740  0.55581045
#>  7:  7  1.778701074  0.00187836
#>  8:  8  0.643191170 -0.61261645
#>  9:  9  0.516456176 -0.59163641
#> 10: 10 -1.752854918 -0.78764467
#> 11: 11 -1.176101700 -0.22120055
#> 12: 12 -1.293772990  0.01217918
#> 13: 13  0.748091387  0.44502859
#> 14: 14 -0.463585127  0.68893519
#> 15: 15  1.079365680  0.68568036
#> 16: 16 -0.009203032  0.69176340
#> 17: 17  0.870474033          NA
#> 18: 18  1.967624379          NA
#> 19: 19 -0.479859282          NA
#> 20: 20  1.109780885          NA
user12728748
  • 8,106
  • 2
  • 9
  • 14