Hotel bookings - data wrangling

Mine Çetinkaya-Rundel, student: Teresa Lewandowski

library(tidyverse)
library(skimr)
library(readr)

# From TidyTuesday: https://github.com/rfordatascience/tidytuesday/blob/master/data/2020/2020-02-11/readme.md
hotels <- read_csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2020/2020-02-11/hotels.csv")

Exercises

Exercise 1.

Warm up! Take a look at an overview of the data with the skim() function.

Note: I already gave you the answer to this exercise. You just need to knit the document and view the output. A definition of all variables is given in the Data dictionary section at the end, though you don’t need to familiarize yourself with all variables in order to work through these exercises.

skim(hotels)

Data summary

Variable type: character

Variable type: Date

Variable type: numeric

Exercise 1.1 (added by ProfCelia)

Look at the documentation of the skimr package. And answer the following questions.

1. Can skimr functions be used in a pipeline? Yes
2. How is skimr used in this project? It provides a summary of the data in the hotels data set (including size of the data set, types of variables, and summary statistics of the variables).
3. Do you think it might be useful for you? Yes, it is useful to get a sense of the data prior to working with it.

Exercise 2.

Are people traveling on a whim? Let’s see…

Fill in the blanks for filtering for hotel bookings where the guest is not from the US (country code "USA") and the lead_time is less than 1 day.

Note: You will need to set eval=TRUE when you have an answer you want to try out.

hotels %>%
  filter(
    country != "USA", 
    lead_time < 1
    )

## # A tibble: 6,174 x 32
##    hotel is_canceled lead_time arrival_date_ye… arrival_date_mo…
##    <chr>       <dbl>     <dbl>            <dbl> <chr>           
##  1 Reso…           0         0             2015 July            
##  2 Reso…           0         0             2015 July            
##  3 Reso…           0         0             2015 July            
##  4 Reso…           0         0             2015 July            
##  5 Reso…           0         0             2015 July            
##  6 Reso…           0         0             2015 July            
##  7 Reso…           0         0             2015 July            
##  8 Reso…           0         0             2015 July            
##  9 Reso…           0         0             2015 July            
## 10 Reso…           0         0             2015 July            
## # … with 6,164 more rows, and 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>, assigned_room_type <chr>, booking_changes <dbl>,
## #   deposit_type <chr>, agent <chr>, company <chr>, days_in_waiting_list <dbl>,
## #   customer_type <chr>, adr <dbl>, required_car_parking_spaces <dbl>,
## #   total_of_special_requests <dbl>, reservation_status <chr>,
## #   reservation_status_date <date>

Exercise 3.

How many bookings involve at least 1 child or baby? (Note the logical op, PC)

In the following chunk, replace

• [AT LEAST] with the logical operator for “at least” (in two places)
• [OR] with the logical operator for “or”

Note: You will need to set eval=TRUE when you have an answer you want to try out.

hotels %>%
  filter(
    children >= 1 | babies >= 1
    )

## # A tibble: 9,332 x 32
##    hotel is_canceled lead_time arrival_date_ye… arrival_date_mo…
##    <chr>       <dbl>     <dbl>            <dbl> <chr>           
##  1 Reso…           0        18             2015 July            
##  2 Reso…           1        47             2015 July            
##  3 Reso…           0         1             2015 July            
##  4 Reso…           0        10             2015 July            
##  5 Reso…           1        79             2015 July            
##  6 Reso…           0       101             2015 July            
##  7 Reso…           0        92             2015 July            
##  8 Reso…           1        26             2015 July            
##  9 Reso…           0       102             2015 July            
## 10 Reso…           0        78             2015 July            
## # … with 9,322 more rows, and 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>, assigned_room_type <chr>, booking_changes <dbl>,
## #   deposit_type <chr>, agent <chr>, company <chr>, days_in_waiting_list <dbl>,
## #   customer_type <chr>, adr <dbl>, required_car_parking_spaces <dbl>,
## #   total_of_special_requests <dbl>, reservation_status <chr>,
## #   reservation_status_date <date>

Exercise 4.

Do you think it’s more likely to find bookings with children or babies in city hotels or resort hotels? I would guess city hotels. Test your intuition. Using filter() determine the number of bookings in resort hotels that have more than 1 child or baby in the room? Then, do the same for city hotels, and compare the numbers of rows in the resulting filtered data frames.

hotels %>%
  filter(
    hotel == "Resort Hotel",
    (children >= 1 | babies >= 1))

## # A tibble: 3,929 x 32
##    hotel is_canceled lead_time arrival_date_ye… arrival_date_mo…
##    <chr>       <dbl>     <dbl>            <dbl> <chr>           
##  1 Reso…           0        18             2015 July            
##  2 Reso…           1        47             2015 July            
##  3 Reso…           0         1             2015 July            
##  4 Reso…           0        10             2015 July            
##  5 Reso…           1        79             2015 July            
##  6 Reso…           0       101             2015 July            
##  7 Reso…           0        92             2015 July            
##  8 Reso…           1        26             2015 July            
##  9 Reso…           0       102             2015 July            
## 10 Reso…           0        78             2015 July            
## # … with 3,919 more rows, and 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>, assigned_room_type <chr>, booking_changes <dbl>,
## #   deposit_type <chr>, agent <chr>, company <chr>, days_in_waiting_list <dbl>,
## #   customer_type <chr>, adr <dbl>, required_car_parking_spaces <dbl>,
## #   total_of_special_requests <dbl>, reservation_status <chr>,
## #   reservation_status_date <date>

