CSV pivot and unpivot

Data conversion

Loading tool

The tool is loaded only when you open it.

All processing for this tool happens in your browser. Your input is not sent to a server.

About this tool

Turn transaction rows into a region-by-month summary, or turn a wide monthly export into a long table with identifier, variable and value fields. Pivot accepts multiple row-key columns, one column-key column, multiple value columns and an explicit aggregation. Unpivot keeps selected identifiers and copies each selected value as its original parsed string. Repeated pivot coordinates are aggregated rather than silently overwritten. Exact decimal arithmetic avoids binary floating-point artifacts; unsupported numeric text produces an error, while a repeating average is returned as an exact reduced fraction such as 1/3 instead of a rounded value. All parsing, reshaping and downloads run in a local browser worker.

Common uses

Summarize a sales export by region and product, spread months across columns, and compute exact totals for selected amount and quantity columns.
Count nonempty observations by an exact category without changing identifiers such as 001, 1, or case-sensitive department codes.
Convert Jan, Feb and Mar columns to long-form variable/value rows for another reporting system while retaining account IDs, blank cells and original decimal spellings.

How to use it

1.Paste CSV text or select a local file. Choose its delimiter, header setting and supported file encoding, then inspect the columns. Select Pivot or Unpivot. In Pivot, choose row keys if needed, one column key, one or more value columns and an aggregation; these roles must not overlap. In Unpivot, choose identifiers to retain and value columns to expand, then name the variable and value output fields.
2.Review the selected fields before running the transformation. Keys are exact strings, with no automatic trimming, case folding or number conversion. Numeric aggregations accept only the documented decimal format. Unpivot retains empty and missing values by default; choose drop-empty only when discarding existing empty strings is intentional. Missing null values still remain. Editing the input or settings invalidates the old result.
3.Run the transformation, review its dimensions and paged result, then download JSON or CSV. Check output-column metadata when pivot categories or source headers contain punctuation. JSON distinguishes missing null values from empty strings and preserves string IDs. Keep CSV formula protection enabled for spreadsheet review; explicitly choose raw CSV only for a trusted destination. Pagination and shortened previews do not truncate a successful full export.

Worked before-and-after pivot and unpivot examples

Pivot exact sales totals by region and product

{
  "input": "region,product,month,amount,units\nNorth,001,Jan,0.10,2\nNorth,001,Jan,0.20,3\nNorth,001,Feb,1.50,1\nSouth,001,Jan,4,2\nNorth,1,Jan,2,1",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "region",
      "product"
    ],
    "columnKey": "month",
    "valueColumns": [
      "amount",
      "units"
    ],
    "aggregation": "sum"
  }
}

{
  "headers": [
    "region",
    "product",
    "pivot:[\"Jan\",\"amount\",\"sum\"]",
    "pivot:[\"Jan\",\"units\",\"sum\"]",
    "pivot:[\"Feb\",\"amount\",\"sum\"]",
    "pivot:[\"Feb\",\"units\",\"sum\"]"
  ],
  "rows": [
    [
      "North",
      "001",
      "0.3",
      "5",
      "1.5",
      "1"
    ],
    [
      "South",
      "001",
      "4",
      "2",
      null,
      null
    ],
    [
      "North",
      "1",
      "2",
      "1",
      null,
      null
    ]
  ]
}

The BEFORE input repeats North/001/Jan, so 0.10 + 0.20 becomes the exact string 0.3 and 2 + 3 becomes 5. Months are ordered by first appearance; each month has amount then units. North/001 and North/1 stay separate. Unobserved region/product/month combinations are null.

Count nonempty values, including text

{
  "input": "id,month,value\nA,Jan,yes\nA,Jan,\nA,Jan\nA,Feb,0\nB,Jan,",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "count"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"Jan\",\"value\",\"count\"]",
    "pivot:[\"Feb\",\"value\",\"count\"]"
  ],
  "rows": [
    [
      "A",
      "1",
      "1"
    ],
    [
      "B",
      "0",
      null
    ]
  ]
}

A/Jan counts yes but skips its empty and missing value fields; A/Feb counts the text 0. B/Jan exists with no nonempty observation, so count is "0". B/Feb has no source coordinate, so it is null. This is neither a distinct count nor a count of all source rows.

Compute an exact finite average

