Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types

Introduction

You've loaded your dataset, run a few exploratory queries, and then you notice it: a column that should contain prices has the word "N/A" stored as a string. A date column full of values like "01/15/2024", "2024-01-15", and "January 15th, 2024" — all representing the same date, all incompatible with each other. A customer ID that appears three times because someone exported the same records twice. Welcome to real-world data.

Data cleaning is where most data projects actually live. Analyses fail not because the math is wrong, but because the data going in is subtly broken. A missing value propagates silently. A numeric column stored as text causes an aggregation to return zero. A duplicate row inflates a sales total by 12%. These aren't edge cases — they're the norm. If you've spent time in Excel hunting down bad data or writing SQL WHERE clauses to exclude nulls and duplicates, you already understand the problem. pandas gives you a systematic, repeatable, and scriptable way to solve it.

By the end of this lesson, you'll have a complete cleaning toolkit that you can apply to any messy dataset you encounter in the wild.

What you'll learn:

• How to detect and handle missing values using isnull(), fillna(), and dropna()
• How to find and remove duplicate rows with duplicated() and drop_duplicates()
• How to audit and convert data types — including the tricky cases like currency strings, mixed-format dates, and boolean-ish columns
• How to build a reusable cleaning pipeline that runs in order without side effects
• How to validate your cleaning work so you can be confident the data is actually fixed

---

Prerequisites

This lesson assumes you're comfortable loading data into pandas and exploring it with methods like .head(), .info(), and .describe(). If you need a refresher, work through Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data before continuing. You should also have a working Python environment — if yours isn't set up yet, Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments walks you through it.

---

The Dataset: A Messy Sales Export

Throughout this lesson, we'll work with a realistic sales export — the kind you'd get from a CRM or ERP system that wasn't designed with data quality in mind. Let's create it programmatically so you can follow along exactly:

import pandas as pd
import numpy as np

data = {
    "order_id": [1001, 1002, 1003, 1002, 1004, 1005, 1006, 1007, 1008, 1009],
    "customer_name": ["Alice Huang", "Bob Martinez", "Carol Smith", "Bob Martinez",
                      "Diana Okafor", "Evan Chen", None, "Grace Lee", "Henry Park", "Isla Torres"],
    "order_date": ["2024-01-15", "2024-01-16", "01/17/2024", "2024-01-16",
                   "January 18, 2024", "2024-01-19", "2024-01-20", "2024-01-21",
                   "2024-01-22", "NOT A DATE"],
    "revenue": ["$1,250.00", "$875.50", "$3,100.00", "$875.50",
                None, "$420.75", "$2,200.00", "$615.00", "$1,890.25", "$740.00"],
    "region": ["West", "East", "North", "East", "South", "West", "North", "East", None, "South"],
    "is_repeat_customer": ["Yes", "No", "Yes", "No", "Yes", "TRUE", "False", "1", "No", "Yes"],
    "discount_pct": [10, 0, 15, 0, np.nan, 5, 20, 0, 10, np.nan],
}

df = pd.DataFrame(data)

Take a moment to look at what we've built. This data has:

• A duplicate row (order 1002 appears twice)
• Missing values in several forms: Python None, NumPy np.nan, and the string "N/A" hiding in revenue
• Mixed date formats across order_date
• Revenue stored as strings with dollar signs and commas
• A boolean column with five different representations of true/false
• A bad date that can't be parsed at all

This is not an exaggeration. This is Tuesday.

---

Step 1: The Initial Audit

Before you touch anything, you need to understand what you're dealing with. Cleaning without auditing is like performing surgery without X-rays.

# Shape of the data
print(df.shape)  # (10, 7)

# Data types and non-null counts
df.info()

df.info() is your first diagnostic tool. It tells you the dtype of each column and how many non-null values it contains. Right now, almost everything will show up as object (pandas-speak for string), which is a sign that numeric and date columns haven't been parsed correctly.

# Missing value summary
missing = df.isnull().sum()
missing_pct = (df.isnull().sum() / len(df) * 100).round(1)

audit = pd.DataFrame({
    "missing_count": missing,
    "missing_pct": missing_pct,
    "dtype": df.dtypes
})
print(audit)

This gives you a structured view of the damage:

                    missing_count  missing_pct   dtype
order_id                        0          0.0   int64
customer_name                   1         10.0  object
order_date                      0          0.0  object
revenue                         1         10.0  object
region                          1         10.0  object
is_repeat_customer              0          0.0  object
discount_pct                    2         20.0 float64

