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.
The idea
Make the value into clean text first, then pad it. Each kind of value a file can give you (missing, a decimal, a whole number, text with spaces around it) needs one small step before .zfill(width) can do its job, and the order of the steps matters.
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 code_to_text(value, width: int) -> str | None: """A code as text, `width` characters long, with the leading zeros a number column dropped.""" # Exercise 1.1: the task and examples are in the text above this cell. if value is None or pd.isna(value): return None if isinstance(value, float): if not value.is_integer(): raise ValueError(f"{value!r} is not a whole number, so it cannot be a code") value = int(value) text = str(value).strip() if not text.isdigit(): raise ValueError(f"{value!r} is not a code: codes are made only of digits") if len(text) > width: raise ValueError(f"{value!r} is longer than {width} digits") return text.zfill(width)Why each part is there
- Missing stays missing.
pd.isna(value)isTruefor bothNoneandNaN, which is how a number column stores an empty cell. ReturningNonestraight away stops a missing value from going through the later steps and coming out as the text'0000000nan'. - A decimal becomes a whole number before it becomes text. Stata and Excel hand codes over as decimals:
str(100305000.0)is'100305000.0', and padding that keeps the.0.int(...)first drops it.value.is_integer()refuses a decimal such as100305000.5, because a code with a fraction is not a code, and saying so is better than quietly cutting it off. str(value).strip()makes whole numbers and text the same kind of thing, and removes the spaces some files put around a code (' 0200207000 ').- Only digits, and not too long (the stretch).
.isdigit()refuses'12A', and the length check refuses a code longer thanwidth, because.zfillnever shortens anything. Both raise aValueErrorthat says what was wrong. The check passes without these two steps; they make the function safe on a file you have not seen yet. text.zfill(width)adds zeros on the left until the text iswidthcharacters long. It uses the inputwidth, not a fixed 10, socode_to_text(5, 2)gives'05'.
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 isTrue. 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.
The idea
Read the sheet exactly as it is, then make it clean in small steps: names you can type, only the rows that are codes, and every code back to its 10 characters.
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 load_code_list(path: Path) -> pd.DataFrame: """The geographic code list from its workbook: code, name, level, parent, with codes as 10-character text.""" # Exercise 3.1: the task and examples are in the text above this cell. table = pd.read_excel(path, header=3, dtype=str) table = table.rename(columns={"Code": "code", "Name": "name", "Geographic Level": "level", "Parent Code": "parent"}) table = table[table["code"].str.fullmatch(r"\d+").fillna(False)] table["code"] = table["code"].map(lambda v: code_to_text(v, 10)) table["parent"] = table["parent"].map(lambda v: code_to_text(v, 10)) return table[["code", "name", "level", "parent"]].reset_index(drop=True)Why each part is there
pd.read_excel(path, header=3, dtype=str): the column names are on the sheet's fourth row (header=3counts from 0), anddtype=strreads every cell as text, so pandas does not turn codes into numbers on the way in..rename(...)comes before anything that uses the new names. The other order is the first cause in the table above:KeyError: 'code'.table[table["code"].str.fullmatch(r"\d+").fillna(False)]keeps the rows whose code is digits only. That drops the blank row and the note under the table, and nothing else: the duplicate city stays, because which row to keep is a decision for Exercise 3.2..map(lambda v: code_to_text(v, 10))reuses your Exercise 1.1 on every code and every parent code. The workbook stored some codes as numbers, so they arrive without their leading zero; this puts it back, the same way it does for Round 1, so the two can be joined.table[["code", "name", "level", "parent"]]returns exactly the four columns, in that order, and.reset_index(drop=True)numbers the rows from 0 again after the filter left gaps.
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_mergethat 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 intoTrue/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.
The idea
A join can only add columns safely when each area code matches at most one row of the code list. So make the code list one row per code first, then join, and let validate prove 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 join_code_list(round_df: pd.DataFrame, code_list: pd.DataFrame) -> pd.DataFrame: """Round rows with their area's name and level, and whether the area code is in the code list.""" # Exercise 3.2: the task and examples are in the text above this cell. # Decision: where a code is listed twice, keep the first row, which carries the official name. keyed = code_list.drop_duplicates(subset="code", keep="first") keyed = keyed[["code", "name", "level"]].rename(columns={"code": "area_code", "name": "area_name", "level": "area_level"}) joined = round_df.merge(keyed, on="area_code", how="left", validate="many_to_one", indicator=True) joined["in_code_list"] = joined.pop("_merge").eq("both") return joinedWhy each part is there
drop_duplicates(subset="code", keep="first"), with its comment, is the decision: the code list names one city twice, and the first row carries the official name. Without this step, everyone in that city would be copied into two rows.- Keeping
code,name,leveland renaming themarea_code,area_name,area_levelgives the two tables one shared column to join on, and gives the new columns names that say what they describe. merge(..., how="left")keeps every person of Round 1, found or not.validate="many_to_one"stops with an error if a code is still listed twice, instead of quietly copying rows;indicator=Trueadds_merge, which says for each row whether its code was found.joined.pop("_merge").eq("both")takes_mergeout and keeps only the answer that matters, asTrueorFalseinin_code_list. The person count is unchanged, which is the point of the exercise.
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;sizecounts rows,nuniquecounts 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.
The idea
Keep the people whose code was not found, then make one row per unknown code that says how many people, and how many households, carry 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 unmatched_codes(joined: pd.DataFrame) -> pd.DataFrame: """One row per area code that is not in the code list: area_code, persons, households.""" # Exercise 3.3: the task and examples are in the text above this cell. missing = joined[~joined["in_code_list"]] table = missing.groupby("area_code").agg(persons=("hhid", "size"), households=("hhid", "nunique")).reset_index() return table.sort_values(["persons", "area_code"], ascending=[False, True]).reset_index(drop=True)Why each part is there
joined[~joined["in_code_list"]]keeps the rows wherein_code_listisFalse:~turns eachTrueintoFalseand back.groupby("area_code").agg(persons=("hhid", "size"), households=("hhid", "nunique"))makes one row per code.sizecounts the rows, one per person;nuniquecounts the different household ids, because one household has several people. Counting rows for both is the first cause in the table above..reset_index()turnsarea_codeback into an ordinary column, and.sort_values(["persons", "area_code"], ascending=[False, True])puts the codes that affect the most people first; the code breaks ties, so the order is the same every time. That is the list the field office needs, most urgent first.
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 number1into the text'1'(VARCHARmeans text);lpad(text, 2, '0')pads text on the left with'0'until it is 2 characters:'1'becomes'01'(like.zfillin Python);... AS regiongives the result the nameregion.
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.
The idea
Let the database do it: turn the old system's number into text, pad it to two characters, and name the result region, all inside the query.
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 offices_by_region(con: duckdb.DuckDBPyConnection) -> pd.DataFrame: """The old system's regional offices, with the region as a two-character code.""" # Exercise 3.4: the task and examples are in the text above this cell. return con.sql(""" SELECT lpad(CAST(region_code AS VARCHAR), 2, '0') AS region, region_name, office, email FROM legacy.region_offices ORDER BY region """).df()Why each part is there
CAST(region_code AS VARCHAR)makes the number1into the text'1'.lpadworks on text only; given the number, the query stops with a type error.lpad(..., 2, '0')pads on the left to two characters, as.zfill(2)does in Python, so the region1becomes'01', the form Round 1 uses.AS regionnames the result, so it can be joined to Round 1'sregion.ORDER BY regionreturns the offices in region order. A database does not promise any order without it, so the same query could come back differently another time.con.sql(...).df()runs the query and hands back a pandas table, which is what the rest of the notebook uses.
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):
- rename the column
Geolocationtogeolocation; - melt:
long = table.melt(id_vars="geolocation", var_name="year", value_name="value_text").id_varsis the column to keep as it is; every other column becomes rows, with its name inyearand its value invalue_text; - the years are now text (
"2023"): make them whole numbers withlong["year"] = long["year"].astype(int); - return
long. Keepvalue_textas 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.
The idea
The published table has one column per year. Melting turns those columns into rows, so each row is one area in one year, the shape a check or a join can use.
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 published_long(wide: pd.DataFrame) -> pd.DataFrame: """The published table with one row per area and year: geolocation, year, value_text.""" # Exercise 4.1: the task and examples are in the text above this cell. long = wide.rename(columns={"Geolocation": "geolocation"}).melt(id_vars="geolocation", var_name="year", value_name="value_text") long["year"] = long["year"].astype(int) return longWhy each part is there
.rename(columns={"Geolocation": "geolocation"})comes first, because the melt names the column it keeps..melt(id_vars="geolocation", var_name="year", value_name="value_text")keepsgeolocationon every row and turns each other column into rows: its header goes intoyear, its cell intovalue_text. Seven areas by five years make 35 rows.long["year"].astype(int): the years came from column headers, which are text ("2023"). As whole numbers they compare and join with the round'syear.value_textstays text on purpose: some cells are..or-, not numbers. Exercise 4.2 reads them, one cell at a time.
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.
The idea
Check for the two placeholders first, and only then make a number. A placeholder becomes None with its reason, never a zero.
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 parse_published_value(text: str) -> tuple[int | None, str]: """A published cell as (number, status): status is 'published', 'not available' (..) or 'not applicable' (-).""" # Exercise 4.2: the task and examples are in the text above this cell. text = str(text).strip() if text == "..": return None, "not available" if text == "-": return None, "not applicable" return int(text.replace(",", "")), "published"Why each part is there
str(text).strip()removes the spaces around a cell, so' .. 'is recognised as.....and-are tested beforeint(...)is tried, becauseint("..")would stop with aValueError. Each returnsNoneand its own status: "not available" (the figure exists but was not published) is a different fact from "not applicable" (there can be no figure).int(text.replace(",", ""))removes the thousands separators, whichintdoes not accept, and returns the number with the status "published".return None, "not available"returns a pair, the same asreturn (None, "not available"). Returning0for a placeholder would look like a real figure and quietly lower every total it is added to.
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.
The idea
An API that splits its answer into pages says where the next page is. Keep reading and following next until there is none, collecting every record on the way.
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 read_api(folder: Path) -> pd.DataFrame: """All records from the API's pages: region, year, value_text.""" # Exercise 4.3: the task and examples are in the text above this cell. rows = [] seen = set() page_name = "page-1.json" while page_name is not None: if page_name in seen: raise ValueError(f"the API's pages loop back to {page_name}") seen.add(page_name) page = PxPage.model_validate_json((folder / page_name).read_text(encoding="utf-8")) for record in page.data: region, year = record.key rows.append({"region": region, "year": int(year), "value_text": record.values[0]}) page_name = page.next return pd.DataFrame(rows)Why each part is there
while page_name is not None:repeats as long as there is a page to read.page_name = page.next, inside the loop, moves on; the last page'snextisNone, which ends it. Outside the loop, the same line would leave the loop reading page 1 forever.PxPage.model_validate_json(...)reads the page and checks its shape in one step, sopage.dataandpage.nextexist, or the page is refused with a message that says what is wrong.- For each record,
region, year = record.keytakes the key apart, and one dictionary per record goes intorows, withint(year)because the key holds the year as text.pd.DataFrame(rows)turns the list into the table at the end. seenis the stretch: a broken API whosenextpoints back to a page already read would loop forever, so the function stops with aValueErrorthat names the page instead.
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 ofweightperregionin the round:round_df.groupby("region", as_index=False)["weight"].sum(), then renameweighttosurvey_total;published: thepopulationof thatyearperregion, frompublished(keep the rows wherepublished["year"] == year, and the columnsregionandpopulation, renamed topublished);- join the two on
region, withvalidate="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:Truewhen the difference, without its sign (.abs()), is at mosttolerance * 100. A tolerance of0.01means 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.
The idea
Total Round 1 per region, put the published figure for the same year beside it, and measure the gap in percent of the published figure. Then flag the regions whose gap is bigger than the tolerance.
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 reconcile(round_df: pd.DataFrame, published: pd.DataFrame, year: int, tolerance: float = 0.01) -> pd.DataFrame: """Weighted persons per region in the round, against the published population of `year`.""" # Exercise 4.4: the task and examples are in the text above this cell. survey = round_df.groupby("region", as_index=False)["weight"].sum().rename(columns={"weight": "survey_total"}) totals = published.loc[published["year"] == year, ["region", "population"]].rename(columns={"population": "published"}) out = survey.merge(totals, on="region", how="left", validate="one_to_one") out["difference_pct"] = ((out["survey_total"] - out["published"]) / out["published"] * 100).round(2) out["within_tolerance"] = out["difference_pct"].abs() <= tolerance * 100 return outWhy each part is there
round_df.groupby("region", as_index=False)["weight"].sum()is the survey's estimate: each person counts as their weight, the number of people they stand for. Counting rows instead would give the sample's size, not the population.published.loc[published["year"] == year, ["region", "population"]]keeps the asked year only. Without the filter, each region would have five published rows, and the join'svalidate="one_to_one"would stop.(survey_total - published) / published * 100is the difference in percent of the published figure: positive when the survey is above it.* 100makes0.026read as2.6, and.round(2)keeps it readable.difference_pct.abs() <= tolerance * 100puts both sides on one scale (a tolerance of0.01is 1 %) and ignores the sign, because too high and too low both need someone to look. In Round 1, one region comes out 2.6 % above its published figure, so the flag has something to find.