Skip to content
Learn Python with BNHR

Get: Round 1 arrives

On this page
  1. Overview
  2. 1.1 · Turn any code into text with its leading zeros
  3. 3.1 · Load the code list
  4. 3.2 · A join that cannot copy rows
  5. 3.3 · Which codes are not in the code list?
  6. 3.4 · Region offices with proper codes
  7. 4.1 · Reshape: one row per region and year
  8. 4.2 · Read the special values
  9. 4.3 · Read every page
  10. 4.4 · Is Round 1 close to the published totals?

Every exercise in 10_get_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 1.1. Turn any code into text with its leading zeros

The task

Why it matters: every later step joins and compares on codes; one lost zero and a person lands in the wrong area, or in none.

Input → output: one code as it came out of a file (a decimal, a whole number, a text, or missing) and a width → the code as text of that width, or None.

Write code_to_text(value, width). It receives one code, as it came out of a file, and returns it as text, width characters long:

value width returns
100305000.0 (a decimal) 10 "0100305000"
5 (a whole number) 2 "05"
"0100305000" (already text) 10 "0100305000"
None, or NaN (missing in a number column) 10 None

Useful pieces: pd.isna(value) is True for None and NaN; isinstance(value, float) is True for a decimal; int(100305000.0) gives 100305000; str(...) makes text; .strip() removes spaces at both ends; .zfill(width) pads with zeros on the left.

Stretch: raise a ValueError with a clear message when the text is not made only of digits ("12A"), or is longer than width.

If the check is red: common causes

what you see the usual cause look at
You get '0100305000.0' or '00100305000.0' The decimal became text before it became a whole number: str(100305000.0) is '100305000.0'. the order of steps 2 and 3
An error such as 'float' object has no attribute 'zfill' .zfill was used on a number. It is a text method. step 3 comes before step 4
You get 'nan' or '0000000nan' for a missing value A missing value went on through the steps. step 1: return None straight away
' 0200207000 ' comes back with spaces The spaces around it were not removed. .strip() in step 3
code_to_text(5, 2) gives '0000000005' The width is fixed at 10 instead of using the input width. use width in .zfill(width)

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. Load the code list

The task

Why it matters: the code list is how you know which codes are real; read it wrongly and every join after it is wrong.

Input → output: the path of the workbook → a table with code, name, level, parent, one row per code-list row, codes as 10-character text.

Write load_code_list(path) so that it returns a clean table with four columns, code, name, level, parent, and every code as 10-character text.

Two pandas tools you need:

  • Renaming columns: table.rename(columns={"Old name": "new_name"}) returns the table with those columns renamed.
  • Keeping some rows: table[condition] keeps the rows where the condition is True. Here the condition is "the code is made only of digits": table["code"].str.fullmatch(r"\d+").fillna(False). (\d+ means "one or more digits"; .fillna(False) treats empty cells as "no".) This drops the blank row and the note under the table.

Do not remove anything else yet; you will see why in a moment.

If the check is red: common causes

what you see the usual cause look at
KeyError: 'code' A column is used by its new name before the rename. do step 2 (rename) before step 3
117 or 118 rows instead of 115 The empty row or the note under the table is still there. the condition in step 3
Codes have 9 characters Step 4 was skipped, or applied to another column. table["code"] = table["code"].map(...)
Extra columns, or the wrong order The table is returned with all its columns. step 5 keeps exactly four

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.2. A join that cannot copy rows

The task

Why it matters: every person needs their area's name, and to know whether their code exists, without anyone being copied or lost.

Input → output: Round 1 and the code list → Round 1 with area_name, area_level and in_code_list added, the same number of rows.

