Why does sum return zero but mean return NaN on an empty column?

The short version

sum() on an empty column returns 0.0, because the sum of no numbers is zero by convention. mean() returns NaN, because the average of no numbers is undefined. This inconsistency matters: an empty result can silently produce a confident zero in a report, which looks like a real finding.

The behaviour

import pandas as pd

empty = pd.DataFrame({"amount": []})

empty["amount"].sum()    # 0.0
empty["amount"].mean()   # nan
empty["amount"].count()  # 0
empty["amount"].max()    # nan

sum and count return zero. mean, max and min return NaN. This split follows the mathematics, but it produces very different behaviour depending on which aggregation you happened to use.

Why this is dangerous

Imagine a filter that unexpectedly matches nothing, perhaps because a city name was misspelled or a date range was wrong.

subset = df[df["city"] == "Leedz"]     # a typo, matches nothing
subset["amount"].sum()                  # 0.0

Your report now says the total for that city is zero. That is not "no data"; it reads as "we looked, and there were no sales". A stakeholder sees a confident zero and draws a conclusion from a typo.

Had you used mean, you would have got NaN, which is visibly wrong and prompts investigation. The sum gives no such signal.

Always check emptiness explicitly

if subset.empty:
    raise ValueError("filter matched no rows")

Or, less drastically, carry the count alongside every total, so a zero from an empty set is distinguishable from a genuine zero.

summary = df.groupby("city").agg(
    total=("amount", "sum"),
    rows=("amount", "size"),
)

A row with a total of zero and a count of zero means "no data". A total of zero with a count of fifty means "fifty transactions summing to nothing", which is a completely different fact.

The same trap after grouping

groupby will not produce an empty group for a key that has no rows, so this usually bites when a filter precedes the aggregation, or when a categorical column contributes empty groups (with observed=False), where an empty group's sum shows as zero.

The reconciliation habit

The cheapest defence is to check that your filtered row count is what you expected before aggregating.

print(f"{len(subset)} rows matched")

One line, and it turns a silent zero into an obvious problem. It is the same discipline as checking a merge did not change the row count: verify the shape of the data before trusting a number computed from it.

In other tools

SQL behaves similarly: SUM of no rows gives NULL rather than zero (which is arguably safer), while COUNT gives zero. The inconsistency across tools is another reason to check for emptiness yourself, rather than relying on a convention that varies.