Data Cleaning, Preparation & Feature Engineering

The predictive ceiling of any statistical or machine learning model is strictly bounded by the structural fidelity of its training data. This fundamental reality is formalized in computer science as the Garbage In, Garbage Out (GIGO) principle. In industrial data science, empirical research confirms that approximately 70% to 80% of project time is consumed by exploratory data cleaning, structural reconciliation, and feature engineering.

The 6 Core Dimensions of Data Quality

Before writing transformation code, professional data engineers assess datasets against six universal dimensions of data quality:

• Accuracy: The degree to which recorded values match true real-world quantities (e.g., a human age of 240 is inaccurate).
• Completeness: The proportion of non-null observations across all required features.
• Consistency: The absence of conflicting data across different tables, sources, or records (e.g., a customer marked as "Churned" with active recurring billing).
• Timeliness: The latency between real-world event occurrence and pipeline availability.
• Validity: Conformity to prescribed business domain rules, schema constraints, and format standards (e.g., valid email syntax, ISO 8601 timestamps).
• Uniqueness: The guarantee that each real-world entity is represented exactly once without duplicate rows.

RAW HETEROGENEOUS INGESTION
+---------------------------------------------------------------------------------+
| Ingest Raw Files (CSV, Parquet, JSON, SQL Lakehouse)                             |
+---------------------------------------------------------------------------------+
                                      |
                                      v
[ STAGE 1: AUTOMATED DATA PROFILING ]
- Audit Null Rates, Cardinality, Value Distributions, Schema Types
                                      |
                                      v
[ STAGE 2: STRUCTURAL CLEANING & DEDUPLICATION ]
- Strip Whitespace, Case Normalization, Exact & Fuzzy Deduplication
                                      |
                                      v
[ STAGE 3: MISSING VALUE IMPUTATION ]
- Classify Mechanisms (MCAR, MAR, MNAR) -> Univariate Median / KNN Imputation
                                      |
                                      v
[ STAGE 4: OUTLIER MITIGATION ]
- Identify Tukey IQR / Z-Score Extremes -> Capping (Winsorization) / Trimming
                                      |
                                      v
[ STAGE 5: FEATURE ENCODING & SCALING ]
- One-Hot / Target Encoding for Categoricals -> Min-Max / Robust Scaling
                                      |
                                      v
[ STAGE 6: FEATURE ENGINEERING & LEAKAGE-FREE PIPELINE ]
- Interaction Terms, Log Transforms, Sklearn Pipeline Assembly
+---------------------------------------------------------------------------------+
| OUTPUT: Clean, Leakage-Free Design Matrix (X_train, X_test, y_train, y_test)    |
+---------------------------------------------------------------------------------+
        

Automated Data Profiling

Data profiling systematically inspects schema types, value frequencies, unique cardinality, memory footprints, and missingness patterns before any transformation is applied:

import pandas as pd
import numpy as np

def profile_dataset(df: pd.DataFrame) -> pd.DataFrame:
    """Generates comprehensive structural health metrics for every column."""
    profile_records = []
    total_rows = len(df)
    
    for col in df.columns:
        null_count = df[col].isna().sum()
        null_pct = (null_count / total_rows) * 100.0
        unique_count = df[col].nunique()
        dtype_str = str(df[col].dtype)
        
        profile_records.append({
            "Column": col,
            "Dtype": dtype_str,
            "Null_Count": null_count,
            "Null_Pct": round(null_pct, 2),
            "Unique_Count": unique_count,
            "Cardinality_Ratio": round(unique_count / total_rows, 4)
        })
        
    return pd.DataFrame(profile_records)

Treating missing data as a simple matter of replacing nulls with the arithmetic mean introduces severe statistical bias, artificially collapses variance, and distorts covariance structures. Donald Rubin's foundational statistical theory establishes three distinct mechanisms governing missing data:

Rubin's 3 Missingness Mechanisms

