29. Python in Excel

29.8 Mini Project: A Weekly City Report with Python in Excel

By Ravindra Bagale · Last updated: 7 October 2026

Example first. Every Monday Shraddha Bagale sends a city report for the previous week: orders, Amount, average delivery time, late orders and each city's share. Last week (9 to 15 November 2026, fictional) there were 16 orders worth ₹5,960. She builds the report once with four Python cells. Next Monday she pastes the new week into the Table, and every number and the chart refresh by themselves. Then she checks two numbers with SUMIFS before sending.

First look at the data (before → after):

Expected result — City report (Excel values)

P1 load + Late flag
P2 groupby City with agg, share %
P3 Platform split
P4 bar chart
Excel check: SUMIFS

The data: tblWeek (16 fictional orders, 9 to 15 November 2026)

P1 (H1): load and flag late orders

df = xl("tblWeek[#All]", headers=True)
df["Late"] = df["Mins"] > 15
df["Late"].sum()

Result: 5 (late orders: AMN-2102, BLK-2104, AMN-2107, BLK-2111, BLK-2115). Booleans add up as 1 for TRUE and 0 for FALSE, which is why .sum() counts them.

P2 (H3): the city report

report = (df.groupby("City")
            .agg(Orders=("Order ID", "count"),
                 Amount=("Amount", "sum"),
                 AvgMins=("Mins", "mean"),
                 LateOrders=("Late", "sum"))
            .sort_values("Amount", ascending=False))
report["Share %"] = (report["Amount"] / report["Amount"].sum() * 100).round(1)
report["AvgMins"] = report["AvgMins"].round(1)
report

Total Amount ₹5,960; the shares add up to 99.9%. pandas .round(1) rounds an exact half to the even digit (0.25 becomes 0.2), while Excel's ROUND rounds a half away from zero (0.25 becomes 0.3). On the rare value that ends exactly in 5, the last digit can differ by 0.1; none of this week's numbers is affected. Mention it in the report notes if your manager compares the two.

P3 (N3): Blinkit vs Amazon Now

df.groupby("Platform")["Amount"].agg(["count", "sum", "mean"]).round(1)

P4 (H12): the chart

report["Amount"].plot(kind="bar", color="#217346")
plt.ylabel("Amount (Rs)")
plt.title("Week 9-15 Nov: Amount by City")

Because report is already sorted by Amount, the bars go from Pune (₹1,490) down to Kolhapur (₹465).

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

Check before sending (Excel formulas next to the Python report)

Next Monday. Replace the rows of tblWeek with the new week (keep the headers). The Table grows or shrinks, xl("tblWeek[#All]", headers=True) reads the new size, and P1 to P4 recalculate in row-major order. Your only manual work: the five checks above and a two-line comment on what changed.

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

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

1. Add an "Avg order" column to the report: Amount divided by Orders, rounded to 0 decimals.
2. Which city has the highest average order?
3. Check it with an Excel formula.