Re: [R] merging with aggregating

From: Adaikalavan Ramasamy <ramasamy_at_cancer.org.uk>
Date: Wed 07 Dec 2005 - 01:40:09 EST

m1 <- cbind( n=c(1,2,3,4,6,7,8,9,10,13), v1=c(12,10,3,8,7,12,1,18,1,2),

             v2=c(0,8,8,4,3,0,0,0,0,0) )

m2 <- cbind( n=c(1,2,3,4,5,6,8,10,11,12), v1=c(0,0,1,12,2,2,2,4,7,0),

             v2=c(2,3,9,8,9,9,0,1,1,1) )

m.all <- merge(m1, m2, by="n", all=T)

	    n v1.x v2.x v1.y v2.y
	1   1   12    0    0    2
	2   2   10    8    0    3
	3   3    3    8    1    9
	4   4    8    4   12    8
	5   5   NA   NA    2    9
	6   6    7    3    2    9
	7   7   12    0   NA   NA
	8   8    1    0    2    0
	9   9   18    0   NA   NA
	10 10    1    0    4    1
	11 11   NA   NA    7    1
	12 12   NA   NA    0    1
	13 13    2    0   NA   NA

Then depending on how many such columns there are, you have a number of ways of aggregating this dataset. One such way is

cbind( n=m.all[ , "n"], 
      v1=rowSums( m.all[ , grep( "^v1", colnames(m.all) )  ], na.rm=T ),
      v2=rowSums( m.all[ , grep( "^v2", colnames(m.all) )], na.rm=T ) )

	    n v1 v2
	1   1 12  2
	2   2 10 11
	3   3  4 17
	4   4 20 12
	5   5  2  9
	6   6  9 12
	7   7 12  0
	8   8  3  0
	9   9 18  0
	10 10  5  1
	11 11  7  1
	12 12  0  1
	13 13  2  0

Regards, Adai

On Tue, 2005-12-06 at 14:22 +0100, Dubravko Dolic wrote:
> Dear List,
>
> I have two data.frame of the following form:
>
> A:
>
> n V1 V2
> 1 12 0
> 2 10 8
> 3 3 8
> 4 8 4
> 6 7 3
> 7 12 0
> 8 1 0
> 9 18 0
> 10 1 0
> 13 2 0
>
> B:
>
> n V1 V2
> 1 0 2
> 2 0 3
> 3 1 9
> 4 12 8
> 5 2 9
> 6 2 9
> 8 2 0
> 10 4 1
> 11 7 1
> 12 0 1
>
>
> Now I want to merge those frame to one data.frame with summing up the
> columns V1 and V2 but not the column n. So the result in this example
> would be:
>
> AB:
>
> n V1 V2
> 1 12 2
> 2 10 11
> 3 4 17
> 4 20 12
> 5 2 9
> 6 9 12
> 7 12 0
> 8 3 0
> 9 18 0
> 10 5 1
> 11 7 1
> 12 0 1
> 13 2 0
>
>
> So Columns V1 and V2 are the sum of A und B while n has its old value.
> Notice that there are different rows in n of A and B.
>
> I don't have a clue how to start here. Any hint is welcome.
>
> Thanks
>
> Dubravko Dolic
> Munich
> Germany
>
> ______________________________________________
> R-help@stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
>



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html Received on Wed Dec 07 02:37:32 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:41:29 EST