Categories: canonical values, mapping tables and encoding for a model

A category is a value from a list, and the list is the asset

status, city, plan, channel: columns whose values come from a set rather than a range. A number column is cleaned by casting; a category column is cleaned by deciding what the set is and mapping everything onto it. The set is the thing to write down, because the day it exists only inside a long CASE expression is the day two reports disagree about how many plans there are.

A mapping table instead of a CASE

Bombay, Mumbai, MUMBAI, Mumbai, MH and Bombay (Mumbai) are one city. Normalising case and whitespace, as the text lesson showed, collapses three of them. The other two are a fact somebody has to declare, and the place to declare it is a table:

CREATE TABLE city_map (
  variant   text PRIMARY KEY,   -- the normalised key as it appears in the data
  canonical text NOT NULL
);
INSERT INTO city_map VALUES ('bombay', 'Mumbai'), ('mumbai', 'Mumbai'), ('mumbai, mh', 'Mumbai');

SELECT p.*, coalesce(m.canonical, p.city_key) AS city
FROM payments_clean p
LEFT JOIN city_map m ON m.variant = p.city_key;

The LEFT JOIN is the important part. A variant that is not in the table comes through unchanged, and a query for WHERE m.variant IS NULL lists every value the map has not seen yet, with its frequency. That list is the to-do list, it gets shorter every month, and adding a row to a table is something a non-technical colleague can do while editing a CASE is not.

Cardinality decides what you can do with it

count(DISTINCT city) is the first thing to know about a category. A status with 4 values and a postcode with 19,000 values are both categories and nothing else about handling them is the same. Low-cardinality columns can be pivoted into one column each, listed in a report, and used as-is by most tools. High-cardinality columns cannot be pivoted, cannot be listed, and need a different treatment before a model sees them.

What a model needs

Most models take numbers. A column of city names has to become numbers, and there are three ways, each with a mechanism worth understanding.

One-hot encoding makes one 0/1 column per value: is_mumbai, is_delhi, is_pune. In SQL that is a CASE per value, or count(*) FILTER (WHERE city = 'Mumbai') in a grouped query. It is faithful and it does not scale: 19,000 postcodes become 19,000 columns, nearly all zero on every row. The free path for the mechanics is pandas.get_dummies or scikit-learn’s OneHotEncoder, but the decision about which values to encode is a SQL decision about cardinality.

Integer codes replace each value with a number: Mumbai 1, Delhi 2, Pune 3. Compact, and wrong for most models, because a linear model or a distance-based one will treat Pune as “more” than Mumbai and halfway between Delhi and something else. The numbers have an order that the cities do not. Integer codes are correct only for ordered categories (small, medium, large; bronze, silver, gold), and then the order must be the one you assign, not the alphabetical one a tool picks.

Frequency and target encoding replace each value with a statistic about it: how often it appears, or the average outcome for rows with that value. Both are one SQL query.

SELECT city, count(*) AS city_freq, avg(churned::int) AS city_churn_rate
FROM customers GROUP BY city;

Frequency encoding is safe. Target encoding is powerful and is the most common source of leakage in tabular work, because a row’s own outcome contributes to the average it is then given as a feature. The rate must be computed on training rows only, excluding the row itself, or the model learns to read its own answer. The leakage lesson returns to this.

Rare values and new values

A category with 300 values where 280 of them appear fewer than ten times is better encoded as the top 20 plus other. Decide the threshold in SQL, from counts, and write the list of kept values into a table so that next month’s data is encoded the same way. A value that appears for the first time at prediction time, a new city, must map to other and not crash the pipeline, which the mapping-table approach handles by construction and a hard-coded CASE does not.

The habit

For every category column, write down three things: the canonical list, the mapping from variants to it, and the encoding rule for a model. All three live in tables or in one view, so that the report and the model agree on what a city is.