Implement SQL HAVING Logic Using groupby().filter() in Pandas

Question

Given an `orders` DataFrame, keep only the rows belonging to customers whose average order value across all their purchases exceeds $500.

Accepted Answer

Optimal Solution:
import pandas as pd

# .filter() discards entire groups where the boolean lambda evaluates to False
filtered_df = df.groupby('customer_id').filter(
    lambda group: group['amount'].mean() > 500
)

Technical Explanation:
Unlike `.agg()`, which collapses groups into summary rows, `groupby().filter()` evaluates a predicate on each group and returns the original raw rows of passing groups.

order_id	customer_id	amount
1	101	800
2	101	400
3	102	100
4	102	200

order_id	customer_id	amount
1	101	800
2	101	400