Notice that revenue shows only 1 missing — but we have "$875.50" stored as a string and what about that None we inserted? pandas converted it to NaN automatically during DataFrame construction. Good. But the "$1,250.00" values are still strings. We'll deal with that in the type conversion section.

---

Step 2: Handling Duplicate Rows

Duplicates are dangerous because they're often silent. A row that appears twice won't trigger an error — it'll just quietly double-count every metric derived from it.

Finding Duplicates

# Check for fully duplicated rows
print(df.duplicated().sum())  # 1 duplicate

# See the duplicate rows themselves
print(df[df.duplicated(keep=False)])

Using keep=False marks all copies of a duplicate row — both the original and the copy. This is useful for inspection. The default keep='first' marks only the second occurrence, which is what you want when you actually remove them.

# Which specific rows are duplicates?
print(df[df.duplicated(keep='first')])

This shows only the row with index 3 — Bob Martinez's order 1002 appearing a second time.

Duplicate on Specific Columns

Sometimes full-row duplication is too strict. What if two different customers placed the same order value on the same day by coincidence? You might want to deduplicate on a business key:

# Duplicate based on order_id alone (the true business key)
print(df[df.duplicated(subset=["order_id"], keep=False)])

This finds both copies of order 1002 and only those rows. In a production context, you'd use your natural key — order ID, transaction ID, or whatever uniquely identifies a record in the source system.

Removing Duplicates

df_clean = df.drop_duplicates(subset=["order_id"], keep="first").reset_index(drop=True)
print(df_clean.shape)  # (9, 7)

We use keep="first" to retain the original record and discard the later copy. reset_index(drop=True) cleans up the index so it runs 0–8 instead of having a gap.

---

Step 3: Handling Missing Values

Missing data is the most nuanced part of cleaning because the right strategy depends entirely on why the data is missing and how you plan to use it.

Three Strategies for Missing Values

1. Drop rows or columns — Use when missingness is random and the affected rows are few enough that removing them won't bias your analysis.

2. Fill with a constant — Use when the missing value has a known substitute, like filling missing discount percentages with 0 because no entry means no discount was applied.

3. Impute — Use when you need to preserve all rows and can make a reasonable statistical estimate (mean, median, mode, or forward-fill for time series).

Dropping Missing Values

# Drop rows where customer_name is missing
# (we can't analyze orders we can't attribute to a customer)
df_clean = df_clean.dropna(subset=["customer_name"])
print(df_clean.shape)  # (8, 7)

dropna(subset=["customer_name"]) drops only rows where customer_name is null, leaving all other rows untouched. Contrast with dropna() alone, which would drop any row with any null — far too aggressive for most real datasets.

# dropna() with no arguments: use with extreme caution
aggressive = df_clean.dropna()
print(aggressive.shape)  # Drops more rows than you expect

Filling Missing Values

# Fill missing discount_pct with 0 (business rule: no entry = no discount)
df_clean["discount_pct"] = df_clean["discount_pct"].fillna(0)

# Fill missing region with "Unknown" as a placeholder
df_clean["region"] = df_clean["region"].fillna("Unknown")

# Verify
print(df_clean[["discount_pct", "region"]].isnull().sum())

The business logic matters here. We fill discount_pct with 0 because the absence of a discount entry almost certainly means no discount was given — that's a meaningful zero, not a guess. Filling region with "Unknown" is more of a placeholder so we can keep the row in the dataset without misattributing it to a real region.

Forward Fill and Backward Fill

For time-series data or ordered records, you can propagate the last known value forward:

# Hypothetical: fill missing values with the previous row's value
df_clean["region"] = df_clean["region"].fillna(method="ffill")

Conditional Imputation

Sometimes the right fill value depends on another column — for example, filling missing revenue with the median revenue for that region:

# Impute missing revenue with median by region
# (We'll handle revenue type conversion first, but here's the pattern)
df_clean["revenue_numeric"] = (
    df_clean["revenue"]
    .str.replace(r"[$,]", "", regex=True)
    .pipe(pd.to_numeric, errors="coerce")
)

region_medians = df_clean.groupby("region")["revenue_numeric"].transform("median")
df_clean["revenue_numeric"] = df_clean["revenue_numeric"].fillna(region_medians)

This is a powerful pattern: transform("median") returns a Series with the same index as the original, so you can use it directly with fillna().

---

Step 4: Fixing Data Types

Type conversion is where many beginners get stuck, because pandas error messages can be cryptic and the source of the problem isn't always obvious. Let's work through the common scenarios systematically.

Currency Strings to Numeric

