The data cleaning checklist I run before any analysis

Cleaning is where the time goes. On every dataset I have worked on, from a sales register at Brijwasi Sweets to a college survey export, getting the data trustworthy has taken longer than the modelling or the charts that followed. That is not a failure of planning; it is the shape of the work. What helps is running the same sequence every time, in the same order, because the order matters: profiling before you touch anything, and fixing types before any other check, otherwise every later check reports about the wrong thing.

Here is the sequence, with the pandas code for each step.

1. Profile before you change a single cell

You cannot clean what you have not measured, and the profile is also your baseline for proving later that you did not lose rows.

import pandas as pd

df = pd.read_csv("sales_raw.csv", dtype=str)   # everything as text on the first read
print(df.shape)
print(df.dtypes)
print((df.isna().mean() * 100).round(1).sort_values(ascending=False).head(15))
print("exact duplicate rows:", df.duplicated().sum())
print(df.nunique().sort_values())

Reading as text on the first pass is deliberate. Left to itself, read_csv infers a type per column from the first chunk of rows and then you are debugging its guesses instead of looking at your data. nunique() sorted ascending is the most useful line here: a column with one distinct value is dead weight, and a column whose unique count equals the row count is an identifier, not a feature.

For a first look without writing anything, drop the file into the CSV and Excel analyzer. It reports rows, columns, missing cells with a percentage, duplicate rows, a detected type and unique count per column, quartiles and an IQR outlier count for every numeric column, and a Pearson correlation matrix across the first fifteen numeric columns. It reads only the first sheet of a workbook, and it treats a column as numeric when at least 90 percent of its filled values are numbers, so one stray text entry does not spoil the statistics. It describes the data and does not change it, which is exactly what you want at this stage.

2. Fix types next, not later

Every downstream check depends on this. A numeric column stored as text makes describe() useless, an outlier check impossible and a range filter silently empty.

df["amount"] = pd.to_numeric(
    df["amount"].str.replace(",", "", regex=False).str.replace("₹", "", regex=False),
    errors="coerce")
df["date"] = pd.to_datetime(df["date"], dayfirst=True, errors="coerce")
df["state"] = df["state"].astype("category")
df["gstin"] = df["gstin"].astype("string")     # identifiers stay text, always

print("amount failed to parse:", df["amount"].isna().sum())
print("date failed to parse:", df["date"].isna().sum())

errors="coerce" converts failures to NaN or NaT instead of raising, and then you count them. That count is information: three failures out of 40,000 is a typo to fix by hand, 4,000 failures means a second date format in the file that you have not noticed yet. Inspect them with df.loc[df["date"].isna(), "date_raw"].head(20) rather than guessing.

Keep GSTIN, PIN, invoice numbers and phone numbers as string. They look like numbers and behave like labels, and nobody has ever needed the mean of a PIN code. The pandas track goes through the dtype system properly if the difference between object, string and category is still fuzzy.

3. Standardise text before you group by it

groupby and drop_duplicates compare strings exactly, so invisible differences split one category into four.

text_cols = ["customer", "city", "state", "salesman"]
for c in text_cols:
    df[c] = (df[c].str.replace(" ", " ", regex=False)   # non-breaking space
                  .str.replace(r"\s+", " ", regex=True)      # collapse runs of spaces
                  .str.strip()
                  .str.title())

df["city"] = df["city"].replace({
    "Bangalore": "Bengaluru", "Blr": "Bengaluru",
    "Bombay": "Mumbai", "Cal": "Kolkata",
})
print(df["city"].value_counts().tail(30))   # rare values are where the typos live

The non-breaking space is worth a line of its own. It arrives with anything copied out of a web page or a PDF, it looks identical to a space, and str.strip() on its own does not remove it in older pandas versions. Zero-width characters (​) come in the same way.

Read value_counts().tail(30) rather than the head. The frequent values are usually fine; the tail is where Bengaluru with a trailing space and BENGALURU and Bengalore are sitting. Build the replacement map by hand from that tail. Automatic fuzzy matching on names is a rabbit hole, and it will merge Sharma Traders with Sharma Trading Co at some point, which is a worse error than leaving them separate.

For a straightforward trim, case and duplicate pass without any code, Clean & Deduplicate Data does trim, whitespace collapse, empty row and column removal, text case and duplicate removal on a chosen set of columns, and reports counts of what it changed.

