Every exercise in 81_stretch_dataframes.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 S1.1. Fertilizer per hectare, and harvest per kilogram of it
The task
Why it matters: an extension office deciding where to run fertilizer trials wants two numbers for each crop: how much fertilizer farmers put on a hectare, and how many kilograms of harvest each kilogram of fertilizer goes with. The old exercise divided production by fertilizer farm by farm. On the survey, that runs into three traps at once: fertilizer is blank where it was not recorded, areas come in two units, and each household stands for a different number of farm households.
Input → output: the plots and the households → one row per crop, the crop as the index, with three columns, sorted by harvest_kg_per_kg_fertilizer, largest first:
| column | what it is |
|---|---|
plots |
how many plots the figures use: plots with a crop and a fertilizer amount recorded |
fertilizer_kg_per_ha |
weighted total fertilizer ÷ weighted total area in hectares, to 1 decimal |
harvest_kg_per_kg_fertilizer |
weighted total harvest ÷ weighted total fertilizer, to 1 decimal |
A weighted total multiplies each plot's value by its household's weight (how many farm households that household stands for) before adding up (module 4, section 3 (30_summarize_and_combine.py)). In the small survey, Rice uses four plots: H3's first plot drops out, because its fertilizer was not recorded.
Two decisions, made for you:
- A blank fertilizer amount is unknown, not zero. Filling it with 0 would say those farmers used none, and their harvest would count as grown without fertilizer (module 5, section 1 (
40_cleaning_and_time.py)). Leave those plots out of all three figures. - Totals, then divide. Dividing plot by plot and averaging lets the smallest plots, where a few kilograms either way change the ratio a lot, pull the answer around. Dividing the totals answers "per hectare of all the crop's land" and "per kilogram of all the fertilizer".
Tools: dropna(subset=[...]) (module 5, section 1 (40_cleaning_and_time.py)), merge(..., validate="many_to_one") (module 4, section 5 (30_summarize_and_combine.py)), groupby(...).agg(name=(column, "sum")) (module 4, section 1 (30_summarize_and_combine.py)).
If the check is red
In the small survey, Rice uses 4 plots and Corn 3. Work out Rice's fertilizer per hectare by hand: (150×120 + 60×120 + 400×300 + 160×200) ÷ (1×120 + 0.5×120 + 2×300 + 0.8×200).
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
Leave out what was not recorded, weight what is left, add it up per crop, and only then divide: a ratio of weighted totals, not an average of ratios.
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 fertilizer_by_crop(plots: pd.DataFrame, households: pd.DataFrame) -> pd.DataFrame: """Per crop: plots used, fertilizer per hectare and harvest per kilogram of fertilizer, weighted. Largest harvest per kilogram first.""" # Exercise S1.1: the task and examples are in the text above this cell. recorded = plots.dropna(subset=["crop", "fertilizer_kg"]).merge(households[["hh_id", "weight"]], on="hh_id", how="left", validate="many_to_one") weighted = recorded.assign( fertilizer=recorded["fertilizer_kg"] * recorded["weight"], area=recorded["area_ha"] * recorded["weight"], harvest=recorded["production_kg"] * recorded["weight"], ) totals = weighted.groupby("crop").agg(plots=("hh_id", "size"), fertilizer=("fertilizer", "sum"), area=("area", "sum"), harvest=("harvest", "sum")) table = pd.DataFrame({ "plots": totals["plots"], "fertilizer_kg_per_ha": (totals["fertilizer"] / totals["area"]).round(1), "harvest_kg_per_kg_fertilizer": (totals["harvest"] / totals["fertilizer"]).round(1), }) return table.sort_values("harvest_kg_per_kg_fertilizer", ascending=False)Why each part is there
dropna(subset=["crop", "fertilizer_kg"])leaves out plots whose fertilizer or crop was not recorded.fillna(0)would be wrong: it claims the farmers used no fertilizer and adds their harvest to the "per kilogram" figure.merge(..., on="hh_id", how="left", validate="many_to_one")gives every plot its household's weight;validatestops with aMergeErrorif a household appeared twice, which would copy plots.- Each value is multiplied by the weight before adding up: a weighted total counts every plot as many times as the households its household stands for.
- The area is
area_ha, notarea: witharea, a plot written in square meters counts as that many hectares. On the farm survey, 151 plots are in square meters. groupby("crop").agg(plots=("hh_id", "size"), ...)counts and sums in one step;sizecounts rows. Plots with no crop are left out bygroupbytoo, but Step 1 already dropped them, which makes the count say exactly which plots were used.- Dividing the totals, then
.round(1)andsort_values(..., ascending=False), gives the table in the order the office reads it.
Stretch S1.2. Rice harvest per household member
The task
Why it matters: the old exercise measured labor productivity: production per labor day. The farm survey has no labor column: it asks nobody how many days they worked. What it has is hh_size, the number of people in the household. Harvest per household member is the closest honest measure, and it is a different one: it counts children, students and elders as well as the people who farm, so it says how much rice each mouth in the household has, not how productive anyone's work is. Name it for what it is, in the column and in any report.
Input → output: the plots and the households → one value per province: the weighted mean, over households that grow rice, of each household's rice harvest (kilograms, all its rice plots together) divided by its hh_size. A Series named rice_kg_per_member, provinces as the index, rounded to 1 decimal, largest first.
In the small survey, H1 harvests 3,500 + 2,500 = 6,000 kg of rice and has 4 members: 1,500 kg each. H2: 11,000 ÷ 3 = 3,666.7 kg each. Aurora's weighted mean is (1,500 × 120 + 3,666.7 × 300) ÷ (120 + 300).
The trap: the weight belongs to the household, but the harvest comes by plot. Join the households to the plots first and a household with three rice plots is counted three times. Add up to one row per household first, then join.
Tools: groupby("hh_id")["production_kg"].sum() (module 4, section 1 (30_summarize_and_combine.py)), merge(..., validate="one_to_one") (module 4, section 5 (30_summarize_and_combine.py)), a weighted mean per group: the sum of value × weight over the sum of weights (module 4, section 3 (30_summarize_and_combine.py)).
If the check is red
In the small survey, Aurora is (1,500 × 120 + 3,666.67 × 300) ÷ 420. If you get another number, check that H1 is counted once, with both its rice plots together.
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
Change the unit of analysis before weighting: plots become households (one rice total each), and only then does the household weight apply, once per household.
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 rice_per_member(plots: pd.DataFrame, households: pd.DataFrame) -> pd.Series: """Per province: the weighted mean of rice harvested per household member, over households that grow rice.""" # Exercise S1.2: the task and examples are in the text above this cell. rice = plots[plots["crop"] == "Rice"].groupby("hh_id")["production_kg"].sum().rename("rice_kg").reset_index() homes = rice.merge(households, on="hh_id", how="left", validate="one_to_one") homes["per_member"] = homes["rice_kg"] / homes["hh_size"] homes["weighted"] = homes["per_member"] * homes["weight"] sums = homes.groupby("province")[["weighted", "weight"]].sum() return (sums["weighted"] / sums["weight"]).round(1).sort_values(ascending=False).rename("rice_kg_per_member")Why each part is there
plots[plots["crop"] == "Rice"].groupby("hh_id")["production_kg"].sum()turns plots into households: one rice total per household. A plot with no crop is not rice, so it is left out by the filter.merge(..., validate="one_to_one")says what the join must be: one household on each side. Joining the plots before adding them up would fail this rule, which is the point: it would count H1's weight twice, once per rice plot.- A weighted mean is
(value × weight).sum() ÷ weight.sum(), here per province:groupby("province")[["weighted", "weight"]].sum()gives both sums side by side, and one division finishes it. - Only households that grow rice are in the mean: a household with no rice is not a household with 0 kg of rice per member for this question. That is why the join starts from the rice totals.
.rename("rice_kg_per_member")names what the number is. "Per member", not "per worker": the survey cannot say who works.
Stretch S1.3. Each plot against its crop's typical yield
The task
Why it matters: the farm typology exercise began by asking which farms do better than the typical farm. Comparing a rice plot with a banana plantation tells you nothing: bananas yield about five times as much per hectare. Compare each plot with plots of the same crop. And use the median as "typical": the plot in the middle, which one mistyped harvest cannot drag the way it drags a mean.
Input → output: the plots → one value per plot, in the plots' own order and index: the plot's yield (production_kg ÷ area_ha) divided by the median yield of the plots of the same crop, rounded to 3 decimals. Missing where the crop was not recorded. A Series named relative_yield.
| plot (small survey) | crop | yield, kg/ha | median of its crop | relative yield |
|---|---|---|---|---|
| H1, 1 | Rice | 3,500 | 5,000 | 0.700 |
| H3, 1 | Rice | 7,000 | 5,000 | 1.400 |
| H3, 3 | (none) | 2,000 | missing |
A relative yield of 1.4 means 40% above the typical plot of that crop; 0.7 means 30% below.
Unweighted, on purpose. This compares plots in the sample, to find the ones worth a second look, not to say anything about all the province's farms. Every plot counts once.
Tools: groupby(...).transform("median"), which gives every row its own group's median, so the result lines up with the plots (module 4, section 2 (30_summarize_and_combine.py)).
If the check is red
In the small survey the rice median is 5,000 kg/ha and the corn median 4,000 kg/ha; H3's third plot has no crop and stays missing.
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
transform keeps one row per plot: every plot gets its crop's median beside it, and one division compares each plot with plots like 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 relative_yield(plots: pd.DataFrame) -> pd.Series: """Each plot's yield divided by the median yield of its crop's plots; missing where the crop is.""" # Exercise S1.3: the task and examples are in the text above this cell. yields = plots["production_kg"] / plots["area_ha"] typical = yields.groupby(plots["crop"]).transform("median") return (yields / typical).round(3).rename("relative_yield")Why each part is there
production_kg / area_hais the yield per hectare. Witharea, H1's 5,000 m² plot would have a yield 10,000 times too small.yields.groupby(plots["crop"]).transform("median")groups the yields by the crop column and hands back a Series the length of the plots, each row holding its own crop's median..agg("median")would give one row per crop instead, which does not line up with the plots.- A plot with no crop belongs to no group, so
transformgives it a missing value, and so does the division: the answer the task asks for, with nothing extra to write. - The median, not the mean: change H3's 7,000 kg/ha to a mistyped 70,000 and the mean of the five rice plots jumps from 5,000 to 17,600 kg/ha, while the median stays at 5,000.
Stretch S1.4. A farm typology: small or large, low or high yield
The task
Why it matters: the typology exercise sorted farms into kinds by two things at once, size and yield, so that advice can be aimed: a large plot with a low yield needs something different from a small plot with a high one. Here the kinds are set within each crop, and the result says what share of each crop's plots, across the seven provinces, is of each kind.
Input → output: the plots and the households → a table with one row per crop (the crop as the index) and the four kinds as columns, in this order: small, low yield, small, high yield, large, low yield, large, high yield. Each cell is the weighted share of the crop's plots of that kind, in percent, to 1 decimal: each row adds up to 100. A kind no plot falls in shows 0.
The rules, for plots with a crop recorded:
| rule | |
|---|---|
| large | area_ha at or above the median area_ha of plots of the same crop; otherwise small |
| high yield | yield (kg/ha) at or above the median yield of plots of the same crop; otherwise low yield |
| share | the sum of the plots' household weights in that kind ÷ the sum for the crop, × 100 |
In the small survey, rice plots measure 1.0, 0.5, 2.0, 1.0 and 0.8 ha: the median is 1.0, and both 1.0 ha plots are large. Ties at the median are why "at or above" matters.
Why the medians are unweighted and the shares weighted: the medians are only where the lines are drawn, from the plots in the sample. The shares are a statement about all the crop's farm plots in the provinces, and each plot stands for as many plots as its household's weight.
Tools: transform("median") (Exercise S1.3), np.where(condition, "large", "small") (module 2 (10_arrays.py)), pd.crosstab(rows, columns, values=..., aggfunc="sum", normalize="index") (module 4, section 4 (30_summarize_and_combine.py)), and .reindex(columns=PLOT_TYPES, fill_value=0) to put the four columns in order, even a kind that is empty. PLOT_TYPES is set at the top of this notebook.
If the check is red
Ties at the median count as large, or high yield: in the small survey, both 1.0 ha rice plots are large, and the 5,000 kg/ha rice plot is high-yield.
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
Two yes-or-no questions per plot, each against its own crop's median, make four kinds; a weighted crosstab then turns the kinds into each crop's shares.
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 plot_types(plots: pd.DataFrame, households: pd.DataFrame) -> pd.DataFrame: """Per crop: the weighted percentage of plots that are small or large, with a low or high yield, against the crop's medians.""" # Exercise S1.4: the task and examples are in the text above this cell. known = plots.dropna(subset=["crop"]).merge(households[["hh_id", "weight"]], on="hh_id", how="left", validate="many_to_one") known["yield_kg_ha"] = known["production_kg"] / known["area_ha"] by_crop = known.groupby("crop") known["size"] = np.where(known["area_ha"] >= by_crop["area_ha"].transform("median"), "large", "small") known["level"] = np.where(known["yield_kg_ha"] >= by_crop["yield_kg_ha"].transform("median"), "high yield", "low yield") known["type"] = known["size"] + ", " + known["level"] shares = pd.crosstab(known["crop"], known["type"], values=known["weight"], aggfunc="sum", normalize="index") return (shares.reindex(columns=PLOT_TYPES, fill_value=0) * 100).round(1).rename_axis(columns=None)Why each part is there
dropna(subset=["crop"])first: a plot with no crop has no median to be measured against. The weight comes in with amany_to_onejoin, as in Exercise S1.1.by_crop["area_ha"].transform("median")gives every plot its crop's median area, lined up row by row, as in Exercise S1.3; the same for the yield.>=, not>: the rule says "at or above". In the small survey, two rice plots sit exactly at the median of 1.0 ha; with>they would turn small.np.where(condition, "large", "small")turns a column ofTrue/Falseinto words; joining the two words with", "makes the label.pd.crosstab(crop, type, values=weight, aggfunc="sum", normalize="index")adds up the weights in each cell and divides each row by its own total: weighted shares, each row summing to 1. Withoutvalues=it would count plots, unweighted..reindex(columns=PLOT_TYPES, fill_value=0)puts the kinds in a fixed order and adds any kind with no plots as 0, so the table always has four columns; then× 100and.round(1).
Stretch S1.5. The follow-up list: who to visit, and where
The task
Why it matters: the typology exercise ended by ranking farms for follow-up: the ones furthest from typical are either remarkable or wrong, and either way someone should go and look. A field supervisor needs the list with where each household is: its province and its municipality's name, which the survey does not carry. The survey has the PSGC code; the PSA code list has the name.
How far from typical: a plot at half its crop's median yield is as far off as a plot at double it. The old exercise subtracted, which treats them differently (0.5 below, 1.0 above). Here distance is times off: the relative yield or its inverse, whichever is larger. Half and double are both 2 times off; 1.0 is 1 time off, exactly typical.
Input → output: the plots, the households, the code list and a number n → the n plots furthest from typical, one row each, with the columns hh_id, plot_no, crop, province, municipality, yield_t_ha (tonnes per hectare, 2 decimals), relative_yield (as in Exercise S1.3, 3 decimals) and times_off (2 decimals). Sorted by times_off, largest first; ties by hh_id, then plot_no. Plots with no crop are left out. The index runs 0, 1, 2, …
In the small survey, H3's first rice plot (relative yield 1.4: 1.40 times off) comes after H1's first (0.7: 1.43 times off). Subtracting would put them the other way round.
The join that matters: two municipalities in the small survey, in different provinces, are called San Isidro, as five names repeat in the real code list. Join on the code, and validate that each code is in the code list once.
Tools: Exercise S1.3's steps, np.maximum(a, b) (module 2 (10_arrays.py)), two merges with validate= (module 4, section 5 (30_summarize_and_combine.py)), sort_values([...], ascending=[False, True, True]), .head(n), .reset_index(drop=True).
If the check is red
In the small survey, H1's first plot (0.7 of typical) comes before H3's first (1.4 of typical): 1 ÷ 0.7 = 1.43 times off, against 1.40.
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
Measure how far each plot is from its crop's typical yield in a way that treats half and double alike, then attach where each household is by its code, and sort.
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 follow_up_list(plots: pd.DataFrame, households: pd.DataFrame, code_list: pd.DataFrame, n: int) -> pd.DataFrame: """The n plots furthest from their crop's median yield, with where each household is.""" # Exercise S1.5: the task and examples are in the text above this cell. known = plots.dropna(subset=["crop"]).copy() known["yield_t_ha"] = known["production_kg"] / known["area_ha"] / 1000 known["relative_yield"] = known["yield_t_ha"] / known.groupby("crop")["yield_t_ha"].transform("median") known["times_off"] = np.maximum(known["relative_yield"], 1 / known["relative_yield"]) where = households[["hh_id", "province", "psgc"]].merge(code_list[["code", "name"]], left_on="psgc", right_on="code", how="left", validate="many_to_one") listed = known.merge(where, on="hh_id", how="left", validate="many_to_one").rename(columns={"name": "municipality"}) listed = listed.sort_values(["times_off", "hh_id", "plot_no"], ascending=[False, True, True]).head(n) listed = listed.assign(yield_t_ha=listed["yield_t_ha"].round(2), relative_yield=listed["relative_yield"].round(3), times_off=listed["times_off"].round(2)) return listed[["hh_id", "plot_no", "crop", "province", "municipality", "yield_t_ha", "relative_yield", "times_off"]].reset_index(drop=True)Why each part is there
np.maximum(rel, 1 / rel)is "times off": for 0.5 it gives 2, for 2 it gives 2, for 1 it gives 1. Subtracting 1 (abs(rel - 1)) would rank a plot at double the median far above one at half, though both are equally suspicious.- The code list is joined on
psgc=code, never on a name: names repeat (San Isidro is in two provinces of the small survey, and five names repeat in the real list).validate="many_to_one"stops if a code is listed twice. - The households are joined first, then the plots: each join is
many_to_oneand can be checked as such. Joining the code list'sprovinceas well would give twoprovincecolumns; onlycodeandnameare taken from it. sort_values(["times_off", "hh_id", "plot_no"], ascending=[False, True, True])gives a fixed order even when two plots are equally far off, so the list is the same every time it is made.- Rounding comes last, after sorting, so two plots that differ only in the third decimal are still ranked by their true values.
.reset_index(drop=True)numbers the list from 0, as a list handed to someone should be.
Stretch S1.6. Irrigation and rice yield, province by province
The task
Why it matters: the fertilizer exercise asked how a crop responds to what goes into it. Water is the other big input. A province where irrigated rice yields far more than rainfed rice is one where irrigation pays; where the gap is small, money may do more elsewhere. The answer must stand for all the province's rice plots, so it is weighted.
Input → output: the plots and the households → one row per province (the province as the index), with three columns, sorted by gain, largest first:
| column | what it is |
|---|---|
No |
the weighted mean yield of rainfed rice plots (irrigated is "No"), in tonnes per hectare, 2 decimals |
Yes |
the same for irrigated rice plots |
gain |
Yes minus No |
The weighted mean yield is the one module 4 uses: each plot's yield, weighted by its household's weight, summed, divided by the sum of the weights. On the farm survey, all rice plots together give 4.50 t/ha this way (4.35 unweighted).
In the small survey, Benguet's rainfed rice is one plot, H3's: 7.0 t/ha. Its irrigated rice is H4's plot: 4.0 t/ha. A gain of −3.0: in a sample this small, one plot decides everything, which is why the farm survey's figures need many plots behind each cell.
Tools: pivot_table(index=..., columns=..., values=[...], aggfunc="sum") (module 4, section 4 (30_summarize_and_combine.py)), then a weighted mean from the two sums, as in Exercise S1.2.
If the check is red
In the small survey, Aurora's rainfed rice is H1's first plot (3.5 t/ha) and its irrigated rice two plots: (5.0 × 120 + 5.5 × 300) ÷ 420 = 5.36 t/ha.
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 weighted mean for every province and irrigation status at once: two pivot tables of sums, one divided by the other, and the gain is one column minus another.
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 irrigation_gain(plots: pd.DataFrame, households: pd.DataFrame) -> pd.DataFrame: """Per province: the weighted mean yield of rainfed and of irrigated rice, in t/ha, and the gain from irrigation.""" # Exercise S1.6: the task and examples are in the text above this cell. rice = plots[plots["crop"] == "Rice"].merge(households[["hh_id", "province", "weight"]], on="hh_id", how="left", validate="many_to_one") rice["yield_t_ha"] = rice["production_kg"] / rice["area_ha"] / 1000 rice["weighted"] = rice["yield_t_ha"] * rice["weight"] sums = rice.pivot_table(index="province", columns="irrigated", values=["weighted", "weight"], aggfunc="sum") means = sums["weighted"] / sums["weight"] means["gain"] = means["Yes"] - means["No"] means.columns.name = None return means[["No", "Yes", "gain"]].round(2).sort_values("gain", ascending=False)Why each part is there
- The rice plots get their household's
provinceandweightwith amany_to_onejoin. The yield isproduction_kg / area_ha / 1000: tonnes per hectare, with square meters already converted inarea_ha. pivot_table(index="province", columns="irrigated", values=["weighted", "weight"], aggfunc="sum")makes both sums for every province and status in one table;sums["weighted"]andsums["weight"]are each a table with columnsNoandYes.- Dividing one table by the other divides cell by cell, because their rows and columns line up by label: a weighted mean in every cell.
means["Yes"] - means["No"]is the gain in tonnes per hectare. A province with only rainfed or only irrigated plots would show a missing gain, which is honest: there is nothing to compare.columns.name = Noneremoves the label "irrigated" over the columns, left over from the pivot, so the table reads as three plain columns.