Long to wide and back - the two shapes tabular data comes in, and when each one is right.
Overview
Two shapes
Most data problems that feel complicated are actually shape problems. The data holds the right information but in the wrong layout. A column that should be a row. A value that should be a column header. A pair of columns that should be a single column of key-value pairs. Reshaping is the tool for converting between these layouts.
The two fundamental shapes are long and wide. Long form has one observation per row with columns naming the variable and holding the value. Wide form has one subject per row with a column per variable. Neither is more correct. They serve different purposes.
Long form is better for storage, joins and group-by. Adding a new variable adds rows, not columns. No schema change is needed. Missing combinations simply do not appear as rows. Most databases, most plotting libraries, and most statistical tools expect long form.
Wide form is better for reading. A table with years across the top is what you put in a report or a spreadsheet. People scan columns faster than they scan long lists of rows. Wide form is also what you need when computing correlations between variables, because each variable must be its own column.
pivot_table converts from long to wide. melt converts from wide to long. They are inverses of each other. Applying one and then the other gets you back to where you started, minus any information lost to aggregation.
The tricky part of pivot is what happens when multiple rows map to the same cell. If two rows both say city=Delhi, year=2024, there is no unique value to put in the cell. pivot raises. pivot_table applies an aggregation function, with mean as the default. That silent averaging is worth knowing about.
This module covers pivot_table, melt, and the stack and unstack methods that do the same work on the index rather than on columns. The examples use small frames where the transformation is visible.
The same data can be laid out two ways.
Long (or tidy) has one row per finding, with columns naming the var and holding the value. City, year, sales. Four rows for two cities over two years.
Wide has one row per subject and a column per var. Two rows, one per city, with a 2023 and a 2024 column.
Neither is more correct. They suit other purposes. Most real work involves converting between them.
Long form is better for storage and math. Adding a year adds rows, not columns. Nothing downstream changes. Group-by works on its own. Missing mixes simply don't appear. DBs and most plotting libraries expect it.
Wide form is better for reading. A table with years across the top is what people want to look at. It's what a spreadsheet or a report needs.
Worth knowing
Long is one row per observation; wide is one row per subject with a column per variable. Same data, different shape.
pivot goes long to wide; melt goes wide to long with id_vars naming the columns to keep.
pivotraises on duplicate index/column pairs, because it cannot choose a winner.
pivot_table aggregates instead, and adds fill_value, margins and several statistics at once.
stack and unstack do the same reshape through index levels rather than named columns.
Work in long form and pivot at the end — adding a category in wide form changes every expression that named the old columns.
Reshaping: pivot and melt
Long to wide and back, and when each shape is right.
Long and wide, the same data
One row per observation, or one row per subject with a column per variable.
example_01.pypandas
import pandas as pd
long = pd.DataFrame({
"city": ["pune", "pune", "delhi", "delhi"],
"year": [2023, 2024, 2023, 2024],
"sales": [10, 20, 30, 40],
})
print("long - one row per observation:")
print(long)
print()
wide = long.pivot(index="city", columns="year", values="sales")
print("wide - one row per city, one column per year:")
print(wide)
print()
print("Same numbers. Long is better for storage and computation,")
print("wide is better for reading and for a chart.")
Output
melt goes the other way
Column names become values in a variable column.
example_02.pypandas
import pandas as pd
wide = pd.DataFrame({
"city": ["pune", "delhi"],
"2023": [10, 30],
"2024": [20, 40],
})
print("wide:"); print(wide)
print()
long = wide.melt(id_vars="city", var_name="year", value_name="sales")
print("melted:"); print(long)
print()
print("id_vars are the columns to KEEP as identifiers; everything")
print("else is unpivoted. Naming both output columns is worth doing -")
print("the defaults are 'variable' and 'value'.")
It aggregates, fills and totals - pivot is the strict special case.
example_04.pypandas
import pandas as pd
df = pd.DataFrame({
"city": ["pune", "pune", "delhi", "delhi"],
"plan": ["free", "paid", "free", "free"],
"sales": [10, 20, 30, 40],
})
print(df.pivot_table(index="city", columns="plan", values="sales",
aggfunc="sum"))
print()
print("fill the gaps and add totals:")
print(df.pivot_table(index="city", columns="plan", values="sales",
aggfunc="sum", fill_value=0, margins=True))
print()
print("several statistics at once:")
print(df.pivot_table(index="city", values="sales", aggfunc=["sum", "mean", "count"]))
print()
try:
df.pivot_table(index="city", values="sales", aggfunc=["sum", "size"])
except AttributeError as e:
print("but 'size' in that list raises:", type(e).__name__)
print(" groupby handles it fine, which is the fallback:")
print(df.groupby("city")["sales"].agg(["sum", "size"]))
Output
stack and unstack move index levels
The same reshape, expressed through the index rather than columns.
example_05.pypandas
import pandas as pd
df = pd.DataFrame({"a": [1, 2], "b": [3, 4]}, index=["x", "y"])
print("wide:"); print(df)
print()
s = df.stack()
print("stacked - columns became an index level:")
print(s)
print(" index:", s.index.names, "->", list(s.index)[:2], "...")
print()
print("unstack puts it back:")
print(s.unstack())
print()
print("This is what groupby-with-two-keys plus unstack was doing.")
Output
Which shape to work in
Long for computing, wide for reading - and pandas prefers long.
example_06.pypandas
import pandas as pd
long = pd.DataFrame({
"city": ["pune", "pune", "delhi", "delhi"],
"year": [2023, 2024, 2023, 2024],
"sales": [10, 20, 30, 40],
})
print("in long form, a new question is one groupby:")
print(long.groupby("year")["sales"].sum())
print()
wide = long.pivot(index="city", columns="year", values="sales")
print("in wide form, the same question needs column arithmetic:")
print(wide.sum())
print()
print("and adding a year means adding a COLUMN in wide form, which")
print("changes every expression that named the old ones.")
print()
print("Keep data long; pivot at the end, for display.")
Output
pivot
df.pivot(index="city", columns="year", values="sales") goes long to wide.
Three args: what turns the row index, what turns the columns, and what fills the cells.
pivot is strict. If a given index/column pair appears more than once, it raises. There's no way to decide which value wins. That strictness is a feature. It tells you the data isn't what you assumed rather than silently keeping one row.
pivot_table
pivot_table is the general version: when a cell has a few values, it aggregates them.
aggfunc defaults to "mean". This Is worth knowing before it surprises you. A pivot table of sales that means when you expected a total looks fully plausible.
fill_value replaces the NaN in empty cells, often with 0.
margins=True adds row and column totals, labelled All.
A list for aggfunc gives a few statistics. A list for index or columns gives nested axes.
One wart, current as of pandas 2.2: "size" inside an aggfunclist raises AttributeError, while "count" in the same spot works. groupby(...).agg(["sum", "size"]) handles it without complaint. Is the fallback when you want a row count alongside other statistics. The gap between the two is the one from the group-by module. size counts rows, count counts non-missing values.
pivot_table is group-by with a nicer layout. Anything it does can be done with groupby plus unstack. It's worth using when the output is a table for a human.
id_vars are the columns to keep as identifiers. all else is unpivoted into two columns. One holding the old column names, one holding the values.
Name both outputs. The defaults are var and value. This Say nothing and have to be renamed later anyway.
value_vars restricts which columns are melted, when you want to unpivot some and keep others as identifiers.
This is the step for data that arrived from a spreadsheet with one column per month. A shape that is convenient to type and painful to find with.
stack and unstack
The same reshape expressed through the index.
stack() moves the innermost column level into the index, making the frame taller and narrower. unstack() moves the innermost index level into the columns.
These are what you reach for after a group-by with a few keys. Here The result already has a MultiIndex. groupby(["city","year"]).sum().unstack() gives the wide table right.
unstack(fill_value=0) fills the gaps, and unstack(level=0) chooses which level moves when there are more than two.
Which to work in
The hands-on advice is to keep data long and pivot at the end.
The reason is maintenance. In wide form, a new year means a new column. Each formula that named the old columns has to change. In long form it means new rows, and nothing changes at all.
Long form also composes with all else in pandas. Group-by, filtering, joins all assume one row per finding.
Pivot when the output is a table someone will read, a chart, or a file for a tool that expects wide. That is a display step. It belongs at the end of the pipeline rather than the middle.
Tidy data
The long form has a name and a meaning, from Hadley Wickham's tidy data:
Each var is a column. Each finding is a row. Each type of observational unit is a table.
Data that follows those rules composes with all: group-by, filtering, joins and most plotting libraries all assume it.
The common violations are worth recognising, because each has a standard fix.
Column headers are values, not var names. A column per year. Fix with melt.
a few vars in one column. A measure column holding both height and weight. Fix with pivot.
vars in both rows and columns. Fix with melt then pivot.
a few values in one cell. "a, b, c" in one field. Fix with str.split and explode.
explode
df.explode("tags") turns a column of lists into one row per element, repeating the other columns.
It's the tool for data that arrived with a few values per cell:
Each tag turns its own row, at which point it can be grouped and counted most times.
The inverse is a group-by with list or ", ".join as the aggregation.
explode makes copy index labels, so reset_index(drop=True) often follows.
pivot_table in more depth
Beyond the basics, three args do real work.
aggfunc accepts a dict keyed by column. Other values can be summarised differently in one table.
index and columns accept lists, giving nested axes. Region and city down the side, year and quarter across the top.
dropna=False keeps columns that are fully empty. This Matters when the table must have a fixed shape for compare.
margins_name renames the All row and column.
The output is a frame with a MultiIndex on one or both axes. The flattening step from the MultiIndex module often follows if the result is going anywhere other than the screen.
Reshaping and memory
Wide data with many empty cells is sparse. Pivoting long data into it can allocate far more than the input.
A long frame with 100,000 rows covering 5,000 users and 3,000 products pivots into a 15,000,000-cell table, most of it NaN. The long form held 100,000 values.
That is the main hands-on arg for staying long: the wide form materialises each mix, whether or not it occurred.
When a wide layout is truly needed for a sparse dataset, pivot_table with fill_value=0 at least avoids the float promotion that NaN forces. A sparse dtype or a other tool is worth considering past a certain size.
Which operation, by shape
Long to wide, no copies — pivot.
Long to wide, with aggregation — pivot_table.
Wide to long — melt.
Column level into index — stack.
Index level into columns — unstack.
Lists in cells into rows — explode.
A rate table of two vars. crosstab, or groupby plus unstack.
The solid way to choose is to write down the shape you've and the shape you want, in rows and columns. Pick the step that moves one to the other. Reshaping goes wrong when it's attempted by trial and error rather than by naming the target.
Reshaping for a chart
Most plotting expects one of two shapes. Knowing which saves a lot of trial and error.
df.plot() on a wide frame draws one line per column, using the index as the x-axis. That is why groupby(...).unstack().plot() works so neatly. Unstack makes just that shape.
Seaborn and similar libraries often want long data, with columns naming the var and the value. A hue= arg doing the splitting.
So the reshape before plotting depends on the library. It's often one call in either direction. Knowing which shape the function wants is faster than adjusting the data until the chart looks right.
Round-tripping
melt then pivot returns to where you started, given the identifier columns uniquely find a row. If they don't, pivot raises on copies. Which is a handy check that the identifiers are what you thought.
That round trip is a quick way to test an assumption about the data's grain. If pivot complains, the key you believed was unique isn't.
Column names after reshaping
pivot and pivot_table use the values of the columns arg as column names. The result's columns are data. That has two results.
They may not be valid identifiers. A year is an int, a product name may hold spaces. So df.column_name won't work and df["2024"] may need to be df[2024].
They change when the data changes. Code that names them breaks when a new group appears. This Is the maintenance arg for staying long.
rename_axis(columns=None) drops the axis name that pivoting leaves behind. This Else shows up as a stray label above the columns when printing.
A summary
Long is one row per finding. Wide is one row per subject.
pivot for long to wide with unique pairs. pivot_table when aggregation is needed.
melt for wide to long, naming both output columns.
stack/unstack do the same through index levels.
explode for lists in cells.
Wide materialises each mix, so it can be far larger than the long form.
Work long, pivot at the end, for display.
And when a reshape is confusing, write down the shape you've and the shape you want. The step follows from that. Trial and error rarely settles.
A closing note on Reshaping
Reshaping is the step people most often approach by trial and error. The one where that approach works worst.
The solid method is to write down the shape you've and the shape you want. What is a row, what is a column. And pick the step that moves between them. Long to wide is pivot, or pivot_table if any cell would hold more than one value. Wide to long is melt. Through the index instead of named columns, it's stack and unstack.
The default that catches people is pivot_table's aggfunc="mean". A table of sales that quietly means where you expected a total looks fully plausible, and nothing shows else.
And the structural advice is to stay long until the end. Wide data materialises each mix whether it occurred or not. Each new group changes the columns. And so each formula that names them.
One more thing
melt accepts ignore_index=False, which keeps the first index rather than renumbering. That matters when the identifiers you want to keep are in the index rather than in columns, since id_vars only names columns.
And wide_to_long handles the exact case of columns named with a stem and a suffix. sales_2023, sales_2024. Unpivoting them into a stem column and a suffix column in one call. This Is else a melt followed by a str.extract.
Questions people ask about Reshaping
What is the difference between pivot and pivot_table? pivot only reshapes and fails if a row and column pair repeats. pivot_table aggregates the duplicates with a function such as mean or sum.
What does melt do? It turns wide data into long data: several columns become two, one holding the old column names and one holding their values.
When do I need long format versus wide? Long format suits groupby, plotting libraries such as seaborn, and storage. Wide format is easier for humans to read and for matrix-style calculations.
Why does pivot raise 'Index contains duplicate entries'? Two rows share the same index and column combination, so pandas does not know which value to keep. Use pivot_table with an aggregation or remove the duplicates.
What is the difference between stack and unstack? stack moves column labels into the row index, and unstack moves an index level into the columns.
How do I fill empty cells in a pivot table? Pass fill_value=0 to pivot_table, or call fillna on the result.
Reshaping, in summary
Long form is one row per finding and composes with all. Wide form is one row per subject and reads better.
Convert with pivot and melt, or through the index with stack and unstack. Use pivot_table when a cell could hold more than one value. Remembering that its aggfunc defaults to mean.
Work long and pivot at the end. In wide form a new group is a new column. Each formula that named the old columns has to change.
Check yourself
0 of 4
Answer without scrolling back up.
Why does `pivot` raise on duplicate index/column pairs?
pivot_table aggregates instead. Note its aggfunc defaults to 'mean', which looks plausible when you expected a total.
What does `pivot_table`'s `aggfunc` default to?
Worth knowing before it surprises you - a pivot of sales that averages when you expected a total looks entirely reasonable.
What are `id_vars` in `melt`?
Name var_name and value_name too - the defaults are 'variable' and 'value', which say nothing.
Why keep data in long form and pivot only at the end?
In wide form a new year is a new column, and every expression naming the old ones has to change. Long form also composes with groupby and joins.
Cheat sheet
Reshaping: pivot and melt
Reshape a pandas DataFrame from long to wide with pivot_table and back again with melt. See when each shape is the right one for your task.
Ashish Jangra builds and maintains VizLearn. Every module here is written and the visualisation behind it hand-built, so the numbers in a readout come from the same code that draws the picture. Corrections are genuinely welcome and get priority over everything else — if a page states something wrong, or an animation misrepresents what the algorithm does, get in touch.