Python Doc & Data Automation

Fix pandas pivot_table Missing Categories

September's summary has four region rows instead of five, and the July column is missing from the rolling twelve-month view, because LATAM had no sales in September and nothing shipped in July. Downstream, the Excel chart linked to the fifth row now plots the total line, a VLOOKUP returns #N/A, and the month-over-month comparison silently shifts every column left by one. Some runs also print:

FutureWarning: The default of observed=False is deprecated and will be changed to True in a future version of pandas. Pass observed=False to retain current behavior or observed=True to adopt the future default and silence this warning.

Root Cause

pivot_table builds its row and column labels from the values that actually occur in the data. A region string that never appears in September's rows cannot become a row label, because pandas has no way to know it exists. Categorical columns do carry the full list of possible values, but whether empty categories are kept depends on observed: with observed=False every category appears, with observed=True only those present. The default is changing from False to True, which is what the warning announces — code that relied on the old default loses rows after an upgrade without any other change. A third path removes columns even when categories are kept: dropna=True (the default) drops columns whose entries are all missing, which is exactly what an empty month is before fill_value applies.

Why a row or column disappears The root asks what dtype the dimension column has. Plain string or object columns can only produce labels that occur in the data, so the fix is to reindex or convert to categorical. Categorical columns with observed True drop unused categories, fixed by passing observed False. Categorical columns with observed False can still lose all-empty columns when dropna is True, fixed by dropna False together with fill_value. What dtype is the dimension? check frame[col].dtype object / string Labels from data only absent value = no row category, observed=True Unused categories dropped new pandas default category, observed=False All-NaN column dropped dropna=True default Reindex or categorical full expected list observed=False explicitly dropna=False plus fill_value

Minimal Diagnostic

Compare the labels you expect with the labels the pivot produced, and print the dtype and settings that decide the difference.

# pip install "pandas>=2.2"
import pandas as pd

EXPECTED_REGIONS = ["EMEA", "North America", "LATAM", "APAC", "Unassigned"]
EXPECTED_MONTHS = [str(p) for p in pd.period_range("2025-10", "2026-09", freq="M")]

sales = pd.DataFrame({
    "region": ["EMEA", "APAC", "North America", "EMEA", "Unassigned"],
    "month": ["2026-08", "2026-09", "2026-09", "2026-06", "2026-09"],
    "revenue": [9600.0, 6100.0, 12800.0, 4100.0, 300.0],
})

def missing_labels(frame: pd.DataFrame, **pivot_kwargs) -> None:
    table = pd.pivot_table(frame, index="region", columns="month", values="revenue",
                           aggfunc="sum", **pivot_kwargs)
    print(f"region dtype={frame['region'].dtype}, month dtype={frame['month'].dtype}, "
          f"kwargs={pivot_kwargs}")
    print("  missing rows:   ", [r for r in EXPECTED_REGIONS if r not in table.index])
    print("  missing columns:", [m for m in EXPECTED_MONTHS if m not in table.columns])

missing_labels(sales, fill_value=0)

region dtype=object, month dtype=object, kwargs={'fill_value': 0}
  missing rows:    ['LATAM']
  missing columns: ['2025-10', '2025-11', '2025-12', '2026-01', '2026-02', '2026-03', '2026-04', '2026-05', '2026-07']

Object dtype and missing labels: the data never mentioned LATAM or those months, so the pivot could not produce them.

Fix: Declare the Full Category Sets

Convert each dimension to a categorical with the complete, ordered list of values the report must always show, and pass observed=False and dropna=False explicitly. Changed lines carry comments.

# pip install "pandas>=2.2"
import pandas as pd

EXPECTED_REGIONS = ["EMEA", "North America", "LATAM", "APAC", "Unassigned"]
EXPECTED_MONTHS = [str(p) for p in pd.period_range("2025-10", "2026-09", freq="M")]

def stable_pivot(frame: pd.DataFrame) -> pd.DataFrame:
    data = frame.copy()
    unknown = set(data["region"].dropna()) - set(EXPECTED_REGIONS)
    if unknown:
        raise ValueError(f"regions not in the expected list: {sorted(unknown)}")  # changed: fail loudly
    data["region"] = pd.Categorical(data["region"], categories=EXPECTED_REGIONS)  # changed
    data["month"] = pd.Categorical(data["month"], categories=EXPECTED_MONTHS)     # changed
    return pd.pivot_table(
        data,
        index="region",
        columns="month",
        values="revenue",
        aggfunc="sum",
        fill_value=0,
        observed=False,          # changed: keep categories with no rows, on every pandas version
        dropna=False,            # changed: keep all-empty month columns
        sort=False,              # changed: declared order, not alphabetical
    )

