Every exercise in 30_new_edition.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. Which edition is each code in?
The task
Why it matters: a typing error is in no edition; a code from a new edition is in that edition. That one fact decides what to fix.
Input → output: a list of codes, and a dictionary of editions ({"2023Q4": {...codes...}, "2024Q2": {...}}) → a table with one row per code: code, and editions, the editions it is in, joined with ", ", or "none".
| code | editions |
|---|---|
0100105000 |
2023Q4, 2024Q2 |
0100306000 |
2024Q2 |
0600399000 |
none |
For each code, keep the edition names whose set contains it: [name for name, codes in editions.items() if code in codes]. Build one dictionary per row and return pd.DataFrame(rows).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
editions holds a list, like ['2024Q2'] |
The names were not joined into one text. | ", ".join(found) |
An empty text instead of none |
No edition was found, and nothing replaced the empty result. | ... if found else "none" |
| KeyError | The dictionary is looked up by code instead of looping over it. | for name, codes in editions.items() |
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
For each code, ask every edition whether it has it, and list the ones that do. A code in no edition is a typing error; a code in only the new edition belongs to the new geography. That one fact decides what to fix.
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 editions_of(codes: list[str], editions: dict[str, set[str]]) -> pd.DataFrame: """For each code, the editions of the code list it appears in (or 'none').""" # Exercise 2.1: the task and examples are in the text above this cell. rows = [] for code in codes: found = [name for name, edition in editions.items() if code in edition] rows.append({"code": code, "editions": ", ".join(found) if found else "none"}) return pd.DataFrame(rows)Why each part is there
for code in codes:goes through the codes one at a time, and[name for name, edition in editions.items() if code in edition]keeps the names of the editions whose set contains it, in the dictionary's order.inon a set is a quick yes or no, however long the code list.", ".join(found) if found else "none"turns the list into one text,2023Q4, 2024Q2, and saysnonewhen nothing was found, instead of an empty text that is easy to miss.- One dictionary per code goes into
rows, andpd.DataFrame(rows)makes the table, with the codes in the order they came.
Exercise 3.1. Load the crosswalk
The task
Why it matters: the crosswalk comes from the code list's publisher, inside the new workbook. Reading it with the same care as the code list keeps every code intact.
Input → output: the path of the 2024Q2 workbook → a table with code_2024q2 and code_2023q4, both as 10-character text, one row per 2024Q2 code.
Read it as in the Get module's Exercise 3.1 (header=3, dtype=str), keep the rows whose Code is made of digits, keep the columns Code and Code (2023Q4 edition), rename them to code_2024q2 and code_2023q4, pad both to 10 characters (.str.zfill(10)), and drop duplicate rows.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| KeyError: 'Code (2023Q4 edition)' | The column name is not exactly the workbook's. | copy it from the workbook's header row |
| Codes have 9 characters | The workbook stores codes as numbers. | .str.zfill(10) on both columns |
| More rows than codes | Blank rows or the note under the table are still there. | keep the rows whose Code is digits |
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 crosswalk is read with the same care as the Get module's code list: every cell as text, only the rows that are codes, both code columns back to 10 characters. One row per new code, saying which old code it came from.
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_crosswalk(path) -> pd.DataFrame: """The crosswalk from the 2024Q2 workbook: code_2024q2 and code_2023q4, 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[table["Code"].str.fullmatch(r"\d+").fillna(False)] crosswalk = table[["Code", "Code (2023Q4 edition)"]].rename(columns={"Code": "code_2024q2", "Code (2023Q4 edition)": "code_2023q4"}) crosswalk = crosswalk.assign(code_2024q2=crosswalk["code_2024q2"].str.zfill(10), code_2023q4=crosswalk["code_2023q4"].str.zfill(10)) return crosswalk.drop_duplicates().reset_index(drop=True)Why each part is there
pd.read_excel(path, header=3, dtype=str)and the digits-only filter are the Get module's Exercise 3.1 again: the header on the fourth row, every cell as text, and the blank row and note under the table dropped.table[["Code", "Code (2023Q4 edition)"]]keeps the two columns that matter, under the workbook's exact names, and.rename(...)gives them names that say which edition each code is in..str.zfill(10)on both columns puts back the leading zeros the workbook lost by storing codes as numbers. The columns are already text, so.zfillworks directly here..drop_duplicates()removes repeated rows, so each 2024Q2 code appears once, which Exercise 3.2'svalidate="many_to_one"relies on. Two 2024Q2 codes can still point to one 2023Q4 code: that is the split municipality.
Exercise 3.2. Round 3 in 2023Q4 geography
The task
Why it matters: with every round on the same codes, a municipality's numbers can be followed from round to round.
Input → output: a round's rows (with area_code in 2024Q2 codes) and the crosswalk → the same rows, with a new column area_code_2023q4. A code that is not in the crosswalk (an unknown code you decided to keep) keeps its own value.
- left-join the rows to the crosswalk:
left_on="area_code", right_on="code_2024q2", withvalidate="many_to_one"; area_code_2023q4is the crosswalk'scode_2023q4, or, where that is missing, the row's ownarea_code:joined["code_2023q4"].fillna(joined["area_code"]);- drop the crosswalk's two columns and return. The number of rows must not change.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
The unknown code has an empty area_code_2023q4 |
Rows not in the crosswalk were not given their own code. | .fillna(joined["area_code"]) |
| MergeError | The crosswalk lists a 2024Q2 code twice. | drop_duplicates() in Exercise 3.1 |
| Extra columns | The crosswalk's columns are still there. | drop code_2024q2 and code_2023q4 |
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 up each person's 2024Q2 code in the crosswalk and add the 2023Q4 code it maps to. A code the crosswalk does not know (an unknown code you chose to keep) keeps its own value, so no person is dropped or left without a 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 to_edition_2023q4(rows: pd.DataFrame, crosswalk: pd.DataFrame) -> pd.DataFrame: """The rows, with area_code_2023q4: each 2024Q2 area code mapped to the 2023Q4 code it came from.""" # Exercise 3.2: the task and examples are in the text above this cell. joined = rows.merge(crosswalk, left_on="area_code", right_on="code_2024q2", how="left", validate="many_to_one") joined["area_code_2023q4"] = joined["code_2023q4"].fillna(joined["area_code"]) return joined.drop(columns=["code_2024q2", "code_2023q4"])Why each part is there
rows.merge(crosswalk, left_on="area_code", right_on="code_2024q2", how="left", validate="many_to_one")keeps every row, and stops if the crosswalk lists a 2024Q2 code twice. The two columns have different names, so the join is told which is which.joined["code_2023q4"].fillna(joined["area_code"])takes the old code where the crosswalk gave one, and the row's own code where it did not.- Dropping the crosswalk's two columns leaves the round as it was, plus
area_code_2023q4: five rows in, five rows out, as the check's hint says.