Working with a single data frame

wk3-d02-single-df

Dr. D

Chico State
DATA 385 - Fall 2026

September 9, 2026

Setup

🎥 Working with a single data frame - code along in wk3-d02.

library(tidyverse)

Important

Note these slides have been modified from the original DS Box slides (and thus video) to add more “you try it”’s, and removed any references to AE03. Disregard those parts of the video.

Data: Hotel bookings

We have a single data frame, and we want to slice it, and dice it, and juice it, and process it.

• Data from two hotels: one resort and one city hotel
• Observations: Each row represents a hotel booking

hotels <- read_csv("data/hotels.csv")

Useful verbs

• select: choose variables (columns)
• arrange: sorting rows based on the value of a variable
• slice: Pick out specific rows by their positions
• filter: Subset the data to keep only rows that match a condition

select

Keep these specific columns

select to keep variables

hotels %>%
  select(hotel, lead_time)

# A tibble: 119,390 × 2
   hotel        lead_time
   <chr>            <dbl>
 1 Resort Hotel       342
 2 Resort Hotel       737
 3 Resort Hotel         7
 4 Resort Hotel        13
 5 Resort Hotel        14
 6 Resort Hotel        14
 7 Resort Hotel         0
 8 Resort Hotel         9
 9 Resort Hotel        85
10 Resort Hotel        75
# ℹ 119,380 more rows

select to exclude variables

hotels %>%
  select(-agent)

# A tibble: 119,390 × 31
   hotel    is_canceled lead_time arrival_date_year arrival_date_month
   <chr>          <dbl>     <dbl>             <dbl> <chr>             
 1 Resort …           0       342              2015 July              
 2 Resort …           0       737              2015 July              
 3 Resort …           0         7              2015 July              
 4 Resort …           0        13              2015 July              
 5 Resort …           0        14              2015 July              
 6 Resort …           0        14              2015 July              
 7 Resort …           0         0              2015 July              
 8 Resort …           0         9              2015 July              
 9 Resort …           1        85              2015 July              
10 Resort …           1        75              2015 July              
# ℹ 119,380 more rows
# ℹ 26 more variables: arrival_date_week_number <dbl>,
#   arrival_date_day_of_month <dbl>, stays_in_weekend_nights <dbl>,
#   stays_in_week_nights <dbl>, adults <dbl>, children <dbl>,
#   babies <dbl>, meal <chr>, country <chr>, market_segment <chr>,
#   distribution_channel <chr>, is_repeated_guest <dbl>,
#   previous_cancellations <dbl>, …

agent is the type of booking agent that made the reservation — worth naming what the column actually means rather than leaving it abstract.

select a range of variables

hotels %>%
  select(hotel:arrival_date_month)

# A tibble: 119,390 × 5
   hotel    is_canceled lead_time arrival_date_year arrival_date_month
   <chr>          <dbl>     <dbl>             <dbl> <chr>             
 1 Resort …           0       342              2015 July              
 2 Resort …           0       737              2015 July              
 3 Resort …           0         7              2015 July              
 4 Resort …           0        13              2015 July              
 5 Resort …           0        14              2015 July              
 6 Resort …           0        14              2015 July              
 7 Resort …           0         0              2015 July              
 8 Resort …           0         9              2015 July              
 9 Resort …           1        85              2015 July              
10 Resort …           1        75              2015 July              
# ℹ 119,380 more rows

Fragile!

Range-selecting by position only works if you know the column order. Always double check ordering using functions like names(hotels).

select variables starting with…

hotels %>%
  select(starts_with("arrival"))

# A tibble: 119,390 × 4
   arrival_date_year arrival_date_month arrival_date_week_number
               <dbl> <chr>                                 <dbl>
 1              2015 July                                     27
 2              2015 July                                     27
 3              2015 July                                     27
 4              2015 July                                     27
 5              2015 July                                     27
 6              2015 July                                     27
 7              2015 July                                     27
 8              2015 July                                     27
 9              2015 July                                     27
10              2015 July                                     27
# ℹ 119,380 more rows
# ℹ 1 more variable: arrival_date_day_of_month <dbl>

select variables ending with…

hotels %>%
  select(ends_with("type"))

