Combining row entries in two files while adding weights where the names are common

Question

I am facing a problem while trying to combining two data frames into one.

Data frame structure :1  
    FROM        TO          WEIGHT
1   abc_1   Dummy           100
2   abc_2   Dummy           20
3   xyz_1   abc_3           40

Data frame structure :1  
    FROM        TO          WEIGHT
1   abc_4   Dummy_7         100
2   abc_7   Dummy_9         2000
3   xyz_1   abc_3           400

I want to combine two data frames with the same structure and add the weights when the FROM and TO fields are the same in both entries. Otherwise I just want to combine the entries in one with the entries in the other. I want a new dataframe that gives me the output

    FROM        TO          WEIGHT
1   abc_1   Dummy           100
2   abc_2   Dummy           20
**3 xyz_1   abc_3           440**
4   abc_4   Dummy_7         100
5   abc_7   Dummy_9         2000

Thanks

Could you please explain further about the result. By "the same", do you mean `abc` with `abc`, `Dummy` with `Dummy`, etc? — Rich Scriven, Apr 02 '14 at 03:22
Sorry if I wasnt clear, but I wanted exact matches of rows with exact FROM and TO values. ie only (xyz_1,abc_3) weights should be added as they are the same rows exist in both dataframes. — srikantrao, Apr 02 '14 at 05:41

score 1 · Accepted Answer · edited Apr 02 '14 at 05:02

1

Just use aggregate by category to sum the various entries. Try using with on the 'rbind' value to get them in the same object.

  with(  rbind( df1, df2), aggregate(WEIGHT, list(FROM=FROM, TO=TO) , sum) ) 
   FROM      TO    x
1 xyz_1   abc_3  440
2 abc_1   Dummy  100
3 abc_2   Dummy   20
4 abc_4 Dummy_7  100
5 abc_7 Dummy_9 2000

edited Apr 02 '14 at 05:02

thelatemail

91,185
12
128
188

answered Apr 02 '14 at 04:56

IRTFM

258,963
21
364
487

You can also put the combined dataset straight into `aggregate`: `aggregate( WEIGHT ~ FROM + TO, data=rbind(df1,df2), sum)`, which will give you more appropriate naming through the formula interface. – thelatemail Apr 02 '14 at 05:05
Right. `aggregate.formula` does provide a nice mechanism. – IRTFM Apr 02 '14 at 05:06

score 0 · Answer 2 · edited Apr 02 '14 at 05:01

with dplyr?

require(dplyr)
df1<-read.table(header=T,text="
FROM        TO          WEIGHT
1   abc_1   Dummy           100
2   abc_2   Dummy           20
3   xyz_1   abc_3           40")

df2<-read.table(header=T,text="
FROM        TO          WEIGHT
1   abc_4   Dummy_7         100
2   abc_7   Dummy_9         2000
3   xyz_1   abc_3           400")

dfmerged<-
rbind(df1,df2) %.% group_by(FROM,TO) %.% summarise (WEIGHT=sum(WEIGHT))

> dfmerged
Source: local data frame [5 x 3]
Groups: FROM

FROM      TO WEIGHT
1 abc_1   Dummy    100
2 abc_2   Dummy     20
3 xyz_1   abc_3    440
4 abc_4 Dummy_7    100
5 abc_7 Dummy_9   2000

Combining row entries in two files while adding weights where the names are common

2 Answers2