Reshaping Data

The executive question: why does the same data need different shapes for spreadsheets, dashboards, and models?

Two shapes: wide and long

The same numbers live in two canonical shapes. Wide format puts one row per unit and spreads time, category, or measurement across the columns. Long format puts one row per unit-period (or unit-category, or unit-measurement) and uses a small fixed set of columns. Either shape can describe the same business reality. Neither is more correct than the other. They are useful for different things.

Wide is the natural shape for a human reading a spreadsheet. A regional director who opens last quarter's revenue file expects to see store names down the left, months across the top, and a number at each intersection. That arrangement compresses three pieces of information per cell — store, month, revenue — into a position-encoded grid that a reader can scan visually. Adding a fourth month means adding a column, which is fine for a small grid but a schema change for the underlying file: anything downstream that referenced sales_2024_03 will need to learn about sales_2024_04.

Long is the natural shape for almost everything that is not a human reading a spreadsheet. A line chart needs one column for time, one for the value, and (if there are multiple lines) one for the series. A regression needs one row per observation, with the predictors as fixed-name columns. A dashboard filter — "show me Store A, Q1 only" — needs the store and month to be values inside the table, not column headers. In all three cases, the wide grid has to be unpivoted first. The same nine numbers, the same business reality, two shapes, and the long shape is the one that connects to the rest of the analytical pipeline. Figure 1 shows the same Bean & Basket quarter both ways.

Figure 1 makes the trade-off concrete. The wide tab is easy for the eye. The long tab is easy for the machine — by which we mean the chart engine, the statistical model, the dashboard filter, the join to another time-indexed table. In R, the verbs that move between these shapes are pivot_wider() and pivot_longer(); in Python's pandas they are .pivot() and .melt(); in SQL they are sometimes PIVOT / UNPIVOT and more often a UNION ALL of one query per column. The mechanics differ; the conceptual move is the same: take information that is encoded as column position and turn it into information encoded as cell value, or vice versa.

A nine-cell teaching table makes the shapes clear; a real file makes the stakes clear. Figure 2 is Zillow's state home-value extract, which arrives with one column per month.

Store it long, display it wide

The rule of thumb is to store data in long form, display it in wide form. The long form is the database-friendly shape: it is extensible (adding a period is a row, not a schema change), it is the natural input to plotting and modeling, and it is the format that joins cleanly to other long-form tables. The wide form is the presentation-friendly shape: at the very end of an analysis, when a manager wants to see a grid on a slide, you pivot the long form wider. The work of analysis happens in long; the work of communication happens in wide.

The opposite mistake — long when wide is wanted — is much rarer and almost always cheap to fix. A manager who wants to see the three months side by side, with stores down the side, is asking for a wide presentation table. Pivoting nine long rows back to three wide rows is one verb in any modern data tool. The cost of starting from long and pivoting wider when needed is essentially zero. The cost of starting from wide and pivoting longer every time something downstream needs it is paid every analysis.

Which shape to store is the only part of this that is a decision, and Figure 3 settles it by asking who the consumer is.