Fixing Messy CSVs with Python & pandas

A CSV Data Cleaner is a script that reads a messy CSV file and turns it into clean, consistent, usable data. As a Forward Deployed Engineer you'll meet dirty data constantly — missing values, wrong types, weird formats, duplicate rows. This tutorial shows you how to fix the most common problems with Python and pandas.

Table of Contents

• What "dirty data" means
• Loading a CSV safely
• Handling missing values
• Fixing data types
• Cleaning text and dates
• Removing duplicates
• Putting it together

What "dirty data" means

Dirty data is data that can't be trusted as-is. Common problems: empty cells, numbers stored as text, inconsistent date formats, stray whitespace, and duplicate rows. Each one silently breaks counts, averages, joins, and reports further down the line.

Cleaning is usually 60–80% of real data work. It's the boring-but-critical step that decides whether your output is correct or garbage.

Loading a CSV safely

Use pandas.read_csv() so you can inspect the data before touching it.

import pandas as pd

df = pd.read_csv("orders.csv")
print(df.head())       # first 5 rows
print(df.info())       # column types + how many non-empty values
print(df.shape)        # (rows, columns)

df.info() is your best friend. It shows every column, its data type, and how many values are missing. That alone tells you most of what you need to fix.

Handling missing values

Missing values show up as NaN. Decide per column how to handle them: drop rows, drop the whole column, or fill with a value.

# Drop rows that have no order_id (can't do anything with them)
df = df.dropna(subset=["order_id"])

# Fill missing price with the column average
df["price"] = df["price"].fillna(df["price"].mean())

# Fill missing category with a placeholder
df["category"] = df["category"].fillna("unknown")

# Drop a column that is almost entirely empty
df = df.drop(columns=["notes"], errors="ignore")

Never blanket-fill everything. A missing number and a missing category need different treatments. Think about what each missing cell means before you fill it.

Fixing data types

A column often arrives as text when it should be numeric, or as a string when it should be a date. Force the right type.

# Convert a text column of prices to numbers
df["price"] = pd.to_numeric(df["price"], errors="coerce")
# "coerce" turns anything un-movable into NaN instead of crashing

# Convert a quantity column to integers
df["qty"] = df["qty"].astype("Int64")

After converting, check df["price"].isna().sum() again — the rows that failed conversion just became missing, so you may need to drop or fill them too.

Cleaning text and dates

Stray whitespace and inconsistent formats are everywhere. Normalize them.

# Strip whitespace and standardize case
df["customer_name"] = df["customer_name"].str.strip().str.title()

# Normalize date strings into real dates
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

pd.to_datetime() is a powerhouse — it parses most date formats automatically. Afterward the column becomes a proper datetime type so you can sort, filter, and compare dates.

Removing duplicates

Duplicates inflate your totals. Drop them, choosing which row to keep.

# Drop rows where every value is identical
df = df.drop_duplicates()

# Drop rows that share the same order_id (keep the first occurrence)
df = df.drop_duplicates(subset=["order_id"], keep="first")

When you dedupe on a subset, you decide what makes two rows "the same." Pick the columns that define uniqueness — often an ID, not the whole row.

Putting it together

Here's a full cleaner for a messy orders.csv, step by step.

import pandas as pd

def clean_orders(path):
    df = pd.read_csv(path)

    # Drop rows with no ID, then duplicates
    df = df.dropna(subset=["order_id"])
    df = df.drop_duplicates(subset=["order_id"], keep="first")

    # Clean text
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    df["category"] = df["category"].fillna("unknown").str.strip().str.lower()

    # Fix types and dates
    df["price"] = pd.to_numeric(df["price"], errors="coerce")
    df["qty"] = pd.to_numeric(df["qty"], errors="coerce").astype("Int64")
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

    # Finish: fill leftover numeric gaps with the mean, then drop bad dates
    df["price"] = df["price"].fillna(df["price"].mean())
    df = df.dropna(subset=["order_date", "qty"])

    # Save cleaned output
    df.to_csv("orders_clean.csv", index=False)
    return df

df = clean_orders("orders.csv")
print(df.info())

This runs through every problem from the sections above in one pass. index=False stops pandas from writing its own row numbers into the CSV.