Cleaning and Reshaping — Python Primer

In the real world you are never handed a clean dataset. What arrives has eleven spellings of one category, dates in three formats, duplicate rows that are not quite duplicates, and a numeric column that is text for ninety per cent of its values.

This is not a failure of the tooling. It is the job, and it is where most of the time goes.

Diagnose Before You Change Anything

Resist the urge to start fixing. First find out what is actually wrong.

def profile(df):
    """Everything you want to know before touching a dataset."""
    print(f"shape: {df.shape}")
    print(f"exact duplicate rows: {df.duplicated().sum()}")
    print(f"memory: {df.memory_usage(deep=True).sum() / 1e6:.1f} MB\n")

    summary = pd.DataFrame({
        'dtype': df.dtypes,
        'missing': df.isna().sum(),
        'missing_pct': (df.isna().mean() * 100).round(1),
        'unique': df.nunique(),
        'sample': [df[c].dropna().iloc[0] if df[c].notna().any() else None
                   for c in df.columns],
    })
    return summary.sort_values('missing_pct', ascending=False)

profile(df)

That single table answers most of your first questions: which columns are mostly empty, which are secretly text, and which have suspiciously few or suspiciously many distinct values.

A column with exactly one unique value carries no information and can go. A column where unique equals the row count is an identifier, not a feature.

Missing Data

The first question is not how to fill gaps. It is what a gap means, and the answer differs per column.

flowchart TD
    Q["A value is missing.<br/>Why?"] --> A["Never collected<br/><i>optional field</i>"]
    Q --> B["Not applicable<br/><i>no spouse → no spouse's name</i>"]
    Q --> C["Failed to record<br/><i>sensor dropout</i>"]
    Q --> D["Means zero<br/><i>no purchases → blank</i>"]
    A --> A2["Consider a<br/>'was missing' flag"]
    B --> B2["Fill with a sentinel<br/>like 'not applicable'"]
    C --> C2["Impute, or drop<br/>if rare"]
    D --> D2["Fill with 0 —<br/>but confirm first"]
    style Q fill:#eef1fc,stroke:#2141c8

Look at the pattern before deciding:

df.isna().mean().sort_values(ascending=False)

# Do gaps cluster together? Often they arrive from the same broken source.
df.isna().corr()

# Are rows with a missing value different from rows without?
mask = df.income.isna()
df.groupby(mask)[['age', 'revenue']].mean()

That last check matters more than people expect. If customers with missing income are systematically younger and spend less, the missingness is itself information, and throwing those rows away biases everything downstream.

Missingness as a feature is frequently the most predictive thing in the dataset:

df['income_was_missing'] = df.income.isna().astype(int)
df['income'] = df.income.fillna(df.income.median())

A blank income field on a loan application tells you something real. Keep the flag.

Filling, done deliberately per column:

df = df.assign(
    income=df.income.fillna(df.income.median()),
    tier=df.tier.fillna('unknown'),
    count=df['count'].fillna(0),
)

Never df.fillna(0) across a whole frame. Zero is a real measurement in most columns, and you have just fabricated data that looks identical to the real thing.

For ordered data, forward fill can be right, but bound it:

df = df.sort_values('timestamp')
df['reading'] = df.reading.ffill(limit=3)   # carry forward at most 3 steps

Without limit, a sensor that failed in March produces a confident reading in December.

Duplicates

Three kinds, in increasing order of difficulty.

Exact duplicates. The whole row repeats.

df.duplicated().sum()
df = df.drop_duplicates()

Key duplicates. The identifier repeats but the rows differ. This is the dangerous one, because it silently multiplies rows in every join.

dupes = df[df.duplicated('customer_id', keep=False)].sort_values('customer_id')
dupes.head(20)          # LOOK at them before deciding

Then choose a rule and state it:

# Keep the most recent record per customer
df = df.sort_values('updated_at').drop_duplicates('customer_id', keep='last')

keep='first', keep='last' and keep=False (drop all of them) are three different business decisions. Make it consciously.

Near duplicates. Delhi and delhi and DELHI. Normalise first, then the exact check finds them:

key = df.city.str.strip().str.lower()
df.assign(_key=key).duplicated('_key').sum()

Outliers

An outlier is a value far from the others. Whether it is an error or the most interesting row in your dataset is a domain question, not a statistical one.

Find them:

# Interquartile range — robust, works on skewed data
q1, q3 = df.revenue.quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
outliers = df[(df.revenue < lo) | (df.revenue > hi)]

