Learning goals for today

  1. Review using:

    • slice() to subset rows by their order (index position) in the data table
    • group_by() and summarize() to create grouped summaries
    • Identifying missing and non-missing entries

  2. And get to know and use

    • The case_when() function to edit entries in a column
    • Reshaping tables with pivot_wider and pivot_longer

Saw Kill: cleaning and comparing observations

DATA 121, Thursday, October 29, 2026

Thursday lab

slice

To remove or keep certain row numbers

  • It can be done “positively”, to keep the stated rows: slice(18:200)

  • Or “negatively” to remove stated rows: slice(-(6:119390))

# first five
hotels |>
  slice(1:5)
# A tibble: 5 × 32
  hotel        is_canceled lead_time arrival_date_year arrival_date_month
  <chr>              <dbl>     <dbl>             <dbl> <chr>             
1 Resort Hotel           0       342              2015 July              
2 Resort Hotel           0       737              2015 July              
3 Resort Hotel           0         7              2015 July              
4 Resort Hotel           0        13              2015 July              
5 Resort Hotel           0        14              2015 July              
hotels |>
  slice(-(6:119390))
# A tibble: 5 × 32
  hotel        is_canceled lead_time arrival_date_year arrival_date_month
  <chr>              <dbl>     <dbl>             <dbl> <chr>             
1 Resort Hotel           0       342              2015 July              
2 Resort Hotel           0       737              2015 July              
3 Resort Hotel           0         7              2015 July              
4 Resort Hotel           0        13              2015 July              
5 Resort Hotel           0        14              2015 July

If/then in Dplyr to calculate grades

case_when(condition ~ output_value)

  • condition is the condition that evaluates as TRUE (the “if”)

  • output_value is the value to output if the condition is TRUE (the “then”)

df <- data.frame(
  student = c("Natascha", "Alex", "Arun", "Arturo", "Ashley", "Oscar", "James", "Elliot"),
  score = c(92, 78, 85, 86, 93, 67, 56, 73))

df %>%
  mutate(grade = case_when(
    score >= 90 ~ 'A',
    score >= 80 ~ 'B',
    score >= 70 ~ 'C',
    score >= 60 ~ 'D',
    TRUE ~ 'F'))
   student score grade
1 Natascha    92     A
2     Alex    78     C
3     Arun    85     B
4   Arturo    86     B
5   Ashley    93     A
6    Oscar    67     D
7    James    56     F
8   Elliot    73     C

Replacing values with case_when()

For each value, we check a condition and choose what to use.

conductivity <- c(510, 999, 533)

case_when(
  conductivity == 999 ~ NA,
  TRUE ~ conductivity
)
[1] 510  NA 533
  • Left of ~: what to check. Right of ~: what to use if it matches.
  • TRUE ~ conductivity keeps every other value.
  • Rules run from top to bottom. The first match wins.

From the lab: replace 999 with NA

In our field data, 999 means the measurement is unavailable. We replace it with NA so it does not get counted as a conductivity reading.

field_clean <- field_data |>
  mutate(
    conductivity_us_cm = case_when(
      conductivity_us_cm == 999 ~ NA,
      TRUE ~ conductivity_us_cm
    )
  )
  • mutate() replaces the conductivity_us_cm column with the values from case_when().
  • <- saves the result as field_clean.

count for multiple variables

hotels |>
  count(hotel, market_segment)
# A tibble: 14 × 3
   hotel        market_segment     n
   <chr>        <chr>          <int>
 1 City Hotel   Aviation         237
 2 City Hotel   Complementary    542
 3 City Hotel   Corporate       2986
 4 City Hotel   Direct          6093
 5 City Hotel   Groups         13975
 6 City Hotel   Offline TA/TO  16747
 7 City Hotel   Online TA      38748
 8 City Hotel   Undefined          2
 9 Resort Hotel Complementary    201
10 Resort Hotel Corporate       2309
11 Resort Hotel Direct          6513
12 Resort Hotel Groups          5836
13 Resort Hotel Offline TA/TO   7472
14 Resort Hotel Online TA      17729

