pandas pivot: DataFrame.pivot() vs pivot_table() With Examples

Short answer: in pandas, DataFrame.pivot() reshapes long data to wide when every index/columns pair occurs exactly once; pandas.pivot_table() does the same reshape but aggregates duplicates (default aggfunc='mean') and can fill or total missing cells. If you hit ValueError: Index contains duplicate entries, cannot reshape, switch to pivot_table() or remove the duplicates. Examples below were run on pandas 3.0.5; syntax is checked against the pandas 3.1.0rc0 reference pages, read 2026-11-07.

What pandas pivot is used for in research

Research data is often stored long: one row per subject per timepoint, per sample per assay, per site per year. Analysis and tables usually want it wide, with one row per subject and one column per timepoint. Pivoting does that reshape; pandas melt reverses it. The same long-to-wide operation exists in other tools, for example PROC TRANSPOSE in SAS and the Stata reshape command, and is one step of data wrangling. Searches for “pivot pandas” mean the same two functions covered here.

Install or access

pandas is open-source and installed from the Python Package Index with pip install pandas (see the install page at pandas.pydata.org). Both functions are in the core library; no extra package is needed.

pivot() vs pivot_table(): which to use

Function	Duplicate index/columns pairs	Aggregation	Use when
DataFrame.pivot()	Raises ValueError	None	Each index/columns pair is unique and you only need to reshape
pandas.pivot_table()	Aggregated with aggfunc (default 'mean')	Function, list or dict of functions	Duplicates exist, or you want sums, means, counts or margins totals
DataFrame.melt()	Not applicable	None	Going the other way, wide to long (see pandas melt)
groupby(...).agg(...).unstack()	Aggregated by the groupby step	Any aggregation	You already aggregate with groupby and want the last index level as columns
pandas.crosstab()	Counted by default	Frequencies, or a function on supplied values	You want a frequency table of two categorical columns

The pandas user guide states that pivot() “can only handle unique rows specified by index and columns” and says to use pivot_table() if the data contains duplicates (user guide, Reshaping and pivot tables, read 2026-11-07). The groupby-unstack and crosstab rows were run on the example data below and return the shapes described.

Step-by-step: reshape long data to wide

The example is synthetic data: five rows of a score measured per subject at two timepoints. It is not from a real study.

1. Build or load the long table.
2. Check uniqueness of the index/columns pair.
3. Call pivot() with keyword arguments only; the signature is DataFrame.pivot(*, columns, index=<no_default>, values=<no_default>), so every argument is keyword-only and columns is required.
4. Optionally call reset_index() to turn the subject index back into an ordinary column.

import pandas as pd

df = pd.DataFrame({
    "subject":   ["S1", "S1", "S2", "S2", "S3"],
    "timepoint": ["baseline", "week4", "baseline", "week4", "baseline"],
    "score":     [12, 15, 10, 14, 11],
})

df.duplicated(subset=["subject", "timepoint"]).any()   # False: safe to pivot()

wide = df.pivot(index="subject", columns="timepoint", values="score")
wide_flat = wide.reset_index()

Output and how to read it

timepoint  baseline  week4
subject
S1             12.0   15.0
S2             10.0   14.0
S3             11.0    NaN

Each row is a subject, each column a timepoint. Subject S3 has no week4 row, so that cell is NaN, and the integer column becomes float because NaN cannot be stored in a plain integer column. The index is named subject and the columns axis is named timepoint; reset_index() makes subject a column again, which is what most plotting and merge code expects. Omitting values pivots all remaining columns and returns hierarchical (MultiIndex) columns, as in the pandas reference examples.

When pivot() fails: duplicate entries

If any subject/timepoint pair appears twice, pivot() raises ValueError: Index contains duplicate entries, cannot reshape. Add a repeat measurement for S1 at week4 to reproduce it, then locate the rows:

dup = pd.concat([df, pd.DataFrame({"subject": ["S1"], "timepoint": ["week4"], "score": [17]})],
                ignore_index=True)