The revenue column contains values like "$1,250.00" — a perfectly readable format for humans, completely useless for arithmetic.

def parse_currency(series):
    """Convert currency strings like '$1,250.00' to float."""
    return (
        series
        .str.strip()                          # Remove leading/trailing whitespace
        .str.replace(r"[$,]", "", regex=True) # Remove $ and commas
        .pipe(pd.to_numeric, errors="coerce") # Convert, turning failures to NaN
    )

df_clean["revenue"] = parse_currency(df_clean["revenue"])
print(df_clean["revenue"].dtype)  # float64
print(df_clean["revenue"])

The critical detail here is errors="coerce" in pd.to_numeric(). Without it, a single bad value throws an exception and stops your entire pipeline. With it, bad values become NaN, which you can then handle explicitly. You trade a hard failure for a visible null — almost always the right choice in a cleaning pipeline.

Mixed-Format Dates

Our order_date column has three different formats and one completely invalid entry. This is a common export artifact when source data was entered by humans without format validation.

# First, see what we're dealing with
print(df_clean["order_date"].value_counts())

pd.to_datetime() with infer_datetime_format=True handles most common formats automatically, but when formats are truly mixed, you need errors="coerce":

df_clean["order_date"] = pd.to_datetime(df_clean["order_date"], errors="coerce")

print(df_clean["order_date"])
print(df_clean["order_date"].dtype)  # datetime64[ns]

The "NOT A DATE" entry becomes NaT (Not a Time) — pandas' equivalent of NaN for datetime columns. You can check for it just like a null:

bad_dates = df_clean[df_clean["order_date"].isnull()]
print(f"Rows with unparseable dates: {len(bad_dates)}")

Once your dates are proper datetime objects, you unlock a whole range of operations:

df_clean["order_month"] = df_clean["order_date"].dt.month
df_clean["order_dayofweek"] = df_clean["order_date"].dt.day_name()
df_clean["days_since_order"] = (pd.Timestamp("today") - df_clean["order_date"]).dt.days

Boolean Columns with Creative Encoding

The is_repeat_customer column is a case study in what happens when multiple people enter data without a standard. We have "Yes", "No", "TRUE", "False", and "1" — all trying to express the same binary concept.

# Map all variants to Python True/False
bool_map = {
    "yes": True, "no": False,
    "true": True, "false": False,
    "1": True, "0": False
}

df_clean["is_repeat_customer"] = (
    df_clean["is_repeat_customer"]
    .str.lower()   # Normalize case first
    .map(bool_map) # Then map to actual booleans
)

print(df_clean["is_repeat_customer"].dtype)     # bool
print(df_clean["is_repeat_customer"].value_counts())

Using .str.lower() before .map() means you don't need separate entries for "True", "TRUE", and "true" — they all collapse to "true" before the lookup.

Categorical Columns

If a column has a small, fixed set of values — like region — converting it to pandas' Categorical type reduces memory usage significantly and enables cleaner groupby operations:

df_clean["region"] = df_clean["region"].astype("category")
print(df_clean["region"].cat.categories)
print(df_clean.memory_usage(deep=True))

For a dataset with millions of rows and a region column with 10 distinct values, this can reduce memory by 70–80%. It also signals intent to anyone reading your code: this column has a bounded domain, not free text.

---

Step 5: Standardizing String Columns

String columns that look clean often aren't. Extra whitespace, inconsistent casing, and hidden special characters cause groupby operations to split what should be a single group into several.

# Standardize customer names
df_clean["customer_name"] = (
    df_clean["customer_name"]
    .str.strip()       # Remove leading/trailing whitespace
    .str.title()       # Consistent title case
)

# Standardize region
df_clean["region"] = (
    df_clean["region"]
    .str.strip()
    .str.title()
)

A common trap: " West" and "West" are different strings. They'll appear as separate groups in a pivot table or groupby(). You won't see the extra space. Your totals will be wrong. Always strip.

---

Step 6: Column-Level Validation

After cleaning, you need to verify that the data actually meets your expectations. Don't just trust that pd.to_numeric() worked — check it.

