Tutorials › Python › groupby and Pivot Tables

groupby and Pivot Tables

Aggregate data by category with groupby — the Pandas equivalent of Excel pivot tables

If I had to pick the one Pandas function that delivers the most business value — the one that shows up in nearly every real analysis I have ever built — it is groupby(). Not because it is fancy. Because it answers the question every manager asks: "How does this metric break down by category?" Total sales by region. Average order value by product line. Number of active customers by city and quarter. Customer churn rate by acquisition channel. All of these are groupby() problems. One function, infinite applications. I have seen analysts spend forty minutes building a pivot table in Excel to answer a question that groupby().agg() answers in four lines of Python. And when the manager asks "can you break it down by quarter too?" — the Excel analyst starts over. The Python analyst adds one line. The mistake beginners make is calling groupby() and then not knowing what to do with the result. You almost always want three things after a groupby: multiple aggregations in the same call (.agg()), the index reset back to a regular column (.reset_index()), and a sort by the aggregated value. That combination — groupby().agg().reset_index().sort_values() — is the most useful chain in data analysis. Learn it until it is automatic.

Examples

groupby — the analytics workhorse
import pandas as pd

# Group by one column
df.groupby("Region")["Sales"].sum()        # total sales per region
df.groupby("Region")["Sales"].mean()       # avg sales per region
df.groupby("Region")["Sales"].count()      # order count per region
df.groupby("Region")["Sales"].agg(["sum", "mean", "count"])

# Group by multiple columns
df.groupby(["Region", "Quarter"])["Sales"].sum()

# Multiple aggregations on multiple columns
result = df.groupby("Region").agg(
    Total_Sales   = ("Sales", "sum"),
    Avg_Sales     = ("Sales", "mean"),
    Order_Count   = ("OrderID", "count"),
    Max_Sale      = ("Sales", "max"),
)

# Reset index to get a flat DataFrame
result = df.groupby("Region")["Sales"].sum().reset_index()
result.columns = ["Region", "Total_Sales"]

# Sort by aggregated value
result.sort_values("Total_Sales", ascending=False)
💡 reset_index() after groupby converts the result from a Series with multi-index back to a regular DataFrame — always do this before further processing.
pivot_table — spreadsheet-style cross-tabs
import pandas as pd

# pivot_table — equivalent to Excel PivotTable
pivot = pd.pivot_table(
    df,
    values="Sales",           # what to aggregate
    index="Region",           # rows
    columns="Quarter",        # columns
    aggfunc="sum",            # how to aggregate
    fill_value=0,             # replace NaN with 0
    margins=True,             # add Grand Total row/column
    margins_name="Total",
)

#             Q1       Q2       Q3      Total
# Region
# Delhi    450000   520000   380000  1350000
# Noida    320000   290000   340000   950000
# Gurgaon  280000   310000   260000   850000
# Total   1050000  1120000   980000  3150000

Key Points

  • ✓groupby().agg() with named aggregations is the cleanest syntax for multi-column summaries
  • ✓Always reset_index() after groupby to get a flat, workable DataFrame
  • ✓pivot_table() adds margins (grand totals) and handles duplicate combinations automatically
  • ✓groupby preserves NaN by default — set dropna=False to include null keys in groups
  • ✓For time series, groupby("Month") after extracting df["Month"] = df["Date"].dt.month

Practice Question

You want total Sales and average Margin, grouped by Region. Which groupby syntax is correct?

Related Topics

Pandas DataFramesThe core Pandas data structure — a 2D table with rows and columnsMerging DataFramesCombine DataFrames with merge and concat — the Pandas equivalent of SQL JOINsDescriptive Statistics in PythonCalculate mean, median, mode, variance, standard deviation and percentiles