Data cleaning with pandas before an AI project

Data cleaning with pandas comes down to five jobs before any model sees the data: load files with the column types you choose, find missing values and decide what each one means, remove duplicates, convert numbers and dates, and join tables without losing or multiplying rows. Then check the result and split off test data before you compute anything from it. The steps below use pandas 3.1, the current release in November 2026.

How do you load CSV and Excel files without losing information?

pd.read_csv and pd.read_excel guess each column's type. That guess goes wrong on identifiers. A customer code like 00417, a PIN code, a phone number or a GSTIN read as a number loses the zeros at the start or turns into a float. Read identifiers as text with dtype={"customer_id": str}, or read the whole file as text with dtype=str and convert the columns you need.

For Excel, sheet_name=None returns every sheet as a dictionary of DataFrames, which is useful when a monthly report has one sheet per branch. Pass engine="openpyxl" for .xlsx files: pandas 3.1 warns that the default engine will change in a future version.

Two pandas 3.0 changes matter if you are updating an old notebook. Text columns now get a dedicated str dtype instead of object, so code that checks for object needs updating. And with Copy-on-Write, chained assignment such as df["amount"][0] = 100 no longer changes df; write df.loc[0, "amount"] = 100 instead.

Missing values: find them, then decide

read_csv already reads empty cells and common markers such as "N/A" and "NULL" as missing. Add your own with na_values=["not known", "TBC"]. Then count them per column with df.isna().sum().

There is no single right fix, so decide column by column:

• Drop the row with dropna(subset=[...]) when the value you want to predict is missing. Never fill in the target.
• Fill with fillna only when the blank has a real meaning, such as no discount meaning 0.
• Keep a flag: a column saying the value was missing often carries information of its own, for example customers who never gave a phone number.

If you fill with an average or median, compute it on the training data only. That rule comes up again in the checks below.

Duplicates and inconsistent text

Exports often repeat rows: the same order sent twice, or a customer entered once per branch. Normalise text first (.str.strip() for stray spaces, .str.lower() for emails), then look at df.duplicated(subset="order_id").sum() before you remove anything. drop_duplicates(subset="order_id", keep="last") keeps the most recent copy; keep=False drops every copy, which is right when you cannot tell which one is correct.

Numbers and dates

Amounts often arrive as text: "1,200", "₹850", "n/a". Remove the separators and symbols, then convert with pd.to_numeric(..., errors="coerce"), which turns anything it cannot read into a missing value you can count. read_csv(thousands=",") handles plain separators at load time.

Dates need an explicit format. Indian and UK exports usually write the day first, and the pandas documentation warns that dayfirst=True "is not strict". Use pd.to_datetime with format="%d/%m/%Y" and errors="coerce", so an impossible date like 31/11/2026 becomes NaT instead of a silent error.

Joining tables without surprises

A join is where row counts quietly change. If the customer file lists the same ID twice, every order for that customer is doubled. merge can check this for you: validate="many_to_one" raises an error unless the keys are unique in the right hand table, and indicator=True adds a _merge column marking each row both, left_only or right_only.

A short example

Orders come from a CSV export and customers from an Excel sheet:

import pandas as pd

orders = pd.read_csv("orders.csv", dtype=str)
customers = pd.read_excel("customers.xlsx", dtype={"customer_id": str},
                          engine="openpyxl")

orders["customer_id"] = orders["customer_id"].str.strip()
orders["amount"] = pd.to_numeric(orders["amount"].str.replace(",", ""),
                                 errors="coerce")
orders["order_date"] = pd.to_datetime(orders["order_date"],
                                      format="%d/%m/%Y", errors="coerce")
orders = orders.drop_duplicates(subset="order_id", keep="last")

df = orders.merge(customers, on="customer_id", how="left",
                  validate="many_to_one", indicator=True)
print(df["_merge"].value_counts())
print(df.isna().sum())

On our five row test file this removed one repeated order and reported one order whose customer was missing from the Excel sheet, one impossible date and one unreadable amount. Each of those is a question for whoever owns the data, not something to guess.

Checks before you train a model

1. Row counts before and after every step, written down. An unexplained change is a bug.
2. Ranges: df.describe() shows minimum and maximum values. Look for negative quantities, dates in the future and prices with an extra zero.
3. The target column: no missing values, and a distribution you would expect from the business.
4. Split first. scikit-learn's guide on common pitfalls says to split train and test data before any preprocessing, so fill values and scaling are learned from training rows only. For data over time, split by date.
5. Leakage: remove columns that would not exist at prediction time, such as a refund flag when you are predicting returns.
6. Keep it as a script you can rerun on next month's export, and test the model the way our glossary describes under model evaluation.

Getting your team there

If your data lives across Tally exports, a CRM and spreadsheets, cleaning it is often the bigger half of the project. Our AI training workshops include a session for technical teams built on their own masked documents, and our custom AI systems service designs and builds the whole system, from what it reads to how it is tested, when you would rather hand it over. Book a consultation to talk it through.