# One-hot encode a column

Shape / columns

A variant of [Encode categories as numbers](https://meridian.online/operations/encode-categories-as-numbers).

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

Page: <https://meridian.online/operations/encode-categories-as-numbers/one-hot-encode-a-column>

## Status

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.

## 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: [`One to Many`](https://api.hub.knime.com/nodes/*IbXHdKHlLVT7F-h1?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`One to Many (PMML)`](https://api.hub.knime.com/nodes/*TI1mrcUChRSuITG_?details=full) (read 2026-09-27)
- mlpack: [`mlpack_preprocess_one_hot_encoding`](https://www.mlpack.org/doc/user/bindings/cli.html) (read 2026-09-27)
- Polars: [`DataFrame.to_dummies`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27)
- RapidMiner Studio: [`Nominal to Binominal`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/types/nominal_to_binominal.html) (read 2026-09-27)
- RapidMiner Studio: [`One-Hot Encoding`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/types/one_hot_encoding.html) (read 2026-09-27)
- Trifacta: [`Convert values to columns`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)
- Trifacta: [`One hot encode`](https://help.alteryx.com/aac/en/trifacta-classic/reference/transformation-reference.html) (read 2026-09-27)
- Trifacta: [`Valuestocols Transform`](https://help.alteryx.com/aac/en/trifacta-classic/wrangle-language/language-appendices/transforms.html) (read 2026-09-27)