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
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.