1

I have two data frames:

df1 = pd.DataFrame({ "name": ["Peter", "John", "Jack", "Mark", "Adam", "Mike", "Aaron", "Mike"], "age": [25, 34, 58, 29, 42, 39, 48, 24], }) df2 = pd.DataFrame({ "name": ["Mark", "Jack", "Adam", "Mike"], "age": [29, 58, 42, 39], "is_funny": [False, True, True, False], })

I want to remove all rows present in df2 from df1 matching in name and age. This would result in the following data frame:

>>> df3 name age 0 Peter 25 1 John 34 2 Aaron 48 3 Mike 24

df.isin() only seems to work on ordered rows and checks using index (not the case here).

Thanks in advance

Improve this question
0

4 Answers 4

Reset to default
3

One way is to use outer merge:

df2.merge(df1, on=['name','age'], how='outer', indicator=True)\ .query('_merge == "right_only"').reindex(df1.columns, axis=1)

Output:

 name age 4 Peter 25 5 John 34 6 Aaron 48 7 Mike 24
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i was going to close as a dupe with pandas merge 101 but maybe it's not a total dupe due to reindex
3

You can use isin on both columns, like:

df1[~df1.set_index(['name','age']).index.isin(df2.set_index(['name','age']).index)] 
Out[177]: name age 0 Peter 25 1 John 34 6 Aaron 48 7 Mike 24
Improve this answer

Comments

2

Outer Merge will select the data as you described.

 import pandas as pd df1 = pd.DataFrame({ "name": ["Peter", "John", "Jack", "Mark", "Adam", "Mike", "Aaron", "Mike"], "age": [25, 34, 58, 29, 42, 39, 48, 24], }) df2 = pd.DataFrame({ "name": ["Mark", "Jack", "Adam", "Mike"], "age": [29, 58, 42, 39], "is_funny": [False, True, True, False], }) df3 = df2.merge(df1, on=['name','age'], how='outer', indicator=True).query('_merge == "right_only"').reindex(df1.columns, axis=1) print(df3)
Improve this answer

Comments

1

One way:

df1[df1.merge(df2, on=['name','age'], how='left', indicator=True)['_merge']=='left_only'] # name age # 0 Peter 25 # 1 John 34 # 6 Aaron 48 # 7 Mike 24

Or:

df3 = pd.concat([df1.assign(source=1),df2.assign(source=2)]) df3 = df3.drop_duplicates(df1.columns, keep=False) df3 = df3[df3['source'] == 1][df1.columns] # name age # 0 Peter 25 # 1 John 34 # 6 Aaron 48 # 7 Mike 24
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.