Challenge 4

Author

Jingyi Yang

1. Start Up

knitr::opts_chunk$set(echo = TRUE)
library(dplyr)

Attaching package: 'dplyr'
The following objects are masked from 'package:stats':

    filter, lag
The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union
library(here)
here() starts at C:/8-601
library(readr)
library(readxl)
library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.4.4     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.0
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(stringr)

2. Import the data

2.1 “poultry” data set

poultry <- read_excel("C:\\8-601\\challenge_datasets\\poultry_tidy.xlsx")

poultry %>% print(n = 10, width = Inf)
# A tibble: 600 × 4
   Product  Year Month     Price_Dollar
   <chr>   <dbl> <chr>            <dbl>
 1 Whole    2013 January           2.38
 2 Whole    2013 February          2.38
 3 Whole    2013 March             2.38
 4 Whole    2013 April             2.38
 5 Whole    2013 May               2.38
 6 Whole    2013 June              2.38
 7 Whole    2013 July              2.38
 8 Whole    2013 August            2.38
 9 Whole    2013 September         2.38
10 Whole    2013 October           2.38
# ℹ 590 more rows

2.2 “organiceggpoultry” data set

2.2.1 “whole data” sheet

setwd("C:\\8-601\\challenge_datasets")
organiceggpoultry<- here("organiceggpoultry.xls")
excel_sheets("organiceggpoultry.xls")
[1] "Data"                            "Organic egg prices, 2004-13"    
[3] "Organic poultry prices, 2004-13"
organiceggpoultry_whole_data<- read_excel("organiceggpoultry.xls", sheet = "Data", range = "B6:L125", col_names = c("Year and Month", "USDA Certified Organic Eggs_Extra Large Dozen", "USDA Certified Organic Eggs_Extra Large 1/2 Doz", "USDA Certified Organic Eggs_Large Dozen",    "USDA Certified Organic Eggs_Large 1/2 Doz", "delet", "USDA Certified Organic Young Chicken_Whole", "USDA Certified Organic Young Chicken_B/S Breast", "USDA Certified Organic Young Chicken_Bone in Breast","USDA Certified Organic Young Chicken_Whole Legs", "USDA Certified Organic Young Chicken_Thighs")) %>% select(- "delet")


organiceggpoultry_whole_data %>% print(n = 10, width = Inf)
# A tibble: 120 × 10
   `Year and Month` `USDA Certified Organic Eggs_Extra Large Dozen`
   <chr>                                                      <dbl>
 1 Jan 2004                                                    230 
 2 February                                                    230 
 3 March                                                       230 
 4 April                                                       234.
 5 May                                                         236 
 6 June                                                        241 
 7 July                                                        241 
 8 August                                                      241 
 9 September                                                   241 
10 October                                                     241 
   `USDA Certified Organic Eggs_Extra Large 1/2 Doz`
                                               <dbl>
 1                                              132 
 2                                              134.
 3                                              137 
 4                                              137 
 5                                              137 
 6                                              137 
 7                                              137 
 8                                              137 
 9                                              136.
10                                              136.
   `USDA Certified Organic Eggs_Large Dozen`
                                       <dbl>
 1                                      230 
 2                                      226.
 3                                      225 
 4                                      225 
 5                                      225 
 6                                      231.
 7                                      234.
 8                                      234.
 9                                      234.
10                                      234.
   `USDA Certified Organic Eggs_Large 1/2 Doz`
                                         <dbl>
 1                                        126 
 2                                        128.
 3                                        131 
 4                                        131 
 5                                        131 
 6                                        134.
 7                                        134.
 8                                        134.
 9                                        130.
10                                        128.
   `USDA Certified Organic Young Chicken_Whole`
                                          <dbl>
 1                                         198.
 2                                         198.
 3                                         209 
 4                                         212 
 5                                         214.
 6                                         216.
 7                                         217 
 8                                         217 
 9                                         217 
10                                         217 
   `USDA Certified Organic Young Chicken_B/S Breast`
                                               <dbl>
 1                                              646.
 2                                              642.
 3                                              642.
 4                                              642.
 5                                              642.
 6                                              641 
 7                                              642.
 8                                              642.
 9                                              642.
10                                              642.
   `USDA Certified Organic Young Chicken_Bone in Breast`
   <chr>                                                
 1 too few                                              
 2 too few                                              
 3 too few                                              
 4 too few                                              
 5 too few                                              
 6 too few                                              
 7 390.5                                                
 8 390.5                                                
 9 390.5                                                
10 390.5                                                
   `USDA Certified Organic Young Chicken_Whole Legs`
                                               <dbl>
 1                                              194.
 2                                              194.
 3                                              194.
 4                                              194.
 5                                              194.
 6                                              202.
 7                                              204.
 8                                              204.
 9                                              204.
10                                              204.
   `USDA Certified Organic Young Chicken_Thighs`
   <chr>                                        
 1 too few                                      
 2 203                                          
 3 203                                          
 4 203                                          
 5 203                                          
 6 200.375                                      
 7 199.5                                        
 8 199.5                                        
 9 199.5                                        
10 199.5                                        
# ℹ 110 more rows

2.2.2 “Organic egg prices” sheet

setwd("C:\\8-601\\challenge_datasets")
organiceggpoultry <- here("organiceggpoultry.xls")
excel_sheets("organiceggpoultry.xls")
[1] "Data"                            "Organic egg prices, 2004-13"    
[3] "Organic poultry prices, 2004-13"
organiceggpoultry_egg_prices<- read_excel("organiceggpoultry.xls", sheet = "Organic egg prices, 2004-13", range = "A2:M71", col_names = c("Dozen", "Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sep.", "Oct.",   "Nov.", "Dec.")) 

organiceggpoultry_egg_prices %>% print(n = 10, width = Inf)
# A tibble: 70 × 13
   Dozen                     Jan.   Feb.   Mar.   Apr.   May    June           
   <chr>                     <chr>  <chr>  <chr>  <chr>  <chr>  <chr>          
 1 "Year"                    Jan.   Feb.   Mar.   Apr.   May    June           
 2 "2013"                    <NA>   <NA>   <NA>   <NA>   <NA>   Cents per pound
 3 "Certified Organic Eggs:" <NA>   <NA>   <NA>   <NA>   <NA>   <NA>           
 4 "Extra Large \nDozen"     290    290    290    290    290    290            
 5 "Extra Large 1/2 Doz."    188.13 188.13 188.13 188.13 188.13 188.13         
 6 "Large \nDozen"           267.5  267.5  267.5  267.5  267.5  267.5          
 7 "Large \n1/2 Doz."        178    178    178    178    178    178            
 8  <NA>                     Jan.   Feb.   Mar.   Apr.   May    June           
 9 "2012"                    <NA>   <NA>   <NA>   <NA>   <NA>   Cents per pound
10 "Certified Organic Eggs:" <NA>   <NA>   <NA>   <NA>   <NA>   <NA>           
   July   Aug.   Sep.   Oct.   Nov.   Dec.  
   <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
 1 July   Aug.   Sep.   Oct.   Nov.   Dec.  
 2 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
 3 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
 4 290    290    290    290    290    290   
 5 188.13 188.13 188.13 188.13 188.13 188.13
 6 267.5  267.5  267.5  267.5  267.5  267.5 
 7 178    178    178    178    178    178   
 8 July   Aug.   Sep.   Oct.   Nov.   Dec.  
 9 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
10 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
# ℹ 60 more rows

2.2.3 “Organic poultry prices” sheet

setwd("C:\\8-601\\challenge_datasets")
organiceggpoultry <- here("organiceggpoultry.xls")
excel_sheets("organiceggpoultry.xls")
[1] "Data"                            "Organic egg prices, 2004-13"    
[3] "Organic poultry prices, 2004-13"
organiceggpoultry_poultry_prices<- read_excel("organiceggpoultry.xls", sheet = "Organic poultry prices, 2004-13", range = "A2:M71", col_names = c("Dozen", "Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sep.", "Oct.",   "Nov.", "Dec.")) 

organiceggpoultry_poultry_prices %>% print(n = 10, width = Inf)
# A tibble: 70 × 13
   Dozen                  Jan.   Feb.   Mar.   Apr.   May    June           
   <chr>                  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>          
 1 Year                   Jan.   Feb.   Mar.   Apr.   May    June           
 2 2013                   <NA>   <NA>   <NA>   <NA>   <NA>   Cents per pound
 3 Organic young chicken: <NA>   <NA>   <NA>   <NA>   <NA>   <NA>           
 4 Whole                  238.5  238.5  238.5  238.5  238.5  238.5          
 5 B/S Breast             703.75 703.75 703.75 703.75 703.75 703.75         
 6 Bone-in Breast         390.5  390.5  390.5  390.5  390.5  390.5          
 7 Whole Legs             203.5  203.5  203.5  203.5  203.5  203.5          
 8 Thighs                 216.25 216.25 216.25 216.25 216.25 216.25         
 9 <NA>                   Jan.   Feb.   Mar.   Apr.   May    June           
10 2012                   <NA>   <NA>   <NA>   <NA>   <NA>   Cents per pound
   July   Aug.   Sep.   Oct.   Nov.   Dec.  
   <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
 1 July   Aug.   Sep.   Oct.   Nov.   Dec.  
 2 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
 3 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
 4 238.5  238.5  238.5  238.5  238.5  238.5 
 5 703.75 703.75 703.75 703.75 703.75 703.75
 6 390.5  390.5  390.5  390.5  390.5  390.5 
 7 203.5  203.5  203.5  203.5  203.5  203.5 
 8 216.25 216.25 216.25 216.25 216.25 216.25
 9 July   Aug.   Sep.   Oct.   Nov.   Dec.  
10 <NA>   <NA>   <NA>   <NA>   <NA>   <NA>  
# ℹ 60 more rows

2.3 “FedFundsRate” data set

setwd("C:\\8-601\\challenge_datasets")
FedFundsRate <- read.csv("FedFundsRate.csv")

tibble(FedFundsRate) %>% print(n = 10, width = Inf)
# A tibble: 904 × 10
    Year Month   Day Federal.Funds.Target.Rate Federal.Funds.Upper.Target
   <int> <int> <int>                     <dbl>                      <dbl>
 1  1954     7     1                        NA                         NA
 2  1954     8     1                        NA                         NA
 3  1954     9     1                        NA                         NA
 4  1954    10     1                        NA                         NA
 5  1954    11     1                        NA                         NA
 6  1954    12     1                        NA                         NA
 7  1955     1     1                        NA                         NA
 8  1955     2     1                        NA                         NA
 9  1955     3     1                        NA                         NA
10  1955     4     1                        NA                         NA
   Federal.Funds.Lower.Target Effective.Federal.Funds.Rate
                        <dbl>                        <dbl>
 1                         NA                         0.8 
 2                         NA                         1.22
 3                         NA                         1.06
 4                         NA                         0.85
 5                         NA                         0.83
 6                         NA                         1.28
 7                         NA                         1.39
 8                         NA                         1.29
 9                         NA                         1.35
10                         NA                         1.43
   Real.GDP..Percent.Change. Unemployment.Rate Inflation.Rate
                       <dbl>             <dbl>          <dbl>
 1                       4.6               5.8             NA
 2                      NA                 6               NA
 3                      NA                 6.1             NA
 4                       8                 5.7             NA
 5                      NA                 5.3             NA
 6                      NA                 5               NA
 7                      11.9               4.9             NA
 8                      NA                 4.7             NA
 9                      NA                 4.6             NA
