Tutorial 3: Data Cleaning with Pandas

By /

Introduction

Data cleaning is one of the most important — and most time-consuming — steps in data analysis. Real-world data is often messy:

Missing Values → Duplicate Records → Incorrect Data Types → Inconsistent Values → Clean Dataset → Reliable Analysis

In this chapter, you’ll learn how to:

  • Identify missing values
  • Handle missing values
  • Find and remove duplicates
  • Correct incorrect data
  • Convert data types
  • Standardize text values

1. Create a Messy Dataset

Let’s create a dataset containing common data problems:

import pandas as pd
import numpy as np

data = {
    "Name": ["Amit", "Priya", "Rahul", "Sneha", "Amit", "Vikram"],
    "Department": ["IT", "HR", "Finance", "it", "IT", "Sales"],
    "Salary": [75000, 60000, np.nan, 90000, 75000, -5000],
    "Experience": [5, 3, 7, np.nan, 5, 4],
    "Email": [
        "amit@example.com",
        "priya@example.com",
        None,
        "sneha@example.com",
        "amit@example.com",
        "vikram@example.com"
    ]
}

df = pd.DataFrame(data)
df

This dataset intentionally contains several common problems:

ProblemExample
Missing salaryNaN
Missing experienceNaN
Missing emailNone
Duplicate recordAmit appears twice
Inconsistent text“IT” and “it”
Incorrect valueSalary = -5000

2. Identify Missing Values

Check whether values are missing:

df.isnull()

This returns True for missing values and False where a value exists.

Count missing values:

df.isnull().sum()

Example output:

Name          0
Department    0
Salary        1
Experience    1
Email         1

This is one of the most useful commands in data cleaning — it’s usually the very first thing you run on any new dataset.

Alternative (identical) syntax:

df.isna().sum()

isna() and isnull() are equivalent in Pandas — isna() is technically the “canonical” name, but both work everywhere.

3. Handling Missing Values

There are several strategies, depending on the situation:

Option 1: Remove rows

df.dropna()

This removes any row containing a missing value. Use with caution — you may lose valuable data if it removes more rows than expected.

Option 2: Fill missing numerical values

For salary, use the average:

df["Salary"] = df["Salary"].fillna(df["Salary"].mean())

For experience, use the median:

df["Experience"] = df["Experience"].fillna(df["Experience"].median())

Mean vs. Median — why the difference?

  • Mean = Sum of values ÷ number of values
  • Median = the middle value when data is sorted

For data with extreme outliers, the median is usually the safer choice, since a single very large or very small value can pull the mean far from what’s “typical.”

Option 3: Fill missing text values

For a missing email:

df["Email"] = df["Email"].fillna("Not Available")

Or for department:

df["Department"] = df["Department"].fillna("Unknown")

4. Find Duplicate Records

Check for duplicates:

df.duplicated()

Count duplicates:

df.duplicated().sum()

Remove duplicate rows:

df = df.drop_duplicates()

Important: df.drop_duplicates() returns a cleaned copy — it doesn’t modify df in place. You must reassign it (df = df.drop_duplicates()) for the change to stick, which is a common trap for beginners.

5. Standardize Text Values

Our dataset contains both "IT" and "it" — technically different strings to Python, even though they mean the same thing to a human.

Convert everything to uppercase:

df["Department"] = df["Department"].str.upper()

Now both become "IT".

You can also use lowercase or title case:

df["Department"] = df["Department"].str.lower()
df["Department"] = df["Department"].str.title()

6. Detect Incorrect Values

Our data contains Salary = -5000 — a negative salary is almost certainly invalid data (perhaps a data-entry error or a placeholder for “unknown”).

Find negative salaries:

df[df["Salary"] < 0]

Replace negative salaries with missing values, so they can be handled properly:

df.loc[df["Salary"] < 0, "Salary"] = np.nan

Now fill the missing value:

df["Salary"] = df["Salary"].fillna(df["Salary"].median())

7. Correct Incorrect Data Types

Check data types:

df.dtypes

Example:

Name           object
Department     object
Salary        float64
Experience    float64
Email          object

If a column should be numeric but isn’t, convert it:

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

What does errors="coerce" do?
If Pandas encounters a value it can’t convert to a number, it replaces it with NaN instead of crashing the whole operation. For example:

pd.to_numeric(["50000", "60000", "invalid"], errors="coerce")

Result: 50000, 60000, NaN

8. A Complete Data Cleaning Workflow

Putting it all together — recreate the messy data, then clean it step by step:

import pandas as pd
import numpy as np

data = {
    "Name": ["Amit", "Priya", "Rahul", "Sneha", "Amit", "Vikram"],
    "Department": ["IT", "HR", "Finance", "it", "IT", "Sales"],
    "Salary": [75000, 60000, np.nan, 90000, 75000, -5000],
    "Experience": [5, 3, 7, np.nan, 5, 4],
    "Email": [
        "amit@example.com",
        "priya@example.com",
        None,
        "sneha@example.com",
        "amit@example.com",
        "vikram@example.com"
    ]
}

df = pd.DataFrame(data)
print("Original Data:")
display(df)

Step 1: Remove duplicate records

df = df.drop_duplicates()

Step 2: Standardize department names

df["Department"] = df["Department"].str.upper()

Step 3: Detect invalid salary values

df.loc[df["Salary"] < 0, "Salary"] = np.nan

Step 4: Fill missing salary values

df["Salary"] = df["Salary"].fillna(df["Salary"].median())

Step 5: Fill missing experience

df["Experience"] = df["Experience"].fillna(df["Experience"].median())

Step 6: Fill missing email

df["Email"] = df["Email"].fillna("Not Available")

Step 7: Check the cleaned data

display(df)

Step 8: Verify no missing values remain

df.isnull().sum()

Before and After

Before cleaning: Amit (IT, 75000), Priya (HR, 60000), Rahul (Finance, NaN), Sneha (it, 90000), Amit (IT, 75000) ← duplicate, Vikram (Sales, -5000) ← invalid

After cleaning: Amit (IT, 75000), Priya (HR, 60000), Rahul (FINANCE, median salary), Sneha (IT, 90000), Vikram (SALES, median salary)

Quick Reference: Data Cleaning Functions

FunctionPurpose
isnull() / isna()Find missing values
fillna()Fill missing values
dropna()Remove rows with missing values
duplicated()Find duplicate rows
drop_duplicates()Remove duplicates
astype()Change data type
pd.to_numeric()Convert values to numbers
.str.upper() / .str.lower()Standardize text case
.str.strip()Remove extra spaces

Practice Exercise

data = {
    "Name": ["Amit", "Priya", "Rahul", "Sneha", "Amit"],
    "Age": [25, 30, None, 28, 25],
    "City": ["Mumbai", "Delhi", "mumbai", None, "Mumbai"],
    "Salary": [50000, 60000, -10000, 70000, 50000]
}
df = pd.DataFrame(data)

Try to: find missing values, remove the duplicate Amit record, standardize city names, replace the invalid salary with NaN, fill missing age with the median, fill missing city with “Unknown,” and calculate the average salary.

Scroll to Top