Skip to content
Learn Python with BNHR

Check: Round 2 arrives

On this page
  1. Overview
  2. 2.1 · The contract for a survey round
  3. 2.2 · A short version of the report
  4. 3.1 · Which regions are far from the published totals?
  5. 4.1 · Drop duplicate persons
  6. 4.2 · Turn "not stated" codes into missing values
  7. 5.1 · Labels from a lookup table
  8. 5.2 · Count what a step changed

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.

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.

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.

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.

  1. Drop exact duplicates (identical rows): table.drop_duplicates().
  2. 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 with kind="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().
  3. 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.

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.

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:

  1. lookup = lookup.assign(code=lookup["code"].astype(str)), and rename code to the column's name and label to <column>_label;
  2. left-join the round to it on the column, with validate="many_to_one";
  3. 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.

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.

Something new

Identify results
LayerExplore a module
ModuleValues and names
AboutModule 2 of Python Essentials: numbers, text and true/false, names for them, f-strings and formats, and reading an error message. Its sections, its notebooks to read on the web, and the companion's solutions and explanations.
CoursePython Essentials, free