Visual walkthrough — Pandas — Series, DataFrame, indexing, groupby, merge, pivot

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

Hum ek chhoti si table poore time use karenge, taaki tum ise dimag mein rakho:

import pandas as pd
df = pd.DataFrame({
    "region": ["N", "N", "S", "S", "S"],
    "rev":    [10,  30,  5,  15,  10]})

Neeche jo bhi hai woh in paanch rows ke saath kya hota hai uski picture hai.

Step 1 — Table asal mein hoti kya hai (raw material)

KYA. Kuch bhi group karne se pehle, hume agree karna hoga ki hum kya group kar rahe hain. Ek DataFrame cells ki ek grid hoti hai. Har cell ke do coordinates hote hain: ek row label (index) aur ek column label.

KYUN. GroupBy kaam karta hai ek column ko padhke (yahan region) aur uski values ko ek rule ki tarah use karta hai — row labels ko buckets mein sort karne ke liye. Toh do cheezein hain jo hume point kar sakni chahiye: (1) row labels 0,1,2,3,4, aur (2) woh column jiske basis par hum group karenge. Agar yeh dono dikhna band ho jaaye, toh baad mein kuch bhi samajh nahi aayega.

PICTURE. Figure dekho. Bilkul left mein grey strip hai woh index (row labels) hai. Har row ek observation hai — ek aisi measurement jo saath mein belong karti hai. Orange column region key column hai; blue column rev woh payload hai jise hum summarise karenge.

Step 2 — Split: key padho, bucket map banao

KYA. Pandas region column mein upar se neeche chalta hai aur record karta hai ki kaunse row labels ke saath kaunsi distinct key value hai. Woh data abhi move nahi karta — sirf ek chhota sa lookup banata hai:

$\text{groups}=\{\underset{\text{key value}}{\underbrace{\text{"N"}}}:\underset{\text{row labels with that key}}{\underbrace{[0,1]}},\text{"S"}:[2,3,4]\}$

Ise symbol by symbol padho: colon ke left wali cheez woh value hai jo key column mein dikhi thi; right wali list har woh row label hai jiska region us value ke barabar hai.

YEH TOOL KYUN — sort nahi, hash map. Tum expect kar sakte ho ki pandas rows ko sort karega taaki equal keys saath aa jayein. Woh isse sasta karta hai: ek hash table use karta hai. Har key value ("N", "S") ko ek slot par hash kiya jaata hai, aur row labels us slot mein pile hote jaate hain jaise-jaise dikhte hain. Yeh sawaal ka jawaab constant time mein deta hai ki "row r kahaan rehti hai?", aur isliye groupby millions of rows tak scale karta hai. (Yeh split ka concrete meaning hai.)

PICTURE. Arrows dikhate hain ki har row label table se uske bucket mein ud raha hai. Rows 0 aur 1 dono "N" bol rahe hain, toh dono arrows N-bucket mein land karte hain; rows 2, 3, 4 "S" bolte hain aur S-bucket mein land karte hain. Abhi kuch add nahi hua — hamare paas sirf sorted addresses hain, values nahi.

Step 3 — Har bucket ke andar payload pick karo

KYA. Humne .groupby("region")["rev"] likha. ["rev"] kehta hai: har bucket ke andar, sirf rev values rakho. Toh Step 2 ke address lists value lists mein badal jaate hain:

$\text{"N"}\to [10,30],\text{"S"}\to [5,15,10]$

Term by term: 10 aur 30 woh rev cells hain row labels 0 aur 1 par (N-bucket ke addresses); 5, 15, 10 woh rev cells hain labels 2, 3, 4 par.

KYUN. Ek reducer jaise mean ko numbers chahiye, row addresses nahi. Yahi woh step hai jahan addresses [0,1] ko actual values [10, 30] se exchange kiya jaata hai. Yahan ["rev"] choose karna (pure frame ko group karne ki jagah) sirf ek promise hai ki hume sirf us ek payload column ki parwah hai — isse picture chhoti rehti hai.

