CODEWITHSUNDEEP
raggregate
Christian
Christian

Reputation: 211

Aggregate rows in a large matrix by rowname

I would like to aggregate the rows of a matrix by adding the values in rows that have the same rowname. My current approach is as follows:

> M
  a b c d
1 1 1 2 0
1 2 3 4 2
2 3 0 1 2
3 4 2 5 2
> index <- as.numeric(rownames(M))
> M <- cbind(M,index)
> Dfmat <- data.frame(M)
> Dfmat <- aggregate(. ~ index, data = Dfmat, sum)
> M <- as.matrix(Dfmat)
> rownames(M) <- M[,"index"]
> M <- subset(M, select= -index)
> M
   a b c d
 1 3 4 6 2
 2 3 0 1 2
 3 4 2 5 2

The problem of this appraoch is that i need to apply it to a number of very large matrices (up to 1.000 rows and 30.000 columns). In these cases the computation time is very high (Same problem when using ddply). Is there a more eficcient to come up with the solution? Does it help that the original input matrices are DocumentTermMatrix from the tm package? As far as I know they are stored in a sparse matrix format.

Upvotes: 9

Views: 9875

Answers (3)

Craig
Craig

Reputation: 4692

There is now an aggregate function in Matrix.utils. This can accomplish what you want with a single line of code and is about 10x faster than the combineByRow solution and 100x faster than the by solution:

N <- 10000

m <- matrix( runif(N*100), nrow=N)
rownames(m) <- sample(1:(N/2),N,replace=T)

> microbenchmark(a<-t(sapply(by(m,rownames(m),colSums),identity)),b<-combineByRow(m),c<-aggregate.Matrix(m,row.names(m)),times = 10)
Unit: milliseconds
                                                  expr        min         lq       mean     median         uq        max neval
 a <- t(sapply(by(m, rownames(m), colSums), identity)) 6000.26552 6173.70391 6660.19820 6419.07778 7093.25002 7723.61642    10
                                  b <- combineByRow(m)  634.96542  689.54724  759.87833  732.37424  866.22673  923.15491    10
                c <- aggregate.Matrix(m, row.names(m))   42.26674   44.60195   53.62292   48.59943   67.40071   70.40842    10

> identical(as.vector(a),as.vector(c))
[1] TRUE

EDIT: Frank is right, rowsum is somewhat faster than any of these solutions. You would want to consider using another one of these other functions only if you were using a Matrix, especially a sparse one, or if you were performing an aggregation besides sum.

Upvotes: 4

Michael Kuhn
Michael Kuhn

Reputation: 8982

The answer by James work as expected, but is quite slow for large matrices. Here is a version that avoids creating of new objects:

combineByRow <- function(m) {
    m <- m[ order(rownames(m)), ]

    ## keep track of previous row name
    prev <- rownames(m)[1]
    i.start <- 1
    i.end <- 1

    ## cache the rownames -- profiling shows that it takes
    ## forever to look at them
    m.rownames <- rownames(m)
    stopifnot(all(!is.na(m.rownames)))


    ## go through matrix in a loop, as we need to combine some unknown
    ## set of rows
    for (i in 2:(1+nrow(m))) {

        curr <- m.rownames[i]

        ## if we found a new row name (or are at the end of the matrix),
        ## combine all rows and mark invalid rows
        if (prev != curr || is.na(curr)) {

            if (i.start < i.end) {
                m[i.start,] <- apply(m[i.start:i.end,], 2, max)
                m.rownames[(1+i.start):i.end] <- NA
            }

            prev <- curr
            i.start <- i
        } else {
            i.end <- i
        }
    }

    m[ which(!is.na(m.rownames)),]    
}

Testing it shows that is about 10x faster than the answer using by (2 vs. 20 seconds in this example):

N <- 10000

m <- matrix( runif(N*100), nrow=N)
rownames(m) <- sample(1:(N/2),N,replace=T)

start <- proc.time()
m1 <- combineByRow(m)
print(proc.time()-start)

start <- proc.time()
m2 <- t(sapply(by(m,rownames(m),function(x) apply(x, 2, max)),identity))
print(proc.time()-start)

all(m1 == m2)

Upvotes: 2

James
James

Reputation: 66844

Here's a solution using by and colSums, but requires some fiddling due to the default output of by.

M <- matrix(1:9,3)
rownames(M) <- c(1,1,2)
t(sapply(by(M,rownames(M),colSums),identity))
  V1 V2 V3
1  3  9 15
2  3  6  9

Upvotes: 9

Related Questions