hotels %>%
  filter(
    hotel == "City Hotel",
    (children >= 1 | babies >= 1))

## # A tibble: 5,403 x 32
##    hotel is_canceled lead_time arrival_date_ye… arrival_date_mo…
##    <chr>       <dbl>     <dbl>            <dbl> <chr>           
##  1 City…           1       100             2015 July            
##  2 City…           0        69             2015 July            
##  3 City…           0        67             2015 July            
##  4 City…           1        60             2015 July            
##  5 City…           0         0             2015 July            
##  6 City…           0         0             2015 July            
##  7 City…           0         0             2015 July            
##  8 City…           0        84             2015 July            
##  9 City…           0        74             2015 July            
## 10 City…           0        75             2015 July            
## # … with 5,393 more rows, and 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>, assigned_room_type <chr>, booking_changes <dbl>,
## #   deposit_type <chr>, agent <chr>, company <chr>, days_in_waiting_list <dbl>,
## #   customer_type <chr>, adr <dbl>, required_car_parking_spaces <dbl>,
## #   total_of_special_requests <dbl>, reservation_status <chr>,
## #   reservation_status_date <date>

Based on these results it is more likely to find bookings with children and/or babies at city hotels.

Exercise 5.

Create a frequency table of the number of adults in a booking. Display the results in descending order so the most common observation is on top. What is the most common number of adults in bookings in this dataset? Are there any surprising results?

Note: Don’t forget to label your R chunk as well (where it says label-me-1). Your label should be short, informative, and shouldn’t include spaces. It also shouldn’t repeat a previous label, otherwise R Markdown will give you an error about repeated R chunk labels.

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

## # A tibble: 14 x 2
##    adults     n
##     <dbl> <int>
##  1      2 89680
##  2      1 23027
##  3      3  6202
##  4      0   403
##  5      4    62
##  6     26     5
##  7      5     2
##  8     20     2
##  9     27     2
## 10      6     1
## 11     10     1
## 12     40     1
## 13     50     1
## 14     55     1

The most common number of adults in a booking is two, which is unsurprising. It is suprising that some bookings were made for so many people per room, since a typical hotel room occupancy is maybe 4 adults at most.

Exercise 6.

Repeat Exercise 5, once for canceled bookings (is_canceled coded as 1) and once for not canceled bookings (is_canceled coded as 0). What does this reveal about the surprising results you spotted in the previous exercise?

Note: Don’t forget to label your R chunk as well (where it says label-me-2).

hotels %>%
  filter(is_canceled == 1) %>%
  count(adults) %>%
  arrange(desc(n))

## # A tibble: 14 x 2
##    adults     n
##     <dbl> <int>
##  1      2 35258
##  2      1  6674
##  3      3  2151
##  4      0   109
##  5      4    16
##  6     26     5
##  7      5     2
##  8     20     2
##  9     27     2
## 10      6     1
## 11     10     1
## 12     40     1
## 13     50     1
## 14     55     1

hotels %>%
  filter(is_canceled == 0) %>%
  count(adults) %>%
  arrange(desc(n))

## # A tibble: 5 x 2
##   adults     n
##    <dbl> <int>
## 1      2 54422
## 2      1 16353
## 3      3  4051
## 4      0   294
## 5      4    46

This data shows us that all of the unusual number of adults per booking were canceled, meaning that they were probably entered incorrectly or as a mistake.

Exercise 7.

Calculate minimum, mean, median, and maximum average daily rate (adr) grouped by hotel type so that you can get these statistics separately for resort and city hotels. Which type of hotel is higher, on average?

hotels %>%
  group_by(hotel) %>%
  summarize(
    min(adr),
    mean(adr),
    median(adr),
    max(adr)
  )

## # A tibble: 2 x 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

Generally speaking, city hotels have a higher average daily rate.

Exercise 8.

We observe two unusual values in the summary statistics above – a negative minimum, and a very high maximum). What types of hotels are these? The negative minimum is a resort hotel, and the very high maximum is a city hotel. Locate these observations in the dataset and find out the arrival date (year and month) as well as how many people (adults, children, and babies) stayed in the room. You can investigate the data in the viewer to locate these values, but preferably you should identify them in a reproducible way with some code.

Hint: For example, you can filter for the given adr amounts and select the relevant columns.

hotels %>%
  filter(adr == -6.38) %>%
  select(hotel, adr, arrival_date_month, arrival_date_day_of_month, arrival_date_year, adults, children, babies)

## # A tibble: 1 x 8
##   hotel   adr arrival_date_mo… arrival_date_da… arrival_date_ye… adults children
##   <chr> <dbl> <chr>                       <dbl>            <dbl>  <dbl>    <dbl>
## 1 Reso… -6.38 March                           5             2017      2        0
## # … with 1 more variable: babies <dbl>

For the negative adr, the group arrived on March 5, 2017 and consisted of 2 adults and no children or babies.

hotels %>%
  filter(adr == 5400) %>%
  select(hotel, adr, arrival_date_month, arrival_date_day_of_month, arrival_date_year, adults, children, babies)

## # A tibble: 1 x 8
##   hotel   adr arrival_date_mo… arrival_date_da… arrival_date_ye… adults children
##   <chr> <dbl> <chr>                       <dbl>            <dbl>  <dbl>    <dbl>
## 1 City…  5400 March                          25             2016      2        0
## # … with 1 more variable: babies <dbl>

For the very high adr, the group arrived on March 25, 2016 and consisted of 2 adults and no children or babies.

Data dictionary

Below is the full data dictionary. Note that it is long (there are lots of variables in the data), but we will be using a limited set of the variables for our analysis.