Fixing Pandas pivot_table NaN When aggfunc Gets Empty Groups

You build a pivot_table, the call completes without an error, but a quarter of your cells show NaN. The data is there — you can see the rows in the original DataFrame — so why is the aggregation coming up empty? The answer almost always points to the same place: aggfunc is receiving an empty Series for certain group combinations, and Pandas quietly returns NaN instead of raising an exception.

What You'll Learn

• Why aggfunc silently produces NaN for missing group combinations
• How Categorical columns create phantom groups that never appear in your data
• Five concrete fixes, from fill_value to custom aggregation functions
• How to validate your index/column combinations before pivoting
• Common traps that cause this problem to appear in production but not in testing

Prerequisites

You need Python 3.8+ and Pandas 1.3+. The examples use numpy for a few helper operations. Some of the Categorical behavior changed across minor Pandas versions, so if you are on an older release, upgrade before debugging — a version mismatch can send you on a long chase for a bug that was already fixed.

The Problem: NaN Where You Expect a Number

Here is the most common symptom. You have a sales DataFrame with columns for region, product, and revenue. You call pivot_table and get a result where entire rows or columns are NaN:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'region': ['North', 'North', 'South', 'South'],
    'product': ['A', 'B', 'A', 'B'],
    'revenue': [100, 200, 150, 0]
})

# Looks fine — all combinations exist
table = df.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum'
)
print(table)

That works perfectly because every combination of region and product exists in the raw data. The trouble starts the moment a combination is missing, or when a column is typed as Categorical with levels that do not all appear.

Why aggfunc Returns NaN on Empty Groups

When Pandas builds a pivot table, it enumerates every combination of index and column values and applies aggfunc to the matching rows. If no rows match a particular combination, Pandas passes an empty Series to your function. Built-in functions like 'sum', 'mean', and 'count' all return NaN (not zero, not an error) when the input is empty — with the exception of 'count', which returns 0.

This is intentional. An empty group means "no data," and NaN signals missing data rather than a true zero. The problem is that many real-world scenarios treat a missing combination as implicitly zero (e.g., zero sales for a product that was never sold in a region). Pandas cannot know which interpretation is correct, so it picks the statistically safe default.

There is a second, subtler cause: Categorical dtypes. When a column is Categorical, Pandas uses all defined categories as axis labels — including categories that have no rows in the filtered dataset. You end up with columns or rows for combinations that literally cannot exist in your data.

Reproducing the Bug

Let's manufacture both failure modes so you can see them side by side.

Missing combination

df_sparse = pd.DataFrame({
    'region': ['North', 'North', 'South'],
    'product': ['A', 'B', 'A'],   # South/B is missing
    'revenue': [100, 200, 150]
})

table = df_sparse.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum'
)
print(table)
# product      A      B
# region
# North    100.0  200.0
# South    150.0    NaN   <-- South/B has no data

Phantom Categorical groups

cat_type = pd.CategoricalDtype(categories=['A', 'B', 'C'], ordered=False)
df_cat = df_sparse.copy()
df_cat['product'] = df_cat['product'].astype(cat_type)

table_cat = df_cat.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum'
)
print(table_cat)
# product      A      B    C
# region
# North    100.0  200.0  NaN  <-- C never existed
# South    150.0    NaN  NaN

Column C appears in the output purely because the Categorical dtype declares it as a valid level. Both cells are NaN because no row has product == 'C'.

Fix 1: Use fill_value to Replace NaN After Aggregation

The quickest fix when a missing combination genuinely means zero is the fill_value parameter. It replaces every NaN in the output with a scalar you specify.

table = df_sparse.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum',
    fill_value=0
)
print(table)
# product    A    B
# region
# North    100  200
# South    150    0

Notice the dtype changes too: once there are no NaN values, Pandas can use integer columns instead of floats. That is a useful side effect. The caveat: fill_value applies uniformly to every NaN cell — it cannot distinguish between "no data" and "truly zero." If that distinction matters to you, use a custom aggfunc instead (see Fix 4).

Fix 2: Set observed=True to Drop Phantom Categorical Groups

When the phantom-group problem is your root cause, the right fix is to tell Pandas to only show categories that are actually observed in the data. In Pandas 1.x this was a groupby parameter; in the pivot_table context you set it directly on the call.

table = df_cat.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum',
    observed=True,   # only include categories present in the data
    fill_value=0
)
print(table)
# product    A    B
# region
# North    100  200
# South    150    0

Category C vanishes entirely. If you are on Pandas 2.x, you may have seen a FutureWarning telling you that the default for observed is changing from False to True. Setting it explicitly silences the warning and makes your intent clear to anyone reading the code later.

Fix 3: Pre-filter Your Data Before Pivoting

Sometimes the cleanest solution is to ensure the input DataFrame only contains the combinations you actually care about before calling pivot_table. If you are aggregating a large dataset and then slicing the result, flip the order: filter first, then pivot.

valid_regions = ['North', 'South']
valid_products = ['A', 'B']

