Filling missing values in pivot tables

Filling missing values in pivot tables is a critical step when reshaping data — especially in cross-tab reports where some combinations (e.g., region × month × salesperson) have no data, resulting in NaN. Pandas’ pivot_table() makes this easy with the fill_value parameter, which replaces missing entries with a specified value (usually 0) to make tables cleaner and calculations more reliable.

In 2026, this is essential for dashboards, financial reporting, cohort analysis, and any multi-dimensional view. Here’s a practical guide with real examples you can copy and adapt.

1. Basic Setup & Sample Data


import pandas as pd

data = {
    'Region': ['North', 'North', 'South', 'South', 'West', 'West'],
    'Month': ['Jan', 'Feb', 'Jan', 'Feb', 'Jan', 'Feb'],
    'Salesperson': ['Alice', 'Bob', 'Charlie', 'Dave', 'Eve', 'Frank'],
    'Sales': [100, 200, 150, 50, 75, 125],
    'Profit': [20, 50, 30, 10, 15, 25]
}

df = pd.DataFrame(data)
print(df)

2. Pivot Table with Missing Values (Default Behavior)

By default, missing combinations appear as NaN.


pivot_default = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index='Region',
    columns=['Month', 'Salesperson'],
    aggfunc='sum'
)

print(pivot_default)

Output (NaN for missing salesperson-month pairs in each region):


                       Profit               Sales             
                        sum                       sum        
Month                     Feb        Jan        Feb        Jan
Salesperson           Bob Frank Charlie Alice Dave  Eve Alice Bob Charlie Dave Eve Frank
Region                                                                                  
North                50.0   0.0     0.0   20.0  0.0  0.0   0.0 200.0     0.0   0.0   0.0   0.0
South                 0.0   0.0    10.0    0.0 50.0  0.0   0.0   0.0   150.0   0.0   0.0   0.0
West                  0.0  25.0     0.0    0.0  0.0 25.0   0.0   0.0     0.0   0.0  75.0   0.0

3. Filling Missing Values with fill_value

Set fill_value=0 to replace NaN with zero — the most common choice for counts, sales, etc.


pivot_filled = pd.pivot_table(
    df,
    values=['Sales', 'Profit'],
    index='Region',
    columns=['Month', 'Salesperson'],
    aggfunc='sum',
    fill_value=0
)

print(pivot_filled)

Output (clean, no NaN):


                       Profit               Sales             
                        sum                       sum        
Month                     Feb        Jan        Feb        Jan
Salesperson           Bob Frank Charlie Alice Dave  Eve Alice Bob Charlie Dave Eve Frank
Region                                                                                  
North                50.0   0.0     0.0   20.0  0.0  0.0 200.0   0.0     0.0   0.0   0.0   0.0
South                 0.0   0.0    10.0    0.0 50.0  0.0   0.0   0.0   150.0   0.0   0.0   0.0
West                  0.0  25.0     0.0    0.0  0.0 25.0   0.0   0.0     0.0   0.0  75.0   0.0

4. Filling with Other Values or Custom Logic

Use fill_value for any constant, or post-process with .fillna() for more control.


# Fill with -1 (useful for debugging missing data)
pivot_custom_fill = pd.pivot_table(
    df,
    values='Sales',
    index='Region',
    columns=['Month', 'Salesperson'],
    aggfunc='sum',
    fill_value=-1
)

# Or fill after creation (more flexible)
pivot = pd.pivot_table(df, values='Sales', index='Region', columns='Salesperson', aggfunc='sum')
pivot_filled_post = pivot.fillna(0)  # or pivot.fillna(pivot.mean(axis=1), axis=0) etc.

5. Modern Alternative in 2026: Polars

For large datasets, Polars is often faster and more memory-efficient with a similar pivot API.


import polars as pl

df_pl = pl.DataFrame(data)
pivot_pl = df_pl.pivot(
    index="Region",
    columns=["Month", "Salesperson"],
    values=["Sales", "Profit"],
    aggregate_function="sum"
).fill_null(0)  # fill missing with 0
print(pivot_pl)

Best Practices & Common Pitfalls

Use fill_value=0 for counts/sums — most natural for business metrics
Avoid filling with mean/median unless it makes analytical sense (can distort totals)
Check for missing combinations first with df.groupby([...]).size() to understand gaps
Add margins=True for grand totals — great for reports
Reset index after pivot if you need Region/Month as regular columns
For huge data, prefer Polars or chunked processing
Visualize: pivot_filled.plot(kind='bar', stacked=True) for instant insights

Conclusion

Filling missing values in pivot tables with fill_value turns sparse, NaN-filled cross-tabs into clean, usable summaries — essential for reporting, visualization, and calculations. In 2026, use Pandas for flexibility on small-to-medium data, and Polars for speed on large datasets. Master fill_value, aggfunc, index/columns, and post-fillna logic, and you'll build reliable, professional pivot reports in minutes.

Next time your pivot table has gaps — reach for fill_value=0 first.