{
  "input": "id,month,value\nA,Jan,0.1\nA,Jan,0.2\nB,Jan,0.125\nB,Jan,0.375",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "avg"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"Jan\",\"value\",\"avg\"]"
  ],
  "rows": [
    [
      "A",
      "0.15"
    ],
    [
      "B",
      "0.25"
    ]
  ]
}

For A, (0.1 + 0.2) / 2 is exactly 0.15. For B, (0.125 + 0.375) / 2 is exactly 0.25. Output numbers are decimal strings; no binary floating-point approximation is introduced.

Find the numeric minimum with negative values

{
  "input": "id,month,value\nA,Jan,-3.20\nA,Jan,7.50\nA,Jan,-0.00",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "min"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"Jan\",\"value\",\"min\"]"
  ],
  "rows": [
    [
      "A",
      "-3.2"
    ]
  ]
}

The numeric minimum of -3.20, 7.50 and -0.00 is -3.2. Aggregation normalizes decimal spelling and compares exact numeric values rather than sorting their text.

Find a maximum beyond JavaScript’s safe integer range

{
  "input": "id,month,value\nA,Jan,9007199254740992\nA,Jan,9007199254740993",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "max"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"Jan\",\"value\",\"max\"]"
  ],
  "rows": [
    [
      "A",
      "9007199254740993"
    ]
  ]
}

The two large integers remain distinct during BigInt-backed comparison. The exact maximum is the string 9007199254740993; it is not rounded to the adjacent representable JavaScript Number.

Keep a repeating average as an exact fraction

{
  "input": "id,month,value\nA,Jan,1\nA,Jan,0\nA,Jan,0",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "avg"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"Jan\",\"value\",\"avg\"]"
  ],
  "rows": [
    [
      "A",
      "1/3"
    ]
  ]
}

The requested average is 1/3, which has no finite decimal expansion. AFTER contains the exact reduced fraction string "1/3", without rounding or binary floating-point conversion. Retain JSON when this representation matters. A fraction result is not eligible decimal input for a later numeric aggregation.

Reject a zero-prefixed identifier as a number

{
  "input": "id,month,value\nA,Jan,001",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "sum"
  }
}

{
  "error": "numeric"
}

The value 001 is not eligible for numeric sum. It produces numeric instead of becoming 1. Such text can still be used as a key, counted as a nonempty value, or copied unchanged by unpivot.

Unpivot before and after without changing raw values

{
  "input": "id,Jan,Feb\n001,1.00,\n1,,null\n002,0\n003, ,=1+1",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "unpivot",
    "idColumns": [
      "id"
    ],
    "valueColumns": [
      "Jan",
      "Feb"
    ],
    "variableName": "month",
    "valueName": "amount",
    "dropEmpty": false
  }
}

{
  "headers": [
    "id",
    "month",
    "amount"
  ],
  "rows": [
    [
      "001",
      "Jan",
      "1.00"
    ],
    [
      "001",
      "Feb",
      ""
    ],
    [
      "1",
      "Jan",
      ""
    ],
    [
      "1",
      "Feb",
      "null"
    ],
    [
      "002",
      "Jan",
      "0"
    ],
    [
      "002",
      "Feb",
      null
    ],
    [
      "003",
      "Jan",
      " "
    ],
    [
      "003",
      "Feb",
      "=1+1"
    ]
  ]
}

Jan and Feb become month rows in source order. The ID 001, decimal spelling 1.00, empty string, literal text null, missing Feb value, whitespace and formula-like text all retain their parsed value. Formula-like text is not executed here; protected CSV changes risky fields, while JSON preserves them.

Drop only existing empty strings during unpivot

{
  "input": "id,Jan,Feb\n001,1.00,\n1,,null\n002,0\n003, ,=1+1",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "unpivot",
    "idColumns": [
      "id"
    ],
    "valueColumns": [
      "Jan",
      "Feb"
    ],
    "variableName": "month",
    "valueName": "amount",
    "dropEmpty": true
  }
}

{
  "headers": [
    "id",
    "month",
    "amount"
  ],
  "rows": [
    [
      "001",
      "Jan",
      "1.00"
    ],
    [
      "1",
      "Feb",
      "null"
    ],
    [
      "002",
      "Jan",
      "0"
    ],
    [
      "002",
      "Feb",
      null
    ],
    [
      "003",
      "Jan",
      " "
    ],
    [
      "003",
      "Feb",
      "=1+1"
    ]
  ]
}

