{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "3620d5c0-740e-9c05-f127-cb50dc3bce6c"
      },
      "source": [
        "Expedia - interactive bookings trends "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "47dbbf5d-9c72-4fea-b94c-06ce87ef437b"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "import seaborn as sns\n",
        "%matplotlib inline\n",
        "\n",
        "from bokeh.plotting import figure, output_notebook, show, vplot, ColumnDataSource\n",
        "from bokeh.charts import TimeSeries\n",
        "from bokeh.models import HoverTool, CrosshairTool\n",
        "from bokeh.palettes import brewer\n",
        "import gc\n",
        "import dask.dataframe as dd\n",
        "\n",
        "output_notebook()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "d0254281-26d1-470c-bc38-fcf8c1972425"
      },
      "source": [
        "## This Notebook analyses trends of hotel bookings and clicks\n",
        "\n",
        "we will start with reading the data, leaving just necessary columns, aggregating it to the day level and dropping the original dataframe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "318a6b92-c236-4d4f-8eb8-4827d8d7eabf"
      },
      "outputs": [],
      "source": [
        "train  =  pd.read_csv('../input/train.csv', usecols = ('date_time', 'hotel_cluster','is_booking'), \n",
        "                      parse_dates = ['date_time'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "696def7c-7a2c-42a7-9937-0f9cd67aa641"
      },
      "outputs": [],
      "source": [
        "train['dow'] = train.date_time.dt.weekday\n",
        "train['year'] = train.date_time.dt.year\n",
        "train['month'] = train.date_time.dt.month\n",
        "train['day'] = train.date_time.dt.day"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "68c39457-8d81-48ff-af33-f08d35fc2359"
      },
      "outputs": [],
      "source": [
        "train_agg = train.groupby(['dow','year','month','day', 'hotel_cluster']).agg(['sum', 'count'] )\n",
        "train_agg.columns = ('bookings', 'total')\n",
        "train_agg.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "8974cc24-13ba-4f1a-855f-2bb7fa9f7b6b"
      },
      "outputs": [],
      "source": [
        "train_agg.info()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "8e07b727-90f9-48f5-ba6d-c0e586d359a4"
      },
      "outputs": [],
      "source": [
        "del(train)\n",
        "\n",
        "gc.collect()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "fb4a004a-5ad3-4616-883f-705d52c567fe"
      },
      "source": [
        "## Bookings per day of week"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "15ea3787-bb33-4733-93cf-62261609b5cb"
      },
      "outputs": [],
      "source": [
        "date_agg_1 = train_agg.groupby(level=0).agg(['sum'] )\n",
        "date_agg_1.columns = ('bookings', 'total')\n",
        "date_agg_1.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "bfd24fb9-92cc-4501-b6a9-49232b1d2934"
      },
      "outputs": [],
      "source": [
        "date_agg_1.plot( kind = 'bar', stacked = True )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "42a8a101-796a-41f0-a13d-a0b64f637a00"
      },
      "source": [
        "## Bookings by year"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "2e085dc0-b240-4ae9-bdac-6cb0c6a41973"
      },
      "outputs": [],
      "source": [
        "date_agg_2 = train_agg.groupby(level=1).sum()\n",
        "date_agg_2.columns = ('bookings', 'total')\n",
        "date_agg_2.index.name = 'Year'\n",
        "date_agg_2.plot(kind='bar', stacked='True')"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "56af21ae-5046-4912-a21c-d070590362d2"
      },
      "source": [
        "## Bookings by month"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "a10289c7-4b1e-4b1f-80d7-aea55f76e627"
      },
      "outputs": [],
      "source": [
        "date_agg_3 = train_agg.groupby(level=[1,2]).sum()\n",
        "date_agg_3.columns = ('bookings', 'total')\n",
        "date_agg_3.plot(kind='bar', stacked='True',figsize=(16,10))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "c86221f2-9f52-4a61-bbc9-dc361acb3979"
      },
      "source": [
        "## Interactive booking, click, and percentage of booking trends with Bokeh"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3a37329e-6584-4277-8526-58a05b40be3e"
      },
      "outputs": [],
      "source": [
        "date_agg_4 = train_agg.groupby(level=[1,2,3]).sum()\n",
        "date_agg_4.columns = ('bookings', 'total')\n",
        "date_agg_4.reset_index(inplace=True)\n",
        "date_agg_4['dt'] = pd.to_datetime(date_agg_4.year*10000 + date_agg_4.month*100 + date_agg_4.day\n",
        "                                  , format='%Y%m%d')\n",
        "date_agg_4.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c08ff341-5e91-4bad-80ff-f93159c16faf"
      },
      "outputs": [],
      "source": [
        "def make_plot(vals, title, ylab):\n",
        "    hover = HoverTool(\n",
        "        tooltips=[\n",
        "            (\"Date\", \"@day\"),\n",
        "            (\"Day of week\", \"@dow\"),\n",
        "            (\"clicks\", \"@clicks\"),\n",
        "            (\"bookings\", \"@bookings\"),\n",
        "        ]\n",
        "    )\n",
        "\n",
        "\n",
        "    ch = CrosshairTool(dimensions = ['height'], line_color='red')\n",
        "\n",
        "    src  = ColumnDataSource({'day': date_agg_4.dt.dt.strftime('%Y-%m-%d') ,\n",
        "                             'dow': date_agg_4.dt.dt.weekday.tolist(),\n",
        "                             'clicks': date_agg_4.total - date_agg_4.bookings,\n",
        "                             'bookings': date_agg_4.bookings})\n",
        "\n",
        "    p = figure(x_axis_type = 'datetime',plot_width=800, plot_height=400, tools=[hover, ch, \n",
        "                                                                                'pan,save, wheel_zoom,box_zoom,reset,resize'])\n",
        "    p.title = title\n",
        "    p.xaxis.axis_label = 'Date'\n",
        "    p.yaxis.axis_label = ylab\n",
        "\n",
        "    p.line((date_agg_4['dt']), vals, color='green',  source = src)\n",
        "    \n",
        "    return p"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "317ce834-8614-45ab-95a5-607713335317"
      },
      "outputs": [],
      "source": [
        "p = make_plot(date_agg_4['total'] - date_agg_4['bookings'], 'Expedia daily clicks', 'Clicks')\n",
        "show(p)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d2dd06b6-1638-4eab-b522-42ddc609f0b4"
      },
      "outputs": [],
      "source": [
        "p = make_plot(date_agg_4['bookings'], 'Expedia daily bookings', 'bookings')\n",
        "show(p)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c501f1d8-b338-4be1-9447-8343524770bd"
      },
      "outputs": [],
      "source": [
        "p = make_plot(date_agg_4['bookings'] / date_agg_4['total'] *100, 'Expedia daily bookings %', 'Bookings, %')\n",
        "show(p)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "25ce101c-e459-44cf-8ae7-1074179452fb"
      },
      "source": [
        "## Building interactive charts for hotel clusters, 5 clusters per chart"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "e224303f-03f6-44dd-b06d-220e2216e1f4"
      },
      "outputs": [],
      "source": [
        "pv_agg = train_agg.reset_index()\n",
        "pv_agg['dt'] = pd.to_datetime( pv_agg.year*10000 + pv_agg.month*100 + pv_agg.day\n",
        "                                  , format='%Y%m%d')\n",
        "pv_agg = pv_agg.pivot(index = 'dt', columns = 'hotel_cluster', values = 'bookings')\n",
        "pv_agg.columns = [str(i) for i in pv_agg.columns]\n",
        "pv_agg['dt'] = pv_agg.index\n",
        "pv_agg['dow'] = pv_agg.dt.dt.weekday\n",
        "pv_agg.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "10901dd8-92ec-4ee8-a617-2bd19613db12"
      },
      "outputs": [],
      "source": [
        "def make_hc_plot(df, start, stop):\n",
        "    hover = HoverTool(\n",
        "        tooltips=[\n",
        "            (\"Date\", \"@day\"),\n",
        "            (\"Day of week\", \"@dow\"),\n",
        "            (\"cluster\", \"@cluster\"),\n",
        "            (\"bookings\", \"@bookings\"),\n",
        "        ]\n",
        "    )\n",
        "    \n",
        "    #colors = brewer['RdYlBu'][stop-start]\n",
        "    colors = ['red', 'darkmagenta', 'green', 'darkorange', 'blue']\n",
        "    ch = CrosshairTool(dimensions = ['height'], line_color='red')\n",
        "    p = figure(x_axis_type = 'datetime',plot_width=800, plot_height=400, tools=[hover, ch, 'pan,wheel_zoom,save,box_zoom,reset,resize'])\n",
        "    p.title = 'Expedia bookings for subset of clusters {} to {}'.format(start, stop)\n",
        "    p.xaxis.axis_label = 'Date'\n",
        "    p.yaxis.axis_label = 'Number of bookings'\n",
        "    \n",
        "    for i in range(start, stop):\n",
        "        src  = ColumnDataSource({'day': df.dt.dt.strftime('%Y-%m-%d'), \n",
        "                                 'dow': df.dow.tolist(),\n",
        "                                 'bookings': df[str(i)],\n",
        "                                 'cluster': [i]*df.shape[0]})\n",
        "        \n",
        "        p.line((df['dt']), df[str(i)], color=colors[i-start], legend = str(i), source = src)\n",
        "\n",
        "    return p"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "614438d1-639e-4e14-9e74-d41dcad8ab8a"
      },
      "outputs": [],
      "source": [
        "tslines = []\n",
        "\n",
        "for i in range(0,100,5):\n",
        "    tsline = make_hc_plot(pv_agg, i, i+5)\n",
        "    tslines.append(tsline)\n",
        "    \n",
        "show(vplot(*tslines))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "bfe77020-98b8-468d-b272-42f9732aedef"
      },
      "outputs": [],
      "source": [
        "print('done')"
      ]
    }
  ],
  "metadata": {
    "_change_revision": 0,
    "_is_fork": false,
    "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.0"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}