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

WHY does pandas exist?

NumPy gives you fast arrays, but arrays are addressed by integer position only. Real data has names: a row is "customer 1023", a column is "revenue". Pandas adds a label axis (the index) so that:

Alignment is automatic — adding two Series matches by label, not position.
Missing data has a home — NaN fills where labels don't match.
Heterogeneous columns coexist — int, float, string, datetime in one table.

import pandas as pd
s = pd.Series([10, 20, 30], index=["a", "b", "c"])
df = pd.DataFrame({"x": [1, 2], "y": [3, 4]}, index=["r0", "r1"])

HOW alignment really works (derive it from scratch)

Think of a Series as a partial function $f : \text{Index} \to \text{Value}$ . When you do s1 + s2, pandas does NOT add element 0 to element 0. It computes:

$(s_1 + s_2)[k] = \begin{cases} s_1[k] + s_2[k] & k \in \text{idx}(s_1)\cap \text{idx}(s_2)\\[4pt] \text{NaN} & \text{otherwise}\end{cases}$

Worked example Alignment by label, not position

a = pd.Series([1, 2, 3], index=["x", "y", "z"])
b = pd.Series([100, 200], index=["y", "x"])
a + b
# x -> 1 + 200 = 201   (Why? both have label "x")
# y -> 2 + 100 = 102   (Why? both have label "y")
# z -> NaN             (Why? "z" missing in b → no match)

Why this step? Pandas first takes the union of indices, reindexes both Series onto it, then adds. Position is irrelevant.

Indexing: the three doors (`[]`, `.loc`, `.iloc`)

Worked example Reading the table three ways

df = pd.DataFrame({"age":[25,32,47], "city":["NY","LA","SF"]},
                  index=["u1","u2","u3"])
 
df.loc["u2", "age"]        # 32   (label row, label col)
df.iloc[1, 0]              # 32   (position row, position col)
df.loc["u1":"u2"]         # rows u1 AND u2 (label slice → inclusive!)
df.iloc[0:2]              # rows 0,1 (position slice → stop excluded)
df[df["age"] > 30]        # boolean mask → u2, u3

Why .loc is inclusive: labels aren't guaranteed to be numbers, so "stop+1" is meaningless; pandas instead includes the named endpoint.

GroupBy: Split → Apply → Combine

$\text{result}[g] = f\big(\{ \text{rows } r : \text{key}(r) = g \}\big)$

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

Worked example Mean revenue per region

df = pd.DataFrame({
    "region": ["N","N","S","S","S"],
    "rev":    [10, 30, 5, 15, 10]})
 
df.groupby("region")["rev"].mean()
# N -> 20.0    (Why? (10+30)/2)
# S -> 10.0    (Why? (5+15+10)/3)

Why this step? groupby("region") builds index → rows mapping {N:[0,1], S:[2,3,4]}; .mean() is applied per group; results are combined with the group keys as the new index.

Merge: SQL-style joins by key

Worked example Inner vs left join

L = pd.DataFrame({"id":[1,2,3], "name":["a","b","c"]})
R = pd.DataFrame({"id":[2,3,4], "score":[88,90,77]})
 
pd.merge(L, R, on="id", how="inner")
# ids 2,3 survive (Why? only these appear in BOTH)
 
pd.merge(L, R, on="id", how="left")
# ids 1,2,3 (Why? all of L); id 1 gets score=NaN (no R match)

Why this step? Merge computes, for each key, the Cartesian product of matching rows on each side, filtered by the how rule.

Pivot: long → wide reshaping

Worked example Sales by (store × month)

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")
#        Feb  Jan
#  A      20   10
#  B      40   30

Why this step? Each unique store → row label, each unique month → column label, and sales drops into cell (store, month). If two rows shared the same (store, month), pivot errors → use pivot_table(aggfunc="sum").

Common mistakes (Steel-man + fix)

Active recall

Recall Cover and answer

Q: What two arrays make a Series? → values + index.
Q: Why is a + b with mismatched indices full of NaN? → alignment takes the union, unmatched labels → NaN.
Q: .loc vs .iloc on slice endpoints? → loc inclusive (label), iloc exclusive (position).
Q: Three steps of groupby? → split, apply, combine.
Q: Default how in merge? → inner.
Q: When must you use pivot_table instead of pivot? → when (index,column) pairs repeat (need aggregation).

