Python Data Cleaning - Real-Time Examples

What is Data Cleaning?

Data Cleaning, also called data cleansing, is the process of finding and fixing inaccurate, incomplete, inconsistent, duplicate, or incorrectly formatted data.

Real-world data is rarely perfect. A customer dataset may contain missing phone numbers, duplicate customers, inconsistent names, invalid ages, incorrect dates, or extra spaces. Python and Pandas provide tools to clean this data efficiently.

🔍 Find Problems

Inspect rows, columns, data types, missing values, duplicates, and unusual values.

🧹 Clean Data

Remove duplicates, handle missing values, standardize text, and fix formatting problems.

✅ Validate Data

Check ranges, formats, data types, and business rules after cleaning.

📊 Prepare for Analysis

Produce a reliable dataset that can be used for analysis, reporting, visualization, or machine learning.

1. Data Cleaning Workflow

Raw Data

→

Find Problems

→

Clean Data

→

Validate

→

Analysis Ready

A good cleaning process does not simply delete problematic rows. First understand why the problem exists, then choose an appropriate treatment.

2. Importing Pandas

Pandas is one of the most commonly used Python libraries for cleaning tabular data.

Import Pandas

import pandas as pd

3. Creating a Real-World Dirty Dataset

The following example represents customer information collected from an application. The dataset contains several common data-quality problems.

Name	Age	City	Email	Salary
Rahul	25	Pune	rahul@gmail.com	45000
Priya	30	mumbai	priya@gmail.com	55000
Rahul	25	Pune	rahul@gmail.com	45000
Amit	None	Delhi	amit@gmail.com	60000
Sneha	28	delhi		52000

Python Dataset

import pandas as pd

data = {
    "Name": [
        "Rahul",
        " Priya ",
        "Rahul",
        "Amit",
        "Sneha"
    ],
    "Age": [
        25,
        30,
        25,
        None,
        28
    ],
    "City": [
        "Pune",
        "mumbai",
        "Pune",
        "Delhi",
        "delhi"
    ],
    "Email": [
        "rahul@gmail.com",
        "priya@gmail.com",
        "rahul@gmail.com",
        "amit@gmail.com",
        " "
    ],
    "Salary": [
        45000,
        55000,
        45000,
        60000,
        52000
    ]
}

df = pd.DataFrame(data)

print(df)

4. Inspecting the Dataset

Before cleaning anything, inspect the dataset.

Dataset Inspection

print(df.head())

print(df.shape)

print(df.columns)

print(df.info())

Good Practice: Always understand the structure of your dataset before modifying its values.

5. Finding Missing Values

Missing values are one of the most common data-quality problems.

Check Missing Values

print(df.isnull())

To count missing values in each column:

Count Missing Values

print(df.isnull().sum())

6. Finding Missing Values as Percentages

Percentages can make missing-data problems easier to understand.

Missing Percentage

missing_percentage = (
    df.isnull().sum() / len(df)
) * 100

print(missing_percentage)

7. Removing Rows with Missing Values

dropna() removes rows containing missing values.

dropna()

clean_df = df.dropna()

print(clean_df)

Warning: Do not automatically remove every row with a missing value. You may lose useful information. Consider the meaning and percentage of missing data first.

8. Filling Missing Numeric Values

Numeric missing values can sometimes be replaced with a meaningful statistic such as the mean or median.

Fill with Median

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

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

print(df)

The median is often useful when the data contains extreme values.

9. Filling Missing Values with Mean

Mean Imputation

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

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

print(df)

Remember: Mean replacement is only one possible strategy. The appropriate approach depends on the dataset and the reason values are missing.

10. Filling Missing Text Values

Text columns can be filled with a known label when appropriate.

Fill Text Values

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

print(df)

11. Detecting Duplicate Rows

Duplicate records can lead to incorrect totals and misleading analysis.

Find Duplicates

duplicates = df.duplicated()

print(duplicates)

Count duplicate rows:

Count Duplicates

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

12. Removing Duplicate Rows

drop_duplicates()

df = df.drop_duplicates()

print(df)

You can also remove duplicates based on selected columns.

Duplicate by Email

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

print(df)

13. Removing Extra Spaces

User-entered text frequently contains unnecessary spaces at the beginning or end.

strip()

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

print(df["Name"])

Apply the same technique to other text columns:

Clean Multiple Text Columns

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

14. Standardizing Text Case

Values such as mumbai, Mumbai, and MUMBAI may represent the same city.

Title Case

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

print(df["City"])

Other useful text transformations include:

Text Case Methods

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

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

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

