hotel_rate_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysing the Hotel Rates (2004–2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependencies\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>141.6038</td>\n",
       "      <td>0.738484</td>\n",
       "      <td>2004-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>143.1187</td>\n",
       "      <td>0.719571</td>\n",
       "      <td>2004-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>137.4212</td>\n",
       "      <td>0.750757</td>\n",
       "      <td>2004-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>141.5987</td>\n",
       "      <td>0.778466</td>\n",
       "      <td>2004-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>148.4632</td>\n",
       "      <td>0.682347</td>\n",
       "      <td>2004-11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Average Daily Rate  Hotel Occupancy     Date\n",
       "0            141.6038         0.738484  2004-07\n",
       "1            143.1187         0.719571  2004-08\n",
       "2            137.4212         0.750757  2004-09\n",
       "3            141.5987         0.778466  2004-10\n",
       "4            148.4632         0.682347  2004-11"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load the csv file as a dataframe\n",
    "hotelrates_df = pd.read_csv(\"Data/hotel_dailyrates.csv\", encoding = \"utf-8\")\n",
    "hotelrates_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting average daily rates to monthly rates\n",
    "- Create subsets for months that don't change number of days\n",
    "- Create a subset for February (which has different days depending on the year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of years from the Date column\n",
    "years_all = [date[0:4] for date in hotelrates_df[\"Date\"]] \n",
    "    # date[0:4] gives substrings from the Date column\n",
    "    \n",
    "years_all = [int(i) for i in years_all] # convert list of strings to list of int"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Regular months (30 and 31 days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of months in numerical format\n",
    "month_30 = [\"04\",\"06\",\"09\",\"11\"]\n",
    "month_31 = [\"01\",\"03\",\"05\",\"07\",\"08\",\"10\",\"12\"]\n",
    "month_28 = [\"02\"]\n",
    "\n",
    "month_by_day = [month_30, month_31]\n",
    "days = [30, 31]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a function that extracts the month (2-digit format) from the Date column\n",
    "def months(df,list):\n",
    "    x = [df.loc[df[\"Date\"].str.slice(-2) == item] for item in list]\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create lists for months with 30 and 31 days whatever the year\n",
    "month_grps = [months(hotelrates_df,grp) for grp in month_by_day] # groups of months based on number of days\n",
    "\n",
    "avg_daily = [[(month[\"Average Daily Rate\"]) for month in grp] for grp in month_grps] # daily average rates per group\n",
    "mo_rate_30 = [(i * days[0]) for i in avg_daily[0]] # monthly rates in the 30-day group       \n",
    "mo_rate_31 = [(i * days[1]) for i in avg_daily[1]] # monthly rates in the 31-day group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>148.8121</td>\n",
       "      <td>0.729100</td>\n",
       "      <td>2005-01</td>\n",
       "      <td>4613.1751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>160.7223</td>\n",
       "      <td>0.755878</td>\n",
       "      <td>2006-01</td>\n",
       "      <td>4982.3913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>179.4341</td>\n",
       "      <td>0.727883</td>\n",
       "      <td>2007-01</td>\n",
       "      <td>5562.4571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>184.5935</td>\n",
       "      <td>0.785091</td>\n",
       "      <td>2008-01</td>\n",
       "      <td>5722.3985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>169.4801</td>\n",
       "      <td>0.742489</td>\n",
       "      <td>2009-01</td>\n",
       "      <td>5253.8831</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Average Daily Rate  Hotel Occupancy     Date  Monthly Rate\n",
       "6             148.8121         0.729100  2005-01     4613.1751\n",
       "18            160.7223         0.755878  2006-01     4982.3913\n",
       "30            179.4341         0.727883  2007-01     5562.4571\n",
       "42            184.5935         0.785091  2008-01     5722.3985\n",
       "54            169.4801         0.742489  2009-01     5253.8831"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create dataframes for months grouped by number of days (30,31)\n",
    "df30 = pd.concat(month_grps[0]) # puts all values in the same columns\n",
    "df30[\"Monthly Rate\"] = pd.concat(mo_rate_30) # add the monthly rate as an additional column\n",
    "\n",
    "df31 = pd.concat(month_grps[1]) # puts all values in the same columns\n",
    "df31[\"Monthly Rate\"] = pd.concat(mo_rate_31) # add the monthly rate as an additional column\n",
    "\n",
    "df31.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For February, if the year falls on a leap year, the number of days is 29. If not, 28."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Extract a dataframe that contains the data for Feb\n",
    "Feb = months(hotelrates_df,month_28)\n",
    "Feb = Feb[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a function that creates a list of leap years \n",
    "# Resource: https://stackoverflow.com/a/18951699/10205963\n",
    "def leap(start,end):\n",
    "    return [year for year in range(start, end + 1)\n",
    "            if (year % 400 == 0) or (year % 4 == 0 and not year % 100 == 0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2004', '2008', '2012', '2016']"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the leap years from the list of years\n",
    "leap_yrs = leap(years_all[0], years_all[-1]) # years[0] = 1st; years[-1] = last\n",
    "leap_yrs = [str(i) for i in leap_yrs]\n",
    "leap_yrs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>197.1489</td>\n",
       "      <td>0.811044</td>\n",
       "      <td>2008-02</td>\n",
       "      <td>5717.3181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>213.3869</td>\n",
       "      <td>0.822019</td>\n",
       "      <td>2012-02</td>\n",
       "      <td>6188.2201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>315.3700</td>\n",
       "      <td>0.935458</td>\n",
       "      <td>2016-02</td>\n",
       "      <td>9145.7300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Average Daily Rate  Hotel Occupancy     Date  Monthly Rate\n",
       "43             197.1489         0.811044  2008-02     5717.3181\n",
       "91             213.3869         0.822019  2012-02     6188.2201\n",
       "139            315.3700         0.935458  2016-02     9145.7300"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a subset for Feb data for leap years\n",
    "leap_Feb = Feb.loc[Feb[\"Date\"].str.contains(\"|\".join(leap_yrs))]\n",
    "\n",
    "# Calculate monthly rates for Feb leap years\n",
    "leap_Feb_mo_rate = [(i * 29) for i in leap_Feb[\"Average Daily Rate\"]] \n",
    "\n",
    "# Add the Monthly Rates as a new column\n",
    "# Resource: https://stackoverflow.com/a/47694287/10205963\n",
    "\n",
    "se = pd.Series(leap_Feb_mo_rate) # Convert the monthly rate list to a series\n",
    "leap_Feb.insert(loc = 3, column = \"Monthly Rate\", value = se.values) # add the column Monthly Rate (no errors)\n",
    "    \n",
    "leap_Feb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>151.1379</td>\n",
       "      <td>0.740805</td>\n",
       "      <td>2005-02</td>\n",
       "      <td>4231.8612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>161.3353</td>\n",
       "      <td>0.723245</td>\n",
       "      <td>2006-02</td>\n",
       "      <td>4517.3884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>186.9618</td>\n",
       "      <td>0.791289</td>\n",
       "      <td>2007-02</td>\n",
       "      <td>5234.9304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>157.9055</td>\n",
       "      <td>0.648616</td>\n",
       "      <td>2009-02</td>\n",
       "      <td>4421.3540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>150.5339</td>\n",
       "      <td>0.762755</td>\n",
       "      <td>2010-02</td>\n",
       "      <td>4214.9492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>181.8734</td>\n",
       "      <td>0.837384</td>\n",
       "      <td>2011-02</td>\n",
       "      <td>5092.4552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>201.8483</td>\n",
       "      <td>0.854944</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>5651.7524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>234.2935</td>\n",
       "      <td>0.898843</td>\n",
       "      <td>2014-02</td>\n",
       "      <td>6560.2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>236.7917</td>\n",
       "      <td>0.883478</td>\n",
       "      <td>2015-02</td>\n",
       "      <td>6630.1676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>292.0611</td>\n",
       "      <td>0.914510</td>\n",
       "      <td>2017-02</td>\n",
       "      <td>8177.7108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>246.3703</td>\n",
       "      <td>0.844234</td>\n",
       "      <td>2018-02</td>\n",
       "      <td>6898.3684</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Average Daily Rate  Hotel Occupancy     Date  Monthly Rate\n",
       "7              151.1379         0.740805  2005-02     4231.8612\n",
       "19             161.3353         0.723245  2006-02     4517.3884\n",
       "31             186.9618         0.791289  2007-02     5234.9304\n",
       "55             157.9055         0.648616  2009-02     4421.3540\n",
       "67             150.5339         0.762755  2010-02     4214.9492\n",
       "79             181.8734         0.837384  2011-02     5092.4552\n",
       "103            201.8483         0.854944  2013-02     5651.7524\n",
       "115            234.2935         0.898843  2014-02     6560.2180\n",
       "127            236.7917         0.883478  2015-02     6630.1676\n",
       "151            292.0611         0.914510  2017-02     8177.7108\n",
       "163            246.3703         0.844234  2018-02     6898.3684"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a subset for Feb data for regular years\n",
    "reg_Feb = Feb.loc[~Feb[\"Date\"].str.contains(\"|\".join(leap_yrs))]\n",
    "\n",
    "# Calculate monthly rates for Feb regular years\n",
    "reg_Feb_mo_rate = [(i * 28) for i in reg_Feb[\"Average Daily Rate\"]] \n",
    "\n",
    "\n",
    "# Add the Monthly Rates as a new column\n",
    "\n",
    "se2 = pd.Series(reg_Feb_mo_rate) # Convert the monthly rate list to a series\n",
    "reg_Feb.insert(loc = 3, column = \"Monthly Rate\", value = se2.values) # add the column Monthly Rate (no errors)\n",
    "\n",
    "reg_Feb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>197.1489</td>\n",
       "      <td>0.811044</td>\n",
       "      <td>2008-02</td>\n",
       "      <td>5717.3181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>213.3869</td>\n",
       "      <td>0.822019</td>\n",
       "      <td>2012-02</td>\n",
       "      <td>6188.2201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>315.3700</td>\n",
       "      <td>0.935458</td>\n",
       "      <td>2016-02</td>\n",
       "      <td>9145.7300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>151.1379</td>\n",
       "      <td>0.740805</td>\n",
       "      <td>2005-02</td>\n",
       "      <td>4231.8612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>161.3353</td>\n",
       "      <td>0.723245</td>\n",
       "      <td>2006-02</td>\n",
       "      <td>4517.3884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>186.9618</td>\n",
       "      <td>0.791289</td>\n",
       "      <td>2007-02</td>\n",
       "      <td>5234.9304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>157.9055</td>\n",
       "      <td>0.648616</td>\n",
       "      <td>2009-02</td>\n",
       "      <td>4421.3540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>150.5339</td>\n",
       "      <td>0.762755</td>\n",
       "      <td>2010-02</td>\n",
       "      <td>4214.9492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>181.8734</td>\n",
       "      <td>0.837384</td>\n",
       "      <td>2011-02</td>\n",
       "      <td>5092.4552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>201.8483</td>\n",
       "      <td>0.854944</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>5651.7524</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>234.2935</td>\n",
       "      <td>0.898843</td>\n",
       "      <td>2014-02</td>\n",
       "      <td>6560.2180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>236.7917</td>\n",
       "      <td>0.883478</td>\n",
       "      <td>2015-02</td>\n",
       "      <td>6630.1676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>292.0611</td>\n",
       "      <td>0.914510</td>\n",
       "      <td>2017-02</td>\n",
       "      <td>8177.7108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>246.3703</td>\n",
       "      <td>0.844234</td>\n",
       "      <td>2018-02</td>\n",
       "      <td>6898.3684</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Average Daily Rate  Hotel Occupancy     Date  Monthly Rate\n",
       "43             197.1489         0.811044  2008-02     5717.3181\n",
       "91             213.3869         0.822019  2012-02     6188.2201\n",
       "139            315.3700         0.935458  2016-02     9145.7300\n",
       "7              151.1379         0.740805  2005-02     4231.8612\n",
       "19             161.3353         0.723245  2006-02     4517.3884\n",
       "31             186.9618         0.791289  2007-02     5234.9304\n",
       "55             157.9055         0.648616  2009-02     4421.3540\n",
       "67             150.5339         0.762755  2010-02     4214.9492\n",
       "79             181.8734         0.837384  2011-02     5092.4552\n",
       "103            201.8483         0.854944  2013-02     5651.7524\n",
       "115            234.2935         0.898843  2014-02     6560.2180\n",
       "127            236.7917         0.883478  2015-02     6630.1676\n",
       "151            292.0611         0.914510  2017-02     8177.7108\n",
       "163            246.3703         0.844234  2018-02     6898.3684"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate the two dataframes together (Feb)\n",
    "dfFeb = pd.concat([leap_Feb,reg_Feb])\n",
    "dfFeb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>157.8543</td>\n",
       "      <td>0.740657</td>\n",
       "      <td>2005-04</td>\n",
       "      <td>4735.629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>169.9458</td>\n",
       "      <td>0.770442</td>\n",
       "      <td>2006-04</td>\n",
       "      <td>5098.374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>183.4274</td>\n",
       "      <td>0.764485</td>\n",
       "      <td>2007-04</td>\n",
       "      <td>5502.822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>183.7442</td>\n",
       "      <td>0.768456</td>\n",
       "      <td>2008-04</td>\n",
       "      <td>5512.326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>157.9462</td>\n",
       "      <td>0.743636</td>\n",
       "      <td>2009-04</td>\n",
       "      <td>4738.386</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Average Daily Rate  Hotel Occupancy     Date  Monthly Rate\n",
       "9             157.8543         0.740657  2005-04      4735.629\n",
       "21            169.9458         0.770442  2006-04      5098.374\n",
       "33            183.4274         0.764485  2007-04      5502.822\n",
       "45            183.7442         0.768456  2008-04      5512.326\n",
       "57            157.9462         0.743636  2009-04      4738.386"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate all dataframes (30-day, 31-day, Feb)\n",
    "hotelrates_df2 = pd.concat([df30, df31, dfFeb])\n",
    "hotelrates_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Daily Rate</th>\n",
       "      <th>Hotel Occupancy</th>\n",
       "      <th>Date</th>\n",
       "      <th>Monthly Rate</th>\n",
       "      <th>Corrected Monthly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>157.8543</td>\n",
       "      <td>0.740657</td>\n",
       "      <td>2005-04</td>\n",
       "      <td>4735.629</td>\n",
       "      <td>3507.477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>169.9458</td>\n",
       "      <td>0.770442</td>\n",
       "      <td>2006-04</td>\n",
       "      <td>5098.374</td>\n",
       "      <td>3928.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>183.4274</td>\n",
       "      <td>0.764485</td>\n",
       "      <td>2007-04</td>\n",
       "      <td>5502.822</td>\n",
       "      <td>4206.826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>183.7442</td>\n",
       "      <td>0.768456</td>\n",
       "      <td>2008-04</td>\n",
       "      <td>5512.326</td>\n",
       "      <td>4235.983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>157.9462</td>\n",
       "      <td>0.743636</td>\n",
       "      <td>2009-04</td>\n",
       "      <td>4738.386</td>\n",
       "      <td>3523.632</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Average Daily Rate  Hotel Occupancy     Date  Monthly Rate  \\\n",
       "9             157.8543         0.740657  2005-04      4735.629   \n",
       "21            169.9458         0.770442  2006-04      5098.374   \n",
       "33            183.4274         0.764485  2007-04      5502.822   \n",
       "45            183.7442         0.768456  2008-04      5512.326   \n",
       "57            157.9462         0.743636  2009-04      4738.386   \n",
       "\n",
       "    Corrected Monthly Rate  \n",
       "9                 3507.477  \n",
       "21                3928.002  \n",
       "33                4206.826  \n",
       "45                4235.983  \n",
       "57                3523.632  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Correct the monthly hotel rates based on the hotel occupancy rates\n",
    "se3 = pd.Series(round((hotelrates_df2[\"Monthly Rate\"] * hotelrates_df2[\"Hotel Occupancy\"]),3))\n",
    "hotelrates_df2.insert(loc = 4, column = \"Corrected Monthly Rate\", value = se3.values)\n",
    "\n",
    "hotelrates_df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save dataframe as csv file\n",
    "hotelrates_df2.to_csv(\"Data/hotel_monthlyrate.csv\", encoding = \"utf-8\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Calculate yearly hotel rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2006,\n",
       " 2014,\n",
       " 2011,\n",
       " 2009,\n",
       " 2004,\n",
       " 2010,\n",
       " 2008,\n",
       " 2015,\n",
       " 2013,\n",
       " 2012,\n",
       " 2016,\n",
       " 2017,\n",
       " 2007,\n",
       " 2005,\n",
       " 2018]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert the years_all list into a list of strings\n",
    "years_all_str = set([str(i) for i in years_all]) # unique years\n",
    "years_all_str = list(years_all_str) # pass the set as a list (for list comprehension below)\n",
    "years_all_int = [int(i) for i in years_all_str] # convert to int (for graphing)\n",
    "years_all_int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a function that returns the sum of each year\n",
    "def totals(df,str):\n",
    "    data = df[df[\"Date\"].str.contains(str)]\n",
    "    year_rate = data[\"Corrected Monthly Rate\"].sum()\n",
    "    return year_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "yearly_rates = [round(totals(hotelrates_df2,yr),3) for yr in years_all_str]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Yearly Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006</td>\n",
       "      <td>46488.121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014</td>\n",
       "      <td>79795.282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011</td>\n",
       "      <td>55845.469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009</td>\n",
       "      <td>43366.102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>19596.943</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Yearly Rate\n",
       "0  2006    46488.121\n",
       "1  2014    79795.282\n",
       "2  2011    55845.469\n",
       "3  2009    43366.102\n",
       "4  2004    19596.943"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a new dataframe \n",
    "hotel_year_rates = pd.DataFrame(dict(years_all_int = years_all_int,\n",
    "                                     yearly_rates = yearly_rates))\n",
    "hotel_year_rates = hotel_year_rates.rename(columns = {\"years_all_int\": \"Year\",\n",
    "                                                      \"yearly_rates\": \"Yearly Rate\"})\n",
    "hotel_year_rates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save dataframe as csv file\n",
    "hotel_year_rates.to_csv(\"Data/hotel_yrrate.csv\", encoding = \"utf-8\", index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}