from pandas import *
from __future__ import division

pandas.set_option('display.height', 500)
pandas.set_option('display.max_rows', 500)
pandas.set_option('display.max_columns', 500)
pandas.set_option('display.width', 500)
pandas.set_option('display.mpl_style', False)
rcParams['figure.figsize'] = 16, 8

xls1 = ExcelFile('CanterburyHotels.xlsx')
hotel_data = xls1.parse('data', parse_dates=[0], index_col=[0], na_values=['..'])

hotel_data.info()

hotel_data.plot(subplots=True)

hotel_data.fillna(method='ffill', inplace=True)

hotel_data.plot(subplots=True)

hotel_data_ma = rolling_mean(hotel_data,12, center=True)
hotel_data_ma['Hotel units'] = hotel_data['Hotel units']

hotel_data_ma.plot(subplots=True)

hotel_data_residuals = hotel_data - hotel_data_ma
hotel_data_sf_1yr = hotel_data_residuals.groupby(lambda x: x.month).mean()
hotel_data_sf_1yr = hotel_data_sf_1yr.add_prefix('SF ')
hotel_data_sf_1yr

hotel_data['Month'] = hotel_data.index.month

hotel_data_join = merge(hotel_data, hotel_data_sf_1yr, left_on='Month', right_index=True)
hotel_data_sf = hotel_data_join.drop(['Hotel units', 'Guest nights', 'Domestic guest nights', 
    'International guest nights', 'Occupancy rate', 'Month'], axis=1)
hotel_data_sf.columns = ['Hotel units', 'Guest nights', 'Domestic guest nights', 'International guest nights', 'Occupancy rate']
hotel_data = hotel_data.drop('Month', axis=1)

hotel_data_sa = hotel_data.add_prefix('SA ')
hotel_data_sa = hotel_data - hotel_data_sf
hotel_data_sa.plot(subplots=True)

hotel_data_ma = rolling_mean(hotel_data, 12, center=True)
hotel_data_ss = hotel_data / hotel_data_ma
hotel_data_ss_mo = hotel_data_ss.groupby(lambda x: x.month)
hotel_data_ss_mo_mean = hotel_data_ss_mo.mean()
hotel_data_si_1yr = hotel_data_ss_mo_mean * ( 12.0 / hotel_data_ss_mo_mean.sum() )
hotel_data_si_1yr = hotel_data_si_1yr.add_prefix('SI ')

hotel_data['Month'] = hotel_data.index.month

hotel_data_join = merge(hotel_data, hotel_data_si_1yr, left_on='Month', right_index=True)
hotel_data_si = hotel_data_join.drop(['Hotel units', 'Guest nights', 'Domestic guest nights', 
    'International guest nights', 'Occupancy rate', 'Month'], axis=1)
hotel_data_si.columns = ['Hotel units', 'Guest nights', 'Domestic guest nights', 'International guest nights', 'Occupancy rate']
hotel_data = hotel_data.drop('Month', axis=1)
hotel_data_si

hotel_data_sa2 = hotel_data / hotel_data_si
hotel_data_sa2.plot(subplots=True)

plot(hotel_data.index, hotel_data['Hotel units'], label='Raw')
plot(hotel_data.index, hotel_data_ma['Hotel units'], label='Centered Moving Average')
plot(hotel_data.index, hotel_data_sa['Hotel units'], label='Adjusted (Additive)')
plot(hotel_data.index, hotel_data_sa2['Hotel units'], label='Adjusted (Multiplicative)')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [15000,7000], color='k', linestyle='--')

ylabel('Hotel units')

legend(loc='upper left')

annotate('Canterbury \nearthquake', xy=(nz_eq, 8500),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

plot(hotel_data.index, hotel_data['Guest nights'], label='Raw')
plot(hotel_data.index, hotel_data_ma['Guest nights'], label='Centered Moving Average')
plot(hotel_data.index, hotel_data_sa['Guest nights'], label='Adjusted (Additive)')
plot(hotel_data.index, hotel_data_sa2['Guest nights'], label='Adjusted (Multiplicative)')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [700,100], color='k', linestyle='--')

ylabel('Guest nights')
legend(loc='upper left')

annotate('Canterbury \nearthquake', xy=(nz_eq, 150),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

plot(hotel_data.index, hotel_data['Domestic guest nights'], label='Raw')
plot(hotel_data.index, hotel_data_ma['Domestic guest nights'], label='Centered Moving Average')
plot(hotel_data.index, hotel_data_sa['Domestic guest nights'], label='Adjusted (Additive)')
plot(hotel_data.index, hotel_data_sa2['Domestic guest nights'], label='Adjusted (Multiplicative)')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [350,100], color='k', linestyle='--')

ylabel('Domestic guest nights')
legend(loc='upper left')

annotate('Canterbury \nearthquake', xy=(nz_eq, 125),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

plot(hotel_data.index, hotel_data['International guest nights'], label='Raw')
plot(hotel_data.index, hotel_data_ma['International guest nights'], label='Centered Moving Average')
plot(hotel_data.index, hotel_data_sa['International guest nights'], label='Adjusted (Additive)')
plot(hotel_data.index, hotel_data_sa2['International guest nights'], label='Adjusted (Multiplicative)')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [350,50], color='k', linestyle='--')

ylabel('International guest nights')
legend(loc='upper left')

annotate('Canterbury \nearthquake', xy=(nz_eq, 75),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

plot(hotel_data.index, hotel_data['Occupancy rate'], label='Raw')
plot(hotel_data.index, hotel_data_ma['Occupancy rate'], label='Centered Moving Average')
plot(hotel_data.index, hotel_data_sa['Occupancy rate'], label='Adjusted (Additive)')
plot(hotel_data.index, hotel_data_sa2['Occupancy rate'], label='Adjusted (Multiplicative)')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [80,20], color='k', linestyle='--')

ylabel('Occupancy rate (%)')
legend(loc='upper left')

annotate('Canterbury \nearthquake', xy=(nz_eq, 25),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

hotel_available = (hotel_data['Hotel units'] - hotel_data['Hotel units'] * ( hotel_data_sa['Occupancy rate']  / 100.0 ))
hotel_nights_per_available = hotel_data_sa['Guest nights'] / hotel_available
hotel_nights_per_available_dom = hotel_data_sa['Domestic guest nights'] / hotel_available
hotel_nights_per_available_int = hotel_data_sa['International guest nights'] / hotel_available

plot(hotel_data.index, hotel_available)

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [9000,3000], color='k', linestyle='--')

ylabel('Available units')

annotate('Canterbury \nearthquake', xy=(nz_eq, 3500),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

plot(hotel_data.index, hotel_nights_per_available, label='All travelers')
plot(hotel_data.index, hotel_nights_per_available_dom, label='Domestic travelers')
plot(hotel_data.index, hotel_nights_per_available_int, label='International travelers')

plot(hotel_data.index, rolling_mean(hotel_nights_per_available,12,center=True), label='All travelers CMA')
plot(hotel_data.index, rolling_mean(hotel_nights_per_available_dom,12,center=True), label='Domestic travelers CMA')
plot(hotel_data.index, rolling_mean(hotel_nights_per_available_int,12,center=True), label='International travelers CMA')

nz_eq = datetime(2011, 2, 1)
plot([nz_eq, nz_eq], [.11,.0], color='k', linestyle='--')

ylabel('Guest nights per available units')
legend()

annotate('Canterbury \nearthquake', xy=(nz_eq, .01),  xycoords='data',
    xytext=(-65, 0), textcoords='offset points',
    arrowprops=dict(arrowstyle="->"))

annotate('Created by Scott Miles', color='gray', xy=(10, 10),  xycoords='axes points',
    xytext=(0, 0), textcoords='offset points',
    arrowprops=None)

bob = (100 - hotel_data['Occupancy rate'])/100.0
plot(hotel_data.index, rolling_mean(hotel_available / hotel_data['Hotel units'],12,center=True))
plot(hotel_data.index, rolling_mean(bob, 12, center=True))