Skip to content
LearnPY
pandas

Reshape wide and long tables

Move values between rows and columns with pivot and melt, and use pivot_table when the same cell appears more than once.

11 min readbeginnerpandas

By the end

  • Describe the difference between wide and long data.
  • Pivot unique rows into columns.
  • Melt columns back into tidy rows.
  • Aggregate duplicates with pivot_table.
  • Choose a shape based on the next operation, not appearance alone.

Wide versus long

Long data has one observation per row: a region, a quarter, and a revenue value. Wide data spreads one of those dimensions across columns: one row per region, with a column for each quarter. Both can be correct; the useful shape depends on the question and the tool.

ShapeRowsColumns
LongOne row per region-quarterregion, quarter, revenue
WideOne row per regionregion, Q1, Q2
The same values, two shapes

Pivot long data into columns

pivot takes an index column, a columns column, and a values column. It requires each index-columns pair to be unique; if a pair repeats, pivot does not know which value belongs in the cell.

Python

One row per region

Ready
Loading editor…

Output

Press Run to execute this cell. The first run downloads Python; later runs reuse the session.

The output index is region and the output columns come from quarter. The name quarter remains attached to the column axis, which matters when you melt the frame back.

Melt wide data back to rows

melt is the inverse operation for the identifier columns: it turns wide columns into a pair of variable and value columns. Reset the index first so the region labels become ordinary data again.

Python

Back to one observation per row

Ready
Loading editor…

Output

Press Run to execute this cell. The first run downloads Python; later runs reuse the session.

Without reset_index, the region index would not be available as an id_vars column. Without sort_values, melt follows the column order rather than a tidy region-quarter order.

Pivot tables handle duplicates

pivot_table is the aggregation-aware version. When an index-columns pair repeats, it combines the values with aggfunc instead of failing. fill_value decides what appears for an empty cell.

Python

Duplicate cells are summed

Ready
Loading editor…

Output

Press Run to execute this cell. The first run downloads Python; later runs reuse the session.

Choose the shape for the next step

Before reshaping, name the observation, the identifier columns, and the measurement. After reshaping, check the index, columns, and one known cell against the long input. A reshape that runs without error can still put the wrong values in the wrong cells.

Python

Reshape and verify one cell

Ready
Loading editor…

Output

Press Run to execute this cell. The first run downloads Python; later runs reuse the session.

This completes the pandas core. The Matplotlib track turns these tables into charts, starting with the anatomy of a figure and the relationship between figures and axes.

Practice

Make it stick

Run the tests as often as you like. Hints and the solution are available when you need them.

Exercise

Pivot long sales into wide with region as the index and quarter as the columns. Assign that to wide. Then melt wide back into a tidy frame with columns region, quarter, and revenue, sorted by region and quarter. Assign that to tidy.

Your solution

Ready
Loading editor…

Results

Run the tests to check your solution. The first run downloads Python; later runs reuse the session.

Quiz

Check your understanding

01What does long data usually have?
02What does pivot require for each index-columns pair?
03What does melt do?

Lesson progress