Shape / columns · All operations

Fill in missing values

Replace, coalesce, impute or flag missing values.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

Impute with the column mean and flag it

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 order_id, amount IS NULL AS was_missing, coalesce(amount, round(avg(amount) OVER (), 2)) AS amount_filled FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE was_missing) = 1 AND max(amount_filled) FILTER (WHERE was_missing) = 211.19 FROM result)) THEN 'as expected' ELSE error('the missing amount was not filled with the mean, 211.19') 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 was not filled with the mean, 211.19”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Replace missing values with a set value

Put a chosen literal in place of each null or NaN.

Fill from neighbouring values

Carry the last present value forward, or interpolate between the values either side.

Predict missing values with a model

Fill missing values with predictions from a model trained on the other columns.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Imputationread 2026-09-27

    Replace nulls or a chosen value in numeric fields with mean, median, mode or a set value

Apache DataFusion (3)
  • Conditional Functionsread 2026-09-27

    7 functions, 1 of them aliases: coalesce, nullif, greatest, least, nvl, …

  • fill_nanread 2026-09-27

    DataFrame method: Fill NaN values in floating-point columns with a value

  • fill_nullread 2026-09-27

    DataFrame method: Fill null values in chosen columns with a value

Apache Hop (3)
  • Coalesceread 2026-09-27

    Return the first non-null value across a list of fields

  • If nullread 2026-09-27

    Replace nulls with a given value, per field, per type or for all fields

  • Null Ifread 2026-09-27

    Set a field to null where it equals a given value

AWS Glue (3)
  • FillMissingValuesread 2026-09-27

    Find records with missing values and add a field holding an imputed value

  • FillMissingValuesread 2026-09-27

    Impute nulls and empty strings with a model trained on the input, into a new column

  • FillWithModeread 2026-09-27

    Replace nulls of a column with its mode, with a rule for ties (minimum, maximum, average)

Duckle (2)
EasyMorph (1)
  • Fill rightread 2026-09-27

    Fill empty cells with the last non-empty value to their left in the same row

Ibis (5)
  • Coalesceread 2026-09-27

    Take the first non-null value from a list of expressions

  • FillNullread 2026-09-27

    Replace nulls across a table with one value or a per-column mapping

  • IsNullread 2026-09-27

    Flag the values that are null

  • NotNullread 2026-09-27

    Flag the values that are not null

  • NullIfread 2026-09-27

    Turn a value into null when it equals a given value

KNIME Analytics Platform (4)
  • Column Mergerread 2026-09-27

    Coalesce two columns, taking the secondary value where the primary is missing

  • Extract Missing Value Causeread 2026-09-27

    Extract the error message attached to each missing value into a column

  • Missing Valueread 2026-09-27

    Replace, impute or remove missing values per column or per data type

  • Missing Value (Apply)read 2026-09-27

    Apply a missing-value replacement model to another table

mlpack (1)
  • Imputationread 2026-09-27

    Replace missing values with imputed ones under several strategies

OpenRefine (5)
  • Facet by blank (null or empty string)read 2026-09-27

    Split rows true or false by whether the cell is null or a zero-length string

  • Facet by empty stringread 2026-09-27

    Split rows true or false by whether the cell is a zero-length string

  • Facet by nullread 2026-09-27

    Split rows true or false by whether the cell is null

  • To empty stringread 2026-09-27

    Set the cells in the current selection to a zero-length string

  • To nullread 2026-09-27

    Set the cells in the current selection to null

Pentaho Data Integration (2)
Polars (8)
  • DataFrame.fill_nanread 2026-09-27

    Replace floating-point NaN values with a value or expression

  • DataFrame.fill_nullread 2026-09-27

    Replace nulls with a value, or by a strategy such as forward, backward, mean or zero

  • DataFrame.interpolateread 2026-09-27

    Fill nulls by interpolating between the surrounding values

  • Expr.fill_nanread 2026-09-27

    Replace floating-point NaN values with a fill value

  • Expr.fill_nullread 2026-09-27

    Replace nulls with a value, or by a strategy such as forward, backward, mean or zero

  • Expr.interpolateread 2026-09-27

    Fill nulls by interpolating between the surrounding values

  • Expr.interpolate_byread 2026-09-27

    Fill nulls by interpolating against the values of another column

  • coalesceread 2026-09-27

    First non-null value across several columns, row by row

RapidMiner Studio (6)
Tableau Prep (1)
  • Merge Fieldsread 2026-09-27

    Merge fields into a target field: nulls in the target take the other fields' values

Trifacta (1)
VisiData (2)
  • delete-cellread 2026-09-23

    Set current cell to null

  • delete-cellsread 2026-09-23

    Set current column to null for selected rows

Background

Filling in missing values puts a substitute in place of each missing value, or marks where values were missing, so later steps can work with the gaps.[1][2] In SQL, null is a marker that a data value does not exist, and the SQL standard defines it as different from both an empty string and zero.[3] Imputation keeps every case by replacing missing data with an estimated value based on other available information.[1]

When to use it

Most statistical packages default to discarding any case that has a missing value, which may introduce bias or affect the representativeness of the results.[1] Deleting the cases with missing values is unbiased only when the data are missing completely at random, and that is rarely the case in practice.[1] Data are missing at random when the missingness can be fully accounted for by variables with complete information, an assumption that is impossible to verify statistically.[4] Data are missing not at random when the value of the variable that is missing is related to the reason it is missing.[4] Before choosing a fill, ask why the values are missing: the answer decides whether a set value, a statistic or a flag tells the truth about the data. (Meridian's view)

In SQL, every aggregate function except COUNT(*) leaves nulls out, so the average of 150, 200, 250 and a null is 200, while the average of 150, 200, 250 and 0 is 150.[3] Replace a null with zero only where zero is the true value, such as a count that really was nothing. (Meridian's view) In Polars, a NaN value is not the same as a null value, and NaN values are filled with fill_nan rather than fill_null.[5][6] DuckDB's floating-point types hold NaN, Infinity and -Infinity as special values alongside ordinary numbers.[7] Check a float column for NaN as well as for nulls before filling it. (Meridian's view)

The method

Null was introduced by E. F. Codd, the creator of the relational database model, to represent missing information and inapplicable information.[3] The 1986 SQL standard basically adopted Codd's proposal after an implementation prototype in IBM System R.[3] SQL defines two functions to handle nulls explicitly, NULLIF and COALESCE, and both are abbreviations for searched CASE expressions.[3]

Univariate imputation fills a column using only that column's non-missing values, while multivariate imputation uses the entire set of available columns to estimate the missing ones.[2] Hot-deck imputation fills a missing value from a randomly selected similar record, and the term dates back to the storage of data on punched cards.[1] Last observation carried forward is a form of hot-deck imputation that sorts the data and fills each gap with the value immediately before it.[1] Last observation carried forward is known to increase the risk of bias and potentially false conclusions, and is not recommended for use.[1] Single imputation treats the imputed values as if they were the actual values, and neglecting that uncertainty can lead to overly precise results.[1]

Compared with the alternatives

Listwise deletion, by far the most common way of dealing with missing data, drops every case with a missing value and decreases the power of the analysis even when it adds no bias.[1] Pairwise deletion drops a case only from the analyses that need its missing variable, so the number of cases is not consistent across estimates.[1] Deleting rows is the honest choice when the gaps are few and random; filling is the choice when every row has to stay, and then the method of filling needs saying. (Meridian's view)

Sources

  1. [1]Imputation (statistics) - Wikipediasecondary source
  2. [2]8.4. Imputation of missing values — scikit-learn 1.9.1 documentationdocumentation
  3. [3]Null (SQL) - Wikipediasecondary source
  4. [4]Missing data - Wikipediasecondary source
  5. [5]polars.Expr.fill_nan — Polars documentationdocumentation
  6. [6]polars.Expr.fill_null — Polars documentationdocumentation
  7. [7]Numeric Types – DuckDBdocumentation

Used in

7 Protocols use this operation: