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

By

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.

Key terms

Related