Skip to content
Learn Python with BNHR

DataFrames

On this page
  1. Overview
  2. 3.1 · Yield by household, lined up by label
  3. 3.2 · Back-check: what changed, household by household
  4. 3.3 · Read the households with their types
  5. 3.4 · Read the plots with numbers as numbers
  6. 3.5 · Read the households from the workbook
  7. 3.6 · Plots to visit
  8. 3.7 · The largest harvests
  9. 3.8 · Area in hectares, and yield
  10. 3.9 · Correct the unit where it was keyed wrongly

Every exercise in 20_dataframes.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 3.1. Yield by household, lined up by label

The task

Why it matters: yield (tonnes harvested per hectare) is the survey's main result. Production and area often come from different steps of the work, each listing households in its own order. Dividing them by position mixes households without any error; dividing them by label cannot.

Input → output: two Series indexed by hh_id, production_kg (kilograms harvested) and area_ha (hectares planted) → one Series of yield in tonnes per hectare, production_kg / 1000 / area_ha, for the households that are in both Series. The two may list households in different orders, and each may have a household the other lacks.

hh_id production_kg area_ha yield (t/ha)
HH-0001 2,340 0.633 3.697
HH-0002 5,990 1.396 4.291
HH-0005 1,500 (not in area_ha) left out

Tools: arithmetic between two Series lines them up by label (alignment, above); a label in only one of them gives NaN, and .dropna() drops those values. Name the result "yield_t_ha" with .rename("yield_t_ha") if you like; the check does not look at the name.

If the check is red: common causes

what you see the usual cause look at
The first case is right, the second is not The values were divided by position, through .to_numpy() or .values. divide the two Series themselves
ValueError: operands could not be broadcast together Arrays of different lengths were divided by position. divide the two Series themselves
The third case is not right, and your result has NaN in it The households in only one Series were kept. .dropna()
Every yield is 1,000 times too large Kilograms were not turned into tonnes. / 1000

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 3.2. Back-check: what changed, household by household

The task

Why it matters: the back-check is how a survey team finds interviews that went wrong. Its tables arrive as files with an hh_id column and row numbers 0, 1, 2, …, so the rows of two tables do not line up until you make hh_id the index.

Input → output: two tables, interview and back_check, each with columns hh_id and hh_size, rows in any order → a table with columns hh_id and change (back-check size minus interview size), one row for every household in either table, in hh_id order, rows numbered 0, 1, 2, … A household in only one table has change NaN.

interview back_check → result
hh_id hh_size hh_id hh_size hh_id change
HH-0001 7 HH-0003 8 HH-0001 -1.0
HH-0002 2 HH-0001 6 HH-0002 NaN
HH-0003 8 HH-0003 0.0

Tools: set_index("hh_id") on each table, then subtract their hh_size columns (alignment pairs the households, and puts the labels in order when the two tables list different ones); .rename("change"), then .reset_index() to turn the labels back into an hh_id column (all in section 1 above).

If the check is red: common causes

what you see the usual cause look at
The first case is right, the second is not The hh_size columns were subtracted without set_index("hh_id"), so rows were paired by number. step 1
The result's labels are HH-0001, … and it has no hh_id column The labels were not turned back into a column. .reset_index()
The columns are hh_id and hh_size, or index and change The Series was not renamed, or was made without set_index("hh_id"). .rename("change"), step 1
Every change has the wrong sign Interview minus back-check, instead of back-check minus interview. step 2
KeyError: 'hh_id' set_index was called twice, or after the column was already the index. the order of the steps

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 3.3. Read the households with their types

The task

Why it matters: every analysis of the households starts with this read. If the codes lose their zeros here, joins fail later; if the dates are swapped here, every month is wrong later, with no error to tell you.

Input → output: the path of a households CSV in the survey's layout → a table with every column of the file, where psgc is text (ten digits, zeros kept) and interview_date is dates, read day first. The other columns keep the types pandas gives them.

column in the file in your table
psgc 0102820000 "0102820000", type str
interview_date 09/11/2025 9 November 2025, type datetime64[us]
hh_size 4 4, type int64

The check reads the survey's file and two small files whose dates are harder to guess. Tools: pd.read_csv with dtype= (codes as text, above); pd.to_datetime with format= or dayfirst=True, or parse_dates= with date_format= (dates, above).

If the check is red: common causes

what you see the usual cause look at
psgc is int64, and codes have nine digits The codes were read as numbers. dtype={"psgc": str}
interview_date is str The dates were not converted. pd.to_datetime(...)
The first file is right; the second has dates in September The dates were read month first: no format, and no dayfirst=True. format="%d/%m/%Y"
ValueError: time data "25/10/2025" doesn't match format "%m/%d/%Y" The same: pandas guessed month first from the first date. format="%d/%m/%Y"
hh_size or weight is str dtype=str made every column text. name only psgc in dtype=

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 3.4. Read the plots with numbers as numbers

The task

Why it matters: production is what yields are made from. Read as text, it cannot be divided or averaged, and a sum gives one long text instead of a total.

Input → output: the path of a plots CSV in the survey's layout → a table with every column of the file, where every number is a number. The survey system writes any number of 1,000 or more with a comma, in whichever column it is: production, and sometimes an area in square meters or a fertilizer amount.

column in the file in your table
production_kg "1,640" 1640, whole numbers
area "2,500" (square meters) 2500.0, decimal numbers
crop (blank) NaN: not recorded

The check reads the survey's file and two small files: one with commas in other columns too, and one whose numbers are all under 1,000. Tools: pd.read_csv with thousands="," (thousands separators, above).

If the check is red: common causes

what you see the usual cause look at
production_kg is str The file was read without thousands=",". pd.read_csv(path, thousands=",")
The first file is right; in the second, area or fertilizer_kg is str Only production_kg had its commas removed. thousands="," covers every column
AttributeError: Can only use .str accessor with string values on the third file .str.replace on a column that has no commas, so it is already numbers. thousands="," instead
Blank crops became "" or another text The blanks were filled. read with the defaults: a blank is NaN

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 3.5. Read the households from the workbook

The task

Why it matters: an office that works in Excel sends a workbook, not a CSV. The same survey arrives with titles above the table and codes stored as numbers, and your table must come out the same as from the CSV.

Input → output: the path of a workbook laid out like farm_survey_2025.xlsx (sheets About, Households, …; three title rows above each table) → the Households sheet as a table, with psgc as ten-digit text. The dates are already dates in the workbook.

in the workbook in your table
row 4: hh_id, province, psgc, … the column names
102820000 (a number, shown as 0102820000) "0102820000"
1001317000 (ten digits already) "1001317000"

The check reads the survey's workbook and a small one of the same layout. Tools: pd.read_excel with sheet_name= and header=; .astype(str).str.zfill(10) (Excel, above).

If the check is red: common causes

what you see the usual cause look at
One column, named after the survey's title The first sheet (About) was read. sheet_name="Households"
Columns called Unnamed: 0, Unnamed: 1, … The header row is wrong: the default, or header=2. header=3
The columns are household values (HH-0001, …) and a household is missing header=4: the first household became the header. header=3
psgc is text with nine digits The codes were made text without the zeros. .str.zfill(10)
psgc is right for the survey, but the small workbook has an eleven-digit code A "0" was added in front of every code. .str.zfill(10) pads only short codes
psgc is int64 The codes were left as numbers. .astype(str).str.zfill(10)

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 3.6. Plots to visit

The task

Why it matters: an extension office plans its visits from a list: plots that are not irrigated, of the crops it advises on, within the size range of its program. Each condition left out, or joined the wrong way, puts the wrong farms on the list.

Input → output: the plots table, a list of crops, and two areas low and high → the rows of the plots whose crop is in crops, whose area_ha is from low to high (both included), and whose irrigated is "No". Every column; the rows keep their labels and their order.

plot crop area_ha irrigated with ["Rice", "Corn"], 0.5 to 1
0 Rice 0.50 No kept: 0.5 is included
2 Rice 0.70 Yes left out: irrigated
3 (not recorded) 0.70 No left out: no crop
7 Corn 1.01 No left out: above 1

Tools: isin, between, & with each condition in brackets, or query (all in this section).

If the check is red: common causes

what you see the usual cause look at
ValueError: The truth value of a Series is ambiguous and between two masks. &
TypeError: operation 'rand_' not supported… A condition without its own brackets. (...) & (...)
ValueError: ('Lengths must match to compare', …) plots["crop"] == crops compares with a list. .isin(crops)
The plots of exactly 0.5 and 1 ha are missing > and < leave out the ends. .between(low, high), or >= and <=
Irrigated plots, or other crops, are in the list | (or) where & (and) was meant, or a condition is missing. all three conditions, joined with &
The plot with no crop is in the list The crop condition was written as "not something else", which includes NaN. .isin(crops)
The rows are right, but numbered 0, 1, 2 .reset_index(drop=True) threw the labels away. return plots[mask] as it is

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 3.7. The largest harvests