if __name__ == "__main__":
    table = stable_pivot(sales)                      # the frame from the diagnostic
    print(table.shape)                               # (5, 12) every period
    print(table.loc["LATAM"].sum(), list(table.columns[:3]))

(5, 12)
0.0 ['2025-10', '2025-11', '2025-12']

The unknown-value check turns a silent problem into a loud one. pd.Categorical converts values outside categories to NaN, and those rows then vanish from the pivot along with their revenue. Raising — or mapping unknowns to an explicit Unassigned bucket before conversion — keeps the grand total honest.

Pivot shape before and after declaring categories The left panel shows a pivot built from object columns with four region rows and only three month columns, because LATAM and nine months had no data. The right panel shows the same data pivoted from categoricals with observed False and dropna False, producing five region rows including a zero LATAM row and all twelve month columns in calendar order. object dtype shape (4, 3) rows: APAC EMEA N.America Unassigned cols: 2026-06 2026-08 2026-09 LATAM: missing categorical, observed=False shape (5, 12) rows: EMEA N.America LATAM APAC Unassigned cols: 2025-10 ... 2026-09 LATAM: all zeros

Variant Fix 1: Reindex After Pivoting

When you cannot change the input dtypes — the frame is shared with other code, or it comes from a library — reindex the result instead. It achieves the same shape after the fact:

# pip install "pandas>=2.2"
import pandas as pd

def reindexed_pivot(frame: pd.DataFrame) -> pd.DataFrame:
    table = pd.pivot_table(frame, index="region", columns="month", values="revenue",
                           aggfunc="sum", fill_value=0)
    return table.reindex(index=EXPECTED_REGIONS, columns=EXPECTED_MONTHS, fill_value=0)

Reindexing is simple and version-independent, but unlike the categorical approach it silently drops labels that are in the data and not in the expected lists — reindex keeps only the listed labels. Compute set(table.index) - set(EXPECTED_REGIONS) before reindexing and treat a non-empty result as an error, or the report loses revenue without warning.

Variant Fix 2: groupby Instead of pivot_table

The same rules apply to groupby, where the warning appears most often. With categoricals, pass observed=False to get every combination, then unstack:

# pip install "pandas>=2.2"
import pandas as pd

def groupby_matrix(frame: pd.DataFrame) -> pd.DataFrame:
    data = frame.assign(
        region=pd.Categorical(frame["region"], categories=EXPECTED_REGIONS),
        month=pd.Categorical(frame["month"], categories=EXPECTED_MONTHS),
    )
    return (data.groupby(["region", "month"], observed=False)["revenue"]
                .sum()                                # sum of an empty group is 0
                .unstack("month")
                .reindex(columns=EXPECTED_MONTHS))

Note the aggregation difference: sum of an empty group returns 0, but mean, min and max return NaN. Decide whether an empty month should display as zero or blank — an average order value of zero is misleading — and fill accordingly rather than applying fillna(0) to everything.

What empty categories contain by aggregation For an empty category with observed False, sum returns zero and should display as zero. count and size return zero and should display as zero. mean returns NaN and should display as blank because an average of nothing is undefined. max and min return NaN and should display as blank or a dash. Aggregation Empty group value Show in report as sum 0 0 count / size 0 0 mean NaN blank not 0 max / min NaN blank or dash

Keeping the Expected Lists Current

Hard-coded lists go stale: a new region launches, and the fix above either raises (good) or maps the region to Unassigned (acceptable) until someone updates the code. Store the lists as data, next to the report, and derive the month window from the report period rather than typing twelve strings:

# pip install "pandas>=2.2" pyyaml
from pathlib import Path
import pandas as pd
import yaml

CONFIG = Path("config/report-dimensions.yaml")

def load_dimensions(period: str, months_back: int = 12) -> dict[str, list[str]]:
    try:
        cfg = yaml.safe_load(CONFIG.read_text(encoding="utf-8"))
    except (OSError, yaml.YAMLError) as exc:
        raise SystemExit(f"cannot load {CONFIG}: {exc}")
    months = pd.period_range(end=pd.Period(period, "M"), periods=months_back, freq="M")
    return {
        "region": cfg["regions"] + ["Unassigned"],
        "channel": cfg["channels"] + ["Unassigned"],
        "month": [str(m) for m in months],
    }

A YAML file that business owners can review — and that lives in version control — makes "why did APAC-South appear this month" answerable from the commit history, and keeps the pivot code free of literals.

