LSMA

LONDON SCHOOL OF MULTIOMICS & AI

Free course

Free course

0 of 35 lessons

Lesson 4 of 8

Module 1 · Python basics

Cleaning and combining data

Cleaning and combining data

Cleaning and combining data

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.

⏱ 11 min readModule 1 · Python basicsFree
Lesson at a glance3 steps · about 11 min
  1. Find the problems
  2. Fix them
  3. Combining tables

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.

Python · setup
import pandas as pd
Files for this lessonSave each file under the name shown here, next to your notebook, or upload it to Colab (folder icon › Upload). Your browser may give the download a different name; rename it before you run the code.
airway_samples_messy.csvThe same sheet with deliberate errors airway_samples.csvThe airway sample sheet: 8 samples, donor and treatment

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.

Python
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())
(9, 3) dex untrt 4 trt 2 Trt 1 NaN 1 untreated 1 Name: count, dtype: int64 ['N61311', ' N052611', 'N052611', 'N080611', 'N061011'] 1

What this does

  • messy.shape gives the size: you expect 8 rows for 8 samples.
  • value_counts() counts how often each distinct value appears in the dex column. dropna=False tells 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 of cell once, 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.

Python
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")))
dex trt untrt cell N052611 1 1 N061011 1 1 N080611 1 1 N61311 1 1 True

What this does

  • .assign(cell=…, dex=…) returns a copy of the table with those two columns replaced by cleaned versions. The original messy table 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, then str.lower() makes it lower case, so Trt becomes trt, and then replace({"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=True throws the old numbers away instead of keeping them as a new column.
  • clean.loc[row condition, "dex"] = "trt" selects the rows where sample is SRR1039517 and the dex column, 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.

Python
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())
sample cell dex reads_m 0 SRR1039508 N61311 untrt 20.6 1 SRR1039509 N61311 trt 18.8 2 SRR1039512 N052611 untrt 25.3 3 SRR1039513 N052611 trt NaN 5

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_m is the number of reads in millions.
  • clean.merge(depth, on="sample", how="left") joins depth onto clean, matching rows by the sample column. how="left" says which rows to keep: all rows of the table on the left, here clean.
  • 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.

Cleaning and combining data · LSMA free course The cleaned sample sheet has eight samples; the depth table has three. A left merge on the sample column keeps all eight samples, copies reads_m for the three that match, and fills NaN for the five that do not. 1 · CLEAN, 8 ROWS 2 · DEPTH, 3 ROWS 3 · LEFT MERGE sample sample reads_m sample reads_m SRR1039508 SRR1039508 20.6 SRR1039508 20.6 SRR1039509 SRR1039509 18.8 SRR1039509 18.8 SRR1039512 SRR1039512 25.3 SRR1039512 25.3 SRR1039513 SRR1039513 NaN NaN NaN NaN NaN + no row for SRR1039513 or the other four samples 5 NaN = 8 samples − 3 matches grey bars: the remaining four sample IDs
A left merge keeps every row of the left table. Where the key has no match on the right, the new column is filled with NaN. Only the 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

Ask BioTutor

Create a free website with Framer, the website builder loved by startups, designers and agencies.