How can I get the median of every three row-intervals for all columns?

Question

I have a huge dataset of 1600 rows and 13 columns, every three rows present data of a single person, while the columns are characteristics of this person. I want to get the median of all columns for every person (3 rows).

For example, from this:

   C1 C2 C3 C4 C5...
R1  1  2  3  4  5
R2  6  7  8  9  1
R3  2  3  4  5  6  
R4  7  6  9  8  3
R5  3  4  6  2  5
R6  9  7  4  3  2
R7  2  5  9  7  6
R8  3  5  8  3  2
R9  9  4  6  5  8

I would like to get the following:

   C1 C2 C3 C4 C5  
R1  2  3  4  5  5  
R2  7  6  6  3  3  
R3  3  5  8  5  6

. . .

please provide machine-readable example data, for example by using R's dput() — David Heckmann, Jun 30 '16 at 09:18
It is a .csv file, therefore, I am open with Python or R code. — Khaled Jimy, Jun 30 '16 at 09:25
Please provide what you have tried (code sample) and how is it failing? — Anton Krosnev, Jun 30 '16 at 09:42

akrun · Accepted Answer · 2016-06-30T09:31:15.297

In R, we create a grouping variable using %/% and then get the median of all the columns with summarise_each

 library(dplyr)
 df1 %>% 
    group_by(grp = ((row_number()-1)%/%3)+1) %>%
    summarise_each(funs(median))
 #   grp    C1    C2    C3    C4    C5
 #  <dbl> <int> <int> <int> <int> <int>
 #1     1     2     3     4     5     5
 #2     2     7     6     6     3     3
 #3     3     3     5     8     5     6

Or using data.table

 library(data.table)
 setDT(df1)[, lapply(.SD, median) , .(grp =gl(nrow(df1), 3, nrow(df1)))]
#   grp C1 C2 C3 C4 C5
#1:  1  2  3  4  5  5
#2:  2  7  6  6  3  3
#3:  3  3  5  8  5  6

Or with aggregate from base R

aggregate(.~grp, transform(df1, grp = ((1:nrow(df1) -1) %/%3) + 1), median)
#  grp C1 C2 C3 C4 C5
#1   1  2  3  4  5  5
#2   2  7  6  6  3  3
#3   3  3  5  8  5  6

score 0 · Answer 2 · answered Jun 30 '16 at 10:17

You can turn your data into an array and use apply:

DF <- read.table(text = "   C1 C2 C3 C4 C5
                 R1  1  2  3  4  5
                 R2  6  7  8  9  1
                 R3  2  3  4  5  6  
                 R4  7  6  9  8  3
                 R5  3  4  6  2  5
                 R6  9  7  4  3  2
                 R7  2  5  9  7  6
                 R8  3  5  8  3  2
                 R9  9  4  6  5  8", header = TRUE)

M <- as.matrix(DF)
dim(M) <- c(3, nrow(M)/3, ncol(M))
apply(M, c(2, 3), median)
#     [,1] [,2] [,3] [,4] [,5]
#[1,]    2    3    4    5    5
#[2,]    7    6    6    3    3
#[3,]    3    5    8    5    6

How can I get the median of every three row-intervals for all columns?

2 Answers2