Dates as Dimensions: Fill the Calendar, Not Just the Labels

When the column dimension is a real date rather than a month string, missing periods have a second consequence: charts built from the table space points by position, so a missing July makes June and August look adjacent and the trend line lies. Resampling a time series fills the calendar before pivoting, and works without declaring every label by hand:

# pip install "pandas>=2.2"
import pandas as pd

def monthly_by_region(frame: pd.DataFrame, start: str, end: str) -> pd.DataFrame:
    """Month-end revenue per region with every month in [start, end] present."""
    data = frame.assign(date=pd.to_datetime(frame["date"]))
    wide = (data.set_index("date")
                .groupby("region")["revenue"]
                .resample("ME")                        # month-end buckets per region
                .sum()
                .unstack("region"))
    calendar = pd.date_range(start=start, end=end, freq="ME")
    wide = wide.reindex(calendar, fill_value=0)       # months with no rows in any region
    wide.index = wide.index.to_period("M").astype(str)
    return wide.T.reindex(EXPECTED_REGIONS, fill_value=0)   # regions as rows, all present

resample fills gaps between a region's first and last transaction, but not before its first or after its last, which is why the reindex against an explicit calendar is still required. The "ME" alias is the month-end frequency in current pandas; older versions used "M", which now raises a deprecation warning. Excel charts then receive one column per month without holes, which keeps the x-axis honest — the same concern covered for chart output in fix openpyxl chart not showing in Excel.

Guarding Against Regressions with a Sparse Test

The missing-category bug only reproduces when data is absent, so ordinary test fixtures — copied from a busy month — never catch it. Add one deliberately sparse fixture and assert the shape:

# pip install "pandas>=2.2" pytest
import pandas as pd
import pytest

from report.pivots import stable_pivot, EXPECTED_REGIONS, EXPECTED_MONTHS

@pytest.fixture
def sparse_sales() -> pd.DataFrame:
    # one region, one month: every other label must still appear
    return pd.DataFrame({"region": ["EMEA"], "month": ["2026-09"], "revenue": [100.0]})

def test_shape_is_stable_when_data_is_sparse(sparse_sales):
    table = stable_pivot(sparse_sales)
    assert table.shape == (len(EXPECTED_REGIONS), len(EXPECTED_MONTHS))
    assert table.to_numpy().sum() == pytest.approx(100.0)

def test_unknown_region_is_rejected():
    bad = pd.DataFrame({"region": ["Antarctica"], "month": ["2026-09"], "revenue": [1.0]})
    with pytest.raises(ValueError, match="not in the expected list"):
        stable_pivot(bad)

Two tests, milliseconds to run, and they pin down both halves of the contract: every expected label is present, and no unexpected label is silently lost. Run them against the minimum and maximum pandas versions you support, since the observed default is precisely the kind of behaviour that changes between them.

Verification

Assert the pivot has exactly the expected labels in the expected order, and that no value was lost between the input and the table.

# pip install "pandas>=2.2"
import pandas as pd

def verify_pivot(table: pd.DataFrame, frame: pd.DataFrame) -> None:
    assert list(table.index) == EXPECTED_REGIONS, f"rows: {list(table.index)}"
    assert [str(c) for c in table.columns] == EXPECTED_MONTHS, f"columns: {list(table.columns)}"
    in_window = frame[frame["month"].isin(EXPECTED_MONTHS)]
    assert abs(table.to_numpy().sum() - in_window["revenue"].sum()) < 0.005, \
        "revenue lost between input and pivot (unknown labels became NaN?)"
    print(f"pivot {table.shape} verified; total {table.to_numpy().sum():,.2f}")

if __name__ == "__main__":
    verify_pivot(stable_pivot(sales), sales)

Run the verification with a deliberately sparse fixture — a month with no rows, a region with no rows — in your test suite. The bug only appears when data is missing, so a test built from a busy month will always pass.

FAQ

Should I just silence the FutureWarning? No. Pass observed explicitly. Silencing it leaves behaviour tied to whatever the default is in the installed pandas version.

Does margins=True work with empty categories? Yes, but the margin row and column are computed from observed data, so an all-zero category contributes zero to them. Totals remain correct.

Why do my categories come out in alphabetical order?pivot_table sorts labels by default. Pass sort=False to keep the declared categorical order, or make the categorical ordered=True.

Is this relevant to crosstab? Yes. pd.crosstab takes dropna=False for the same purpose and respects categorical dtypes; the same fix applies.

Part of Building Pivot Tables and Summaries for Excel.