3. pandas: groupby and agg

Grouping is the most common operation in pandas. Rows are split into groups by the value of a column, and a total is calculated for each group:

df = xl("A1:C11", headers=True)
df.groupby("City")["Amount"].sum()                    # sum by city
df.groupby("City")["Amount"].agg(["sum", "count", "mean"])
df.groupby(["City", "Manager"])["Amount"].sum()       # grouping by two keys

The result of agg is a small table: rows are cities, columns are sum, count and mean. In regular Excel you get the same with SUMIF, COUNTIF and AVERAGEIF, and grouping by two keys with SUMIFS. Checking "Python against formulas" catches mistakes in the code, such as a forgotten column or the wrong range in xl().

An important check: the sum of the group totals must equal the overall total. If it doesn't, some rows didn't land in any group (for example, a city was misspelled).