Turn columns into rows

A table with twelve month columns becomes a table with a month column and a value column, twelve rows per record. Pick the columns that identify a row and everything else is melted down into name-and-value pairs. This is the shape every charting library, database table and pivot tool actually wants, and almost no spreadsheet export arrives in.

Wide is for reading, long is for everything else

A wide table puts each period or category in its own column. It reads beautifully. Every report anyone has ever printed is wide, and so is every spreadsheet a human maintains by hand, because adding a column for the new month is the obvious thing to do.

Then you try to do something with it and everything objects. A charting library wants one row per point, with the series in a column. A database wants a fixed schema, and a table that gains a column every month is not one. A pivot tool wants a value column to aggregate, and cannot aggregate across twelve of them. A group-by wants to group by month, which is not a column that exists. Adding January's number to February's needs a formula naming both, and a formula naming both breaks in March.

Long format fixes all of that at once. One row per observation, the thing being measured named in a column, the number in another. It reads worse and it computes better, which is the whole trade. Pandas calls this melt, SQL calls it UNPIVOT, R calls it gathering, and they are all the same operation.

Worked example

Eight rows of sales, four month columns:

region,product,jan,feb,mar,apr
East,Widget,1200.50,1310.00,1288.25,1402.75
East,Gadget,980.00,1015.40,,1120.00
West,Widget,1105.25,1180.75,1240.50,1198.00
...

Mark region and product as identifiers, name the new columns month and amount:

region,product,month,amount
East,Widget,jan,1200.50
East,Widget,feb,1310.00
East,Widget,mar,1288.25
East,Widget,apr,1402.75
East,Gadget,jan,980.00
East,Gadget,feb,1015.40
East,Gadget,mar,
East,Gadget,apr,1120.00
...

Eight rows by six columns became thirty-two rows by four columns. The identifiers repeat down the output, which is exactly the redundancy that makes the format work: every row now stands on its own and can be filtered, grouped or charted without reference to its neighbors.

That empty cell for East Gadget in March is still there as a row with a blank amount. Turn Blank values to Drop and it disappears entirely, leaving thirty rows. Which one you want depends on what happens next: keep the blanks if the grid's completeness matters, drop them if you are about to compute an average and do not want to think about whether blanks are being counted.

Choosing the identifier columns

The identifiers are the columns that say which thing a row is about. Everything else is treated as a measurement of that thing and gets melted.

Left empty, the tool guesses by taking the non-numeric columns from the left of the file until it hits a numeric one. On a normal wide export that is precisely right: the labels are on the left and the numbers are on the right. It is wrong when your file has a numeric id in the first column, so check the guess before you download.

Getting this wrong is the one way to produce nonsense here, and the nonsense is obvious: if you forget to mark a label column as an identifier, it melts, and you get rows saying that product has a value of Widget sitting alongside rows saying jan has a value of 1200.50. The output has half as many useful rows and twice as many total. If the result looks like that, you have one identifier too few.

You can also name the value columns explicitly, which is the tidier option when a file has twelve month columns and three other columns you want left alone but not repeated either. Mark the identifiers, name the twelve, and the other three are simply dropped.

It round trips with the long-to-wide tool

Take the long output above, send it to the long-to-wide page, set the identifiers to region and product, the names from month and the values from amount, and you get the original grid back.

That pairing is more useful than it sounds. The common workflow is: unpivot the report so you can filter and group it properly, do the work, then pivot the result back into the wide shape whoever asked for it expects to read. Both directions are lossless as long as the identifier and name columns together are unique, which is the same condition the original grid satisfied by existing.

The one asymmetry: month columns come back in sorted order rather than calendar order, apr, feb, jan, mar rather than jan, feb, mar, apr, because the tool has no way to know those are months. Use 2024-01 style names if the order matters, which sorts correctly and is better for every other purpose too.

Details

Row order follows the input. All of the first row's values, then all of the second row's, in the column order they appeared. Nothing is sorted, so the output is predictable and diffable.
The new column names are checked for collisions. If your file already has a column called value, the new one becomes value_2 rather than colliding.
Values are moved as text. Leading zeros, trailing decimals and long ids all survive.
Melting everything is refused. Marking every column as an identifier leaves nothing to melt, so the tool says so rather than handing back an empty file.
No identifiers at all gets a warning. It will still run, but the output has no way of saying which row each value came from, which is almost never what anyone wants.

Frequently Asked Questions

What is the difference between unpivoting and transposing?

Transposing swaps the axes and keeps the same number of cells: an 8 by 6 table becomes 6 by 8. Unpivoting turns a grid of values into one row per value with its identifiers repeated beside it, so 8 by 6 with two identifier columns becomes 32 by 4. People often say transpose when they mean this. If your goal is a month column rather than twelve month columns, this is the tool.

Which columns should be identifiers?

The ones that say which thing the row is about, rather than what was measured. Ids, names, regions, categories, dates that label the record rather than a measurement period. Everything else melts. Left empty the tool takes the non-numeric columns from the left, which is right for most exports and wrong when the first column is a numeric id.

Should I drop the blanks?

Keep them if the completeness of the grid matters, or if something downstream expects every combination to be present. Drop them if you are about to aggregate, since a blank row contributes nothing to a sum and only complicates a count. The tool reports how many rows dropping removed, so the two versions are easy to compare.

Can I go back to the wide format afterwards?

Yes, that is what the long-to-wide tool is for, and the pair round trips exactly as long as the identifiers plus the name column are unique. The one thing that does not survive is column order: months come back alphabetically because nothing tells the tool they are months. Naming them 2024-01 rather than jan fixes it and sorts correctly everywhere else too.

Why is this called melt in some tools?

Pandas named its version melt, and the name stuck in the Python world. SQL Server and Oracle call it UNPIVOT. R's tidyverse calls it pivot_longer, having previously called it gather. All four are the same operation: wide grid in, name-value pairs out. The tidy-data literature calls the result long format, which is the term this page uses.

How big a file can I unpivot?

100 MB in. Remember that the output is bigger than the input in row count: a file with twelve value columns produces twelve times as many rows. The columns are far fewer, so the file size grows less than the row count suggests, but a 500,000-row wide file becomes a six-million-row long one and that is worth knowing before you start.

Melt the grid

One row per observation, which is the shape every other tool has been asking for.

Back to the wide-to-long tool