# A tibble: 119,390 × 4
   reserved_room_type assigned_room_type deposit_type customer_type
   <chr>              <chr>              <chr>        <chr>        
 1 C                  C                  No Deposit   Transient    
 2 C                  C                  No Deposit   Transient    
 3 A                  C                  No Deposit   Transient    
 4 A                  A                  No Deposit   Transient    
 5 A                  A                  No Deposit   Transient    
 6 A                  A                  No Deposit   Transient    
 7 C                  C                  No Deposit   Transient    
 8 C                  C                  No Deposit   Transient    
 9 A                  A                  No Deposit   Transient    
10 D                  D                  No Deposit   Transient    
# ℹ 119,380 more rows

These four *_type columns (reserved_room_type, assigned_room_type, deposit_type, customer_type) use coded letters that mean something specific (reservation codes) — a good moment to underline that naming variables sensibly is what makes starts_with/ends_with useful in the first place.

Select helpers

• starts_with(): starts with a prefix
• ends_with(): ends with a suffix
• contains(): contains a literal string
• num_range(): matches a numerical range like x01, x02, x03
• one_of(): matches variable names in a character vector
• everything(): matches all variables
• last_col(): select last variable, possibly with an offset
• matches(): matches a regular expression

See help for any of these functions for more info, e.g. ?everything.

arrange in ascending / descending order

hotels %>%
  select(adults, children, babies) %>%
  arrange(babies)

# A tibble: 119,390 × 3
   adults children babies
    <dbl>    <dbl>  <dbl>
 1      2        0      0
 2      2        0      0
 3      1        0      0
 4      1        0      0
 5      2        0      0
 6      2        0      0
 7      2        0      0
 8      2        0      0
 9      2        0      0
10      2        0      0
# ℹ 119,380 more rows

hotels %>%
  select(adults, children, babies) %>%
  arrange(desc(babies))

# A tibble: 119,390 × 3
   adults children babies
    <dbl>    <dbl>  <dbl>
 1      2        0     10
 2      1        0      9
 3      2        0      2
 4      2        0      2
 5      2        0      2
 6      2        0      2
 7      2        0      2
 8      2        0      2
 9      2        0      2
10      2        0      2
# ℹ 119,380 more rows

Live reaction to point out: 2 adults + 10 babies, then 1 adult + 9 babies — “you don’t want to be next door to that.” Good comic beat, and it seeds the data-quality-skepticism theme that runs through the rest of the deck (see the little-ones note later).

You try it: arrange

Your turn

Choose a different numeric variable, then arrange the hotel bookings from largest to smallest. What do the first few rows tell you?

slice for certain row numbers

# 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 H…           0       342              2015 July              
2 Resort H…           0       737              2015 July              
3 Resort H…           0         7              2015 July              
4 Resort H…           0        13              2015 July              
5 Resort H…           0        14              2015 July              
# ℹ 27 more variables: arrival_date_week_number <dbl>,
#   arrival_date_day_of_month <dbl>, stays_in_weekend_nights <dbl>,
#   stays_in_week_nights <dbl>, adults <dbl>, children <dbl>,
#   babies <dbl>, meal <chr>, country <chr>, market_segment <chr>,
#   distribution_channel <chr>, is_repeated_guest <dbl>,
#   previous_cancellations <dbl>,
#   previous_bookings_not_canceled <dbl>, reserved_room_type <chr>, …

A note on comments

Note

In R, you can use # for adding comments to your code. Any text following # will be printed as is, and won’t be run as R code. This is useful for leaving comments and for temporarily disabling certain lines of code while debugging.

hotels %>%
  # slice the first five rows  # this line is a comment
  # select(hotel) %>%          # this one doesn't run
  slice(1:5)                   # this line runs

# A tibble: 5 × 32
  hotel     is_canceled lead_time arrival_date_year arrival_date_month
  <chr>           <dbl>     <dbl>             <dbl> <chr>             
