In Pandas, how to delete rows from a Data Frame based on another Data Frame?

Question

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?

Answer 1

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  [email protected] 1  [email protected] 2  [email protected] 3  [email protected] 4  [email protected]  EXCLUDE = pd.DataFrame({'email':['[email protected]','[email protected]']}) print (EXCLUDE)      email 0  [email protected] 1  [email protected] 

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  [email protected]       both 1  [email protected]  left_only 2  [email protected]  left_only 3  [email protected]  left_only 4  [email protected]       both  print (df.loc[df._merge == 'left_only', ['email']])      email 1  [email protected] 2  [email protected] 3  [email protected]