Long to Wide with pivot
Overview
The other direction, for when a person should be one row.
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Spread a long table back into a grid.
- Name what stays down the side and what goes across the top.
- Explain the duplicate-entries error and fix it.
- Choose between
pivotandpivot_table.
Why This Matters
Long is the shape for computing; wide is the shape for reading. The final table in a report almost always wants one row per person and a column per period, and pivot is the trip back. Its one error message is unhelpful until you know what it means, at which point it is precise.
Step 1: pivot spreads a column across the top
index stays down the side, columns spreads across the top, values fills the cells. The exact inverse of melt.
The round trip
Melt then pivot gets you back where you started, which is a useful way to convince yourself what each one does.
import pandas as pd
wide = pd.DataFrame({"name": ["Ada", "Bo"], "jan": [90, 70], "feb": [80, 60]})
long = wide.melt(id_vars=["name"], var_name="month", value_name="score")
back = long.pivot(index="name", columns="month", values="score")
print(back)The index is the thing you kept
The name is now the index rather than a column. reset_index() puts it back if you want a plain table.
import pandas as pd
long = pd.DataFrame({"name": ["Ada", "Ada"], "month": ["jan", "feb"], "score": [90, 80]})
wide = long.pivot(index="name", columns="month", values="score")
print(list(wide.columns))
print(wide.reset_index())Mini Practice #1: There and back
Try It YourselfMelt this table and pivot it back, then check the result has the columns you started with.
Step 2: It needs the pairs to be unique
A grid cell holds one value. If two rows claim the same index and column, pivot cannot choose between them and says so.
The error, and what it means
"Index contains duplicate entries, cannot reshape" means two rows want the same cell. It is not a corrupt table; it is a question pivot cannot answer on its own.
import pandas as pd
long = pd.DataFrame({"name": ["Ada", "Ada"], "month": ["jan", "jan"], "score": [90, 80]})
try:
long.pivot(index="name", columns="month", values="score")
except ValueError as exc:
print("ValueError:", exc)pivot_table answers it
Supplying an aggfunc tells pandas what to do with the two values. That is a decision you should be making anyway.
import pandas as pd
long = pd.DataFrame({"name": ["Ada", "Ada"], "month": ["jan", "jan"], "score": [90, 80]})
print(pd.pivot_table(long, index="name", columns="month", values="score", aggfunc="mean"))If the duplicates are not legitimate — the same reading recorded twice — the honest fix is upstream, with drop_duplicates, rather than averaging a mistake.
Mini Practice #2: Two rows, one cell
Try It YourselfRun this to see the error, then get an answer out of it with pivot_table.
Step 3: When to Use It
One row per person for reading
A long table of scores becomes a grid with a column per month.
df.pivot(index="name", columns="month", values="score")Comparing periods side by side
Wide is the shape for a table a person scans across.
When Not to Use It
Do not use pivot when a name and month can appear twice — it raises. Use pivot_table with an aggfunc, and decide how duplicates combine.
End-of-Lesson Exercises
Exercise 1: Spread the months across
Write month_columns(df) that spreads a long table with name, month and score into one row per name, and returns the sorted column names.
index is what stays down the side, columns is what spreads across the top, values fills the cells. sorted() on the result keeps the answer stable.
Exercise 2: Spread a table that has duplicates
Write wide_mean(df) that spreads the same long shape into one row per name, averaging where a name has more than one score for a month, and returns the shape of the result. A plain pivot cannot do this.
pivot raises on duplicate index/column pairs. pivot_table with aggfunc="mean" answers the question pivot could not, then .shape gives the grid size.