Re: [R] merging with aggregating

From: Pierre Kleiber <pkleiber_at_hawaii.rr.com>
Date: Wed 07 Dec 2005 - 06:26:21 EST

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@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
>
-- 
-----------------------------------------------------------------
Pierre Kleiber, Ph.D       Email: pkleiber@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."

______________________________________________
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 07:17:55 2005

This archive was generated by hypermail 2.1.8 : Wed 07 Dec 2005 - 09:36:59 EST