Hi, I have a matrix that looks like this a <- c(1,1,1,1,2,2,3,3,3,3) b <- c(2,2,2,3,4,4,4,5,5,6) c <- c(1,2,3,4,5,6,7,8,9,10) M <- matrix(nr=10,nc=3) M[,1] <- a M[,2] <- b M[,3] <- c> M[,1] [,2] [,3] [1,] 1 2 1 [2,] 1 2 2 [3,] 1 2 3 [4,] 1 3 4 [5,] 2 4 5 [6,] 2 4 6 [7,] 3 4 7 [8,] 3 5 8 [9,] 3 5 9 [10,] 3 6 10 I want to reduce the matrix according to the following: If the values of the two first columns are the same in two or more rows the values in the third column of the corresponding rows should be added and only one of the rows should be keept. Hence the matrix M above should look like this 1 2 6 1 3 4 2 4 11 3 4 7 3 5 17 3 6 10 Thank you Henrik -- View this message in context: http://r.789695.n4.nabble.com/Aggregate-certain-rows-in-a-matrix-tp2528454p2528454.html Sent from the R help mailing list archive at Nabble.com.
one way is the following:
M <- cbind(c(1,1,1,1,2,2,3,3,3,3), c(2,2,2,3,4,4,4,5,5,6),
c(1,2,3,4,5,6,7,8,9,10))
ind <- do.call(paste, c(as.data.frame(M[, 1:2], sep = "\r")))
M[, 3] <- ave(M[, 3], ind, FUN = "sum")
unique(M)
I hope it helps.
Best,
Dimitris
On 9/6/2010 4:29 PM, Kennedy wrote:>
> Hi,
>
> I have a matrix that looks like this
>
> a<- c(1,1,1,1,2,2,3,3,3,3)
> b<- c(2,2,2,3,4,4,4,5,5,6)
> c<- c(1,2,3,4,5,6,7,8,9,10)
> M<- matrix(nr=10,nc=3)
> M[,1]<- a
> M[,2]<- b
> M[,3]<- c
>
>> M
> [,1] [,2] [,3]
> [1,] 1 2 1
> [2,] 1 2 2
> [3,] 1 2 3
> [4,] 1 3 4
> [5,] 2 4 5
> [6,] 2 4 6
> [7,] 3 4 7
> [8,] 3 5 8
> [9,] 3 5 9
> [10,] 3 6 10
>
> I want to reduce the matrix according to the following: If the values of
the
> two first columns are the same in two or more rows the values in the third
> column of the corresponding rows should be added and only one of the rows
> should be keept. Hence the matrix M above should look like this
>
> 1 2 6
> 1 3 4
> 2 4 11
> 3 4 7
> 3 5 17
> 3 6 10
>
>
> Thank you
>
> Henrik
>
>
>
--
Dimitris Rizopoulos
Assistant Professor
Department of Biostatistics
Erasmus University Medical Center
Address: PO Box 2040, 3000 CA Rotterdam, the Netherlands
Tel: +31/(0)10/7043478
Fax: +31/(0)10/7043014
On Mon, Sep 6, 2010 at 3:29 PM, Kennedy <henrik.aldberg at gmail.com> wrote:> I want to reduce the matrix according to the following: If the values of the > two first columns are the same in two or more rows the values in the third > column of the corresponding rows should be added and only one of the rows > should be keept. Hence the matrix M above should look like this > > ?1 2 6 > ?1 3 4 > ?2 4 11 > ?3 4 7 > ?3 5 17 > ?3 6 10Use library(plyr), convert to data frame, do ddply, convert back to matrix if you want. I'm surprised mmply doesn't do it, but I dont think it does: > Md=data.frame(M) > ddply(Md,c(1,2),function(r){sum(r[,3])}) X1 X2 V1 1 1 2 6 2 1 3 4 3 2 4 11 4 3 4 7 5 3 5 17 6 3 6 10 plyr is on CRAN and that's the third time today I've told someone to use it. Barry
On Sep 6, 2010, at 10:47 AM, Dimitris Rizopoulos wrote:> one way is the following: > > M <- cbind(c(1,1,1,1,2,2,3,3,3,3), c(2,2,2,3,4,4,4,5,5,6), > c(1,2,3,4,5,6,7,8,9,10)) > > ind <- do.call(paste, c(as.data.frame(M[, 1:2], sep = "\r"))) > M[, 3] <- ave(M[, 3], ind, FUN = "sum") > unique(M)I had been working on a similar approach with ave( ,paste(), sum) inside a datafrmae, but I liked your approach of setting up the results of the paste operation as a vector outside of M. (Skips the dataframe operation I was using.) The above solution is "destructive", so I constructed this similar alternative that returns the results without altering M: > cbind(M, ave(M[ , 3], list(M[,1], M[,2]), FUN=sum))[ !duplicated(M[,1:2]), c(1,2,4)] [,1] [,2] [,3] [1,] 1 2 6 [2,] 1 3 4 [3,] 2 4 11 [4,] 3 4 7 [5,] 3 5 17 [6,] 3 6 10> > > I hope it helps. > > Best, > Dimitris > > > On 9/6/2010 4:29 PM, Kennedy wrote: >> >> Hi, >> >> I have a matrix that looks like this >> >> a<- c(1,1,1,1,2,2,3,3,3,3) >> b<- c(2,2,2,3,4,4,4,5,5,6) >> c<- c(1,2,3,4,5,6,7,8,9,10) >> M<- matrix(nr=10,nc=3) >> M[,1]<- a >> M[,2]<- b >> M[,3]<- c >> >>> M >> [,1] [,2] [,3] >> [1,] 1 2 1 >> [2,] 1 2 2 >> [3,] 1 2 3 >> [4,] 1 3 4 >> [5,] 2 4 5 >> [6,] 2 4 6 >> [7,] 3 4 7 >> [8,] 3 5 8 >> [9,] 3 5 9 >> [10,] 3 6 10 >> >> I want to reduce the matrix according to the following: If the >> values of the >> two first columns are the same in two or more rows the values in >> the third >> column of the corresponding rows should be added and only one of >> the rows >> should be keept. Hence the matrix M above should look like this >> >> 1 2 6 >> 1 3 4 >> 2 4 11 >> 3 4 7 >> 3 5 17 >> 3 6 10 >> >> >> Thank you >> >> Henrik >> >> >> > > -- > Dimitris Rizopoulos > Assistant Professor > Department of Biostatistics > Erasmus University Medical Center > > Address: PO Box 2040, 3000 CA Rotterdam, the Netherlands > Tel: +31/(0)10/7043478 > Fax: +31/(0)10/7043014 > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.David Winsemius, MD West Hartford, CT