Shape / rows · All operations

Drop rows with missing or error values

A variant of Filter rows

Drop, or keep only, the rows that hold nulls, empty cells, NaNs or error values.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Drop, or keep only, rows with nulls, empty cells, NaNs or error values

SQL

CREATE TEMP TABLE readings AS SELECT * FROM (VALUES
  (1, 'north', 1.5,            '10'),
  (2, NULL,    2.0,            '20'),
  (3, '',      3.0,            '30'),
  (4, 'west',  'NaN'::DOUBLE,  '40'),
  (5, 'east',  5.0,            '#DIV/0!'),
  (6, 'south', 6.0,            '60'),
  (7, 'north', 7.0,            NULL),
  (8, 'west',  NULL,           '80')
) t(id, site, score, raw);
-- a row is bad when any cell is null, empty, NaN, or text that does not read as a number
CREATE TEMP TABLE flagged AS
SELECT *, (site IS NULL OR site = '' OR score IS NULL OR isnan(score) OR raw IS NULL OR TRY_CAST(raw AS DOUBLE) IS NULL) AS is_bad
FROM readings;
CREATE TEMP TABLE clean AS SELECT id, site, score, raw FROM flagged WHERE NOT is_bad;
CREATE TEMP TABLE bad AS SELECT id, site, score, raw FROM flagged WHERE is_bad;
SELECT * FROM clean ORDER BY id;
SELECT * FROM bad ORDER BY id;
SELECT CASE WHEN ((SELECT list(id ORDER BY id) FROM clean) = [1, 6] AND (SELECT list(id ORDER BY id) FROM bad) = [2, 3, 4, 5, 7, 8]) THEN 'as expected' ELSE error('the clean rows were not 1 and 6, or the rows with a null, an empty cell, a NaN or an error value were not 2, 3, 4, 5, 7 and 8') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the clean rows were not 1 and 6, or the rows with a null, an empty cell, a NaN or an error value were not 2, 3, 4, 5, 7 and 8”.
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).

AWS Glue (1)

Remove null rowsread 2026-09-27
Remove rows in which every column is null or empty

EasyMorph (2)

Filter by typeread 2026-09-27
Keep or remove rows by the data type of their values, such as errors
Remove empty rowsread 2026-09-27
Drop rows whose cells are all empty or whitespace

Ibis (1)

DropNullread 2026-09-27
Drop rows that hold nulls in any or all of a subset of columns

Polars (2)

DataFrame.drop_nansread 2026-09-27
Drop the rows that hold a NaN in any of the chosen columns
DataFrame.drop_nullsread 2026-09-27
Drop the rows that hold a null in any of the chosen columns

Power Query M (2)

Table.RemoveRowsWithErrorsread 2026-09-27
Drop rows that hold an error value in any cell, or in named columns
Table.SelectRowsWithErrorsread 2026-09-27
Keep only rows that hold an error value in any cell, or in named columns

RapidMiner Studio (1)

Filter Examples with Missing Valuesread 2026-09-27
Drop rows whose count of missing values exceeds a threshold

Tableau Prep (1)

Null Valuesread 2026-09-27
Show the null values of a field or exclude them

Trifacta (2)

Filter mismatchedread 2026-09-27
Keep or delete rows whose values mismatch the column's data type
Filter missingread 2026-09-27
Keep or delete rows with missing values

Background

Listwise deletion, also known as complete case, deletes all cases with a missing value, and is by far the most common means of dealing with missing data.^[1] In Polars, drop_nulls drops all rows that contain one or more null values, considering all columns or a chosen subset.^[2] In pandas, dropna drops a row if any value is missing, or only if all are with how='all', and thresh requires a number of non-missing values.^[3]

When to use it

If the data are missing completely at random, listwise deletion adds no bias but decreases the power of the analysis by decreasing the effective sample size.^[1] If cases are not missing completely at random, listwise deletion introduces bias, because the cases with missing data are not representative of the original sample.^[1] Data are rarely missing completely at random.^[1]^[4] Before dropping incomplete rows, count them and compare them with the rest; if they differ, the rows that remain describe a different population from the one you started with. (Meridian's view)

In SQL, a query with WHERE i = NULL logically returns zero rows, because comparing with null returns Unknown even where i is null; IS NULL and IS NOT NULL test for null instead.^[5] In Polars, a null value is not the same as a NaN value, and NaN values are removed with drop_nans rather than drop_nulls.^[2] In DuckDB, NaN compares equal to NaN and greater than any other floating-point number.^[6] In DuckDB, isnan(x) returns true if the floating-point value is not a number.^[7] Treat empty strings, whitespace and placeholder text such as N/A as missing only by a decision you write down, alongside the null test. (Meridian's view)

The method

In DuckDB, any comparison with a NULL value returns NULL, including NULL = NULL, and IS NULL or IS NOT NULL checks whether a value is NULL.^[8] In DuckDB, TRY_CAST never throws an error and returns NULL if a cast is not possible, as in TRY_CAST('hello' AS INTEGER).^[9] A text column meant to hold numbers can be checked for error values such as #DIV/0! by casting each value with TRY_CAST and treating a NULL result from a value that was not null as an error. (Meridian's view)

In pandas, the missing-value marker depends on the data type: numpy.nan for NumPy types, NaT for datetimes and timedeltas, and NA for the nullable types.^[10] In pandas, isna() and notna() also consider None a missing value.^[10] In Polars, a row is dropped only when all of its values are null by filtering with pl.all_horizontal and is_null.^[2]

Compared with the alternatives

Imputation preserves all cases by replacing missing data with an estimated value based on other available information.^[1] Pairwise deletion, or available case analysis, deletes a case only from the analyses that need a variable it is missing, and keeps it in the others.^[1] In DuckDB, NULL values are ignored in most aggregate functions.^[8] For a single total or average there may be no need to drop incomplete rows first; drop them when the analysis needs every value of a row present. (Meridian's view)

Sources

[1]Imputation (statistics) - Wikipediasecondary source
[2]polars.DataFrame.drop_nullsdocumentation
[3]pandas.DataFrame.dropna — pandas 3.1.1 documentationdocumentation
[4]Missing data - Wikipediasecondary source
[5]Null (SQL) - Wikipediasecondary source
[6]Numeric Types – DuckDBdocumentation
[7]Numeric Functions – DuckDBdocumentation
[8]NULL Values – DuckDBdocumentation
[9]Casting – DuckDBdocumentation
[10]Working with missing data — pandas 3.1.1 documentationdocumentation