Systems and records
Dataset missing value semantics: 7 checks run on 13,997 nulls
By SourceX Editorial · Updated
10 sections
Short answer
Dataset missing value semantics come down to three meanings a bare null cannot carry: no value can exist, a value exists but was not recorded, or a value was removed on purpose. Accept a delivery only when every null maps to one declared meaning and no field in the same row contradicts it. Run on the Ames housing file, that test confirmed 59.6 percent of 13,997 nulls and found 112 contradicted.
Retrieval
Dataset missing value
Key takeaways
- A null carries one of three meanings, inapplicable, unavailable or withheld, and the file format records none of them.
- The decisive check compares each declared-absent null with a governing field in the same row, such as basement area for a basement grade.
- In the Ames housing copy at commit 4fe1723, 8,345 of 13,997 nulls were confirmed absent, 5,090 could not be checked and 112 were contradicted by their own row.
- The package's cleaning function fills 559 nulls with values the file cannot confirm, including zero street frontage on 104 corner lots.
- Withheld values cannot be inferred from the data, so the supplier must declare them or state in the manifest that none were removed.
What does a null mean in a delivered dataset?
A null in a delivered dataset means one of three things, and the cell alone never says which: no value can exist for this record, a value exists but nobody recorded it, or a value existed and the supplier removed it on purpose. Call them inapplicable, unavailable and withheld. A buyer who treats the three alike trains a model on structural facts, recording gaps and policy decisions as if they were one signal.
The acceptance decision is narrow. Accept the nulls in a delivery when each one maps to exactly one declared meaning and no other field in the same row contradicts that meaning. Three kinds of evidence support it: a data dictionary that names the meaning of every null field by field, a governing field that can confirm or refute an absence claim, and a removal log for anything withheld.
This page turns that decision into seven checks, runs them on a public file at a pinned commit and reports what they find. The vocabulary for coding missing values is covered in the guide to a missingness encoding standard; the question here is narrower and later in the pipeline: how a delivery engineer proves that the nulls in a received file mean what the dictionary says they mean.
Why does a file that loads cleanly still fail on meaning?
Loading, type checks and checksums prove that the bytes arrived and parse; none of them records why a cell is empty. In the Ames housing file used below, every one of the 13,997 nulls is a well-formed missing value in a correctly typed column, and the file still holds nulls whose stated meaning its own rows disprove.
Statistical exchange standards already separate the meanings that most business exports merge. The SDMX code list for observation status defines M as a missing value where data cannot exist and L as a missing value where data exist but were not collected, and it reserves O for a missing value when no breakdown of the reason is made. Confidentiality sits in a different attribute altogether: SDMX's guidelines on confidentiality say a confidential observation usually carries the code C.
A typical CSV delivery is the O case without saying so. SDMX's own 2014 note on implementing the observation status list warns that the list mixes concepts that are not always mutually exclusive, which is the trap a supplier falls into when one token stands for absence in one field and for unknown in the next.
Seven checks that sort every null in a delivery
The matrix below is the acceptance test. Each check has a pass rule a buyer can run on the delivered files alone, without the supplier's systems, and the last column shows what it found on the Ames housing data as distributed in the AmesHousing R package at commit 4fe1723: 2,930 property sales, 82 columns, 27 of them with at least one null.
Checks 3 to 5 do the work a schema cannot. They use other fields in the same row as evidence for or against the meaning a null claims, so a contradiction is a fact about the delivery rather than an opinion about it. Across the file, 8,345 nulls were confirmed absent, 5,090 could not be checked and 112 were contradicted by their own row:
| Check | What it catches | Pass rule | Result on the Ames copy |
|---|---|---|---|
| 1. Null inventory | Nulls hidden as empty strings or stray tokens | Null and empty-string counts are listed per field before any parsing rule runs | 27 fields hold 13,997 nulls; no empty strings |
| 2. Declared meaning | Fields whose nulls have no stated meaning | Every field with a null names that null's meaning | 14 fields declare absence (13,291 nulls); 13 declare nothing (706 nulls) |
| 3. Governing-field agreement | Absence claims the same row disproves | A declared-absent null sits where the governing field is 0 | 8,186 agree, 7 contradicted, 8 undetermined, 5,090 have no governing field |
| 4. Value-coded absence | A None category that disagrees with its numbers | None pairs with area 0; a named type pairs with area above 0 | 7 sales say None with veneer area above 0; 3 name a veneer with area 0 |
| 5. Impossible nulls | Unrecorded values in fields that cannot be empty | No null where another field proves a value exists | 105: 104 corner lots without frontage and 1 garage without a build year |
| 6. Withheld declaration | Removed values passed off as gaps | A withheld code or a nothing-withheld statement, matching the removal log | Neither is present, so the check cannot pass |
| 7. Recode audit | Cleaning that turns an unknown into a fact | Each fill rule is logged with its count and no fill asserts what the file cannot confirm | 559 of 13,838 filled nulls unconfirmed; 111 contradicted by their row |
Where the Ames rows contradict their own nulls
The contradictions are few, but each one is a value that exists and was never recorded: the unavailable meaning hiding under an absent label.
Basements show it first. Three sales record a basement of 1,595, 725 and 936 square feet, complete with quality and condition grades, yet leave basement exposure null, the token the package reads as no basement. A fourth sale, with a 3,206 square foot basement, leaves its second finish type null in the same way.
One garage shows it across four fields. The sale numbered 1357 in the file records a detached garage of 360 square feet that holds one car, with finish, quality, condition and build year all null. The sale numbered 2237 records a detached garage and nothing else: area, car capacity, finish, quality, condition and build year are all null, so the check labels the nulls that depend on garage area undetermined rather than guessing which meaning applies.
The largest group sits in a field with no declared meaning at all. Lot Frontage, the linear feet of street connected to the property, is null for 490 sales, and 104 of them are corner lots, which border streets on two sides and so cannot have zero frontage. The other 386 nulls fall on lots coded Inside, CulDSac, FR2 and FR3, where the file offers no evidence either way.
Read together, the file holds three groups: nulls confirmed absent by a governing field, nulls the documentation calls absent that no field can confirm, and 562 nulls the check could not show to be absent. The table sorts all 13,997 into five labels with the action each one calls for:
| Label | Null cells | Where they sit | Buyer action |
|---|---|---|---|
| Absent, confirmed | 8,345 | Basement, garage, fireplace, pool and misc-feature fields; garage build year where no garage exists; 2 basement bath counts where no basement exists | Accept and recode to an explicit not-applicable value |
| Absent, unconfirmable | 5,090 | Alley access (2,732) and fence (2,358), which have no governing field | Accept on the documentation and flag as unverified |
| Contradicted | 112 | 104 corner-lot frontages, 3 basement exposures, 1 basement finish type, 3 garage fields and 1 garage build year | Return to the supplier as unavailable, not absent |
| Undetermined | 15 | Two sales whose basement area or garage area is itself null | Query the source record or hold the rows out |
| No evidence | 435 | 386 frontages, 23 veneer types, 23 veneer areas and 1 null each in basement area, electrical and garage area | Treat as unknown; never fill with 0 or None |
Where cleaning turns an unknown into a fact
Meaning is most often lost after delivery, when someone fills the nulls. The AmesHousing package's processing function, make_ames, fills nulls field by field; read rule by rule against the raw file, its 26 filling rules touch 13,838 nulls, and 559 of the filled values state something the file cannot confirm.
Most fills are faithful. Missing pool quality becomes No_Pool, and all 2,917 of those sales have a pool area of 0. The unconfirmed fills cluster in three places. All 490 missing frontages become 0, which asserts zero feet of street on 104 corner lots. The 23 missing veneer types become None, merging unknowns into the 1,752 sales that recorded no veneer, and their 23 areas become 0. And sale 1357, with its 360 square foot garage, gets No_Garage for finish, quality and condition.
One fill shows the honest alternative. The package's change log for version 0.0.3 says the missing electrical value was converted from NA to Unknown, a label that keeps the meaning open instead of inventing one. Garage build year, with 159 nulls, is dropped from the processed data rather than filled.
The rule for a buyer follows directly. A fill is acceptable when the dictionary or a governing field supports it, every fill rule ships in a recode log with the number of cells it changed, and the raw nulls survive alongside the filled column. Of the 559 unconfirmed fills here, 111 are refuted outright by another field in the same row.
How should a supplier declare values it withheld?
A supplier has to declare withheld values explicitly, because no check on the file can find them: a redacted cell and an unrecorded cell look identical once both are null. Check 6 therefore passes only on a statement, either a withheld code with a count per field or a line in the manifest saying nothing was withheld, and the counts must match the supplier's removal log.
The documentation in the Ames package explains absence and nothing else, so the check cannot pass on that copy even though nothing suggests values were removed. That is the intended result. The test records what a delivery proves, and an unstated policy proves nothing.
Business records make this check matter far more than a housing file does. Ticket notes cleared under a retention rule, salary fields removed for privacy and records held back for a legal hold all arrive as nulls unless the supplier says otherwise. SDMX keeps confidentiality in its own attribute so that a confidential observation is never read as an uncollected one, and a delivery can do the same with a withheld reason column or a removal log keyed by field and record.
The null meaning manifest that ships with each version
Every check reads from one artifact: a manifest entry for each field that has nulls, versioned with the data so that a rerun proves the same result on the same bytes. The entries in the table are written for the Ames copy as a supplier would ship them, and the governing rule column is what lets a buyer run checks 3 to 5 without interpretation.
The checklist turns the manifest into something a second engineer can reproduce. Each item names an input the count depends on:
- Pin the data: repository or storage location, commit or version, and a SHA-256 for every file, so a recount reads the same bytes.
- Pin the dictionary version beside the data version; a null meaning that changes between releases is a schema change and is announced as one.
- Ship governing rules as data, field by field, so checks 3 to 5 run without anyone reading prose.
- Print every rule's inputs and the rows each check fails, as the script behind this page does for all 13,997 nulls.
- Log every fill with its rule and count, and keep the raw nulls in a separate column or file.
- Declare withheld values or state that none exist, with the removal-log count beside the statement.
- Rerun the checks in the buyer's own load path, in the format the buyer will train or evaluate from.
| Field | Null meaning | Governing rule | Count in this version | Evidence |
|---|---|---|---|---|
| Bsmt Exposure | Inapplicable: no basement | Null only where Total Bsmt SF is 0 | 83 nulls; 3 break the rule, 1 undetermined | Package documentation; failing sales listed |
| Garage Finish | Inapplicable: no garage | Null only where Garage Area is 0 | 159 nulls; 1 breaks the rule, 1 undetermined | Package documentation |
| Lot Frontage | Not declared; unavailable on corner lots | Never null where Lot Config is Corner | 490 nulls; 104 on corner lots | Supplier to confirm the meaning |
| Mas Vnr Type | Not declared; None already means no veneer | None pairs with Mas Vnr Area 0 | 23 nulls; 10 value conflicts | None recorded 1,752 times |
| Fence | Inapplicable: no fence | No governing field | 2,358 nulls | Documentation only; unverifiable |
| Any field | Withheld | Count equals the removal log | None declared | Statement required |
Limits of the test, and the strongest alternative explanation
The test proves contradictions; it cannot prove that a consistent null is true. A basement grade that is null where basement area is 0 agrees with the absence claim, but both fields could carry the same data-entry error. And 5,090 nulls in the Ames copy have no governing field at all.
Governing rules are domain judgments. The corner-lot rule rests on what a corner lot is. The FR2 and FR3 lot codes were left out of the frontage rule because their definitions were not verified for this article, so 24 more frontage nulls on those lots may also be unavailable. A supplier knows these rules better than any buyer, which is why the manifest should come from the supplier and the check from the buyer.
The copy matters too. The AmesHousing package is a third-party distribution of Dean De Cock's 2011 data, which describes 2,930 residential property sales in Ames, Iowa from 2006 to 2010. The package's change log says about two dozen data points were changed in version 0.0.3 while updated parcel identifiers were tracked down, and De Cock's original documentation file could not be opened for this article. The counts should be rerun on the Journal of Statistics Education download before anyone cites them as properties of the original file.
The strongest alternative explanation is that the contradictions are harmless noise: 112 cells in a file of 2,930 sales. For a classroom regression that may be true. For a licensed delivery it is not, because the same mechanism in business records, a resolution field null where the outcome field shows a result, mislabels exactly the cases a model most needs to learn.
Methods: how the counts were produced and how to rerun them
Data: data/ames_raw.rda from github.com/topepo/AmesHousing at commit 4fe17239bf753268defc8c2fa7c055a10e97fe2d (committed 2020-06-23), file SHA-256 ca0a3c2e6d35f9bef9ebcb1c5926154853f9ff68843d83eb53ab57255367247c, 2,930 rows and 82 columns, parsed on 2026-10-28 with the Python rdata package. No code from the repository was run. A null is a cell that is NA after parsing; empty strings were counted separately and there were none.
Declared meaning: the 14 declared-absent fields are those whose nulls make_ames fills with a No_ label (No_Basement, No_Garage, No_Fireplace, No_Pool, No_Fence, No_Alley_Access), plus Misc Feature, filled with None, a value never recorded in that field. Mas Vnr Type is excluded because None occurs there 1,752 times as a recorded value. Governing fields: Total Bsmt SF for the five basement grade and type fields, the three basement area components and the two basement bath counts; Garage Area for the four garage fields, garage build year and car capacity; Fireplaces for fireplace quality; Pool Area for pool quality; Misc Val for misc feature. A null is consistent when the governing value is 0, contradicted when it is above 0 and undetermined when the governing value is null. Alley and Fence have no governing field. A Lot Frontage null is contradicted when Lot Config is Corner; every other null in a field without a declared meaning or governing field counts as no evidence. Check 4 compares Mas Vnr Type with Mas Vnr Area: None with an area above 0, or a named veneer type with an area of 0, is a conflict, and rows where either field is null are left out of that count.
Recode audit: the 26 filling rules in make_ames.R were read and counted, not run. A filled cell counts as unconfirmed when its raw null is contradicted, undetermined or without evidence, with three exceptions: the Electrical fill (Unknown) asserts nothing and counts as confirmed; the Alley and Fence fills restate the documented meaning and count as confirmed; fills of the governing fields themselves (one null each in Total Bsmt SF and Garage Area) always count as unconfirmed. The share 59.6 percent is 8,345 of 13,997, rounded once to one decimal. The script, ames_null_meaning_check.py, prints every input and every failing sale and is kept with this article's working files for review.
What to write into a data request about nulls
Put the meaning test in the request, before any export exists: ask for the null meaning manifest, the governing rules, a withheld statement with a removal count, and a recode log if the supplier cleans anything. Ask that raw nulls survive any cleaning so the buyer can run checks 3 to 7 independently.
SourceX takes these requirements through its request-data form, where a buyer sets out scope, volume, language, format, timing and licensing needs. Because it finds each dataset among U.S. businesses after a request arrives, instead of selling from an inventory, the null meaning manifest and the acceptance test can be written into the format requirements before the first export is planned.
Frequently asked questions
Is a null the same as a zero in a delivered dataset?
No. A zero is a recorded measurement and a null is the absence of one, which can mean the value cannot exist, was not recorded or was removed. Filling nulls with zero turns an unknown into a claim; in the Ames copy, that fill gives 104 corner lots zero feet of street frontage.
How can a buyer tell what a null means without the supplier's system?
Use other fields in the same row as evidence. If the dictionary says a null basement grade means no basement, the basement area in that row should be zero; when it is not, the null means unrecorded. The supplier should ship these governing rules with the data so the buyer can run them.
Does a schema or a checksum validate missing value semantics?
No. A schema confirms that a column allows nulls and a checksum confirms that the bytes arrived intact; neither records why a cell is empty. Meaning needs a declared code per field and checks that compare each null with related fields.
Should a supplier fill missing values before delivery?
Only with values the evidence supports, and only alongside the raw nulls. Fill rules belong in a recode log with the count each rule changed, so the buyer can audit them, and a neutral label such as Unknown is safer than a zero or None.
What should a manifest say about withheld values?
It should give a withheld code and a count per field, or state that nothing was withheld, and those counts should match the supplier's removal log. A redacted value and an unrecorded value look the same once both are null, so only a declaration can separate them.
Sources
- The AmesHousing package documents ames_raw as the data described by De Cock (2011), with 82 fields recorded for 2,930 properties in Ames, Iowa, and lists each field, including Lot Frontage as linear feet of street connected to the property. Source
- The package's make_ames function treats a missing basement grade as no basement and fills missing values field by field, including No_Garage, No_Pool, None for veneer type, 0 for lot frontage, and drops garage build year. Source
- The package's change log says version 0.0.3 changed about two dozen data points while updated parcel identifiers were found, and converted the missing electrical value from NA to Unknown. Source
- The ames_raw data file at the pinned commit is the input counted on this page: 2,930 rows, 82 columns and 13,997 null cells in 27 columns. Source
- De Cock's 2011 Journal of Statistics Education paper describes residential property sales in Ames, Iowa from 2006 to 2010, with 2,930 observations in the final data set. Source
- The SDMX observation status code list defines M as a missing value where data cannot exist, L as a missing value where data exist but were not collected, and O as a missing value when no breakdown of reasons is made. Source
- SDMX's 2014 note on implementing the observation status code list lists L and M among the missing-value statuses and says the list mixes concepts that are not always mutually exclusive. Source
- SDMX's guidelines on confidentiality and embargo say a confidential observation usually carries the confidentiality status code C, an attribute separate from observation status. Source
Tell us what data you need
Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.