Which literal strings in a file does a reader turn into absent markers, and when does that default destroy real values?
answer
- a built-in list of spellings
- it is a setting, not the data
- real values can collide with it
- code columns collide worst
- count the holes right after the read
basics
~20 sReaders ship a built-in list of spellings — an empty field, a dash, a question mark, short abbreviations for unavailable — and map any field matching one to the absent-value marker. A real value spelled the same way is destroyed, silently.
solid answer
~50 sA reader over a character-only file has to decide which fields represent nothing, and it does so with a built-in list of literal spellings: typically an empty field, a dash, a question mark, and a handful of short abbreviations for unavailable or not applicable, often in several casings. Any field matching one is replaced by whatever the tool uses to mark a cell with no value. Two things follow. First, this is a **reader setting**, not a property of your data: the list can be replaced, extended, or switched off entirely. Second, the reader cannot distinguish a genuine value that happens to be spelled that way from a marker — a two-letter country code that collides with one of the abbreviations, a category literally named with a dash, a grade recorded as a single question mark. Those rows lose their value and look like holes. Designs also differ: some keep an empty field as an empty string and reserve absence for an explicit token.
go deeper
Know that readers have a built-in list of literal strings they treat as nothing, and that an empty field is usually on it. That is a setting you can see and change.
Explain the collision: the reader cannot tell a genuine value spelled like a marker from a marker. Give a concrete case, and say that designs differ on whether an empty field even counts.
Show that you check absent counts per column immediately after a read and recognise the systematic shape of a collision — all the rows of one code, not a scatter.
The angle is whose problem this is. If a feed keeps colliding with default spellings, the durable fix is agreeing an explicit absence spelling with the producer, not a longer list of exceptions in every consumer.
## The mapping the reader has to make In a file that stores every value as characters, absence has to be written down somehow, and there is no universal way to write it. Producers use an empty field between two separators, a dash, a question mark, a short abbreviation for *not available* or *not applicable*, the lower-case word for nothing, the upper-case one, a sentinel number, or a sentence. The reader therefore ships with a **built-in list of spellings it treats as absent**, and any field matching one is replaced by the tool's absent-value marker — whatever it puts in a cell that has no value. This mapping is the only part of absence that belongs to the read. It is worth being clear about what is and is not happening: - The file holds **literal text**. - The reader maps some of that text to an **absent marker in the table**. - Whether that marker then means *unknown*, *not applicable*, *zero* or *not yet collected* is a separate matter entirely, and the reader has no opinion about it. ## Why the default is the dangerous part The list is a default, and defaults are invisible. Nobody chose to treat a dash as nothing; it simply happened during a read that reported success. Three collision patterns account for most of the damage: 1. **A real code that is spelled like an abbreviation.** Two-letter code systems are the classic case: at least one country's code is also a common abbreviation for *not available*, so an entire country's rows arrive with an empty cell in the column that identifies them. Downstream, those rows either drop out of a grouping or all pile into one bucket. 2. **A category whose name is punctuation.** A dash used as a legitimate category label, or a question mark recorded as an answer, matches the list exactly. 3. **A value that is genuinely empty text.** An empty field can mean *this is the empty string* rather than *there is nothing here*, and once it has become an absent marker the distinction is unrecoverable from the table. In every case there is no error, no warning and no clue. The column simply has holes in it where values used to be, and holes look ordinary. ## The designs differ, and the difference matters Do not carry one tool's behaviour as the model: | Reader behaviour | Consequence | |---|---| | Maps a built-in list of spellings, including the empty field | The commonest default, and the reason nobody notices it is a default | | Keeps an empty field as an empty string, reserving absence for an explicit token | An empty text value survives the read intact | | Reads a shape whose own declaration distinguishes present-but-empty from absent | Nothing is inferred; the producer already made the distinction | The corollary is that **the same file read by two tools can have a different number of absent cells**, without either tool being wrong. ## What to do about it - **Look at the list your reader uses.** It is documented, and most readers let you replace it, add to it, or turn the built-in one off so that only what you name counts as absent. - **Be specific per column where the risk is real.** Identifier and code columns are where a collision does the most damage, because they are the columns joins depend on. - **Check at the read, not later.** A count of absent cells per column immediately after the read, compared against what the producer says it sent, is the cheapest possible detector. A column that suddenly has holes in it did not gain them in your analysis; it gained them at the file boundary. - **Treat a suspiciously round number of holes as a signal.** Collisions are systematic, not random: every row of one country, every record of one category. A column with exactly as many holes as you have rows for one code is telling you what happened. ## The related loss on the way out The mapping runs in both directions and is not symmetric. A writer emitting characters has to choose a spelling for absence, and the reader that picks the file up next has to map it back. If the writer emits a spelling that the next reader's list does not include, the absent cells come back as ordinary text; if it emits an empty field and the next reader keeps empty fields as empty strings, the same thing happens. The round trip is worth testing rather than assuming — which literal strings become absent markers is the reader's setting, and a setting on one side of a file boundary is not a setting on the other.
- How would you tell a collision apart from genuinely missing data?By its shape. Genuine gaps are usually scattered and uneven; a collision is systematic — every row carrying one particular code, and exactly as many holes as there are rows for it. Cross-checking the affected rows against the raw text in the file settles it immediately.
- Does turning the built-in list off entirely solve the problem?It removes the collisions and creates the opposite risk: genuine absences arrive as ordinary text, so a column that should be numeric widens to text or a coercion step has to deal with them. The usual middle ground is to name the exact spellings this feed uses and rely on nothing else.
- Why is a code column the worst place for this to happen?Because code columns are what joins and groupings key on. A destroyed value in a measurement column distorts one number; a destroyed value in a key column removes whole rows from a match or collects them into a single anonymous bucket, and the row count is the only visible symptom.
saying these in an interview costs you the question
- Thinks the set of absent spellings is fixed and cannot be changed
- Assumes an empty field must always become an absent marker
- Believes a collision produces a warning of some kind
- Treats the holes as a data-quality problem at the source without checking the read
- Assumes two tools reading one file produce the same number of absent cells