Data Cleaning

The previous course built the distinction over eleven lessons: the number measured on the data a model has seen and the number measured on data it has not seen are two different numbers, and each number is written next to a baseline computed on its own split. That course closed with something missing. The features given to the model were always taken as ready: mean consumption was a number, the region was five binary columns, volatility had already been computed. Which column was turned into a number in which way, and how much that conversion moved the difference from the baseline, was never asked.

This lesson asks that question and sets up the unit of measurement for the course. The method is the same in every lesson: the model is held fixed and only the features change. A single preparation decision is switched on and off, the two numbers on the held-out set are printed side by side, and the difference between them is called the contribution. The rule of the course follows from this: a preparation step exists to the extent of its contribution measured on the held-out set. A step whose contribution has not been measured is not a step but a habit.

• PS1. The dataset is inherited from the Introduction to Machine Learning course and is fictional: 1,260 subscribers derived from the regional measurement network, a label for suspected illicit use or a fault, seed 20260218, split 756/252/252.
• PS2. In this course the held-out set is the test set, and the baseline there is 0.7579 — telling every subscriber the most frequent class of the training set. By the rule of the Introduction to Machine Learning course, every look accumulates a selection share; the accounting of that share is left to the end of the course.
• PS3. The table used by the Introduction to Machine Learning course is the cleaned table. This lesson goes one step back: the raw record form of the same setup, before the cleaning decisions were made.
• PS4. The raw record has three defects and all three are fictional. Some of the older meters report consumption in liters; the region name is recorded in eleven distinct spellings; an unreadable meter is read a second time, so the same subscriber appears on more than one row.
• PS5. The defects corrupt only the feature columns. The label is an inspection result and is affected by none of the defects; this is why the baseline stays fixed at 0.7579.
• PS6. The split is made by subscriber. Duplicate rows do not cross between splits; they only change the weight within the training set.
• PS7. Two model families are compared: the depth 6 decision tree of the Introduction to Machine Learning course and a distance-based method that looks at the nearest fifteen neighbors. Both are written within the lesson, both are models, and their settings do not change for the whole course.
• PS8. The measure is accuracy. The test set is 252 subscribers, so one subscriber is worth 0.0040, and differences smaller than that count as unmeasured.

The cleaning procedure itself is not built in this lesson. Missing values, type conversion, duplicate records and category merging were measured in the Data Collection and Preparation course of the Data Analytics curriculum; the measure there was the number of rows — how many rows were fixed, how many were dropped. The question here is different: how much does the same decision move the number on the held-out set, and which model family is sensitive to it.

The Raw Record and Three Defects

The setup regenerates the dataset with the seed of the Introduction to Machine Learning course, splits it by subscriber, and builds the raw record form of the training and test splits. The label is not touched.

# preparation.py — MODEL: the fictional subscriber table of the Introduction to Machine Learning course is regenerated with the same
# seed; the three defects of the RAW record are laid on top of it.
import math

SEED, CANDIDATES, M32 = 20260218, 1400, 0xFFFFFFFF
REGION = [("north", 0.28, 21, 0.00), ("south", 0.22, 17, -0.10),
          ("east", 0.18, 26, 0.30), ("west", 0.14, 14, -0.05),
          ("central", 0.18, 23, 0.05)]


def rng(t):
    x = ((t ^ (t >> 16)) * 2246822507) & M32
    x = ((x ^ (x >> 13)) * 3266489909) & M32
    s = [(x ^ (x >> 16)) & M32]

    def next_value():
        s[0] = (s[0] * 1664525 + 1013904223) & M32
        return s[0] / 4294967296
    return next_value


def pick(u, w):
    t = 0.0
    for i, x in enumerate(w):
        t += x
        if u < t:
            return i
    return len(w) - 1


SUBSCRIBERS = []
for i in range(CANDIDATES):
    r = rng(SEED + i)
    b = REGION[pick(r(), [x[1] for x in REGION])]
    household = 1 + pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05])
    satisfaction = 1 + pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17])
    if r() >= 0.046:
        SUBSCRIBERS.append({"id": 10001 + i, "region": b, "household": household,
                            "satisfaction": satisfaction})
READINGS = {}
for k in SUBSCRIBERS:
    r = rng(SEED + 7000 + k["id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        READINGS.setdefault(k["id"], []).append(0.0 if r() < 0.038 else math.floor(
            k["region"][2] * math.exp((r() + r() + r() - 1.5) * 0.62)
            * (1 - d * 0.05) * 100 + 0.5) / 100)
        r()

