How to calculate the mean for every n consecutive vectors and for every n consecutive rows

user_17265810 0
Viewed 111

How to calculate the mean for every n consecutive vectors and every for n consecutive rows from a df, creating a new data frame with the results? The idea is a non-overlapping sliding window approach.

I already can make the average for every n vectors with the code:

ds<-data.frame(do.call(cbind, lapply(seq(1, ncol(df), by = 8), function(idx) rowMeans(df[c(idx, idx + 1)]))))

But now I need a code for averaging in both ways: by column and by row

The data example

df <- data.frame(v1=1:6,V2=7:12,V3=13:18,v4=19:24,v5=25:30,v6=31:36)

Acordingly, for n = 2 I expect to get

df1 <-data.frame(v1 = c(4.5,3.5,5.5),v2 = c(16.5,18.5,20.5),v2=c(28.5,30.5,32.5))

Thank you for answering :)

r average
2 Answers

This probably isn't the prettiest way, but it will get you what you want:

# Load library:
library(tidyverse)

# Load data:
df <- data.frame(v1=1:6,V2=7:12,V3=13:18,v4=19:24,v5=25:30,v6=31:36)

# Summarize column means:
last_row <- summarise_all(df, mean)

# Add this as a new row to dataframe:
df2 <- rbind(df, last_row)

# Summarize row means:
df_mean <- df2 %>% 
  rowwise() %>% 
  mutate(Mean_Rows = sum(v1+V2+V3+v4+v5+v6)/6)

# Add this as a new column to dataframe:
df3 <- cbind(rating = c("rating1",
                 "rating2",
                 "rating3",
                 "rating4",
                 "rating5",
                 "rating6",
                 "Mean_Columns"),
             df_mean)

# Print final result:
df3

Which gives you this output:

        rating  v1   V2   V3   v4   v5   v6 Mean_Rows
1      rating1 1.0  7.0 13.0 19.0 25.0 31.0      16.0
2      rating2 2.0  8.0 14.0 20.0 26.0 32.0      17.0
3      rating3 3.0  9.0 15.0 21.0 27.0 33.0      18.0
4      rating4 4.0 10.0 16.0 22.0 28.0 34.0      19.0
5      rating5 5.0 11.0 17.0 23.0 29.0 35.0      20.0
6      rating6 6.0 12.0 18.0 24.0 30.0 36.0      21.0
7 Mean_Columns 3.5  9.5 15.5 21.5 27.5 33.5      18.5
user_166315650

This is probably easiest when the data is a matrix rather than a data frame. The function below builds an index according to row and column window width and uses that to calculate the mean.

window_mean <- function(mat, row_width, col_width) {
  
  dims <- dim(mat)
  
  stopifnot("Incompatible dimensions" = sum(dims %% c(row_width, col_width)) == 0)
  
  idx <- matrix(seq(prod(dims) / row_width * col_width),
                nrow(mat) / row_width,
                ncol(mat) / col_width) %x% matrix(1, row_width, col_width)
  
  `dim<-`(tapply(mat, idx, mean), dims / c(row_width, col_width))
}

Testing:

mat <- as.matrix(df)

window_mean(mat, 2, 2)
     [,1] [,2] [,3]
[1,]  4.5 16.5 28.5
[2,]  6.5 18.5 30.5
[3,]  8.5 20.5 32.5

window_mean(mat, 2, 3)
     [,1] [,2]
[1,]  7.5 25.5
[2,]  9.5 27.5
[3,] 11.5 29.5

window_mean(mat, 6, 2)
     [,1] [,2] [,3]
[1,]  6.5 18.5 30.5

window_mean(mat, 2, 5)
Error in window_mean(mat, 2, 5) : Incompatible dimensions
user_28352610
Related