Shape / columns · All operations

Fill with the mean, median or mode

A variant of Fill in missing values

Replace missing values with a statistic of the column, such as its mean, median or mode.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Fill missing values with the mean, median or mode of the column

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,
       round(coalesce(amount, avg(amount) OVER ()), 2) AS filled_mean,
       coalesce(amount, median(amount) OVER ())::DOUBLE AS filled_median,
       coalesce(amount, mode(amount) OVER ())::DOUBLE AS filled_mode
FROM orders;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT [filled_mean, filled_median, filled_mode] FROM result WHERE order_id = 4) = [211.19, 120.0, 510.0] AND (SELECT [filled_mean, filled_median, filled_mode] FROM result WHERE order_id = 1) = [120.0, 120.0, 120.0] AND (SELECT count(*) FROM result WHERE filled_mean IS NULL OR filled_median IS NULL OR filled_mode IS NULL) = 0) THEN 'as expected' ELSE error('the missing amount was not filled with the mean 211.19, the median 120 and the mode 510, or a present amount changed') 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, the median 120 and the mode 510, or a present amount changed”.
  • 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).

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

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

AWS Glue (1)
  • FillWithModeread 2026-09-27

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

KNIME Analytics Platform (2)
  • 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

Polars (2)
  • DataFrame.fill_nullread 2026-09-27

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

  • Expr.fill_nullread 2026-09-27

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

RapidMiner Studio (3)

Background

In scikit-learn, SimpleImputer can fill missing values with a statistic of the column they are in: its mean, its median or its most frequent value.[1] Mean substitution replaces any missing value with the mean of that variable for all other cases, which leaves the sample mean of the variable unchanged.[2] Mean imputation attenuates any correlations involving the variable that is imputed.[2]

When to use it

The median is not skewed by a small proportion of extreme values, and so gives a better representation of the centre than the mean.[3] Reach for the median on skewed amounts such as prices or incomes, where one large order would pull the mean. (Meridian's view) scikit-learn's SimpleImputer supports categorical data represented as strings when using the most_frequent or constant strategy.[1] For a text or category column, fill with the most frequent value or a set value, never a mean. (Meridian's view)

Mean imputation has some attractive properties for univariate analysis but becomes problematic for multivariate analysis.[2] Mean imputation can be carried out within classes, such as categories like gender.[2] In DuckDB, an aggregate used as a window function with PARTITION BY is computed per partition, as in sum(amount) OVER (PARTITION BY region).[4]

In scikit-learn, preprocessing transformations should be learnt from the training data only, and fit should never be called on the test data.[5] Compute the fill statistic on the training rows and reuse that number for later data, rather than recomputing it on the rows being scored. (Meridian's view)

The method

In DuckDB, every aggregate function can be used as a window function, and with no ORDER BY the default frame is the entire partition.[4] Keep the window empty, OVER () or OVER (PARTITION BY a group), when the fill should be one figure for the column or the group. (Meridian's view) DuckDB's aggregate window functions, apart from list, ignore nulls.[4] DuckDB's median returns the middle value, averaging the middle pair of an even count for quantitative values and returning the lower value for ordinal ones.[6] DuckDB's mode returns the most frequent value, and its result is affected by ordering.[6] AWS Glue's FillWithMode takes a tie-breaking rule: for the values 1 2 2 3 3 4, MINIMUM gives 2 as the mode, MAXIMUM gives 3 and AVERAGE gives 2.5.[7] Where two values tie for most frequent, choose the tie-break yourself rather than leave it to the engine. (Meridian's view)

Compared with the alternatives

Regression imputation has the opposite problem to mean imputation: the values it imputes fit the regression line with no residual variance, suggesting more precision than is warranted.[2] scikit-learn's IterativeImputer is multivariate, using the entire set of available features to estimate missing values, while SimpleImputer uses only the non-missing values of the column being filled.[1]

Sources

  1. [1]8.4. Imputation of missing values — scikit-learn 1.9.1 documentationdocumentation
  2. [2]Imputation (statistics) - Wikipediasecondary source
  3. [3]Median - Wikipediasecondary source
  4. [4]Window Functions – DuckDBdocumentation
  5. [5]12. Common pitfalls and recommended practices — scikit-learn 1.9.1 documentationdocumentation
  6. [6]Aggregate Functions – DuckDBdocumentation
  7. [7]FillWithMode class - AWS Gluedocumentation