How to average every n rows

user_17265810 0
Viewed 525

I have many vectors in the form:

structure(list(v1 = 1:6, V2 = 7:12, V3 = 13:18, v4 = 19:24, v5 = 25:30, 
v6 = 31:36), class = "data.frame", row.names = c(NA, -6L))

and want to average every n = 2 consecutive values in each column. The code:

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

gives me:

structure(list(X1 = c(3.5, 9.5), X2 = c(15.5, 21.5), X3 = c(27.5, 
33.5)), class = "data.frame", row.names = c("v1", "V2"))

but actually I would expect 6 vectors with 3 rows each as

df <-data.frame(v1 = c(1.5,3.5,5.5),v2 = c(7.5,9.5,11.5),v3=c(13.5,15.5,17.5),v4=c(19.5,21.5,23.5),v5=c(25.5,27.5,29.5),v6=c(31.5,33.5,35.5))

Thank you for any help.

seq r average
3 Answers

Possible tidyverse option (particularly if you have an uneven number of rows):

library(tidyverse)

df %>% 
  mutate(grp = 1+ (row_number()-1) %/% 2) %>% 
  group_by(grp) %>% 
  summarise(across(everything(), mean, na.rm = TRUE)) %>% 
  select(-grp)

Output

     v1    V2    V3    v4    v5    v6
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1   1.5   7.5  13.5  19.5  25.5  31.5
2   3.5   9.5  15.5  21.5  27.5  33.5
3   5.5  11.5  17.5  23.5  29.5  35.5

base R options

do.call(rbind, lapply(split(df, c(0, rep(1:(nrow(df)-1)%/%2))), colMeans, na.rm = TRUE))

Another option using aggregate:

aggregate(df, list(rep(1:(nrow(df) %/% 2 + 1), each = 2, len = nrow(df))), mean)[-1]

Data Table

dt <- df
dt <- setDT(dt)[, as.list(colMeans(.SD)), by = as.integer(c(0, rep(1:(nrow(df)-1)%/%2)))]
dt[, as.integer:=NULL]

    v1   V2   V3   v4   v5   v6
1: 1.5  7.5 13.5 19.5 25.5 31.5
2: 3.5  9.5 15.5 21.5 27.5 33.5
3: 5.5 11.5 17.5 23.5 29.5 35.5

Data

df <-
  structure(
    list(
      v1 = 1:6,
      V2 = 7:12,
      V3 = 13:18,
      v4 = 19:24,
      v5 = 25:30,
      v6 = 31:36
    ),
    class = "data.frame",
    row.names = c(NA,-6L)
  )
user_152931910

I would approach this by creating a grouping variable. Imagine that you add one to every odd number, then group by that and summarise the mean of each column.

like this

library(dplyr)

data <- structure(
  list(
    v1 = 1:6, V2 = 7:12, V3 = 13:18, v4 = 19:24, v5 = 25:30,
    v6 = 31:36), class = "data.frame", row.names = c(NA, -6L))

n <- nrow(data)/2
data %>%
  mutate(
    group_id = rep(1:n, each = 2)
  ) %>%
  group_by(group_id) %>%
  summarise(across(everything(), mean)) %>%
  ungroup() %>% 
  select(-group_id)

#> # A tibble: 3 x 6
#>      v1    V2    V3    v4    v5    v6
#>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1   1.5   7.5  13.5  19.5  25.5  31.5
#> 2   3.5   9.5  15.5  21.5  27.5  33.5
#> 3   5.5  11.5  17.5  23.5  29.5  35.5

Created on 2022-02-04 by the reprex package (v2.0.1)

user_102666750 
#build a new column as mark
df$mark=1+((1:nrow(df))-1) %/% 2
df
#  v1 V2 V3 v4 v5 v6 mark
#1  1  7 13 19 25 31    1
#2  2  8 14 20 26 32    1
#3  3  9 15 21 27 33    2
#4  4 10 16 22 28 34    2
#5  5 11 17 23 29 35    3
#6  6 12 18 24 30 36    3

#calculated according to mark column. The value here is the column index of mark.
library(foqat)
x=svri(df, mode="custom", value=7)
x
#  custom cycle  v1   V2   V3   v4   v5   v6
#1            1 1.5  7.5 13.5 19.5 25.5 31.5
#2            2 3.5  9.5 15.5 21.5 27.5 33.5
#3            3 5.5 11.5 17.5 23.5 29.5 35.5
user_91256590
Related