Geek Answers Handbook

Summing two frames of data based on total value

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 the other -

  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 values ​​from df1 to df2 based on day.of.week. I tried 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 merging concatenates strings that have a common meaning. For example, in the above example for day.of.week = 5. Instead of combining with two records, each of which has one count, it instead merges with one count record one, so instead of the total number of two, I get the total units.

  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
+4
source share
2 answers

No need to merge. You can just do

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

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

+7
source

In addition to the suggestion Ben gave you in using merge , you can also do this simply by using a subset:

 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 does not imply repeating the lines of day.of.week , since match returns only the first match.

+1
source

More articles:


All Articles