Lessons · Python · rows with no key vanish from a group-by

Rows with no key vanish from a group-by

Grouping by a field drops rows where that field is missing, so group totals add up to less than the column total, and nothing says why.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Sales by region that miss the orders with no region, tickets by team that miss the unassigned ones: the report is wrong in the direction nobody notices.

How to think about it

Label missing keys before grouping ('unknown'), or group with dropna=False in pandas, and always compare the sum of the groups to the total of the column.

Worked example

rows = [("north", 10), ("south", 20), (None, 5)]

One row has no region.

totals = {}

Group totals.

for region, amount in rows:

Walk the rows.

    totals[region or "unknown"] = totals.get(region or "unknown", 0) + amount

Label the missing key instead of losing the row.

print(totals)

{'north': 10, 'south': 20, 'unknown': 5}.

print(sum(totals.values()) == sum(a for _, a in rows))

True: the groups account for every row.

Your turn

Group in pandas without dropping missing keys.

df.groupby("region", dropna=)["amount"].sum()

The trap

pandas drops missing keys by default; SQL's GROUP BY keeps them as one NULL group. The same report in two tools can differ by exactly the unlabelled rows.

Practise rows with no key vanish from a group-by on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.