Several Summaries at Once: agg

agg works out several summaries for each group in one table, from a list such as ['sum', 'mean', 'count'] or by named aggregation, which names each new column and the column and summary it comes from; groupby takes a list of columns to group by two things at once, and reset_index or as_index=False turns the group labels back into ordinary columns.

Journeyman · Python · Updated

By

After this lesson you can

  • Work out several summaries for each group at once with agg, using a list or named aggregation
  • Group by two columns, and turn the group labels back into columns with reset_index or as_index=False

Key takeaways

  • agg(["sum", "mean", "count"]) works out all three summaries for each group and hands back one table with a column for each.
  • Named aggregation, such as orders=("order_id", "count"), names the new column and says which column it comes from and which summary to use.
  • groupby(["neighborhood", "size"]) makes one group for each combination that has rows, labeled by both values.
  • reset_index() turns the group labels back into ordinary columns, and as_index=False does the same inside groupby.

Crust & Co. Pizza's owner is planning next summer's menu and asks for one table: for each pizza size, how many orders, how much they sold, and how long they took to deliver. That's three questions about the same groups, and a report usually has a table like this near the top. You'll build summary tables like it for class projects, lab results and any job that ends in "send me the numbers".

The data is the shop's delivery orders, orders.csv: 3,006 orders over 13 weeks, with the columns order_id, ordered_at, neighborhood, size (small, medium, large or party), toppings, total, delivery_minutes, driver and tip.

One groupby, several summaries: agg

With what Grouping with groupby teaches, each question is its own line:

import pandas as pd

orders = pd.read_csv("orders.csv")
by_size = orders.groupby("size")
print(by_size["order_id"].count())
size
large      955
medium    1114
party      283
small      654
Name: order_id, dtype: int64

Two more lines give the sales and the delivery times, as two more separate results, and the owner still wants one table. agg (short for aggregate, which means "summarize many values into one") takes a list of summaries and works out every one of them for each group:

orders.groupby("size")["total"].agg(["sum", "mean", "count"]).round(2)
             sum   mean  count
size
large   24669.58  25.83    955
medium  25522.75  22.91   1114
party   11281.64  39.86    283
small   13027.26  19.92    654

One row per size, one column per summary. A party pizza order averages $39.86, about twice a small one.

Your own column names: named aggregation

A list works on one column at a time, and its columns are named after the summaries, so sum doesn't say what was summed. Named aggregation fixes both. Inside agg, each new column gets a name of your choice, and after the = comes a pair in parentheses: the column to use and the summary.

summary = orders.groupby("size").agg(
    orders=("order_id", "count"),
    sales=("total", "sum"),
    avg_minutes=("delivery_minutes", "mean"),
)
summary.round(2)
        orders     sales  avg_minutes
size
large      955  24669.58        27.41
medium    1114  25522.75        26.88
party      283  11281.64        25.70
small      654  13027.26        27.11

Read the first one aloud as "a column called orders: the order_id column, counted". Three columns, three different source columns, one table, and the names say what each number is. This is the form to reach for in a report.

Grouping by two columns

"Which sizes sell best in each neighborhood?" needs groups made of two values. Give groupby a list of columns:

orders.groupby(["neighborhood", "size"])["total"].sum().head(6)
neighborhood  size
Elm Park      large     6457.62
              medium    7355.85
              party     3432.39
              small     3137.74
Hillside      large     5278.00
              medium    5435.56
Name: total, dtype: float64

Each row is one combination, labeled by both values; pandas leaves the neighborhood blank where it repeats, to keep the labels readable. Row labels on two levels like these are called a MultiIndex. Only combinations that have orders get a row: Westgate has no party pizzas, so the result has 19 rows, not 20.

Labels back into columns: reset_index and as_index=False

The group labels sit at the left as row labels, not as columns. That's fine to read, but saving the table to a file, joining it to another table or charting it all go more smoothly with plain columns. reset_index() moves the labels back into ordinary columns and numbers the rows from 0:

by_both = orders.groupby(["neighborhood", "size"])["total"].sum().reset_index()
by_both.head(6)
  neighborhood    size    total
0     Elm Park   large  6457.62
1     Elm Park  medium  7355.85
2     Elm Park   party  3432.39
3     Elm Park   small  3137.74
4     Hillside   large  5278.00
5     Hillside  medium  5435.56

as_index=False inside groupby gives the same result in one step: "don't make the groups the index". It works with named aggregation too:

orders.groupby("size", as_index=False).agg(
    orders=("order_id", "count"),
    avg_tip=("tip", "mean"),
).round(2)
     size  orders  avg_tip
0   large     955     3.75
1  medium    1114     3.24
2   party     283     5.64
3   small     654     2.90

Use whichever reads better to you; both hand back a table whose group values are a column like any other.

Check yourself

2 questions to try on the lesson page.

So what

One agg call with named columns turns a pile of separate answers into the single, labeled summary table a report needs, and reset_index makes that table ready to save, join or chart.

Key terms

Related