Pandas — Series, DataFrame, indexing, groupby, merge, pivot: Worked examples

5.4.21 · D3 · Coding › Scientific Computing (Python) › Pandas — Series, DataFrame, indexing, groupby, merge, pivot

The scenario matrix

Pandas mein har operation ke kuch "edge conditions" hote hain jo output badal dete hain. Neeche full grid hai. Har cell ek scenario class hai; aage ke worked examples us cell ke saath tagged hain jo woh cover karta hai.

Case class	Tricky sub-cases	Example jo cover karta hai
Alignment (`+`)	fully matched · partially matched · disjoint (all NaN)	Ex 1
Indexing slices	`.loc` inclusive vs `.iloc` exclusive · label ≠ position	Ex 2
Boolean masking	mask kuch select kare · mask kuch na select kare (empty frame)	Ex 3
GroupBy	normal groups · ek row wala group · `NaN`/`None` key dropped	Ex 4
GroupBy multi-agg	ek saath kai functions · missing data par count vs size	Ex 5
Merge `how`	inner ( $\cap$ ) · left · right · outer ( $\cup$ ) · duplicate keys → row explosion	Ex 6, Ex 7
Merge edge cases	overlapping non-key columns (`suffixes=`) · multi-key joins	Ex 8
Pivot	unique cells (plain pivot) · repeated cells → must aggregate	Ex 9
Word problem	end-to-end: merge → groupby → pivot on real data	Ex 10
Exam twist	chained-indexing trap + degenerate empty result	Ex 11

Ex 1 — Alignment: matched, partial, disjoint

Worked example Teen Series ko teen alag label overlaps ke saath add karo

import pandas as pd
import numpy as np
a = pd.Series([1, 2, 3], index=["x", "y", "z"])
b = pd.Series([100, 200],  index=["y", "x"])   # partial overlap
c = pd.Series([7, 8],      index=["p", "q"])   # ZERO overlap with a

a + b aur phir a + c compute karo.

Forecast: kaun se labels numbers ke roop mein aayenge, aur kaun se NaN ke roop mein? (Alignment cell cover karta hai)

Step 1 — a + b ke liye labels ka union lo. a ke labels hain ${x, y, z}$ , b ke labels hain ${y, x}$ . Union = ${x, y, z}$ (dono sides se har naam). Yeh step kyun? Pandas "first-to-first" add nahi karta. Pehle woh dono Series ko union ( $\cup$ ) of their index labels par align karta hai — yahi label axis ka poora point hai (dekho Tidy Data).

Step 2 — Har label fill karo; missing partner → NaN.

$x$ : dono ke paas hai → $1 + 200 = 201$
$y$ : dono ke paas hai → $2 + 100 = 102$
$z$ : sirf a ke paas hai → NaN

Yeh step kyun? Kyunki Step 1 ne output index ko union fix kar diya, result mein har union label ke liye ek slot zaroor hoga — including labels jo sirf ek side par hain. Ek one-sided label ka koi partner nahi hai add karne ke liye, aur pandas koi number invent karne se mana karta hai, toh us slot ka honest answer hai NaN. Short mein: union output ⇒ NaN for every unmatched label (dekho Missing Data / NaN).

Step 3 — a + c karo (disjoint case). Union = ${x, y, z, p, q}$ . Koi label dono mein nahi hai, toh har cell NaN hai. Yeh step kyun? Yeh alignment ka degenerate extreme hai — zero intersection ( $\cap$ empty hai). Result mein 5 rows hain, sab NaN. Yeh unhe surprise karta hai jo error expect karte hain; pandas ek valid but numbers-empty Series return karta hai. Dekho Missing Data / NaN.

Verify:

a + b  ->  x: 201.0   y: 102.0   z: NaN
a + c  ->  x,y,z,p,q  all NaN     (5 labels, 0 numbers)

Sanity check: a+b mein non-NaN cells ki sankhya = intersection ka size $∣ {x, y, z} \cap {y, x} ∣ = 2$ . ✓

Ex 2 — `.loc` vs `.iloc` jab label ≠ position

Worked example Same slice, do alag answers