10                       6.7               4.7             NA
# ℹ 894 more rows

2.4 “hotel_bookings” data set

setwd("C:\\8-601\\challenge_datasets")
hotel_bookings <- read.csv("hotel_bookings.csv")

tibble (hotel_bookings) %>% print(n = 10, width = Inf)
# A tibble: 119,390 × 32
   hotel        is_canceled lead_time arrival_date_year arrival_date_month
   <chr>              <int>     <int>             <int> <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              
 6 Resort Hotel           0        14              2015 July              
 7 Resort Hotel           0         0              2015 July              
 8 Resort Hotel           0         9              2015 July              
 9 Resort Hotel           1        85              2015 July              
10 Resort Hotel           1        75              2015 July              
   arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights
                      <int>                     <int>                   <int>
 1                       27                         1                       0
 2                       27                         1                       0
 3                       27                         1                       0
 4                       27                         1                       0
 5                       27                         1                       0
 6                       27                         1                       0
 7                       27                         1                       0
 8                       27                         1                       0
 9                       27                         1                       0
10                       27                         1                       0
   stays_in_week_nights adults children babies meal  country market_segment
                  <int>  <int>    <int>  <int> <chr> <chr>   <chr>         
 1                    0      2        0      0 BB    PRT     Direct        
 2                    0      2        0      0 BB    PRT     Direct        
 3                    1      1        0      0 BB    GBR     Direct        
 4                    1      1        0      0 BB    GBR     Corporate     
 5                    2      2        0      0 BB    GBR     Online TA     
 6                    2      2        0      0 BB    GBR     Online TA     
 7                    2      2        0      0 BB    PRT     Direct        
 8                    2      2        0      0 FB    PRT     Direct        
 9                    3      2        0      0 BB    PRT     Online TA     
10                    3      2        0      0 HB    PRT     Offline TA/TO 
   distribution_channel is_repeated_guest previous_cancellations
   <chr>                            <int>                  <int>
 1 Direct                               0                      0
 2 Direct                               0                      0
 3 Direct                               0                      0
 4 Corporate                            0                      0
 5 TA/TO                                0                      0
 6 TA/TO                                0                      0
 7 Direct                               0                      0
 8 Direct                               0                      0
 9 TA/TO                                0                      0
10 TA/TO                                0                      0
   previous_bookings_not_canceled reserved_room_type assigned_room_type
                            <int> <chr>              <chr>             
 1                              0 C                  C                 
 2                              0 C                  C                 
 3                              0 A                  C                 
 4                              0 A                  A                 
 5                              0 A                  A                 
 6                              0 A                  A                 
 7                              0 C                  C                 
 8                              0 C                  C                 
 9                              0 A                  A                 
10                              0 D                  D                 
   booking_changes deposit_type agent company days_in_waiting_list customer_type
             <int> <chr>        <chr> <chr>                  <int> <chr>        
 1               3 No Deposit   NULL  NULL                       0 Transient    
 2               4 No Deposit   NULL  NULL                       0 Transient    
 3               0 No Deposit   NULL  NULL                       0 Transient    
 4               0 No Deposit   304   NULL                       0 Transient    
 5               0 No Deposit   240   NULL                       0 Transient    
 6               0 No Deposit   240   NULL                       0 Transient    
 7               0 No Deposit   NULL  NULL                       0 Transient    
 8               0 No Deposit   303   NULL                       0 Transient    
 9               0 No Deposit   240   NULL                       0 Transient    
10               0 No Deposit   15    NULL                       0 Transient    
     adr required_car_parking_spaces total_of_special_requests
   <dbl>                       <int>                     <int>
 1    0                            0                         0
 2    0                            0                         0
 3   75                            0                         0
 4   75                            0                         0
 5   98                            0                         1
 6   98                            0                         1
 7  107                            0                         0
 8  103                            0                         1
 9   82                            0                         1
10  106.                           0                         0
   reservation_status reservation_status_date
   <chr>              <chr>                  
 1 Check-Out          2015-07-01             
 2 Check-Out          2015-07-01             
 3 Check-Out          2015-07-02             
 4 Check-Out          2015-07-02             
 5 Check-Out          2015-07-03             
 6 Check-Out          2015-07-03             
 7 Check-Out          2015-07-03             
 8 Check-Out          2015-07-03             
 9 Canceled           2015-05-06             
10 Canceled           2015-04-22             
# ℹ 119,380 more rows

3. clean the data

3.1 “poultry” data set

Arrange the “product” column through the alphabet order, which makes the table cleaner and easier to analyze through different categories in the “product” column.

poultry_clean <- poultry %>%
  arrange(Product)

tibble(poultry_clean) %>% print(n = 10, width = Inf)
# A tibble: 600 × 4
   Product     Year Month     Price_Dollar
   <chr>      <dbl> <chr>            <dbl>
 1 B/S Breast  2013 January           7.04
 2 B/S Breast  2013 February          7.04
 3 B/S Breast  2013 March             7.04
 4 B/S Breast  2013 April             7.04
 5 B/S Breast  2013 May               7.04
 6 B/S Breast  2013 June              7.04
 7 B/S Breast  2013 July              7.04
 8 B/S Breast  2013 August            7.04
 9 B/S Breast  2013 September         7.04
10 B/S Breast  2013 October           7.04
# ℹ 590 more rows

3.2 “organiceggpoultry” data set

3.2.1 “whole data” sheet

Cleaning the “whole data” sheet through 1) separating the “Year and Month” column and putting the information into “month” and “year” two columns, and 2) cleaning the value “too few” in the “USDA Certified Organic Young Chicken_Bone in Breast” and “USDA Certified Organic Young Chicken_Thighs” columns and change the variables in the columns into numerical variables.

organiceggpoultry_whole_data_clean <- organiceggpoultry_whole_data %>%
 mutate(`Year and Month` = str_remove( `Year and Month`, " /1")) %>%
  separate(`Year and Month`, into=c("month", "year"), sep=" ") %>%
  fill(year)%>%
 mutate(`USDA Certified Organic Young Chicken_Bone in Breast`= str_replace(`USDA Certified Organic Young Chicken_Bone in Breast`,"too few","N/A"),`USDA Certified Organic Young Chicken_Bone in Breast`=as.numeric(`USDA Certified Organic Young Chicken_Bone in Breast`))%>%
  mutate(`USDA Certified Organic Young Chicken_Thighs`= str_replace(`USDA Certified Organic Young Chicken_Thighs`,"too few","N/A"),`USDA Certified Organic Young Chicken_Thighs`=as.numeric(`USDA Certified Organic Young Chicken_Thighs`))

tibble(organiceggpoultry_whole_data_clean)%>% print(n = 10, width = Inf)
# A tibble: 120 × 11
   month     year  `USDA Certified Organic Eggs_Extra Large Dozen`
   <chr>     <chr>                                           <dbl>
 1 Jan       2004                                             230 
 2 February  2004                                             230 
 3 March     2004                                             230 
 4 April     2004                                             234.
 5 May       2004                                             236 
 6 June      2004                                             241 
 7 July      2004                                             241 
 8 August    2004                                             241 
 9 September 2004                                             241 
10 October   2004                                             241 
   `USDA Certified Organic Eggs_Extra Large 1/2 Doz`
                                               <dbl>
 1                                              132 
 2                                              134.
 3                                              137 
 4                                              137 
 5                                              137 
 6                                              137 
 7                                              137 
 8                                              137 
 9                                              136.
10                                              136.
   `USDA Certified Organic Eggs_Large Dozen`
                                       <dbl>
 1                                      230 
 2                                      226.
 3                                      225 
 4                                      225 
 5                                      225 
 6                                      231.
 7                                      234.
 8                                      234.
 9                                      234.
10                                      234.
   `USDA Certified Organic Eggs_Large 1/2 Doz`
                                         <dbl>
 1                                        126 
 2                                        128.
 3                                        131 
 4                                        131 
 5                                        131 
 6                                        134.
 7                                        134.
 8                                        134.
 9                                        130.
10                                        128.
   `USDA Certified Organic Young Chicken_Whole`
                                          <dbl>
 1                                         198.
 2                                         198.
 3                                         209 
 4                                         212 
 5                                         214.
 6                                         216.
 7                                         217 
 8                                         217 
 9                                         217 
10                                         217 
   `USDA Certified Organic Young Chicken_B/S Breast`
                                               <dbl>
 1                                              646.
 2                                              642.
 3                                              642.
 4                                              642.
 5                                              642.
 6                                              641 
 7                                              642.
 8                                              642.
 9                                              642.
10                                              642.
   `USDA Certified Organic Young Chicken_Bone in Breast`
                                                   <dbl>
 1                                                   NA 
 2                                                   NA 
 3                                                   NA 
 4                                                   NA 
 5                                                   NA 
 6                                                   NA 
 7                                                  390.
 8                                                  390.
 9                                                  390.
10                                                  390.
   `USDA Certified Organic Young Chicken_Whole Legs`
                                               <dbl>
 1                                              194.
 2                                              194.
 3                                              194.
 4                                              194.
 5                                              194.
 6                                              202.
 7                                              204.
 8                                              204.
 9                                              204.
10                                              204.
   `USDA Certified Organic Young Chicken_Thighs`
                                           <dbl>
 1                                           NA 
 2                                          203 
 3                                          203 
 4                                          203 
 5                                          203 
 6                                          200.
 7                                          200.
 8                                          200.
 9                                          200.
10                                          200.
# ℹ 110 more rows

3.2.2 “Organic egg prices” sheet

Cleaning the “Organic egg prices” sheet by cleaning the titles and empty cells.

organiceggpoultry_egg_prices_clean<- organiceggpoultry_egg_prices %>%
mutate(year=case_when (str_starts(Dozen,"20")~ Dozen))%>%
fill(year, .direction = "down")%>%
filter(!str_detect(Dozen, "Year"))%>%
  drop_na%>%
  mutate(Type="Certified Organic Eggs")
  
 
tibble (organiceggpoultry_egg_prices_clean) %>% print(n = 10, width = Inf)
# A tibble: 40 × 15
   Dozen                  Jan.   Feb.   Mar.   Apr.   May    June   July  
   <chr>                  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
 1 "Extra Large \nDozen"  290    290    290    290    290    290    290   
 2 "Extra Large 1/2 Doz." 188.13 188.13 188.13 188.13 188.13 188.13 188.13
 3 "Large \nDozen"        267.5  267.5  267.5  267.5  267.5  267.5  267.5 
 4 "Large \n1/2 Doz."     178    178    178    178    178    178    178   
 5 "Extra Large \nDozen"  288.5  288.5  288.5  288.5  288.5  288.5  288.5 
 6 "Extra Large 1/2 Doz." 185.5  185.5  185.5  185.5  185.5  185.5  185.5 
 7 "Large \nDozen"        267.5  267.5  267.5  267.5  267.5  267.5  267.5 
 8 "Large \n1/2 Doz."     174.5  174.5  173.25 173.25 173.25 173.25 173.25
 9 "Extra Large \nDozen"  285.5  285.5  285.5  285.5  285.5  285.5  285.5 
