Systems and records
A dataset missingness encoding standard for null, unknown and redacted
By SourceX Editorial · Updated
9 sections
Short answer
Keep two columns for every nullable field: the value, null when nothing usable is there, and a reason code saying why. Not applicable means no proper value exists, unknown means one should exist but was never recorded, and redacted means the supplier removed it on purpose. Declare the closed code list in the schema, ship consumer handling rules, and give buyers checks they can rerun.
Redaction
Dataset missingness encoding
Key takeaways
- Null, unknown, redacted and not applicable are different facts, so one blank cell cannot carry them without corrupting training data and quality metrics.
- Put the value and a reason code in separate columns, and declare the closed code list in the package schema.
- Default parsers swallow strings like N/A and NULL, and SQL comparisons with null never match, so the encoding must survive naive reads.
- Never impute a redacted value, and never count not applicable as incomplete.
- Use a specific code only when system evidence proves it; otherwise unknown is the honest answer.
How should a dataset tell null, unknown, redacted and not applicable apart?
Give every nullable field two columns: the value, which is null whenever nothing usable is there, and a reason code that says why. Null, unknown, redacted and not applicable describe different facts about the world and about the supplier's handling, so a dataset missingness encoding standard keeps them apart from the first export and documents them in the schema, not in a README nobody parses.
Not applicable means the field has no proper value for this record, such as a refund date on a ticket that never involved a refund. Unknown means a value should exist but nobody recorded it. Redacted means a value existed and the supplier removed it on purpose, usually for privacy or confidentiality. A plain null with no reason is a fourth state that only says the cell is empty, and it should become rare once the reason column exists.
The rest of this guide gives the reason vocabulary, how to carry it in common file formats, what a consuming pipeline should do with each code, and the validation checks that let a buyer trust the encoding without access to the supplier's systems.
Why does one blank cell break training and evaluation?
A single blank breaks things because every downstream tool collapses it into one missing marker, and the model then learns from a mix of real absences, policy removals and structural gaps as if they were one thing. A ticket with no escalation reason because it was never escalated teaches something very different from a ticket whose escalation note was redacted.
Default parsers make this worse. The pandas CSV reader treats a long list of strings as missing by default, including an empty string, N/A, n/a, NA, NULL, null, NaN and None, so a source system that wrote N/A to mean not applicable loses that meaning on the first read unless the consumer turns the defaults off. SQL engines add a second trap: in PostgreSQL an ordinary comparison with a null yields null, signifying unknown, so a filter like field = NULL matches nothing and two missing values never compare as equal.
Aggregates drift too. A completeness metric that counts nulls will report a well-documented field with many not-applicable rows as low quality, while a field where the supplier silently filled gaps with zero looks perfect. Buyers who rely on missingness rates to compare suppliers end up rewarding the worse package.
Which reason codes belong in a missingness vocabulary?
Use a short, closed vocabulary of reason codes, defined once for the whole package and versioned with the schema. The healthcare interoperability standard FHIR already publishes a data absent reason code system with codes such as unknown, asked-unknown, not-asked, asked-declined, masked, not-applicable, unsupported and error, and borrowing its distinctions saves a team from inventing its own. The vocabulary below adapts those distinctions to business records; the code names are suggestions, and the mapping column shows the nearest published FHIR code.
Keep the list closed. A free-text reason field turns into hundreds of spellings within one export cycle, and a consumer cannot write handling rules for values it has never seen.
| Reason code | What it asserts | Use it when | Nearest FHIR code |
|---|---|---|---|
| not_applicable | No proper value exists for this record | A conditional field whose condition is false, such as a refund date on a ticket with no refund | not-applicable |
| unknown | A value should exist but was not recorded | The workflow required the field but the record is empty and no other reason is provable | unknown |
| not_captured | The workflow never asked for it on this record | The field was optional, added later, or skipped by the process that created the record | not-asked |
| declined | The person or party was asked and refused | A customer declined a survey question or a counterparty refused to give a detail | asked-declined |
| redacted | A value existed and the supplier removed it | De-identification or confidentiality rules removed the content before delivery | masked |
| not_supported | The source system could not hold this value | An older system version lacked the field, or one source in a merged dataset never had it | unsupported |
| pending | The value is expected to arrive later | The record was exported while still open, such as an unresolved ticket | temp-unknown |
| source_error | A process failure lost the value | A failed sync, a corrupt attachment or a parse error during export | error |
How do the codes travel in CSV, Parquet and JSON?
Carry the value and the reason as two physical columns in every format, then declare the reason column and its allowed codes in the schema that ships with the files. Formats differ in how they mark the null itself, but none of the common ones stores a reason alongside the null.
In Apache Arrow and the formats built around it, null-ness lives in a validity bitmap with one bit per slot, which records only whether a value is present. The Frictionless Table Schema goes furthest for text files: its missingValues property can list several missing-value strings, each with an optional human-readable label, and version 2 of the specification allows that list per field. The W3C CSV on the Web metadata standard has a null property that names the string or strings used for null values in a column and defaults to the empty string. Both specs describe which strings mean missing; neither replaces a reason column, because a labeled sentinel inside the value column forces every consumer to parse two types out of one field.
| Format | Value column when missing | Reason column | Where to declare it | Common pitfall |
|---|---|---|---|---|
| CSV with Table Schema | Empty string, listed in missingValues | Separate string column with an enum constraint | Table Schema field definitions and missingValues | Writing N/A or -9999 into numeric columns, which readers coerce or misread |
| CSV with CSVW metadata | Empty string, named by the null property | Separate string column | CSVW column descriptions | Relying on the default empty-string null while some exports wrote a space |
| Parquet or Arrow | True null via the validity bitmap | Separate dictionary-encoded string column | Schema field metadata plus the package data dictionary | Using NaN in a float column, which is a number to most engines rather than a null |
| JSON Lines | JSON null, never an omitted key | Sibling key such as field_reason | JSON Schema with an enum for the reason key | Dropping keys for missing values, which hides the difference between unknown and not exported |
| SQL database dump | SQL NULL | Separate column with a check constraint | Column comments and the data dictionary | Filters written as field = NULL, which match nothing |
What should a buyer's pipeline do with each code?
Each reason code implies a different handling rule, so the package should ship consumer guidance next to the vocabulary rather than leave every buyer to guess. The table below is the guidance a supplier can publish as is; a buyer can override a rule, but should do so deliberately and record why.
Two rules matter most. Never impute a redacted value, because the imputation reconstructs something the supplier was obliged to remove and can produce a plausible fake that later looks like leaked data. And never treat not_applicable as a gap in completeness metrics, because the field is complete for that record by definition.
| Reason code | As a model input | As a training target | In evaluation and metrics |
|---|---|---|---|
| not_applicable | Feed the reason as a categorical signal; it is informative | Exclude the field as a target for this record | Count as complete; exclude from accuracy on that field |
| unknown | Keep the reason flag; impute only with a documented method | Drop the record from targets for this field | Report separately as a quality gap per field |
| not_captured | Treat like unknown, but analyze by process or period | Drop from targets | Report by time window to show process changes |
| declined | Keep the flag; refusal often carries meaning | Do not infer the withheld answer | Report separately from unknown |
| redacted | Keep the flag; never impute or reconstruct | Exclude; never train a model to predict removed content | Exclude from accuracy; report counts against the redaction log |
| not_supported | Treat as a source or period marker | Exclude for affected sources | Stratify metrics by source system or version |
| pending | Exclude the record from training snapshots | Exclude until resolved | Refresh in the next release rather than score now |
| source_error | Exclude or repair upstream | Exclude | Report as a supplier defect with a count |
Which checks prove the encoding before the package ships?
Validation should be runnable, so a buyer can rerun every check on the delivered files without the supplier's systems. Ship the checks as a small script or a list of queries with expected results, and record the schema version and content version they were run against.
At a glance
Which checks prove the encoding before the package ships
- Pairing: every null value has exactly one reason code, and every non-null value has an empty reason.
- Closed vocabulary: every reason value appears in the declared enum for that schema version.
- Conditional logic: not_applicable appears only where the documented condition is false, for example refund_date is not_applicable only when refund_issued is false.
- No sentinels: numeric and date columns contain no stand-in values such as -9999, 1900-01-01, zero or the strings N/A and NULL.
- Redaction reconciliation: redacted counts per field match the counts in the redaction log for the same release.
- Parser test: a default read with pandas or a similar library produces the same null counts as the schema-aware read, which proves no literal string is being swallowed.
- Distribution report: reason-code counts per field and per source system, so a jump between releases is visible.
- Stable identifiers: record IDs are unchanged between the value file and any separate reason file, and the manifest lists every file with its hash.
How would reason codes look on an illustrative support ticket export?
Consider an illustrative export of help desk tickets from a system such as Zendesk or Jira Service Management, prepared for a buyer training a triage model. The fields and rows below are invented for this example; counts and values are not drawn from any real company.
The exception sits in the last row. Agents in this illustrative help desk typed N/A into the free-text resolution note when they had nothing to add. That string is data, an agent's own entry, not missingness, so the supplier keeps it verbatim with no reason code and documents the habit in the data dictionary. Converting it to null would erase a real behavior the buyer's model may need to see, and a default pandas read would convert it anyway unless the buyer disables the default missing-value list.
Two rows also show why the codes cannot be swapped. The refund date on the first ticket is not_applicable because no refund was issued, which is a fact the model can use. The customer phone on the second ticket is redacted, which the buyer must never fill in.
| Ticket and field | Value delivered | Reason delivered | Why |
|---|---|---|---|
| T1 refund_date | null | not_applicable | refund_issued is false, so no refund date can exist |
| T2 customer_phone | null | redacted | Phone numbers are removed under the de-identification rules |
| T3 satisfaction_score | null | declined | The survey was sent and the customer chose not to answer |
| T4 product_area | null | not_supported | The field was added to the form after this ticket closed |
| T5 resolution_code | null | pending | The ticket was still open on the export date |
| T6 first_reply_at | null | unknown | A reply is required by the workflow but no timestamp exists |
| T7 resolution_note | N/A | empty | Agent-typed text kept verbatim; not a missing value |
When is a plain null honest enough?
A plain null with reason unknown is the honest answer whenever the supplier cannot prove a more specific reason. The vocabulary is only useful if each code is a claim the supplier can back with system evidence, such as a condition field, a form version history or a redaction log. Guessing that a value was not_captured because the field looks optional is worse than saying unknown.
Some fields need no reason column at all. A required key that the source system enforces, such as a ticket ID, should never be null, and a null there is a defect to fix rather than a state to encode. Very wide tables with hundreds of sparse attributes are another limit: a reason column per field doubles the width, so many teams use one long-format exceptions file keyed by record ID and field name instead, which carries the same information.
Stop and ask for specialist review when the reason itself is sensitive. A declined flag on a health or financial question can reveal something about a person even with the answer removed, and a redacted flag on a single rare record can point to the very detail that was removed. Privacy reviewers should check reason codes with the same care as values.
What should a data request say about missing values?
When you ask a supplier for data, put the encoding in the request rather than discovering it at delivery: name the reason vocabulary, the formats, the validation checks you will rerun, and whether you need an exceptions file for wide tables. A supplier who already tracks conditions and redaction logs can usually produce reason codes from the same evidence.
Buyers who work with SourceX can write the missingness specification straight into the format field of its request-data form, next to the scope, volume, language, timing and licensing needs it also asks for. Because SourceX sources each dataset from U.S. businesses only once such a request arrives, rather than selling sets it already holds, the reason vocabulary can shape the export from the start. The redacted codes themselves come from de-identification, which SourceX performs through approved processing routes.
Frequently asked questions
Is NaN the same as null in a dataset?
No. NaN is a floating point value meaning not a number, and most engines treat it as a number rather than an absent value, while null means no value is present. pandas uses NaN as its missing marker for NumPy float columns, which is why reading data through it can blur the two. Deliver true nulls and keep NaN only where a calculation genuinely produced it.
Should a supplier use -9999 or 0 for missing numbers?
No. Sentinel numbers sit inside the value range, so averages, sums and models treat them as real measurements unless every consumer knows to strip them. Use a true null in the value column and put the reason in a separate column instead.
What is the difference between redacted and unknown?
Redacted means the supplier had the value and removed it on purpose, so the value existed and must not be reconstructed. Unknown means no one recorded a value that should exist. The two need different handling in training and in quality metrics.
Can the Frictionless Table Schema hold missing-value reasons on its own?
Partly. Its missingValues property can list several missing-value strings with human-readable labels, so a labeled sentinel can stand for a reason. That still puts two kinds of content in one column, so a separate reason column is easier for consumers to validate and use.
How should not applicable values count in completeness metrics?
They should count as complete, because the field has no proper value for that record. Report completeness only over records where the field applies, and show not applicable counts separately so reviewers can see the condition at work.
Sources
- The Frictionless Table Schema missingValues property lists strings to treat as missing, each optionally with a human-readable label, and can be set per field. Source
- The W3C CSV on the Web metadata null property gives the string or strings used for null values in a column, defaulting to the empty string. Source
- Apache Arrow records whether each array slot is null in a validity bitmap with one bit per slot. Source
- pandas read_csv recognizes a default list of strings as missing, including the empty string, N/A, n/a, NA, NULL, null, NaN and None, unless keep_default_na is turned off. Source
- pandas represents missing values with different sentinels by data type, including numpy.nan for NumPy types and pd.NA for nullable extension types. Source
- In PostgreSQL, ordinary comparison operators yield null, signifying unknown, when either input is null, and IS DISTINCT FROM treats null as a comparable value. Source
- HL7's data absent reason code system defines distinct codes for why a value is missing, including unknown, not-asked, asked-declined, masked, not-applicable, unsupported, temp-unknown and error. Source
Tell us what data you need
Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.