Lesson 5 of 9 · 46 min · UG / diploma
pandas — filter, group, join
If Excel is the language of offices, pandas is Excel that you can put in git, test, and run every night. A DataFrame is a named table: rows and columns, types, missing values. You load a CSV, filter a boolean mask, groupby a house, merge two tables on roll number. This is how attendance dashboards, fee ageing reports and science-fair result sheets are built in Python.
Tabular data
Rows and columns you can filter like a spreadsheet.
You will be able to
- ✓ Load tabular data into a DataFrame (conceptually)
- ✓ Filter with a boolean mask
- ✓ Aggregate with groupby and know when to merge
DataFrame = spreadsheet with an API
Columns have names and types. df["marks"] is a Series (one column). df[df["marks"] >= 40] keeps rows — the same filter you wrote with append, but on 50,000 rows in compiled loops. .head() is how you look; .info() is how you see missing values and dtypes.
Missing data is NaN, not a blank you ignore. dropna and fillna are deliberate choices. Silently filling 0 for a missing test score can award a zero the student never sat.
The verbs you will use every week
Filter: df[df.house == "Blue"]. Select columns: df[["name", "marks"]]. Sort: sort_values("marks", ascending=False). Assign: df["pct"] = df.used * 100 / df.seats. These are the data operations — not a special branch of computer science, just table verbs with Python names.
groupby("house")["marks"].mean() is the house-wise average. That one line is a pivot table. Internships will ask you to groupby two columns (house, term) and count. Start with one.
Join is how real databases think
Students live in one table, marks in another. merge(students, marks, on="roll") is a JOIN. Left join keeps students with no marks (absent). Inner join drops them. Choosing the join is a product decision, not a syntax quiz.
Two CSVs that both contain a name spelled three ways will not join. Canonical IDs (roll numbers, Aadhaar-style school IDs) exist so joins work. Never join on full name if you can join on an id.
Chaining vs mutating
A pipeline that returns a new DataFrame at each step is easier to test than one that overwrites df in six places. Prefer df2 = df[df.marks >= 40].copy() when you will keep editing. SettingWithCopy warnings are pandas telling you that you edited a slice that might not write back.
For huge files, read in chunks or use a database. pandas is an in-memory table. It is not magic RAM.
Words that matter
- DataFrame
- A 2-D labelled table — pandas’ main object.
- Series
- A single labelled column.
- groupby
- Split rows by a key, then aggregate each group.
- merge
- SQL-style join of two tables on a key.
Common mistakes
Avoid: Joining two tables on student name.
Do this: Join on a stable id (roll, admission number).
Avoid: Treating NaN as zero without saying so.
Do this: Decide drop, fill, or flag missing — then document it.
On a full Python install — pandas
The default library for tabular work in Python: CSV/Excel in, filtered and grouped tables out, matplotlib-friendly.
pip install pandas openpyxl
Load marks, keep passes, house averages — the intern’s first dashboard.
Real library code (not run in this browser sandbox)
import pandas as pd
df = pd.read_csv("data/marks.csv")
passed = df[df["marks"] >= 40]
house_avg = df.groupby("house")["marks"].mean()
print(passed[["name", "marks"]])
print(house_avg)
# Excel round-trip for the principal
passed.to_excel("data/passed.xlsx", index=False)
Example program — Filter + groupby as loops
Same verbs, visible. pandas is this, vectorised and with names.
Edit the example, press Run, then Build if you want a compile check.
build
Press Build to compile.
Your turn — Filter Blue house
Collect names where house is Blue. Print so Kabir appears.
Edit the example, press Run, then Build if you want a compile check.
build
Press Build to compile.
Self-assessment
Check your understanding before you mark the lesson complete.
Progress is stored in a browser cookie on this device.