class: center, middle, inverse, title-slide .title[ # Reading (and writing) data in R ] .author[ ### Guilherme Dias ] --- exclude: true count: false <link href="https://fonts.googleapis.com/css?family=Roboto|Source+Sans+Pro:300,400,600|Ubuntu+Mono&subset=latin-ext" rel="stylesheet"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.3.1/css/all.css" integrity="sha384-mzrmE5qonljUremFsqc01SB46JvROS7bZs3IO2EmfFsd15uHvIt+Y8vEf7N7fWAU" crossorigin="anonymous"> <!-- ------------ Only edit title, subtitle & author above this ------------ --> --- name: spot # Spot the problems 🔍 A collaborator sends you `messy_samples.csv`: ``` # Exported from PlateReader v2.1 on 2026-03-14 sample;gene;ct;conc_ng_ul;treated S01;BRCA1;24,1;12,5;yes S02;2-Sep;25,3;n/a;no S03;TP53;Undetermined;10,2;yes S04;MYC;23,8;-;no ``` **How many problems can you find?** ??? Give the class a minute. Ask for shouts. --- name: spot_answers # Spot the problems 🔍 ``` # Exported from PlateReader v2.1 on 2026-03-14 sample;gene;ct;conc_ng_ul;treated S01;BRCA1;24,1;12,5;yes S02;2-Sep;25,3;n/a;no S03;TP53;Undetermined;10,2;yes S04;MYC;23,8;-;no ``` -- 1. A **comment line** above the header 2. Columns separated by **`;`**, not `,` 3. **Decimal commas**: `24,1` 4. Three ways to say "missing": `n/a`, `-`, `Undetermined` 5. **`yes`/`no`** instead of `TRUE`/`FALSE` 6. `2-Sep`: a gene name **eaten by Excel** (SEPT2) --- name: naive # Just read it? ``` r read.csv("data/slide_loading_data/messy_samples.csv") ``` ``` ## Error in read.table(file = file, header = header, sep = sep, quote = quote, : more columns than column names ``` -- R isn't being difficult. It's following the defaults: commas between columns, the first line is the header. **We need to tell R what the file looks like.** --- name: fixed # Tell R how ``` r samples <- read.csv2("data/slide_loading_data/messy_samples.csv", # ; and , comment.char = "#", # skip comments na.strings = c("n/a", "-", "Undetermined")) # missing samples$treated <- samples$treated == "yes" # TRUE/FALSE str(samples) ``` ``` ## 'data.frame': 4 obs. of 5 variables: ## $ sample : chr "S01" "S02" "S03" "S04" ## $ gene : chr "BRCA1" "2-Sep" "TP53" "MYC" ## $ ct : num 24.1 25.3 NA 23.8 ## $ conc_ng_ul: num 12.5 NA 10.2 NA ## $ treated : logi TRUE FALSE TRUE FALSE ``` -- 5 of 6 fixed. `2-Sep` can't be fixed by R: **fix it at the source**. --- name: today # Today - Where is my file? **Paths** - Reading text files: **`read.csv()`** and friends - **Check** what you read - **Excel** and other formats - **Writing** data - When reading **fails** --- name: paths # Where is my file? R looks for files in the **working directory**: ``` r getwd() # where am I? file.exists("data/samples.csv") # is the file there? ``` -- .pull-left-50[ **Do** - Use an RStudio **Project** - Keep data in a `data/` folder - Use **relative** paths: `"data/samples.csv"` ] .pull-right-50[ **Don't** - `setwd()` in your script - Absolute paths: `"C:/Users/me/Desktop/..."` They only work on your computer. ] <div style="clear: both;"></div> .footnote[Error `cannot open file ... No such file or directory`? Check `getwd()` and the spelling.] .footnote[https://tidyverse.org/blog/2017/12/workflow-vs-script/] --- name: read_family # Text files: pick the right reader | File looks like | Function | Separator | Decimal | |:----------------|:---------|:---------:|:-------:| | `24.1,12.5` | `read.csv()` | `,` | `.` | | `24,1;12,5` | `read.csv2()` | `;` | `,` | | `24.1⇥12.5` | `read.delim()` | tab | `.` | | anything else | `read.table(sep = ...)` | you choose | `dec = ...` | <br> All return a **data frame**. All are `read.table()` with different defaults. .footnote[Not sure? Open the file in a **text editor** (not Excel) and look.] --- name: arguments # The arguments you'll need | Argument | Fixes | Example | |:---------|:------|:--------| | `header` | is line 1 column names? | `header = TRUE` | | `sep` | column separator | `sep = "\t"` | | `dec` | decimal mark | `dec = ","` | | `na.strings` | how missing is written | `na.strings = c("", "NA", "n/a")` | | `skip` | lines to ignore at the top | `skip = 3` | | `comment.char` | comment lines | `comment.char = "#"` | <br> Everything else: `?read.table` --- name: check # Always check what you read ``` r dim(samples) # expected number of rows and columns? head(samples, 3) # does it look right? colSums(is.na(samples)) # where are the NAs? ``` ``` ## [1] 4 5 ## sample gene ct conc_ng_ul treated ## 1 S01 BRCA1 24.1 12.5 TRUE ## 2 S02 2-Sep 25.3 NA FALSE ## 3 S03 TP53 NA 10.2 TRUE ## sample gene ct conc_ng_ul treated ## 0 0 1 2 0 ``` .footnote[Also: `str()` (are numbers numbers?), `summary()` (any odd values?).] --- name: excel # Excel files ``` r library(readxl) excel_sheets("data/results.xlsx") # which sheets? results <- read_excel("data/results.xlsx", sheet = "qpcr", # sheet by name or number skip = 1, # skip a title row na = c("", "n/a")) ``` -- .content-box-yellow[ Excel silently changes gene names into dates (`SEPT2` → `2-Sep`, `MARCH1` → `1-Mar`). It happened so often that the genes were **renamed** (`SEPTIN2`, `MARCHF1`). Don't open gene lists in Excel. ] --- name: other_formats # Other formats | Data | Package | Function | |:-----|:--------|:---------| | big CSV/TSV, fast | `readr`, `data.table` | `read_csv()`, `fread()` | | Excel | `readxl` | `read_excel()` | | GTF, GFF, BED | `rtracklayer` | `import()` | | FASTA | `Biostrings` | `readDNAStringSet()` | | SPSS, Stata, SAS | `haven` | `read_sav()`, `read_dta()` | <br> Files on the web: give the **URL** instead of a path. ``` r read.csv("https://example.org/data/samples.csv") ``` --- name: writing # Writing data A table for colleagues, Excel or other programs: ``` r write.csv(samples, "results/samples_clean.csv", row.names = FALSE) write.table(samples, "results/samples_clean.tsv", sep = "\t", row.names = FALSE, quote = FALSE) ``` -- An R object, to read back into R later: ``` r saveRDS(samples, "results/samples_clean.rds") samples <- readRDS("results/samples_clean.rds") ``` `.rds` keeps everything: types, factor levels, attributes. A CSV keeps only text. --- name: broken # When reading fails ``` r read.table("data/slide_loading_data/broken_genes.tsv", header = TRUE) ``` ``` ## Error in scan(file = file, what = what, sep = sep, quote = quote, dec = dec, : line 2 did not have 4 elements ``` -- Which line? Count the fields: ``` r count.fields("data/slide_loading_data/broken_genes.tsv", sep = "\t") readLines("data/slide_loading_data/broken_genes.tsv")[3] ``` ``` ## [1] 4 4 3 4 ## [1] "TP53\t17\t7668402" ``` TP53 has no `end`. Fix the file, or use `fill = TRUE` to get an `NA`. .footnote[Careful: `read.csv()` and `read.delim()` use `fill = TRUE` by default, so short rows are padded **silently**.] --- name: recap # Recap | Step | How | |:-----|:----| | 1. Look at the file | text editor, `readLines(file, n = 5)` | | 2. Pick the reader | `read.csv()`, `read.csv2()`, `read.delim()`, `read_excel()` | | 3. Describe the file | `sep`, `dec`, `na.strings`, `skip`, `comment.char` | | 4. Check | `dim()`, `str()`, `head()`, `colSums(is.na())` | | 5. Save | `write.csv(row.names = FALSE)`, `saveRDS()` | <br> **Remember:** R reads exactly what you tell it to. Look first, then read, then check. <!-- --------------------- Do not edit this and below --------------------- --> --- name: end_slide class: end-slide, middle count: false # See you at the next lecture! .end-text[ <p class="smaller"> <span class="small" style="line-height: 1.2;">Graphics from </span><img src="./assets/freepik.jpg" style="max-height:20px; vertical-align:middle;"><br> Created: 12-Nov-2026 • <a href="https://www.scilifelab.se/">SciLifeLab</a> • <a href="https://nbis.se/">NBIS</a> </p> ]