Pivot a TSV (Long → Wide)
A pivot table without the spreadsheet. Your data has one row per observation — region, month, revenue — and you want a grid with regions down the side and months across the top. Name the three fields, choose how to combine values that land in the same cell, and that's the whole operation. The reverse is unpivot.
How to use
- Paste or drop long-format data — one row per observation, with the header on row one.
- Rows: the field you want down the left side. Comma-separate for a two-level row index.
- Columns from: the field whose distinct values become the column headers. Keep an eye on its cardinality — 200 distinct values means 200 columns.
- Values: the field to put in the cells. Not needed if you're combining with count.
- Set a fill like
0if empty cells should be zeros rather than blanks — that matters if the result is going into a chart.
Why the aggregate is not optional
A pivot has to decide what happens when several input rows land in the same cell — same region, same month, two revenue figures. Spreadsheet pivot tables default to summing and don't tell you they did. Here it's an explicit choice, and the one to reach for when you're unsure is count: if every cell shows 1, there was exactly one row per combination and any aggregate would give the same answer. Cells showing more than 1 tell you the aggregate is doing real work, and you should be sure it's the right one.
Column values are sorted naturally, so numeric-looking columns come out in numeric order (2 before 10) rather than as strings. Row groups appear in first-seen order; sort them beforehand with sort if you want a specific order down the side.
When to pivot and when not to
Pivot for human reading: a cross-tab is far easier to scan than a long list, and it's the right shape for a chart with one series per column. Don't pivot for storage or further processing — long format is what databases, plotting libraries, and every join expect. If you find yourself pivoting and then needing to filter or join, do that work in long format first and pivot as the last step.
FAQ
Can I have two levels of columns?
Not directly, but joining the two fields first with concatenate columns gives you composite headers like 2026-Q1, which is usually more readable in a TSV than a real two-level header would be — TSV has no way to express a merged header cell anyway.
Where are the row and column totals?
Not generated — a totals row in a data file tends to get accidentally included in later aggregations, which is worse than adding it yourself in a spreadsheet at the end. If you need the totals as data, group and aggregate gives you them as a proper table.
The output has hundreds of columns.
Your columns from field has high cardinality — probably an ID or a timestamp rather than a category. Check with value counts; if it's a date, coarsen it to a month first with reformat dates.
What's the pandas equivalent?
df.pivot_table(index='region', columns='month', values='revenue', aggfunc='sum'). This page is the same operation with the arguments as form fields.
Privacy
100% client-side. No upload. See the privacy policy.