The task

Why it matters: the largest harvests are the first values a survey team checks for keying mistakes (a production with one zero too many). The list must be the same every time it is made, so ties need a rule: here, by household, then by plot.

Input → output: the plots table and a number n → the n plots with the largest production_kg, largest first; plots with the same production in hh_id order, then plot_no order. Only the columns hh_id, plot_no, crop and production_kg, in that order; the rows keep their labels.

label hh_id plot_no crop production_kg
3 HH-0301 1 Banana 9,100
1 HH-0302 2 Corn 4,200
4 HH-0302 1 Rice 3,000
2 HH-0304 1 Rice 3,000

Tools: sort_values with a list of columns and a list for ascending; the first n rows by position, .head(n) or .iloc[:n]; a list of columns in brackets (all in this section).

If the check is red: common causes

what you see the usual cause look at
The smallest harvests Sorted smallest first. ascending=[False, True, True]
The right plots, but the 3,000 kg ones in the wrong order Ties not broken by hh_id and plot_no, or nlargest. sort by all three columns
Too few or too many rows, or KeyError: 'Cannot get right slice bound…' .loc[:n] takes rows down to the label n. .head(n) or .iloc[:n]
Extra columns, or the columns in another order The columns were not picked, or picked in another order. [["hh_id", "plot_no", "crop", "production_kg"]]
The rows are numbered 0, 1, 2 .reset_index(drop=True) threw the labels away. keep the labels

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 3.8. Area in hectares, and yield

The task

Why it matters: every yield in the rest of the course is computed this way. Getting the unit wrong does not raise an error: it only gives smaller yields, as the chart above shows.

Input → output: the plots as read from the file (columns area, area_unit, production_kg, …) → a new table with every column of the input and two more at the end: area_ha (the area where area_unit is "ha", the area divided by 10,000 where it is "sqm") and yield_t_ha (production_kg / 1000 / area_ha). The table given must not change.

area area_unit production_kg → area_ha yield_t_ha
0.5 ha 2,000 0.5 4.0
2,500 sqm 1,100 0.25 4.4

Tools: where and assign, with a lambda for the column that uses area_ha (this section).

If the check is red: common causes

what you see the usual cause look at
The result looks right, but the check is red The function changed the table it was given: plots["area_ha"] = ... adds the column to the caller's table. plots.assign(...)
The hectare plots are divided by 10,000 The condition is reversed. where(plots["area_unit"] == "ha", ...)
Square-meter plots have yields near 0 The yield used area, not area_ha. lambda t: ... / t["area_ha"]
KeyError: 'area_ha' plots["area_ha"] was used inside the same assign without a lambda: the column does not exist yet. yield_t_ha=lambda t: ...
Yields 1,000 times too large Kilograms were not turned into tonnes. / 1000

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 3.9. Correct the unit where it was keyed wrongly

The task

Why it matters: fixing a few values of a column is the most common change of all, and the one chained assignment breaks without an error. The survey's field manual says no plot of a farm household covers 100 hectares, so an area over 100 keyed as "ha" is a square-meter area keyed with the wrong unit.

Input → output: the plots as read from the file → a corrected table: wherever area_unit is "ha" and area is over 100, area_unit becomes "sqm". Everything else stays as it is, including an area of exactly 100. The table given must not change.

area area_unit → area_unit
0.5 ha ha
100 ha ha: not over 100
1,940 ha sqm
2,500 sqm sqm

Tools: a mask with &; .loc[mask, "area_unit"] = "sqm" on a table of its own (.copy()), or assign with mask (this section).

If the check is red: common causes

what you see the usual cause look at
Nothing changed, and a ChainedAssignmentError warning appears Chained assignment: fixed[mask]["area_unit"] = "sqm" changes a temporary copy. fixed.loc[mask, "area_unit"] = "sqm"
The result is right, but the check is red The table given was changed: fixed = plots is not a copy. plots.copy(), or assign
The plot of exactly 100 ha changed >= where the rule says over 100. > 100
The area column changed instead The column name in .loc is "area". .loc[mask, "area_unit"]

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 stretch exercises
Stretch exercises S1Stretch: DataFrames
AboutStretch exercises of Arrays & DataFrames, after module 5: harder DataFrame exercises on the farm survey, with less help, each with its check and its solution on the companion.
CourseArrays & DataFrames, free