Shape / columns · All operations

Collapse indicator columns into one

A variant of Encode categories as numbers

Turn a set of 0 or 1 indicator columns back into one column naming the indicator that is set.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Collapse indicator columns into one column naming the one that is set

SQL
CREATE TEMP TABLE wide AS SELECT * FROM (VALUES
  (1, 1, 0, 0), (2, 0, 1, 0), (3, 0, 0, 1), (4, 0, 0, 0), (5, 1, 0, 0), (6, 1, 1, 0), (7, 1, 1, NULL)
) t(id, widget, gadget, gizmo);
-- none set gives NULL; two or more set (rows 6 and 7) gives 'multiple', so the collapse never picks one of them silently.
-- An unknown indicator (row 7's NULL gizmo) counts as not set: a plain sum would be NULL there, and the row would become 'widget'
CREATE TEMP TABLE result AS
SELECT id, CASE WHEN coalesce(widget, 0) + coalesce(gadget, 0) + coalesce(gizmo, 0) > 1 THEN 'multiple'
                WHEN widget = 1 THEN 'widget' WHEN gadget = 1 THEN 'gadget' WHEN gizmo = 1 THEN 'gizmo' END AS product FROM wide;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'product'] AND (SELECT list(product ORDER BY id) FROM result) = ['widget', 'gadget', 'gizmo', NULL, 'widget', 'multiple', 'multiple']) THEN 'as expected' ELSE error('the three indicator columns were not collapsed to one product column, with NULL where none is set and multiple where two are') 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 three indicator columns were not collapsed to one product column, with NULL where none is set and multiple where two are”.
  • 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).

KNIME Analytics Platform (2)
  • Many to Oneread 2026-09-27

    Collapse several indicator columns into one column naming the column that matched

  • Many to One (PMML)read 2026-09-27

    Collapse indicator columns into one column while recording the step in PMML

Background

Collapsing indicator columns turns a set of 0 or 1 columns back into one column naming the category whose indicator is set.[1][2]

When to use it

When a one-hot encoded variable is a model's output, its values may need converting back into categories to present them in an application.[3] Reach for the collapse whenever a table arrives with one yes-or-no column per option and the question you are asking is which option each row chose. (Meridian's view)

pandas' from_dummies accepts a default_category, the implied category for a row whose dummies are all zero.[1] Without a default category, pandas' from_dummies raises an error for a row with no category assigned, and also for a row with more than one.[1] KNIME's Many to One node puts a missing value in the result when no column matches, and fails with no output when several columns match.[2] scikit-learn's OneHotEncoder maps an all-zero row back to the dropped category if a category was dropped, and to None if not.[4] Decide before collapsing what a row with no indicator set, and a row with two, should become, and keep both cases visible rather than letting the first matching column win. (Meridian's view) Count the rows with more than one indicator set before you trust the result: that count tells you whether the columns were truly exclusive. (Meridian's view)

The method

In digital circuits, a priority encoder converts from a one-hot representation to binary.[3] Collapsing indicator columns is the same move made on a table: find the position that is set and write down its name. (Meridian's view)

pandas' from_dummies reads dummy column names such as prefix_A and prefix_B, and its sep argument names the character that separates the prefix from the category.[1] pandas' from_dummies expects the dummy columns to hold only 1s and 0s, or boolean values, and raises an error when they contain missing (NA) values.[1]

Sources

  1. [1]pandas.from_dummies — pandas 3.1.0 documentationdocumentation
  2. [2]Many to Onedocumentation
  3. [3]One-hot - Wikipediasecondary source
  4. [4]8.3. Preprocessing data — scikit-learn 1.9.1 documentationdocumentation