Cleaning Excel Data With Pandas: A Production Workflow

Raw Excel exports rarely arrive analysis-ready: inconsistent headers, hidden whitespace, placeholder strings, duplicate rows, and numbers stored as text all break downstream reports. Cleaning that data in pandas gives you a scriptable, version-controlled, testable alternative to manual spreadsheet edits. This guide builds a linear cleaning pipeline — ingest, normalize, denoise, deduplicate, validate, export — one stage at a time, extending the Advanced Data Transformation and Cleaning overview.

Every block runs in order against a sample workbook created in the first step.

Install dependencies

pip install pandas openpyxl numpy

Create a sample workbook

This workbook deliberately contains the problems you meet in the wild: messy header casing, whitespace, a placeholder "N/A", a fully blank row, a duplicate order, a negative amount, and amounts stored as text:

import pandas as pd

raw = pd.DataFrame({
    " Order ID ": ["1001", "1002", "1003", "1003", "1004", None],
    "Transaction Date": ["2024-01-05", "2024-01-06", "2024-01-07",
                         "2024-01-07", "2019-12-31", None],
    "Status": ["pending", "Complete", "PENDING", "PENDING", "complete", None],
    "Amount": ["$1,200.50", "980.00", "450.00", "450.00", "-75.00", None],
})
raw.to_excel("report_input.xlsx", sheet_name="Data", index=False)
print(f"Wrote {len(raw)} rows")

Step 1: Load everything as text

Load with dtype=str so pandas does not guess a type per cell — guessing is what produces object columns mixing strings, numbers, and dates. We coerce deliberately in Step 2.

import logging
logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

def load_excel_data(file_path: str, sheet_name=0) -> pd.DataFrame:
    df = pd.read_excel(
        file_path,
        sheet_name=sheet_name,
        header=0,
        engine="openpyxl",
        dtype=str,        # prevent premature, per-cell type coercion
    )
    logging.info(f"Loaded {len(df)} rows from {file_path}")
    return df

df = load_excel_data("report_input.xlsx", sheet_name="Data")
print(df.columns.tolist())

read_excel() has no skip_blank_lines argument (that belongs to read_csv()); remove blank rows with dropna(how="all") in Step 3 instead.

Step 2: Standardize headers and types

Normalize column names to predictable snake_case, then coerce each column to its intended type. Stripping currency symbols and separators before pd.to_numeric keeps the cast from collapsing to all-NaN.

