根据 pandas 中的多个条件过滤分组的行 [英] Filter grouped rows based on multiple conditions in Pandas

问题描述

给出如下数据框：

  city district        date  price
0   bj       cy  2019-03-01    NaN
1   bj       cy  2019-04-01    6.0
2   sh       hp  2019-03-01    4.0
3   sh       hp  2019-04-01    3.0
4   bj       hd  2019-03-01    7.0
5   bj       hd  2019-04-01    NaN

我需要过滤分组行满足以下两个条件时，城市和区的值：日期是 2019-04-01 ，价格是 NaN 。

I need to filter grouped rows of city and district when both of the following conditions were met: date is 2019-04-01 and price is NaN.

我已经用以下代码进行了测试：

I have tested with the following code:

df['date'] = pd.to_datetime(df['date']).dt.date.astype(str)
df.groupby(['city','district']).filter(lambda x: (x['price'].isnull() & x['date'].isin(['2019-04-01'])).any())

退出：

  city district        date  price
4   bj       hd  2019-03-01    7.0
5   bj       hd  2019-04-01    NaN

另一个测试：

df.groupby(['city','district']).filter(lambda x: (x['price'].isnull() & x['date']).any())

出：

  city district        date  price
0   bj       cy  2019-03-01    NaN
1   bj       cy  2019-04-01    6.0
4   bj       hd  2019-03-01    7.0
5   bj       hd  2019-04-01    NaN

但是我需要如下。如何修改上面的代码？

But I need is as below. How could I modify the code above? Thanks a lot.

  city district      date  price
0   bj       cy  2019/3/1    NaN
1   bj       cy  2019/4/1    6.0
2   sh       hp  2019/3/1    4.0
3   sh       hp  2019/4/1    3.0

推荐答案

我认为您需要反转掩码-这里& 到 | ， isnull 到 notna ， eq 到 ne 和任何到 all ：

I think you need invert mask - here & to |, isnull to notna, eq to ne and any to all:

df['date'] = pd.to_datetime(df['date'])

f = lambda x: (x['price'].notna() | x['date'].ne('2019-04-01')).all()
df = df.groupby(['city','district']).filter(f)
print (df)
  city district       date  price
0   bj       cy 2019-03-01    NaN
1   bj       cy 2019-04-01    6.0
2   sh       hp 2019-03-01    4.0
3   sh       hp 2019-04-01    3.0

或者可以将不用于将布尔值 True 反转为 False 和 False 到 True ：

Or is possible use not for invert boolean True to False and False to True:

f = lambda x: not (x['price'].isnull() & x['date'].eq('2019-04-01')).any()
df = df.groupby(['city','district']).filter(f)

这篇关于根据 pandas 中的多个条件过滤分组的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！