Online Tool Store
· 4 min read
How to Pivot Data Between Long and Wide
Manesh Jayawardhana
CIO & Co-founder
The data arrives long: one row per person per metric, three hundred rows for a hundred people. The report needs it wide: one row per person, one column per metric.
Reshaping is a standard operation with one failure mode that loses data without saying so.
Which format for which purpose
Long format — one row per observation, with a column naming what was measured and a column holding the value.
Better for storage. Adding a new metric adds rows and changes no schema. Most statistical software expects it, and it handles sparse data naturally — a person missing one metric simply has no row for it.
Wide format — one row per subject, one column per metric.
Better for reading. A human comparing four metrics across a hundred people wants them side by side, and spreadsheets are built around this shape.
The usual pattern is long for the database and wide for the output, with a pivot in between. Neither is correct in general; they are correct for different jobs.
| Aspect | Long | Wide |
|---|---|---|
| Adding a metric | Add rows | Change schema |
| Human readability | Poor | Good |
| Sparse data | Natural | Produces empty cells |
| Statistical tools | Usually expected | Often needs reshaping |
The duplicate key problem
Going long to wide, each combination of identifier and key becomes one cell. If the source has two rows with the same identifier and key, both cannot occupy that cell.
What happens next depends on the tool, and most choices are bad:
Last value wins, silently. The most common default and the most dangerous, because nothing indicates data was discarded.
First value wins, silently. Same problem, different direction.
Aggregate — sum, mean or count. Sometimes exactly right, and wrong if the duplicates were an error rather than genuine repeated measurements.
Flag and stop. The only option that tells you something is wrong.
Duplicates in a pivot are almost always a signal. Either the data has an extra dimension nobody accounted for — a timestamp, a location, a version — or there is a genuine duplication upstream. Both are worth knowing about before the report goes out.
Going wide to long
The reverse is less dangerous and has its own decision: which columns are identifiers and which are values.
Get that wrong and identifier columns get melted into the value column, producing rows saying that a person’s “name” metric has the value “Ada”. It is obvious once seen and easy to do on a wide file with many columns.
Empty cells in the wide format become either missing rows or rows with null values, and which you want depends on whether absence is meaningful. For most analysis, omitting the row is cleaner.
Column names come from data
A consequence of pivoting that catches people building pipelines.
In a long-to-wide pivot, the output columns are determined by the values in the key column. Add a new metric to the source data and the output gains a column; remove one and it loses a column.
That means the output schema is not fixed. Anything downstream expecting a specific set of columns breaks when the source data changes, and it breaks with a missing-column error rather than anything descriptive.
Two defences: pivot against an explicit list of expected keys rather than whatever is present, so unexpected values are flagged rather than silently added; or keep the long format as the interface and pivot only at the point of presentation.
Common mistakes to avoid
- Pivoting without checking for duplicate key combinations.
- Accepting a tool’s silent last-value-wins default.
- Melting identifier columns into the value column.
- Pivoting a column with high cardinality, which produces hundreds of mostly-empty columns.
- Treating the wide output as the source of truth after reshaping, rather than keeping the long original.
How to do it with Long to Wide Format Converter
The Long to Wide Format Converter reshapes in the browser and flags duplicates.
- Paste the data with its header row.
- Choose the direction and identify the key and value columns.
- Read the duplicate report before using the output — that is where data goes missing.
- Keep the long version as the source of truth.
Other data tools that keep files local are in the tools directory.
Frequently asked questions
Which format should my data be stored in?
Long, generally. Adding a metric adds rows rather than changing the schema, sparse data is handled naturally, and most statistical tools expect it. Wide is for presentation.
What happens to duplicate keys?
They are flagged rather than resolved silently. Two rows with the same identifier and key cannot both occupy one cell, and a tool that quietly keeps the last one is discarding data without telling you.
Is anything uploaded?
No. Reshaping happens in the browser, so the data stays on your device.
Final thought
Read the duplicate report. A pivot that silently drops rows produces a clean-looking table that is quietly missing data, and nothing downstream will tell you.