Only the empty Feb for 001 and empty Jan for 1 are removed. The absent Feb value for 002 remains null. Whitespace and literal null text also remain. This explicit option does not silently erase missing observations.

Keep empty and missing category keys separate

{
  "input": "id,month,value\nA,,1\nA\nB,Jan,2",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "id"
    ],
    "columnKey": "month",
    "valueColumns": [
      "value"
    ],
    "aggregation": "sum"
  }
}

{
  "headers": [
    "id",
    "pivot:[\"\",\"value\",\"sum\"]",
    "pivot:[null,\"value\",\"sum\"]",
    "pivot:[\"Jan\",\"value\",\"sum\"]"
  ],
  "rows": [
    [
      "A",
      "1",
      null,
      null
    ],
    [
      "B",
      null,
      null,
      "2"
    ]
  ]
}

The empty month string and missing month field create different pivot columns. A has an empty-category sum of 1, and its missing-category record has no numeric value, so that aggregate is null. B has only Jan. Metadata records each category as the exact string or null.

Disambiguate a generated header that matches a row key

{
  "input": "\"pivot:[\"\"Jan\"\",\"\"amount\"\",\"\"sum\"\"]\",month,amount\n001,Jan,2",
  "parseOptions": {
    "delimiter": ",",
    "header": true
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "pivot:[\"Jan\",\"amount\",\"sum\"]"
    ],
    "columnKey": "month",
    "valueColumns": [
      "amount"
    ],
    "aggregation": "sum"
  }
}

{
  "headers": [
    "pivot:[\"Jan\",\"amount\",\"sum\"]",
    "pivot:[\"Jan\",\"amount\",\"sum\"] [2]"
  ],
  "rows": [
    [
      "001",
      "2"
    ]
  ]
}

The original row-key header is itself pivot:["Jan","amount","sum"]. It is kept unchanged; the generated aggregate header receives the suffix " [2]". The JSON columns metadata remains the authoritative mapping of source field, category and aggregation.

Pivot a headerless semicolon-delimited input

{
  "input": "001;Jan;0.1\n001;Jan;0.2",
  "parseOptions": {
    "delimiter": ";",
    "header": false
  },
  "options": {
    "mode": "pivot",
    "rowKeys": [
      "column1"
    ],
    "columnKey": "column2",
    "valueColumns": [
      "column3"
    ],
    "aggregation": "sum"
  }
}

{
  "headers": [
    "column1",
    "pivot:[\"Jan\",\"column3\",\"sum\"]"
  ],
  "rows": [
    [
      "001",
      "0.3"
    ]
  ]
}

With header:false, the first row is data and positional names are column1, column2 and column3. Two rows for identifier 001 and month Jan sum to 0.3. The delimiter setting is explicit; no format guessing is needed.

Common pivot and unpivot mistakes

Using only product as a row key when the product is unique only within a region; select both keys.
Counting source rows or distinct values when the selected count operation actually counts present nonempty values.
Assuming 001 and 1 are the same key, or expecting exported CSV to prevent spreadsheet type inference.
Selecting identifier columns for numeric aggregation or expecting scientific notation and localized number separators to be accepted.
Treating an exact fraction average such as 1/3 as a rounded decimal or as eligible decimal input to another numeric aggregation.
Dropping empty values before checking whether missing observations carry meaning in the destination system.
Expecting an aggregated pivot to be reversible to its individual source records.
Opening untrusted raw CSV in a spreadsheet or sharing a result without checking for private data.

Limits and notes

