python - 找到列中的值与列表 Python 中的值不匹配的行

Question

我有一个带有 ID 和一些电子邮件地址的数据框

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

我想找到与接受的电子邮件值列表不匹配的行(即不是“@abc.com”、“@ABC.COM”、“@Abc.com”)。我想要得到的是这个

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

我编写了以下代码并且它可以工作，但我必须手动检查每个 sup_email 列并重复该过程，这是低效的

#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)]

我想知道是否有更有效的方法使用 for 循环来执行此操作。到目前为止我所管理的是显示一个包含未接受电子邮件的列，但它没有显示包含未接受电子邮件的行。感谢我能得到的任何形式的帮助，谢谢。

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)

Answer 1

你可以这样做:

# 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])

输出

   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]