Pandas & DataFrames

Loading, cleaning, joining, and transforming tabular data with pandas — taught from zero, starting with what a table even is, then building up to the group-by, join, and reshape moves that every ML dataset passes through before a model ever sees it.

beginner#pandas#dataframes#data-cleaning

Start here — what pandas actually is

Almost every dataset you will ever meet arrives as a table: rows going down, columns going across. A spreadsheet of sales. A CSV of sensor readings. A log of training runs. Before a model can learn anything from that table, somebody has to load it, find the broken rows, fix them, combine it with another table, and boil it down into summaries.

is the tool for that job. In one sentence:

The one-sentence version

pandas is a spreadsheet you drive with code instead of a mouse. Same idea — rows, columns, filters, sums, pivot tables — but every operation is a line of Python you can save, re-run, and hand to someone else. That's the entire pitch: reproducibility and scale, in exchange for typing instead of clicking.

Think of it like the difference between cooking and eating:

A model is the meal. pandas is the kitchen — the washing, chopping, and measuring. It is unglamorous and it is where most of the time actually goes: practitioners routinely report spending the majority of a project on getting data into shape rather than on the model itself. Learning to move confidently in the kitchen is the single biggest speed-up available to a beginner.

How to read this page

Everything starts from plain language and a picture. Flip the Depth switch at the top when you want the formal statement of what an operation computes, or the edge cases that bite in production. Nothing is hidden for good — the deeper panels sit inline, one click away.

The Series — one labelled column

Start smaller than a table. A is one column: a list of values, where every value also carries a label.

That's the whole difference from a plain Python list. A list gives you values in positions 0, 1, 2. A Series gives you values and names for them.

Think of it like a coat-check rack:

A Python list is a row of hooks numbered 1, 2, 3 — to find your coat you must remember your number. A Series is a rack where each hook also has a name on it. You can still count along to hook 3, but you can also just walk up and ask for the one labelled accuracy. Both ways of getting at your coat stay available, and that double life is exactly why pandas exists.

Those labels live in an — the strip of names down the left-hand side. The Index matters more than it looks: when pandas combines two objects, it lines them up by label, not by position. Add two Series together and pandas matches alice to alice, no matter what order the rows happen to be in.

Try to recall

What does a Series have that a plain Python list does not?

Hint: Look at the left edge of a printed Series.

The DataFrame — the whole table

Stack several Series side by side, sharing one Index, and you get a — the object you will spend your whole pandas life holding.

The mental picture that never fails you

A DataFrame is a dict of columns. The keys are column names; each value is a Series of the same length. Nearly every confusing error message in pandas becomes obvious once you picture it that way: df["accuracy"] hands you one column back, exactly like looking up a key in a dictionary.

Let's build one. Below is a small log of machine-learning training runs — the kind of table you produce ten times a week once you start experimenting. Run it:

Python · runs in your browser

What this does: Builds a small DataFrame of ML training runs from a dict of columns, then prints the four things you should look at first for any new table: the data itself, its shape (rows, columns), the type of each column, and quick summary statistics. Getting in the habit of running these four lines on every new dataset will save you hours of confusion later.

Notice the four moves in that cell. They are the ones to make on every new dataset, before anything else:

Command	What it tells you
`df.head()`	What the data actually looks like
`df.shape`	How many rows and columns you have
`df.dtypes`	How pandas is interpreting each column
`df.describe()`	Range, centre, and spread of the numbers

is worth pausing on. If a column of numbers got read from a messy CSV as text, then df["price"].sum() will glue the strings end to end instead of adding them — and pandas will not complain. Checking dtypes early is the cheapest bug-prevention habit in data work.

Text columns look different across pandas versions

Ask an older pandas (2.x) for the dtype of a text column and it says object — a catch-all meaning arbitrary Python objects. pandas 3.0, released in January 2026, infers a dedicated str dtype instead, which is faster and type-safe. The cell above prints its own version, so you can see which one you are on. Everything else on this page behaves identically either way.

You load a CSV and df.dtypes shows the column 'price' as object (or str) rather than float64. What is the most likely cause?

Selecting — getting at the part you want

You almost never want the whole table. You want some rows and some columns. pandas gives you three tools, and beginners mix them up constantly, so let's separate them cleanly.

