Shape / columns · All operations

Flag missing values

A variant of Fill in missing values

Add a column that marks whether each value is missing, leaving the value alone.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Add a column that flags missing values and leave the values alone

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT *, amount IS NULL AS amount_missing, region IS NULL AS region_missing, tags IS NULL AS tags_missing FROM orders;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['order_id', 'customer', 'region', 'order_date', 'amount', 'product', 'tags', 'meta', 'amount_missing', 'region_missing', 'tags_missing'] AND (SELECT count(*) FILTER (WHERE amount_missing) = 1 AND count(*) FILTER (WHERE region_missing) = 1 AND count(*) FILTER (WHERE tags_missing) = 2 AND count(*) = 10 AND count(amount) = 9 AND sum(amount) = 1900.74 FROM result)) THEN 'as expected' ELSE error('the missing amount, region and tags were not flagged in new columns with every value and every row kept') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the missing amount, region and tags were not flagged in new columns with every value and every row kept”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Ibis (2)
  • IsNullread 2026-09-27

    Flag the values that are null

  • NotNullread 2026-09-27

    Flag the values that are not null

KNIME Analytics Platform (1)
OpenRefine (3)

Background

In scikit-learn, the MissingIndicator transformer turns a dataset into a matching binary matrix that shows where values are missing.[1] In DuckDB, IS NULL and IS NOT NULL test whether a value is NULL, whereas any comparison with NULL, even NULL = NULL, returns NULL.[2]

When to use it

Flag before you fill: once a gap holds a mean or a default, nothing in the column says it was ever empty. (Meridian's view) scikit-learn's SimpleImputer and IterativeImputer have an add_indicator parameter, False by default, that stacks a missing-value indicator next to the imputer's output.[1] Samuelson and Spirer (1992) discussed how missing or distorted data about demographics, law enforcement and health could be indicators of patterns of human rights violations.[3]

The missing-indicator method sets missing observations to a fixed value, usually zero, and adds a 1/0 dummy variable to the model to indicate whether the value was missing.[4] In nonrandomized studies the missing-indicator method typically results in biased estimates, both when data are missing at random and when missing completely at random.[4] In randomized trials the missing-indicator method is a valid way to handle missing baseline covariate data, irrespective of the mechanism of missingness.[4] Study design, rather than the mechanism of missingness, determines whether the missing-indicator method is valid.[4] Treat a flag as a record of where the gaps were, not as a cure for them in an analysis of cause and effect. (Meridian's view)

The method

scikit-learn's MissingIndicator turns a dataset into a matching binary matrix that shows where values are missing.[1] By default scikit-learn's MissingIndicator builds the mask only for features that contained missing values at fit time, and features='all' returns every feature.[1] scikit-learn's missing_values parameter lets a placeholder other than NaN, such as an integer, mark a value as missing.[1] OpenRefine can facet any column by null cells, by empty cells holding a zero-length string, or by blank cells, which means both.[5] Decide which of those a flag means, a true null, an empty string or either, and name the flag column to say so. (Meridian's view)

Compared with the alternatives

Complete case analysis includes only participants without missing observations and, besides reducing statistical power, will often give biased estimates of associations.[4] The missing-indicator method does not impute missing values, and keeps each participant in the analysis to maintain statistical power.[4]

Sources

  1. [1]8.4. Imputation of missing values — scikit-learn 1.9.1 documentationdocumentation
  2. [2]NULL Values – DuckDBdocumentation
  3. [3]Missing data - Wikipediasecondary source
  4. [4]Missing covariate data in clinical research: when and when not to use the missing-indicator method for analysis - PMCsecondary source
  5. [5]Exploring facets | OpenRefinedocumentation