Lesson 4 of 5
Module 1 · R basics
Real sample sheets are typed by people, and people make mistakes. This lesson finds and fixes them in a deliberately messy copy of the airway sheet.
Find the problems
Load airway_samples_messy.csv. Telling R that empty cells are missing (na.strings) makes them visible.
By default, read.csv() reads an empty cell in a text column as an empty piece of text, "", which looks like a value but contains nothing. It would not be counted as missing, and it would quietly become a third "treatment" in any table. Listing "" in na.strings tells R to treat empty cells as NA, so they show up in counts and checks.
In Data frames and files you read the clean sample sheet and checked it with str() and table(). This lesson starts from the version most people actually receive: the same eight samples, typed by hand, with the kinds of slips that creep into any spreadsheet. The approach is always the same. First look, and write down every problem you find; only then change anything. Fixing as you go makes it easy to miss a second problem hidden behind the first.
library(dplyr)
messy <- read.csv("airway_samples_messy.csv", na.strings = c("", "NA"))
nrow(messy)
table(messy$dex, useNA = "ifany")
unique(messy$cell)
sum(duplicated(messy))What this does, line by line
library(dplyr)loads the dplyr package installed in Setting up R and RStudio.read.csv(…, na.strings = c("", "NA"))reads the messy file and treats both empty cells and the text NA as missing.nrow(messy)counts the rows. The study has eight samples, so this is the first check.table(messy$dex, useNA = "ifany")counts each treatment label.useNA = "ifany"adds a count of missing values if there are any; by defaulttable()leaves them out, which would hide the problem.unique(messy$cell)lists each distinct donor label once.duplicated(messy)gives TRUE for every row that is identical to an earlier row, andsum()counts those TRUEs.
Reading the output. There are 9 rows, one more than the 8 samples in the study. The treatment table has five columns, where there should be two: four spellings of the two treatments and one <NA>, a missing value. The counts add up to 9 (1 + 2 + 1 + 4 + 1). The donor list has five entries where there should be four; look closely and one starts with a space. The last line says one row is an exact copy of another.
Each check answers a question you can state in advance. How many samples should there be? Which treatment labels are allowed? How many donors took part? Writing the expected answer next to the check, even as a comment, turns a vague look at the data into a test that either passes or fails. unique() is especially useful on short label columns, because near-duplicates such as "N052611" and " N052611" sit side by side in its output. Wrapping it in sort() puts similar spellings next to each other in a longer list.
Your table may list trt before Trt. The order of upper- and lower-case labels depends on your computer's language settings; the counts are the same.
Four problems in nine rows, all common in real data:
- Inconsistent labels:
Trt,trt,untreatedanduntrtmean only two things. - Stray whitespace:
" N052611"has a leading space, so R treats it as a different donor. - A duplicate row: one sample appears twice.
- A missing value: one sample has no treatment recorded.
Each has an ordinary cause. Different people, or the same person on different days, type the same category in different ways. A leading space often comes from copying a cell out of an email or a document. Duplicates appear when two sheets are pasted together and a row is in both. Missing values appear when a field is skipped. None of these would stop R from running; every one would change a result. Two "donors" called N052611 and " N052611" would each have only one sample, and a paired analysis would quietly lose that donor.
The problems also interact. duplicated() only flags rows that are identical character for character, so a copy of a row that differed only by a space or a capital letter would not be counted. That is why the cleaning below standardises the labels first and removes duplicates last.
Fix them with dplyr
mutate() changes columns, distinct() removes duplicate rows. The pipe |> passes the result of each step to the next; read it as “then”.
So messy |> mutate(…) |> distinct() reads as "take messy, then change some columns, then remove duplicate rows". Without the pipe you would have to nest the calls inside each other, as distinct(mutate(messy, …)), which reads inside out. The pipe |> has been part of R itself since version 4.1. You will also see an older pipe, %>%, in many scripts and help pages; for everyday use it does the same job.
mutate() and distinct() are two of a small set of dplyr functions, often called verbs, that each do one job on a table and return a new table. The others you will use most are filter(), which keeps rows that pass a test; select(), which keeps columns by name; arrange(), which sorts rows; and summarise(), which reduces many rows to one summary row, usually after group_by() has split the table into groups. Inside these verbs you write column names without samples$ in front, because dplyr knows which table you mean.
clean <- messy |>
mutate(cell = trimws(cell),
dex = tolower(trimws(dex)),
dex = recode(dex, untreated = "untrt")) |>
distinct()
# SRR1039517 is the treated sample from donor N080611 (checked in the study record)
clean$dex[clean$sample == "SRR1039517"] <- "trt"
table(clean$cell, clean$dex, useNA = "ifany")
all.equal(clean, read.csv("airway_samples.csv"))What this does, line by line
clean <- messy |>starts a pipeline whose final result will be stored asclean. The raw objectmessyis not changed.cell = trimws(cell)removes spaces from the start and end of every donor label, so " N052611" becomes "N052611".dex = tolower(trimws(dex))does the same to the treatment column and then turns it into lower case, so Trt becomes trt.dex = recode(dex, untreated = "untrt")replaces the value untreated with untrt. Inside onemutate(), each line sees the result of the lines before it, so this works on the already trimmed, lower-case column.distinct()keeps only the first copy of any repeated row, leaving 8.- The comment records where the missing value came from. The next line uses logical indexing from Vectors, functions and loops:
clean$sample == "SRR1039517"picks out that sample's row, and<- "trt"fills in its treatment. table(…, useNA = "ifany")repeats the design check from Data frames and files.all.equal(clean, read.csv("airway_samples.csv"))compares the cleaned table with the correct reference sheet.
Reading the output. The table again shows four donors, each with one treated and one untreated sample, and there is no <NA> column, because useNA = "ifany" only adds one when something is missing. [1] TRUE from all.equal() means the two tables have the same columns, the same types and the same values in the same order. In real work you will rarely have a correct reference to compare with; the design table is then your main check.
With a clean table, the other verbs read almost as sentences. clean |> filter(dex == "trt") |> select(sample, cell) lists the four treated samples and their donors. count(clean, dex) counts the rows in each treatment group, and should give 4 and 4. Both return new tables and leave clean unchanged.
That last point is a common source of confusion. A pipeline that is not assigned with <- prints its result to the Console and then the result is gone. If you run clean |> distinct() on its own and then use clean, nothing has been removed. Either store the result under a new name or deliberately overwrite the old one, and prefer a new name, as this lesson does with messy and clean, so that the raw version stays available for checking.
The cleaned table now matches the reference sheet exactly. Notice that the missing value was filled from a record, not a guess. If you cannot confirm a value, leave it missing and say so.
Filling a missing label with the most common value, or guessing from the pattern, may look harmless, but it puts an invented data point into your analysis. If the guess is wrong, a treated sample is analysed as untreated and the comparison is weakened or reversed. A sample with an unknown label can be left out of the treatment comparison and reported in the methods; a sample with a wrong label cannot be detected later.
Go deeper: joining a second table with left_join()
Sample information often arrives in more than one file: the sample sheet here, and perhaps a second table with each donor's age and sex. left_join() from dplyr combines them by a shared column, called the key. For a hypothetical table called donors, with one row per donor, left_join(clean, donors, by = "cell") keeps every row of clean and adds the matching donor columns.
Two checks matter. If a donor label is missing or spelt differently in donors, its new columns are filled with NA, which is one more reason to trim whitespace first. If a donor appears twice in donors, that donor's samples are duplicated and the row count grows. Compare nrow() before and after, or add relationship = "many-to-one" (dplyr 1.1.0 or later), which makes dplyr stop with an error when a row matches more than one row of the second table.
Long and wide tables with tidyr
Tables usually come wide, one column per sample. ggplot2 and many tools want them long, one row per measurement. pivot_longer() converts between them. The values below are made up for the example.
In a wide table, each row is one gene and the measurements for different samples sit side by side. In a long table, each row is one measurement, and extra columns say which gene and which sample it belongs to. The same numbers are present in both; only the arrangement changes. Long format is what ggplot2 needs, because it maps columns to what you see: one column for the sample, which can go on the x-axis or set the colour, and one column for the value.
library(tidyr)
wide <- data.frame(gene = c("GeneA", "GeneB"),
S1 = c(10, 200), S2 = c(12, 180), S3 = c(30, 90))
long <- pivot_longer(wide, cols = -gene, names_to = "sample", values_to = "count")
longWhat this does
library(tidyr)loads tidyr.data.frame(gene = …, S1 = …, S2 = …, S3 = …)builds a small wide table: two genes, three sample columns.cols = -genetellspivot_longer()which columns to stack: every column exceptgene. The minus sign means "not this one".names_to = "sample"names the new column that will hold the old column names (S1, S2, S3);values_to = "count"names the new column that will hold the numbers.
Reading the output. A tibble is the tidyverse's version of a data frame; it behaves the same but prints a short header. 6 × 3 means 6 rows and 3 columns: 2 genes × 3 samples = 6 measurements. The line under the column names gives each column's type: <chr> is text and <dbl> is a number with decimals allowed (a "double"). Every original value is still there: GeneA's 10, 12 and 30 now sit in three rows, each labelled with its sample. The reverse step, from long back to wide, is pivot_wider().
Long format also makes group summaries short. long |> group_by(sample) |> summarise(total = sum(count)) gives one row per sample with its total: 210 for S1, 192 for S2 and 120 for S3. In the wide table you would need a separate sum for every sample column. This is the same idea as the library sizes from colSums() in Data frames and files, written for a long table.
Neither shape is better in general; each suits different tools. ggplot2 and dplyr's grouped summaries want long tables, while the count matrices used for RNA-seq statistics in Module 4 are wide, with one column per sample. pivot_wider(long, names_from = sample, values_from = count) turns the long table back into the original wide one. A pitfall when widening is a repeated pair: if GeneA had two rows for S1, pivot_wider() would not know which value to put in the cell, and would warn you and return list-columns instead of numbers. That warning usually means a duplicate you have not yet found.
sample column, and the six counts move into one count column, GeneA's in navy and GeneB's in plum. No value is lost or changed. Schematic, not to scale.dplyr and tidyr belong to the tidyverse, a family of packages described in Wickham et al. 2019, "Welcome to the tidyverse" (Journal of Open Source Software). The long format that pivot_longer() produces is the tidy data layout of Wickham 2014 (Journal of Statistical Software). In a methods section, cite the packages themselves with citation("dplyr") and citation("tidyr"), and give their versions from packageVersion(). Versions matter because functions evolve: the dplyr documentation now marks recode() as superseded, meaning it still works but newer functions are preferred. A script that records its versions can still be understood when the defaults change.
Key point
Never edit the raw file by hand. Write every fix in your script, with a comment saying why, so the cleaning can be checked and repeated.
Suppose a collaborator later sends a corrected sheet with a tenth sample. If your fixes live in a script, you run it again on the new file and every step is repeated in seconds. If you had fixed the old file by hand in a spreadsheet, you would have to remember and redo each change, and nobody could see what you changed or why.
Further reading · Module 1R for Data Science, 2nd edition: the free book that covers this module and much more. · All reading for this course
Check your understanding
1. Why did " N052611" appear as a separate donor?
2. What does distinct() do?
3. A treatment value is missing and you cannot confirm it. What should you do?
Up next
Lesson 5
Figures with ggplot2