14  Data transformation

Are Gentoo penguins heavier than Adelie penguins? The penguins data has the answer, but getting there means filtering rows, picking columns, computing a mean, and grouping by species. Every data analysis you will ever write is some arrangement of those same moves, and the question that shapes this chapter is: what is the smallest set of operations that covers all of them?

14.1 The idea: verbs for data

library(palmerpenguins)
#> 
#> Attaching package: 'palmerpenguins'
#> The following objects are masked from 'package:datasets':
#> 
#>     penguins, penguins_raw
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

The penguins data frame (loaded by palmerpenguins) has 344 rows and 8 columns: species, island, bill measurements, flipper length, body mass, sex, and year. Every example in this chapter uses it.

penguins
#> # 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  Torger…           39.1          18.7               181        3750
#>  2 Adelie  Torger…           39.5          17.4               186        3800
#>  3 Adelie  Torger…           40.3          18                 195        3250
#>  4 Adelie  Torger…           NA            NA                  NA          NA
#>  5 Adelie  Torger…           36.7          19.3               193        3450
#>  6 Adelie  Torger…           39.3          20.6               190        3650
#>  7 Adelie  Torger…           38.9          17.8               181        3625
#>  8 Adelie  Torger…           39.2          19.6               195        4675
#>  9 Adelie  Torger…           34.1          18.1               193        3475
#> 10 Adelie  Torger…           42            20.2               190        4250
#> # ℹ 334 more rows
#> # ℹ 2 more variables: sex <fct>, year <int>

To answer the opening question you keep the rows that have a body mass, split them by species, and average. dplyr gives each of those moves its own function, and every one of them takes a data frame as its first argument and returns a data frame. filter() keeps rows that match a condition, select() picks columns by name, mutate() adds columns computed from existing ones, summarise() collapses rows into a summary, and arrange() reorders them. Because each verb hands back the same kind of object it received, you can chain them (via pipes, Chapter 15), and each one does exactly one thing.

NoteCodd’s algebra

These verbs did not come from nowhere. The 1970 paper that introduced the relational model (Section 11.1) also defined a relational algebra: a small set of operations on tables where every operation takes a table in and returns a table out, among them restriction (keep rows) and projection (keep columns). IBM resisted at first, because it had a competing database product called IMS. The composability was too useful, and by the 1980s relational databases had won.

dplyr is Codd’s algebra with friendlier names. The same ideas appear in SQL, pandas, and data.table because the operations are the same; only the syntax differs. arrange() is the one exception: ordering has no relational equivalent, but you need it for display.

14.2 filter(): keep rows

You have 344 penguins. You only care about the Adelie ones. How do you throw the rest away?

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

The logical expression works the same way as in Section 8.2. species here refers to the column, not to anything in your global environment: dplyr evaluates the expression inside the data frame.

Multiple conditions, separated by commas, are combined with AND:

filter(penguins, species == "Adelie", body_mass_g > 4000)

This keeps only Adelie penguins heavier than 4000 grams. You can also write & explicitly, but the comma is cleaner.

For OR, use |:

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

When testing membership in a set, %in% is clearer than chaining |:

filter(penguins, species %in% c("Adelie", "Chinstrap"))

One detail worth knowing: filter() drops rows where the condition evaluates to NA. If body_mass_g is NA for a row, then body_mass_g > 4000 is NA, and filter() excludes it. This is usually what you want, but if you need to keep NA rows, test for them explicitly: filter(penguins, is.na(body_mass_g)).

filter() is the logical subsetting from Section 11.3.2, penguins[penguins$species == "Adelie", ], with the intent in the function name and a result the next verb can take as input. But keeping rows is only half the story; what about columns you don’t need?

Exercises

1. Filter penguins to keep only female penguins from Biscoe island.
2. Filter penguins to keep rows where flipper_length_mm is greater than 200 or body_mass_g is less than 3000.
3. How many rows have missing values in sex? Use filter() and nrow().