10 "Extra Large 1/2 Doz." 185.5  185.5  185.5  185.5  185.5  185.5  185.5 
   Aug.   Sep.   Oct.   Nov.   Dec.   year  Type                  
   <chr>  <chr>  <chr>  <chr>  <chr>  <chr> <chr>                 
 1 290    290    290    290    290    2013  Certified Organic Eggs
 2 188.13 188.13 188.13 188.13 188.13 2013  Certified Organic Eggs
 3 267.5  267.5  267.5  267.5  267.5  2013  Certified Organic Eggs
 4 178    178    178    178    178    2013  Certified Organic Eggs
 5 288.5  290    290    290    290    2012  Certified Organic Eggs
 6 185.5  188.13 188.13 188.13 188.13 2012  Certified Organic Eggs
 7 267.5  267.5  267.5  267.5  267.5  2012  Certified Organic Eggs
 8 173.25 178    178    178    178    2012  Certified Organic Eggs
 9 285.5  285.5  285.5  285.5  288.5  2011  Certified Organic Eggs
10 185.5  185.5  185.5  185.5  185.5  2011  Certified Organic Eggs
# ℹ 30 more rows

3.2.3 “Organic poultry prices” sheet

Cleaning the “Organic egg prices” sheet by cleaning the titles and empty cells.

organiceggpoultry_poultry_prices_clean<- organiceggpoultry_poultry_prices %>%
mutate(year=case_when (str_starts(Dozen,"20")~ Dozen))%>%
fill(year, .direction = "down")%>%
filter(!str_detect(Dozen, "Year"))%>%
  drop_na%>%
  mutate(Type="Organic young chicken")
  
 
tibble(organiceggpoultry_poultry_prices_clean) %>% print(n = 10, width = Inf)
# A tibble: 43 × 15
   Dozen          Jan.   Feb.   Mar.   Apr.   May    June   July   Aug.   Sep.  
   <chr>          <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr>  <chr> 
 1 Whole          238.5  238.5  238.5  238.5  238.5  238.5  238.5  238.5  238.5 
 2 B/S Breast     703.75 703.75 703.75 703.75 703.75 703.75 703.75 703.75 703.75
 3 Bone-in Breast 390.5  390.5  390.5  390.5  390.5  390.5  390.5  390.5  390.5 
 4 Whole Legs     203.5  203.5  203.5  203.5  203.5  203.5  203.5  203.5  203.5 
 5 Thighs         216.25 216.25 216.25 216.25 216.25 216.25 216.25 216.25 216.25
 6 Whole          235    238.5  238.5  238.5  238.5  238.5  238.5  238.5  238.5 
 7 B/S Breast     637.5  700    700    700    700    700    700    700    700   
 8 Bone-in Breast 390.5  390.5  390.5  390.5  390.5  390.5  390.5  390.5  390.5 
 9 Whole Legs     203.5  203.5  203.5  203.5  203.5  203.5  203.5  203.5  203.5 
10 Thighs         215    215    215    215    215    215    215    216.25 216.25
   Oct.   Nov.   Dec.   year  Type                 
   <chr>  <chr>  <chr>  <chr> <chr>                
 1 238.5  238.5  238.5  2013  Organic young chicken
 2 703.75 703.75 703.75 2013  Organic young chicken
 3 390.5  390.5  390.5  2013  Organic young chicken
 4 203.5  203.5  203.5  2013  Organic young chicken
 5 216.25 216.25 216.25 2013  Organic young chicken
 6 238.5  238.5  238.5  2012  Organic young chicken
 7 700    703.75 703.75 2012  Organic young chicken
 8 390.5  390.5  390.5  2012  Organic young chicken
 9 203.5  203.5  203.5  2012  Organic young chicken
10 216.25 216.25 216.25 2012  Organic young chicken
# ℹ 33 more rows

3.3 “FedFundsRate” data set

Cleaning the “FedFundsRate” data set by 1) cleaning the “month” column with the “month. name” function to make values in the column easier to understand and 2) pulling out the nulls with no values.

FedFundsRate_clean <- FedFundsRate %>%
  mutate(Month=month.name[Month])

tibble(FedFundsRate_clean) %>% print(n = 10, width = Inf)
# A tibble: 904 × 10
    Year Month       Day Federal.Funds.Target.Rate Federal.Funds.Upper.Target
   <int> <chr>     <int>                     <dbl>                      <dbl>
 1  1954 July          1                        NA                         NA
 2  1954 August        1                        NA                         NA
 3  1954 September     1                        NA                         NA
 4  1954 October       1                        NA                         NA
 5  1954 November      1                        NA                         NA
 6  1954 December      1                        NA                         NA
 7  1955 January       1                        NA                         NA
 8  1955 February      1                        NA                         NA
 9  1955 March         1                        NA                         NA
10  1955 April         1                        NA                         NA
   Federal.Funds.Lower.Target Effective.Federal.Funds.Rate
                        <dbl>                        <dbl>
 1                         NA                         0.8 
 2                         NA                         1.22
 3                         NA                         1.06
 4                         NA                         0.85
 5                         NA                         0.83
 6                         NA                         1.28
 7                         NA                         1.39
 8                         NA                         1.29
 9                         NA                         1.35
10                         NA                         1.43
   Real.GDP..Percent.Change. Unemployment.Rate Inflation.Rate
                       <dbl>             <dbl>          <dbl>
 1                       4.6               5.8             NA
 2                      NA                 6               NA
 3                      NA                 6.1             NA
 4                       8                 5.7             NA
 5                      NA                 5.3             NA
 6                      NA                 5               NA
 7                      11.9               4.9             NA
 8                      NA                 4.7             NA
 9                      NA                 4.6             NA
10                       6.7               4.7             NA
# ℹ 894 more rows
FedFundsRate_no_value <- FedFundsRate %>%
  summarise(across(everything(),~sum(is.na(.x)))) %>%
  pivot_longer(everything()) %>%
  arrange(desc(value))

tibble(FedFundsRate_no_value) %>% print(n = 10, width = Inf)
# A tibble: 10 × 2
   name                         value
   <chr>                        <int>
 1 Federal.Funds.Upper.Target     801
 2 Federal.Funds.Lower.Target     801
 3 Real.GDP..Percent.Change.      654
 4 Federal.Funds.Target.Rate      442
 5 Inflation.Rate                 194
 6 Effective.Federal.Funds.Rate   152
 7 Unemployment.Rate              152
 8 Year                             0
 9 Month                            0
10 Day                              0

3.4 “hotel_bookings” data set

Cleaning the “hotel_bookings” data set by mutating “arrival_date_year,” “arrival_date_month,” and “arrival_date_day_of_month” into the “arrival_date” column.

hotel_bookings_clean <- hotel_bookings %>%
  unite(arrival_date, arrival_date_year,arrival_date_month, arrival_date_day_of_month)%>%
  mutate(arrival_date= ymd(arrival_date))

tibble(hotel_bookings_clean)%>% print(n = 10, width = Inf)
# A tibble: 119,390 × 30
   hotel        is_canceled lead_time arrival_date arrival_date_week_number
   <chr>              <int>     <int> <date>                          <int>
 1 Resort Hotel           0       342 2015-07-01                         27
 2 Resort Hotel           0       737 2015-07-01                         27
 3 Resort Hotel           0         7 2015-07-01                         27
 4 Resort Hotel           0        13 2015-07-01                         27
 5 Resort Hotel           0        14 2015-07-01                         27
 6 Resort Hotel           0        14 2015-07-01                         27
 7 Resort Hotel           0         0 2015-07-01                         27
 8 Resort Hotel           0         9 2015-07-01                         27
 9 Resort Hotel           1        85 2015-07-01                         27
10 Resort Hotel           1        75 2015-07-01                         27
   stays_in_weekend_nights stays_in_week_nights adults children babies meal 
                     <int>                <int>  <int>    <int>  <int> <chr>
 1                       0                    0      2        0      0 BB   
 2                       0                    0      2        0      0 BB   
 3                       0                    1      1        0      0 BB   
 4                       0                    1      1        0      0 BB   
 5                       0                    2      2        0      0 BB   
 6                       0                    2      2        0      0 BB   
 7                       0                    2      2        0      0 BB   
 8                       0                    2      2        0      0 FB   
 9                       0                    3      2        0      0 BB   
10                       0                    3      2        0      0 HB   
   country market_segment distribution_channel is_repeated_guest
   <chr>   <chr>          <chr>                            <int>
 1 PRT     Direct         Direct                               0
 2 PRT     Direct         Direct                               0
 3 GBR     Direct         Direct                               0
 4 GBR     Corporate      Corporate                            0
 5 GBR     Online TA      TA/TO                                0
 6 GBR     Online TA      TA/TO                                0
 7 PRT     Direct         Direct                               0
 8 PRT     Direct         Direct                               0
 9 PRT     Online TA      TA/TO                                0
10 PRT     Offline TA/TO  TA/TO                                0
   previous_cancellations previous_bookings_not_canceled reserved_room_type
                    <int>                          <int> <chr>             
 1                      0                              0 C                 
 2                      0                              0 C                 
 3                      0                              0 A                 
 4                      0                              0 A                 
 5                      0                              0 A                 
 6                      0                              0 A                 
 7                      0                              0 C                 
 8                      0                              0 C                 
 9                      0                              0 A                 
10                      0                              0 D                 
   assigned_room_type booking_changes deposit_type agent company
   <chr>                        <int> <chr>        <chr> <chr>  
 1 C                                3 No Deposit   NULL  NULL   
 2 C                                4 No Deposit   NULL  NULL   
 3 C                                0 No Deposit   NULL  NULL   
 4 A                                0 No Deposit   304   NULL   
 5 A                                0 No Deposit   240   NULL   
 6 A                                0 No Deposit   240   NULL   
 7 C                                0 No Deposit   NULL  NULL   
 8 C                                0 No Deposit   303   NULL   
 9 A                                0 No Deposit   240   NULL   
10 D                                0 No Deposit   15    NULL   
   days_in_waiting_list customer_type   adr required_car_parking_spaces
                  <int> <chr>         <dbl>                       <int>
 1                    0 Transient        0                            0
 2                    0 Transient        0                            0
 3                    0 Transient       75                            0
 4                    0 Transient       75                            0
 5                    0 Transient       98                            0
 6                    0 Transient       98                            0
 7                    0 Transient      107                            0
 8                    0 Transient      103                            0
 9                    0 Transient       82                            0
10                    0 Transient      106.                           0
   total_of_special_requests reservation_status reservation_status_date
                       <int> <chr>              <chr>                  
 1                         0 Check-Out          2015-07-01             
 2                         0 Check-Out          2015-07-01             
 3                         0 Check-Out          2015-07-02             
 4                         0 Check-Out          2015-07-02             
 5                         1 Check-Out          2015-07-03             
 6                         1 Check-Out          2015-07-03             
 7                         0 Check-Out          2015-07-03             
 8                         1 Check-Out          2015-07-03             
 9                         1 Canceled           2015-05-06             
10                         0 Canceled           2015-04-22             
# ℹ 119,380 more rows

4. Pivot the data

4.1 “poultry” data set

Using the “pivot_wider” function to make each category in “Product” into columns, which makes the column wider and the table easier to understand.

