Data wrangling

Reading and wrangling data with the {tidyverse}

Dr Massimiliano Canzi

Today

By the end of this class you will be able to

explain what the tidyverse is and what tidy data looks like
read and write .csv files with {readr}
use the core {dplyr} verbs: select(), filter(), arrange(), rename(), mutate()
summarise data by group with summarise() and count()
chain everything together with the pipe |>

The tidyverse

“R is not a language driven by the purity of its philosophy; R is a language designed to get shit done.”

Hadley Wickham (Chief Scientist at Posit, formerly RStudio)

Tidyverse

A collection of R packages that share the same design, grammar and data structures.

A dialect of R: everything you know from base R still works
Functions are verbs named after what they do
The data always comes first, so functions can be chained
Output is (almost) always a data frame, so the next step knows what to expect
Together with Quarto / R Markdown: reproducible analyses

Base R vs tidyverse

Same question: mean body mass of Gentoo penguins, in kg

Base R

gentoo <- penguins[penguins$species == "Gentoo", ]
mean(gentoo$body_mass_g / 1000, na.rm = TRUE)

tidyverse

penguins |>
  filter(species == "Gentoo") |>
  summarise(mass_kg = mean(body_mass_g / 1000, na.rm = TRUE))

Both give the same answer; the tidyverse version reads more like a sentence.

The core tidyverse

library(tidyverse) loads nine packages:

Package	What it does
`readr`	read and write rectangular data (.csv, .tsv)
`tibble`	a modern data frame
`dplyr`	manipulate data: filter, select, mutate, summarise
`tidyr`	reshape data (wide to long and back)
`ggplot2`	plots
`stringr`	work with text
`forcats`	work with factors (categorical variables)
`lubridate`	work with dates and times
`purrr`	apply functions repeatedly (iteration)

The typical workflow

Almost every analysis goes through the same steps, and each has its own tools:

Import	Tidy	Transform	Visualise	Model	Communicate
`{readr}`	`{tidyr}`	`{dplyr}`	`{ggplot2}`	`lm()` and friends	Quarto, R Markdown
today	today	today	next class	lectures 4–13	your assignment

The steps are not a straight line: plotting often sends you back to transform, and a model often sends you back to plot.

Tidy data

A data set is tidy when

each variable is a column
each observation is a row
each value is a cell

Frequency is a variable. Where is it?

Untidy (wide)

participant	rt_high	rt_low
P01	580	642
P02	611	690

Tidy (long)

participant	frequency	rt
P01	high	580
P01	low	642
P02	high	611
P02	low	690

Discussion

You record 20 speakers reading the same 30 words, and measure F1, F2 and the duration of each vowel.

What is one row in your tidy data set? Which columns do you need?
Where would information about the speakers, such as age or dialect, go?

From wide to long

pivot_longer() from {tidyr} turns the wide table into a tidy one:

wide <- tibble(participant = c("P01", "P02"), rt_high = c(580, 611), rt_low = c(642, 690))
wide |>
  pivot_longer(c(rt_high, rt_low), names_to = "frequency", values_to = "rt",
               names_prefix = "rt_")

# A tibble: 4 × 3
  participant frequency    rt
  <chr>       <chr>     <dbl>
1 P01         high        580
2 P01         low         642
3 P02         high        611
# ℹ 1 more row

pivot_wider() goes the other way, which is sometimes handy for tables in a paper.

Tibbles

A tibble is a data frame with nicer defaults.

penguins

# A tibble: 344 × 8
  species island    bill_length_mm bill_depth_mm flipper_length_mm
  <fct>   <fct>              <dbl>         <dbl>             <int>
1 Adelie  Torgersen           39.1          18.7               181
2 Adelie  Torgersen           39.5          17.4               186
3 Adelie  Torgersen           40.3          18                 195
# ℹ 341 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

Prints only the first rows and the columns that fit
Shows the type of each column: <fct>, <dbl>, <int>, <chr>

`glimpse()`

A transposed overview: one line per column.

glimpse(penguins)

Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Ad…
$ island            <fct> Torgersen, Torgersen, Torgersen, Torgersen…
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39…
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19…
$ flipper_length_mm <int> 181, 186, 195, NA, 193, 190, 181, 195, 193…
$ body_mass_g       <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 46…
$ sex               <fct> male, female, female, NA, female, male, fe…
$ year              <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, …

Example data

Did you know: the 344 rows of the penguins data are real birds: adult Adélie, Chinstrap and Gentoo penguins nesting near Palmer Station, Antarctica, measured by Dr Kristen Gorman and the Palmer Station LTER team from 2007 to 2009.

Sources: Gorman, Williams & Fraser (2014), PLoS ONE; palmerpenguins documentation

The `penguins` data

344 penguins, 3 species, 3 islands in the Palmer Archipelago (Antarctica).

install.packages("palmerpenguins")
library(palmerpenguins)

`penguins` in base R

Heads up! Since R 4.5.0, base R also ships a penguins data set, but with shorter column names:

`{palmerpenguins}`	base R ≥ 4.5.0
`bill_length_mm`	`bill_len`
`bill_depth_mm`	`bill_dep`
`flipper_length_mm`	`flipper_len`
`body_mass_g`	`body_mass`

In this course we always load {palmerpenguins} first. If you get object 'bill_length_mm' not found, you forgot!

`{readr}`

Reading and writing files

Function	For files that look like
`read_csv()`	`word,rt` with commas between values
`read_tsv()`	values separated by tabs
`read_csv2()`	`word;rt` with semicolons and decimal commas, as in German Excel exports
`read_delim()`	any other separator
`write_csv()`	saves a data frame as a .csv file

library(readr)
data <- read_csv("data/my_file.csv")

# ... do things with data ...

write_csv(data, "data/my_file_clean.csv")

What `read_csv()` tells you

rt <- read_csv(example_file)

Rows: 6 Columns: 5
── Column specification ──────────────────────────────────────────────
Delimiter: ","
chr (3): participant, word, frequency
dbl (2): rt, accuracy

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Rows and columns read
The type it guessed for each column
If a guess is wrong, set it yourself:

rt <- read_csv("data/rt.csv",
               col_types = cols(participant = col_factor()))

`read_csv()` vs `read.csv()`

	`read_csv()`	`read.csv()`
Package	`{readr}`	base R
Returns	tibble	data.frame
Speed	fast	slower on large files
Reports column types	yes	no

Use read_csv() (with an _) in this course.

`{dplyr}`

Did you know: Adélie penguins take their name from Adélie Land in Antarctica, which the French explorer Jules Dumont d’Urville named after his wife, Adèle, in 1840.

Source: Australian Antarctic Division

The grammar of `{dplyr}`

Every {dplyr} verb works the same way:

The first argument is a data frame
The other arguments say what to do, using column names without quotes
The output is a new data frame

Six verbs

Verb	Works on	Does
`select()`	columns	keep or drop columns
`rename()`	columns	change column names
`mutate()`	columns	create or change columns
`filter()`	rows	keep rows that match a condition
`arrange()`	rows	sort rows
`summarise()`	groups	collapse many rows into one

The verb never changes your data

filter(penguins, species == "Gentoo")

# A tibble: 124 × 8
  species island bill_length_mm bill_depth_mm flipper_length_mm
  <fct>   <fct>           <dbl>         <dbl>             <int>
1 Gentoo  Biscoe           46.1          13.2               211
2 Gentoo  Biscoe           50            16.3               230
3 Gentoo  Biscoe           48.7          14.1               210
# ℹ 121 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

nrow(penguins)

[1] 344

Nothing happened to penguins! To keep the result, assign it:

gentoo <- filter(penguins, species == "Gentoo")
nrow(gentoo)

[1] 124

Columns

`select()`

Keep the columns you name:

select(penguins, species, bill_length_mm)

# A tibble: 344 × 2
  species bill_length_mm
  <fct>            <dbl>
1 Adelie            39.1
2 Adelie            39.5
3 Adelie            40.3
# ℹ 341 more rows

Drop a column with -

select(penguins, -year)

`select()` helpers

Select by name pattern or type instead of typing every name:

select(penguins, species:bill_depth_mm)   # a range of columns
select(penguins, starts_with("bill"))     # names starting with "bill"
select(penguins, ends_with("_mm"))        # names ending with "_mm"
select(penguins, contains("length"))      # names containing "length"
select(penguins, where(is.numeric))       # all numeric columns
select(penguins, sex, everything())       # move sex to the front

select(penguins, ends_with("_mm"))

# A tibble: 344 × 3
  bill_length_mm bill_depth_mm flipper_length_mm
           <dbl>         <dbl>             <int>
1           39.1          18.7               181
2           39.5          17.4               186
3           40.3          18                 195
# ℹ 341 more rows

`select()` by position

Also possible, but fragile:

select(penguins, 1, 3, 5)
select(penguins, 1:4)
select(penguins, -(1:5))

If the column order changes (new data export, a colleague’s file), your code silently selects the wrong columns. Prefer names.

`rename()`

new_name = old_name

rename(penguins, Type = species)

# A tibble: 344 × 8
  Type   island    bill_length_mm bill_depth_mm flipper_length_mm
  <fct>  <fct>              <dbl>         <dbl>             <int>
1 Adelie Torgersen           39.1          18.7               181
2 Adelie Torgersen           39.5          17.4               186
3 Adelie Torgersen           40.3          18                 195
# ℹ 341 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

select() can rename too, but drops everything you do not name:

select(penguins, Type = species, bill_length_mm)

# A tibble: 344 × 2
  Type   bill_length_mm
  <fct>           <dbl>
1 Adelie           39.1
2 Adelie           39.5
3 Adelie           40.3
# ℹ 341 more rows

`mutate()`

Create new columns, or overwrite existing ones:

penguins_2 <- mutate(penguins,
                     body_mass_kg = body_mass_g / 1000,
                     bill_ratio   = bill_length_mm / bill_depth_mm)
select(penguins_2, species, body_mass_kg, bill_ratio)

# A tibble: 344 × 3
  species body_mass_kg bill_ratio
  <fct>          <dbl>      <dbl>
1 Adelie          3.75       2.09
2 Adelie          3.8        2.27
3 Adelie          3.25       2.24
# ℹ 341 more rows

New columns are added at the end. You can use a column you just created in the same mutate().

`mutate()` with conditions

if_else() for two outcomes

mutate(penguins, size = if_else(body_mass_g > 4500, "large", "small"))

case_when() for more than two

penguins_2 <- mutate(penguins,
  size = case_when(
    body_mass_g < 3500 ~ "small",
    body_mass_g < 4500 ~ "medium",
    body_mass_g >= 4500 ~ "large"
  )
)
count(penguins_2, size)

# A tibble: 4 × 2
  size       n
  <chr>  <int>
1 large    118
2 medium   153
3 small     71
# ℹ 1 more row

Conditions are checked in order. Rows matching none of them get NA.

`mutate()` on many columns: `across()`

mutate_if() and mutate_all() are superseded: they still work, but are no longer developed. Use across():

# old
mutate_if(penguins, is.factor, as.character)

# new
mutate(penguins, across(where(is.factor), as.character))

across(columns, function): apply this function to these columns

mutate(penguins, across(ends_with("_mm"), \(x) x / 10))  # mm to cm

\(x) x / 10 is a short way to write a small function: take x, divide it by 10.

Rows

`filter()`

Keep the rows where a condition is TRUE:

filter(penguins, species == "Gentoo")

# A tibble: 124 × 8
  species island bill_length_mm bill_depth_mm flipper_length_mm
  <fct>   <fct>           <dbl>         <dbl>             <int>
1 Gentoo  Biscoe           46.1          13.2               211
2 Gentoo  Biscoe           50            16.3               230
3 Gentoo  Biscoe           48.7          14.1               210
# ℹ 121 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

Comparison operators

Operator	Meaning	Example
`==`	equal to	`species == "Gentoo"`
`!=`	not equal to	`species != "Gentoo"`
`>`, `>=`	greater than (or equal)	`body_mass_g > 4000`
`<`, `<=`	less than (or equal)	`year <= 2008`
`%in%`	one of	`island %in% c("Biscoe", "Dream")`
`is.na()`	missing	`is.na(sex)`

= assigns, == compares. filter(penguins, species = "Gentoo") gives an error.

Combining conditions

, or &: both must be true (AND)

filter(penguins, species == "Gentoo", body_mass_g > 5000)
filter(penguins, species == "Gentoo" & body_mass_g > 5000)

|: at least one must be true (OR)

filter(penguins, species == "Gentoo" | species == "Chinstrap")

%in% is shorter when matching several values of one column

unique(filter(penguins, species %in% c("Gentoo", "Chinstrap"))$species)

[1] Gentoo    Chinstrap
Levels: Adelie Chinstrap Gentoo

`filter()` and missing values

filter() drops rows where the condition is NA.

nrow(penguins)

[1] 344

sum(is.na(penguins$sex))

[1] 11

nrow(filter(penguins, sex == "female"))

[1] 165

nrow(filter(penguins, sex != "female"))

[1] 168

165 + 168 = 333, not 344! The 11 penguins with unknown sex are dropped by both.

Discussion

