Shape / columns · All operations

Bin by chosen boundaries

A variant of Group values into bins

Assign values to named ranges set by given break points, bounds or a threshold.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Assign amounts to named ranges set by break points, and to a threshold

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,
       CASE WHEN amount < 100 THEN 'low' WHEN amount < 300 THEN 'mid' WHEN amount >= 300 THEN 'high' END AS band,
       amount >= 200 AS over_threshold
FROM orders;
SELECT * FROM result ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(band ORDER BY order_id) FROM result) = ['mid', 'low', 'high', NULL, 'low', 'high', 'low', 'high', 'low', 'mid'] AND (SELECT list(over_threshold ORDER BY order_id) FROM result) = [false, false, true, NULL, false, true, false, true, false, true]) THEN 'as expected' ELSE error('the amounts were not placed in low below 100, mid from 100 to below 300 and high from 300, or the threshold 200 was not applied') 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 were not placed in low below 100, mid from 100 to below 300 and high from 300, or the threshold 200 was not applied”.
  • 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).

Apache Hop (1)
  • Number rangeread 2026-09-27

    Assign numeric values to named ranges by lower and upper bounds

Ibis (1)
  • Bucketread 2026-09-27

    Assign numeric values to buckets set by a list of edges

KNIME Analytics Platform (3)
  • Binner (Dictionary)read 2026-09-27

    Label values by the interval they fall into, with bounds and labels from a second table

  • 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)
Pentaho Data Integration (1)
  • Number rangeread 2026-09-27

    Assign a numeric field to named ranges

Polars (1)
  • Expr.cutread 2026-09-27

    Bin continuous values into categories by given break points

RapidMiner Studio (2)

Background

In pandas, a sequence of scalars given as cut's bins defines the bin edges, allowing for non-uniform width, and the range of the values is not extended.[1]

When to use it

Reach for chosen boundaries when the bands mean something outside the data: tax brackets, age groups, a service-level threshold, or the bands a report has always used. (Meridian's view) Prefer fixed edges when counts are compared from one period to the next, since edges computed from each period's own data move with it. (Meridian's view)

PostgreSQL's width_bucket, given an array of thresholds, needs the array sorted smallest first, or unexpected results will be obtained.[2] In pandas's cut, bin edges that are not unique raise a ValueError unless duplicates are set to be dropped.[1] In pandas's cut, the include_lowest option decides whether values equal to the first bin edge are placed in the first bin.[1] RapidMiner's Discretize by User Specification defines each class by its upper limit, takes each lower limit from the class before, and assumes negative infinity below the first class.[3] Leave the first and last bands open-ended, so a value beyond the expected range still lands in a band rather than dropping out. (Meridian's view)

The method

In PostgreSQL, a CASE expression returns the result after the first condition that is true, and the remainder of the expression is not processed.[4] Write the bands as a ladder of upper limits in ascending order, so each WHEN needs only its upper limit. (Meridian's view)

DuckDB's histogram aggregate counts values against a list of upper boundaries, with bins that are left-open and right-closed.[5]

Sources

  1. [1]pandas.cut — pandas 3.1.0 documentationdocumentation
  2. [2]PostgreSQL: Documentation: 18: 9.3. Mathematical Functions and Operatorsdocumentation
  3. [3]Discretize by User Specification - Rapidminer Documentationdocumentation
  4. [4]PostgreSQL: Documentation: 18: 9.18. Conditional Expressionsdocumentation
  5. [5]Aggregate Functions – DuckDBdocumentation