• MCAR (Missing Completely at Random): The probability of missingness is entirely unrelated to any observed or unobserved variable (e.g., a laboratory test tube dropped randomly on the floor). Listwise deletion (dropping rows) is valid for MCAR because it reduces sample size without introducing systemic bias.
• MAR (Missing at Random): The probability of missingness depends systematically on observed variables in the dataset, but not on the missing value itself (e.g., men in a medical study may be less likely to report depression scores than women, but conditional on gender, missingness is random). Imputation conditioning on observed variables (e.g., regression or KNN imputation) yields unbiased estimates.
• MNAR (Missing Not at Random): The probability of missingness depends directly on the unobserved value itself (e.g., high-income individuals refusing to disclose their wealth on a tax survey). Dropping rows or applying standard mean imputation severely biases the model; MNAR requires explicit missingness indicator flags or domain modeling.

import numpy as np
import pandas as pd
from sklearn.impute import SimpleImputer, KNNImputer

raw_data = pd.DataFrame({
    "age": [25.0, np.nan, 47.0, 52.0, 31.0],
    "income": [55000.0, 62000.0, np.nan, 110000.0, 58000.0],
    "credit_score": [710.0, 680.0, 750.0, np.nan, 690.0]
})

# 1. Univariate Median Imputation (Fast baseline)
median_imputer = SimpleImputer(strategy="median")
imputed_median = median_imputer.fit_transform(raw_data)

# 2. Multivariate KNN Imputation (Preserves covariance structure)
knn_imputer = KNNImputer(n_neighbors=2, weights="distance")
imputed_knn = knn_imputer.fit_transform(raw_data)
df_clean_knn = pd.DataFrame(imputed_knn, columns=raw_data.columns)

Inconsistent data formatting undermines join keys, creates redundant categorical levels (e.g., "New York", "new york", "NY "), and consumes excessive memory.

Exact vs. Fuzzy Deduplication

Exact duplicates share identical values across all columns and are excised using hash-based row comparisons via df.drop_duplicates(). Fuzzy duplicates represent the same real-world entity with minor typographical discrepancies (e.g., "Johnathan Doe" vs. "John Doe") and require string distance algorithms like Levenshtein distance or soundex phonetic indexing.

import pandas as pd

df_raw = pd.DataFrame({
    "customer_name": ["  Diya Patel ", "diya patel", "Marcus Aurelius\n"],
    "state_code": [" ca ", "CA", "Ny"],
    "order_total": ["$1,250.50", "$420.00", "MISSING"],
    "signup_date": ["2023/10/14", "14-10-2023", "2023-10-15"]
})

# 1. Standardize text features: Strip whitespace and case-fold
df_raw["customer_name"] = df_raw["customer_name"].str.strip().str.title()
df_raw["state_code"] = df_raw["state_code"].str.strip().str.upper()

