Why does pivot raise a duplicate entries error in pandas?

The short version

pivot simply rearranges values; it cannot combine them. If two rows share the same index and column combination, there is no single value for that cell, so pandas raises rather than guess. Use pivot_table, which aggregates, or remove the duplicates first.

Seeing the error

import pandas as pd

df = pd.DataFrame({
    "city": ["Leeds", "Leeds"],
    "kind": ["online", "online"],
    "amount": [1, 2],
})

df.pivot(index="city", columns="kind", values="amount")
# ValueError: Index contains duplicate entries, cannot reshape

Two rows both claim the Leeds/online cell, with the values 1 and 2. Which should go in the cell? Pandas will not choose for you.

The fix

df.pivot_table(index="city", columns="kind", values="amount", aggfunc="sum")

kind   online
city
Leeds       3

pivot_table aggregates the clashing rows, here summing them to 3. You must state the aggregation, because the correct answer depends entirely on what your data means.

The error is telling you something real

It is tempting to switch to pivot_table and move on, but stop and ask why the duplicates exist. They usually mean one of two things.

Either your data legitimately has several rows per combination (several orders per city per channel), in which case aggregating is correct and you should choose the aggregation deliberately: sum for totals, mean for an average, count for how many.

Or the duplicates are an error: a botched join that multiplied rows, or a file loaded twice. In that case pivot_table will silently sum the duplicates and hand you a number that is double what it should be. The error was a warning, and suppressing it hides a real bug.

Check before you aggregate

df.duplicated(subset=["city", "kind"], keep=False).sum()

If duplicates are unexpected, look at them before deciding what to do.

df[df.duplicated(subset=["city", "kind"], keep=False)]

The default aggregation

pivot_table defaults to mean if you do not specify aggfunc. That surprises people who expect a sum, and it is a quiet source of wrong numbers. Always state aggfunc explicitly, so a reader can see what the cells contain.

Which to use

Use pivot when the combination of index and columns is genuinely unique and you just want to rearrange the layout. Its refusal to aggregate is a feature: it verifies your assumption that each cell has one value.

Use pivot_table when you are summarising, and want to reduce many rows per cell to one figure. Because it is a superset, many people use it always, but you then lose the automatic duplicate check that pivot gives you for free.