{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Tour Dates Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from snowflake.sqlalchemy import URL\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load monthly sheets\n",
    "monthly_dates = [\n",
    "    '2017.11.28', '2017.12.4', '2017.12.11',\n",
    "    '2017.12.18', '2018.1.2', '2018.1.16',\n",
    "    '2018.1.23', '2018.2.5', '2018.2.12',\n",
    "    '2018.2.20', '2018.3.5', '2018.3.12',\n",
    "    '2018.3.26', '2018.4.2', '2018.4.9',\n",
    "    '2018.4.16', '2018.4.23', '2018.4.30',\n",
    "    '2018.5.7', '2018.5.15', '2018.5.21',\n",
    "    '2018.5.29', '2018.6.4', '2018.6.11',\n",
    "    '2018.6.18', '2018.6.25', '2018.7.9',\n",
    "    '2018.7.16', '2018.7.23', '2018.7.30',\n",
    "    '2018.8.13', '2018.8.20', '2018.8.27',\n",
    "    '2018.9.4', '2018.9.10', '2018.9.24',\n",
    "    '2018.10.1', '2018.11.5', '2018.11.12']\n",
    "\n",
    "for date in monthly_dates:\n",
    "    tours_monthly = pd.read_excel(\n",
    "        'Tours {}.xlsx'.format(date), sheet_name=['Tours', 'New This Week'],\n",
    "        usecols='A:F')\n",
    "    df_list.append(tours_monthly['Tours'])\n",
    "    df_list.append(tours_monthly['New This Week'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# load Jacob's spreadsheet\n",
    "tours_jh = pd.read_excel(\n",
    "    'orchard_touring.xlsx', sheet_name=['Tours', 'Past Dates'], usecols='A:F')\n",
    "df_list.append(tours_jh['Tours'])\n",
    "df_list.append(tours_jh['Past Dates'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load historical data\n",
    "months = [\n",
    "    'January 2016', 'February 2016', 'March 2016',\n",
    "    'April 2016', 'May 2016', 'June 2016',\n",
    "    'July 2016', 'August 2016', 'September 2016',\n",
    "    'October 2016', 'November 2016', 'December 2016',\n",
    "    'January 2017', 'February 2017', 'March 2017',\n",
    "    'April 2017', 'May 2017', 'June 2017',\n",
    "    'July 2017', 'August 2017', 'September 2017',\n",
    "    'October 2017', 'November 2017']\n",
    "\n",
    "tours_historical = pd.read_excel(\n",
    "    'historical_tour_dates.xlsx', sheet_name=months, usecols='A:D,F')\n",
    "\n",
    "# prep data\n",
    "for month in months:\n",
    "    # split location\n",
    "    loc = tours_historical[month]['City/State'].str.split(\",\", expand=True)\n",
    "    tours_historical[month]['City'] = loc[0]\n",
    "    tours_historical[month]['State'] = loc[1]\n",
    "    \n",
    "    # rename columns\n",
    "    tours_historical[month] = tours_historical[month].rename(\n",
    "        index=str,\n",
    "        columns={'Venue': 'Venue/Tour', 'Date': 'Tour Dates', 'Band': 'Artist'})\n",
    "    \n",
    "    # drop column\n",
    "    tours_historical[month].drop('City/State', axis=1, inplace=True)\n",
    "    \n",
    "    # reorder columns\n",
    "    tours_historical[month] = tours_historical[month].reindex(\n",
    "        columns=['Artist', 'Label', 'Tour Dates', 'Venue/Tour', 'City', 'State'])\n",
    "    \n",
    "    df_list.append(tours_historical[month])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine, remove duplicates, and rename columns\n",
    "tour_dates_data = pd.concat(df_list, axis=0)\n",
    "tour_dates_data.drop_duplicates(['Artist', 'Tour Dates'], inplace=True)\n",
    "\n",
    "tour_dates_data = tour_dates_data.rename(\n",
    "    index=str,\n",
    "    columns={\n",
    "        'Artist': 'artist',\n",
    "        'Label': 'label',\n",
    "        'Tour Dates': 'date',\n",
    "        'Venue/Tour': 'venue',\n",
    "        'City': 'city',\n",
    "        'State': 'state'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# recast column types\n",
    "tour_dates_data = tour_dates_data.astype(\n",
    "    {'artist': str, 'label': str, 'venue': str, 'city': str, 'state': str})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove new lines\n",
    "tour_dates_data = tour_dates_data.replace('\\n', '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# manually replace anomalous data\n",
    "tour_dates_data.replace({\n",
    "    'Obsessed, The': 'The Obsessed',\n",
    "    'Future Generaions': 'Future Generations',\n",
    "    'Bright Light Social Hour': 'The Bright Light Social Hour',\n",
    "    'The Fabulous Thunderbirds feat. Kim Wilson': 'The Fabulous Thunderbirds',\n",
    "    'Charlie Hunter & Lucy Woodward': 'Charlie Hunter',\n",
    "    'Dr Dog': 'Dr. Dog',\n",
    "    'Ballroom Thieves': 'The Ballroom Thieves',\n",
    "    'Jim Breuer (stand up)': 'Jim Breuer',\n",
    "    'Alfredo Rodgriguez': 'Alfredo Rodriguez',\n",
    "    'Alfredo Rodriquez': 'Alfredo Rodriguez',\n",
    "    'Cyrille Aimee\\'': 'Cyrille Aimee',\n",
    "    'Brigit Mendler': 'Bridgit Mendler',\n",
    "    'Killswitch Engage': 'Killswitch',\n",
    "    'Lee Fields & The Expression': 'Lee Fields & The Expressions',\n",
    "    'Budos Band': 'The Budos Band',\n",
    "    'Micheal Franti & Spearhead': 'Michael Franti & Spearhead',\n",
    "    'Fairfield Four': 'The Fairfield Four',\n",
    "    'Bokante': 'Bokanté',\n",
    "    'Arthur King Presents: Tim Rutili': 'Tim Rutili',\n",
    "    'Wolverserpent': 'Wolvserpent'},\n",
    "    inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write data to Snowflake\n",
    "user = 'jamesc'\n",
    "password = ''\n",
    "\n",
    "engine = create_engine(URL(\n",
    "    user=user,\n",
    "    password=password,\n",
    "    account='orchard',\n",
    "    database='dev_engineering',\n",
    "    schema='events_streams',\n",
    "    role='dev_engineering',\n",
    "    warehouse='dev_performance_warehouse'))\n",
    "\n",
    "tour_dates_data.to_sql(\n",
    "    'raw_tour_dates',\n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    index=False,\n",
    "    index_label=None, \n",
    "    chunksize=20000)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "events_streams",
   "language": "python",
   "name": "events_streams"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}