Pandas — Cleaning (NaN, dtypes, duplicates)

• Detect missing values with isna / notna; count per column.
• Decide: dropna, fillna(value), or per-column strategy.
• Fix dtypes with astype and to_datetime.
• Find and remove duplicate rows with duplicated / drop_duplicates.

import pandas as pd
df = pd.read_csv("dirty.csv")     # something with missing values
print(df.isna().sum())            # missing per column
print(df.duplicated().sum())      # number of duplicate rows

Two questions, two one-liners. Run them on every new dataset before any analysis.

Detecting missing

df.isna()              # DataFrame of booleans
df["score"].isna().sum()  # count missing in one column
df.isna().sum()        # missing per column
df.isna().any(axis=1).sum()  # rows with ANY missing value

Handling missing

# Option A: drop
df.dropna()                       # any row with any NaN
df.dropna(subset=["score"])       # only if score is NaN
df.dropna(thresh=3)               # keep rows with ≥3 non-NaN

# Option B: fill
df.fillna({"score": 0, "name": "Unknown"})
df["score"].fillna(df["score"].mean(), inplace=False)
df["score"].fillna(method="ffill")     # carry last good value forward

Pick the strategy that matches the data. "Missing score" might mean "not yet taken" (fill with NaN, keep) or "0 attempted, didn't show" (fill with 0). Document the choice in a comment.

Fixing dtypes

df["id"] = df["id"].astype(str)
df["price"] = df["price"].astype(float)
df["date"]  = pd.to_datetime(df["date"], errors="coerce")
                                  # errors="coerce" → NaT for unparseable

to_datetime with errors="coerce" is the cleanest way to bring messy date columns under control — unparseable values become NaT (the date equivalent of NaN).

Duplicates

df.duplicated().sum()              # how many exact-match dupes
df.duplicated(subset=["id"]).sum() # dupe by one column
df.drop_duplicates()               # remove
df.drop_duplicates(subset=["id"], keep="last")
                                  # keep the most recent

import pandas as pd

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

print("\nbefore:")
print(df.isna().sum())
print(f"duplicates: {df.duplicated(subset=['order_id']).sum()}")
print(df.dtypes)

# 1. Parse dates (handles mixed formats)
df["date"] = pd.to_datetime(df["date"], errors="coerce", dayfirst=False)

# 2. Force numeric columns
for col in ["quantity", "price"]:
    df[col] = pd.to_numeric(df[col], errors="coerce")

# 3. Drop rows missing required fields
df = df.dropna(subset=["customer", "product", "quantity", "price", "date"])

# 4. Drop duplicate order_ids, keep first
df = df.drop_duplicates(subset=["order_id"])

# 5. Trim and Title-case text columns
for col in ["customer", "product"]:
    df[col] = df[col].str.strip().str.title()

print("\nafter:")
print(df.isna().sum())
print(f"duplicates: {df.duplicated(subset=['order_id']).sum()}")
print(df.head())

Sample output

before:
order_id     0
customer     1
product      0
quantity     2
price        1
date         0
dtype: int64
duplicates: 1
order_id     int64
customer    object
product     object
quantity    object   ← had "one" in there
price       object   ← had blanks
date        object

after:
(all zero missing, no duplicate ids, dtypes now int / float / datetime)

Read the diff

This is yesterday's janitor script — but in 5 lines instead of 50, because pandas vectorises every step. to_numeric(errors="coerce") turned the word "one" into NaN, which dropna then removed. Less code, fewer bugs.

Three cleanup tools cover most real-world dirt: isna + dropna/fillna for missing values; astype/to_numeric/to_datetime for dtypes; drop_duplicates for duplicates. Vectorised — way faster than Python loops. Tomorrow: merge — pandas's JOIN.