Each run starts with fresh data. Download any exported files before another run. Your written answers and code drafts are saved on this device; export your work to keep a backup.
M28C01 · Explore · predict · practice
Choose the worksheet you actually need
An Excel workbook can contain both observations and documentation. The bundled workbook has a Penguins data sheet and a Data_Dictionary sheet with field meanings and units. Specify the intended sheet so a change in tab order does not silently change your input.
Read the example
path = DATA_DIR / "public" / "penguins.xlsx"
with pd.ExcelFile(path, engine="openpyxl") as book:
print("Worksheets:", book.sheet_names)
result = pd.read_excel(book, sheet_name="Penguins")
print(result.head(3))
print("Shape:", result.shape)
print("Known body masses:", result["body_mass_g"].count())
Verified example output · pandas 2.2.3
Worksheets: ['Penguins', 'Data_Dictionary']
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
Shape: (344, 8)
Known body masses: 342
Watch for this. The Excel lesson requires openpyxl. A .xlsx file is not plain delimited text and should not be passed to read_csv.
Your turn
Read only A:D from Data_Dictionary into a DataFrame named dictionary. Select the fields measured in mm or g into result and print those rows.
For the code check, keep these result variable names: dictionary, result. Different valid approaches are welcome.
Show a hint
Use usecols="A:D", then filter unit with isin(["mm", "g"]).
The four measurement fields appear with their units and two missing values each. The dictionary describes the data; it does not fill those missing measurements.
Compare your explanations
Read. It identifies the observations sheet directly rather than assuming it is the first worksheet.
Predict. Yes. The workbook is a faithful format conversion, with missing source measurements represented by blank cells.
Explain. Descriptions and units are metadata, not extra penguin observations.
One quick check
Which argument selects an Excel worksheet by name?
What you should be able to explain. Inspect workbook structure and choose a worksheet explicitly.
M28C02 · Explore · predict · practice
Read an array of JSON records
Records-oriented JSON represents each row as an object whose keys name fields. The bundled JSON is a documented conversion of the original public CSV. Its null values represent the same missing entries as the source NA tokens.
species island body_mass_g
0 Adelie Torgersen 3750.0
1 Adelie Torgersen 3800.0
2 Adelie Torgersen 3250.0
3 Adelie Torgersen NaN
4 Adelie Torgersen 3450.0
Loaded records: 344
Missing body masses: 2
Watch for this. JSON orientation describes structure. Use the orientation that matches the supplied document.
Your turn
Read the records JSON and summarize the number of observations and known body masses by species. Store the table in result with columns species, Observations, and Known_mass; keep species as a normal column.
For the code check, keep these result variable names: result. Different valid approaches are welcome.
Show a hint
Use groupby(..., as_index=False) with size and count.
Adelie has 152 observations and 151 known body masses; Chinstrap has 68 and 68; Gentoo has 124 and 123.
Compare your explanations
Read. One object in the outer JSON array.
Predict. No. It is parsed as a missing value.
Explain. The formats carry values differently and the readers apply their own inference rules; choose a consistent schema when exact dtypes matter.
One quick check
In records-oriented JSON, field names are stored as what?
What you should be able to explain. Read JSON according to its actual record structure.
M28C03 · Explore · predict · practice
Read JSON Lines in manageable batches
JSON Lines stores one complete JSON object on each line, without an outer array. Use lines=True so pandas reads that structure correctly. Batch processing can summarize a larger file without retaining every row at once, provided you combine the statistics correctly.
species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g sex year
0 Adelie Torgersen 39.1 18.7 181.0 3750.0 male 2007
1 Adelie Torgersen 39.5 17.4 186.0 3800.0 female 2007
2 Adelie Torgersen 40.3 18.0 195.0 3250.0 female 2007
Rows: 344
Watch for this. This file is small; chunking is taught as a technique, not claimed as a speed improvement here.
Your turn
Read the file in chunks of 100 rows. Accumulate total rows, the sum of known body masses, and the count of known body masses. Store a dictionary named result with keys rows, mass_sum_g, known_mass, and mean_mass_g for those totals and the overall mean.
For the code check, keep these result variable names: result. Different valid approaches are welcome.
Show a hint
Add each chunk sum and count separately, then divide the combined sum by the combined known count.
Show one worked solution
path = DATA_DIR / "public" / "penguins.jsonl"
total_rows = 0
mass_sum = 0.0
known_count = 0
with pd.read_json(path, lines=True, chunksize=100) as reader:
for chunk in reader:
total_rows += len(chunk)
mass_sum += chunk["body_mass_g"].sum()
known_count += chunk["body_mass_g"].count()
result = {"rows": total_rows, "known_mass": int(known_count), "mass_sum_g": float(mass_sum), "mean_mass_g": mass_sum / known_count}
print(result)
The four chunks contain 344 rows in total. The 342 known body masses sum to 1,437,000 g, giving a mean of approximately 4,201.75 g. Missing values do not contribute zero measurements to that mean.
Compare your explanations
Read. lines=True.
Predict. 344, the same number as the public CSV and records JSON.
Explain. Chunks may have different numbers of known observations; their means require weighting by those known counts.
One quick check
Which calculation correctly combines unequal chunks?
What you should be able to explain. Combine batch statistics using the matching totals and counts.
M28C04 · Explore · predict · practice
Flatten nested JSON and verify the values
Nested JSON can place related fields inside another object. The teaching version groups the four measurements under measurements while preserving all source values. json_normalize flattens those nested fields into columns with dotted names.
species island sex year measurements.bill_length_mm measurements.bill_depth_mm \
0 Adelie Torgersen male 2007 39.1 18.7
1 Adelie Torgersen female 2007 39.5 17.4
2 Adelie Torgersen female 2007 40.3 18.0
measurements.flipper_length_mm measurements.body_mass_g
0 181.0 3750.0
1 186.0 3800.0
2 195.0 3250.0
Columns: ['species', 'island', 'sex', 'year', 'measurements.bill_length_mm', 'measurements.bill_depth_mm', 'measurements.flipper_length_mm', 'measurements.body_mass_g']
Known body masses: 342
Watch for this. The nested structure is a course-created representation, not a claim about an upstream research API.
Your turn
Flatten the file, remove the measurements. prefix from column names, and restore the CSV column order. Store the source CSV in reference and the flattened table in result, using the same explicit nullable schema for both. Compare all values and missing entries.
For the code check, keep these result variable names: reference, result. Different valid approaches are welcome.
Show a hint
Use rename for the four measurement columns, reindex columns from the reference, then astype with the shared schema.
Flattening and renaming change the representation, not the observations. An explicit shared schema makes missing-value and dtype comparisons predictable across file readers.
Compare your explanations
Read. The body_mass_g field that was inside each record's measurements object.
Predict. No. Each of the 344 record objects still contributes one row.
Explain. A different nesting structure, especially lists, can change what one output row represents.
One quick check
Does json_normalize recover missing research measurements?
What you should be able to explain. Verify semantic equality after changing a data representation.