Lesson 01 · Data Science → AI/ML Engineering

Select, filter, aggregate

The three moves behind most data questions at work: which columns, which rows, what number.

≈ 15 minLearn → Practice → ReviewBuilds on: your pandas Days 1–4

How this works

  1. Learn. Each piece of syntax comes with what it returns, plus a short example you can run and change. For beginners, studying worked examples before solving problems leads to more learning than problem-solving alone.[1]
  2. Practice. You write code that the page checks automatically. The hints point back to the syntax section, not to the answer.
  3. Practice, then review. After the lesson, the practice set drills each skill until it's mastered. Then short cards show up on the Daily review page, spaced further apart each time you get them right. Spaced practice beats cramming for long-term memory.[2]

Why write it yourself? In Anthropic's 2026 trial, engineers who handed coding to an AI scored below 40% on a follow-up quiz. Those who worked to understand the code scored 65% or higher.[3]

Every example below uses this table. pd and df are already loaded. You can bring the table back anytime with the ▦ df table button (bottom-left) or the T key. Note that Cho's sales value is missing (NaN).

regionrepsales
0EastAna120.0
1WestBen80.0
2EastChoNaN
3NorthDev200.0
4WestEli150.0

Part 1 · Learn

The syntax, and what each piece returns

Run each example, then change something (a column name, a number) and run it again. Changing the code and checking you were right is how you learn it.

ASelect columns

SyntaxReturns
df["sales"]A Series: one column, still with the row labels (0–4).
df[["rep", "sales"]]A DataFrame. A list of names inside the brackets (so double brackets) gives back a table.

BCompare → boolean mask

SyntaxReturns
df["sales"] > 100A Series of True/False values, one per row. This is a boolean mask.
> >= < <= == !=The comparison operators. == means "equals" and != means "not equal".
NaN > 100False. Any comparison with a missing value is False.

CFilter rows with a mask

SyntaxReturns
df[mask]A DataFrame containing only the rows where the mask is True. The original row labels are kept, so they won't be renumbered 0, 1, 2.
df[mask][["rep"]]Filter first, then pick columns. Each step returns a new table, so you can chain them.

DCombine conditions

SyntaxMeans
(a) & (b)AND: both are true
(a) | (b)OR: at least one is true
~(a)NOT: flips True and False

Rule: put parentheses around every condition. Python evaluates & before > and ==, so leaving them out breaks the code. Also note that the words and/or/not don't work on masks.

EAggregate: many values → one number

SyntaxReturns
.sum() .mean() .min() .max()A single number. They skip NaN by default (skipna=True).
.count()The number of non-missing values.
len(df)The number of rows, missing values included. This is plain Python, not a pandas method.
.fillna(0)A new Series with the gaps filled. The original is unchanged, so you can chain an aggregation straight after it.

Part 2 · Practice

Now write it yourself

Before each one, fill in the # predict: line. Try it without hints first. If you need one, the hint points back to the syntax section.

1 · The big deals

Put every row with sales over 100 into big.

Hint

Combine B and C: build the mask, then put it inside df[ ].

After it passes: what happened to Cho?

NaN > 100 is False, so missing rows drop out of filters without any error or warning.

2 · How complete is the data?

Set n_recorded to how many reps have a sales value, and n_rows to how many rows the table has.

Hint

See E. One of these skips NaN and the other doesn't.

Why this matters at work

Comparing count() with len() is the quickest way to check for missing data. Run it before you trust any average.

3 · Average deal size, two ways

Set avg to the mean of sales (pandas default). Then set avg_zero to the mean with the missing value counted as 0.

Hint

See E. For the second one, fill the gaps first and then aggregate.

After it passes: which number is "right"?

That depends on whether the missing value means "unknown" or "sold nothing". It's a business question. An AI will pick one answer without telling you, so you're the one who has to know to ask.

4 · Debug the AI's code

An assistant wrote this to find big deals in the East. Run it as it is first and read the error. Then fix it so east_big holds the right rows.

Hint

Look at the rule in D.

Stretch · Filter, then pick columns

Set report to only the rep and sales columns for deals not in the East. Use ~.

Hint

Use D for ~, then chain the column selection from C.

Part 3 · Remember

Spaced review starts tomorrow

Next, do the practice set until this lesson's skills are mastered (3 clean solves each). Then its 11 review cards start appearing on the Daily review page from the next day. The cards use a different table, so you have to recall the syntax rather than an answer. After each card you rate it Easy, Medium or Hard; easier cards come back later (up to 90 days), and a card you had to look up comes back tomorrow. This is the Leitner box system.[4] It takes about 5 minutes a day.

Lost the syntax? The pandas syntax sheet has all of today's syntax on one page.

Use it at work tomorrow

Pick one small data question you'd normally give to an AI, like a filter, a count or an average. Write it yourself first with a # predict: line, and run count() against len() before you trust the average.

Primary source

Python for Data Analysis §5.2 Indexing, Selection, and Filtering and §5.3 Summarizing, by Wes McKinney, the creator of pandas. The official edition is free online.

Ask me anything. I'm your teacher for this course. Paste any error or confusing output and I'll explain the idea behind it. Still open: tell me 3–5 tasks you do at work in a normal week, and I'll build a lesson around them.

[1] Worked-example effect (Sweller, 1988 onward) · [2] Cepeda et al., Distributed practice in verbal recall tasks, Psychological Bulletin 2006 (317 experiments) · [3] Anthropic, AI assistance and coding skills, 2026 · [4] Nicky Case, How to Remember Anything Forever-ish