A CSV lands in your folder and the numbers look fine until you sort by date and half the column collapses into text. Column headers carry stray spaces, a price field mixes "$1,200" with "1200.00", and three people have spelled the same country four ways. This is normal. It is also the part of the job that decides whether anything you report later holds up.
Python and pandas give you a dependable way through it. The workflow below is worth keeping: inspect before you touch anything, fix problems in a deliberate order, and finish with a script you can rerun on next month's export. Every snippet works on a DataFrame called df, so swap in your own column names as you go.
Inspect First, Edit Later
Resist the urge to start cleaning row by row. Load the file with type inference switched off, then read a summary instead of scrolling.
df = pd.read_csv("raw/orders.csv", dtype=str, keep_default_na=False)
print(df.shape)
print(df.info())
print(df.isna().sum())
print(df.nunique())
Reading everything as text prevents pandas from quietly turning a ZIP code like 01234 into 1234, and it keeps a stray "N/A" visible as a string rather than burying it in a column of missing values. Two more commands are worth running before you decide anything: df.head(10) for the top of the file and df.sample(5, random_state=0) for a random slice, because the first rows are often the tidiest.
The nunique() output tells you a lot. A column with one unique value carries no information. A column with almost as many unique values as there are rows is usually an identifier, not a category, and should stay as text.
Fix Column Names and Types
Header names with spaces, capitals and brackets will make every later line of code painful. Normalise them once, at the top of the script.
df.columns = (df.columns.str.strip().str.lower().str.replace(r"[^0-9a-z]+", "_", regex=True))
Now convert the columns that should be numbers or dates, and do it with errors="coerce" so bad entries become NaN instead of crashing the run. A failed conversion is information: df["price"].isna().sum() after a pd.to_numeric(df["price"], errors="coerce") call tells you exactly how many values were unreadable. Fixing those is a decision about the data, not a syntax problem.
Decide What Each Missing Value Means
A blank cell can mean "nobody collected it", "it does not apply here", "the answer is zero", or "someone typed a dash". Those are four different situations and they deserve four different treatments. Work through this list before you run a single fill:
- Drop the row only when the missing field is essential and the gap is rare. Losing three rows out of fifty thousand is a rounding error; losing three hundred is a finding.
- Impute a summary value such as the median for numeric columns, and write a comment saying so. Be careful with zeros — for income, weight or counts, zero is a real value and filling blanks with it drags your averages down.
- Add a flag column like price_was_missing and fill the value separately. You keep the row and you keep the ability to check later whether the gap changed your results.
- Give text a label such as "unknown" so it becomes its own category rather than an invisible hole. Then check whether those rows behave differently from the rest.
Deduplicate and Normalise Text
Duplicate records hide behind whitespace and capitalisation. " [email protected] " and "[email protected]" are one person. Clean the key first, then look for repeats:
df["email"] = df["email"].str.strip().str.lower()
dupes = df[df.duplicated(subset=["email"], keep=False)]
df = df.drop_duplicates(subset=["order_id"], keep="last")
Check the duplicates before you delete them. Sometimes the later row is the corrected one, sometimes the earlier row is the original order, and sometimes you are looking at two genuine purchases. For categories, run df["country"].value_counts(), then map the variants onto one spelling with a dictionary and run value_counts() again to confirm the merge worked.
Numbers and Dates Arrive as Strings
Anything a human typed into a spreadsheet is a string until you say otherwise. Currency symbols, thousands separators and units all need stripping before conversion.
df["price"] = pd.to_numeric(df["price"].str.replace(r"[$,]", "", regex=True), errors="coerce")
Dates are the classic trap. "03/04/2024" is either 3 April or 4 March depending on who exported the file, so pass the format explicitly whenever the source is consistent, and check the NaT count afterwards to catch the rows that did not match. If the formats are genuinely mixed, convert with errors="coerce" and inspect the failures by hand rather than letting pandas guess. Confirm that unit columns agree too: a mix of kilograms and pounds will produce plausible-looking nonsense.
Wrap It in a Reusable Function
Once the steps work in a notebook, move them into one function that takes a raw DataFrame and returns a clean one. Keep every transformation in the same order and add a few assertions at the end so a silent failure cannot slip through.
def clean_orders(df):
df = df.copy()
# rename, convert, fill, dedupe...
return df
Then run the checks: is order_id unique, is price free of nulls, does the row count fall within a range you expect? An assertion that fails loudly is far cheaper than a dashboard that quietly reports the wrong total for a month.
Keep the Raw File Untouched
Treat the original export as read-only. Write cleaned output to a new file with a date in the name, log the row count before and after each step, and note any judgement call in a comment right above the code that makes it. When someone asks six weeks later why February revenue changed, the answer is in the script rather than in your memory.
If a cleaning step is not in the script, it did not happen.
Start with a small subset, get the pipeline right, then run it against the full file. The next messy CSV will take an afternoon instead of a week.
Photo: Pexels / Pixabay
