Business ke zyadatar sawaal aise hote hain: har city ka total revenue kitna?, kaunsi category sabse zyada bikti hai? In sawaalon ka jawab hai GroupBy. SQL ka GROUP BY aata hai to ye aur bhi aasaan lagega.
Setup
from io import StringIO
import pandas as pd
csv_data = """order_id,date,city,category,product,quantity,price
1001,2026-01-05,Delhi,Electronics,Headphones,2,1500
1002,2026-01-06,Mumbai,Clothing,T-Shirt,3,499
1003,2026-01-06,Delhi,Clothing,Jeans,1,1299
1004,2026-01-07,Bangalore,Electronics,Mouse,4,650
1005,2026-01-08,Mumbai,Grocery,Rice 5kg,2,420
1006,2026-01-09,Pune,Electronics,Keyboard,1,
1007,2026-01-10,Delhi,Grocery,Tea 1kg,5,380
1008,2026-01-11,Bangalore,Clothing,Jacket,1,2499
1009,2026-01-12,Pune,Grocery,Oil 1L,3,160
1010,2026-01-12,Mumbai,Electronics,Charger,2,899"""
df = pd.read_csv(StringIO(csv_data), parse_dates=["date"])
df["price"] = df["price"].fillna(1199)
df["revenue"] = df["quantity"] * df["price"]
print(df[["city", "category", "product", "revenue"]])
Output
city category product revenue
0 Delhi Electronics Headphones 3000.0
1 Mumbai Clothing T-Shirt 1497.0
2 Delhi Clothing Jeans 1299.0
3 Bangalore Electronics Mouse 2600.0
4 Mumbai Grocery Rice 5kg 840.0
5 Pune Electronics Keyboard 1199.0
6 Delhi Grocery Tea 1kg 1900.0
7 Bangalore Clothing Jacket 2499.0
8 Pune Grocery Oil 1L 480.0
9 Mumbai Electronics Charger 1798.0
GroupBy ka basic idea: split → apply → combine
Data ko groups me baanto (split), har group pe calculation karo (apply), aur result jodo (combine):
Example
city_revenue = df.groupby("city")["revenue"].sum().sort_values(ascending=False)
print(city_revenue)
Output
city
Delhi 6199.0
Bangalore 5099.0
Mumbai 4135.0
Pune 1679.0
Name: revenue, dtype: float64
Ek saath kai calculations: agg()
Named aggregation se result ke columns ke naam bhi khud de sakte hain:
Example
summary = df.groupby("category").agg(
orders=("order_id", "count"),
total_qty=("quantity", "sum"),
revenue=("revenue", "sum"),
avg_price=("price", "mean"),
).round(1)
print(summary)
Output
orders total_qty revenue avg_price
category
Clothing 3 5 5295.0 1432.3
Electronics 4 9 8597.0 1062.0
Grocery 3 10 3220.0 320.0
Do columns pe group
Example
print(df.groupby(["city", "category"])["revenue"].sum().reset_index())
Output
city category revenue
0 Bangalore Clothing 2499.0
1 Bangalore Electronics 2600.0
2 Delhi Clothing 1299.0
3 Delhi Electronics 3000.0
4 Delhi Grocery 1900.0
5 Mumbai Clothing 1497.0
6 Mumbai Electronics 1798.0
7 Mumbai Grocery 840.0
8 Pune Electronics 1199.0
9 Pune Grocery 480.0
reset_index() group wale columns ko wapas normal columns bana deta hai — aage kaam karna aasaan ho jaata hai.
merge() — do tables jodna
Maan lijiye ek alag table me city ka region hai. SQL ke JOIN ki tarah merge() se dono tables jud jaati hain:
Example
regions = pd.DataFrame({
"city": ["Delhi", "Mumbai", "Bangalore", "Pune", "Chennai"],
"region": ["North", "West", "South", "West", "South"],
})
merged = df.merge(regions, on="city", how="left")
print(merged[["order_id", "city", "region", "revenue"]])
print()
print(merged.groupby("region")["revenue"].sum())
Output
order_id city region revenue
0 1001 Delhi North 3000.0
1 1002 Mumbai West 1497.0
2 1003 Delhi North 1299.0
3 1004 Bangalore South 2600.0
4 1005 Mumbai West 840.0
5 1006 Pune West 1199.0
6 1007 Delhi North 1900.0
7 1008 Bangalore South 2499.0
8 1009 Pune West 480.0
9 1010 Mumbai West 1798.0
region
North 6199.0
South 5099.0
West 5814.0
Name: revenue, dtype: float64
| Result me kya aata hai | SQL me |
|---|---|---|
| Sirf wo rows jo dono tables me match karein | INNER JOIN |
| Left table ki saari rows + match wali info | LEFT JOIN |
| Right table ki saari rows | RIGHT JOIN |
| Dono tables ki saari rows | FULL OUTER JOIN |
pivot_table() — Excel wala pivot
Example
pivot = df.pivot_table(values="revenue", index="city", columns="category",
aggfunc="sum", fill_value=0)
print(pivot)
Output
category Clothing Electronics Grocery
city
Bangalore 2499.0 2600.0 0.0
Delhi 1299.0 3000.0 1900.0
Mumbai 1497.0 1798.0 840.0
Pune 0.0 1199.0 480.0
concat() — rows jodna
Naye mahine ka data aaya? concat() se purani table ke neeche jod dijiye:
Example
new_orders = pd.DataFrame({
"order_id": [1011, 1012],
"city": ["Delhi", "Pune"],
"category": ["Clothing", "Grocery"],
"revenue": [1998.0, 760.0],
})
all_orders = pd.concat([df[["order_id", "city", "category", "revenue"]], new_orders],
ignore_index=True)
print(all_orders.tail(4))
print("Rows:", len(all_orders))
Output
order_id city category revenue
8 1009 Pune Grocery 480.0
9 1010 Mumbai Electronics 1798.0
10 1011 Delhi Clothing 1998.0
11 1012 Pune Grocery 760.0
Rows: 12
Pandas ko aur gehraai se seekhna ho to ye guide dekhiye: Pandas Data Manipulation — Indexing, GroupBy, Merge & Reshape.