Filtering Groups (HAVING)
Python · GroupBy & SQL-Style Analytics
Theory
SQL's HAVING filters groups after aggregation, for example 'cities with total sales above 2000'. pandas has two ways. The first mirrors SQL: aggregate, then filter the result with a boolean mask - totals = df.groupby('city')['amount'].sum(); totals[totals > 2000]. With named aggregation you can filter on any summary column: summary.query('orders >= 2 and total > 2000').
The second is groupby().filter(func), which keeps or drops the original rows of whole groups: df.groupby('city').filter(lambda g: g['amount'].sum() > 2000) returns every order from cities whose total exceeds 2000, in their original order. The function receives each group as a small DataFrame and must return a single True or False. An equivalent vectorised version uses transform: df[df.groupby('city')['amount'].transform('sum') > 2000], which is usually faster on large data. WHERE-style row filters go before groupby; HAVING-style filters go after.
Why we use it
Many questions are about qualifying groups - customers with more than 3 orders, products with total sales above a target, cities with at least 2 stores. HAVING-style filtering answers them without manual loops.
When to use it
Filter the aggregated result when you want the summary table. Use groupby().filter or a transform mask when you want the original detail rows of qualifying groups. Apply row-level conditions with a normal mask before grouping.
How it works
- Rows of df
- Optional WHERE: filter rows with a mask
- groupby('city') and compute the group measure
- Group measure passes the condition? Yes: Keep the group. No: Drop the group.
- Need detail rows? Yes: groupby().filter(...) or transform mask. No: Use the filtered summary.
- Qualifying groups only
Syntax
g = df.groupby('key')['col'].sum(); g[g > x]
df.groupby('key').agg(...).query('total > x')
df.groupby('key').filter(lambda g: cond)
df[df.groupby('key')['col'].transform('sum') > x]Example
totals = df.groupby('city')['amount'].sum()
print(totals[totals > 2000])
print(df.groupby('city').filter(lambda g: len(g) >= 2))Key points
- Filter the aggregated result with a mask or query() - this is HAVING
- groupby().filter(func) returns original rows of groups where func is True
- transform('sum') > x gives the same rows as filter, usually faster
- Row filters (WHERE) go before groupby; group filters (HAVING) go after
- SQL equivalent: SELECT city, SUM(amount) FROM t GROUP BY city HAVING SUM(amount) > 2000