Summing two dataframes based on common value

Question

I have a dataframe that looks like

    day.of.week count
1           0     3
2           3     1
3           4     1
4           5     1
5           6     3

and another like

    day.of.week count
1           0    17
2           1     6
3           2     1
4           3     1
5           4     5
6           5     1
7           6    13

I want to add the values from df1 to df2 based on day.of.week. I was trying to use ddply

total=ddply(merge(total, subtotal, all.x=TRUE,all.y=TRUE),
                   .(day.of.week), summarize, count=sum(count))

which almost works, but merge combines rows that have a shared value. For instance in the example above for day.of.week=5. Rather than being merged to two records each with count one, it is instead merged to one record of count one, so instead of total count of two I get a total count of one.

       day.of.week count
  1            0     3
  2            0    17
  3            1     6
  4            2     1
  5            3     1
  6            4     1
  7            4     5
  8            5     1
  9            6     3
  10           6    13

Try `by="day.of.week"` rather than `all.x=TRUE`, `all.y=TRUE` ... — Ben Bolker, Sep 28 '11 at 16:36

score 7 · Accepted Answer · answered Sep 28 '11 at 17:35

7

There is no need to merge. You can simply do

ddply(rbind(d1, d2), .(day.of.week), summarize, sum_count = sum(count))

I have assumed that both data frames have identical column names day.of.week and count

answered Sep 28 '11 at 17:35

Ramnath

54,439
16
125
152

I was making it too hard. Thanks. – matt_k Sep 28 '11 at 22:20

score 1 · Answer 2 · answered Sep 28 '11 at 16:48

In addition to the suggestion Ben gave you about using merge, you could also do this simply using subsetting:

d1 <- read.table(textConnection("    day.of.week count
1           0     3
2           3     1
3           4     1
4           5     1
5           6     3"),sep="",header = TRUE)

d2 <- read.table(textConnection("    day.of.week count1
1           0    17
2           1     6
3           2     1
4           3     1
5           4     5
6           5     1
7           6    13"),sep = "",header = TRUE)

d2[match(d1[,1],d2[,1]),2] <- d2[match(d1[,1],d2[,1]),2] + d1[,2]
> d2
  day.of.week count1
1           0     20
2           1      6
3           2      1
4           3      2
5           4      6
6           5      2
7           6     16

This assumes no repeated day.of.week rows, since match will return only the first match.

Summing two dataframes based on common value

2 Answers2

Linked