Every exercise in 70_capstone.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 K1.1. Weighted yields by province, for one crop
The task
Why it matters: this is the number the extension office has never had: each province's yield for a crop, standing for all its farm plots, with the count of plots behind it so a figure from five plots is not read like one from two hundred.
Input → output: the plots, the households and a crop → one row per province where the crop is grown, the province as the index, with plots (how many of the crop's plots) and yield_t_ha (the weighted mean of the plots' yields, in tonnes per hectare, 2 decimals). Lowest yield first, so the provinces to look at come first; ties by province name.
A plot's yield is production_kg ÷ area_ha ÷ 1000. The weighted mean multiplies each plot's yield by its household's weight, adds those up, and divides by the sum of the weights (module 4, section 3 (30_summarize_and_combine.py)). On the farm survey, all rice plots together give 4.50 t/ha this way.
| province | plots | yield_t_ha |
|---|---|---|
| Camarines Sur | 161 | … |
| … | … | … |
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Aurora's rice is 4.5 in the small survey | Unweighted: the mean of 3.0, 5.0 and 5.5. | multiply by weight before adding up |
| Aurora's rice is 4.71 or 5.13 in the small survey | Total harvest ÷ total area (unweighted or weighted), not the mean of the plots' yields. | the definition above |
| One plot's yield is tiny | area instead of area_ha. |
Step 2 |
| Yields in the thousands | Kilograms per hectare, not tonnes. | / 1000 |
| The right numbers in another order | Not sorted lowest first, or ties not broken by province. | sort_values(["yield_t_ha", "province"]) |
| A province with no plots of the crop appears | The households were the left side of the join. | start from the crop's plots |
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
One weighted mean per province: join the weight on, multiply, add up per province, divide; and keep the plot count beside it, because a mean is only as good as the plots behind 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 weighted_yields(plots: pd.DataFrame, households: pd.DataFrame, crop: str) -> pd.DataFrame: """Per province: how many plots of the crop, and their weighted mean yield in t/ha. Lowest first.""" # Exercise K1.1: the task and examples are in the text above this cell. grown = plots[plots["crop"] == crop].merge(households[["hh_id", "province", "weight"]], on="hh_id", how="left", validate="many_to_one") grown["yield_t_ha"] = grown["production_kg"] / grown["area_ha"] / 1000 grown["weighted"] = grown["yield_t_ha"] * grown["weight"] sums = grown.groupby("province").agg(plots=("hh_id", "size"), weighted=("weighted", "sum"), weight=("weight", "sum")) table = pd.DataFrame({"plots": sums["plots"], "yield_t_ha": (sums["weighted"] / sums["weight"]).round(2)}) return table.reset_index().sort_values(["yield_t_ha", "province"]).set_index("province")Why each part is there
merge(..., validate="many_to_one")brings each plot its household's province and weight, and stops if a household is listed twice, which would copy its plots.area_ha, neverarea: square-meter plots would otherwise have yields 10,000 times too small and drag their province down.groupby("province").agg(plots=("hh_id", "size"), weighted=("weighted", "sum"), weight=("weight", "sum"))gives the count and both sums in one step; one division makes the weighted mean.- Sorting by
["yield_t_ha", "province"]puts the lowest first and gives a fixed order when two provinces tie, so the table reads the same every time.
Exercise K1.2. The months whose file never came
The task
Why it matters: a missing month is invisible in a combined table: there are simply no rows for it, and a line chart draws straight across. The analyst's report must say which months are missing, every time, without anyone looking.
Input → output: a list of file names (as [p.name for p in folder.iterdir()] gives them) → the months between the first and the last price file that have no file, as texts "YYYY-MM", in order. A price file is named exactly farmgate_YYYY-MM.csv; any other name in the folder (a README.md, a copy named farmgate_2024-01 (1).csv, a farmgate_2023-10_draft.csv) is not a price file and is ignored. No price files at all: an empty list.
| file names | missing months |
|---|---|
farmgate_2024-11.csv, farmgate_2025-02.csv, farmgate_2024-12.csv |
["2025-01"] |
farmgate_2023-01.csv, farmgate_2023-04.csv |
["2023-02", "2023-03"] |
Tools: re.fullmatch(r"farmgate_(\d{4}-\d{2})\.csv", name) picks out the month (Python Essentials, text); pd.period_range(first, last, freq="M") lists every month between two (module 5, section 4 (40_cleaning_and_time.py)), and .strftime("%Y-%m") writes each as text.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Wrong across the new year | The months were not sorted, or were compared as numbers. | sorted(...) of the texts, then period_range |
| Months before the first file are listed | A draft or another file was taken for a price file. | re.fullmatch, not re.search |
| ValueError or IndexError with no price files | found[0] of an empty list. |
return [] first |
| Periods instead of texts | period_range gives periods. |
.strftime("%Y-%m") |
| Every month is listed | The comparison is between a period and a text. | compare texts with texts |
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
List the months that should be there (every month from the first file to the last) and take away the months that are: what remains never came.
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 missing_months(names: list[str]) -> list[str]: """The months between the first and the last price file that have no file, as "YYYY-MM" texts, in order.""" # Exercise K1.2: the task and examples are in the text above this cell. found = sorted({match.group(1) for name in names if (match := re.fullmatch(r"farmgate_(\d{4}-(?:0[1-9]|1[0-2]))\.csv", name))}) if not found: return [] have = set(found) return [month for month in pd.period_range(found[0], found[-1], freq="M").strftime("%Y-%m") if month not in have]Why each part is there
re.fullmatchmust match the whole name, sofarmgate_2024-01 (1).csvandREADME.mdare not price files. The month part(0[1-9]|1[0-2])refuses a month 13, which would otherwise stopperiod_range.- A set comprehension removes repeats, and
sortedputs the months in order: texts like"2024-11"sort in date order because the year comes first and the month has two digits. That is why the files are named this way. pd.period_range(found[0], found[-1], freq="M")is every month from the first to the last, whatever order the names came in;.strftime("%Y-%m")writes them in the same form as the names.- The list comprehension keeps the months not found, in calendar order. Months before the first file or after the last cannot be known to be missing, so they are not reported.
Exercise K1.3. The weighted totals, from the raw export
The task
Why it matters: this is the function the officer will defend on Friday. It must take the export exactly as it comes and give the totals the published table rounds, so that the only difference left is rounding.
Input → output: the raw households and plots (course.households(clean=False), course.plots(clean=False): production already numbers, every trap still there) → one row per province, the province as the index, with whole numbers:
| column | what it is |
|---|---|
farm_households |
the sum of the households' weights |
rice_area_ha |
the weighted total area of rice plots, in hectares |
rice_production_t |
the weighted total rice harvest, in tonnes |
Every province of the households has a row; a province with no rice plots shows 0 for rice (a weighted total of nothing is 0, not unknown).
Tools: drop_duplicates() (module 5, section 5 (40_cleaning_and_time.py)), one unit for area (module 5, section 2 (40_cleaning_and_time.py)), weighted totals (module 4, section 3 (30_summarize_and_combine.py)), merge(..., validate="many_to_one") (module 4, section 5 (30_summarize_and_combine.py)).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| Aurora has 700 farm households in the small export | The duplicate household was not dropped. | households.drop_duplicates() |
| MergeError: Merge keys are not unique in right dataset | The same: a household still listed twice. | Step 1 before the join |
| Rice area in the thousands | Square meters added as hectares. | Step 2 |
| Aurora's rice area is 800 | The duplicate plot was counted twice. | plots.drop_duplicates() |
| Benguet is missing, or shows a missing value | The rice totals have no row for a province with no rice. | start the table from the households; fillna(0) |
| IntCastingNaNError | astype(int) on a missing value. |
fillna(0) before astype(int) |
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
Clean exactly what the export gets wrong (duplicate rows, two units of area), then weight and add up per province; what is left between this and the published table is rounding.
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 weighted_totals(households: pd.DataFrame, plots: pd.DataFrame) -> pd.DataFrame: """Per province, from the raw export: farm households, rice area in hectares and rice harvest in tonnes, weighted.""" # Exercise K1.3: the task and examples are in the text above this cell. homes = households.drop_duplicates() fields = plots.drop_duplicates() fields = fields.assign(area_ha=fields["area"].where(fields["area_unit"] == "ha", fields["area"] / 10_000)) rice = fields[fields["crop"] == "Rice"].merge(homes[["hh_id", "province", "weight"]], on="hh_id", how="left", validate="many_to_one") totals = pd.DataFrame({ "farm_households": homes.groupby("province")["weight"].sum(), "rice_area_ha": (rice["area_ha"] * rice["weight"]).groupby(rice["province"]).sum(), "rice_production_t": (rice["production_kg"] * rice["weight"]).groupby(rice["province"]).sum() / 1000, }) return totals.fillna(0).round(0).astype(int)Why each part is there
drop_duplicates()on both tables: the export repeats 6 households and 4 plots, whole rows. A repeated household adds its weight twice to the farm households, and in the join it would copy every one of its plots too.area.where(area_unit == "ha", area / 10_000)keeps hectares and converts square meters. Skipping it is the trap that puts the quick rice area out by a factor of hundreds: 151 plots count their square meters as hectares.merge(..., validate="many_to_one")gives each rice plot its household's weight, and stops if a household is still listed twice, which is the check that Step 1 worked.- Farm households come from the households table, not the join: a household with no rice still counts, and so does every province.
- Building one
DataFramefrom the three Series lines them up by province; a province with no rice plot is missing from the two rice Series, andfillna(0)says what is true: no rice, 0 hectares.round(0).astype(int)gives whole numbers.