Input is capped at 2 MiB of encoded file bytes and 2 MiB of decoded UTF-8 text, 10,000 data rows, 128 columns and 200,000 cells after padding missing trailing fields. Output is capped at 50,000 rows, 256 columns including row keys, 500,000 cells and 12 MiB for each JSON or formula-protected CSV representation. Distinct row-key groups are limited to 10,000 and column-key groups to 256; the total output-column limit may be reached earlier. Dense row-group × category × value-column growth is checked before output rows are materialized. Exceeded limits block transformation, not silently truncate it. Worker cancellation and a deadline bound execution, but are not a hard browser memory sandbox.
Keys and unpivot identifiers remain exact strings: 001 and 1, A and a, empty strings and missing null fields are distinct. No trimming, case folding, number/date conversion, Unicode normalization, fuzzy matching or formula evaluation occurs. Composite keys use tuple identity. Row keys, the column key and value columns must have disjoint roles. With no row keys, all records share one row group; with no unpivot identifiers, only variable/value columns are emitted. Pivot rows and categories use first-seen order; selected value fields retain selection order. Unpivot uses input row order and selected value-column order. No automatic sorting occurs.
Count counts present nonempty values, including arbitrary text; it is not a distinct count or an unconditional row count. Numeric aggregations skip only null and "" and require optional minus, 0 or a nonzero-leading integer, optionally followed by a point and fraction digits. At most 100 decimal digits total and 50 fractional places are accepted. Whitespace, plus signs, exponent notation, grouping separators, currency and zero-prefixed integers are rejected. BigInt gives exact sum/min/max and average strings, even beyond JavaScript’s safe integer range. Finite averages use normalized decimals; repeating averages use exact reduced fractions such as 1/3. Decimal output has the same precision cap; a fraction’s numerator and denominator are each limited to 100 digits. Trailing fractional zeros and negative zero are normalized. Excess precision fails the whole transformation. Fraction outputs are strings, not accepted decimal input for a later numeric aggregation. An existing all-blank coordinate is "0" for count and null for numeric aggregates; a coordinate never present in the input is null for every aggregation.
Pivot labels use pivot: followed by a JSON tuple of category value, value-column name and aggregation. A collision with a selected row-key header receives a deterministic suffix such as " [2]"; JSON column metadata preserves the exact mapping. Do not split labels by guessed punctuation. Unpivot retains missing null, existing "" and literal "null" text by default. Drop-empty removes only existing empty strings; missing fields, whitespace and the word null remain. Output variable/value names must be nonblank, distinct and different from retained ID headers. Aggregation, normalization of numeric output and dropping empty strings lose information: unpivot cannot reconstruct original transaction rows from an aggregate.
Choose comma, semicolon, tab or pipe explicitly, and UTF-8, UTF-16LE or UTF-16BE for files. Invalid encoding or a mismatched BOM is rejected. Headers must be nonblank and exactly unique; original spacing/case remains significant. Headerless names are column1, column2 and so on, with width set by the first record. Short rows retain missing trailing values as null; extra-wide rows and malformed quoting are rejected. Preview shows 25 rows per page and 12 columns, clipping long names/values; downloads retain the complete bounded result. JSON preserves result strings, nulls and metadata, not original CSV bytes. CSV writes null as an unquoted empty field and "" as a quoted empty field, but ordinary readers often merge them. Formula protection adds apostrophes and changes risky headers/values; raw CSV may activate formulas, and protection is not universal after saving and reopening. Spreadsheet type inference can still alter IDs. Inputs stay local without saved history; downloaded files may expose private data and survive clearing the page.

Frequently asked questions

How are several rows at the same pivot coordinate handled?

They belong to one aggregate for the complete row-key tuple, column-key value and selected value field. Choose count, sum, average, minimum or maximum explicitly. For example, amounts 0.1 and 0.2 produce the exact sum string 0.3. The tool never silently selects the first or last source row. Use additional row keys when records should remain separate.

Why is my amount rejected, or my average shown as a fraction?

Numeric aggregation is deliberately strict. Identifier-like leading zeros, whitespace, a plus sign, exponent notation and unsupported precision are not guessed into numbers. Select only intended numeric columns or clean a separate copy first. An average such as (1 + 0 + 0) / 3 has no finite decimal representation, so the exact reduced fraction string 1/3 is returned. The tool never silently rounds. Decimal results and fraction components have explicit precision limits. Count and unpivot can retain nonnumeric text without numeric coercion.

Will pivot followed by unpivot recover my original CSV?

Not in general. Pivot combines duplicate coordinates, may normalize eligible number spellings and creates cells for missing combinations. Unpivot copies the selected wide cells into long rows, but cannot recover individual transactions, their original ordering or discarded columns. Keep the original input. JSON preserves the reshape result’s strings, nulls and column metadata, not the original quoting or file bytes.

What is the difference between empty, missing and the text null?

An existing zero-length cell is an empty string; a trailing field not present in a short source record is missing; the characters null are ordinary text. JSON keeps those as "", null and "null". Unpivot retains all three by default. Drop-empty removes only existing zero-length strings, preserving missing null fields. Numeric aggregates skip missing and empty values, while the literal text null is an invalid numeric value. Count includes that nonempty text.