[R] merging with aggregating

[Pierre Kleiber]

Here's a solution that uses aggregate(), as suggested in the subject of this thread.

 > 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) )
 > tt <- as.data.frame(rbind(m1,m2))
 > aggregate(list(v1=tt$v1,v2=tt$v2),by=list(n=tt$n),sum)
     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

Cheers, Pierre


Adaikalavan Ramasamy offered the following remark on 12/06/05 04:40...
> 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 at 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 at 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
> 

-- 
-----------------------------------------------------------------
Pierre Kleiber, Ph.D       Email: pkleiber at honlab.nmfs.hawaii.edu
Fishery Biologist            Tel: 808 983-5399 / (hm)808 737-7544
NOAA Fisheries Service - Honolulu Laboratory    Fax: 808 983-2902
2570 Dole St., Honolulu, HI 96822-2396
-----------------------------------------------------------------
  "God could have told Moses about galaxies and mitochondria and
   all.  But behold... It was good enough for government work."