Pandas merge and keep only non-matching records

Question

How can I merge/join these two dataframes ONLY on "id". Produce 3 new dataframes:

1)R1 = Merged records
2)R2 = (DF1 - Merged records)
3)R3 = (DF2 - Merged records)

Using pandas in Python.

First dataframe (DF1)

|        id | name  |
|-----------|-------|
| 1         | Mark  |
| 2         | Dart  |
| 3         | Julia |
| 4         | Oolia |
| 5         | Talia |

Second dataframe (DF2)

|        id | salary |
|-----------|--------|
| 1         | 20     |
| 2         | 30     |
| 3         | 40     |
| 4         | 50     |
| 6         | 33     |
| 7         | 23     |
| 8         | 24     |
| 9         | 28     |

My solution for

R1 =pd.merge(DF1, DF2, on='id', how='inner')

I am unsure that is the easiest way to get R2 and R3

R2 should look like

|        id | name  |
|-----------|-------|
| 5         | Talia |

R3 should look like:

|        id | salary |
|-----------|--------|
| 6         | 33     |
| 7         | 23     |
| 8         | 24     |
| 9         | 28     |

Quang Hoang · Accepted Answer · 2020-07-31T02:28:06.860

11

You can turn on indicator in merge and look for the corresponding values:

total_merge = df1.merge(df2, on='id', how='outer', indicator=True)

R1 = total_merge[total_merge['_merge']=='both']
R2 = total_merge[total_merge['_merge']=='left_only']
R3 = total_merge[total_merge['_merge']=='right_only']

Update: Ben's suggestion would be something like this:

dfs = {k:v for k,v in total_merge.groupby('_merge')}

and then you can do, for examples:

dfs['both']

edited Jul 31 '20 at 02:28

answered Jul 31 '20 at 01:14

Quang Hoang

146,074
10
56
74

1

May be we can just do groupby ~ and output into dict ~ – BENY Jul 31 '20 at 01:21
1

@YOBEN_S yes, that's indeed a very clean approach. This is just to match OP's expectation, which is somewhat inferior to your suggestion. – Quang Hoang Jul 31 '20 at 01:25
I have accepted the answer. But will be interested to know whatr @YOBEN_S solution looks like – misguided Jul 31 '20 at 02:14
1

@misguided see updated answer. – Quang Hoang Jul 31 '20 at 02:28

Pandas merge and keep only non-matching records

1 Answers1