BIP · MODULE 3 · BEGINNER’S WALKTHROUGH
From a messy table
to data you can trust.
A step-by-step companion to M3_homework_REGULAR.ipynb: what to type, what each line means, and why the cleaning decisions matter.
Executed against the notebook’s embedded practice data: 17 / 17 checks passed · Final table: 38 rows
Start here
This guide follows the file M3_homework_REGULAR.ipynb found in the BIP Drive folder. It supplies worked solutions for the individual coding tasks, suggested reasoning for the written answers, and guidance for the group task. The data were deliberately made messy for teaching; the results below are not reference measurements for research.
How to use it: open your original notebook, run Setup, then work from top to bottom. In each exercise, replace the relevant None with the solution shown here. Leave the notebook’s printing, preparation, and Check cells in place. Run the exercise and then its Check cell. Do not paste all the gap-replacement lines into Setup: some depend on preparation in later cells.
The written answers below are examples to learn from, not claims about your own experience or your group’s decisions. Rewrite them in your own words.
A tiny Python vocabulary
- DataFrame: a table, like a spreadsheet.
dfis the original table. - Series: one column of a table.
df["ri"]selects the refractive-index column. - Variable: a name holding a value or object.
n_rows = len(df)saves a count. - Function or method: an operation.
len(df)counts rows;df.head(8)shows the first eight. - Boolean:
TrueorFalse. A mask is a column of these answers, one per row. - Filtering:
table[mask]keeps rows markedTrue. =versus==:=assigns a value;==asks whether values are equal.NoneversusNaN:Nonemarks unfinished answers in this notebook;NaNrepresents missing numeric data. Neither is zero.- Chaining:
column.str.strip().str.lower()does one operation, then the next on its result.
Read longer expressions from the inside out or left to right along the dots. For example, df["ri"].isna().sum() means: select ri → mark missing entries → count the marks. [Pandas basics and indexing: see the references at the end.]
Setup: load the tools and table
Run the original Setup cell unchanged, except for replacing "write your name here" with your name.
import io, gzip, base64, math
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
importmakes a library available. The names afterasare short aliases:pdfor pandas,npfor NumPy, andpltfor plotting.io,gzip, andbase64unpack the table stored inside the notebook. You do not have to edit the longDATAstring.- This provided line decodes the stored text, decompresses it, wraps the bytes as a readable file, and lets pandas read the CSV:
df = pd.read_csv(io.BytesIO(gzip.decompress(base64.b64decode(DATA))))
Read it from the innermost parentheses outward. Its result is the starting table df, with 60 rows and 7 columns.
The helper functions are there to help you learn:
show(label, value)prints an answer with its label.peek(table)prints its size and first three rows.check(...)tests whether a condition is true.check_value(...)compares a numerical answer with the expected value, allowing a small numerical tolerance.OKmeans the particular check passed; it does not prove every scientific decision is universally correct.
If you get NameError: name 'df' is not defined, the table has not been loaded in your current runtime. Run Setup again, then rerun the later exercises in order.
Task 1 · Meet the table
1.1 How big is it?
Replace the two gaps with:
n_rows = len(df)
n_cols = df.shape[1]
len(df)counts the records in the table. Save that number inn_rows.df.shapereturns a pair:(number_of_rows, number_of_columns).- Python starts counting positions at 0:
df.shape[0]is the first item, the row count;df.shape[1]is the second, the column count. - The provided
df.head(8).to_string()displays the first eight rows as text. This is an inspection step, not a cleaning step.
Expected: n_rows = 60, n_cols = 7.
1.2 Is ri a number?
ri_is_text = (not pd.api.types.is_numeric_dtype(df["ri"]))
df["ri"]selects the column.is_numeric_dtype(...)asks whether pandas stored it as a numerical data type.notreverses the answer: the variable isTruewhen the column is not numeric.print(df.dtypes)shows the type of every column. Text may appear asobjectorstr, depending on pandas version.
The notebook’s comma search is an example of a filter:
df[df["ri"].astype(str).str.contains(",")]
astype(str) makes text for inspection; .str.contains(",") marks text containing a comma; the outer df[...] selects those rows. head(3) then limits the display to three rows.
Expected: ri_is_text = True.
Suggested Answer 1.2: “Some entries use decimal commas, such as 1,429, rather than decimal points. With the default reading settings, pandas does not interpret these as ordinary numbers, so the column is stored as text instead of numeric data.”
Task 2 · Missing values and duplicates
2.1 Count missing refractive indices
n_missing = df["ri"].isna().sum()
- Select
ri. .isna()returnsTruefor a missing entry andFalseotherwise..sum()counts theTrueentries because they count as 1;Falsecounts as 0.
Expected: 3 missing entries: tetraethoxytitanium, quinoline, and cyclohexanethiol.
Nothing has been removed yet. You have counted the missing measurements; the later range filter will remove their rows. Never replace missing values with zero just to make the column numerical: that would invent a measurement.
2.2 Remove exact duplicates
The notebook already defines the comparison columns:
key_cols = [
"name", "formula", "ri", "temperature_C",
"wavelength_nm", "source"
]
Fill the gaps:
n_dup = df.duplicated(subset=key_cols).sum()
df2 = df.drop_duplicates(subset=key_cols)
subset=key_colssays which columns must agree. The artificialidis deliberately excluded: otherwise two identical records with different IDs would appear different..duplicated(...)marks the second and later copies, not the first..sum()therefore counts the extra rows to remove..drop_duplicates(...)keeps the first copy and returns a table without the repeats. Assign it todf2;dfremains the original table.
Expected: n_dup = 3; df2 has 57 rows. The duplicated compounds are cycloheptanol, chlorocyclobutane, and cyclopropylsulfanylbenzene.
There are six rows involved in three duplicate pairs, but only three extra rows. For displaying both members of a pair, use duplicated(..., keep=False); for counting removable extras, keep the default.
Task 3 · Make the numbers usable
3.1 Count decimal commas
n_comma = df2["ri"].astype(str).str.contains(",").sum()
- Start with
df2, the table after duplicate removal. - Convert the entries to text for string inspection.
- Mark entries containing a comma.
- Count those marks.
Expected: 3. The actual entries are 1,429 (ethynylcyclopropane), 1,603 (cinnamonitrile), and 1,470 (cyclooctene).
3.2 Convert text to numerical values
The first line is already provided. Fill in the second:
ri_text = df2["ri"].astype(str).str.replace(",", ".")
ri_num = pd.to_numeric(ri_text, errors="coerce")
.str.replace(",", ".")changes the text"1,429"into"1.429". It is still text at this stage.pd.to_numeric(...)converts valid numerical text into actual numbers.errors="coerce"turns anything unparseable into a missing numerical value instead of stopping with an exception. Inspect conversion failures in real work; this option can hide unexpected bad text if used without checking.- Here, the three missing original entries remain missing. There are 54 non-missing numbers among 57 rows. The 54 include values that are numerical but scientifically wrong.
The provided continuation makes a separate working table and replaces its ri column:
df3 = df2.copy()
df3["ri"] = ri_num
.copy() makes the intended separation explicit. ri_num.notna().sum() counts non-missing values; notna() is the opposite of isna(). [Reference 1.]
3.3 Fix values that are 1,000 times too big
n_big = (df3["ri"] > 100).sum()
df3["ri"] > 100asks a question for each row.- The parentheses make the comparison happen before
.sum(). - Count the values above the exercise’s threshold.
Expected: 2. In this actual table, cyclopentanone is 1437 and pentanal is 1395. The notebook’s 1436 is a teaching example, not one of the actual flagged entries.
The provided fix is:
mask_big = df3["ri"] > 100
df3.loc[mask_big, "ri"] = df3.loc[mask_big, "ri"] / 1000
mask_bigstores the True/False selection..loc[mask_big, "ri"]means “thericells in the selected rows.”- The right side divides those selected values by 1000.
- The left side writes the results back into the same cells.
The two values become 1.437 and 1.395. This correction is justified by the deliberately specified error pattern in the exercise, not by a general license to guess where decimal points belong.
Rerun trap: this cell changes df3. If you rerun it immediately, n_big becomes 0, since the two values are already fixed. Rerun 3.2 to rebuild df3, then rerun 3.3 once, followed by its Check cell.
3.4 Keep only the broad valid range
df4 = df3[(df3["ri"] >= 1) & (df3["ri"] <= 2.5)]
- The first comparison marks values at least 1.
- The second marks values at most 2.5.
&means both conditions must hold for the same row.- Each comparison needs its own parentheses. Use
&, not Python’s scalarand, for these pandas masks. - The outer brackets keep rows where the combined mask is
True. Missing entries do not pass this filter.
Expected: 50 rows. You remove three missing entries and these four out-of-range values:
| Compound | Stored ri |
|---|---|
| cyclohexanone | 0.0 |
| cyclopropanamine | 14.21 |
| cycloheptane | 14.45 |
| methylidenecyclopentane | 0.0 |
The range 1–2.5 is this exercise’s screening rule for these ordinary liquids, not a universal law for every material and wavelength. [Mask syntax: Reference 2.]
Suggested Answer 3.4: “The exercise identifies the large integer-like entries as a known factor-of-1000 error. For a value such as 13.9, dividing by 10 might look plausible, but plausibility alone does not establish the intended value. I would record the original value, flag the suspected decimal error, exclude the row for this analysis, and verify the original source before correcting it.”
The prompt’s 13.9 is another illustration. The actual suspicious nonzero entries here are 14.21 and 14.45. Record the values you actually observe in your own decision notes.
Task 4 · Match names and measurement conditions
4.1 Standardize names and count repeats
The notebook provides:
df4 = df4.copy()
df4["name_clean"] = df4["name"].str.strip().str.lower()
.str.strip()removes whitespace at the beginning and end, not inside the name..str.lower()converts letters to lowercase.- Assign the result to a new column,
name_clean, preserving the originalname.
Fill the gap:
n_same_name = df4["name_clean"].duplicated().sum()
Expected: 3 extra occurrences. The three pairs become bromobenzene, dimethoxymethylbenzene, and nonane after normalization.
The provided duplicated(keep=False) display shows all six rows involved; .sort_values("name_clean") puts each pair together so you can inspect its measurements and sources. These are not the exact duplicate records removed in Task 2: their measurements and sources differ.
Lowercasing names is a useful first pass, not a complete chemical identity check. Real synonyms, isomers, stereochemistry, and inconsistent names need stronger identifiers or source review.
4.2 Make one row per compound
The long one-line solution can be formatted this way without changing its meaning:
df5 = df4.groupby("name_clean", as_index=False).agg(
ri=("ri", "mean"),
formula=("formula", "first"),
temperature_C=("temperature_C", "first"),
wavelength_nm=("wavelength_nm", "first")
)
groupby("name_clean")gathers rows with the same cleaned name.as_index=Falsekeeps the name as an ordinary column in the result..agg(...)describes how to turn each group into one row.- The structure
output_name=("input_column", "operation")names the new column and its calculation. ri=("ri", "mean")averages the refractive indices in each group. A one-row group keeps its original value."first"retains the first available value for the other columns. It does not check that the values agree or decide which source is most trustworthy.
Expected: 47 rows. The merged measurements are:
| Cleaned name | Input values | Mean |
|---|---|---|
| bromobenzene | 1.561 and 1.560 | 1.5605 |
| dimethoxymethylbenzene | 1.493 and 1.497 | 1.495 |
| nonane | 1.405 and 1.406 | 1.4055 |
Why this needs care: the provided dataset was checked: within each cleaned-name group, formula, temperature, and wavelength agree. That makes the exercise’s use of first safe for those fields here. In real work, confirm identity and conditions before averaging, or group by identity and conditions. Otherwise you could average incompatible measurements and label them with just the first row’s temperature.
Also, this aggregation drops id, name, and source. Keep a separate provenance/decision log in a real project. An extra decimal place in a mean is arithmetic, not proof of greater measurement precision. Averaging is the course’s chosen policy, not proof that the average is the true value.
4.3 Keep 20 °C and 589 nm
keep20 = (df5["temperature_C"] == 20) & (df5["wavelength_nm"] == 589)
== 20marks rows measured at the target temperature.== 589marks rows measured at the target wavelength.&requires both. The variable namekeep20is only a name: the expression actually checks both conditions.
The notebook already provides:
n_other = int((~keep20).sum())
df6 = df5[keep20]
~ flips each True to False and vice versa. Thus (~keep20).sum() counts rows not meeting both conditions. int(...) turns the count into an ordinary Python integer. The printed value_counts() summaries show how often each temperature and wavelength appears.
Expected: n_other = 6; df6 has 41 rows. Four rows are at 25 °C and two are at 633 nm; these groups do not overlap in this table.
- 25 °C: cyclobutanone, isothiocyanatobenzene, propylcyclohexane, trimethoxymethane.
- 633 nm: ethenylsulfanylethane, phenylhydrazine.
Suggested Answer 4.3: “We could correct some measurements if we knew a suitable, validated temperature dependence or wavelength-dispersion relationship for the particular liquid, along with enough information to estimate correction uncertainty. The notebook’s approximate temperature coefficient is a teaching approximation, not a universal constant. A temperature correction alone cannot convert 633 nm data to 589 nm.”
To understand the sign of that approximation: the prompt says warming lowers ri by about 0.0004 per degree. Moving from 25 °C back to 20 °C would therefore add approximately 0.002, at the same wavelength. Task 4.3 asks you to filter, not apply this optional correction.
Task 5 · Use the chemist’s eye
5.1 Understand the provided alkane selection
df6 = df6.copy()
df6["n_C"] = df6["formula"].str.extract(r"C(\d+)")[0].astype(float)
df6["n_H"] = df6["formula"].str.extract(r"H(\d+)")[0].astype(float)
alkanes = df6[
df6["formula"].str.fullmatch(r"C\d+H\d+")
& (df6["n_H"] == 2 * df6["n_C"] + 2)
]
The expressions look technical, but each has a narrow job:
r"..."is a raw string, convenient for patterns with backslashes.- In
C(\d+),Cmatches the carbon symbol;\d+matches one or more digits; parentheses capture those digits. ForC10H22, this extracts"10". .str.extract(...)returns a table of captures.[0]selects the first capture column, not the first row..astype(float)converts the extracted text to numbers; missing matches can remain missing. The hydrogen line does the same for H..str.fullmatch(r"C\d+H\d+")requires the whole formula to contain just C and H with explicit numbers in that order. It rejects an oxygen-containing formula even if its C/H counts happen to fit.n_H == 2 * n_C + 2checks the formula pattern for the acyclic saturated hydrocarbons selected in this exercise.&requires both tests.
This is a small pattern matcher for the notebook’s formula format, not a general chemical formula parser or a test for molecular structure. It does not handle every valid formula notation, such as omitted counts of one, reordered elements, or parentheses.
Fill the gap:
n_alkanes = len(alkanes)
Expected: 6.
Read the plot, one line at a time
fig, ax = plt.subplots(figsize=(6, 3.5))creates a figure and a plotting area; the size is in inches.ax.scatter(alkanes["n_C"], alkanes["ri"], ...)plots carbon count horizontally and refractive index vertically. The color argument changes appearance, not data.for _, r in alkanes.iterrows():visits each alkane row._is the unused row index;rcontains that row’s values.ax.annotate(...)writes the compound’s name beside its point. The offset moves the text slightly so it is easier to read.set_xlabel(...)andset_ylabel(...)name the axes;plt.show()displays the plot.

