Data Cleaning and Feature Engineering

Loading the Audited Dataset

import pandas as pd

# Load from checkpoint
df = pd.read_parquet('output/clean_orders.parquet')
print(f'Loaded: {df.shape}')
print(df.dtypes)
print(f'Date range: {df["order_date"].min().date()} to {df["order_date"].max().date()}')

Applying the Advanced Cleaning Checklist

import pandas as pd
import numpy as np

def remove_duplicates(df):
    n_before = len(df)
    df = df.drop_duplicates(subset=['order_id'], keep='first')
    print(f'Duplicates removed: {n_before - len(df)}')
    return df

def standardise_categories(df):
    df['category'] = (df['category']
                      .astype(str)
                      .str.strip()
                      .str.title())
    return df

def cap_price_outliers(df):
    q1, q3 = df['unit_price'].quantile([0.25, 0.75])
    iqr = q3 - q1
    upper = q3 + 3 * iqr
    df['unit_price'] = df['unit_price'].clip(upper=upper)
    return df

df = remove_duplicates(df)
df = standardise_categories(df)
df = cap_price_outliers(df)
print('Cleaning complete.')

Engineering Date Features

import pandas as pd

df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['quarter'] = df['order_date'].dt.quarter
df['day_of_week'] = df['order_date'].dt.dayofweek  # 0=Monday
df['week'] = df['order_date'].dt.isocalendar().week.astype('int32')
df['year_month'] = df['order_date'].dt.to_period('M').astype(str)

print('Date features created:')
print(df[['order_date', 'year', 'month', 'quarter', 'year_month']].head())

Computing Line-Item Revenue

import pandas as pd

# Line-item revenue
df['revenue'] = (df['quantity'] * df['unit_price']).astype('float32')

# Gross margin (if unit_cost column exists)
if 'unit_cost' in df.columns:
    df['gross_margin'] = (df['unit_price'] - df['unit_cost']) / df['unit_price']

# Discount flag: orders with more than 20% off list price
if 'list_price' in df.columns:
    df['is_discounted'] = df['unit_price'] < df['list_price'] * 0.8

print('Revenue stats:')
print(df['revenue'].describe().round(2))

Customer Cohort Assignment

import pandas as pd

# First purchase date per customer
first_purchase = (
    df.groupby('customer_id')['order_date']
    .min()
    .dt.to_period('M')
    .astype(str)
    .rename('cohort_month')
)

# Merge back onto orders
df = df.merge(first_purchase, on='customer_id', how='left')
print('Cohort distribution (top 5):')
print(df['cohort_month'].value_counts().head())
print(f'Distinct cohorts: {df["cohort_month"].nunique()}')

Customer Lifetime Value Features

import pandas as pd

customer_stats = df.groupby('customer_id').agg(
    total_orders=('order_id', 'nunique'),
    total_revenue=('revenue', 'sum'),
    avg_order_value=('revenue', 'mean'),
    categories_purchased=('category', 'nunique'),
    first_order=('order_date', 'min'),
    last_order=('order_date', 'max')
).reset_index()

customer_stats['days_as_customer'] = (
    (customer_stats['last_order'] - customer_stats['first_order'])
    .dt.days
)

print(customer_stats.describe().round(2))

Schema Validation with Assertions

import pandas as pd

def validate_clean_df(df):
    required_cols = ['order_id', 'customer_id', 'revenue', 'year_month',
                     'cohort_month', 'category', 'region', 'order_date']
    missing = [c for c in required_cols if c not in df.columns]
    assert not missing, f'Missing columns: {missing}'

    assert (df['revenue'] >= 0).all(), 'Negative revenue found'
    assert df['cohort_month'].notna().all(), 'Null cohort_month values'
    assert df['order_date'].notna().all(), 'Null order dates'
    print(f'Validation passed: {len(df):,} rows, {len(df.columns)} columns')

validate_clean_df(df)

Handling Low-Frequency Categories

import pandas as pd

def collapse_rare_values(df, col, min_pct=0.005):
    threshold = len(df) * min_pct
    counts = df[col].value_counts()
    rare = counts[counts < threshold].index
    n_rare = len(rare)
    df[col] = df[col].apply(lambda x: 'Other' if x in rare else x)
    print(f'{col}: collapsed {n_rare} rare values into "Other"')
    return df

df = collapse_rare_values(df, 'category', min_pct=0.005)
df = collapse_rare_values(df, 'region', min_pct=0.005)
print('Category distribution after collapsing:')
print(df['category'].value_counts())

Saving the Feature-Engineered Dataset

import pandas as pd

# Convert category columns back to Categorical for memory efficiency
for col in ['category', 'region', 'acquisition_channel']:
    if col in df.columns:
        df[col] = df[col].astype('category')

# Save analysis-ready dataset
df.to_parquet('output/analysis_ready.parquet', index=False)

# Also save customer stats for segmentation
customer_stats.to_parquet('output/customer_stats.parquet', index=False)

print(f'Analysis-ready dataset: {df.shape}')
print(f'Memory usage: {df.memory_usage(deep=True).sum()/1e6:.1f} MB')

Feature Engineering Summary

import pandas as pd

# Summary of engineered features
df = pd.read_parquet('output/analysis_ready.parquet')

original_cols = ['order_id', 'customer_id', 'order_date', 'quantity', 'unit_price']
engineered_cols = [c for c in df.columns if c not in original_cols]

print('Original columns:', original_cols)
print('Engineered features:', engineered_cols)
print(f'Total columns: {len(df.columns)}')
print(f'Total rows: {len(df):,}')

Documenting Decisions in Code

# Engineering decisions log
DECISIONS = '''
# Data Cleaning & Feature Engineering Decisions

## Duplicate handling
Kept first occurrence of duplicate order_id (most likely the original order).

## Outlier capping
Unit price capped at Q3 + 3*IQR (not 1.5*IQR) because price distribution
has legitimate high-value enterprise orders that should not be removed.

## Rare category threshold: 0.5%
Values below 0.5% of total orders collapsed to "Other" to keep charts readable
and avoid spurious significance in category-level tests.

## Cohort assignment
Cohort = first purchase calendar month (not signup month), because many users
sign up but do not purchase until months later.
'''
print(DECISIONS)

Quick Check

Lesson Recap