Pandas — Series, DataFrame, indexing, groupby, merge, pivot: Exercises

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

Shuru karne se pehle, parent note ka ek-sentence model yaad rakho: pandas data ko label se align karta hai, position se nahi. Neeche ke almost har trap mein ek beginner secretly position assume kar leta hai.

Level 1 — Recognition

Goal: ek task diya hai, sahi tool ka naam batao. Abhi code output ki zarurat nahi.

L1.1

Tumhare paas numbers ka ek column hai jiske row labels "a","b","c" hain. Yeh pandas object kya hai — Series ya DataFrame?

Recall Solution

Ek Series. Series exactly ek 1-D labelled array hai: ek (values, index) pair. Ek column + ek index = Series. Jis moment tumhare paas do ya zyada columns ek row index share karte hain, woh DataFrame ban jaata hai.

L1.2

Tum chahte ho "left table ki saari rows, jahan right table ka koi match nahi wahan NaN fill karo." pd.merge mein kaun sa how pass karoge?

Recall Solution

how="left". Yeh left table ki har key ko rakhta hai aur missing right-side columns ke liye NaN insert karta hai. Compare karo: inner = keys ka intersection, outer = union, right = left ka mirror.

L1.3

Tumhe row "u2" aur column "age" ko naam se select karna hai. df["age"], df.loc[...], df.iloc[...] mein se kaun sa use karoge?

Recall Solution

df.loc["u2", "age"]. .loc label door hai. .iloc ko integer positions chahiye; plain df["age"] sirf ek column laata hai, yeh ek step mein naam se row bhi nahi pick kar sakta.

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

Level 2 — Application

Goal: ek tool ek baar chalao aur result sahi se padho.

L2.1

a = pd.Series([1, 2, 3], index=["x", "y", "z"])
b = pd.Series([100, 200], index=["y", "x"])
result = a + b

Koi bhi NaN include karte hue poora result likho.

Recall Solution

Pandas labels {x, y, z} ka union leta hai, dono sides ko uske upar reindex karta hai, phir label ke hisaab se add karta hai:

x : $1 + 100 = 101$ (dono ke paas x hai; note karo b ki x value 100 hai, position 1 pe baitha hai, lekin position irrelevant hai).
y : $2 + 200 = 202$ .
z : NaN (sirf a ke paas z hai; b mein koi partner nahi).

