Long vs wide data in pandas: pivot_table, melt and crosstab

The idea

Any table of numbers can be shaped two ways. In a long table every row is one observation, and the things that describe it (channel, status, year) each get a column. In a wide table some of those descriptions have moved into the column headers, so one row holds several observations side by side.

Neither is better. They are for different readers. Long is for computing: groupby, merge, query and charts all want to point at a column name, and a long table has one column for each thing you might group by. Wide is for reading: a grid with channels down the side and statuses across the top puts the comparison in front of your eyes.

Reshaping is the move between the two. It does not add information and does not remove any. The same numbers sit in different places, so the row count and the column count change while the total stays put.

Figure 1

Same six numbers, two shapes: orders by channel and status

Long: groupby result
channel	status	orders
app	cancelled	265
app	completed	3885
marketplace	cancelled	108
marketplace	completed	1597
web	cancelled	353
web	completed	5367

Wide: pivot_table grid
channel	cancelled	completed
app	265	3885
marketplace	108	1597
web	353	5367

Step 1 of 5 A groupby(['channel', 'status']).size() gives a long table: one row per combination, with the number in its own column. Computers like this shape.

A groupby(['channel', 'status']).size() gives a long table: one row per combination, with the number in its own column. Computers like this shape.
index='channel' says what stays as rows. The channel values are kept and each one is written once.
columns='status' turns the values of status into column headers. cancelled and completed stop being cells and become the top of the grid.
Each long row drops into the cell where its row label and its column label cross. The app's 265 cancelled and 3,885 completed orders land side by side.
Same six numbers, three rows instead of six. A person can compare channels at a glance. A groupby or a chart still wants the long shape.

Why it exists

A groupby on two columns gives you 21 combinations, one under the other. That is the correct answer and a terrible thing to put in front of a CFO. Meanwhile, the spreadsheet Marketing sends you has a column per channel, and you cannot group by "channel" because it is not a column.

The same data keeps changing hands between people who need different shapes. Analysts compute in long, managers read in wide, and an export from another tool can arrive in either. Knowing the two shapes, and the one-line moves between them, means you stop copying numbers by hand.

There is a practical test for which shape you have. Ask whether a value you want to group, filter or colour by is in a column or in a header. If it is in a header, the table is wide in that respect, and you reshape before you do anything else. Once the answer is a column name, most pandas code writes itself.

How it works under the hood

Long to wide: choose three things

To go wide you answer three questions. Which column becomes the rows? Which column's values become the column headers? What goes in each cell? pivot_table(index=..., columns=..., values=..., aggfunc=...) is those answers in order.

Figure 2

Reading a pivot_table call

orders long: one row per order
index='channel' becomes the row labels
columns='status' becomes the column headers
values='order_id', aggfunc='count' fills each cell
The grid empty cells are NaN

Step 1 of 4 pivot_table starts from a long table with one row per observation, like orders.

pivot_table starts from a long table with one row per observation, like orders.
index and columns pick which two columns define the axes of the grid.
values and aggfunc say what goes in a cell. Several rows can fall in the same cell, and aggfunc is how they are combined: a count, a sum or a mean.
A combination that never occurs leaves an empty cell, NaN. fill_value=0 writes 0 when zero is the true answer.

The fourth argument is what makes it pivot_table and not pivot. When several long rows land in one cell, someone has to combine them. aggfunc='sum' adds, 'count' counts, 'mean' averages. The plain pivot has no aggfunc and raises an error when two rows want the same cell, which is useful when you expected exactly one.

Cells that no row reached are NaN, not zero. A status that never occurs in a certain channel has no row at all, so the grid has a hole. fill_value=0 fills it, and it is the right choice for counts, where no rows really means zero. For an average, an empty cell means "no data", and filling it with 0 would say the average is zero.

Wide to long: `melt`

melt is the opposite move. id_vars are the columns that stay, var_name is the name of the new column holding the old headers and value_name names the column holding the cells.

Figure 3

melt: a year per column becomes a year per row

Wide: one column per year
channel	y2023	y2024	y2025
app	819	1366	2135
marketplace	464	601	702
web	1888	2079	1946

Long: after melt
channel	year	orders
app	y2023	819
app	y2024	1366
app	y2025	2135
marketplace	y2023	464
marketplace	y2024	601
marketplace	y2025	702
web	y2023	1888
web	y2024	2079
web	y2025	1946

Step 1 of 4 A sheet from a colleague: one column per year. It reads well, but you cannot groupby('year') because year is not a column, it is three of them.

A sheet from a colleague: one column per year. It reads well, but you cannot groupby('year') because year is not a column, it is three of them.
melt(id_vars='channel', var_name='year', value_name='orders') keeps channel as it is and stacks the three year columns into two: the old header goes into year, the cell goes into orders.
Every row of the wide sheet unfolds into three rows. Nine numbers in, nine numbers out: only the shape changed.
Now year is a column. You can group by it, merge on it, or hand it to a chart as the x axis.

Use it whenever the headers are really values: years, months, channels. A tell-tale sign is that you want to loop over the columns, or you catch yourself writing the same line five times with a different column name.

The index versions: `unstack` and `stack`

A groupby with two keys returns a Series with a two-level index. unstack() moves the inner level into the columns, which is a pivot done on the result of a groupby, with no aggfunc needed because the grouping already aggregated. stack() does the opposite and moves the columns back into the inner index level. It is the same move as pivot_table and melt, on a table whose labels are in the index.

Shares: `crosstab`

pd.crosstab(df.channel, df.status) counts the combinations straight away, without a values column. Add normalize='index' and each row sums to 1, so you read the status mix inside every channel as a share. Choosing 'index', 'columns' or 'all' decides what the percentages are of: the row, the column or the grand total. State it in the heading of the table, because 12% of what is the whole question.

One more reshape: `explode`

Some columns hold a list in a cell, such as a product's tags. explode gives each list item its own row and repeats the other columns, so it creates rows like a merge does. Counting products after an explode counts tags, not products. The same care as a fan-out applies.

Common misconceptions

"A pivot table changes the data"

It rearranges it. Add up the cells of a count pivot and you get the number of rows you started with: the six numbers in the diagram sum to 11,575 in both shapes. If a reshaped table's total differs from the original, a filter, an aggfunc or a dropna took something out. That check costs one line.

"Wide is the proper shape and long is the messy one"

Long is the shape pandas and most charting tools prefer. A table with a column per year is easy to read and hard to analyse: adding 2026 means adding a column and changing code. A long table just gets more rows. Think of wide as a presentation layer made at the end.

"Empty cells in a pivot mean zero"

They mean no rows reached that cell. For a count that is the same as zero. For a mean, rate or average price it is "unknown", and fillna(0) would drag every later calculation down. Decide per table whether a missing cell is a zero or an unknown, then fill it or leave it.

Check yourself

One question. Answering it ticks this lesson off on the map; it never locks a level.

A sheet has one row per channel and one column per month, 36 month columns. You want the monthly average across all channels as a chart with months on the x axis. What do you do first? pivot_table it, so the months become rows. melt it, so each month becomes a row with a month column and a value column, then group by month. Nothing: a wide sheet already works with groupby('month'). crosstab it, so the months are counted.