Cleaning data

Cleaning is where analysts spend most of their time. The aim is a table where every column has the right type, every row means one thing, and the missing values are handled deliberately rather than by accident.

A typical cleaning pass

df = df.drop_duplicates()
df["city"] = df["city"].str.strip().str.title()
df["sales"] = pd.to_numeric(df["sales"], errors="coerce")
df["sales"] = df["sales"].fillna(df["sales"].median())
df["date"] = pd.to_datetime(df["date"], dayfirst=True)
df = df.rename(columns=str.lower)

Tip: our Clean & Deduplicate Data tool does many of these steps without code.

Step 1: diagnose first

print(df.isna().sum())
print(df.isna().mean().mul(100).round(1))      # % missing per column
print(df.duplicated().sum())
print(df.duplicated(subset=["bill_id"]).sum())
print(df.dtypes)

Never clean blindly. A column that is 60% missing probably should be dropped, not filled.

Step 2: duplicates

drop_duplicates() compares every column, which misses the common case of the same bill re-imported with a different timestamp. Define what makes a row unique:

df = df.sort_values("updated_at").drop_duplicates(subset=["bill_id", "product"], keep="last")

Step 3: missing values

Strategy	When to use	Code
Drop rows	Few rows, key field missing	df.dropna(subset=["bill_id"])
Drop column	Mostly empty, not needed	df.drop(columns=["remarks"])
Constant	Missing means "none"	df["discount"].fillna(0)
Median / mode	Numeric or categorical gaps	df["age"].fillna(df["age"].median())
Forward fill	Time series, value carries	df["rate"].ffill()
Group-aware fill	Fill within each city	see below

df["sales"] = df["sales"].fillna(df.groupby("city")["sales"].transform("median"))

Filling a city's missing sales with that city's median is far better than one global median. Record how many values you filled — it belongs in your notes.

Step 4: types

df["amount"] = (df["amount"].astype(str)
                            .str.replace(",", "", regex=False)
                            .str.replace("₹", "", regex=False)
                            .pipe(pd.to_numeric, errors="coerce"))
df["gstin"] = df["gstin"].astype("string").str.upper().str.strip()
df["date"] = pd.to_datetime(df["date"], dayfirst=True, errors="coerce")
print(df["date"].isna().sum(), "dates failed to parse")

Indian exports often write amounts as "1,20,000" and dates as DD/MM/YYYY, so dayfirst=True and comma stripping are routine. errors="coerce" turns bad values into NaN instead of raising, so you can count and inspect them.

Step 5: messy text and categories

df["city"] = (df["city"].str.strip().str.title()
                        .replace({"Bangalore": "Bengaluru", "Bombay": "Mumbai"}))
print(df["city"].value_counts())

Always print value_counts() afterwards — that is how you spot "Delhi" and "New Delhi" still living apart.

Step 6: impossible values

df = df[df["qty"] > 0]
df.loc[df["age"] > 110, "age"] = pd.NA
df["discount_pct"] = df["discount_pct"].clip(0, 100)

Common mistakes

• Filling missing values before understanding why they are missing.
• Using the mean on skewed money columns; the median is safer.
• Cleaning in a notebook by hand so the work cannot be repeated next month — put it in a function.
• Silently dropping rows; always print the row count before and after.