3.4 Data Cleaning Pipelines — Handle Missing Values, Duplicates & Text Data

3.4 Data Cleaning Pipelines with Pandas

Raw datasets are rarely ready for analysis. They may contain missing values, duplicate records, inconsistent text, unnecessary spaces, inconsistent capitalization, and invalid entries.

Data cleaning is the process of identifying and correcting or appropriately handling these problems before analysis.

1. Why Is Data Cleaning Important?

Poor-quality data can produce misleading statistics, incorrect visualizations, and unreliable machine-learning models.

Consider the following values in a City column:

London
 london
London 
LONDON
London

These values may refer to the same city but are represented differently.

A data-cleaning process can standardize them:

London
London
London
London
London

2. Sample Dataset

We will use a deliberately imperfect dataset:

import pandas as pd

data = {
    "Name": [
        "Alice",
        " Brian ",
        "Clara",
        "Alice",
        None
    ],

    "City": [
        "London",
        " london ",
        "LONDON",
        "London",
        "Paris"
    ],

    "Score": [
        85,
        None,
        76,
        85,
        91
    ]
}

df = pd.DataFrame(data)

print(df)

This dataset contains:

• A missing name
• A missing score
• Extra spaces
• Inconsistent capitalization
• A duplicate record

3. Missing Values

A missing value means that information expected for a particular observation is unavailable or not recorded.

pandas commonly represents missing values using NaN or None, depending on the data type and context.

The first step is to identify missing values.

df.isna()

To count missing values in every column:

df.isna().sum()

Example:

Name     1
City     0
Score    1
dtype: int64

4. Calculating Missing-Value Percentage

Counting missing values is useful, but percentages can be more informative when datasets have different sizes.

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

print(missing_percentage)

This calculates the percentage of missing observations in each column.

5. Removing Missing Values with dropna()

The dropna() method removes rows or columns containing missing values according to the selected options.

The simplest form removes rows containing at least one missing value:

cleaned_df = df.dropna()

print(cleaned_df)

If a row contains a missing value, that row is removed.

6. Dropping Rows or Columns

The axis parameter controls whether rows or columns are removed.

Remove rows containing missing values:

df.dropna(axis=0)

Remove columns containing missing values:

df.dropna(axis=1)

7. how Parameter in dropna()

The how parameter controls when a row or column should be removed.

how="any" removes the row if at least one selected value is missing.

df.dropna(
    how="any"
)

how="all" removes a row only when all selected values are missing.

df.dropna(
    how="all"
)

8. Dropping Rows Based on Specific Columns

Sometimes you do not want missing values in every column to cause a row to be removed.

Use subset to specify the columns that matter.

df.dropna(
    subset=["Name"]
)

This removes rows where Name is missing, while missing values in unrelated columns do not determine whether the row is removed.

9. Keeping Rows with Enough Data

The thresh parameter specifies the minimum number of non-missing values required.

df.dropna(
    thresh=3
)

A row is retained if it contains at least three non-missing values.

10. Replacing Missing Values with fillna()

Instead of deleting missing records, you can replace missing values using fillna().

df.fillna(0)

This replaces missing values with 0.

11. Filling Different Columns with Different Values

A dictionary can specify different replacement values for different columns.

df = df.fillna({
    "Score": 0,
    "City": "Unknown"
})

This is safer than applying the same replacement value to every column.

12. Filling Missing Numerical Values

Numerical missing values are sometimes replaced using a representative statistic such as the mean or median.

Fill with the mean:

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

Fill with the median:

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

The median is often useful when the data contains extreme values because it is less sensitive to outliers than the mean.

13. Filling Categorical Missing Values

For categorical data, the most frequent category can sometimes be used as a replacement.

df["City"] = df["City"].fillna(
    df["City"].mode()[0]
)

Another option is to explicitly mark the value:

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

14. Forward Fill

In sequential or time-series data, a missing value may sometimes be filled using the previous valid observation.

df["Value"] = df["Value"].ffill()

This is known as forward filling.

15. Backward Fill

Backward filling uses the next valid observation to fill a missing value.

df["Value"] = df["Value"].bfill()

16. dropna() vs fillna()

17. Identifying Duplicate Records

Duplicate rows can distort counts, averages, frequencies, and other analyses.

Use duplicated() to identify duplicate rows.

df.duplicated()

The result is a Boolean Series:

• True → duplicate row
• False → not identified as a duplicate

Count duplicate rows:

df.duplicated().sum()

18. Removing Duplicate Rows

Use drop_duplicates() to remove duplicate records.

cleaned_df = df.drop_duplicates()

The resulting DataFrame retains one copy of each duplicated row.

19. Finding Duplicates Based on Selected Columns

Sometimes two records should be considered duplicates based on specific identifying columns rather than every column.

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

Remove duplicates based on Student ID:

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

This is particularly useful when a column such as Student ID, Product ID, or Transaction ID is expected to uniquely identify a record.

20. Controlling Which Duplicate to Keep

The keep parameter controls which duplicate occurrence is retained.

Keep the first occurrence:

df.drop_duplicates(
    keep="first"
)

Keep the last occurrence:

df.drop_duplicates(
    keep="last"
)

Remove every occurrence belonging to a duplicate group:

df.drop_duplicates(
    keep=False
)

21. String Cleaning with .str

pandas provides vectorized string operations through the .str accessor.

Example:

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

This applies the string operation to every value in the selected Series.

22. Removing Extra Spaces with strip()

Leading and trailing spaces can cause values that should be equal to appear different.

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

Example:

"  Alice  " → "Alice"
" Brian "  → "Brian"

This is one of the most common text-cleaning operations.

23. Standardizing Letter Case

Use lower(), upper(), or title() through the .str accessor.

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

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

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

Example:

"london" → "London"
"LONDON" → "London"
" london " → "London"

A common normalization pattern is:

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

24. Replacing Text with str.replace()

Use str.replace() to replace unwanted text patterns.

df["City"] = df["City"].str.replace(
    "Ldn",
    "London",
    regex=False
)

The regex=False argument makes the replacement a literal string replacement.

25. Finding Text Patterns with str.contains()

str.contains() checks whether a pattern occurs within string values.

df["City"].str.contains(
    "London",
    case=False,
    na=False
)

Here:

• case=False makes the search case-insensitive.
• na=False treats missing values as False.

26. Checking Prefixes and Suffixes

Use startswith() and endswith() through the .str accessor.

df["Name"].str.startswith(
    "A",
    na=False
)

df["Email"].str.endswith(
    "@example.com",
    na=False
)

These operations are useful when validating or filtering text fields.

27. Measuring Text Length

Use str.len() to determine the length of strings.

df["Name"].str.len()

This can help identify suspicious values such as unusually short or unusually long identifiers.

28. Splitting Text

str.split() can separate strings based on a delimiter.

df["Name"].str.split(
    " "
)

For example, a full name can potentially be separated into components.

You can expand the result into separate columns:

df[
    ["FirstName", "LastName"]
] = df["FullName"].str.split(
    " ",
    n=1,
    expand=True
)

29. Building a String-Cleaning Pipeline

Multiple string operations can be chained together.

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

The operations execute from top to bottom:

1. Remove leading and trailing spaces.
2. Convert text to lowercase.
3. Convert it to title case.

This transforms values such as:

" LONDON "
" london "
"London"

into a consistent representation:

"London"
"London"
"London"

30. Handling Missing Values Before String Operations

String operations need to be handled carefully when a column contains missing values.

For example:

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

pandas string operations generally propagate missing values rather than treating them as ordinary text.

If you need missing values to be treated as empty strings for a particular operation, you can explicitly fill them:

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

31. Complete Cleaning Pipeline

A practical cleaning pipeline may combine several operations.

import pandas as pd

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

# Remove duplicate records
df = df.drop_duplicates()

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

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

# Fill missing numerical values
df["Score"] = df["Score"].fillna(
    df["Score"].median()
)

# Fill missing categorical values
df["City"] = df["City"].fillna(
    "Unknown"
)

print(df)

This is a simple example of a sequential data-cleaning pipeline.

32. Validate After Cleaning

Cleaning is not complete until you verify that the intended changes actually occurred.

print(df.shape)

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

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

print(df.head())

print(df.dtypes)

Validation answers questions such as:

• Did the number of rows change as expected?
• Are missing values still present?
• Are duplicate records still present?
• Are text values standardized?
• Did any data type unexpectedly change?

33. Before vs After Cleaning

34. Understanding the Result of Cleaning Methods

Many pandas methods return a modified object rather than requiring the original DataFrame to be changed automatically.

For example:

cleaned_df = df.dropna()

The result is stored in cleaned_df.

Similarly:

df = df.drop_duplicates()

Here the cleaned result is assigned back to df.

35. Method Chaining for Cleaning

Pandas operations can be chained to create a compact cleaning workflow.

cleaned_df = (
    df
    .drop_duplicates()
    .assign(
        Name=lambda x: x["Name"].str.strip(),
        City=lambda x: (
            x["City"]
            .str.strip()
            .str.title()
        )
    )
)

Method chaining can make complex transformations easier to read when the pipeline is logically organized.

36. Choosing the Right Cleaning Strategy

37. Real-World Cleaning Workflow

A typical Data Science cleaning workflow may look like this:

import pandas as pd

# 1. Load
df = pd.read_csv(
    "customer_data.csv"
)

# 2. Inspect
print(df.shape)
print(df.info())
print(df.isna().sum())

# 3. Remove exact duplicates
df = df.drop_duplicates()

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

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

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

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

# 6. Validate
print(df.shape)
print(df.isna().sum())
print(df.duplicated().sum())

# 7. Preview
print(df.head())

38. Data Cleaning Decisions That Require Care

Some cleaning decisions cannot be made safely using a universal rule.

Do not automatically:

• Delete every row containing a missing value.
• Replace every missing numerical value with zero.
• Replace every missing value with the mean.
• Remove every duplicate without checking its meaning.
• Convert every text value to lowercase without considering whether capitalization has meaning.
• Modify identifiers merely to make them look consistent.

39. Pandas Data Cleaning Interview Questions

df.isna().sum()

df = df.drop_duplicates()

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

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

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

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

40. Examination Questions

Multiple Choice Questions

Short Answer Questions

1. Define data cleaning.
2. Why is data cleaning important before analysis?
3. Differentiate between dropna() and fillna().
4. What is the purpose of duplicated()?
5. What is the purpose of drop_duplicates()?
6. Explain the use of the .str accessor.
7. Write a statement to remove leading and trailing spaces from the Name column.
8. Write a statement to convert the City column to title case.

41. Practical Examination Questions

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

df = df.dropna()

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

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

df = df.drop_duplicates()

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

df = df.drop_duplicates()

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

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

42. Common Data Cleaning Mistakes

43. Data Cleaning Checklist

45. Key Takeaways

• Data cleaning prepares raw data for reliable analysis.
• Use isna() and isna().sum() to identify missing values.
• Use dropna() when incomplete records can be safely removed.
• Use fillna() when missing information should be replaced using a justified strategy.
• Use duplicated() to detect duplicate records.
• Use drop_duplicates() to remove duplicate records.
• Use the .str accessor for vectorized text operations.
• str.strip() removes leading and trailing whitespace.
• str.lower(), str.upper(), and str.title() help standardize text case.
• str.replace() can correct known text patterns.
• Always validate the dataset after cleaning.