In Pandas, how to remove rows from a data frame based on another data frame?

Question

I have 2 data frames, one of which is called USERS, and the other is EXCLUDE. Both of them have a field called "email".

Basically, I want to delete every line in USERS who have an email contained in EXCLUDE.

How can i do this?

Answer 1

You can use boolean indexing and state with isin , turning the boolean Series is ~ :

 import pandas as pd USERS = pd.DataFrame({'email':[' [e-mail] ',' [e-mail] ',' [e-mail] ',' [e-mail] ',' [e-mail] ']}) print (USERS) email 0 [e-mail] 1 [e-mail] 2 [e-mail] 3 [e-mail] 4 [e-mail] EXCLUDE = pd.DataFrame({'email':[' [e-mail] ',' [e-mail] ']}) print (EXCLUDE) email 0 [e-mail] 1 [e-mail] 

 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 [e-mail] 2 [e-mail] 3 [e-mail] 

---

Another solution with merge :

 df = pd.merge(USERS, EXCLUDE, how='outer', indicator=True) print (df) email _merge 0 [e-mail] both 1 [e-mail] left_only 2 [e-mail] left_only 3 [e-mail] left_only 4 [e-mail] both print (df.loc[df._merge == 'left_only', ['email']]) email 1 [e-mail] 2 [e-mail] 3 [e-mail]