df = pd.DataFrame({"v": [10, 20, 30, 40]}, index=[100, 200, 300, 400])

df.loc[100:300] aur df.iloc[0:2] evaluate karo.

Forecast: har ek kitni rows return karta hai? (Indexing-slices cell cover karta hai)

Step 1 — .loc[100:300] ko labels ke roop mein padho. .loc naam tag se slice karta hai. Yeh row labelled 100 se row labelled 300 tak jaata hai, endpoint include karke. Yeh step kyun? Labels necessarily numbers nahi hote — "one past the stop" ka matlab string label ke liye nahi banta, isliye pandas label slices ko named endpoint include karne ke liye define karta hai. Return hone wali rows: labels $100, 200, 300$ → 3 rows.

Step 2 — .iloc[0:2] ko positions ke roop mein padho. .iloc tags ignore karta hai aur Python list ki tarah $0, 1, 2, \dots$ count karta hai, stop exclude karke. Positions $0, 1$ → labels $100, 200$ → 2 rows. Yeh step kyun? Yeh parent ke mistake box ka exact trap hai: default index ke saath dono accident se coincide karte hain; yahan labels ( $100 \dots$ ) ≠ positions ( $0 \dots$ ) toh difference visible hai.

Verify:

df.loc[100:300]  -> labels 100,200,300  (3 rows, values 10,20,30)
df.iloc[0:2]     -> labels 100,200       (2 rows, values 10,20)

Count check: loc = 3, iloc = 2, difference exactly 1 (included endpoint). ✓

Ex 3 — Boolean masking: kuch rows vs koi nahi

Worked example Ek mask jo match kare, aur ek mask jo kuch match na kare

df = pd.DataFrame({"age": [25, 32, 47]}, index=["u1", "u2", "u3"])

df[df["age"] > 30] aur df[df["age"] > 100] evaluate karo.

Forecast: doosra wala — error, ya empty table? (Boolean-masking cell cover karta hai)

Step 1 — > 30 ke liye mask banao. df["age"] > 30 ek boolean Series hai [False, True, True] jo ["u1","u2","u3"] ke saath aligned hai. Yeh step kyun? Mask khud ek label-aligned Series hai; pandas sirf woh rows rakhta hai jahan mask True hai. Result: u2 (32), u3 (47) → 2 rows.

Step 2 — Degenerate mask > 100. Mask hai [False, False, False]. Koi row survive nahi karta. Yeh step kyun? Humhe empty case jaanna chahiye: pandas ek valid DataFrame return karta hai with 0 rows lekin same columns ke saath — yeh raise nahi karta. Iske upar downstream .mean() NaN dega, crash nahi.

Verify:

df[df["age"] > 30]   -> 2 rows (u2, u3)
df[df["age"] > 100]  -> 0 rows, columns still ["age"]

Sanity: har result ki len = mask mein True ki sankhya = 2 aur 0. ✓

Ex 4 — GroupBy ek akele group aur `NaN` key ke saath

Neeche ki figure is example ke liye puri split-apply-combine journey dikhati hai. Left se right padho: left par hain chaar raw rows (key ke hisaab se colour-coded); arrows har row ko uske bucket mein le jaate hain (magenta box N ke liye, violet box S ke liye); dashed grey arrow dikhata hai woh row jiska key None hai jo throw away ho raha hai; har box ke andar aap collected values aur unka mean computed dekh sakte ho. Takeaway: None ki key kabhi kisi bucket tak nahi pahunchti, aur ek single row wala bucket (S) bilkul valid hai.

Figure — Pandas — Series, DataFrame, indexing, groupby, merge, pivot

Worked example Split-apply-combine jab ek group mein ek row ho aur ek key missing ho

df = pd.DataFrame({
    "region": ["N", "N", "S", None],   # None is coerced to NaN here
    "rev":    [10,  30,  5,   99]})
df.groupby("region")["rev"].mean()

Forecast: kya None-key row (rev=99) output mein kahin dikhi? (GroupBy cell cover karta hai)