def validate_dataframe(df):
    """Run assertions to confirm the cleaned DataFrame meets expectations."""
    
    # No duplicate order IDs
    assert df["order_id"].duplicated().sum() == 0, "Duplicate order IDs found"
    
    # Revenue is numeric and non-negative
    assert df["revenue"].dtype in [float, "float64"], "Revenue is not numeric"
    assert (df["revenue"] >= 0).all(), "Negative revenue values found"
    
    # Dates are datetime type
    assert pd.api.types.is_datetime64_any_dtype(df["order_date"]), "order_date is not datetime"
    
    # Known regions only
    valid_regions = {"North", "South", "East", "West", "Unknown"}
    invalid = set(df["region"].dropna().unique()) - valid_regions
    assert not invalid, f"Unexpected region values: {invalid}"
    
    # Discount between 0 and 100
    assert df["discount_pct"].between(0, 100).all(), "Discount out of range [0, 100]"
    
    print("✓ All validation checks passed")

validate_dataframe(df_clean)

This validation function acts as a contract. If future data exports violate any of these rules, your pipeline fails loudly instead of producing silently wrong results. This is the kind of defensive programming that separates a reliable data pipeline from a script that works until it doesn't.

---

Building a Reusable Cleaning Pipeline

Now let's assemble everything into a single, callable function. This is how you move from ad-hoc exploration to production-ready code.

import pandas as pd
import numpy as np


def parse_currency(series):
    return (
        series
        .str.strip()
        .str.replace(r"[$,]", "", regex=True)
        .pipe(pd.to_numeric, errors="coerce")
    )


def clean_sales_data(df: pd.DataFrame) -> pd.DataFrame:
    """
    Clean a raw sales export DataFrame.
    
    Steps:
        1. Remove duplicate orders (keep first occurrence)
        2. Drop rows with missing customer names
        3. Convert revenue from currency string to float
        4. Parse order_date to datetime
        5. Standardize is_repeat_customer to boolean
        6. Fill missing discount_pct with 0
        7. Fill missing region with 'Unknown'
        8. Normalize string columns
        9. Convert region to Categorical
    
    Returns a cleaned copy of the input DataFrame.
    """
    df = df.copy()  # Never mutate the input
    
    # 1. Deduplicate
    df = df.drop_duplicates(subset=["order_id"], keep="first").reset_index(drop=True)
    
    # 2. Drop unattributable orders
    df = df.dropna(subset=["customer_name"]).reset_index(drop=True)
    
    # 3. Revenue
    df["revenue"] = parse_currency(df["revenue"])
    
    # 4. Dates
    df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
    
    # 5. Boolean column
    bool_map = {"yes": True, "no": False, "true": True, "false": False, "1": True, "0": False}
    df["is_repeat_customer"] = df["is_repeat_customer"].str.lower().map(bool_map)
    
    # 6. Fill missing discount
    df["discount_pct"] = df["discount_pct"].fillna(0)
    
    # 7. Fill missing region
    df["region"] = df["region"].fillna("Unknown")
    
    # 8. Normalize strings
    df["customer_name"] = df["customer_name"].str.strip().str.title()
    df["region"] = df["region"].str.strip().str.title()
    
    # 9. Categorize region
    df["region"] = df["region"].astype("category")
    
    return df


# Usage
raw_df = pd.DataFrame(data)  # Your raw input
clean_df = clean_sales_data(raw_df)
print(clean_df.dtypes)
print(clean_df.head())

Notice df = df.copy() at the top of the function. This is not optional. Without it, pandas may modify the original DataFrame through a reference, and you'll spend an hour debugging mysterious mutations. Always copy your input at the start of a cleaning function.

---

Hands-On Exercise

Work through this exercise using the techniques from this lesson. Don't look at the solution until you've made a genuine attempt.

Scenario: You've been handed a CSV export from a healthcare billing system. Here's the raw data:

billing_data = {
    "claim_id": [2001, 2002, 2003, 2002, 2004, 2005],
    "patient_id": ["P-1001", "P-1002", "P-1003", "P-1002", "P-1004", None],
    "service_date": ["03/01/2024", "2024-03-02", "March 3, 2024",
                     "2024-03-02", "UNKNOWN", "2024-03-05"],
    "billed_amount": ["$4,500.00", "$1,200.75", None, "$1,200.75", "$8,900.00", "$650.00"],
    "paid_amount": ["$4,000.00", "$1,100.00", "$2,300.00", "$1,100.00", None, "$620.00"],
    "claim_status": ["Approved", "approved", "APPROVED", "approved", "Denied", "Pending"],
    "is_primary_insurance": ["Yes", "TRUE", "1", "TRUE", "No", "False"],
}

billing_df = pd.DataFrame(billing_data)

Your tasks:

