Module 2: Selecting and filtering · Lesson 2.5 · about 20 min
New columns from calculations
Add columns with arithmetic, conditions (np.where and np.select), rounding and clipping, and assign for several at once, the way analysts build revenue, margin and flag columns.
Module 2 · about 20 minutes · uses Lesson 2.3. You’ll be able to build the calculated columns most analyses need: revenue, margin, flags and bands.
In everyday terms: adding a column to a spreadsheet and filling the formula down, except there is nothing to fill: one line covers every row, now and in next month’s file.
1. Arithmetic
2. Yes/no and categories
Two outcomes: np.where(condition, if_true, if_false).
Several: np.select([conditions], [values], default=...), checked in order.
A True/False column is useful on its own: orders['is_promo'] = orders['discount'] > 0, then orders['is_promo'].mean() is the share of promo lines.
3. Several at once with assign
lambda d: ... means “using the table as it is at this step”, so margin_pct can use the margin made just before it.
- df['rev'] = df.qty * df.price
- Arithmetic on columns, row by row.
- df['big'] = df.qty >= 3
- A True/False column.
- np.where(cond, a, b)
- a where cond is True, else b.
- assign(...)
- Add several columns and return a new table.
- Physical meaning
- A new column is a new fact about each row, derived from facts you already have. Revenue is not in the file; it is quantity × price × (1 − discount) for each line.
“Discounts are already in the price.” Here unit_price is the list price and discount a share; revenue is qty × unit_price × (1 − discount). Always check what each column means before computing with it.
Watch it happen, one step at a time:
| qty | unit_price | discount |
|---|---|---|
| 2 | 20.0 | 0.1 |
| 1 | 5.0 | 0 |
| 3 | 10.0 | 0.2 |
revenue = qty * unit_price * (1 - discount)| qty | unit_price | discount | revenue |
|---|---|---|---|
| 2 | 20.0 | 0.1 | 36.0 |
| 1 | 5.0 | 0 | 5.0 |
| 3 | 10.0 | 0.2 | 24.0 |
4. Your turn
How it works
Principle: a new column is any expression that produces one value per row. Because the work is done on whole arrays, a column calculation on a million rows takes milliseconds; the same in a Python loop takes seconds or minutes (Lesson 8.8).
Rounding money: round at the end (when reporting), not at every step, or small rounding errors add up across thousands of rows. round(2) uses banker’s rounding at exact halves (2.675 → 2.67 can surprise you); for invoices, use Python’s decimal module.
5. Practice
- Add
unit_cost_after=unit_price × (1 − discount). - Flag orders over $100 as
high_value(True/False). - Bands: revenue < 10 ‘low’, < 50 ‘mid’, otherwise ‘high’. Which function?
Answers
df['unit_cost_after'] = df['unit_price'] * (1 - df['discount']).df['high_value'] = df['revenue'] > 100.np.select([df.revenue < 10, df.revenue < 50], ['low', 'mid'], default='high'), orpd.cut(Lesson 4.5).
New words: calculated column, np.where, np.select, assign, lambda.
- Column arithmetic works row by row for the whole table.
- Comparisons make True/False columns.
- np.where for two outcomes; np.select or pd.cut for more.
- assign adds columns inside a chain.
Check yourself
Sources
- pandas documentation, User Guide: 10 minutes to pandas (setting) https://pandas.pydata.org/docs/user_guide/10min.html#setting
- McKinney, W. (2022), Python for Data Analysis, 3rd edition, O'Reilly (open access) https://wesmckinney.com/book/
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.