In pandas, why does `pivot` raise on a long station, date and reading weather table when `pivot_table` succeeds, and what does `pivot_table` add?
answer
- one cell, one value
- repeated index and column pair
- pivot_table groups before reshaping
- aggfunc, fill_value, margins
basics
~10 sDataFrame.pivot only reshapes, so two rows sharing an index and column pair raise ValueError: Index contains duplicate entries, cannot reshape. pivot_table aggregates those duplicates with aggfunc (default mean) and adds fill_value and margins.
solid answer
~40 s`DataFrame.pivot(index=, columns=, values=)` moves each value into the one cell named by its index and column labels and never aggregates, so two readings for the same station and date raise `ValueError: Index contains duplicate entries, cannot reshape`. `pivot_table` groups by the index and column keys first, then aggregates each cell with `aggfunc`, which defaults to `"mean"` and accepts a string, a function, a list or a per-column dict. It also takes `fill_value`, which replaces empty cells after aggregation, and `margins=True`, which adds an `All` row and column computed from the underlying rows. I use `pivot` when the keys are known to be unique and I want a duplicate to fail loudly, and `pivot_table` when aggregation is the point.
code
python · 14 linesimport pandas as pd
long = pd.DataFrame({
"station": ["ST01", "ST01", "ST02"],
"date": ["2026-03-01", "2026-03-02", "2026-03-01"],
"reading": [4, 5, 7],
})
# unique keys: pivot works, the missing ST02 cell makes both columns float64
print(long.pivot(index="station", columns="date", values="reading").dtypes)
# pivot_table with sum and fill_value keeps int64
print(long.pivot_table(index="station", columns="date", values="reading",
aggfunc="sum", fill_value=0).dtypes)go deeper
Recall that pivot spreads a long table into columns without aggregating, and pivot_table aggregates with a default mean.
Explain why a repeated station and date pair makes pivot raise, and what aggfunc, fill_value and margins each control in pivot_table.
Treat the duplicate error as a data signal: decide whether to fix the source or aggregate, and explain why a mean margin can differ from the cells.
Frame which reshapes in a pipeline must fail on duplicates and which may aggregate, so a silent mean never hides a re-sent reading.
## What `pivot` does `DataFrame.pivot` turns a **long** table (one row per observation) into a **wide** one by taking the distinct values of one column as the new column labels. Its arguments are **keyword-only** in every current release: `columns` is required, while `index` and `values` are optional. - `index` names the column whose values become the row labels; leave it out and the frame's existing index is used. - `columns` names the column whose values become the new column labels. - `values` names the column that fills the cells; leave it out and every remaining column is spread, which gives a `MultiIndex` on the columns. `pivot` is a pure reshape. Each `(index, column)` pair must point at **exactly one** row, because there is exactly one cell to put it in. ## Why a duplicate raises Take a weather table where station `ST01` reported twice on `2026-03-01` (a re-sent reading). There are two candidate values for one cell, and `pivot` has no rule for choosing, so it refuses: ```python import pandas as pd long = pd.DataFrame({ "station": ["ST01", "ST01", "ST02", "ST02", "ST01"], "date": ["2026-03-01", "2026-03-02", "2026-03-01", "2026-03-02", "2026-03-01"], "reading": [4.0, 5.5, 7.0, 6.5, 6.0], }) try: long.pivot(index="station", columns="date", values="reading") except ValueError as err: print(err) # Index contains duplicate entries, cannot reshape wide = long.pivot_table(index="station", columns="date", values="reading", aggfunc="mean", margins=True) print(wide) ``` The error is useful: it tells you the long table is not one row per station and date. Before reaching for `pivot_table`, decide whether the duplicate is a **data defect** (fix it upstream, or remove the repeat with the duplicate-handling methods) or a **real repeated measurement** (aggregate it). ## What `pivot_table` adds `pivot_table` groups by the `index` and `columns` keys, aggregates each group, and only then reshapes, so duplicates are expected rather than fatal. | Argument | Default | What it does | |---|---|---| | `aggfunc` | `"mean"` | String alias, function, list (gives hierarchical columns), or dict per values column | | `fill_value` | `None` | Replaces missing cells **in the result, after aggregation** | | `margins` | `False` | Adds an `All` row and column | | `margins_name` | `"All"` | Label of the margin row and column | | `dropna` | `True` | Drops all-NaN result columns and keys containing NA | | `sort` | `True` | Sorts the result's labels | Useful `aggfunc` choices on a weather table: 1. `"mean"` for the average reading per station and day. 2. `"max"` or `"min"` for the extreme of a day. 3. `"size"` to count rows per cell, or `"count"` to count non-null readings. 4. `["mean", "max"]` for both at once, which adds an outer column level per function. ## Margins come from rows, not from cells `margins=True` applies `aggfunc` to **every underlying row** of that station (or that date, or the whole table). With `"mean"`, `ST01`'s margin above is the mean of its three readings (4.0, 5.5, 6.0), which is 5.1667, and **not** the mean of the two cells shown in its row (5.0 and 5.5), which would be 5.25. With `"sum"` the two coincide; with `"mean"`, `"median"` or `"max"` of an unbalanced table they do not. When a reviewer recomputes a margin from the printed cells and gets a different number, this is why. ## Holes, dtypes and `fill_value` When a station has no reading for a date, the cell is `NaN`. On a NumPy `int64` values column, that hole makes the whole result column `float64`, from `pivot` and from `pivot_table` alike. `pivot_table(..., aggfunc="sum", fill_value=0)` fills the hole before the result is assembled and keeps `int64`; `pivot` has no `fill_value`, so you would chain `.fillna(0)` and cast afterwards. ## Choosing between them - Use `pivot` when the keys are unique by construction and a duplicate should **fail loudly**. - Use `pivot_table` when several rows per cell are legitimate and you know which aggregate the report needs. - Never switch to `pivot_table` just to make the error go away: its silent `"mean"` can average a corrected reading with the wrong one it was meant to replace. - For a pure count of two categorical columns, `pd.crosstab` is the shorter spelling of `pivot_table(aggfunc="size", fill_value=0)`. Going back from wide to long is `melt`, and moving index levels to columns is `unstack`, which `pivot` itself calls after setting the index.
- How do you count readings per station and date instead of averaging them?Pass `aggfunc="size"` to count rows per cell, including rows whose reading is missing, or `aggfunc="count"` to count only non-null readings. Add `fill_value=0` so empty cells show 0 rather than NaN. `pd.crosstab(long["station"], long["date"])` gives the same row counts with zeros already filled.
- Is `long.pivot("station", "date", "reading")` a valid call?No. `DataFrame.pivot` takes keyword-only arguments, so the positional call raises `TypeError: DataFrame.pivot() takes 1 positional argument but 4 were given`. Write `long.pivot(index="station", columns="date", values="reading")`.
- Why might a margin cell disagree with the average of the cells printed in its row?Because `margins=True` applies `aggfunc` to all the underlying rows of the group, not to the aggregated cells. With `"mean"` and a station that has two readings on one day and one on another, the row margin weights each reading equally, while averaging the two cells weights each day equally.
- pivot and pivot_table are interchangeable; pivot_table is only slower
- pivot keeps the last value when an index and column pair repeats
- pivot_table sums each cell by default, like a spreadsheet pivot
- fill_value fills missing readings in the input before aggregation
- The All margin is the average of the cells shown in that row