poultry_pivot <- poultry_clean %>%
pivot_wider(names_from = "Product", values_from = "Price_Dollar")

tibble(poultry_pivot)%>% print(n = 10, width = Inf)
# A tibble: 120 × 7
    Year Month     `B/S Breast` `Bone-in Breast` Thighs Whole `Whole Legs`
   <dbl> <chr>            <dbl>            <dbl>  <dbl> <dbl>        <dbl>
 1  2013 January           7.04             3.90   2.16  2.38         2.04
 2  2013 February          7.04             3.90   2.16  2.38         2.04
 3  2013 March             7.04             3.90   2.16  2.38         2.04
 4  2013 April             7.04             3.90   2.16  2.38         2.04
 5  2013 May               7.04             3.90   2.16  2.38         2.04
 6  2013 June              7.04             3.90   2.16  2.38         2.04
 7  2013 July              7.04             3.90   2.16  2.38         2.04
 8  2013 August            7.04             3.90   2.16  2.38         2.04
 9  2013 September         7.04             3.90   2.16  2.38         2.04
10  2013 October           7.04             3.90   2.16  2.38         2.04
# ℹ 110 more rows

4.2 “organiceggpoultry” data set

4.2.1 “whole data” sheet

Pivoting the “whole data” sheet by putting the titles from “USDA Certified Organic Eggs_Extra Large Dozen” to “USDA Certified Organic Young Chicken_Thighs_clean” into the “element&dozen”column.

organiceggpoultry_whole_data_pivot <- pivot_longer(organiceggpoultry_whole_data_clean, cols = c("USDA Certified Organic Eggs_Extra Large Dozen", "USDA Certified Organic Eggs_Extra Large 1/2 Doz", "USDA Certified Organic Eggs_Large Dozen",  "USDA Certified Organic Eggs_Large 1/2 Doz", "USDA Certified Organic Young Chicken_Whole", "USDA Certified Organic Young Chicken_B/S Breast", "USDA Certified Organic Young Chicken_Bone in Breast","USDA Certified Organic Young Chicken_Whole Legs", "USDA Certified Organic Young Chicken_Thighs"), names_to = "element&dozen", values_to = "value")

tibble(organiceggpoultry_whole_data_pivot)%>% print(n = 10, width = Inf)
# A tibble: 1,080 × 4
   month    year  `element&dozen`                                     value
   <chr>    <chr> <chr>                                               <dbl>
 1 Jan      2004  USDA Certified Organic Eggs_Extra Large Dozen        230 
 2 Jan      2004  USDA Certified Organic Eggs_Extra Large 1/2 Doz      132 
 3 Jan      2004  USDA Certified Organic Eggs_Large Dozen              230 
 4 Jan      2004  USDA Certified Organic Eggs_Large 1/2 Doz            126 
 5 Jan      2004  USDA Certified Organic Young Chicken_Whole           198.
 6 Jan      2004  USDA Certified Organic Young Chicken_B/S Breast      646.
 7 Jan      2004  USDA Certified Organic Young Chicken_Bone in Breast   NA 
 8 Jan      2004  USDA Certified Organic Young Chicken_Whole Legs      194.
 9 Jan      2004  USDA Certified Organic Young Chicken_Thighs           NA 
10 February 2004  USDA Certified Organic Eggs_Extra Large Dozen        230 
# ℹ 1,070 more rows

4.2.2 “Organic egg prices” sheet

Pivoting the “Organic egg prices” sheet by putting the titles from “Jan.” to “Dec.” into the “months” column.

organiceggpoultry_egg_prices_clean_pivot <- pivot_longer(organiceggpoultry_egg_prices_clean, cols = c("Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sep.", "Oct.",    "Nov.", "Dec."), names_to = "months", values_to = "value")

organiceggpoultry_egg_prices_clean_pivot%>% print(n = 10, width = Inf)
# A tibble: 480 × 5
   Dozen                 year  Type                   months value
   <chr>                 <chr> <chr>                  <chr>  <chr>
 1 "Extra Large \nDozen" 2013  Certified Organic Eggs Jan.   290  
 2 "Extra Large \nDozen" 2013  Certified Organic Eggs Feb.   290  
 3 "Extra Large \nDozen" 2013  Certified Organic Eggs Mar.   290  
 4 "Extra Large \nDozen" 2013  Certified Organic Eggs Apr.   290  
 5 "Extra Large \nDozen" 2013  Certified Organic Eggs May    290  
 6 "Extra Large \nDozen" 2013  Certified Organic Eggs June   290  
 7 "Extra Large \nDozen" 2013  Certified Organic Eggs July   290  
 8 "Extra Large \nDozen" 2013  Certified Organic Eggs Aug.   290  
 9 "Extra Large \nDozen" 2013  Certified Organic Eggs Sep.   290  
10 "Extra Large \nDozen" 2013  Certified Organic Eggs Oct.   290  
# ℹ 470 more rows

4.2.3 “Organic poultry prices” sheet

Pivoting the “Organic egg prices” sheet by putting the titles from “Jan.” to “Dec.” into the “months” column.

organiceggpoultry_poultry_prices_clean_pivot <- pivot_longer(organiceggpoultry_poultry_prices_clean, cols = c("Jan.", "Feb.", "Mar.", "Apr.", "May", "June", "July", "Aug.", "Sep.", "Oct.",    "Nov.", "Dec."), names_to = "months", values_to = "value")

organiceggpoultry_poultry_prices_clean_pivot%>% print(n = 10, width = Inf)
# A tibble: 516 × 5
   Dozen year  Type                  months value
   <chr> <chr> <chr>                 <chr>  <chr>
 1 Whole 2013  Organic young chicken Jan.   238.5
 2 Whole 2013  Organic young chicken Feb.   238.5
 3 Whole 2013  Organic young chicken Mar.   238.5
 4 Whole 2013  Organic young chicken Apr.   238.5
 5 Whole 2013  Organic young chicken May    238.5
 6 Whole 2013  Organic young chicken June   238.5
 7 Whole 2013  Organic young chicken July   238.5
 8 Whole 2013  Organic young chicken Aug.   238.5
 9 Whole 2013  Organic young chicken Sep.   238.5
10 Whole 2013  Organic young chicken Oct.   238.5
# ℹ 506 more rows

4.3 “FedFundsRate” data set

Pivoting the “FedFundsRate” sheet by putting the titles from “Federal.Funds.Target.Rate” to “Inflation.Rate” into the “Rate_Types” column.

FedFundsRate_pivot <- FedFundsRate_clean%>%
  pivot_longer(cols = c(Federal.Funds.Target.Rate:Inflation.Rate), names_to = "Rate_Types", values_to = "value")

FedFundsRate_pivot%>% print(n = 10, width = Inf)
# A tibble: 6,328 × 5
    Year Month    Day Rate_Types                   value
   <int> <chr>  <int> <chr>                        <dbl>
 1  1954 July       1 Federal.Funds.Target.Rate     NA  
 2  1954 July       1 Federal.Funds.Upper.Target    NA  
 3  1954 July       1 Federal.Funds.Lower.Target    NA  
 4  1954 July       1 Effective.Federal.Funds.Rate   0.8
 5  1954 July       1 Real.GDP..Percent.Change.      4.6
 6  1954 July       1 Unemployment.Rate              5.8
 7  1954 July       1 Inflation.Rate                NA  
 8  1954 August     1 Federal.Funds.Target.Rate     NA  
 9  1954 August     1 Federal.Funds.Upper.Target    NA  
10  1954 August     1 Federal.Funds.Lower.Target    NA  
# ℹ 6,318 more rows

4.4 “hotel_bookings” data set

As this is a wide data set containing information, keeping the data set after cleaning is a better choice than doing further pivoting by using the “pivot_longer” and “pivot_wider” functions.

5. Data analysis

Analysis of the data sets by calculating the mean, median, standard deviation, mode, and quantile.

5.1 “poultry” data set

# Analysis the values in the "B/S Breast" column though grouping by "Year". 

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

brest_summary <- poultry_pivot%>%
  group_by(Year)%>%
  summarise(`breast_mean_number` = mean(`B/S Breast`, na.rm =TRUE), `breast_median_number` = median(`B/S Breast`, na.rm =TRUE), `breast_sd_number` = sd(`B/S Breast`,na.rm =TRUE), `breast_max_number`=max(`B/S Breast`, na.rm =TRUE), `breast_min_number`=min(`B/S Breast`, na.rm =TRUE), `breast_mode_number`= find_mode(`B/S Breast`), `breast_quant25` = quantile(`B/S Breast`, na.rm = T, probs = q[1]),`breast_quant50` = quantile(`B/S Breast`, na.rm = T, probs = q[2]), `breast_quant75` = quantile(`B/S Breast`, na.rm = T, probs = q[3])) %>%
  ungroup()

brest_summary%>% print(n = 10, width = Inf)
# A tibble: 10 × 10
    Year breast_mean_number breast_median_number breast_sd_number
   <dbl>              <dbl>                <dbl>            <dbl>
 1  2004               6.43                 6.42          0.0100 
 2  2005               6.45                 6.46          0.00433
 3  2006               6.46                 6.46          0      
 4  2007               6.46                 6.46          0      
 5  2008               6.46                 6.46          0      
 6  2009               6.46                 6.46          0      
 7  2010               6.43                 6.46          0.0385 
 8  2011               6.38                 6.38          0      
 9  2012               6.95                 7             0.183  
10  2013               7.04                 7.04          0      
   breast_max_number breast_min_number breast_mode_number breast_quant25
               <dbl>             <dbl>              <dbl>          <dbl>
 1              6.46              6.41               6.42           6.42
 2              6.46              6.44               6.46           6.46
 3              6.46              6.46               6.46           6.46
 4              6.46              6.46               6.46           6.46
 5              6.46              6.46               6.46           6.46
 6              6.46              6.46               6.46           6.46
 7              6.46              6.38               6.46           6.38
 8              6.38              6.38               6.38           6.38
 9              7.04              6.38               7              7   
10              7.04              7.04               7.04           7.04
   breast_quant50 breast_quant75
            <dbl>          <dbl>
 1           6.42           6.42
 2           6.46           6.46
 3           6.46           6.46
 4           6.46           6.46
 5           6.46           6.46
 6           6.46           6.46
 7           6.46           6.46
 8           6.38           6.38
 9           7              7   
10           7.04           7.04
# Analysis of the values in the "Bone-in Breast" column through grouping by "Year".

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