Step 1 — Rows ko key ke hisaab se buckets mein split karo (figure mein magenta aur violet boxes). Pandas map banata hai {N:[0,1], S:[2]}. None key wali row drop ho jaati hai — dashed grey arrow follow karo — default groupby woh rows ignore karta hai jiska key NaN/None ho. (Is object-typed key column mein pandas Python None ko missing marker NaN treat karta hai, toh woh same drop rule follow karta hai.) Yeh step kyun? Missing key ka matlab hai "hum nahi jaante yeh row kis group mein hai", toh ise kisi bucket mein place nahi kiya ja sakta. Yeh ek Split-Apply-Combine design choice hai (use karo dropna=False ise apne group ke roop mein rakhne ke liye). Dekho Missing Data / NaN.

Step 2 — Har bucket par .mean() apply karo.

N: $(10 + 30) /2 = 20.0$
S: single row → $5/1 = 5.0$ (ek-element group bilkul legal hai)

Yeh step kyun? Function independently har bucket ki sirf values par run hota hai — dropped None row (rev=99) kabhi kisi bucket mein place nahi hua, toh uska 99 N ya S ke mean mein kabhi leak nahi ho sakta. Yahi exactly kyun pandas missing-key row ko applying se pehle drop karta hai: unknown group ki row include karna silently har group ka statistic corrupt kar deta.

Step 3 — Combine karo, group keys index ban jaate hain. Output index = ["N","S"] (koi NaN row nahi).

Yeh step kyun? Combine par, pandas har answer ko us key ke saath label karta hai jisne use produce kiya, toh group keys naya row index ban jaate hain — yahi result ko self-describing banata hai (tum seedha "N → 20.0" padh sakte ho). Kyunki None row ne kabhi koi group form nahi kiya, koi NaN key us index mein nahi aata.

Verify:

N -> 20.0
S ->  5.0     (lone-row group works)
(row rev=99 with key None is absent)

Sanity: accounted rows ka total = 2 (N) + 1 (S) = 3, aur humne 4 se shuru kiya → exactly 1 missing key ke liye dropped. ✓

Ex 5 — Multi-function agg + count-vs-size on missing data

Worked example

sum, mean, count jab ek value NaN ho

df = pd.DataFrame({
    "region": ["N", "N", "S"],
    "rev":    [10, np.nan, 40]})
df.groupby("region")["rev"].agg(["sum", "mean", "count", "size"])

Forecast: group N (values 10 aur NaN) ke liye, mean aur count kya hain? (GroupBy-multi-agg cell cover karta hai)

Step 1 — Har group ke liye compute karo, NaN ko math mein skip karte hue. Group N ke values hain [10, NaN].

sum = 10 (NaN skipped) — Kyun? pandas ke numeric aggregators default mein NaN skip karte hain.
mean = $10/1 = 10.0$ — non-NaN values ke count se divide karta hai, 2 se nahi.

Step 2 — count aur size ka fark samjho.

count = non-NaN values ki sankhya = group N ke liye 1.
size = group mein rows ki sankhya = group N ke liye 2 (NaN row bhi count hota hai). Yeh step kyun? Yeh interviews mein sabse confused pair hai: count present data measure karta hai, size present rows measure karta hai. Clean data par dono agree karte hain; missing data par alag ho jaate hain.

Step 3 — Group S (single clean value 40). sum=40, mean=40, count=1, size=1. Yeh step kyun? Ek group jo all-clean aur single-row dono ho, use bhi exactly same rules follow karni chahiye — koi special case nahi. Koi NaN present nahi hone se, count aur size coincide karte hain (dono 1), yahi reason hai ki tidy data par log kabhi note nahi karte ki dono alag hain. Inhe yahan agree karte dekha — Step 2 mein divergence confirm hoti hai sirf missing value ki wajah se.

Verify:

        sum  mean  count  size
N        10  10.0     1     2
S        40  40.0     1     1

Sanity: size - count = NaN per group ki sankhya = N ke liye 1, S ke liye 0. ✓

Ex 6 — Merge: same tables par charon `how` modes

