Merging two datasets summing a shared column

May 15, 2023

I have a situation similar to this one:

I have two datasets, this one:

from	to	frequency
a	a	2
a	b	3

and this one:

from	to	frequency
a	a	3
a	b	4

Now, I want to merge the two, keeping "from" and "to" variables the same, since they are exactly the same BUT, meanwhile, SUMMING the frequency.

This is what we should get:

from	to	frequency
a	a	5
a	b	7

>Solution :

With base R we can use aggregate + rbind

> aggregate(frequency ~ ., rbind(df1, df2), sum)
  from to frequency
1    a  a         5
2    a  b         7

Or, we can use xtabs + as.data.frame + rbind

> as.data.frame(xtabs(frequency ~ ., rbind(df1, df2)))
  from to Freq
1    a  a    5
2    a  b    7

Data

> dput(df1)
structure(list(from = c("a", "a"), to = c("a", "b"), frequency = 2:3), class = "data.frame", row.names = c(NA,
-2L))

> dput(df2)
structure(list(from = c("a", "a"), to = c("a", "b"), frequency = 3:4), class = "data.frame", row.names = c(NA,
-2L))