Why is my grouped total lower than my source total?

Check for missing grouping keys. Pandas groupby excludes NA keys by default. Use dropna=False when your report must retain those rows, then reconcile both row counts and amounts. This keeps unassigned records visible; it does not discover their true country.

The behavior is specified in the official DataFrame.groupby reference. Empty strings and whitespace are not automatically the same as NA, so define the normalization rule before grouping.

A regional report that loses $50

An agency is preparing a synthetic sales report covering the US, UK, and Germany. Five order records total $300, but two records lack a usable country. Grouping only the known countries produces $250. The missing $50 is still in the source; it has disappeared from this particular summary.

Run the complete example

The code preserves country_raw and cleans a separate country key. For this fixture, a blank or whitespace-only country means unassigned. In production, confirm that interpretation with the source owner rather than applying it to every text field.

import pandas as pd

sales = pd.DataFrame({
    "order_id": [101, 102, 103, 104, 105],
    "country_raw": ["US", "UK", "DE", None, "  "],
    "amount_cents": [12000, 8000, 5000, 4000, 1000],
})
# Preserve source values; normalize a separate key.
sales["country"] = (
    sales["country_raw"].astype("string")
    .str.strip().replace("", pd.NA)
)
source_total = sales["amount_cents"].sum()

known_only = sales.groupby("country")["amount_cents"].sum()
report = sales.groupby("country", dropna=False).agg(
    order_rows=("order_id", "size"),
    amount_cents=("amount_cents", "sum"),
)
missing = sales["country"].isna()

assert source_total == 30000
assert known_only.sum() == 25000
assert report["amount_cents"].sum() == source_total
assert report["order_rows"].sum() == len(sales)
assert sales.loc[missing, "amount_cents"].sum() == 5000
assert missing.sum() == 2
print(report)

Expected result

country    order_rows    amount_cents
DE         1             5000
UK         1             8000
US         1             12000
<NA>       2             5000

The unassigned group contains $50 from two orders. Known-country revenue is $250, unassigned revenue is $50, and their sum is $300. The unassigned share is $50 / $300 = 16.67%, rounded to two decimal places. For a dashboard, display the missing-key group as “Unassigned” while retaining an explicit missingness flag in the underlying data.

What the six assertions protect

The checks establish the source total, demonstrate the default exclusion, reconcile the complete grouped total, retain all five order rows, and verify both the amount and row count with missing keys. Checking amounts alone can miss compensating errors—for example, a dropped positive amount and a dropped refund with equal magnitude.

Validation: this full example passed all six assertions in pandas 2.2.3 locally. Test your own installed version and actual column types before using the pattern in a reporting pipeline.

What changes with two grouping columns?

When grouping by country and channel, a missing value in either key can exclude a record under the default behavior. Use an explicit missingness check such as sales[["country", "channel"]].isna().any(axis=1) after adding and normalizing both fields. Reconcile at the intended country–channel grain, rather than assuming a country-only check covers the new report.

Missing keys and missing amounts need different rules

dropna=False concerns the grouping key. It does not make unknown amounts trustworthy or repair malformed numbers. This fixture deliberately has complete integer amounts. If amounts can be missing, report amount coverage and choose an explicit aggregation rule before interpreting the sum. A retained group may still have incomplete revenue.

Do not assign unclassified orders to the US or UK merely to match a target audience. Investigate the source: an incomplete export, failed lookup, or intentionally unavailable value may require different handling. Keep the unassigned amount visible while that investigation is open.

Use this explanation in a dashboard review

“The source contains $300 across five orders. Our first country summary showed $250 because two orders lacked country values. We now retain their $50 in an unassigned group and reconcile the report to the source. Country attribution remains unresolved.”

This explains a transformation issue. It does not establish a real revenue decline, an attribution fix, or a change in customer behavior.

Related practice

For malformed amount strings, use to_numeric with an invalid-value audit. For duplicate totals after a join, see pandas merge validation. Return to the pandas practice reference for other operations.