Grouping, Merging, Pivoting and Time Series in pandas

The real power of pandas appears when you summarise and combine data: total revenue per city, average rating per product and month, orders joined to customers, a monthly sales table like an Excel pivot. This lesson covers groupby with aggregation, merge (SQL-style joins), concat, pivot_table and crosstab, reshaping with melt, and time-series resampling and rolling windows.

groupby: Split, Apply, Combine

df.groupby("city")["revenue"].sum() splits rows into groups by city, applies sum to each group's revenue and combines the results. Group by several columns with a list. agg() computes several statistics at once, and named aggregation — agg(total=("revenue", "sum"), orders=("order_id", "count")) — gives clean column names. transform() returns a result aligned to the original rows (e.g. each order's share of its city's total), and filter() keeps whole groups that satisfy a condition.

Combining DataFrames

pd.merge(left, right, on="key", how=...) joins tables like SQL: inner (only matches), left (all left rows), right and outer (everything). Use left_on/right_on for differently named keys, validate="many_to_one" to catch unexpected duplicates, and indicator=True to see where each row came from. pd.concat([df1, df2]) stacks tables with the same columns (e.g. monthly files).

Pivot Tables, Reshaping and Time Series

pivot_table(index="city", columns="month", values="revenue", aggfunc="sum", fill_value=0, margins=True) builds a spreadsheet-style summary; pd.crosstab counts combinations. melt turns wide tables into long ones (one row per observation), which plotting and grouping prefer. With a datetime column, resample("W") or resample("ME") groups by week or month-end, rolling(7).mean() smooths daily data, and pct_change() gives growth rates.

Examples

groupby with agg, named aggregation, transform and filter

Python

import pandas as pd

orders = pd.DataFrame({
    "order_id": range(1, 9),
    "city": ["Pune", "Mumbai", "Pune", "Delhi", "Mumbai", "Pune", "Delhi", "Mumbai"],
    "category": ["books", "books", "electronics", "books", "electronics", "books", "electronics", "books"],
    "revenue": [450, 899, 1500, 350, 2200, 600, 999, 300],
})
print(orders.groupby("city")["revenue"].sum())
print(orders.groupby(["city", "category"])["revenue"].mean())

summary = orders.groupby("city").agg(
    orders=("order_id", "count"),
    total=("revenue", "sum"),
    average=("revenue", "mean"),
    biggest=("revenue", "max"),
).sort_values("total", ascending=False)
print(summary.round(1))

orders["share_of_city"] = (orders["revenue"] / orders.groupby("city")["revenue"].transform("sum")).round(2)
print(orders[["city", "revenue", "share_of_city"]].head(4))
print(orders.groupby("city").filter(lambda g: g["revenue"].sum() > 2000)["city"].unique().tolist())

Output

city
Delhi     1349
Mumbai    3399
Pune      2550
Name: revenue, dtype: int64
city    category
Delhi   books           350.0
        electronics     999.0
Mumbai  books           599.5
        electronics    2200.0
Pune    books           525.0
        electronics    1500.0
Name: revenue, dtype: float64
        orders  total  average  biggest
city
Mumbai       3   3399   1133.0     2200
Pune         3   2550    850.0     1500
Delhi        2   1349    674.5      999
     city  revenue  share_of_city
0    Pune      450           0.18
1  Mumbai      899           0.26
2    Pune     1500           0.59
3   Delhi      350           0.26
['Pune', 'Mumbai']

Joining tables with merge and stacking with concat

Python

import pandas as pd

customers = pd.DataFrame({"customer_id": [1, 2, 3, 4],
                          "name": ["Asha", "Ravi", "Meera", "Kiran"],
                          "city": ["Pune", "Mumbai", "Pune", "Delhi"]})
orders = pd.DataFrame({"order_id": [101, 102, 103, 104, 105],
                       "customer_id": [1, 2, 1, 3, 9],          # customer 9 does not exist
                       "amount": [450, 899, 300, 1200, 75]})

