Transform — Cleaning, Casting, Deriving

Transform is where logic lives

Extract pulls bytes; load writes them; transform is where the logic that justifies your team's existence sits. The discipline is to make every transformation explicit, named, and testable — not buried in a 200-line notebook cell.

The four kinds of transform

Cleaning — fixing types, parsing dates, handling nulls, normalizing strings.
Deriving — computing new columns from existing ones (amount_local = amount_usd * fx_rate).
Joining — combining tables.
Reshaping — pivot, unpivot, group-then-aggregate.

Build them as small named functions that take a DataFrame and return a DataFrame. A pipeline that's raw.pipe(clean).pipe(derive).pipe(join_customers).pipe(reshape) reads as a contract; one that's do_everything(raw) with 200 lines of inline ops doesn't.

Code

Composable transform functions, plugged together with .pipe()·python

import pandas as pd

def clean(df: pd.DataFrame) -> pd.DataFrame:
    return (df
        .assign(
            order_date=lambda d: pd.to_datetime(d['order_date'], errors='raise'),
            amount_usd=lambda d: pd.to_numeric(d['amount_usd'].astype(str).str.replace(',', ''), errors='raise'),
            customer_id=lambda d: d['customer_id'].str.strip().str.upper(),
        )
        .dropna(subset=['order_id', 'customer_id', 'amount_usd'])
        .drop_duplicates('order_id')
    )

def derive(df: pd.DataFrame) -> pd.DataFrame:
    return df.assign(
        month=lambda d: d['order_date'].dt.to_period('M'),
        amount_local=lambda d: d['amount_usd'] * d.get('fx_rate', 1.0),
        is_high_value=lambda d: d['amount_usd'] > 500,
    )

def attach_customers(df: pd.DataFrame, customers: pd.DataFrame) -> pd.DataFrame:
    return df.merge(
        customers[['customer_id', 'country', 'tier']],
        on='customer_id', how='left', validate='many_to_one',
    )

result = (
    raw
      .pipe(clean)
      .pipe(derive)
      .pipe(attach_customers, customers=customers)
)

Exercise

Take any single multi-step transformation you've written and split it into 3–4 named functions that each take and return a DataFrame. Compose them with .pipe(). Then write one pytest test for each function using a 10-row synthetic input. The test suite is the artifact that lets you refactor without fear.