Formatting, derived fields and preparing features

Why this matters

Cleaned data is correct; it is not yet ready. Most analyses need values the source never recorded: revenue calculated from quantity and price, an age band instead of a birth date, a customer's order count, a category turned into something a model can use. Creating these is the last step of preparation, and the step where business logic enters the data, which makes it the step most in need of being written down.

The exam asks about derived fields, binning, scaling, encoding and formatting. This lesson covers each, and ends with a distinction that prevents a common class of error: formatting is for output, not for storage.

The lesson

Calculated columns and the logic behind them

A calculated (or derived) column is computed from other columns. Common examples are:

• Arithmetic: revenue = quantity × unit price × (1 − discount).
• Date parts and differences: order month, day of the week, days between signup and first order.
• Conditional logic: a CASE expression in SQL, or IF in a spreadsheet, that labels orders over £100 as "large".
• Aggregates brought back to the row: each customer's total orders, attached to every order for comparison.

A derived column embeds a business rule, and business rules are where analyses disagree. "Revenue" before or after discount? Including or excluding returns? Tax? Two analysts who both calculate revenue correctly from different definitions will produce different numbers, and both will be right about their own definition.

So every derived column should have its definition recorded: the formula, the fields it uses, and the decisions it embeds. When the same measure is used in many places, it should be calculated once, in one place, such as a view or a semantic layer, rather than re-derived in each report.

A practical check for any derived column is to calculate it for a few rows by hand and compare.

Binning and bucketing continuous values

Binning (or bucketing) turns a continuous value into categories: ages into bands, order values into small, medium and large, days since last purchase into recency groups.

Bins make data easier to read and report, and they can reduce the influence of noise and outliers. They also discard information, since everyone aged 25 to 34 becomes identical, and the choice of boundaries can change conclusions.

Three decisions define a binning:

• Equal width: each bin covers the same range, such as 0–20, 20–40, 40–60. Simple, and with skewed data most values land in one bin.
• Equal frequency (quantile bins): each bin holds the same number of records, such as quartiles. Balanced, and the ranges can be uneven and awkward to read.
• Business-defined: boundaries that mean something, such as the age bands in a regulation or the price points in a catalogue.

Whatever the choice, the boundaries must be unambiguous. If one band is "18–24" and the next "24–34", a 24-year-old belongs to both. State whether each boundary is inclusive, and check that every value falls into exactly one bin.

Normalisation and standardisation of scales

Columns measured on different scales cannot be compared directly, and some analyses and models are distorted by scale: income in tens of thousands swamps age in tens.

Min-max normalisation rescales values to a fixed range, usually 0 to 1: (value − minimum) ÷ (maximum − minimum). The smallest value becomes 0 and the largest 1. It keeps the shape of the distribution, and it is sensitive to outliers, because a single extreme value sets the maximum and squeezes everything else together.

Standardisation (z-score scaling) rescales values to have a mean of 0 and a standard deviation of 1: (value − mean) ÷ standard deviation. Values are then expressed as distances from the average in standard deviations, which makes different columns comparable.

Two cautions apply. Scaling parameters, such as the minimum, maximum or mean, must come from the data the analysis or model is built on and then be applied unchanged to new data; recalculating them on new data makes old and new values incomparable. And the word normalisation also means organising database tables, covered in domain 1, so the context decides which is meant.

Encoding categories for analysis and models

Many statistical methods and models need numbers, not text categories, so categories are encoded.

• Label encoding assigns each category an integer: North = 1, South = 2, East = 3, West = 4. It is compact, and it implies an order and a distance that may not exist: a model may treat West as "more" than North.
• One-hot encoding creates a separate 0/1 column for each category: is_north, is_south and so on. It implies no order, and it adds a column per category, which becomes unwieldy with many categories.
• Ordinal encoding is label encoding used where an order is real, such as small = 1, medium = 2, large = 3.

The right choice depends on whether the category has a natural order and how many values it has. Encoding should also define what happens to a category that appears later and was not present when the encoding was built, such as a new region, rather than failing or silently mapping it to something.

Number, currency and date formatting for output, not storage

Formatting controls how a value is displayed: thousands separators, decimal places, currency symbols, percentage signs, date layouts such as 4 March 2025.

Formatting belongs at the end, in the report or chart, not in the stored data. A value stored as the text £1,234.50 cannot be summed, sorted numerically or converted to another currency without parsing it back. A date stored as 4 March 2025 cannot be sorted chronologically as text. A percentage stored as the text 12% cannot be averaged.

The rule is to store values in their raw, typed form, such as the number 1234.5, the date 2025-03-04, the fraction 0.12, and to apply formatting only where the value is presented. Formatting should also respect the audience: decimal separators, date order and currency differ between countries, and a report for an international audience needs to state its conventions.

Rounding is a special case of formatting with a trap: round for display, never before calculating. Summing values that were rounded first produces totals that do not match the sum of the underlying data, which is how a table's rows end up not adding up to its total.