Shape / columns · All operations

Rescale a number column

Normalise, standardise, z-score, min-max, log transform.

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

Z-score and min-max

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,
       (amount - avg(amount) OVER ()) / stddev_samp(amount) OVER () AS z,
       (amount - min(amount) OVER ()) / (max(amount) OVER () - min(amount) OVER ()) AS minmax
FROM orders WHERE amount IS NOT NULL;
SELECT order_id, amount, round(z, 3) AS z, round(minmax, 3) AS minmax FROM result;
SELECT CASE WHEN ((SELECT abs(avg(z)) < 1e-9 AND abs(stddev_samp(z) - 1) < 1e-9 AND min(minmax) = 0 AND max(minmax) = 1 FROM result)) THEN 'as expected' ELSE error('z does not have mean 0 and deviation 1, or min-max does not span 0 to 1') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “z does not have mean 0 and deviation 1, or min-max does not span 0 to 1”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Apply a log or power transform

Apply a logarithm or a Box-Cox or Yeo-Johnson transform to make a column less skewed.

Apply a saved scaling

Rescale a new table with the parameters learnt from an earlier scaling.

Rescale by z-score or range

Rescale numeric columns to zero mean and unit variance, a chosen range, or another linear scheme.

Reverse a scaling

Map scaled values back to their original range.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Duckle (1)
  • Z-Scoreread 2026-09-25

    standardise a value against the mean and stddev of the whole input

KNIME Analytics Platform (6)
  • Denormalizerread 2026-09-27

    Reverse a normalisation using the model from Normalizer, restoring original ranges

  • Denormalizer (PMML)read 2026-09-27

    Reverse a normalisation described in a PMML model

  • Normalizerread 2026-09-27

    Normalise selected numeric columns by min-max, z-score or another chosen method

  • Normalizer (Apply)read 2026-09-27

    Normalise a table with the parameters of an existing normalisation model

  • Normalizer (PMML)read 2026-09-27

    Normalise numeric columns and record the transformation in PMML

  • Normalizer Apply (PMML)read 2026-09-27

    Normalise a table with parameters from a PMML model

mlpack (1)
  • mlpack_preprocess_scaleread 2026-09-27

    Scale features by one of six techniques; scalers can be saved, reapplied and inverted

RapidMiner Studio (6)
  • De-Normalizeread 2026-09-27

    Build the inverse of a normalisation model to map values back to the original scale

  • Generate Power Transformread 2026-09-27

    Apply a Box-Cox or Yeo-Johnson power transform, or its inverse, to numeric columns

  • Logarithmread 2026-09-27

    Apply a natural or base-n logarithm to series columns

  • Normalizeread 2026-09-27

    Rescale numeric columns by z-score, range, proportion or interquartile range

  • Normalize (Series)read 2026-09-27

    Normalise series columns

  • Scale by Weightsread 2026-09-27

    Multiply numeric columns by their feature weights and drop columns with weight zero

Trifacta (2)
  • Scale to meanread 2026-09-27

    Rescale a column to zero mean and unit variance

  • Scale to min maxread 2026-09-27

    Rescale a numeric column to a chosen minimum and maximum

Background

Feature scaling, also known as data normalisation, is a method used to normalise the range of the variables or features of data.[1] Min-max scaling rescales a feature's range to [0, 1] or [−1, 1], and standardisation gives each feature zero mean and unit variance.[1] A power transform is a monotonic transformation of data, used to stabilise variance and make the data more like a normal distribution.[2]

When to use it

Many classifiers measure the distance between two points by the Euclidean distance, and a feature with a broad range of values then governs that distance.[1] Feature scaling is also important when regularisation is part of the loss function, so that coefficients are penalised appropriately.[1] The k-means clustering algorithm is sensitive to feature scales.[1] In scikit-learn, a feature whose variance is orders of magnitude larger than the others might dominate a learning algorithm's objective function.[3]

In scikit-learn, scaling with the mean and variance is likely not to work very well on data with many outliers, and RobustScaler uses more robust estimates of the centre and range instead.[3] In scikit-learn's StandardScaler, a feature whose variance is zero cannot reach unit variance and is left as it is, with a scaling factor of 1.[4] Check for a column holding a single repeated value before a min-max or z-score step, and decide on purpose what it should become. (Meridian's view)