Label versus position — the fork in the road

There are exactly two ways to point at a row: by its name or by its place in line. pandas gives each way its own accessor so it never has to guess which you meant.
• .loc — select by label. Give me the row named 'ana'.
• .iloc — select by integer position. Give me the row sitting in slot 3.

The third tool is the one you will reach for most: a — a yes/no column that filters rows.

Filtering by hand, before you write any code

Take the runs table from above and ask for the runs that scored above 0.8, showing only the model and its accuracy.

Evaluate the condition on every row. accuracy > 0.8 gives, going down the six rows: 0.71 → False, 0.74 → False, 0.80 → False, 0.83 → True, 0.86 → True, 0.88 → True. (Note that 0.80 > 0.8 is False — it is not strictly greater. Off-by-a-hair boundary mistakes like this are a classic source of quietly wrong row counts.)
Keep only the True rows. That leaves rows 4, 5, and 6.
Keep only the requested columns. Drop everything except model and accuracy.

Result: three rows — base 0.83, large 0.86, large 0.88.

In pandas that entire paragraph is one line: runs.loc[runs["accuracy"] > 0.8, ["model", "accuracy"]] — a mask for the rows, a list for the columns, separated by a comma.

Now run it and check the answer against what you worked out:

Python · runs in your browser

What this does: Shows the three ways to select data — a boolean mask for rows, .loc for label-based selection, and .iloc for position-based selection — on the training-run table. The .loc line reproduces the hand-worked filter above; compare its output to the three rows you predicted.

import numpy as np
import pandas as pd

runs = pd.DataFrame({
    "run_id":   [1, 2, 3, 4, 5, 6],
    "model":    ["small", "small", "base", "base", "large", "large"],
    "lr":       [0.01, 0.003, 0.01, 0.003, 0.01, 0.003],
    "accuracy": [0.71, 0.74, 0.80, 0.83, 0.86, 0.88],
    "minutes":  [12.0, 13.0, 40.0, 41.0, 150.0, np.nan],
})

# 1. One column -> a Series
print("one column:")
print(runs["accuracy"].to_list())

# 2. A boolean mask: True/False for every row
mask = runs["accuracy"] > 0.8
print("\nthe mask itself:", mask.to_list())

# 3. .loc[rows, columns] -- rows by mask, columns by name
print("\nrows above 0.8, two columns (the hand-worked answer):")
print(runs.loc[mask, ["model", "accuracy"]])

# 4. .iloc[rows, columns] -- everything by integer position
print("\n.iloc[0, 1] is the value in row 0, column 1:", runs.iloc[0, 1])
print(".iloc[:2, :3] takes the first 2 rows and first 3 columns ->", runs.iloc[:2, :3].shape)

# 5. Combining conditions: & is 'and', | is 'or' -- and each side needs brackets
both = runs.loc[(runs["accuracy"] > 0.8) & (runs["lr"] == 0.003)]
print("\naccurate AND low learning rate:", both["run_id"].to_list())

Two selection traps everyone falls into once

Use & and |, not and and or. Python's and demands a single true-or-false answer, but a mask is a whole column of them, so it raises an error. & and | combine the columns element by element.
Bracket each condition. & binds more tightly than > in Python, so a > 1 & b > 2 is silently parsed as a > (1 & b) > 2. Write (a > 1) & (b > 2).

Try to recall

You want the row sitting in the third position of the table, regardless of what it is called. Which accessor do you use, and why not the other one?

Hint: One letter of the name gives it away.

Vectorization — why you never loop over rows

Here is the habit that separates fast pandas from painfully slow pandas. Coming from ordinary Python, your instinct is to write a loop: for each row, do the thing. In pandas, you instead describe the operation on the whole column at once.

Talk to the manager, not to each worker

Writing a Python loop over 200,000 rows means 200,000 round trips between Python and the data — a conversation with every single worker, one at a time. Writing df["x"] * 2 hands the whole job down to compiled code that does all 200,000 multiplications in one tight pass, with no Python in the middle. Same answer, a fraction of the time.

That is , and it is the same idea you met in NumPy — pandas columns are NumPy arrays underneath, wearing labels.

Do not take it on faith. Time it yourself:

Python · runs in your browser

