Merging two Dataframes row wise with Duplicates in R

Question

I have a dataframe with duplicate rows. I want to merge with another dataframe such that each duplicate row is accounted for.

For Example,

df1:

   A    B    C
1  100  200  300
2  400  500  600
3  100  200  300

df2:

   A    B    C
1  100  200  300
2  600  700  800

After Merge: It should return

   A    B    C
1  100  200  300

But when I did:

merge(x=df1, y=df2)

I get

   A    B    C
1  100  200  300
2  100  200  300

Its returning two rows despite df2 having that row only once

Does this answer your question? [Remove duplicated rows](https://stackoverflow.com/questions/13967063/remove-duplicated-rows) — camille, Oct 01 '21 at 18:59

akrun · Answer 1 · 2021-10-01T21:20:45.827

1

An option is match after pasteing the columns together

 df1[match(do.call(paste, df2), do.call(paste, df1), nomatch = 0),]
    A   B   C
1 100 200 300

data

df1 <- structure(list(A = c(100L, 400L, 100L), B = c(200L, 500L, 200L
), C = c(300L, 600L, 300L)), class = "data.frame", row.names = c(NA, 
-3L))

df2 <- structure(list(A = c(100L, 600L), B = c(200L, 700L), C = c(300L, 
800L)), class = "data.frame", row.names = c(NA, -2L))

edited Oct 01 '21 at 21:20

answered Oct 01 '21 at 18:53

akrun

874,273
37
540
662

Thank you. But I think I would run into problem is there are two rows in df2 too. or If df1 has 3 duplicate rows, and df2 has 2 matching rows. – Meghanath L Oct 01 '21 at 20:14
@MeghanathL try the updated solution – akrun Oct 01 '21 at 21:20

score 1 · Answer 2 · answered Oct 01 '21 at 19:40

A contemporary tidyverse option.

library(dplyr)

df1 %>%
  bind_rows(df2) %>%
  filter(duplicated(.)) %>%
  distinct(across(everything()))
  
#     A   B   C
# 1 100 200 300

Data

df1 <- structure(list(A = c(100L, 400L, 100L), B = c(200L, 500L, 200L
), C = c(300L, 600L, 300L)), class = "data.frame", row.names = c(NA, 
                                                                 -3L))

df2 <- structure(list(A = c(100L, 600L), B = c(200L, 700L), C = c(300L, 
                                                                  800L)), class = "data.frame", row.names = c(NA, -2L))

ThomasIsCoding · Answer 3 · 2021-10-02T20:34:46.807

1

Maybe we can use intersect in dplyr

> intersect(df1,df2)
    A   B   C
1 100 200 300

edited Oct 02 '21 at 20:34

answered Oct 01 '21 at 21:28

ThomasIsCoding

96,636
9
24
81

Merging two Dataframes row wise with Duplicates in R

3 Answers3

data