Skip to content
Learn Python with BNHR

Summarize and combine

On this page
  1. Overview
  2. 4.1 · A summary by crop
  3. 4.2 · Yields by province and crop
  4. 4.3 · Each plot's share of its farm
  5. 4.4 · Farm size rank within the province
  6. 4.5 · Fieldwork progress, province by province
  7. 4.6 · A weighted mean that skips missing values
  8. 4.7 · Crop area for the provinces, estimated
  9. 4.8 · Crop area as a province-by-crop table
  10. 4.9 · Irrigated plots, province by province
  11. 4.10 · Households in every municipality
  12. 4.11 · Price files stacked into one table

Every exercise in 30_summarize_and_combine.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 4.1. A summary by crop

The task

Why it matters: this is the first table of any report on the survey: how many plots of each crop, how many of them have the fertilizer question answered, and how much land they cover. If it leaves out the plots with no crop, its totals don't match the survey's, and a careful reader stops trusting the rest.

Input → output: the plots table → one row per crop, the plots with no crop included (as a missing crop), with three columns:

crop plots with_fertilizer area_ha
Banana 136 120 124.96
… … … …
(missing) 14 … …
  • plots: how many plots;
  • with_fertilizer: how many plots have a fertilizer_kg amount;
  • area_ha: their total area in hectares (from area_ha, not area: area mixes hectares and square meters).

Tools: groupby(..., dropna=False) and .agg() with named aggregations, "size", "count" and "sum", all above.

If the check is red: common causes

what you see the usual cause look at
Four rows, no missing crop groupby left out the plots with no crop. groupby("crop", dropna=False)
with_fertilizer equals plots in every row size used for both. ("fertilizer_kg", "count")
plots is 0 for the missing crop count on crop counts the crops that are present, and that group has none. ("plot_no", "size")
area_ha in the thousands area added instead of area_ha: square meters mixed with hectares. ("area_ha", "sum")
TypeError: Must provide 'func' or tuples of '(column, aggfunc)' A named result written without its brackets, or with a list. plots=("plot_no", "size")

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.

Exercise 4.2. Yields by province and crop

The task

Why it matters: "which provinces get the most from a hectare of rice?" is the question an extension office asks first. This table answers it for every crop at once, and section 3 shows what it still gets wrong.

Input → output: the farm table (plots with their province and yield_t_ha) → one row per province and crop, as a long table with four columns:

province crop plots mean_yield
Bukidnon Banana 71 22.69
Bukidnon Corn 130 …
… … … …
  • plots: how many plots of that crop in that province;
  • mean_yield: the mean of their yield_t_ha.

Plots with no crop have no place in a yield table: leave them out, as groupby does by default.

Tools: groupby with a list of keys, named aggregations and .reset_index(), all above.

If the check is red: common causes

what you see the usual cause look at
Rows whose crop is missing dropna=False kept the plots with no crop; a yield table has no row for them. groupby(["province", "crop"])
Four rows, or seven Grouped by one key only. a list of both keys
mean_yield close but not equal Mean production divided by mean area: a different question. ("yield_t_ha", "mean")
KeyError: 'yield_t_ha' The function was given plots, which has no yield; the check gives it farm. the Try it cell's argument

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.

Exercise 4.3. Each plot's share of its farm

The task

Why it matters: a household whose land is mostly one plot farms differently from one spread over four. The share of each plot in its household's land is the measure, and computing it is the pattern behind every share: a value over its group's total.

Input → output: the plots table → a Series (one column) with each plot's area_ha divided by its household's total area_ha, one value per plot, in the plots' order:

hh_id plot_no area_ha share of farm
HH-0001 1 0.439 0.694
HH-0001 2 0.194 0.306
HH-0002 1 0.454 0.325

A household's shares add up to 1; a household with one plot has a share of 1.

Tools: groupby("hh_id")["area_ha"].transform("sum"), above.

If the check is red: common causes

what you see the usual cause look at
Every share missing (None) Divided by groupby(...).sum(): one value per household, indexed by hh_id, so nothing lines up. .transform("sum")
Tiny shares, adding up to 1 over all plots Divided by the survey's total: the wrong denominator. groupby("hh_id")
Shares above 1, or a square-meter plot near 1 area used instead of area_ha. plots["area_ha"]
Shares that add up to 1 per crop or province Grouped by the wrong column. the group is the household

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.

Exercise 4.4. Farm size rank within the province

The task

Why it matters: "the largest farms of each province" is a common request from a program that targets them. Ranking within the province, not across the survey, compares a farm with its neighbors: a large farm in Ilocos Norte may be small by Bukidnon's measure.

Input → output: a table with one row per household (hh_id, province, farm_ha, built in the cell below) → a Series with each household's rank within its province, 1 for the largest farm. Tied farms share the best rank they cover, and the next rank is skipped (method="min"):

province farm_ha rank
A 3.0 1
A 2.0 2
A 2.0 2
A 1.0 4
B 1.5 1

Tools: groupby(...)[...].rank(ascending=..., method=...), above.

If the check is red: common causes

what you see the usual cause look at
Ranks in the hundreds Ranked across the survey, not within the province. groupby("province")
The smallest farm is 1 The default order is from the smallest. ascending=False
2.5 for the tied farms The default method averages ties. method="min"
The farm after a tie is 3, not 4 method="dense" does not skip a rank. method="min"

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.

Exercise 4.5. Fieldwork progress, province by province

The task

Why it matters: a survey manager watches each province's interviews add up against its target of 170. A running total for the whole survey hides a province that falls behind.

Input → output: the households table → one row per province and interview date (only the dates with interviews), with:

province interview_date interviews to_date
Bukidnon 2025-10-01 2 2
Bukidnon 2025-10-02 3 5
… … … …
Cotabato 2025-10-01 … starts again
  • interviews: households interviewed in that province that day;
  • to_date: the province's running total, starting again in each province.

Tools: groupby([...]).size(), .reset_index(), and a grouped cumsum(), all above.

If the check is red: common causes

what you see the usual cause look at
to_date keeps growing from one province to the next cumsum() without groupby: one running total for the whole table. groupby("province")["interviews"].cumsum()
to_date equals interviews Grouped by province and date, so every group is one row. group the cumsum by province only
interviews is the sum of hh_size Summed a column instead of counting rows. .size()
KeyError: 'interviews' The count is still named 0 or size. .rename("interviews") before .reset_index()

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.

Exercise 4.6. A weighted mean that skips missing values

The task

Why it matters: every estimate of an average in this survey goes through a weighted mean. Written once, right, with the missing values handled, it can be used for yields, fertilizer, household size, anything.

Input → output: two Series of the same length, values and weights → one number, the weighted mean Σ w·x / Σ w, counting only the rows where the value is present:

values weights weighted mean
1, 2, 3 1, 1, 2 (1 + 2 + 6) / 4 = 2.25
10, missing, 20 1, 5, 3 (10 + 60) / (1 + 3) = 17.5, not 70 / 9

Tools: notna() and boolean masks (module 3), *, .sum().

If the check is red: common causes

what you see the usual cause look at
2.0 for the first case The plain mean: the weights are not used. (values * weights).sum() / weights.sum()
9.0 for the first case The weighted total, not divided by the weights. divide by weights.sum()
3.0 for the first case Divided by the number of rows instead of the weights. weights.sum()
7.7778 for the second case The weight of the missing value is still in the denominator. keep values.notna() rows in both sums
nan Every value was dropped, or values and weights come from different tables and don't line up. the mask, and where the two Series come from

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.

Exercise 4.7. Crop area for the provinces, estimated

The task

Why it matters: "how many hectares of rice do the seven provinces' farm households grow?" is a total for the provinces, not for the sample. The sample's 880 hectares of rice are the land of 1,034 plots; weighted, they stand for the rice land of about 686,000 farm households.

Input → output: the farm table → one row per crop (plots with a crop only), with:

crop plots area_ha share
Banana 136 … …
Rice 1,034 … …
  • plots: plots in the sample;
  • area_ha: the weighted total area, Σ weight × area_ha: the hectares the provinces' farm households grow;
  • share: the crop's share of the weighted area of all crops; the shares add up to 1.

Tools: a column of area_ha × weight, groupby with named aggregations, and a share as one total over another, above.

If the check is red: common causes

what you see the usual cause look at
area_ha under 1,000 for every crop The sample's hectares, not weighted. area_ha * weight, then sum
Shares that add up to a little less than 1 The denominator includes the plots with no crop. divide by the result's own area_ha.sum()
A row for the missing crop dropna=False: these are crop estimates. the default groupby("crop")
plots in the hundreds of thousands The weights were summed, not the plots counted. ("plot_no", "size")

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.

Exercise 4.8. Crop area as a province-by-crop table

The task

Why it matters: this is the table that goes in the report: the estimated hectares of each crop in each province, as a grid a reader can scan. The chart under the exercise draws it.

Input → output: the farm table → a wide table with provinces as the index and one column per crop, each cell the weighted total area (Σ weight × area_ha) of that crop in that province, and 0 where a province has no plots of a crop:

province Banana Corn Rice Vegetables
Bukidnon 55,784 102,079 … …
Camarines Sur 0 … … …

Tools: a weighted column as in Exercise 4.7, and pivot_table with aggfunc="sum" and fill_value=0, above.

If the check is red: common causes

what you see the usual cause look at
Cells of a few hundred, none much above 1,000 The mean, pivot_table's default, not the sum. aggfunc="sum"
No cell above 200 The sample's hectares: the area is not weighted. area_ha * weight
None in some cells Empty cells left missing. fill_value=0
Provinces across the top index and columns swapped. index="province", columns="crop"
ValueError: Index contains duplicate entries, cannot reshape pivot used: it cannot add up. pivot_table

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.

Exercise 4.9. Irrigated plots, province by province

The task

Why it matters: an irrigation program asks where a crop's plots still depend on rain. The answer is a share of each province's plots of that crop, weighted, so it describes the provinces' farms and not the sample's.

Input → output: the farm table and a crop name → a table with provinces as the index and the columns No and Yes (irrigated), each cell the weighted share of that province's plots of the crop: each row adds up to 1.

province No Yes
Bukidnon 0.448 0.552
… … …

Unweighted, Bukidnon's rice would read 0.484 and 0.516: close, but not the provinces' answer.

Tools: filter to the crop (module 3), then pd.crosstab with values=, aggfunc="sum" and normalize=, above.

If the check is red: common causes

what you see the usual cause look at
Bukidnon's rice reads 0.484 and 0.516 Plots counted, not weighted. values=one["weight"], aggfunc="sum"
Columns, not rows, add up to 1 The wrong denominator. normalize="index"
Every cell small, the whole table adding up to 1 normalize="all". normalize="index"
Rice shares that look like all crops' The crop was not filtered first. farm[farm["crop"] == crop]
ValueError: values cannot be used without an aggfunc values= without aggfunc=. aggfunc="sum"

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.

Exercise 4.10. Households in every municipality

The task

Why it matters: a field office plans next year's visits from a list of every municipality with how many households were interviewed there, the ones with none included. A municipality missing from the list is one nobody plans a visit to; one counted under the wrong Quezon sends a team to the wrong province.

Input → output: the households and the code list → one row per municipality of the code list (all of them, none repeated), with:

code name province households households_represented
0102801000 Adams Ilocos Norte … …
… … … … …
a municipality with no household … … 0 0
  • households: households interviewed there;
  • households_represented: the sum of their weights;
  • 0 in both where no household was interviewed.

Tools: groupby on the code, then merge with left_on=/right_on=, the right how=, validate=, and fillna(0) (module 3), all above.

If the check is red: common causes

what you see the usual cause look at
202 rows, not 212 An inner join dropped the municipalities with no household. the code list on the left, how="left"
More rows than the code list Joined on the name: every Quezon matched every Quezon. left_on="code", right_on="psgc"
A Quezon with households from two provinces Grouped by name instead of code. groupby("psgc")
None where there should be 0 The empty counts were not filled. fillna(0)
MergeError: Merge keys are not unique validate= caught a key that repeats: often the households themselves were joined, not their counts. count first, then join

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.

Exercise 4.11. Price files stacked into one table

The task

Why it matters: monthly files are how prices arrive, and a trend needs them in one table, each row knowing its month. The month is in the file's name, not inside the file, so it must be added as the files are stacked, or it is lost.

Input → output: a list of paths to monthly price files in the older layout (province, commodity, price_php_per_kg), such as PATHS.prices / "farmgate_2024-10.csv" → one table with the columns month (text, such as "2024-10", from the file's name), province, commodity, price_php_per_kg, and rows numbered from 0:

month province commodity price_php_per_kg
2024-10 Ilocos Norte Palay …
… … … …

Tools: a list comprehension to read the files (Python Essentials), path.stem (the file name without .csv) and .removeprefix("farmgate_"), and pd.concat with keys= and names=, above.

If the check is red: common causes

what you see the usual cause look at
No month column The tables were stacked without keys=, so the month was lost. pd.concat(tables, keys=months, ...)
month reads farmgate_2024-10 The prefix is still on. .removeprefix("farmgate_")
A column row or level_1 reset_index() without level= brought the files' row numbers in as a column. reset_index(level="month"), then reset_index(drop=True)
ValueError: Cannot set ignore_index=True and specify keys pandas 3 refuses both at once. drop ignore_index, reset the index after
TypeError: first argument must be an iterable of pandas objects pd.concat was given one table, or the tables not in a list. pd.concat([...])

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.

Something new

Identify results
LayerExplore a module
ModuleSummarize and combine
AboutModule 4 of Arrays & DataFrames: groups, values within groups, survey weights, long and wide tables, and joins that neither lose nor multiply rows. Its sections, its notebooks to read on the web, and the companion's solutions and explanations.
CourseArrays & DataFrames, free