Unpivot a TSV (Wide → Long)
You've been handed a spreadsheet with a column per month. Nothing can plot it, no database wants it, and adding a month means adding a column. Unpivoting — melting, in R and pandas terms — turns those twelve columns into two: one holding the month name, one holding the value. Name the columns to keep as identifiers and everything else is melted. The reverse is pivot.
How to use
- Paste or drop the wide file.
- List the identifier columns — the ones that describe what each row is, and should be repeated on every output row.
- Rename the two output columns if
variableandvaluearen't meaningful.monthandrevenueusually are. - Leave skip empty values on unless the absence of a value is itself data you need to keep.
What the output looks like
Input with 100 rows and 12 melted columns produces up to 1,200 output rows — one per non-empty cell. The identifier values repeat down the file, which looks wasteful and is exactly right: it's what makes each row independently meaningful. That's the property every downstream tool relies on.
Melted column names come through as data in the name column, so a header like Jan-26 becomes the literal string Jan-26 in every row it produced. If those headers are dates you want to work with, run the output through reformat dates on the name column to get ISO values you can sort and filter.
Why long format is worth the trouble
Adding a new period means adding rows, not restructuring the file. Filtering to a subset is a row filter rather than a column selection. Every plotting library wants one row per point. SQL can group and aggregate it without listing columns by name. And joins work — a wide table can't be joined on its column headers, because headers aren't data.
The one place wide beats long is human reading, which is why pivot exists as the last step. Keep the long version as the source of truth and pivot for presentation.
FAQ
What if I leave the ID field blank?
Every column gets melted and the output has just the name and value columns — a flat list of every cell in the file with its column name. Occasionally useful for auditing, rarely what you want otherwise.
Can I melt only some of the non-ID columns?
Indirectly: drop the columns you don't want with delete columns first, or extract just the ID columns plus the ones to melt with extract columns, then unpivot. Keeping the selection in one field would make the interface ambiguous about which list wins.
Does it round-trip with pivot?
Yes, if you unpivot and then pivot back with the same fields and a first aggregate. What doesn't survive is empty cells you skipped, and the column order, which pivot sorts naturally.
What's the pandas equivalent?
df.melt(id_vars=['region'], var_name='month', value_name='revenue'), or tidyr::pivot_longer() in R.
Privacy
100% client-side. No upload. See the privacy policy.