Every exercise in 31_share_round.py, with the solution and why it works. Each answer stays closed until you swear you tried it first. Opening one changes nothing in your notebook; your check still has to go green from your own code. Read the whole notebook, with its lessons.
Exercise 2.1. Age groups
The task
Why it matters: a release groups ages into bands: single years would make too many small cells.
Input → output: an age (a number, or missing) → its group's label, or None when the age is missing.
| age | group |
|---|---|
| 0 to 14 | "0-14" |
| 15 to 24 | "15-24" |
| 25 to 44 | "25-44" |
| 45 to 64 | "45-64" |
| 65 and over | "65+" |
AGE_GROUPS (in the setup cell) lists them as (lowest, highest, label): loop over it and return the label whose range holds the age. Check for a missing age first (pd.isna(age)).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| 14 or 15 in the wrong band | < instead of <= at one end. |
both ends included |
| An error for a missing age | NaN reached the comparison. |
check pd.isna(age) first |
Still stuck after ten minutes? Ask your table, then raise your hand.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Look the age up in the list of bands and return the first band that holds it. A missing age is not in any band, so it gives None before any comparison is tried.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def age_group(age) -> str | None: """The age group label for an age, or None when the age is missing.""" # Exercise 2.1: the task and examples are in the text above this cell. if age is None or pd.isna(age): return None for lowest, highest, label in AGE_GROUPS: if lowest <= age <= highest: return label return NoneWhy each part is there
if age is None or pd.isna(age): return Nonecomes first, because comparingNaNwith a number is alwaysFalse, and a missing age would quietly fall through every band.for lowest, highest, label in AGE_GROUPS:takes each band apart, andlowest <= age <= highestincludes both ends, so 14 is in0-14and 15 in15-24. Using<at one end is the first cause in the table above.- The bands come from
AGE_GROUPS, not from numbers typed into the function, so changing the release's bands is one change in the setup cell. The last band's highest age is large enough that every real age has a band; the finalreturn Noneonly answers what no band holds.
Exercise 2.2. People by province, sex and age group
The task
Why it matters: this is the table the release is about. Built by code from the stored round, it is the same table every quarter.
Input → output: a round's stored rows → one row per province, sex and age group, with respondents (how many people answered: rows) and weighted_persons (the sum of their weights, rounded to a whole number).
Prepare three columns, then group:
province: the first five characters ofarea_code, then"00000"; wherearea_codeis empty (an unknown area),"unknown";sex:sex_label, with missing values as"Not stated";age_group:agethrough yourage_group(.map(age_group)), with missing values as"Not stated";- group:
.groupby(["province", "sex", "age_group"]).agg(respondents=("hhid", "size"), weighted_persons=("weight", "sum")), then.reset_index(), and roundweighted_personswith.round().
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Fewer than six respondents in total | Rows with a missing province, sex or age were dropped: groupby leaves out missing keys. |
fill them with "unknown" / "Not stated" first |
Provinces like 01001 |
The "00000" was not added. |
area_code.str[:5] + "00000" |
Decimals in weighted_persons |
Not rounded. | .round() |
Still stuck after ten minutes? Ask your table, then raise your hand.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Prepare three columns to group by, giving every missing value a label of its own, then count and add up. A person with an unknown area, sex or age still counts; they just land in their own cell.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def release_table(rows: pd.DataFrame) -> pd.DataFrame: """People by province, sex and age group: respondents and weighted persons.""" # Exercise 2.2: the task and examples are in the text above this cell. prepared = rows.assign( province=(rows["area_code"].str[:5] + "00000").fillna("unknown"), sex=rows["sex_label"].fillna("Not stated"), age_group=rows["age"].map(age_group).fillna("Not stated"), ) table = prepared.groupby(["province", "sex", "age_group"]).agg(respondents=("hhid", "size"), weighted_persons=("weight", "sum")).reset_index() return table.assign(weighted_persons=table["weighted_persons"].round())Why each part is there
provinceis the area code's first five characters plus"00000", the province's own code in the code list. Where the area is unknown the code is missing, so the result is missing too, and.fillna("unknown")gives it a label.sexcomes fromsex_label, andage_groupfrom your Exercise 2.1 through.map(age_group); both fill missing values with"Not stated".- Those labels matter because
groupbyleaves out rows whose keys are missing: without them the release would quietly lose people, the first cause in the table above. With them, six people in give six respondents out. .agg(respondents=("hhid", "size"), weighted_persons=("weight", "sum"))counts the people who answered and adds up the people they stand for;.round()makes the weighted figure a whole number of people.
Exercise 3.1. Suppress small cells
The task
Why it matters: this is the step that makes the release safe to publish. It runs on every release, by code, so no small cell slips through by hand.
Input → output: the release table and a threshold → the table ready to publish: a column suppressed (True where respondents is below the threshold), weighted_persons empty where suppressed, and no respondents column (the number of respondents in a cell is not published).
Tools: a condition (primer §2 (90_primers.py)), .where(condition) (keeps values where the condition is True, empty elsewhere), and .drop(columns=...).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
respondents is still there |
It was not dropped. | .drop(columns="respondents") |
| Small cells show a 0 instead of nothing | A zero is a number: it says "no one", which is false. | .where(~small) leaves them empty |
| The cell with exactly 30 is hidden | <= instead of <. |
below the threshold only |
Still stuck after ten minutes? Ask your table, then raise your hand.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
A cell built from too few respondents could point to a real person, so its figure is hidden. The table says which cells are hidden, and it never publishes the respondent counts that decided it.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def suppress(table: pd.DataFrame, threshold: int) -> pd.DataFrame: """The table ready to publish: small cells hidden and marked, respondents removed.""" # Exercise 3.1: the task and examples are in the text above this cell. small = table["respondents"] < threshold return table.assign(weighted_persons=table["weighted_persons"].where(~small), suppressed=small).drop(columns="respondents")Why each part is there
small = table["respondents"] < thresholdmarks the cells below the threshold. At exactly the threshold the cell is published, so the comparison is<, not<=.table["weighted_persons"].where(~small)keeps the figure where the cell is not small and leaves it empty where it is. Empty, not 0: a zero would say "no one lives here", which is false.suppressed=smalladds the column that tells a reader which cells were hidden on purpose, so an empty cell is never mistaken for a missing figure..drop(columns="respondents"): the number of respondents in a cell is not published, because a small one is exactly what suppression protects.
Exercise 4.1. A codebook from the contract
The task
Input → output: a pandera schema → one row per column, with:
| column of the codebook | from the pandera column | example |
|---|---|---|
column |
its name | sex |
description |
column.description |
Sex, from the questionnaire |
type |
str(column.dtype), put in words with TYPE_NAMES |
text |
may be missing |
column.nullable, as "yes" or "no" |
no |
rules |
each check's check.error, joined with "; "; empty text when there are none |
isin(['Female', 'Male', 'Not stated']) |
Loop over schema.columns.items(): each item is a column's name and its pandera column. TYPE_NAMES.get(text, text) gives the words for a type, or the type itself when it is not in the list.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
rules shows objects like <Check in_range…> |
The checks were joined, not their text. | check.error |
rules shows in_range without its limits |
check.name is the rule's name only. |
check.error has the values |
type shows float64 |
The type was not put in words. | TYPE_NAMES.get(...) |
may be missing is True or False |
The yes/no was not written as text. | "yes" if column.nullable else "no" |
Still stuck after ten minutes? Ask your table, then raise your hand.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
The codebook is read off the contract itself, column by column, so it can never describe the release differently from how it was checked.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def codebook(schema: pa.DataFrameSchema) -> pd.DataFrame: """One row per column of the schema: what it means, its type, whether it may be missing, and its rules.""" # Exercise 4.1: the task and examples are in the text above this cell. rows = [{"column": name, "description": column.description, "type": TYPE_NAMES.get(str(column.dtype), str(column.dtype)), "may be missing": "yes" if column.nullable else "no", "rules": "; ".join(check.error for check in column.checks)} for name, column in schema.columns.items()] return pd.DataFrame(rows)Why each part is there
schema.columns.items()gives each column's name and its pandera column, which carries itsdescription, itsdtype, whether it isnullable, and itschecks.TYPE_NAMES.get(str(column.dtype), str(column.dtype))puts the type in words,textinstead ofstr, and falls back to the type itself for one the list does not name."yes" if column.nullable else "no"writes the answer as a reader expects it, not asTrueorFalse."; ".join(check.error for check in column.checks)lists each rule with its values (check.errorreads likeisin(['Female', 'Male', 'Not stated']);check.namewould give onlyisin). A column with no rules joins nothing and gets an empty text.- One dictionary per column, in a list comprehension, and
pd.DataFrame(rows)makes the codebook.
Exercise 5.1. Open every file you wrote
The task
Why it matters: an export can "succeed" and still write the wrong thing: a CSV whose empty cells became zeros, a database missing rows. The only proof is to read each file back.
Input → output: a release folder → one row per data file (release_table.parquet, release_table.csv, release.sqlite), with file, rows, and suppressed hidden: True when, in that file, every row marked suppressed has an empty weighted_persons.
Read each with its own reader: pd.read_parquet(path), pd.read_csv(path), and for SQLite pd.read_sql_query("SELECT * FROM release_table", con) with con = sqlite3.connect(path) (close it after: with closing(sqlite3.connect(path)) as con:). SQLite stores True as 1, so make the column yes/no with .astype(bool) before using it as a condition.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| The CSV with zeros passes | Only the row count is checked. | the suppressed rows' weighted_persons must be empty |
| KeyError: 'suppressed' in the SQLite table | The query did not select every column. | SELECT * FROM release_table |
| SQLite's suppressed rows are not found | 1 was compared with True without converting. |
.astype(bool) |
Still stuck after ten minutes? Ask your table, then raise your hand.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the common causes above cover most red checks. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
An export can succeed and still write the wrong thing, so read each file back with its own reader and check two facts: how many rows it holds, and that every suppressed cell is really empty in it.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def check_release(folder: Path) -> pd.DataFrame: """For each data file of a release: how many rows, and whether every suppressed cell is empty.""" # Exercise 5.1: the task and examples are in the text above this cell. tables = {"release_table.parquet": pd.read_parquet(folder / "release_table.parquet"), "release_table.csv": pd.read_csv(folder / "release_table.csv")} with closing(sqlite3.connect(folder / "release.sqlite")) as con: tables["release.sqlite"] = pd.read_sql_query("SELECT * FROM release_table", con) return pd.DataFrame([{"file": name, "rows": len(t), "suppressed hidden": bool(t.loc[t["suppressed"].astype(bool), "weighted_persons"].isna().all())} for name, t in tables.items()])Why each part is there
- Each format has its reader:
pd.read_parquet,pd.read_csv, and for SQLite a query through a connection.with closing(sqlite3.connect(...)) as con:closes the connection when the block ends, even after an error, so the file is never left locked. rowsislen(t): a wrong row count is the first damage a broken export shows.t.loc[t["suppressed"].astype(bool), "weighted_persons"].isna().all()takes the suppressed rows' figures and asks whether all of them are empty. SQLite storesTrueas1, hence.astype(bool); a CSV whose empty cells came back as zeros fails here, which a row count alone would miss.bool(...)returns a plainTrueorFalse, and one dictionary per file makes the table the check compares.