How to Clean Data with Pandas: A 6-Step Data Cleaning Guide

Raw data is rarely ready for analysis. It may contain missing values, duplicate records, inconsistent categories, incorrect data types, impossible values, and unusual observations.

Data cleaning, also called data wrangling or data preprocessing, is the process of finding and correcting these problems before you create visualisations, calculate statistics, or train a machine-learning model.

In this chapter, you will learn a practical six-step data cleaning workflow with Python and pandas:

1. Remove irrelevant data
2. Find and remove duplicates
3. Repair structural errors
4. Handle missing values
5. Investigate and treat outliers
6. Validate the cleaned dataset

By the end of the chapter, you will have a reusable framework for turning a raw CSV file into a cleaner and more trustworthy dataset.

What Is Data Cleaning?

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

Common data quality problems include:

• Missing values in important columns
• Duplicate rows or repeated records
• Inconsistent labels such as Male, male, and M
• Numbers stored as text
• Invalid dates
• Negative values where only positive values are possible
• Outliers caused by data-entry or measurement errors
• Columns that do not contribute to the analysis

Cleaning does not mean changing data until it produces the result you want. Every transformation should have a clear reason and should preserve as much valid information as possible.

Why Data Cleaning Matters

Analysis and machine-learning models depend on the quality of the input data.

Poor-quality data can:

• Inflate counts because of duplicates
• Produce incorrect averages and percentages
• Cause Python errors because of incompatible data types
• Hide patterns behind inconsistent category labels
• Bias a model because missing values were handled incorrectly
• Distort charts and summary statistics
• Make the final results difficult to reproduce

A well-documented cleaning process makes the analysis easier to understand, verify, and repeat.

Prepare the Data Cleaning Environment

For this tutorial, you only need a small group of libraries:

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

The examples use a DataFrame named df. Replace example column names such as price, age, city, and gender with the actual column names in your dataset.

Load a CSV File with pandas

You can practise with your own CSV file or download a public dataset from a source such as Kaggle Datasets.

Load a CSV File Locally

When the CSV file is stored in the same folder as the notebook, use:

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

df.head()

For a file inside a subfolder named data, use:

df = pd.read_csv("data/data.csv")

Load a CSV File in Google Colab

To read a file stored in Google Drive, mount Drive first:

from google.colab import drive

drive.mount("/content/drive")

After authorising access, files in your Drive are normally available under:

/content/drive/MyDrive/

For example:

df = pd.read_csv("/content/drive/MyDrive/datasets/data.csv")

df.head()

Only mount Google Drive in notebooks you trust because code in the notebook may be able to access your Drive files.

Preserve the Original Data

Keep an unchanged copy of the imported dataset before cleaning it:

raw_df = pd.read_csv("data.csv")
df = raw_df.copy()

This allows you to compare the cleaned data with the original or restart without reading the file again.

Rerunning the following line also reloads the original data from disk and discards changes currently stored in df:

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

Inspect the Dataset Before Cleaning

Do not begin deleting or replacing values immediately. First, inspect the dataset and identify its problems.

Preview the Rows

df.head()

To inspect random rows instead of only the first five:

df.sample(5, random_state=42)

Check the Dataset Size

print(f"Rows: {df.shape[0]}")
print(f"Columns: {df.shape[1]}")

Inspect Column Names and Data Types

df.info()

This displays:

• Column names
• Non-null counts
• Data types
• Approximate memory usage

Review Summary Statistics

For numerical columns:

df.describe()

For numerical and categorical columns:

df.describe(include="all")

Count Missing Values

missing_summary = pd.DataFrame({
    "missing_count": df.isna().sum(),
    "missing_percentage": df.isna().mean().mul(100).round(2)
})

missing_summary.sort_values(
    "missing_percentage",
    ascending=False
)

Count Duplicate Rows

duplicate_count = df.duplicated().sum()

print(f"Duplicate rows: {duplicate_count}")

This initial audit gives you a baseline against which you can compare the cleaned dataset.

The Six Steps of Data Cleaning with pandas

The order may change slightly depending on the dataset. For example, you may need to standardise text before detecting duplicates that differ only in capitalisation.

Step 1: Remove Irrelevant Data

Remove columns only when they are not needed for the analysis or cannot provide meaningful information.

Possible candidates include:

• Exported row numbers such as Unnamed: 0
• Duplicate columns
• Constant columns containing the same value in every row
• Internal identifiers that are not relevant to the analysis
• Test or temporary fields accidentally included in the export

Inspect the columns first:

print(df.columns.tolist())

Remove known irrelevant columns with drop():

columns_to_remove = ["irrelevant_id", "temporary_note"]

df = df.drop(
    columns=columns_to_remove,
    errors="ignore"
)

The errors="ignore" argument prevents an error when one of the listed columns is absent.

