Data Cleaning

Learning Objectives

Data cleaning is one of the most important practical skills for a data analyst. Real-world datasets often contain missing, duplicated, incorrectly formatted or inconsistent information.

What Is Data Cleaning?

Data cleaning is the process of detecting and correcting inaccurate, incomplete, duplicated, inconsistent or improperly formatted data.

Consider the following customer dataset:

Name       Age       City
Ali        25        Lahore
ali        25        lahore
Sara       NaN       Islamabad
Ahmed      "31"      ISB

The data contains several problems:

• Different capitalization.
• A duplicated customer.
• A missing age.
• Age stored as text.
• Different representations of Islamabad.

Why Data Cleaning Matters

Poor-quality data can produce poor-quality analysis.

Bad Data
   ↓
Incorrect Calculations
   ↓
Incorrect Analysis
   ↓
Incorrect Decisions

For example, if a sales dataset contains duplicate transactions, the calculated revenue may be higher than the actual revenue.

Professional Data Cleaning Workflow

A professional cleaning process usually follows a structured workflow.

1. Load Data
      ↓
2. Inspect Data
      ↓
3. Identify Problems
      ↓
4. Clean Data
      ↓
5. Validate Data
      ↓
6. Analyze Data
      ↓
7. Export Clean Dataset

Inspecting Data

Before changing anything, inspect the dataset.

import pandas as pd

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

print(df.head())

print(df.shape)

print(df.columns)

print(df.info())

print(df.describe())

You should investigate:

• Number of rows.
• Number of columns.
• Column names.
• Data types.
• Missing values.
• Unexpected values.
• Duplicate records.

Finding Missing Values

Missing values are extremely common in real-world datasets.

print(df.isna())

To count missing values in each column:

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

To calculate the percentage of missing values:

missing_percentage = (
    df.isna().mean() * 100
)

print(missing_percentage)

Removing Missing Data

Pandas provides dropna() for removing rows or columns containing missing values.

cleaned = df.dropna()

print(cleaned)

You can remove rows only when specific columns are missing.

cleaned = df.dropna(
    subset=["CustomerID"]
)

You can also remove columns containing missing values, although this should be done carefully.

cleaned = df.dropna(
    axis=1
)

Filling Missing Values

Instead of deleting missing values, you can replace them with appropriate values.

df["Age"] = df[
    "Age"
].fillna(0)

For numerical data, the mean or median may sometimes be useful.

median_age = df[
    "Age"
].median()

df["Age"] = df[
    "Age"
].fillna(median_age)

For categorical data, a common strategy is to use the most frequent value or an explicit category such as "Unknown".

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

Detecting Duplicate Records

Duplicate rows can lead to incorrect calculations.

duplicates = df.duplicated()

print(duplicates)

Count duplicates:

print(
    df.duplicated().sum()
)

Remove complete duplicate rows:

df = df.drop_duplicates()

You can also identify duplicates based on specific columns.

df.duplicated(
    subset=["CustomerID"]
)

Correcting Data Types

A dataset may contain numbers stored as strings, dates stored as text or inconsistent types.

print(df.dtypes)

Convert a column to numeric data:

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

With errors="coerce", values that cannot be converted become missing values.

df["Quantity"] = df[
    "Quantity"
].astype("Int64")

Cleaning Text Data

Text fields frequently contain extra spaces, inconsistent capitalization and formatting problems.

df["Name"] = df[
    "Name"
].str.strip()

Convert text to lowercase:

df["City"] = df[
    "City"
].str.lower()

Convert text to uppercase:

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

Capitalize words:

df["City"] = df[
    "City"
].str.title()

"  Lahore "
"LAHORE"
"lahore"
"Lahore "

Replacing Values

Use replace() when values need to be standardized.

df["City"] = df[
    "City"
].replace({
    "ISB": "Islamabad",
    "LHR": "Lahore"
})

You can also replace individual values:

df["Status"] = df[
    "Status"
].replace(
    "Completed ",
    "Completed"
)

Combining replacement with string cleaning can create a more reliable standardization process.

Cleaning Dates

Dates are often imported as strings and need to be converted before analysis.

df["OrderDate"] = pd.to_datetime(
    df["OrderDate"],
    errors="coerce"
)

Once converted, you can extract useful components.

df["Year"] = (
    df["OrderDate"].dt.year
)

df["Month"] = (
    df["OrderDate"].dt.month
)

df["Day"] = (
    df["OrderDate"].dt.day
)

Cleaning Numeric Data

Numeric fields can contain currency symbols, commas, spaces or other characters.

prices = pd.Series([
    "$1,200",
    "$850",
    "$2,500"
])

prices = (
    prices
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .astype(float)
)

print(prices)

0    1200.0
1     850.0
2    2500.0
dtype: float64

Numeric cleaning is particularly important when data comes from manually maintained spreadsheets or exported reports.

Understanding Outliers

An outlier is a value that is unusually different from other observations.

sales = pd.Series([
    100,
    120,
    110,
    105,
    115,
    5000
])

print(
    sales.describe()
)

The value 5000 is dramatically larger than the other values.

Simple Statistical Approach

One common technique uses the interquartile range (IQR).

q1 = sales.quantile(0.25)

q3 = sales.quantile(0.75)

iqr = q3 - q1

lower = q1 - 1.5 * iqr

upper = q3 + 1.5 * iqr

outliers = sales[
    (sales < lower) |
    (sales > upper)
]

print(outliers)

This is a useful analytical technique, but the appropriate outlier method depends on the dataset and business problem.

Data Validation

Cleaning should be followed by validation.

Suppose product quantity must always be greater than zero.

invalid = df[
    df["Quantity"] <= 0
]

print(invalid)

Validate prices:

invalid_prices = df[
    df["Price"] < 0
]

Validate categories:

valid_categories = [
    "Electronics",
    "Clothing",
    "Home"
]

invalid = df[
    ~df["Category"].isin(
        valid_categories
    )
]

Cleaning Column Names

Consistent column names make analytical code easier to understand and maintain.

df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)

print(df.columns)

For example:

Customer Name
Order Date
Total Sales

can become:

customer_name
order_date
total_sales

Standardizing Data

Standardization means representing equivalent values consistently.

df["Gender"] = df[
    "Gender"
].replace({
    "M": "Male",
    "m": "Male",
    "F": "Female",
    "f": "Female"
})

Another example:

df["Payment"] = df[
    "Payment"
].str.strip().str.title()

This can turn values such as:

" cash "
"Cash"
"cash"
"CASH"

into a consistent representation.

Conditional Cleaning

Sometimes the correct cleaning rule depends on a condition.

df.loc[
    df["Age"] < 0,
    "Age"
] = None

You can also create standardized categories.

df["CustomerType"] = "Regular"

df.loc[
    df["TotalSales"] >= 5000,
    "CustomerType"
] = "Premium"

This approach is useful when business rules are known and clearly defined.

Using apply()

apply() allows you to apply a function to values in a Series or along a DataFrame axis.

def clean_name(name):
    return name.strip().title()

df["Name"] = df[
    "Name"
].apply(clean_name)

For simple operations, built-in vectorized Pandas methods are often preferable because they are generally clearer and can be more efficient.

df["Name"] = df[
    "Name"
].str.strip().str.title()

Keep the Raw Data Safe

A professional data workflow should preserve the original dataset.

raw_df = pd.read_csv(
    "sales.csv"
)

df = raw_df.copy()

Perform cleaning on df while keeping raw_df unchanged.

df["Name"] = (
    df["Name"]
    .str.strip()
    .str.title()
)

Practical Example — Cleaning Ecommerce Data

Consider this intentionally messy ecommerce dataset.

import pandas as pd

df = pd.DataFrame({
    "Customer Name": [
        " ali ",
        "Sara",
        "Ahmed",
        "Ali"
    ],

    "City": [
        "lahore",
        "ISB",
        " Islamabad ",
        "Lahore"
    ],

    "Price": [
        "$1,200",
        "$800",
        "$500",
        "$1,200"
    ],

    "Quantity": [
        "2",
        "3",
        "1",
        "2"
    ]
})

print(df)

Step 1 — Clean Column Names

df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)

Step 2 — Clean Customer Names

df["customer_name"] = (
    df["customer_name"]
    .str.strip()
    .str.title()
)

Step 3 — Standardize Cities

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

df["city"] = df[
    "city"
].replace({
    "Isb": "Islamabad"
})

Step 4 — Clean Prices

df["price"] = (
    df["price"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .astype(float)
)

Step 5 — Convert Quantity

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

Step 6 — Calculate Revenue

df["revenue"] = (
    df["price"] *
    df["quantity"]
)

Step 7 — Validate

print(df.info())
print(df.isna().sum())
print(df.duplicated().sum())

Raw Ecommerce Data
        ↓
Column Cleaning
        ↓
Text Standardization
        ↓
Numeric Conversion
        ↓
Validation
        ↓
Calculated Revenue
        ↓
Analysis Ready

Common Data Cleaning Mistakes

Practice Exercises

Mini Challenge — Real-World Sales Data Cleaner

Your company has received a messy sales report. Before the data can be analyzed, you must clean it.

Starter Dataset

import pandas as pd

sales = pd.DataFrame({
    " Customer Name ": [
        " ali ",
        "Sara",
        "AHMED",
        "Ali"
    ],

    " City ": [
        "lahore",
        "ISB",
        " Islamabad ",
        "Lahore"
    ],

    " Price ": [
        "$1,200",
        "$800",
        "$500",
        "$1,200"
    ],

    " Quantity ": [
        "2",
        "3",
        "1",
        "2"
    ]
})

print(sales)

Suggested Cleaning Pipeline

# Preserve raw data
raw_sales = sales.copy()

# Clean column names
sales.columns = (
    sales.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)

# Clean names
sales["customer_name"] = (
    sales["customer_name"]
    .str.strip()
    .str.title()
)

# Clean cities
sales["city"] = (
    sales["city"]
    .str.strip()
    .str.title()
)

sales["city"] = sales[
    "city"
].replace({
    "Isb": "Islamabad"
})

# Clean prices
sales["price"] = (
    sales["price"]
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

sales["price"] = pd.to_numeric(
    sales["price"],
    errors="coerce"
)

# Convert quantity
sales["quantity"] = pd.to_numeric(
    sales["quantity"],
    errors="coerce"
)

# Calculate revenue
sales["revenue"] = (
    sales["price"] *
    sales["quantity"]
)

print(sales)

invalid = sales[
    (sales["price"] < 0) |
    (sales["quantity"] <= 0)
]

print("Invalid records:")
print(invalid)

sales.to_csv(
    "clean_sales.csv",
    index=False
)

Knowledge Check

Lesson Summary

Data cleaning is a critical part of professional data analysis. Before generating charts, calculating KPIs or building dashboards, analysts need confidence that the underlying data is reliable.

You have now covered a complete foundation for preparing real-world datasets with Python and Pandas. The next lesson moves into Matplotlib, where you will turn cleaned data into meaningful visualizations and charts.