Locate rows where values in columns do not match values from a list Python

user_11918314 0
Viewed 753

I have a dataframe with ID and some email addresses

personid  sup1_email      sup2_email         sup3_email        sup4_email
1         evan.o@abc.com  jon.k@abc.com      kelm.q@abc.com    john.d@abc.com 
5         evan.o@abc.com  polly.u@abc.com    jim.e@ABC.COM     nan
11        jim.y@abc.com   manfred.a@abc.com  greg.s@Abc.com    adele.a@abc.com 
52        jim.y@abc.com   manfred.a@abc.com  greg.s@Abc.com    adele.a@abc.com
65        evan.o@abc.com  lenny.t@yahoo.com  john.s@abc.com    sally.j@ABC.com
89        dom.q@ABC.com   laurie.g@Abc.com   topher.u@abc.com  ross.k@qqpower.com

I would like to locate the rows which do not match the list of accepted email values (ie NOT '@abc.com', '@ABC.COM', '@Abc.com'). What i'd like to get is this

personid  sup1_email      sup2_email         sup3_email        sup4_email
65        evan.o@abc.com  lenny.t@yahoo.com  john.s@abc.com    sally.j@ABC.com
89        dom.q@ABC.com   laurie.g@Abc.com   topher.u@abc.com  ross.k@qqpower.com

I've written the following code and it works but I have to manually check for each sup_email column and repeat the process, which is inefficient

#list down all the variations of accepted email domains
email_adds = ['@abc.com','@ABC.COM','@Abc.com']
#combine the variations of email addresses in the list
accepted_emails = '|'.join(email_adds)


not_accepted = df.loc[~df['sup1_email'].str.contains(accepted_emails, na=False)]

I was wondering if there was a more efficient way to do this using a for loop. What i've managed so far is to show a column which contains a non-accepted email, but it is not showing the rows that contain non-accepted emails. Appreciate any form of help I can get, thank you.

sup_emails = df[['sup1_email','sup2_email', 'sup3_email', 'sup4_email']]

#for each sup column, check if the accepted email addresses are not in it
for col in sup_emails:
    if any(x not in col for x in accepted_emails):
        print(col)
pandas dataframe list-comprehension for-loop python
3 Answers

One idea:

#list down all the variations of accepted email domains
email_adds = ['@abc.com','@ABC.COM','@Abc.com']
#combine the variations of email addresses in the list
accepted_emails = '|'.join(email_adds)

#columns for test
c = ['sup1_email','sup2_email', 'sup3_email', 'sup4_email']
#reshape and test all values, if `nan` pass `True`
m = df[c].stack(dropna=False).str.contains(accepted_emails, na=True).unstack().all(axis=1)

df = df[~m]
print (df)
   personid      sup1_email         sup2_email        sup3_email  \
4        65  evan.o@abc.com  lenny.t@yahoo.com    john.s@abc.com   
5        89   dom.q@ABC.com   laurie.g@Abc.com  topher.u@abc.com   

           sup4_email  
4     sally.j@ABC.com  
5  ross.k@qqpower.com

Your solution with generator and any:

c = ['sup1_email','sup2_email', 'sup3_email', 'sup4_email']

f = lambda y: any(x in y for x in email_adds) if isinstance(y, str) else True
df = df[~df[c].applymap(f).all(axis=1)]
print (df)
   personid      sup1_email         sup2_email        sup3_email  \
4        65  evan.o@abc.com  lenny.t@yahoo.com    john.s@abc.com   
5        89   dom.q@ABC.com   laurie.g@Abc.com  topher.u@abc.com   

           sup4_email  
4     sally.j@ABC.com  
5  ross.k@qqpower.com
user_29010020

You could do:

# list down all the variations of accepted email domains
email_adds = ['@abc.com','@ABC.COM','@Abc.com']

# combine the variations of email addresses in the list
accepted_emails = '|'.join(email_adds)

# create a single email column
melted = df.melt('personid')

# check the matching emails
mask = melted['value'].str.contains(accepted_emails, na=True)

# filter out the ones that do not match
mask = df['personid'].isin(melted.loc[~mask, 'personid'])

print(df[mask])

Output

   personid      sup1_email  ...        sup3_email          sup4_email
4        65  evan.o@abc.com  ...    john.s@abc.com     sally.j@ABC.com
5        89   dom.q@ABC.com  ...  topher.u@abc.com  ross.k@qqpower.com

[2 rows x 5 columns]
user_40015920

Lets try check to see that the characters after @ are either ABC or abc or Abc in all the columns. Of course we can have to temporarily filter out PersonID.After the check, reverse the outcome using ~ and mask them

df[-(df.iloc[:,1:].apply(lambda x: x.str.contains('(\@(?=ABC|abc|Abc))').all(), axis=1))]



 personid      sup1_email         sup2_email        sup3_email  \
4      65.0  evan.o@abc.com  lenny.t@yahoo.com    john.s@abc.com   
5      89.0   dom.q@ABC.com   laurie.g@Abc.com  topher.u@abc.com   

           sup4_email  
4     sally.j@ABC.com  
5  ross.k@qqpower.com
user_89869750
Related