pandas · Chapter 13 of 22
Groups with agg, transform and filter
By the end of this chapter you can give each group several statistics at once with agg, name every result column with named aggregation, put a group's answer back on every row with transform, keep or drop whole groups with filter, use apply when nothing else fits (and know what it costs), and pick each group's top row. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Grouping and combining
- 4 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
Chapter 12 gives one number per group. Kargha's questions want more. Shabnam wants one table about every city: its revenue, how many orders it placed, and what an average order is worth. Harpreet splits a coupon over an order's lines by what each line is worth, so every line needs its order's total beside it. Shabnam asks the weavers to keep the designs that sell on the loom, so she wants the lines of the skus that sold at least so many units. And she wants every line to say how many orders its customer has placed.
With chapter 12 alone, the city table is three groupbys and a dict, and the order totals come out the wrong length. Here is the second one tried on the category codes, the sku's letters SAR, DUP, STL and KUR:
import pandas as pd
lines = pd.read_csv("/workspace/data/orders_site.csv").drop_duplicates()
lines = lines[lines["customer_id"] != "TEST"].dropna(subset=["qty"]).copy()
lines["line_rs"] = lines["price_rs"] * lines["qty"]
lines["code"] = lines["sku"].str.strip().str.upper().str.slice(3, 6)
by_code = lines.groupby("code")
lines["code_rs"] = by_code["line_rs"].sum()
print(lines["code_rs"].head(3))
print(lines["code_rs"].isna().sum())
0 NaN
1 NaN
2 NaN
Name: code_rs, dtype: float64
14073
Every one of the 14,073 values is missing, and there was no error. The sum has 4 values labelled DUP, KUR, SAR and STL; the table's rows are labelled 0, 1, 2. A new column lines up by label (chapter 2), no row is labelled "SAR", so every row gets NaN. This chapter gives each question its own tool, and that one is transform.
The examples use the website's lines as the code above leaves them: no duplicate or test lines, no lines without a qty, line_rs, and the category code.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
city_report.py: the city report
Write city_report(lines) in city_report.py. lines is the website's lines as read_csv reads orders_site.csv, with the exact duplicate, test and cancelled lines and the lines with no qty dropped, the cities cleaned to the 16 names (chapter 10), qty as whole numbers and a column line_rs that is price_rs times qty. The row labels are the file's own. An order (one order_id) can have several lines.
Return a DataFrame labelled by city, with exactly three columns in this order: revenue_rs, the sum of line_rs over the city's lines (decimals); orders, the number of different order_id values of the city (whole numbers); and avg_order_rs, revenue_rs divided by orders, rounded to 2 decimal places. The rows are sorted by revenue_rs, highest first. Named aggregation with agg makes the first two columns in one call.
The Check tries the website's lines and a table of 7 lines in 3 cities.
- Task 2
order_share.py: each line's share of its order
Write line_share(lines) in order_share.py. lines is the same table as in Task 1.
Return a Series with one value for each line, with the table's own labels in the table's order: that line's line_rs divided by the sum of line_rs over all the lines of its order (the same order_id), as decimals. A line that is a whole order on its own has a share of 1.0, and the shares of one order add up to 1. transform on a groupby is the tool for it.
The Check tries the website's lines and a table of 7 lines of 4 orders.
- Task 3
popular.py: the designs that sell
Write popular_lines(lines, min_units) in popular.py. lines is the same table as in Task 1, and min_units is a whole number.
Return a DataFrame of every line, with every column, whose sku sold at least min_units units in the table: the sum of qty over all the lines of that sku is min_units or more. The lines keep their own labels and the table's order. When no sku reaches min_units, return an empty DataFrame with the same columns. filter on a groupby, given a lambda or a function of one group, keeps whole groups.
The Check tries the website's lines with min_units of 20 and 150, and a table of 7 lines with min_units of 3, 2 and 10 (no sku reaches 10).
- Task 4 · interview question
Interview: customer_orders.py, agg or transform
Shabnam wants every line to say how many orders its customer placed. Harpreet's with_order_count(lines) adds a column, but every value in it is a missing value. Fix it so it returns a new DataFrame: the lines, with every column, their own labels and their order, and one more column at the end, customer_orders: how many different order_id values that line's customer (customer_id) has in the table, as whole numbers. The table it was given is not changed.
lines is the same table as in Task 1. The Check also tries a table of 7 lines of 3 customers.
Devika will ask: agg or transform here, and what does the length of each one's result tell you?
What you will learn
- agg: several statistics per group, from a list or a dict.
- Named aggregation: name=("column", "statistic"), one result column each, named by you.
- transform: a group's answer back on every row, the same length as the table.
- filter: keep or drop whole groups by a question about each group.
- apply: your own function of each group's DataFrame, and its cost.
- A group's top row: sort, then the first row of each group, or idxmax with loc.
More in Grouping and combining
Build "Groups with agg, transform and filter" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.