pandas pivot_table "duplicate entries" error fix

TL;DR

pivot_table needs one value per row/column combo, and your data has repeats. Fix it by passing an explicit aggfunc (like 'sum' or 'mean') so pandas knows how to combine the duplicates, or dedupe first if the repeats are junk. It happens because pivot_table aggregates by default only when it must; duplicates force the question of how.

ValueError: Index contains duplicate entries, cannot reshape

Use this when

• df.pivot_table(...) raises about duplicate entries
• df.pivot(...) fails the same way (pivot never aggregates, so it always fails on dupes)
• Reshaping long-to-wide hits repeated index/column pairs

Not for

• pd.crosstab questions (same fix applies, but different function)
• Merge fan-out duplication, separate skill
• General melt/stack/unstack usage

Steps

1. Find the duplicated pairs:

df.duplicated(subset=['row_key', 'col_key'], keep=False).sum()

Expected output: the count of rows involved in duplicates.

1. Look at an example to decide: real repeats or junk?

df[df.duplicated(subset=['row_key', 'col_key'], keep=False)].head(10)

Expected output: the offending rows. Decide whether to aggregate or dedupe.

1. If the repeats are real, aggregate explicitly:

df.pivot_table(index='row_key', columns='col_key', values='val', aggfunc='sum')

Expected output: the wide table, duplicates combined by sum. Use 'mean', 'count', 'first' as appropriate.

1. If the repeats are junk, dedupe first:

df = df.drop_duplicates(subset=['row_key', 'col_key'], keep='last')
df.pivot(index='row_key', columns='col_key', values='val')

Expected output: the wide table with no aggregation needed.

1. For multiple values per cell, pass a list of aggfuncs:

df.pivot_table(index='row_key', columns='col_key', values='val', aggfunc=['sum', 'count'])

Expected output: a MultiIndex-columned frame with both aggregations.

Variant phrasings

pandas pivot duplicate entries cannot reshape

The plain pivot version of this error. Switch to pivot_table with an aggfunc, or dedupe.

pivot_table aggregation function for duplicates

aggfunc accepts 'sum', 'mean', 'count', 'min', 'max', 'first', 'last', or any function. It also accepts a dict per value column.

reshape long to wide with duplicate keys

If every combination should be unique but isnt, the duplicates are a data quality signal. Log them before dropping.

Why it happens

A pivot maps each (index, column) pair to exactly one cell. Two rows with the same pair would need to share a cell, which is impossible without combining them. pivot refuses outright; pivot_table asks you how via aggfunc.

Edge cases

• aggfunc='first'/'last' silently picks one; fine for junk dupes, dangerous for real ones.
• NaN values are excluded from most aggfuncs; count wont count them, size would, but size isnt a valid pivot_table aggfunc.
• After pivoting, the columns may be a MultiIndex; flatten with df.columns = ['_'.join(map(str, c)) for c in df.columns].
• margins=True adds row/column totals; it aggregates the already-aggregated cells.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_hHcR6gWOo7XHrDcNzIHhHA