Four rows, one of them with no region. Count what the groupby returns and what it adds up to.
import pandas as pd
frame = pd.DataFrame({'region': ['n', None, 'n'], 'sales': [1, 2, 3]})
frame.groupby('region')['sales'].sum().to_dict()
# -> {'n': 4} the row with no region is simply gone
frame['sales'].sum() # -> 6
# The keyword that keeps it:
frame.groupby('region', dropna=False)['sales'].sum().to_dict()
# -> {'n': 4, nan: 2} now it adds up — with a key of nan
# nan is a poor dictionary key: it is not equal to itself, so looking it
# up again does not reliably find it. Give it a name instead.
- The default is dropna=True, and it is silent
groupby cannot invent a group for a row whose key is missing, so by default it leaves those rows out entirely — no warning, no count, nothing in the result to suggest they existed. The symptom is always the same: a breakdown that does not reconcile with the total, which is why reconciling is the check worth building in rather than performing by eye.
- dropna=False keeps them, under a key of nan
That fixes the arithmetic and leaves you with nan as a dictionary key — and nan is not equal to itself, so `result[float('nan')]` will not reliably find it and two nan keys need not collapse. Keeping the rows is step one; giving them a name a human and a lookup can both use is step two.
- A report that does not add up is worse than one that is obviously wrong
Every regional figure here is correct. Only the relationship between them and the total is broken, which means nothing looks suspicious until somebody sums the column — and by then the number has been in three meetings. Asserting that the parts equal the whole is one line, and it is the only thing that would have caught this on the first run.
Sum the sales column and compare it with the sum of what the groupby returned. The gap is exactly the rows whose key groupby could not place.
groupby takes dropna=False, which keeps those rows under a key of nan. That fixes the arithmetic — but nan is a bad key, so replace it with 'unknown' on the way into the dict.
Fill the missing values on a *copy* of the column rather than in the frame: frame['region'].fillna('unknown') returns a new Series, and grouping by that Series groups the original frame without touching it. Then sorted() gives the order, with 'unknown' pushed last by sorting on a key that puts it there.
import pandas as pd def sales_by_region(frame): regions = frame['region'].____('unknown') totals = frame.groupby(____)['sales'].sum() names = sorted(totals.index, key=lambda name: (name == '____', name)) return {name: ____(totals[name]) for name in names}
import pandas as pd
def sales_by_region(frame):
# fillna on the column returns a new Series — grouping by it groups the
# frame without writing 'unknown' into the caller's data.
regions = frame["region"].fillna("unknown")
# Grouping by that Series rather than by the column name, so every row
# has a key and nothing is dropped.
totals = frame.groupby(regions)["sales"].sum()
# Sorted for readability, with 'unknown' last: the tuple key puts False
# before True, so every real region sorts ahead of it.
names = sorted(totals.index, key=lambda name: (name == "unknown", name))
return {name: float(totals[name]) for name in names}
frame.groupby(series) groups the frame’s rows using that Series' values, matched up by index — so a transformed copy of a column can do the grouping while the frame itself stays exactly as the caller passed it. It is the cleanest way to group by something derived: a month extracted from a date, a band computed from a number, a filled-in key.
dropna=False also fixes the arithmetic and leaves nan as a dictionary key — which is not equal to itself, so callers cannot reliably look it up and JSON cannot represent it properly. Naming the group costs one fillna and makes the result ordinary data. The name is also what appears in the report, and 'unknown' is a better thing for a director to read than 'NaN'.
sorted(..., key=lambda name: (name == 'unknown', name)) sorts on a pair: False before True, then alphabetically within each. So every real region comes first, in order, and 'unknown' lands at the end — with no separate branch and no assumption that it exists. The same trick puts 'other' or 'total' last in any report.
Sometimes dropping the unkeyed rows is right — a breakdown of orders by customer, where a row with no customer is a data error you want to fix rather than a category to report. The rule is not 'always keep them', it is 'always know how many there are'. If the answer is meant to be zero, assert that instead: frame['region'].isna().any() is one line and turns a silent drop into a loud one. The reconciliation check matters more than either choice — a breakdown that does not sum to the total is a bug however you resolved the missing keys.
- Return the count of rows per region alongside the total, and check that the counts sum to len(frame) as well.
- Use dropna=False instead and see what a nan key does to the dict — then try looking it up.
- Add a second grouping column and work out what happens when only one of the two keys is missing.