Skip to content

Data Breakdowns

Business questions often require comparisons across groups. You might want to compare sectors, regions, company sizes, high-margin firms against low-margin firms, or even combinations of several of these categories at once. Pandas' grouping operations make it possible to summarise data across one or more of these dimensions before visualising the result, turning a large, detailed dataset into a small, digestible summary table.

A one-way breakdown summarises the data by a single category, such as sector. A two-way breakdown summarises by two categories at once, such as sector and year. A three-way breakdown might combine region, sector, and year together. A four-way breakdown can combine even more classifications, though the result needs to be interpreted with real care, since too many groups at once can quickly make the resulting summary difficult to read and act on.

The purpose of this section is to move beyond single, isolated charts and show how visualisation can be combined with structured aggregation. This combination is highly relevant in business, economics, and management, since so many real decisions depend on comparing performance across organisational units, product groups, regions, or market segments, rather than looking at the dataset as one undifferentiated whole.

Python Code: One-Way Breakdown by Sector

# One-way breakdown: summarise by sector

sector_summary = (
    data
    .groupby("sector")
    .agg(
        total_sales=("sales_million", "sum"),
        average_profit_margin=("profit_margin", "mean"),
        average_employees=("employees", "mean"),
        average_digital_score=("digital_score", "mean")
    )
    .sort_values("total_sales", ascending=False)
)

print(sector_summary)

Expected output / interpretation

The table ranks sectors by total sales and also reports average margin, employees, and digital score.

Why this style of .agg() call is worth learning well: using named arguments like total_sales=("sales_million", "sum") inside .agg() lets you calculate several different statistics on several different columns in one single, clearly readable call, while simultaneously giving each result a clean, descriptive output column name. This is considerably more readable than chaining multiple separate .groupby() calls together, and it scales nicely as the number of summary statistics you need grows.

Python Code: Two-Way Breakdown by Sector and Year

# Two-way breakdown: sector by year

sector_year_sales = data.pivot_table(
    index="year",
    columns="sector",
    values="sales_million",
    aggfunc="sum"
)

print(sector_year_sales.head())

plt.figure(figsize=(10, 6))
sector_year_sales.plot(figsize=(10, 6))
plt.title("Sales by Sector and Year")
plt.xlabel("Year")
plt.ylabel("Sales (£m)")
plt.grid(True)
plt.tight_layout()
plt.show()

Expected output / interpretation

The output creates a sector-year table and visualises how each sector changes over time.

How this builds on the one-way breakdown: pivot_table() here plays a very similar role to the one seen in Section D, but now with sector used as the columns instead of company. This produces a table with years down the rows and one column per sector, which pandas can then plot directly using .plot(), automatically drawing one line per sector without needing an explicit loop this time, since pandas recognises the wide-format structure and handles the looping internally.

Python Code: Three-Way Breakdown

# Three-way breakdown: region, sector and year

region_sector_year = (
    data
    .groupby(["region", "sector", "year"])
    .agg(
        sales=("sales_million", "sum"),
        profit=("profit_million", "sum"),
        employees=("employees", "sum")
    )
    .reset_index()
)

print(region_sector_year.head(15))

Expected output / interpretation

The result summarises sales, profit, and employees by region, sector, and year.

A key detail: .reset_index(). Grouping by three columns at once (region, sector, year) produces a result with all three as a combined, multi-level index by default, which can be awkward to work with directly. .reset_index() converts these grouping variables back into ordinary columns, giving you back a standard, flat DataFrame, which is generally far easier to filter, sort, merge, or pass on to a plotting function.

Why three-way breakdowns need extra care: with three grouping variables combined, the resulting table can grow quite large very quickly, potentially one row for every unique region-sector-year combination that actually exists in the data. It's worth using .head(15) (or similar) while exploring a breakdown like this, and considering whether the full table is genuinely useful, or whether a narrower, more targeted question (for example, focusing on just one region or one sector) would communicate the finding more clearly.

Python Code: Four-Way Breakdown with Business Categories

# Four-way breakdown using categories.
# We create size and profitability groups before grouping.

data["size_group"] = pd.qcut(
    data["employees"],
    q=3,
    labels=["Small", "Medium", "Large"]
)

data["profitability_group"] = pd.qcut(
    data["profit_margin"],
    q=3,
    labels=["Low margin", "Medium margin", "High margin"]
)

four_way = (
    data
    .groupby(["region", "sector", "size_group", "profitability_group"], observed=True)
    .agg(
        observations=("company", "count"),
        average_sales=("sales_million", "mean"),
        average_digital_score=("digital_score", "mean")
    )
    .reset_index()
)

print(four_way.head(20))

Expected output / interpretation

This creates size and profitability groups, then summarises performance by region, sector, size group, and margin group.

Unpacking the two new tools introduced here:

  • pd.qcut() creates categories based on quantiles rather than fixed value thresholds. Setting q=3 splits companies into three roughly equally-sized groups (by count, not by the underlying value range), the smallest third of companies by employee count become "Small", the middle third "Medium", and the largest third "Large". This is often more useful than fixed cutoffs like "under 100 employees = small," since it automatically adapts to whatever range of company sizes actually exists within your specific dataset.
  • observed=True inside .groupby() tells pandas to only include combinations of categories that actually occur in the data, rather than generating a row for every theoretically possible combination of region, sector, size group, and profitability group, many of which may not exist among the real companies in this dataset. Without this option, the resulting table can balloon with a large number of entirely empty rows, making genuine patterns harder to spot amid the clutter.

A word of caution on four-way breakdowns generally: as the number of grouping variables increases, each individual group tends to contain fewer and fewer observations, in the most extreme cases, perhaps just one or two companies per group. The observations column included above exists precisely to help you judge this: a group summarising a single company isn't really telling you much about "small, high-margin, technology firms in the North region" as a general pattern, it's just describing that one particular company. Always check the observation counts before drawing conclusions from a highly detailed breakdown like this one.