Write join_code_list(round_df, code_list). It returns Round 1 with three new columns: area_name, area_level, and in_code_list (True when the person's area code was found).

Tools you need:

  • table.drop_duplicates(subset="code", keep="first") keeps the first row for each code. Which row to keep is a decision: here the first row carries the official name ("City of …"). Write that reason as a comment in your code, so the next person knows.
  • left.merge(right, on="area_code", how="left", validate="many_to_one", indicator=True) joins on a column both tables have, and adds a column _merge that says, for each row, "both" (a match was found) or "left_only" (no match).
  • table.pop("_merge") takes that column out of the table and gives it to you; .eq("both") turns it into True/False.

If the check is red: common causes

what you see the usual cause look at
More rows than Round 1 A code is listed twice in the code list, so its people were copied. step 1: drop_duplicates before the join
MergeError: … not a many-to-one merge validate did its job: the right side still has a duplicate. step 1 must come before step 3
KeyError: 'area_code' in the merge The code list's code was not renamed to area_code, so the tables share no column. step 2
in_code_list is missing, or always True The indicator was not turned into a yes/no column. indicator=True and step 4

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.3. Which codes are not in the code list?

The task

Why it matters: these are the records to send back to the field office, with how many people and households each code affects.

Input → output: the joined Round 1 → one row per unknown area code, with persons and households, the largest first.

The people whose code has no match are the ones to report back to the field office. Write unmatched_codes(joined) that returns one row per unknown area code, with the number of people and of households that have it, the largest first:

area_code persons households
0600399000 6 2
… … …

Tools you need:

  • joined[~joined["in_code_list"]] keeps the rows not in the code list (~ means "not").
  • Grouping: table.groupby("area_code").agg(persons=("hhid", "size"), households=("hhid", "nunique")) makes one row per area code; size counts rows, nunique counts different values.
  • .reset_index() turns the area code back into a normal column; .sort_values(["persons", "area_code"], ascending=[False, True]) sorts by persons, largest first, then by code.

If the check is red: common causes

what you see the usual cause look at
households is the same as persons Both count rows. households counts different hhids: nunique
Every area code is listed The rows in the code list were not left out first. step 1: ~joined["in_code_list"]
The right numbers, in a different order The sort is missing or reversed. persons largest first, then area code
A column called hhid instead of persons The results were not named inside .agg(...). persons=("hhid", "size")

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.4. Region offices with proper codes

The task

Why it matters: the old system's offices can only be joined to Round 1 when both use the same kind of region code.

Input → output: a DuckDB connection with the old system attached → one row per office, with region as two-character text like '01'.

Write offices_by_region(con). It runs one SQL query on legacy.region_offices and returns a table with region (text, two characters, like '01'), region_name, office and email, ordered by region.

The SQL pieces you need, from the inside out:

  • CAST(region_code AS VARCHAR) turns the number 1 into the text '1' (VARCHAR means text);
  • lpad(text, 2, '0') pads text on the left with '0' until it is 2 characters: '1' becomes '01' (like .zfill in Python);
  • ... AS region gives the result the name region.

In Python, con.sql("SELECT ...").df() runs a query and gives the result as a pandas table. A text between triple quotes can span several lines, which keeps a long query readable.

If the check is red: common causes

what you see the usual cause look at
region is '1' instead of '01' The number became text but was not padded. lpad(..., 2, '0')
An error about converting or comparing types lpad was given a number. CAST(region_code AS VARCHAR) inside lpad
A column is missing Not all four columns are selected. region, region_name, office, email
The right rows in a different order No ORDER BY. ORDER BY 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. Reshape: one row per region and year

The task

Why it matters: a long table can be checked and joined row by row; a wide one cannot.

Input → output: the wide published table → one row per area and year, with geolocation, year (a whole number) and value_text.

Write published_long(wide):

  1. rename the column Geolocation to geolocation;
  2. melt: long = table.melt(id_vars="geolocation", var_name="year", value_name="value_text"). id_vars is the column to keep as it is; every other column becomes rows, with its name in year and its value in value_text;
  3. the years are now text ("2023"): make them whole numbers with long["year"] = long["year"].astype(int);
  4. return long. Keep value_text as text for now.

If the check is red: common causes

what you see the usual cause look at
year is text The year names came from the column headers, which are text. .astype(int) on year
A column is still called Geolocation The rename is missing. step 1
7 rows instead of 35 The table was not melted. .melt(id_vars="geolocation", ...)
KeyError: 'geolocation' in melt Melt used the new name before the rename. rename 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 4.2. Read the special values

The task

Why it matters: a placeholder must never become a zero: a zero is a real number, and would quietly lower every total.

Input → output: one published cell as text → a pair: (the number or None, its status).

Statistical tables mark cells that have no number:

  • .. means not available: the figure exists, but was not published;
  • - means not applicable: there can be no figure (the region did not exist yet).

Both must become missing (None), never zero, and you should keep the reason. Numbers have commas between thousands, which int() does not accept.

Write parse_published_value(text) that returns a pair (number, status). A pair is written with round brackets: return (1842300, "published").

text returns
".." (None, "not available")
"-" (None, "not applicable")
"1,842,300" (1842300, "published")

If the check is red: common causes

what you see the usual cause look at
ValueError: invalid literal for int() The commas are still there, or a placeholder reached int(...). remove commas; check .. and - first
' .. ' is not recognised The spaces around it were not removed. .strip() first
Only the number comes back The result is a single value, not a pair. return (number, status)
The status is close but not right The status texts must be exactly published, not available, not applicable. spelling

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.3. Read every page

The task

Why it matters: an API that splits its answer into pages gives you only part of the data unless you follow every page.

Input → output: the folder of saved pages → one table with all records: region, year (a whole number), value_text.

Write read_api(folder) that reads all pages, starting from page-1.json and following next until it is None, and returns one table with region, year (a whole number) and value_text.

This needs a while loop: it repeats its block as long as its condition is true.

page_name = "page-1.json"
while page_name is not None:
    ...                      # read this page, collect its records
    page_name = page.next    # move on (None after the last page ends the loop)

To read and check one page: page = PxPage.model_validate_json((folder / page_name).read_text(encoding="utf-8")). Then page.data is the list of records; for each record, region, year = record.key takes the key apart, and record.values[0] is the cell.

Collect one dictionary per record in a list (rows.append({...})), and at the end return pd.DataFrame(rows).

Stretch: keep a set of the pages already read, and raise a ValueError if next points back to one of them (a broken API could loop forever).

If the check is red: common causes

what you see the usual cause look at
15 records instead of 30 Only the first page was read. the loop must move on with page_name = page.next
The cell never finishes page_name never changes, so the loop never ends. page_name = page.next must be inside the loop (indented)
'dict' object has no attribute 'data' The page was read with json.loads, which gives dictionaries. PxPage.model_validate_json(...) gives page.data
year is text The key holds the year as text. int(year)

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.4. Is Round 1 close to the published totals?

The task

Why it matters: a survey round that is far from the published figures needs someone to look at it before it is released.

Input → output: Round 1, the published table, a year and a tolerance → one row per region with both totals, the difference in percent, and whether it is within the tolerance.

Write reconcile(round_df, published, year, tolerance) that returns one row per region:

region survey_total published difference_pct within_tolerance
01 1,842,300.0 1,842,300 0.0 True
  • survey_total: the sum of weight per region in the round: round_df.groupby("region", as_index=False)["weight"].sum(), then rename weight to survey_total;
  • published: the population of that year per region, from published (keep the rows where published["year"] == year, and the columns region and population, renamed to published);
  • join the two on region, with validate="one_to_one" (one row per region on each side);
  • difference_pct: (survey_total - published) / published * 100, rounded to 2 decimals with .round(2);
  • within_tolerance: True when the difference, without its sign (.abs()), is at most tolerance * 100. A tolerance of 0.01 means 1 %.

If the check is red: common causes

what you see the usual cause look at
difference_pct is 0.026 instead of 2.6 The share was not turned into percent. * 100
The signs are reversed The subtraction is the wrong way round. survey_total - published
Every region is (or is not) within the tolerance The tolerance and the difference are on different scales, or the sign is kept. abs(...) <= tolerance * 100
published is empty for every region The published table was not filtered to year, or regions do not match. published["year"] == year

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 capstone
CapstoneCapstone
AboutThe capstone of Arrays & DataFrames: in groups, build something that fixes a real problem with the farm survey or the prices, then show it working.
CourseArrays & DataFrames, free