Skip to content
Learn Python with BNHR

Stretch: DataFrames

On this page
  1. Overview
  2. S1.1 · Fertilizer per hectare, and harvest per kilogram of it
  3. S1.2 · Rice harvest per household member
  4. S1.3 · Each plot against its crop's typical yield
  5. S1.4 · A farm typology: small or large, low or high yield
  6. S1.5 · The follow-up list: who to visit, and where
  7. S1.6 · Irrigation and rice yield, province by province

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.

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.

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.

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.

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.

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.

Something new

Identify results
LayerExplore a module
ModuleArrays with NumPy
AboutModule 2 of Arrays & DataFrames: arrays, their dtype and shape, masks, vectorized arithmetic and broadcasting, and sums along an axis with missing values. Its sections, its notebooks to read on the web, and the companion's solutions and explanations.
CourseArrays & DataFrames, free