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
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())
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
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))
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
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())
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
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
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.
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