60

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?

Zoe
  • 27,060
  • 21
  • 118
  • 148
Vini
  • 875
  • 1
  • 7
  • 14

6 Answers6

98

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
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Hi Jezrael, can you explain what the ix is in the second solution: (df.ix[df._merge == 'left_only', ['email']]). thanks! – user8322222 Jan 16 '19 at 12:47
  • @user8322222 - it is old code, now working `loc`, it is filter by rows with boolena mask with `isin`and also by column - here filter column `email` – jezrael Jan 16 '19 at 12:49
  • Thanks Jezrael! I have a similar question to this one except I would like to keep the columns for both dataframes in the end. Would love your input if you have the time: – user8322222 Jan 16 '19 at 14:19
  • @user8322222 - so need `print (df[df._merge == 'left_only'])` ? – jezrael Jan 16 '19 at 14:20
  • You're already commenting on it lol : https://stackoverflow.com/questions/54219055/how-to-remove-rows-from-pandas-dataframe-if-the-same-row-exists-in-another-dataf – user8322222 Jan 16 '19 at 14:23
  • Elegant solution. Thank you. I performed the same task but using a for loop on the EXCLUDE.Email and delete the row in USERS whenever EXCLUDE.Email == USERS.Email. Not efficient at all compared to your answer. Thanks a lot. – Selfcontrol7 Aug 04 '21 at 13:27
7

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": ["a@g.com", "b@g.com", "c@g.com", 
                                "d@g.com", "e@g.com"],
                      "name": ["a", "s", "d", 
                               "f", "g"],
                      "nutrient_of_choice": ["pizza", "corn", "bread", 
                                             "coffee", "sausage"]})

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
3  d@g.com    f             coffee
4  e@g.com    g            sausage

EXCLUDE = pd.DataFrame({"email":["x@g.com", "d@g.com"],
                        "name": ["a", "f"]})

print(EXCLUDE)

     email name
0  x@g.com    a
1  d@g.com    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
Kapara newbie
  • 91
  • 2
  • 5
4

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
Maryam Bahrami
  • 1,056
  • 9
  • 18
2

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) ]
drGabriel
  • 548
  • 6
  • 5
1

I know this post is old but it's a fairly common question that needs an updated answer I would say.

I believe the best option is to use the loc operator

USERS[~USERS.loc[:,'EMAIL'].isin(EXCLUDE['EMAIL'])]
Jason Perez
  • 21
  • 3
1

Another way is to use query:

USERS.query('email != @EXCLUDE["email"]')

@ is needed to access the other dataframe EXCLUDE.

rachwa
  • 1,805
  • 1
  • 14
  • 17