pandas filtering rows by group value

Question

Here is the data that I'm practicing with

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

I want to filter individual rows by a grouped valued. I know I can do the following to filter groups

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

Which finds which days have an average bill greater than $20. This works because groupby.filter takes a function to apply to each subframe that should return True or False. However, what if I want to find every meal (row) whose value for total_bill is greater than that day's total_bill. Eg if a row has total_bill of 22 and was on Sunday, then it should be kept because Sunday's total_bill average was 21.41.

This is my attempt:

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

However, this yields something that looks like this (first few rows)

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

This is not in the same order as the dataframe so I can't just take the boolean column and use it to index the data.

So now I do the following:

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]

Which gives me the desired result... there has to be an easier way, right? Please let me know if there is a proper way to do this. If not, is there at least a way I can combine the two steps into one? I can do the groupby, but am not sure how to get the values without storing the grouped object as a variable and then referencing it. Thanks!

Scott Boston · Accepted Answer

The best way I think to do is to use boolean indexing with groupby and transfrom. First you group by day to find mean for that day, then use transform to apply that mean to each row, next compare that mean to the actual total_billed on that day, then use that boolean series to filter your dataframe with boolean indexing.

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

Output:

     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]

pandas filtering rows by group value

Answers (2)

Related Questions