Duplicates and a Repeatable Cleaning Function

Nine rows in the messy export appear twice. Each duplicate is a real order counted again, so every total you have calculated from the export so far is too high: 23,500.67 dollars instead of 22,486.69, an overstatement of 4.5%. Duplicates come from re-run exports, copy-pasted rows and systems that retry a failed upload. They are the last problem in the file, and fixing them lets you turn this whole section into a single function.

Finding duplicates

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
dupes = messy.duplicated()
print(dupes.sum(), "duplicate rows")

both_copies = messy[messy.duplicated(keep=False)].sort_values("order_id")
print(both_copies.head(4).to_string())

duplicated() returns a mask that is True for every row identical to an earlier one. The first appearance is not flagged (keep="first"), which is what you want for removal. For inspection, keep=False flags every copy, and sorting by the id puts each pair together. Look at them before deleting anything: rows 22 and 113 are the same order, same product, same total, almost a hundred rows apart. That pattern, identical rows far apart, is typical of an export that was appended to itself.

Removing them

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
deduped = messy.drop_duplicates()
print(len(messy), "->", len(deduped))
print(deduped["order_id"].is_unique)

After dropping exact duplicates, every order_id appears once, which confirms that the duplicates were full copies and not two different versions of the same order. If is_unique had been False, you would have conflicting rows: the same id with, say, two different totals. Those need a decision, such as keeping the latest version, and drop_duplicates(subset=["order_id"], keep="last") implements it once you have made that decision.

Clean first, deduplicate second

Duplicate detection compares values exactly. "Ali Evans" and "Ali Evans " are different strings, and $22.00 and 22.00 are different totals. In this export the copies happen to be byte-for-byte identical, but in general you strip text, standardise case and convert numbers before looking for duplicates, or near-identical copies survive.

One function for the whole section

You now have five cleaning steps, written across five lessons. Scattered across a notebook, they are fragile: run a cell twice or skip one, and the result changes. Put them in one function, in the right order, with the checks at the end:

import pandas as pd

COUNTRIES = {"Egypt", "United Arab Emirates", "Saudi Arabia", "Jordan",
             "United Kingdom", "Germany", "United States", "Canada"}

def parse_money(values):
    cleaned = (values.astype("string").str.strip()
               .str.replace("$", "", regex=False).str.replace(",", "", regex=False))
    return pd.to_numeric(cleaned, errors="coerce")

def parse_dates(raw):
    iso = pd.to_datetime(raw, format="%Y-%m-%d", errors="coerce")
    dmy = pd.to_datetime(raw, format="%d/%m/%Y", errors="coerce")
    mdy = pd.to_datetime(raw, format="%m/%d/%Y", errors="coerce")
    ambiguous = dmy.notna() & mdy.notna() & (dmy != mdy)
    return iso.fillna(dmy).fillna(mdy), ambiguous

def clean_orders(path):
    """Load the messy export and return a clean, deduplicated DataFrame."""
    df = pd.read_csv(path)
    df["customer"] = df["customer"].str.strip()
    df["country"] = df["country"].str.strip().str.title()
    df["channel"] = df["channel"].str.strip().str.replace(" ", "_").fillna("unknown")
    df["quantity"] = df["quantity"].astype("Int64")
    df["total"] = parse_money(df["total"])
    df["order_date"], df["date_is_ambiguous"] = parse_dates(df["order_date"])
    df = df.drop_duplicates().reset_index(drop=True)

    assert df["order_id"].is_unique, "duplicate order ids remain"
    assert df["order_date"].notna().all(), "unparsed dates"
    assert df["total"].notna().all() and (df["total"] > 0).all(), "bad totals"
    assert set(df["country"]) <= COUNTRIES, f"unknown countries: {set(df['country']) - COUNTRIES}"
    return df

clean = clean_orders("orders_messy.csv")
print(clean.shape)
print(clean.dtypes)
print(round(clean["total"].sum(), 2), "total sales,", clean["date_is_ambiguous"].sum(), "ambiguous dates")

df["order_date"], df["date_is_ambiguous"] = parse_dates(...) unpacks the two values the helper returns into two columns at once. reset_index(drop=True) renumbers the rows 0 to 158 after the drop, so positions and labels agree again. Notice the dtypes too: quantity is Int64 and total is Float64, both nullable, because parse_money converts through pandas’ string type and to_numeric keeps the nullable family. They behave like ordinary numbers in every calculation you will do.

The function reads its own file, so it never depends on what you happened to run earlier in a notebook. Same input, same output, every time. When next month’s export arrives, clean_orders("orders_messy_2026_01.csv") runs the whole section in one line, and if the new file contains a ninth country spelling or a total in euros, an assertion stops it with a message naming the problem.

Where should the function live? While you are exploring, in the first cell of your notebook. Once a second notebook or a colleague needs it, move it to its own file, say cleaning.py next to your notebooks, and write from cleaning import clean_orders wherever you need it. One definition, imported everywhere, means a fix made once reaches every analysis that depends on it.

The export is clean: 159 orders, 22,486.69 dollars of sales, and 25 dates flagged as ambiguous (30 before the duplicates went). Section 4 moves from cleaning to answering, starting with the most used tool in pandas: groupby.