Challenge 4

challenge_4

More data wrangling: mutate

Author

Priya Marla

Published

January 12, 2023

Code

library(tidyverse)
library(lubridate)
knitr::opts_chunk$set(echo = TRUE, warning=FALSE, message=FALSE)

Challenge Overview

Today’s challenge is to:

read in a data set, and describe the data set using both words and any supporting information (e.g., tables, etc)
tidy data (as needed, including sanity checks)
identify variables that need to be mutated
mutate variables and sanity check all mutations

Read in data

Read in one (or more) of the following datasets, using the correct R package and command.

abc_poll.csv ⭐
poultry_tidy.csv⭐⭐
FedFundsRate.csv⭐⭐⭐
hotel_bookings.csv⭐⭐⭐⭐
debt_in_trillions ⭐⭐⭐⭐⭐

Code

dataset <- read_csv("_data/hotel_bookings.csv") #reading the data from csv file
dataset

# A tibble: 119,390 × 32
   hotel  is_ca…¹ lead_…² arriv…³ arriv…⁴ arriv…⁵ arriv…⁶ stays…⁷ stays…⁸ adults
   <chr>    <dbl>   <dbl>   <dbl> <chr>     <dbl>   <dbl>   <dbl>   <dbl>  <dbl>
 1 Resor…       0     342    2015 July         27       1       0       0      2
 2 Resor…       0     737    2015 July         27       1       0       0      2
 3 Resor…       0       7    2015 July         27       1       0       1      1
 4 Resor…       0      13    2015 July         27       1       0       1      1
 5 Resor…       0      14    2015 July         27       1       0       2      2
 6 Resor…       0      14    2015 July         27       1       0       2      2
 7 Resor…       0       0    2015 July         27       1       0       2      2
 8 Resor…       0       9    2015 July         27       1       0       2      2
 9 Resor…       1      85    2015 July         27       1       0       3      2
10 Resor…       1      75    2015 July         27       1       0       3      2
# … with 119,380 more rows, 22 more variables: 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>, …

Code

dim(dataset) #to get the number of rows and columns

[1] 119390     32

Code

unique(dataset$hotel) #to get the unique rows in hotel column

[1] "Resort Hotel" "City Hotel"

Briefly describe the data

This dataset describes the information of the reservations made in Resort Hotel and City Hotel. There are 119390 rows and 32 columns. Each columns represents various data such as for what dates and what hotel the booking was made, whether reservation is called or not and if the payment is made or not etc.

Tidy Data (as needed)

Is your data already tidy, or is there work to be done? Be sure to anticipate your end result to provide a sanity check, and document your work here.

The data needs to be cleaned. The date is spread across in multiple columns. If is there in a single column it;’ll will be easy to calculate stats. Can also calculate the date with lead time and date of arrival columns.

Code

select(dataset, 3:7 )

# A tibble: 119,390 × 5
   lead_time arrival_date_year arrival_date_month arrival_date_week_nu…¹ arriv…²
       <dbl>             <dbl> <chr>                               <dbl>   <dbl>
 1       342              2015 July                                   27       1
 2       737              2015 July                                   27       1
 3         7              2015 July                                   27       1
 4        13              2015 July                                   27       1
 5        14              2015 July                                   27       1
 6        14              2015 July                                   27       1
 7         0              2015 July                                   27       1
 8         9              2015 July                                   27       1
 9        85              2015 July                                   27       1
10        75              2015 July                                   27       1
# … with 119,380 more rows, and abbreviated variable names
#   ¹arrival_date_week_number, ²arrival_date_day_of_month

Any additional comments?

Identify variables that need to be mutated

Are there any variables that require mutation to be usable in your analysis stream? For example, are all time variables correctly coded as dates? Are all string variables reduced and cleaned to sensible categories? Do you need to turn any variables into factors and reorder for ease of graphics and visualization?

Document your work here.

The country column is placed in the 14th place, it should be moved up to 2nd place i.e after the hotel column. Created a new column arrival_date by getting the date from “arrival_date_day_of_month”, “arrival_date_month”,“arrival_date_year” columns. Removing these 3 columns and moving the arrival date column to column after “lead_time”. Creating the new column booking_date to know the date of booking with the information from columns lead_time and arrival_date. After tidying up the data, total columns remaining are 29

Code

#tidying the dataset
tidy_data <- dataset %>%
  relocate("country",.after = "hotel") %>% #relocating the country column
  mutate(arrival_date = (str_c(arrival_date_day_of_month,arrival_date_month,arrival_date_year, sep = "/")), arrival_date = dmy(arrival_date), .after = lead_time) %>% #variable for arrival date
  mutate(booking_date = arrival_date-days(lead_time), .after = lead_time) %>% #variable to know the date of booking
  select(-"lead_time")
tidy_data <- tidy_data[,-6:-9] #removed columns with arrival date information
tidy_data

# A tibble: 119,390 × 29
   hotel    country is_ca…¹ booking_…² arrival_…³ stays…⁴ stays…⁵ adults child…⁶
   <chr>    <chr>     <dbl> <date>     <date>       <dbl>   <dbl>  <dbl>   <dbl>
 1 Resort … PRT           0 2014-07-24 2015-07-01       0       0      2       0
 2 Resort … PRT           0 2013-06-24 2015-07-01       0       0      2       0
 3 Resort … GBR           0 2015-06-24 2015-07-01       0       1      1       0
 4 Resort … GBR           0 2015-06-18 2015-07-01       0       1      1       0
 5 Resort … GBR           0 2015-06-17 2015-07-01       0       2      2       0
 6 Resort … GBR           0 2015-06-17 2015-07-01       0       2      2       0
 7 Resort … PRT           0 2015-07-01 2015-07-01       0       2      2       0
 8 Resort … PRT           0 2015-06-22 2015-07-01       0       2      2       0
 9 Resort … PRT           1 2015-04-07 2015-07-01       0       3      2       0
10 Resort … PRT           1 2015-04-17 2015-07-01       0       3      2       0
# … with 119,380 more rows, 20 more variables: babies <dbl>, meal <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>, …

Any additional comments?