bone_summary <- poultry_pivot%>%
  group_by(Year)%>%
  summarise(`bone_mean_number` = mean(`Bone-in Breast`, na.rm =TRUE), `bone_median_number` = median(`Bone-in Breast`, na.rm =TRUE), `bone_sd_number` = sd(`Bone-in Breast`,na.rm =TRUE), `bone_max_number`=max(`Bone-in Breast`, na.rm =TRUE), `bone_min_number`=min(`Bone-in Breast`, na.rm =TRUE), `bone_mode_number`= find_mode(`Bone-in Breast`), `bone_quant25` = quantile(`Bone-in Breast`, na.rm = T, probs = q[1]),`bone_quant50` = quantile(`Bone-in Breast`, na.rm = T, probs = q[2]), `bone_quant75` = quantile(`Bone-in Breast`, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'Year'. You can override using the
`.groups` argument.
bone_summary%>% print(n = 10, width = Inf)
# A tibble: 11 × 10
    Year bone_mean_number bone_median_number bone_sd_number bone_max_number
   <dbl>            <dbl>              <dbl>          <dbl>           <dbl>
 1  2004             3.90               3.90              0            3.90
 2  2004             3.90               3.90              0            3.90
 3  2005             3.90               3.90              0            3.90
 4  2006             3.90               3.90              0            3.90
 5  2007             3.90               3.90              0            3.90
 6  2008             3.90               3.90              0            3.90
 7  2009             3.90               3.90              0            3.90
 8  2010             3.90               3.90              0            3.90
 9  2011             3.90               3.90              0            3.90
10  2012             3.90               3.90              0            3.90
   bone_min_number bone_mode_number bone_quant25 bone_quant50 bone_quant75
             <dbl>            <dbl>        <dbl>        <dbl>        <dbl>
 1            3.90            NA            3.90         3.90         3.90
 2            3.90             3.90         3.90         3.90         3.90
 3            3.90             3.90         3.90         3.90         3.90
 4            3.90             3.90         3.90         3.90         3.90
 5            3.90             3.90         3.90         3.90         3.90
 6            3.90             3.90         3.90         3.90         3.90
 7            3.90             3.90         3.90         3.90         3.90
 8            3.90             3.90         3.90         3.90         3.90
 9            3.90             3.90         3.90         3.90         3.90
10            3.90             3.90         3.90         3.90         3.90
# ℹ 1 more row
# Analysis of the values in the "thighs" column through grouping by "Year".

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

thighs_summary <- poultry_pivot%>%
  group_by(Year)%>%
  summarise(`thighs_mean_number` = mean(`Thighs`, na.rm =TRUE), `thighs_median_number` = median(`Thighs`, na.rm =TRUE), `thighs_sd_number` = sd(`Thighs`,na.rm =TRUE), `thighs_max_number`=max(`Thighs`, na.rm =TRUE), `thighs_min_number`=min(`Thighs`, na.rm =TRUE), `thighs_mode_number`= find_mode(`Thighs`), `thighs_quant25` = quantile(`Thighs`, na.rm = T, probs = q[1]),`thighs_quant50` = quantile(`Thighs`, na.rm = T, probs = q[2]), `thighs_quant75` = quantile(`Thighs`, na.rm = T, probs = q[3])) %>%
  ungroup()

thighs_summary%>% print(n = 10, width = Inf)
# A tibble: 10 × 10
    Year thighs_mean_number thighs_median_number thighs_sd_number
   <dbl>              <dbl>                <dbl>            <dbl>
 1  2004               2.01                 2.00          0.0172 
 2  2005               2.21                 2.22          0.0260 
 3  2006               2.22                 2.22          0      
 4  2007               2.22                 2.22          0      
 5  2008               2.22                 2.22          0      
 6  2009               2.22                 2.22          0      
 7  2010               2.19                 2.22          0.0337 
 8  2011               2.15                 2.15          0      
 9  2012               2.16                 2.15          0.00644
10  2013               2.16                 2.16          0      
   thighs_max_number thighs_min_number thighs_mode_number thighs_quant25
               <dbl>             <dbl>              <dbl>          <dbl>
 1              2.03              2.00               2.00           2.00
 2              2.22              2.13               2.22           2.22
 3              2.22              2.22               2.22           2.22
 4              2.22              2.22               2.22           2.22
 5              2.22              2.22               2.22           2.22
 6              2.22              2.22               2.22           2.22
 7              2.22              2.15               2.22           2.15
 8              2.15              2.15               2.15           2.15
 9              2.16              2.15               2.15           2.15
10              2.16              2.16               2.16           2.16
   thighs_quant50 thighs_quant75
            <dbl>          <dbl>
 1           2.00           2.03
 2           2.22           2.22
 3           2.22           2.22
 4           2.22           2.22
 5           2.22           2.22
 6           2.22           2.22
 7           2.22           2.22
 8           2.15           2.15
 9           2.15           2.16
10           2.16           2.16
# Analysis of the values in the "whole" column through grouping by "Year".

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

whole_summary <- poultry_pivot%>%
  group_by(Year)%>%
  summarise(`whole_mean_number` = mean(`Whole`, na.rm =TRUE), `whole_median_number` = median(`Whole`, na.rm =TRUE), `whole_sd_number` = sd(`Whole`,na.rm =TRUE), `whole_max_number`=max(`Whole`, na.rm =TRUE), `whole_min_number`=min(`Whole`, na.rm =TRUE), `whole_mode_number`= find_mode(`Whole`), `whole_quant25` = quantile(`Whole`, na.rm = T, probs = q[1]),`whole_quant50` = quantile(`Whole`, na.rm = T, probs = q[2]), `whole_quant75` = quantile(`Whole`, na.rm = T, probs = q[3])) %>%
  ungroup()

whole_summary%>% print(n = 10, width = Inf)
# A tibble: 10 × 10
    Year whole_mean_number whole_median_number whole_sd_number whole_max_number
   <dbl>             <dbl>               <dbl>           <dbl>            <dbl>
 1  2004              2.12                2.17          0.0740             2.17
 2  2005              2.17                2.17          0                  2.17
 3  2006              2.20                2.20          0.0135             2.20
 4  2007              2.20                2.20          0                  2.20
 5  2008              2.37                2.48          0.142              2.48
 6  2009              2.48                2.48          0                  2.48
 7  2010              2.39                2.35          0.0577             2.48
 8  2011              2.35                2.35          0                  2.35
 9  2012              2.38                2.38          0.0101             2.38
10  2013              2.38                2.38          0                  2.38
   whole_min_number whole_mode_number whole_quant25 whole_quant50 whole_quant75
              <dbl>             <dbl>         <dbl>         <dbl>         <dbl>
 1             1.98              2.17          2.11          2.17          2.17
 2             2.17              2.17          2.17          2.17          2.17
 3             2.17              2.20          2.20          2.20          2.20
 4             2.20              2.20          2.20          2.20          2.20
 5             2.20              2.48          2.20          2.48          2.48
 6             2.48              2.48          2.48          2.48          2.48
 7             2.35              2.35          2.35          2.35          2.43
 8             2.35              2.35          2.35          2.35          2.35
 9             2.35              2.38          2.38          2.38          2.38
10             2.38              2.38          2.38          2.38          2.38
# Analysis of the values in the "whole_legs" column through grouping by "Year".

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

whole_legs_summary <- poultry_pivot%>%
  group_by(Year)%>%
  summarise(`whole_legs_mean_number` = mean(`Whole Legs`, na.rm =TRUE), `whole_legs_median_number` = median(`Whole Legs`, na.rm =TRUE), `whole_legs_sd_number` = sd(`Whole Legs`,na.rm =TRUE), `whole_legs_max_number`=max(`Whole Legs`, na.rm =TRUE), `whole_legs_min_number`=min(`Whole Legs`, na.rm =TRUE), `whole_legs_mode_number`= find_mode(`Whole Legs`), `whole_legs_quant25` = quantile(`Whole Legs`, na.rm = T, probs = q[1]),`whole_legs_quant50` = quantile(`Whole Legs`, na.rm = T, probs = q[2]), `whole_legs_quant75` = quantile(`Whole Legs`, na.rm = T, probs = q[3])) %>%
  ungroup()

whole_legs_summary%>% print(n = 10, width = Inf)
# A tibble: 10 × 10
    Year whole_legs_mean_number whole_legs_median_number whole_legs_sd_number
   <dbl>                  <dbl>                    <dbl>                <dbl>
 1  2004                   1.99                     2.03               0.0505
 2  2005                   2.04                     2.04               0     
 3  2006                   2.04                     2.04               0     
 4  2007                   2.04                     2.04               0     
 5  2008                   2.04                     2.04               0     
 6  2009                   2.04                     2.04               0     
 7  2010                   2.04                     2.04               0     
 8  2011                   2.04                     2.04               0     
 9  2012                   2.04                     2.04               0     
10  2013                   2.04                     2.04               0     
   whole_legs_max_number whole_legs_min_number whole_legs_mode_number
                   <dbl>                 <dbl>                  <dbl>
 1                  2.04                  1.94                   2.04
 2                  2.04                  2.04                   2.04
 3                  2.04                  2.04                   2.04
 4                  2.04                  2.04                   2.04
 5                  2.04                  2.04                   2.04
 6                  2.04                  2.04                   2.04
 7                  2.04                  2.04                   2.04
 8                  2.04                  2.04                   2.04
 9                  2.04                  2.04                   2.04
10                  2.04                  2.04                   2.04
   whole_legs_quant25 whole_legs_quant50 whole_legs_quant75
                <dbl>              <dbl>              <dbl>
 1               1.94               2.03               2.04
 2               2.04               2.04               2.04
 3               2.04               2.04               2.04
 4               2.04               2.04               2.04
 5               2.04               2.04               2.04
 6               2.04               2.04               2.04
 7               2.04               2.04               2.04
 8               2.04               2.04               2.04
 9               2.04               2.04               2.04
10               2.04               2.04               2.04

5.2 “organiceggpoultry” data set

5.2.1 “whole data” sheet

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

whole_data_summary <- organiceggpoultry_whole_data_pivot %>%
  mutate(value= as.numeric (value)) %>%
  group_by(year, `element&dozen`)%>%
  summarise(`whole_data_mean_number` = mean(`value`, na.rm =TRUE), `whole_data_median_number` = median(`value`, na.rm =TRUE), `whole_data_sd_number` = sd(`value`,na.rm =TRUE), `whole_data_max_number`=max(`value`, na.rm =TRUE), `whole_data_min_number`=min(`value`, na.rm =TRUE), `whole_data_mode_number`= find_mode(`value`), `whole_data_quant25` = quantile(`value`, na.rm = T, probs = q[1]),`whole_data_quant50` = quantile(`value`, na.rm = T, probs = q[2]), `whole_data_quant75` = quantile(`value`, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'year', 'element&dozen'. You can override
using the `.groups` argument.
whole_data_summary%>% print(n = 10, width = Inf)
# A tibble: 91 × 11
   year  `element&dozen`                                    
   <chr> <chr>                                              
 1 2004  USDA Certified Organic Eggs_Extra Large 1/2 Doz    
 2 2004  USDA Certified Organic Eggs_Extra Large Dozen      
 3 2004  USDA Certified Organic Eggs_Large 1/2 Doz          
 4 2004  USDA Certified Organic Eggs_Large Dozen            
 5 2004  USDA Certified Organic Young Chicken_B/S Breast    
 6 2004  USDA Certified Organic Young Chicken_Bone in Breast
 7 2004  USDA Certified Organic Young Chicken_Bone in Breast
 8 2004  USDA Certified Organic Young Chicken_Thighs        
 9 2004  USDA Certified Organic Young Chicken_Whole         
10 2004  USDA Certified Organic Young Chicken_Whole Legs    
   whole_data_mean_number whole_data_median_number whole_data_sd_number
                    <dbl>                    <dbl>                <dbl>
 1                   136.                     136.                 1.50
 2                   237.                     241                  4.91
 3                   130.                     130.                 2.41
 4                   230.                     232.                 3.85
 5                   643.                     642.                 1.00
 6                   390.                     390.                 0   
 7                   390.                     390.                 0   
 8                   201.                     200.                 1.72
 9                   212.                     217.                 7.40
10                   199.                     203.                 5.05
   whole_data_max_number whole_data_min_number whole_data_mode_number
                   <dbl>                 <dbl>                  <dbl>
 1                  137                   132                    137 
 2                  241                   230                    241 
 3                  134.                  126                    128.
 4                  234.                  225                    234.
 5                  646.                  641                    642.
 6                  390.                  390.                    NA 
 7                  390.                  390.                   390.
 8                  203                   200.                   200.
 9                  217                   198.                   217 
10                  204.                  194.                   204.
   whole_data_quant25 whole_data_quant50 whole_data_quant75
                <dbl>              <dbl>              <dbl>
 1               136.               136.               137 
 2               233.               241                241 
 3               128.               130.               132.
 4               226.               232.               234.
 5               642.               642.               642.
 6               390.               390.               390.
 7               390.               390.               390.
 8               200.               200.               203 
 9               211.               217.               217 
10               194.               203.               204.
# ℹ 81 more rows

5.2.2 “Organic egg prices” sheet

organiceggpoultry_egg_prices_clean_pivot
# A tibble: 480 × 5
   Dozen                 year  Type                   months value
   <chr>                 <chr> <chr>                  <chr>  <chr>
 1 "Extra Large \nDozen" 2013  Certified Organic Eggs Jan.   290  
 2 "Extra Large \nDozen" 2013  Certified Organic Eggs Feb.   290  
 3 "Extra Large \nDozen" 2013  Certified Organic Eggs Mar.   290  
 4 "Extra Large \nDozen" 2013  Certified Organic Eggs Apr.   290  
 5 "Extra Large \nDozen" 2013  Certified Organic Eggs May    290  
 6 "Extra Large \nDozen" 2013  Certified Organic Eggs June   290  
 7 "Extra Large \nDozen" 2013  Certified Organic Eggs July   290  
 8 "Extra Large \nDozen" 2013  Certified Organic Eggs Aug.   290  
 9 "Extra Large \nDozen" 2013  Certified Organic Eggs Sep.   290  
10 "Extra Large \nDozen" 2013  Certified Organic Eggs Oct.   290  
# ℹ 470 more rows
find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

egg_prices_summary <- organiceggpoultry_egg_prices_clean_pivot %>%
  mutate(value= as.numeric (value)) %>%
  group_by(year, Dozen)%>%
  summarise(`egg_prices_mean_number` = mean(`value`, na.rm =TRUE), `egg_prices_median_number` = median(`value`, na.rm =TRUE), `egg_prices_sd_number` = sd(`value`,na.rm =TRUE), `egg_prices_max_number`=max(`value`, na.rm =TRUE), `egg_prices_min_number`=min(`value`, na.rm =TRUE), `egg_prices_mode_number`= find_mode(`value`), `egg_prices_quant25` = quantile(`value`, na.rm = T, probs = q[1]),`egg_prices_quant50` = quantile(`value`, na.rm = T, probs = q[2]), `egg_prices_quant75` = quantile(`value`, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'year'. You can override using the
`.groups` argument.
egg_prices_summary %>% print(n = 10, width = Inf)
# A tibble: 40 × 11
   year  Dozen                             egg_prices_mean_number
   <chr> <chr>                                              <dbl>
 1 2004  "Extra Large \nDozen"                               237.
 2 2004  "Extra Large 1/2 Doz."                              136.
 3 2004  "Large \n1/2 Doz."                                  130.
 4 2004  "Large \nDozen"                                     230.
 5 2005  "Extra Large \nDozen"                               241 
 6 2005  "Extra Large 1/2 Doz."                              136.
 7 2005  "Large \n1/2 Doz."                                  128.
 8 2005  "Large \nDozen"                                     234.
 9 2006  "Extra Large \nDozen"                               242.
10 2006  "Extra Large 1/2 Doz.\n1/2 Dozen"                   136.
   egg_prices_median_number egg_prices_sd_number egg_prices_max_number
                      <dbl>                <dbl>                 <dbl>
 1                     241                 4.91                   241 
 2                     136.                1.50                   137 
 3                     130.                2.41                   134.
 4                     232.                3.85                   234.
 5                     241                 0                      241 
 6                     136.                0                      136.
 7                     128.                0                      128.
 8                     234.                0                      234.
 9                     242.                0.924                  244.
10                     136.                0.758                  138.
   egg_prices_min_number egg_prices_mode_number egg_prices_quant25
                   <dbl>                  <dbl>              <dbl>
 1                  230                    241                233.
 2                  132                    137                136.
 3                  126                    128.               128.
 4                  225                    234.               226.
 5                  241                    241                241 
 6                  136.                   136.               136.
 7                  128.                   128.               128.
 8                  234.                   234.               234.
 9                  240                    242.               241.
10                  136.                   136.               136.
   egg_prices_quant50 egg_prices_quant75
                <dbl>              <dbl>
 1               241                241 
 2               136.               137 
 3               130.               132.
 4               232.               234.
 5               241                241 
 6               136.               136.
 7               128.               128.
 8               234.               234.
 9               242.               242.
10               136.               136.
# ℹ 30 more rows

5.2.3 “Organic poultry prices” sheet

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

poultry_prices_summary <- organiceggpoultry_poultry_prices_clean_pivot %>%
  mutate(value= as.numeric (value)) %>%
  group_by(year, Dozen)%>%
  summarise(`poultry_prices_mean_number` = mean(`value`, na.rm =TRUE), `poultry_prices_median_number` = median(`value`, na.rm =TRUE), `poultry_prices_sd_number` = sd(`value`,na.rm =TRUE), `poultry_prices_max_number`=max(`value`, na.rm =TRUE), `poultry_prices_min_number`=min(`value`, na.rm =TRUE), `poultry_prices_mode_number`= find_mode(`value`), `poultry_prices_quant25` = quantile(`value`, na.rm = T, probs = q[1]),`poultry_prices_quant50` = quantile(`value`, na.rm = T, probs = q[2]), `poultry_prices_quant75` = quantile(`value`, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'year'. You can override using the
`.groups` argument.
poultry_prices_summary%>% print(n = 10, width = Inf)
# A tibble: 43 × 11
   year  Dozen          poultry_prices_mean_number poultry_prices_median_number
   <chr> <chr>                               <dbl>                        <dbl>
 1 2005  B/S Breast                           645.                         646.
 2 2005  Bone-in Breast                       390.                         390.
 3 2005  Whole                                217                          217 
 4 2006  B/S Breast                           646.                         646.
 5 2006  Bone-in Breast                       390.                         390.
 6 2006  Thighs                               222                          222 
 7 2006  Whole                                220.                         220.
 8 2006  Whole Legs                           204.                         204.
 9 2007  B/S Breast                           646.                         646.
10 2007  Bone-in Breast                       390.                         390.
   poultry_prices_sd_number poultry_prices_max_number poultry_prices_min_number
                      <dbl>                     <dbl>                     <dbl>
 1                    0.433                      646.                      644 
 2                    0                          390.                      390.
 3                    0                          217                       217 
 4                    0                          646.                      646.
 5                    0                          390.                      390.
 6                    0                          222                       222 
 7                    1.35                       220.                      217 
 8                    0                          204.                      204.
 9                    0                          646.                      646.
10                    0                          390.                      390.
   poultry_prices_mode_number poultry_prices_quant25 poultry_prices_quant50
                        <dbl>                  <dbl>                  <dbl>
 1                       646.                   646.                   646.
 2                       390.                   390.                   390.
 3                       217                    217                    217 
 4                       646.                   646.                   646.
 5                       390.                   390.                   390.
 6                       222                    222                    222 
 7                       220.                   220.                   220.
 8                       204.                   204.                   204.
 9                       646.                   646.                   646.
10                       390.                   390.                   390.
   poultry_prices_quant75
                    <dbl>
 1                   646.
 2                   390.
 3                   217 
 4                   646.
 5                   390.
 6                   222 
 7                   220.
 8                   204.
 9                   646.
10                   390.
# ℹ 33 more rows

5.3 “FedFundsRate” data set

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

 FedFundsRate_summary <- FedFundsRate_pivot %>%
  group_by(Year, Rate_Types)%>%
  summarise(`FedFundsRate_mean_number` = mean(`value`, na.rm =TRUE), ` FedFundsRate_median_number` = median(`value`, na.rm =TRUE), ` FedFundsRate_sd_number` = sd(`value`,na.rm =TRUE), ` FedFundsRate_max_number`=max(`value`, na.rm =TRUE), ` FedFundsRate_min_number`=min(`value`, na.rm =TRUE), ` FedFundsRate_mode_number`= find_mode(`value`), ` FedFundsRate_quant25` = quantile(`value`, na.rm = T, probs = q[1]),` FedFundsRate_quant50` = quantile(`value`, na.rm = T, probs = q[2]), ` FedFundsRate_quant75` = quantile(`value`, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'Year', 'Rate_Types'. You can override
using the `.groups` argument.
 FedFundsRate_summary%>% print(n = 10, width = Inf)
# A tibble: 700 × 11
    Year Rate_Types                   FedFundsRate_mean_number
   <int> <chr>                                           <dbl>
 1  1954 Effective.Federal.Funds.Rate                     1.01
 2  1954 Effective.Federal.Funds.Rate                     1.01
 3  1954 Effective.Federal.Funds.Rate                     1.01
 4  1954 Effective.Federal.Funds.Rate                     1.01
 5  1954 Effective.Federal.Funds.Rate                     1.01
 6  1954 Effective.Federal.Funds.Rate                     1.01
 7  1954 Federal.Funds.Lower.Target                     NaN   
 8  1954 Federal.Funds.Target.Rate                      NaN   
 9  1954 Federal.Funds.Upper.Target                     NaN   
10  1954 Inflation.Rate                                 NaN   
   ` FedFundsRate_median_number` ` FedFundsRate_sd_number`
                           <dbl>                     <dbl>
 1                         0.955                     0.210
 2                         0.955                     0.210
 3                         0.955                     0.210
 4                         0.955                     0.210
 5                         0.955                     0.210
 6                         0.955                     0.210
 7                        NA                        NA    
 8                        NA                        NA    
 9                        NA                        NA    
10                        NA                        NA    
   ` FedFundsRate_max_number` ` FedFundsRate_min_number`
                        <dbl>                      <dbl>
 1                       1.28                        0.8
 2                       1.28                        0.8
 3                       1.28                        0.8
 4                       1.28                        0.8
 5                       1.28                        0.8
 6                       1.28                        0.8
 7                    -Inf                         Inf  
 8                    -Inf                         Inf  
 9                    -Inf                         Inf  
10                    -Inf                         Inf  
   ` FedFundsRate_mode_number` ` FedFundsRate_quant25` ` FedFundsRate_quant50`
                         <dbl>                   <dbl>                   <dbl>
 1                        0.8                    0.835                   0.955
 2                        1.22                   0.835                   0.955
 3                        1.06                   0.835                   0.955
 4                        0.85                   0.835                   0.955
 5                        0.83                   0.835                   0.955
 6                        1.28                   0.835                   0.955
 7                       NA                     NA                      NA    
 8                       NA                     NA                      NA    
 9                       NA                     NA                      NA    
10                       NA                     NA                      NA    
   ` FedFundsRate_quant75`
                     <dbl>
 1                    1.18
 2                    1.18
 3                    1.18
 4                    1.18
 5                    1.18
 6                    1.18
 7                   NA   
 8                   NA   
 9                   NA   
10                   NA   
# ℹ 690 more rows

5.4 “hotel_bookings” data set

# 1. Analysis of the "lead_time", which is the time used from the start of the process till the end for two different hotels. 

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

 lead_time_summary <- hotel_bookings_clean %>%
  group_by(hotel)%>%
  summarise(mean_number= mean(lead_time, na.rm =TRUE), median_number = median(lead_time, na.rm =TRUE), sd_number = sd(lead_time,na.rm =TRUE), max_number=max(lead_time, na.rm =TRUE), min_number=min(lead_time, na.rm =TRUE), mode_number= find_mode(lead_time), quant25 = quantile(lead_time, na.rm = T, probs = q[1]),quant50 = quantile(lead_time, na.rm = T, probs = q[2]), quant75 = quantile(lead_time, na.rm = T, probs = q[3]))%>%
  ungroup()

lead_time_summary%>% print(n = 10, width = Inf)
# A tibble: 2 × 10
  hotel        mean_number median_number sd_number max_number min_number
  <chr>              <dbl>         <dbl>     <dbl>      <int>      <int>
1 City Hotel         110.             74     111.         629          0
2 Resort Hotel        92.7            57      97.3        737          0
  mode_number quant25 quant50 quant75
        <int>   <dbl>   <dbl>   <dbl>
1           0      23      74     163
2           0      10      57     155
# 2.Analysis of the "adr," which is the price, for different types of rooms in two hotels.

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

price_summary <- hotel_bookings_clean%>%
  group_by(hotel, assigned_room_type)%>%
  summarise(mean_number= mean(adr, na.rm =TRUE), median_number = median(adr, na.rm =TRUE), sd_number = sd(adr,na.rm =TRUE), max_number=max(adr, na.rm =TRUE), min_number=min(adr, na.rm =TRUE), mode_number= find_mode(adr), quant25 = quantile(adr, na.rm = T, probs = q[1]),quant50 = quantile(adr, na.rm = T, probs = q[2]), quant75 = quantile(adr, na.rm = T, probs = q[3]))%>%
  ungroup()
`summarise()` has grouped output by 'hotel'. You can override using the
`.groups` argument.
number_assigned_room_type <- hotel_bookings_clean %>%
   group_by(hotel)%>%
  count(assigned_room_type)%>%
  ungroup()

price_summary_final <- mutate (number_assigned_room_type, price_summary)

price_summary_final%>% print(n = 10, width = Inf)
# A tibble: 20 × 12
   hotel        assigned_room_type     n mean_number median_number sd_number
   <chr>        <chr>              <int>       <dbl>         <dbl>     <dbl>
 1 City Hotel   A                  57007        96.7          94.5      37.3
 2 City Hotel   B                   2004        93.9          90.8      35.8
 3 City Hotel   C                    161       101.          100        45.0
 4 City Hotel   D                  14983       122.          122        39.2
 5 City Hotel   E                   2168       144.          145        50.8
 6 City Hotel   F                   2018       179.          184.       54.6
 7 City Hotel   G                    700       184.          203        86.2
 8 City Hotel   K                    279        53.7          20.9      60.9
 9 City Hotel   P                     10         0             0         0  
10 Resort Hotel A                  17046        81.4          66        50.8
   max_number min_number mode_number quant25 quant50 quant75
        <dbl>      <dbl>       <dbl>   <dbl>   <dbl>   <dbl>
 1      5400           0          62    75      94.5    115 
 2       284.          0           0    77.0    90.8    110 
 3       213           0           0    80     100      120 
 4       376.          0          75    95.5   122      148.
 5       452.          0         106   110     145      179.
 6       350.          0           0   156     184.     212.
 7       510           0           0   136.    203      244.
 8       283.          0           0     0      20.9    100.
 9         0           0           0     0       0        0 
10       450           0          48    46      66      103.
# ℹ 10 more rows
# 3. Analysis of the "adults," "children,"  and "babies" with different room types and hotels.

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

occupants_summary <- hotel_bookings_clean%>%
  group_by(hotel, assigned_room_type)%>%
  summarise(adults_mean_number= mean(adults, na.rm =TRUE), adults_median_number = median(adults, na.rm =TRUE), adults_sd_number = sd(adults,na.rm =TRUE), adults_max_number=max(adults, na.rm =TRUE), adults_min_number=min(adults, na.rm =TRUE), adults_mode_number= find_mode(adults), adults_quant25 = quantile(adults, na.rm = T, probs = q[1]),adults_quant50 = quantile(adults, na.rm = T, probs = q[2]), adults_quant75 = quantile(adults, na.rm = T, probs = q[3]), kids_mean_number= mean(children+babies, na.rm =TRUE), kids_median_number = median(children+babies, na.rm =TRUE), kids_sd_number = sd(children+babies,na.rm =TRUE), kids_max_number=max(children+babies, na.rm =TRUE), kids_min_number=min(children+babies, na.rm =TRUE), kids_mode_number= find_mode(children+babies), adults_quant25 = quantile(children+babies, na.rm = T, probs = q[1]),adults_quant50 = quantile(children+babies, na.rm = T, probs = q[2]), adults_quant75 = quantile(children+babies, na.rm = T, probs = q[3]))%>%
  ungroup()
`summarise()` has grouped output by 'hotel'. You can override using the
`.groups` argument.
occupants_summary%>% print(n = 10, width = Inf)
# A tibble: 20 × 17
   hotel        assigned_room_type adults_mean_number adults_median_number
   <chr>        <chr>                           <dbl>                <dbl>
 1 City Hotel   A                                1.78                    2
 2 City Hotel   B                                1.65                    2
 3 City Hotel   C                                1.74                    2
 4 City Hotel   D                                2.09                    2
 5 City Hotel   E                                2.04                    2
 6 City Hotel   F                                1.98                    2
 7 City Hotel   G                                2.14                    2
 8 City Hotel   K                                1.19                    1
 9 City Hotel   P                                0                       0
10 Resort Hotel A                                1.81                    2
   adults_sd_number adults_max_number adults_min_number adults_mode_number
              <dbl>             <int>             <int>              <int>
 1            0.434                 3                 0                  2
 2            0.673                 3                 0                  2
 3            0.587                 3                 0                  2
 4            0.606                 4                 0                  2
 5            0.679                 4                 0                  2
 6            0.375                 4                 1                  2
 7            0.681                 4                 0                  2
 8            0.947                 3                 0                  2
 9            0                     0                 0                  0
10            0.914                55                 0                  2
   adults_quant25 adults_quant50 adults_quant75 kids_mean_number
            <dbl>          <dbl>          <dbl>            <dbl>
 1              0              0              0           0.0352
 2              0              0              0           0.310 
 3              0              0              0           0.124 
 4              0              0              0           0.0660
 5              0              0              0           0.216 
 6              0              2              2           1.42  
 7              0              0              2           0.949 
 8              0              0              0           0.0502
 9              0              0              0           0     
10              0              0              0           0.0331
   kids_median_number kids_sd_number kids_max_number kids_min_number
                <dbl>          <dbl>           <int>           <int>
 1                  0          0.193               3               0
 2                  0          0.722               9               0
 3                  0          0.367               2               0
 4                  0          0.272              10               0
 5                  0          0.565               3               0
 6                  2          0.872               3               0
 7                  0          1.05                3               0
 8                  0          0.235               2               0
 9                  0          0                   0               0
10                  0          0.182               2               0
   kids_mode_number
              <int>
 1                0
 2                0
 3                0
 4                0
 5                0
 6                2
 7                0
 8                0
 9                0
10                0
# ℹ 10 more rows
number_occupants_summary <- hotel_bookings_clean %>%
   group_by(hotel)%>%
  count(assigned_room_type)%>%
  ungroup()

occupants_summary_final <- mutate (number_occupants_summary, occupants_summary)

occupants_summary_final%>% print(n = 10, width = Inf)
# A tibble: 20 × 18
   hotel        assigned_room_type     n adults_mean_number adults_median_number
   <chr>        <chr>              <int>              <dbl>                <dbl>
 1 City Hotel   A                  57007               1.78                    2
 2 City Hotel   B                   2004               1.65                    2
 3 City Hotel   C                    161               1.74                    2
 4 City Hotel   D                  14983               2.09                    2
 5 City Hotel   E                   2168               2.04                    2
 6 City Hotel   F                   2018               1.98                    2
 7 City Hotel   G                    700               2.14                    2
 8 City Hotel   K                    279               1.19                    1
 9 City Hotel   P                     10               0                       0
10 Resort Hotel A                  17046               1.81                    2
   adults_sd_number adults_max_number adults_min_number adults_mode_number
              <dbl>             <int>             <int>              <int>
 1            0.434                 3                 0                  2
 2            0.673                 3                 0                  2
 3            0.587                 3                 0                  2
 4            0.606                 4                 0                  2
 5            0.679                 4                 0                  2
 6            0.375                 4                 1                  2
 7            0.681                 4                 0                  2
 8            0.947                 3                 0                  2
 9            0                     0                 0                  0
10            0.914                55                 0                  2
   adults_quant25 adults_quant50 adults_quant75 kids_mean_number
            <dbl>          <dbl>          <dbl>            <dbl>
 1              0              0              0           0.0352
 2              0              0              0           0.310 
 3              0              0              0           0.124 
 4              0              0              0           0.0660
 5              0              0              0           0.216 
 6              0              2              2           1.42  
 7              0              0              2           0.949 
 8              0              0              0           0.0502
 9              0              0              0           0     
10              0              0              0           0.0331
   kids_median_number kids_sd_number kids_max_number kids_min_number
                <dbl>          <dbl>           <int>           <int>
 1                  0          0.193               3               0
 2                  0          0.722               9               0
 3                  0          0.367               2               0
 4                  0          0.272              10               0
 5                  0          0.565               3               0
 6                  2          0.872               3               0
 7                  0          1.05                3               0
 8                  0          0.235               2               0
 9                  0          0                   0               0
10                  0          0.182               2               0
   kids_mode_number
              <int>
 1                0
 2                0
 3                0
 4                0
 5                0
 6                2
 7                0
 8                0
 9                0
10                0
# ℹ 10 more rows
# 4. Analysis of the "previous_cancellations" with different room types and hotels.

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

previous_cancellations_summary <- hotel_bookings_clean%>%
  group_by(hotel, assigned_room_type)%>%
  summarise(previous_cancellations_mean_number= mean(previous_cancellations, na.rm =TRUE), previous_cancellations_median_number = median(previous_cancellations, na.rm =TRUE), previous_cancellations_sd_number = sd(previous_cancellations,na.rm =TRUE), previous_cancellations_max_number=max(previous_cancellations, na.rm =TRUE), previous_cancellations_min_number=min(previous_cancellations, na.rm =TRUE), previous_cancellations_mode_number= find_mode(previous_cancellations), previous_cancellations_quant25 = quantile(previous_cancellations, na.rm = T, probs = q[1]),previous_cancellations_quant50 = quantile(previous_cancellations, na.rm = T, probs = q[2]), previous_cancellations_quant75 = quantile(previous_cancellations, na.rm = T, probs = q[3]))%>%
  ungroup()
`summarise()` has grouped output by 'hotel'. You can override using the
`.groups` argument.
previous_cancellations_summary%>% print(n = 10, width = Inf)
# A tibble: 20 × 11
   hotel        assigned_room_type previous_cancellations_mean_number
   <chr>        <chr>                                           <dbl>
 1 City Hotel   A                                             0.103  
 2 City Hotel   B                                             0.0334 
 3 City Hotel   C                                             0.0683 
 4 City Hotel   D                                             0.0165 
 5 City Hotel   E                                             0.0235 
 6 City Hotel   F                                             0.0268 
 7 City Hotel   G                                             0.0514 
 8 City Hotel   K                                             0.00717
 9 City Hotel   P                                             0      
10 Resort Hotel A                                             0.220  
   previous_cancellations_median_number previous_cancellations_sd_number
                                  <dbl>                            <dbl>
 1                                    0                           0.456 
 2                                    0                           0.215 
 3                                    0                           0.514 
 4                                    0                           0.265 
 5                                    0                           0.373 
 6                                    0                           0.232 
 7                                    0                           0.411 
 8                                    0                           0.0845
 9                                    0                           0     
10                                    0                           2.03  
   previous_cancellations_max_number previous_cancellations_min_number
                               <int>                             <int>
 1                                21                                 0
 2                                 4                                 0
 3                                 5                                 0
 4                                13                                 0
 5                                11                                 0
 6                                 4                                 0
 7                                 4                                 0
 8                                 1                                 0
 9                                 0                                 0
10                                26                                 0
   previous_cancellations_mode_number previous_cancellations_quant25
                                <int>                          <dbl>
 1                                  0                              0
 2                                  0                              0
 3                                  0                              0
 4                                  0                              0
 5                                  0                              0
 6                                  0                              0
 7                                  0                              0
 8                                  0                              0
 9                                  0                              0
10                                  0                              0
   previous_cancellations_quant50 previous_cancellations_quant75
                            <dbl>                          <dbl>
 1                              0                              0
 2                              0                              0
 3                              0                              0
 4                              0                              0
 5                              0                              0
 6                              0                              0
 7                              0                              0
 8                              0                              0
 9                              0                              0
10                              0                              0
# ℹ 10 more rows
number_previous_cancellations_summary <- hotel_bookings_clean %>%
   group_by(hotel)%>%
  count(assigned_room_type)%>%
  ungroup()

previous_cancellations_summary_final <- mutate(number_previous_cancellations_summary, previous_cancellations_summary)

previous_cancellations_summary_final%>% print(n = 10, width = Inf)
# A tibble: 20 × 12
   hotel        assigned_room_type     n previous_cancellations_mean_number
   <chr>        <chr>              <int>                              <dbl>
 1 City Hotel   A                  57007                            0.103  
 2 City Hotel   B                   2004                            0.0334 
 3 City Hotel   C                    161                            0.0683 
 4 City Hotel   D                  14983                            0.0165 
 5 City Hotel   E                   2168                            0.0235 
 6 City Hotel   F                   2018                            0.0268 
 7 City Hotel   G                    700                            0.0514 
 8 City Hotel   K                    279                            0.00717
 9 City Hotel   P                     10                            0      
10 Resort Hotel A                  17046                            0.220  
   previous_cancellations_median_number previous_cancellations_sd_number
                                  <dbl>                            <dbl>
 1                                    0                           0.456 
 2                                    0                           0.215 
 3                                    0                           0.514 
 4                                    0                           0.265 
 5                                    0                           0.373 
 6                                    0                           0.232 
 7                                    0                           0.411 
 8                                    0                           0.0845
 9                                    0                           0     
10                                    0                           2.03  
   previous_cancellations_max_number previous_cancellations_min_number
                               <int>                             <int>
 1                                21                                 0
 2                                 4                                 0
 3                                 5                                 0
 4                                13                                 0
 5                                11                                 0
 6                                 4                                 0
 7                                 4                                 0
 8                                 1                                 0
 9                                 0                                 0
10                                26                                 0
   previous_cancellations_mode_number previous_cancellations_quant25
                                <int>                          <dbl>
 1                                  0                              0
 2                                  0                              0
 3                                  0                              0
 4                                  0                              0
 5                                  0                              0
 6                                  0                              0
 7                                  0                              0
 8                                  0                              0
 9                                  0                              0
10                                  0                              0
   previous_cancellations_quant50 previous_cancellations_quant75
                            <dbl>                          <dbl>
 1                              0                              0
 2                              0                              0
 3                              0                              0
 4                              0                              0
 5                              0                              0
 6                              0                              0
 7                              0                              0
 8                              0                              0
 9                              0                              0
10                              0                              0
# ℹ 10 more rows
# 5. Analysis of the "required_car_parking_spaces" with different room types and hotels.

find_mode <- function(x) {
  u <- unique(x)
  tab <- tabulate(match(x, u))
  u[tab == max(tab)]}

q = c(.25, .5, .75)

required_car_parking_spaces_summary <- hotel_bookings_clean%>%
  group_by(hotel, assigned_room_type)%>%
  summarise(required_car_parking_spaces_mean_number= mean(required_car_parking_spaces, na.rm =TRUE), required_car_parking_spaces_median_number = median(required_car_parking_spaces, na.rm =TRUE), required_car_parking_spaces_sd_number = sd(required_car_parking_spaces,na.rm =TRUE), required_car_parking_spaces_max_number=max(required_car_parking_spaces, na.rm =TRUE), required_car_parking_spaces_min_number=min(required_car_parking_spaces, na.rm =TRUE), required_car_parking_spaces_mode_number= find_mode(required_car_parking_spaces), required_car_parking_spaces_quant25 = quantile(required_car_parking_spaces, na.rm = T, probs = q[1]),required_car_parking_spaces_quant50 = quantile(required_car_parking_spaces, na.rm = T, probs = q[2]), required_car_parking_spaces_quant75 = quantile(required_car_parking_spaces, na.rm = T, probs = q[3])) %>%
  ungroup()
`summarise()` has grouped output by 'hotel'. You can override using the
`.groups` argument.
required_car_parking_spaces_summary%>% print(n = 10, width = Inf)
# A tibble: 20 × 11
   hotel        assigned_room_type required_car_parking_spaces_mean_number
   <chr>        <chr>                                                <dbl>
 1 City Hotel   A                                                  0.0187 
 2 City Hotel   B                                                  0.0389 
 3 City Hotel   C                                                  0.00621
 4 City Hotel   D                                                  0.0325 
 5 City Hotel   E                                                  0.0623 
 6 City Hotel   F                                                  0.0540 
 7 City Hotel   G                                                  0.0643 
 8 City Hotel   K                                                  0.0466 
 9 City Hotel   P                                                  0      
10 Resort Hotel A                                                  0.0933 
   required_car_parking_spaces_median_number
                                       <dbl>
 1                                         0
 2                                         0
 3                                         0
 4                                         0
 5                                         0
 6                                         0
 7                                         0
 8                                         0
 9                                         0
10                                         0
   required_car_parking_spaces_sd_number required_car_parking_spaces_max_number
                                   <dbl>                                  <int>
 1                                0.136                                       3
 2                                0.193                                       1
 3                                0.0788                                      1
 4                                0.178                                       2
 5                                0.242                                       1
 6                                0.228                                       2
 7                                0.245                                       1
 8                                0.211                                       1
 9                                0                                           0
10                                0.294                                       3
   required_car_parking_spaces_min_number
                                    <int>
 1                                      0
 2                                      0
 3                                      0
 4                                      0
 5                                      0
 6                                      0
 7                                      0
 8                                      0
 9                                      0
10                                      0
   required_car_parking_spaces_mode_number required_car_parking_spaces_quant25
                                     <int>                               <dbl>
 1                                       0                                   0
 2                                       0                                   0
 3                                       0                                   0
 4                                       0                                   0
 5                                       0                                   0
 6                                       0                                   0
 7                                       0                                   0
 8                                       0                                   0
 9                                       0                                   0
10                                       0                                   0
   required_car_parking_spaces_quant50 required_car_parking_spaces_quant75
                                 <dbl>                               <dbl>
 1                                   0                                   0
 2                                   0                                   0
 3                                   0                                   0
 4                                   0                                   0
 5                                   0                                   0
 6                                   0                                   0
 7                                   0                                   0
 8                                   0                                   0
 9                                   0                                   0
10                                   0                                   0
# ℹ 10 more rows
number_required_car_parking_spaces_summary <- hotel_bookings_clean %>%
   group_by(hotel)%>%
  count(assigned_room_type)%>%
  ungroup()

required_car_parking_spaces_summary_final <- mutate(number_required_car_parking_spaces_summary, required_car_parking_spaces_summary)

required_car_parking_spaces_summary_final%>% print(n = 10, width = Inf)
# A tibble: 20 × 12
   hotel        assigned_room_type     n required_car_parking_spaces_mean_number
   <chr>        <chr>              <int>                                   <dbl>
 1 City Hotel   A                  57007                                 0.0187 
 2 City Hotel   B                   2004                                 0.0389 
 3 City Hotel   C                    161                                 0.00621
 4 City Hotel   D                  14983                                 0.0325 
 5 City Hotel   E                   2168                                 0.0623 
 6 City Hotel   F                   2018                                 0.0540 
 7 City Hotel   G                    700                                 0.0643 
 8 City Hotel   K                    279                                 0.0466 
 9 City Hotel   P                     10                                 0      
10 Resort Hotel A                  17046                                 0.0933 
   required_car_parking_spaces_median_number
                                       <dbl>
 1                                         0
 2                                         0
 3                                         0
 4                                         0
 5                                         0
 6                                         0
 7                                         0
 8                                         0
 9                                         0
10                                         0
   required_car_parking_spaces_sd_number required_car_parking_spaces_max_number
                                   <dbl>                                  <int>
 1                                0.136                                       3
 2                                0.193                                       1
 3                                0.0788                                      1
 4                                0.178                                       2
 5                                0.242                                       1
 6                                0.228                                       2
 7                                0.245                                       1
 8                                0.211                                       1
 9                                0                                           0
10                                0.294                                       3
   required_car_parking_spaces_min_number
                                    <int>
 1                                      0
 2                                      0
 3                                      0
 4                                      0
 5                                      0
 6                                      0
 7                                      0
 8                                      0
 9                                      0
10                                      0
   required_car_parking_spaces_mode_number required_car_parking_spaces_quant25
                                     <int>                               <dbl>
 1                                       0                                   0
 2                                       0                                   0
 3                                       0                                   0
 4                                       0                                   0
 5                                       0                                   0
 6                                       0                                   0
 7                                       0                                   0
 8                                       0                                   0
 9                                       0                                   0
10                                       0                                   0
   required_car_parking_spaces_quant50 required_car_parking_spaces_quant75
                                 <dbl>                               <dbl>
 1                                   0                                   0
 2                                   0                                   0
 3                                   0                                   0
 4                                   0                                   0
 5                                   0                                   0
 6                                   0                                   0
 7                                   0                                   0
 8                                   0                                   0
 9                                   0                                   0
10                                   0                                   0
# ℹ 10 more rows