What this does: Races the same arithmetic done two ways over 200,000 rows — once vectorized on the whole column, once with a Python loop — and prints both timings, the speed-up, and a check that the answers are identical. The exact numbers depend on your machine, but the vectorized version wins by a wide margin every time.

Why is df['x'] * 2 dramatically faster than looping over df['x'] in Python?

Missing data — the holes in every real dataset

Real tables have gaps. A sensor dropped out, a form field was left blank, a join found no match. pandas marks every such hole with .

NaN means unknown, not zero

This distinction decides whether your analysis is right or wrong. A missing temperature reading is not 0 °C — it is we do not know. Treat unknowns as zeros and you drag every average toward zero and invent a pattern that was never in the data. pandas takes the honest position: NaN contaminates arithmetic (anything plus unknown is unknown) but is skipped in summaries, so mean() averages the values you actually have.

Take this deliberately damaged version of the training log. Three of its twenty cells are missing:

Where the holes are — a missing-value map— interactive, drag & zoom

Loading chart…

Each square is one cell of the damaged table below; orange means the value is missing. Reading down a column tells you how broken that field is — minutes has lost two of five values. Plotting this map is the fastest way to see whether gaps are scattered at random or concentrated in one column or one stretch of rows, which is what decides how you should fix them.

Once you can see the holes, you have exactly three honest options:

Option	Command	When it is the right call
Drop the rows	`df.dropna()`	Few rows affected, and you can afford to lose them
Fill the gaps	`df.fillna(value)`	The gap has a defensible stand-in — a median, a category like `unknown`
Leave them	do nothing	The summaries you need already skip NaN correctly

dropna() is greedier than it looks

Bare df.dropna() deletes a row if any column in it is missing. On a wide table with scattered gaps that can wipe out most of your data in one line — in the example below it takes five rows down to one. Almost always you want df.dropna(subset=["the_column_that_matters"]) instead.

Python · runs in your browser

What this does: Finds and fixes missing values in a damaged table — counts the gaps per column, shows how greedy a bare dropna() is (five rows become one), and fills the gaps sensibly with a median for the numbers and a placeholder category for the text. Compare the isna().sum() output to the orange squares in the map above.

import numpy as np
import pandas as pd

raw = pd.DataFrame({
    "run_id":   [1, 2, 3, 4, 5],
    "model":    ["small", "small", None, "large", "large"],
    "accuracy": [0.71, None, 0.80, 0.86, 0.88],
    "minutes":  [12.0, 13.0, 40.0, None, None],
})
print(raw)

# 1. Count the holes, column by column -- always do this first
print("\nmissing per column:")
print(raw.isna().sum())

# 2. Summaries skip NaN: the mean divides by 4 present values, not 5 rows
print("\naccuracy mean (over the 4 values that exist):", raw["accuracy"].mean())

# 3. Bare dropna() is brutal -- one bad cell kills the whole row
print("\nrows left after a bare dropna():", len(raw.dropna()))
print("rows left after dropna(subset=['accuracy']):", len(raw.dropna(subset=["accuracy"])))

# 4. Filling: a median for numbers, an explicit category for text
filled = raw.copy()
filled["minutes"] = filled["minutes"].fillna(filled["minutes"].median())
filled["model"] = filled["model"].fillna("unknown")
print("\nafter filling:")
print(filled)

Split-apply-combine — the group-by

Now the operation that earns pandas its keep. You rarely want a number for the whole table; you want one per group. Average accuracy per model. Total sales per region. Best score per student.

Three steps, one line

Every group-by is the same three moves, always in this order:
1. Split — deal the rows into piles by some key.
2. Apply — compute a number for each pile independently.
3. Combine — stack those numbers back into one small result table.
pandas calls the whole pattern split-apply-combine, and writes all three steps as a single line.

Think of it like sorting a bag of laundry:

Tip the bag out and make piles by colour (split). Weigh each pile (apply). Write the weights on one list (combine). Nobody would weigh the whole bag and call it an answer about the whites — and that is exactly the mistake a table-wide average makes.

Average accuracy per model, computed by hand

Group the six training runs by model and average their accuracy.

