Tutorials › Python › groupby and Pivot Tables in pandas

groupby and Pivot Tables in pandas

Aggregate and summarise data by categories — the most-used pandas operation in reporting

groupby is the pandas equivalent of SQL GROUP BY and Excel pivot tables. It groups rows by one or more columns and applies an aggregation function (sum, mean, count, etc.) to each group. This is the operation data analysts use most in daily work — calculating total sales by region, average salary by department, order count by month. Mastering groupby makes you dramatically more productive.

Examples

groupby fundamentals
import pandas as pd

df = pd.read_csv("sales.csv")

# GROUP BY one column
df.groupby("Region")["Revenue"].sum()
# Region
# Delhi      4523000
# Gurgaon    2187000
# Noida      3091000

# GROUP BY multiple columns
df.groupby(["Region","Category"])["Revenue"].sum()

# Multiple aggregations — agg()
df.groupby("Region").agg(
    Total_Revenue=("Revenue", "sum"),
    Avg_Order=("Revenue", "mean"),
    Order_Count=("OrderID", "count"),
    Max_Sale=("Revenue", "max")
).round(0)

# Reset index to get a flat DataFrame
summary = df.groupby("Region")["Revenue"].sum().reset_index()
# Region  Revenue
# Delhi   4523000  ...
Pivot tables with pandas
# pd.pivot_table — crosstab (like Excel pivot)
pivot = pd.pivot_table(
    df,
    values="Revenue",
    index="Region",
    columns="Category",
    aggfunc="sum",
    fill_value=0    # replace NaN with 0
)
# Category  Electronics  Furniture  Clothing
# Region
# Delhi       1500000     800000    1200000
# Noida       900000      600000     900000

# pd.crosstab — count-based crosstab
pd.crosstab(df["Region"], df["Category"])

# Add margins (totals row and column)
pd.pivot_table(
    df, values="Revenue",
    index="Region", columns="Quarter",
    aggfunc="sum", margins=True
)
💡 Use groupby().agg() for programmatic analysis; pivot_table for Excel-style crosstab views.

Key Points

  • ✓groupby().sum() / .mean() / .count() — the three most common aggregations
  • ✓Use .agg() to apply multiple aggregations in one step and name the output columns
  • ✓Always .reset_index() after groupby to get a clean flat DataFrame for further work
  • ✓fill_value=0 in pivot_table fills missing combinations with 0 instead of NaN
  • ✓margins=True adds a "Total" row and column to pivot tables

Practice Question

You want total Revenue AND order count by Region in one DataFrame. Which approach gives both in one step?