Exercises: Applied Data Cleaning & Analysis

Quick Think

This lesson steps up a level from lesson 1 — the goal is to combine skills like handling multiple conditions, cleaning real-world data with missing values, calculating new columns, grouping and summarizing, and merging two tables into a single data flow. This isn't new teaching content — you'll simply reapply the concepts explained in the conditional-selection, handling-missing, operations, groupby, and merging lessons. Since each task builds on the one before it, we recommend working through them in order.

Exercises

Task 1: In a DataFrame with Product, Category, Price, and Qty columns, set some of the Price values to NaN, check the missing count with isna().sum(), then fill them in with fillna() using the column mean. Task 2: Filter rows where Category == "Drink" and Qty > 2 using conditional selection with the & operator. Task 3: Create a new Total column (Price * Qty), then use groupby("Category") to calculate the Total sum for each category. Task 4: Build a new table with Category and Supplier name, then use merge() to combine it with the original DataFrame based on the Category column (try using how="left").

Can you detect missing values and fill them in with fillna()?
Can you combine multiple conditions with the & / | operators to filter data?
Can you use groupby() and merge() together in sequence within a single data flow?

Code Example

python

import pandas as pd
import numpy as np

data = {
    "Product": ["Tea", "Coffee", "Juice", "Cake", "Water"],
    "Category": ["Drink", "Drink", "Drink", "Food", "Drink"],
    "Price": [1200, 1800, np.nan, 2500, 700],
    "Qty": [3, 2, 5, 1, 4]
}
df = pd.DataFrame(data)

# Task 1: missing data
# TODO: df["Price"].isna().sum()
# TODO: df["Price"] = df["Price"].fillna(df["Price"].mean())


# Task 2: multi-condition filter
# TODO: df[(df["Category"] == "Drink") & (df["Qty"] > 2)]


# Task 3: new column + groupby
# TODO: df["Total"] = df["Price"] * df["Qty"]
# TODO: df.groupby("Category")["Total"].sum()


# Task 4: merge
suppliers = pd.DataFrame({
    "Category": ["Drink", "Food"],
    "Supplier": ["ABC Beverage", "XYZ Bakery"]
})
# TODO: pd.merge(df, suppliers, on="Category", how="left")

You should see

You'll end up with a clean DataFrame with no missing values, a filtered set of Drink rows, a table of Total sums by Category, and a merged table that includes the Supplier name.

5-Minute Try

In 5 minutes, run Task 2 and Task 3 back to back, then work out by hand what the Total sum for the Drink category should be in the groupby result and check it against the actual output.

A Quick Warning

Before filling in the mean with fillna(), check whether the column's data type is actually numeric — trying to fill a text column with a mean value will throw an error.