DateTimeDimensionGenerator.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "from pandas import DataFrame,Series\n",
    "import numpy as np\n",
    "import re\n",
    "import datetime\n",
    "from datetime import timedelta\n",
    "from dateutil import relativedelta\n",
    "import calendar\n",
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configure your PostgreSQL database info here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Host Address: localhost\n"
     ]
    }
   ],
   "source": [
    "host = raw_input(\"Host Address: \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db_name = \"pokemon_go\"\n",
    "username = \"pokemon_go_role\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "password = getpass.getpass()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create the Date Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "years = xrange(2016, 2025)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "start_date = str(datetime.date(years[0], 1, 1))\n",
    "end_date = str(datetime.date(years[-1], 12, 31))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2016-01-01', '2016-01-02', '2016-01-03', '2016-01-04',\n",
       "               '2016-01-05', '2016-01-06', '2016-01-07', '2016-01-08',\n",
       "               '2016-01-09', '2016-01-10',\n",
       "               ...\n",
       "               '2024-12-22', '2024-12-23', '2024-12-24', '2024-12-25',\n",
       "               '2024-12-26', '2024-12-27', '2024-12-28', '2024-12-29',\n",
       "               '2024-12-30', '2024-12-31'],\n",
       "              dtype='datetime64[ns]', length=3288, freq='D')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_dates = pd.date_range(start_date, end_date, freq='D')\n",
    "all_dates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## All the date functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a datetime timestamp, produce a datekey\n",
    "def datetimeToDateKey(date):\n",
    "    date_str = str(date)\n",
    "    date_str = date_str.split(\" \")[0]\n",
    "    date_str = re.sub('-', '', date_str)\n",
    "    return date_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a datetime timestamp, convert it to a string of just the date in YYYY-mm-dd format\n",
    "def fullDate(date):\n",
    "    date_str = str(date)\n",
    "    date_str = date_str.split(\" \")[0]\n",
    "    return date_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Returns the weekday as a number\n",
    "def weekdayNum(date):\n",
    "    return date.isoweekday()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the weekday as a string in its full-length form\n",
    "def weekdayStr(date):\n",
    "    return date.strftime(\"%A\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the weekday as a string in its abbreviated form\n",
    "def weekdayStrAbv(date):\n",
    "    return date.strftime(\"%a\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the day of the month\n",
    "def dayNumMonth(date):\n",
    "    return date.day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Numbers each day, constantly increasing from the first day\n",
    "def dayNumOverall(date):\n",
    "    day_one = all_dates[0]\n",
    "    date_diff = date - day_one\n",
    "    return date_diff.days + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the day of the month\n",
    "def isWeekday(date):\n",
    "    if date.isoweekday() in range(1,6):\n",
    "        return \"Weekday\"\n",
    "    else:\n",
    "        return \"Weekend\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the day of the month\n",
    "def weekNum(date):\n",
    "    return date.isocalendar()[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def weekBeginDate(date):\n",
    "    dow = date.isoweekday()\n",
    "    week_start = date - timedelta(days=(dow - 1))\n",
    "    return week_start"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def weekBeginDateKey(date):\n",
    "    week_begin_date = weekBeginDate(date)\n",
    "    date_key = datetimeToDateKey(week_begin_date)\n",
    "    return int(date_key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Numbers each day, constantly increasing from the first day\n",
    "def weekNumOverall(date):\n",
    "    # Find the date that the first week in the entire data set starts\n",
    "    first_day = all_dates[0]\n",
    "    first_week_start = weekBeginDate(first_day)\n",
    "    \n",
    "    # Find the date that starts the week of the current date\n",
    "    curr_week_start = weekBeginDate(date)\n",
    "    \n",
    "    # Get the difference and find out how many weeks have passed\n",
    "    date_diff = curr_week_start - first_week_start\n",
    "    week_number = int(date_diff.days / 7.0 + 1.0)\n",
    "    return week_number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Returns the weekday as a number\n",
    "def monthNum(date):\n",
    "    return date.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Returns the weekday as a number\n",
    "def monthNumOverall(date):\n",
    "    start_date = all_dates[0]\n",
    "    rel_date = relativedelta.relativedelta(date, start_date)\n",
    "    month_diff = rel_date.years * 12 + rel_date.months\n",
    "    return month_diff + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the month as a string in its full-length form\n",
    "def monthStr(date):\n",
    "    return date.strftime(\"%B\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the month as a string in its abbreviated form\n",
    "def monthStrAbv(date):\n",
    "    return date.strftime(\"%b\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the quarter in the year\n",
    "def quarter(date):\n",
    "    month = date.month\n",
    "    quarter = month / 4 + 1\n",
    "    return quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the year as a string\n",
    "def year(date):\n",
    "    return date.strftime(\"%Y\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Returns the year and month as a concatenated string\n",
    "def yearmo(date):\n",
    "    year = date.strftime(\"%Y\")\n",
    "    month = date.strftime(\"%m\")\n",
    "    return year + month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Returns whether or not the date is the last day of the month\n",
    "def isMonthEnd(date):\n",
    "    year = date.year\n",
    "    month = date.month\n",
    "    \n",
    "    month_end = calendar.monthrange(year, month)[1]\n",
    "    \n",
    "    if (month_end == date.day):\n",
    "        return \"Month End\"\n",
    "    else:\n",
    "        return \"Not Month End\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Use the date functions to make a dateframe\n",
    "\n",
    "# Dates\n",
    "date_dim = DataFrame(all_dates, columns=[\"full_date\"])\n",
    "date_dim[\"date_key\"] = date_dim[\"full_date\"].map(datetimeToDateKey)\n",
    "date_dim = date_dim[['date_key', 'full_date']] # Reorder\n",
    "\n",
    "# Days of Week\n",
    "date_dim[\"day_of_week\"] = date_dim[\"full_date\"].map(weekdayNum)\n",
    "date_dim[\"day_of_week_name\"] = date_dim[\"full_date\"].map(weekdayStr)\n",
    "date_dim[\"day_of_week_name_abbrev\"] = date_dim[\"full_date\"].map(weekdayStrAbv)\n",
    "\n",
    "date_dim[\"day_of_month\"] = date_dim[\"full_date\"].map(dayNumMonth)\n",
    "date_dim[\"day_number_overall\"] = date_dim[\"full_date\"].map(dayNumOverall)\n",
    "date_dim[\"day_number_overall\"] = date_dim[\"full_date\"].map(dayNumOverall)\n",
    "\n",
    "date_dim[\"weekday_flag\"] = date_dim[\"full_date\"].map(isWeekday)\n",
    "date_dim[\"week_number\"] = date_dim[\"full_date\"].map(weekNum)\n",
    "date_dim[\"week_number_overall\"] = date_dim[\"full_date\"].map(weekNumOverall)\n",
    "\n",
    "date_dim[\"week_begin_date\"] = date_dim[\"full_date\"].map(weekBeginDate)\n",
    "date_dim[\"week_begin_date_key\"] = date_dim[\"full_date\"].map(weekBeginDateKey)\n",
    "\n",
    "date_dim[\"month_number\"] = date_dim[\"full_date\"].map(monthNum)\n",
    "date_dim[\"month_number_overall\"] = date_dim[\"full_date\"].map(monthNumOverall)\n",
    "date_dim[\"month\"] = date_dim[\"full_date\"].map(monthStr)\n",
    "date_dim[\"month_abbrev\"] = date_dim[\"full_date\"].map(monthStrAbv)\n",
    "\n",
    "date_dim[\"quarter\"] = date_dim[\"full_date\"].map(quarter)\n",
    "\n",
    "date_dim[\"year\"] = date_dim[\"full_date\"].map(year)\n",
    "date_dim[\"year_month\"] = date_dim[\"full_date\"].map(yearmo)\n",
    "\n",
    "date_dim[\"month_end_flag\"] = date_dim[\"full_date\"].map(isMonthEnd)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Create the Time Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import time\n",
    "import math"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 1440 minutes in a day\n",
    "minutes = xrange(0,1440)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 12-hour time label\n",
    "def time_label_12(min_num):\n",
    "    hours, minutes = divmod(int(min_num), 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%I:%M %p'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 24-hour time label\n",
    "def time_label_24(min_num):\n",
    "    hours, minutes = divmod(int(min_num), 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 15 minute interval it occures in\n",
    "def time_interval_15_min(min_num):\n",
    "    return str(int(math.floor(min_num / 15.0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 30 minute interval it occures in\n",
    "def time_interval_30_min(min_num):\n",
    "    return str(int(math.floor(min_num / 30.0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 60 minute interval it occures in\n",
    "def time_interval_60_min(min_num):\n",
    "    return str(int(math.floor(min_num / 60.0)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 12-hour time label \n",
    "# with only hours (this takes up less space and is useful in some cases)\n",
    "def label_hh(min_num):\n",
    "    hours, minutes = divmod(int(min_num), 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%I %p'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 24-hour time label with just hours\n",
    "def label_hh24(min_num):\n",
    "    hours, minutes = divmod(int(min_num), 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%H'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 15 minute interval label for a 24-hour clock\n",
    "def label_15_min_24(min_num):\n",
    "    interval_num = time_interval_15_min(min_num)\n",
    "    int_min_num = int(interval_num) * 15\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 30 minute interval label for a 24-hour clock\n",
    "def label_30_min_24(min_num):\n",
    "    interval_num = time_interval_30_min(min_num)\n",
    "    int_min_num = int(interval_num) * 30\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 60 minute interval label for a 24-hour clock\n",
    "def label_60_min_24(min_num):\n",
    "    interval_num = time_interval_60_min(min_num)\n",
    "    int_min_num = int(interval_num) * 60\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 15 minute interval label for a 12-hour clock\n",
    "def label_15_min_12(min_num):\n",
    "    interval_num = time_interval_15_min(min_num)\n",
    "    int_min_num = int(interval_num) * 15\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%I:%M %p'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute number, return the 30 minute interval label for a 12-hour clock\n",
    "def label_30_min_12(min_num):\n",
    "    interval_num = time_interval_30_min(min_num)\n",
    "    int_min_num = int(interval_num) * 30\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%I:%M %p'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a miute number, return the 60 minute interval label for a 12-hour clock\n",
    "def label_60_min_12(min_num):\n",
    "    interval_num = time_interval_60_min(min_num)\n",
    "    int_min_num = int(interval_num) * 60\n",
    "    hours, minutes = divmod(int_min_num, 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(time.strftime(timestamp, '%I:%M %p'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Given a minute, return just the minute portion\n",
    "def minute_after_hour(min_num):\n",
    "    hours, minutes = divmod(int(min_num), 60)\n",
    "    timestamp = time(hour=hours, minute=minutes)\n",
    "    return str(int(time.strftime(timestamp, '%M')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "minute_after_hour(61)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "time_dim = DataFrame(Series(minutes), columns=[\"time_key\"])\n",
    "time_dim[\"time_label_24\"] = time_dim[\"time_key\"].map(time_label_24)\n",
    "time_dim[\"time_label_12\"] = time_dim[\"time_key\"].map(time_label_12)\n",
    "\n",
    "time_dim[\"time_interval_15min\"] = time_dim[\"time_key\"].map(time_interval_15_min)\n",
    "time_dim[\"time_interval_30min\"] = time_dim[\"time_key\"].map(time_interval_30_min)\n",
    "time_dim[\"time_interval_60min\"] = time_dim[\"time_key\"].map(time_interval_60_min)\n",
    "\n",
    "time_dim[\"label_hh\"] = time_dim[\"time_key\"].map(label_hh)\n",
    "time_dim[\"label_hh24\"] = time_dim[\"time_key\"].map(label_hh24)\n",
    "\n",
    "time_dim[\"label_15min_24\"] = time_dim[\"time_key\"].map(label_15_min_24)\n",
    "time_dim[\"label_30min_24\"] = time_dim[\"time_key\"].map(label_30_min_24)\n",
    "time_dim[\"label_60min_24\"] = time_dim[\"time_key\"].map(label_60_min_24)\n",
    "\n",
    "time_dim[\"label_15min_12\"] = time_dim[\"time_key\"].map(label_15_min_12)\n",
    "time_dim[\"label_30min_12\"] = time_dim[\"time_key\"].map(label_30_min_12)\n",
    "time_dim[\"label_60min_12\"] = time_dim[\"time_key\"].map(label_60_min_12)\n",
    "\n",
    "time_dim[\"minute_after_hour\"] = time_dim[\"time_key\"].map(minute_after_hour)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Write the data to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'date_dim' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-71-243793b0a336>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdate_dim\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhead\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m: name 'date_dim' is not defined"
     ]
    }
   ],
   "source": [
    "date_dim.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_key</th>\n",
       "      <th>time_label_24</th>\n",
       "      <th>time_label_12</th>\n",
       "      <th>time_interval_15min</th>\n",
       "      <th>time_interval_30min</th>\n",
       "      <th>time_interval_60min</th>\n",
       "      <th>label_hh</th>\n",
       "      <th>label_hh24</th>\n",
       "      <th>label_15min_24</th>\n",
       "      <th>label_30min_24</th>\n",
       "      <th>label_60min_24</th>\n",
       "      <th>label_15min_12</th>\n",
       "      <th>label_30min_12</th>\n",
       "      <th>label_60min_12</th>\n",
       "      <th>minute_after_hour</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>00:01</td>\n",
       "      <td>12:01 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>00:02</td>\n",
       "      <td>12:02 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>00:03</td>\n",
       "      <td>12:03 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>00:04</td>\n",
       "      <td>12:04 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>00:05</td>\n",
       "      <td>12:05 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>00:06</td>\n",
       "      <td>12:06 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>00:07</td>\n",
       "      <td>12:07 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>00:08</td>\n",
       "      <td>12:08 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>00:09</td>\n",
       "      <td>12:09 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>00:10</td>\n",
       "      <td>12:10 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>00:11</td>\n",
       "      <td>12:11 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>00:12</td>\n",
       "      <td>12:12 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>00:13</td>\n",
       "      <td>12:13 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>00:14</td>\n",
       "      <td>12:14 AM</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>00:15</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>00:16</td>\n",
       "      <td>12:16 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>00:17</td>\n",
       "      <td>12:17 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>00:18</td>\n",
       "      <td>12:18 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>00:19</td>\n",
       "      <td>12:19 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>00:20</td>\n",
       "      <td>12:20 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>00:21</td>\n",
       "      <td>12:21 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>00:22</td>\n",
       "      <td>12:22 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>00:23</td>\n",
       "      <td>12:23 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>00:24</td>\n",
       "      <td>12:24 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>00:25</td>\n",
       "      <td>12:25 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>00:26</td>\n",
       "      <td>12:26 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>00:27</td>\n",
       "      <td>12:27 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>00:28</td>\n",
       "      <td>12:28 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>00:29</td>\n",
       "      <td>12:29 AM</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12 AM</td>\n",
       "      <td>00</td>\n",
       "      <td>00:15</td>\n",
       "      <td>00:00</td>\n",
       "      <td>00:00</td>\n",
       "      <td>12:15 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>12:00 AM</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>70</td>\n",
       "      <td>01:10</td>\n",
       "      <td>01:10 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>71</td>\n",
       "      <td>01:11</td>\n",
       "      <td>01:11 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>72</td>\n",
       "      <td>01:12</td>\n",
       "      <td>01:12 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>73</td>\n",
       "      <td>01:13</td>\n",
       "      <td>01:13 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>74</td>\n",
       "      <td>01:14</td>\n",
       "      <td>01:14 AM</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>75</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>76</td>\n",
       "      <td>01:16</td>\n",
       "      <td>01:16 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>77</td>\n",
       "      <td>01:17</td>\n",
       "      <td>01:17 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>78</td>\n",
       "      <td>01:18</td>\n",
       "      <td>01:18 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>79</td>\n",
       "      <td>01:19</td>\n",
       "      <td>01:19 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>80</td>\n",
       "      <td>01:20</td>\n",
       "      <td>01:20 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>81</td>\n",
       "      <td>01:21</td>\n",
       "      <td>01:21 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>82</td>\n",
       "      <td>01:22</td>\n",
       "      <td>01:22 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>83</td>\n",
       "      <td>01:23</td>\n",
       "      <td>01:23 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>84</td>\n",
       "      <td>01:24</td>\n",
       "      <td>01:24 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>85</td>\n",
       "      <td>01:25</td>\n",
       "      <td>01:25 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>86</td>\n",
       "      <td>01:26</td>\n",
       "      <td>01:26 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>87</td>\n",
       "      <td>01:27</td>\n",
       "      <td>01:27 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>88</td>\n",
       "      <td>01:28</td>\n",
       "      <td>01:28 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>89</td>\n",
       "      <td>01:29</td>\n",
       "      <td>01:29 AM</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:15</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:15 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>90</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>91</td>\n",
       "      <td>01:31</td>\n",
       "      <td>01:31 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>92</td>\n",
       "      <td>01:32</td>\n",
       "      <td>01:32 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>93</td>\n",
       "      <td>01:33</td>\n",
       "      <td>01:33 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>94</td>\n",
       "      <td>01:34</td>\n",
       "      <td>01:34 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>95</td>\n",
       "      <td>01:35</td>\n",
       "      <td>01:35 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>96</td>\n",
       "      <td>01:36</td>\n",
       "      <td>01:36 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>97</td>\n",
       "      <td>01:37</td>\n",
       "      <td>01:37 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>98</td>\n",
       "      <td>01:38</td>\n",
       "      <td>01:38 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>99</td>\n",
       "      <td>01:39</td>\n",
       "      <td>01:39 AM</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>01 AM</td>\n",
       "      <td>01</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:30</td>\n",
       "      <td>01:00</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:30 AM</td>\n",
       "      <td>01:00 AM</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    time_key time_label_24 time_label_12  time_interval_15min  \\\n",
       "0          0         00:00      12:00 AM                    0   \n",
       "1          1         00:01      12:01 AM                    0   \n",
       "2          2         00:02      12:02 AM                    0   \n",
       "3          3         00:03      12:03 AM                    0   \n",
       "4          4         00:04      12:04 AM                    0   \n",
       "5          5         00:05      12:05 AM                    0   \n",
       "6          6         00:06      12:06 AM                    0   \n",
       "7          7         00:07      12:07 AM                    0   \n",
       "8          8         00:08      12:08 AM                    0   \n",
       "9          9         00:09      12:09 AM                    0   \n",
       "10        10         00:10      12:10 AM                    0   \n",
       "11        11         00:11      12:11 AM                    0   \n",
       "12        12         00:12      12:12 AM                    0   \n",
       "13        13         00:13      12:13 AM                    0   \n",
       "14        14         00:14      12:14 AM                    0   \n",
       "15        15         00:15      12:15 AM                    1   \n",
       "16        16         00:16      12:16 AM                    1   \n",
       "17        17         00:17      12:17 AM                    1   \n",
       "18        18         00:18      12:18 AM                    1   \n",
       "19        19         00:19      12:19 AM                    1   \n",
       "20        20         00:20      12:20 AM                    1   \n",
       "21        21         00:21      12:21 AM                    1   \n",
       "22        22         00:22      12:22 AM                    1   \n",
       "23        23         00:23      12:23 AM                    1   \n",
       "24        24         00:24      12:24 AM                    1   \n",
       "25        25         00:25      12:25 AM                    1   \n",
       "26        26         00:26      12:26 AM                    1   \n",
       "27        27         00:27      12:27 AM                    1   \n",
       "28        28         00:28      12:28 AM                    1   \n",
       "29        29         00:29      12:29 AM                    1   \n",
       "..       ...           ...           ...                  ...   \n",
       "70        70         01:10      01:10 AM                    4   \n",
       "71        71         01:11      01:11 AM                    4   \n",
       "72        72         01:12      01:12 AM                    4   \n",
       "73        73         01:13      01:13 AM                    4   \n",
       "74        74         01:14      01:14 AM                    4   \n",
       "75        75         01:15      01:15 AM                    5   \n",
       "76        76         01:16      01:16 AM                    5   \n",
       "77        77         01:17      01:17 AM                    5   \n",
       "78        78         01:18      01:18 AM                    5   \n",
       "79        79         01:19      01:19 AM                    5   \n",
       "80        80         01:20      01:20 AM                    5   \n",
       "81        81         01:21      01:21 AM                    5   \n",
       "82        82         01:22      01:22 AM                    5   \n",
       "83        83         01:23      01:23 AM                    5   \n",
       "84        84         01:24      01:24 AM                    5   \n",
       "85        85         01:25      01:25 AM                    5   \n",
       "86        86         01:26      01:26 AM                    5   \n",
       "87        87         01:27      01:27 AM                    5   \n",
       "88        88         01:28      01:28 AM                    5   \n",
       "89        89         01:29      01:29 AM                    5   \n",
       "90        90         01:30      01:30 AM                    6   \n",
       "91        91         01:31      01:31 AM                    6   \n",
       "92        92         01:32      01:32 AM                    6   \n",
       "93        93         01:33      01:33 AM                    6   \n",
       "94        94         01:34      01:34 AM                    6   \n",
       "95        95         01:35      01:35 AM                    6   \n",
       "96        96         01:36      01:36 AM                    6   \n",
       "97        97         01:37      01:37 AM                    6   \n",
       "98        98         01:38      01:38 AM                    6   \n",
       "99        99         01:39      01:39 AM                    6   \n",
       "\n",
       "    time_interval_30min  time_interval_60min label_hh label_hh24  \\\n",
       "0                     0                    0    12 AM         00   \n",
       "1                     0                    0    12 AM         00   \n",
       "2                     0                    0    12 AM         00   \n",
       "3                     0                    0    12 AM         00   \n",
       "4                     0                    0    12 AM         00   \n",
       "5                     0                    0    12 AM         00   \n",
       "6                     0                    0    12 AM         00   \n",
       "7                     0                    0    12 AM         00   \n",
       "8                     0                    0    12 AM         00   \n",
       "9                     0                    0    12 AM         00   \n",
       "10                    0                    0    12 AM         00   \n",
       "11                    0                    0    12 AM         00   \n",
       "12                    0                    0    12 AM         00   \n",
       "13                    0                    0    12 AM         00   \n",
       "14                    0                    0    12 AM         00   \n",
       "15                    0                    0    12 AM         00   \n",
       "16                    0                    0    12 AM         00   \n",
       "17                    0                    0    12 AM         00   \n",
       "18                    0                    0    12 AM         00   \n",
       "19                    0                    0    12 AM         00   \n",
       "20                    0                    0    12 AM         00   \n",
       "21                    0                    0    12 AM         00   \n",
       "22                    0                    0    12 AM         00   \n",
       "23                    0                    0    12 AM         00   \n",
       "24                    0                    0    12 AM         00   \n",
       "25                    0                    0    12 AM         00   \n",
       "26                    0                    0    12 AM         00   \n",
       "27                    0                    0    12 AM         00   \n",
       "28                    0                    0    12 AM         00   \n",
       "29                    0                    0    12 AM         00   \n",
       "..                  ...                  ...      ...        ...   \n",
       "70                    2                    1    01 AM         01   \n",
       "71                    2                    1    01 AM         01   \n",
       "72                    2                    1    01 AM         01   \n",
       "73                    2                    1    01 AM         01   \n",
       "74                    2                    1    01 AM         01   \n",
       "75                    2                    1    01 AM         01   \n",
       "76                    2                    1    01 AM         01   \n",
       "77                    2                    1    01 AM         01   \n",
       "78                    2                    1    01 AM         01   \n",
       "79                    2                    1    01 AM         01   \n",
       "80                    2                    1    01 AM         01   \n",
       "81                    2                    1    01 AM         01   \n",
       "82                    2                    1    01 AM         01   \n",
       "83                    2                    1    01 AM         01   \n",
       "84                    2                    1    01 AM         01   \n",
       "85                    2                    1    01 AM         01   \n",
       "86                    2                    1    01 AM         01   \n",
       "87                    2                    1    01 AM         01   \n",
       "88                    2                    1    01 AM         01   \n",
       "89                    2                    1    01 AM         01   \n",
       "90                    3                    1    01 AM         01   \n",
       "91                    3                    1    01 AM         01   \n",
       "92                    3                    1    01 AM         01   \n",
       "93                    3                    1    01 AM         01   \n",
       "94                    3                    1    01 AM         01   \n",
       "95                    3                    1    01 AM         01   \n",
       "96                    3                    1    01 AM         01   \n",
       "97                    3                    1    01 AM         01   \n",
       "98                    3                    1    01 AM         01   \n",
       "99                    3                    1    01 AM         01   \n",
       "\n",
       "   label_15min_24 label_30min_24 label_60min_24 label_15min_12 label_30min_12  \\\n",
       "0           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "1           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "2           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "3           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "4           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "5           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "6           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "7           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "8           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "9           00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "10          00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "11          00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "12          00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "13          00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "14          00:00          00:00          00:00       12:00 AM       12:00 AM   \n",
       "15          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "16          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "17          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "18          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "19          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "20          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "21          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "22          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "23          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "24          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "25          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "26          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "27          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "28          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "29          00:15          00:00          00:00       12:15 AM       12:00 AM   \n",
       "..            ...            ...            ...            ...            ...   \n",
       "70          01:00          01:00          01:00       01:00 AM       01:00 AM   \n",
       "71          01:00          01:00          01:00       01:00 AM       01:00 AM   \n",
       "72          01:00          01:00          01:00       01:00 AM       01:00 AM   \n",
       "73          01:00          01:00          01:00       01:00 AM       01:00 AM   \n",
       "74          01:00          01:00          01:00       01:00 AM       01:00 AM   \n",
       "75          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "76          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "77          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "78          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "79          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "80          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "81          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "82          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "83          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "84          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "85          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "86          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "87          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "88          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "89          01:15          01:00          01:00       01:15 AM       01:00 AM   \n",
       "90          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "91          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "92          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "93          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "94          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "95          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "96          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "97          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "98          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "99          01:30          01:30          01:00       01:30 AM       01:30 AM   \n",
       "\n",
       "   label_60min_12 minute_after_hour  \n",
       "0        12:00 AM                 0  \n",
       "1        12:00 AM                 1  \n",
       "2        12:00 AM                 2  \n",
       "3        12:00 AM                 3  \n",
       "4        12:00 AM                 4  \n",
       "5        12:00 AM                 5  \n",
       "6        12:00 AM                 6  \n",
       "7        12:00 AM                 7  \n",
       "8        12:00 AM                 8  \n",
       "9        12:00 AM                 9  \n",
       "10       12:00 AM                10  \n",
       "11       12:00 AM                11  \n",
       "12       12:00 AM                12  \n",
       "13       12:00 AM                13  \n",
       "14       12:00 AM                14  \n",
       "15       12:00 AM                15  \n",
       "16       12:00 AM                16  \n",
       "17       12:00 AM                17  \n",
       "18       12:00 AM                18  \n",
       "19       12:00 AM                19  \n",
       "20       12:00 AM                20  \n",
       "21       12:00 AM                21  \n",
       "22       12:00 AM                22  \n",
       "23       12:00 AM                23  \n",
       "24       12:00 AM                24  \n",
       "25       12:00 AM                25  \n",
       "26       12:00 AM                26  \n",
       "27       12:00 AM                27  \n",
       "28       12:00 AM                28  \n",
       "29       12:00 AM                29  \n",
       "..            ...               ...  \n",
       "70       01:00 AM                10  \n",
       "71       01:00 AM                11  \n",
       "72       01:00 AM                12  \n",
       "73       01:00 AM                13  \n",
       "74       01:00 AM                14  \n",
       "75       01:00 AM                15  \n",
       "76       01:00 AM                16  \n",
       "77       01:00 AM                17  \n",
       "78       01:00 AM                18  \n",
       "79       01:00 AM                19  \n",
       "80       01:00 AM                20  \n",
       "81       01:00 AM                21  \n",
       "82       01:00 AM                22  \n",
       "83       01:00 AM                23  \n",
       "84       01:00 AM                24  \n",
       "85       01:00 AM                25  \n",
       "86       01:00 AM                26  \n",
       "87       01:00 AM                27  \n",
       "88       01:00 AM                28  \n",
       "89       01:00 AM                29  \n",
       "90       01:00 AM                30  \n",
       "91       01:00 AM                31  \n",
       "92       01:00 AM                32  \n",
       "93       01:00 AM                33  \n",
       "94       01:00 AM                34  \n",
       "95       01:00 AM                35  \n",
       "96       01:00 AM                36  \n",
       "97       01:00 AM                37  \n",
       "98       01:00 AM                38  \n",
       "99       01:00 AM                39  \n",
       "\n",
       "[100 rows x 15 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_dim.head(n=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "## Export each to a CSV first so that we can use the COPY command. It's substantially more efficient.\n",
    "# date_dim.to_csv(path_or_buf=\"./date_dim.csv\", index=False)\n",
    "time_dim.to_csv(path_or_buf=\"./time_dim.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the database table the lazy way\n",
    "We want to load the data from the CSV to save time, but we also want to programmatically create the table in postgres. This simply takes our dataframe, removes all the data but keeps the column names, and uses that to create the table. It also gives them appropriate data types (most of the time). Then, we fill the table with a copy command."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "engine = create_engine('postgresql://' + username + ':' + password + '@' + host + '/' + db_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "empty_date_dim = date_dim.copy()\n",
    "empty_date_dim = empty_date_dim.drop(empty_date_dim.index[0:date_dim.shape[0]])\n",
    "empty_date_dim.to_sql(\"date_dimension\", engine, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "empty_time_dim = time_dim.copy()\n",
    "empty_time_dim = empty_time_dim.drop(empty_time_dim.index[0:time_dim.shape[0]])\n",
    "empty_time_dim.to_sql(\"time_dimension\", engine, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have to create a new connection here. The above uses sqlalchemy, which then itself uses psycopg2, but we want to use psycopg2 directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "connection_string = \"dbname='\" + db_name + \"' \"\n",
    "connection_string += \"user='\" + username + \"' \"\n",
    "connection_string += \"host='\" + host + \"' \"\n",
    "connection_string += \"password='\" + password + \"' \""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Set up a copy statement. The %s will be replaced later\n",
    "sql_statement = \"\"\"\n",
    "    COPY %s FROM STDIN WITH\n",
    "    CSV\n",
    "    HEADER\n",
    "    DELIMITER AS ','\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def load_file(conn, table_name, primary_key, file_object):\n",
    "    cursor = conn.cursor()\n",
    "    cursor.copy_expert(sql=sql_statement % table_name, file=file_object)\n",
    "    conn.commit()\n",
    "    \n",
    "    # Add add primary key, index and then vacuum \n",
    "    cursor.execute(\"ALTER TABLE \" + table_name + \" ADD  PRIMARY KEY (\" + primary_key + \")\")\n",
    "    cursor.execute(\"VACUUM VERBOSE ANALYZE \" + table_name)\n",
    "    cursor.execute(\"CREATE INDEX ON \" + table_name + \" (\" + primary_key + \" ASC NULLS LAST);\")\n",
    "    cursor.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<open file './date_dim.csv', mode 'r' at 0x1148f5f60>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_dim_file = open(\"./date_dim.csv\")\n",
    "date_dim_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'date_dim_file' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-57-270064a96dda>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mautocommit\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m     \u001b[0mload_file\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'date_dimension'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mprimary_key\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"date_key\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfile_object\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdate_dim_file\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'date_dim_file' is not defined"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(connection_string)\n",
    "conn.autocommit = True\n",
    "try:\n",
    "    load_file(conn, table_name='date_dimension', primary_key=\"date_key\", file_object=date_dim_file)\n",
    "finally:\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<open file './time_dim.csv', mode 'r' at 0x114aacdb0>"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_dim_file = open(\"./time_dim.csv\")\n",
    "time_dim_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(connection_string)\n",
    "conn.autocommit = True\n",
    "try:\n",
    "    load_file(conn, table_name='time_dimension', primary_key=\"time_key\", file_object=time_dim_file)\n",
    "finally:\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pokemon_info_df = pd.read_csv(filepath_or_buffer=\"./dimension_table_csvs/pokemon_info.csv\")\n",
    "empty_pokemon_pk_info = pokemon_info_df.copy()\n",
    "empty_pokemon_pk_info = empty_pokemon_pk_info.drop(empty_pokemon_pk_info.index[0:empty_pokemon_pk_info.shape[0]])\n",
    "empty_pokemon_pk_info.to_sql(\"pokemon_info\", engine, if_exists=\"replace\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<open file './dimension_table_csvs/pokemon_info.csv', mode 'r' at 0x1148b5ed0>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pokemon_info_dim_file = open(\"./dimension_table_csvs/pokemon_info.csv\")\n",
    "pokemon_info_dim_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(connection_string)\n",
    "conn.autocommit = True\n",
    "try:\n",
    "    load_file(conn, table_name='pokemon_info', primary_key=\"pokemon_id\", file_object=pokemon_info_dim_file)\n",
    "finally:\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}