PICTURE. Step 2 jaisi hi do buckets, lekin ab har ek mein blue numbers ka ek chhota vertical stack hai — woh raw revenues jo us region se belong karte hain.

Step 4 — Apply: har bucket ko ek number mein collapse karo

KYA. Ab reducer chalta hai, ek baar har bucket ke liye, independently:

$\text{mean}(N)=\frac{10+30}{2}=20.0,\text{mean}(S)=\frac{5+15+10}{3}=10.0$

Mean formula padhte hain: numerator us ek bucket ki saari values ka sum hai; denominator usi bucket mein kitni values hain (N ke liye 2, S ke liye 3). N-bucket ki koi bhi cheez S-bucket ke saath kabhi mix nahi hoti — pehle split karne ka poora point yahi hai.

YEH TOOL KYUN — mean ek "reducer" ki example ke roop mein. Ek reducer ka jawaab hota hai "is list ko ek single scalar mein summarise karo." sum, count, max, mean sab reducers hain; sirf arithmetic mein alag hain. Humne mean choose kiya kyunki parent example ne "average revenue per region" poochha. .sum() laga do aur sirf picture mein arrow ki arithmetic badlegi — machine bilkul same hai.

PICTURE. Har bucket ki numbers ki stack ek labelled "mean" gate se hoke funnel hoti hai aur right side par ek single number ke roop mein nikalti hai.

Step 5 — Combine: answers ko ek naye labelled Series mein jodo

KYA. Step 4 ke do scalars ek nayi Series mein stack hote hain jiska index group keys hain:

region
N    20.0
S    10.0
Name: rev, dtype: float64

KYUN. Original index 0,1,2,3,4 chala gaya — woh individual observations describe karta tha, aur ab hamare paas individual observations nahi hain, hamare paas per-region summaries hain. Har row ke liye natural naya label woh key hai jisne use produce kiya. Isliye groupby ke baad tumhara index grouping column mein badal jaata hai: yeh woh akela label hai jiska abhi bhi koi matlab hai. (Yeh naya frame ab tidy hai: ek row per region, summarised quantity ka ek column.)

PICTURE. Do answer-numbers ek do-row table mein slide ho jaate hain; grey index strip ab 0..4 ki jagah N, S padhti hai.

Step 6 — Edge case: ek empty group aur ek missing value

KYA. Do degenerate cheezein ho sakti hain, aur machine ko dono survive karni chahiye.

1. Ek key jiske zero rows hain (jaise tum .groupby() karo ek categorical par jo region "E" list karta hai lekin koi row use nahi karti). Woh bucket empty hai: mean([]). Pandas NaN return karta hai, crash nahi.
2. Payload mein ek NaN. Maan lo row 3 ka rev missing hota. Toh S-bucket [5, NaN, 10] hai. Default se mean NaN skip karta hai: woh present values ki count se divide karta hai, toh $\frac{5+10}{2}=7.5$, $\frac{5+10}{3}$ nahi.

KYUN. Yeh har real dataset ke do "no data" corners hain (dekho Missing Data / NaN). GroupBy inhe is rule se handle karta hai: "jo hai usse reduce karo; agar kuch nahi hai, toh jawaab NaN hai". Yeh jaanna tumhe ek NaN cell ko bug samajhne se rokta hai — machine tumhe bata rahi hai ki bucket empty tha ya value absent thi.

PICTURE. Left panel: ek empty bucket → mean gate NaN emit karta hai. Right panel: S-bucket mein ek greyed-out NaN value division se pehle skip ho rahi hai.

Ek-picture summary

Yahan poora pipeline ek canvas par hai: raw table → hash into buckets (split) → har bucket reduce karo (apply) → key labels ke neeche restack karo (combine). Paanch original rows ko trace karo bilkul end tak do final numbers tak.