{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ebbaf4eb-2891-4e64-b48d-330e621c14b1"
      },
      "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": "f1bf5c4a-1a5f-485c-acdb-de1774e1d0af"
      },
      "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": "78402833-5304-4679-b625-0ca959cda855"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "650687b3-8f7b-4508-a567-592112768afd"
      },
      "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": "79c32d2c-3289-48e2-9f05-72bf5f4f31cf"
      },
      "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": "6b02d3c0-4f51-4f37-a86c-a46a0ef7a58e"
      },
      "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": "1d2e1c48-6f3e-4724-a634-db6c80f0e54f"
      },
      "outputs": [],
      "source": [
        "train_agg.info()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "3e92ce9d-3559-4d92-b881-d19406653ecd"
      },
      "outputs": [],
      "source": [
        "del(train)\n",
        "\n",
        "gc.collect()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "92a82e63-e237-4935-9c2f-2d4631baa504"
      },
      "source": [
        "## Bookings per day of week"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "ce120c2e-4f00-47f0-bd1d-90c7e9631a4e"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "397c1286-edc7-4594-a5f3-bc5d2a079955"
      },
      "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": "b95c86b9-b53d-4808-9953-9e40212b5549"
      },
      "outputs": [],
      "source": [
        "date_agg_1.plot( kind = 'bar', stacked = True )"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "f9a1a8d8-d12d-4587-b54d-c4d13cf3dc6b"
      },
      "source": [
        "## Bookings by year"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c121c18d-0e60-4cdb-9d6c-f382f74e9c69"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "4cdff028-e6a4-48b1-93cb-3bee070f7dfb"
      },
      "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": "99503740-f062-4eea-a1b1-a716e3378469"
      },
      "source": [
        "## Bookings by month"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "99c8dcfa-d0b0-4779-a6e6-4a6624d87423"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "55571ec4-392f-42fe-842f-51178c74a0a2"
      },
      "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": "f1b6a3c6-2aa5-4766-8c96-6e9bb86bba25"
      },
      "source": [
        "## Interactive booking, click, and percentage of booking trends with Bokeh"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "1828f3bd-75d4-4464-81c6-ba0a5f3e379f"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c6a8b413-980f-4e5f-8f7e-cc49e5a0bbd2"
      },
      "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": "d759460d-cb04-4cb4-8238-99bf5e37fec1"
      },
      "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": "5c3b74de-dc63-491c-a483-a0103128c270"
      },
      "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": "7a942e90-c62e-483e-8ad1-4870bc4aeaa5"
      },
      "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": "8d7b8cc6-c822-46b5-972c-fa42b8297f19"
      },
      "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": "abde24cd-cdc8-4fd4-90d7-b24d66ab4255"
      },
      "source": [
        "## Building interactive charts for hotel clusters, 5 clusters per chart"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d7719ceb-0b19-4e3c-8402-caf38053049e"
      },
      "outputs": [],
      "source": ""
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "01fc580e-0e9c-42ff-9143-7e50dc63fc8d"
      },
      "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": "81da1453-382b-406d-9dc0-19dab3b9f745"
      },
      "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": "183ffd22-c877-48cb-867b-c26858424fbc"
      },
      "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": "7c66b8ca-cf8c-44ac-a667-de812c2e49ea"
      },
      "outputs": [],
      "source": [
        "print('done')"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "5baf8fde-f4c5-4440-8cac-3e061089af37"
      },
      "outputs": [],
      "source": ""
    }
  ],
  "metadata": {
    "_change_revision": 0,
    "_is_fork": false,
    "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": "3.3.2"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}