Seven moves on a table

Meet the data

Two small tables. You will act on them one move at a time. Every move is shown three ways: an instruction in plain English, the line of Python an AI tool would write, and the table before and after.

`schools`
school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

NaN is how Python shows a missing value ("not a number"). Two are missing here: Grant's absences and Hale's district.

In schools, what does one row represent?

`funding`
district	year	per_pupil
North	2025	12,000
South	2024	9500
South	2025	9800
East	2025	10,200

In funding, what does one row represent?

1 · Keep rows Which rows?

“Keep only schools with at least 250 students.”

schools[schools["students"] >= 250]

How to read this line

`schools[ ... ]`	Square brackets with a condition inside: keep the rows where it is true.
`schools["students"]`	The `students` column. Quoted name in brackets = a column.
`>= 250`	The condition: at least 250.

Before

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: how many rows will come out?

After 8 → 5 rows

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Diaz	South	600	60
Ford	East	300	75
Hale	NaN	350	70

What can go wrong: the question quietly narrows. Any average you compute next describes schools with at least 250 students, not schools. Nothing in the output table says so.

2 · Pick columns Which columns?

“Show just each school's name and number of students.”

schools[["school", "students"]]

How to read this line

[["school", "students"]] A list of column names inside the brackets (note the double brackets): keep those columns.

Before

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: how many rows will come out?

After 8 → 8 rows

school	students
Adams	400
Baker	250
Cole	150
Diaz	600
Evans	100
Ford	300
Grant	200
Hale	350

Can it change a number? No. It changes what you see, not what the data says.

3 · Sort Presentation

“List the schools from largest to smallest.”

schools.sort_values("students", ascending=False)

How to read this line

`schools.sort_values(...)`	A dot means do this to the thing on the left: sort `schools`.
`"students"`	Sort by this column.
`ascending=False`	A named option: biggest first. Leave it out and the default (smallest first) applies.

Before

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: which school will be in the first row?

After 8 → 8 rows

school	district	students	absent
Diaz	South	600	60
Adams	North	400	60
Hale	NaN	350	70
Ford	East	300	75
Baker	North	250	50
Grant	East	200	NaN
Cole	North	150	45
Evans	South	100	30

Can it change a number? No. But the order of rows is what people read first, so it shapes the story even though no value moved.

4 · Add a column Which columns?

“Calculate each school's absence rate.”

schools["absent_rate"] = schools["absent"] / schools["students"]

How to read this line

`schools["absent_rate"] = ...`	A single `=` stores a result. Here it stores a new column named `absent_rate`.
`schools["absent"] / schools["students"]`	Divide one column by another, row by row.

Before

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: what will Grant's absent_rate be?

After 8 → 8 rows, one new column

school	district	students	absent	absent_rate
Adams	North	400	60	0.15
Baker	North	250	50	0.20
Cole	North	150	45	0.30
Diaz	South	600	60	0.10
Evans	South	100	30	0.30
Ford	East	300	75	0.25
Grant	East	200	NaN	NaN
Hale	NaN	350	70	0.20

What can go wrong: this is where definitions live. Change the denominator and every rate changes. A line like schools["absent"].fillna(0) would give Grant a perfect record. That is a decision dressed up as cleaning.

5 · Collapse What is a row?

“Total students in each district.”

schools.groupby("district")["students"].sum()

How to read this line

`.groupby("district")`	Split the rows into one pile per district. On its own this produces nothing you can see.
`["students"].sum()`	Apply a sum to the students column in each pile, then combine the answers into a new table.

Before (coloured by district)

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: how many rows will come out?

Split → apply → combine

East

Ford · 300

Grant · 200

sum = 500

North

Adams · 400

Baker · 250

Cole · 150

sum = 800

South

Diaz · 600

Evans · 100

sum = 700

NaN

Hale · 350

sum = dropped

After 8 → 3 rows

district	students
East	500
North	800
South	700

What changed: one row now represents a district, not a school. And the districts add up to 2,000 students, while the schools add up to 2,350. Where did Hale go? Nothing in the output says.

Going further: apply without collapsing

Swap .sum() for .transform("sum") and every school keeps its row, gaining its district's total:

schools["district_total"] = schools.groupby("district")["students"].transform("sum")

school	district	students	absent	district_total
Adams	North	400	60	800
Baker	North	250	50	800
Cole	North	150	45	800
Diaz	South	600	60	700
Evans	South	100	30	700
Ford	East	300	75	500
Grant	East	200	NaN	500
Hale	NaN	350	70	NaN

Now students / district_total is each school's share of its district. Comparing each unit with its own group is an idea that returns in regression. Notice that Hale still gets NaN.

6 · Match What is a row?

“Add each district's funding per pupil to the schools table.”

schools.merge(funding, on="district", how="left")

How to read this line

`schools.merge(funding, ...)`	Match rows of `schools` to rows of `funding`.
`on="district"`	Rows match when their district is the same.
`how="left"`	Keep every row of the left table (`schools`), even with no match. Leave this out and the default is `"inner"`: unmatched rows are dropped.

schools

school	district	students
Adams	North	400
Baker	North	250
Cole	North	150
Diaz	South	600
Evans	South	100
Ford	East	300
Grant	East	200
Hale	NaN	350

funding

district	year	per_pupil
North	2025	12,000
South	2024	9500
South	2025	9800
East	2025	10,200

Predict: how many rows will come out?

After 8 → 10 rows

school	district	students	absent	year	per_pupil
Adams	North	400	60	2,025	12,000
Baker	North	250	50	2,025	12,000
Cole	North	150	45	2,025	12,000
Diaz	South	600	60	2,024	9,500
Diaz	South	600	60	2,025	9,800
Evans	South	100	30	2,024	9,500
Evans	South	100	30	2,025	9,800
Ford	East	300	75	2,025	10,200
Grant	East	200	NaN	2,025	10,200
Hale	NaN	350	70	NaN	NaN

What can go wrong: a match can multiply rows. Total students in this table: 3,050, up from 2,350. Any sum or average from here counts Diaz and Evans twice. The question to ask of any match: did the row count change?

7 · Reshape What is a row?

“Stack students and absences into one column.” You will see this in AI output more than you will ask for it.

schools.melt(id_vars=["school", "district"], value_vars=["students", "absent"])

How to read this line

`.melt(...)`	Turn columns into rows: “wide” to “long”. The reverse is `.pivot(...)`.
`id_vars=[...]`	Columns that identify a row and are kept as they are.
`value_vars=[...]`	Columns whose values get stacked.

Before

school	district	students	absent
Adams	North	400	60
Baker	North	250	50
Cole	North	150	45
Diaz	South	600	60
Evans	South	100	30
Ford	East	300	75
Grant	East	200	NaN
Hale	NaN	350	70

Predict: how many rows will come out?

After 8 → 16 rows

school	district	variable	value
Adams	North	students	400
Baker	North	students	250
Cole	North	students	150
Diaz	South	students	600
Evans	South	students	100
Ford	East	students	300
Grant	East	students	200
Hale	NaN	students	350
Adams	North	absent	60
Baker	North	absent	50
Cole	North	absent	45
Diaz	South	absent	60
Evans	South	absent	30
Ford	East	absent	75
Grant	East	absent	NaN
Hale	NaN	absent	70

Can it change a number? No value changes, but what a row means does. Averaging the value column now would mix students with absences, and produce nonsense that looks just like an answer.

Recap: seven moves

Question to ask	Move	Python you'll see	Can it change a number?
Which rows?	Keep rows	`df[condition]`, `.dropna()`	Yes
Which columns?	Pick columns	`df[["a", "b"]]`	No
Which columns?	Add a column	`df["new"] = ...`	Yes
What is a row?	Collapse	`.groupby(...).sum()`	Yes
	Match	`.merge(...)`	Yes
	Reshape	`.melt()`, `.pivot()`	No, but a row's meaning changes
Presentation	Sort	`.sort_values(...)`	No

Four moves can change a number, and three of them did it silently here: Hale vanished in the collapse, Diaz and Evans doubled in the match, and Grant's missing count became a missing rate. None of it showed up as an error.

Two habits for any analysis you did not run: after each step, ask what does one row represent now? and how many rows went in, and how many came out?