Neeche ki figure do tables ki id values ka Venn diagram hai. Isse kya padhe: left (magenta) circle L ki ids rakhta hai, right (violet) circle R ki ids rakhta hai; beech ki overlap intersection hai $\cap = {2, 3}$ ; poora coloured area union hai $\cup = {1, 2, 3, 4}$ . Har how mode bas ek rule hai jo kehta hai kaunsa region rakhna hai — inner overlap rakhta hai, left poora magenta circle rakhta hai, right poora violet circle rakhta hai, outer sab kuch rakhta hai. Neeche orange line charon resulting id-sets list karti hai.

Worked example Tables ka ek pair, chaar join results

L = pd.DataFrame({"id":[1,2,3], "name":["a","b","c"]})
R = pd.DataFrame({"id":[2,3,4], "score":[88,90,77]})

inner, left, right, outer ke liye surviving ids do.

Forecast: aage padhne se pehle har ek ka id set likho. (Merge-how cell cover karta hai)

Step 1 — Key sets dhundho (Venn figure dekho). $L ids = {1, 2, 3}$ , $R ids = {2, 3, 4}$ . Intersection $\cap = {2, 3}$ (overlap), union $\cup = {1, 2, 3, 4}$ (poora area). Yeh step kyun? how literally in do sets par ek rule hai — socho SQL Joins.

Step 2 — Har rule apply karo.

inner → intersection ${2, 3}$ → 2 rows.
left → L ka poora ${1, 2, 3}$ ; id 1 ka koi R match nahi → score = NaN. 3 rows.
right → R ka poora ${2, 3, 4}$ ; id 4 ka koi L match nahi → name = NaN. 3 rows.
outer → union ${1, 2, 3, 4}$ ; id 1 ke paas score nahi, id 4 ke paas name nahi → dono NaN. 4 rows.

Yeh step kyun? Har how mode magic nahi hai — yeh pandas hai jo decide karta hai kaunsa key set rakhna hai aur phir, rakhe gaye keys ke liye jinka doosri side par koi partner nahi, us missing side ke columns ko NaN se fill karta hai (wahi union⇒NaN principle Ex 1 se, ab puri rows par apply hota hai). inner $\cap$ rakhta hai; left/right ek full circle rakhte hain; outer $\cup$ rakhta hai. NaNs exactly wahan aate hain jahan ek rakha gaya key ka koi partner nahi tha, yahi reason hai ki left/right/outer inhe create karte hain aur inner kabhi nahi karta. Dekho SQL Joins aur Missing Data / NaN.

Verify:

inner: ids {2,3}       (2 rows)
left:  ids {1,2,3}     (3 rows, id1 score NaN)
right: ids {2,3,4}     (3 rows, id4 name NaN)
outer: ids {1,2,3,4}   (4 rows)

Sanity: rows(outer) = rows(left) + rows(right) − rows(inner) = 3 + 3 − 2 = 4. ✓ (key sets par inclusion–exclusion)

Ex 7 — Merge with duplicate keys: row explosion

Worked example Jab ek key dono sides par repeat hoti hai

L = pd.DataFrame({"id":[1,1],   "name":["a1","a2"]})
R = pd.DataFrame({"id":[1,1,1], "score":[10,20,30]})
pd.merge(L, R, on="id", how="inner")

Forecast: 2 rows? 3 rows? zyada? (Merge duplicate-keys cell cover karta hai)

Step 1 — Har key value ke liye matches count karo. Key 1 L mein 2 baar aata hai aur R mein 3 baar. Yeh step kyun? Merge rows one-to-one pair nahi karta; har shared key value ke liye woh matching rows ka Cartesian product banata hai.

Step 2 — Multiply karo. Key 1 ke liye: $2 \times 3 = 6$ output rows (har L-row jisme id=1 hai har R-row ke saath pair hoti hai jisme id=1 hai). Yeh step kyun? Yeh "row explosion" ek merge ke unexpectedly balloon hone ka classic cause hai — join karne se pehle hamesha key uniqueness check karo.

Verify:

result has 2 * 3 = 6 rows

Sanity: pairs (a1,10)(a1,20)(a1,30)(a2,10)(a2,20)(a2,30) = 6. ✓

Ex 8 — Merge edge cases: colliding column names aur multi-key joins

Worked example Do overlapping columns, aur ek join jise do keys chahiye

