Skip to content

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

This page covers A dataset missingness encoding standard for null, unknown and redacted.Illustrative

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.

Which reason codes belong in a missingness vocabulary?
Reason codeWhat it assertsUse it whenNearest FHIR code
not_applicableNo proper value exists for this recordA conditional field whose condition is false, such as a refund date on a ticket with no refundnot-applicable
unknownA value should exist but was not recordedThe workflow required the field but the record is empty and no other reason is provableunknown
not_capturedThe workflow never asked for it on this recordThe field was optional, added later, or skipped by the process that created the recordnot-asked
declinedThe person or party was asked and refusedA customer declined a survey question or a counterparty refused to give a detailasked-declined
redactedA value existed and the supplier removed itDe-identification or confidentiality rules removed the content before deliverymasked
not_supportedThe source system could not hold this valueAn older system version lacked the field, or one source in a merged dataset never had itunsupported
pendingThe value is expected to arrive laterThe record was exported while still open, such as an unresolved tickettemp-unknown
source_errorA process failure lost the valueA failed sync, a corrupt attachment or a parse error during exporterror

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.

How do the codes travel in CSV, Parquet and JSON?
FormatValue column when missingReason columnWhere to declare itCommon pitfall
CSV with Table SchemaEmpty string, listed in missingValuesSeparate string column with an enum constraintTable Schema field definitions and missingValuesWriting N/A or -9999 into numeric columns, which readers coerce or misread
CSV with CSVW metadataEmpty string, named by the null propertySeparate string columnCSVW column descriptionsRelying on the default empty-string null while some exports wrote a space
Parquet or ArrowTrue null via the validity bitmapSeparate dictionary-encoded string columnSchema field metadata plus the package data dictionaryUsing NaN in a float column, which is a number to most engines rather than a null
JSON LinesJSON null, never an omitted keySibling key such as field_reasonJSON Schema with an enum for the reason keyDropping keys for missing values, which hides the difference between unknown and not exported
SQL database dumpSQL NULLSeparate column with a check constraintColumn comments and the data dictionaryFilters 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.

What should a buyer's pipeline do with each code?
Reason codeAs a model inputAs a training targetIn evaluation and metrics
not_applicableFeed the reason as a categorical signal; it is informativeExclude the field as a target for this recordCount as complete; exclude from accuracy on that field
unknownKeep the reason flag; impute only with a documented methodDrop the record from targets for this fieldReport separately as a quality gap per field
not_capturedTreat like unknown, but analyze by process or periodDrop from targetsReport by time window to show process changes
declinedKeep the flag; refusal often carries meaningDo not infer the withheld answerReport separately from unknown
redactedKeep the flag; never impute or reconstructExclude; never train a model to predict removed contentExclude from accuracy; report counts against the redaction log
not_supportedTreat as a source or period markerExclude for affected sourcesStratify metrics by source system or version
pendingExclude the record from training snapshotsExclude until resolvedRefresh in the next release rather than score now
source_errorExclude or repair upstreamExcludeReport 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.

How would reason codes look on an illustrative support ticket export?
Ticket and fieldValue deliveredReason deliveredWhy
T1 refund_datenullnot_applicablerefund_issued is false, so no refund date can exist
T2 customer_phonenullredactedPhone numbers are removed under the de-identification rules
T3 satisfaction_scorenulldeclinedThe survey was sent and the customer chose not to answer
T4 product_areanullnot_supportedThe field was added to the form after this ticket closed
T5 resolution_codenullpendingThe ticket was still open on the export date
T6 first_reply_atnullunknownA reply is required by the workflow but no timestamp exists
T7 resolution_noteN/AemptyAgent-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.

See if your company qualifies