DATA = []
for k in SUBSCRIBERS:
    v = READINGS.get(k["id"])
    if v is None:
        continue
    mean, vol, r = sum(v) / len(v), max(v) - min(v), rng(SEED + 51000 + k["id"])
    z = (0.052 * (mean - 20) + 0.85 * (min(v) == 0.0) + 0.026 * vol
         + 0.24 * (k["household"] >= 5) - 0.20 * (k["satisfaction"] >= 4) + k["region"][3]
         + 0.9 * (k["id"] - 10001) / 1399 + (r() + r() + r() - 1.5) * 1.30)
    DATA.append({"id": k["id"], "household": k["household"], "satisfaction": k["satisfaction"],
                 "period": len(v), "mean_consumption": round(mean, 2),
                 "volatility": round(vol, 2), "zero_readings": int(min(v) == 0.0),
                 "region": k["region"][0], "suspicious": int(z > 1.35)})


def shuffle(data, seed):
    r, s = rng(seed), list(range(len(data)))
    for i in range(len(s) - 1, 0, -1):
        j = int(r() * (i + 1))
        s[i], s[j] = s[j], s[i]
    return [data[i] for i in s]


K = shuffle(DATA, SEED + 90000)
TRAIN0, TEST0 = K[:756], K[1008:]
BASELINE = sum(x["suspicious"] == 0 for x in TEST0) / len(TEST0)

SPELLING = {"north": ["north", "North", "NORTH"], "south": ["south", "South"],
            "east": ["east", "East"], "west": ["west", "WEST"],
            "central": ["central", "Central "]}


def make_raw(rows, seed):                   # liter records + spelling inconsistency
    r, out = rng(seed), []
    for x in rows:
        y = dict(x)
        if r() < 0.35:
            y["mean_consumption"] = round(y["mean_consumption"] * 1000, 1)
            y["volatility"] = round(y["volatility"] * 1000, 1)
        v = SPELLING[y["region"]]
        y["region"] = v[0] if r() < 0.70 else v[int(r() * (len(v) - 1)) + 1]
        out.append(y)
    return out


def duplicate(rows, seed):                  # an unreadable meter is read a second time
    r, out = rng(seed), []
    for x in rows:
        out.append(x)
        if x["zero_readings"] == 1 and r() < 0.85:
            out += [dict(x), dict(x)]
    return out


RAW_TRAIN = duplicate(make_raw(TRAIN0, SEED + 61000), SEED + 62000)
RAW_TEST = make_raw(TEST0, SEED + 63000)
print(f"train {len(TRAIN0)} subscribers, {len(RAW_TRAIN)} rows in the raw record; "
      f"test {len(TEST0)} subscribers, {len(RAW_TEST)} rows")
print(f"rows recorded in liters: train "
      f"{sum(1 for x in RAW_TRAIN if x['mean_consumption'] > 1000)}, test "
      f"{sum(1 for x in RAW_TEST if x['mean_consumption'] > 1000)}")
print(f"{len({x['region'] for x in RAW_TRAIN})} distinct spellings for five regions, "
      f"baseline (most frequent class) on test {BASELINE:.4f}")

train 756 subscribers, 864 rows in the raw record; test 252 subscribers, 252 rows
rows recorded in liters: train 295, test 86
11 distinct spellings for five regions, baseline (most frequent class) on test 0.7579

The size of the three defects can be measured: 756 subscribers have grown to 864 rows, 295 rows have shifted consumption by a factor of a thousand, and five regions are recorded in eleven distinct spellings. These are the same defects that were described by counting rows in the Data Analytics curriculum. What is new is that each of them is a decision, and that the price of the decision will be read off the model.

The Same Decision, Two Model Families

The code below writes the two models and applies the three cleaning decisions one at a time. The decision tree picks a feature and a threshold at each node, splits the set in two, and states the majority class at the leaf. The distance-based method finds the fifteen nearest neighbors of a subscriber and states their majority; closeness is the sum of the squared differences over all columns.

def gini(s):
    p = sum(x["suspicious"] for x in s) / len(s) if s else 0.0
    return 2 * p * (1 - p)


