Clean a messy CSV with pandas: text, numbers, dates and duplicates

Real exports are untidy. This tutorial cleans a small one that contains the five problems you will meet most often, and ends with a count of what is usable. The data is embedded in the script so you can run it as it is.

The data and the plan

order_id,customer,country,order_date,amount,status
1001, Ana Silva ,DE,2025-03-01,"1,200.50",paid
1002,Ben Okoye,de,2025-03-02,89.9,Paid
1003,Cy Larsen,Germany,2025-03-02,,paid
1004,Di Moreau,FR,2025-03-xx,45,refunded
1005,Ana Silva,DE,2025-03-04,300,PAID
1005,Ana Silva,DE,2025-03-04,300,PAID
1006,Eli Rossi,IT,2025-03-05,$72.00,paid

Spot them: padded text (Ana Silva), inconsistent case and spelling (DE, de, Germany; paid, Paid, PAID), numbers with a thousands separator or a currency sign, a missing amount, an impossible date (2025-03-xx) and an exact duplicate row (order 1005).

The plan: read everything as text, fix each column deliberately, report what is still wrong.

Step 1: Read it as text

import io

import pandas as pd

RAW = """order_id,customer,country,order_date,amount,status
1001, Ana Silva ,DE,2025-03-01,"1,200.50",paid
1002,Ben Okoye,de,2025-03-02,89.9,Paid
1003,Cy Larsen,Germany,2025-03-02,,paid
1004,Di Moreau,FR,2025-03-xx,45,refunded
1005,Ana Silva,DE,2025-03-04,300,PAID
1005,Ana Silva,DE,2025-03-04,300,PAID
1006,Eli Rossi,IT,2025-03-05,$72.00,paid
"""

df = pd.read_csv(io.StringIO(RAW), dtype=str)
print(df.shape)
print(df.isna().sum().to_dict())

(7, 6)
{'order_id': 0, 'customer': 0, 'country': 0, 'order_date': 0, 'amount': 1, 'status': 0}

Seven rows, and one genuinely missing amount. dtype=str stops pandas guessing, so nothing is converted until you say so.

Step 2: Normalise text

Strip spaces, then make case and spelling consistent. Mapping GERMANY to the country code is an explicit decision you can see and review.

df["customer"] = df["customer"].str.strip()
df["status"] = df["status"].str.strip().str.lower()
df["country"] = df["country"].str.strip().str.upper().replace({"GERMANY": "DE"})

Step 3: Convert numbers

Remove everything that is not a digit, a dot or a minus sign, then convert. errors="coerce" turns anything still unreadable into NaN instead of stopping the script.

df["amount"] = pd.to_numeric(df["amount"].str.replace(r"[^0-9.\-]", "", regex=True), errors="coerce")

The regular expression handles both "1,200.50" and $72.00. It assumes a dot is the decimal separator. If your data uses a comma for decimals (1.200,50), the rule must change, so look at your data first.

Step 4: Convert dates, never guess

Give to_datetime the exact format. A value that does not match becomes NaT (not a time), rather than being quietly interpreted as something else.

df["order_date"] = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")

Step 5: Remove exact duplicates

Do this after normalising, because rows that differ only by spacing or case become identical once cleaned.

before = len(df)
df = df.drop_duplicates()
print(f"dropped {before - len(df)} duplicate row(s)")

Step 6: Report what is still wrong, then use what is left

problems = df[df["amount"].isna() | df["order_date"].isna()]
print(problems[["order_id", "amount", "order_date"]].to_string(index=False))

clean = df.dropna(subset=["amount", "order_date"])
print(clean.dtypes)
print(clean.groupby("country")["amount"].sum().round(2).to_dict())
print(len(clean), "of", len(df), "rows usable")

The script prints:

dropped 1 duplicate row(s)
order_id  amount order_date
    1003     NaN 2025-03-02
    1004    45.0        NaT
{'DE': 1590.4, 'IT': 72.0}
4 of 6 rows usable

Order 1003 has no amount and order 1004 has an invalid date, so neither is used. The four clean rows give 1,590.40 for Germany (1,200.50 + 89.90 + 300.00) and 72.00 for Italy. In between, print(clean.dtypes) shows amount as float64 and order_date as a datetime. In pandas 3 the text columns show as str.

Check your work

• The row counts must add up: 7 read, 1 duplicate dropped, 6 remaining, 2 unusable, 4 clean.
• Add the German amounts by hand and compare with the grouped total.
• Print df["country"].unique(): it should list only two-letter codes.

The habits that matter

• Never overwrite your raw file. Keep the original and write the cleaned result somewhere else.
• Make every decision visible. The mapping from GERMANY to DE is one line anyone can read. A silent guess is a future bug.
• Report rejects. The rows you could not use are a message to whoever produced the file.