# (A) dono tables mein ek non-key column "val" hai
L = pd.DataFrame({"id":[1,2], "val":[10, 20]})
R = pd.DataFrame({"id":[1,2], "val":[99, 88]})
pd.merge(L, R, on="id", suffixes=("_L", "_R"))
 
# (B) ek row ki true identity (store, month) hai, ek column nahi
sales = pd.DataFrame({"store":["A","A","B"], "month":["Jan","Feb","Jan"],
                      "qty":[3, 5, 7]})
targets = pd.DataFrame({"store":["A","A","B"], "month":["Jan","Feb","Jan"],
                        "goal":[2, 6, 4]})
pd.merge(sales, targets, on=["store", "month"])

Forecast: (A) merged frame ke column names kya hain? (B) kitni rows survive karti hain, aur kya sirf store par single-key join galat ho sakta hai? (Merge-edge-cases cell cover karta hai)

Step 1 — (A) Name collision detect karo. L aur R dono mein val naam ka ek non-key column hai. id par merge karne ke baad, pandas ke paas do columns honge jinka naam val hoga — ek ambiguous table. Yeh step kyun? Ek DataFrame physically duplicate column labels rakh sakta hai, lekin phir df["val"] ek column nahi chunega — yeh ambiguous hai. Pandas ko ek rule chahiye inhe distinguishable rakhne ke liye.

Step 2 — (A) suffixes= se resolve karo. suffixes=("_L", "_R") clashing columns ko val_L aur val_R rename kar deta hai. Key column id ko suffix nahi milta (woh shared hai, duplicate nahi). Yeh step kyun? Suffix tag har surviving copy ko kis table se aaya yeh identify karta hai, unambiguous access restore karta hai: df["val_L"] L ki value hai, df["val_R"] R ki. (Agar tum suffixes omit karo, pandas silently defaults ("_x", "_y") use karta hai — yeh jaanna tumhe mysterious val_x/val_y columns se bachata hai.)

Step 3 — (B) Pair of keys par join karo. Yahan ek row ki identity combination (store, month) hai, toh hum on=["store", "month"] pass karte hain. Pandas ek left row ko ek right row se tabhi match karta hai jab dono fields agree karein. Yeh step kyun? Agar hum sirf store par join karte, store A (jiske do rows hain: Jan aur Feb) doosri side ke har A row se match karta — ek Cartesian explosion (Ex 7 trap) aur galat pairings (A-Jan ki qty A-Feb ke goal ke saath). Multi-key join tuple $(store, month)$ ko ek composite key maanta hai, toh har real observation apne true partner ke saath line up hota hai. Socho ise do-column hash key ki tarah.

Verify:

(A) columns -> ["id", "val_L", "val_R"];  id 1 -> val_L=10, val_R=99
(B) 3 rows: (A,Jan,qty=3,goal=2) (A,Feb,5,6) (B,Jan,7,4)

Sanity: (A) exactly ek clashing name → exactly do suffixed columns, key un-suffixed → 3 columns total. (B) har (store,month) pair dono sides par unique hai → koi explosion nahi → rows = 3 = min matches. ✓

Ex 9 — Pivot: unique cells vs repeated cells

Worked example Plain pivot kaam karta hai; phir ek duplicate

pivot_table force karta hai

df = pd.DataFrame({
  "store":["A","A","B","B"],
  "month":["Jan","Feb","Jan","Feb"],
  "sales":[10, 20, 30, 40]})
df.pivot(index="store", columns="month", values="sales")

Phir ek doosra frame jahan store A ke do Jan rows hain.

Forecast: pehle pivot ka cell (A, Jan) kya hold karta hai, aur kya hota hai jab ek (store,month) pair repeat hota hai? (Pivot cell cover karta hai)

Step 1 — Har row ko ek cell mein map karo. Rows → grid coordinates: (A,Jan)=10, (A,Feb)=20, (B,Jan)=30, (B,Feb)=40. Har coordinate exactly ek baar hit hoti hai, toh pivot value directly daalta hai. Yeh step kyun? pivot pure reshaping hai — ise chahiye ki har (index, column) pair unique ho, warna woh decide nahi kar sakta kaunsa single value cell mein daalein.

Step 2 — Ise duplicate se todo.

df2 = pd.DataFrame({
  "store":["A","A","B"],
  "month":["Jan","Jan","Feb"],   # A/Jan appears TWICE
  "sales":[10, 5, 40]})
df2.pivot(index="store", columns="month", values="sales")
# -> ValueError: Index contains duplicate entries, cannot reshape

Yeh step kyun? Ab coordinate (A, Jan) se do values 10 aur 5 daalने ko kaha ja raha hai. pivot koi aggregation nahi karta, toh woh unke beech choose nahi kar sakta — woh ValueError raise karta hai rather than guess karne se. Yeh exactly woh failure hai jiske baare mein parent note ne warn kiya tha.

Step 3 — pivot_table se fix karo.

df2.pivot_table(index="store", columns="month",
                values="sales", aggfunc="sum")
#        Feb   Jan
#  A     NaN    15    <- (10 + 5) summed
#  B      40   NaN

Yeh step kyun? pivot_table hai "groupby phir reshape": woh har (index, column) cell mein aane wali rows ko group karta hai aur aggfunc (yahan sum) apply karta hai inhe ek value mein collapse karne ke liye — toh (A,Jan) ban jaata hai 10+5=15. Cells jahan koi row hi nahi hai (jaise (A,Feb) aur (B,Jan)) NaN se fill hoti hain, wahi missing-marker principle is poori page par baaki jagah ki tarah. aggfunc choose karna hi tarika hai pandas ko batane ka ki repeated cells kaise combine karein.

Verify:

plain pivot   (A,Jan)=10, (A,Feb)=20, (B,Jan)=30, (B,Feb)=40
df2.pivot     -> ValueError (duplicate (A,Jan))
pivot_table   (A,Jan)=15  (10+5),  (B,Feb)=40

Sanity: sab cells ka sum preserved hai — original df2 sales sum = 10+5+40 = 55, pivoted non-NaN cells 15+40 = 55. ✓

Ex 10 — Word problem: full pipeline merge → groupby → pivot

Worked example Do raw tables se revenue report

Ek shop orders aur ek product price list rakhti hai. "Total revenue per (category × month)" banao.

orders = pd.DataFrame({
  "prod": ["pen","pen","mug","mug"],
  "month":["Jan","Feb","Jan","Feb"],
  "qty":  [3,     5,    2,    4]})
prices = pd.DataFrame({
  "prod": ["pen","mug"],
  "cat":  ["stationery","kitchen"],
  "price":[2, 6]})

Forecast: kitchen ka revenue Feb mein kya hai? (Word-problem cell cover karta hai)

Step 1 — Orders ko prices ke saath prod par merge karo.

m = pd.merge(orders, prices, on="prod", how="left")

Yeh step kyun? qty orders mein hai, price/cat prices mein; ek left join har order se price info attach karta hai (sab orders rakhta hai even agar koi price missing ho). prices mein har prod key unique hai, toh koi row explosion nahi hoti (Ex 7 se contrast karo).

Step 2 — Har row ke liye revenue compute karo.

m["rev"] = m["qty"] * m["price"]

Rows: pen-Jan $3 \times 2 = 6$ , pen-Feb $5 \times 2 = 10$ , mug-Jan $2 \times 6 = 12$ , mug-Feb $4 \times 6 = 24$ . Yeh step kyun? Vectorised column multiply (under the hood NumPy operation) — revenue per-order hai, aggregate karne se pehle compute hoti hai.

Step 3 — groupby se (category, month) par aggregate karo.

g = m.groupby(["cat", "month"])["rev"].sum()
# (kitchen, Feb)    -> 24
# (kitchen, Jan)    -> 12
# (stationery, Feb) -> 10
# (stationery, Jan) ->  6

Yeh step kyun? Yeh report ka split-apply-combine dil hai: rows ko pair (cat, month) se split karo, har bucket mein rev sum karo, combine karo. Kyunki har product exactly ek category se map hota hai, pen rows stationery mein jaate hain aur mug rows kitchen mein.

Step 4 — Grouped result ko category × month grid mein pivot karo.

m.pivot_table(index="cat", columns="month",
              values="rev", aggfunc="sum")

Yeh step kyun? pivot_table Step 3 aur reshape ek call mein karta hai — woh (index, column) = (cat, month) se group karta hai, rev sum karta hai, phir sums ko grid ki tarah lay out karta hai. Yahan har (cat, month) cell ek single summed value hai.

Verify:

             Feb  Jan
kitchen       24   12      <- kitchen/Feb = 24
stationery    10    6

Units check: qty (items) × price (₹/item) = ₹, "revenue" ke liye sahi dimension. Kitchen-Feb = mug-Feb = 4×6 = 24, aur us bucket ka groupby total pivot cell se match karta hai. ✓

Ex 11 — Exam twist: chained-indexing trap + empty result

Worked example Woh write jo silently kuch nahi karta, phir ek empty aggregate

df = pd.DataFrame({"a":[1, -1, 2], "b":[0, 0, 0]})
# (i) the trap
df[df["a"] > 0]["b"] = 5        # WRONG
# (ii) the fix
df.loc[df["a"] > 0, "b"] = 5    # RIGHT
# (iii) degenerate aggregate
df.loc[df["a"] > 100, "b"].mean()

Forecast: line (i) ke baad, kya koi b 5 ke barabar hai? Aur line (iii) kya return karta hai? (Exam-twist cell cover karta hai)

Step 1 — Chained write (i) diagnose karo. df[df["a"]>0] ek copy return karta hai; ["b"] = 5 us throw-away copy mein write karta hai, phir woh discard ho jaata hai. df unchanged hai (pandas SettingWithCopyWarning raise karta hai). Yeh step kyun? Do bracket operations ek ke baad ek = "select, phir selection ki copy mein assign karo". Original kabhi write nahi dekh paata.

Step 2 — Single-.loc fix (ii). df.loc[mask, "b"] = 5 rows aur column ko ek hi call mein address karta hai, toh pandas original mein write karta hai. Rows jahan a>0 hain positions 0 aur 2 hain → unka b 5 ho jaata hai.

Step 3 — Degenerate aggregate (iii). Mask a > 100 koi row select nahi karta; empty Series ka .mean() NaN hai (error nahi, 0 nahi). Yeh step kyun? Jaanna chahiye ki "mean of nothing" NaN hai — 0 sum ko 0 count se divide karna undefined hai, aur pandas use NaN encode karta hai. Dekho Missing Data / NaN.

Verify:

after (i):  df["b"] == [0,0,0]   (write lost)
after (ii): df["b"] == [5,0,5]   (rows where a>0)
line (iii): NaN                  (empty mean)

Sanity: sirf woh rows badli jahan a>0 tha (positions 0,2); row 1 jahan a=-1 hai b=0 rakhta hai. ✓

Active recall

Recall Cover karke answer karo

Q: a + c disjoint indices ke saath — error ya all-NaN? → all-NaN, valid Series.
Q: .loc[100:300] vs .iloc[0:2] index [100,200,300,400] par? → 3 rows vs 2 rows.
Q: None wala groupby key — kahan jaata hai? → NaN mein coerce hota hai aur default mein drop hota hai (dropna=False rakhne ke liye).
Q: Group [10, NaN] par count vs size? → count=1 (non-NaN), size=2 (rows).
Q: left/right/inner se outer row count? → left + right − inner (inclusion–exclusion).
Q: Merge key 2× (L) aur 3× (R) repeat ho? → 2×3 = 6 rows (Cartesian per key).
Q: Do tables ek non-key column val share karti hain — dono kaise rakhen? → suffixes=("_L","_R") (defaults _x,_y hain).
Q: Row identity (store,month) hai — kaise merge karein? → on=["store","month"] (multi-key join).
Q: Pivot mein duplicate (index,column) ho? → pivot ValueError raise karta hai; pivot_table(aggfunc=...) use karo.
Q: Empty selection ka .mean()? → NaN.
Q: $\cap$ aur $\cup$ ka kya matlab hai? → intersection (dono mein naam) aur union (kisi mein bhi naam).