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

camille
– camille

2021-10-01 18:59:52 +00:00
Commented Oct 1, 2021 at 18:59 — camille
– camille, Commented Oct 1, 2021 at 18:59
unique(merge(df1, df2))

Ronak Shah
– Ronak Shah

2021-10-02 12:53:46 +00:00
Commented Oct 2, 2021 at 12:53 — Ronak Shah
– Ronak Shah, Commented Oct 2, 2021 at 12:53

rjen · Accepted Answer · 2021-10-01 19:40:33Z

1

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))

answered Oct 1, 2021 at 19:40

rjen

1,9941 gold badge9 silver badges21 bronze badges

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

Comments

akrun · Accepted Answer · 2021-10-01 21:20:45Z

0

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 1, 2021 at 21:20

answered Oct 1, 2021 at 18:53

akrun

891k38 gold badges590 silver badges700 bronze badges

2 Comments

Meghanath L Over a year ago

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.

akrun Over a year ago

@MeghanathL try the updated solution

ThomasIsCoding · Accepted Answer · 2021-10-02 20:34:46Z

0

Maybe we can use intersect in dplyr

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

edited Oct 2, 2021 at 20:34

answered Oct 1, 2021 at 21:28

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Collectives™ on Stack Overflow

Merging two Dataframes row wise with Duplicates in R

3 Answers 3

Comments

data

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

data

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related