Tutorials › Python › Data Cleaning with Pandas

I want to be direct with you about something that no job description or university course prepares you for: data cleaning is the job. Not dashboards. Not machine learning. Not beautiful charts. Cleaning. In twenty years of working with data teams, I have never once received a dataset that was ready to analyse. Not from a multinational corporation. Not from a well-funded startup. Not from a government ministry. Every single dataset had something wrong — column names with trailing spaces, numbers stored as text, dates in three different formats in the same column, duplicate rows from system bugs, encoding errors from someone exporting a Hindi-language field through the wrong codepage. The analysts who are fast and trusted are the ones who have a systematic cleaning process they run on every dataset, every time, without skipping steps. The ones who struggle are the ones who eyeball the data, miss something, and spend two days debugging a pivot table that was silently summing null values as zero. This tutorial gives you that systematic process. Memorise it. Run it on every new dataset before you do anything else. Column names first. Data types second. Duplicates third. Whitespace fourth. Nulls fifth. In that order, every time. It will save you more hours than any other skill in this series.

import pandas as pd

df = pd.read_csv("raw_sales.csv")

# STEP 1 — Standardise column names
df.columns = (
    df.columns
    .str.strip()          # remove whitespace
    .str.lower()          # lowercase
    .str.replace(" ", "_")  # spaces to underscores
    .str.replace(r"[^\w]", "", regex=True)  # remove special chars
)
# "Order ID" → "order_id", "Sales Amount (₹)" → "sales_amount_"

# STEP 2 — Fix data types
df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce")
df["sales"]      = pd.to_numeric(df["sales"], errors="coerce")
# errors="coerce" → invalid values become NaN (not an error)

# STEP 3 — Remove duplicates
print(df.duplicated().sum())    # how many duplicate rows
df = df.drop_duplicates()
df = df.drop_duplicates(subset=["order_id"])  # based on key column

# STEP 4 — Strip whitespace from string columns
str_cols = df.select_dtypes(include="object").columns
df[str_cols] = df[str_cols].apply(lambda x: x.str.strip())

# STEP 5 — Standardise text case
df["city"] = df["city"].str.title()   # "delhi" → "Delhi"
df["status"] = df["status"].str.upper()  # "active" → "ACTIVE"

print(f"After cleaning: {df.shape}")