4. Handle missing values by mechanism, not by reflex

df.fillna(0) across a whole frame is the single most common way to ruin a dataset. A blank means something different in every column, and the right action depends on why it is blank.

In practice:

df["discount"] = df["discount"].fillna(0)
df["income_missing"] = df["monthly_income"].isna()      # missingness as a feature
df["state"] = df["state"].fillna(df["pincode"].map(pin_to_state))

The distinction statisticians draw between missing completely at random, missing at random and missing not at random is not academic here. If high earners skip the income question, the mean of the answers you did get is biased no matter which imputation you use, and no amount of cleaning fixes it. The honest move is to say so in your write-up. The statistics notes cover why the mechanism changes what you are allowed to conclude.

5. Look for impossible values, not just outliers

An outlier may be real. An impossible value is a defect, and you find those with rules rather than statistics.

checks = {
    "negative qty": df["qty"] < 0,
    "zero amount": df["amount"] == 0,
    "future date": df["date"] > pd.Timestamp.today(),
    "date before go-live": df["date"] < pd.Timestamp("2019-07-01"),
    "bad gst rate": ~df["gst_rate"].isin([0, 0.25, 3, 5, 12, 18, 28]),
    "bad pincode": ~df["pincode"].str.fullmatch(r"[1-9][0-9]{5}").fillna(False),
}
report = pd.DataFrame({k: [v.sum()] for k, v in checks.items()}).T
report.columns = ["rows"]
print(report)

Write the rules from domain knowledge, not from the data. An Indian PIN code is six digits and never starts with zero. GST rates come from a fixed set. A sale cannot predate the system going live. Each rule that fires is either a data defect or a gap in your understanding of the business, and both are worth knowing before you build anything.

Report first, fix second, and never clip silently. A negative quantity is often a credit note that belongs in the data with its sign intact, and clipping it to zero destroys the return.

6. Dedupe on business keys

key = ["invoice_no", "item_code"]
dupes = df[df.duplicated(subset=key, keep=False)].sort_values(key)
dupes.to_csv("duplicates_for_review.csv", index=False)    # look before you drop

df = df.sort_values("updated_at").drop_duplicates(subset=key, keep="last")

Full-row drop_duplicates is almost always too strict, because real duplicates differ in one irrelevant field such as an export timestamp. Sort by a recency column before dropping so that keep="last" keeps the version you meant to keep, and write the duplicate rows to a file first. Twice now I have found that what looked like duplicate invoices were two genuine line items with the same item code and different batch numbers, which meant the key was wrong, not the data.

7. Write down what you changed

The cleaning must be a script, not a session of interactive fixes. Keep the raw file untouched and read-only, write the cleaned output to a new path, and keep a short log next to it: rows in, rows out, rows dropped and why, columns renamed, values imputed and with what, rules that fired and how many rows each caught. Ten lines of text at the top of the script is enough. When someone asks in November why the April total moved, that log is the answer, and without it you will re-derive it from memory and get it wrong.

The honest part

Most of the project time goes here, and the fraction does not shrink much with experience. What experience changes is the order: profiling before fixing, types before checks, and asking what a blank means before filling it. Cleaning also cannot create information that was never recorded. If 30 percent of the income column is missing not at random, the correct output is a narrower claim, not a cleverer imputation. The best returns usually come from fixing the collection process so next quarter's extract needs less of this, which is the argument to make to whoever owns the form. If you want the surrounding theory rather than the mechanics, the data science track is the place to start.

Frequently asked questions

Should I use an automated profiling library instead? They are useful for a first pass and they will not tell you that a blank weight_kg means unmeasured. Use one, or the browser analyzer, to see the shape of the data quickly, then do the mechanism reasoning yourself.

Is it safe to drop rows with any missing value? Rarely. df.dropna() on a wide frame can remove most of your rows, and it biases the result whenever missingness is related to what you are measuring. Drop rows only when the missing field is essential to the specific analysis, and report how many you dropped.

How do I know when the data is clean enough? When every rule in step 5 reports zero or a number you can explain, the row count reconciles with the source, and a column you know the answer for matches the answer. Clean enough is defined by the question you are about to ask, not in the abstract.

Do I need to clean before loading into a database or a BI tool? Fix types and identifiers before loading, because those are painful to change afterwards. Category standardisation and imputation can live in a transformation layer, where they are versioned and visible instead of buried in one person's notebook.