15. Converting Data Types

Correct data types are essential for reliable calculations.

Convert Age to Integer

df["Age"] = df["Age"].astype(int)

print(df.dtypes)

For numeric values stored as text:

Convert to Numeric

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

16. Cleaning Numeric Values Stored as Text

Real datasets may contain values such as "45,000" or "₹50000".

Remove Commas

df["Salary"] = (
    df["Salary"]
    .astype(str)
    .str.replace(",", "", regex=False)
)

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

17. Cleaning Currency Values

Currency Cleaning

df["Salary"] = (
    df["Salary"]
    .astype(str)
    .str.replace("₹", "", regex=False)
    .str.replace(",", "", regex=False)
    .str.strip()
)

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

print(df["Salary"])

18. Converting Dates

Dates should be converted to Pandas datetime values before performing date-based analysis.

Convert Date

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

print(df.dtypes)

Why use errors="coerce"? Invalid date strings are converted to missing datetime values instead of causing the entire conversion to fail.

19. Detecting Invalid Values

Data can be non-missing but still incorrect. For example, an age of -5 or 250 is invalid for a normal customer dataset.

Find Invalid Ages

invalid_age = df[
    (df["Age"] < 0) |
    (df["Age"] > 120)
]

print(invalid_age)

20. Replacing Invalid Values

Once invalid values are identified, they can be replaced with a missing value and handled using an appropriate strategy.

Replace Invalid Ages

df.loc[
    (df["Age"] < 0) |
    (df["Age"] > 120),
    "Age"
] = pd.NA

print(df)

21. Validating Email Addresses

Regular expressions can be used for basic format validation.

Email Validation

email_pattern = r"^[^@\s]+@[^@\s]+\.[^@\s]+$"

valid_email = df["Email"].str.match(
    email_pattern,
    na=False
)

print(valid_email)

Output / Email Validation Series Preview

                        0       True

                        1       True

                        2      False

                        3       True

                        4      False

                        Name: Email, dtype: bool

Note: A format check can identify obviously malformed values, but it cannot prove that an email address actually exists.

22. Handling Empty Strings

An empty string is not necessarily represented as a Pandas missing value. Convert blank strings when appropriate.

Convert Blank Values

df["Email"] = df["Email"].replace(
    r"^\s*$",
    pd.NA,
    regex=True
)

print(df)

Output / Converted DataFrame Preview

DataFrame after replacing blank spaces with pd.NA:

	Name	Email
0	Rahul	rahul@gmail.com
1	Priya	priya@gmail.com
2	Sneha	<NA>

23. Renaming Columns

Standardized column names make data processing easier.

Rename Columns

df = df.rename(columns={
    "Name": "customer_name",
    "Age": "age",
    "City": "city",
    "Email": "email",
    "Salary": "salary"
})

print(df.columns)

Output / Renamed Columns Preview

                        Index(['customer_name', 'age', 'city', 'email', 'salary'], dtype='object')
                    

24. Standardizing Column Names Automatically

For larger datasets, column names can be normalized automatically.

Normalize Column Names

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

print(df.columns)

Output / Standardized Columns Preview

                        Index(['employee_id', 'first_name', 'last_name', 'department', 'salary'], dtype='object')
                    

25. Detecting Outliers

An outlier is a value that is unusually far from most other observations. Outliers may be legitimate or may indicate data-entry problems.

IQR Method

Q1 = df["Salary"].quantile(0.25)
Q3 = df["Salary"].quantile(0.75)

IQR = Q3 - Q1

lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outliers = df[
    (df["Salary"] < lower) |
    (df["Salary"] > upper)
]

print(outliers)

Output / Outliers Preview

Detected Outliers DataFrame:

	Name	Department	Salary
4	Vikram Singh	Executive	550000

Important: Do not automatically delete every outlier. Some outliers represent genuine business events.

26. Filtering Invalid Records

Business rules can be used to identify records that need review.

Business Validation

invalid_records = df[
    (df["Age"] < 18) |
    (df["Salary"] < 0)
]

print(invalid_records)

Output / Invalid Records Preview

Filtered DataFrame:

	Name	Age	Salary
2	Rahul	16	-5000

27. Using replace() for Standardization

The replace() function is useful when a dataset contains different labels for the same value.

Standardize City Names

df["City"] = df["City"].replace({
    "Bombay": "Mumbai",
    "mumbai": "Mumbai",
    "MUMBAI": "Mumbai",
    "delhi": "Delhi"
})

print(df["City"])

Output / Standardized Series Preview

                        0      Mumbai

                        1      Mumbai

                        2       Delhi

                        3      Mumbai

                        4       Delhi

                        Name: City, dtype: object

28. Cleaning Data from a CSV File

A common real-world workflow starts by loading a CSV file.

CSV Cleaning

import pandas as pd

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

print("Before Cleaning:")
print(df.shape)

# Remove duplicates
df = df.drop_duplicates()

# Remove extra spaces
df["Name"] = df["Name"].str.strip()

# Standardize city
df["City"] = df["City"].str.title()

# Convert blank email values
df["Email"] = df["Email"].replace(
    r"^\s*$",
    pd.NA,
    regex=True
)

print("After Cleaning:")
print(df.shape)

Output / Shape Comparison Preview

                        Before Cleaning:

                        (5, 4)

                        After Cleaning:

                        (3, 4)

29. Real-Time Example: Customer Data Cleaning

Imagine an e-commerce company has collected customer information from several registration forms. The data contains inconsistent names, cities, duplicate records, missing ages, and blank email addresses.

Complete Customer Cleaning

import pandas as pd

data = {
    "Name": [
        " Rahul ",
        "Priya",
        "Rahul",
        "AMIT",
        "Sneha "
    ],
    "Age": [
        25,
        30,
        25,
        None,
        28
    ],
    "City": [
        "pune",
        "MUMBAI",
        "Pune",
        "delhi",
        "Delhi"
    ],
    "Email": [
        "rahul@gmail.com",
        "priya@gmail.com",
        "rahul@gmail.com",
        "amit@gmail.com",
        " "
    ]
}

df = pd.DataFrame(data)

print("Original Data:")
print(df)

# 1. Remove extra spaces
df["Name"] = df["Name"].str.strip()
df["City"] = df["City"].str.strip()
df["Email"] = df["Email"].str.strip()

# 2. Standardize text
df["Name"] = df["Name"].str.title()
df["City"] = df["City"].str.title()
df["Email"] = df["Email"].str.lower()

# 3. Convert blank emails to missing values
df["Email"] = df["Email"].replace(
    r"^\s*$",
    pd.NA,
    regex=True
)

# 4. Fill missing age with median
df["Age"] = df["Age"].fillna(
    df["Age"].median()
)

# 5. Remove duplicate customers
df = df.drop_duplicates(
    subset=["Email"],
    keep="first"
)

print("\nCleaned Data:")
print(df)

Output / Data Pipeline Preview

Original Data:

	Name	Age	City	Email
0	Rahul	25.0	pune	rahul@gmail.com
1	Priya	30.0	MUMBAI	priya@gmail.com
2	Rahul	25.0	Pune	rahul@gmail.com
3	AMIT	NaN	delhi	amit@gmail.com
4	Sneha	28.0	Delhi

Cleaned Data:

	Name	Age	City	Email
0	Rahul	25.0	Pune	rahul@gmail.com
1	Priya	30.0	Mumbai	priya@gmail.com
3	Amit	26.5	Delhi	amit@gmail.com
4	Sneha	28.0	Delhi	<NA>

Result: The customer data is standardized, duplicate records are removed, missing age information is handled, and blank email values are represented as missing data.

30. Real-Time Example: Sales Data Cleaning

A sales department receives transaction data where prices contain commas, product names have inconsistent capitalization, and some quantities are missing.

Sales Cleaning

import pandas as pd

data = {
    "Product": [
        "Laptop",
        " laptop ",
        "MOBILE",
        "Mobile",
        "Tablet"
    ],
    "Quantity": [
        10,
        None,
        25,
        20,
        15
    ],
    "Price": [
        "60,000",
        "60,000",
        "25,000",
        "25,000",
        "18,000"
    ]
}

df = pd.DataFrame(data)

# Clean product names
df["Product"] = (
    df["Product"]
    .str.strip()
    .str.title()
)

# Convert price to numeric
df["Price"] = (
    df["Price"]
    .str.replace(",", "", regex=False)
)

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

# Fill missing quantity
df["Quantity"] = df["Quantity"].fillna(0)

# Calculate sales
df["Total"] = (
    df["Quantity"] *
    df["Price"]
)

print(df)

Output / Cleaned Sales Preview

Final Calculated DataFrame Result:

	Product	Quantity	Price	Total
0	Laptop	10.0	60000	600000.0
1	Laptop	0.0	60000	0.0
2	Mobile	25.0	25000	625000.0
3	Mobile	20.0	25000	500000.0
4	Tablet	15.0	18000	270000.0

