Lesson 3 — Intermediate

GroupBy, Merge aur Pivot — Summary Tables banao

Raw data se ek line ki summary nikaalna — "Har city ka total kitna?" — yahi groupby hai. Do tables ko jodna — orders + customers — yahi merge. Aur Excel jaisa cross-report — pivot. Teenon ek hi lesson me.

⏱ 30 min• Intermediate• Jupyter / Colab

GroupBy kyun?

WHAT

GroupBy = split → apply → combine. Column ke unique values ke hisaab se data split karo, har group pe function lagao, results wapas jodo. city-wise total isi se banta hai.

WHEN

Har report me: city-wise revenue, month-wise signups, department-wise salary, category-wise margin. Dashboard ka har "summary tile" ek groupby hai.

WHERE

SQL GROUP BY ka pandas avatar. Interview me "SQL aata hai?" ke saath ye practical poocha jaata hai — dono same kaam, syntax alag.

GroupBy + Agg

import pandas as pd

df = pd.DataFrame({
    "city":   ["Lucknow","Kanpur","Lucknow","Varanasi","Kanpur","Lucknow"],
    "month":  ["Jan","Jan","Feb","Feb","Jan","Feb"],
    "amount": [4500, 2300, 6100, 2800, 3400, 1200],
    "orders": [3, 2, 5, 2, 3, 1]})

# Ek column ka summary
print(df.groupby("city")["amount"].sum())
# city
# Kanpur      5700
# Lucknow    11800
# Varanasi    2800

# Multiple aggregations - har column pe alag function
print(df.groupby("city").agg(
    total_amount=("amount", "sum"),
    avg_order=("amount", "mean"),
    n_orders=("orders", "sum")))

# Multi-key groupby (city + month)
print(df.groupby(["city", "month"])["amount"].sum())

# Group-wise row count (value_counts ka groupby version)
print(df.groupby("city").size())

# transform = group value HAR row pe broadcast (shape same rehti hai)
df["city_total"] = df.groupby("city")["amount"].transform("sum")

# filter = poora group hi hata do agar condition fail
high_cities = df.groupby("city").filter(lambda g: g["amount"].sum() > 6000)

agg vs transform: agg ek NROW (groups) ka result deta hai; transform utne hi ROWS jaise input — row pe line-level column chahiye (har order ka % of city total) to transform.

Merge — do tables ka mel

orders = pd.DataFrame({
    "order_id":  [101, 102, 103, 104],
    "customer":  ["Amit", "Priya", "Amit", "Sana"],
    "amount":    [1200, 800, 1500, 900]})

cust = pd.DataFrame({
    "customer": ["Amit", "Priya", "Rahul"],
    "city":     ["Lucknow", "Kanpur", "Lucknow"]})

# inner = sirf jo dono me match
m = orders.merge(cust, on="customer", how="inner")   # 3 rows (Rahul out)

# left = orders ke saare rows, match nahi to NaN
m = orders.merge(cust, on="customer", how="left")

# outer = dono ke sab rows;  right = cust ke sab rows
m = orders.merge(cust, on="customer", how="outer")

# Naam alag hon to
orders.merge(cust, left_on="customer", right_index=True, how="left")

# Multiple keys
big.merge(dim, on=["city", "month"], how="inner")

# concat = join nahi, STACK (rows jodna)
top = pd.concat([df_jan, df_feb], ignore_index=True)

Type yaad rakho: inner = intersection, left = pehla table safe, outer = sab. ETL me 90% kaam left join hai — orders ka har row bachna chahiye, customer detail mile ya na mile.

Pivot_table — Excel wala cross report

# city x month ka matrix (rows=city, cols=month, values=sum)
pt = df.pivot_table(index="city", columns="month",
                    values="amount", aggfunc="sum", fill_value=0)
print(pt)
# month     Feb   Jan
# city
# Kanpur       0  5700
# Lucknow   7300  4500
# Varanasi  2800     0

# Margin ke saath (margins=True last row/col ka total deta hai)
pt = df.pivot_table(index="city", values="amount",
                    aggfunc=["sum", "mean"], margins=True)

# Cross-tab (counts) - chi-square jaise quick views
print(pd.crosstab(df["city"], df["month"]))

Exercise

Question: Har group pe sum/mean jaise summary functions lagane ka pandas method? (3 letters)

Question: Common column se do DataFrame jodne wala pandas function? (5 letters)

Common mistakes

Merge se row explosion: join key UNIQUE nahi to rows multiply (1:N ya N:M). Merge ke baad hamesha shape check karo.
how ka galat choice: inner join se silently data chupke se kat jaata hai — pehle len compare karo.
groupby ke baad index: result ka index ban jaata hai — .reset_index() karo warna pivot/export me dikkat.
columns ki jagah full df: df.groupby("city").sum() non-numeric columns pe error/ignore — apna column explicitly do ([["amount"]]).

Interview question: "merge aur join me farak?" — merge() generic hai (on/left_on/right_on sab chalta hai); join() INDEX pe join karta hai (default inner). Practical me merge zyada use hota hai kyunki column-based kaam aasan hai.

Lesson complete?

Lesson ho gaya? Ab next pe chalte hain — Matplotlib se Graphs.