5 Data transformation (I): filtering, arranging, selecting and mutating

Objectives

Understand the purpose of data transformation. You rarely get data in exactly the form needed for analysis. Transformation involves creating new variables, reordering or selecting observations, and renaming columns.
Use dplyr verbs to manipulate rows and columns. Learn filter() to subset rows, distinct() to remove duplicates, arrange() to reorder rows, select(), rename() and relocate() to choose, rename and reposition variables, and mutate() to create new columns.
Chain operations with the pipe. Use the pipe (|> in base R, or %>% from magrittr) to express sequences of transformations in a readable way.
Recognize a handful of correctness traps. dplyr is forgiving about syntax but not about logic; you will see a few specific ways a pipeline can run without error and still silently produce the wrong answer.

Notes

library(tidyverse)
library(palmerpenguins)

Every dplyr verb works the same way: the first argument is always a data frame, the rest of the arguments describe what to do using the (unquoted) column names, and the result is always a new data frame. dplyr never modifies its input in place, so filter(penguins, ...) leaves the original penguins completely untouched unless you explicitly reassign the result to a name.

Picking rows with `filter()`

filter() keeps only the rows where a logical condition is TRUE.

adelie_dream <- penguins |>
  filter(species == "Adelie", island == "Dream")

adelie_dream |> head()

# A tibble: 6 × 8
  species island bill_length_mm bill_depth_mm flipper_length_mm body_mass_g
  <fct>   <fct>           <dbl>         <dbl>             <int>       <int>
1 Adelie  Dream            39.5          16.7               178        3250
2 Adelie  Dream            37.2          18.1               178        3900
3 Adelie  Dream            39.5          17.8               188        3300
4 Adelie  Dream            40.9          18.9               184        3900
5 Adelie  Dream            36.4          17                 195        3325
6 Adelie  Dream            39.2          21.1               196        4150
# ℹ 2 more variables: sex <fct>, year <int>

Separating conditions with a comma (as above) means “and”: both conditions must hold. You can combine conditions explicitly with & (and), | (or), and check membership in a set of values with %in%, which is usually clearer than a long chain of | comparisons against the same variable.

# every penguin that is either an Adelie or a Chinstrap
adelie_or_chinstrap <- penguins |>
  filter(species %in% c("Adelie", "Chinstrap"))

nrow(adelie_or_chinstrap)

[1] 220

= is not ==

Inside filter(), a single = does not test equality; it tries to name an argument, and dplyr will stop you with an error rather than silently doing the wrong thing (see Example 4.1). Always use == to compare values.

Removing duplicate rows with `distinct()`

distinct() keeps only the unique combinations of the columns you name, dropping every duplicate after the first.