1. Audit the data: how many missing values are there per column? What are the data types?
2. Remove duplicate claims, keeping the first occurrence.
3. Drop rows where patient_id is null.
4. Parse billed_amount and paid_amount as numeric floats.
5. Parse service_date as datetime, coercing failures to NaT.
6. Standardize claim_status to title case (e.g., "Approved", "Denied", "Pending").
7. Convert is_primary_insurance to a proper boolean.
8. Write a validation function that asserts: no duplicate claim_id, both amount columns are non-negative, and claim_status only contains known values.
9. Wrap everything in a single clean_billing_data() function.

Stretch goal: Calculate the balance_due (billed minus paid) and flag rows where it's negative — that would indicate an overpayment.

---

Common Mistakes & Troubleshooting

Mistake 1: Modifying a Slice Without Knowing It

# This will produce a SettingWithCopyWarning and may not work
subset = df[df["region"] == "West"]
subset["revenue"] = subset["revenue"].fillna(0)  # Dangerous!

# Correct: use .copy() when you create a subset you plan to modify
subset = df[df["region"] == "West"].copy()
subset["revenue"] = subset["revenue"].fillna(0)

pandas warns you with SettingWithCopyWarning when it detects this pattern. Don't suppress the warning — fix it. Learn more about safe selection patterns in the lesson on Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks.

Mistake 2: `pd.to_numeric()` Without `errors="coerce"`

# Will raise ValueError if any value can't be converted
df["revenue"] = pd.to_numeric(df["revenue"])

# Use errors="coerce" to surface problems as NaN instead of crashing
df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")

# Then inspect what failed
print(df[df["revenue"].isnull()])

Mistake 3: Assuming `dropna()` Works on NaT

NaT values in datetime columns are caught by isnull() and dropna() — but only after the column has been converted to datetime. If you run dropna() while dates are still strings, the string "NOT A DATE" won't be dropped.

# Wrong order: dropna() before type conversion won't catch bad date strings
df = df.dropna(subset=["order_date"])  # "NOT A DATE" survives this
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")

# Correct order: convert first, then drop
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df = df.dropna(subset=["order_date"])  # NaT is now caught

Mistake 4: Not Resetting the Index After Dropping Rows

After dropna() or drop_duplicates(), the index has gaps. Row 0, 1, 3, 4 (with 2 missing) will cause unexpected behavior in index-based operations:

df = df.dropna().reset_index(drop=True)  # Always reset after dropping rows

Mistake 5: Using `==` to Check for NaN

# This always returns False, even for NaN values
df[df["revenue"] == np.nan]  # Empty result

# Correct
df[df["revenue"].isnull()]

NaN != NaN is a property of the IEEE floating-point standard — no value is equal to "not a number," not even itself. Always use .isnull() or .notna() to check for missing values.

Mistake 6: Chained Assignment with `.str` Methods

When you chain multiple string operations, each one creates a new Series. This is fine for reading, but if the column has been filtered first:

# Potentially problematic
df[df["region"].notna()]["region"] = df[df["region"].notna()]["region"].str.strip()

# Safe: use .loc for assignment
mask = df["region"].notna()
df.loc[mask, "region"] = df.loc[mask, "region"].str.strip()

---

Summary & Next Steps

Let's consolidate what you've built:

Problem	pandas Solution
Audit missing values	isnull().sum(), info()
Remove duplicates	drop_duplicates(subset=, keep=)
Drop rows with nulls	dropna(subset=)
Fill missing values	fillna(), ffill(), bfill()
Currency strings to float	str.replace() + pd.to_numeric(errors="coerce")
Mixed date formats	pd.to_datetime(errors="coerce")
Boolean columns	str.lower().map(dict)
Categorical columns	.astype("category")
Validate cleaned data	Assertions against business rules
Reusable cleaning	Single function, df.copy() at start

The cleaning pipeline you've built here is the foundation for every serious data project. Raw data flows in, clean data flows out, and every transformation is visible, auditable, and repeatable.

Where to go next:

With clean, properly-typed data, you're ready to actually analyze it. The natural next step is aggregation and groupby operations — computing revenue by region, average discount by customer type, monthly trends. You'll also want to get comfortable with reshaping: pivoting, melting, and joining multiple cleaned DataFrames together.

For those coming from Excel or SQL backgrounds, the mental model for these operations will feel familiar even when the syntax is new — you already understand what you're trying to compute, which is half the battle. The Python Basics for Excel Users: Variables, Lists, Dictionaries, and Loops lesson is also worth a review if you find yourself reaching for the right data structure when building lookup tables for your cleaning logic.

The habit to build: run a cleaning pipeline before any analysis, and validate after it runs. Data quality problems caught at the source are infinitely cheaper than insights that turn out to be wrong.