# 2. Clean currency strings and cast to floating-point
df_raw["order_total"] = (
    df_raw["order_total"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)
df_raw["order_total"] = pd.to_numeric(df_raw["order_total"], errors="coerce")

# 3. Standardize heterogeneous dates to ISO-8601 Datetime
df_raw["signup_date"] = pd.to_datetime(df_raw["signup_date"], format="mixed")

# 4. Memory Optimization: Downcast high-cardinality strings to Categorical
df_raw["state_code"] = df_raw["state_code"].astype("category")

An outlier is an observation that deviates markedly from the overall distribution of values. Outliers stem from three distinct sources: data entry errors (e.g., weight recorded as 750 kg instead of 75 kg), sensor measurement failures, or genuine natural extremes (e.g., catastrophic financial crash returns).

Parametric (Z-Score) vs. Non-Parametric (Tukey's IQR) Detection

• Z-Score Method (Parametric): Assumes the underlying data is normally distributed. Measures how many standard deviations an observation lies from the mean:
  Z = (Value - Mean) / Standard_Deviation.
  Observations where absolute Z exceeds 3.0 are flagged as severe anomalies (occurring with less than 0.27% probability under a Gaussian curve).
• Tukey's Fences / IQR Method (Non-Parametric): Makes zero distributional assumptions and is completely robust to extreme skewness. Utilizes the Interquartile Range (IQR = 75th Percentile - 25th Percentile):
  Lower_Fence = 25th_Percentile - (1.5 * IQR)
Upper_Fence = 75th_Percentile + (1.5 * IQR)

TUKEY'S BOXPLOT FENCES (Non-Parametric):
                    +-------------------+
  ---|--------------|     |   +   |     |--------------|---  o (Extreme Outlier)
     |              +-------------------+              |
Lower Fence         25th   Median 75th             Upper Fence
Q1 - (1.5 * IQR)    (Q1)   (Q2)   (Q3)             Q3 + (1.5 * IQR)

GAUSSIAN Z-SCORE THRESHOLDS (Parametric):
                  -3 SD        -2 SD        -1 SD       Mean       +1 SD        +2 SD        +3 SD
Thresholds:       |------------|------------|-----------( 0 )-----------|------------|------------|
Probability:      |   0.1%     |    2.1%    |   13.6%   |   34.1%   |   34.1%    |    13.6%   |    2.1%    | 0.1%
Action:           Flag Outlier                                                                Flag Outlier
        

import numpy as np
import pandas as pd

def cap_outliers_iqr(series: pd.Series, factor: float = 1.5) -> pd.Series:
    """Clamps extreme values to inner Tukey fences (Winsorization)."""
    q25 = series.quantile(0.25)
    q75 = series.quantile(0.75)
    iqr = q75 - q25
    
    lower_bound = q25 - (factor * iqr)
    upper_bound = q75 + (factor * iqr)
    
    # Vectorized clipping prevents data loss while mitigating extreme leverage
    return series.clip(lower=lower_bound, upper=upper_bound)

Machine learning optimization routines (e.g., gradient descent, matrix factorizations) execute strictly on real-valued numerical matrices. Discrete qualitative attributes must be mathematically transformed into numerical vectors without introducing artificial ordinal bias.

One-Hot Encoding vs. Ordinal Encoding

• One-Hot Encoding (Nominal Features): For categories with no inherent natural hierarchy (e.g., ["Red", "Green", "Blue"]). Creates a binary indicator column for each unique category level. To prevent the Dummy Variable Trap (perfect multicollinearity in linear models), one indicator column must be dropped: drop_first=True.
• Ordinal Encoding (Ordinal Features): For categories with an unambiguous, monotonic ranking (e.g., ["Low", "Medium", "High"] → [0, 1, 2]). Preserves ordered topological relationships.

import pandas as pd
from sklearn.preprocessing import OneHotEncoder, OrdinalEncoder

df_sample = pd.DataFrame({
    "city": ["Berlin", "Tokyo", "Paris", "Tokyo"],
    "customer_tier": ["Bronze", "Platinum", "Silver", "Bronze"]
})

# 1. One-Hot Encoding for Nominal feature (dropping first category to avoid Dummy Trap)
ohe = OneHotEncoder(drop="first", sparse_output=False)
encoded_city = ohe.fit_transform(df_sample[["city"]])

# 2. Ordinal Encoding with explicit monotonic ordering hierarchy
tier_order = [["Bronze", "Silver", "Gold", "Platinum"]]
ordinal_enc = OrdinalEncoder(categories=tier_order)
df_sample["tier_encoded"] = ordinal_enc.fit_transform(df_sample[["customer_tier"]])

Features measured on dramatically disparate scales (e.g., Annual Income spanning 20,000 to 500,000 vs. Age spanning 18 to 75) severely impair machine learning algorithms. Distance-based models (KNN, Support Vector Machines, K-Means clustering) and gradient-optimized models (Logistic Regression, Neural Networks) will assign disproportionate weight to variables with large raw numerical magnitudes.

from sklearn.preprocessing import MinMaxScaler, StandardScaler, RobustScaler

numeric_matrix = df[["age", "income", "credit_score"]]

# 1. Min-Max Normalization: Suitable for bounded algorithms (Neural Network inputs)
scaled_minmax = MinMaxScaler().fit_transform(numeric_matrix)

# 2. Z-Score Standardization: Suitable for PCA and regularized linear regression
scaled_standard = StandardScaler().fit_transform(numeric_matrix)

# 3. Robust Scaling: Preferred when datasets possess heavy tails and verified outliers
scaled_robust = RobustScaler().fit_transform(numeric_matrix)

Feature engineering is the process of synthesizing new informative predictors from raw variables using domain knowledge, mathematical transformations, and relational combinations. It frequently provides higher performance boosts to machine learning models than hyperparameter tuning.

Distribution Normalization: Log & Power Transforms

Many parametric algorithms assume features are approximately normally distributed. Highly right-skewed variables (e.g., transaction volumes, web traffic counts) can be transformed using the natural logarithm:
Transformed_X = log(1 + X) (implemented via np.log1p to prevent undefined zero errors).

import numpy as np
import pandas as pd

df_analytics = pd.DataFrame({
    "user_id": [101, 102, 103],
    "total_spend": [1500.0, 45000.0, 300.0],
    "total_sessions": [10, 50, 2],
    "tenure_months": [12, 24, 3]
})

# 1. Domain Ratio Feature: Spend velocity per session
df_analytics["spend_per_session"] = (
    df_analytics["total_spend"] / df_analytics["total_sessions"]
)

# 2. Log Transformation for heavily skewed financial values
df_analytics["log_spend"] = np.log1p(df_analytics["total_spend"])

# 3. Cyclical Temporal Encoding: Preserve continuous time continuity
hour_of_day = 14  # 2:00 PM
df_analytics["sin_hour"] = np.sin(2 * np.pi * hour_of_day / 24.0)
df_analytics["cos_hour"] = np.cos(2 * np.pi * hour_of_day / 24.0)

The most catastrophic error in applied data science is Data Leakage: the inadvertent contamination of training data with statistical information belonging strictly to the test set or future production inferences.

INCORRECT WORKFLOW (Severe Data Leakage):
Raw Dataset ===> [ Compute Global Mean & Scaler ] ===> [ Train / Test Split ]  (FLAWED!)
                       ^
                       Test set information leaks into training partition!

CORRECT PRODUCTION WORKFLOW (Strict Leak-Free Pipeline):
Raw Dataset ===> [ 1. TRAIN / TEST SPLIT FIRST ]
                        |
        +---------------+---------------+
        |                               |
        v                               v
[ Training Set (80%) ]          [ Test Set (20%) ]
        |                               |
        v                               |
[ Compute Means & Scalers ]             |
(Call .fit_transform())                 |
        |                               |
        v                               v
[ Fitted Pipeline ] ========> [ Apply Pre-computed Parameters ]
                              (Call .transform() ONLY - ZERO fitting!)
        

from sklearn.model_selection import train_test_split
from sklearn.pipeline import Pipeline
from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.preprocessing import StandardScaler, OneHotEncoder
from sklearn.ensemble import RandomForestClassifier

# 1. Strict Initial Partitioning
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.20, random_state=42, stratify=y
)

# 2. Sub-pipeline for continuous numerical predictors
numeric_transformer = Pipeline(steps=[
    ("imputer", SimpleImputer(strategy="median")),
    ("scaler", StandardScaler())
])

# 3. Sub-pipeline for discrete nominal predictors
categorical_transformer = Pipeline(steps=[
    ("imputer", SimpleImputer(strategy="most_frequent")),
    ("onehot", OneHotEncoder(handle_unknown="ignore", drop="first"))
])

# 4. ColumnTransformer coordinates transformations across feature subsets
preprocessor = ColumnTransformer(transformers=[
    ("num", numeric_transformer, ["age", "income", "credit_score"]),
    ("cat", categorical_transformer, ["city", "account_tier"])
])

# 5. Full End-to-End Leakage-Free Pipeline
model_pipeline = Pipeline(steps=[
    ("preprocessor", preprocessor),
    ("classifier", RandomForestClassifier(n_estimators=100, random_state=42))
])

# Fitting occurs STRICTLY on training split
model_pipeline.fit(X_train, y_train)

# Inference transforms test data using training parameters
test_accuracy = model_pipeline.score(X_test, y_test)
print(f"Leak-free Test Accuracy: {test_accuracy:.4f}")