GroupBy Analysis by Region and Category

After computing a revenue column, the natural next step is to aggregate it by business dimensions like region and category. The Pandas groupby() call splits the DataFrame into groups, you apply an aggregation function, and Pandas combines the results into a summary table. This split-apply-combine pattern replaces nested SQL GROUP BY queries.

Aggregate revenue by product category to identify which categories drive the most sales. Use .agg() to compute multiple statistics at once — total revenue, order count, and average order value — in a single pass over the data rather than three separate groupby calls.

Pass a list of columns to groupby() to create a two-level summary. The result has a MultiIndex — outer level is region, inner is category. Use .reset_index() to flatten it into a plain DataFrame suitable for further filtering or export to a report.

Absolute revenue numbers are useful, but revenue share shows each category's contribution to the total. Divide the per-category total by the grand total and multiply by 100. The transform('sum') trick broadcasts the grand total back to each row so you can compute the percentage in a single vectorised step.

Identify the top-5 products by total revenue using groupby('product')['revenue'].sum().nlargest(5). The nlargest() method is more concise than sorting and slicing. Knowing the top SKUs guides inventory decisions and helps focus promotional efforts where revenue impact is highest.

A region might have high order count but low average order value, or vice versa. Compute both metrics side by side to distinguish volume-driven regions from value-driven ones. Use agg() with a dict to assign descriptive column names and keep the output readable.

Compute the share of total revenue contributed by each region. Use groupby().sum() and then divide by the scalar total. This makes it easy to answer executive-level questions like "What percentage of our revenue comes from the North region?" in a single DataFrame expression.

Use groupby().filter() to keep only rows belonging to categories whose total revenue exceeds a threshold. This is useful when you want to remove niche categories from a visualisation or model to focus on material segments. The filter receives a group DataFrame and returns a boolean.

Combine two groupby keys — month and category — to track how each category's revenue evolves over time. Pivot the result with unstack('category') to get a matrix where rows are months and columns are categories, making it easy to spot seasonal patterns per category.

Identify the single best-selling product in each region by chaining groupby with idxmax(). Group by region and product to get total revenue per combination, then for each region pick the product index with the maximum revenue. This pattern reveals whether different regions prefer different products.

After computing the region-category summary, export it to Excel with to_excel() so stakeholders who prefer spreadsheets can consume it. Use ExcelWriter to write multiple summary DataFrames to separate sheets in a single workbook. Add a timestamp to the file name so each run produces a versioned output.

Test your understanding of Data Analysis concepts from this lesson.

In this lesson you learned: grouping by one and two keys to compute revenue summaries, computing revenue share percentages and top-N SKUs, and exporting multi-sheet Excel summary reports. Next up we explore monthly trend visualisation with line charts and moving averages.