1 Resort H…           0       342              2015 July              
2 Resort H…           0       737              2015 July              
3 Resort H…           0         7              2015 July              
4 Resort H…           0        13              2015 July              
5 Resort H…           0        14              2015 July              
# ℹ 27 more variables: arrival_date_week_number <dbl>,
#   arrival_date_day_of_month <dbl>, stays_in_weekend_nights <dbl>,
#   stays_in_week_nights <dbl>, adults <dbl>, children <dbl>,
#   babies <dbl>, meal <chr>, country <chr>, market_segment <chr>,
#   distribution_channel <chr>, is_repeated_guest <dbl>,
#   previous_cancellations <dbl>,
#   previous_bookings_not_canceled <dbl>, reserved_room_type <chr>, …

filter

Keeps rows where a logical condition is TRUE.

filter to select a subset of rows

# bookings in City Hotels
hotels %>%
  filter(hotel == "City Hotel") 

# A tibble: 79,330 × 32
   hotel    is_canceled lead_time arrival_date_year arrival_date_month
   <chr>          <dbl>     <dbl>             <dbl> <chr>             
 1 City Ho…           0         6              2015 July              
 2 City Ho…           1        88              2015 July              
 3 City Ho…           1        65              2015 July              
 4 City Ho…           1        92              2015 July              
 5 City Ho…           1       100              2015 July              
 6 City Ho…           1        79              2015 July              
 7 City Ho…           0         3              2015 July              
 8 City Ho…           1        63              2015 July              
 9 City Ho…           1        62              2015 July              
10 City Ho…           1        62              2015 July              
# ℹ 79,320 more rows
# ℹ 27 more variables: arrival_date_week_number <dbl>,
#   arrival_date_day_of_month <dbl>, stays_in_weekend_nights <dbl>,
#   stays_in_week_nights <dbl>, adults <dbl>, children <dbl>,
#   babies <dbl>, meal <chr>, country <chr>, market_segment <chr>,
#   distribution_channel <chr>, is_repeated_guest <dbl>,
#   previous_cancellations <dbl>, …

filter for many conditions at once

bookings with no adults and at least one child in the room

hotels %>%
  filter(adults == 0, children >= 1) %>%
  select(adults, babies, children)

# A tibble: 223 × 3
   adults babies children
    <dbl>  <dbl>    <dbl>
 1      0      0        3
 2      0      0        2
 3      0      0        2
 4      0      0        2
 5      0      0        2
 6      0      0        3
 7      0      1        2
 8      0      0        2
 9      0      0        2
10      0      0        2
# ℹ 213 more rows

filter for more complex conditions

bookings
with no adults and
(some children or babies) in the room

hotels %>%
  filter(adults == 0,
         children >= 1 | babies >= 1) %>%
  select(adults, babies, children)

# A tibble: 223 × 3
   adults babies children
    <dbl>  <dbl>    <dbl>
 1      0      0        3
 2      0      0        2
 3      0      0        2
 4      0      0        2
 5      0      0        2
 6      0      0        3
 7      0      1        2
 8      0      0        2
 9      0      0        2
10      0      0        2
# ℹ 213 more rows

Logical operators in R

operator	definition	operator	definition
<	less than	x | y	x OR y
<=	less than or equal to	is.na(x)	test if x is NA
>	greater than	!is.na(x)	test if x is not NA
>=	greater than or equal to	x %in% y	test if x is in y
==	exactly equal to	!(x %in% y)	test if x is not in y
!=	not equal to	!x	not x
x & y	x AND y

You try it: filter

Your turn

Filter the hotel bookings using two conditions of your choice.

More useful verbs

• distinct: filter for unique rows
• count: to create a frequency table
• mutate: add new variables
• summarise: create summary statistics by summarizing variables into numeric values.
• group_by: for grouped operations

distinct and count

distinct to filter for unique rows

… and arrange to order alphabetically

hotels %>%
  distinct(market_segment) %>%
  arrange(market_segment)

# A tibble: 8 × 1
  market_segment
  <chr>         
1 Aviation      
2 Complementary 
3 Corporate     
4 Direct        
5 Groups        
6 Offline TA/TO 
7 Online TA     
8 Undefined     

hotels %>%
  distinct(hotel, market_segment) %>%
  arrange(hotel, market_segment)