$(a + b) [k] = {a [k] + b [k] NaN k \in idx (a) \cap idx (b) otherwise$ Toh result hai x -> 101.0, y -> 202.0, z -> NaN (sab floats, kyunki NaN float force karta hai). Dekho Missing Data / NaN.

L2.2

df = pd.DataFrame({"region":["N","N","S","S","S"],
                   "rev":[10,30,5,15,10]})
result = df.groupby("region")["rev"].sum()

Dono group totals do.

Recall Solution

groupby("region") pehle rows ko key ke hisaab se buckets mein split karta hai: {N:[row0,row1], S:[row2,row3,row4]}. Phir .sum() har bucket pe apply hota hai, aur results ko keys ke saath new index ke roop mein combine kiya jaata hai:

N : $10 + 30 = 40$ .
S : $5 + 15 + 10 = 30$ .

Yeh Split-Apply-Combine pattern action mein hai.

L2.3

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

Kaun se id rows survive karte hain, aur kyun?

Recall Solution

inner key values ka intersection rakhta hai. L ke paas ids {1,2,3} hain, R ke paas {2,3,4} hain. Intersection = {2,3}. Toh exactly do rows survive karti hain:

id=2 → name=b, score=88
id=3 → name=c, score=90

id=1 (sirf L mein) aur id=4 (sirf R mein) drop ho jaate hain. Ise SQL inner join ki tarah socho.

Level 3 — Analysis

Goal: woh subtle output predict karo jahan position vs label clash hoti hai.

L3.1

df = pd.DataFrame({"age":[25,32,47]}, index=[10, 20, 30])
part_loc  = df.loc[10:20]
part_iloc = df.iloc[0:2]

Har ek kitni rows return karta hai? Kya woh same rows hain?

Recall Solution

Yahan labels 10,20,30 numbers hain lekin positions nahi — yahi poori trick hai.

df.loc[10:20] label se slice karta hai aur stop ko inclusive rakhta hai → labels 10 aur 20 → 2 rows (ages 25, 32).
df.iloc[0:2] position se slice karta hai aur stop ko exclude karta hai → positions 0,1 → 2 rows (ages 25, 32).

Yahan yeh coincidentally match karte hain (dono 2 rows). Lekin df.loc[10:30] 3 rows dega (label 30 inclusive) jabki df.iloc[0:3] bhi 3 rows deta hai — df.loc[10:20] vs df.iloc[0:3] try karo aur woh alag ho jaate hain. Safe reading: .loc named endpoints count karta hai, .iloc Python-style half-open positions count karta hai.

L3.2

df = pd.DataFrame({"region":["N","N","S"], "rev":[10, None, 20]})
result = df.groupby("region")["rev"].mean()

Group N kya evaluate hota hai? (Usmein ek None hai.)

Recall Solution

None NaN ban jaata hai. Pandas aggregation functions default se NaN skip karte hain (skipna=True).

Group N = values [10, NaN] → non-missing values ka mean = $10/1 = 10.0$ .
Group S = [20] → $20.0$ .

Key insight: denominator mein count non-NaN entries ki sankhya hai, rows ki nahi. Dekho Missing Data / NaN.

L3.3

L = pd.DataFrame({"id":[1,2,2], "name":["a","b","c"]})
R = pd.DataFrame({"id":[2,2],   "score":[88,90]})
result = pd.merge(L, R, on="id", how="inner")

Result mein kitni rows hain?

Recall Solution

Merge har shared key ke liye, dono sides ke matching rows ka Cartesian product banata hai.

Key 1: R mein nahi → 0 rows contribute karta hai.
Key 2: L ke paas 2 rows hain (b,c), R ke paas 2 rows hain (88, 90) → $2 \times 2 = 4$ rows.

Total = 4 rows: (b,88),(b,90),(c,88),(c,90). Many-to-many keys se yeh "row explosion" classic surprise hai — figure ise visually dikhata hai.

Level 4 — Synthesis

Goal: ek real question ka jawab dene ke liye do ya zyada tools chain karo.

L4.1

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

Poora 2×2 grid fill karo. Kyun yeh pivot_table hona chahiye aur pivot nahi?

Recall Solution

Notice karo store A month Jan mein do baar appear karta hai (rows 0 aur 4: sales 10 aur 5). Plain pivot error raise karta kyunki (A, Jan) cell ke paas do candidate values hain aur pivot guess karne se mana karta hai. pivot_table unhe aggfunc="sum" se aggregate karta hai.

(A, Jan) = $10 + 5 = 15$
(A, Feb) = $20$
(B, Jan) = $30$
(B, Feb) = $40$

store	Feb	Jan
A	20	15
B	40	30

Yeh Tidy Data (long) → wide reshaping hai.

L4.2

L2.3 ki tables use karte hue (L mein ids 1,2,3; R mein ids 2,3,4), ek left merge karo, phir batao score column mein kitne NaN values aate hain.

Recall Solution

pd.merge(L, R, on="id", how="left") saari left keys {1,2,3} rakhta hai:

id=1 → R mein koi match nahi → score=NaN
id=2 → score=88
id=3 → score=90

Toh score mein exactly 1 NaN aata hai (sirf id=1).

L4.3

df = pd.DataFrame({
    "region":["N","N","S","S"],
    "product":["x","y","x","y"],
    "rev":[10, 20, 30, 40]})

Har region ka total revenue compute karo, phir woh region dhundho jiska total bada hai.

Recall Solution

Step 1 — group aur sum (split-apply-combine): df.groupby("region")["rev"].sum() →

N = $10 + 20 = 30$
S = $30 + 40 = 70$

Step 2 — .idxmax() maximum ka index label return karta hai, value nahi: df.groupby("region")["rev"].sum().idxmax() → "S" (kyunki $70 > 30$ ).

Bada total: region S with 70.

Level 5 — Mastery

Goal: ek chhoti pipeline design karo aur har step ke baare mein reason karo.

L5.1

Tumhare paas orders aur prices hain:

orders = pd.DataFrame({
    "item":["apple","banana","apple","cherry"],
    "qty": [2, 5, 3, 1]})
prices = pd.DataFrame({
    "item":["apple","banana"],
    "price":[4, 2]})

Har item ka total spend jo known price rakhta hai compute karo, phir grand total. Har design choice explain karo.

Recall Solution

Step 1 — orders par prices join karo. Inner merge use karo taaki bina price wale items (cherry) cleanly drop ho jaayein (design choice: hum sirf known-price spend chahte hain): m = pd.merge(orders, prices, on="item", how="inner") Surviving rows: (apple,2,4), (banana,5,2), (apple,3,4). cherry gaya (koi price nahi).

Step 2 — har row ka line total. Ek column add karo: m["spend"] = m["qty"] * m["price"] → apple: $2 \times 4 = 8$ , banana: $5 \times 2 = 10$ , apple: $3 \times 4 = 12$ .

Step 3 — item ke hisaab se group aur sum karo (split-apply-combine): per_item = m.groupby("item")["spend"].sum()

apple = $8 + 12 = 20$
banana = $10$

Step 4 — grand total: per_item.sum() = $20 + 10 = 30$ .

Design summary: merge (inner) → derive → groupby → reduce. inner choose karna deliberate tha — yahan left join cherry ko price=NaN ke saath rakhta, aur qty*NaN=NaN total ko silently poison karta jab tak handle na karo.

L5.2

L5.1 ka wahi m. Ab tum ek store-style grid chahte ho: items as rows, ek single column "spend". Jab tum m.pivot_table(index="item", values="spend", aggfunc="sum") chalate ho, kya milta hai, aur kyun yeh L5.1 Step 3 ke groupby ke equivalent hai?

Recall Solution

Sirf index ke saath (koi columns nahi), pivot_table exactly ek groupby-sum mein degenerate ho jaata hai:

apple → $20$
banana → $10$

Kyun equivalent hai: pivot_table(index="item", aggfunc="sum") item ke hisaab se rows split karta hai, spend par sum apply karta hai, aur item ko index ke roop mein combine karta hai — bilkul wahi split-apply-combine machinery. Bina columns argument ke pivot sirf ek groupby hai jo table ka costume pehne hua hai.

Score yourself

Recall Rubric

L1 (tool ka naam): 3 questions — aim 3/3.
L2 (ek baar chalao): agar L2.1 mein NaN mila, toh alignment click ho gayi.
L3 (predict): L3.3 mein row-explosion real filter hai — 4 rows, 2 nahi.
L4 (combine): L4.1 mein pivot_table hona chahiye, cell (A,Jan)=15.
L5 (design): kya tum justify kar sake kyun inner L5.1 mein? Toh tum joins samajhte ho, sirf unka syntax nahi.