Business Result: The cleaned dataset can now be used for revenue calculations, product comparisons, reporting, and visualization.

31. Real-Time Example: Employee Data Validation

An HR department wants to verify that employee records satisfy basic business rules.

Employee Validation

import pandas as pd

data = {
    "Employee": [
        "Amit",
        "Priya",
        "Rahul",
        "Sneha"
    ],
    "Age": [
        29,
        31,
        150,
        27
    ],
    "Salary": [
        60000,
        75000,
        -5000,
        65000
    ]
}

df = pd.DataFrame(data)

# Find invalid ages
invalid_age = df[
    (df["Age"] < 18) |
    (df["Age"] > 100)
]

# Find invalid salaries
invalid_salary = df[
    df["Salary"] < 0
]

print("Invalid Age Records:")
print(invalid_age)

print("\nInvalid Salary Records:")
print(invalid_salary)

Output / Validation Preview

Invalid Age Records:

	Employee	Age	Salary
2	Rahul	150	-5000

Invalid Salary Records:

	Employee	Age	Salary
2	Rahul	150	-5000

Result: Invalid records can be sent for manual review instead of silently deleting potentially important information.

32. Real-Time Example: Inventory Data Cleaning

Inventory systems can contain inconsistent product names, negative stock values, and missing prices.

Inventory Cleaning

import pandas as pd

data = {
    "Product": [
        "Laptop",
        " laptop ",
        "Mobile",
        "MOBILE",
        "Monitor"
    ],
    "Stock": [
        20,
        15,
        -5,
        30,
        None
    ],
    "Price": [
        60000,
        60000,
        25000,
        25000,
        22000
    ]
}

df = pd.DataFrame(data)

# Standardize names
df["Product"] = (
    df["Product"]
    .str.strip()
    .str.title()
)

# Replace negative stock with missing
df.loc[
    df["Stock"] < 0,
    "Stock"
] = pd.NA

# Fill missing stock
df["Stock"] = df["Stock"].fillna(0)

print(df)

Output / Cleaned Inventory Preview

Cleaned DataFrame Result:

	Product	Stock	Price
0	Laptop	20.0	60000
1	Laptop	15.0	60000
2	Mobile	0.0	25000
3	Mobile	30.0	25000
4	Monitor	0.0	22000

33. Comparing Data Before and After Cleaning

Before and After

print("Rows before cleaning:", len(df))

df = df.drop_duplicates()

print("Rows after cleaning:", len(df))

print("Missing values:")
print(df.isnull().sum())

print("Data types:")
print(df.dtypes)

Output / Validation Preview

                        Rows before cleaning: 5

                        Rows after cleaning: 3

                        Missing values:

                        Name      0

                        City      0

                        Email     0

                        Age       1

                        dtype: int64

                        Data types:

                        Name      object

                        City      object

                        Email     object

                        Age       float64

                        dtype: object

Tip: Track what changes your cleaning process makes. This improves transparency and makes debugging easier.

34. Creating a Reusable Cleaning Function

If the same type of dataset arrives regularly, put cleaning steps into a reusable function.

Reusable Function

import pandas as pd

def clean_customer_data(df):

    df = df.copy()

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

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

    df["Email"] = (
        df["Email"]
        .str.strip()
        .str.lower()
    )

    df = df.drop_duplicates()

    return df

clean_df = clean_customer_data(df)

print(clean_df)

Output / Function Execution Preview

Returned Cleaned DataFrame:

	Name	City	Email	Age
0	Rahul Sharma	Mumbai	rahul@gmail.com	28
1	Priya Patel	Delhi	priya@yahoo.com	34
2	Amit Kumar	Bangalore	amit@outlook.com	NaN

Benefit: The same cleaning logic can be reused whenever new customer data is received.

35. Reading and Cleaning a CSV File

This example demonstrates a complete CSV cleaning pipeline.

Complete CSV Pipeline

import pandas as pd

# Load data
df = pd.read_csv("customers.csv")

# Inspect
print(df.head())
print(df.info())

# Remove duplicates
df = df.drop_duplicates()

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

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

# Clean email
df["Email"] = (
    df["Email"]
    .str.strip()
    .str.lower()
)

# Handle missing values
df["Age"] = df["Age"].fillna(
    df["Age"].median()
)

# Validate
print("\nMissing Values:")
print(df.isnull().sum())

print("\nFinal Dataset:")
print(df)

Output / Cleaning Results Preview

Missing Values:

                        Name      0

                        City      0

                        Email     0

                        Age       0

                        dtype: int64

Final Dataset:

	Name	City	Email	Age
