Every exercise in 20_check_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. The contract for a survey round
The task
Why it matters: this schema is the single place where "a correct round" is defined. Every later round is checked against it, and in the Store module the database's own rules are generated from it.
Input → output: the set of area codes in the code list → a pandera DataFrameSchema.
The schema below is half written. The comments mark the six rules to add; the explainer and the table above show every piece you need. The rules a correct round must meet:
| column or rule | the rule |
|---|---|
age |
a number between 0 and 120; may be missing (a person who did not say) |
sex |
"1" or "2"; may be missing |
hours |
a number between 0 and 112 (the hours in a week a person can work, at most 16 a day); may be missing |
weight |
a number greater than 0; never missing |
| each person once | no two rows share hhid and member_no |
| region matches area | region is the first two characters of area_code |
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Row 8 is flagged | A rule refuses missing values where they are allowed. | nullable=True on age, sex, hours |
| Row 6 is not flagged | The uniqueness rule is missing. | unique=["hhid", "member_no"] |
| Row 7 is not flagged | The region rule is missing, or compares the wrong part. | t["region"] == t["area_code"].str[:2] |
| SchemaError: expected series … to have type float64, got int64 | A number column without coerce=True. |
add coerce=True to age, hours, weight |
| NameError inside a lambda | The table is called t inside the lambda. |
lambda t: t["region"] ... |
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 schema is the round's contract, written once: each column says what it may hold, and the checks on the whole table say what must hold across columns. The six rules each fill one gap in the skeleton.
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 survey_schema(area_codes: set[str]) -> pa.DataFrameSchema: """The contract: the rules every staged round must meet.""" # Exercise 2.1: the task and examples are in the text above this cell. return pa.DataFrameSchema( { "round": pa.Column(str), "hhid": pa.Column(str), "member_no": pa.Column(int, pa.Check.ge(1), coerce=True), "region": pa.Column(str, pa.Check.isin(REGIONS)), "area_code": pa.Column(str, pa.Check.str_matches(r"^\d{10}$")), "age": pa.Column(float, pa.Check.in_range(0, 120), nullable=True, coerce=True), "sex": pa.Column(str, pa.Check.isin(["1", "2"]), nullable=True), "employed": pa.Column(str, pa.Check.isin(["1", "2"]), nullable=True), "hours": pa.Column(float, pa.Check.in_range(0, 112), nullable=True, coerce=True), "weight": pa.Column(float, pa.Check.gt(0), coerce=True), "in_code_list": pa.Column(bool), }, checks=[ pa.Check(lambda t: t["region"] == t["area_code"].str[:2], name="region is the start of the area code"), pa.Check(lambda t: t["hours"].isna() | t["employed"].eq("1"), name="hours only for the employed"), pa.Check(lambda t: t["in_code_list"] == t["area_code"].isin(area_codes), name="in_code_list says whether the code is in the code list"), ], unique=["hhid", "member_no"], )Why each part is there
- Age and hours:
pa.Column(float, pa.Check.in_range(0, 120), nullable=True, coerce=True).in_rangeincludes both ends;nullable=Truelets a person who did not answer through, which is the first cause in the table above when it is missing;coerce=Truereads whole numbers as decimals, because a column with a missing value is stored as decimals and the type must match. Hours are the same within_range(0, 112). - Sex:
pa.Column(str, pa.Check.isin(["1", "2"]), nullable=True). The codes are text, as the round stores them, so the list holds"1"and"2", not1and2. - Weight:
pa.Column(float, pa.Check.gt(0), coerce=True), withoutnullable: every person must stand for some people, so a missing or zero weight is refused. - Each person once:
unique=["hhid", "member_no"]belongs to the whole schema, not to one column: no two rows may share both values. That is why the copy of row 0 in row 6 flags both rows. - Region matches area:
pa.Check(lambda t: t["region"] == t["area_code"].str[:2], name=...)sits inchecks=[...], because it compares two columns. The lambda receives the whole table astand returns oneTrueorFalseper row; the name is what the failure report shows.
Exercise 2.2. A short version of the report
The task
Why it matters: a report of dozens of rows is hard to read in a meeting. A summary with one row per rule says what is wrong and how often, and points to where to look.
Input → output: the failures table (columns column, check, failure_case, index) → one row per column and rule, with failures (how many) and example (the first failing value), the most frequent first.
| column | check | failures | example |
|---|---|---|---|
| age | in_range(0, 120) | 6 | 999.0 |
| … | … | … | … |
Tools: grouping, as in the Get module (primer §5 (90_primers.py)): table.groupby(["column", "check"], dropna=False).agg(failures=("failure_case", "size"), example=("failure_case", "first")). dropna=False keeps rules that involve several columns, whose column is empty. Then .reset_index() and sort by failures, largest first, then by check.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| A rule is missing from the summary | Rules on several columns have no column, and groupby drops empty keys by default. |
dropna=False |
| The counts are right, the order is not | The sort is missing or reversed. | failures largest first, then check |
example holds a count |
The second result counts again. | example=("failure_case", "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
One row per rule that failed: group the failures by column and rule, count them, and keep one example of what failed, most frequent first.
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 failure_summary(failures: pd.DataFrame) -> pd.DataFrame: """One row per column and rule: how many failures, and one example value. Most frequent first.""" # Exercise 2.2: the task and examples are in the text above this cell. summary = failures.groupby(["column", "check"], dropna=False).agg(failures=("failure_case", "size"), example=("failure_case", "first")).reset_index() return summary.sort_values(["failures", "check"], ascending=[False, True]).reset_index(drop=True)Why each part is there
groupby(["column", "check"], dropna=False): rules on several columns (region against area code, hours only for the employed) have nocolumn, andgroupbyleaves out empty keys unless told otherwise. Withoutdropna=Falsethose rules vanish from the summary..agg(failures=("failure_case", "size"), example=("failure_case", "first"))names both results:sizecounts the failing rows,firstkeeps the first value that failed, so the summary shows what to look for..reset_index()turns the two group keys back into columns, and.sort_values(["failures", "check"], ascending=[False, True])puts the most frequent problem first and breaks ties by the rule's name, so the order is the same every time..reset_index(drop=True)numbers the rows from 0 again, as the check expects.
Exercise 3.1. Which regions are far from the published totals?
The task
Why it matters: this is the comparison you will run on every round from now on; written as a function, it can.
Input → output: a round, the published population (region, year, population), a year and a tolerance → the sorted list of regions whose weighted total is further than the tolerance from the published figure.
| call | returns |
|---|---|
regions_outside(round2, published, 2023, 0.01) |
["04"] |
regions_outside(round2, published, 2023, 0.05) |
[] |
It is the Get module's Exercise 4.4, made shorter: sum the weights per region, compare with the published population, keep the regions where abs(survey - published) / published is greater than the tolerance.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Every region is listed | The tolerance is compared in percent (1) instead of as a share (0.01), or the sign is kept. | divide by published; abs(...); > tolerance |
| The wrong year's figures | The published table was not filtered to year. |
published["year"] == year |
NaN in the comparison |
The two Series have regions in a different form (1 and '01'). |
both indexed by the text region |
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 Get module's comparison, as a question with a short answer: which regions are further from the published figure than the tolerance allows?
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 regions_outside(round_df: pd.DataFrame, published: pd.DataFrame, year: int, tolerance: float) -> list[str]: """The regions whose weighted total is further than `tolerance` (0.01 = 1 %) from the published population.""" # Exercise 3.1: the task and examples are in the text above this cell. survey = round_df.groupby("region")["weight"].sum() totals = published[published["year"] == year].set_index("region")["population"] gap = (survey - totals.reindex(survey.index)).abs() / totals.reindex(survey.index) return sorted(gap[gap > tolerance].index)Why each part is there
round_df.groupby("region")["weight"].sum()is the survey's estimate per region, as a Series indexed by region.published[published["year"] == year].set_index("region")["population"]is the published figure for the asked year, indexed the same way, so the two line up region by region..reindex(survey.index)puts the published figures in the survey's order; a region the publication lacks gets a missing value, and is never listed.(survey - totals).abs() / totalsis the gap as a share of the published figure, without its sign. The tolerance is a share too (0.01is 1 %), so the two compare directly; comparing a share with a percent is the first cause in the table above.sorted(gap[gap > tolerance].index)keeps the regions further than the tolerance and returns them as a sorted list. With Round 2's data that is region 04 at 1 %, and none at 5 %.
Exercise 4.1. Drop duplicate persons
The task
Why it matters: a person counted twice is counted twice in every total. The rule for which copy stays must be explicit, so it can be explained and repeated.
Input → output: a round and a rule ("first" or "most-complete") → the round with each person (hhid, member_no) once.
- Drop exact duplicates (identical rows):
table.drop_duplicates(). - Then, for people still listed more than once:
- rule
"first": keep the first:table.drop_duplicates(subset=["hhid", "member_no"], keep="first"); - rule
"most-complete": keep the row with the fewest missing values; if equal, the first. One way: add a column with each row's number of missing values (table.isna().sum(axis=1)), sort by it withkind="stable"(so equal rows keep their order), drop duplicates keeping the first, drop the helper column, and put the rows back in their original order with.sort_index().
- rule
- Return the table with
.reset_index(drop=True).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Four rows remain | The exact duplicates were not dropped first. | step 1 |
| Both rules give the same result | The rule is not used. | if rule == "first": ... else: ... |
| The right rows, in another order | The sort for "most-complete" was not undone. | .sort_index() before returning |
| A helper column is still there | The count of missing values was kept. | .drop(columns="_missing") |
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
First remove rows that are exact copies, which carry no choice. Then, for people still listed twice, apply the rule the participant chose, so which copy stays is a decision written in the configuration, not an accident of order.
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 drop_duplicate_persons(table: pd.DataFrame, rule: Literal["first", "most-complete"]) -> pd.DataFrame: """Each person once: exact duplicates dropped, then the rule decides between different rows for the same person.""" # Exercise 4.1: the task and examples are in the text above this cell. table = table.drop_duplicates() if rule == "first": table = table.drop_duplicates(subset=["hhid", "member_no"], keep="first") else: ranked = table.assign(_missing=table.isna().sum(axis=1)).sort_values("_missing", kind="stable") table = ranked.drop_duplicates(subset=["hhid", "member_no"], keep="first").drop(columns="_missing").sort_index() return table.reset_index(drop=True)Why each part is there
table.drop_duplicates()removes rows identical in every column. Done first, so the rule only has to decide between copies that really differ; skipping it is the first cause in the table above.- Rule
"first":drop_duplicates(subset=["hhid", "member_no"], keep="first")keeps the first row for each person, in file order. - Rule
"most-complete": a helper column counts each row's missing values (table.isna().sum(axis=1)); sorting by it withkind="stable"puts the fullest copy first and keeps file order among equals;drop_duplicates(..., keep="first")then keeps that copy. The helper column goes, and.sort_index()puts the rows back in their original order. .reset_index(drop=True)numbers the rows from 0 again. Either rule leaves three rows in the check's table; they differ only in which copy of household H2's person stays.
Exercise 4.2. Turn "not stated" codes into missing values
The task
Why it matters: an age of 999 left in the data raises every average; a missing age does not. Taking the codes from the old system's table, not typing them into your code, means a new code added there is used here too.
Input → output: a round and the codes table (variable, code, meaning) → the round with each listed code in its column replaced by a missing value.
For each row of the codes table (for row in codes.itertuples():), if the round has that column:
- if the column holds numbers (
pd.api.types.is_numeric_dtype(table[row.variable])), compare as a number:table[row.variable] == row.code; - otherwise it holds text codes: compare as text,
table[row.variable] == str(row.code); - where it matches, set a missing value:
table.loc[matches, row.variable] = None.
Work on a copy (table = table.copy() first), so the table you were given does not change, and return it.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| The 999s are still there | The number column was compared as text: '999.0' is not '999'. |
compare numbers as numbers |
| Sex 9 is still there | The text column was compared with the number 9. | str(row.code) for text columns |
| KeyError: 'employed' | A variable in the codes table is not a column of this table. | skip it: if row.variable not in table.columns: continue |
| The region 999 became missing | A column not in the codes table was changed. | change only row.variable |
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 codes that mean "not stated" live in the old system's table, so take them from there: for each listed code, find it in its column and make it missing. A new code added to that table is then handled here with no change to the code.
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 blank_missing_codes(table: pd.DataFrame, codes: pd.DataFrame) -> pd.DataFrame: """The table with every "not stated" code (from the codes table) replaced by a missing value.""" # Exercise 4.2: the task and examples are in the text above this cell. table = table.copy() for row in codes.itertuples(): if row.variable not in table.columns: continue column = table[row.variable] matches = column == row.code if pd.api.types.is_numeric_dtype(column) else column == str(row.code) table.loc[matches, row.variable] = None return tableWhy each part is there
table = table.copy()first, so the table you were given does not change under the caller.for row in codes.itertuples():gives one listed code at a time, withrow.variable,row.codeandrow.meaning. A variable this round does not have is skipped withcontinue, because the codes table lists variables of other files too.- A number column is compared with the number, a text column with the code as text:
999is not"999", and the text"999.0"would match neither.pd.api.types.is_numeric_dtype(...)says which kind the column is. table.loc[matches, row.variable] = Nonechanges only the matching cells of that one column, so a region that happens to be'999'stays, as the check's hint says.
Exercise 5.1. Labels from a lookup table
The task
Why it matters: codes are for joining; labels are for people. Taking labels from the lookup table, by a join, means nobody types "Female" by hand, and a code with no label stands out.
Input → output: a round, a lookup table (code as a whole number, label) and a column name → the round with a new column <column>_label.
The old system stores codes as numbers (1), the round as text ("1"): a join between them matches nothing (the Get module's lesson). Make the lookup's code text first:
lookup = lookup.assign(code=lookup["code"].astype(str)), and renamecodeto the column's name andlabelto<column>_label;- left-join the round to it on the column, with
validate="many_to_one"; - return the joined table. The number of rows must not change.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Every label is missing | The lookup's codes are numbers, the round's are text. | .astype(str) on the lookup's code |
| KeyError in the merge | The lookup's code was not renamed to the column's name. |
rename(columns={"code": column, ...}) |
| The label column has another name | It must be f"{column}_label". |
the rename of label |
| More rows than before | The lookup lists a code twice. | validate="many_to_one" will say so |
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
Labels come from the lookup table by a join, never typed by hand. The one trap is the code's type: the old system stores numbers, the round stores text, and a join between them matches nothing.
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 add_labels(table: pd.DataFrame, lookup: pd.DataFrame, column: str) -> pd.DataFrame: """The table with a <column>_label column, from a lookup table of codes and labels.""" # Exercise 5.1: the task and examples are in the text above this cell. lookup = lookup.assign(code=lookup["code"].astype(str)).rename(columns={"code": column, "label": f"{column}_label"}) return table.merge(lookup[[column, f"{column}_label"]], on=column, how="left", validate="many_to_one")Why each part is there
lookup["code"].astype(str)makes the lookup's codes text, the form the round uses, so1and"1"meet. Without it every label comes out missing, the first cause in the table above.- Renaming
codeto the column's name (sex) andlabeltof"{column}_label"(sex_label) gives the join its shared column and the new column its name, for any column the function is given. merge(..., how="left", validate="many_to_one")keeps every person, labelled or not, and stops if the lookup lists a code twice, so the row count cannot grow. A missing sex simply has no label, which is what the check expects.
Exercise 5.2. Count what a step changed
The task
Why it matters: a cleaning step that changes 5 values is a fix; one that changes 5,000 is probably a bug. The cleaning report counts every step's changes, so anyone can see what cleaning did.
Input → output: a column before a step and the same column after it (same rows, same order) → how many values differ, counting "missing before and missing after" as no change.
before.ne(after) is True where the values differ, but also where both are missing (to pandas, missing is not equal to missing). Remove those: & ~(before.isna() & after.isna()). Count the Trues with .sum(), and return a whole number with int(...).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| One too many for every missing value | Missing in both is counted as a change. | & ~(before.isna() & after.isna()) |
The result is True or a Series |
The count is missing. | .sum(), then int(...) |
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
Count the values a cleaning step changed. The one subtlety is missing values: to pandas, missing is not equal to missing, so "missing before and missing after" must be taken out by hand.
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 count_changed_values(before: pd.Series, after: pd.Series) -> int: """How many values differ between two versions of a column; missing in both counts as unchanged.""" # Exercise 5.2: the task and examples are in the text above this cell. return int((before.ne(after) & ~(before.isna() & after.isna())).sum())Why each part is there
before.ne(after)isTruewherever the two differ, value by value, and also wherever both are missing.~(before.isna() & after.isna())isTrueeverywhere except where both are missing, and&combines the two, so a missing value left missing is not counted. Without it every missing value counts as a change, the first cause in the table above..sum()counts theTrues andint(...)returns a plain whole number, the count the cleaning report shows for the step.