A grammar of data tidying

The goal of tidyr is to help you tidy your data via…

  • pivoting for going between wide and long data
  • splitting and combining character columns
  • nesting and unnesting columns
  • clarifying how NAs should be treated

Pivoting data

Keep all of the data but change the shape of a data frame

How do we…?

Wider vs. longer

wider

more columns

# A tibble: 2 × 4
  customer_id item_1 item_2       item_3
        <dbl> <chr>  <chr>        <chr> 
1           1 bread  milk         banana
2           2 milk   toilet paper <NA>

Wider vs. longer

wider

more columns

# A tibble: 2 × 4
  customer_id item_1 item_2       item_3
        <dbl> <chr>  <chr>        <chr> 
1           1 bread  milk         banana
2           2 milk   toilet paper <NA>

longer

more rows

# A tibble: 6 × 3
  customer_id item_no item        
        <dbl> <chr>   <chr>       
1           1 item_1  bread       
2           1 item_2  milk        
3           1 item_3  banana      
4           2 item_1  milk        
5           2 item_2  toilet paper
6           2 item_3  <NA>

pivot_longer()

  • data (as usual)
pivot_longer(
  data,
  cols,
  names_to = "name",
  values_to = "value"
  )

pivot_longer()

  • data (as usual)
  • cols: columns to pivot into longer format
pivot_longer(
  data,
  cols,
  names_to = "name",
  values_to = "value"
  )

pivot_longer()

  • data (as usual)
  • cols: columns to pivot into longer format
  • names_to: name of the column where the column names of pivoted variables go (character string)
pivot_longer(
  data,
  cols,
  names_to = "name",
  values_to = "value"
  )

pivot_longer()

  • data (as usual)
  • cols: columns to pivot into longer format
  • names_to: name of the column where the column names of pivoted variables go (character string)
  • values_to: name of the column where data in pivoted variables go (character string)
pivot_longer(
  data,
  cols,
  names_to = "name",
  values_to = "value"
  )

Customers -> purchases

purchases <- customers %>%
  pivot_longer(
    cols = item_1:item_3,  # variables item_1 to item_3
    names_to = "item_no",  # column names -> new column called item_no
    values_to = "item"     # values in columns -> new column called item
    )

purchases
# A tibble: 6 × 3
  customer_id item_no item        
        <dbl> <chr>   <chr>       
1           1 item_1  bread       
2           1 item_2  milk        
3           1 item_3  banana      
4           2 item_1  milk        
5           2 item_2  toilet paper
6           2 item_3  <NA>

Why pivot?

Most likely, because the next step of your analysis needs it. More on left_join later…

prices
# A tibble: 5 × 2
  item         price
  <chr>        <dbl>
1 avocado       0.5 
2 banana        0.15
3 bread         1   
4 milk          0.8 
5 toilet paper  3   
purchases %>%
  left_join(prices)
# A tibble: 6 × 4
  customer_id item_no item         price
        <dbl> <chr>   <chr>        <dbl>
1           1 item_1  bread         1   
2           1 item_2  milk          0.8 
3           1 item_3  banana        0.15
4           2 item_1  milk          0.8 
5           2 item_2  toilet paper  3   
6           2 item_3  <NA>         NA

Purchases -> customers

  • data (as usual)
  • names_from: which column in the long format contains the what should be column names in the wide format
  • values_from: which column in the long format contains the what should be values in the new columns in the wide format
purchases %>%
  pivot_wider(
    names_from = item_no,
    values_from = item
  )
# A tibble: 2 × 4
  customer_id item_1 item_2       item_3
        <dbl> <chr>  <chr>        <chr> 
1           1 bread  milk         banana
2           2 milk   toilet paper <NA>

Your turn…

Try the examples from the slides on your own.

Dataset (download here)

More examples of pivoting

https://cran.r-project.org/web/packages/tidyr/vignettes/pivot.html