Skip to content
Learn Python with BNHR

Stretch: polars

On this page
  1. Overview
  2. S2.1 · Every price file, in one lazy query
  3. S2.2 · The months with no file
  4. S2.3 · Each crop's share of its province's farm area
  5. S2.4 · Each province's best rice yields
  6. S2.5 · Codes that match nothing

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.csv have province, commodity, price_php_per_kg, and no month: the month is only in the file's name. From farmgate_2025-01.csv on, the files have Month, Commodity, Province Code (10-digit text, to be turned into the province's name with the code list) and Price (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.

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.

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.

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.

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.

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