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 afertilizer_kgamount;area_ha: their total area in hectares (fromarea_ha, notarea:areamixes 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.
The idea
One group per crop, the missing crop included, and three summaries named as the report needs them: rows, answers to the fertilizer question, and land.
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 crop_summary(plots: pd.DataFrame) -> pd.DataFrame: """One row per crop (plots with no crop included): plots, with_fertilizer and area_ha.""" # Exercise 4.1: the task and examples are in the text above this cell. return plots.groupby("crop", dropna=False).agg( plots=("plot_no", "size"), with_fertilizer=("fertilizer_kg", "count"), area_ha=("area_ha", "sum"), )Why each part is there
dropna=Falsekeeps the 14 plots with no crop as a group of their own. Without it their rows, and their land, vanish from the table without a warning.("plot_no", "size")counts the rows of each group. Any column would do, sincesizecounts rows whether or not a value is missing.("fertilizer_kg", "count")counts only the plots that have an amount;sizethere would count every plot and hide the 194 that have none.("area_ha", "sum")adds up hectares. Addingareainstead would add square meters to hectares, and the 151 plots measured in square meters would count ten thousand times too much.
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 theiryield_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.
The idea
Two keys make one group per province and crop; the default dropna=True is the right choice here, because a plot with no crop belongs in no crop's yield.
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 crop_yields(farm: pd.DataFrame) -> pd.DataFrame: """One row per province and crop (plots with a crop only): province, crop, plots and mean_yield.""" # Exercise 4.2: the task and examples are in the text above this cell. return farm.groupby(["province", "crop"]).agg(plots=("plot_no", "size"), mean_yield=("yield_t_ha", "mean")).reset_index()Why each part is there
groupby(["province", "crop"])makes one group per combination that occurs; Iloilo, for one, has no banana plots, so it has no banana row.- The default leaves out the 14 plots with no crop. Here that is a decision, not an accident: their yield is not the yield of any crop.
("yield_t_ha", "mean")is the mean of the plots' yields. Dividing the mean production by the mean area would answer a different question (the yield of the average hectare) and give different numbers..reset_index()turns the two index levels into columns, the long shape the next sections build on.
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.
The idea
A share is a value over its group's total; transform("sum") puts the household's total on every plot's row, so the division lines up row by row.
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 share_of_farm(plots: pd.DataFrame) -> pd.Series: """Each plot's area as a share of its household's total area, one value per plot, in the plots' order.""" # Exercise 4.3: the task and examples are in the text above this cell. return plots["area_ha"] / plots.groupby("hh_id")["area_ha"].transform("sum")Why each part is there
groupby("hh_id")makes the household the group, so the denominator is the household's land, not the province's or the survey's.transform("sum")returns one value per plot, indexed likeplots..sum()would return one value per household, indexed byhh_id, and dividing by it would line up nothing: every share missing.area_haon both sides:areamixes square meters and hectares, so a plot measured in square meters would look ten thousand times larger.
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.
The idea
Grouped, rank starts again at 1 in every province; ascending=False makes the largest farm first, and method="min" gives tied farms the best rank they cover.
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 size_rank(farms: pd.DataFrame) -> pd.Series: """Each household's farm size rank within its province: 1 for the largest; ties share the best rank they cover.""" # Exercise 4.4: the task and examples are in the text above this cell. return farms.groupby("province")["farm_ha"].rank(ascending=False, method="min")Why each part is there
groupby("province")makes the ranks restart in each province. Without it, the ranks run across all 1,190 farms.ascending=False: the default ranks the smallest farm 1.method="min": the default,"average", gives two farms tied for second 2.5 each;"dense"would not skip the next rank, so the fourth-largest farm would be third. Farms of the same size occur within provinces in the real data too, so the choice shows there as well.
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.
The idea
Count the interviews per province and day, then keep a running total that starts again in each province: a grouped cumsum over rows already in date order.
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 fieldwork_progress(households: pd.DataFrame) -> pd.DataFrame: """One row per province and interview date: interviews that day and to_date, the province's running total.""" # Exercise 4.5: the task and examples are in the text above this cell. progress = households.groupby(["province", "interview_date"]).size().rename("interviews").reset_index() progress["to_date"] = progress.groupby("province")["interviews"].cumsum() return progressWhy each part is there
groupby(["province", "interview_date"]).size()counts households per province and day.groupbysorts its keys, so within each province the days come out in date order, which is the order the running total must add in..rename("interviews").reset_index()names the count and makes province and date columns again.groupby("province")["interviews"].cumsum()starts at zero in each province. A plaincumsum()would carry Bukidnon's total into Camarines Sur's first day.
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.
The idea
A weighted mean is Σ w·x over Σ w, and both sums must cover the same rows: keep only the rows where the value is present before adding up either.
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_mean(values: pd.Series, weights: pd.Series) -> float: """The weighted mean of values: sum(w * x) / sum(w), over the rows where the value is present.""" # Exercise 4.6: the task and examples are in the text above this cell. present = values.notna() return (values[present] * weights[present]).sum() / weights[present].sum()Why each part is there
present = values.notna()marks the rows with a value. Without it,(values * weights).sum()skips a missing value butweights.sum()still counts its weight, and the mean comes out too low (126.1 kg of fertilizer instead of 137.8).values[present] * weights[present]multiplies each value by its own weight; the two Series line up by their index, which is why they must come from the same table.- Dividing by
weights[present].sum(), not by the number of rows, is what makes it a weighted mean: dividing by the count would give a total per row, not a mean.
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.
The idea
A weighted total is the sum of weight × value; a weighted share is one weighted total over the sum of them all, so the shares add up to 1.
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 crop_area_estimates(farm: pd.DataFrame) -> pd.DataFrame: """One row per crop: plots in the sample, area_ha (weighted total hectares) and share (of all crops' weighted area).""" # Exercise 4.7: the task and examples are in the text above this cell. estimates = farm.assign(w_area=farm["area_ha"] * farm["weight"]).groupby("crop").agg( plots=("plot_no", "size"), area_ha=("w_area", "sum"), ) estimates["share"] = estimates["area_ha"] / estimates["area_ha"].sum() return estimatesWhy each part is there
area_ha * weightis the land each plot stands for: a plot of 0.5 ha in a household of weight 1,000 represents 500 ha.("w_area", "sum")per crop is the weighted total;("plot_no", "size")stays unweighted, because it reports the sample a reader should know the estimate rests on.- Dividing by
estimates["area_ha"].sum()uses the total of the crops in the table, so the shares add up to 1. Dividing by the weighted area of every plot would count the plots with no crop in the denominator but in no row.
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.
The idea
A weighted total in each cell of a grid: the weighted area column, summed by pivot_table into province rows and crop columns, with 0 where there is nothing to sum.
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 area_table(farm: pd.DataFrame) -> pd.DataFrame: """Weighted crop area in hectares: provinces as the index, one column per crop, 0 where a province has none.""" # Exercise 4.8: the task and examples are in the text above this cell. return farm.assign(w_area=farm["area_ha"] * farm["weight"]).pivot_table( index="province", columns="crop", values="w_area", aggfunc="sum", fill_value=0 )Why each part is there
area_ha * weightmakes each plot's land the land it stands for, so the cells are estimates for the provinces, not the sample's hectares.aggfunc="sum": the default is"mean", which would give the mean weighted area of a plot, a number with no use here.fill_value=0: five province-crop cells have no plots (banana grows only in Bukidnon and Cotabato), and an estimated area of nothing is 0 hectares, not missing.
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.
The idea
A crosstab that adds up weights instead of counting rows gives the plots each cell stands for; normalizing by the row turns them into each province'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 irrigated_share(farm: pd.DataFrame, crop: str) -> pd.DataFrame: """For one crop: provinces as the index, No and Yes as columns, each row the weighted shares of its plots.""" # Exercise 4.9: the task and examples are in the text above this cell. one = farm[farm["crop"] == crop] return pd.crosstab(one["province"], one["irrigated"], values=one["weight"], aggfunc="sum", normalize="index")Why each part is there
farm[farm["crop"] == crop]first: the shares are of this crop's plots, not of every plot in the province.values=one["weight"], aggfunc="sum"adds up the weights in each cell, the plots it stands for. Without themcrosstabcounts sample plots, and the shares describe the sample.normalize="index"divides by each province's row total, so each row adds up to 1: "of Bukidnon's rice plots, what share is irrigated"."columns"would answer "of the irrigated plots, what share is in Bukidnon", and"all"a share of the whole table.
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.
The idea
Count on the code, which is unique, then join the counts onto the full code list with a left join, so every municipality is there once, and the ones with no household get 0.
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 municipality_counts(households: pd.DataFrame, code_list: pd.DataFrame) -> pd.DataFrame: """One row per municipality of the code list: code, name, province, households and households_represented (0 where none).""" # Exercise 4.10: the task and examples are in the text above this cell. counts = households.groupby("psgc").agg(households=("hh_id", "size"), households_represented=("weight", "sum")).reset_index() result = code_list[["code", "name", "province"]].merge(counts, left_on="code", right_on="psgc", how="left", validate="one_to_one") result[["households", "households_represented"]] = result[["households", "households_represented"]].fillna(0) return result[["code", "name", "province", "households", "households_represented"]]Why each part is there
groupby("psgc"), not by name: names repeat across provinces, so grouping by name would add Nueva Ecija's Quezon to Bukidnon's.- The code list on the left with
how="left"keeps all its municipalities; an inner join would drop the 10 where nobody was interviewed, the very ones the field office needs to see. left_on="code", right_on="psgc"matches on the code. Joining on the name would match every Quezon to every Quezon and multiply the rows;validate="one_to_one"would stop that, since each code is once in each table.fillna(0): a municipality with no match has missing counts, and "no household interviewed" is 0, not unknown.
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.
The idea
Each file's month is in its name, so it is taken from the name as the file is read, and concat's keys= stamps it on that file's rows as they are stacked.
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 stack_months(paths: list) -> pd.DataFrame: """The price files at these paths in one table: month (from each file's name), province, commodity, price_php_per_kg.""" # Exercise 4.11: the task and examples are in the text above this cell. months = [path.stem.removeprefix("farmgate_") for path in paths] tables = [pd.read_csv(path) for path in paths] return pd.concat(tables, keys=months, names=["month", "row"]).reset_index(level="month").reset_index(drop=True)Why each part is there
path.stem.removeprefix("farmgate_")turns.../farmgate_2024-10.csvinto"2024-10":stemdrops the folders and the.csv,removeprefixthe start.keys=monthslabels each table's rows with its month, in the same order as the tables;names=["month", "row"]names the two index levels this makes.reset_index(level="month")turns the month level into a column;reset_index(drop=True)then numbers the rows from 0, dropping the row numbers each file brought. (ignore_index=Truecannot be combined withkeys=: pandas 3 refuses.)