Every exercise in 40_cleaning_and_time.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 5.1. Not stated is missing
The task
Why it matters: every average, share or chart of the head's age or sex is wrong while 999 and 9 are counted as answers. This function fixes both columns at once, and section 5 builds it into the cleaning you can rerun.
Input → output: the households table → the same table, with head_sex 9 and head_age 999 turned into <NA>, and both columns of type Int64. Every other column stays as it was.
| head_sex | head_age | → | head_sex | head_age |
|---|---|---|---|---|
| 1 | 58 | 1 | 58 | |
| 9 | 999 | <NA> |
<NA> |
|
| 2 | 99 | 2 | 99 |
A head aged 99 is rare but possible: only the code 999 means "not stated". The codes are in the setup cell as NOT_STATED = {"head_sex": 9, "head_age": 999}.
Tools: .astype("Int64") and .mask(condition), above; table.assign(column=...) returns a new table with that column replaced (module 3 (20_dataframes.py), section 4).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
the type is float64 |
mask on the plain integer column: pandas turned it into decimals to hold NaN |
astype("Int64") before .mask(...) |
the type is object |
replace(999, pd.NA) on a plain integer column, which gives a column of mixed Python values |
astype("Int64").mask(column == 999) |
| 0 or an average where the code was | the codes were filled instead of made missing | .mask(...) makes missing; leave out fillna |
| the head aged 99 is missing | the condition catches more than the code, such as >= 99 |
== code |
| wrong on "a table with no codes at all" | the type is changed only when a code is found | change the type for every table |
| one column still has its code | only one column is handled | loop over NOT_STATED.items() |
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
Turn each column into nullable integers first, so it can hold a missing value without becoming decimals, then mask the one value that means "not stated". The codes come from the codebook, kept in one dictionary.
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 mark_not_stated(households: pd.DataFrame) -> pd.DataFrame: """The households with the not-stated codes (head_sex 9, head_age 999) as <NA>, both columns nullable Int64.""" # Exercise 5.1: the task and examples are in the text above this cell. table = households for column, code in NOT_STATED.items(): table = table.assign(**{column: table[column].astype("Int64").mask(table[column] == code)}) return tableWhy each part is there
astype("Int64")comes first: masking a plainint64column turns it intofloat64, so ages would become 58.0 and the check's types would not match..mask(households[column] == code)makes missing exactly the rows with the code: 999 and nothing else, so a head aged 99 keeps the age.- The loop over
NOT_STATED.items()keeps the codes in one place: a third coded column would be one more entry in the dictionary, not more code. assign(**{column: ...})returns a new table, so the raw table stays as it was read, and the function can run again on it.
Exercise 5.2. A crop summary that loses nothing
The task
Why it matters: this is the table a report would print. Every plot must be counted once, every kilogram of production must be in a total, and the average fertilizer must describe the plots whose amount is known.
Input → output: the plots table → one row per crop, the crops in alphabetical order (as groupby sorts them), with the plots that have no crop as a row named Not recorded, and three columns:
| column | what it holds |
|---|---|
plots |
how many plots (every plot, whether or not its fertilizer is recorded) |
production_kg |
the total production |
fertilizer_kg |
the average fertilizer, over the plots whose amount is recorded |
| crop | plots | production_kg | fertilizer_kg |
|---|---|---|---|
| Banana | 137 | … | … |
| … | |||
| Not recorded | 14 | … | … |
Tools: fillna, above; groupby(...).agg(name=("column", "function")) with "size", "sum" and "mean" (module 4 (30_summarize_and_combine.py), section 1).
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
no Not recorded row |
groupby leaves out missing keys |
fillna("Not recorded") on crop first |
a row named NaN or 0 |
groupby(dropna=False) keeps the group but not the name; fillna(0) on the whole table |
fill crop alone, with "Not recorded" |
| fertilizer averages too low | missing amounts filled with 0 | leave fertilizer_kg missing |
| fewer plots than in the table | dropna() before grouping, or "count" on a column with gaps |
no dropna(); ("plot_no", "size") |
| KeyError naming a column | a typo in a column name, or agg given a name that is not a column |
the column names in the task |
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
Fill the missing label so those plots keep a row of their own, and leave the missing amounts alone: mean() skips them, which is exactly "the average over the plots whose amount is recorded".
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 (missing crops as "Not recorded"): number of plots, total production, average fertilizer.""" # Exercise 5.2: the task and examples are in the text above this cell. labeled = plots.assign(crop=plots["crop"].fillna("Not recorded")) return labeled.groupby("crop").agg( plots=("plot_no", "size"), production_kg=("production_kg", "sum"), fertilizer_kg=("fertilizer_kg", "mean"), )Why each part is there
fillna("Not recorded")oncropalone gives the 14 plots a group of their own. Without it,groupbydrops them, and their production is missing from the totals.fertilizer_kgis not filled: a missing amount filled with 0 would pull the average down, as the dropdown above shows.("plot_no", "size")counts every row in the group."count"would count only the values that are not missing, so counting a column with gaps would give fewer plots than there are.- No
dropna(): it would drop rows missing any column, and with them their production.
Exercise 5.3. The province in the code
The task
Why it matters: a PSGC code is built from parts. Its first two digits are the region and the next three the province, so the province's own code is those five digits followed by five zeros. That is the key that joins a household to the province rows of the PSA code list (course.code_list()["province_code"]), and it only works while the code is text: as a number, the leading zero of 0102800000 is gone.
Input → output: a column of 10-digit PSGC codes, as text → a column of province codes, as text, in the same order.
| psgc | → province code |
|---|---|
"0102820000" |
"0102800000" |
"1204712000" |
"1204700000" |
Tools: .str[:5] and adding a text, above.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
numbers such as 102800000 |
the code was made a number first, which drops the leading zero | keep the code as text: .str[:5] |
"0102000000" for "0102820000" |
four characters kept instead of five | .str[:5] |
"01028" |
the five zeros were not added | + "00000" |
| AttributeError: Can only use .str accessor with string values | the column is not text (read without dtype={"psgc": str}) |
raw_households["psgc"].dtype should be str |
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
Work on the code as text: keep its first five characters, the region and the province, and put five zeros after them. Text keeps the leading zero that a number would drop.
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 province_code(psgc: pd.Series) -> pd.Series: """The province's PSGC code for each 10-digit code: its first five digits followed by five zeros, as text.""" # Exercise 5.3: the task and examples are in the text above this cell. return psgc.str[:5] + "00000"Why each part is there
psgc.str[:5]slices every value at once, as"0102820000"[:5]slices one text:"01028".+ "00000"adds the same text to every value, giving the 10 characters a PSGC code always has.- No
astype(int): as a number,0102820000is102820000, and no arithmetic gives the leading zero back.
Exercise 5.4. An area_ha column in the plots table
The task
Why it matters: module 2 converted the areas as a NumPy array (Exercise 2.5); here the conversion becomes a column of the table, so it travels with each plot through filters, groups and joins. Yields, totals of land and every per-hectare figure in the later modules use this column. It is also the second half of the course's cleaning of the plots, so section 5 reuses it.
Input → output: the plots table → the same table with one new column at the end, area_ha: the area in hectares, whatever unit it was written in. area and area_unit stay as they are.
| area | area_unit | → area_ha |
|---|---|---|
| 1.5 | ha | 1.5 |
| 2500 | sqm | 0.25 |
Tools: .where(condition, other), above; assign; SQM_PER_HA is in the setup cell.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| areas of thousands of hectares | the square meters were left as they were, or multiplied | divide by SQM_PER_HA |
| the hectares are tiny too | every area was divided, not only the square meters | .where(area_unit == "ha", ...) keeps the hectares |
| 2,500 sqm gives 2.5 or 25 | divided by 1,000 or 100 | 10,000 square meters in a hectare |
| wrong only on the survey | area itself was changed, or the column is not last |
add a new column with assign(area_ha=...) |
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 column in one unit: keep hectares as they are, divide square meters by 10,000, and store the result beside the original, so what was written is still there to check.
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 add_area_ha(plots: pd.DataFrame) -> pd.DataFrame: """The plots with a new last column, area_ha: every area in hectares (square meters divided by 10,000).""" # Exercise 5.4: the task and examples are in the text above this cell. return plots.assign(area_ha=plots["area"].where(plots["area_unit"] == "ha", plots["area"] / SQM_PER_HA))Why each part is there
plots["area"].where(plots["area_unit"] == "ha", plots["area"] / SQM_PER_HA)keeps the hectares and replaces the rest with their value in hectares, row by row.- Divide, don't multiply: a hectare is the bigger unit, so there are fewer hectares than square meters.
assign(area_ha=...)adds a new last column and leavesareaandarea_unitalone, so the table still says what the enumerator wrote.
Exercise 5.5. Read the dates day-first
The task
Why it matters: every count by month, every time between two dates and every check against the fieldwork period depends on the dates being the right ones. A wrong guess that doesn't fail is the one nobody notices.
Input → output: a column of dates written as text, day/month/year → a column of dates (datetime64), each the date that was written.
| text | → date |
|---|---|
"03/11/2025" |
3 November 2025 |
"25/12/2025" |
25 December 2025 |
Tools: pd.to_datetime(text, format=...), above.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| ValueError: time data "25/12/2025" doesn't match format "%m/%d/%Y" | no format given, and pandas guessed month-first from the first value | format="%d/%m/%Y" |
| wrong on "days all 12 or less", with no error | a guess (no format, or format="mixed") read them month-first |
format="%d/%m/%Y" |
| ValueError … doesn't match format "%d/%m/%y" | %y is a two-digit year |
%Y, capital |
| ValueError … doesn't match format "%m/%d/%Y" | day and month swapped in the format | %d first |
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
Say the format, so pandas never guesses: day, then month, then a four-digit year, separated by slashes.
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 parse_day_first(text: pd.Series) -> pd.Series: """The dates written as day/month/year text, as dates.""" # Exercise 5.5: the task and examples are in the text above this cell. return pd.to_datetime(text, format="%d/%m/%Y")Why each part is there
format="%d/%m/%Y"makes the order a rule:03/11/2025is the 3rd of November, whatever the first value of the column is.- Without a format, pandas guesses from the first value, so the same code reads one file correctly and the next one wrongly;
format="mixed"guesses for each value, and swaps day and month wherever the day is 12 or less. - A value that doesn't fit the format now raises an error that names it, which is what you want from a column that should all be one format.
Exercise 5.6. Interviews by month
The task
Why it matters: a fieldwork report starts with how many interviews were done each month. Counting by period keeps each month with its year, and a count in the wrong month is the first sign of a date read the wrong way.
Input → output: a column of dates → the number of interviews in each month, as a column indexed by month periods (2025-10, 2025-11, …), in order of month.
| month | interviews |
|---|---|
| 2025-10 | … |
| 2025-11 | … |
| 2025-12 | … |
Tools: .dt.to_period("M"), above; value_counts() and sort_index() (module 3 (20_dataframes.py), section 3), or groupby(...).size().
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
months as 10, 11, 12 |
.dt.month gives the month's number without its year |
.dt.to_period("M") |
months as text such as "2025-10" |
.dt.strftime(...) makes text, not periods |
.dt.to_period("M") |
| the right counts, in the wrong order | value_counts() puts the most frequent first |
.sort_index() |
| AttributeError: Can only use .dt accessor with datetimelike values | the dates are still text | parse them first (Exercise 5.5) |
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
Turn each date into the month it falls in, keeping its year, then count the months and put them in calendar 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 interviews_by_month(dates: pd.Series) -> pd.Series: """The number of dates in each month: a column indexed by monthly periods, in order.""" # Exercise 5.6: the task and examples are in the text above this cell. return dates.dt.to_period("M").value_counts().sort_index()Why each part is there
dates.dt.to_period("M")gives2025-11for any day in November 2025: a month with its year, so January 2025 and January 2026 stay apart.value_counts()counts each month, most frequent first;sort_index()puts them in calendar order, which periods know.dates.groupby(dates.dt.to_period("M")).size()gives the same counts, already in order.
Exercise 5.7. One price series, every month
The task
Why it matters: every later step (quarters, a rolling mean, growth on the month and on the year) assumes one row per month. This function makes that true for any commodity, with the missing month visible instead of skipped.
Input → output: the prices table and a commodity → that commodity's average price across the provinces in each month, as a column indexed by monthly periods, with every month from the first to the last and NaN where a month has no prices.
| month | price |
|---|---|
| 2023-08 | 22.24 |
| 2023-09 | NaN |
| 2023-10 | 21.27 |
Don't fill the gap: no price was collected that month (section 1's rule for amounts).
Tools: a filter (module 3 (20_dataframes.py), section 3), groupby and mean, pd.period_range and reindex, above.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| 47 months instead of 48 (3 instead of 4 in the small table) | the missing month was not added | reindex(pd.period_range(...)) |
still 47 after asfreq("M") |
on a period index, asfreq re-labels; it adds no months |
reindex |
| the missing month has a price | filled with ffill, interpolate or fillna |
leave it NaN |
months shown as 2023-09-01 |
the index was turned into timestamps | keep the periods for this function |
| prices too high or too low | the commodity was not filtered, or sum/median used |
prices["commodity"] == commodity, then mean() |
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
Average the commodity's prices by month, then put the result on the full list of months, so a month with no file becomes a visible NaN instead of a row that isn't there.
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 monthly_series(prices: pd.DataFrame, commodity: str) -> pd.Series: """A commodity's average price across provinces for every month from the first to the last (NaN where none).""" # Exercise 5.7: the task and examples are in the text above this cell. by_month = prices[prices["commodity"] == commodity].groupby("month")["price"].mean() every = pd.period_range(by_month.index.min(), by_month.index.max(), freq="M") return by_month.reindex(every)Why each part is there
prices[prices["commodity"] == commodity].groupby("month")["price"].mean()gives one average a month, for the months that have prices.pd.period_range(first, last, freq="M")lists every month between them, including the ones with no rows..reindex(...)gives the series one row per month in that list, NaN where it had none. On a period index,asfreq("M")would not do this: it re-labels the periods it has and adds none..resample("M").mean()on the averages would also add the missing month.- No
fillnaorffill: the price of a month nobody collected is unknown, and a filled value would look measured.
Exercise 5.8. Growth on the month and on the year
The task
Why it matters: "palay is up 6.6% on a year ago" is the sentence a price bulletin leads with. It must compare August with August, even when a file is missing in between.
Input → output: a monthly price series indexed by monthly periods, which may skip months → a table with one row for every month from the first to the last and three columns: price (NaN in the months skipped), mom_pct (percent change from the month before) and yoy_pct (percent change from the same month a year before). A change that would need a missing month is NaN.
| month | price | mom_pct | yoy_pct |
|---|---|---|---|
| 2024-08 | 23.71 | … | 6.6 |
| 2024-09 | … | … | NaN |
Tools: pd.period_range and reindex, above; pct_change(1) and pct_change(12), times 100; pd.DataFrame({"price": ..., ...}) to build the table.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| right on "none left out", wrong on the others | pct_change ran on the series that skips a month, so it compared across the gap |
reindex to every month first |
changes such as 0.066 instead of 6.6 |
fractions, not percent | * 100 |
yoy_pct equals mom_pct |
pct_change() used for both |
pct_change(12) for the year |
| numbers where the gap should give NaN | the gap was filled (ffill, interpolate) before the changes |
leave the missing month NaN |
| changes in pesos, not percent | diff() gives the difference, not the change in percent |
pct_change |
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 every month a row first, so that "one row back" is the month before and "twelve rows back" is the same month a year before; then compute both changes, in percent.
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 growth(monthly: pd.Series) -> pd.DataFrame: """Every month from the first to the last: the price, its percent change on the month and on the year.""" # Exercise 5.8: the task and examples are in the text above this cell. price = monthly.reindex(pd.period_range(monthly.index.min(), monthly.index.max(), freq="M")) return pd.DataFrame({"price": price, "mom_pct": price.pct_change() * 100, "yoy_pct": price.pct_change(12) * 100})Why each part is there
monthly.reindex(pd.period_range(first, last, freq="M"))comes first.pct_changecounts rows, and on a series that skips a month, 12 rows back is 13 months back.pct_change()is(this − previous) / previous, a fraction;* 100makes it the percent the table asks for.pct_change(12)compares each month with the one 12 rows, and so 12 months, before: the same month a year earlier.- A change that needs the missing month is NaN by itself: pandas 3's
pct_changenever fills a missing value (see the box below).
Exercise 5.9. The households, cleaned
The task
Why it matters: this is the cleaning the rest of the course relies on, in one function you can run again on next year's file. It brings together section 1 and this section.
Input → output: the raw households table → the table with rows entered twice dropped (only rows identical in every column), the index numbered again from 0, and the not-stated codes made missing (your mark_not_stated from Exercise 5.1). A household with two different answers keeps both rows: that is for the checks.
Tools: drop_duplicates() and reset_index(drop=True), above; mark_not_stated.
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
| 1,196 rows | the copies were not dropped | drop_duplicates() |
| the household with two ages lost a row | drop_duplicates("hh_id") keeps the first row of each key |
drop_duplicates(), no columns named |
| the right rows, but the check is red | the old row numbers were kept (the index jumps where copies were) | reset_index(drop=True) |
a column named index |
reset_index() without drop=True |
drop=True |
999 and 9 still there, or float64 |
mark_not_stated not applied, or Exercise 5.1 not right yet |
Exercise 5.1's check |
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
Drop only what is certainly a copy (identical in every column), number the rows again, then reuse the not-stated function: cleaning is the earlier steps, composed in one place you can rerun.
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 clean_households(raw: pd.DataFrame) -> pd.DataFrame: """The households cleaned: rows entered twice dropped, rows numbered from 0, not-stated codes as <NA>.""" # Exercise 5.9: the task and examples are in the text above this cell. return mark_not_stated(raw.drop_duplicates().reset_index(drop=True))Why each part is there
drop_duplicates()with no columns named drops a row only when every value matches an earlier row, so two different answers for one household both survive for the checks to find.reset_index(drop=True)gives the rows 0, 1, 2, … again;drop=Truethrows the old numbers away instead of keeping them as a column.mark_not_stated(...)is Exercise 5.1, reused rather than written again, so the codes live in one place.- The order of steps 1 and 3 doesn't change the result: a copy is still a copy after its codes are masked.
Exercise 5.10. A check for the survey
The task
Why it matters: this function is what you run first on the next survey file. It tells you, in one list, everything this module found by hand.
Input → output: a households table and a plots table → the list of the names of the rules they break, in the order below; an empty list when they break none.
| rule | it holds when |
|---|---|
hh_id is unique |
no hh_id appears twice in the households |
each plot is listed once |
no two plots share hh_id and plot_no |
every plot has a household |
every plot's hh_id is in the households |
head_sex is 1, 2 or missing |
every head_sex is 1, 2 or missing (so the code 9 breaks it) |
head_age is 15 to 100 or missing |
every head_age is from 15 to 100, or missing |
every area is in hectares |
the plots have an area_ha column, and every value in it is above 0 |
The skeleton has the names and two of the rules written; you write the other four. Each rule is one True or False for the whole table: a condition per row, then .all() ("every row") or .any() ("at least one row").
Tools: is_unique, duplicated(subset), above; isin (module 3 (20_dataframes.py), section 3), between, isna().
If the check is red: common causes
| what you see | the usual cause | look at |
|---|---|---|
the cleaned survey breaks head_sex is 1, 2 or missing |
isin([1, 2]) is False for a missing value |
add ` |
| ValueError: The truth value of a Series is ambiguous | a rule is a column of True/False, not one value |
end the rule with .all() or .any() |
| KeyError: 'area_ha' | the area rule reads the column before checking it exists | keep "area_ha" in plots.columns and ... as given |
| the names of the rules that hold, not the ones that fail | if holds instead of if not holds |
the last line of the skeleton |
| the plot rule fails on clean tables | duplicated("hh_id"): a household may have several plots |
the key is ["hh_id", "plot_no"] |
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
Write each rule as one True or False for the whole table, keep them in a dictionary by name, and return the names of the ones that are False: every problem at once, in a list that is empty when all is well.
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 survey_problems(households: pd.DataFrame, plots: pd.DataFrame) -> list[str]: """The names of the rules the survey's tables break, in a fixed order; an empty list when they break none.""" # Exercise 5.10: the task and examples are in the text above this cell. rules = { "hh_id is unique": households["hh_id"].is_unique, "each plot is listed once": not plots.duplicated(["hh_id", "plot_no"]).any(), "every plot has a household": bool(plots["hh_id"].isin(households["hh_id"]).all()), "head_sex is 1, 2 or missing": bool((households["head_sex"].isin([1, 2]) | households["head_sex"].isna()).all()), "head_age is 15 to 100 or missing": bool((households["head_age"].between(15, 100) | households["head_age"].isna()).all()), "every area is in hectares": "area_ha" in plots.columns and bool(plots["area_ha"].gt(0).all()), } return [name for name, holds in rules.items() if not holds]Why each part is there
- Each plot once:
not plots.duplicated(["hh_id", "plot_no"]).any():duplicatedmarks every repeat of the key,.any()asks whether there is at least one, andnotturns "there is a repeat" into "the rule holds". - Every plot has a household:
plots["hh_id"].isin(households["hh_id"]).all(): each plot's household is among the households. - head_sex:
(households["head_sex"].isin([1, 2]) | households["head_sex"].isna()).all(). Missing must be allowed in so:isin([1, 2])isFalsefor<NA>, so without the| isna()the cleaned table, whose not-stated answers are missing, would break the rule. - head_age:
(households["head_age"].between(15, 100) | households["head_age"].isna()).all():betweenincludes both ends; the code 999 is outside them. - Areas:
"area_ha" in plots.columns and ...checks that the column exists before reading it.andstops at the firstFalse, so a table without the column givesFalseinstead of a KeyError. bool(...)andnotmake each rule a plainTrueorFalse; a column of them (forgetting.all()) would raise ValueError: The truth value of a Series is ambiguous.