python - Pandas 按组值过滤行

Question

这是我正在练习的数据

import pandas as pd
df = pd.read_csv("https://raw.githubusercontent.com/mwaskom/seaborn-data/master/tips.csv")

我想按分组值过滤单个行。我知道我可以执行以下操作来过滤组

df.groupby("day").filter(lambda x: x['total_bill'].mean() > 20).day.unique()

它会发现哪些天的平均账单大于 20 美元。这是有效的，因为 groupby.filter 需要一个函数来应用于每个应该返回 True 或 False 的子帧。但是，如果我想找到 total_bill 的值大于当天的 total_bill 的每一餐(行)怎么办？例如，如果某行的 total_bill 为 22 并且是在周日，那么应该保留它，因为周日的 total_bill 平均值为 21.41.

这是我的尝试:

df.groupby('day').apply(lambda x: x['total_bill'] > x['total_bill'].mean())

然而，这会产生看起来像这样的东西(前几行)

day    
Fri  90     True
     91     True
     92    False
     93    False
     94     True
Name: total_bill, dtype: bool

这与数据框的顺序不同，所以我不能只获取 bool 列并使用它来索引数据。

所以现在我执行以下操作:

grouped = (df
           .groupby('day')
           .apply(lambda x: x['total_bill'] > x['total_bill'].mean())
           .reset_index())

index_bill = (grouped
             .loc[grouped.total_bill == True, 'level_1'].values)
df.loc[index_bill]

这给了我想要的结果......必须有更简单的方法，对吧？如果有正确的方法，请告诉我。如果没有，是否至少有一种方法可以将这两个步骤合二为一？我可以执行 groupby，但我不确定如何在不将分组对象存储为变量然后引用它的情况下获取值。谢谢!

Answer 1

我认为最好的方法是对 groupby 和 transfrom 使用 bool 索引。首先，您按天分组以找到当天的均值，然后使用转换将该均值应用于每一行，接下来将该均值与当天的实际 total_billed 进行比较，然后使用该 bool 系列通过 bool 索引过滤您的数据框。

df[df.groupby('day')['total_bill'].transform('mean') < df['total_bill']]

输出:

     total_bill   tip     sex smoker   day    time  size
3         23.68  3.31    Male     No   Sun  Dinner     2
4         24.59  3.61  Female     No   Sun  Dinner     4
5         25.29  4.71    Male     No   Sun  Dinner     4
7         26.88  3.12    Male     No   Sun  Dinner     4
11        35.26  5.00  Female     No   Sun  Dinner     4
15        21.58  3.92    Male     No   Sun  Dinner     2
19        20.65  3.35    Male     No   Sat  Dinner     3
23        39.42  7.58    Male     No   Sat  Dinner     4
28        21.70  4.30    Male     No   Sat  Dinner     2
33        20.69  2.45  Female     No   Sat  Dinner     4
35        24.06  3.60    Male     No   Sat  Dinner     3
39        31.27  5.00    Male     No   Sat  Dinner     3
44        30.40  5.60    Male     No   Sun  Dinner     4
46        22.23  5.00    Male     No   Sun  Dinner     2
47        32.40  6.00    Male     No   Sun  Dinner     4
48        28.55  2.05    Male     No   Sun  Dinner     3
52        34.81  5.20  Female     No   Sun  Dinner     4
54        25.56  4.34    Male     No   Sun  Dinner     4
56        38.01  3.00    Male    Yes   Sat  Dinner     4
57        26.41  1.50  Female     No   Sat  Dinner     2
59        48.27  6.73    Male     No   Sat  Dinner     4
72        26.86  3.14  Female    Yes   Sat  Dinner     2
73        25.28  5.00  Female    Yes   Sat  Dinner     2
77        27.20  4.00    Male     No  Thur   Lunch     4
78        22.76  3.00    Male     No  Thur   Lunch     2
80        19.44  3.00    Male    Yes  Thur   Lunch     2
83        32.68  5.00    Male    Yes  Thur   Lunch     2
85        34.83  5.17  Female     No  Thur   Lunch     4
87        18.28  4.00    Male     No  Thur   Lunch     2
88        24.71  5.85    Male     No  Thur   Lunch     2
..          ...   ...     ...    ...   ...     ...   ...
180       34.65  3.68    Male    Yes   Sun  Dinner     4
181       23.33  5.65    Male    Yes   Sun  Dinner     2
182       45.35  3.50    Male    Yes   Sun  Dinner     3
183       23.17  6.50    Male    Yes   Sun  Dinner     4
184       40.55  3.00    Male    Yes   Sun  Dinner     2
187       30.46  2.00    Male    Yes   Sun  Dinner     5
189       23.10  4.00    Male    Yes   Sun  Dinner     3
191       19.81  4.19  Female    Yes  Thur   Lunch     2
192       28.44  2.56    Male    Yes  Thur   Lunch     2
197       43.11  5.00  Female    Yes  Thur   Lunch     4
200       18.71  4.00    Male    Yes  Thur   Lunch     3
204       20.53  4.00    Male    Yes  Thur   Lunch     4
206       26.59  3.41    Male    Yes   Sat  Dinner     3
207       38.73  3.00    Male    Yes   Sat  Dinner     4
208       24.27  2.03    Male    Yes   Sat  Dinner     2
210       30.06  2.00    Male    Yes   Sat  Dinner     3
211       25.89  5.16    Male    Yes   Sat  Dinner     4
212       48.33  9.00    Male     No   Sat  Dinner     4
214       28.17  6.50  Female    Yes   Sat  Dinner     3
216       28.15  3.00    Male    Yes   Sat  Dinner     5
219       30.14  3.09  Female    Yes   Sat  Dinner     4
227       20.45  3.00    Male     No   Sat  Dinner     4
229       22.12  2.88  Female    Yes   Sat  Dinner     2
230       24.01  2.00    Male    Yes   Sat  Dinner     4
237       32.83  1.17    Male    Yes   Sat  Dinner     2
238       35.83  4.67  Female     No   Sat  Dinner     3
239       29.03  5.92    Male     No   Sat  Dinner     3
240       27.18  2.00  Female    Yes   Sat  Dinner     2
241       22.67  2.00    Male    Yes   Sat  Dinner     2
243       18.78  3.00  Female     No  Thur  Dinner     2

[97 rows x 7 columns]