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

Posted on

Question :

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

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?

Asked By: Vini

||

Answer #1:

You can use boolean indexing and condition with isin, inverting boolean Series is by ~:

import pandas as pd

USERS = pd.DataFrame({'email':['a@g.com','b@g.com','b@g.com','c@g.com','d@g.com']})
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':['a@g.com','d@g.com']})
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  b@g.com
2  b@g.com
3  c@g.com

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
Answered By: jezrael

Answer #2:

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': ['a@g.com',
                                'b@g.com',
                                'b@g.com',
                                'c@g.com',
                                'd@g.com']})

EXCLUDE = pd.DataFrame({'email':['a@g.com',
                                 'd@g.com']})

# 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
Answered By: Maryam Bahrami

Leave a Reply

Your email address will not be published. Required fields are marked *