Class 27 - Filtering, sorting & transforming with Pandas

Date: November 18, 2026

⚠️Homework

Homework 10 due November 29, 2026

‼️Ending Skills

Last week for skills: 6, 7, 8

🆕New Skills

First week for skills: 9

Reference

For all work with tables, refer to the Tables page in the menu at the top of the page! It covers PLL tables, but is good reference regardless.

Skills: None

Notes (review before class)

We continue with the cleaned events DataFrame from the Class 26 reading.

Computing New Columns

Lifting computes a whole column at once, e.g. each order's price before discounts:

TICKET_PRICE = 10.0
events["total"] = events["numtix"] * TICKET_PRICE

A mask and .loc update just some rows, e.g. 10% off with the "birthday" code:

bday_mask = events["discount"] == "birthday"
events.loc[bday_mask, "total"] = events["total"] * 0.90

• The same notation adds a column or updates one (unlike PLL's add_column and transform_column): pandas creates the column if there is none by that name.

Applying a Function

When lifting isn't enough, apply calls a function (a lambda or a named one) on each value of a Series, or, with axis=1, on each row of a DataFrame:

events["valid_email"] = events["email"].apply(lambda e: "@" in e)
events["contact"] = events.apply(lambda r: r["name"] + " <" + r["email"] + ">", axis=1)

• A Series' apply passes single values, like transform_column.
• With axis=1, the function gets a whole row, so a column can be computed (or recomputed) from several of its cells, which transform_column can't do.

Sorting

events.sort_values("numtix")                   # fewest tickets first
events.sort_values("numtix", ascending=False)  # most tickets first

• Like PLL's order_by: it makes a new DataFrame (events doesn't change), in which each row keeps its label.

Aggregating and Grouping

events["numtix"].sum()    # total number of tickets sold
events["numtix"].mean()   # average number of tickets per order

To summarize one column for each value of another (a spreadsheet's "pivot table"), use groupby, rather than filtering to each value and summing:

events.groupby("discount").count()           # how many orders had each code
events.groupby("discount")["numtix"].sum()   # tickets sold with each code

• groupby takes the column whose values group the rows, and returns a GroupBy: select a column of it, and an operation on it is done per group.
• The second line is a Series labeled by discount code: 14 tickets with no code (""), 2 with "birthday", 8 with "student".
• The pandas documentation lists many more GroupBy operations (means, other statistics, ...).

Recap (15 mins)

We continue with the restaurant orders from Class 26: orders is loaded from the CSV file, and first_orders is its first three rows, made by hand. orders is:

         date    dish  quantity order_type
0  2023-07-01   Pasta         2    dine-in
1  2023-07-01   Salad         1    takeout
2  2023-07-02  Burger         3    dine-in
3  2023-07-02   Pasta         2    dine-in
4  2023-07-03   Steak         1    takeout

Here is more PLL table work, done in pandas, with the PLL version in the comments:

PRICES = {"Pasta": 12.0, "Salad": 8.0, "Burger": 10.0, "Steak": 25.0}

def order_total(r: pd.Series) -> float:
    """the price of order r: its quantity times its dish's price"""
    return r["quantity"] * PRICES[r["dish"]]

def takeout_only(t: pd.DataFrame) -> pd.DataFrame:
    """the takeout orders in t"""
    # PLL: t.filter(lambda r: r["order_type"] == "takeout")
    return t[t["order_type"] == "takeout"]

def test_order_total():
    assert order_total(first_orders.iloc[0]) == 24.0   # 2 Pasta
    assert order_total(first_orders.iloc[1]) == 8.0    # 1 Salad

def test_takeout_only():
    assert len(takeout_only(first_orders)) == 1
    assert takeout_only(first_orders).iloc[0]["dish"] == "Salad"

# PLL: orders.order_by("quantity", ascending=False)
by_quantity = orders.sort_values("quantity", ascending=False)

# PLL: orders.add_column("total", order_total)
orders["total"] = orders.apply(order_total, axis=1)

# PLL: orders.transform_column("total", lambda price: price * 0.9)
orders["total"] = orders["total"].apply(lambda price: price * 0.9)

# PLL: filter to each order type, then sum each one's totals
sales = orders.groupby("order_type")["total"].sum()

• What is order_total(orders.iloc[4])? What is sales["takeout"]?
• What are by_quantity.iloc[0]["dish"] and by_quantity.loc[0]["dish"]? Does by_quantity have a total column?
• Why does orders.apply(order_total, axis=1) need axis=1, while orders["total"].apply(...) doesn't? Which apply could be written with lifting instead?

Class Exercises (40 mins)

Filtering

1. Filter your workout DataFrame to show only workouts longer than 45 minutes.
2. From the photos dataset, filter to show only photos with subject "Forest".
3. What happens if you try to filter by a column that doesn't exist? Try it and note the error.

Sorting

1. Sort your workout DataFrame by duration from longest to shortest.
2. Sort the photos dataset by date (ascending), then print the first 5 rows.

Adding new columns (like add_column)

1. Add a column called workout_category that is "long" if duration ≥ 60, "medium" if 30-59, "short" otherwise.
2. For your workouts, add a column called calories_burned that estimates calories as duration * 8.
3. In the photos dataset, add a column called is_landscape that checks if the location contains "Park" or "Garden".

Transforming columns (like transform_column)

1. Convert all workout durations from minutes to hours (divide by 60).
2. In the photos dataset, transform the subject column to be all uppercase.

Combining operations

1. Filter workouts to only "long" category, then sort by calories burned.
2. What is the average duration of workouts in each category?

Wrap-up (5 mins)

• Pandas provides the same table operations you learned with PLL tables: filtering with boolean conditions, sorting by columns, adding new columns with computations, and transforming existing columns.
• The syntax is different (boolean masks vs functions, .sort_values() vs .order_by()), but the concepts and capabilities are the same.