11 Data Transformation

Author

Affiliation

Md Rasel Biswas

IASDS, University of Dhaka

1 Getting started

1.1 Why transform data?

Before analysing data, we often need to:

Keep observations that answer a particular question.
Choose the variables we need.
Create new variables from existing measurements.
Calculate summaries, frequencies, and proportions.

We will learn these tasks one at a time using the penguins data.

1.2 Load the packages

library(tidyverse)
library(palmerpenguins)

dplyr, loaded with tidyverse, provides functions for transforming data. palmerpenguins provides the dataset used in this lecture.

1.3 Tibble review

A tibble is a data frame with convenient printing behaviour.
Use tibble() to create one or as_tibble() to convert a data frame.

mtcars_t <- as_tibble(mtcars)
mtcars_t

# A tibble: 32 × 11
     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
 1  21       6  160    110  3.9   2.62  16.5     0     1     4     4
 2  21       6  160    110  3.9   2.88  17.0     0     1     4     4
 3  22.8     4  108     93  3.85  2.32  18.6     1     1     4     1
 4  21.4     6  258    110  3.08  3.22  19.4     1     0     3     1
 5  18.7     8  360    175  3.15  3.44  17.0     0     0     3     2
 6  18.1     6  225    105  2.76  3.46  20.2     1     0     3     1
 7  14.3     8  360    245  3.21  3.57  15.8     0     0     3     4
 8  24.4     4  147.    62  3.69  3.19  20       1     0     4     2
 9  22.8     4  141.    95  3.92  3.15  22.9     1     0     4     2
10  19.2     6  168.   123  3.92  3.44  18.3     1     0     4     4
# ℹ 22 more rows

1.4 Meet the penguins data

The penguins data contain measurements on 344 penguins of three species, observed on three islands in Antarctica during 2007–2009. The data were collected by Dr. Kristen Gorman and colleagues.

glimpse(penguins)

Rows: 344
Columns: 8
$ species           <fct> Adelie, Adelie, Adelie, Adelie, Adelie, Adelie, Adel…
$ island            <fct> Torgersen, Torgersen, Torgersen, Torgersen, Torgerse…
$ bill_length_mm    <dbl> 39.1, 39.5, 40.3, NA, 36.7, 39.3, 38.9, 39.2, 34.1, …
$ bill_depth_mm     <dbl> 18.7, 17.4, 18.0, NA, 19.3, 20.6, 17.8, 19.6, 18.1, …
$ flipper_length_mm <int> 181, 186, 195, NA, 193, 190, 181, 195, 193, 190, 186…
$ body_mass_g       <int> 3750, 3800, 3250, NA, 3450, 3650, 3625, 4675, 3475, …
$ sex               <fct> male, female, female, NA, female, male, female, male…
$ year              <int> 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007, 2007…

The eight variables describe species, island, bill dimensions, flipper length, body mass, sex, and year. Some measurements and sex values are missing (NA).

1.5 A map of the lecture

Task	Main verbs
Keep or reorder rows	`filter()`, `arrange()`, `slice()`, `distinct()`
Choose, rename, or create columns	`select()`, `rename()`, `mutate()`
Compute overall or grouped summaries	`summarise()`, `group_by()`
Find frequencies and proportions	`count()`, `mutate()`
Repeat a transformation across columns	`across()`

These verbs take a data frame as their first argument and return a data frame. The result does not replace the original data unless you assign it.

2 The pipe: reading a sequence of steps

2.1 One operation, two equivalent forms

Problem: Keep only Chinstrap penguins.

filter(penguins, species == "Chinstrap")

penguins |>
  filter(species == "Chinstrap")

# A tibble: 68 × 8
   species   island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>     <fct>           <dbl>         <dbl>             <int>       <int>
 1 Chinstrap Dream            46.5          17.9               192        3500
 2 Chinstrap Dream            50            19.5               196        3900
 3 Chinstrap Dream            51.3          19.2               193        3650
 4 Chinstrap Dream            45.4          18.7               188        3525
 5 Chinstrap Dream            52.7          19.8               197        3725
 6 Chinstrap Dream            45.2          17.8               198        3950
 7 Chinstrap Dream            46.1          18.2               178        3250
 8 Chinstrap Dream            51.3          18.2               197        3750
 9 Chinstrap Dream            46            18.9               195        4150
10 Chinstrap Dream            51.3          19.9               198        3700
# ℹ 58 more rows
# ℹ 2 more variables: sex <fct>, year <int>

Read |> as then. It passes the result on its left to the first argument of the function on its right: x |> f(y) is equivalent to f(x, y).

2.2 Saving a result

chinstrap <- penguins |>
  filter(species == "Chinstrap")

