Every exercise in 83_stretch_day3.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.
Stretch S3.1. Complementary suppression
The task
suppress hides small cells, but a province's total is published too: when only one of its cells is hidden, the total minus the visible cells gives it back (the explainer in 31_share_round.py, section 3, shows it). Write suppress_complementary(table, threshold):
- hide every cell with fewer respondents than the threshold, and mark it
"small cell"in a columnsuppressed; - in each province where exactly one cell is hidden that way, also hide the province's other cell with the fewest respondents, marked
"complementary"(the first one, if two tie); suppressedis empty text for a published cell;weighted_personsis empty for both kinds of hidden cell;respondentsis dropped.
The input has the columns province, age_group, respondents and weighted_persons, like explain.SUPPRESSION_TABLE. A province where every cell is small has nothing left to hide.
If the check is red
At 10, one cell of 0600300000 is small, so a second is hidden; at 30, two are small, which is safe already.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the check's hint above covers the usual slip. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Hide the small cells, then look at each province: where only one cell is hidden, the published total minus the visible cells would give it back, so hide one more, the next smallest. Each hidden cell says why it was hidden.
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 suppress_complementary(table: pd.DataFrame, threshold: int) -> pd.DataFrame: """Small cells hidden, and one more cell in each province where a single hidden cell could be worked out.""" reason = pd.Series("", index=table.index).mask(table["respondents"] < threshold, "small cell") for _, cells in table.groupby("province"): hidden = reason[cells.index] != "" if hidden.sum() == 1 and (~hidden).any(): reason[cells.loc[~hidden, "respondents"].idxmin()] = "complementary" return table.assign(weighted_persons=table["weighted_persons"].where(reason == ""), suppressed=reason).drop(columns="respondents")Why each part is there
pd.Series("", index=table.index).mask(table["respondents"] < threshold, "small cell")starts every cell's reason as empty text and writes"small cell"where the cell is below the threshold.for _, cells in table.groupby("province"):looks at one province at a time.hidden.sum() == 1finds a province with exactly one small cell;(~hidden).any()makes sure there is another cell to hide, so a province where every cell is already small is left alone.cells.loc[~hidden, "respondents"].idxmin()picks the visible cell with the fewest respondents (the first, on a tie) and marks it"complementary": hiding the smallest one loses the least information..where(reason == "")empties the figure of both kinds of hidden cell,suppressed=reasonsays which kind, andrespondentsis dropped, as in Exercise 3.1. At a threshold of 10 one cell of0600300000is small, so a second is hidden; at 30 two are, which is safe already.
Stretch S3.2. Parts that add up to the whole
The task
Round each part of a distribution on its own, and the parts may add up to 999 or 1,001 per thousand: a reader who adds them finds the release "wrong". The largest remainder method fixes it. Write per_thousand(values): for a Series of non-negative numbers, each value's share per 1,000, as whole numbers that add up to exactly 1,000:
- the exact shares:
values / values.sum() * 1000; - round each down (
numpy.floor, or// 1); - hand out what is missing to 1,000, one each, to the values with the largest remainders (exact minus rounded down); on a tie, the earlier value first;
- keep the Series' index, and give back whole numbers (
.astype(int)).
Tip: remainders.sort_values(ascending=False, kind="stable") sorts largest first and keeps ties in their order.
If the check is red
Three equal parts give 334, 333 and 333: the first one gets the spare unit.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the check's hint above covers the usual slip. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Round every share down, which leaves a few thousandths unassigned, then give them out one each to the shares that lost the most in rounding. The parts then add up to exactly 1,000, and no share is off by more than one.
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 per_thousand(values: pd.Series) -> pd.Series: """Each value's share per 1,000, as whole numbers adding up to exactly 1,000 (largest remainder method).""" exact = values / values.sum() * 1000 rounded = exact // 1 missing = int(1000 - rounded.sum()) for label in (exact - rounded).sort_values(ascending=False, kind="stable").index[:missing]: rounded[label] += 1 return rounded.astype(int)Why each part is there
values / values.sum() * 1000is each part's exact share per thousand, andexact // 1rounds it down, so the rounded parts can only add up to 1,000 or less.missing = int(1000 - rounded.sum())is how many thousandths are left to give out: never more than the number of parts.(exact - rounded)is what each part lost in rounding..sort_values(ascending=False, kind="stable")puts the largest losses first and keeps ties in their original order, so the earlier part wins a tie;.index[:missing]takes as many as there are thousandths to give.- Each of those gets one more, and
.astype(int)returns whole numbers with the Series' own labels. Three equal parts give 334, 333 and 333.
Stretch S3.3. The crosswalk the other way, by shares
The task
In 30_new_edition.py you mapped Round 3 back to the old geography, because new → old never copies anyone. Sometimes the question goes the other way: Round 1's totals in the new geography. A municipality that split has two successors, and the old data cannot say who lives in which; the usual answer is to share out its total by each successor's share in a round that has both (Round 3).
Write allocate_to_2024q2(totals, crosswalk, shares):
totals:area_code_2023q4andweighted_persons, one row per old code;crosswalk:code_2024q2andcode_2023q4, as from yourload_crosswalk;shares:code_2024q2andshare, only for successors of a split: the shares of one old code's successors add up to 1;- result:
code_2024q2andweighted_persons, one row per new code, sorted by code. A code with no successor in the crosswalk keeps its own code; a successor with no share gets the whole total (share 1).
Join the totals to the crosswalk (old → new: validate="one_to_many", since one old code may have two successors), then to the shares (validate="many_to_one"), multiply, and add up by new code. The grand total must not change: check it yourself.
If the check is red
The split's 2,000 becomes 800 and 1,200; 0600303000 keeps its own code; the grand total stays 3,800.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the check's hint above covers the usual slip. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Old to new can copy people, because an old code that split has two successors. So the old total is shared out between them by each one's share in a round that has both, and the grand total stays the same.
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 allocate_to_2024q2(totals: pd.DataFrame, crosswalk: pd.DataFrame, shares: pd.DataFrame) -> pd.DataFrame: """Totals in 2023Q4 geography shared out to 2024Q2 codes.""" joined = totals.merge(crosswalk, left_on="area_code_2023q4", right_on="code_2023q4", how="left", validate="one_to_many") joined["code_2024q2"] = joined["code_2024q2"].fillna(joined["area_code_2023q4"]) joined = joined.merge(shares, on="code_2024q2", how="left", validate="many_to_one") joined["weighted_persons"] = joined["weighted_persons"] * joined["share"].fillna(1.0) return joined.groupby("code_2024q2", as_index=False)["weighted_persons"].sum().sort_values("code_2024q2", ignore_index=True)Why each part is there
- The first join goes from each old code to its new codes with
validate="one_to_many": one old code may become two, which is allowed here and nowhere else in the course. joined["code_2024q2"].fillna(joined["area_code_2023q4"]): a code the crosswalk does not know keeps its own code, so its people are not lost.- The second join brings in each successor's
share(validate="many_to_one": one share per new code)..fillna(1.0)gives every other code its whole total, so only a split is divided. weighted_persons * share, thengroupby("code_2024q2").sum(), gives one total per new code, sorted by code. The split's 2,000 becomes 800 and 1,200, and the grand total stays 3,800: check that yourself on any real allocation.
Stretch S3.4. A canned query with two parameters
The task
Write region_sex_sql(): the release's cells for one region and one sex, with the columns province, age_group, weighted_persons and suppressed, sorted by province then age group. Two parameters, :region and :sex: Datasette shows a box for each.
Then add it to your portal: the button below adds it to datasette.json as a second canned query, beside region_summary, and adds suppressed to the table's facets. Restart Datasette to see it.
If the check is red
Region 01, Female: three cells, sorted by province then age group.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the check's hint above covers the usual slip. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Exercise 3.1's canned query with a second parameter: Datasette shows a box for each :name in the SQL and fills them from what the visitor types.
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 region_sex_sql() -> str: """The release's cells for one region and one sex.""" return """ SELECT province, age_group, weighted_persons, suppressed FROM release_table WHERE substr(province, 1, 2) = :region AND sex = :sex ORDER BY province, age_group """Why each part is there
WHERE substr(province, 1, 2) = :region AND sex = :sexkeeps one region's provinces and one sex; both values come from the visitor as parameters, never pasted into the SQL.- The query lists the cells themselves (
province,age_group,weighted_persons,suppressed), not sums: a visitor sees which cells are hidden, and why a total would be incomplete. ORDER BY province, age_groupfixes the order, so the same question always gives the same table: region 01, Female, gives three cells.
Stretch S3.5. Matching names to codes
The task
Another agency sends a list of local governments by name: reference/lgu_names_other_agency.csv, with the province's name and a count of facilities. To join it to anything, each name needs its code. Names are written differently (Penablanca, Sta. Cruz, TALAVERA), one has a typo, two are not municipalities at all, and the same municipality name exists in several provinces: the province is part of the match.
Write match_names(lgus, places):
lgus:lgu_nameandprovince, as in the file;places:code,nameandprovincefor every city and municipality (fromplaces()below);- result: the rows of
lguswith three more columns:code,matched_name(the code list's name), andmatch:"exact"when the two names are the same once normalised,"close"whendifflib.get_close_matches(name, candidates, n=1, cutoff=0.8)finds one among the same province's normalised names, or"none"(and empty code and name).
Normalise a name: lower case; accents removed (unicodedata.normalize("NFKD", text), then drop the characters for which unicodedata.combining(c) is true); sta. written santa, a leading s. written san; spaces collapsed.
A close match is a guess: a report should show it for a person to confirm, never join on it silently.
If the check is red
The two San Jose rows get two different codes: one per province. Cabiaa is close to Cabiao.
The solution, and why it works
Try it yourself first: the check names the input that failed, and the check's hint above covers the usual slip. Open this when you have been stuck for ten minutes, or when your check is green and you want to compare.
The idea
Make both lists of names comparable first: same case, no accents, the usual abbreviations written out. Then look for the name among the same province's municipalities: exactly, or as a close guess that a person must confirm.
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 match_names(lgus: pd.DataFrame, places: pd.DataFrame) -> pd.DataFrame: """Each name matched to a code within its province: exact once normalised, close, or none.""" def normalise(text: str) -> str: text = "".join(c for c in unicodedata.normalize("NFKD", text.lower()) if not unicodedata.combining(c)) text = " ".join(text.split()).replace("sta. ", "santa ") return "san " + text[3:] if text.startswith("s. ") else text
found = [] for name, province in zip(lgus["lgu_name"], lgus["province"]): candidates = places[places["province"] == province] by_name = dict(zip(candidates["name"].map(normalise), zip(candidates["code"], candidates["name"]))) key = normalise(name) close = difflib.get_close_matches(key, list(by_name), n=1, cutoff=0.8) if key in by_name: found.append((*by_name[key], "exact")) elif close: found.append((*by_name[close[0]], "close")) else: found.append((None, None, "none")) return lgus.assign(code=[f[0] for f in found], matched_name=[f[1] for f in found], match=[f[2] for f in found])Why each part is there
normaliselower-cases the name and removes accents (unicodedata.normalize("NFKD", ...)splitsñintonplus a mark, and the marks are dropped), writessta.assantaand a leadings.assan, and collapses spaces, soSta. CruzandSanta Cruzbecome the same text.places[places["province"] == province]keeps only the same province's candidates, because municipality names repeat across provinces: the two San Jose rows get two different codes.by_namemaps each candidate's normalised name to its code and its name as the code list writes it. An exact match is a dictionary lookup.difflib.get_close_matches(key, list(by_name), n=1, cutoff=0.8)finds the most similar name, if any is at least 80 % alike:CabiaafindsCabiao. It is marked"close", a guess for a person to confirm, never joined on silently; with no match the code and name stay empty and the match says"none".lgus.assign(...)returns the agency's rows unchanged, with the three new columns beside them, in the same order.