Skip to main content

Table-reshape problem

Transpose or Pivot a CSV? Choose the Correct Reshape

Determine whether your data needs a mechanical transpose or a grouped pivot before changing its rows and columns.

Use the matching tool

Pivot Table

Open Pivot Table

What the error actually means

Transpose rotates every cell, turning rows into columns. Pivot groups records by keys and calculates or places values into a new layout. Choosing transpose for transactional data often creates thousands of unusable columns instead of the desired summary.

Likely causes

  • The desired output was described only as “make rows columns.”
  • Repeated records need aggregation.
  • The source lacks a unique key-variable combination.
  • A spreadsheet example hides the underlying grouping rule.

Reshape intent

Problem

Transpose sales transactions to get totals by month

Correct pattern

Pivot by month and category with an explicit sum aggregation

A safe repair workflow

  1. 1Write the intended output grain in one sentence.
  2. 2Identify row keys, new column categories and value fields.
  3. 3Choose an aggregation for duplicate combinations.
  4. 4Test on a small sample and reconcile totals.

How to verify the result

A file that downloads successfully is not automatically a correct file. Check the result at both the structural and business-data levels:

  • Each output row represents the intended entity.
  • Duplicate combinations use the approved aggregation.
  • Grand totals reconcile.
  • The output dimensions fit the destination.

Read the deeper guides

How to Transpose CSV Data Without Losing the HeadersSwap CSV rows and columns in Excel, Python, or a browser, with practical checks for headers, uneven rows, data types, and file-size limits.How to Clean CSV Data Without Hiding the Original ProblemsLearn practical techniques to clean messy CSV files. Remove duplicates, fix formatting, and standardize data without Excel crashes or complex scripts.