Data Cleaning in pandas: Missing Values & Data Types

Analysts often say they spend 80% of their time cleaning data and 20% analysing it. Whether or not the exact number is right, the lesson holds: real data is messy. A CSV exported from a Flipkart seller dashboard or a kirana billing app will have blank cells, prices stored as text like "₹1,200", duplicate orders, and dates that import as plain strings. Before any analysis is trustworthy, you have to clean it.

This guide covers the everyday cleaning toolkit in pandas: detecting and handling missing values, fixing data types, and removing duplicates. We will work with a messy orders DataFrame throughout.

import pandas as pd

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

Detecting Missing Values

In pandas, missing data shows up as NaN (Not a Number) for numbers and NaT for dates. The detection functions are isna() and its alias isnull() (identical - use whichever reads better).

Count missing values per column - the first thing to run on any dataset:

df.isna().sum()
# order_id      0
# customer       12
# amount          5
# order_date      3
# city            8

Get the percentage missing, which matters more than raw counts:

(df.isna().mean() * 100).round(1)
# customer    2.4
# amount      1.0

See the actual rows with any missing value:

df[df.isna().any(axis=1)]

axis=1 means "look across columns within each row." This is invaluable for eyeballing what is wrong before you decide how to fix it.

Handling Missing Values: Drop or Fill

There is no universal right answer - it depends on the column and how much is missing.

Dropping with dropna

Remove rows that have any missing value:

df_clean = df.dropna()

That is often too aggressive - one missing optional field should not delete an entire order. Be targeted:

# Drop rows only where the critical columns are missing
df = df.dropna(subset=["order_id", "amount"])

# Drop a column that is mostly empty
df = df.dropna(axis=1, thresh=int(0.5 * len(df)))  # keep cols >=50% filled

Use subset to protect against deleting good data over a trivial gap.

Filling with fillna

More often you want to fill missing values with a sensible default.

# Missing amounts become 0 (only if "no value" truly means zero)
df["amount"] = df["amount"].fillna(0)

# Missing city becomes a label, not a blank
df["city"] = df["city"].fillna("Unknown")

# Fill numeric gaps with the column's median (robust to outliers)
df["amount"] = df["amount"].fillna(df["amount"].median())

Choose the fill value deliberately. Filling missing ratings with 0 will drag down an average; filling with the median preserves the distribution. Filling a missing city with "Unknown" keeps the row in city-level reports instead of silently dropping it.

A common interview point: do not blindly fill missing numbers with the mean. The mean is sensitive to outliers; the median is usually safer for skewed Indian sales data where a few bulk orders distort the average.

Fixing Data Types

The classic mess: amount imports as text because values contain ₹ and commas. You cannot do math on text, so you must convert.

First diagnose with dtypes:

df.dtypes
# amount        object   <- text, should be numeric
# order_date    object   <- text, should be datetime

object almost always means string. Clean the text, then convert:

# Strip currency symbol and commas, then convert to number
df["amount"] = (
    df["amount"]
    .str.replace("₹", "", regex=False)
    .str.replace(",", "", regex=False)
    .astype(float)
)

If some values cannot be converted (stray text, blanks), astype will error. Use to_numeric with errors="coerce" to turn bad values into NaN instead of crashing:

df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

This is the safer choice for dirty real-world files - convert what you can, flag the rest as missing, then handle those nulls.

Convert text dates to real datetimes:

df["order_date"] = pd.to_datetime(df["order_date"], errors="coerce", dayfirst=True)

The dayfirst=True matters in India, where dates are commonly written DD/MM/YYYY. Without it, pandas may read 03/04/2025 as March 4 instead of April 3.

For categories with few distinct values (city, payment mode), convert to category dtype to save memory and speed up grouping:

df["payment"] = df["payment"].astype("category")

Removing Duplicates

Duplicate rows inflate totals - a double-counted order makes revenue look higher than it is.

Find duplicates:

df.duplicated().sum()           # how many fully duplicate rows
df[df.duplicated(keep=False)]   # show all copies, including the first

Drop them, keeping the first occurrence:

df = df.drop_duplicates()

Often you want uniqueness on a key column, not the whole row - for example, one record per order_id, keeping the latest:

df = df.sort_values("order_date").drop_duplicates(subset="order_id", keep="last")

subset defines what counts as a duplicate; keep="last" retains the most recent version.

Standardising Text

Inconsistent text creates fake categories: "mumbai", "Mumbai ", and "MUMBAI" look like three cities to groupby. Normalise them:

df["city"] = df["city"].str.strip().str.title()
# "  mumbai " -> "Mumbai"

strip() removes surrounding whitespace; title() standardises capitalisation. This single step often fixes inflated distinct counts.

A Sensible Cleaning Order

Cleaning steps interact, so sequence matters:

1. Inspect - info(), isna().sum(), dtypes, head().
2. Standardise text - strip and case-normalise key string columns.
3. Fix types - convert amounts and dates, using coerce to surface bad values as NaN.
4. Handle missing values - drop or fill, deciding per column.
5. Remove duplicates - on the whole row or on a business key.
6. Re-inspect - confirm isna().sum() and dtypes look right.

Best Practices

• Never overwrite your raw file; clean into a new DataFrame or save a separate output.
• Prefer to_numeric(..., errors="coerce") over astype for dirty columns so one bad value does not crash the job.
• Always pass dayfirst=True for Indian date formats.
• Justify your fill choice - median over mean for skewed data, a label over blanks for categories.
• Re-run your null and dtype checks after cleaning to verify the fixes actually worked.

Clean data is the foundation everything else stands on. Master isna, fillna, dropna, to_numeric, astype, and drop_duplicates, and you will turn any messy CSV into something you can analyse with confidence.

Related: Working with Dates & Times in pandas · Practise Python

Don't just read. Prove your skill on DevWithData.