A table with one row per measurement is widened to one column per measure. Which three roles must its columns be assigned?

## Two layouts, one pair of operations The same numbers can sit in a rectangle two ways. In **the long layout** there is one row per measurement: a few columns say which subject and which moment the row is about, one column holds the *name* of the thing measured, and one column holds its number. In **the wide layout** there is one column per measure, and those identifying columns appear once per subject rather than once per measurement. **A widening** goes from the first arrangement to the second: it turns one column's distinct values into new column headers and fills them from a second column. **Folding back** is its inverse: it turns a set of headers into one column holding the measure's name and one column holding its number. Neither operation can be read off the table. Both are declarations *you* write, and the declaration has exactly three parts. ## The three roles - **The identifying columns.** These say which subject and which moment a row is about, and they stay put through the conversion. After the widening they are the row key: the result has one row per distinct combination of their values. Choosing them *is* choosing the grain of the output, which is why they are a decision and not leftovers. - **The header source.** One column, whose distinct values become the new column headers. This is the column whose contents stop being data and become schema. - **The cell source.** The column whose entries are placed inside the new cells. In the simplest case there is exactly one; some tools accept more than one, which changes how the resulting headers are named. One more thing is implied rather than named: the pairing rule. Each input row contributes one cell, at the intersection of its row key and its header. The operation leans on each such pair occurring once, and that precondition is worth saying out loud even though nothing in the declaration states it. ## Every input column has to land somewhere The declaration is total over the input's columns, and this is where people are caught out: 1. A column named as an identifying column joins the row key, so the result's row count is the number of distinct combinations of those columns — not the input's row count. 2. A column named as the header source disappears as a column and reappears as a set of headers. 3. A column named as the cell source disappears as a column and reappears inside the cells. 4. A column named in none of the three still has to be disposed of, and **what happens then varies by design**: some tools treat every unnamed column as another identifying column, which silently widens the row key and hands you more rows than you expected; others discard it. Do not rely on which behaviour you have — assign a role to every column deliberately. ## Reading the declaration backwards | what you declare | in a widening | in a fold back | |---|---|---| | the identifying columns | stay put; become the row key | stay put; repeat once per measure | | the header source | its distinct values become the headers | the headers supply the name column's values | | the cell source | fills the new cells | the cells supply the value column | A fold back is the same three-part declaration read from right to left. You say which columns to keep; everything else is folded, and the folded columns' *labels* go into one column while their *contents* go into another. There is one asymmetry worth stating. A widening invents no column names of its own — it takes them from data. A fold back does invent two: a column holding the measure's name and a column holding its number. **Whether a tool supplies default names for that pair, and which words it uses, varies.** Name both explicitly. The alternative is a pipeline whose next step refers to a column that does not exist, failing at run time one step after the step that caused it. ## What an interviewer is listening for - That you say three roles, not two. Someone who has only met the operation through a worked example usually remembers the header source and forgets that the identifying columns were a choice. - That you can state the grain of the result from the declaration alone, before looking at any data. - That you describe the fold back as the same declaration reversed rather than as a separate trick with its own rules. - That you name the invented pair rather than assuming the defaults are there and are what you think they are. - That you notice the operation is total: nothing in the input gets to be unmentioned.