# A tibble: 14 × 2
   hotel        market_segment
   <chr>        <chr>         
 1 City Hotel   Aviation      
 2 City Hotel   Complementary 
 3 City Hotel   Corporate     
 4 City Hotel   Direct        
 5 City Hotel   Groups        
 6 City Hotel   Offline TA/TO 
 7 City Hotel   Online TA     
 8 City Hotel   Undefined     
 9 Resort Hotel Complementary 
10 Resort Hotel Corporate     
11 Resort Hotel Direct        
12 Resort Hotel Groups        
13 Resort Hotel Offline TA/TO 
14 Resort Hotel Online TA     

count to create frequency tables

alphabetical order by default

hotels %>%
  count(market_segment)

# A tibble: 8 × 2
  market_segment     n
  <chr>          <int>
1 Aviation         237
2 Complementary    743
3 Corporate       5295
4 Direct         12606
5 Groups         19811
6 Offline TA/TO  24219
7 Online TA      56477
8 Undefined          2

descending frequency order

hotels %>%
  count(market_segment, sort = TRUE)

# A tibble: 8 × 2
  market_segment     n
  <chr>          <int>
1 Online TA      56477
2 Offline TA/TO  24219
3 Groups         19811
4 Direct         12606
5 Corporate       5295
6 Complementary    743
7 Aviation         237
8 Undefined          2

count and arrange

ascending frequency order

hotels %>%
  count(market_segment) %>%
  arrange(n)

# A tibble: 8 × 2
  market_segment     n
  <chr>          <int>
1 Undefined          2
2 Aviation         237
3 Complementary    743
4 Corporate       5295
5 Direct         12606
6 Groups         19811
7 Offline TA/TO  24219
8 Online TA      56477

descending frequency order, same as sort = TRUE above

hotels %>%
  count(market_segment) %>%
  arrange(desc(n))

# A tibble: 8 × 2
  market_segment     n
  <chr>          <int>
1 Online TA      56477
2 Offline TA/TO  24219
3 Groups         19811
4 Direct         12606
5 Corporate       5295
6 Complementary    743
7 Aviation         237
8 Undefined          2

count for multiple variables

Number of market_segments within each different hotel.

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

Order matters when you count

Market segments within hotel types

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

Hotel types within market segments

hotels %>%
  count(market_segment, hotel)

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

You try it: count

Your turn

Use count() to create a frequency table for the number of deposit_types within each customer_type. Then arrange() the result so the most frequent combination appears first.

mutate

Add new variables

mutate to add a new variable

hotels %>%
  mutate(little_ones = children + babies) %>%
  select(children, babies, little_ones) %>%
  arrange(desc(little_ones))

# A tibble: 119,390 × 3
   children babies little_ones
      <dbl>  <dbl>       <dbl>
 1       10      0          10
 2        0     10          10
 3        0      9           9
 4        2      1           3
 5        2      1           3
 6        2      1           3
 7        3      0           3
 8        2      1           3
 9        2      1           3
10        3      0           3
# ℹ 119,380 more rows

Little ones in resort and city hotels

Resort Hotel

hotels %>%
  mutate(little_ones = children + babies) %>%
  filter(little_ones >= 1, hotel == "Resort Hotel") %>%
  select(hotel, little_ones)

# A tibble: 3,929 × 2
   hotel        little_ones
   <chr>              <dbl>
 1 Resort Hotel           1
 2 Resort Hotel           2
 3 Resort Hotel           2
 4 Resort Hotel           2
 5 Resort Hotel           1
 6 Resort Hotel           1
 7 Resort Hotel           2
 8 Resort Hotel           2
 9 Resort Hotel           1
10 Resort Hotel           1
# ℹ 3,919 more rows

City Hotel

hotels %>%
  mutate(little_ones = children + babies) %>%
  filter(little_ones >= 1, hotel == "City Hotel") %>%
  select(hotel, little_ones)

# A tibble: 5,403 × 2
   hotel      little_ones
   <chr>            <dbl>
 1 City Hotel           1
 2 City Hotel           1
 3 City Hotel           2
 4 City Hotel           1
 5 City Hotel           1
 6 City Hotel           1
 7 City Hotel           1
 8 City Hotel           1
 9 City Hotel           1
10 City Hotel           1
# ℹ 5,393 more rows