Recall Feynman: explain to a 12-year-old

Imagine a big chart of lockers, each with a name tag (the index). A Series is one column of lockers; a DataFrame is the whole wall. When you add two columns of lockers, you match them by name tag, not by which is first — if a name is missing on one side, that locker stays empty (NaN). Groupby is sorting kids into teams by their team-shirt colour and counting each team. Merge is gluing two charts together using a shared ID column, like matching school IDs to test scores. Pivot is rotating a long list into a neat grid.

Flashcards

What is a pandas Series structurally?

A 1-D labelled array = aligned (values array, index array).

Why does adding two Series with different indices produce NaN?

Pandas aligns by the union of labels; unmatched labels have no partner, so the result is NaN.

Difference between .loc and .iloc on a slice?

.loc is label-based and inclusive of the stop; .iloc is position-based and excludes the stop.

What are the three phases of groupby?

Split rows by key, apply a function per group, combine results into one labelled object.

What is the default join type in pd.merge?

inner (keeps only keys present in both tables).

When must you use pivot_table instead of pivot?

When the (index, column) combination is not unique — pivot_table aggregates duplicates, pivot raises an error.

How do you safely set values on filtered rows?

Use a single .loc call: df.loc[mask, "col"] = value (avoid chained indexing).

What does how="outer" return in a merge?

The union of keys from both tables, filling NaN where one side lacks a match.

Is df.groupby("k") eager or lazy?

Lazy — it returns a GroupBy object; computation happens only when an aggregation/apply is called.

Connections

NumPy — each pandas column is a NumPy array; vectorized ops underneath.
SQL Joins — merge mirrors INNER/LEFT/RIGHT/OUTER JOIN semantics.
Split-Apply-Combine — the general data-analysis pattern groupby implements.
Tidy Data — pivot/melt convert between long (tidy) and wide layouts.
Hash Tables — the index uses hashing for $O(1)$ label lookup.
Missing Data / NaN — alignment is the main source of NaNs.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, pandas ka core idea bahut simple hai: ek Series matlab ek column jisme har value ke saath ek label (index) chipka hua hota hai, aur ek DataFrame matlab aise columns ki ek poori table jo ek hi row-index share karte hain. Sabse important baat — pandas data ko label se align karta hai, position se nahi. Isiliye jab tum do Series jodte ho aur unke index match nahi karte, to wahan NaN aa jaata hai. Yeh feature hi pandas ko itna powerful banata hai.

Indexing ke liye do darwaze yaad rakho: .loc label se kaam karta hai aur slice mein stop bhi include hota hai; .iloc position se kaam karta hai aur Python ki tarah stop exclude hota hai. Beginners yahin galti karte hain. Aur agar filtered rows mein value set karni hai to hamesha single .loc[mask, "col"] = val use karo, warna SettingWithCopyWarning aayega aur change lost ho jayega.

Groupby ka funda hai split-apply-combine: rows ko key ke hisaab se teams mein baant do, har team par function (mean, sum) chalao, phir result ko ek table mein jod do. Merge SQL join jaisa hai — on="key" se do tables ko jodta hai, aur how decide karta hai kaun se keys bachenge (default inner = sirf common keys). Pivot long data ko wide grid mein ghuma deta hai; agar (row, column) pair repeat ho to pivot_table use karo jo automatically aggregate kar deta hai.

Exam aur real projects dono mein yeh 5 cheezein 80% kaam karwa deti hain. Inhe ratna nahi, samajhna hai — bas yaad rakho ki sab kuch labels ke alignment par based hai, baaki sab us idea ka extension hai.

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

WHY does pandas exist?

HOW alignment really works (derive it from scratch)

Indexing: the three doors (`[]`, `.loc`, `.iloc`)

GroupBy: Split → Apply → Combine

Merge: SQL-style joins by key

Pivot: long → wide reshaping

Common mistakes (Steel-man + fix)

Active recall

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Scientific Computing (Python)

Connections