{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"categories-and-encoding","title":"Categories: canonical values, mapping tables and encoding for a model","url":"https://addaly.com/learn/data-and-sql/categories-and-encoding","position":52,"of":66,"minutes":9,"answer":"Clean a category with a mapping table that a LEFT JOIN can expose gaps in, choose the encoding by cardinality and by whether the values have an order.","keyIdea":"Clean a category with a mapping table that a LEFT JOIN can expose gaps in, choose the encoding by cardinality and by whether the values have an order, and never give a model integer codes for categories that are not ordered."},"body":{"format":"markdown","text":"## A category is a value from a list, and the list is the asset\n\n`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.\n\n## A mapping table instead of a CASE\n\n`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:\n\n```sql\nCREATE TABLE city_map (\n  variant   text PRIMARY KEY,   -- the normalised key as it appears in the data\n  canonical text NOT NULL\n);\nINSERT INTO city_map VALUES ('bombay', 'Mumbai'), ('mumbai', 'Mumbai'), ('mumbai, mh', 'Mumbai');\n```\n\n```sql\nSELECT p.*, coalesce(m.canonical, p.city_key) AS city\nFROM payments_clean p\nLEFT JOIN city_map m ON m.variant = p.city_key;\n```\n\nThe 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.\n\n## Cardinality decides what you can do with it\n\n`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.\n\n## What a model needs\n\nMost models take numbers. A column of city names has to become numbers, and there are three ways, each with a mechanism worth understanding.\n\n**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.\n\n**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.\n\n**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.\n\n```sql\nSELECT city, count(*) AS city_freq, avg(churned::int) AS city_churn_rate\nFROM customers GROUP BY city;\n```\n\nFrequency 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.\n\n## Rare values and new values\n\nA 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.\n\n## The habit\n\nFor 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."},"check":{"question":"A model is trained with `city` replaced by an integer code assigned alphabetically, so Ahmedabad is 1, Bengaluru is 2, Chennai is 3, and so on. Why is that a problem for a linear or distance-based model?","options":[{"text":"Integer codes overflow for high-cardinality columns, so cities beyond the first few thousand share codes and are confused with each other.","correct":false,"tempting":"Integers hold billions of values; no realistic city list approaches a limit."},{"text":"The codes carry an order and spacing that the cities do not have, so the model treats Chennai as greater than Ahmedabad and two steps away from it.","correct":true},{"text":"Alphabetical codes change whenever a new city is added, so the model cannot be retrained without re-encoding, which is fine for the first training run.","correct":false,"tempting":"Code instability is a real maintenance problem but does not make the first model wrong; the fabricated order does."},{"text":"Linear models cannot accept integer columns at all and require every feature to be a floating-point value between 0 and 1.","correct":false,"tempting":"Linear models accept integers freely; scaling is a separate concern and does not address the invented ordering."}]},"prev":{"slug":"missing-values-honestly","title":"Missing values: NULL, empty, N/A, zero and minus one","url":"https://addaly.com/learn/data-and-sql/missing-values-honestly"},"next":{"slug":"building-a-feature-table","title":"A feature table: one row per entity, as of a moment","url":"https://addaly.com/learn/data-and-sql/building-a-feature-table"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Categories: canonical values, mapping tables and encoding for a model', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/categories-and-encoding (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Categories: canonical values, mapping tables and encoding for a model","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":5,"slug":"data-that-arrives-dirty","title":"Data that arrives dirty"},"answer":null,"keyIdea":"Clean a category with a mapping table that a LEFT JOIN can expose gaps in, choose the encoding by cardinality and by whether the values have an order, and never give a model integer codes for categories that are not ordered.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"a-category-is-a-value-from-a-list-and-the-list-is-the-asset","runs":[{"text":"A category is a value from a list, and the list is the asset"}]},{"t":"p","runs":[{"text":"status","code":true},{"text":", "},{"text":"city","code":true},{"text":", "},{"text":"plan","code":true},{"text":", "},{"text":"channel","code":true},{"text":": 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 "},{"text":"CASE","code":true},{"text":" expression is the day two reports disagree about how many plans there are."}]},{"t":"h2","id":"a-mapping-table-instead-of-a-case","runs":[{"text":"A mapping table instead of a CASE"}]},{"t":"p","runs":[{"text":"Bombay","code":true},{"text":", "},{"text":"Mumbai","code":true},{"text":", "},{"text":"MUMBAI","code":true},{"text":", "},{"text":"Mumbai, MH","code":true},{"text":" and "},{"text":"Bombay (Mumbai)","code":true},{"text":" 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:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE city_map (\n  variant   text PRIMARY KEY,   -- the normalised key as it appears in the data\n  canonical text NOT NULL\n);\nINSERT INTO city_map VALUES ('bombay', 'Mumbai'), ('mumbai', 'Mumbai'), ('mumbai, mh', 'Mumbai');"},{"t":"code","lang":"sql","label":"SQL","text":"SELECT p.*, coalesce(m.canonical, p.city_key) AS city\nFROM payments_clean p\nLEFT JOIN city_map m ON m.variant = p.city_key;"},{"t":"p","runs":[{"text":"The LEFT JOIN is the important part. A variant that is not in the table comes through unchanged, and a query for "},{"text":"WHERE m.variant IS NULL","code":true},{"text":" 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 "},{"text":"CASE","code":true},{"text":" is not."}]},{"t":"h2","id":"cardinality-decides-what-you-can-do-with-it","runs":[{"text":"Cardinality decides what you can do with it"}]},{"t":"p","runs":[{"text":"count(DISTINCT city)","code":true},{"text":" is the first thing to know about a category. A "},{"text":"status","code":true},{"text":" with 4 values and a "},{"text":"postcode","code":true},{"text":" 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."}]},{"t":"h2","id":"what-a-model-needs","runs":[{"text":"What a model needs"}]},{"t":"p","runs":[{"text":"Most models take numbers. A column of city names has to become numbers, and there are three ways, each with a mechanism worth understanding."}]},{"t":"p","runs":[{"text":"One-hot encoding","b":true},{"text":" makes one 0/1 column per value: "},{"text":"is_mumbai","code":true},{"text":", "},{"text":"is_delhi","code":true},{"text":", "},{"text":"is_pune","code":true},{"text":". In SQL that is a "},{"text":"CASE","code":true},{"text":" per value, or "},{"text":"count(*) FILTER (WHERE city = 'Mumbai')","code":true},{"text":" 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 "},{"text":"pandas.get_dummies","code":true},{"text":" or scikit-learn’s "},{"text":"OneHotEncoder","code":true},{"text":", but the decision about which values to encode is a SQL decision about cardinality."}]},{"t":"p","runs":[{"text":"Integer codes","b":true},{"text":" 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."}]},{"t":"p","runs":[{"text":"Frequency and target encoding","b":true},{"text":" 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."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, count(*) AS city_freq, avg(churned::int) AS city_churn_rate\nFROM customers GROUP BY city;"},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"rare-values-and-new-values","runs":[{"text":"Rare values and new values"}]},{"t":"p","runs":[{"text":"A category with 300 values where 280 of them appear fewer than ten times is better encoded as the top 20 plus "},{"text":"other","code":true},{"text":". 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 "},{"text":"other","code":true},{"text":" and not crash the pipeline, which the mapping-table approach handles by construction and a hard-coded "},{"text":"CASE","code":true},{"text":" does not."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"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."}]}],"check":{"question":[{"text":"A model is trained with "},{"text":"city","code":true},{"text":" replaced by an integer code assigned alphabetically, so Ahmedabad is 1, Bengaluru is 2, Chennai is 3, and so on. Why is that a problem for a linear or distance-based model?"}],"options":[{"text":[{"text":"Integer codes overflow for high-cardinality columns, so cities beyond the first few thousand share codes and are confused with each other."}],"correct":false,"tempting":[{"text":"Integers hold billions of values; no realistic city list approaches a limit."}]},{"text":[{"text":"The codes carry an order and spacing that the cities do not have, so the model treats Chennai as greater than Ahmedabad and two steps away from it."}],"correct":true,"tempting":null},{"text":[{"text":"Alphabetical codes change whenever a new city is added, so the model cannot be retrained without re-encoding, which is fine for the first training run."}],"correct":false,"tempting":[{"text":"Code instability is a real maintenance problem but does not make the first model wrong; the fabricated order does."}]},{"text":[{"text":"Linear models cannot accept integer columns at all and require every feature to be a floating-point value between 0 and 1."}],"correct":false,"tempting":[{"text":"Linear models accept integers freely; scaling is a separate concern and does not address the invented ordering."}]}],"order":[2,3,0,1],"why":null},"prev":{"slug":"missing-values-honestly","title":"Missing values: NULL, empty, N/A, zero and minus one","minutes":9},"next":{"slug":"building-a-feature-table","title":"A feature table: one row per entity, as of a moment","minutes":10}}}