Long to Wide with pivot
pivot turns a long table back into a wide one, with index naming the rows, columns naming the column headers and values filling the cells, stops with an error when two rows would land in the same cell, and pivot_table, which combines those rows instead, is the right choice only when combining them is what you meant.
Journeyman · Python · Updated
After this lesson you can
- Turn a long table back into a wide one with pivot, using index, columns and values
- Explain why pivot stops on repeated rows, and when pivot_table, which combines them, is the right choice
Key takeaways
- pivot(index="student_id", columns="quiz", values="score") gives one row per student and a column per quiz.
- Each cell of the wide table must come from exactly one long row. When two rows would fill the same cell, pivot stops with "Index contains duplicate entries".
- The fix is usually another column in index, such as term, so each cell has one row again.
- pivot_table never stops on repeated rows; it averages them (or sums, with aggfunc). That's right when you want the average, and hides a mistake when you don't.
Long tables are what pandas works best with, and wide tables are what people read: a report card has a row per student and a column per quiz, not 1,920 rows of scores. So most analyses melt the data, do the work, and turn the answer back into a wide table for the people who asked. You'll do that last step for every report.
The example is Oakridge Middle School's quiz scores for the whole year, the long table Wide to Long with melt made: 1,920 rows of student_id, term (fall or spring), quiz and score.
import pandas as pd
fall = pd.read_csv("fall_scores.csv")
spring = pd.read_csv("spring_scores.csv")
both = pd.concat(
[fall.assign(term="fall"), spring.assign(term="spring")],
ignore_index=True,
)
year = both.melt(id_vars=["student_id", "term"], var_name="quiz", value_name="score")
year.head(3)
student_id term quiz score
0 1001 fall quiz_1 74
1 1002 fall quiz_1 85
2 1003 fall quiz_1 78
Back to one row per student
The homeroom teachers want the fall scores as a table they can read: a row per student, a column per quiz. pivot builds it from three column names:
index: which column's values become the rows:student_id.columns: which column's values become the column headers:quiz.values: which column fills the cells:score.
fall_long = year[year["term"] == "fall"]
wide = fall_long.pivot(index="student_id", columns="quiz", values="score")
print("rows before:", len(fall_long))
print("rows after:", len(wide))
wide.head(3)
rows before: 960
rows after: 240
quiz quiz_1 quiz_2 quiz_3 quiz_4
student_id
1001 74 59 84 72
1002 85 74 76 79
1003 78 74 77 77
960 rows became 240 rows of 4 scores: the melt, undone. The students' ids are now the row labels, the index, and quiz above them is the name pandas gives the column headers. wide.reset_index() turns student_id back into an ordinary column.
When pivot refuses: repeated rows
Now try the whole year the same way:
year.pivot(index="student_id", columns="quiz", values="score")
ValueError: Index contains duplicate entries, cannot reshape
Student 1001 has two quiz_1 rows in year, fall's 74 and spring's 71, and the wide table has one cell for "student 1001, quiz_1". pivot won't pick one for you, so it stops. The error means: some cells would get more than one row. The usual fix is to name the column that tells those rows apart, here term, as part of the index:
by_term = year.pivot(index=["student_id", "term"], columns="quiz", values="score")
print("rows:", len(by_term))
by_term.head(4)
rows: 480
quiz quiz_1 quiz_2 quiz_3 quiz_4
student_id term
1001 fall 74 59 84 72
spring 71 77 67 66
1002 fall 85 74 76 79
spring 82 78 92 89
One row per student per term, and every cell has exactly one score.
pivot_table combines instead
Pivot Tables and crosstab used pivot_table, which looks almost the same, and it doesn't stop on the whole year:
averaged = year.pivot_table(index="student_id", columns="quiz", values="score")
print("rows:", len(averaged))
averaged.head(2)
rows: 240
quiz quiz_1 quiz_2 quiz_3 quiz_4
student_id
1001 72.5 68.0 75.5 69.0
1002 83.5 76.0 84.0 84.0
When two rows land in one cell, pivot_table combines them, with the average unless aggfunc= says otherwise: 72.5 is the average of 74 and 71. If you wanted each student's year average for each quiz, that's the right table. If you thought each cell had one score, it's a quiet mistake: two scores became one number, and nothing told you.
So choose on purpose. Use pivot when each cell should hold exactly one value; its error is a check that it does. Use pivot_table when rows should be combined, and say how with aggfunc=.
stack and unstack, named
pandas has two more reshaping tools you'll meet in other people's code. stack moves the column headers into the row labels, which makes a long shape out of a wide one, and unstack moves them back:
fall.set_index("student_id").head(2).stack()
student_id
1001 quiz_1 74
quiz_2 59
quiz_3 84
quiz_4 72
1002 quiz_1 85
quiz_2 74
quiz_3 76
quiz_4 79
dtype: int64
They work on the row labels instead of on named columns, so melt and pivot, which name every column, are easier to read and check.
Check yourself
2 questions to try on the lesson page.
So what
pivot turns long data back into a table people can read, and its error for repeated rows is a gift: it tells you a cell would hide two values, so you can name what tells them apart or choose to combine them with pivot_table.