pandas 基于列值从分组数据框中移除行

ffx8fchx  于 2022-12-25  发布在  其他
关注(0)|答案(1)|浏览(140)

我想从数据框中的每个子组中删除满足特定条件的行。子组基于两列DaysID进行分组,以下是我的数据框:

df = pd.DataFrame({'Days':[5,5,5,5,6,6],
                   'ID':['A11','A11','A11','A11','B12','B12'],
                    'Country':['DE','DE','FR','US','US','US'],
                    'Car1':['BMW','Volvo','Audi','BMW','Mercedes','BMW'],
                     'Car2':['Volvo','Mercedes','BMW','Volvo','Volvo','Volvo'],
                    'Car3':['Mercedes',nan,'Volvo',nan,nan,nan]},
                       )

    Days  ID   Country    Car1      Car2      Car3
0     5  A11      DE       BMW     Volvo  Mercedes
1     5  A11      DE     Volvo  Mercedes       nan
2     5  A11      FR      Audi       BMW     Volvo
3     5  A11      US       BMW     Volvo       nan
4     6  B12      US  Mercedes     Volvo       nan
5     6  B12      US       BMW     Volvo       nan

我希望从每个组中删除满足以下三个条件的行:

1. Car3=nan
2. Car1=Car2 (from another row within the group)
3. Car2=Car3 (from another row within the group)

我想要的预期 Dataframe :

Days   ID Country      Car1   Car2      Car3
0     5  A11      DE       BMW  Volvo  Mercedes
1     5  A11      FR      Audi    BMW     Volvo
2     6  B12      US  Mercedes  Volvo       nan
3     6  B12      US       BMW  Volvo       nan
muk1a3rh

muk1a3rh1#

您可以使用Series.isin检查lambda函数中每个组的成员资格,并在boolean indexing中通过~过滤反转掩码:

m = (df.groupby(['Days','ID'], group_keys=False)
      .apply(lambda x: x['Car1'].isin(x['Car2']) & x['Car2'].isin(x['Car3'])) & 
       df['Car3'].isna())

df = df[~m].reset_index(drop=True)
print (df)
   Days   ID Country      Car1   Car2      Car3
0     5  A11      DE       BMW  Volvo  Mercedes
1     5  A11      FR      Audi    BMW     Volvo
2     6  B12      US  Mercedes  Volvo       NaN
3     6  B12      US       BMW  Volvo       NaN

相关问题