dup.pivot(index="subject", columns="timepoint", values="score")   # ValueError
dup[dup.duplicated(subset=["subject", "timepoint"], keep=False)]  # rows 1 and 5

Decide what the duplicate means before aggregating: a data-entry repeat should be removed, a legitimate replicate measurement can be averaged. The pandas reference for DataFrame.pivot points to pivot_table() when values must be aggregated.

pivot_table(): aggregation and missing values

Signature (pandas 3.1.0rc0 reference): pandas.pivot_table(data, values=None, index=None, columns=None, aggfunc='mean', fill_value=None, margins=False, dropna=True, margins_name='All', observed=True, sort=True, **kwargs).

dup.pivot_table(index="subject", columns="timepoint", values="score", aggfunc="mean")

timepoint  baseline  week4
subject
S1             12.0   16.0
S2             10.0   14.0
S3             11.0    NaN

S1 week4 is now the mean of 15 and 17. Missing and total handling, each run on this data:

• NaN for absent combinations. S3 week4 stays NaN: it never existed, so there is nothing to aggregate.
• fill_value=0 replaces missing cells in the result (here S3 week4 becomes 0.0). It fills after aggregation. Only use it when zero is a meaningful value; for a score that was simply not measured, leaving NaN is more honest than a 0 that will pull means down.
• margins=True adds an All row and column of totals computed with the same aggfunc (here, overall means: baseline 11.0, week4 15.33, all 13.17). margins_name renames it.
• dropna=True (default) excludes columns whose entries are all NaN.
• aggfunc accepts a function, a list (giving hierarchical columns) or a dict mapping each values column to a function or list, for example {'D': 'mean', 'E': ['min', 'max', 'mean']} in the pandas reference.
• observed defaults to True as of pandas 3.0.0 (it was False before); this matters only when index or columns are categorical.
• **kwargs are passed on to aggfunc; added in pandas 3.0.0.

Back to long format, and other routes

wide.reset_index().melt(id_vars='subject', var_name='timepoint', value_name='score') returns the long layout; the missing S3 week4 comes back as an explicit NaN row (see pandas melt). For counts, pd.crosstab(dup['subject'], dup['timepoint']) returns 1, 2 for S1, 1, 1 for S2 and 1, 0 for S3, which doubles as a duplicate check. To tidy the result afterwards, see how to drop columns in pandas, or bin continuous scores first with pandas cut.

Troubleshooting

Symptom	Cause	Fix
ValueError: Index contains duplicate entries, cannot reshape	An index/columns pair occurs more than once	Find rows with duplicated(subset=[...], keep=False); remove, or use pivot_table()
Integers turned into floats	Missing cells are NaN	Expected; resolve missing data first if you need integers
Subject names are the index, not a column	pivot sets the index	reset_index()
Means appear where you expected sums	aggfunc defaults to 'mean'	Set aggfunc='sum' or 'count'

Equivalents in other tools

Tool	Equivalent
SAS	PROC TRANSPOSE (guide)
Stata	reshape wide (guide)

Within Python itself, the groupby-unstack and crosstab routes above need no extra package.

FAQ

What is the difference between pandas pivot and pivot_table?

pivot() only reshapes and fails on duplicate index/columns pairs; pivot_table() aggregates duplicates (mean by default) and supports fill_value and margins.

How do I turn the pivot result back into a normal DataFrame?

Call reset_index().

How do I undo a pivot?

Use melt() after reset_index(); see pandas melt.

Does pandas pivot_table work like an Excel pivot table?

The pandas user guide describes it as producing spreadsheet-style pivot tables, with aggregation of numeric data.

Sources

• pandas documentation, pandas.DataFrame.pivot (3.1.0rc0), read 2026-11-07.
• pandas documentation, pandas.pivot_table, read 2026-11-07.
• pandas user guide, Reshaping and pivot tables, read 2026-11-07.