# Create new data frame with the means of another every n columns

I have a data frame, consisting of thousands of rows and 27 columns. What I want is to create a new simplified one, where every 3 consecutive columns are substituted by their row means.

This is a simple example to clarify:

``````df = data.frame(x1=c(1,2,3), x2=c(1,1,1), x3=c(4,5,3), x4=c(0,0,0), x5=c(1,2,1),
x6=c(2,4,5), x7=c(3,3,9), x8=c(0,0,1), x9=c(1,1,0))
``````

I would like to obtain a new data frame with 3 rows and 3 columns, where the 1st column would be the mean of `x1`, `x2`, and `x3`, etc.

Does anyone know how can this be done, in base R if possible?

Thanks in advance for any help!

This can be done with pivots and group_bys, but the easiest way is to create a transformation matrix just by filling a sequence into a matrix, and then taking inner product.

``````df = data.frame(x1=c(1,2,3), x2=c(1,1,1), x3=c(4,5,3), x4=c(0,0,0), x5=c(1,2,1),
x6=c(2,4,5), x7=c(3,3,9), x8=c(0,0,1), x9=c(1,1,0))

n_col <- ncol(df)
n_row <- nrow(df)

transmat <- matrix(
rep(c(rep(1/3,3), rep(0, n_col)), n_col)[1:(n_col * n_col / 3)],
nrow = n_col)
transmat
#>            [,1]      [,2]      [,3]
#>  [1,] 0.3333333 0.0000000 0.0000000
#>  [2,] 0.3333333 0.0000000 0.0000000
#>  [3,] 0.3333333 0.0000000 0.0000000
#>  [4,] 0.0000000 0.3333333 0.0000000
#>  [5,] 0.0000000 0.3333333 0.0000000
#>  [6,] 0.0000000 0.3333333 0.0000000
#>  [7,] 0.0000000 0.0000000 0.3333333
#>  [8,] 0.0000000 0.0000000 0.3333333
#>  [9,] 0.0000000 0.0000000 0.3333333

df
#>   x1 x2 x3 x4 x5 x6 x7 x8 x9
#> 1  1  1  4  0  1  2  3  0  1
#> 2  2  1  5  0  2  4  3  0  1
#> 3  3  1  3  0  1  5  9  1  0
as.matrix(df) %*% transmat
#>          [,1] [,2]     [,3]
#> [1,] 2.000000    1 1.333333
#> [2,] 2.666667    2 1.333333
#> [3,] 2.333333    2 3.333333
``````

Created on 2021-06-21 by the reprex package (v1.0.0)

1 Like

Thanks @arthur.t, nice trick!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.