In a listening experiment, some participants did not respond on a few trials, so those cells are NA.

Should these trials be removed, kept as NA, or counted as wrong answers?
Does your answer depend on why the response is missing?

`filter_out()` (new in `{dplyr}` 1.2.0)

Say which rows to drop. Rows with NA are kept.

nrow(filter_out(penguins, sex == "female"))

[1] 179

Rule of thumb:

filter() when you know what to keep
filter_out() when you know what to drop

Requires {dplyr} ≥ 1.2.0. Check with packageVersion("dplyr").

`arrange()`

Sort rows, smallest first

arrange(penguins, body_mass_g)

# A tibble: 344 × 8
  species   island bill_length_mm bill_depth_mm flipper_length_mm
  <fct>     <fct>           <dbl>         <dbl>             <int>
1 Chinstrap Dream            46.9          16.6               192
2 Adelie    Biscoe           36.5          16.6               181
3 Adelie    Biscoe           36.4          17.1               184
# ℹ 341 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

Largest first with desc(); sort by several columns in turn

arrange(penguins, desc(body_mass_g))
arrange(penguins, species, desc(body_mass_g))

Groups

Did you know: chinstrap penguins sleep in microsleeps: more than 10,000 a day, about 4 seconds each, which add up to about 11 hours of sleep.

Source: Libourel et al. (2023), Science

`summarise()`

Collapse many rows into one

summarise(penguins,
          mean_mass = mean(body_mass_g, na.rm = TRUE),
          sd_mass   = sd(body_mass_g, na.rm = TRUE),
          n         = n())

# A tibble: 1 × 3
  mean_mass sd_mass     n
      <dbl>   <dbl> <int>
1     4202.    802.   344

na.rm = TRUE: ignore missing values. Without it, one NA makes the whole result NA.

`group_by()` + `summarise()`

One row per group

penguins_grouped <- group_by(penguins, species)
summarise(penguins_grouped,
          mean_mass = mean(body_mass_g, na.rm = TRUE),
          n         = n())

# A tibble: 3 × 3
  species   mean_mass     n
  <fct>         <dbl> <int>
1 Adelie        3701.   152
2 Chinstrap     3733.    68
3 Gentoo        5076.   124

group_by() changes nothing you can see. It tells the next verb to work group by group.

`.by`: grouping for one step

Same result, without a separate group_by():

summarise(penguins,
          mean_mass = mean(body_mass_g, na.rm = TRUE),
          n         = n(),
          .by       = c(species, sex))

# A tibble: 8 × 4
  species sex    mean_mass     n
  <fct>   <fct>      <dbl> <int>
1 Adelie  male       4043.    73
2 Adelie  female     3369.    73
3 Adelie  <NA>       3540      6
# ℹ 5 more rows

.by groups for this verb only, so you never forget to ungroup(). It works in summarise(), mutate(), filter() and slice().

`count()`

A shortcut for counting rows per group

count(penguins, species, island)

# A tibble: 5 × 3
  species island        n
  <fct>   <fct>     <int>
1 Adelie  Biscoe       44
2 Adelie  Dream        56
3 Adelie  Torgersen    52
# ℹ 2 more rows

Same counts as summarise(penguins, n = n(), .by = c(species, island)), but count() also sorts the rows by group.

The pipe

Did you know: gentoo penguins are the fastest swimming birds: they reach about 36 km/h in short bursts, close to Usain Bolt’s average speed in his 100 m world record.

Source: Guinness World Records

The problem

Three steps, three intermediate objects:

penguins_2 <- filter(penguins, species == "Gentoo" | species == "Chinstrap")
penguins_3 <- mutate(penguins_2, bill_length_cm = bill_length_mm / 10)
penguins_4 <- select(penguins_3, species, bill_length_cm)

Or nested, read from the inside out:

penguins_4 <- select(
  mutate(
    filter(penguins, species == "Gentoo" | species == "Chinstrap"),
    bill_length_cm = bill_length_mm / 10),
  species, bill_length_cm)

The pipe `|>`

x |> f(y) is the same as f(x, y)

Takes the result on the left, passes it as the first argument of the function on the right
Read it aloud as then. There is no need to repeat the object at every step

penguins_2 <- penguins |>
  filter(species == "Gentoo" | species == "Chinstrap") |>
  mutate(bill_length_cm = bill_length_mm / 10) |>
  select(species, bill_length_cm)
penguins_2

# A tibble: 192 × 2
  species bill_length_cm
  <fct>            <dbl>
