Every exercise in 82_stretch_polars.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 S2.1. Every price file, in one lazy query
The task
Why it matters: course.prices() has read the farmgate prices for you all along. Here you write that reader yourself, lazily, so it would cope with ten years of files as easily as four. It is the core of the capstone's third problem.
Input → output: the prices folder and the code list (polars) → a LazyFrame that gives every price in one table of four columns, sorted by month, province, commodity:
| month | province | commodity | price |
|---|---|---|---|
| 2022-01-01 | Bukidnon | Eggplant | 29.0 |
month is a Date, the first day of the month. Two things make it more than one scan_csv:
- two layouts. Files up to
farmgate_2024-12.csvhaveprovince,commodity,price_php_per_kg, and no month: the month is only in the file's name. Fromfarmgate_2025-01.csvon, the files haveMonth,Commodity,Province Code(10-digit text, to be turned into the province's name with the code list) andPrice (PHP/kg). One glob across both fails with a SchemaError, as module 6 showed. - the code list has one row per municipality, so each province code is in it many times. Joining it as it is would copy every price once per municipality.
Tools: list the files with sorted(folder.glob("farmgate_*.csv")) and split them by name (path.stem < "farmgate_2025-01"); pl.scan_csv takes a list of paths. include_file_paths="path" adds each row's file as a column; pl.col("path").str.extract(r"farmgate_(\d{4}-\d{2})", 1) takes the month out of it, and .str.to_date("%Y-%m") makes it a date. select(new=pl.col("old")) renames as it selects. pl.concat([lazy_a, lazy_b]) stacks two queries with the same columns. The result has the same 1,316 rows as course.prices().
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| SchemaError: extra column in file outside of expected schema: Month | One scan across both layouts. | split the files by name, one scan per layout |
| SchemaError: datatypes of join keys don't match | Province Code read as a number. |
schema_overrides={"Province Code": pl.String} |
| ComputeError: join keys did not fulfill m:1 validation, or far too many rows | The code list joined as it is: a row per municipality. | .select("province_code", "province").unique() first |
SchemaError or ShapeError at pl.concat |
The two queries' columns differ in name, order or type. | the same select(month=..., province=..., commodity=..., price=...) in both |
month is String |
The month text was not made a date. | .str.to_date("%Y-%m") |
scans the files is False, or not a LazyFrame |
pl.read_csv, or .collect() at the end. |
pl.scan_csv, and return the query |
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 scans, one per layout, each turned into the same four columns, then stacked: the older files get their month from their names, the newer get their province names from the code list, reduced to one row per province first.
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 all_prices(folder: Path, code_list: pl.DataFrame) -> pl.LazyFrame: """Every monthly price file, both layouts, as one lazy query: month (a date), province, commodity, price.""" paths = sorted(folder.glob("farmgate_*.csv")) older = [p for p in paths if p.stem < "farmgate_2025-01"] newer = [p for p in paths if p.stem >= "farmgate_2025-01"] old_layout = pl.scan_csv(older, include_file_paths="path").select( month=pl.col("path").str.extract(r"farmgate_(\d{4}-\d{2})", 1).str.to_date("%Y-%m"), province=pl.col("province"), commodity=pl.col("commodity"), price=pl.col("price_php_per_kg"), ) names = code_list.lazy().select("province_code", "province").unique() new_layout = ( pl.scan_csv(newer, schema_overrides={"Province Code": pl.String}) .join(names, left_on="Province Code", right_on="province_code", how="left", validate="m:1") .select( month=pl.col("Month").str.to_date("%Y-%m"), province=pl.col("province"), commodity=pl.col("Commodity"), price=pl.col("Price (PHP/kg)"), ) ) return pl.concat([old_layout, new_layout]).sort("month", "province", "commodity")Why each part is there
- The files are split by name, because the layout changed at a known month:
"farmgate_2024-12" < "farmgate_2025-01"compares the texts letter by letter, which for names like these is the order of the months.pl.scan_csvtakes the list of paths, so each layout is one scan. include_file_paths="path"adds the file each row came from..str.extract(r"farmgate_(\d{4}-\d{2})", 1)keeps the part in brackets, such as2024-12, and.str.to_date("%Y-%m")makes it the first day of that month. The pattern looks at the file's name only, so it works whatever folder the files sit in.schema_overrides={"Province Code": pl.String}keeps the code as text, with its leading zero. Read as a number,0102800000becomes102800000, and the join stops with SchemaError: datatypes of join keys don't match.code_list.select("province_code", "province").unique()leaves one row per province. The code list has a row per municipality, so withoutunique()each price would be copied once per municipality of its province;validate="m:1"turns that mistake into an error instead of a table many times too long.select(month=..., province=..., commodity=..., price=...)gives both queries the same columns in the same order, whichpl.concatneeds. The sort gives a fixed order, and the function returns the query: the caller collects it.
Stretch S2.2. The months with no file
The task
Why it matters: a month whose file never came has no rows at all, so nothing in the table says it is missing. A chart quietly draws a straight line across the gap, and a growth rate from the month before compares two months apart. You find a missing month only by comparing the table with the calendar.
Input → output: a prices table in polars (with month as a Date, the first day of each month) → a polars DataFrame with one column, month: every month between the first and the last month of the table that has no row, in order. No missing month gives a table with no rows.
For the course's prices, the answer is one month:
| month |
|---|
| 2023-09-01 |
Tools: pl.date_range(start, end, interval="1mo", eager=True) makes the calendar (start and end from prices["month"].min() and .max()); .alias("month").to_frame() makes it a table; join(..., on="month", how="anti") keeps the rows of the left table that have no match in the right one.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| the months that are there, not those missing | A semi or inner join instead of an anti-join, or the tables the wrong way round. | calendar.join(prices, on="month", how="anti") |
ColumnNotFoundError for month in the calendar |
The calendar's column has another name (date, or none). |
.alias("month") |
TypeError or InvalidOperationError in date_range |
interval written another way, or the start and end are not dates. |
interval="1mo", and prices["month"].min() |
columns other than month |
The prices' columns came along. | an anti-join keeps only the left table's columns: the calendar on the left |
month of type Datetime |
The calendar was made with pl.datetime_range. |
pl.date_range |
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
Make the calendar the table should cover, then keep the months of the calendar that the table does not have. An anti-join does exactly that: the left rows with no partner on the right.
The function, solved
Copy it with the button on the code's frame, and paste it over all the code in the exercise's cell, so the indentation stays right.
def missing_months(prices: pl.DataFrame) -> pl.DataFrame: """The months between the first and the last of the prices that have no row.""" calendar = pl.date_range(prices["month"].min(), prices["month"].max(), interval="1mo", eager=True).alias("month").to_frame() return calendar.join(prices, on="month", how="anti").sort("month")Why each part is there
pl.date_range(first, last, interval="1mo", eager=True)lists the first day of every month from the first to the last, both included.eager=Truegives the values now, as a Series, instead of an expression..alias("month").to_frame()names the column and makes it a one-column table, so it can be joined onmonth.how="anti"keeps the calendar's months that match no row of the prices. The prices have many rows per month; an anti-join only asks whether there is any, so it does not need them reduced to one row per month first.sort("month")gives the months in order. With nothing missing the result is an empty table with themonthcolumn, which the check's second case expects.
Stretch S2.3. Each crop's share of its province's farm area
The task
Why it matters: "rice is 63% of the farmed area of Camarines Sur" is a share within a group: each crop's area over its own province's total. In module 4 (30_summarize_and_combine.py) you wrote it with groupby(...).transform("sum"). polars writes it as a window expression: pl.col("x").sum().over("province") is the sum of x over the rows of the same province, set beside every one of them, without collapsing the table.
Input → output: the clean plots and households (polars) → one row per province and crop, sorted by province then crop, with four columns:
| province | crop | area_ha | share |
|---|---|---|---|
| Bukidnon | Banana | 55,783.9… | 0.264… |
| Bukidnon | Corn | 102,079.3… | 0.483… |
area_ha is the weighted area: the sum of each plot's area_ha times its household's weight, the hectares the province's farms would show. share is that area over the province's total over all its crops, so a province's shares add up to 1. Plots with no crop recorded are left out.
Tools: a join for province and weight (validate="m:1"), drop_nulls("crop") or a filter, group_by("province", "crop").agg(...), then with_columns(share=pl.col("area_ha") / pl.col("area_ha").sum().over("province")).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
a row whose crop is null |
The plots with no crop are still there. | drop_nulls("crop") before grouping |
every share is 1.0 |
.over("province", "crop"), or over on the wrong column: each row divided by itself. |
.over("province") |
| shares add up to 1 over the whole survey, not per province | .sum() without .over(...). |
pl.col("area_ha").sum().over("province") |
area_ha equals the plain sum of areas |
The weight is not in the sum. | (pl.col("area_ha") * pl.col("weight")).sum() |
| the table has one row per plot | with_columns without group_by first. |
group by province and crop, then the share |
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
Group once to get each province and crop's weighted area, then divide each row by its province's total, which a window expression puts beside every row without a second grouping or a join.
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_shares(plots: pl.DataFrame, households: pl.DataFrame) -> pl.DataFrame: """Each crop's weighted area, and its share of its province's weighted farm area.""" return ( plots.join(households.select("hh_id", "province", "weight"), on="hh_id", how="left", validate="m:1") .drop_nulls("crop") .group_by("province", "crop") .agg(area_ha=(pl.col("area_ha") * pl.col("weight")).sum()) .with_columns(share=pl.col("area_ha") / pl.col("area_ha").sum().over("province")) .sort("province", "crop") )Why each part is there
- The join brings each plot its household's
provinceandweight;validate="m:1"stops if a household were listed twice.drop_nulls("crop")leaves out the 14 plots with no crop, which would otherwise form a group of their own and take a share. (pl.col("area_ha") * pl.col("weight")).sum()insideaggis the weighted area: each plot counts as many times as the farm households its household stands for. Naming itarea_hakeeps the unit in the name.pl.col("area_ha").sum().over("province")is the province's total, repeated on each of its rows: the polars form of pandas'groupby("province")["area_ha"].transform("sum"). Dividing by it gives shares that add up to 1 in each province.- The sort by
province,cropgives a fixed order;group_bygives none.
Stretch S2.4. Each province's best rice yields
The task
Why it matters: an extension office wants to visit the farms with the best rice yields in each province, to learn what they do. That is a rank within a group, and ranks raise a question a plain sort does not: what happens when two plots tie.
Input → output: the clean plots and households (polars), and a number n → the rice plots whose yield ranks among the top n of their province, with five columns, sorted by province, rank, hh_id, plot_no:
| province | hh_id | plot_no | yield_t_ha | rank |
|---|---|---|---|---|
| Bukidnon | HH-0881 | 2 | 6.628… | 1 |
yield_t_ha is production_kg / area_ha / 1000, as in module 6. rank is 1 for the province's highest yield. Plots that tie share a rank, and the next rank is skipped: yields 7.0, 5.0, 5.0, 4.0 rank 1, 2, 2, 4. So with n = 2 both plots at 5.0 are kept, and a province can give more than n plots.
Tools: pl.col("yield_t_ha").rank("min", descending=True).over("province"); "min" gives tied values the lowest rank of the tie, which is the rule above. Then filter(pl.col("rank") <= n).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| rank 1 is the lowest yield | Ranked in ascending order. | descending=True |
| only one province has a rank 1 | Ranked across the whole survey. | .over("province") |
| ties ranked 1 and 2, one of them dropped | The "ordinal" method, or head(n) after a sort. |
rank("min", ...) and filter(pl.col("rank") <= n) |
| ranks 1, 1, 2 instead of 1, 1, 3 | The "dense" method. |
rank("min", ...) |
| ranks like 1.5 | The default method, "average", gives ties the mean of their ranks. |
rank("min", ...) |
| Corn in the result | The rice filter is missing. | filter(pl.col("crop") == "Rice") |
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
Rank each plot's yield against the other rice plots of its own province with a window expression, then keep the ranks up to n. The rank method decides ties, and the task's rule is "min".
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 top_rice_plots(plots: pl.DataFrame, households: pl.DataFrame, n: int) -> pl.DataFrame: """The rice plots whose yield ranks among the top n of their province (ties share a rank).""" return ( plots.join(households.select("hh_id", "province"), on="hh_id", how="left", validate="m:1") .filter(pl.col("crop") == "Rice") .with_columns(yield_t_ha=pl.col("production_kg") / pl.col("area_ha") / 1000) .with_columns(rank=pl.col("yield_t_ha").rank("min", descending=True).over("province")) .filter(pl.col("rank") <= n) .select("province", "hh_id", "plot_no", "yield_t_ha", "rank") .sort("province", "rank", "hh_id", "plot_no") )Why each part is there
- The join brings each plot its
province; the filter keeps rice; the yield is computed per plot, as in Exercise 6.2. rank("min", descending=True)gives 1 to the highest yield, and tied yields the lowest rank of their tie, skipping the ranks they use up: 1, 2, 2, 4."ordinal"would break the tie by row order (1, 2, 3, 4) and drop one of two equal plots for no reason;"dense"would not skip (1, 2, 2, 3) and could keep more plots than the rule says..over("province")ranks within each province, not across the survey: each province has its own rank 1.filter(pl.col("rank") <= n)keeps the topn, ties included.head(n)after a sort would cut a tie in half and would not work per province.- The sort by
province,rank,hh_id,plot_nogives one order even when plots tie.
Stretch S2.5. Codes that match nothing
The task
Why it matters: a join with the code list brings each household its municipality's name and income class. A household whose psgc is not in the code list gets nothing: dropped by an inner join, or left with empty columns by a left join, and nobody is told. Before the join, an anti-join lists exactly what it would miss. On the clean survey that list is empty; on households read carelessly, with psgc as a number, it is most of the survey.
Input → output: households and the code list (polars, psgc and code both text) → one row per household psgc that is not a code of the code list, with households, how many households have it, sorted by psgc. Nothing unmatched gives a table with no rows.
| psgc | households |
|---|---|
| 102802000 | 10 |
(That row is from a careless read: Bacarra, Ilocos Norte, is 0102802000. Read as a number and back to text, the leading zero is gone and the code matches nothing.)
Tools: join(code_list, left_on="psgc", right_on="code", how="anti"); group_by("psgc").agg(households=pl.len()); sort("psgc").
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| the codes that do match | A semi or inner join instead of how="anti". |
how="anti" |
| ColumnNotFoundError: psgc or code | on="psgc", but the code list calls its key code. |
left_on="psgc", right_on="code" |
| SchemaError: datatypes of join keys don't match | One key is a number. | both keys as text |
| one row per household, not per code | The count is missing. | group_by("psgc").agg(households=pl.len()) |
| columns of the code list in the result | A left join, then a filter on missing names. | an anti-join keeps only the households' columns |
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
An anti-join keeps the households that a join would leave without a match; counting them by code says which codes to fix and how much each one matters.
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 unmatched_codes(households: pl.DataFrame, code_list: pl.DataFrame) -> pl.DataFrame: """The household codes that are not in the code list, with how many households have each.""" return ( households.join(code_list, left_on="psgc", right_on="code", how="anti") .group_by("psgc") .agg(households=pl.len()) .sort("psgc") )Why each part is there
join(code_list, left_on="psgc", right_on="code", how="anti")keeps the left rows with no partner on the right, and only the left table's columns: the households the code list cannot place.left_onandright_onname the key in each table, because the survey calls itpsgcand the code listcode. Both must be text: a number on one side stops the join with SchemaError: datatypes of join keys don't match, which is why the careless read is turned back into text, without its zeros, in the check.group_by("psgc").agg(households=pl.len())gives one row per code with its count: 854 rows (850 households: some were entered twice) but only 162 codes in the careless read, so a fix per code is a short list.sort("psgc")gives a fixed order. When everything matches, the result is an empty table with the two columns: the check's first case, and the answer you want before every join.