def tree(s, depth, fields, min_size=2):     # MODEL: decision tree, depth 6
    p = sum(x["suspicious"] for x in s) / len(s)
    best = None
    if depth and len(s) >= 2 * min_size and 0.0 < p < 1.0:
        for a in fields:
            d = sorted({x[a] for x in s})
            for v in (d[1:] if len(d) < 10 else
                      [d[int(i * len(d) / 10)] for i in range(1, 10)]):
                left = [x for x in s if x[a] < v]
                right = [x for x in s if x[a] >= v]
                if min(len(left), len(right)) < min_size:
                    continue
                k = gini(s) - (len(left) * gini(left) + len(right) * gini(right)) / len(s)
                if best is None or k > best[0]:
                    best = (k, a, v, left, right)
    if best is None or best[0] <= 1e-9:
        return {"prediction": int(p > 0.5)}
    return {"field": best[1], "threshold": best[2],
            "left": tree(best[3], depth - 1, fields, min_size),
            "right": tree(best[4], depth - 1, fields, min_size)}


def tree_predict(d, x):
    while "prediction" not in d:
        d = d["left"] if x[d["field"]] < d["threshold"] else d["right"]
    return d["prediction"]


def neighbor_predict(train, x, fields, k=15):  # MODEL: distance-based method
    u = sorted((sum((x[a] - y[a]) ** 2 for a in fields), y["suspicious"]) for y in train)[:k]
    return int(sum(t[1] for t in u) * 2 > k)


def expand_columns(train, test):            # region spellings become binary columns
    cats = sorted({x["region"] for x in train})
    fields = ["mean_consumption", "volatility", "household", "satisfaction", "period", "zero_readings"]
    expand = (lambda s: [dict(x, **{"b_" + k: int(x["region"] == k) for k in cats})
                         for x in s])
    return fields + ["b_" + k for k in cats], expand(train), expand(test)


def measure(train, test):
    fields, e, s = expand_columns(train, test)
    m = tree(e, 6, fields)
    return (sum(tree_predict(m, x) == x["suspicious"] for x in s) / len(s),
            sum(neighbor_predict(e, x, fields) == x["suspicious"] for x in s) / len(s))


def fix_units(s):                           # liter records are converted to cubic meters
    return [dict(x, mean_consumption=round(x["mean_consumption"] / 1000, 2),
                 volatility=round(x["volatility"] / 1000, 2))
            if x["mean_consumption"] > 1000 else dict(x) for x in s]


def fix_spelling(s):
    return [dict(x, region=x["region"].strip().lower()) for x in s]


def dedupe(s):                              # the first reading is kept, the copies are dropped
    g, out = set(), []
    for x in s:
        if x["id"] not in g:
            g.add(x["id"])
            out.append(x)
    return out


DECISIONS = [("none (raw record)", lambda e, s: (e, s)),
             ("units fixed", lambda e, s: (fix_units(e), fix_units(s))),
             ("spelling merged", lambda e, s: (fix_spelling(e), fix_spelling(s))),
             ("duplicates dropped", lambda e, s: (dedupe(e), s)),
             ("all three", lambda e, s: (dedupe(fix_spelling(fix_units(e))), fix_spelling(fix_units(s))))]

RESULT = {}
print(f"{'decision applied':<22} {'tree':>8} {'distance':>9}")
print(f"{'baseline':<22} {BASELINE:>8.4f} {BASELINE:>9.4f}")
for name, f in DECISIONS:
    RESULT[name] = measure(*f(RAW_TRAIN, RAW_TEST))
    print(f"{name:<22} {RESULT[name][0]:>8.4f} {RESULT[name][1]:>9.4f}")

decision applied           tree  distance
baseline                 0.7579    0.7579
none (raw record)        0.7659    0.7540
units fixed              0.7579    0.8016
spelling merged          0.7738    0.7540
duplicates dropped       0.7778    0.7659
all three                0.7778    0.7857

The second row is the starting point of the lesson. The distance-based method trained on the raw record reads 0.7540 on the test set, and this is below the baseline. A procedure that looks at no feature, learns nothing and gives every subscriber the same answer is better than it. The tree trained on the same raw record exceeds the baseline by 0.0079. Two models, the same table, two different signs.

The third row is the real number of the lesson. Fixing the units takes the distance-based method from 0.7540 to 0.8016; it lowers the tree from 0.7659 to 0.7579. The same decision, the same data, the opposite sign. The reason is the way the models read a column. The tree looks only at the order in each column and splits with a single threshold; when the unit mix-up breaks the ordering, the tree can first split at the thousand boundary and then reuse the column in every branch, and the price it pays is one node depth. The distance-based method, on the other hand, sums the differences of all columns; a column that has grown a thousandfold determines the sum on its own, and the household, satisfaction, period and region columns all fall out of the calculation.

