29. Python in Excel

29.5 pandas groupby in Excel: Totals, Several Measures and a Pivot Table

By Ravindra Bagale · Last updated: 7 October 2026

Example first. Rani needs a city summary with three numbers per city: orders, Amount and average Mins, sorted by Amount. In Excel that is three formulas per row or a PivotTable. In a Python cell it is one groupby with agg. The result matches GROUPBY and PIVOTBY from lessons 9.10 and 9.11 to the rupee, which is a good way to trust both.

First look at the data (before → after):

Expected result — City summary (Excel values)

df.groupby("City").agg(Orders=…, Amount=…, AvgMins=…)
.sort_values("Amount", ascending=False)

1. One measure

df.groupby("City")["Amount"].sum()

The groups come out sorted A to Z, because groupby sorts the group keys by default.

2. Several measures, named and sorted

summary = (df.groupby("City")
             .agg(Orders=("Order ID", "count"),
                  Amount=("Amount", "sum"),
                  AvgMins=("Mins", "mean"))
             .sort_values("Amount", ascending=False))
summary

Read Orders=("Order ID", "count") as "make a column called Orders by counting the Order ID column". The same pattern works for "sum", "mean", "min", "max" and "nunique".

3. Filter first, then group (Delivered only)

df[df["Status"] == "Delivered"].groupby("City")["Amount"].sum()

Nagpur and Sambhaji Nagar disappear because they have no Delivered rows to group. Total 2215, the same as =SUMIFS(tblOrders[Amount],tblOrders[Status],"Delivered").

4. A pivot table with totals

pd.pivot_table(df, values="Amount", index="City", columns="Platform",
               aggfunc="sum", fill_value=0, margins=True, margins_name="Total")

Expected output computed by running this exact code locally with pandas on the same fictional data (Python in Excel runs it in the Microsoft Cloud; numbers are the same, the look of the card may differ).

The same numbers three ways

Column totals: Amazon Now ₹537, Blinkit ₹2,333, grand total ₹2,870, exactly as in lesson 9.11.

Microsoft sources (checked 7 October 2026): Python in Excel DataFrames; Get started with Python in Excel.

Practice task (try it before you open the answer):

1. Group by Category and return the total Qty.
2. Group by Platform and return the number of orders and the average Amount, rounded to 1 decimal.
3. Give both results.