How to add elements of columns shared between two dataframes in R

Question

I have two dataframes:

Dataframe #1

A   B   C   D   E 
2   1   0   5   7

and

Dataframe #2

C   E   F   G 
3   1   0   9

I would like to combine the two dataframes such that the 1st dataframe serves as the reference and the columns in common are added together

The solution should look like:

A   B   C   D   E 
2   1   3   5   8

Notice the elements of the shared columns (columns C and E) were added and the extra columns in Dataframe #2 (columns F and G) were ignored because they were not found in Dataframe #1. Each Dataframe has a single row.

I have tried accomplishing this with the native functions in R as well as the merge package but I've had no luck. In my application I need to combine thousands of columns many of which are shared between the two dataframes so I'm working on developing a robust solution.

Thanks!

Possible duplicate of How to merge two data frames on common columns in R with sum of others? — neilfws
– neilfws, Commented Mar 19, 2017 at 23:37
overlap = intersect(names(df1), names(df2)); df1[overlap] = df1[overlap] + df2[overlap]. Assumes the number of rows in the data frames are the same. — Gregor Thomas
– Gregor Thomas, Commented Mar 19, 2017 at 23:44

Gregor Thomas · Accepted Answer · 2022-07-15 18:01:12Z

4

This should work:

overlap = intersect(names(df1), names(df2))
df1[overlap] = df1[overlap] + df2[overlap]

It assumes the data frames have the same number of rows.

edited Jul 15, 2022 at 18:01

answered Mar 19, 2017 at 23:45

Gregor Thomas

147k22 gold badges185 silver badges320 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

neilfws · Accepted Answer · 2017-03-19 23:46:06Z

1

It may be easier to convert from wide to long format first, do the operations and then convert back. For example, using dplyr and tidyr:

library(dplyr)
library(tidyr)
df1 <- data.frame(A = 2, B = 1, C = 0, D = 5, E = 7)
df2 <- data.frame(C = 3, E = 1, F = 0, G = 9)

df1 %>%
  gather(variable, value) %>%
  left_join(gather(df2, variable, value), by = "variable") %>%
  rowwise() %>%
  mutate(sum = sum(value.x, value.y, na.rm = TRUE)) %>%
  select(variable, sum) %>%
  spread(variable, sum)

What this does: puts variables from df1 into 1 column and their values in another; does the same to df2; combines df1 and df2 using df1 as reference; sums the values for each variable; selects the variable column and the new values (sums); converts back to wide format.

Result:

# A tibble: 1 × 5
      A     B     C     D     E
* <dbl> <dbl> <dbl> <dbl> <dbl>
1     2     1     3     5     8

In general when you have the same measurement for multiple variables in many columns, it's worth considering whether the long format is a better representation of the data.

answered Mar 19, 2017 at 23:46

neilfws

34k5 gold badges58 silver badges72 bronze badges

1 Comment

Rich Scriven Over a year ago

So you voted to close the question as a duplicate then posted an answer?

Collectives™ on Stack Overflow

How to add elements of columns shared between two dataframes in R

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related