A temperature column reports no absent cells, yet its minimum is far below anything the instrument can physically read - why?
answer
- the column is full, not clean
- somebody encoded absence as a value
- an in-type code passes every absence check
- look at the extremes and the value counts
basics
~20 sThe producer wrote a stand-in code into the column - an ordinary value of the column's own type used to mean no reading. Every absence check reads it as genuine data, so nothing is flagged, and the code lands in the extremes and the averages.
solid answer
~50 sSomebody upstream needed to say no reading in a column that only holds numbers, so they wrote a **stand-in code**: an ordinary value of the column's own type, typically far outside the range the instrument can produce. That is not absence; it is data. The absence check, which asks cell by cell whether a value is there, answers no for every one of those cells; a step that removes incomplete rows keeps them; and the code is summed, averaged and compared like any other reading, which is what dragged your minimum down. A genuine hole would at least announce itself, because tools do something conspicuous with holes in an aggregate - stepping over them in some designs, making the whole answer absent in others. A stand-in code announces nothing. Find it in the distinct values and the extremes, then convert it to real absence in one documented step.
go deeper
Know that a value meaning no data can be sitting in a column as ordinary data, and that a count of absent cells is therefore not evidence that a column is complete.
Explain the mechanics: the code shares the column's representation, so the absence check, incomplete-row removal and every aggregate treat it as a reading, and it shows first in the extremes and in a listing of distinct values with counts.
Demonstrate the hunt and the handover: profile the column, settle with the producer what their system writes when there is no reading, convert it at one boundary, and say what you do when the code is itself a legal value.
Decide where in your systems absence may be represented as a value at all, and what the team does about historical data already carrying codes that can no longer be told apart from real readings.
## What a stand-in code is, and why producers write them A **stand-in code** is an ordinary, in-type value that a producing system writes into a column to mean there is no data here. Instruments do it because the field on the wire has to carry a number. Legacy systems do it because the storage they were built on had no way to say nothing. Export jobs do it because somebody decided a filled column was tidier than a ragged one. The code is usually chosen to look obviously wrong to a human reader - a large negative number in a column of positive readings, a date centuries in the past, a repeated round number - and the whole trick depends on a human noticing. Nothing in the column enforces that. The code is stored the same way as a genuine reading, it has the same representation, and it is a value in every sense the tool cares about. ## Why every absence check passes The absence test is a question about presence, not about meaning: is there a value in this cell. For a stand-in code the answer is yes. That single fact produces the whole failure mode. | Behaviour | A cell with nothing recorded | A stand-in code | |---|---|---| | Flagged by the absence test | yes | no | | Removed by dropping incomplete rows | yes | no | | Entering a sum or an average | handled specially, and the treatment differs by tool and by surface | added in like any other reading | | Showing up in the minimum or maximum | no | yes, often as an impossible extreme | | Appearing in a listing of distinct values | as the tool's marker | as an ordinary value with a suspicious count | A hole is loud. Whatever a given design does with it - step over it, or make the whole result absent - the hole is countable, flaggable and generally visible in a summary. A code is silent. It produces a plausible-looking number that is simply wrong, which is the more expensive class of failure because nothing prompts anybody to look. ## What it does to the numbers - It sets the extremes. A minimum far below what the process can produce is the clearest single symptom, and it is usually the first thing anybody notices. - It moves the average, in the direction of the code and in proportion to how often it appears. A code appearing in one row of every twenty is enough to make an average meaningless without making it look absurd. - It forms a category. In any per-value or per-group summary, the code sits there as its own value with a large count. - It survives cleaning. Every step that targets absence - dropping incomplete rows, filling holes, counting holes - passes straight over it, so a pipeline can look thoroughly cleaned and still be wrong. ## Finding one in a column you did not produce Four cheap checks, before any analysis: 1. List the distinct values with their counts and look for one exact value repeating far more often than its neighbours. 2. Read the minimum and maximum against a range you can defend physically or commercially, not against the data itself. 3. Plot or tabulate the distribution coarsely and look for a spike standing alone, away from the body of the readings. 4. Ask the producing team, in words, what their system writes when there is no reading. This is the only check that gives you an answer rather than a suspicion. ## The case you cannot detect from the column alone The dangerous variant is a code that is also a legal reading - zero used to mean no reading in a column where zero genuinely occurs. Then the measured value and the stand-in are the same bits, and no profiling of that column will ever separate them. Recovery has to come from outside: the times the device or shop was known to be down, a companion status field, whole rows arriving at the same value together, or the producer's own answer. Where none of that exists, the honest move is to report the ambiguity and the affected volume rather than to pick a meaning you cannot justify. ## Once you have found it Convert the code to real absence once, at the boundary where the data enters your process, and record the mapping next to the dataset so the next person does not rediscover it. Doing the conversion in each downstream report instead gives you several places to get it wrong and several places to update when the producer changes the convention. And do not convert it to zero: that swaps one wrong answer for another, because it turns an admission of ignorance into a measurement.
- What if the stand-in code is itself a legal reading, such as zero in a column where zero really occurs?Then the column cannot tell you, and no amount of profiling will: the stored values are identical for both meanings. The evidence has to come from outside the column - device or shop uptime, a companion status field, the pattern of whole rows arriving at the same value together, or the producer's own answer. Report the ambiguity and how many rows it touches instead of picking a reading you cannot justify.
- Why is a stand-in code more dangerous than a hole, when both mean the same thing to the producer?A hole is visible: it is counted, it is flagged, and most surfaces do something conspicuous with it. A code is invisible: it passes every check and participates in arithmetic, so the error surfaces as a plausible number rather than as a gap. The failure that produces a confident wrong answer with no warning is the one that gets published.
saying these in an interview costs you the question
- Trusts a zero count of absent cells as proof a column is complete.
- Says a stand-in code is just another way of marking absence.
- Assumes an impossible value would have raised an error somewhere.
- Removes incomplete rows and expects the codes to go with them.
- Replaces the code with zero instead of with real absence.