# 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 November 19, 2026

By Gregor Dzierzon

## 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`.

```python
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)
```

```text
   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`.

```python
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)
```

```text
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:

```python
year.pivot(index="student_id", columns="quiz", values="score")
```

```text
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:

```python
by_term = year.pivot(index=["student_id", "term"], columns="quiz", values="score")
print("rows:", len(by_term))
by_term.head(4)
```

```text
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:

```python
averaged = year.pivot_table(index="student_id", columns="quiz", values="score")
print("rows:", len(averaged))
averaged.head(2)
```

```text
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:

```python
fall.set_index("student_id").head(2).stack()
```

```text
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

- [pivot()](https://www.ask-codey.com/glossary/pivot-python)
- [pivot_table()](https://www.ask-codey.com/glossary/pivot-table-python)
- [Wide Format](https://www.ask-codey.com/glossary/wide-format)
- [Long Format](https://www.ask-codey.com/glossary/long-format)

## Related

- [Wide to Long with melt](https://www.ask-codey.com/lessons/pandas-melt)
- [Pivot Tables and crosstab](https://www.ask-codey.com/lessons/pandas-pivot-table)

Source: https://www.ask-codey.com/lessons/pandas-pivot