This creates chinstrap; penguins remains unchanged. We will combine several verbs after learning the row and column operations.

3 Working with rows

3.1 Keep rows with `filter()`

Problem: Find Chinstrap penguins whose bill length exceeds 52 mm.

penguins |>
  filter(species == "Chinstrap", bill_length_mm > 52)

# A tibble: 7 × 8
  species   island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
  <fct>     <fct>           <dbl>         <dbl>             <int>       <int>
1 Chinstrap Dream            52.7          19.8               197        3725
2 Chinstrap Dream            58            17.8               181        3700
3 Chinstrap Dream            52.8          20                 205        4550
4 Chinstrap Dream            54.2          20.8               201        4300
5 Chinstrap Dream            53.5          19.9               205        4500
6 Chinstrap Dream            52.2          18.8               197        3450
7 Chinstrap Dream            55.8          19.8               207        4000
# ℹ 2 more variables: sex <fct>, year <int>

Comparisons: >, >=, <, <=, ==, and !=.
Combine conditions with & or a comma for and, and | for or.
filter() keeps rows where the condition is TRUE; FALSE and NA are dropped.

3.2 Match either of two values

Problem: Keep Chinstrap or Gentoo penguins.

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

# A tibble: 192 × 8
   species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>           <dbl>         <dbl>             <int>       <int>
 1 Gentoo  Biscoe           46.1          13.2               211        4500
 2 Gentoo  Biscoe           50            16.3               230        5700
 3 Gentoo  Biscoe           48.7          14.1               210        4450
 4 Gentoo  Biscoe           50            15.2               218        5700
 5 Gentoo  Biscoe           47.6          14.5               215        5400
 6 Gentoo  Biscoe           46.5          13.5               210        4550
 7 Gentoo  Biscoe           45.4          14.6               211        4800
 8 Gentoo  Biscoe           46.7          15.3               219        5200
 9 Gentoo  Biscoe           43.3          13.4               209        4400
10 Gentoo  Biscoe           46.8          15.4               215        5150
# ℹ 182 more rows
# ℹ 2 more variables: sex <fct>, year <int>

The %in% operator gives a compact equivalent:

penguins |>
  filter(species %in% c("Chinstrap", "Gentoo"))

# A tibble: 192 × 8
   species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>           <dbl>         <dbl>             <int>       <int>
 1 Gentoo  Biscoe           46.1          13.2               211        4500
 2 Gentoo  Biscoe           50            16.3               230        5700
 3 Gentoo  Biscoe           48.7          14.1               210        4450
 4 Gentoo  Biscoe           50            15.2               218        5700
 5 Gentoo  Biscoe           47.6          14.5               215        5400
 6 Gentoo  Biscoe           46.5          13.5               210        4550
 7 Gentoo  Biscoe           45.4          14.6               211        4800
 8 Gentoo  Biscoe           46.7          15.3               219        5200
 9 Gentoo  Biscoe           43.3          13.4               209        4400
10 Gentoo  Biscoe           46.8          15.4               215        5150
# ℹ 182 more rows
# ℹ 2 more variables: sex <fct>, year <int>

3.3 Exercise 1: filtering

Keep Gentoo penguins with bill depth at least 15.5 mm.
Keep male penguins recorded on Dream or Biscoe Island.
Keep penguins that are either Gentoo or have body mass greater than 4500 g.

3.4 Order rows with `arrange()`

arrange() reorders the observations by one or more variables (column names)
As inputs, arrange() takes a data frame and a set of column names (variables) to order by
With several columns, each additional column breaks ties in the preceding columns
The default is ascending order (lowest to highest); use desc() for descending order. Missing values are placed last.

penguins |>
  arrange(bill_length_mm)

# A tibble: 344 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Dream               32.1          15.5               188        3050
 2 Adelie  Dream               33.1          16.1               178        2900
 3 Adelie  Torgersen           33.5          19                 190        3600
 4 Adelie  Dream               34            17.1               185        3400
 5 Adelie  Torgersen           34.1          18.1               193        3475
 6 Adelie  Torgersen           34.4          18.4               184        3325
 7 Adelie  Biscoe              34.5          18.1               187        2900
 8 Adelie  Torgersen           34.6          21.1               198        4400
 9 Adelie  Torgersen           34.6          17.2               189        3200
10 Adelie  Biscoe              35            17.9               190        3450
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

# arrange in descending order
penguins |>
  arrange(desc(bill_depth_mm))

