Shape / columns · All operations

Group values into bins

Discretise, bucket, tile, histogram bins.

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

one clause of SQL

The DuckDB step

Equal-width bins

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
SELECT order_id, amount, floor(amount / 100) * 100 AS bin_floor FROM orders WHERE amount IS NOT NULL;
SELECT * FROM result;
SELECT equi_width_bins(min(amount), max(amount), 5, true) AS edges FROM orders;
SELECT CASE WHEN ((SELECT count(*) FILTER (WHERE bin_floor = 0) = 4 AND count(*) FILTER (WHERE bin_floor = 500) = 2 AND max(bin_floor) FILTER (WHERE order_id = 10) = 200 FROM result)) THEN 'as expected' ELSE error('the amounts did not fall in the bins expected') 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 amounts did not fall in the bins expected”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Bin by chosen boundaries
Assign values to named ranges set by given break points, bounds or a threshold.
Bin into groups of equal size
Assign each row to a tile or quantile so that each bin holds about the same number of rows.

Bin into equal-width ranges

Assign each value to one of a number of ranges of the same width.

Bin against a target column

Choose bin boundaries that best separate the values of a class or label column.

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 (2)

Multi-Field Binningread 2026-09-27
Bin several numeric fields at once into equal-record or equal-interval tiles
Tileread 2026-09-27
Assign each record a tile number by one of five methods: equal records, equal sum, manual, ...

Apache Hop (1)

Number rangeread 2026-09-27
Assign numeric values to named ranges by lower and upper bounds

Bruin (1)

bruin.width_bucket(expr, min_value, max_value, num_buckets)read 2026-09-27
Emit a bucket number expression for a numeric value

dbt-utils (1)

width_bucketread 2026-09-27
Assign a numeric expression to one of N equal-width buckets between min and max

Duckle (1)

Bucketizeread 2026-09-25
bin a number into N equal-width buckets

EasyMorph (1)

Create bucketsread 2026-09-27
Assign each row to a fixed-width range of a numeric column

Ibis (1)

Bucketread 2026-09-27
Assign numeric values to buckets set by a list of edges

KNIME Analytics Platform (9)

Binnerread 2026-09-27
Bin numeric columns by equal width, equal frequency, quantiles or custom cut-offs
Binner (Apply)read 2026-09-27
Apply an existing binning model to numeric columns
Binner (Dictionary)read 2026-09-27
Label values by the interval they fall into, with bounds and labels from a second table
CAIM Applierread 2026-09-27
Discretise columns using a binning model produced by CAIM Binner
CAIM Binnerread 2026-09-27
Supervised discretisation: choose bin boundaries against a class column with CAIM
DB Binnerread 2026-09-27
In-database: bin numeric values into labelled intervals, automatic or custom, as SQL
DB Binner (Apply)read 2026-09-27
In-database: apply an existing binning model to numeric columns as SQL
Numeric Binnerread 2026-09-27
Assign numeric values to manually defined named intervals per column
Numeric Binner (PMML)read 2026-09-27
Assign numeric values to named intervals and record the step in PMML

mlpack (1)

mlpack_preprocess_binarizeread 2026-09-27
Convert each value in chosen dimensions to 0 or 1 by a threshold

Pentaho Data Integration (1)

Number rangeread 2026-09-27
Assign a numeric field to named ranges

Polars (2)

Expr.cutread 2026-09-27
Bin continuous values into categories by given break points
Expr.qcutread 2026-09-27
Bin continuous values into categories by their quantiles

RapidMiner Studio (6)

Discretize by Binningread 2026-09-27
Bin numeric columns into a given number of equal-width bins
Discretize by Entropyread 2026-09-27
Bin numeric columns at boundaries that minimise label entropy
Discretize by Frequencyread 2026-09-27
Bin numeric columns into a given number of equal-frequency bins
Discretize by Sizeread 2026-09-27
Bin numeric columns so that each bin holds a given number of rows
Discretize by User Specificationread 2026-09-27
Bin numeric columns into user-defined classes, each given by its upper limit
Numerical to Binominalread 2026-09-27
Convert numeric columns to true/false by whether the value falls inside a given range

