In Pandas, how to delete rows from a Data Frame based on another Data Frame?
PythonPandasMergePython Problem Overview
I have 2 Data Frames, one named USERS and another named EXCLUDE. Both of them have a field named "email".
Basically, I want to remove every row in USERS that has an email contained in EXCLUDE.
How can I do it?
Python Solutions
Solution 1 - Python
You can use boolean indexing and condition with isin, inverting boolean Series is by ~:
import pandas as pd
USERS = pd.DataFrame({'email':['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]']})
print (USERS)
email
0 a@g.com
1 b@g.com
2 b@g.com
3 c@g.com
4 d@g.com
EXCLUDE = pd.DataFrame({'email':['[email protected]','[email protected]']})
print (EXCLUDE)
email
0 a@g.com
1 d@g.com
print (USERS.email.isin(EXCLUDE.email))
0 True
1 False
2 False
3 False
4 True
Name: email, dtype: bool
print (~USERS.email.isin(EXCLUDE.email))
0 False
1 True
2 True
3 True
4 False
Name: email, dtype: bool
print (USERS[~USERS.email.isin(EXCLUDE.email)])
email
1 [email protected]
2 [email protected]
3 [email protected]
Another solution with merge:
df = pd.merge(USERS, EXCLUDE, how='outer', indicator=True)
print (df)
email _merge
0 a@g.com both
1 b@g.com left_only
2 b@g.com left_only
3 c@g.com left_only
4 d@g.com both
print (df.loc[df._merge == 'left_only', ['email']])
email
1 b@g.com
2 b@g.com
3 c@g.com
Solution 2 - Python
Just to expand jezrael's answer, the same approach could be used in order to filter rows based on multiple columns.
USERS = pd.DataFrame({"email": ["[email protected]", "[email protected]", "[email protected]",
"[email protected]", "[email protected]"],
"name": ["a", "s", "d",
"f", "g"],
"nutrient_of_choice": ["pizza", "corn", "bread",
"coffee", "sausage"]})
print(USERS)
email name nutrient_of_choice
0 [email protected] a pizza
1 [email protected] s corn
2 [email protected] d bread
3 [email protected] f coffee
4 [email protected] g sausage
EXCLUDE = pd.DataFrame({"email":["[email protected]", "[email protected]"],
"name": ["a", "f"]})
print(EXCLUDE)
email name
0 x@g.com a
1 [email protected] f
Now, suppose we would like to filter only rows with matching names and emails:
USERS = pd.merge(USERS, EXCLUDE, on=["email", "name"], how="outer", indicator=True)
print(USERS)
email name nutrient_of_choice _merge
0 a@g.com a pizza left_only
1 b@g.com s corn left_only
2 c@g.com d bread left_only
3 d@g.com f coffee both
4 e@g.com g sausage left_only
5 x@g.com a NaN right_only
USERS = USERS.loc[USERS["_merge"] == "left_only"].drop("_merge", axis=1)
print(USERS)
email name nutrient_of_choice
0 a@g.com a pizza
1 b@g.com s corn
2 c@g.com d bread
4 e@g.com g sausage
Solution 3 - Python
You can also use inner join, take the indices or rows in USERS, that has email EXCLUDE, and then drop the them from the USERS. Following I use the @jezrael example to show this:
import pandas as pd
USERS = pd.DataFrame({'email': ['[email protected]',
'[email protected]',
'[email protected]',
'[email protected]',
'[email protected]']})
EXCLUDE = pd.DataFrame({'email':['[email protected]',
'[email protected]']})
# rows in USERS and EXCLUDE with the same email
duplicates = pd.merge(USERS, EXCLUDE, how='inner',
left_on=['email'], right_on=['email'],
left_index=True)
# drop the indices from USERS
USERS = USERS.drop(duplicates.index)
This return:
USERS
email
2 b@g.com
3 c@g.com
4 d@g.com
Solution 4 - Python
My solution is just to find the elements is common, extract the shared key and then use that key to remove them from the original data:
emails2remove = pd.merge(USERS, EXCLUDE, how='inner', on=['email'])['email']
USERS = USERS[ ~USERS['email'].isin(emails2remove) ]