# A tibble: 344 × 8
   species   island   bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>     <fct>             <dbl>         <dbl>             <int>       <int>
 1 Adelie    Torgers…           46            21.5               194        4200
 2 Adelie    Torgers…           38.6          21.2               191        3800
 3 Adelie    Dream              42.3          21.2               191        4150
 4 Adelie    Torgers…           34.6          21.1               198        4400
 5 Adelie    Dream              39.2          21.1               196        4150
 6 Adelie    Biscoe             41.3          21.1               195        4400
 7 Chinstrap Dream              54.2          20.8               201        4300
 8 Adelie    Torgers…           42.5          20.7               197        4500
 9 Adelie    Biscoe             39.6          20.7               191        3900
10 Chinstrap Dream              52            20.7               210        4800
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

# order by several variables
penguins |>
  arrange(desc(bill_depth_mm),
          desc(flipper_length_mm))

# A tibble: 344 × 8
   species   island   bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>     <fct>             <dbl>         <dbl>             <int>       <int>
 1 Adelie    Torgers…           46            21.5               194        4200
 2 Adelie    Torgers…           38.6          21.2               191        3800
 3 Adelie    Dream              42.3          21.2               191        4150
 4 Adelie    Torgers…           34.6          21.1               198        4400
 5 Adelie    Dream              39.2          21.1               196        4150
 6 Adelie    Biscoe             41.3          21.1               195        4400
 7 Chinstrap Dream              54.2          20.8               201        4300
 8 Chinstrap Dream              52            20.7               210        4800
 9 Adelie    Torgers…           42.5          20.7               197        4500
10 Adelie    Biscoe             39.6          20.7               191        3900
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

3.5 Select rows by position with `slice()`

Problem: Keep selected row positions in the current ordering.

penguins |>
  slice(1:5)        # first 5 rows

# A tibble: 5 × 8
  species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
  <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
1 Adelie  Torgersen           39.1          18.7               181        3750
2 Adelie  Torgersen           39.5          17.4               186        3800
3 Adelie  Torgersen           40.3          18                 195        3250
4 Adelie  Torgersen           NA            NA                  NA          NA
5 Adelie  Torgersen           36.7          19.3               193        3450
# ℹ 2 more variables: sex <fct>, year <int>

penguins |>
  slice(c(3, 10))   # specific rows

# A tibble: 2 × 8
  species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
  <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
1 Adelie  Torgersen           40.3          18                 195        3250
2 Adelie  Torgersen           42            20.2               190        4250
# ℹ 2 more variables: sex <fct>, year <int>

3.6 Select the largest values

Problem: Find the three largest bill lengths.

penguins |>
  slice_max(order_by = bill_length_mm, n = 3)

# A tibble: 3 × 8
  species   island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
  <fct>     <fct>           <dbl>         <dbl>             <int>       <int>
1 Gentoo    Biscoe           59.6          17                 230        6050
2 Chinstrap Dream            58            17.8               181        3700
3 Gentoo    Biscoe           55.9          17                 228        5600
# ℹ 2 more variables: sex <fct>, year <int>

By default, ties are retained, so more than three rows may be returned. Use with_ties = FALSE to keep at most three rows.

3.7 Select the smallest values

Problem: Find the three smallest body masses.

penguins |>
  slice_min(order_by = body_mass_g, n = 3)

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

3.8 Sample rows

Random sampling of rows

penguins |>
  slice_sample(n = 3)        # sample 3 penguins

penguins |>
  slice_sample(prop = 0.1)   # sample 10% of data

penguins |>
  slice_sample(n = 5, replace = TRUE) # sample with replacement

For reproducible sampling, run set.seed(123) before the sampling code.

3.9 Find unique rows with `distinct()`

distinct() finds all the unique rows in a dataset

# Remove duplicate rows, if any
penguins |>
  distinct()

# A tibble: 344 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

Most of the time, however, you’ll want the distinct combination of some variables, so you can also optionally supply column names

# find all unique species and year pairs
penguins |>
  distinct(species, year)

# A tibble: 9 × 2
  species    year
  <fct>     <int>
1 Adelie     2007
2 Adelie     2008
3 Adelie     2009
4 Gentoo     2007
5 Gentoo     2008
6 Gentoo     2009
7 Chinstrap  2007
8 Chinstrap  2008
9 Chinstrap  2009

When column names are supplied, only those columns are returned by default. We will count these combinations later using count().

3.10 Exercise 2: row operations

Sort penguins by species, then by decreasing body mass within species.
Return exactly five rows with the largest non-missing flipper lengths.
List the distinct combinations of species and island.

4 Working with columns

Choose columns first, rename them when needed, then create or transform variables.

4.1 Choose columns with `select()`

In practice, an analysis often uses only a subset of the available variables.
select() is used to create a new data frame with the variables mentioned in the arguments (selected variables)
As inputs, a data frame, and column names to be selected are used in select()

Create a data frame with three variables:
- year, island, and species

penguins |>
  select(year, island, species)

# A tibble: 344 × 3
    year island    species
   <int> <fct>     <fct>  
 1  2007 Torgersen Adelie 
 2  2007 Torgersen Adelie 
 3  2007 Torgersen Adelie 
 4  2007 Torgersen Adelie 
 5  2007 Torgersen Adelie 
 6  2007 Torgersen Adelie 
 7  2007 Torgersen Adelie 
 8  2007 Torgersen Adelie 
 9  2007 Torgersen Adelie 
10  2007 Torgersen Adelie 
# ℹ 334 more rows

names(penguins)

[1] "species"           "island"            "bill_length_mm"   
[4] "bill_depth_mm"     "flipper_length_mm" "body_mass_g"      
[7] "sex"               "year"

A colon (:) can be used to select a number of consecutive variables

penguins |>
  select(species:body_mass_g)

# A tibble: 344 × 6
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows

We can also omit variables using the negative sign.

penguins |>
  select(species:bill_depth_mm, -island)

# A tibble: 344 × 3
   species bill_length_mm bill_depth_mm
   <fct>            <dbl>         <dbl>
 1 Adelie            39.1          18.7
 2 Adelie            39.5          17.4
 3 Adelie            40.3          18  
 4 Adelie            NA            NA  
 5 Adelie            36.7          19.3
 6 Adelie            39.3          20.6
 7 Adelie            38.9          17.8
 8 Adelie            39.2          19.6
 9 Adelie            34.1          18.1
10 Adelie            42            20.2
# ℹ 334 more rows

select() can also be used to rename a variable and reorder the variables

penguins |>
  select(species, year, bill_len = bill_length_mm)

# A tibble: 344 × 3
   species  year bill_len
   <fct>   <int>    <dbl>
 1 Adelie   2007     39.1
 2 Adelie   2007     39.5
 3 Adelie   2007     40.3
 4 Adelie   2007     NA  
 5 Adelie   2007     36.7
 6 Adelie   2007     39.3
 7 Adelie   2007     38.9
 8 Adelie   2007     39.2
 9 Adelie   2007     34.1
10 Adelie   2007     42  
# ℹ 334 more rows

select() has some helper functions that can be used to select a subset of variables
- starts_with("abc"), ends_with("th"), contains("co"), etc.

names(penguins)

[1] "species"           "island"            "bill_length_mm"   
[4] "bill_depth_mm"     "flipper_length_mm" "body_mass_g"      
[7] "sex"               "year"

penguins |>
  select(starts_with("bill"))

# A tibble: 344 × 2
   bill_length_mm bill_depth_mm
            <dbl>         <dbl>
 1           39.1          18.7
 2           39.5          17.4
 3           40.3          18  
 4           NA            NA  
 5           36.7          19.3
 6           39.3          20.6
 7           38.9          17.8
 8           39.2          19.6
 9           34.1          18.1
10           42            20.2
# ℹ 334 more rows

4.2 Rename columns with `rename()`

If you want to keep all the existing variables and just want to rename a few, you can use rename() instead of select()

penguins |>
  rename(location = island)

# A tibble: 344 × 8
   species location  bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

Use new_name = old_name. Unlike select(), rename() keeps the other columns.

4.3 Exercise 3: selecting and renaming

Starting with the penguins data, only keep the body_mass_g variable.
Starting with the penguins data, keep columns from bill_length_mm to body_mass_g, and year
Starting with the penguins data, keep all columns except island
From penguins, keep the species column and any columns that end with “mm”.
Rename bill_length_mm to bill_length, keeping all other columns.

4.4 Create variables with `mutate()`

mutate() creates new variables or changes existing ones without changing the number of rows.
E.g. create a new variable body_mass_kg (body mass in kg) by body_mass_g/1000

penguins |>
  mutate(body_mass_kg = body_mass_g / 1000)

# A tibble: 344 × 9
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows
# ℹ 3 more variables: sex <fct>, year <int>, body_mass_kg <dbl>

By default, mutate() adds new columns on the right side of your data

Use .before = 1 to place the new column before the first column.

penguins |>
  mutate(body_mass_kg = body_mass_g / 1000,
         .before = 1)

# A tibble: 344 × 9
   body_mass_kg species island    bill_length_mm bill_depth_mm flipper_length_mm
          <dbl> <fct>   <fct>              <dbl>         <dbl>             <int>
 1         3.75 Adelie  Torgersen           39.1          18.7               181
 2         3.8  Adelie  Torgersen           39.5          17.4               186
 3         3.25 Adelie  Torgersen           40.3          18                 195
 4        NA    Adelie  Torgersen           NA            NA                  NA
 5         3.45 Adelie  Torgersen           36.7          19.3               193
 6         3.65 Adelie  Torgersen           39.3          20.6               190
 7         3.62 Adelie  Torgersen           38.9          17.8               181
 8         4.68 Adelie  Torgersen           39.2          19.6               195
 9         3.48 Adelie  Torgersen           34.1          18.1               193
10         4.25 Adelie  Torgersen           42            20.2               190
# ℹ 334 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

4.5 Recode into two categories

Suppose we want to classify penguins by their flipper size, so create a new variable flip_size, which will be either “large” or “short”
- “large” if flipper size is greater than 210 mm
- “short” if flipper size is less than or equal to 210 mm
if_else(condition, true, false) is used to obtain a variable with two levels depending on whether condition is satisfied or not

penguins |>
  mutate(
    flip_size = if_else(
      flipper_length_mm > 210, "large", "short")
    )

# A tibble: 344 × 9
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows
# ℹ 3 more variables: sex <fct>, year <int>, flip_size <chr>

4.6 Recode into several categories

Use case_when() for several conditions. The first matching condition is used.
For example, we are interested in classifying penguins into three categories (large, medium, and small) by their body mass where
- “small”: body mass ≤ 3000 g
- “medium”: 3000 g < body mass ≤ 4500 g
- “large”: body mass > 4500 g

penguins |>
  mutate(
    mass_c = case_when(
      body_mass_g > 4500 ~ "large",
      body_mass_g > 3000 &
        body_mass_g <= 4500 ~ "medium",
      body_mass_g <= 3000 ~ "small"
    ), .before = 1
  )

# A tibble: 344 × 9
   mass_c species island    bill_length_mm bill_depth_mm flipper_length_mm
   <chr>  <fct>   <fct>              <dbl>         <dbl>             <int>
 1 medium Adelie  Torgersen           39.1          18.7               181
 2 medium Adelie  Torgersen           39.5          17.4               186
 3 medium Adelie  Torgersen           40.3          18                 195
 4 <NA>   Adelie  Torgersen           NA            NA                  NA
 5 medium Adelie  Torgersen           36.7          19.3               193
 6 medium Adelie  Torgersen           39.3          20.6               190
 7 medium Adelie  Torgersen           38.9          17.8               181
 8 large  Adelie  Torgersen           39.2          19.6               195
 9 medium Adelie  Torgersen           34.1          18.1               193
10 medium Adelie  Torgersen           42            20.2               190
# ℹ 334 more rows
# ℹ 3 more variables: body_mass_g <int>, sex <fct>, year <int>

Missing measurements remain NA in these examples; they are not assigned a size category.

5 Combining row and column operations

5.1 A complete pipeline

Problem: Find female Adelie penguins, show their bill measurements, and order them from longest to shortest bill.

penguins |>
  filter(species == "Adelie", sex == "female") |>
  select(species, sex, starts_with("bill")) |>
  arrange(desc(bill_length_mm))

# A tibble: 73 × 4
   species sex    bill_length_mm bill_depth_mm
   <fct>   <fct>           <dbl>         <dbl>
 1 Adelie  female           42.2          18.5
 2 Adelie  female           41.1          17.6
 3 Adelie  female           40.9          16.8
 4 Adelie  female           40.5          17.9
 5 Adelie  female           40.3          18  
 6 Adelie  female           40.2          17  
 7 Adelie  female           40.2          17.1
 8 Adelie  female           39.7          17.7
 9 Adelie  female           39.6          17.7
10 Adelie  female           39.6          17.2
# ℹ 63 more rows

Read from top to bottom: start with the data, keep rows, choose columns, then sort. Keep a variable until every step that needs it has finished.

5.2 The same work without a pipe

A shorter example can be written using nested calls:

arrange(
  filter(penguins, species == "Adelie", sex == "female"),
  desc(bill_length_mm)
)

Or using an intermediate object:

penguins1 <- filter(penguins, species == "Adelie", sex == "female")
arrange(penguins1, desc(bill_length_mm))

The pipe expresses these operations in the order we think about them.

5.3 Recognising `%>%`

You may also see %>%, the magrittr pipe, available after loading tidyverse. For the simple pipelines here, it gives the same result as |>.

penguins %>%
  filter(species == "Adelie", sex == "female") %>%
  arrange(desc(bill_length_mm))

We continue using |> consistently.

5.4 Exercise 4: pipelines and new variables

Keep female penguins from Dream Island, then select species and all columns starting with "bill".
Create flipper_m by converting flipper_length_mm to metres.
Create year_fct by converting year to a factor using as.factor().

Within a single mutate() call:
1. Convert species to character.
2. Create flipper_cm in centimetres.
3. Convert island to lowercase using tolower().
Create a body-mass category using the boundaries above, then keep only Gentoo penguins in the large category.

6 Overall and grouped summaries

6.1 Start with an overall summary

