A Data Cleaning Checklist

By the end of this lesson you will have a ten-step checklist you can apply to any messy file, and you will use it to clean the practice sales data: remove duplicates, fix spellings, handle blanks, catch impossible values, flag outliers, fix types and prove the result with checks. You will also keep a cleaning log, so anyone can see exactly what you changed.

Cleaning is like washing and sorting vegetables before cooking. Nobody photographs it, but skip it and the whole dish is spoiled. Analysts often say most of their time goes into preparing data. Whatever the exact share, cleaning is where you learn the data best, so treat it as part of the analysis, not a chore before it.

The checklist

The rest of this lesson works through the steps on sales.csv, then on students.csv.

Steps 1 to 4: copy, structure, duplicates and text

import pandas as pd

raw = pd.read_csv("sales.csv")
df = raw.copy()                                   # step 1: never touch raw
print("Rows:", len(df))

# step 3: duplicates
print("Exact duplicate rows:", df.duplicated().sum())
df = df.drop_duplicates()
print("Repeated order IDs left:", df["order_id"].duplicated().sum())

# step 4: text
print("City spellings before:", df["city"].nunique())
df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
print("City spellings after:", df["city"].nunique())

df["payment_mode"] = (df["payment_mode"].str.strip().str.title()
                      .replace({"Cod": "Cash On Delivery", "Upi": "UPI"}))
print(df["payment_mode"].value_counts(dropna=False))

Rows: 1217
Exact duplicate rows: 8
Repeated order IDs left: 0
City spellings before: 20
City spellings after: 10
payment_mode
UPI                 615
Card                208
Cash On Delivery    186
Wallet              100
Net Banking          89
NaN                  11
Name: count, dtype: int64

What you should see: 8 exact duplicate rows removed, no repeated order IDs left, and the cities reduced from 20 spellings to 10 real cities. Payment modes are now consistent, apart from blanks. Notice the order of operations: strip() removes spaces at the ends, title() makes "DELHI" and "delhi" both "Delhi", and replace maps true synonyms such as Bangalore to Bengaluru.

Steps 5 to 7: blanks, impossible values and outliers

Each blank needs a decision, and the right decision depends on the column:

• Blank payment_mode: the sale still happened, so keep the row and label the blank "Unknown".
• Blank quantity: we cannot calculate revenue, so drop these rows and record how many.
• Negative quantity: impossible for a sale (it may be a return entered wrongly), so drop it and note it for the data owner.
• Very large quantities: possible (a school buying 120 books), so keep them but add a flag column.

import pandas as pd

df = pd.read_csv("sales.csv").drop_duplicates()

df["payment_mode"] = df["payment_mode"].fillna("Unknown")          # step 5: label
missing_qty = df["quantity"].isna().sum()
invalid_qty = (df["quantity"] <= 0).sum()
df = df[df["quantity"] > 0].copy()                                  # steps 5 and 6: drop
print(f"Dropped {missing_qty} blank and {invalid_qty} invalid quantities")

q1, q3 = df["quantity"].quantile([0.25, 0.75])
upper = q3 + 1.5 * (q3 - q1)
print("Unusual if quantity is above:", upper)
df["bulk_order"] = df["quantity"] > 10                               # step 7: flag with a business rule
print(df.loc[df["bulk_order"], ["order_id", "city", "product", "quantity"]])

Dropped 6 blank and 1 invalid quantities
Unusual if quantity is above: 3.5
      order_id   city           product  quantity
99    ORD10100  Delhi  Masala Chai 250g      60.0
1020  ORD11014   Pune        Exam Guide     120.0

What you should see: 6 blank and 1 invalid quantity dropped, then two bulk orders flagged. The IQR rule (anything above the third quartile plus 1.5 times the interquartile range) says any quantity above 3.5 is unusual, which would flag ordinary orders of four smartwatches. That is why we used a business rule instead: more than 10 units is a bulk order. Statistical rules are a starting point; business sense decides. See Skewness & Outliers for the theory.

Keeping the flag lets you report both views: "Revenue was ₹X, or ₹Y excluding two bulk orders."

Step 8: fix types in the students file

Text hiding inside a number column is common. In students.csv, absent students have AB instead of a mark, so pandas reads that whole column as text. pd.to_numeric(..., errors="coerce") converts what it can and turns the rest into blanks.

import pandas as pd

