# Re: [R] merging with aggregating

From: Marc Schwartz <MSchwartz_at_mn.rr.com>
Date: Wed 07 Dec 2005 - 01:11:10 EST

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

There might be a somewhat easier way, but here is one approach:

# Use merge() to join A and B on 'n'
# Set all = TRUE to include non-matched rows

> C

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

```# Now get the rowSums() for the V1/V2 column pairs
# and create a new dataframe from the
# results

> AB <- data.frame(n = C\$n,

V1 = rowSums(C[, c(2, 4)], na.rm = TRUE),
V2 = rowSums(C[, c(3, 5)], na.rm = TRUE))

```

> AB

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