# Z-score — only sensible when roughly normal
z = (df.revenue - df.revenue.mean()) / df.revenue.std()
outliers = df[z.abs() > 3]

Prefer the interquartile method. The mean and standard deviation are themselves distorted by the outliers you are trying to find, which is a circularity the quartile version avoids.

Then decide, in this order:

Is it impossible? A negative age, a date in 2231, a percentage of 340. That is an error. Fix or drop it.
Is it possible but extreme? One customer really did spend a million. That is real, and removing it makes your model wrong about your most valuable customer.
Is it hurting a model that assumes otherwise? Clip rather than delete, and record that you did.

df['revenue_clipped'] = df.revenue.clip(lower=lo, upper=hi)

Keep the original column. Someone will ask.

Impossible Values

Range checks catch more real problems than any statistical method:

checks = {
    'age': (0, 120),
    'percentage': (0, 100),
    'revenue': (0, None),
}
for col, (lo, hi) in checks.items():
    if col not in df: continue
    bad = pd.Series(False, index=df.index)
    if lo is not None: bad |= df[col] < lo
    if hi is not None: bad |= df[col] > hi
    if bad.any():
        print(f"{col}: {bad.sum()} impossible values")
        print(df.loc[bad, col].describe())

Encoded missing values hide here. A dataset with ages of -1 or 999 is using those as “unknown”, and if you do not catch it your average age is memorable.

Reshaping

Two shapes, and you will convert between them constantly.

flowchart LR
    W["<b>WIDE</b><br/>city | 2023 | 2024 | 2025<br/>Delhi | 31.2 | 316.5 | 33.8<br/><i>one row per subject</i>"]
    L["<b>LONG</b><br/>city | year | value<br/>Delhi | 2023 | 31.2<br/>Delhi | 2024 | 316.5<br/><i>one row per observation</i>"]
    W -->|"melt()"| L
    L -->|"pivot() / pivot_table()"| W
    style L fill:#eef1fc,stroke:#2141c8

Long is what analysis and plotting libraries want. Wide is what humans want to read. Neither is correct in general.

long = wide.melt(
    id_vars=['city'],                       # columns to keep as they are
    value_vars=['2023', '2024', '2025'],    # columns to fold into rows
    var_name='year',
    value_name='population',
)

wide = long.pivot(index='city', columns='year', values='population')

If pivot raises about duplicate entries, that is useful information: your data has more than one row per index-column pair. Decide what that means before aggregating it away.

wide = long.pivot_table(index='city', columns='year',
                        values='population', aggfunc='mean')

stack and unstack do the same job for a MultiIndex, moving a level between rows and columns.

Build a Pipeline You Can Re-run

Cleaning done interactively in a notebook works once. Next month’s file arrives and you start again, and you do not remember what you did.

Write it as functions:

def clean_orders(raw: pd.DataFrame) -> pd.DataFrame:
    """Return a cleaned copy. The input is not modified."""
    df = raw.copy()

    # 1. Normalise text keys
    for c in ['city', 'tier']:
        df[c] = df[c].str.strip().str.lower()

    # 2. Impossible values become missing
    df.loc[~df.age.between(0, 120), 'age'] = pd.NA

    # 3. Record missingness before filling it
    df['income_was_missing'] = df.income.isna().astype(int)
    df['income'] = df.income.fillna(df.income.median())

    # 4. One row per order, most recent wins
    df = df.sort_values('updated_at').drop_duplicates('order_id', keep='last')

    return df


def check_orders(df: pd.DataFrame) -> None:
    """Assumptions that must hold. Fail loudly if they do not."""
    assert df.order_id.is_unique, "order_id is not unique"
    assert df.revenue.ge(0).all(), "negative revenue"
    assert df.age.between(0, 120).all() | df.age.isna().all(), "impossible age"

clean = clean_orders(raw)
check_orders(clean)

Three properties make this worth the effort. It is idempotent, so running it twice gives the same answer. It is testable, with five made-up rows. And the check function turns next month’s surprise into a crash rather than a wrong number in a report.

What Carries Forward

Diagnose before you fix. The profile table answers most first questions in one look.

Decide what missing means per column. Never fillna(0) across a frame, and keep a flag when missingness might be informative.

Look at your duplicates before dropping them. keep='first' and keep='last' are different business decisions.

Prefer the interquartile method for outliers, and clip rather than delete when the value is real.

Write cleaning as functions with assertions. Next month’s file is the reason.

Next: joining data, which is where a plausible-looking result quietly has the wrong number of rows.