st = pd.read_csv("students.csv")
print(st[["maths", "hindi"]].dtypes)

for col in ["maths", "science", "english", "hindi", "social_science"]:
    st[col] = pd.to_numeric(st[col], errors="coerce")
print("Absent (blank) marks per subject:")
print(st[["maths", "science", "english", "hindi", "social_science"]].isna().sum())

print(st["school_type"].value_counts())
st["school_type"] = st["school_type"].str.strip().str.title().replace({"Govt": "Government", "Govt.": "Government"})
print(st["school_type"].value_counts())

maths    str
hindi    str
dtype: object
Absent (blank) marks per subject:
maths             2
science           6
english           0
hindi             1
social_science    0
dtype: int64
school_type
Government    237
Private       234
Private         3
government      2
private         2
PRIVATE         2
Govt.           2
Govt            1
Name: count, dtype: int64
school_type
Government    242
Private       241
Name: count, dtype: int64

What you should see: the mark columns that were text become numbers, with a few blanks for absent students, and eight spellings of school type become two. Be careful with the next decision: an absent student should not get 0 marks in an average of performance, but should count in an attendance report. The same blank means different things for different questions.

Steps 9 and 10: validate and log

Cleaning is not finished until you can prove it. Assertions are checks that stop the program with an error if they fail. A cleaning log records each step and how many rows it touched. Put both in one reusable function.

import pandas as pd

def clean_sales(path):
    log = []
    df = pd.read_csv(path)
    log.append(("loaded", len(df)))
    df = df.drop_duplicates()
    log.append(("dropped exact duplicates", len(df)))
    df = df[df["quantity"] > 0].copy()
    log.append(("dropped blank or invalid quantity", len(df)))
    df["city"] = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})
    df["payment_mode"] = (df["payment_mode"].fillna("Unknown").str.strip().str.title()
                          .replace({"Cod": "Cash On Delivery", "Upi": "UPI"}))
    df["order_date"] = pd.to_datetime(df["order_date"])
    df["quantity"] = df["quantity"].astype(int)
    df["revenue"] = df["quantity"] * df["unit_price"] * (1 - df["discount"] / 100)
    df["bulk_order"] = df["quantity"] > 10

    assert df["order_id"].is_unique, "order IDs repeat"
    assert (df["quantity"] > 0).all(), "bad quantity"
    assert df["city"].nunique() == 10, "unexpected city spelling"
    assert df["discount"].between(0, 100).all(), "discount out of range"
    return df, log

sales, log = clean_sales("sales.csv")
for step, rows in log:
    print(f"{step:36} {rows:5} rows")
print("Total revenue: Rs", round(sales["revenue"].sum()))
print("Without bulk orders: Rs", round(sales.loc[~sales["bulk_order"], "revenue"].sum()))

loaded                                1217 rows
dropped exact duplicates              1209 rows
dropped blank or invalid quantity     1202 rows
Total revenue: Rs 1907606
Without bulk orders: Rs 1845506

What you should see: a log from 1,217 rows down to 1,202 clean rows, followed by total revenue with and without bulk orders. If next month's file has a new city spelling, the assertion fails loudly instead of quietly producing a wrong report. That is exactly what you want.

Common mistakes

• Cleaning the raw file in place. Always keep the original so you can start again.
• Filling every blank with 0. A blank quantity is "unknown", not "zero items"; filling it changes averages.
• Deleting outliers because they look odd. Flag them, ask, and report with and without them.
• Cleaning without a log. If you cannot explain why 15 rows disappeared, nobody can trust the result.

Key points

• Work through ten steps: protect, structure, duplicates, text, blanks, invalid values, outliers, types, validate, log.
• Fix text with strip, title or upper, and replace for synonyms.
• Decide blanks column by column: drop, fill or label, and record the reason.
• Flag unusual values with a business rule; remove only impossible ones.
• Put cleaning in a function with assertions and a log so it can be rerun and trusted.

import pandas as pd

def clean_employees(path):
    df = pd.read_csv(path).drop_duplicates()
    df["department"] = df["department"].str.strip().str.title().replace({"Hr": "HR"})
    print("Blank salaries:", df["salary"].isna().sum())
    expected = {"Engineering", "Sales", "Support", "Data Science", "Finance", "HR"}
    assert set(df["department"]) <= expected, "unexpected department name"
    return df

emp = clean_employees("employees.csv")
print(emp["department"].value_counts())