Pandas Cleaning Data

Real data is often messy, and cleaning it is one of the most important jobs in data analysis.

Common Problems

The Example Data

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name": ["Ali", "Sara", "Omar", "Sara", "Hina"],
    "city": [" karachi", "Lahore", None, "Lahore", "KARACHI"],
    "marks": [78, np.nan, 65, np.nan, 90],
    "fee": ["15000", "18000", "15000", "18000", "12000"]
})
print(df)

   name      city  marks    fee
0   Ali   karachi   78.0  15000
1  Sara    Lahore    NaN  18000
2  Omar       NaN   65.0  15000
3  Sara    Lahore    NaN  18000
4  Hina   KARACHI   90.0  12000

NaN means "Not a Number", which is how Pandas shows a missing value.

Finding Missing Values

print(df.isna().sum())

name     0
city     1
marks    2
fee      0
dtype: int64

isna() marks each empty cell as True, and sum() counts them per column.

Dropping or Filling Missing Values

print(df.dropna())
print(df["marks"].fillna(0))

   name      city  marks    fee
0   Ali   karachi   78.0  15000
4  Hina   KARACHI   90.0  12000
0    78.0
1     0.0
2    65.0
3     0.0
4    90.0
Name: marks, dtype: float64

A common choice is to fill missing numbers with the column average:

df["marks"] = df["marks"].fillna(df["marks"].mean())
df["city"] = df["city"].fillna("Unknown")
print(df)

   name      city      marks    fee
0   Ali   karachi  78.000000  15000
1  Sara    Lahore  77.666667  18000
2  Omar   Unknown  65.000000  15000
3  Sara    Lahore  77.666667  18000
4  Hina   KARACHI  90.000000  12000

Removing Duplicates

print(df.duplicated())
df = df.drop_duplicates()
print(len(df))

0    False
1    False
2    False
3     True
4    False
dtype: bool
4

Row 3 was an exact copy of row 1, so it was removed. Use subset=["name"] to check only some columns.

Fixing Data Types

The fee column holds text, so you cannot add it up properly. Convert it with astype():

print(df["fee"].dtype)
df["fee"] = df["fee"].astype(int)
print(df["fee"].sum())

str
60000

For messy values, pd.to_numeric(df["col"], errors="coerce") turns anything that is not a number into NaN instead of raising an error. For dates, use pd.to_datetime().

Cleaning Text

The .str accessor gives you string methods for a whole column:

df["city"] = df["city"].str.strip().str.title()
print(df["city"].tolist())

['Karachi', 'Lahore', 'Unknown', 'Karachi']

Now " karachi" and "KARACHI" are both "Karachi", so grouping and counting will work correctly.

Renaming and Dropping Columns

df = df.rename(columns={"marks": "score"})
df = df.drop(columns=["fee"])
print(df)

   name     city      score
0   Ali  Karachi  78.000000
1  Sara   Lahore  77.666667
2  Omar  Unknown  65.000000
4  Hina  Karachi  90.000000

Exercise