Level 5 · Departments with at least seven people

Filter before or after?

There are two very different filters in a grouped report:

• Before grouping, you filter rows: “only completed orders”, “only 2025”. This changes what goes into each group.
• After grouping, you filter groups: “only departments with at least seven people”, “only customers who spent over $1,000”. This needs the aggregated numbers first.

In pandas, both are ordinary boolean masks. The difference is just which table you apply them to.

Aggregate, then mask

by_dept = employees.groupby("department", as_index=False).agg(
    n_employees=("employee_id", "count"),
    avg_salary=("salary", "mean"),
)
big = by_dept[by_dept["n_employees"] >= 7]

by_dept is a normal DataFrame with one row per department, so you filter it exactly like any other table. Then sort and tidy up:

big.sort_values("avg_salary", ascending=False).reset_index(drop=True)

Why the threshold matters

An average of two salaries can swing wildly with one hire. The Executive team has two people, both very senior, and including it would put it at the top of any ranking while telling you nothing about pay policy. Minimum group sizes are standard practice for averages, rates and any comparison between groups. In customer analytics you’ll often see rules like “only products with at least 30 orders” before ranking by return rate.

State the threshold in your output or notes. A ranking that silently drops three departments will confuse the first person who goes looking for one of them.

Keeping the original rows: filter and transform

Sometimes you want the employees in big departments, not the summary. groupby().filter keeps whole groups that pass a test:

employees.groupby("department").filter(lambda g: len(g) >= 7)

transform returns a value per original row, aligned with the original index, which is great for masks and for “compare each row to its group”:

dept_size = employees.groupby("department")["employee_id"].transform("count")
employees[dept_size >= 7]

employees["vs_dept_avg"] = (
    employees["salary"] - employees.groupby("department")["salary"].transform("mean")
)

That second example, each person’s salary minus their department’s average, is a classic interview task, and transform is the cleanest answer.

Common mistakes

• Filtering the raw employee rows with n_employees, which doesn’t exist until you aggregate.
• Applying the threshold to the wrong number, such as average salary instead of headcount.
• Rounding before filtering on a rounded value, which can push borderline groups across the line. Round at the end.

If you know SQL

Filtering after aggregation is SQL’s HAVING COUNT(*) >= 7. Filtering before is WHERE. In pandas both are masks; only the timing differs.