Split into three piles by the model column:
- small → runs 1 and 2 → accuracies 0.71, 0.74
- base → runs 3 and 4 → accuracies 0.80, 0.83
- large → runs 5 and 6 → accuracies 0.86, 0.88
Apply the mean inside each pile:
- small: $(0.71 + 0.74)/2 = 0.725$
- base: $(0.80 + 0.83)/2 = 0.815$
- large: $(0.86 + 0.88)/2 = 0.870$
Combine into a three-row result, one row per group. pandas sorts the group keys alphabetically by default, so it comes back as base, large, small — not in the order the groups first appeared. If that ordering matters to you, pass sort=False.

One line: runs.groupby("model")["accuracy"].mean().

Those three numbers are the entire result — and seeing them side by side is the point of computing them:

Average accuracy per model — the combine step, drawn— interactive, drag & zoom

Loading chart…

The three bars are exactly the numbers worked out by hand above, in the alphabetical order pandas returns them. This is what a group-by is for: six raw rows collapse into three comparable numbers, and the trend that was invisible in the table — bigger model, better accuracy — becomes obvious at a glance.

Python · runs in your browser

What this does: Runs the split-apply-combine pattern three ways — a single statistic per group, several statistics at once with named output columns, and a quick bar chart of the result — reproducing the hand-worked averages 0.815, 0.870 and 0.725. Notice that groupby returns the groups in alphabetical order, not the order they appear in the table.

Group-wise transforms — the move that feeds a model

transform deserves its own section, because it is the bridge from data cleaning to actual machine learning.

The problem: a raw number is often meaningless without its context. A score of 90 is excellent in a hard class and mediocre in an easy one. A model needs the relative number, not the absolute one.

Think of it like grading on a curve:

A teacher who reports raw marks tells you little across different classes. A teacher who reports "two-thirds of a standard deviation above your class average" tells you something comparable everywhere. transform is how you grade on a curve — per group, with the group's own mean and spread.

The standard version of this is the , computed within each group:

Watch it work. Class A is tightly bunched, class B is spread wide, and the z-scores make the two comparable:

Python · runs in your browser

What this does: Computes a per-class z-score with groupby.transform — each row is compared against its own class average and spread rather than the whole table's. Ana scores 70 and Dee scores 50, but relative to their own classes ana is a full standard deviation below while dee is only 0.76 below, because class B is far more spread out. That relative number is what a model should see.

Try to recall

You want to add a column holding each row's group average, keeping every original row. Do you use agg or transform?

Hint: Ask what shape you need back.

Joining tables — merge

Your data almost never lives in one table. Facts get split across several — runs in one, model specifications in another, costs in a third — and you need them side by side.

Matching on a shared column

A glues two tables together by a key: a column both tables have. pandas walks the key values, and wherever they match, it stitches the two rows into one wider row.

Think of it like a name badge at a conference:

You have a list of attendees and a separate list of dinner choices, both keyed by name. Merging is walking down the attendee list, finding each person's dinner choice by name, and writing it on their badge. The only real question is what to do about mismatches — someone on one list but not the other — and that is precisely what the how argument decides.

Joining runs to model specs, by hand

You have six training runs keyed by model, and a specification table listing four models with their parameter counts:

model	params_m
small	12
base	120
large	1300
huge	7000

Merge the two on model, and note the mismatch: huge has a spec but no runs.

An inner join keeps only matches. Each of the 6 runs finds its spec, so all 6 survive and each gains a params_m column. huge matches nothing and is dropped. Result: 6 rows.
A left join keeps everything on the left. Merge starting from the spec table and you keep all four models. small, base and large each expand to their 2 runs — that is 6 rows — and huge survives as a single row with NaN in every run column. Result: 7 rows.
Watch the row count. It went from 6 to 7 without you asking. That is the number to check after every merge.

`how=`	Keeps	Reach for it when
`inner` (default)	Only keys present in both	You need complete records and can drop the rest
`left`	All left rows, filling gaps with NaN	Enriching a main table with optional extras
`right`	All right rows	Same as left, sides swapped
`outer`	All keys from either	Reconciling two sources and seeing what each is missing

Python · runs in your browser

What this does: Merges the training runs with a table of model specifications and shows how the how= argument changes the answer — inner keeps the 6 matched rows, left keeps all 4 specs and grows the result to 7 rows because the unmatched model 'huge' survives with NaN. The final indicator column names exactly which side each row came from, which is the fastest way to debug a merge that produced the wrong count.

Always check the row count after a merge

