You called df.pivot_table(values="amount", index="month", columns="category") and pandas threw a KeyError, usually pointing at one of the column names. The issue is almost always a column name typo or that the column doesn’t exist after a previous transformation.
📌 Quick answer: Print df.columns.tolist() right before the pivot_table call. Verify the names of values, index, and columns arguments match exactly. If you got the DataFrame from a merge or groupby, the column may have been renamed (e.g. amount_x) or moved into the index.
Cause 1: Column name typo or whitespace
The most common cause. Pivot_table errors usually point to whichever column it tried first.
import pandas as pd
df = pd.read_csv("sales.csv")
df.pivot_table(values="Amount", index="month", columns="category")
# ❌ KeyError: 'Amount' if column is "amount" (lowercase)
# Diagnose
print(df.columns.tolist())
# Fix: normalize at load
df.columns = df.columns.str.strip().str.lower()Cause 2: Column was moved into the index by groupby
If you did df.groupby("month").sum().pivot_table(...), “month” is now the index, not a column. pivot_table expects column names but gets index names.
grouped = df.groupby("month").sum()
grouped.pivot_table(index="month", ...) # ❌ KeyError: 'month' (it's the index)
# Fix: reset_index first
grouped = df.groupby("month").sum().reset_index()
grouped.pivot_table(index="month", ...) # ✓ worksCause 3: fill_value type mismatch causes silent KeyError downstream
You passed fill_value=0 but the values column is float. Pandas may raise a downstream KeyError when applying the fill.
df.pivot_table(values="amount", index="month", columns="category", fill_value=0)
# Sometimes errors if dtype is object
# Fix: cast first
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
df.pivot_table(values="amount", index="month", columns="category", fill_value=0)Cause 4: aggfunc dict references missing column
Passing aggfunc={"amount":"sum", "qty":"mean"} when “qty” doesn’t exist.
df.pivot_table(values=["amount","qty"], index="month", aggfunc={"amount":"sum","qty":"mean"})
# ❌ KeyError if 'qty' is actually 'quantity'
# Diagnose: check both values columns AND aggfunc keys match
print([c for c in df.columns if "qty" in c.lower() or "quantity" in c.lower()])Prevention
- Normalize column names at load:
df.columns = df.columns.str.strip().str.lower() - reset_index() after groupby if you plan to pivot
- Verify dtype of value columns before pivot (numeric for sum/mean)
- Use named-agg pattern when possible for clearer errors
Related Guides
Frequently Asked Questions
Why does pivot_table raise KeyError when the column is in df.columns?
Whitespace, case, or wrong stage. Print df.columns.tolist() right before the call to verify the exact name. If you did a groupby() before pivot_table(), the column may now be in df.index. Use .reset_index() to move it back.
Can I pivot on a column that’s currently the index?
No. pivot_table reads from columns, not the index. Call df.reset_index() first to move the index column back into the columns.
Why does fill_value cause KeyError?
Type mismatch between fill_value and the values column. If the values column is object dtype with mixed strings and numbers, fill_value=0 may fail. Cast first: df[‘col’] = pd.to_numeric(df[‘col’], errors=’coerce’).
What’s the difference between pivot and pivot_table?
pivot() requires unique index/columns combinations and raises if duplicates exist. pivot_table() handles duplicates by aggregating (sum by default) and never raises on duplicates. Use pivot_table for almost all real-world data.
How do I pivot with multiple values columns?
Pass a list: df.pivot_table(values=[‘amount’,’qty’], index=’month’, aggfunc=’sum’). Result has MultiIndex columns. Flatten with .columns.map(‘_’.join) or just use named-agg via .groupby() instead.