0	Rahul Sharma	Mumbai	rahul@gmail.com	28.0
1	Priya Patel	Delhi	priya@yahoo.com	34.0
2	Amit Kumar	Bangalore	amit@outlook.com	31.0

36. Saving Cleaned Data

After cleaning, save the processed dataset separately from the original raw data.

Save CSV

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

Save Excel

df.to_excel(
    "customers_cleaned.xlsx",
    index=False
)

37. Common Data Cleaning Problems

Problem	Example	Common Solution
Missing values	Age = NaN	`fillna()` or `dropna()`
Duplicates	Same customer twice	`drop_duplicates()`
Extra spaces	" Rahul "	`str.strip()`
Inconsistent case	"mumbai", "Mumbai"	`str.title()`
Wrong data type	"50000" instead of 50000	`pd.to_numeric()`
Invalid dates	"unknown"	`pd.to_datetime()`
Invalid values	Age = -10	Business-rule validation
Outliers	Extremely large salary	Investigate using statistical methods
Inconsistent labels	"M", "Male", "male"	`replace()` / standardization

38. Data Cleaning Best Practices

Always keep the original raw dataset unchanged.
Inspect data before modifying it.
Understand why a value is missing before filling it.
Do not remove rows simply because they look unusual.
Use consistent text formatting.
Validate data types before calculations.
Use business rules to identify invalid values.
Track how many records are removed or changed.
Separate raw, cleaned, and final datasets when practical.
Create reusable cleaning functions for recurring workflows.
Validate the final dataset after every major transformation.
Document important assumptions used during cleaning.

39. Complete Real-Time Example: E-Commerce Customer Cleaning

This example combines the most important cleaning techniques into one practical e-commerce workflow.

Complete Project

import pandas as pd

# ---------------------------------
# 1. Load customer data
# ---------------------------------

data = {
    "Customer": [
        " Rahul ",
        "Priya",
        "Rahul",
        "AMIT",
        "Sneha ",
        "Vikas"
    ],
    "Age": [
        25,
        30,
        25,
        None,
        28,
        150
    ],
    "City": [
        "pune",
        "MUMBAI",
        "Pune",
        "delhi",
        "Delhi",
        "mumbai"
    ],
    "Email": [
        "rahul@gmail.com",
        "priya@gmail.com",
        "rahul@gmail.com",
        "amit@gmail.com",
        " ",
        "vikas@gmail.com"
    ],
    "Orders": [
        5,
        8,
        5,
        10,
        4,
        7
    ],
    "Amount": [
        15000,
        28000,
        15000,
        42000,
        12000,
        25000
    ]
}

df = pd.DataFrame(data)

print("Original Data")
print(df)

# ---------------------------------
# 2. Remove extra spaces
# ---------------------------------

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

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

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

# ---------------------------------
# 3. Standardize text
# ---------------------------------

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

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

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

# ---------------------------------
# 4. Convert blank email to missing
# ---------------------------------

df["Email"] = df["Email"].replace(
    r"^\s*$",
    pd.NA,
    regex=True
)

# ---------------------------------
# 5. Validate age
# ---------------------------------

df.loc[
    (df["Age"] < 18) |
    (df["Age"] > 100),
    "Age"
] = pd.NA

# ---------------------------------
# 6. Fill missing age
# ---------------------------------

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

# ---------------------------------
# 7. Remove duplicate customers
# ---------------------------------

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

# ---------------------------------
# 8. Validate order values
# ---------------------------------

df = df[df["Orders"] >= 0]

df = df[df["Amount"] >= 0]

# ---------------------------------
# 9. Final validation
# ---------------------------------

print("\nMissing Values:")
print(df.isnull().sum())

print("\nFinal Data:")
print(df)

# ---------------------------------
# 10. Save cleaned data
# ---------------------------------

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

Final Workflow: Raw customer data → remove spaces → standardize text → handle missing values → validate ages → remove duplicates → validate business values → save clean dataset.

40. Important Data Cleaning Rules

Never assume raw data is already clean.
Inspect the dataset before making changes.
Check missing values in every important column.
Identify duplicate records.
Remove unnecessary spaces from text.
Standardize text capitalization.
Convert columns to appropriate data types.
Validate numeric ranges.
Validate dates and other structured values.
Investigate outliers before removing them.
Use business rules for validation.
Keep raw and cleaned datasets separate.
Track important changes made during cleaning.
Validate the final dataset again after cleaning.
Only use cleaned data for downstream analysis when appropriate.

Python Data Cleaning

Clean, validate, and prepare real-world data for reliable analysis.