Module 2: Reading and Understanding Tables · Lecture 6 of 32
Answer a focused question without accidentally changing the wrong rows. Build boolean conditions, choose columns explicitly, and distinguish a sorted view from the underlying business key.
What you will learn
- Combine boolean conditions with correct parentheses.
- Select labels with loc and positions with iloc.
- Perform one-step assignments compatible with Copy-on-Write.
Understand the problem
Filter a population before ranking it
Suppose a manager asks for the three largest non-cancelled paid-channel orders. The correct population excludes cancellations and includes only the paid channel before ranking net value. Sorting the full table first and then taking three rows can answer a different question. State the population in a mask so a reviewer can read it independently of the presentation.
Boolean operators act element by element
Use & for and, | for or, and ~ for not when combining pandas boolean Series. Parenthesize each comparison because Python operator precedence does not match the way an English sentence is read. The Python words and and or expect a single truth value and cannot decide the truth of an entire Series. Explicitly handle missing values in a mask when unknown membership needs a policy.
Assignments need one owner
In pandas 3.x, Copy-on-Write makes derived objects behave independently for mutation. A chained expression such as table[mask][‘flag’] = True does not reliably mean “update the original table.” Assign through table.loc[mask, ‘flag’] instead. When a filtered table is a separate analytical product, treat it as a separate object and name it accordingly.
Choose deterministic ordering
Tied values can appear in a different order if the sort has no tie-breaker. For stable reports sort by net_paise descending and order_id ascending. Row position is a presentation detail; order_id is the entity key. Keep the key when exporting a ranked subset so another analyst can locate the same source records.
Run the worked example
Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 6 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.
from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT
orders = load_orders()
mask = orders['channel'].eq('paid') & orders['status'].ne('cancelled')
top = (orders.loc[mask, ['order_id', 'channel', 'net_paise']]
.sort_values(['net_paise', 'order_id'], ascending=[False, True])
.head(3))
tagged = orders.copy()
tagged['review'] = False
tagged.loc[tagged['net_paise'].ge(200000), 'review'] = True
print(top.to_string(index=False))
print('Flagged for review:', int(tagged['review'].sum()))
assert top['net_paise'].is_monotonic_decreasing
assert top['channel'].eq('paid').all()
assert 'review' not in orders.columns
Verified output
order_id channel net_paise
O0024 paid 319600
O0084 paid 319600
O0156 paid 319600
Flagged for review: 33
Walk through the result
The mask is a Series aligned to the original index. loc uses it to select rows and an explicit list to select columns. The second sort key makes ties reproducible. tagged is a separate table; its review flag does not change orders. The final assertion tests that separation, while the other assertions verify population membership and ordering. The threshold is an example review policy, not an automatic declaration that large orders are wrong.
Common mistakes and diagnosis
Do not use iloc with an order ID or loc with a presumed position. Do not remove parentheses from a compound condition. Sorting does not validate a key or repair duplicates. If a filter unexpectedly returns no rows, inspect normalized category values and missingness before changing the condition to a looser substring match. A spelling mismatch should not be concealed with a broad filter.
Practice assignment
Select the five most recent completed organic orders, retaining order_id, ordered_at, and net_paise. Add a boolean high_value column to a separate copy using a 1000 INR threshold. Check that the original table is unchanged.
Worked solution and explanation
Add the following code below the worked example, or run the matching file in the solutions folder. Compare the result with the business definition before trusting a passing assertion.
subset = orders.loc[orders['status'].eq('completed') & orders['channel'].eq('organic'),
['order_id', 'ordered_at', 'net_paise']]
recent = subset.sort_values(['ordered_at', 'order_id'], ascending=[False, True]).head(5).copy()
recent['high_value'] = recent['net_paise'].ge(100000)
assert len(recent) <= 5
assert recent['ordered_at'].is_monotonic_decreasing
assert 'high_value' not in orders.columns
Check your understanding
Why can a two-key sort be important even when only one metric appears in the report?
Answer guide
Ties need a stable ordering rule if the report selects a top N, creates a snapshot, or compares outputs across runs. Without a tie-breaker, two equally valid row orders can produce different selected keys. Sorting by the metric and then a unique key makes the reporting rule explicit and repeatable.
Reference and next step
Download the complete course kit: datasets, all examples, solutions, and capstone.
Return to the full Data Analysis with Python and pandas curriculum.