def standardize_schema(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    df.columns = (
        df.columns.str.strip()
        .str.lower()
        .str.replace(r"\s+", "_", regex=True)
    )

    # amount: strip "$" and "," before numeric cast
    if "amount" in df.columns:
        df["amount"] = (df["amount"].str.replace(r"[$,]", "", regex=True)
                        .pipe(pd.to_numeric, errors="coerce"))

    if "transaction_date" in df.columns:
        df["transaction_date"] = pd.to_datetime(df["transaction_date"], errors="coerce")

    if "status" in df.columns:
        df["status"] = df["status"].astype("category")

    return df

df = standardize_schema(df)
print(df.dtypes)

Step 3: Drop structural noise

Excel exports carry empty rows from copy-paste, template padding, and footer notes. Drop fully blank rows, then drop rows missing a critical identifier, and strip residual whitespace from text columns. Dropping a row is the blunt option; when a gap should be filled rather than deleted, reach for handling missing data in Excel reports instead.

def purge_noise(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    initial = len(df)

    df = df.dropna(how="all")                       # fully blank rows
    df = df.dropna(subset=["order_id", "transaction_date"])  # missing keys

    text_cols = df.select_dtypes(include=["object", "string"]).columns
    for col in text_cols:
        df[col] = df[col].str.strip()

    logging.info(f"Purged {initial - len(df)} noisy/empty rows")
    return df

df = purge_noise(df)
print(f"{len(df)} rows remain")

Step 4: Deduplicate and normalize values

Duplicates come from repeated exports and overlapping date ranges. Sort first so the survivor is deterministic, then drop on the business key. For a focused treatment of single-column deduplication, see Pandas Drop Duplicates from Excel Column.

def deduplicate_records(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()
    # Newest first, so keep="first" retains the most recent record per key
    df = df.sort_values("transaction_date", ascending=False)
    df = df.drop_duplicates(subset=["order_id"], keep="first")

    # Normalize the status vocabulary (string ops on a category need .astype(str))
    df["status"] = (df["status"].astype(str).str.upper()
                    .replace({"PENDING": "OPEN", "COMPLETE": "CLOSED"}))
    return df

df = deduplicate_records(df)
print(df[["order_id", "status"]])

Step 5: Validate and derive

Run business-rule checks and compute the columns the report needs. Logging the row counts you drop gives you an audit trail when a scheduled run produces unexpected totals.

def validate_and_prepare(df: pd.DataFrame) -> pd.DataFrame:
    df = df.copy()

    neg = df["amount"] < 0
    if neg.any():
        logging.warning(f"Dropping {int(neg.sum())} rows with negative amounts")
        df = df[~neg]

    # Keep only in-scope dates
    df = df[df["transaction_date"] >= pd.Timestamp("2020-01-01")]

    df["fiscal_quarter"] = df["transaction_date"].dt.quarter
    df["fiscal_year"] = df["transaction_date"].dt.year
    return df

df = validate_and_prepare(df)
print(df)

Step 6: Export the cleaned data

Write the result for the next stage. CSV is universally interoperable; for a styled workbook, use to_excel with the openpyxl engine.

def export_clean_data(df: pd.DataFrame, output_path: str):
    df.to_csv(output_path, index=False)
    logging.info(f"Exported {len(df)} rows to {output_path}")

export_clean_data(df, "report_clean.csv")
df.to_excel("report_clean.xlsx", index=False, engine="openpyxl")
print("Export complete")

For large static datasets you read repeatedly, Parquet (via pip install pyarrow) compresses well and preserves dtypes — swap to_csv for df.to_parquet(path, index=False).

Common errors and fixes

ValueError: could not convert string to float — currency symbols, thousands separators, or trailing spaces in a numeric column. Strip them first:

s = pd.Series(["$1,200.50", "980.00"])
cleaned = pd.to_numeric(s.str.replace(r"[$,]", "", regex=True), errors="coerce")
print(cleaned.tolist())

Footer notes or merged cells inflate the column count — restrict parsing to the real data region with usecols and skipfooter:

subset = pd.read_excel("report_input.xlsx", usecols="A:D", skipfooter=0,
                       engine="openpyxl")
print(subset.shape)

MemoryError on a large workbook — declare dtypes up front and drop columns you do not need; convert high-cardinality strings to category to shrink memory.

Ambiguous dates (MM/DD vs DD/MM) — coerce explicitly and set dayfirst to match the source:

dates = pd.to_datetime(pd.Series(["05/01/2024", "31/12/2019"]),
                       dayfirst=True, errors="coerce")
print(dates.tolist())

A cleaning function that reports what it did

Cleaning silently is how a pipeline loses the ability to explain itself. Returning a record of every change alongside the cleaned frame costs almost nothing and answers the question that always follows a surprising number:

import pandas as pd

def clean(df: pd.DataFrame):
    log = []
    out = df.copy()

    before = len(out)
    out = out.dropna(how="all")
    if len(out) != before:
        log.append(f"dropped {before - len(out)} fully empty row(s)")

    for column in out.select_dtypes(include="object"):
        stripped = out[column].astype("string").str.strip()
        changed = int((stripped != out[column]).sum())
        if changed:
            log.append(f"{column}: trimmed whitespace on {changed} value(s)")
        out[column] = stripped

    before = len(out)
    out = out.drop_duplicates()
    if len(out) != before:
        log.append(f"dropped {before - len(out)} exact duplicate row(s)")

    return out, log

cleaned, changes = clean(pd.read_excel("submitted.xlsx", dtype=object))
print("\n".join(changes) or "no changes")

Each entry is one line in the run log, and together they explain the gap between the row count in the source and the row count in the report. Without them, "the file had 4,812 rows and the report shows 4,790" is a question nobody can answer three weeks later.

Normalise the key before you trust a comparison

The order in the diagram exists because of one specific failure: "North " and "North" are different strings, so a duplicate check run before trimming finds nothing. The same applies to joins, groupings and lookups — every operation that compares values silently depends on normalisation having happened first:

import pandas as pd

def comparison_key(series):
    return (
        series.astype("string")
        .str.replace(r"\s+", " ", regex=True)
        .str.strip()
        .str.casefold()
    )

frame = pd.DataFrame({"Region": ["North", "north ", " NORTH", "South"]})
frame["_key"] = comparison_key(frame["Region"])
print(frame.groupby("_key").size().to_dict())        # {'north': 3, 'south': 1}

Keeping the original alongside the key is deliberate. The report should display North as the business writes it, while every comparison uses the folded version — mangling the stored value to make comparisons easier throws away information a reader expects to see.

Deciding what cleaning may change

Not every anomaly is yours to fix. A useful line: correct anything mechanical and unambiguous, report anything that requires a judgement.

Silently "correcting" the right-hand column is how a pipeline acquires a second, undocumented layer of business logic — and the first symptom is a report whose numbers cannot be reconciled with the source it came from. Pushing those cases back to validation keeps the cleaning step honest and the data owner informed.

Cleaning numbers that arrived as text

A single stray value turns a numeric column into text, and every later sum silently produces the wrong answer or raises. Stripping the decorations before converting handles the common cases:

import pandas as pd

def to_number(series, decimal_comma=False):
    text = series.astype(str).str.strip().str.replace(r"[£$€\s]", "", regex=True)
    text = text.str.replace(r"^\((.*)\)$", r"-\1", regex=True)       # (1,234) means negative
    if decimal_comma:
        text = text.str.replace(".", "", regex=False).str.replace(",", ".", regex=False)
    else:
        text = text.str.replace(",", "", regex=False)
    return pd.to_numeric(
        text.replace({"": None, "nan": None, "n/a": None, "-": None}), errors="coerce"
    )

messy = pd.Series(["£1,234.50", "(500)", "99", "n/a", ""])
print(to_number(messy).tolist())        # [1234.5, -500.0, 99.0, nan, nan]

Accounting parentheses are the detail most conversions miss: (500) means negative five hundred in every finance export, and treated as text it becomes NaN, quietly removing a debit from the totals. The decimal_comma switch matters just as much — 49,50 is either forty-nine and a half or four thousand nine hundred and fifty depending on the source's locale, and no heuristic can decide that safely. Ask the source, record the answer per feed, and pass it in.

Comparing before and after tells you what failed rather than leaving it to be discovered:

converted = to_number(messy)
broke = converted.isna() & messy.astype(str).str.strip().ne("")
print("unconvertible:", messy[broke].tolist())

Splitting and combining columns

Real exports pack several facts into one column — a full name, an address line, a code with a suffix — and pandas handles the split declaratively:

import pandas as pd

people = pd.DataFrame({"Name": ["Ada Lovelace", "Grace Hopper", "Prince"]})
parts = people["Name"].str.strip().str.split(" ", n=1, expand=True)
people["First"] = parts[0]
people["Last"] = parts[1].fillna("")           # single-word names must not vanish
print(people)

codes = pd.DataFrame({"SKU": ["A-100-EU", "B-200-US"]})
codes[["Family", "Number", "Market"]] = codes["SKU"].str.split("-", expand=True)
print(codes)

n=1 on the first split is what keeps van der Berg intact as a surname, and fillna("") covers the mononym that would otherwise become NaN and break a later concatenation. The second example shows the fragile version: str.split("-", expand=True) assumes exactly three parts, and a code with four raises a shape error. Where the format is not guaranteed, str.extract with a named regular expression fails more gracefully, returning NaN for the parts it could not find.

Cleaning is not a one-off script

The steps that make sense for this month's file are the steps you will run next month, so they belong in a function with tests rather than in a notebook cell. Two habits make that practical: keep every rule idempotent, so running the cleaner twice changes nothing the second time, and keep the input untouched, so the original file is always available to re-derive from.

def test_cleaning_is_idempotent():
    once, _ = clean(raw)
    twice, log = clean(once)
    assert once.equals(twice)
    assert not log                      # nothing left to change

An idempotent cleaner is safe to re-run after a partial failure, safe to apply to a file that has already been through it, and much easier to reason about — which is what turns cleaning from a recurring chore into a step nobody has to think about.

Dates are the hardest column to clean

A date column that arrives from a spreadsheet can hold three incompatible representations at once: real datetimes, text in several formats, and Excel serial numbers. Handling all three explicitly is the only reliable approach:

import pandas as pd

EXCEL_EPOCH = pd.Timestamp("1899-12-30")

def to_datetime_mixed(series, dayfirst=True):
    numeric = pd.to_numeric(series, errors="coerce")
    from_serial = EXCEL_EPOCH + pd.to_timedelta(numeric, unit="D")
    from_text = pd.to_datetime(series.where(numeric.isna()), errors="coerce",
                               format="mixed", dayfirst=dayfirst)
    return from_serial.fillna(from_text)

raw = pd.Series(["2026-01-14", "15/01/2026", 45678, "2026-02-30", None])
parsed = to_datetime_mixed(raw)
print(parsed.tolist())

2026-02-30 stays NaT because no calendar has that day, which is exactly right — it is bad data rather than a parsing failure, and it should be reported rather than guessed at. The dayfirst argument carries the same warning as a decimal comma: 03/04/2026 is March or April depending on who typed it, and choosing wrongly produces dates that are plausible, wrong and almost impossible to notice.

Once parsed, deriving the period columns a report needs is a one-liner each, and doing it centrally stops five different formats appearing across a workbook:

frame = pd.DataFrame({"Order_Date": parsed.dropna()})
frame["Month"] = frame["Order_Date"].dt.to_period("M").astype(str)     # 2026-01
frame["Quarter"] = frame["Order_Date"].dt.to_period("Q").astype(str)   # 2026Q1
frame["Weekday"] = frame["Order_Date"].dt.day_name()
print(frame)

Know when the source should be fixed instead

Every cleaning rule is a small, permanent tax. A pipeline that trims whitespace because one system exports it, uppercases because another does not, and repairs three date formats is doing work that would be better done once at the source — and each rule is a place where a future change can go unnoticed.

The judgement is about ownership. Where the source is a system you or a colleague controls, raising the export's quality removes the rule permanently and helps everyone else who consumes it. Where the source is a spreadsheet typed by a person outside your team, cleaning is the right answer and validation rules in the workbook they receive back are what stop the same corrections being needed next month.

Keep the raw file

However thorough the cleaning, keep the untouched source. Every cleaned figure is derived, and the ability to re-derive it — after a rule changes, after a bug is found, after someone questions a number — depends entirely on the original still existing. Archiving the input alongside the output, with its size and modification time recorded in the run log, costs a few kilobytes a month and is the difference between answering a question in minutes and rebuilding a month of history from memory.

Key takeaways

• Keep the same order every run. Load as text, normalize headers and types, drop structural noise, deduplicate on the business key, validate, then export — the sequence is a correctness property, not a preference.
• Load as text, coerce on purpose. dtype=str gives one predictable starting point; strip currency symbols and separators before pd.to_numeric so a cast never collapses a whole column to NaN.
• One function per stage. Isolated stages let you log row counts at every boundary, test each step alone, and add rules without disturbing the rest of the pipeline.
• Deduplicate deterministically. Sort before drop_duplicates so the survivor is chosen on purpose, and cast a category column back to str before running .str operations on it.
• Fail loudly on bad data. A scheduled run that logs and drops negative amounts or out-of-scope dates is safer than one that quietly ships a broken total.

Frequently asked questions

Does read_excel() have a skip_blank_lines argument? No — that parameter belongs to read_csv(). With read_excel(), remove fully blank rows after loading using df.dropna(how="all").

Why apply string operations on the status column with .astype(str) first? Once a column is converted to the category dtype, vectorized .str methods operate on the categories rather than giving the behaviour you expect. Cast back with .astype(str) before .str.upper() and .replace(...), as the deduplicate step does.

Why strip $ and , before pd.to_numeric?to_numeric with errors="coerce" turns any unparseable string into NaN, so a value like "$1,200.50" collapses to NaN and the whole column can end up empty. Remove currency symbols and thousands separators first, then cast.

How do I exclude footer notes or merged cells that inflate the column count? Restrict parsing to the real data region with usecols (for example usecols="A:D") and skipfooter so trailing notes don't become phantom rows or columns.

Should I export to CSV, Excel, or something else? CSV is the most interoperable for handoff to the next stage. Use to_excel with the openpyxl engine when you need a styled workbook, or to_parquet (via pyarrow) for large static datasets you re-read often, since it compresses well and preserves dtypes.

Related

Up one level: Advanced Data Transformation and Cleaning — the full ingest-to-export pipeline this workflow sits inside.

Go deeper on a single cleaning step:

• Pandas Drop Duplicates from Excel Column — remove repeated entries precisely, with keep and subset options.
• Remove Blank Rows from Excel with Pandas — strip empty and whitespace-only rows.

Related workflows in this section:

• Handling Missing Data in Excel Reports — fill gaps instead of dropping rows.
• Merging and Joining Excel DataFrames — combine cleaned exports without multiplying rows.
• Applying Conditional Formatting with openpyxl — style the cleaned output for delivery.