Best Practices for Data Cleaning Using Pandas: A Data Engineer Handbook Guide

The Data Engineer Handbook prescribes a five-step pandas workflow—remove duplicates, standardize column names, impute missing numeric values with the median, convert date strings to datetime objects, and validate data types—to create production-ready datasets that prevent data leakage and runtime errors.

Data cleaning is the critical first step that determines the reliability of every downstream analysis and machine learning model. According to the DataExpert-io/data-engineer-handbook repository, implementing a standardized pandas cleaning routine creates reusable pipeline components and ensures consistent data hygiene across ingestion jobs. The following best practices for data cleaning using pandas are derived directly from the handbook's data_cleaning.md file and reflect patterns used in production data engineering workflows.

The Five-Step Cleaning Process

The handbook outlines a concise, repeatable process implemented directly with pandas. Each step targets a specific data quality issue that commonly corrupts analytics or breaks machine learning pipelines.

Remove Duplicate Rows

Duplicate records inflate training datasets, cause data leakage in ML models, and bias statistical aggregations. The handbook recommends immediate deduplication as the first operation to ensure each observation represents a unique entity.

In data_cleaning.md#L4-L5, the authors specify using drop_duplicates() immediately after loading data:

df = df.drop_duplicates()

This method checks all columns by default and keeps the first occurrence, which is sufficient for most raw data ingestion scenarios.

Standardize Column Names

Inconsistent naming conventions—mixed cases, spaces, and special characters—simplify code readability and reduce bugs when joining datasets across different sources. The handbook advocates for lowercase, underscore-separated column names as a defensive programming practice.

According to data_cleaning.md#L5-L6, transform column headers using list comprehension:

df.columns = [c.lower().replace(" ", "_") for c in df.columns]

This one-line operation ensures that Customer ID becomes customer_id and Purchase Date becomes purchase_date, preventing attribute errors during subsequent transformations.

Handle Missing Values

Missing data breaks algorithms or produces misleading results if handled improperly. The handbook recommends domain-specific logic for categorical variables and median imputation for numeric columns to maintain analytical soundness without introducing extreme outliers.

As documented in data_cleaning.md#L7-L9, select numeric columns by dtype and fill with the median:

num_cols = df.select_dtypes(include="number").columns
df[num_cols] = df[num_cols].fillna(df[num_cols].median())

Using the median rather than the mean provides robustness against skewed distributions and extreme values that might distort the central tendency.

Convert Dates to Datetime

Proper datetime objects enable time-based slicing, resampling, and temporal feature engineering that string representations cannot support. The handbook emphasizes explicit conversion to prevent type errors during time-series operations.

Per data_cleaning.md#L10-L12, conditionally convert date columns:

if "date" in df.columns:
    df["date"] = pd.to_datetime(df["date"])

This defensive check prevents KeyError exceptions when processing schemas that may not contain temporal fields, while ensuring ISO-compliant datetime parsing when present.

Validate Data Types

Ensuring each column has the expected type prevents runtime errors later in pipelines, particularly during model training where scikit-learn estimators require strict numeric inputs. While not explicitly line-referenced in the cleaning guide, the handbook implies type validation through its emphasis on select_dtypes usage and clean data architecture.

Verify types with df.dtypes and cast as needed:

df["customer_id"] = df["customer_id"].astype("int")
df["category"] = df["category"].astype("category")

Production Implementation Strategy

Codifying these steps into a reusable module enables version control, unit testing, and reuse across Jupyter notebooks, Airflow tasks, and production pipelines.

Create a Reusable Cleaning Function

Wrap the five steps into a single function that accepts a file path and returns a cleaned DataFrame. This pattern appears in the handbook's example code and supports idempotent pipeline operations:

import pandas as pd

def clean_dataframe(path: str) -> pd.DataFrame:
    """
    Load a CSV and apply the handbook's data-cleaning best practices.
    """
    # Load raw data

    df = pd.read_csv(path)

    # 1️⃣ Drop duplicates

    df = df.drop_duplicates()

    # 2️⃣ Standardise column names

    df.columns = [c.lower().replace(" ", "_") for c in df.columns]

    # 3️⃣ Impute missing numeric values with median

    num_cols = df.select_dtypes(include="number").columns
    df[num_cols] = df[num_cols].fillna(df[num_cols].median())

    # 4️⃣ Convert any 'date' column to datetime

    if "date" in df.columns:
        df["date"] = pd.to_datetime(df["date"])

    return df

# Example usage

cleaned_df = clean_dataframe("data.csv")
print(cleaned_df.head())

Running this function on any CSV automatically enforces the core cleaning steps, making the output ready for downstream analytics or machine-learning models.

Pipeline Architecture Integration

The handbook suggests embedding this cleaning logic between the ingestion layer and the analytics warehouse. The recommended flow follows a medallion architecture pattern:

• Ingestion layer receives raw CSV/Parquet files
• Cleaning step executes the pandas script (as implemented in cleaning.py)
• Gold zone stores the cleaned parquet files with strict schema enforcement

By isolating cleaning logic in a dedicated module (e.g., cleaning.py), data teams guarantee that every new raw file entering the data lake undergoes identical hygiene checks before consumption by BI tools or ML training jobs.

Summary

• Remove duplicates first using df.drop_duplicates() to prevent data leakage and statistical bias.
• Standardize column names to lowercase with underscores to eliminate whitespace-related bugs.
• Impute numeric missing values with the median rather than the mean to handle skewed distributions.
• Convert date strings to datetime objects using pd.to_datetime() for time-series compatibility.
• Validate data types after cleaning to ensure schema consistency across pipeline stages.

Frequently Asked Questions

How do I handle missing values in pandas without biasing my data?

The Data Engineer Handbook recommends using the median for numeric imputation rather than the mean, as implemented in data_cleaning.md#L7-L9. The median provides robustness against outliers and skewed distributions. For categorical variables, use mode imputation or create an explicit "Unknown" category to preserve the missingness indicator as a potential feature.

Why should I standardize column names before data analysis?

Standardizing column names to lowercase with underscores prevents attribute access errors and simplifies query writing. As noted in data_cleaning.md#L5-L6, inconsistent naming causes bugs when joining datasets from different sources. The transformation df.columns = [c.lower().replace(" ", "_") for c in df.columns] ensures that column references remain consistent across SQL queries, Python code, and configuration files.

How do I prevent duplicate rows from causing data leakage in ML pipelines?

Execute df.drop_duplicates() immediately after loading raw data, as specified in data_cleaning.md#L4-L5. Duplicate records artificially inflate training set size and cause identical observations to appear in both training and validation splits, leading to optimistic performance estimates that fail in production. Deduplication should occur before any train-test split operation.

What is the best way to validate data types in a pandas DataFrame?

Use df.dtypes to inspect current types and explicitly cast columns using astype(). The handbook emphasizes validating types after cleaning to prevent runtime errors during model training. For production pipelines, combine this with select_dtypes() to programmatically verify that numeric features contain only number-compatible values before feeding them to scikit-learn estimators.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}