Cleaning messy data

Why cleaning comes first

If "Bengaluru", "Bangalore" and " bengaluru " are three different cities in your data, your sales-by-city chart is wrong. If a price column is text, you cannot add it up. A model trained on dirty data learns the dirt. People often say "garbage in, garbage out", and they are right.

The common problems and their fixes

Missing values: drop or fill?

NaN ("not a number") is how pandas marks a missing value. You can drop rows with missing values, but if many rows have gaps you lose a lot of data. You can fill (impute) them, for example with the median of the column. The median is often safer than the mean because a few extreme values do not move it. Whatever you do, write it down, and consider adding a column that says the value was filled.

df["salary"].isna().sum()                 # how many are missing
df["salary"] = df["salary"].fillna(df["salary"].median())
df = df.dropna(subset=["customer_id"])     # rows without an id are useless

Turning text into numbers safely

df["price"] = pd.to_numeric(
    df["price"].str.replace(",", "").str.replace("₹", ""),
    errors="coerce",
)

Dates

Indian data often writes dates as day/month/year, while some tools assume month/day/year. "05/01/2025" is 5 January in India but May 1 in the US. Always pass the format explicitly, for example format="%d/%m/%Y", and check a few rows afterwards.

A safe cleaning routine

1. Keep the raw file unchanged. Clean a copy in code.
2. Look first: info(), isna().sum(), value_counts() on text columns, describe() on numbers.
3. Fix one problem at a time and re-check the counts after each step.
4. Record every decision (why you dropped or filled) in comments or a notes cell.
5. Put the steps in a function so next month's file gets exactly the same treatment.

Common mistakes

• Filling missing values with 0 when 0 is a real value (a salary of 0 is not "unknown"). Fix: use the median, a flag, or leave it missing.
• Dropping all rows with any missing value and losing half the data. Fix: drop only on the columns that truly matter.
• Removing "outliers" that are real (a genuine ₹5 lakh order). Fix: investigate before deleting.
• Cleaning by hand in Excel, so nobody can repeat it. Fix: clean in code.