14.3 select(): pick columns

Eight columns is manageable. Eighty is not. When you only need species, bill length, and body mass, carrying around five extra columns clutters every printout, slows every join, and obscures what matters. select() strips a data frame down to the columns you name.

select(penguins, species, bill_length_mm, body_mass_g)
#> # A tibble: 344 × 3
#>    species bill_length_mm body_mass_g
#>    <fct>            <dbl>       <int>
#>  1 Adelie            39.1        3750
#>  2 Adelie            39.5        3800
#>  3 Adelie            40.3        3250
#>  4 Adelie            NA            NA
#>  5 Adelie            36.7        3450
#>  6 Adelie            39.3        3650
#>  7 Adelie            38.9        3625
#>  8 Adelie            39.2        4675
#>  9 Adelie            34.1        3475
#> 10 Adelie            42          4250
#> # ℹ 334 more rows

The rest are gone.

Drop columns by prefixing with -:

select(penguins, -year)

For more flexible selection, dplyr provides helper functions. starts_with(), ends_with(), and contains() match column names by pattern:

select(penguins, starts_with("bill"))

select(penguins, ends_with("mm"))

where() selects columns based on their content, not their name. where(is.numeric) keeps every numeric column:

select(penguins, species, where(is.numeric))

You can rename columns inside select():

select(penguins, species, mass = body_mass_g)
#> # A tibble: 344 × 2
#>    species  mass
#>    <fct>   <int>
#>  1 Adelie   3750
#>  2 Adelie   3800
#>  3 Adelie   3250
#>  4 Adelie     NA
#>  5 Adelie   3450
#>  6 Adelie   3650
#>  7 Adelie   3625
#>  8 Adelie   4675
#>  9 Adelie   3475
#> 10 Adelie   4250
#> # ℹ 334 more rows

This selects species and body_mass_g, renaming the latter to mass, and every other column is gone. If you want the new name and the other six columns, use rename():

rename(penguins, mass = body_mass_g)

To reorder columns (move species to the front, keep everything else), use everything():

select(penguins, island, species, everything())

Exercises

1. Select all columns from penguins that contain the string "length".
2. Select species, island, and all numeric columns. (Hint: combine names and where().)
3. What is the difference between select(penguins, mass = body_mass_g) and rename(penguins, mass = body_mass_g)? Try both.

14.4 mutate(): compute new columns

Body mass is recorded in grams, but your collaborator’s model expects kilograms.

