Conditional Aggregation

Sometimes you need to count or sum only the rows that meet a condition — “what percentage of users are active?” or “how much revenue came from each region?” In SQL, you’d use CASE WHEN inside an aggregate. In pandas, you create a boolean mask and aggregate it directly. The pattern is elegant once you see it: True counts as 1, False as 0.

When Basic Aggregates Aren’t Enough

So far, your aggregates operate on all rows. But what if you need to count only certain values? Sum only specific items? Get multiple conditional counts in one result?

In SQL, you’d use CASE WHEN inside SUM(). In pandas, you use boolean masks — which are simpler and more Pythonic.

The Pattern: Boolean Mask + .sum()

A boolean comparison returns True/False. Since Python treats True as 1 and False as 0, you can .sum() a boolean mask to count matches:

# Count employees in each department
hr_count = (techcorp_workforce["department"] == "HR").sum()
admin_count = (techcorp_workforce["department"] == "Admin").sum()
total = len(techcorp_workforce)

pd.DataFrame({
    "total": [total],
    "hr_count": [hr_count],
    "admin_count": [admin_count]
})

One result with multiple conditional counts — no groupby needed.

high_earners = (techcorp_workforce["salary"] > 100000).sum()
total = len(techcorp_workforce)
round(100.0 * high_earners / total, 2)

Computing a Ratio

Conditional Sums

You can sum values conditionally too, not just count:

Pivot-Style Cross-Tabulations

For breakdowns by two dimensions, pd.crosstab() is the pandas equivalent of a pivot table:

pd.crosstab(titanic["survived"], titanic["pclass"])

One line, and you’ve got survival counts broken down by passenger class.

Share of Active Users

Processed Ticket Rate By Type

Key Takeaways

• Boolean masks + .sum() count matching rows: (df["col"] > x).sum().
• Use [condition]["col"].sum() for conditional sums.
• pd.crosstab() creates pivot-style cross-tabulations in one line.
• For percentages: conditional count / total count * 100.
• These techniques replace SQL’s SUM(CASE WHEN ...) pattern.

What’s Next

You’ve now got all the building blocks. The final lesson pulls everything together with complex analyses that combine filtering, grouping, and conditional aggregation.