How to get merged data frame from two data frames having some same columns(R)

Question

I want to merge them and find the values of one dataframe that would like to be added to the existing values of the other based on the same columns.

For example:

df1

No	A	B	C	D
1	1	0	1	0
2	0	1	2	1
3	0	0	1	0

df2

No	A	B	E	F
1	1	0	1	1
2	0	1	2	1
3	2	1	1	0

Finally, I want the output table like this.

df

No	A	B	C	D	E	F
1	2	0	1	0	1	1
2	0	2	2	1	2	1
3	2	1	1	0	1	0

Note: I did try merge(), but in this case, it did not work. Any help/suggestion would be appreciated.

Reproducible sample data

df1 <- 
structure(list(No = 1:3, A = c(1L, 0L, 0L), B = c(0L, 1L, 0L), 
    C = c(1L, 2L, 1L), D = c(0L, 1L, 0L)), class = "data.frame", row.names = c(NA, 
-3L))

df2 <- 
structure(list(No = 1:3, A = c(1L, 0L, 2L), B = c(0L, 1L, 1L), 
    E = c(1L, 2L, 1L), F = c(1L, 1L, 0L)), class = "data.frame", row.names = c(NA, 
-3L))

Answer 1

You can also carry out this operation by left_joining these two data frames:

library(dplyr)
library(stringr)

df1 %>%
  left_join(df2, by = "No") %>%
  mutate(across(ends_with(".x"), ~ .x + get(str_replace(cur_column(), "\\.x", "\\.y")))) %>%
  rename_with(~ str_replace(., "\\.x", ""), ends_with(".x")) %>%
  select(!ends_with(".y"))

  No A B C D E F
1  1 2 0 1 0 1 1
2  2 0 2 2 1 2 1
3  3 2 1 1 0 1 0

Answer 2

You can first row-bind the two dataframes and then compute the sum of each column while 'grouping' by the No column. This can be done like so:

library(dplyr)

bind_rows(df1, df2) %>%
  group_by(No) %>%
  summarise(across(c(A, B, C, D, E, `F`), sum, na.rm = TRUE), 
            .groups = "drop")

If a particular column doesn't exist in one dataframe (i.e. columns E and F), values will be padded with NA. Adding the na.rm = TRUE argument (to be passed to sum()) means that these values will get treated like zeros.