Best Practices for Data Cleaning with pandas: A 4-Step Engineering Workflow

The Data Engineer Handbook recommends a four-step workflow for data cleaning with pandas: deduplicate rows, standardize column names, impute missing numeric values with the median, and normalize date fields to datetime.

Effective data pipelines depend on clean, predictable data. The DataExpert-io/data-engineer-handbook repository documents a concise, repeatable approach to data cleaning with pandas that prioritizes downstream reliability. This workflow is deliberately sequenced so each step builds on the previous, reducing errors and simplifying debugging.

Why Data Cleaning Order Matters

The handbook emphasizes sequential execution for a reason. Deduplication must happen before type conversion. Column name standardization must precede any column-specific operations. This deterministic structure ensures that later transformations—feature engineering, model training, or analytics—operate on consistent data.

4 Core Steps for Data Cleaning with pandas

Step 1: Deduplicate Rows with drop_duplicates()

Duplicate records cause data leakage in ML pipelines and inflate aggregate metrics. The drop_duplicates() method in pandas/core/frame.py removes identical rows while preserving the first occurrence by default.

import pandas as pd

# Load raw data

df = pd.read_csv("data.csv")

# Remove duplicate rows

df = df.drop_duplicates()

Set keep='last' to retain the most recent record, or keep=False to drop all duplicates entirely.

Step 2: Standardize Column Names

Inconsistent naming conventions break downstream references. The handbook recommends lower-case names with underscores replacing spaces—this creates a predictable schema that SQL queries, Python attribute access, and configuration files can rely on.

# Standardize column names

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

This single list comprehension handles mixed-case headers, trailing spaces, and inconsistent delimiters common in exported spreadsheets.

Step 3: Handle Missing Values with Median Imputation

Numeric nulls require domain-aware treatment. The handbook defaults to median imputation for numeric columns, as the median preserves distributional characteristics better than the mean in skewed datasets.

# Isolate numeric columns

num_cols = df.select_dtypes(include="number").columns

# Impute with median

df[num_cols] = df[num_cols].fillna(df[num_cols].median())

Use include=["int64", "float64"] for stricter type matching, or switch to mean() when working with normally distributed data.

Step 4: Normalize Date Fields to datetime

Date strings in inconsistent formats cause silent parsing failures. Converting to pandas' datetime64[ns] type via pd.to_datetime() enables time-based feature engineering and guarantees consistent timezone handling.

# Convert date column if present

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

The errors='coerce' parameter converts unparseable values to NaT (Not a Time) for explicit handling rather than raising exceptions.

Complete Data Cleaning Pipeline

Combine all four steps into a reusable function per the handbook's data_cleaning.md reference:

import pandas as pd

def clean_dataframe(df: pd.DataFrame, date_col: str = "date") -> pd.DataFrame:
    """
    Apply handbook-recommended data cleaning with pandas.
    
    Source: DataExpert-io/data-engineer-handbook/data_cleaning.md
    """
    # 1. Deduplicate

    df = df.drop_duplicates()
    
    # 2. Standardize column names

    df.columns = [c.lower().replace(" ", "_") for c in df.columns]
    
    # 3. Impute numeric missing values

    num_cols = df.select_dtypes(include="number").columns
    df[num_cols] = df[num_cols].fillna(df[num_cols].median())
    
    # 4. Normalize dates

    if date_col in df.columns:
        df[date_col] = pd.to_datetime(df[date_col])
    
    return df


# Usage

df_raw = pd.read_csv("raw_data.csv")
df_clean = clean_dataframe(df_raw)

Key Source Files

The handbook's cleaning workflow is documented in:

File	Purpose
data_cleaning.md	Central reference for pandas cleaning best practices

View the complete documentation at github.com/DataExpert-io/data-engineer-handbook.

Summary

• Deduplicate first using drop_duplicates() to eliminate observation-level redundancy
• Standardize names to lower-case with underscores for schema predictability
• Impute numerics with median via fillna() to preserve distributions
• Convert dates to datetime early for reliable temporal operations
• Execute sequentially so later steps operate on clean, deterministic data

Frequently Asked Questions

Should I use mean or median for missing numeric values in pandas?

Median imputation is generally preferred according to the handbook, as it is robust to outliers and preserves the central tendency of skewed distributions. Use mean only when data is normally distributed and outliers are explicitly handled.

What is the correct order for data cleaning steps with pandas?

Deduplication → column name standardization → missing value imputation → type conversion (including dates). This sequence prevents operations like fillna() from being applied to duplicate rows and ensures column references match after renaming.

How does pd.to_datetime() handle malformed date strings?

By default, pd.to_datetime() raises a ParserError for unparseable formats. Pass errors='coerce' to convert invalid values to NaT (Not a Time), allowing explicit null handling rather than pipeline failure.

Does drop_duplicates() modify the original DataFrame?

No—drop_duplicates() returns a new DataFrame by default. Assign the result (e.g., df = df.drop_duplicates()) or pass inplace=True to modify in place, though explicit assignment is preferred for pipeline clarity.

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