Shape / columns · All operations

One-hot encode a column

A variant of Encode categories as numbers

Turn each distinct value of a column into its own 0 or 1 indicator column.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

One-hot encode a column with a 0 or 1 column for each distinct value, nulls included

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
PIVOT (SELECT order_id, customer, coalesce(region, '(missing)') AS region, 1 AS present FROM orders)
ON region USING coalesce(max(present), 0) GROUP BY order_id, customer;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(column_name ORDER BY column_name) FROM (DESCRIBE result)) = ['(missing)', 'customer', 'east', 'north', 'order_id', 'south', 'west'] AND (SELECT count(*) = 10 AND min("(missing)" + east + north + south + west) = 1 AND max("(missing)" + east + north + south + west) = 1 AND sum(north) = 3 AND sum(south) = 2 AND sum(east) = 2 AND sum(west) = 2 AND sum("(missing)") = 1 FROM result) AND (SELECT "(missing)" FROM result WHERE order_id = 9) = 1) THEN 'as expected' ELSE error('the region was not spread into one 0 or 1 column for each of its four values and the missing one, with exactly one set in each of the ten rows') 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 region was not spread into one 0 or 1 column for each of its four values and the missing one, with exactly one set in each of the ten rows”.
  • 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)
  • One to Manyread 2026-09-27

    One-hot encode: turn each possible value of a column into a 0/1 column

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

    One-hot encode a column and record the step in PMML

mlpack (1)
Polars (1)
  • DataFrame.to_dummiesread 2026-09-27

    One-hot encode columns into an indicator column per distinct value

RapidMiner Studio (2)
  • Nominal to Binominalread 2026-09-27

    Turn each value of a nominal column into its own true/false column

  • One-Hot Encodingread 2026-09-27

    One-hot encode nominal columns, dropping those with too many values; returns a reusable model

Trifacta (3)

Background

One-hot encoding turns a column with n possible values into n binary columns, with a 1 in the column for the row's value and 0 in the others.[1] One-hot encoding is also known as one-of-K or dummy encoding.[1]

When to use it

Ordinal encoding of nominal values, which have no quantitative relationship, can create a fictional order, so one-hot encoding is often applied to nominal variables instead.[2] Because one-hot encoding creates a new variable for each unique value, a column with many unique values can create a 'big p' problem of too many predictors.[2] Check how many distinct values a column holds before spreading it, and group the rare ones first if the count runs into the hundreds. (Meridian's view)

If a regression includes a dummy for every category as well as a constant term, the dummies sum to 1 on every row and cause perfect multicollinearity, the dummy variable trap.[3] scikit-learn's OneHotEncoder can drop one category per feature, which avoids co-linearity in non-regularised regression such as LinearRegression.[1]

pandas' get_dummies encodes missing (NA) values as all zeros unless dummy_na is set, which adds a NaN indicator column.[4] scikit-learn's OneHotEncoder raises an error by default when it meets a category that was not present during fitting.[5] When scikit-learn's OneHotEncoder drops a category and ignores unknown ones, an unknown category gets the same all-zero encoding as the dropped category.[1] Fix the list of categories when the encoding is learnt and encode later tables against that list, so a new or missing value cannot silently add or remove a column. (Meridian's view)

The method

In digital circuits and machine learning, a one-hot is a group of bits whose only legal combinations have a single 1 and every other bit 0.[2] In statistics, dummy variables are a similar technique for representing categorical data.[2] In natural language processing, a one-hot vector is a 1 × N vector that distinguishes each word in a vocabulary, with a single 1 in the cell that identifies the word.[2]

DuckDB's PIVOT statement separates the distinct values of a column into their own columns, each calculated by an aggregate over the rows that match that value.[6] DuckDB's PIVOT fills a column with NULL where its combination of values is not present in the underlying data.[6] If a DuckDB PIVOT statement leaves out the USING clause, its aggregate defaults to count(*).[6] For a one-hot table, wrap the aggregate in coalesce so an absent value reads 0, and use max rather than a count so a repeated value still reads 1. (Meridian's view) scikit-learn's OneHotEncoder returns a SciPy sparse matrix in Compressed Sparse Row (CSR) format by default.[5]

Sources

  1. [1]8.3. Preprocessing data — scikit-learn 1.9.1 documentationdocumentation
  2. [2]One-hot - Wikipediasecondary source
  3. [3]Dummy variable (statistics) - Wikipediasecondary source
  4. [4]pandas.get_dummies — pandas 3.1.0 documentationdocumentation
  5. [5]OneHotEncoder — scikit-learn 1.9.1 documentationdocumentation
  6. [6]PIVOT Statement – DuckDBdocumentation