Skip to content
Learn Python with BNHR

Cleaning and time

On this page
  1. Overview
  2. 5.1 · Not stated is missing
  3. 5.2 · A crop summary that loses nothing
  4. 5.3 · The province in the code
  5. 5.4 · An area_ha column in the plots table
  6. 5.5 · Read the dates day-first
  7. 5.6 · Interviews by month
  8. 5.7 · One price series, every month
  9. 5.8 · Growth on the month and on the year
  10. 5.9 · The households, cleaned
  11. 5.10 · A check for the survey

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Something new

Identify results
LayerExplore a debugging drill
Debugging drill D1Debugging drill: five broken functions
AboutThe debugging drill of Python for Data Management, after module 3: five broken functions, each with one everyday mistake, to read, find, fix and explain in twenty minutes.
CoursePython for Data Management, free