Important caution: don’t let students conclude from 3929 vs. 5403 that city-hotel guests are “more likely” to have kids — the two hotels don’t have equal total bookings, so comparing raw counts here confounds with the base rate. This is the single most important stats-literacy point in this deck; worth pausing on.

What is happening here?

What is happening in the following chunk?

hotels %>%
  mutate(little_ones = children + babies) %>%
  count(hotel, little_ones) %>%
  mutate(prop = n / sum(n))

# A tibble: 12 × 4
   hotel        little_ones     n       prop
   <chr>              <dbl> <int>      <dbl>
 1 City Hotel             0 73923 0.619     
 2 City Hotel             1  3263 0.0273    
 3 City Hotel             2  2056 0.0172    
 4 City Hotel             3    82 0.000687  
 5 City Hotel             9     1 0.00000838
 6 City Hotel            10     1 0.00000838
 7 City Hotel            NA     4 0.0000335 
 8 Resort Hotel           0 36131 0.303     
 9 Resort Hotel           1  2183 0.0183    
10 Resort Hotel           2  1716 0.0144    
11 Resort Hotel           3    29 0.000243  
12 Resort Hotel          10     1 0.00000838

You try it: mutate

Your turn

Create a new variable called total_people that adds together adults, children, and babies. Use select to show these 4 variables only and verify that your code worked.

summarise and group_by

A powerful combination

summarise for summary stats

# mean average daily rate for all bookings
hotels %>%
  summarise(mean_adr = mean(adr))

# A tibble: 1 × 1
  mean_adr
     <dbl>
1     102.

Note

summarise() changes the data frame entirely: it collapses rows down to a single summary statistic, and removes all columns that are irrelevant to the calculation.

Always name your summary columns

summarise() lets you get away with not naming your new column, but that’s not recommended!

❌

hotels %>%
  summarise(mean(adr))

# A tibble: 1 × 1
  `mean(adr)`
        <dbl>
1        102.

✅

hotels %>%
  summarise(mean_adr = mean(adr))

# A tibble: 1 × 1
  mean_adr
     <dbl>
1     102.

You try it: summarize

Your turn

Use summarise() to calculate the average number of adults per room. Name the output column clearly.

group_by for grouped operations

average daily rate for all bookings at city and resort hotels

hotels %>%
  group_by(hotel) %>%
  summarise(mean_adr = mean(adr))

# A tibble: 2 × 2
  hotel        mean_adr
  <chr>           <dbl>
1 City Hotel      105. 
2 Resort Hotel     95.0

Calculating frequencies

The following two give the same result, so count is simply short for group_by+summarize(n=n()).

hotels %>%
  group_by(hotel) %>%
  summarise(n = n())

# A tibble: 2 × 2
  hotel            n
  <chr>        <int>
1 City Hotel   79330
2 Resort Hotel 40060

hotels %>%
  count(hotel)

# A tibble: 2 × 2
  hotel            n
  <chr>        <int>
1 City Hotel   79330
2 Resort Hotel 40060

You try it: group_by

Your turn

See if the average number of adults per room differs by a categorical variable of your choice. That is, group_by a categorical variable and summarize the adults variable. Name the output column clearly.

Multiple summary statistics

summarise can be used for multiple summary statistics as well.

hotels %>%
  group_by(hotel) %>% 
  summarise(min_adr = min(adr),
            mean_adr = mean(adr),
            median_adr = median(adr),
            max_adr = max(adr)
            )

# A tibble: 2 × 5
  hotel        min_adr mean_adr median_adr max_adr
  <chr>          <dbl>    <dbl>      <dbl>   <dbl>
1 City Hotel      0       105.        99.9    5400
2 Resort Hotel   -6.38     95.0       75       508

Worth flagging the min/max out loud: min ADR of -6.38 doesn’t really make sense for a rate — maybe a refund situation. Max of 5400 is a real but extreme value. Model “does this number make sense” thinking on real output rather than skipping past it.

Acknowledgements & Credits

Note

This page adapts material from Data Science in a Box (Unit 2, Deck 7: “Working with a single data frame”) by Mine Çetinkaya-Rundel, licensed under CC BY-SA 4.0. Source: tidyverse/datascience-box. Modified: converted from xaringan to Quarto revealjs and added activities.