# every combination of species and island actually observed in the data
penguins |> distinct(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

Add .keep_all = TRUE if you want to keep the first full row for each unique combination, rather than just the columns you named.

Reordering rows with `arrange()`

arrange() sorts rows by one or more columns, ascending by default; wrap a column in desc() to sort it in descending order instead. arrange() only changes the order of the rows; it never adds or removes any.

penguins_sorted <- penguins |>
  arrange(desc(body_mass_g), flipper_length_mm)

penguins_sorted |> head()

# A tibble: 6 × 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
5 Gentoo  Biscoe           45.2          16.4               223        5950
6 Gentoo  Biscoe           49.8          15.9               229        5950
# ℹ 2 more variables: sex <fct>, year <int>

Missing values always sort to the end

Regardless of whether you sort ascending or with desc(), rows with NA in the sorting column are always placed last. arrange(desc(x)) does not mean “biggest first, then NA, then smallest;” it means “biggest first, then smallest, then NA.”

Picking, dropping and renaming columns

select() narrows a data frame down to the columns you want, in the order you name them. It understands ranges (bill_length_mm:bill_depth_mm), negation (- or !), and helper functions like starts_with(), ends_with(), contains(), and where() (for selecting by a column’s type or a condition, such as where(is.numeric)).

penguins |> select(species, island, body_mass_g) |> head()

# A tibble: 6 × 3
  species island    body_mass_g
  <fct>   <fct>           <int>
1 Adelie  Torgersen        3750
2 Adelie  Torgersen        3800
3 Adelie  Torgersen        3250
4 Adelie  Torgersen          NA
5 Adelie  Torgersen        3450
6 Adelie  Torgersen        3650

# every column except the two bill measurements
penguins |> select(-(bill_length_mm:bill_depth_mm)) |> head()

# A tibble: 6 × 6
  species island    flipper_length_mm body_mass_g sex     year
  <fct>   <fct>                 <int>       <int> <fct>  <int>
1 Adelie  Torgersen               181        3750 male    2007
2 Adelie  Torgersen               186        3800 female  2007
3 Adelie  Torgersen               195        3250 female  2007
4 Adelie  Torgersen                NA          NA <NA>    2007
5 Adelie  Torgersen               193        3450 female  2007
6 Adelie  Torgersen               190        3650 male    2007

You can rename a column while selecting it, with the new name on the left of =:

penguins |> select(flipper_mm = flipper_length_mm, species) |> head()

# A tibble: 6 × 2
  flipper_mm species
       <int> <fct>  
1        181 Adelie 
2        186 Adelie 
3        195 Adelie 
4         NA Adelie 
5        193 Adelie 
6        190 Adelie

If you want to rename a column without dropping every other column, use rename() instead of select():

penguins |> rename(flipper_mm = flipper_length_mm) |> head()

# A tibble: 6 × 8
  species island bill_length_mm bill_depth_mm flipper_mm body_mass_g sex    year
  <fct>   <fct>           <dbl>         <dbl>      <int>       <int> <fct> <int>
1 Adelie  Torge…           39.1          18.7        181        3750 male   2007
2 Adelie  Torge…           39.5          17.4        186        3800 fema…  2007
3 Adelie  Torge…           40.3          18          195        3250 fema…  2007
4 Adelie  Torge…           NA            NA           NA          NA <NA>   2007
5 Adelie  Torge…           36.7          19.3        193        3450 fema…  2007
6 Adelie  Torge…           39.3          20.6        190        3650 male   2007

relocate() moves columns to a new position without changing which columns are present, using .before or .after to say where:

penguins |> relocate(body_mass_g, .before = species) |> head()

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

Creating new columns with `mutate()`

mutate() adds new columns that are computed from existing ones, and you can refer to a column created earlier in the same mutate() call.

penguins |>
  mutate(
    bill_ratio = bill_length_mm / bill_depth_mm,
    heavy = body_mass_g > 4500
  ) |>
  head()

# A tibble: 6 × 10
  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
# ℹ 4 more variables: sex <fct>, year <int>, bill_ratio <dbl>, heavy <lgl>

New columns are added at the end by default; use .before or .after (just like relocate()) to control where they land, or .keep = "used" if you only want to keep the columns that went into computing the new one, which is handy for checking your work.

penguins |>
  mutate(bill_ratio = bill_length_mm / bill_depth_mm, .keep = "used") |>
  head()

# A tibble: 6 × 3
  bill_length_mm bill_depth_mm bill_ratio
           <dbl>         <dbl>      <dbl>
1           39.1          18.7       2.09
2           39.5          17.4       2.27
3           40.3          18         2.24
4           NA            NA        NA   
5           36.7          19.3       1.90
6           39.3          20.6       1.91

If you only want to keep the newly created columns (and any grouping columns) and drop everything else, transmute() does that in one step.

Combining steps with the pipe

Stringing several verbs together by saving an intermediate object after each one is verbose, and re-using the same name for each intermediate step (as the previous version of these notes did) risks exactly the shadowing problem in Example 4.4. The pipe, |>, passes the result on its left into the first argument of the call on its right, so an entire sequence of transformations reads top to bottom as a single pipeline instead:

tall_penguins <- penguins |>
  filter(!is.na(body_mass_g)) |>
  mutate(bill_ratio = bill_length_mm / bill_depth_mm) |>
  select(species, island, body_mass_g, bill_ratio) |>
  arrange(desc(body_mass_g))

tall_penguins |> head()

# A tibble: 6 × 4
  species island body_mass_g bill_ratio
  <fct>   <fct>        <int>      <dbl>
1 Gentoo  Biscoe        6300       3.24
2 Gentoo  Biscoe        6050       3.51
3 Gentoo  Biscoe        6000       3.13
4 Gentoo  Biscoe        6000       3.01
5 Gentoo  Biscoe        5950       2.76
6 Gentoo  Biscoe        5950       3.13

Read the pipe as “then”: filter, then mutate, then select, then arrange. Each step’s output becomes the next step’s input, and nothing is overwritten along the way until the very last line assigns the final result to tall_penguins.

Here is the same pattern applied to a dataset from outside palmerpenguins, to get some practice on data you have not seen built into a package before:

stress <- read_csv("data/student_stress_survey.csv")

stress |>
  filter(study_hours > 10) |>
  mutate(hours_per_stress_point = study_hours / stress_level) |>
  select(major, study_hours, stress_level, hours_per_stress_point) |>
  arrange(desc(hours_per_stress_point)) |>
  head()

# A tibble: 6 × 4
  major    study_hours stress_level hours_per_stress_point
  <chr>          <dbl>        <dbl>                  <dbl>
1 Biology         17.4            1                   17.4
2 Nursing         16.7            1                   16.7
3 Business        16.7            1                   16.7
4 Business        16.0            1                   16.0
5 Business        15.8            1                   15.8
6 Biology         15.0            1                   15.0

Fringe cases and common pitfalls

Example 4.1

filter(x = value) does not silently do the wrong thing; it stops you.

filter(penguins, species = "Adelie")

Error in `filter()`:
! We detected a named input.
ℹ This usually means that you've used `=` instead of `==`.
ℹ Did you mean `species == "Adelie"`?

Modern dplyr specifically detects this mistake and refuses to guess what you meant, offering the exact fix in the error message itself. Older versions of dplyr (and many other R functions in general) are not always this forgiving, so it is worth building the habit of reading == for “is equal to” and = only for naming an argument or assigning a value.

Example 4.2

!= quietly drops missing values along with everything else.

penguins$sex has a handful of genuinely missing values. Suppose you want every penguin that is not recorded as male, expecting that to include both females and unknowns:

sum(is.na(penguins$sex))          # how many sex values are missing?

[1] 11

not_male <- penguins |> filter(sex != "male")
nrow(not_male)                    # fewer rows than you might expect

[1] 165

sex != "male" evaluates to NA, not TRUE, for every row where sex is already NA, because R cannot know whether an unknown value is or is not equal to "male". filter() keeps only rows where the condition is TRUE, so NA rows are silently excluded, the same way they were silently excluded from the plots in Session 3. If you want to keep the missing values too, you have to ask for them explicitly:

not_male_or_unknown <- penguins |> filter(sex != "male" | is.na(sex))
nrow(not_male_or_unknown)         # now includes the missing-sex penguins

[1] 176

Any time you filter with !=, <, or > on a column that might contain NA, decide on purpose whether missing values belong in your result, rather than letting filter() decide for you.

Example 4.3

Floating-point rounding error (from Session 1) can make filter() miss a row that looks like an exact match.

example_data <- tibble(y = 0.1 + 0.2)
example_data$y                          # prints as 0.3

[1] 0.3

example_data |> filter(y == 0.3)        # 0 rows: not actually equal underneath

# A tibble: 0 × 1
# ℹ 1 variable: y <dbl>

example_data |> filter(near(y, 0.3))    # 1 row: near() allows for tiny rounding error

# A tibble: 1 × 1
      y
  <dbl>
1   0.3

This is the exact same floating-point issue from Session 1’s 0.1 + 0.2 == 0.3, just showing up inside a filter() instead of at the console. Whenever you filter on an exact match to a computed decimal value, prefer dplyr’s near() over ==.

Example 4.4

Reassigning a built-in dataset’s own name hides your changes from yourself.

An earlier version of these notes did the following, one step at a time:

penguins <- penguins |> rename(flipper_mm = flipper_length_mm)
penguins <- penguins |> mutate(bill_size = bill_length_mm + bill_depth_mm)

penguins2 <- penguins |> rename(flipper_mm = flipper_length_mm)
"flipper_length_mm" %in% names(penguins2)  # gone

[1] FALSE

"flipper_mm" %in% names(penguins2)         # here instead

[1] TRUE

The moment you run penguins <- penguins |> rename(...), the original palmerpenguins::penguins is shadowed by your modified version for the rest of the R session. Every chunk after that point that refers to penguins gets your renamed copy, not the original, which is easy to forget an hour (or a week) later. If you restart R (as recommended in Session 1) the shadowing disappears and old code that relied on the renamed column suddenly breaks, while code that still expects the original name works again. Assigning transformed data to a new name, such as penguins_renamed, avoids the problem entirely and makes it obvious at a glance which object is the original and which is derived.

Recap

Term	Definition
`filter()`	Keeps rows where a logical condition is `TRUE`; drops rows where it is `FALSE` or `NA`.
`%in%`	Tests membership in a set of values; clearer than chaining several `\|` comparisons on one variable.
`distinct()`	Keeps only unique combinations of the named columns.
`arrange()`	Reorders rows by one or more columns; `NA` always sorts last regardless of direction.
`select()`	Narrows and reorders columns; supports ranges, negation, and helpers like `starts_with()` and `where()`.
`rename()`	Renames specific columns while keeping every other column.
`relocate()`	Moves columns to a new position with `.before` or `.after`, without changing which columns exist.
`mutate()`	Adds new columns computed from existing ones; `.before`, `.after`, and `.keep` control placement and what survives.
`transmute()`	Like `mutate()`, but keeps only the newly created (and grouping) columns.
The pipe (`\|>`)	Passes the result on its left into the first argument on its right; read it as “then.”
Floating-point filtering	Use `near()` instead of `==` when filtering on a computed decimal value.

Check your understanding

Problems

What is the difference between filter() and arrange()? Can either one change how many rows are in the result?
You write filter(df, score = 90) and R stops with an error instead of running. What mistake did you make, and how do you fix it?
A dataset has a region column with a few missing values. You run filter(df, region != "West") expecting to get everyone outside the West region, including anyone with an unknown region. Explain why the missing-region rows are excluded, and how you would include them.
Why does dplyr’s near() function exist, and when should you reach for it instead of ==?
Your classmate writes penguins <- penguins |> mutate(mass_kg = body_mass_g / 1000) at the top of their script, then spends twenty minutes confused about why a later chunk that expects the original palmerpenguins::penguins gives strange results. What happened, and what habit would prevent it?

Solutions

filter() selects which rows are present in the result and can reduce the number of rows (or leave it unchanged if every row matches). arrange() only changes the order of the rows; it always returns the same number of rows it was given.
Inside filter(), = names an argument rather than testing equality, so score = 90 is not a valid comparison. dplyr detects this specific mistake and errors instead of guessing. The fix is to use ==: filter(df, score == 90).
region != "West" evaluates to NA, not TRUE, for any row where region is already missing, because R cannot determine whether an unknown region is or is not “West.” Since filter() only keeps rows where the condition is exactly TRUE, the missing-region rows are dropped along with the true non-matches. To include them, add an explicit is.na() check: filter(df, region != "West" | is.na(region)).
near() compares two numbers while allowing for a small amount of floating-point rounding error, which real decimal arithmetic almost always introduces. Use it instead of == whenever you are filtering (or otherwise comparing) a numeric column that was computed rather than typed in directly, since two mathematically equal computed values can differ by a tiny amount at the level of their exact binary representation.
Their line reassigns the name penguins to a modified copy of the data, so every subsequent chunk that refers to penguins sees the modified version (with the new mass_kg column) instead of the original palmerpenguins::penguins. Assigning the transformed data to a new name, such as penguins_kg, keeps the original available and makes it clear which object is which.

Objectives

Notes

Picking rows with filter()

Removing duplicate rows with distinct()

Reordering rows with arrange()

Picking, dropping and renaming columns

Creating new columns with mutate()

Combining steps with the pipe

Fringe cases and common pitfalls

Recap

Check your understanding

Picking rows with `filter()`

Removing duplicate rows with `distinct()`

Reordering rows with `arrange()`

Creating new columns with `mutate()`