df_filtered = df[
    df['region'].isin(valid_regions) &
    df['product'].isin(valid_products)
].copy()

table = df_filtered.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc='sum',
    fill_value=0
)

Pre-filtering also speeds up the pivot on large datasets because Pandas does less groupby work. This is especially valuable when you have already done a merge that introduced rows with null keys — similar to the duplicate-row issues described in this guide to Pandas merge producing duplicate rows on non-unique keys.

Fix 4: Use a Custom aggfunc That Handles Empty Input

If you need granular control over what happens when a group is empty — for example, returning -1 as a sentinel instead of NaN, or returning the median only when there are at least three observations — write your own aggregation function.

def safe_sum(series):
    if series.empty:
        return 0  # or np.nan, or -1 — whatever your domain requires
    return series.sum()

table = df_sparse.pivot_table(
    values='revenue',
    index='region',
    columns='product',
    aggfunc=safe_sum
)
print(table)
# product      A      B
# region
# North    100.0  200.0
# South    150.0    0.0

You can also pass a dictionary to aggfunc when you are pivoting multiple value columns and want different behavior per column:

def safe_mean(series):
    return series.mean() if len(series) >= 2 else np.nan

table = df.pivot_table(
    values=['revenue', 'units'],
    index='region',
    columns='product',
    aggfunc={'revenue': safe_sum, 'units': safe_mean}
)

Custom functions add flexibility but slow things down on large DataFrames because Pandas cannot use its vectorized C extensions. Profile before committing to this approach in a hot path.

Fix 5: Validate Index and Column Combinations Exist

Before you pivot, it helps to know exactly which combinations are missing so you can decide how to handle them. A quick cross-tab gives you that picture:

combo_counts = df_sparse.groupby(['region', 'product']).size().unstack(fill_value=0)
print(combo_counts)
# product  A  B
# region
# North    1  1
# South    1  0   <-- South/B is missing

Any zero in this view will become a NaN in your pivot table unless you apply one of the fixes above. Running this check at the start of your analysis pipeline makes the root cause obvious and lets you document why a particular combination should be zero versus truly absent.

You can also assert that all expected combinations exist before pivoting, which is useful in automated pipelines:

expected = pd.MultiIndex.from_product(
    [['North', 'South'], ['A', 'B']],
    names=['region', 'product']
)
actual = pd.MultiIndex.from_frame(df_sparse[['region', 'product']].drop_duplicates())
missing = expected.difference(actual)
if not missing.empty:
    print(f"Missing combinations: {missing.tolist()}")

Common Pitfalls and Gotchas

dropna=True silently removes index rows

The pivot_table function has a dropna parameter that defaults to True. It drops columns whose entries are all NaN. This is usually what you want, but it can hide the phantom-group problem: the Categorical column C from the earlier example would be dropped automatically, making it look like the issue was silently resolved when it was just concealed. Set dropna=False temporarily when you are debugging to see the full extent of the missing data.

margins=True with fill_value

If you add row/column totals using margins=True, be aware that fill_value applies to the body cells but not always to the margin cells. The margin aggregation re-reads the original data, so a missing combination filled with zero in the body does not affect the margin total — the margin will still be the honest sum of the original data. This is correct behavior but often surprises people the first time they see it.

sort=False and unexpected NaN positions

By default pivot_table sorts index and column labels. If you pass sort=False, the label order follows the first appearance in the data. That alone does not cause NaN, but it makes the output harder to read during debugging. Always let the default sort run while you diagnose.

NaN from the source data itself

Not all NaN in a pivot table comes from empty groups. If the source column contains NaN values, those rows are excluded from the aggregation by default (consistent with how SQL handles nulls in aggregate functions). If you want to include them, fill the source column first with df['revenue'].fillna(0) before pivoting. This is a different problem from empty groups, so diagnose which one you have before applying a fix. Similar silent-null issues appear across many aggregation tools — for example, this same pattern causes confusion in Excel SUMIFS returning zero when a criteria range contains error values and in SUMIFS returning zero when sum range cells hold text-formatted numbers.

Multiple aggfunc values and column MultiIndex

When you pass a list of functions to aggfunc, the output has a MultiIndex on the columns. Accessing a specific cell with table['revenue'] works, but fill_value still applies across the board. If one function in the list expects a numeric result and another expects a string, the fill value type may cause a subtle dtype mismatch downstream. Keep your aggfunc list homogeneous in return type when possible.

Wrapping Up

The NaN from pivot_table is almost never a bug in Pandas — it is Pandas correctly telling you that a combination has no data. The fix depends on what that missing data means in your domain.

• Add fill_value=0 when a missing combination legitimately means zero and you want a clean numeric table.
• Add observed=True whenever any column involved in the pivot is a Categorical dtype, to suppress phantom groups.
• Pre-filter your DataFrame to the exact set of dimension values your analysis needs before calling pivot_table.
• Write a custom aggfunc when you need domain-specific handling for empty groups rather than a uniform fallback.
• Run a groupby().size().unstack() check first when you are not sure which combinations are missing — it turns an invisible problem into a visible one in two lines of code.