Data Cleaning and Preprocessing Checklist Builder

Data Cleaning Checklist — E-Commerce Transaction Dataset (500K rows)

Step 1: Initial Data Profiling

import pandas as pd

df = pd.read_csv('transactions.csv')

# Shape and types

print(f"Rows: {df.shape[0]:,} | Columns: {df.shape[1]}")

print(df.dtypes)

print(df.describe(include='all'))

# Missing values summary

missing = df.isnull().sum()

missing_pct = (missing / len(df) * 100).round(2)

missing_report = pd.DataFrame({'count': missing, 'pct': missing_pct})

print(missing_report[missing_report['count'] > 0].sort_values('pct', ascending=False))

# Unique values per column

print(df.nunique())

Step 2: Missing Data Strategy

| Missing % | Strategy | Rationale |

|-----------|----------|----------|

| <5% | Drop rows OR mean/median impute | Minimal impact on dataset size |

| 5-30% | Impute (median for numeric, mode for categorical) + add `_missing` flag column | Preserve rows while flagging imputation |

| >30% | Consider dropping the column OR building a separate model | Too much imputation introduces noise |

| MCAR (random) | Safe to impute with simple methods | Missingness is not informative |

| MAR/MNAR (systematic) | Investigate WHY it's missing — the pattern may be a feature itself | e.g., missing income = customer didn't provide = possible signal |

# Impute numeric with median, categorical with mode

for col in df.select_dtypes(include='number').columns:

if df[col].isnull().sum() > 0:

df[f'{col}_missing'] = df[col].isnull().astype(int)

df[col].fillna(df[col].median(), inplace=True)

Step 3: Duplicate Detection

# Exact duplicates

exact_dupes = df.duplicated().sum()

print(f"Exact duplicates: {exact_dupes}")

# Business-key duplicates (same customer + same order)

biz_dupes = df.duplicated(subset=['customer_id', 'order_id'], keep=False)

print(f"Business-key duplicates: {biz_dupes.sum()}")

# Resolution: keep first occurrence

df = df.drop_duplicates(subset=['customer_id', 'order_id'], keep='first')

Step 4: Outlier Detection

# IQR method for numeric columns

def flag_outliers_iqr(series, factor=1.5):

Q1, Q3 = series.quantile([0.25, 0.75])

IQR = Q3 - Q1

return (series < Q1 - factor * IQR) | (series > Q3 + factor * IQR)

df['price_outlier'] = flag_outliers_iqr(df['transaction_amount'])

print(f"Price outliers: {df['price_outlier'].sum()}")

Step 5: Validation Rules

assert (df['transaction_amount'] >= 0).all(), "Negative transaction amounts found!"

assert df['email'].str.contains('@').all(), "Invalid email addresses found!"

assert df['order_date'].between('2020-01-01', '2026-12-31').all(), "Dates out of expected range!"

Data Quality Report

| Check | Before | After | Action Taken |

|-------|--------|-------|--------------|

| Total rows | 500,000 | 487,231 | Removed 12,769 duplicates |

| Missing (address) | 23.4% | 0% | Imputed with 'Unknown' + flag |

| Outliers (price) | 342 rows | 342 flagged | Kept but flagged, not removed |

| Date format | Mixed (US/EU) | ISO 8601 | Standardized to YYYY-MM-DD |

| Negative prices | 17 rows | 0 | Investigated — data entry errors, corrected |