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

> Master data cleaning with pandas using a 4-step engineering workflow. Learn best practices for deduplication, standardization, imputation, and normalization to refine your data.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: best-practices
- Published: 2026-08-06

---

**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](https://github.com/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/pandas/core/frame.py) removes identical rows while preserving the first occurrence by default.

```python
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.

```python

# 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.

```python

# 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.

```python

# 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/data_cleaning.md) reference:

```python
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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/data_cleaning.md) | Central reference for pandas cleaning best practices |

View the complete documentation at [github.com/DataExpert-io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/data_cleaning.md).

## 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.