A standard score is defined with the population mean and standard deviation, and is estimated with the sample mean and sample standard deviation when those are unknown.[5] The distinction between population and sample statistics in a standard score often is not made, though it should always be stated.[5] In DuckDB, stddev_samp is the square root of var_samp, the sample variance with Bessel's bias correction, while stddev_pop is the square root of var_pop, which has no bias correction.[6] scikit-learn's StandardScaler uses a biased estimator for the standard deviation, equivalent to numpy.std(x, ddof=0), and notes that the choice is unlikely to affect model performance.[4] Say which deviation a z-score was divided by, sample or population, so that z-scores of the same column from two tools can be compared. (Meridian's view)

In scikit-learn, data transforms used when training a model must also be used on later data, test or production, or the feature space changes and the model cannot perform effectively.[7] scikit-learn describes data leakage as using information that would not be available at prediction time, which gives overly optimistic performance estimates.[7] scikit-learn's guidance is that preprocessing transformations should be learnt from the training data only, so a normalising average should be the training subset's, not that of all the data.[7] In scikit-learn, a fitted StandardScaler stores the mean and standard deviation learnt from the training set and uses them on later data through its transform method.[4] In scikit-learn, a fitted MinMaxScaler applies the same shift and scale to new data, and in its documented example a new value below the training minimum becomes -1.5.[3] scikit-learn's StandardScaler has an inverse_transform method that scales data back to the original representation.[4] Keep the parameters of every scaling beside its output, so that the same step can be applied to new rows and undone when the results go back to a reader. (Meridian's view)

The logarithm and square root transformations are commonly used for positive data.[8] Power transforms, and the logarithm in particular, can often induce symmetry in values that range over several orders of magnitude, such as incomes or rainfall volumes.[8] The logarithm is often favoured because its result is easy to interpret in terms of fold changes.[8] The Box–Cox transform can only be applied to strictly positive data, while the Yeo–Johnson transformation also allows zero and negative values.[3][2] Box–Cox transformations can make big improvements in model fit, but data that are rather heavy-tailed can still make the assumption of normality unrealistic.[2] In scikit-learn's examples, power transforms give very Gaussian-like results on some distributions and are ineffective on others.[3] Look at a column's distribution before and after a power transform, rather than trusting that the transform has made it normal. (Meridian's view)

The method

Min-max scaling subtracts the column's minimum from each value and divides the result by the difference between the maximum and the minimum.[1] To rescale to an arbitrary range from a to b, the min-max result is multiplied by b minus a, and a is added.[1] Mean normalisation subtracts the mean from each value and divides by the maximum minus the minimum.[1] In scikit-learn, MaxAbsScaler divides each feature by its largest maximum value, so that the training data lies within the range [-1, 1].[3]

Standardisation subtracts each feature's mean from its values and then divides them by the feature's standard deviation.[1] A z-score is the number of standard deviations by which a value lies above or below the mean, positive above it and negative below it.[5] Because the numerator and denominator of a z-score share units of measure, the units cancel and the z-score is a dimensionless quantity.[5] Robust scaling, also known as standardisation using the median and the interquartile range (IQR), subtracts the median and divides by the IQR so that it is robust to outliers.[1]

A power transform is a family of functions that create a monotonic transformation of data using power functions.[2] The power transformation family includes the logarithm, square root and reciprocal transformations as special cases, and is parameterised by a value λ.[8] Estimating λ in regression analysis is known as the Box–Cox transformation, and since the family includes the identity, it can also show that the data are best analysed untransformed.[8] Box and Cox (1964) introduced the geometric mean into the power transformation.[2] The Yeo–Johnson transformation was published by In-Kwon Yeo and Richard A. Johnson in 2000 in Biometrika, as A New Family of Power Transformations to Improve Normality or Symmetry.[2] In scikit-learn's PowerTransformer, λ is determined through maximum likelihood estimation, and the output is given zero mean and unit variance by default.[3]

Compared with the alternatives

The word normalising is used for converting a value into a standard score, but it can refer to many types of ratios.[5] In scikit-learn, a quantile transform smooths out unusual distributions and is less influenced by outliers than scaling methods, but it distorts correlations and distances within and across features.[3] Rescaling units, such as to millions of people, does not spread out points crowded in one corner of a chart, while a logarithmic transformation spreads them more uniformly.[8]

Sources

  1. [1]Feature scaling - Wikipediasecondary source
  2. [2]Power transform - Wikipediasecondary source
  3. [3]8.3. Preprocessing data — scikit-learn 1.9.1 documentationdocumentation
  4. [4]StandardScaler — scikit-learn 1.9.1 documentationdocumentation
  5. [5]Standard score - Wikipediasecondary source
  6. [6]Aggregate Functions – DuckDBdocumentation
  7. [7]12. Common pitfalls and recommended practices — scikit-learn 1.9.1 documentationdocumentation
  8. [8]Data transformation (statistics) - Wikipediasecondary source

Used in

One Protocol uses this operation: