# Encode categories as numbers Shape / columns one-hot, dummy, label or ordinal encoding Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step one-hot encode without naming the values 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, product, 1 AS present FROM orders) ON product USING coalesce(max(present), 0) GROUP BY order_id ORDER BY order_id; SELECT * FROM result; SELECT CASE WHEN ((SELECT count(*) = 10 AND min(gadget + gizmo + widget) = 1 AND max(gadget + gizmo + widget) = 1 AND sum(widget) = 4 FROM result)) THEN 'as expected' ELSE error('a row does not carry exactly one indicator, or widget is not set four times') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - [Collapse indicator columns into one](https://meridian.online/operations/encode-categories-as-numbers/collapse-indicator-columns-into-one): Turn a set of 0 or 1 indicator columns back into one column naming the indicator that is set. - Number each category: Replace each category with an integer code, and map the codes back to categories when needed. - [One-hot encode a column](https://meridian.online/operations/encode-categories-as-numbers/one-hot-encode-a-column): Turn each distinct value of a column into its own 0 or 1 indicator column. - Other encoding steps: Pack columns into feature vectors or work with categorical types as part of preparing data for a model. - [Encode categories by the target](https://meridian.online/operations/encode-categories-as-numbers/encode-categories-by-the-target): Replace each category with a statistic of the label for that category, such as its mean or weight of evidence. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Alteryx Designer: [`Transformation`](https://help.alteryx.com/current/en/designer/tools/alteryx-intelligence-suite/machine-learning/transformation-tool.html) (read 2026-09-27) - KNIME Analytics Platform: [`Category to Number`](https://api.hub.knime.com/nodes/*WFiVV3UvOmAjUNSr?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Category to Number (Apply)`](https://api.hub.knime.com/nodes/*PZnqZ-UrUIsHyco1?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Create Bit Vector`](https://api.hub.knime.com/nodes/*CMq1zWZqWiDfnXiX?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Create Byte Vector`](https://api.hub.knime.com/nodes/*SyBmsyFDsF2faRFI?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Many to One`](https://api.hub.knime.com/nodes/*XbOgJJl-gFiDMtr1?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Many to One (PMML)`](https://api.hub.knime.com/nodes/*pu1W2Sq3367PuKdk?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Number to Category (Apply)`](https://api.hub.knime.com/nodes/*1mEYxPoHmDXSWziT?details=full) (read 2026-09-27) - 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: [`Normalizing labels`](https://www.mlpack.org/doc/user/preprocessing.html) (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) - Polars: [`Expr.cat (group)`](https://docs.pola.rs/api/python/stable/reference/expressions/categories.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: [`Nominal to Numerical`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/types/nominal_to_numerical.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) - RapidMiner Studio: [`Target Encoding`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/types/target_encoding.html) (read 2026-09-27) - RapidMiner Studio: [`Weight of Evidence`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/blending/weight_of_evidence.html) (read 2026-09-27) - Talend: [`tModelEncoder`](https://help.qlik.com/talend/en-US/components/8.0/machine-learning/machine-learning-component) (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)