Every exercise in 82_stretch_day2.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 S2.1. The contract as a class
The task
pandera can also write a schema as a class, one attribute per column, which some teams find easier to read. Write SurveyModel, a pa.DataFrameModel with the same column rules as your survey_schema for age, sex, hours and weight, and the each-person-once rule (class Config: unique = ["hhid", "member_no"]). Columns look like age: Series[float] = pa.Field(ge=0, le=120, nullable=True, coerce=True).
The check runs SurveyModel.validate(table, lazy=True) on a small table and compares which rows fail.
If the check is red
Row 3 is all missing values, which is allowed; rows 0 and 4 are the same person.
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
The same contract as survey_schema, written as a class: one attribute per column, with the column's type in its annotation and its rules in pa.Field(...). Some teams find it easier to read, and it validates the same way.
The class, 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.
class SurveyModel(pa.DataFrameModel): hhid: Series[str] member_no: Series[int] = pa.Field(ge=1, coerce=True) age: Series[float] = pa.Field(ge=0, le=120, nullable=True, coerce=True) sex: Series[str] = pa.Field(isin=["1", "2"], nullable=True) hours: Series[float] = pa.Field(ge=0, le=112, nullable=True, coerce=True) weight: Series[float] = pa.Field(gt=0, coerce=True)
class Config: unique = ["hhid", "member_no"]Why each part is there
age: Series[float] = pa.Field(ge=0, le=120, nullable=True, coerce=True)says what Exercise 2.1'spa.Column(float, pa.Check.in_range(0, 120), nullable=True, coerce=True)says:geandleare "at least" and "at most". Hours are the same withle=112.sex: Series[str] = pa.Field(isin=["1", "2"], nullable=True)keeps the codes as text, andweight: Series[float] = pa.Field(gt=0, coerce=True)has nonullable, so a missing weight fails.class Config: unique = ["hhid", "member_no"]is the each-person-once rule; in the class form, rules on the whole table go inConfig. That is why rows 0 and 4, the same person, both fail, while row 3, all missing, passes.
Stretch S2.2. The clean round as an Excel file, codes intact
The task
Colleagues will ask for Excel. Write write_excel(clean, path): write the table to an .xlsx file so that, opened again, every code column (hhid, area_code, region, sex, employed) is still text, leading zeros included, and return the number of rows written. pandas writes text as text; the trap is reading it back without dtype=str, and Excel itself turning 0100105000 into a number when someone types over it. As a second sheet named about, write one row per column with the column's name and a description you choose.
The check writes a small table with your function and reads it back with dtype=str.
If the check is red
Two sheets, 'round' and 'about', with codes read back unchanged.
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
pandas writes text as text, so the codes stay intact in the file; the danger is reading them back as numbers. Write both sheets in one go with an ExcelWriter, and leave the zeros to whoever reads it with dtype=str.
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 write_excel(clean: pd.DataFrame, path: Path) -> int: """Write the round and an 'about' sheet to Excel. Returns the number of rows written.""" about = pd.DataFrame({"column": clean.columns, "description": [f"the column {c}" for c in clean.columns]}) with pd.ExcelWriter(path) as writer: clean.to_excel(writer, sheet_name="round", index=False) about.to_excel(writer, sheet_name="about", index=False) return len(clean)Why each part is there
aboutis a small table, one row per column with its name and a description, built fromclean.columns, so it can never list a column the data does not have. The descriptions are yours to improve; the check only needs the sheet.with pd.ExcelWriter(path) as writer:opens one file for both sheets and saves it when the block ends;to_excel(writer, sheet_name=..., index=False)writes each sheet, without pandas' row numbers.- The codes are text in
clean, so Excel stores them as text, leading zeros included. The check reads them back withdtype=strand finds them unchanged; read without it, pandas would guess they are numbers and drop the zeros, the Get module's lesson again. return len(clean)returns the number of rows written.
Stretch S2.3. Several rounds, all or nothing
The task
load_round keeps one round all-or-nothing. A quarterly release may need several rounds to change together: if one fails, none should. Write load_rounds(con, table, rounds), where rounds is a dictionary {round_id: rows}: one transaction for all of them; on any error, roll back and raise. Return the total number of rows of those rounds in the table afterwards.
If the check is red
When the second round fails, the first must not change either: H0 must still be there.
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
One transaction around all the rounds, not one per round: if any round fails, every round, including those already loaded in this call, goes back to how it was.
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_rounds(con: duckdb.DuckDBPyConnection, table: str, rounds: dict[str, pd.DataFrame]) -> int: """Replace several rounds in one transaction. Returns their total number of rows afterwards.""" con.execute("BEGIN TRANSACTION") try: for round_id, rows in rounds.items(): con.execute(f"DELETE FROM {table} WHERE round = ?", [round_id]) con.register("incoming", rows) con.execute(f"INSERT INTO {table} BY NAME SELECT * FROM incoming") con.unregister("incoming") con.execute("COMMIT") except Exception: con.execute("ROLLBACK") raise placeholders = ", ".join("?" for _ in rounds) return con.execute(f"SELECT count(*) FROM {table} WHERE round IN ({placeholders})", list(rounds)).fetchall()[0][0]Why each part is there
BEGIN TRANSACTIONonce, then for each round Exercise 3.1's delete and insert, inside the sametry. Nothing is final until the singleCOMMITafter the loop.- On any error,
ROLLBACKundoes every round's delete and insert together, andraisepasses the error on. That is what the check's hint tests: when the second round fails, the first round's rows,H0among them, are still the old ones. con.unregister("incoming")after each insert frees the name for the next round's rows.- The count uses one
?per round (", ".join("?" for _ in rounds)) and passes the round IDs as values, soround IN (?, ?)counts every round loaded without putting the IDs into the SQL text.
Stretch S2.4. A warning, written as a pandera check
The task
warnings_for compares weights in plain Python. Write it as pandera does it: weight_shift_check(previous_mean, limit) returns a pa.Check on the weight column that warns (raise_warning=True) when the column's mean differs from previous_mean by more than limit (0.10 = 10 %). A check on a whole column receives the column, a Series, and returns True (fine) or False.
The check validates two small tables with a schema using your check, and counts the warnings.
If the check is red
One warning for the second table, none for the first; and the validation must not fail.
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
A pandera check can look at a whole column at once and, with raise_warning=True, warn instead of failing. The comparison is warnings_for's, written as such a check.
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 weight_shift_check(previous_mean: float, limit: float) -> pa.Check: """A pandera check that warns when the mean weight moved more than `limit` from the previous round's.""" return pa.Check(lambda weight: abs(weight.mean() / previous_mean - 1) <= limit, raise_warning=True, name=f"mean weight within {limit:.0%} of the previous round")Why each part is there
pa.Check(lambda weight: ...)on theweightcolumn receives the whole column as a Series, soweight.mean()is the round's mean weight.abs(weight.mean() / previous_mean - 1) <= limitisTruewhen the mean is within the limit of the previous round's: a mean of 520 against 500 is 4 % up, fine at 10 %; 650 against 500 is 30 % up, outside it.raise_warning=Truemakes aFalsea warning, not a failure, so the validation still passes, which is what a warning is: odd, not impossible. Thenameis what the warning says, so it reads as a sentence.