How to Clean Data in Python with Pandas: 6-Step 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.

In this Python pandas data cleaning tutorial, you will learn how to clean data with pandas using a practical six-step workflow. The process covers removing irrelevant data, finding duplicates, fixing structural errors, handling missing values, investigating outliers, and validating the final dataset.

The six steps are:

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 pandas DataFrame before visualisation, statistical analysis, or machine learning.

What Is Data Cleaning in Python?

Data cleaning in Python is the process of detecting and correcting inaccurate, incomplete, inconsistent, duplicated, or poorly formatted data. With pandas, you can inspect, transform, validate, and export cleaned datasets using a consistent workflow.

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 Is Important for Data Analysis

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.

Set Up Python and pandas for Data Cleaning

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.

How to 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 Local CSV File with pandas

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 from Google Drive in 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.

Keep a Copy of the Original Raw 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")

How to Inspect a pandas DataFrame Before Cleaning

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

Preview Rows with head() and sample()

df.head()

To inspect random rows instead of only the first five:

df.sample(5, random_state=42)

Check Rows and Columns with shape

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

Check Column Names and Data Types with info()

df.info()

This displays:

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

Review Summary Statistics with describe()

For numerical columns:

df.describe()

For numerical and categorical columns:

df.describe(include="all")

Find Missing Values in pandas

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
)

Find Duplicate Rows in pandas

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.

6-Step Data Cleaning Process in Python with pandas

A practical pandas data cleaning workflow usually starts with removing irrelevant fields and duplicates, then standardising structure and data types, handling missing values, investigating outliers, and validating the result.

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 Columns and 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 and Remove 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 Duplicates in pandas

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 Specific 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: Fix Structural Errors and Data Types

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 and Standardise 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 Categorical Values

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 Columns to Numeric Data

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 Columns to Datetime

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 in pandas

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 Time-Series or Ordered 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 Handling Missing Values

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: Detect and Handle Outliers in Python

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 Cleaned Data in pandas

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 Data 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.

Validate Data with Python Assertions

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 Data 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.

How to Save Cleaned Data with pandas

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.

Complete 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.

pandas 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 pandas Data Cleaning Mistakes

Dropping Every Row with Missing Data

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 Missing Number 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.

Removing Every Statistical Outlier

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

Converting Missing 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")

Causing Data Leakage 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 Original Raw Dataset

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

pandas Data Cleaning FAQ

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 Validate Values in a pandas Column?

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

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

When Is a Dataset Clean Enough for Analysis?

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: Exploratory Data Analysis in Python

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.