How Many Subscribers a Contribution Is Worth

Converting the differences into a number of subscribers separates the rows that are worth reading.

RAW_RESULT = RESULT["none (raw record)"]
print(f"{'decision':<22} {'tree contribution':>19} {'subscribers':>12} "
      f"{'distance contribution':>23} {'subscribers':>12}")
for name, _ in DECISIONS[1:]:
    ka, ku = RESULT[name][0] - RAW_RESULT[0], RESULT[name][1] - RAW_RESULT[1]
    print(f"{name:<22} {ka:>+19.4f} {round(ka * 252):>12} "
          f"{ku:>+23.4f} {round(ku * 252):>12}")
print(f"\ndifference over the baseline, raw record: tree {RAW_RESULT[0] - BASELINE:+.4f}, "
      f"distance {RAW_RESULT[1] - BASELINE:+.4f}")
u = RESULT["all three"]
print(f"difference over the baseline, all three: tree {u[0] - BASELINE:+.4f}, "
      f"distance {u[1] - BASELINE:+.4f}")

decision                 tree contribution  subscribers   distance contribution  subscribers
units fixed                        -0.0079           -2                 +0.0476           12
spelling merged                    +0.0079            2                 +0.0000            0
duplicates dropped                 +0.0119            3                 +0.0119            3
all three                          +0.0119            3                 +0.0317            8

difference over the baseline, raw record: tree +0.0079, distance -0.0040
difference over the baseline, all three: tree +0.0198, distance +0.0278

The table has a single large number worth reading: the twelve subscribers that the unit fix adds to the distance-based method. The remaining cells are two or three subscribers, and on a set of 252 subscribers, two subscribers cannot be told apart from the randomness of the set itself. The −0.0079 harm the tree sees from the unit fix is of that size too; its sign is negative, but the sentence to read is “the contribution of this step to the tree has not been measured”, not “it harms the tree”. This distinction continues through the course: the sign of a contribution acquires meaning when its size exceeds the measurement resolution.

The last two rows show where the differences come from. When all three defects are fixed together, the tree exceeds the baseline by 0.0198 and the distance-based method by 0.0278. These two differences come from learning: the defects were generated independently of the label, none of them is more frequent among suspicious subscribers, so no cleaning decision carries hidden information about the label into the model. Later in the course the opposite will be measured too — there are steps whose contribution comes not from learning but from leakage, and the two can be told apart only when the source is asked.

There is one more check. When all three defects are fixed together, the tree’s test accuracy comes out at 0.7778, and this is exactly the same as the number that the Introduction to Machine Learning course read with the same model, the same split and the same seed. This is no coincidence: the cleaned table is the table that course used from the very start. What was not visible throughout the previous course was that this table was itself the product of a series of decisions.

Summary

• The distance-based method trained on the raw record reads 0.7540 on the test set and stays 0.0040 below the baseline; the decision tree trained on the same table exceeds the baseline by 0.0079 with 0.7659.
• Fixing the unit mix-up adds +0.0476 to the distance-based method, that is, twelve subscribers; the same decision takes −0.0079 from the decision tree, that is, two subscribers.
• The reason the sign reverses is the way the models read a column: the tree looks only at the order in each column and makes up for the mixed units with one node of depth, while the distance-based method sums all the columns and a thousandfold column determines the sum on its own.
• Spelling merging and duplicate record dropping move two or three subscribers in both models; since one subscriber is worth 0.0040 on a 252-subscriber test set, these differences count as unmeasured.
• When all three defects are fixed together the tree reads 0.7778; this is the same number that the Introduction to Machine Learning course read with the same model and the same split, because that course was already using the cleaned table.

Next Step

This lesson showed that the unit mix-up breaks the distance-based method, but even after the fix that method stayed behind the tree and could exceed the baseline by only 0.0278. The reason is still in the table: the columns were converted to the same unit but not to the same magnitude. Mean consumption varies between zero and forty-three, household size between one and six, and the region columns between zero and one; for a method that sums the squares of the differences, this silently gives the columns weights. The next lesson measures two scaling decisions — standardization and normalization — separately in three model families, builds the discipline of learning the scale only from the training set and applying it to the held-out set, and counts in which model family the contribution comes out as zero.