CODEWITHSUNDEEP
rmatrixaggregate
yliueagle
yliueagle

Reputation: 1201

Fast aggregate matrix by column and row groups

I have a big matrix mat with rownames group_label_x and colnames group_label_y. I want to aggregate mat into ave_mat, by group_label_x and group_label_y, where the value of ave_mat[i,j] is the average value of mat[ group_label_x[i], group_label_y[j] ]. This can be achieved using a double forloop, or applying twice the aggregate function (aggregate( mat, by = list(group_label_x), FUN='mean' )). But is there any approach that can achieve faster speed? (since I have many matrices to aggregate).

The following code generates a demo random matrix of approximately 1E4 rows and 2E4 cols, which I want to aggregate into a ~1E3 x 1E3 matrix: 

set.seed(1)

dim_x_raw = 1E4
dim_y_raw = 2E4

n_groups_x = 1E3
n_groups_y = 1E3

group_len_x = diff(sort(sample( 1:dim_x_raw, n_groups_x )))
group_label_x = rep( paste0('group_', 1:length(group_len_x)), group_len_x )

group_len_y = diff(sort(sample( 1:dim_y_raw, n_groups_y )))
group_label_y = rep( paste0('group_', 1:length(group_len_y)), group_len_y )

mat = matrix( runif( length(group_label_x)*length(group_label_y) ), length(group_label_x) )

######################################

My aggreagation code (which is slow):

ave_mat_x = aggregate( mat, by = list(group_label_x), FUN='mean' )
ave_mat = aggregate( t(ave_mat_x), by = list(group_label_y), FUN='mean' )

Upvotes: 0

Views: 940

Answers (1)

Roman
Roman

Reputation: 17648

You can try

library(data.table)
# add row and colnames
mat = matrix(runif( length(group_label_x)*length(group_label_y)), length(group_label_x), 
              dimnames = list(group_label_x, group_label_y))
# transform to data.table
mat_dt <- data.table(mat, keep.rownames = TRUE, stringsAsFactors = FALSE)
rm(mat) #rmove the old matrix
# melt, summarise per group and calculate mean
mat_dt <- melt(mat_dt, id.vars = "rn")
head(mat_dt)
        rn variable     value
1: group_1  group_1 0.8718050
2: group_1  group_1 0.9671970
3: group_1  group_1 0.8669163
4: group_1  group_1 0.4377153
5: group_1  group_1 0.1919378
6: group_1  group_1 0.0822944
res <- mat_dt[,.(Mean=mean(value)),.(rn, variable)]
head(res)
        rn variable      Mean
1: group_1  group_1 0.4888935
2: group_2  group_1 0.3903115
3: group_3  group_1 0.4601481
4: group_4  group_1 0.5023852
5: group_5  group_1 0.5067483
6: group_6  group_1 0.4851856
dim(res)
[1] 998001      3

Of course you can run all in one line and check the speed

system.time(
 res <- melt(data.table(mat, keep.rownames = TRUE, stringsAsFactors = FALSE), id.vars = "rn")[,.(Mean=mean(value)),.(rn, variable)]
+ )
       User      System verstrichen 
       8.15        0.01        8.19

Upvotes: 1

Related Questions