Mean of many subsets of a dataframe

Question

I have large dataframe containing many replicates. The replicates are in groups of 3. So the first 3 replicates are in column 1, 2 and 3. The second set 4, 5 and 6... and so on.

Now I create a new dataframe containing for each set of replicates the mean.

The code below works, but it is really clumpy and especially the cbind and the collumname setting is really ugly.

# first i create the new dataframe
data.mean<- data.frame(matrix(nrows=30))

# iterate over every third collumn
for(col in seq(1,length(colnames(data)), by=3)){

    # create a subset from the dataframe and compute the mean of the rows and finally cbind it to the result dataframe
    data.mean <-cbind(data.mean,apply(subset(data, select=seq(col,length.out =   3)),1,mean, na.rm = TRUE))

    # setting the new collumn name to the colname from the old dataset (name of the first replicate)
    colnames(data.mean)[ncol(data.mean)] <- colnames(data)[col]
}

I really want to improve my R coding style so i am happy about every tip!

Answer 1

Here is a proposal for a different approach that doesn't use a for loop and has some simplifications.

First, an example data frame:

dat <- data.frame(a1 = 9:11, a2 = 2:4, a3 = 3:5,
                  b1 = 4:6, b2 = 5:7, b3 = 1:3)
#   a1 a2 a3 b1 b2 b3
# 1  1  2  3  4  5  6
# 2  2  3  4  5  6  7
# 3  3  4  5  6  7  8

Now, we set the number of columns per group:

# number of columns per group (1-3, 4-6)
n <- 3

Based on this information, some necessary information can be calculated:

# number of groups
n_grp <- ncol(dat) / n
# 2

# column indices (one vector per group)
idx_grp <- split(seq(dat), rep(seq(n_grp), each = n))
# $`1`
# [1] 2 3 4
#
# $`2`
# [1] 5 6 7

In the next step, lapply is used to calculate the row means of each group. This is much more convenient with the rowMeans function.

# calculate the row means for all groups
res <- lapply(idx_grp, function(i) {
    # subset of the data frame
    tmp <- dat[i]
    # calculate row means
    rowMeans(tmp, na.rm = TRUE)
})
# $`1`
# [1] 4.666667 5.666667 6.666667
#
# $`2`
# [1] 3.333333 4.333333 5.333333

The command above returns a list. It can be transformed into a data frame:

# transform list into a data frame
dat2 <- as.data.frame(res)
#         X1       X2
# 1 4.666667 3.333333
# 2 5.666667 4.333333
# 3 6.666667 5.333333

In order to set the column names of the new data frame, we first have to extract the column names of the groups' first columns.

# extract names of first column of each group
names_frst <- names(dat)[sapply(idx_grp, "[", 1)]
# [1] "a1" "b1"

Now, these names are used for the new data frame:

# modify column names of new data frame
names(dat2) <- names_frst
#         a1       b1
# 1 4.666667 3.333333
# 2 5.666667 4.333333
# 3 6.666667 5.333333

Done.

Answer 2

I wanted to post a tidyverse-based solution to this and was prepared to insert the standard rant about how great dplyr and tidyr are for this kind of thing. But given the unique format of your particular data frame, I don't think the tidyverse approach (at least not the one I came up with) is all that great.

Nonetheless, here it is:

require(tidyverse)

# an example data frame
dat <- data.frame(1:3, 2:4, 3:5, 10:12, 11:13, 12:14)

# rename each column to its position
col_positions <- 1:dim(dat)[2]
names(dat) <- col_positions

# define the number of replicates per group
N_GROUPS <- 3

# the tidyr / dplyr functions
result <- 
    dat %>%
        mutate(row_num = row_number()) %>%
        gather(column, value, -row_num) %>%
        mutate(column = as.numeric(column)) %>%
        mutate(col_group = ((column - 1) %/% N_GROUPS) + 1) %>%
        group_by(row_num, col_group) %>%
        summarize(mean_val = mean(value)) %>%
        spread(col_group, mean_val) %>%
        ungroup() %>%
        select(-row_num)

The result data frame looks like this:

# A tibble: 3 x 2
    `1`   `2`
* <dbl> <dbl>
1     2    11
2     3    12
3     4    13

...which I think is the output you want.

Let me unpack the tidyverse a bit:

        mutate(row_num = row_number()) %>%

This adds a column to the data frame with the row numbers of the original data frame.

        gather(column, value, -row_num) %>%

This converts the data frame to "long" format, with one record per row. So if the original data frame had six columns and n rows, the new one will have 6*n rows, and three columns, one named column, one named value and our extra row_num column not included in the gather() call.

        mutate(column = as.numeric(column)) %>%

This makes the values in the column column into numbers so we can use arithmetic to define the column groups.

        mutate(col_group = ((column - 1) %/% N_GROUPS) + 1) %>%

The groups of columns are defined here using integer division.

        group_by(row_num, col_group) %>%

We group our long data frame by row_num (of the original data frame) and the column group we defined above.

        summarize(mean_val = mean(value)) %>%

This calculates the mean of each group.

        spread(col_group, mean_val)

This converts the data frame from "long" format back to the wide format.

        ungroup() %>%
        select(-row_num)

The last two functions just get rid of the row_num column to get the output in the format you want. If you don't mind having the row_num column there, you don't need them.