Course topics

By WebNest Studio Engineering Team

Python Tutorial

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.

Why it matters

"Total sales per region", "average salary by department" and "orders joined with their customers" are the questions analysis exists to answer. groupby and merge are the pandas equivalents of SQL's GROUP BY and JOIN, and they are the operations interviewers most often ask candidates to write.

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.

Exercise

Print the total amount for each region as a dictionary. Then merge the sales with the table of managers and print the manager responsible for the largest single sale.

Expected output: {'north': 420, 'south': 450} then Ravi

Python · starter code
import pandas as pd

sales = pd.DataFrame({
    "region": ["north", "south", "north"],
    "amount": [120, 450, 300],
})
managers = pd.DataFrame({
    "region": ["north", "south"],
    "manager": ["Asha", "Ravi"],
})

# TODO: group by region, total the amount, and print the result as a dictionary

# TODO: merge sales with managers on region,
#       then print the manager of the row with the largest amount
Show hints
  • sales.groupby("region")["amount"].sum() gives a Series; to_dict() converts it.
  • After sales.merge(managers, on="region"), merged["amount"].idxmax() gives the index of the largest amount.
Show solution
Python · solution
import pandas as pd

sales = pd.DataFrame({
    "region": ["north", "south", "north"],
    "amount": [120, 450, 300],
})
managers = pd.DataFrame({
    "region": ["north", "south"],
    "manager": ["Asha", "Ravi"],
})

print(sales.groupby("region")["amount"].sum().to_dict())

merged = sales.merge(managers, on="region")
print(merged.loc[merged["amount"].idxmax(), "manager"])

Quick quiz

1. Which three steps does groupby perform?

  • A. Split the rows into groups, apply a function to each, combine the results
  • B. Sort, filter, merge
  • C. Read, clean, write
  • D. Join, pivot, plot
Show answer

A. This is known as split-apply-combine.

2. What kind of join does merge perform by default?

  • A. Left
  • B. Inner
  • C. Outer
  • D. Cross
Show answer

B. Only rows whose key appears in both tables are kept. Use how="left" to keep every row of the left table.

3. What is the difference between merge and concat?

  • A. There is none
  • B. merge only works on Series
  • C. concat joins on keys
  • D. merge joins tables on matching key values; concat stacks tables on top of or beside each other
Show answer

D. concat is used to combine files that have the same columns.

Interview questions

What are the types of join available in merge?

Show answer

inner keeps only the rows whose key is in both tables. left keeps every row of the left table, with missing values where the right has no match, and right does the reverse. outer keeps all rows from both. The type is chosen with the how argument, and the key with on, or with left_on and right_on when the column names differ.

What is the difference between agg and transform after a groupby?

Show answer

agg reduces each group to one row, so the result has one row per group; it is used for summaries such as totals and averages. transform returns a result with the same number of rows as the original, with each row receiving the value computed for its group; it is used to add a column such as each row's share of its group total.

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 in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

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

These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering