Hotel KPI Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4e41b59f",
   "metadata": {},
   "source": [
    "### Data Wrangling in R\n",
    "*This is a notebook by Ogechi Anoliefo.*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7eae1ada",
   "metadata": {},
   "source": [
    "#### 1. Loading the packages to be used (N/B: I already had them installed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "04b2c1b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#included suppressPackageStartupMessages() to suppress the startup messages when loading the packages\n",
    "suppressPackageStartupMessages(library(tidyverse))\n",
    "suppressPackageStartupMessages(library(dplyr))\n",
    "suppressPackageStartupMessages(library(psych))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ca047f7",
   "metadata": {},
   "source": [
    "#### 2. Importing the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2c08e999",
   "metadata": {},
   "outputs": [],
   "source": [
    "df<- read_csv(\"C:\\\\Users\\\\ETIABA CHAMBER'S\\\\Documents\\\\Data_Analytics\\\\My_portfolio\\\\Hotel_Booking\\\\hotel_bookings.csv\", show_col_types=FALSE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c1b0418c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 32</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>lead_time</th><th scope=col>arrival_date_year</th><th scope=col>arrival_date_month</th><th scope=col>arrival_date_week_number</th><th scope=col>arrival_date_day_of_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>adults</th><th scope=col>⋯</th><th scope=col>deposit_type</th><th scope=col>agent</th><th scope=col>company</th><th scope=col>days_in_waiting_list</th><th scope=col>customer_type</th><th scope=col>adr</th><th scope=col>required_car_parking_spaces</th><th scope=col>total_of_special_requests</th><th scope=col>reservation_status</th><th scope=col>reservation_status_date</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>⋯</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>342</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>0</td><td>2</td><td>⋯</td><td>No Deposit</td><td>NULL</td><td>NULL</td><td>0</td><td>Transient</td><td> 0</td><td>0</td><td>0</td><td>Check-Out</td><td>2015-07-01</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>737</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>0</td><td>2</td><td>⋯</td><td>No Deposit</td><td>NULL</td><td>NULL</td><td>0</td><td>Transient</td><td> 0</td><td>0</td><td>0</td><td>Check-Out</td><td>2015-07-01</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>  7</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>1</td><td>1</td><td>⋯</td><td>No Deposit</td><td>NULL</td><td>NULL</td><td>0</td><td>Transient</td><td>75</td><td>0</td><td>0</td><td>Check-Out</td><td>2015-07-02</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td> 13</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>1</td><td>1</td><td>⋯</td><td>No Deposit</td><td>304 </td><td>NULL</td><td>0</td><td>Transient</td><td>75</td><td>0</td><td>0</td><td>Check-Out</td><td>2015-07-02</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td> 14</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>2</td><td>2</td><td>⋯</td><td>No Deposit</td><td>240 </td><td>NULL</td><td>0</td><td>Transient</td><td>98</td><td>0</td><td>1</td><td>Check-Out</td><td>2015-07-03</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td> 14</td><td>2015</td><td>July</td><td>27</td><td>1</td><td>0</td><td>2</td><td>2</td><td>⋯</td><td>No Deposit</td><td>240 </td><td>NULL</td><td>0</td><td>Transient</td><td>98</td><td>0</td><td>1</td><td>Check-Out</td><td>2015-07-03</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 32\n",
       "\\begin{tabular}{lllllllllllllllllllll}\n",
       " hotel & is\\_canceled & lead\\_time & arrival\\_date\\_year & arrival\\_date\\_month & arrival\\_date\\_week\\_number & arrival\\_date\\_day\\_of\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & adults & ⋯ & deposit\\_type & agent & company & days\\_in\\_waiting\\_list & customer\\_type & adr & required\\_car\\_parking\\_spaces & total\\_of\\_special\\_requests & reservation\\_status & reservation\\_status\\_date\\\\\n",
       " <chr> & <dbl> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & ⋯ & <chr> & <chr> & <chr> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <chr> & <date>\\\\\n",
       "\\hline\n",
       "\t Resort Hotel & 0 & 342 & 2015 & July & 27 & 1 & 0 & 0 & 2 & ⋯ & No Deposit & NULL & NULL & 0 & Transient &  0 & 0 & 0 & Check-Out & 2015-07-01\\\\\n",
       "\t Resort Hotel & 0 & 737 & 2015 & July & 27 & 1 & 0 & 0 & 2 & ⋯ & No Deposit & NULL & NULL & 0 & Transient &  0 & 0 & 0 & Check-Out & 2015-07-01\\\\\n",
       "\t Resort Hotel & 0 &   7 & 2015 & July & 27 & 1 & 0 & 1 & 1 & ⋯ & No Deposit & NULL & NULL & 0 & Transient & 75 & 0 & 0 & Check-Out & 2015-07-02\\\\\n",
       "\t Resort Hotel & 0 &  13 & 2015 & July & 27 & 1 & 0 & 1 & 1 & ⋯ & No Deposit & 304  & NULL & 0 & Transient & 75 & 0 & 0 & Check-Out & 2015-07-02\\\\\n",
       "\t Resort Hotel & 0 &  14 & 2015 & July & 27 & 1 & 0 & 2 & 2 & ⋯ & No Deposit & 240  & NULL & 0 & Transient & 98 & 0 & 1 & Check-Out & 2015-07-03\\\\\n",
       "\t Resort Hotel & 0 &  14 & 2015 & July & 27 & 1 & 0 & 2 & 2 & ⋯ & No Deposit & 240  & NULL & 0 & Transient & 98 & 0 & 1 & Check-Out & 2015-07-03\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 32\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | lead_time &lt;dbl&gt; | arrival_date_year &lt;dbl&gt; | arrival_date_month &lt;chr&gt; | arrival_date_week_number &lt;dbl&gt; | arrival_date_day_of_month &lt;dbl&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | adults &lt;dbl&gt; | ⋯ ⋯ | deposit_type &lt;chr&gt; | agent &lt;chr&gt; | company &lt;chr&gt; | days_in_waiting_list &lt;dbl&gt; | customer_type &lt;chr&gt; | adr &lt;dbl&gt; | required_car_parking_spaces &lt;dbl&gt; | total_of_special_requests &lt;dbl&gt; | reservation_status &lt;chr&gt; | reservation_status_date &lt;date&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| Resort Hotel | 0 | 342 | 2015 | July | 27 | 1 | 0 | 0 | 2 | ⋯ | No Deposit | NULL | NULL | 0 | Transient |  0 | 0 | 0 | Check-Out | 2015-07-01 |\n",
       "| Resort Hotel | 0 | 737 | 2015 | July | 27 | 1 | 0 | 0 | 2 | ⋯ | No Deposit | NULL | NULL | 0 | Transient |  0 | 0 | 0 | Check-Out | 2015-07-01 |\n",
       "| Resort Hotel | 0 |   7 | 2015 | July | 27 | 1 | 0 | 1 | 1 | ⋯ | No Deposit | NULL | NULL | 0 | Transient | 75 | 0 | 0 | Check-Out | 2015-07-02 |\n",
       "| Resort Hotel | 0 |  13 | 2015 | July | 27 | 1 | 0 | 1 | 1 | ⋯ | No Deposit | 304  | NULL | 0 | Transient | 75 | 0 | 0 | Check-Out | 2015-07-02 |\n",
       "| Resort Hotel | 0 |  14 | 2015 | July | 27 | 1 | 0 | 2 | 2 | ⋯ | No Deposit | 240  | NULL | 0 | Transient | 98 | 0 | 1 | Check-Out | 2015-07-03 |\n",
       "| Resort Hotel | 0 |  14 | 2015 | July | 27 | 1 | 0 | 2 | 2 | ⋯ | No Deposit | 240  | NULL | 0 | Transient | 98 | 0 | 1 | Check-Out | 2015-07-03 |\n",
       "\n"
      ],
      "text/plain": [
       "  hotel        is_canceled lead_time arrival_date_year arrival_date_month\n",
       "1 Resort Hotel 0           342       2015              July              \n",
       "2 Resort Hotel 0           737       2015              July              \n",
       "3 Resort Hotel 0             7       2015              July              \n",
       "4 Resort Hotel 0            13       2015              July              \n",
       "5 Resort Hotel 0            14       2015              July              \n",
       "6 Resort Hotel 0            14       2015              July              \n",
       "  arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights\n",
       "1 27                       1                         0                      \n",
       "2 27                       1                         0                      \n",
       "3 27                       1                         0                      \n",
       "4 27                       1                         0                      \n",
       "5 27                       1                         0                      \n",
       "6 27                       1                         0                      \n",
       "  stays_in_week_nights adults ⋯ deposit_type agent company days_in_waiting_list\n",
       "1 0                    2      ⋯ No Deposit   NULL  NULL    0                   \n",
       "2 0                    2      ⋯ No Deposit   NULL  NULL    0                   \n",
       "3 1                    1      ⋯ No Deposit   NULL  NULL    0                   \n",
       "4 1                    1      ⋯ No Deposit   304   NULL    0                   \n",
       "5 2                    2      ⋯ No Deposit   240   NULL    0                   \n",
       "6 2                    2      ⋯ No Deposit   240   NULL    0                   \n",
       "  customer_type adr required_car_parking_spaces total_of_special_requests\n",
       "1 Transient      0  0                           0                        \n",
       "2 Transient      0  0                           0                        \n",
       "3 Transient     75  0                           0                        \n",
       "4 Transient     75  0                           0                        \n",
       "5 Transient     98  0                           1                        \n",
       "6 Transient     98  0                           1                        \n",
       "  reservation_status reservation_status_date\n",
       "1 Check-Out          2015-07-01             \n",
       "2 Check-Out          2015-07-01             \n",
       "3 Check-Out          2015-07-02             \n",
       "4 Check-Out          2015-07-02             \n",
       "5 Check-Out          2015-07-03             \n",
       "6 Check-Out          2015-07-03             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "spec_tbl_df [88,998 × 32] (S3: spec_tbl_df/tbl_df/tbl/data.frame)\n",
      " $ hotel                         : chr [1:88998] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled                   : num [1:88998] 0 0 0 0 0 0 0 0 1 1 ...\n",
      " $ lead_time                     : num [1:88998] 342 737 7 13 14 14 0 9 85 75 ...\n",
      " $ arrival_date_year             : num [1:88998] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_date_month            : chr [1:88998] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ arrival_date_week_number      : num [1:88998] 27 27 27 27 27 27 27 27 27 27 ...\n",
      " $ arrival_date_day_of_month     : num [1:88998] 1 1 1 1 1 1 1 1 1 1 ...\n",
      " $ stays_in_weekend_nights       : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights          : num [1:88998] 0 0 1 1 2 2 2 2 3 3 ...\n",
      " $ adults                        : num [1:88998] 2 2 1 1 2 2 2 2 2 2 ...\n",
      " $ children                      : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ babies                        : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ meal                          : chr [1:88998] \"BB\" \"BB\" \"BB\" \"BB\" ...\n",
      " $ country                       : chr [1:88998] \"PRT\" \"PRT\" \"GBR\" \"GBR\" ...\n",
      " $ market_segment                : chr [1:88998] \"Direct\" \"Direct\" \"Direct\" \"Corporate\" ...\n",
      " $ distribution_channel          : chr [1:88998] \"Direct\" \"Direct\" \"Direct\" \"Corporate\" ...\n",
      " $ is_repeated_guest             : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ previous_cancellations        : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ previous_bookings_not_canceled: num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ reserved_room_type            : chr [1:88998] \"C\" \"C\" \"A\" \"A\" ...\n",
      " $ assigned_room_type            : chr [1:88998] \"C\" \"C\" \"C\" \"A\" ...\n",
      " $ booking_changes               : num [1:88998] 3 4 0 0 0 0 0 0 0 0 ...\n",
      " $ deposit_type                  : chr [1:88998] \"No Deposit\" \"No Deposit\" \"No Deposit\" \"No Deposit\" ...\n",
      " $ agent                         : chr [1:88998] \"NULL\" \"NULL\" \"NULL\" \"304\" ...\n",
      " $ company                       : chr [1:88998] \"NULL\" \"NULL\" \"NULL\" \"NULL\" ...\n",
      " $ days_in_waiting_list          : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ customer_type                 : chr [1:88998] \"Transient\" \"Transient\" \"Transient\" \"Transient\" ...\n",
      " $ adr                           : num [1:88998] 0 0 75 75 98 ...\n",
      " $ required_car_parking_spaces   : num [1:88998] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ total_of_special_requests     : num [1:88998] 0 0 0 0 1 1 0 1 1 0 ...\n",
      " $ reservation_status            : chr [1:88998] \"Check-Out\" \"Check-Out\" \"Check-Out\" \"Check-Out\" ...\n",
      " $ reservation_status_date       : Date[1:88998], format: \"2015-07-01\" \"2015-07-01\" ...\n",
      " - attr(*, \"spec\")=\n",
      "  .. cols(\n",
      "  ..   hotel = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   is_canceled = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   lead_time = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   arrival_date_year = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   arrival_date_month = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   arrival_date_week_number = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   arrival_date_day_of_month = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   stays_in_weekend_nights = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   stays_in_week_nights = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   adults = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   children = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   babies = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   meal = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   country = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   market_segment = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   distribution_channel = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   is_repeated_guest = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   previous_cancellations = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   previous_bookings_not_canceled = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   reserved_room_type = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   assigned_room_type = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   booking_changes = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   deposit_type = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   agent = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   company = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   days_in_waiting_list = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   customer_type = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   adr = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   required_car_parking_spaces = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   total_of_special_requests = \u001b[32mcol_double()\u001b[39m,\n",
      "  ..   reservation_status = \u001b[31mcol_character()\u001b[39m,\n",
      "  ..   reservation_status_date = \u001b[34mcol_date(format = \"\")\u001b[39m\n",
      "  .. )\n",
      " - attr(*, \"problems\")=<externalptr> \n"
     ]
    }
   ],
   "source": [
    "#Confirming that the dataset had been properly imported\n",
    "head(df)\n",
    "str(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65c10c6f",
   "metadata": {},
   "source": [
    "The dataset contained a total of 88,998 rows and 32 columns."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01d0d95b",
   "metadata": {},
   "source": [
    "#### 3. Cleaning the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38d1e128",
   "metadata": {},
   "source": [
    "##### ~ Handling duplicates\n",
    "Since this was actual hotel data, all personal identifying information (such as name, phone number, email) had been removed from the data, and as such the primary key had been taken out. However, how I handled duplicates was by checking for and removing duplicate rows with the exact same values across all 32 columns. It is (near) impossible for any 2 guest bookings to have the same values across all 32 columns.    \n",
    "N/B: In a typical work scenario, I would use the primary key to check for duplicates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "518f86ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "26941"
      ],
      "text/latex": [
       "26941"
      ],
      "text/markdown": [
       "26941"
      ],
      "text/plain": [
       "[1] 26941"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#using the nrow() and duplicated() functions to find out the number of duplicate rows with same values across all 32 columns\n",
    "nrow(df[duplicated(df[,1:32]),])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97622f78",
   "metadata": {},
   "source": [
    "26,941 duplicate rows returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c28d32fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "62057"
      ],
      "text/latex": [
       "62057"
      ],
      "text/markdown": [
       "62057"
      ],
      "text/plain": [
       "[1] 62057"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#dropping duplicates\n",
    "df1 <- distinct(df, df[,1:32], .keep_all = TRUE)\n",
    "nrow(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74850dd3",
   "metadata": {},
   "source": [
    "62,057 entries left."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "348b6019",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Warning message in cbind(parts$left, ellip_h, parts$right, deparse.level = 0L):\n",
      "\"number of rows of result is not a multiple of vector length (arg 2)\"\n",
      "Warning message in cbind(parts$left, ellip_h, parts$right, deparse.level = 0L):\n",
      "\"number of rows of result is not a multiple of vector length (arg 2)\"\n",
      "Warning message in cbind(parts$left, ellip_h, parts$right, deparse.level = 0L):\n",
      "\"number of rows of result is not a multiple of vector length (arg 2)\"\n",
      "Warning message in cbind(parts$left, ellip_h, parts$right, deparse.level = 0L):\n",
      "\"number of rows of result is not a multiple of vector length (arg 2)\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 0 × 32</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>lead_time</th><th scope=col>arrival_date_year</th><th scope=col>arrival_date_month</th><th scope=col>arrival_date_week_number</th><th scope=col>arrival_date_day_of_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>adults</th><th scope=col>⋯</th><th scope=col>deposit_type</th><th scope=col>agent</th><th scope=col>company</th><th scope=col>days_in_waiting_list</th><th scope=col>customer_type</th><th scope=col>adr</th><th scope=col>required_car_parking_spaces</th><th scope=col>total_of_special_requests</th><th scope=col>reservation_status</th><th scope=col>reservation_status_date</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>⋯</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 0 × 32\n",
       "\\begin{tabular}{lllllllllllllllllllll}\n",
       " hotel & is\\_canceled & lead\\_time & arrival\\_date\\_year & arrival\\_date\\_month & arrival\\_date\\_week\\_number & arrival\\_date\\_day\\_of\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & adults & ⋯ & deposit\\_type & agent & company & days\\_in\\_waiting\\_list & customer\\_type & adr & required\\_car\\_parking\\_spaces & total\\_of\\_special\\_requests & reservation\\_status & reservation\\_status\\_date\\\\\n",
       " <chr> & <dbl> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & ⋯ & <chr> & <chr> & <chr> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <chr> & <date>\\\\\n",
       "\\hline\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 0 × 32\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | lead_time &lt;dbl&gt; | arrival_date_year &lt;dbl&gt; | arrival_date_month &lt;chr&gt; | arrival_date_week_number &lt;dbl&gt; | arrival_date_day_of_month &lt;dbl&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | adults &lt;dbl&gt; | ⋯ ⋯ | deposit_type &lt;chr&gt; | agent &lt;chr&gt; | company &lt;chr&gt; | days_in_waiting_list &lt;dbl&gt; | customer_type &lt;chr&gt; | adr &lt;dbl&gt; | required_car_parking_spaces &lt;dbl&gt; | total_of_special_requests &lt;dbl&gt; | reservation_status &lt;chr&gt; | reservation_status_date &lt;date&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "\n"
      ],
      "text/plain": [
       "     hotel is_canceled lead_time arrival_date_year arrival_date_month\n",
       "     arrival_date_week_number arrival_date_day_of_month stays_in_weekend_nights\n",
       "     stays_in_week_nights adults ⋯ deposit_type agent company\n",
       "     days_in_waiting_list customer_type adr required_car_parking_spaces\n",
       "     total_of_special_requests reservation_status reservation_status_date"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#double-checking that there were no more duplicates \n",
    "df1[duplicated(df1[,1:32]),]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ff6b25b",
   "metadata": {},
   "source": [
    "The last line of code above returned 0 entries, confirming that there were no duplicates in df1."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ff16efa",
   "metadata": {},
   "source": [
    "##### ~ Dropping irrelevant columns\n",
    "Because my analysis was focused on KPI changes over time, only the following 7 columns were useful for my analysis: hotel, is_canceelled, arrival_date_year, arrival_date_month, stays_in_weekend_nights, stays_in_week_nights, adr. I droped the remaining 25 columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c854b889",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tibble [62,057 × 7] (S3: tbl_df/tbl/data.frame)\n",
      " $ hotel                  : chr [1:62057] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled            : num [1:62057] 0 0 0 0 0 0 0 1 1 1 ...\n",
      " $ arrival_date_year      : num [1:62057] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_date_month     : chr [1:62057] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ stays_in_weekend_nights: num [1:62057] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights   : num [1:62057] 0 0 1 1 2 2 2 3 3 4 ...\n",
      " $ adr                    : num [1:62057] 0 0 75 75 98 ...\n"
     ]
    }
   ],
   "source": [
    "#dropping irrelevant columns\n",
    "df2 <- select(df1, -3, -6, -7, -10:-14, -15:-27, -29:-32)\n",
    "str(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dd90a25",
   "metadata": {},
   "source": [
    "##### ~ Handling missing values\n",
    "My next step was to check for missing values in the columns left."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f37b7e8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "0"
      ],
      "text/latex": [
       "0"
      ],
      "text/markdown": [
       "0"
      ],
      "text/plain": [
       "[1] 0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#to return sum of missing values in df2\n",
    "sum(is.na(df2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db1f502d",
   "metadata": {},
   "source": [
    "No missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a59fa9f0",
   "metadata": {},
   "source": [
    "##### ~ Renaming some columns\n",
    "I renamed some of the columns for clarity. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "49f70f2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#renaming columns\n",
    "df2 <- df2 %>%\n",
    "  rename(arrival_year = arrival_date_year,\n",
    "         arrival_month = arrival_date_month,\n",
    "         average_daily_rate = adr)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2f514d93",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tibble [62,057 × 7] (S3: tbl_df/tbl/data.frame)\n",
      " $ hotel                  : chr [1:62057] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled            : num [1:62057] 0 0 0 0 0 0 0 1 1 1 ...\n",
      " $ arrival_year           : num [1:62057] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_month          : chr [1:62057] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ stays_in_weekend_nights: num [1:62057] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights   : num [1:62057] 0 0 1 1 2 2 2 3 3 4 ...\n",
      " $ average_daily_rate     : num [1:62057] 0 0 75 75 98 ...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 7</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>arrival_year</th><th scope=col>arrival_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>average_daily_rate</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>0</td><td>  0</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>0</td><td>  0</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>1</td><td> 75</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>1</td><td> 75</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>2</td><td> 98</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>2</td><td>107</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 7\n",
       "\\begin{tabular}{lllllll}\n",
       " hotel & is\\_canceled & arrival\\_year & arrival\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & average\\_daily\\_rate\\\\\n",
       " <chr> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 0 &   0\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 0 &   0\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 1 &  75\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 1 &  75\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 2 &  98\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 2 & 107\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 7\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | arrival_year &lt;dbl&gt; | arrival_month &lt;chr&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | average_daily_rate &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 0 |   0 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 0 |   0 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 1 |  75 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 1 |  75 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 2 |  98 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 2 | 107 |\n",
       "\n"
      ],
      "text/plain": [
       "  hotel        is_canceled arrival_year arrival_month stays_in_weekend_nights\n",
       "1 Resort Hotel 0           2015         July          0                      \n",
       "2 Resort Hotel 0           2015         July          0                      \n",
       "3 Resort Hotel 0           2015         July          0                      \n",
       "4 Resort Hotel 0           2015         July          0                      \n",
       "5 Resort Hotel 0           2015         July          0                      \n",
       "6 Resort Hotel 0           2015         July          0                      \n",
       "  stays_in_week_nights average_daily_rate\n",
       "1 0                      0               \n",
       "2 0                      0               \n",
       "3 1                     75               \n",
       "4 1                     75               \n",
       "5 2                     98               \n",
       "6 2                    107               "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#double-checking that my columns had been properly renamed\n",
    "str(df2)\n",
    "head(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea8c50c2",
   "metadata": {},
   "source": [
    "##### ~ Dropping entries that were not relevant to my analysis\n",
    "I mentioned earlier that this analysis would focus on data for the period from **July 2015 to June 2017** for the **Resort Hotel** alone, so I went ahead with entries that met the above conditions, and dropped the rest.  \n",
    "Also the dataset is such that the \"is_cancelled\" column cointains only 0 and 1 values. 0 represents when a booking was not cancelled and 1 represents when a booking was cancelled. For my analysis, I went ahead with only the cases where the booking was not cancelled (ie. I dropped the entries where the booking was cancelled)      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a6f3b39d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "25992"
      ],
      "text/latex": [
       "25992"
      ],
      "text/markdown": [
       "25992"
      ],
      "text/plain": [
       "[1] 25992"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#selecting only enties where the booking was not cancelled, and the hotel was Resort Hotel\n",
    "df3 <- df2[df2$is_canceled == 0 & df2$hotel == 'Resort Hotel',]\n",
    "nrow(df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b415aecf",
   "metadata": {},
   "source": [
    "25,992 entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0ef54b77",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tibble [23,856 × 7] (S3: tbl_df/tbl/data.frame)\n",
      " $ hotel                  : chr [1:23856] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled            : num [1:23856] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ arrival_year           : num [1:23856] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_month          : chr [1:23856] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ stays_in_weekend_nights: num [1:23856] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights   : num [1:23856] 0 0 1 1 2 2 2 4 4 4 ...\n",
      " $ average_daily_rate     : num [1:23856] 0 0 75 75 98 ...\n"
     ]
    }
   ],
   "source": [
    "#dropping all entries after June 2017 (ie. dropping entries for July 2017 and August 2017)\n",
    "df4 <- df3[!((df3$arrival_month == \"July\" & df3$arrival_year == 2017) | \n",
    "             (df3$arrival_month == \"August\" & df3$arrival_year == 2017)),]\n",
    "\n",
    "str(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5015e5c",
   "metadata": {},
   "source": [
    "Dropped 2,136 rows; 23,856 rows left"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68270165",
   "metadata": {},
   "source": [
    "##### ~ Dropping bad data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "53ec10cf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A psych: 7 × 13</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>vars</th><th scope=col>n</th><th scope=col>mean</th><th scope=col>sd</th><th scope=col>median</th><th scope=col>trimmed</th><th scope=col>mad</th><th scope=col>min</th><th scope=col>max</th><th scope=col>range</th><th scope=col>skew</th><th scope=col>kurtosis</th><th scope=col>se</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>hotel*</th><td>1</td><td>23856</td><td>   1.000000</td><td> 0.0000000</td><td>   1</td><td>   1.000000</td><td> 0.0000</td><td>   1.00</td><td>   1</td><td>  0.00</td><td>        NaN</td><td>       NaN</td><td>0.000000000</td></tr>\n",
       "\t<tr><th scope=row>is_canceled</th><td>2</td><td>23856</td><td>   0.000000</td><td> 0.0000000</td><td>   0</td><td>   0.000000</td><td> 0.0000</td><td>   0.00</td><td>   0</td><td>  0.00</td><td>        NaN</td><td>       NaN</td><td>0.000000000</td></tr>\n",
       "\t<tr><th scope=row>arrival_year</th><td>3</td><td>23856</td><td>2016.028630</td><td> 0.6980359</td><td>2016</td><td>2016.035785</td><td> 0.0000</td><td>2015.00</td><td>2017</td><td>  2.00</td><td>-0.03893328</td><td>-0.9480246</td><td>0.004519381</td></tr>\n",
       "\t<tr><th scope=row>arrival_month*</th><td>4</td><td>23856</td><td>   6.512282</td><td> 3.4758465</td><td>   7</td><td>   6.530756</td><td> 4.4478</td><td>   1.00</td><td>  12</td><td> 11.00</td><td>-0.04160526</td><td>-1.2283331</td><td>0.022504107</td></tr>\n",
       "\t<tr><th scope=row>stays_in_weekend_nights</th><td>5</td><td>23856</td><td>   1.132839</td><td> 1.1630451</td><td>   1</td><td>   1.005921</td><td> 1.4826</td><td>   0.00</td><td>  19</td><td> 19.00</td><td> 1.57992437</td><td> 9.3931871</td><td>0.007530048</td></tr>\n",
       "\t<tr><th scope=row>stays_in_week_nights</th><td>6</td><td>23856</td><td>   2.987215</td><td> 2.4957150</td><td>   2</td><td>   2.701719</td><td> 1.4826</td><td>   0.00</td><td>  50</td><td> 50.00</td><td> 2.37788804</td><td>18.1145363</td><td>0.016158319</td></tr>\n",
       "\t<tr><th scope=row>average_daily_rate</th><td>7</td><td>23856</td><td>  84.532770</td><td>52.9328086</td><td>  69</td><td>  77.508349</td><td>38.5476</td><td>  -6.38</td><td> 508</td><td>514.38</td><td> 1.31861336</td><td> 1.9900011</td><td>0.342709486</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A psych: 7 × 13\n",
       "\\begin{tabular}{r|lllllllllllll}\n",
       "  & vars & n & mean & sd & median & trimmed & mad & min & max & range & skew & kurtosis & se\\\\\n",
       "  & <int> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\thotel* & 1 & 23856 &    1.000000 &  0.0000000 &    1 &    1.000000 &  0.0000 &    1.00 &    1 &   0.00 &         NaN &        NaN & 0.000000000\\\\\n",
       "\tis\\_canceled & 2 & 23856 &    0.000000 &  0.0000000 &    0 &    0.000000 &  0.0000 &    0.00 &    0 &   0.00 &         NaN &        NaN & 0.000000000\\\\\n",
       "\tarrival\\_year & 3 & 23856 & 2016.028630 &  0.6980359 & 2016 & 2016.035785 &  0.0000 & 2015.00 & 2017 &   2.00 & -0.03893328 & -0.9480246 & 0.004519381\\\\\n",
       "\tarrival\\_month* & 4 & 23856 &    6.512282 &  3.4758465 &    7 &    6.530756 &  4.4478 &    1.00 &   12 &  11.00 & -0.04160526 & -1.2283331 & 0.022504107\\\\\n",
       "\tstays\\_in\\_weekend\\_nights & 5 & 23856 &    1.132839 &  1.1630451 &    1 &    1.005921 &  1.4826 &    0.00 &   19 &  19.00 &  1.57992437 &  9.3931871 & 0.007530048\\\\\n",
       "\tstays\\_in\\_week\\_nights & 6 & 23856 &    2.987215 &  2.4957150 &    2 &    2.701719 &  1.4826 &    0.00 &   50 &  50.00 &  2.37788804 & 18.1145363 & 0.016158319\\\\\n",
       "\taverage\\_daily\\_rate & 7 & 23856 &   84.532770 & 52.9328086 &   69 &   77.508349 & 38.5476 &   -6.38 &  508 & 514.38 &  1.31861336 &  1.9900011 & 0.342709486\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A psych: 7 × 13\n",
       "\n",
       "| <!--/--> | vars &lt;int&gt; | n &lt;dbl&gt; | mean &lt;dbl&gt; | sd &lt;dbl&gt; | median &lt;dbl&gt; | trimmed &lt;dbl&gt; | mad &lt;dbl&gt; | min &lt;dbl&gt; | max &lt;dbl&gt; | range &lt;dbl&gt; | skew &lt;dbl&gt; | kurtosis &lt;dbl&gt; | se &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| hotel* | 1 | 23856 |    1.000000 |  0.0000000 |    1 |    1.000000 |  0.0000 |    1.00 |    1 |   0.00 |         NaN |        NaN | 0.000000000 |\n",
       "| is_canceled | 2 | 23856 |    0.000000 |  0.0000000 |    0 |    0.000000 |  0.0000 |    0.00 |    0 |   0.00 |         NaN |        NaN | 0.000000000 |\n",
       "| arrival_year | 3 | 23856 | 2016.028630 |  0.6980359 | 2016 | 2016.035785 |  0.0000 | 2015.00 | 2017 |   2.00 | -0.03893328 | -0.9480246 | 0.004519381 |\n",
       "| arrival_month* | 4 | 23856 |    6.512282 |  3.4758465 |    7 |    6.530756 |  4.4478 |    1.00 |   12 |  11.00 | -0.04160526 | -1.2283331 | 0.022504107 |\n",
       "| stays_in_weekend_nights | 5 | 23856 |    1.132839 |  1.1630451 |    1 |    1.005921 |  1.4826 |    0.00 |   19 |  19.00 |  1.57992437 |  9.3931871 | 0.007530048 |\n",
       "| stays_in_week_nights | 6 | 23856 |    2.987215 |  2.4957150 |    2 |    2.701719 |  1.4826 |    0.00 |   50 |  50.00 |  2.37788804 | 18.1145363 | 0.016158319 |\n",
       "| average_daily_rate | 7 | 23856 |   84.532770 | 52.9328086 |   69 |   77.508349 | 38.5476 |   -6.38 |  508 | 514.38 |  1.31861336 |  1.9900011 | 0.342709486 |\n",
       "\n"
      ],
      "text/plain": [
       "                        vars n     mean        sd         median trimmed    \n",
       "hotel*                  1    23856    1.000000  0.0000000    1      1.000000\n",
       "is_canceled             2    23856    0.000000  0.0000000    0      0.000000\n",
       "arrival_year            3    23856 2016.028630  0.6980359 2016   2016.035785\n",
       "arrival_month*          4    23856    6.512282  3.4758465    7      6.530756\n",
       "stays_in_weekend_nights 5    23856    1.132839  1.1630451    1      1.005921\n",
       "stays_in_week_nights    6    23856    2.987215  2.4957150    2      2.701719\n",
       "average_daily_rate      7    23856   84.532770 52.9328086   69     77.508349\n",
       "                        mad     min     max  range  skew        kurtosis  \n",
       "hotel*                   0.0000    1.00    1   0.00         NaN        NaN\n",
       "is_canceled              0.0000    0.00    0   0.00         NaN        NaN\n",
       "arrival_year             0.0000 2015.00 2017   2.00 -0.03893328 -0.9480246\n",
       "arrival_month*           4.4478    1.00   12  11.00 -0.04160526 -1.2283331\n",
       "stays_in_weekend_nights  1.4826    0.00   19  19.00  1.57992437  9.3931871\n",
       "stays_in_week_nights     1.4826    0.00   50  50.00  2.37788804 18.1145363\n",
       "average_daily_rate      38.5476   -6.38  508 514.38  1.31861336  1.9900011\n",
       "                        se         \n",
       "hotel*                  0.000000000\n",
       "is_canceled             0.000000000\n",
       "arrival_year            0.004519381\n",
       "arrival_month*          0.022504107\n",
       "stays_in_weekend_nights 0.007530048\n",
       "stays_in_week_nights    0.016158319\n",
       "average_daily_rate      0.342709486"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#looking at the statistics for numeric columns  \n",
    "describe(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7586df9e",
   "metadata": {},
   "source": [
    "From the summary statistics above, I saw that the minimum Average Daily Rate was a negaitive number. This should not be possible, so I took a closer look."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a0faa58c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 1 × 7</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>arrival_year</th><th scope=col>arrival_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>average_daily_rate</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2017</td><td>March</td><td>4</td><td>6</td><td>-6.38</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 1 × 7\n",
       "\\begin{tabular}{lllllll}\n",
       " hotel & is\\_canceled & arrival\\_year & arrival\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & average\\_daily\\_rate\\\\\n",
       " <chr> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Resort Hotel & 0 & 2017 & March & 4 & 6 & -6.38\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 1 × 7\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | arrival_year &lt;dbl&gt; | arrival_month &lt;chr&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | average_daily_rate &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "| Resort Hotel | 0 | 2017 | March | 4 | 6 | -6.38 |\n",
       "\n"
      ],
      "text/plain": [
       "  hotel        is_canceled arrival_year arrival_month stays_in_weekend_nights\n",
       "1 Resort Hotel 0           2017         March         4                      \n",
       "  stays_in_week_nights average_daily_rate\n",
       "1 6                    -6.38             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#checking for entries where Average Daily Rate was below 0\n",
    "df4[(df4$average_daily_rate < 0),]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66b89b8b",
   "metadata": {},
   "source": [
    "There was only one entry with Average Daily Rate less than 0. I dropped this entry   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "3a446ad1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tibble [23,855 × 7] (S3: tbl_df/tbl/data.frame)\n",
      " $ hotel                  : chr [1:23855] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled            : num [1:23855] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ arrival_year           : num [1:23855] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_month          : chr [1:23855] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ stays_in_weekend_nights: num [1:23855] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights   : num [1:23855] 0 0 1 1 2 2 2 4 4 4 ...\n",
      " $ average_daily_rate     : num [1:23855] 0 0 75 75 98 ...\n"
     ]
    }
   ],
   "source": [
    "#dropping bad entry where Average Daily Rate was negative\n",
    "df5 <- df4[!(df4$average_daily_rate < 0),]\n",
    "str(df5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f1e77b9",
   "metadata": {},
   "source": [
    "23,855 entries left."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ad54547b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 0 × 7</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>arrival_year</th><th scope=col>arrival_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>average_daily_rate</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 0 × 7\n",
       "\\begin{tabular}{lllllll}\n",
       " hotel & is\\_canceled & arrival\\_year & arrival\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & average\\_daily\\_rate\\\\\n",
       " <chr> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 0 × 7\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | arrival_year &lt;dbl&gt; | arrival_month &lt;chr&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | average_daily_rate &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "\n"
      ],
      "text/plain": [
       "     hotel is_canceled arrival_year arrival_month stays_in_weekend_nights\n",
       "     stays_in_week_nights average_daily_rate"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#double-checking that there were no more Average Daily Rate entries that were less than 0 \n",
    "df5[(df5$average_daily_rate < 0),]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed4535b6",
   "metadata": {},
   "source": [
    "No more entries with Average Daily Rate less than 0."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "163db627",
   "metadata": {},
   "source": [
    "#### 4. Adding additional columns\n",
    "To aid my analysis, I added 2 calculated columns:    \n",
    "i).  \"no_of days_stayed\" to calculate the total number of days that each guest stayed.  \n",
    "ii). \"guest_spend\" to calculate the amount spent by each guest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "df265456",
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 <- df5 %>%\n",
    "  mutate(no_of_days_stayed = stays_in_weekend_nights + stays_in_week_nights)\n",
    "\n",
    "df5 <- df5 %>%\n",
    "  mutate(guest_spend = no_of_days_stayed * average_daily_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "6d70d3d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tibble [23,855 × 9] (S3: tbl_df/tbl/data.frame)\n",
      " $ hotel                  : chr [1:23855] \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" \"Resort Hotel\" ...\n",
      " $ is_canceled            : num [1:23855] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ arrival_year           : num [1:23855] 2015 2015 2015 2015 2015 ...\n",
      " $ arrival_month          : chr [1:23855] \"July\" \"July\" \"July\" \"July\" ...\n",
      " $ stays_in_weekend_nights: num [1:23855] 0 0 0 0 0 0 0 0 0 0 ...\n",
      " $ stays_in_week_nights   : num [1:23855] 0 0 1 1 2 2 2 4 4 4 ...\n",
      " $ average_daily_rate     : num [1:23855] 0 0 75 75 98 ...\n",
      " $ no_of_days_stayed      : num [1:23855] 0 0 1 1 2 2 2 4 4 4 ...\n",
      " $ guest_spend            : num [1:23855] 0 0 75 75 196 ...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 10 × 9</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>hotel</th><th scope=col>is_canceled</th><th scope=col>arrival_year</th><th scope=col>arrival_month</th><th scope=col>stays_in_weekend_nights</th><th scope=col>stays_in_week_nights</th><th scope=col>average_daily_rate</th><th scope=col>no_of_days_stayed</th><th scope=col>guest_spend</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>0</td><td>  0.00</td><td>0</td><td>  0.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>0</td><td>  0.00</td><td>0</td><td>  0.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>1</td><td> 75.00</td><td>1</td><td> 75.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>1</td><td> 75.00</td><td>1</td><td> 75.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>2</td><td> 98.00</td><td>2</td><td>196.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>2</td><td>107.00</td><td>2</td><td>214.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>2</td><td>103.00</td><td>2</td><td>206.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>4</td><td>145.00</td><td>4</td><td>580.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>4</td><td> 97.00</td><td>4</td><td>388.00</td></tr>\n",
       "\t<tr><td>Resort Hotel</td><td>0</td><td>2015</td><td>July</td><td>0</td><td>4</td><td>154.77</td><td>4</td><td>619.08</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 10 × 9\n",
       "\\begin{tabular}{lllllllll}\n",
       " hotel & is\\_canceled & arrival\\_year & arrival\\_month & stays\\_in\\_weekend\\_nights & stays\\_in\\_week\\_nights & average\\_daily\\_rate & no\\_of\\_days\\_stayed & guest\\_spend\\\\\n",
       " <chr> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 0 &   0.00 & 0 &   0.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 0 &   0.00 & 0 &   0.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 1 &  75.00 & 1 &  75.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 1 &  75.00 & 1 &  75.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 2 &  98.00 & 2 & 196.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 2 & 107.00 & 2 & 214.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 2 & 103.00 & 2 & 206.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 4 & 145.00 & 4 & 580.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 4 &  97.00 & 4 & 388.00\\\\\n",
       "\t Resort Hotel & 0 & 2015 & July & 0 & 4 & 154.77 & 4 & 619.08\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 10 × 9\n",
       "\n",
       "| hotel &lt;chr&gt; | is_canceled &lt;dbl&gt; | arrival_year &lt;dbl&gt; | arrival_month &lt;chr&gt; | stays_in_weekend_nights &lt;dbl&gt; | stays_in_week_nights &lt;dbl&gt; | average_daily_rate &lt;dbl&gt; | no_of_days_stayed &lt;dbl&gt; | guest_spend &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 0 |   0.00 | 0 |   0.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 0 |   0.00 | 0 |   0.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 1 |  75.00 | 1 |  75.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 1 |  75.00 | 1 |  75.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 2 |  98.00 | 2 | 196.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 2 | 107.00 | 2 | 214.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 2 | 103.00 | 2 | 206.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 4 | 145.00 | 4 | 580.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 4 |  97.00 | 4 | 388.00 |\n",
       "| Resort Hotel | 0 | 2015 | July | 0 | 4 | 154.77 | 4 | 619.08 |\n",
       "\n"
      ],
      "text/plain": [
       "   hotel        is_canceled arrival_year arrival_month stays_in_weekend_nights\n",
       "1  Resort Hotel 0           2015         July          0                      \n",
       "2  Resort Hotel 0           2015         July          0                      \n",
       "3  Resort Hotel 0           2015         July          0                      \n",
       "4  Resort Hotel 0           2015         July          0                      \n",
       "5  Resort Hotel 0           2015         July          0                      \n",
       "6  Resort Hotel 0           2015         July          0                      \n",
       "7  Resort Hotel 0           2015         July          0                      \n",
       "8  Resort Hotel 0           2015         July          0                      \n",
       "9  Resort Hotel 0           2015         July          0                      \n",
       "10 Resort Hotel 0           2015         July          0                      \n",
       "   stays_in_week_nights average_daily_rate no_of_days_stayed guest_spend\n",
       "1  0                      0.00             0                   0.00     \n",
       "2  0                      0.00             0                   0.00     \n",
       "3  1                     75.00             1                  75.00     \n",
       "4  1                     75.00             1                  75.00     \n",
       "5  2                     98.00             2                 196.00     \n",
       "6  2                    107.00             2                 214.00     \n",
       "7  2                    103.00             2                 206.00     \n",
       "8  4                    145.00             4                 580.00     \n",
       "9  4                     97.00             4                 388.00     \n",
       "10 4                    154.77             4                 619.08     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#double-checking that my columns were successfully added\n",
    "str(df5)\n",
    "head(df5, 10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a99a862",
   "metadata": {},
   "source": [
    "#### 5. Exporting my cleaned dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "38f833a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#saving my final dataset as a CSV file called \"hotel_booking_cleaned\"\n",
    "write.csv(df5, \"C:\\\\Users\\\\ETIABA CHAMBER'S\\\\Documents\\\\Data_Analytics\\\\My_portfolio\\\\Hotel_Booking\\\\hotel_bookings_cleaned.csv\", row.names = FALSE)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.2.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}