In pandas, why does sum() over an all-NaN column return 0 while mean() returns NaN, and what do skipna and min_count change?
answer
- reductions skip gaps by default
- the empty sum is zero
- a mean needs at least one value
- a minimum number of valid values
basics
~20 sReductions skip missing values by default (skipna=True), so an all-NaN column is summed as an empty sum, 0, while its mean has no values to divide and is NaN. sum(min_count=1) returns NaN instead; skipna=False makes any gap produce NaN.
solid answer
~40 spandas reductions default to `skipna=True`, so missing values are excluded before the operation runs. An all-`NaN` column therefore reduces over nothing: the user guide says NA or empty data is treated as zero when summing and as one for a product, so `sum()` is `0.0` and `prod()` is `1.0`, while `mean()`, `min()` and `max()` have no defined value and return `NaN`. `sum` and `prod` take `min_count`, default 0, the number of valid values required for a result; with `min_count=1` an all-missing column sums to `NaN`, or `pd.NA` for a nullable dtype. `skipna=False` goes the other way: one gap makes the result `NaN`. `mean()` has no `min_count` parameter.
code
python · 14 linesimport numpy as np
import pandas as pd
energy = pd.DataFrame(
{
"meter_a": [1.2, np.nan, 0.8],
"meter_b": [np.nan, np.nan, np.nan], # offline all day
}
)
print(energy.sum()) # meter_b reports 0.0
print(energy.sum(min_count=1)) # meter_b reports NaN
print(energy.mean()) # meter_b reports NaN
print(energy.sum(skipna=False)) # any gap makes the total NaNgo deeper
Recall that pandas reductions skip missing values by default, and that an all-missing column sums to 0 while its mean is NaN.
Explain the empty-sum identity, what min_count requires and what skipna=False changes, and which reductions accept min_count and which do not.
Spot totals that silently report 0.0 for offline sources in a pipeline, and set min_count from how many valid readings make a total trustworthy.
Decide whether the team's reporting code should require an explicit min_count on every sum, so missing sources never surface as zero consumption.
## Reductions skip missing values by default A **reduction** collapses a Series to one value, or a DataFrame to one value per column: `sum`, `prod`, `mean`, `median`, `min`, `max`, `std`, `count`. In pandas, these take a **`skipna`** parameter whose default is `True`: missing values are excluded and the reduction runs over whatever valid values remain. On a column with some gaps that is usually what you want; on a column with **no valid values at all** the result depends on what each reduction does with an empty input. ## Why the all-NaN sum is 0 The user guide states the rule directly: when summing, NA values or empty data are treated as **zero**; when taking a product, they are treated as **one**. Those are the identity values of addition and multiplication, the same answer Python's built-in `sum([])` gives. So: - `pd.Series([np.nan, np.nan]).sum()` returns `0.0`. - `pd.Series([np.nan, np.nan]).prod()` returns `1.0`. A mean, a minimum or a maximum of nothing has no identity value, so those return `NaN`: - `mean()` would divide by a count of zero and returns `NaN`. - `min()` and `max()` return `NaN`. - `count()` returns 0, the number of non-missing values. ## The practical danger In an IoT pipeline that totals each meter's energy per day, a meter that was **offline all day** reports a daily total of `0.0`. Downstream that looks like a real measurement of no consumption, not a missing day. The opposite reduction, `mean()`, reports `NaN` for the same meter, so a report that shows both can disagree with itself. A defensive sequence in such a pipeline looks like this: 1. Count the valid readings per meter with `count()` alongside the total, so a zero total with a zero count is visible as missing data. 2. Sum with an explicit `min_count`, chosen from how many readings make a total trustworthy. 3. Leave the resulting `NaN` in the output, or flag it, rather than filling it back to 0. ## min_count: requiring enough valid values `sum()` and `prod()` accept **`min_count`**, documented as the required number of valid values to perform the operation: if fewer than `min_count` non-NA values are present, the result is NA. The default is `0`, which is what allows the empty sum. - `s.sum(min_count=1)` returns `NaN` for an all-missing column and the normal sum otherwise. - `df.sum(min_count=1)` applies the rule **per column**. - A larger value, such as `min_count=12` for hourly readings summed per day, returns NA when too few hours were recorded to trust the total. - For a nullable dtype such as `Int64` or `Float64`, the NA result is `pd.NA` rather than `NaN`. Among the Series and DataFrame reductions, `min_count` exists only on `sum` and `prod`. `mean(min_count=1)` and `min(min_count=1)` raise `TypeError` for an unexpected keyword; a mean or minimum of nothing is already `NaN`, so there is no empty-input identity to guard against. ## skipna=False: any gap poisons the result Setting **`skipna=False`** stops pandas from excluding missing values, so the reduction follows ordinary missing-value arithmetic: one `NaN` in the column makes `sum`, `mean`, `min` and `max` return `NaN`. Use it when a partial result would be wrong rather than incomplete, for example when a total must cover every row or not be reported. Cumulative methods behave the same way: `cumsum()` skips gaps but keeps them in place in the output, while `cumsum(skipna=False)` turns every value after the first gap into `NaN`. ## Summary table | Call on an all-`NaN` float column | Result | |---|---| | `sum()` | `0.0` | | `prod()` | `1.0` | | `mean()`, `min()`, `max()` | `NaN` | | `count()` | `0` | | `sum(min_count=1)` | `NaN` | | `sum(skipna=False)` | `NaN` | | Call on `[1.0, NaN, 3.0]` | Result | |---|---| | `sum()` | `4.0` | | `sum(skipna=False)` | `NaN` | | `mean()` | `2.0` | | `sum(min_count=3)` | `NaN` (only two valid values) | ## Where this shows up beyond plain columns Aggregations after `groupby` and windows from `rolling` have their own versions of these knobs (`min_count` on grouped sums, `min_periods` on windows) and are covered in their own lessons. The library-neutral material on missing values explains why counting and aggregating over gaps needs care in any tool; the pandas-specific facts are the `skipna=True` default, the empty-sum identity and the `min_count` argument.
- What does pd.Series([np.nan], dtype='Float64').sum(min_count=1) return?`pd.NA`. The nullable `Float64` dtype uses `pd.NA` as its missing marker, so when `min_count` is not met the result is that marker rather than the float `NaN` a `float64` column would return. Without `min_count` the sum is 0.0, as for any all-missing column.
- How would you report a daily energy total only when at least 20 of 24 hourly readings are present?Use `sum(min_count=20)` on each day's readings, for example on the hourly columns of a wide table, or as the aggregation after grouping by day. Days with fewer than 20 valid values return NA instead of a total built from too few hours, and the gap stays visible downstream.
- Does cumsum() treat gaps the same way as sum()?It also skips gaps by default, but keeps them in place: `pd.Series([1.0, np.nan, 3.0]).cumsum()` is `[1.0, NaN, 4.0]`. With `skipna=False` the running total becomes `NaN` from the first gap onwards, giving `[1.0, NaN, NaN]`.
- Expecting sum() of an all-NaN column to return NaN by default.
- Believing mean() accepts min_count like sum() does.
- Thinking skipna=False makes pandas treat NaN as zero.
- Assuming min_count counts missing values rather than valid ones.
- Reading a 0.0 daily total as proof the meter recorded nothing.