1 Introduction

So far, we’ve typed our data into R. Real data comes in files: from instruments, collaborators, databases. In this lab you will:

  • organise your files and use relative paths,
  • read text files with read.table(), read.csv(), read.csv2() and read.delim(),
  • describe a file to R: separator, decimal mark, missing values, comment lines,
  • check what you’ve read,
  • find and fix problems in broken files,
  • read Excel files,
  • write data to files,
  • explore a real genome annotation.

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.

2 Get the files

  1. Open your 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")
  1. Download the files for this lab into 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".

3 Reading text files

  1. 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
  1. The first line has one name fewer than the other lines. What did 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"
  1. Which gene has the highest expression in 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"
  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
  1. 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" ...
  1. Using 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
  1. qpcr_export.txt was exported from a qPCR machine. Look at the first five lines. Then read it, so that:
    • the instrument information at the top is skipped,
    • 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?

  1. How many Ct values are missing? What is the mean Ct of IL6 in control samples (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").

4 Broken files

  1. Read 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
  1. Now read it with 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"
  1. Fix the problem. You can fix the file in a text editor, or fix the line in R and read the corrected lines with the 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?

5 Excel files

Install readxl if you don’t have it: install.packages("readxl").

  1. 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
  1. Look at 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"
  1. Which samples have an RNA integrity number (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

6 Writing data

  1. Create a folder called 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
  1. Make 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"

7 A real genome annotation

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
  1. Download the Drosophila melanogaster annotation from Ensembl into 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")
  1. Read the file with 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\";" ...
  1. Which chromosomes (and scaffolds) are in the file, and how many features does each have? Which feature types are there, and how many of each?

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
  1. Keep only the genes. What is their mean and median length? What fraction of genes is on the + 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
  1. How many exons are there on each of the main chromosome arms (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

8 Cheatsheet

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())