mutate(penguins, 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  Torger…           39.1          18.7               181        3750
#>  2 Adelie  Torger…           39.5          17.4               186        3800
#>  3 Adelie  Torger…           40.3          18                 195        3250
#>  4 Adelie  Torger…           NA            NA                  NA          NA
#>  5 Adelie  Torger…           36.7          19.3               193        3450
#>  6 Adelie  Torger…           39.3          20.6               190        3650
#>  7 Adelie  Torger…           38.9          17.8               181        3625
#>  8 Adelie  Torger…           39.2          19.6               195        4675
#>  9 Adelie  Torger…           34.1          18.1               193        3475
#> 10 Adelie  Torger…           42            20.2               190        4250
#> # ℹ 334 more rows
#> # ℹ 3 more variables: sex <fct>, year <int>, mass_kg <dbl>

You can reference a column you just created in the same mutate() call:

mutate(
  penguins,
  mass_kg = body_mass_g / 1000,
  mass_lb = mass_kg * 2.205
)

mass_lb uses mass_kg, which was defined one line above. In a plain function call the second argument cannot see the first; mutate() evaluates its arguments in order, top to bottom, so each expression can use every column defined above it. Reverse the two lines and mass_lb fails, because mass_kg does not exist yet.

Any function that works on vectors works inside mutate(): log(), sqrt(), cumsum(), lag(), lead(), percent_rank(). The conditional functions from Section 8.5 work here too:

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

For more than two categories, case_when() is the tool:

mutate(
  penguins,
  size = case_when(
    body_mass_g > 5000 ~ "large",
    body_mass_g > 3500 ~ "medium",
    .default = "small"
  )
)

If you want to keep only the new columns and drop everything else, use .keep = "none":

mutate(
  penguins,
  mass_kg = body_mass_g / 1000,
  .keep = "none"
)
#> # A tibble: 344 × 1
#>    mass_kg
#>      <dbl>
#>  1    3.75
#>  2    3.8 
#>  3    3.25
#>  4   NA   
#>  5    3.45
#>  6    3.65
#>  7    3.62
#>  8    4.68
#>  9    3.48
#> 10    4.25
#> # ℹ 334 more rows

Exercises

1. Add a column bill_ratio that divides bill_length_mm by bill_depth_mm.
2. Use mutate() and case_when() to create a column island_code that maps "Biscoe" to "B", "Dream" to "D", and "Torgersen" to "T".
3. What happens if you use a column name that already exists in mutate()? Try mutate(penguins, species = toupper(species)).

14.5 summarise() and group_by(): aggregate

What is the average body mass of a penguin in this dataset?

summarise(penguins, mean_mass = mean(body_mass_g, na.rm = TRUE))
#> # A tibble: 1 × 1
#>   mean_mass
#>       <dbl>
#> 1     4202.

One row, one number. The opening question asked for the mean per species, and summarise() on its own has no idea what a species is.

penguins_grouped <- group_by(penguins, species)
summarise(penguins_grouped, mean_mass = mean(body_mass_g, na.rm = TRUE))
#> # A tibble: 3 × 2
#>   species   mean_mass
#>   <fct>         <dbl>
#> 1 Adelie        3701.
#> 2 Chinstrap     3733.
#> 3 Gentoo        5076.

The summarise() call is unchanged and now returns three rows, one per species. group_by() did not touch the data; it attached metadata that tells subsequent verbs to operate per group, and summarise() ran mean() once inside each. Print penguins_grouped and the only visible difference is a Groups: line in the header.

NoteGrouping in other languages

SQL spells this GROUP BY; Spark uses explicit partitioning; in functional programming the same idea appears as foldByKey, where a reduce operation runs per partition rather than globally. The underlying pattern, an aggregation function paired with a partitioning structure, is the same across all of them.

You can group by multiple variables:

summarise(
  group_by(penguins, species, island),
  mean_mass = mean(body_mass_g, na.rm = TRUE),
  count = n()
)
#> `summarise()` has regrouped the output.
#> ℹ Summaries were computed grouped by species and island.
#> ℹ Output is grouped by species.
#> ℹ Use `summarise(.groups = "drop_last")` to silence this message.
#> ℹ Use `summarise(.by = c(species, island))` for per-operation grouping
#>   (`?dplyr::dplyr_by`) instead.
#> # A tibble: 5 × 4
#> # Groups:   species [3]
#>   species   island    mean_mass count
#>   <fct>     <fct>         <dbl> <int>
#> 1 Adelie    Biscoe        3710.    44
#> 2 Adelie    Dream         3688.    56
#> 3 Adelie    Torgersen     3706.    52
#> 4 Chinstrap Dream         3733.    68
#> 5 Gentoo    Biscoe        5076.   124

summarise() prints a message this time: it removed the last grouping variable, island, and the result is still grouped by species. That leftover grouping is easy to forget about, and the Opinion at the end of this section says what to do with it.

n() counts the number of rows in each group; n_distinct() counts unique values:

summarise(
  group_by(penguins, species),
  n_islands = n_distinct(island)
)
#> # A tibble: 3 × 2
#>   species   n_islands
#>   <fct>         <int>
#> 1 Adelie            3
#> 2 Chinstrap         1
#> 3 Gentoo            1

The shortcut count() does group_by() plus summarise(n = n()) in one step:

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

TipOpinion

After summarise(), the last grouping variable is dropped and the result may still be grouped by the others. A mutate() you expect to operate on the full data frame then operates per group instead. Add .groups = "drop" to summarise(), or call ungroup() afterward.

Exercises

1. Compute the median flipper_length_mm for each species.
2. Use group_by() and summarise() to find the maximum bill_length_mm per species and island combination.
3. Use count() to find how many penguins were observed in each year.

14.6 arrange(): order rows

arrange() sorts rows. The default is ascending:

arrange(penguins, body_mass_g)
#> # 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 Chinstr… 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
#>  4 Adelie   Biscoe           34.5          18.1               187        2900
#>  5 Adelie   Dream            33.1          16.1               178        2900
#>  6 Adelie   Torge…           38.6          17                 188        2900
#>  7 Chinstr… Dream            43.2          16.6               187        2900
#>  8 Adelie   Biscoe           37.9          18.6               193        2925
#>  9 Adelie   Dream            37.5          18.9               179        2975
#> 10 Adelie   Dream            37            16.9               185        3000
#> # ℹ 334 more rows
#> # ℹ 2 more variables: sex <fct>, year <int>

Wrap a column in desc() for descending order:

arrange(penguins, desc(body_mass_g))

Multiple columns break ties; sort by species alphabetically, then by mass descending within each species:

arrange(penguins, species, desc(body_mass_g))

Unlike the other verbs, arrange() ignores grouping by default. If you want to sort within groups, set .by_group = TRUE.

arrange() is pure display. No information is added or removed, only the order of rows changes. But a well-sorted table can reveal patterns that a jumbled one hides, and sometimes the five core verbs are not quite enough to expose them.

14.7 More verbs

distinct() returns unique rows:

distinct(penguins, species, island)
#> # A tibble: 5 × 2
#>   species   island   
#>   <fct>     <fct>    
#> 1 Adelie    Torgersen
#> 2 Adelie    Biscoe   
#> 3 Adelie    Dream    
#> 4 Gentoo    Biscoe   
#> 5 Chinstrap Dream

Five combinations of species and island exist in the data.

slice_head() and slice_tail() keep the first or last n rows:

slice_head(penguins, 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 Adelie  Torgers…           39.1          18.7               181        3750
#> 2 Adelie  Torgers…           39.5          17.4               186        3800
#> 3 Adelie  Torgers…           40.3          18                 195        3250
#> # ℹ 2 more variables: sex <fct>, year <int>

slice_max() and slice_min() keep the rows with the largest or smallest values:

slice_max(penguins, body_mass_g, n = 3)
#> # A tibble: 4 × 8
#>   species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
#>   <fct>   <fct>           <dbl>         <dbl>             <int>       <int>
#> 1 Gentoo  Biscoe           49.2          15.2               221        6300
#> 2 Gentoo  Biscoe           59.6          17                 230        6050
#> 3 Gentoo  Biscoe           51.1          16.3               220        6000
#> 4 Gentoo  Biscoe           48.8          16.2               222        6000
#> # ℹ 2 more variables: sex <fct>, year <int>

pull() extracts a single column as a vector, the pipe-friendly equivalent of $:

pull(penguins, species) |> head()
#> [1] Adelie Adelie Adelie Adelie Adelie Adelie
#> Levels: Adelie Chinstrap Gentoo

relocate() moves columns without dropping any. Move sex and year to the front:

relocate(penguins, sex, year)

Finally, across() applies a function to multiple columns at once. Summarise all numeric columns by their mean:

summarise(penguins, 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.

across() works inside mutate() too. Round every numeric column to one decimal place:

mutate(penguins, across(where(is.numeric), \(x) round(x, 1)))

across() pairs naturally with where(), starts_with(), and the other selection helpers. Chapter 19 generalizes the same idea (apply a function to each element of a collection) beyond data frames. But before you build elaborate pipelines out of these verbs, it is worth seeing what they look like without dplyr at all.

Exercises

1. Use distinct() to find all unique values of island.
2. Use slice_max() to find the 5 penguins with the longest flippers.
3. Use across() inside summarise() to compute the standard deviation (sd()) of every numeric column, grouped by species.

14.8 The base R equivalents

Everything dplyr does, base R can do too. The verbs are convenient wrappers, not new capabilities. So what exactly do you gain?

filter() is logical subsetting:

# dplyr
filter(penguins, species == "Adelie", body_mass_g > 4000)

# base R
penguins[penguins$species == "Adelie" & penguins$body_mass_g > 4000, ]

select() is column indexing:

# dplyr
select(penguins, species, body_mass_g)

# base R
penguins[, c("species", "body_mass_g")]

mutate() is direct assignment:

# dplyr
mutate(penguins, mass_kg = body_mass_g / 1000)

# base R
penguins$mass_kg <- penguins$body_mass_g / 1000

The base R version modifies the binding; the dplyr version returns a new data frame and leaves the original untouched. This distinction matters more as pipelines grow longer.

summarise() plus group_by() maps to aggregate():

# dplyr
summarise(group_by(penguins, species), mean_mass = mean(body_mass_g, na.rm = TRUE))

# base R
aggregate(body_mass_g ~ species, data = penguins, FUN = mean, na.rm = TRUE)

arrange() is order():

# dplyr
arrange(penguins, desc(body_mass_g))

# base R
penguins[order(-penguins$body_mass_g), ]

Base R also has subset(), which combines filtering and column selection:

subset(penguins, species == "Adelie", select = c(species, body_mass_g))

subset() is clean and readable; for simple operations, it works well. Where dplyr pulls ahead is composability: every verb takes a data frame and returns one, so you can chain them. Base R’s equivalents each return something slightly different: aggregate() renames your columns, order() gives you indices instead of a data frame. So you spend time wiring them together instead of thinking about your data.

dplyr and SQL implement the same operations, which is why a dplyr call has a line-for-line SQL translation:

dplyr	SQL
filter(penguins, species == "Adelie")	SELECT * FROM penguins WHERE species = 'Adelie'
group_by(species) |> summarise(n = n())	SELECT species, COUNT(*) AS n ... GROUP BY species

Call show_query() on a database-backed tibble to see the translation directly.

NoteVerb composition is a monoid

A dplyr pipeline is function composition: each verb transforms a data frame and passes it to the next, the same composition from lambda calculus, (g ∘ f)(x) = g(f(x)), applied to tables instead of numbers. This is the same monoid pattern from Section 4.1: an associative operation (verb composition) with an identity element (a verb that returns the data frame unchanged). Compose any sequence of dplyr verbs and the result is still a data frame.

TipOpinion

Learn the base R equivalents. You will encounter them in other people’s code, in Stack Overflow answers, in older packages. But write dplyr in your own work, because the verbs compose, the names communicate intent, and the argument order is consistent. When you read filter(penguins, species == "Adelie"), the operation is the function name. When you read penguins[penguins$species == "Adelie", ], you have to parse the indexing to understand what’s happening. That parsing cost, multiplied across every line of a script, is exactly the kind of friction Codd’s algebra was designed to remove.

Now you have every verb you need to answer the question from the start of the chapter:

penguins |>
  filter(!is.na(body_mass_g)) |>
  group_by(species) |>
  summarise(mean_mass = mean(body_mass_g))
#> # A tibble: 3 × 2
#>   species   mean_mass
#>   <fct>         <dbl>
#> 1 Adelie        3701.
#> 2 Chinstrap     3733.
#> 3 Gentoo        5076.

Gentoo penguins average roughly 5,076 grams; Adelie average 3,701. The answer was always in the data. Getting to it required four verbs and five lines.

Exercises

1. Rewrite select(penguins, species, island, body_mass_g) using base R bracket notation.
2. Rewrite arrange(penguins, bill_length_mm) using order().
3. Use aggregate() to compute the mean flipper_length_mm per island. Compare with the group_by() plus summarise() version.