Introduction

Pivoting a pandas DataFrame means reshaping long-form rows into a wider table where one column becomes the new columns axis. The right tool is usually pivot when every index and column combination is unique, or pivot_table when duplicates exist and you need aggregation.

Use pivot For Clean One-To-One Reshaping

Suppose you start with long data:

1import pandas as pd
2
3df = pd.DataFrame({
4    "date": ["2026-03-01", "2026-03-01", "2026-03-02", "2026-03-02"],
5    "metric": ["sales", "cost", "sales", "cost"],
6    "value": [120, 80, 150, 90]
7})
8
9print(df)

You can pivot it like this:

pivoted = df.pivot(index="date", columns="metric", values="value")
print(pivoted)

Result:

• each unique date becomes a row
• each metric becomes a column
• 'value fills the cells'

This is the cleanest API when there is exactly one value for each row-column combination.

Use pivot_table When Duplicates Exist

If the same index and column pair appears more than once, pivot raises an error because it does not know which value to keep.

That is when pivot_table is the right tool:

1import pandas as pd
2
3df = pd.DataFrame({
4    "date": ["2026-03-01", "2026-03-01", "2026-03-01"],
5    "metric": ["sales", "sales", "cost"],
6    "value": [120, 140, 80]
7})
8
9table = df.pivot_table(
10    index="date",
11    columns="metric",
12    values="value",
13    aggfunc="mean"
14)
15
16print(table)

Now duplicates are aggregated instead of causing failure.

Common aggregation functions include:

• '"mean"'
• '"sum"'
• '"count"'
• 'max'
• 'min'

That makes pivot_table the more flexible tool for real-world messy data.

Reset The Index If You Want A Flat DataFrame

After pivoting, the former index often becomes the actual DataFrame index. If you want it back as a regular column, call reset_index.

flat = pivoted.reset_index()
print(flat)

This is useful before exporting, merging, or serializing the result.

Multiple Value Columns And MultiIndex Output

Pandas can also pivot multiple value columns, which often creates a MultiIndex on the columns.

1df = pd.DataFrame({
2    "date": ["2026-03-01", "2026-03-01", "2026-03-02", "2026-03-02"],
3    "metric": ["sales", "cost", "sales", "cost"],
4    "amount": [120, 80, 150, 90],
5    "count": [3, 1, 4, 1]
6})
7
8wide = df.pivot(index="date", columns="metric", values=["amount", "count"])
9print(wide)

The output is correct, but the column structure becomes more complex. If needed, you can flatten it afterward.

Know The Reverse Operation

Pivoting is often only half the story. To move wide data back into long form, use melt.

1restored = pivoted.reset_index().melt(
2    id_vars="date",
3    var_name="metric",
4    value_name="value"
5)
6
7print(restored)

Understanding both pivot and melt makes reshaping much easier because you can move between long and wide forms deliberately.

A Good Rule Of Thumb

Use:

• 'pivot when the data is already unique per index-column pair'
• 'pivot_table when duplicates exist or aggregation is needed'
• 'melt when you need to go back to long form'

That simple rule covers most pandas reshaping tasks.

Common Pitfalls

The biggest mistake is using pivot on data that contains duplicate combinations. Pandas will reject that because the result would be ambiguous.

Another mistake is forgetting that the chosen index becomes the DataFrame index after the pivot. If later code expects a normal column, use reset_index.

People also get confused by MultiIndex columns after pivoting multiple value fields. The data is valid, but you may need extra cleanup if you want simple flat column names.

Finally, do not guess which reshaping function to use. Decide first whether you are doing pure rearrangement or rearrangement plus aggregation.

Summary

• 'pivot reshapes long data into wide form when each index-column pair is unique.'
• 'pivot_table handles duplicates by aggregating values.'
• 'reset_index flattens the result when you want former index values back as columns.'
• Pivoting multiple value columns can create MultiIndex columns.
• 'melt is the reverse-style operation when you need to return to long form.'