Machine Learning Data Preparation: Cleaning, Encoding and Scaling

The booking desk at a medical clinic exports ten appointments to a CSV file. One age is -1, two distances are blank, and the booking channel is a word. Not one value of that file can go into a model as it stands: models do arithmetic, and arithmetic needs complete numbers. Most of the real work in machine learning is exactly this translation, and mistakes made here are the hardest to see later, because the model still trains and produces numbers either way.

Step one: parse and clean without guessing silently

The goal is to predict whether a patient will miss their appointment (no_show). Parsing comes first. The important habit is to look at what the file really contains. An age of -1 is not a very young patient: it is a placeholder someone typed when the field was unknown, so it must become "missing" rather than be averaged in as if it were real.

import csv
import io
from statistics import median

RAW = """age,days_ahead,channel,distance_km,no_show
34,3,phone,4.2,0
27,14,app,,1
61,1,walkin,0.8,0
45,21,app,12.5,1
19,7,app,3.0,0
52,2,phone,,0
38,30,phone,22.0,1
29,5,app,6.1,0
-1,9,app,2.4,0
70,1,walkin,1.5,0
"""

def parse(text):
    rows = []
    for rec in csv.DictReader(io.StringIO(text)):
        age = int(rec["age"])
        rows.append({
            "age": age if age > 0 else None,           # -1 is a placeholder, not an age
            "days_ahead": int(rec["days_ahead"]),
            "channel": rec["channel"],
            "distance_km": float(rec["distance_km"]) if rec["distance_km"] else None,
            "no_show": int(rec["no_show"]),
        })
    return rows

rows = parse(RAW)
for col in ("age", "distance_km"):
    missing = sum(1 for r in rows if r[col] is None)
    print(f"{col}: {missing} missing of {len(rows)}")

# Fill gaps with the median of the values we do have
for col in ("age", "distance_km"):
    fill = median(r[col] for r in rows if r[col] is not None)
    print(f"{col} median used to fill: {fill}")
    for r in rows:
        if r[col] is None:
            r[col] = fill
print(rows[1])

age: 1 missing of 10
distance_km: 2 missing of 10
age median used to fill: 38
distance_km median used to fill: 3.6
{'age': 27, 'days_ahead': 14, 'channel': 'app', 'distance_km': 3.6, 'no_show': 1}

The median is a sturdy default for filling gaps because one extreme value (a patient who lives 22 km away) does not drag it around the way it would drag a mean. Two cautions apply. Filling hides the fact that a value was missing, which can itself be informative (people who leave the distance blank might behave differently), so many practitioners add a separate "was missing" column. And to keep this lesson short we computed the median over all ten rows; in a real pipeline it must come from the training rows only, for the reason in the next section.

Step two: encode categories as numbers

A channel of app, phone or walkin has no numeric meaning. The tempting shortcut is to code them as 0, 1, 2. That quietly tells the model that walk-in is twice phone and that app and walk-in are far apart, relationships that do not exist. One-hot encoding avoids this by creating one 0/1 column per category, so each category stands alone. Reserve ordinal numbers for categories with a genuine order, such as shirt sizes S, M, L.

Step three: scale, using only what you learned from training data

Our columns live on different scales: age in tens, days ahead up to 30, distance in single digits. Models that measure distance between rows (nearest neighbours, as in the next lesson) or that update weights by gradient (linear and logistic regression) behave badly when one column is simply bigger. Standardising rescales each column to mean 0 and standard deviation 1 using (value - mean) / std.

The mean and standard deviation are themselves learned from data, so they belong to training. You compute them on the training rows and then apply the same numbers to validation, test and live rows. If you compute them on everything, information about the test set leaks into training.

from statistics import mean, pstdev

CHANNELS = ["app", "phone", "walkin"]          # fixed order, decided once

def one_hot(channel):
    return [1.0 if channel == c else 0.0 for c in CHANNELS]

# numeric columns after cleaning: age, days_ahead, distance_km
train = [(34, 3, 4.2), (27, 14, 6.1), (61, 1, 0.8), (45, 21, 12.5), (19, 7, 3.0), (52, 2, 6.1)]
test = [(38, 30, 22.0), (29, 5, 6.1)]

# learn scaling numbers from TRAIN only
cols = list(zip(*train))
means = [mean(c) for c in cols]
stds = [pstdev(c) for c in cols]
print("train means:", [round(m, 2) for m in means])
print("train stds :", [round(s, 2) for s in stds])

def scale(row):
    return [(v - m) / s for v, m, s in zip(row, means, stds)]

for numbers, channel in zip(test, ["phone", "app"]):
    features = scale(numbers) + one_hot(channel)
    print([round(f, 2) for f in features])

train means: [39.67, 8, 5.45]
train stds : [14.46, 7.26, 3.65]
[-0.12, 3.03, 4.54, 0.0, 1.0, 0.0]
[-0.74, -0.41, 0.18, 1.0, 0.0, 0.0]

Read the first test row: a 38-year-old booking 30 days ahead from 22 km away. Its days_ahead is 3.03 and its distance is 4.54 standard deviations above the training average. The model has not seen anything like it, and the scaled numbers make that visible at a glance. That is a useful diagnostic in itself: a feature far outside the training range is a warning that predictions for that row deserve little trust.

Common mistakes and the symptoms they cause

How this shows up in real projects and interviews

On real projects, cleaning dominates the calendar. The data comes from forms, exports and joins, and its quirks (sentinel values, mixed units, duplicated rows, inconsistent spellings) are domain knowledge you only gain by looking at rows, not at summaries. A good habit is to print ten random raw rows before doing anything. In interviews, expect questions such as "how would you handle missing values?" and "why scale features?". A strong answer gives two or three options with trade-offs and mentions that every learned transformation must be fitted on training data only. If your records live in a database, the earlier steps are often best done in SQL; see SQL Fundamentals for grouping and filtering patterns.