inner = pd.merge(orders, customers, on="customer_id", how="inner")
print(inner[["order_id", "name", "amount"]])

left = pd.merge(orders, customers, on="customer_id", how="left", validate="many_to_one")
print(left[left["name"].isna()][["order_id", "customer_id"]])

outer = pd.merge(customers, orders, on="customer_id", how="outer", indicator=True)
print(outer["_merge"].value_counts().to_dict())

per_customer = inner.groupby("name", as_index=False)["amount"].sum()
print(per_customer)

january = pd.DataFrame({"month": ["Jan"] * 2, "sales": [100, 200]})
february = pd.DataFrame({"month": ["Feb"] * 2, "sales": [150, 250]})
print(pd.concat([january, february], ignore_index=True))

Output

   order_id   name  amount
0       101   Asha     450
1       102   Ravi     899
2       103   Asha     300
3       104  Meera    1200
   order_id  customer_id
4       105            9
{'both': 4, 'left_only': 1, 'right_only': 1}
    name  amount
0   Asha     750
1  Meera    1200
2   Ravi     899
  month  sales
0   Jan    100
1   Jan    200
2   Feb    150
3   Feb    250

Pivot tables, crosstab, melt, resample and rolling

Python

import numpy as np
import pandas as pd

sales = pd.DataFrame({
    "month": ["Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar"],
    "city": ["Pune", "Mumbai", "Pune", "Mumbai", "Pune", "Mumbai", "Pune"],
    "revenue": [1000, 1500, 1200, 900, 300, 2000, 1100],
})
pivot = sales.pivot_table(index="city", columns="month", values="revenue",
                          aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(pivot[["Jan", "Feb", "Mar", "Total"]])
print(pd.crosstab(sales["city"], sales["month"])[["Jan", "Feb", "Mar"]])

wide = pd.DataFrame({"student": ["Asha", "Ravi"], "maths": [91, 78], "science": [85, 88]})
long = wide.melt(id_vars="student", var_name="subject", value_name="score")
print(long)

rng = np.random.default_rng(1)
days = pd.date_range("2026-01-01", periods=28, freq="D")
daily = pd.Series(rng.integers(80, 120, size=28), index=days, name="visits")
print(daily.resample("W").sum())
print(daily.rolling(7).mean().dropna().round(1).head(3))
print(daily.resample("W").sum().pct_change().round(3).tolist())

Output

month    Jan   Feb   Mar  Total
city
Mumbai  1500   900  2000   4400
Pune    1000  1500  1100   3600
Total   2500  2400  3100   8000
month   Jan  Feb  Mar
city
Mumbai    1    1    1
Pune      1    2    1
  student  subject  score
0    Asha    maths     91
1    Ravi    maths     78
2    Asha  science     85
3    Ravi  science     88
2026-01-04    426
2026-01-11    690
2026-01-18    691
2026-01-25    714
2026-02-01    302
Freq: W-SUN, Name: visits, dtype: int64
2026-01-07    100.6
2026-01-08    103.3
2026-01-09    101.7
Freq: D, Name: visits, dtype: float64
[nan, 0.62, 0.001, 0.033, -0.577]

Common Mistakes

Forgetting as_index=False or reset_index() and then struggling with a grouped index.
Merging on keys with duplicates on both sides and multiplying rows — use validate= to catch it.
Using an inner join by default and silently losing unmatched rows.
Resampling data whose date column is not a DatetimeIndex (set_index or on="date").
Building summaries with loops over unique values instead of groupby.

Key Points to Remember

groupby + agg summarises groups; named aggregation gives clean column names.
transform aligns group results to rows; filter keeps whole groups.
merge joins tables (inner/left/right/outer); concat stacks them.
pivot_table and crosstab build spreadsheet-style summaries; melt makes data long.
resample, rolling and pct_change analyse time series.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local project environment for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.