Chapter 8 Exercises: Cleaning Messy Data

How to use these exercises: Work through the sections in order. Parts A and B test core understanding. Parts C and D require writing and running code — have a Jupyter notebook open. Part E asks you to think about the bigger picture of cleaning decisions. Part M (Metacognitive) asks you to reflect on your own learning.

Difficulty key: ⭐ Foundational | ⭐⭐ Intermediate | ⭐⭐⭐ Advanced | ⭐⭐⭐⭐ Extension

Part A: Conceptual Understanding ⭐

These questions check whether you absorbed the core ideas. No code required.

Exercise 8.1 — The five types of mess

List the five types of data quality problems described in this chapter. For each one, give a one-sentence example that's different from the examples in the chapter.

Exercise 8.2 — MCAR, MAR, MNAR

Classify each of the following as MCAR, MAR, or MNAR. Briefly explain your reasoning.

1. A fitness tracker fails to record heart rate during intense exercise because the sensor can't get a reliable reading when there's too much sweat.
2. A spreadsheet was partially corrupted during a file transfer, losing 5% of rows at random.
3. In a salary survey, respondents with higher salaries are more likely to skip the income question.
4. An online survey has missing responses for participants using mobile devices because the form was poorly formatted on small screens.
5. Patients with more severe illness are less likely to complete a follow-up questionnaire because they feel too unwell.

Exercise 8.3 — Cleaning as analysis

In your own words, explain what the chapter means by "cleaning IS analysis." Give one specific example where a cleaning decision would change the conclusions of an analysis.

Exercise 8.4 — Why not just dropna()?

Explain two scenarios where calling df.dropna() would be inappropriate. For each scenario, suggest a better approach.

Exercise 8.5 — Data type consequences

Explain what goes wrong in each of these scenarios:

1. You try to compute the mean of a column, but pandas returns an error because the column is stored as object type.
2. You store ZIP codes as integers and notice that "02134" (Boston) has become "2134."
3. You merge two DataFrames on a "year" column, but get no matches — one DataFrame stores year as int64 and the other as object.

Exercise 8.6 — Outlier or insight?

For each of the following, argue whether the value should be removed as an outlier or kept as a valid data point. There's no single right answer — the goal is to practice reasoning.

1. In a dataset of employee salaries at a small company, the CEO's salary is 10x the median.
2. In a dataset of daily temperatures, one reading shows -40 degrees in a city that normally ranges from 30 to 100 degrees Fahrenheit.
3. In a dataset of website page load times, one page took 45 seconds to load when the average is 2 seconds.
4. In a dataset of ages at a retirement home, one record shows age 23.

Part B: Code Reading and Prediction ⭐⭐

Read each code snippet and predict the output before running it. Then verify in your notebook.

Exercise 8.7 — isnull patterns

import pandas as pd
import numpy as np

s = pd.Series([1, np.nan, 3, None, 5, np.nan])
print(s.isnull().sum())
print(s.notnull().sum())
print(s.dropna().mean())

What three numbers will be printed?

Exercise 8.8 — fillna behavior

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'A': [1, np.nan, 3, np.nan, 5],
    'B': ['x', None, 'y', 'z', None]
})

df_filled = df.fillna({'A': 0, 'B': 'unknown'})
print(df_filled)
print()
print(df)  # Is the original changed?

What will the two print statements show? Will the original df be modified?

Exercise 8.9 — Duplicate detection

import pandas as pd

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Alice', 'Carol', 'Bob'],
    'score': [85, 92, 85, 78, 95]
})

print(df.duplicated().sum())
print(df.duplicated(subset=['name']).sum())

What two numbers will be printed, and why are they different?

Exercise 8.10 — to_numeric with errors

import pandas as pd

s = pd.Series(['10', '20', 'thirty', '40', 'N/A'])

print(pd.to_numeric(s, errors='coerce'))
print(pd.to_numeric(s, errors='coerce').sum())

What will be printed?

Part C: Applied Coding ⭐⭐

Open a Jupyter notebook and solve these problems with code. Each problem builds a skill you'll use in real projects.

Exercise 8.11 — Missing value audit function ⭐⭐

Write a function called missing_report(df) that takes a DataFrame and returns a new DataFrame with columns: column_name, missing_count, missing_pct, dtype. Sort the output by missing_pct descending. Only include columns that have at least one missing value.

Test it on this data:

import pandas as pd
import numpy as np

test_df = pd.DataFrame({
    'id': range(100),
    'age': [np.nan if i % 10 == 0 else i + 20 for i in range(100)],
    'income': [np.nan if i % 5 == 0 else 50000 + i * 100 for i in range(100)],
    'name': ['Alice'] * 100,
    'score': [np.nan if i % 3 == 0 else 75 + (i % 25) for i in range(100)]
})

def missing_report(df):
    missing = df.isnull().sum()
    missing_pct = (df.isnull().mean() * 100).round(2)
    dtypes = df.dtypes

    report = pd.DataFrame({
        'column_name': missing.index,
        'missing_count': missing.values,
        'missing_pct': missing_pct.values,
        'dtype': dtypes.values
    })

    report = report[report['missing_count'] > 0]
    report = report.sort_values('missing_pct', ascending=False)
    return report.reset_index(drop=True)

Exercise 8.12 — Cleaning a price column ⭐⭐

Given this Series of messy prices, write code to convert it to clean numeric values:

prices = pd.Series([
    '$12.99', '$1,299.00', '15.50', '$0.99', 'free',
    'N/A', '$45', '  $99.99  ', None, '1000'
])

Your cleaned Series should have: 12.99, 1299.0, 15.5, 0.99, 0.0, NaN, 45.0, 99.99, NaN, 1000.0

Note: "free" should become 0.0, not NaN.

clean = (
    prices
    .str.strip()
    .str.replace('$', '', regex=False)
    .str.replace(',', '', regex=False)
    .replace({'free': '0', 'N/A': np.nan})
    .pipe(pd.to_numeric, errors='coerce')
)

Exercise 8.13 — Standardizing country names ⭐⭐

Given this Series of inconsistent country names, standardize them to a canonical form:

countries = pd.Series([
    'United States', 'USA', 'US', 'U.S.A.', 'united states',
    'Brazil', 'brasil', 'BRAZIL', 'Brazil ',
    'United Kingdom', 'UK', 'U.K.', 'Great Britain',
    'South Korea', 'Korea, Republic of', 'Republic of Korea',
    None, '', 'Germany', 'germany'
])

Write code that produces a clean Series with consistent names. Handle the blank strings and None values.

# Step 1: Normalize whitespace and case
clean = countries.str.strip().str.title()

# Step 2: Replace blanks with NaN
clean = clean.replace({'': np.nan})

# Step 3: Map variations to canonical names
mapping = {
    'Usa': 'United States',
    'Us': 'United States',
    'U.S.A.': 'United States',
    'Brasil': 'Brazil',
    'Uk': 'United Kingdom',
    'U.K.': 'United Kingdom',
    'Great Britain': 'United Kingdom',
    'Korea, Republic Of': 'South Korea',
    'Republic Of Korea': 'South Korea',
}
clean = clean.replace(mapping)

Exercise 8.14 — Detecting implausible values ⭐⭐

Write a function flag_implausible(df, column, min_val=None, max_val=None) that: 1. Returns a boolean Series where True means the value is outside the plausible range 2. Handles NaN values (they should NOT be flagged as implausible)

Test it:

ages = pd.Series([25, 30, -5, 150, None, 45, 0, 200, 67, np.nan])
print(flag_implausible(ages, min_val=0, max_val=120))

Expected: [False, False, True, True, False, False, False, True, False, False]

def flag_implausible(series, min_val=None, max_val=None):
    flags = pd.Series(False, index=series.index)
    if min_val is not None:
        flags = flags | (series < min_val)
    if max_val is not None:
        flags = flags | (series > max_val)
    # Don't flag NaN as implausible
    flags = flags & series.notna()
    return flags

Exercise 8.15 — Group-based imputation ⭐⭐⭐

Given this dataset of student test scores with some missing values:

students = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Carol', 'Dave', 'Eve',
             'Frank', 'Grace', 'Henry', 'Ivy', 'Jack'],
    'department': ['Math', 'Math', 'Math', 'English', 'English',
                   'English', 'Science', 'Science', 'Science', 'Science'],
    'score': [85, np.nan, 92, 78, np.nan, 82, np.nan, 88, 91, np.nan]
})

Impute missing scores using the median score within each department. Explain why this is better than using the overall median.

students['score_imputed'] = students.groupby('department')['score'].transform(
    lambda x: x.fillna(x.median())
)

Exercise 8.16 — Complete cleaning pipeline ⭐⭐⭐

Clean this messy dataset end to end. Write a cleaning pipeline that handles all five types of data problems. Document each step.

messy = pd.DataFrame({
    'employee_id': [1001, 1002, 1003, 1003, 1004, 1005, 1006, 1007, 1008, 1009,
                    1010, 1002, 1011, 1012, 1013],
    'name': ['Alice Smith', 'bob jones', 'Carol Lee', 'Carol Lee', 'David Kim',
             None, 'Eve Torres', 'FRANK NGUYEN', '  Grace Hall  ', 'Henry Wu',
             'Iris Patel', 'Bob Jones', 'Jack Ryan', 'Kate Mills', 'Leo Park'],
    'department': ['Engineering', 'engineering', 'Sales', 'Sales', 'HR',
                   'Engineering', 'Sales', 'hr', 'Engineering', 'SALES',
                   'Marketing', 'Engineering', 'Marketing', None, 'marketing'],
    'salary': ['75000', '82000', '65000', '65000', 'N/A', '91000',
               '58000', '79000', '88000', '62000', '71000', '82000',
               '67000', '73000', '-5000'],
    'hire_date': ['2020-01-15', '2019-03-22', '2021-06-01', '2021-06-01',
                  '2022-11-10', '2018-07-30', 'June 5, 2023', '2017-09-14',
                  '2020-04-20', '2022-01-01', '2023-03-15', '2019-03-22',
                  '2021-08-08', '2020-12-01', '2019-05-17']
})

Your cleaned output should have: no duplicates, no missing names, proper data types, consistent department names, valid salaries, and parsed dates.

Part D: Debugging and Troubleshooting ⭐⭐

Each problem presents buggy code. Find and fix the error.

Exercise 8.17 — The silent failure

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'temperature': [72.1, np.nan, 68.5, np.nan, 71.3, 69.8]
})

# "Fill missing temperatures with the mean"
df['temperature'].fillna(df['temperature'].mean())

print(f"Missing after fill: {df['temperature'].isnull().sum()}")
# Prints: Missing after fill: 2
# Expected: Missing after fill: 0

Why does the fill appear to have no effect? Fix the code.

df['temperature'] = df['temperature'].fillna(df['temperature'].mean())

Exercise 8.18 — The type surprise

import pandas as pd

df = pd.DataFrame({
    'count': ['10', '20', '30', '40', '50']
})

print(df['count'].sum())
# Prints: 1020304050
# Expected: 150

What went wrong? Fix it.

df['count'] = pd.to_numeric(df['count'])
print(df['count'].sum())  # 150

Exercise 8.19 — The disappearing rows

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Carol', 'Dave', 'Eve'],
    'age': [25, 30, np.nan, 35, 40],
    'score': [85, np.nan, 92, 78, np.nan]
})

# "I only want to drop rows where age is missing"
clean = df.dropna()
print(f"Rows remaining: {len(clean)}")
# Prints: Rows remaining: 2
# Expected: Rows remaining: 4

Why were more rows dropped than expected? Fix it.

clean = df.dropna(subset=['age'])  # Only drop where age is missing

Exercise 8.20 — The merge mystery

import pandas as pd

sales = pd.DataFrame({
    'product': ['Widget', 'Gadget', 'Doohickey'],
    'revenue': [1000, 2000, 1500]
})

costs = pd.DataFrame({
    'product': ['widget', 'gadget', 'doohickey'],
    'cost': [400, 800, 600]
})

merged = sales.merge(costs, on='product')
print(merged)
# Empty DataFrame!

Why is the merge empty? Fix it.

sales['product'] = sales['product'].str.lower()
# OR
costs['product'] = costs['product'].str.title()
# Then merge
merged = sales.merge(costs, on='product')

Exercise 8.21 — The NaN equality trap

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'status': ['active', np.nan, 'inactive', np.nan, 'active']
})

# "Count how many are missing"
missing_count = len(df[df['status'] == np.nan])
print(f"Missing: {missing_count}")
# Prints: Missing: 0
# Expected: Missing: 2

Why does the equality check fail? Fix it.

missing_count = df['status'].isnull().sum()
# OR
missing_count = len(df[df['status'].isnull()])

Part E: Synthesis and Critical Thinking ⭐⭐⭐

These problems require combining multiple concepts and thinking carefully about trade-offs.

Exercise 8.22 — The imputation experiment

Using the following dataset, compute the mean of income using three different missing value strategies: (a) drop missing rows, (b) fill with the overall median, (c) fill with the group median by region. Compare the three results and explain which is most appropriate and why.

import pandas as pd
import numpy as np

data = pd.DataFrame({
    'person_id': range(1, 21),
    'region': ['Urban'] * 10 + ['Rural'] * 10,
    'income': [55000, 62000, np.nan, 71000, 48000,
               np.nan, 83000, 59000, 65000, np.nan,
               28000, np.nan, 32000, np.nan, 25000,
               31000, np.nan, 29000, 33000, 27000]
})

Exercise 8.23 — Design a cleaning strategy

You've been given a dataset of 50,000 restaurant health inspection records with the following issues:

• 12% of rows are missing the inspection_score column
• 3% of rows are exact duplicates
• The restaurant_name column has inconsistent capitalization and abbreviations ("McDonald's", "mcdonalds", "McDonalds", "MCDONALD'S")
• The date column is a mix of "2024-03-15" and "March 15, 2024" formats
• The score column has 47 values that are negative (scores range from 0 to 100)
• The zip_code column is stored as int64, losing leading zeros

Without writing code, describe your cleaning strategy in order. For each step, state what you'd do, why, and what assumptions you're making.

Exercise 8.24 — The ethical audit

A school district provides you with student test score data to analyze achievement gaps by race and socioeconomic status. You discover:

• 18% of scores are missing for Black students, compared to 6% for white students
• 22% of scores are missing for students receiving free/reduced lunch, compared to 4% for others
• Missing scores are concentrated in three schools that had testing irregularities

You've been asked to report the "average test score by race" for a school board meeting. Describe at least three different approaches you could take, the result each would produce, and which you'd recommend and why.

Exercise 8.25 — Sensitivity analysis

Take the student dataset from Exercise 8.15 and run your analysis (average score by department) under three different imputation strategies: (a) drop missing, (b) fill with 0, (c) fill with group median. Create a table comparing the results. How much do the department averages change depending on your strategy? What does this tell you about the sensitivity of your results to cleaning decisions?

Exercise 8.26 — Building a cleaning function

Write a function called clean_survey_data(df) that takes a raw survey DataFrame and returns a cleaned version. The function should: 1. Remove exact duplicate rows 2. Standardize all string columns (strip whitespace, title case) 3. Convert any column named *_date to datetime 4. Convert any column named *_score or *_count to numeric 5. Return a tuple of (cleaned_df, cleaning_report_dict)

The cleaning report should include: rows before/after, duplicates removed, columns converted, and values coerced to NaN.

Part M: Metacognitive Reflection ⭐

Exercise 8.27 — Your cleaning instincts

Think about a time you worked with data (even just a spreadsheet or a list of information) and encountered something messy. How did you handle it at the time? Now that you've read this chapter, what would you do differently?

Exercise 8.28 — What surprised you?

What was the most surprising or counterintuitive idea in this chapter? Why did it surprise you, and how does it change how you think about working with data?

Bonus Challenges ⭐⭐⭐⭐

For students who want to push further.

Exercise 8.29 — Fuzzy deduplication

Research the fuzzywuzzy or rapidfuzz Python library. Use it to find near-duplicate restaurant names in this list:

names = pd.Series([
    "McDonald's", "McDonalds", "Mcdonald's", "mcdonalds",
    "Burger King", "BURGER KING", "Burger king",
    "Wendy's", "Wendys", "wendy's",
    "Taco Bell", "TacoBell", "taco bell",
    "Chick-fil-A", "Chickfila", "Chick Fil A"
])

Group the names into clusters of likely duplicates. What similarity threshold works best?

Exercise 8.30 — Automated data quality report

Write a comprehensive data_quality_report(df) function that produces a full diagnostic including: - Missing values per column (count and percentage) - Duplicate row count - Data type summary - Unique value counts per column - Min/max/mean for numeric columns - Value counts for categorical columns with fewer than 20 unique values - Potential outliers using the IQR method

Format the output as a readable string or as a dictionary of DataFrames. Test it on a dataset of your choice.