What must be true of a table before one column's distinct values can safely become new headers filled from a second column?
answer
- one slot, one value
- the address is key plus header
- identifier uniqueness is the wrong check
- a claim about the input's grain
- count distinct pairs against rows
basics
~20 sEach combination of row key and new header must occur exactly once. The result has one slot per combination, so two input rows sharing the same identifying values and the same header value leave one cell with two candidate contents.
solid answer
~40 sA widening — turning one column's distinct values into new headers, filled from a second column — assigns three roles: the **identifying columns**, which stay put and become the rows; the **header source**, whose distinct values become the new column labels; and the **cell source**, which supplies what goes inside. A cell is addressed by the row key together with a header, and there is exactly one slot per address. So the unstated precondition is that each row-key-and-header pair occurs exactly once in the input. When it occurs twice the layout cannot express both values, and the widening stops being a pure rearrangement: something has to decide what the single cell holds. Checking that the identifying columns look reasonable is not the same check — the pair is what must be unique.
go deeper
Recall the shape: identifying columns become rows, one column's distinct values become headers, a second column fills the cells. One cell holds one value, so each key-and-header pair may appear only once.
Explain why the precondition follows from the address space rather than from any tool's rules, and why checking the identifying columns alone misses it. Show the count that tests it.
Show that you treat a layout change as a step that can compute. Say what a second value for one cell means in the data before deciding what the code should do with it.
Frame it as grain: the widening asserts a grain for the input, and an unstated assertion made in a cosmetic-looking step is the kind that survives review and then moves a reported number.
## The three roles a widening assigns A **widening** turns one column's distinct values into new column labels, filled from a second column. Before it can run, three roles are assigned to the input's columns: - **The identifying columns** — the columns that say which subject and which moment a row is about. They stay put and become the rows of the result. - **The header source** — the column whose distinct values become the new column labels. - **The cell source** — the column the contents of the cells are taken from. Everything else either travels along as an identifying column or does not appear in the result at all. Once the roles are fixed, so is the result's address space: a cell is addressed by a **row key** — the tuple of identifying values — together with **one header**. There is exactly one slot per address, and that single sentence is where the whole subject lives. ## The precondition, stated plainly For the widening to be a rearrangement of values you already have, **each row-key-and-header pair must occur exactly once in the input**. Then every input value has a slot of its own and no slot has to hold two. If a pair occurs twice, the shape cannot express it. One slot, two candidate contents. This is not a defect in those two rows — they may be perfectly good data — it is an arithmetic property of the layout you asked for. Something must resolve the cell, and the resolution is arithmetic that nobody wrote down. ## A worked count Take a table of readings with identifying columns `subject` and `day`, a header source holding five measure names, and a cell source holding the number: | input rows | distinct key-and-header pairs | what the widening can be | |---|---|---| | 500 | 500 | a pure rearrangement: every value lands in its own cell | | 501 | 500 | a rearrangement plus one undeclared decision about one cell | | 1,000 | 600 | 400 values must be resolved away by something | The first row is the case everybody pictures: 100 subject-days times 5 measures, each measured once, so 500 numbers move into 500 cells and the totals of the whole table are untouched. The third row is the case that hurts, and nothing about the calling code looks different. ## Unique identifying columns are not the check The common mistake is to check the wrong uniqueness. Suppose `subject` and `day` together identify a row in your mental model, and you verify that each `subject`/`day` pair appears the expected number of times. That says nothing: the address includes the header. If one subject-day recorded the same measure twice — a retry, a correction, a second sensor, a finer grain than you assumed — the identifying columns are exactly as unique as before and the pair is not. The converse also misleads. Identifying columns that repeat across rows are entirely normal in the long layout — one row per measurement means the identifiers are written once per measure, so of course they repeat. Repetition of the identifiers is the shape working as intended; repetition of the **pair** is the problem. ## What the precondition is really a claim about It is a claim about **grain** — what exactly one row of the input is about. A widening asserts that the grain is one row per row-key per header. When the true grain is finer, the assertion is false and the tool is left holding a question you did not answer. That is why this is worth saying out loud at the moment you write the operation. A layout change reads as cosmetic, so it attracts no review; a step that computes an average attracts review. Here the second is hiding inside the first. ## When the precondition is violated What happens next is not one thing. The dispositions fall into three kinds: a tool that treats one value per cell as a stated precondition checks the pairs and refuses to produce a result; a tool whose widening surface accepts a reduction and supplies a default one quietly resolves the cell; a tool whose cells can hold a collection keeps both values in the cell. Only the first tells you anything happened, so you cannot rely on a result appearing as evidence that the precondition held. ## What to do with it 1. **Name the three roles out loud** before writing the call — which columns identify, which supplies headers, which supplies cells. Most surprises here start as a role you never consciously assigned. 2. **Count the distinct key-and-header pairs against the row count.** Equal means rearrangement; fewer means at least one cell is being decided rather than filled. 3. **Decide what a second value means** — a correction, a duplicate delivery, a genuinely finer grain — before you change the layout, not inside it.
- Does a widening whose cells are counts of rows need the same precondition?No — a counted cross-tabulation is a widening whose cells are counts of the rows landing at each address, so several rows per address is the whole point and counting is the declared reduction. The precondition applies to a widening that carries values across from a cell source, where duplication means two candidate contents for one slot.
- Why is repetition of the identifying columns normal in the input but repetition of the pair not?In the long layout — one row per measurement, with the measure's name in one column and its number in another — the identifiers are written once per measure, so they repeat by design. The widening collapses those repeats into a single row. The pair is different: it is the cell's address, and two rows at one address is two values for one slot.
saying these in an interview costs you the question
- Thinks a layout change only moves values and can never alter a number
- Checks that the identifying columns are unique and calls that the precondition
- Assumes any tool will raise an error when a cell is ambiguous
- Cannot say which column supplies headers and which supplies cell contents
- Treats repeated identifying values in the long layout as itself the defect