A merge is the single most common way to silently corrupt a dataset. If a key is duplicated on both sides, pandas produces the cross-product of the matches — 3 rows against 3 rows becomes 9 — and a table can balloon without a single error message. Print df.shape before and after, every time. When you expect a strict relationship, say so and let pandas enforce it: merge(..., validate="one_to_many") raises immediately if the assumption is violated.

Reshaping — long versus wide

The same data can be laid out two ways, and different tools want different layouts.

Long (also called tidy): one row per observation, with columns naming the variable and its value. run, metric, value. Databases and most plotting libraries love this.
Wide: one row per subject, one column per variable. run, accuracy, minutes. Humans and spreadsheets prefer this.

Think of it like a train timetable:

Long format is the arrivals board — one line per event, endlessly scrolling. Wide format is the printed grid on the wall — one row per train, one column per station. Identical information, opposite ergonomics.

The gotchas that bite everyone

After merging two tables you notice the row count went from 1,000 to 1,340. What should you suspect first?

Where this shows up in ML

Every one of these is a pandas move wearing a machine-learning hat:

Building a dataset is read_csv → dropna → merge with a labels table.
Feature engineering is assign, groupby().transform(), and map — the group-wise z-score above is a feature.
Train/test splitting by user or by date is a boolean mask over a column.
Reporting per-class metrics is a groupby on the predicted label.
Auditing a model for fairness is a groupby on a demographic column, comparing the same metric across groups.

Explain it yourself

Explain split-apply-combine to a friend using the laundry-sorting picture, with no code and no pandas jargon. Then say in one sentence how transform differs from agg. If you stall on that last sentence, reread the group-wise transform section — the difference is the shape of what comes back.

Recap — the key ideas

A Series is one column of values plus a label for each (its Index); a DataFrame is a dict of Series sharing one Index — a spreadsheet you drive with code.
Check head, shape, dtypes, describe on every new table. A wrong dtype is the quietest bug in data work.
Select rows by label with .loc, by position with .iloc, and by condition with a boolean mask — combining conditions with & and |, each side bracketed.
Vectorize: state the operation on the whole column instead of looping over rows, and it runs in compiled code, many times faster.
NaN means unknown, not zero. Summaries skip it; dropna() is greedier than it looks; missingness is often information worth keeping.
groupby is split-apply-combine: agg returns one row per group, transform returns one value per row, filter keeps or drops whole groups.
merge joins tables on a shared key; how= decides what happens to non-matches, and you check the row count afterwards, every time.
Never assign through a chain — do it in one step with .loc.

Practice — and how to make it stick

Learn it the way that actually works

• Retrieval practice: before rereading anything, close your eyes and name the three steps of split-apply-combine, and the difference between .loc and .iloc. Pulling it from memory is what builds the memory.
• Spaced repetition: mark this topic complete to add it to your Review queue, so it resurfaces right before you would have forgotten it.
• Interleaving: mix these exercises with NumPy drills rather than doing all of one then all of the other — pandas columns are NumPy arrays, and switching between the two views cements both.

By hand, before any code: take the six-run table and predict what runs.groupby("lr")["accuracy"].mean() returns — how many rows, what labels, what values. Then run it and check.
Break it on purpose: duplicate the small row in the configs table and re-run the inner merge. Watch the row count grow, then add validate="one_to_many" and watch pandas refuse.
Real data: load any CSV you care about, run the four orientation commands, and write down one question the table can answer. Answer it with a single groupby.
From scratch: implement groupby(...).mean() yourself in plain Python with a dictionary of lists. Doing it the slow way once makes the fast way permanent.

Try it right here — edit and run the code, and if you get stuck or hit an error, ask Ada on the right: she can see your code and terminal output.

Practice labPython · runs in your browser · Ada can see your terminal

Your task: Clean, join, and summarize in one go. Run the starter to see where it stands, then complete the three TODOs: (1) fill the missing minutes with the column median, (2) merge in the params_m column from configs, and (3) produce a per-model summary with the number of runs, the mean accuracy, and the max minutes. Predict each answer before you run it.

editor

terminal

Press Run (⌘/Ctrl+Enter) to execute.

Ask Ada — she can read your terminal

Next up: now that the data is in shape, learn to show it — Plotting & Visualization.