Tableau Prep (2)

NTILEread 2026-09-27
Distribute the rows of a partition into a number of tiles
Tileread 2026-09-27
Distribute rows into a number of buckets by ordered fields within optional groups

Trifacta (1)

Bin columnread 2026-09-27
Bin numeric values into ranges of equal or custom size

Background

Binning groups a numeric column's values into a smaller number of intervals, called bins, turning a continuous variable into a categorical one.^[1]^[2] Bins are typically of equal width or hold equal shares of the data, and they can also follow edges given by hand.^[3]^[2]

When to use it

Binning can turn people's ages into a smaller number of age ranges.^[2]^[1] Binning is also used in machine learning to speed up decision-tree boosting, in algorithms such as Microsoft's LightGBM and scikit-learn's histogram-based gradient boosting.^[1] Many machine learning algorithms are known to produce better models by discretising continuous attributes.^[3]

Whenever continuous data is discretised there is always some discretisation error, and the goal is to keep it negligible for the purpose at hand.^[3] Keep the raw column beside the binned one, so the bins can be drawn again differently without going back to the source. (Meridian's view) There is no best number of bins, and different bin sizes can reveal different features of the data.^[4] When a few large values stretch the range, count the rows in each bin before relying on the bins. (Meridian's view)

PostgreSQL's width_bucket returns 0 for an input below the low bound and the bucket count plus 1 for an input at or above the high bound.^[5] In pandas's cut, a value outside the range of the bins is not assigned to any bin and is NA in the result, as is any value that was NA to begin with.^[2] Decide, and write down, which side of an edge a value on it belongs to, and where values outside the range and missing values go. (Meridian's view)

The method

With nice set to true, DuckDB's equi_width_bins may adjust the minimum, the maximum and the bin count to produce more aesthetically pleasing results.^[6] Given a number of bins, pandas's cut makes equal-width bins over the range of the values, extended by 0.1% on each side to include the minimum and maximum.^[2]

Grouping data is at least as old as Graunt's work in the 17th century, but no systematic guidelines for the number of bins were given until Sturges's work in 1926.^[4] Sturges's rule is derived from a binomial distribution and implicitly assumes an approximately normal distribution.^[4] The Freedman–Diaconis rule sets the bin width from the interquartile range, which is less sensitive to outliers than the standard deviation used in Scott's rule.^[4] The term histogram was first introduced by Karl Pearson in lectures delivered in 1892 at University College London.^[4]

Fayyad and Irani's method selects the bin boundary that minimises the entropy in the induced partitions, then applies itself recursively to both partitions until a stopping criterion is reached.^[7]^[3] Usama M. Fayyad and Keki B. Irani derived a criterion based on the minimum description length principle for deciding the partitioning of intervals.^[8] scikit-learn's KBinsDiscretizer offers a third strategy beside equal widths and equal counts, kmeans, in which the values in each bin share the nearest centre of a one-dimensional k-means cluster.^[9]

Compared with the alternatives

Binning is a generalisation of rounding.^[1] Rather than evenly spaced bins, some applications prefer to vary the bin width, which avoids bins with low counts.^[4]

Sources

[1]Data binning - Wikipediasecondary source
[2]pandas.cut — pandas 3.1.0 documentationdocumentation
[3]Discretization of continuous features - Wikipediasecondary source
[4]Histogram - Wikipediasecondary source
[5]PostgreSQL: Documentation: 18: 9.3. Mathematical Functions and Operatorsdocumentation
[6]Utility Functions – DuckDBdocumentation
[7]Discretize by Entropy - Rapidminer Documentationdocumentation
[8]Multi-Interval Discretization of Continuous-Valued Attributes for Classification Learningsecondary source
[9]KBinsDiscretizer — scikit-learn 1.9.1 documentationdocumentation