| Compound | Carbon atoms | Stored ri |
Exercise decision |
|---|---|---|---|
| pentane | 5 | 1.538 | Flag |
| hexane | 6 | 1.573 | Flag |
| heptane | 7 | 1.388 | Keep |
| octane | 8 | 1.398 | Keep |
| nonane | 9 | 1.4055 | Keep |
| decane | 10 | 1.142 | Flag |
5.2 Select the suspect names
suspects = alkanes[
(alkanes["ri"] < 1.35) | (alkanes["ri"] > 1.43)
]["name_clean"].tolist()
- Start with the alkanes, not every liquid in
df6. A plausible value for one chemical family can be unusual for another. < 1.35marks unusually low values for this exercise’s selected series.> 1.43marks unusually high ones.|means either comparison can be true;&would ask for a value to be both below 1.35 and above 1.43, which cannot happen.[...]keeps the flagged alkane rows.["name_clean"]then selects their names, and.tolist()makes an ordinary Python list.
Expected: ["decane", "hexane", "pentane"] in the execution order here. The notebook sorts the list for its check, so the order is not the scientific point.
The 1.35–1.43 interval is a rule for these selected alkanes under the target conditions, not a universal acceptance interval for all alkanes or all liquids. “Suspect” means “needs investigation,” not that a range test alone proves a measurement is wrong.
5.3 Exclude the suspects
df7 = df6[~df6["name_clean"].isin(suspects)]
.isin(suspects)isTrueif a row’s name occurs in the suspect list.~reverses that selection so you keep the other names.- Filter the full
df6table, not only the alkanes: the output should retain acceptable compounds from every family. - Save the result as
df7.
Expected: 38 final rows. The workflow excludes three suspects; it does not replace their values with guessed literature numbers.
Suggested Answer 5.3: “Pentane at 1.538 passes the broad 1–2.5 screen because it is numerically inside that interval. But the interval only catches extreme errors. Comparing pentane with chemically similar alkanes at the same conditions reveals that it is inconsistent with this series. The plot makes that mismatch visible.”
Task 6 · Discuss and reflect
This is not another fill-in coding test. Your group has to supply its actual discussion and finish the separate melting-point class exercise. The refractive-index notebook alone cannot establish your group’s melting-point decisions.
Understand the summary code
The provided story is a list of (description, number) pairs. In:
for what, number in story:
print(f"{what:30s} {number}")
for visits the pairs one at a time; what, number assigns their two parts to variables. The f string inserts their values into text. :30s formats the description in a field at least 30 characters wide so the counts line up. The None if ... else ... expressions elsewhere in the summary are safeguards for unfinished work.
After completing Tasks 1–5, the summary should read:
start 60
exact duplicates removed 3
missing values removed 3
impossible values removed 4
repeated names merged 3
other conditions removed 6
suspect alkanes removed 3
final rows 38
6.1 Compare your experiences
Each person should say what was easiest, what was hardest, and what surprised them. These are personal answers; there is no honest prewritten “correct” response. If someone has NOT YET, compare the first point where their row counts diverge from the timeline below.
6.2 Finish the melting-point class work
With your group, complete the separate cleaned melting-point table and decision list. Discuss which checks are easy to automate—missing values, formatting, exact duplicates, and condition filters—and which decisions require chemical judgment, source checking, or reasoning about uncertainty. Record where you disagreed and why. Agree who uploads the actual group work as directed by the course.
6.3 Reflection prompts, with example reasoning
- Source: “This is the course’s embedded, deliberately altered practice dataset. Its labels include Database, Paper, Handbook, and Supplier sheet, but they are not complete bibliographic citations.” For real use, you would need traceable original sources.
- Units: “Refractive index is dimensionless. Temperature is in degrees Celsius, and wavelength is in nanometres.” Do not label
riin nm; nm belongs to the measurement wavelength. - Conditions: “The retained measurements are at 20 °C and 589 nm.”
- Uncertainty: “No numerical measurement uncertainties are supplied. Differences between sources illustrate disagreement, but they are not automatically standard uncertainties. Averaging and cleaning do not establish the true uncertainty.”
- Still confusing: choose something that genuinely remains unclear to you; for example, why masks need
&instead ofand, or when averaging two sources is justified. - Question for a tutor: an example is “What evidence would justify correcting a suspected transcription error instead of excluding it?” Choose your own question if something else matters more.
The whole cleaning journey
| Stage | What changed | Rows remaining |
|---|---|---|
df |
Starting practice data | 60 |
df2 |
Remove 3 extra exact-duplicate rows | 57 |
df3 |
Convert text; repair 2 factor-of-1000 entries | 57 |
df4 |
Remove 3 missing and 4 out-of-range entries | 50 |
df5 |
Merge 3 extra same-name records by taking means | 47 |
df6 |
Remove 6 records at other conditions | 41 |
df7 |
Remove 3 suspect alkane records | 38 |
Formatting repairs change values, not row counts. Merging reduces rows but is not the same operation as deleting an exact duplicate. Counting missing entries happens in Task 2; removing them happens in Task 3.4.
Troubleshooting before you submit
NameError: a required earlier cell has not run in the current session. Start at Setup and proceed in order.NoneTypeerror: an earlier answer is stillNone, or a dependent cell has not been rerun after you filled it.- “Truth value of a Series is ambiguous”: use
&or|for row-by-row pandas comparisons, with parentheses around each comparison; do not useandoror. - The factor-of-1000 count is zero: you probably reran the fixing cell. Rebuild
df3in 3.2 and continue forward once. - Repeated-name count is zero: check that you used
df4, not the already groupeddf5. - Numbers do not match: inspect the earliest mismatch. Do not change a threshold just to force the final count.
- Indexes have gaps: filters preserve original row labels. That is normal; use
len(table)for row counts, not the last index label. - Only three rows remain at the end: you may have kept the suspects rather than inverted the mask with
~. - Final run: use the notebook’s Run all command from a clean start. Confirm all 17 supplied checks print
OK, add your written answers, finish the group requirements, and save/upload as the course instructs.
Sources and verification
Primary course source: BIP / M3_homework_REGULAR.ipynb, retrieved from the connected Drive folder for this walkthrough. The original file was not modified. Exercise-specific thresholds, tasks, and teaching assumptions come from that notebook. The table contents and outputs shown here were read or computed from its embedded practice data, not filled with invented examples. The full notebook and raw dataset are not included in this public site.
Execution verification: all code cells, including the original Check cells, were run sequentially with the 16 answer gaps filled. All 17 checks passed; the final table had 38 rows. Checked with pandas 3.1.1. The duplicate-name groups were additionally checked for agreement in formula, temperature, and wavelength before explaining the aggregation.
Official pandas references for the syntax used here:
- Converting numerical text:
pandas.to_numeric— conversion anderrors="coerce". - Indexing and selecting data — selecting rows and columns,
.loc, masks, and duplicate handling. - Group-by operations — grouping and named aggregation.
- Working with missing data — identifying and handling missing entries.
This is a learning companion, not an official marking scheme. A passed check shows agreement with this exercise, not independent validation of the underlying chemical measurements.