When a table is widened into one column per measure using two name columns instead of one, what do the resulting column labels look like?
answer
- the header is no longer one name
- one part per name column
- leading part groups, trailing varies fastest
- levels, or one composed string
basics
~20 sEach output column is identified by two name parts, one from each name column, with one column per pair the data holds. Whether those parts stay separately addressable or are composed into a single string depends on the tool's label space.
solid answer
~50 sA widening — turning one column's distinct values into new headers, filled from a second column — has to be told which column supplies the headers. Give it two, and a single name can no longer say which column is which, because a column now stands for a combination: this measure, in this region. So every output column is identified by an ordered **pair** of parts, one from each name column, and you get one column per pair the data actually holds. What happens to that pair then depends on the tool, not on the data. Where column labels are structured, both parts survive as separate levels you can address. Where column labels are a flat list of strings, no level exists: the parts are composed into one string with a separator you supplied or the tool chose, and the structure is gone at the moment the labels are made.
go deeper
Recall the shape of the result: two name columns means each header carries two parts, one from each column, and the number of columns is the number of name pairs the data holds.
Explain where the two parts actually live — separate addressable parts in one design, a single composed string in another — and say what sets the nesting order when the parts survive.
Show that you check the label space before writing the next step, because code that selects a column by a plain name silently means something different once headers carry two parts.
Weigh whether compound headers should cross a team boundary at all: every consumer of that table inherits the obligation to understand them, and that obligation is paid on every future change.
## What a second name column changes A **widening** turns one column's distinct values into new column headers and fills the cells from a second column. Two things have to be named for it to run: the **header source**, whose distinct values become the new headers, and the **cell source**, the column the cells are taken from. With a single header source, each new column stands for one value, and one name is enough to say which column it is. Give the widening two name columns and that stops being true. A column now stands for a *combination* — this measure, in this region — so one name cannot identify it. Every output column is identified by an **ordered pair of parts**, one drawn from each name column, and that pair is the real identifier however the tool chooses to store or display it. How many columns you get is the number of distinct pairs involved. That is at most the count of distinct values in the first name column multiplied by the count in the second, and it is usually fewer, because real data rarely holds every combination. Some tools produce exactly the pairs present; others complete the grid and create a cell for combinations that never occurred. Count the distinct pairs before you run the widening — a modest input can produce a header row nobody wants to read. ## Where the two parts end up This is the part candidates get wrong, because most people have only ever used one design. What happens to the pair depends on the tool's **label space** — what a column label is allowed to be — and not on the data at all. - Where column labels are **structured**, a label really is a pair of parts. Each part is a **level** you can name, select on, drop, reorder or rename, and the structure lives as long as the table does. - Where column labels are a **flat list of strings**, there is no level to speak of. The two parts are composed into one string with a separator — one you supplied through a naming template, or one the tool picked — and the structure is gone at the moment the labels are created. | | structured column labels | flat string labels | |---|---|---| | what one label is | an ordered pair of parts | a single string | | addressing one part | name the part you mean | match text inside the string | | dropping or reordering a part | a change to the labels only | rebuild every label | | what tends to go wrong | every later step must understand compound labels | a separator that also occurs inside a part | Neither is the "correct" design. Structure is more expressive and costs every consumer of that table an obligation to understand it; a composed string is universally consumable and has already thrown the parts away. ## Reading the header row Where levels exist, the header row reads as nested blocks: the **leading** part spans a block of columns and the **trailing** part varies fastest inside that block, so all the regions for one measure sit together before the next measure begins. Which of your two name columns ends up leading follows the order in which you named them to the widening, not the order they sit in the input — and getting that backwards produces a perfectly correct table grouped the least useful way. State the order deliberately and look at the result. ## The pair is the identifier Two consequences follow immediately, and both bite in ordinary code: 1. **The trailing name alone does not identify a column.** The same measure name appears under every region, so a lookup by that name alone either misses or matches several columns, depending on the design. 2. **Code written for plain names quietly means something else.** A step that selected a column by one string, compared header names against a list, or assumed the header row is a flat list of names is now looking at labels of a different shape, and the failure surfaces one step later than the cause. ## What to do about it 1. Establish the label space before you write the next step: separately addressable parts, or one composed string. 2. Decide at the boundary of the reshaping work which of the two you are handing on, and make that explicit rather than inherited. 3. If you compose the parts into one string, choose the separator yourself rather than accepting whatever the tool picked. 4. Count distinct pairs against the input's row count before widening, so the width of the result is not a surprise.
- Can two-part headers also appear when there is only one name column?Yes, when more than one column supplies the cells. The tool then has to say both which distinct value and which cell source a column came from, so it composes the two — as a second label part where parts exist, and as one composed string where they do not. Same shape of header, different route to it.
- Do you get one column per combination, or only per combination present in the data?It varies. Some tools produce exactly the pairs the data holds; others complete the grid and invent a cell for every combination that never occurred. The completed form can be far wider than the input suggests, and afterwards an invented cell looks exactly like a genuinely absent measurement unless you recorded which was which.
- Does widening on a second name column change the row count?Not by itself. A widening moves measurements out of rows and into columns, so the rows collapse to one per identifying key and the extra name column only affects how many columns come out. If your row count moved in a way you did not expect, the cause is in the keys, not in the second name column.
A filing cabinet with drawers and folders: the drawer label groups, the folder label varies inside it, and pulling out a whole drawer needs no reading. A cabinet with no drawers, where every folder is labelled "sales-north", holds the same two facts but you can only get at them by reading each label character by character.
saying these in an interview costs you the question
- Assumes every tool gives a two-level header you can address one level of
- Thinks the trailing name on its own identifies exactly one column
- Believes the nesting order is fixed rather than set by the order you named the columns
- Says a second name column adds rows rather than adding headers
- Treats a compound label as a plain string and is puzzled when a lookup misses