seatgeek_redshift_adhoc_9.13.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import time\n",
    "import json\n",
    "from sqlalchemy import *\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "pd.set_option('display.max_columns', 500)\n",
    "\n",
    "df = pd.DataFrame()\n",
    "\n",
    "city_list = ['San Francisco', 'Oakland', 'Berkeley', 'San Jose',\n",
    "            'New York', 'Brooklyn', 'Bronx', 'Flushing', 'East Rutherford',\n",
    "           'Washington, DC', 'Vienna',\n",
    "            'Chicago', 'Rosemont', 'Evanston',\n",
    "           'Los Angeles', 'Hollywood', 'West Hollywood', 'Pasadena',\n",
    "           'Boston', 'Medford']\n",
    "\n",
    "for i in range(1,11):\n",
    "    responseEvents = requests.get(f'https://api.seatgeek.com/events?per_page=5000&page={i}&client_id=MTI5NzU0MTd8MTUzNTkwMTU5Mi45Nw')\n",
    "\n",
    "    eventsDict = json.loads(responseEvents.content.decode(\"utf-8\")) #dictionary\n",
    "\n",
    "    eventsList = eventsDict['events'] #list of ten dictionaries\n",
    "\n",
    "    local_date = [eventsList[i]['datetime_local'] for i in range(len(eventsList))]\n",
    "    utc_date = [eventsList[i]['datetime_utc'] for i in range(len(eventsList))]\n",
    "    Id = [eventsList[i]['id'] for i in range(len(eventsList))]\n",
    "    score = [eventsList[i]['score'] for i in range(len(eventsList))]\n",
    "    title = [eventsList[i]['short_title'] for i in range(len(eventsList))]\n",
    "    avgPrice = [eventsList[i]['stats']['average_price'] for i in range(len(eventsList))]\n",
    "    hiPrice = [eventsList[i]['stats']['highest_price'] for i in range(len(eventsList))]\n",
    "    listCount = [eventsList[i]['stats']['listing_count'] for i in range(len(eventsList))]\n",
    "    lowPrice = [eventsList[i]['stats']['lowest_price'] for i in range(len(eventsList))]\n",
    "    lowPriceDeals = [eventsList[i]['stats']['lowest_price_good_deals'] for i in range(len(eventsList))]\n",
    "    Type = [eventsList[i]['type'] for i in range(len(eventsList))]\n",
    "    city = [eventsList[i]['venue']['city'] for i in range(len(eventsList))]\n",
    "    state = [eventsList[i]['venue']['state'] for i in range(len(eventsList))]\n",
    "    lat = [eventsList[i]['venue']['location']['lat'] for i in range(len(eventsList))]\n",
    "    lon = [eventsList[i]['venue']['location']['lon'] for i in range(len(eventsList))]\n",
    "    postal = [eventsList[i]['venue']['postal_code'] for i in range(len(eventsList))]\n",
    "    url = [eventsList[i]['url'] for i in range(len(eventsList))]\n",
    "    performer = [eventsList[i]['performers'][0]['name'] for i in range(len(eventsList))]\n",
    "\n",
    "    dfNew = pd.DataFrame({\"local_date\": local_date, \"utc_date\": utc_date, \"Id\": Id, \"score\": score, \"title\":title, \n",
    "                   \"avgPrice\": avgPrice, \"hiPrice\": hiPrice, \"listCount\": listCount, \"lowPrice\": lowPrice, \n",
    "                   \"lowPriceDeals\": lowPriceDeals, \"Type\":Type, \"city\": city, \"state\":state, \n",
    "                   \"lat\": lat, \"lon\": lon, \"postal\":postal, \"url\": url, \"performer\": performer})\n",
    "                                  \n",
    "    df = pd.concat([df, dfNew])\n",
    "\n",
    "dfCity = df.loc[df.city.apply(lambda x: x in city_list)] #filter by city list\n",
    "dfConcert = dfCity.loc[dfCity.Type == \"concert\"] #filter by Type == concert\n",
    "\n",
    "today = datetime.date.today() - datetime.timedelta(days=1)\n",
    "\n",
    "dfConcert.to_csv(f'SeatGeek_{today.month}_{today.day}_{today.year}.csv')\n",
    "\n",
    "time.sleep(30)\n",
    "\n",
    "############################################## MOVE FROM S3 TO REDSHIFT ########################################################\n",
    "\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://awsuser:Capstone1@redshift-cluster-1.cah6qt0iybst.us-east-2.redshift.amazonaws.com:5439/dev')\n",
    "\n",
    "# get day\n",
    "month = today.month\n",
    "day = today.day\n",
    "year = today.year\n",
    "\n",
    "# =============================================================================================================\n",
    "# SEATGEEK.VENUES_DF\n",
    "\n",
    "# make sure working table exists\n",
    "engine.execute(\"CREATE TABLE IF NOT EXISTS working.sg_venues_df (LIKE seatgeek.venues_df);\")\n",
    "\n",
    "# fill working table\n",
    "engine.execute(text(\"\"\"COPY working.sg_venues_df FROM 's3://nycdsa.ta-am/SeatGeek_venueDF_{}_{}_{}.csv'\n",
    "    CREDENTIALS 'aws_iam_role=arn:aws:iam::148285915521:role/myRedshiftRole'\n",
    "    DELIMITER ',' REGION 'us-east-2'\n",
    "    IGNOREHEADER 1\n",
    "    CSV;\"\"\".format(month,day,year)).execution_options(autocommit=True))\n",
    "# fill only if listing_id and listing_deliv_method is unique\n",
    "engine.execute(text(\"\"\"INSERT INTO seatgeek.venues_df\n",
    "    SELECT w.*\n",
    "    FROM working.sg_venues_df AS w\n",
    "    WHERE\n",
    "        NOT EXISTS (SELECT 1\n",
    "            FROM seatgeek.venues_df AS s\n",
    "            WHERE s.venue_id = w.venue_id);\"\"\").execution_options(autocommit=True))\n",
    "# empty working table\n",
    "engine.execute(\"TRUNCATE working.sg_venues_df;\")\n",
    "\n",
    "\n",
    "\n",
    "# =============================================================================================================\n",
    "# SEATGEEK.PRICES_DF\n",
    "\n",
    "# make sure working table exists\n",
    "engine.execute(\"CREATE TABLE IF NOT EXISTS working.sg_prices_df (LIKE seatgeek.prices_df);\")\n",
    "\n",
    "# fill working table\n",
    "engine.execute(text(\"\"\"COPY working.sg_prices_df FROM 's3://nycdsa.ta-am/SeatGeek_pricesDF_{}_{}_{}.csv'\n",
    "    CREDENTIALS 'aws_iam_role=arn:aws:iam::148285915521:role/myRedshiftRole'\n",
    "    DELIMITER ',' REGION 'us-east-2'\n",
    "    IGNOREHEADER 1\n",
    "    CSV;\"\"\".format(month,day,year)).execution_options(autocommit=True))\n",
    "# fill only if listing_id and listing_deliv_method is unique\n",
    "engine.execute(text(\"\"\"INSERT INTO seatgeek.prices_df\n",
    "    SELECT w.*\n",
    "    FROM working.sg_prices_df AS w\n",
    "    WHERE\n",
    "        NOT EXISTS (SELECT 1\n",
    "            FROM seatgeek.prices_df AS s\n",
    "            WHERE s.event_id = w.event_id);\"\"\").execution_options(autocommit=True))\n",
    "# empty working table\n",
    "engine.execute(\"TRUNCATE working.sg_prices_df;\")\n",
    "\n",
    "\n",
    "# =============================================================================================================\n",
    "# SEATGEEK.EVENTS_DF\n",
    "\n",
    "# make sure working table exists\n",
    "engine.execute(\"CREATE TABLE IF NOT EXISTS working.sg_events_df (LIKE seatgeek.events_df);\")\n",
    "\n",
    "# fill working table\n",
    "engine.execute(text(\"\"\"COPY working.sg_events_df FROM 's3://nycdsa.ta-am/SeatGeek_eventsDF_{}_{}_{}.csv'\n",
    "    CREDENTIALS 'aws_iam_role=arn:aws:iam::148285915521:role/myRedshiftRole'\n",
    "    DELIMITER ',' REGION 'us-east-2'\n",
    "    IGNOREHEADER 1\n",
    "    CSV;\"\"\".format(month,day,year)).execution_options(autocommit=True))\n",
    "# fill only if listing_id and listing_deliv_method is unique\n",
    "engine.execute(text(\"\"\"INSERT INTO seatgeek.events_df\n",
    "    SELECT w.*\n",
    "    FROM working.sg_events_df AS w\n",
    "    WHERE\n",
    "        NOT EXISTS (SELECT 1\n",
    "            FROM seatgeek.events_df AS s\n",
    "            WHERE s.event_id = w.event_id);\"\"\").execution_options(autocommit=True))\n",
    "# empty working table\n",
    "engine.execute(\"TRUNCATE working.sg_events_df;\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}