Grouping and Summarizing
Most questions a manager asks are about groups: which neighborhood orders the most, which pizza size sells best on Fridays. Using a pizza shop's delivery orders, group and summarize with groupby and agg, work out shares of a total, build pivot tables, and total sales by week and month.
Updated
Grouping
- Grouping with groupby (Apprentice): groupby sorts a table's rows into groups that share a value, such as a neighborhood, works out one number for each group (a sum, a mean, a count) and hands back one row per group, which answers "which one sold the most?" in a line instead of a filter for every group.
- Several Summaries at Once: agg (Journeyman): 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.
- Shares and Percentages of a Total (Journeyman): A group's total means more beside the whole, so divide each group's total by the grand total and multiply by 100 for its share as a percentage; transform('sum') lines each group's total up with every row, so each row's share of its own group is one division; and shares are rounded to one decimal place only at the end, for people to read.
Pivot Tables
- Pivot Tables and crosstab (Journeyman): pivot_table turns a summary of two columns into a grid, one value down the side (index) and one across the top (columns), with the cells worked out from values by aggfunc; fill_value puts 0 in an empty cell, margins adds the totals, and crosstab counts every combination of two columns, as row percentages with normalize='index'.
Totals by Week and Month
- Totals by Week and Month: resample (Journeyman): Total a column by month with groupby on dt.to_period('M'), and by week with resample('W') on a date index, which labels each week by the Sunday it ends on and keeps every week from the first to the last, so a week with no orders shows as 0 instead of disappearing, while its mean is NaN.
Exercise: Pizza Delivery Report
- Exercise: Pizza Delivery Report (Journeyman): Write five summary functions for a pizza shop's summer of delivery orders, sales by neighborhood, a summary of each size by named aggregation, each neighborhood's share of the orders, a weekday-by-size pivot table with totals and the sales week by week, and let pytest check the table each one returns.