1 Gentoo            4.61
2 Gentoo            5   
3 Gentoo            4.87
# ℹ 189 more rows

Reading a pipe aloud

Code	Read it as
`penguins_2 <- penguins \\|>`	Take `penguins`, then
`filter(species == "Gentoo" \\| species == "Chinstrap") \\|>`	keep only Gentoo and Chinstrap penguins, then
`mutate(bill_length_cm = bill_length_mm / 10) \\|>`	add bill length in cm, then
`select(species, bill_length_cm)`	keep only these two columns.

Store the result as penguins_2. If you cannot read a pipe aloud like this, it is probably doing too much at once.

`PRO TIP!` Save to the right

penguins |>
  filter(species == "Gentoo" | species == "Chinstrap") |>
  mutate(bill_length_cm = bill_length_mm / 10) |>
  select(species, bill_length_cm) -> penguins_2

Same output as the previous slide. The code now reads top to bottom in the order things happen.

`|>` vs `%>%`

You will see two pipes in the wild:

%>% from {magrittr}, loaded with the tidyverse (since 2014)
|> built into R itself (since R 4.1.0)

For everything in this course they are interchangeable. We use |>: it works without loading any package.

Keyboard shortcut: Ctrl/Cmd + Shift + M

To make it insert |>: Tools > Global Options > Code > Use native pipe operator

From wrangling to plotting

The output of a pipe can go straight into {ggplot2}

penguins |>
  filter_out(is.na(sex)) |>
  ggplot(aes(x = flipper_length_mm, y = body_mass_g, colour = species)) +
  geom_point(size = 2, alpha = 0.8) +
  labs(x = "Flipper length (mm)", y = "Body mass (g)", colour = NULL)

Note: {ggplot2} layers are added with +, not |>. More next class.

From wrangling to plotting

Common errors

You wrote	Problem
`filter(df, species = "Gentoo")`	`=` instead of `==`
`filter(df, species == Gentoo)`	text values need `"quotes"`
`select(df, Species)`	names are case sensitive
`filter(df, ...)` and nothing changes	result not assigned with `<-`
`mean(body_mass_g)` gives `NA`	missing values: add `na.rm = TRUE`
`object 'bill_length_mm' not found`	forgot `library(palmerpenguins)`
`could not find function "filter_out"`	`{dplyr}` older than 1.2.0

Summary

{readr}: read_csv(), write_csv()
Columns: select(), rename(), mutate(), across()
Rows: filter(), filter_out(), arrange()
Groups: summarise(), count(), group_by() or .by
Chain with |>, read as then
Always assign the result if you want to keep it

Slides and code

These slides

mcanzi.de/ws26_statistics/02_data_wrangling.html

Code, data and all lectures

github.com/mcanzi/ws26_statistics

Data wrangling

Reading and wrangling data with the {tidyverse}

Dr Massimiliano Canzi

Today

The tidyverse

Tidyverse

Base R vs tidyverse

The core tidyverse

The typical workflow

Tidy data

Discussion

From wide to long

Tibbles

glimpse()

Example data

The penguins data

penguins in base R

{readr}

Reading and writing files

What read_csv() tells you

read_csv() vs read.csv()

{dplyr}

The grammar of {dplyr}

Six verbs

The verb never changes your data

Columns

select()

select() helpers

select() by position

rename()

mutate()

mutate() with conditions

mutate() on many columns: across()

Rows

filter()

Comparison operators

Combining conditions

filter() and missing values

Discussion

filter_out() (new in {dplyr} 1.2.0)

arrange()

Groups

summarise()

group_by() + summarise()

.by: grouping for one step

count()

The pipe

The problem

The pipe |>

Reading a pipe aloud

PRO TIP! Save to the right

|> vs %>%

From wrangling to plotting

From wrangling to plotting

Common errors

Common errors

Summary

Slides and code

`glimpse()`

The `penguins` data

`penguins` in base R

`{readr}`

What `read_csv()` tells you

`read_csv()` vs `read.csv()`

`{dplyr}`

The grammar of `{dplyr}`

`select()`

`select()` helpers

`select()` by position

`rename()`

`mutate()`

`mutate()` with conditions

`mutate()` on many columns: `across()`

`filter()`

`filter()` and missing values

`filter_out()` (new in `{dplyr}` 1.2.0)

`arrange()`

`summarise()`

`group_by()` + `summarise()`

`.by`: grouping for one step

`count()`

The pipe `|>`

`PRO TIP!` Save to the right

`|>` vs `%>%`