Skillversitas

Cleaning Messy Data

Real data arrives with problems: missing cells, inconsistent types, duplicated rows, impossible values, and units that disagree across sources. Cleaning is the step that turns the raw table into something a model can trust, and its rules are simple: fix types first, handle missingness deliberately (never silently), remove true duplicates, and treat outliers as findings rather than noise. Everything here is pandas plus a little scikit-learn, and every transformation should be a line of code you can rerun - not a spreadsheet edit.

Fixing Types

import pandas as pd

df["amount"] = pd.to_numeric(df["amount"], errors="coerce")   # junk becomes NaN
df["created_at"] = pd.to_datetime(df["created_at"], errors="coerce")
df["status"] = df["status"].str.strip().str.lower()
print(df.dtypes)

errors="coerce" turns unparseable values into NaN instead of raising, so the problem moves into the missing-value step where it can be counted and handled. Strip and normalize string columns before any grouping - " Active" and "active" are the same category wearing different clothes.

Duplicates

print("exact duplicates:", df.duplicated().sum())
df = df.drop_duplicates()

# near-duplicates: same customer, same day, same amount
key = ["customer_id", "order_date", "amount"]
print("soft duplicates:", df.duplicated(subset=key).sum())
df = df.drop_duplicates(subset=key, keep="first")

Exact duplicates are almost always safe to drop; "soft" duplicates (same business key) need a judgment call - a second order the same day is real, a re-imported row is not. Decide from the data's origin, and record the count you dropped.

Missing Values

from sklearn.impute import SimpleImputer, KNNImputer

print(df.isna().sum()[lambda s: s > 0])

# numeric: median (robust to outliers)
imp_num = SimpleImputer(strategy="median")
df[["amount", "age"]] = imp_num.fit_transform(df[["amount", "age"]])

# categorical: mode or an explicit "unknown"
df["region"] = df["region"].fillna("unknown")

# better: keep a missingness indicator - "was it missing?" is itself a signal
df["age_missing"] = df["age"].isna().astype(int)
df["age"] = df["age"].fillna(df["age"].median())

The strategy depends on why the value is missing. Missing-at-random: median/mode imputation is fine. Missing in a pattern (high-value customers skip the "satisfaction" survey): the missingness indicator carries the information. For high-dimensional numeric data, KNNImputer fills each value from its neighbors - better than the median, slower than anything else. The rule against: never forward-fill a time series into a feature, and never delete rows with missingness until you have counted what you are throwing away.

Outliers

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
mask = (df["amount"] > q3 + 3 * iqr) | (df["amount"] < q1 - 3 * iqr)
print("extreme rows:", mask.sum())
print(df.loc[mask].head())

# options, in order of aggressiveness:
df.loc[mask, "amount"] = df.loc[mask, "amount"].clip(upper=q3 + 3 * iqr)  # winsorize
# df = df[~mask]                                                        # drop (rarely right)

Look at the rows before deciding. A handful of impossible values (amount = -1, age = 400) get fixed or dropped; a genuine tail (whale customers) gets kept - trees handle it, linear models may want a log or a winsorized version. The 3-IQR fence finds the extreme tail; the 1.5-IQR fence is for box-plot whiskers, not for deleting data.

Units and Consistency

  • One unit per column: convert everything to the model's unit (cents, not dollars-and-cents mixed; seconds, not a mix of ms and s).
  • One time basis: all timestamps in UTC, all durations in the same unit.
  • One labeling scheme: merge synonym categories ("CA", "California") before encoding.
  • Write the cleaned dataset to a file (to_parquet) so every model starts from the identical table.

Summary

Clean in the fixed order: types (coerce junk to NaN), duplicates (exact, then business-key), missingness (strategy plus an indicator when the pattern matters), outliers (inspect rows, winsorize or keep, rarely drop), and unit consistency. Keep every step as rerunnable code, save the cleaned table, and let the count of rows touched by each step be part of the record - cleaning is a transformation with a paper trail, not a cleanup.