Problem: Find the mean and standard deviation of body mass for all penguins.

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

# A tibble: 1 × 2
  mean_mass sd_mass
      <dbl>   <dbl>
1     4202.    802.

On ungrouped data, summarise() returns one row.
Each summary expression returns one value.
na.rm = TRUE excludes missing values from that calculation. Without it, these summaries would be NA.

6.2 Define groups with `group_by()`

Problem: Prepare to calculate separate summaries for each species.

penguins |>
  group_by(species)

# A tibble: 344 × 8
# Groups:   species [3]
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <int>       <int>
 1 Adelie  Torgersen           39.1          18.7               181        3750
 2 Adelie  Torgersen           39.5          17.4               186        3800
 3 Adelie  Torgersen           40.3          18                 195        3250
 4 Adelie  Torgersen           NA            NA                  NA          NA
 5 Adelie  Torgersen           36.7          19.3               193        3450
 6 Adelie  Torgersen           39.3          20.6               190        3650
 7 Adelie  Torgersen           38.9          17.8               181        3625
 8 Adelie  Torgersen           39.2          19.6               195        4675
 9 Adelie  Torgersen           34.1          18.1               193        3475
10 Adelie  Torgersen           42            20.2               190        4250
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <int>

group_by() keeps the rows and columns but adds grouping information. Later operations can then work separately within each group.

6.3 Summarise within groups

penguins |>
  group_by(species) |>
  summarise(
    mean_mass = mean(body_mass_g, na.rm = TRUE),
    sd_mass = sd(body_mass_g, na.rm = TRUE)
  )

# A tibble: 3 × 3
  species   mean_mass sd_mass
  <fct>         <dbl>   <dbl>
1 Adelie        3701.    459.
2 Chinstrap     3733.    384.
3 Gentoo        5076.    504.

The result has one row per species. With this single grouping variable, summarise() drops the grouping by default. With several grouping variables, some grouping can remain; use ungroup() when later steps should use the whole result.

6.4 Temporary grouping with `.by`

The .by argument groups data for just one operation. Here it gives the same species summaries without retaining grouping afterwards.

penguins |>
  summarise(
    mean_mass = mean(body_mass_g, na.rm = TRUE),
    sd_mass = sd(body_mass_g, na.rm = TRUE),
    .by = species
  )

# A tibble: 3 × 3
  species   mean_mass sd_mass
  <fct>         <dbl>   <dbl>
1 Adelie        3701.    459.
2 Gentoo        5076.    504.
3 Chinstrap     3733.    384.

Use .by on ungrouped data as an alternative to group_by(). We will also use it inside mutate() when calculating proportions within groups.

6.5 Exercise 5: grouped summaries

For Chinstrap penguins, find the maximum and minimum flipper lengths by island. Name the summary columns flip_max and flip_min; exclude missing measurements. How many islands appear in the result?

In one pipeline:
1. Create bill_ratio as bill length divided by bill depth.
2. Keep only species and bill_ratio.
3. Group by species.
4. Calculate mean bill ratio, excluding missing values, and name it bill_ratio_mean.
Rewrite the summary in question 2 using .by instead of group_by().

7 Frequencies and proportions

7.1 Count observations with `count()`

Problem: How many penguins belong to each species?

penguins |>
  count(species)

# A tibble: 3 × 2
  species       n
  <fct>     <int>
1 Adelie      152
2 Chinstrap    68
3 Gentoo      124

The output column n contains the frequency. Use name to rename it and sort = TRUE to display the most frequent categories first.

penguins |>
  count(species, name = "freq", sort = TRUE)

# A tibble: 3 × 2
  species    freq
  <fct>     <int>
1 Adelie      152
2 Gentoo      124
3 Chinstrap    68

7.2 Relative frequencies

Problem: What proportion of the observations belongs to each species?

penguins |>
  count(species) |>
  mutate(prop = n / sum(n))

# A tibble: 3 × 3
  species       n  prop
  <fct>     <int> <dbl>
1 Adelie      152 0.442
2 Chinstrap    68 0.198
3 Gentoo      124 0.360

The denominator is the total number of observations. The proportions sum to 1.

7.3 Count a newly created category

penguins |>
  mutate(
    flip_size = if_else(flipper_length_mm > 210, "large", "short")
  ) |>
  count(flip_size)

# A tibble: 3 × 2
  flip_size     n
  <chr>     <int>
1 large       100
2 short       242
3 <NA>          2

count() includes missing values as a separate category. Here, NA means flipper length was missing, not that the penguin had a short flipper.

penguins |>
  mutate(
    mass_c = case_when(
      body_mass_g > 4500 ~ "large",
      body_mass_g > 3000 & body_mass_g <= 4500 ~ "medium",
      body_mass_g <= 3000 ~ "small"
    )
  ) |>
  count(mass_c)

# A tibble: 4 × 2
  mass_c     n
  <chr>  <int>
1 large    115
2 medium   216
3 small     11
4 <NA>       2

7.4 Joint frequencies

Problem: Count each combination of species and year.

penguins |>
  count(species, year, sort = TRUE)

# A tibble: 9 × 3
  species    year     n
  <fct>     <int> <int>
1 Adelie     2009    52
2 Adelie     2007    50
3 Adelie     2008    50
4 Gentoo     2008    46
5 Gentoo     2009    44
6 Gentoo     2007    34
7 Chinstrap  2007    26
8 Chinstrap  2009    24
9 Chinstrap  2008    18

distinct(species, year) lists the combinations; count(species, year) also reports how often each combination occurs.

7.5 Joint proportions: use the grand total

penguins |>
  count(species, year) |>
  mutate(prop = n / sum(n))

# A tibble: 9 × 4
  species    year     n   prop
  <fct>     <int> <int>  <dbl>
1 Adelie     2007    50 0.145 
2 Adelie     2008    50 0.145 
3 Adelie     2009    52 0.151 
4 Chinstrap  2007    26 0.0756
5 Chinstrap  2008    18 0.0523
6 Chinstrap  2009    24 0.0698
7 Gentoo     2007    34 0.0988
8 Gentoo     2008    46 0.134 
9 Gentoo     2009    44 0.128

Each proportion is the fraction of all penguins in that species–year cell. All cell proportions together sum to 1.

7.6 Conditional proportions: within species

Problem: Within each species, what proportion was observed in each year?

penguins |>
  count(species, year) |>
  mutate(prop = n / sum(n), .by = species)

# A tibble: 9 × 4
  species    year     n  prop
  <fct>     <int> <int> <dbl>
1 Adelie     2007    50 0.329
2 Adelie     2008    50 0.329
3 Adelie     2009    52 0.342
4 Chinstrap  2007    26 0.382
5 Chinstrap  2008    18 0.265
6 Chinstrap  2009    24 0.353
7 Gentoo     2007    34 0.274
8 Gentoo     2008    46 0.371
9 Gentoo     2009    44 0.355

The denominator is the total for that species. Proportions sum to 1 within each species across years.

7.7 Conditional proportions: within year

Problem: Within each year, what proportion belongs to each species?

penguins |>
  count(species, year) |>
  mutate(prop = n / sum(n), .by = year)

# A tibble: 9 × 4
  species    year     n  prop
  <fct>     <int> <int> <dbl>
1 Adelie     2007    50 0.455
2 Adelie     2008    50 0.439
3 Adelie     2009    52 0.433
4 Chinstrap  2007    26 0.236
5 Chinstrap  2008    18 0.158
6 Chinstrap  2009    24 0.2  
7 Gentoo     2007    34 0.309
8 Gentoo     2008    46 0.404
9 Gentoo     2009    44 0.367

The denominator is the total for that year. Proportions sum to 1 within each year across species.

7.8 Which denominator answers the question?

Proportion	Denominator	Sums to 1 over
Joint	Grand total	All species–year cells
Conditional on species	Species total	Years within each species
Conditional on year	Year total	Species within each year

A marginal distribution summarises one variable over the other. For example, count(species) |> mutate(prop = n / sum(n)) gives the marginal distribution of species. Within-group proportions are conditional.

7.9 The same results as contingency tables

The tables below display the results just calculated. Table-formatting code is hidden to keep the focus on count() and the choice of denominator.

Joint frequencies
year	Adelie	Chinstrap	Gentoo
2007	50	26	34
2008	50	18	46
2009	52	24	44

Each cell below shows its frequency followed by its proportion in parentheses.

Frequency and overall proportions

Joint proportions
year	Adelie	Chinstrap	Gentoo
2007	50 (0.145)	26 (0.076)	34 (0.099)
2008	50 (0.145)	18 (0.052)	46 (0.134)
2009	52 (0.151)	24 (0.070)	44 (0.128)

Frequency and proportions within species

Conditional on species
year	Adelie	Chinstrap	Gentoo
2007	50 (0.329)	26 (0.382)	34 (0.274)
2008	50 (0.329)	18 (0.265)	46 (0.371)
2009	52 (0.342)	24 (0.353)	44 (0.355)

Frequency and proportions within year

Conditional on year
year	Adelie	Chinstrap	Gentoo
2007	50 (0.455)	26 (0.236)	34 (0.309)
2008	50 (0.439)	18 (0.158)	46 (0.404)
2009	52 (0.433)	24 (0.200)	44 (0.367)

7.10 Exercise 6: frequencies and proportions

