Shape / columns · All operations

Drop empty or constant columns

A variant of Choose and rename columns

Drop the columns whose values are all empty, mostly missing or a single repeated value.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Drop the columns that are empty, mostly missing or hold one value

SQL
CREATE TEMP TABLE wide AS SELECT * FROM (VALUES
  (1, NULL, 'x', NULL, 'a', 1.5),
  (2, NULL, 'x', NULL, 'b', NULL),
  (3, NULL, 'x', 'p',  'a', 2.5),
  (4, NULL, 'x', NULL, 'c', 3.5),
  (5, NULL, 'x', 'q',  'b', NULL),
  (6, NULL, 'x', NULL, 'a', 4.5)
) t(id, empty_col, constant_col, mostly_missing_col, label, measure);
-- a column goes when it has at most one distinct value, or when more than half of its values are missing
SET VARIABLE useless = (
  SELECT list(col ORDER BY col)
  FROM (SELECT col, count(DISTINCT v) AS n_distinct, 1 - count(v) / count(*) AS missing_share
        FROM (SELECT id, COLUMNS(* EXCLUDE (id))::VARCHAR FROM wide)
             UNPIVOT INCLUDE NULLS (v FOR col IN (COLUMNS(* EXCLUDE (id))))
        GROUP BY col)
  WHERE n_distinct <= 1 OR missing_share > 0.5);
CREATE TEMP TABLE result AS
SELECT COLUMNS(lambda c: NOT list_contains(getvariable('useless'), c)) FROM wide;
SELECT * FROM result;
SELECT CASE WHEN (getvariable('useless') = ['constant_col', 'empty_col', 'mostly_missing_col'] AND (SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'label', 'measure'] AND (SELECT count(*) FROM result) = 6 AND (SELECT count(measure) FROM result) = 4) THEN 'as expected' ELSE error('the empty, constant and mostly missing columns were not the three dropped, with the measure that is one third missing kept') 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 empty, constant and mostly missing columns were not the three dropped, with the measure that is one third missing 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).

AWS Glue (2)
  • Drop Null Fieldsread 2026-09-27

    Remove columns whose values are all null

  • DropNullFieldsread 2026-09-27

    Drop fields whose value is missing or null in every record

EasyMorph (1)
  • Remove empty columnsread 2026-09-27

    Drop columns that are entirely empty, whitespace or a single repeated value

KNIME Analytics Platform (2)
RapidMiner Studio (1)
VisiData (1)
  • hide-uniform-colsread 2026-09-23

    Hide each column that has one distinct value across several rows

Background

In scikit-learn, VarianceThreshold removes features whose variance does not meet a threshold, by default those with the same value in all samples.[1] In KNIME, the Missing Value Column Filter removes each selected column that meets a set condition on its number of missing values.[2]

When to use it

In EasyMorph, the use cases given for Remove empty columns include imported spreadsheets with sparsely located tables, and columns that only contain null replacement values such as NULL.[3] In EasyMorph, Remove empty columns can treat whitespace as empty, removing columns whose values are all empty or hold only spaces, line breaks or tabs.[3] In AWS Glue, DropNullFields drops the fields of a DynamicFrame whose type is NullType, which are fields with missing or null values in every record.[4] Check what a tool counts as empty before relying on it: a column of empty strings, of the text NULL or of spaces is empty to a reader but not necessarily to the tool. (Meridian's view)

In EasyMorph, Remove empty columns does nothing when the dataset has columns but no rows.[3] In KNIME, the Constant Value Column Filter can be given a minimum number of rows that a table must have to be considered for filtering.[5] Set a floor on rows before trusting the verdict: in a small sample, a column can hold a single value by chance. (Meridian's view) Which columns go depends on the data, so the same step can return different columns on different loads; in a pipeline others rely on, run it once, record the columns it dropped, and drop those by name from then on. (Meridian's view)

The method

In KNIME, the Constant Value Column Filter removes columns that exclusively contain duplicates of the same value, such as only zeroes, only identical strings or only missing cells.[5] In RapidMiner, Filter Attributes with Missing Values keeps special attributes, such as id and label, by default, unless it is set to include them.[6] In scikit-learn, Boolean features are treated as Bernoulli random variables with variance p(1 - p), so a threshold of .8 * (1 - .8) removes features that are one or zero in more than 80% of samples.[1]

In DuckDB, count(arg) returns the number of rows where arg is not NULL, while count() returns the number of rows.[7] A column's share of missing values is then one minus count of the column over count of rows, and a column with no more than one distinct non-missing value is constant. (Meridian's view)

Sources

  1. [1]1.13. Feature selection — scikit-learn 1.9.1 documentationdocumentation
  2. [2]Missing Value Column Filterdocumentation
  3. [3]REMOVE EMPTY COLUMNS [EasyMorph Help]documentation
  4. [4]DropNullFields class - AWS Gluedocumentation
  5. [5]Constant Value Column Filterdocumentation
  6. [6]Filter Attributes with Missing Values - Rapidminer Documentationdocumentation
  7. [7]Aggregate Functions – DuckDBdocumentation