Shape / columns · All operations

Encode categories as numbers

One-hot, dummy, label or ordinal encoding.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

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.
  • The statement checks its own result and raises an error when it is wrong: “a row does not carry exactly one indicator, or widget is not set four times”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Number each category

Replace each category with an integer code, and map the codes back to categories when needed.

Other encoding steps

Pack columns into feature vectors or work with categorical types as part of preparing data for a model.

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 (1)
  • Transformationread 2026-09-27

    Pipeline prep: set data types, fill missing values, select features, encode categories

KNIME Analytics Platform (9)
  • Category to Numberread 2026-09-27

    Map each category of a nominal column to an integer

  • Category to Number (Apply)read 2026-09-27

    Apply an existing category-to-integer mapping model to a table

  • Create Bit Vectorread 2026-09-27

    Build a bit-vector column from numeric or string columns, bit positions, or hex or binary text

  • Create Byte Vectorread 2026-09-27

    Build a byte-vector column from integer columns

  • 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

  • Number to Category (Apply)read 2026-09-27

    Reverse a category-to-number mapping, turning integers back into categories

  • 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 (2)
Polars (2)
  • DataFrame.to_dummiesread 2026-09-27

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

  • Expr.cat (group)read 2026-09-27

    7 expressions: get_categories, physical, to, len_chars, starts_with and others; work on categorical and enum values

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

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

  • Nominal to Numericalread 2026-09-27

    Convert nominal columns to numbers by dummy, effect or unique-integer coding

  • One-Hot Encodingread 2026-09-27

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

  • Target Encodingread 2026-09-27

    Replace nominal categories with the label's probability or mean per category, with smoothing

  • Weight of Evidenceread 2026-09-27

    Bin numeric columns and replace each bin with its weight-of-evidence value against the label

Talend (1)
  • tModelEncoderread 2026-09-27

    Turn columns into feature vectors with feature-processing algorithms, for the model training components

Trifacta (3)

Background

In regression, categorical variables must be converted to quantitative data before the data can be analysed, which is done through coding systems.[1] For ease in statistical processing, the values of a categorical variable may be given numeric indices, such as 1 to K for a variable with K possible values.[1] In machine learning, categorical variables are transformed during pre-processing, because many machine learning models need their input variables to be numeric.[2]

When to use it

In general, the numbers given to a categorical variable's values are arbitrary, with no significance beyond being a convenient label for a value.[1] Ordinal variables have a meaningful ordering, while nominal variables have no meaningful ordering.[1] Ordinal data can be converted to numbers through ordinal encoding, as when test ratings from A to F are ranked using numbers from 6 to 1.[2] Using ordinal encoding on nominal variables can potentially create a fictional ordinal relationship, since there is no quantitative relationship between their values.[2] In scikit-learn, integer category codes cannot be used directly with all estimators, because those estimators expect continuous input and would read the categories as ordered.[3]

Standard statistical models assume that the number of categories is known in advance, and changing the number of categories on the fly is tricky.[1] In scikit-learn, OrdinalEncoder passes missing values through as missing by default, and its encoded_missing_value parameter gives them a code instead.[3] In scikit-learn, OrdinalEncoder and OneHotEncoder can gather infrequent categories into a single output for each feature, through their min_frequency and max_categories parameters.[3]

The method

There are three main coding systems typically used for categorical variables in regression: dummy coding, effects coding and contrast coding.[1] In regression, only g - 1 of a categorical variable's g groups are coded, which minimises redundancy while still representing the complete data set.[1] In effects coding, the group of least interest is coded -1, and each group is compared with the mean of all groups combined rather than with a control group.[1] Contrast coding lets a researcher design a comparison for a specific research question, rather than having the coding system dictate the comparison.[1] The choice of coding system does not affect the F or R-squared statistics, but the interpretation of the regression coefficients varies with it.[1] Nonsense coding uses arbitrary values in place of the usual 0, 1 and -1 codes, and is not recommended, because it leads to uninterpretable statistical results.[1]

In scikit-learn, OrdinalEncoder transforms each categorical feature into one new feature of integers, from 0 to the number of categories minus 1.[3] Embeddings code categorical values into low-dimensional real-valued vector spaces, usually so that similar values are assigned similar vectors.[1]

Compared with the alternatives

In scikit-learn, target encoding suits categorical features with high cardinality, where one-hot encoding would inflate the feature space and make a downstream model more expensive.[3]

Sources

  1. [1]Categorical variable - Wikipediasecondary source
  2. [2]One-hot - Wikipediasecondary source
  3. [3]8.3. Preprocessing data — scikit-learn 1.9.1 documentationdocumentation