Lesson 4 of 8
Module 1 · Python basics
Real sample sheets are typed by people. This lesson finds and fixes the errors in a deliberately messy copy of the airway sheet, then joins it to a second table.
Before you start. This lesson carries on in the notebook from NumPy, pandas and files. If you open a new notebook, run this setup cell first.
import pandas as pd
Find the problems
Before fixing anything, look. Cleaning data has two separate steps: first find and list every problem, then fix each one in code. Doing them in that order means you know what you changed and why. The file below is a copy of the airway sample sheet from NumPy, pandas and files, with mistakes added on purpose, of the kinds people make when they type a sheet by hand.
messy = pd.read_csv("airway_samples_messy.csv")
print(messy.shape)
print(messy["dex"].value_counts(dropna=False))
print(messy["cell"].unique().tolist())
print(messy.duplicated().sum())What this does
messy.shapegives the size: you expect 8 rows for 8 samples.value_counts()counts how often each distinct value appears in thedexcolumn.dropna=Falsetells it to count missing values too; by default they would be left out of the list, and you would not see the gap.unique()lists each distinct value ofcellonce, in the order first seen, and.tolist()turns that into a plain list so the quotes around each value are printed.duplicated()marks each row True if it is an exact copy of an earlier row, and.sum()counts the Trues.
Reading the output
(9, 3): one row more than the 8 samples in the study, the first sign of a problem. The counts list five different spellings where there should be two, and they add up to 9: 4 + 2 + 1 + 1 + 1. The last line under them describes the result itself: it is named count, and its dtype (data type) is int64, whole numbers stored in 64 bits. The donor list shows five values for four donors; because tolist() prints quotes, you can see the space inside ' N052611'. To Python, a space is a character like any other, so ' N052611' and 'N052611' are different donors. The final 1 is the number of duplicated rows.
Four problems in nine rows: inconsistent labels (Trt, untreated), a leading space in " N052611", one duplicated row, and one missing treatment.
Each of these would give a wrong answer later without any error message. The extra spellings would create four treatment groups instead of two; the space would split one donor into two, breaking the pairs; and the duplicate would count one sample twice, as if it were an extra replicate.
Why these four checks, rather than reading the table by eye? Nine rows can be read by eye, but a real sample sheet may have hundreds, and a stray space is invisible in a printed table. value_counts and unique summarise a column so that every distinct value appears once, however many rows there are, which makes odd spellings stand out. Two more lines are worth adding to the list. messy.isna().sum(), from NumPy, pandas and files, counts gaps in every column at once. messy[messy.duplicated(keep=False)] shows every copy of a duplicated row, not just the later ones, so you can see which sample was entered twice.
Remember that duplicated() only finds exact copies. Two rows for the same sample that differ by a capital letter or a trailing space are not counted, which is one reason the fixes below tidy the text before removing duplicates.
Broman & Woo (2018), "Data organization in spreadsheets", The American Statistician 72(1), gives practical rules for typing sample sheets so that they need little cleaning: be consistent with codes and spellings, put one value in each cell, leave no cells empty, avoid spaces in identifiers and keep notes in a separate file. Spreadsheets cause their own errors too. Ziemann, Eren & El-Osta (2016, Genome Biology) found that about a fifth of papers with Excel gene lists in their supplementary files contained gene names that the software had silently turned into dates, such as SEPT2 becoming 2-Sep. That study covered a selection of journals and years, so read the fraction as a sign of how common the problem was, not as a fixed rate.
Fix them
The .str methods work on every value in a text column at once. Chaining steps inside round brackets keeps the cleaning readable.
Chaining means writing several steps one after another, each starting with a dot and working on the result of the step before. The outer round brackets let the chain run over several lines, one step per line, so it reads like a list of instructions.
clean = (
messy
.assign(cell=messy["cell"].str.strip(),
dex=messy["dex"].str.strip().str.lower().replace({"untreated": "untrt"}))
.drop_duplicates()
.reset_index(drop=True)
)
# SRR1039517 is the treated sample from donor N080611 (checked in the study record)
clean.loc[clean["sample"] == "SRR1039517", "dex"] = "trt"
print(pd.crosstab(clean["cell"], clean["dex"]))
print(clean.equals(pd.read_csv("airway_samples.csv")))What this does
.assign(cell=…, dex=…)returns a copy of the table with those two columns replaced by cleaned versions. The originalmessytable is not changed, so you can always compare before and after.str.strip()removes spaces at the start and end of each value, which turns ' N052611' into 'N052611'.- For
dex, the value is stripped, thenstr.lower()makes it lower case, so Trt becomes trt, and thenreplace({"untreated": "untrt"})swaps the long spelling for the standard one. The missing value stays missing through all three steps. .drop_duplicates()removes any row that is an exact copy of an earlier one. It comes after the text fixes, so rows that differed only by a stray space or a capital letter are recognised as the same row..reset_index(drop=True)renumbers the rows 0 to 7. Without it, the index would keep a gap where the duplicate was removed;drop=Truethrows the old numbers away instead of keeping them as a new column.clean.loc[row condition, "dex"] = "trt"selects the rows wheresampleis SRR1039517 and thedexcolumn, and stores trt there. The comment above it records where the value came from.- The last line compares the result with the correct sheet from NumPy, pandas and files.
equals()returns True only if both tables have the same values in the same places, including the same row labels.
Reading the output
The crosstab is now identical to NumPy, pandas and files: four donors, each with one treated and one untreated sample. True means the cleaned table matches the reference sheet cell for cell. In real work you rarely have a perfect reference to compare against, which is why the crosstab, a check of the design itself, is the test you will use most often.
The cleaned table matches the reference sheet exactly. The missing value was filled from a record, not a guess; if you cannot confirm a value, leave it missing and say so.
Here, the study record says which sample was treated. Guessing would have been tempting, because every donor needs one of each and only one slot was empty, but a guess built on an assumption about the design would hide the very error a check should catch. The comment next to the fix means anyone reading the code can see the source and check it.
One detail in the chain is easy to mix up. .replace({"untreated": "untrt"}), without .str, swaps whole values: a cell must be exactly untreated to change. .str.replace("untreated", "untrt"), with .str, swaps text inside each value, so it would also change a longer label that merely contains the word. For fixing labels, replacing whole values is usually what you want, because it cannot alter anything you did not intend.
When the table is clean, save it as a new file, for example clean.to_csv("airway_samples_clean.csv", index=False). The index=False part stops pandas writing the row numbers as an extra, unnamed first column, which would otherwise reappear every time the file is read back in.
Combining tables
merge() joins two tables on a shared column, like a lookup. The sequencing depths below are made up for the exercise, and cover only three samples.
The shared column is called the key. For each row of the first table, pandas looks up the row in the second table with the same key value and copies its other columns across. It works like a lookup function in a spreadsheet, but for every row at once.
depth = pd.DataFrame({"sample": ["SRR1039508", "SRR1039509", "SRR1039512"],
"reads_m": [20.6, 18.8, 25.3]})
merged = clean.merge(depth, on="sample", how="left")
print(merged.head(4))
print(merged["reads_m"].isna().sum())What this does
pd.DataFrame({…})builds a small table from a dictionary: each key becomes a column name and each list becomes that column's values.reads_mis the number of reads in millions.clean.merge(depth, on="sample", how="left")joinsdepthontoclean, matching rows by thesamplecolumn.how="left"says which rows to keep: all rows of the table on the left, hereclean.merged.head(4)shows the first four rows;isna().sum()counts missing depths.
Reading the output
The merged table has the three columns of clean plus reads_m. The first three samples have depths; SRR1039513 has none in depth, so it gets NaN. The last line, 5, is the number of samples without a depth: 8 samples minus 3 matches.
sample and reads_m columns are drawn. Schematic, not to scale.A left join keeps every sample from the first table and fills NaN where the second table has no match. Counting the gaps afterwards, here 5, is the quality check.
Other values of how keep different rows. "inner" keeps only the samples found in both tables, here 3, and silently drops the other five. "outer" keeps every key from either table. The usual mistake is a key that appears more than once in the second table: each match makes its own row, so a sample listed twice in depth would appear twice in the result. Comparing the number of rows before and after a merge catches this at once; here, the left merge should still give 8.
pandas can make that check for you. Adding validate="one_to_one" to merge() stops with an error if the key is repeated in either table, instead of quietly multiplying rows. Adding indicator=True adds a column called _merge that says, for each row, whether its key was found in both tables or only in the left one, which shows exactly which samples are missing a depth.
A quieter pitfall is a key that looks the same but is not. If one table holds the sample IDs as text and another holds them with a trailing space, or in a different case, no rows match, and a left merge returns NaN for every sample without any error. That is why the cleaning steps above, stripping spaces and standardising case, come before any merge: a key must be clean in both tables before it can join them.
Go deeper: let the merge check itself
merge() has two options that turn this section's checks into code. indicator=True adds a column called _merge that says, for each row, where its key was found: both, left_only or right_only. Here, merged["_merge"].value_counts() would report 3 rows found in both tables and 5 found only in clean: the same numbers as the NaN count, but with a label you can filter on.
validate="one_to_one" makes pandas stop with a MergeError if a key appears more than once in either table, so a sample listed twice is caught before it quietly adds a row. validate="many_to_one" allows repeated keys on the left only, which suits joining many measurements to one row per sample. Writing clean.merge(depth, on="sample", how="left", validate="one_to_one", indicator=True) records in a single line what you expect the join to do, and fails loudly if it does not.
Key point
Never edit raw files by hand. Every fix lives in code, with a comment saying why, so cleaning can be checked and repeated.
Imagine the lab sends a corrected sheet next month, with one new sample added. If you had fixed the spellings by hand in a spreadsheet, you would have to remember and repeat every edit. With the fixes in code, you rerun the notebook, and the crosstab and the merge counts tell you at once whether the new sheet has new problems. Keep the raw file untouched, and save the cleaned table under a different name.
Further reading · Module 1The Python Tutorial: the official tutorial, for anything you want explained a second way. · All reading for this course
Check your understanding
1. What does .str.strip() do?
2. After a left merge, some rows have NaN . Why?
3. What does drop_duplicates() remove?
Up next
Lesson 5
Figures with matplotlib and seaborn