Find the frequency and relative frequency of each island.
Count penguins by species and sex, retaining missing sex as a separate category.
Using those counts, calculate the proportions of sex categories within each species.
Explain how the denominator changes if .by = species is omitted.

8 Repeating operations across columns

8.1 The repeated-code approach

Problem: Find the mean of each bill and flipper measurement by species.

penguins |>
  summarise(
    bill_length_mean = mean(bill_length_mm, na.rm = TRUE),
    bill_depth_mean = mean(bill_depth_mm, na.rm = TRUE),
    flipper_length_mean = mean(flipper_length_mm, na.rm = TRUE),
    .by = species
  )

# A tibble: 3 × 4
  species   bill_length_mean bill_depth_mean flipper_length_mean
  <fct>                <dbl>           <dbl>               <dbl>
1 Adelie                38.8            18.3                190.
2 Gentoo                47.5            15.0                217.
3 Chinstrap             48.8            18.4                196.

8.2 Use `across()` to avoid repetition

penguins |>
  summarise(
    across(
      .cols = ends_with("mm"),
      .fns = \(x) mean(x, na.rm = TRUE)
    ),
    .by = species
  )

# A tibble: 3 × 4
  species   bill_length_mm bill_depth_mm flipper_length_mm
  <fct>              <dbl>         <dbl>             <dbl>
1 Adelie              38.8          18.3              190.
2 Gentoo              47.5          15.0              217.
3 Chinstrap           48.8          18.4              196.

.cols chooses columns, using the same helpers as select().
.fns specifies the function to apply to each selected column.
\(x) is shorthand for function(x).
The values match the previous example; this version retains the original column names.

8.3 Other column selections and function notation

Select columns by position and calculate their standard deviations:

penguins |>
  summarise(across(3:6, \(x) sd(x, na.rm = TRUE)))

# A tibble: 1 × 4
  bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
           <dbl>         <dbl>             <dbl>       <dbl>
1           5.46          1.97              14.1        802.

You may also see formula notation; .x represents the current column:

penguins |>
  summarise(across(3:6, ~sd(.x, na.rm = TRUE)))

8.4 `across()` inside `summarise()` or `mutate()`

penguins |>
  summarise(across(where(is.numeric), \(x) mean(x, na.rm = TRUE)))

# A tibble: 1 × 5
  bill_length_mm bill_depth_mm flipper_length_mm body_mass_g  year
           <dbl>         <dbl>             <dbl>       <dbl> <dbl>
1           43.9          17.2              201.       4202. 2008.

This returns one row of means for all numeric columns, including year. Select measurement columns explicitly when a mean year is not meaningful.

penguins |>
  mutate(across(where(is.numeric), \(x) mean(x, na.rm = TRUE)))

# A tibble: 344 × 8
   species island    bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
   <fct>   <fct>              <dbl>         <dbl>             <dbl>       <dbl>
 1 Adelie  Torgersen           43.9          17.2              201.       4202.
 2 Adelie  Torgersen           43.9          17.2              201.       4202.
 3 Adelie  Torgersen           43.9          17.2              201.       4202.
 4 Adelie  Torgersen           43.9          17.2              201.       4202.
 5 Adelie  Torgersen           43.9          17.2              201.       4202.
 6 Adelie  Torgersen           43.9          17.2              201.       4202.
 7 Adelie  Torgersen           43.9          17.2              201.       4202.
 8 Adelie  Torgersen           43.9          17.2              201.       4202.
 9 Adelie  Torgersen           43.9          17.2              201.       4202.
10 Adelie  Torgersen           43.9          17.2              201.       4202.
# ℹ 334 more rows
# ℹ 2 more variables: sex <fct>, year <dbl>

This keeps all 344 rows but replaces every numeric column with its mean, repeated down the column. It does not merely fill missing values. The original penguins object remains unchanged because we did not assign the result.

8.5 Exercise 7: `across()`

Find the standard deviation of every column ending in "mm", by species.
Convert all columns ending in "mm" to centimetres using mutate(across(...)). Explain why their column names would then need updating.

9 Putting it together

9.1 Final practice

Using one pipeline, start with penguins and:

Keep female penguins from Dream or Biscoe Island.
Create body_mass_kg from body_mass_g.
Find mean body mass in kg for each species, excluding missing measurements.
Arrange the summary from largest to smallest mean.

9.2 Choose the verb by the task

Keep rows: filter(); choose row positions or extremes: slice() and its helpers.
Sort rows: arrange(); list unique combinations: distinct().
Keep columns: select(); change names: rename().
Create or transform columns: mutate().
Reduce to summaries: summarise(), with group_by() or .by when needed.
Count categories: count(); choose the denominator before computing proportions.
Repeat an operation over columns: across().