Find Constant Columns

A constant column contains only one unique value, including or excluding missing values depending on the calculation.

constant_columns = [
    column
    for column in df.columns
    if df[column].nunique(dropna=False) <= 1
]

print(constant_columns)

Review the result before removing anything:

df = df.drop(columns=constant_columns)

Do not automatically remove every identifier. An order ID, customer ID, or transaction ID may be essential for checking uniqueness, joining tables, or detecting duplicate records.

Step 2: Find and Remove Duplicate Data

Duplicates may be introduced during data entry, scraping, concatenation, or database joins.

Count fully duplicated rows:

duplicate_count = df.duplicated().sum()

print(f"Duplicate rows before cleaning: {duplicate_count}")

Inspect them before deletion:

duplicate_rows = df[df.duplicated(keep=False)]

duplicate_rows.head()

Remove exact duplicates:

df = df.drop_duplicates().copy()

Find Duplicates Using Selected Columns

Two records may represent the same entity even when one non-essential field differs.

For example, you can identify repeated customer records using an email address:

email_duplicates = df[
    df.duplicated(
        subset=["email"],
        keep=False
    )
]

email_duplicates.head()

Remove repeated email records while keeping the first occurrence:

df = df.drop_duplicates(
    subset=["email"],
    keep="first"
).copy()

Choosing keep="first" or keep="last" should be based on a business rule, such as retaining the newest record after sorting by date.

Step 3: Repair Structural Errors

Structural errors occur when the same type of information is represented in inconsistent ways.

Examples include:

• New York, new_york, and NEW YORK
• Male, male, and M
• Prices stored as text such as €1,250
• Dates stored in several formats
• Leading or trailing spaces in category names

Clean Column Names

Standardise column names early in the process:

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

For example, Purchase Date becomes purchase_date.

Normalise Text Values

df["city"] = (
    df["city"]
    .astype("string")
    .str.strip()
    .str.replace("_", " ", regex=False)
    .str.lower()
)

Use .astype("string") rather than .astype(str) when you want pandas missing values to remain missing instead of becoming the literal text "nan".

Standardise Categories

First normalise the text, then map known variants:

df["gender"] = (
    df["gender"]
    .astype("string")
    .str.strip()
    .str.lower()
)

gender_mapping = {
    "m": "Male",
    "male": "Male",
    "f": "Female",
    "female": "Female"
}

df["gender"] = df["gender"].replace(gender_mapping)

Inspect the remaining categories:

print(df["gender"].value_counts(dropna=False))

Do not force unexpected categories into an existing label without investigating what they mean.

Convert Text to Numbers

Suppose the price column contains currency symbols and commas:

df["price"] = (
    df["price"]
    .astype("string")
    .str.replace("€", "", regex=False)
    .str.replace(",", "", regex=False)
    .str.strip()
)

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

With errors="coerce", values that cannot be converted become NaN. Review these new missing values because they may reveal invalid source data.

Convert Text to Dates

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

When your source uses a known date format, specify it explicitly:

df["purchase_date"] = pd.to_datetime(
    df["purchase_date"],
    format="%Y-%m-%d",
    errors="coerce"
)

Explicit formats reduce ambiguity and make parsing behaviour easier to understand.

Step 4: Handle Missing Values

Missing values require context. A missing value may mean:

• The information was not collected
• The value does not apply
• A sensor failed
• A user skipped a question
• A conversion created an invalid value
• The value is genuinely unknown

Start by measuring both the number and percentage of missing values:

missing_summary = pd.DataFrame({
    "missing_count": df.isna().sum(),
    "missing_percentage": df.isna().mean().mul(100).round(2)
})

missing_summary.sort_values(
    "missing_percentage",
    ascending=False
)

Option 1: Remove Rows with Missing Values

Remove rows only when the missing values make those records unusable and the loss of data is acceptable.

Remove rows missing a required value:

df = df.dropna(subset=["price"]).copy()

Remove rows missing any value:

df = df.dropna().copy()

The second option can remove a large portion of the dataset, so use it carefully.

Option 2: Fill Numerical Missing Values

The median is often useful for skewed numerical data because it is less sensitive to extreme values than the mean:

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

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

For approximately symmetric data, the mean may be reasonable:

mean_score = df["score"].mean()

df["score"] = df["score"].fillna(mean_score)

Option 3: Fill Categorical Missing Values

Use a meaningful placeholder when the value is genuinely unknown:

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

You can use the most frequent category when that choice is justified:

status_mode = df["status"].mode(dropna=True)

if not status_mode.empty:
    df["status"] = df["status"].fillna(status_mode.iloc[0])

Option 4: Interpolate Ordered or Time-Series Data

Interpolation estimates values between existing observations and may be useful when rows have a meaningful order.

df = df.sort_values("date")

