Data cleaning in Python with pandas means loading a raw dataset into a DataFrame and fixing what would make your analysis wrong: columns stored with the wrong type, inconsistent dates, missing values, duplicate rows, messy category labels, and suspicious outliers. The goal is not a perfect table. It is a table you can trust enough to answer a business question, with every change documented so someone else can repeat it.

Why it matters: analysts often say cleaning takes more time than the analysis itself, and the mistakes made here travel all the way to the dashboard. A revenue total that counts the same order twice or a date column read as text will produce a confident, wrong answer. This guide gives you a repeatable step-by-step workflow you can apply to almost any messy CSV or spreadsheet export.

Before you start: a sample messy dataset

Imagine an export of online orders with columns like order_id, order_date, customer_email, country, amount, and status. Typical problems: dates in two formats, amounts stored as text with currency symbols, "USA", "usa", and "United States" in the same column, blank emails, and a few orders exported twice. Every step below targets one of those issues. If you are moving from spreadsheets, why Python is the next step after Excel is a good primer.

Step 1: Load the data and keep the raw copy

import pandas as pd

raw = pd.read_csv("orders.csv")
df = raw.copy()

import pandas as pd

raw = pd.read_csv("orders.csv")
df = raw.copy()

import pandas as pd

raw = pd.read_csv("orders.csv")
df = raw.copy()

import pandas as pd

raw = pd.read_csv("orders.csv")
df = raw.copy()

Never clean the only copy. Keeping raw untouched lets you compare before and after and undo a bad decision.

Step 2: Profile before you change anything

Spend a few minutes understanding the shape of the problem:

df.shape
df.info()
df.isna().sum()
df.describe(include="all")
df["country"].value_counts(dropna=False)

df.shape
df.info()
df.isna().sum()
df.describe(include="all")
df["country"].value_counts(dropna=False)

df.shape
df.info()
df.isna().sum()
df.describe(include="all")
df["country"].value_counts(dropna=False)

df.shape
df.info()
df.isna().sum()
df.describe(include="all")
df["country"].value_counts(dropna=False)

info() shows column types and non-null counts; value_counts exposes inconsistent labels. Write down what you find. Those notes become your cleaning plan and, later, the assumptions section of your analysis.

Step 3: Standardize column names

df.columns = (df.columns.str.strip()
                        .str.lower()
                        .str.replace(" ", "_"))

df.columns = (df.columns.str.strip()
                        .str.lower()
                        .str.replace(" ", "_"))

df.columns = (df.columns.str.strip()
                        .str.lower()
                        .str.replace(" ", "_"))

df.columns = (df.columns.str.strip()
                        .str.lower()
                        .str.replace(" ", "_"))

Consistent snake_case names prevent errors from hidden spaces and make code easier to read.

Step 4: Fix data types

Numbers stored as text cannot be summed, and dates stored as text cannot be filtered by month. Convert them explicitly:

df["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(r"[$,]", "", regex=True),
    errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

df["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(r"[$,]", "", regex=True),
    errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

df["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(r"[$,]", "", regex=True),
    errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

df["amount"] = pd.to_numeric(
    df["amount"].astype(str).str.replace(r"[$,]", "", regex=True),
    errors="coerce")
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

With errors="coerce", values that cannot be parsed become missing instead of crashing the script. Always check how many values were coerced; a sudden jump in missing dates usually means a format you did not expect. The pandas user guide documents parsing options in detail.

Step 5: Clean text and categories

df["country"] = df["country"].str.strip().str.upper()
df["country"] = df["country"].replace(
    {"USA": "US", "UNITED STATES": "US"})
df["customer_email"] = df["customer_email"].str.strip().str.lower()

df["country"] = df["country"].str.strip().str.upper()
df["country"] = df["country"].replace(
    {"USA": "US", "UNITED STATES": "US"})
df["customer_email"] = df["customer_email"].str.strip().str.lower()

df["country"] = df["country"].str.strip().str.upper()
df["country"] = df["country"].replace(
    {"USA": "US", "UNITED STATES": "US"})
df["customer_email"] = df["customer_email"].str.strip().str.lower()

df["country"] = df["country"].str.strip().str.upper()
df["country"] = df["country"].replace(
    {"USA": "US", "UNITED STATES": "US"})
df["customer_email"] = df["customer_email"].str.strip().str.lower()

Run value_counts again afterward. Every remaining variant is a decision you should make on purpose, not leave to chance.

Step 6: Handle missing values deliberately

There is no single right answer for missing data. Choose per column, based on what the analysis needs:

• Drop rows when the missing field is essential (an order without an ID or amount).

• Fill with a label such as "unknown" for categories, so the rows still count.

• Impute numeric values with a median only when you can justify it, and flag imputed rows in a separate column.

• Leave as missing when filling would invent information.

The pandas guide to working with missing data explains how NaN, None, and NaT behave in each case.

df = df.dropna(subset=["order_id", "amount"])
df["country"] = df["country"].fillna("UNKNOWN")

df = df.dropna(subset=["order_id", "amount"])
df["country"] = df["country"].fillna("UNKNOWN")

df = df.dropna(subset=["order_id", "amount"])
df["country"] = df["country"].fillna("UNKNOWN")

df = df.dropna(subset=["order_id", "amount"])
df["country"] = df["country"].fillna("UNKNOWN")

Step 7: Remove duplicates

dupes = df.duplicated(subset=["order_id"], keep="first")
print(dupes.sum(), "duplicate orders")
df = df[~dupes]

dupes = df.duplicated(subset=["order_id"], keep="first")
print(dupes.sum(), "duplicate orders")
df = df[~dupes]

dupes = df.duplicated(subset=["order_id"], keep="first")
print(dupes.sum(), "duplicate orders")
df = df[~dupes]

dupes = df.duplicated(subset=["order_id"], keep="first")
print(dupes.sum(), "duplicate orders")
df = df[~dupes]

Decide what makes a row unique. Exact duplicates are easy; two rows with the same order ID but different amounts need investigation, not silent deletion.

Step 8: Check outliers and impossible values

Some values are rare but real; others are errors. Use simple rules to find candidates, then look at them:

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
suspects = df[(df["amount"] < 0) | (df["amount"] > q3 + 3 * iqr)]

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
suspects = df[(df["amount"] < 0) | (df["amount"] > q3 + 3 * iqr)]

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
suspects = df[(df["amount"] < 0) | (df["amount"] > q3 + 3 * iqr)]

q1, q3 = df["amount"].quantile([0.25, 0.75])
iqr = q3 - q1
suspects = df[(df["amount"] < 0) | (df["amount"] > q3 + 3 * iqr)]

Negative amounts might be refunds that belong in a separate analysis. A huge order might be a real corporate purchase. Document what you keep, cap, or remove, and why.

Step 9: Validate the result

Finish with checks that fail loudly if something is off:

assert df["order_id"].is_unique
assert df["amount"].ge(0).all()
assert df["order_date"].notna().all()

assert df["order_id"].is_unique
assert df["amount"].ge(0).all()
assert df["order_date"].notna().all()

assert df["order_id"].is_unique
assert df["amount"].ge(0).all()
assert df["order_date"].notna().all()

assert df["order_id"].is_unique
assert df["amount"].ge(0).all()
assert df["order_date"].notna().all()

Also compare row counts and key totals with the raw data so you can explain every row you removed. Data quality frameworks generally look at dimensions such as completeness, validity, consistency, and uniqueness; this step is where you confirm them. IBM's overview of data cleaning summarizes these quality checks well. For how teams monitor this at scale, see data observability.

Step 10: Turn the steps into a reusable pipeline

Messy exports come back every week. Put each step in a small function and chain them so the whole process runs in one command:

clean = (raw.pipe(standardize_columns)
            .pipe(fix_types)
            .pipe(clean_categories)
            .pipe(drop_duplicates_by_id))

clean = (raw.pipe(standardize_columns)
            .pipe(fix_types)
            .pipe(clean_categories)
            .pipe(drop_duplicates_by_id))

clean = (raw.pipe(standardize_columns)
            .pipe(fix_types)
            .pipe(clean_categories)
            .pipe(drop_duplicates_by_id))

clean = (raw.pipe(standardize_columns)
            .pipe(fix_types)
            .pipe(clean_categories)
            .pipe(drop_duplicates_by_id))

A pipeline like this is easy to test, easy to review, and makes a strong portfolio piece because it shows how you think, not just what you produced.

Common data cleaning mistakes

• Overwriting the raw data, so mistakes cannot be traced.

• Filling missing values with zero when zero has a real meaning.

• Dropping duplicates without defining what a duplicate is.

• Ignoring time zones and date formats when combining sources.

• Cleaning by hand in a spreadsheet, then forgetting the steps next month.

Many of the same habits apply when you clean data in a database first; see common mistakes when learning SQL.

FAQ

Should I clean data in SQL or pandas?

Both are common. Filtering and joining large tables is often easier in SQL near the source; detailed, exploratory cleaning is often faster in pandas. Many analysts use SQL to extract and pandas to refine.

Can AI tools clean data for me?

AI assistants can suggest code and spot patterns, which speeds up the work. You still need to review each change, because a confident suggestion can drop valid rows or merge categories that should stay separate.

How much Python do I need for this?

Basic syntax, functions, and the core pandas operations shown here are enough to clean most business datasets.

Practice on real business questions

The Data Analytics Course is an 11-week live program with one class per week and about 6 to 8 hours of weekly work. It covers data foundations, SQL, Python and exploratory analysis with pandas, visualization, statistics, experimentation, and AI-assisted analytics, ending with a capstone and career readiness. Developers who want to build LLM-powered systems can look at the engineering track instead.

• Data Analytics Course

• AI Engineering Course