pandas · Chapter 12 of 22

Grouping with groupby

By the end of this chapter you can split a table into groups by one column or several with groupby, compute a statistic of each group, say which labels the result gets and how to make it a plain table again, tell size from count, put two group results side by side, and keep the rows whose key is missing. 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

Almost every question Kargha asks ends in "by something". Shabnam wants the revenue by city, to know where to spend on courier deals. Harpreet pays the payment company a fee on every order, so she wants the orders and the lines by payment type. Shabnam also wants to see what each channel sells: revenue by category and channel. And Gopal plans his packing by the number of lines each city sends.

With chapter 6's masks, "revenue by sku" is a loop: one mask for each sku, one sum for each mask.

import pandas as pd

lines = pd.read_csv("/workspace/data/orders_site.csv").drop_duplicates()
lines = lines[lines["customer_id"] != "TEST"].copy()
lines["line_rs"] = lines["price_rs"] * lines["qty"]
lines["sku"] = lines["sku"].str.strip().str.upper()


def by_mask(lines):
    totals = {}
    for sku in lines["sku"].unique():
        totals[sku] = lines[lines["sku"] == sku]["line_rs"].sum()
    return totals

It gives the right numbers, as a dict. But each of the 120 masks compares all 14,087 lines again, and it is slow. In ipython, against the one line this chapter teaches:

In [7]: %timeit by_mask(lines)
47.1 ms ± 2.32 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [8]: %timeit lines.groupby("sku")["line_rs"].sum()
438 μs ± 9.4 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

About a hundred times faster, and the answer is a Series labelled by sku, ready for chapter 11's sort_values and nlargest.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    city_revenue.py: revenue by city

    Write revenue_by_city(lines) in city_revenue.py. lines is the website's lines as read_csv reads orders_site.csv, with the exact duplicate, test and cancelled lines dropped, the cities cleaned to the 16 names (chapter 10), and a column line_rs that is price_rs times qty (a missing value where qty is blank). The row labels are the file's own.

    Return a Series with one value for each city: the sum of line_rs over that city's lines, as decimals, labelled by the city names from A to Z. Use one groupby: no loop over the cities.

    The Check tries the website's lines, a table of 7 lines in 3 cities, and a table of 1 line.

  2. Task 2

    payment_counts.py: orders and lines by payment type

    Write payment_counts(lines) in payment_counts.py. lines is the same table as in Task 1. An order (one order_id) can have several lines, and a few lines have no payment (a missing value).

    Return a DataFrame with one row for each payment type, labelled by the payment from A to Z, and then, when some lines have no payment, one more row labelled with a missing value (NaN) for them. It has exactly two columns, in this order: orders, the number of different order_id values in that group, and lines, the number of lines in that group, both whole numbers. One groupby on payment gives both.

    The Check tries the website's lines, a table of 7 lines of which one has no payment, and a table of 6 lines that all have one (then there is no NaN row).

  3. Task 3

    category_channel.py: revenue by category and channel

    Write revenue_by_category_channel(sales) in category_channel.py. sales is the three channels' lines that were not cancelled, in one table that Devika made (chapter 15 shows how), with the columns channel (website, marketplace or store), order_id, sku, category, qty and line_rs.

    Return a plain DataFrame with exactly three columns, category, channel and line_rs (the sum of line_rs for that category and channel), in that order, one row for each combination of category and channel that has lines, sorted by category and then by channel, both A to Z, and labelled 0, 1, 2 and so on. The category and the channel must be ordinary columns, not row labels: a groupby on two keys labels its result by pairs of them, a MultiIndex.

    The Check tries the three channels' lines and a small table of 5 lines.

  4. Task 4 · interview question

    Interview: lines_per_city.py, count or size

    Gopal plans his packing by the number of lines each city sends. Harpreet's lines_per_city(lines) gives numbers far too small: Delhi comes out at about a third of the lines he packed, and a city of a small table whose lines have no rating gets 0. Fix it so it returns a Series with one value for each city: how many lines that city has, every line counted whatever is in it, as whole numbers, labelled by the city names from A to Z.

    lines is the same table as in Task 1. The Check also tries a table of 7 lines in 3 cities.

    Devika will ask: why did count and size give different numbers for the same groups?

What you will learn

  • Split, apply, combine: what a groupby does, in three steps.
  • groupby: a plan for the groups, not a result yet.
  • A column, then a statistic: sum, mean, max, nunique and the rest, one value per group.
  • The keys become the index: sorted A to Z; sort=False, as_index=False and reset_index.
  • size against count: every row, or only the values that are there.
  • Several keys and several columns: a MultiIndex to read, and a table of results.
  • Missing keys: dropped by default, kept with dropna=False.

More in Grouping and combining

Build "Grouping with groupby" 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.