So far, we’ve typed our data into R. Real data comes in files: from instruments, collaborators, databases. In this lab you will:
read.table(), read.csv(), read.csv2() and read.delim(),As always: predict first, then run. If the output surprises you, find out why. Click the key to reveal example code. Keep your code and notes in your R Markdown learning journal.
r-course Project. Where is R looking for files? Create a folder called data inside it.
> No r-course Project yet? Create one: File → New Project → New Directory → New Project, name it r-course.getwd()
dir.create("data")
data/:base <- "https://raw.githubusercontent.com/NBISweden/workshop-r/master/data/lab_loadingdata/"
files <- c("normalized.txt", "example.data", "cell_counts_eu.csv",
"qpcr_export.txt", "primers_broken.tsv", "rna_qc.xlsx")
download.file(paste0(base, files), destfile = paste0("data/", files),
method = "libcurl", mode = "wb")
Check that all six files arrived:
file.exists(paste0("data/", files))
list.files("data")
## [1] TRUE TRUE TRUE TRUE TRUE TRUE
## [1] "cell_counts_eu.csv" "example.data" "normalized.txt"
## [4] "primers_broken.tsv" "qpcr_export.txt" "rna_qc.xlsx"
From now on, read every file with a relative path like "data/qpcr_export.txt".
normalized.txt holds normalised expression values for Arabidopsis thaliana genes in a bzip29 mutant and wild type (three replicates each). Look at the first three lines with readLines(). What separates the columns? Then read it with read.table(), using the defaults.
readLines("data/normalized.txt", n = 3) # \t means a tab
expr <- read.table("data/normalized.txt")
dim(expr)
head(expr, 3)
## [1] "bZIP29_1\tbZIP29_2\tbZIP29_3\tWT_1\tWT_2\tWT_3"
## [2] "AT1G01020.1\t13.5727390788326\t14.1671426761817\t12.9727029171751\t14.8181737869517\t15.9040169362625\t11.3270622675928"
## [3] "AT1G01030.1\t1.41723379939617\t1.20145379585993\t1.38543429212549\t0.85902456735952\t1.096828754225\t0.859643118522669"
## [1] 18945 6
## bZIP29_1 bZIP29_2 bZIP29_3 WT_1 WT_2 WT_3
## AT1G01020.1 13.572739 14.167143 12.972703 14.8181738 15.904017 11.3270623
## AT1G01030.1 1.417234 1.201454 1.385434 0.8590246 1.096829 0.8596431
## AT1G01040.2 41.862906 41.199853 42.696566 37.5286358 34.849241 48.6456871
read.table() do with the first column?
rownames(expr)[1:5] # the gene IDs became row names
## [1] "AT1G01020.1" "AT1G01030.1" "AT1G01040.2" "AT1G01050.1" "AT1G01060.1"
WT_1? Is it really a gene? Look at the last rows of the table, remove the rows that aren’t genes, and try again.
rownames(expr)[which.max(expr$WT_1)] # "no_feature" is not a gene!
tail(expr, 3) # summary rows from the read-counting software
expr <- expr[rownames(expr) != "no_feature" & rownames(expr) != "ambiguous", ]
rownames(expr)[which.max(expr$WT_1)]
## [1] "no_feature"
## bZIP29_1 bZIP29_2 bZIP29_3 WT_1 WT_2
## ATMG01360.1 1.362725 2.753332 1.133537 1.342226 10.96829
## no_feature 76805.514015 80221.720737 78090.444696 77965.069734 91087.39029
## ambiguous 8868.667554 10047.507792 9676.754736 9702.950934 9569.13290
## WT_3
## ATMG01360.1 6.573741e-01
## no_feature 7.806303e+04
## ambiguous 9.623300e+03
## [1] "AT5G60390.1"
example.data has the same data, but with : between columns, a header line, and some missing values. Read it. How many values are missing in each column? How many genes have no missing values at all?
ex <- read.table("data/example.data", sep = ":", header = TRUE)
colSums(is.na(ex))
nrow(na.omit(ex))
## bZIP29_1 bZIP29_2 bZIP29_3 WT_1 WT_2 WT_3
## 2784 2776 2835 2909 2792 2818
## [1] 7178
cell_counts_eu.csv comes from an instrument set to European settings. Try to read it with read.csv(). What happens? Look at the file with readLines(), then read it correctly.
read.csv("data/cell_counts_eu.csv") # error
## Error in read.table(file = file, header = header, sep = sep, quote = quote, : more columns than column names
readLines("data/cell_counts_eu.csv", n = 3) # ; between columns, , as decimal mark
growth <- read.csv2("data/cell_counts_eu.csv")
str(growth)
## [1] "culture;timepoint_h;od600;viability_pct;medium"
## [2] "C1;0;0,051;98,2;LB"
## [3] "C2;0;0,048;97,5;M9"
## 'data.frame': 8 obs. of 5 variables:
## $ culture : chr "C1" "C2" "C1" "C2" ...
## $ timepoint_h : int 0 0 4 4 8 8 12 12
## $ od600 : num 0.051 0.048 0.312 0.187 1.124 ...
## $ viability_pct: num 98.2 97.5 96.8 97.1 93.4 95.9 88.1 92.3
## $ medium : chr "LB" "M9" "LB" "M9" ...
growth: what was the OD600 of each culture after 12 hours? In which medium did the bacteria grow faster?
growth[growth$timepoint_h == 12, ] # faster growth in LB
## culture timepoint_h od600 viability_pct medium
## 7 C1 12 1.987 88.1 LB
## 8 C2 12 1.105 92.3 M9
qpcr_export.txt was exported from a qPCR machine. Look at the first five lines. Then read it, so that:
Undetermined and empty cells become NA,ct is a number.
readLines("data/qpcr_export.txt", n = 5)
qpcr <- read.delim("data/qpcr_export.txt", skip = 3,
na.strings = c("Undetermined", ""))
str(qpcr)
## [1] "# Instrument: QuantStudio 5" "# Run date: 2026-05-02"
## [3] "# Operator: AL" "well\tsample\tgene\tct"
## [5] "A1\tctrl_1\tGAPDH\t18.2"
## 'data.frame': 12 obs. of 4 variables:
## $ well : chr "A1" "A2" "A3" "A4" ...
## $ sample: chr "ctrl_1" "ctrl_2" "trt_1" "trt_2" ...
## $ gene : chr "GAPDH" "GAPDH" "GAPDH" "GAPDH" ...
## $ ct : num 18.2 18.5 18.1 18.4 29.8 30.4 25.1 24.7 NA 33.9 ...
comment.char = "#" instead of skip = 3 works too. Why?
ctrl_1, ctrl_2) and in treated samples?
sum(is.na(qpcr$ct))
mean(qpcr$ct[qpcr$gene == "IL6" & qpcr$sample %in% c("ctrl_1", "ctrl_2")])
mean(qpcr$ct[qpcr$gene == "IL6" & qpcr$sample %in% c("trt_1", "trt_2")])
## [1] 2
## [1] 30.1
## [1] 24.9
%in% checks whether each value is one of several options. Without it, write (qpcr$sample == "ctrl_1" | qpcr$sample == "ctrl_2").
primers_broken.tsv with read.delim(). No error. But look at the result carefully: is everything in the right column?
primers <- read.delim("data/primers_broken.tsv")
primers # the primer IDs became row names and every column is shifted!
## primer target sequence tm
## P1 GAPDH GAAGGTGAAGGTCGGAGTC 59.2 NA
## P2 IL6 ACTCACCTCTTCAGAACGAATTG 58.7 NA
## P3 TNF CCTCTCTCTAATCAGCCCTCTG NA 60.1
## P4 ACTB CATGTACGTTGCTATCCAGGC 59.8 NA
## P5 IL1B ATGATGGCTTATTACAGTGGCAA 57.9 NA
read.table(header = TRUE, sep = "\t"). Use count.fields() to find the line that causes the problem, and readLines() to look at it. What is wrong?
read.table("data/primers_broken.tsv", header = TRUE, sep = "\t") # the line number in the error is misleading
## Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : line 1 did not have 5 elements
count.fields("data/primers_broken.tsv", sep = "\t") # line 4 has 5 fields, not 4
readLines("data/primers_broken.tsv")[4] # two tabs after the sequence
## [1] 4 4 4 5 4 4
## [1] "P3\tTNF\tCCTCTCTCTAATCAGCCCTCTG\t\t60.1"
text argument.
lines <- readLines("data/primers_broken.tsv")
lines[4] <- sub("\t\t", "\t", lines[4])
primers <- read.delim(text = lines)
primers
## primer target sequence tm
## 1 P1 GAPDH GAAGGTGAAGGTCGGAGTC 59.2
## 2 P2 IL6 ACTCACCTCTTCAGAACGAATTG 58.7
## 3 P3 TNF CCTCTCTCTAATCAGCCCTCTG 60.1
## 4 P4 ACTB CATGTACGTTGCTATCCAGGC 59.8
## 5 P5 IL1B ATGATGGCTTATTACAGTGGCAA 57.9
Write in your journal: why is the silent result from exercise 11 more dangerous than the error in exercise 12?
Install readxl if you don’t have it: install.packages("readxl").
rna_qc.xlsx holds RNA quality results. Which sheets does it have? Read the README sheet, then the samples sheet. The samples sheet has a title row above the column names, and missing concentrations are written n/a.
library(readxl)
excel_sheets("data/rna_qc.xlsx")
read_excel("data/rna_qc.xlsx", sheet = "README", col_names = FALSE)
rna <- read_excel("data/rna_qc.xlsx", sheet = "samples", skip = 1, na = "n/a")
rna
## [1] "README" "samples"
## # A tibble: 4 × 1
## ...1
## <chr>
## 1 RNA extraction, mouse liver
## 2 Extracted by: AL
## 3 Date: 2026-04-20
## 4 See sheet 'samples' for the data
## # A tibble: 6 × 5
## sample gene_of_interest rin conc_ng_ul strain
## <chr> <chr> <dbl> <dbl> <chr>
## 1 L01 MARCHF1 8.9 152. WT
## 2 L02 46267 9.1 NA WT
## 3 L03 MARCHF1 7.4 98.6 KO
## 4 L04 <NA> 8.2 120. KO
## 5 L05 SEPTIN2 6.8 NA KO
## 6 L06 MARCHF1 9.3 175 WT
gene_of_interest. One value is a number. Someone typed the old gene symbol SEPT2, and Excel turned it into a date, which R reads as Excel’s internal date number. Replace it with the current symbol, SEPTIN2.
rna$gene_of_interest[rna$gene_of_interest == "46267"] <- "SEPTIN2"
rna$gene_of_interest
## [1] "MARCHF1" "SEPTIN2" "MARCHF1" NA "SEPTIN2" "MARCHF1"
rin) below 7? What is the mean RIN in each strain?
rna$sample[rna$rin < 7]
mean(rna$rin[rna$strain == "WT"])
mean(rna$rin[rna$strain == "KO"])
## [1] "L05"
## [1] 9.1
## [1] 7.466667
results. Write qpcr to results/qpcr_clean.csv without row names. Read it back. Is it identical to qpcr?
dir.create("results", showWarnings = FALSE)
write.csv(qpcr, "results/qpcr_clean.csv", row.names = FALSE)
qpcr2 <- read.csv("results/qpcr_clean.csv")
all.equal(qpcr, qpcr2)
## [1] TRUE
What happens if you leave out row.names = FALSE? Read the file back and compare.
write.csv(qpcr, "results/qpcr_rownames.csv")
head(read.csv("results/qpcr_rownames.csv"), 3) # an extra column "X" with the row numbers
## X well sample gene ct
## 1 1 A1 ctrl_1 GAPDH 18.2
## 2 2 A2 ctrl_2 GAPDH 18.5
## 3 3 A3 trt_1 GAPDH 18.1
gene a factor with the levels in the order "TNF", "IL6", "GAPDH". Save qpcr both as a CSV and as an .rds file, read both back, and compare the levels of gene. What do you notice?
qpcr$gene <- factor(qpcr$gene, levels = c("TNF", "IL6", "GAPDH"))
write.csv(qpcr, "results/qpcr_factor.csv", row.names = FALSE)
saveRDS(qpcr, "results/qpcr_factor.rds")
class(read.csv("results/qpcr_factor.csv")$gene) # back to character
levels(readRDS("results/qpcr_factor.rds")$gene) # factor, levels in your order
## [1] "character"
## [1] "TNF" "IL6" "GAPDH"
A GTF file describes where the genes, transcripts and exons of a genome are. It is a tab-separated text file with nine columns and no header:
| # | Column | Content |
|---|---|---|
| 1 | seqname | chromosome |
| 2 | source | where the annotation comes from |
| 3 | feature | gene, transcript, exon, CDS, … |
| 4 | start | start position (1-based) |
| 5 | end | end position |
| 6 | score | a number, or . |
| 7 | strand | + or - |
| 8 | frame | 0, 1, 2, or . |
| 9 | attribute | gene_id "..."; gene_name "..."; and more |
data/. You don’t need to unzip it: R can read .gz files directly.download.file("https://ftp.ensembl.org/pub/release-86/gtf/drosophila_melanogaster/Drosophila_melanogaster.BDGP6.86.gtf.gz",
"data/Drosophila_melanogaster.BDGP6.86.gtf.gz", mode = "wb")
read.delim(). It has no header, its first lines are comments starting with #, and the attribute column contains quotes, so add quote = "". Give the columns meaningful names, then check the dimensions and structure.
gtf <- read.delim("data/Drosophila_melanogaster.BDGP6.86.gtf.gz",
header = FALSE, comment.char = "#", quote = "")
colnames(gtf) <- c("chromosome", "source", "feature", "start", "end",
"score", "strand", "frame", "attribute")
dim(gtf)
str(gtf)
## [1] 538684 9
## 'data.frame': 538684 obs. of 9 variables:
## $ chromosome: chr "3R" "3R" "3R" "3R" ...
## $ source : chr "FlyBase" "FlyBase" "FlyBase" "FlyBase" ...
## $ feature : chr "gene" "transcript" "exon" "gene" ...
## $ start : int 722370 722370 722370 835381 835381 835381 835381 835381 869486 869486 ...
## $ end : int 722621 722621 722621 2503907 2503907 835491 835491 835383 869548 869548 ...
## $ score : chr "." "." "." "." ...
## $ strand : chr "-" "-" "-" "+" ...
## $ frame : chr "." "." "." "." ...
## $ attribute : chr "gene_id \"FBgn0085804\"; gene_name \"CR41571\"; gene_source \"FlyBase\"; gene_biotype \"pseudogene\";" "gene_id \"FBgn0085804\"; transcript_id \"FBtr0114258\"; gene_name \"CR41571\"; gene_source \"FlyBase\"; gene_bi"| __truncated__ "gene_id \"FBgn0085804\"; transcript_id \"FBtr0114258\"; exon_number \"1\"; gene_name \"CR41571\"; gene_source \"| __truncated__ "gene_id \"FBgn0267431\"; gene_name \"CG45784\"; gene_source \"FlyBase\"; gene_biotype \"protein_coding\";" ...
table(gtf$chromosome)
table(gtf$feature)
##
## 211000022278158 211000022278279 211000022278282
## 3 3 3
## 211000022278298 211000022278307 211000022278309
## 3 3 3
## 211000022278436 211000022278449 211000022278498
## 3 4 3
## 211000022278522 211000022278603 211000022278604
## 3 3 3
## 211000022278664 211000022278724 211000022278750
## 3 3 3
## 211000022278760 211000022278875 211000022278877
## 7 3 3
## 211000022278878 211000022278879 211000022278880
## 3 3 3
## 211000022278985 211000022279055 211000022279108
## 3 3 3
## 211000022279132 211000022279134 211000022279165
## 3 3 3
## 211000022279188 211000022279222 211000022279264
## 12 3 3
## 211000022279342 211000022279392 211000022279446
## 3 3 3
## 211000022279528 211000022279529 211000022279531
## 3 3 3
## 211000022279555 211000022279681 211000022279708
## 3 3 3
## 211000022280133 211000022280328 211000022280341
## 3 14 3
## 211000022280347 211000022280481 211000022280494
## 3 3 6
## 211000022280645 211000022280703 2L
## 3 3 96740
## 2R 3L 3R
## 110108 100745 125133
## 4 dmel_mitochondrion_genome rDNA
## 7844 146 59
## Unmapped_Scaffold_8 X Y
## 43 97193 504
##
## CDS exon five_prime_utr gene Selenocysteine
## 160188 186414 46060 17559 4
## start_codon stop_codon three_prime_utr transcript
## 30389 30296 33034 34740
+ strand?
genes <- gtf[gtf$feature == "gene", ]
gene_length <- genes$end - genes$start + 1
mean(gene_length)
median(gene_length)
mean(genes$strand == "+")
## [1] 5754.282
## [1] 1710
## [1] 0.5016231
2L, 2R, 3L, 3R, 4, X)? What is the median exon length on 3R?
exons <- gtf[gtf$feature == "exon", ]
table(exons$chromosome)[c("2L", "2R", "3L", "3R", "4", "X")]
exons_3R <- exons[exons$chromosome == "3R", ]
median(exons_3R$end - exons_3R$start + 1)
##
## 2L 2R 3L 3R 4 X
## 32747 38551 34347 43159 3165 34136
## [1] 252
| Task | Function | Key arguments |
|---|---|---|
| Where am I? | getwd(), list.files(), file.exists() |
|
| Look at a file | readLines() |
n |
| Comma-separated | read.csv() |
na.strings, skip, comment.char |
| Semicolon, decimal comma | read.csv2() |
|
| Tab-separated | read.delim() |
|
| Anything else | read.table() |
sep, dec, header, quote, fill |
| Find broken lines | count.fields() |
sep |
| Excel | readxl::read_excel(), excel_sheets() |
sheet, skip, na |
| Write a table | write.csv(), write.table() |
row.names = FALSE, sep, quote |
| Save an R object | saveRDS(), readRDS() |
|
| Check | dim(), str(), head(), tail(), colSums(is.na()) |