Calculate the mean of every 13 rows in data frame

Solution 1:

Here's a solution using aggregate() and rep().

df <- data.frame(a=1:12, b=13:24 );
df;
##     a  b
## 1   1 13
## 2   2 14
## 3   3 15
## 4   4 16
## 5   5 17
## 6   6 18
## 7   7 19
## 8   8 20
## 9   9 21
## 10 10 22
## 11 11 23
## 12 12 24
n <- 5;
aggregate(df, list(rep(1:(nrow(df) %/% n + 1), each = n, len = nrow(df))), mean)[-1];
##      a    b
## 1  3.0 15.0
## 2  8.0 20.0
## 3 11.5 23.5

The important part of this solution that handles the issue of non-divisibility of nrow(df) by n is specifying the len parameter (actually the full parameter name is length.out) of rep(), which automatically caps the group vector to the appropriate length.

Solution 2:

If df is a data.table, you can use %/% to group as in

library(data.table)
setDT(df)
n <- 13 # every 13 rows

df[, mean(z), by= (seq(nrow(df)) - 1) %/% n]

if instead you want every nTH row, use %% instead of %/%

df[, mean(z), by= (seq(nrow(df)) - 1) %% n]

Solution 3:

This should work. Use n = 13 for clumping 13 rows together. If you have 27 rows, you'll get groups of size 13, 13, 1.

n.colmeans = function(df, n = 10){
    aggregate(x = df,
              by = list(gl(ceiling(nrow(df)/n), n)[1:nrow(df)]),
              FUN = mean)
}

n.colmeans(state.x77, 10)

  Group.1 Population Income Illiteracy Life Exp Murder HS Grad Frost     Area
1       1     4892.8 4690.8       1.44   70.508   9.53   53.63  75.1 116163.6
2       2     3570.5 4419.4       1.12   71.110   7.07   53.35  99.8  44406.6
3       3     3697.9 4505.5       0.93   70.855   6.64   55.25 131.7  60873.0
4       4     5663.9 4331.2       1.33   70.752   7.12   49.59 103.6  56949.5
5       5     3407.0 4232.1       1.03   71.168   6.53   53.72 112.1  75286.7