df["temperature"] = df["temperature"].interpolate(
    method="linear"
)

Interpolation is not automatically appropriate for every time series. Consider the sampling interval, trend, seasonality, gap length, and purpose of the analysis before using it.

Advanced signal-processing methods such as Fourier-based reconstruction require additional assumptions and are not a general replacement for missing-value analysis.

Avoid Data Leakage

When preparing data for machine learning, calculate imputation values from the training data only. Using the full dataset before splitting can leak information from the test set into the training process.

Step 5: Investigate and Treat Outliers

An outlier is an observation that differs substantially from the rest of the data.

Outliers may represent:

• Data-entry errors
• Measurement errors
• Unusual but valid events
• Rare groups in the population
• Fraud or anomalies
• A naturally skewed distribution

Do not remove a value only because a statistical rule labels it as unusual. Investigate its meaning first.

Visualise Outliers with a Box Plot

plt.figure(figsize=(8, 6))
sns.boxplot(y=df["price"])
plt.title("Price Distribution Before Outlier Treatment")
plt.ylabel("Price")
plt.show()

You can also inspect the distribution:

df["price"].describe()

Detect Outliers with the IQR Method

The interquartile range is calculated as:

IQR = Q3 - Q1

A common rule flags values below Q1 - 1.5 × IQR or above Q3 + 1.5 × IQR.

price_values = df["price"].dropna()

q1 = price_values.quantile(0.25)
q3 = price_values.quantile(0.75)
iqr = q3 - q1

lower_bound = q1 - (1.5 * iqr)
upper_bound = q3 + (1.5 * iqr)

outlier_mask = (
    df["price"].notna()
    & (
        (df["price"] < lower_bound)
        | (df["price"] > upper_bound)
    )
)

print(f"Potential price outliers: {outlier_mask.sum()}")

Inspect the flagged rows:

df.loc[outlier_mask].sort_values("price")

Remove Confirmed Outliers

After confirming that the flagged values are invalid or outside the intended scope:

df_without_outliers = df.loc[~outlier_mask].copy()

Keeping the result in a new DataFrame makes the decision easier to compare and reverse.

Cap Extreme Values Instead of Removing Rows

Winsorisation-style capping keeps the rows but limits the values to the selected boundaries:

df["price_capped"] = df["price"].clip(
    lower=lower_bound,
    upper=upper_bound
)

Whether you remove, cap, transform, or retain outliers depends on the cause of the unusual values and the goal of the analysis.

The IQR rule is not equally suitable for every distribution. Highly skewed data may naturally contain many values above the upper boundary.

Step 6: Validate the Cleaned Data

Validation checks whether the cleaned dataset follows the rules expected for the project.

Examples include:

• Required columns contain no missing values
• Ages and prices are not negative
• Ratings remain within an allowed range
• Categories contain only accepted labels
• Identifier columns remain unique
• Dates fall within a realistic period
• Duplicate rows have been removed

Create Boolean Validation Checks

checks = {
    "no_duplicate_rows": not df.duplicated().any(),
    "age_is_non_negative": df["age"].dropna().ge(0).all(),
    "ratings_are_between_1_and_5": (
        df["rating"]
        .dropna()
        .between(1, 5)
        .all()
    ),
    "gender_values_are_valid": (
        df["gender"]
        .dropna()
        .isin(["Male", "Female"])
        .all()
    ),
    "category_has_no_missing_values": (
        df["category"]
        .notna()
        .all()
    )
}

validation_results = pd.Series(
    checks,
    name="passed"
)

validation_results

Each expression returns True when the rule passes and False when it fails.

Use .all() when every value must meet a condition. Using .sum() returns the number of matching rows rather than a pass-or-fail result.

Use Assertions for Required Rules

Assertions stop the notebook when an essential rule fails:

assert not df.duplicated().any(), "Duplicate rows remain."
assert df["age"].dropna().ge(0).all(), "Negative ages found."
assert df["rating"].dropna().between(1, 5).all(), (
    "Ratings outside the range 1 to 5 were found."
)

Assertions are useful for reproducible pipelines because they make data-quality failures visible instead of allowing the analysis to continue silently.

Compare the Dataset Before and After Cleaning

comparison = pd.Series({
    "raw_rows": len(raw_df),
    "clean_rows": len(df),
    "rows_removed": len(raw_df) - len(df),
    "raw_columns": raw_df.shape[1],
    "clean_columns": df.shape[1],
    "duplicate_rows_remaining": df.duplicated().sum(),
    "missing_values_remaining": int(df.isna().sum().sum())
})

comparison

A lower row count is not automatically evidence of better data. Record why rows or columns were removed.

Save the Cleaned Dataset

Save the final DataFrame to a new file instead of overwriting the raw source:

df.to_csv(
    "data_cleaned.csv",
    index=False
)

For a Parquet file that preserves data types more reliably:

df.to_parquet(
    "data_cleaned.parquet",
    index=False
)

Parquet support may require an additional package such as pyarrow.

A Reusable pandas Data Cleaning Example

The following example combines several of the techniques from this chapter. Adjust the column names and rules for your own dataset.

import pandas as pd

# Load and preserve the original data
raw_df = pd.read_csv("data.csv")
df = raw_df.copy()

# Standardise column names
df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_", regex=False)
)

# Remove known irrelevant columns
df = df.drop(
    columns=["irrelevant_id"],
    errors="ignore"
)

# Clean text values
if "city" in df.columns:
    df["city"] = (
        df["city"]
        .astype("string")
        .str.strip()
        .str.replace("_", " ", regex=False)
        .str.lower()
    )

# Convert price to a numerical column
if "price" in df.columns:
    df["price"] = (
        df["price"]
        .astype("string")
        .str.replace("€", "", regex=False)
        .str.replace(",", "", regex=False)
        .str.strip()
    )

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

# Remove duplicate rows
df = df.drop_duplicates().copy()

# Fill missing ages with the median
if "age" in df.columns:
    median_age = df["age"].median()
    df["age"] = df["age"].fillna(median_age)

# Validate the result
assert not df.duplicated().any(), "Duplicate rows remain."

# Export the cleaned data
df.to_csv(
    "data_cleaned.csv",
    index=False
)

A real project should also record why each transformation was chosen.

Data Cleaning Checklist

Before considering a dataset ready for analysis, confirm that you have:

• Preserved the original data
• Inspected the rows, columns, and data types
• Measured missing values and duplicates
• Removed only genuinely irrelevant fields
• Reviewed duplicate records before deleting them
• Standardised column names and categorical labels
• Converted numerical and date columns correctly
• Investigated values converted to NaN
• Chosen a justified missing-value strategy
• Investigated outliers before treating them
• Added logical validation rules
• Compared the raw and cleaned datasets
• Saved the cleaned data to a new file
• Documented the cleaning decisions

Common Data Cleaning Mistakes

Removing Every Row with a Missing Value

Using df.dropna() without checking the result can remove a large or biased portion of the dataset.

Measure the missingness first and decide which columns are essential.

Filling Every Numerical Gap with the Mean

The mean may be distorted by extreme values and may not represent a skewed distribution. The median is often more robust, but neither choice is automatically correct.

Deleting Every Statistical Outlier

Unusual values may be valid and important. Investigate the source and context before removing them.

Converting Missing Text Values to the String “nan”

Using .astype(str) converts missing values into ordinary text. Prefer pandas’ nullable string type:

df["column"] = df["column"].astype("string")

Cleaning the Test Set Before a Machine-Learning Split

Calculating medians, category mappings, or scaling parameters from the complete dataset can cause data leakage. Fit preprocessing steps on the training data and apply the learned transformation to the validation and test data.

Overwriting the Raw Dataset

Always preserve the original file and export cleaned data under a new name.

Frequently Asked Questions

What is the difference between data cleaning and data wrangling?

Data cleaning focuses on detecting and correcting data-quality problems.

Data wrangling is a broader process that can also include reshaping, joining, aggregating, and transforming data into the structure required for analysis.

How do I find missing values in pandas?

Use:

df.isna().sum()

To calculate percentages:

df.isna().mean().mul(100)

How do I remove duplicate rows in pandas?

Use:

df = df.drop_duplicates()

To detect duplicates based on selected columns:

df = df.drop_duplicates(
    subset=["email"]
)

Should I remove or fill missing data?

The correct decision depends on why the values are missing, how much data is affected, which columns are involved, and how the dataset will be used.

Dropping rows may be appropriate when only a small number of non-critical records are incomplete. Imputation may be better when removing rows would discard too much useful information.

What is the IQR method for outlier detection?

The IQR method uses the middle 50% of the data.

It calculates the first quartile, third quartile, and interquartile range. Values outside 1.5 × IQR below the first quartile or above the third quartile are commonly flagged as potential outliers.

The rule identifies unusual values; it does not prove that they are errors.

How do I check whether a pandas column contains only valid values?

Use .isin() together with .all():

df["status"].dropna().isin(
    ["Active", "Inactive"]
).all()

When is a dataset completely clean?

There is no universal definition of perfectly clean data.

A dataset is ready when it satisfies the documented quality rules required for its intended analysis and any remaining limitations are understood.

Next Steps

You now have a six-step framework for cleaning data with pandas:

1. Remove irrelevant data
2. Find and remove duplicates
3. Repair structural errors
4. Handle missing values
5. Investigate and treat outliers
6. Validate the cleaned dataset

The next phase is exploratory data analysis, where you can use summary statistics and visualisations to understand distributions, relationships, trends, and potential modelling opportunities.