基于其他列值的存在删除重复行

Question

我是python新手，正在尝试删除任何带有空EOB代码的行，其中该Account Number已经存在多个EOB代码。例如，我们有这个“407”Account Number贡献了三行。我希望删除缺少EOB代码的行，但保留剩下的两行（EOB代码7730和3033）

然而，这里的复杂性（至少对我来说）是其他Account Number从未有过EOB代码。就像下面以“2300”和“6200”结尾的账户一样。在这些特定情况下，这些类型的帐户应保留在数据帧中

以下是此数据集的一小部分：

data = {'Account Number': ['407','407','407','4901','4901','4901','4901','4901','6902','6902','6902','6902','8700','6900','2300','6200','2400','2400','3200','3200','3200','3200','3200','3200','3400','2200','3300','7701','7701','7701','7701','7701','7701','3100','401','401','401','6600','6600','6600','6600'],
     'Payer':['BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS','BCBS'],
     'Remit Type':['IP Denied','IP Denied','IP Denied','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Denied','IP Denied','IP Denied','IP Denied','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Paid','IP Denied','IP Denied','IP Denied','IP Denied','IP Denied','IP Denied','IP Paid','IP Denied','IP Denied','IP Denied','IP Paid','IP Paid','IP Paid','IP Paid'],
     'EOB':['','7730','3033','5001','','9932','3035','3038','9015','5000','','9932','','','','','','','','3035','829','9932','2635','5002','','','','851','','852','9932','818','9015','','','2628','3035','5003','','3035','9932'],
     'Date':['Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10','Mar 10'],
     'Status':['INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS SUSPENDED', 'INPATIENT CLAIMS PAID','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS SUSPENDED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS DENIED','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID','INPATIENT CLAIMS PAID']}
df = pd.DataFrame(data,columns=['Account Number','Payer','Remit Type','EOB','Date','Status'])

Answer 1

首先，就个人而言，我建议不要在pandas中使用''空字符串。改用np.nan：

import numpy as np

df['EOB'] = df['EOB'].replace('', np.nan)

然后定义一个helper函数，当有超过1行时，仅将其应用于dropna，并基于Account Number将其应用于groupby项

def dropna_if_not_the_only_row(df):
    if df.shape[0] == 1:
        return df
    else:
        return df.dropna()

df.groupby('Account Number').apply(lambda x : dropna_if_not_the_only_row(x)).reset_index(drop=True)