attended-events/part_1_data_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/predicthq/phq-data-science-docs/blob/master/attended-events/part_1_data_engineering.ipynb\" target=\"_blank\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Attended Events Data Science Guides\n",
    "\n",
    "# Part 1: Data Engineering\n",
    "\n",
    "PredictHQ's Attended Events are scheduled to occur at a specific venue and usually depend on attendance. This *How to Series* allows you to quickly extract the data (Part 1), explore the data (Part 2) and experiment with different aggregations (Part 3).\n",
    "\n",
    "\n",
    "**A How To Guide to extracting data from PredictHQ's Attended Events data (conferences, expos, concerts, festivals, performing-arts, sports, community).**\n",
    "\n",
    "This notebook will guide you through how to extract Attended Events for a location and time of your choice.\n",
    "\n",
    "- [Setup](#setup)\n",
    "- [Access Token](#access_token)\n",
    "- [SDK Parameters](#setting_params) \n",
    "- [Fetch Events from the API](#calling_api)\n",
    "- [Exploring the Output DataFrame](#exploring_df)\n",
    "- [Appendix - Finding place_id](#appendix)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='setup'></a>\n",
    "# Setup\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If using Google Colab uncomment the following code block."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %%capture\n",
    "# !git clone https://github.com/predicthq/phq-data-science-docs.git\n",
    "# %cd phq-data-science-docs/attended-events\n",
    "# !pip install predicthq>=1.6.3 timezonefinder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "If running locally, configure the required dependencies in your Python environment by using the [requirements.txt](https://github.com/predicthq/phq-data-science-docs/blob/master/attended-events/requirements.txt) file which is shared alongside the notebook.\n",
    "\n",
    "These requirements can be installed by runing the command `pip install -r requirements.txt`\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from predicthq import Client\n",
    "from timezonefinder import TimezoneFinder\n",
    "import requests\n",
    "\n",
    "# To display more columns and with a larger width in the DataFrame\n",
    "pd.set_option(\"display.max_columns\", 50)\n",
    "pd.options.display.max_colwidth = 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='access_token'></a>\n",
    "# Access Token\n",
    "An Access Token is required to query the API.\n",
    "\n",
    "The following link will guide you through creating an account and an access token. \n",
    "\n",
    " - https://docs.predicthq.com/guides/quickstart/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace Access Token with own access token.\n",
    "ACCESS_TOKEN = 'REPLACE_WITH_ACCESS_TOKEN'\n",
    "phq = Client(access_token=ACCESS_TOKEN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='setting_params'></a>\n",
    "# SDK Parameters\n",
    "To search for Attended Events, start by building a parameter dictionary and adding the required filters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "parameters = dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Location\n",
    "Observances, public holidays and school holidays change by location. Specifying the location ensures you will see the relevant events for the location.\n",
    "\n",
    "The notebook provides a default location in Chicago in Northeastern Illinois.\n",
    "\n",
    "This can be adjusted to suit a location that is of interest to you.\n",
    "\n",
    "We can do this in two ways:  \n",
    "\n",
    "  1) Using the `within` parameter, which contains `latitude`, `longitude` of the interested location with a `radius` and a `unit` for the radius.\n",
    "  \n",
    "  2) Using a list of `place_id` values.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using latitude, longitude and a radius\n",
    "latitude, longitude = (41.881832, -87.623177)\n",
    "radius = 5\n",
    "radius_unit = \"km\"\n",
    "\n",
    "within = f\"{radius}{radius_unit}@{latitude},{longitude}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, we could have used a list of `place_id` for our search (See our [Appendix on place_ids](#appendix) for detailed explanation)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using a list of place_id\n",
    "place_ids = [4887398]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use either `within` or `place_id` as a filter but you cannot use both."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "parameters.update(within=within)  # Comment if you want to use place_ids\n",
    "# parameters.update(place__scope=place_ids)  # Keep commented if you want to use lat and long"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Date \"YYYY-MM-DD\"\n",
    "\n",
    "To define the period of time for which Attended Events will be returned, set the greater than or equal (`active__gte`) and less than or equal (`active__lte`) parameters. This will select all Attended Events that are active within this period.\n",
    "\n",
    "You could also use either of these parameters depending on your time period of interest:\n",
    "\n",
    "`gte - Greater than or equal.` <br>\n",
    "`gt - Greater than.`<br>\n",
    "`lte - Less than or equal.`<br>\n",
    "`lt - Less than.`<br>\n",
    "\n",
    "\n",
    "The default example in this notebook is to search for events from 2019 to 2021."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = \"2019-01-01\"\n",
    "end_time = \"2021-12-31\"\n",
    "parameters.update(active__gte=start_time)\n",
    "parameters.update(active__lte=end_time)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Timezone \n",
    "By using the timezone of the location of interest, the dates provided in the `active` parameter are first converted to the local timezone. This ensures appropriate events will be returned. Timezones are in [TZ Database](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) format.\n",
    "\n",
    "For our Chicago example, the timezone is `America/Chicago`.\n",
    "You can use `TimezoneFinder()` to find the timezone of your location of interest."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "America/Chicago\n"
     ]
    }
   ],
   "source": [
    "timezone = TimezoneFinder().timezone_at(lat=latitude, lng=longitude)\n",
    "print(timezone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "parameters.update(active__tz=timezone)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Rank range\n",
    "\n",
    "Similar to the date period, the rank range can be set to filter events. The rank_type can be set to either `rank`, `local_rank` or `aviation_rank`. The `rank` reflects the [estimated impact of an event](https://docs.predicthq.com/start/ranks/). The `local_rank` reflects the [estimated impact of an event in their local area](https://docs.predicthq.com/start/ranks/) by considering population density. Local Rank is useful when comparing events from multiple locations. The `aviation_rank` reflects the number of passengers who attend the event by flight. As a rule of thumb, here is the estimation of the number of attendance/passengers for typical rank_type and rank_threshold settings:\n",
    "\n",
    "rank_type |rank_threshold |Number of attendance/passengers\n",
    ":-----:|:-----:|:-----:\n",
    "rank|$20$|$\\sim30$\n",
    "rank|$30$|$\\sim100$\n",
    "rank|$40$|$\\sim300$\n",
    "rank|$50$|$\\sim1000$\n",
    "rank|$60$|$\\sim3000$\n",
    "aviation_rank|$30$|$\\sim20$\n",
    "aviation_rank|$40$|$\\sim40$\n",
    "aviation_rank|$50$|$\\sim100$\n",
    "aviation_rank|$60$|$\\sim200$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select events according to rank_type, rank threshold.\n",
    "rank_type = \"rank\" # Set to be either \"rank\", \"local_rank\" or \"aviation_rank\".\n",
    "rank_threshold = 40 \n",
    "filter_parameter = \"gte\"\n",
    "parameters.update({f\"{rank_type}__{filter_parameter}\": rank_threshold})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Categories\n",
    "Specify a list of Attended Events categories to return."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = [\n",
    "    \"conferences\",\n",
    "    \"expos\",\n",
    "    \"concerts\",\n",
    "    \"festivals\",\n",
    "    \"performing-arts\",\n",
    "    \"sports\",\n",
    "    \"community\",\n",
    "]\n",
    "parameters.update(category=categories)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Limits \n",
    "When pulling historical data for a large time period, many results are returned. To speed up the execution, set limit to the highest available setting (500). By doing this, each call to the API returns 500 results and this will generally speed up the time to retrieve large datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "parameters.update(limit=500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Checking the parameters\n",
    "Finally, let's take a look at the parameters we have set for our search."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'within': '5km@41.881832,-87.623177',\n",
       " 'active__gte': '2019-01-01',\n",
       " 'active__lte': '2021-12-31',\n",
       " 'active__tz': 'America/Chicago',\n",
       " 'rank__gte': 40,\n",
       " 'category': ['conferences',\n",
       "  'expos',\n",
       "  'concerts',\n",
       "  'festivals',\n",
       "  'performing-arts',\n",
       "  'sports',\n",
       "  'community'],\n",
       " 'limit': 500}"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "parameters"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can check out the full list of available parameters to query our Events API for Attended Events on our [Events API Resource page](https://docs.predicthq.com/resources/events/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='calling_api'></a>\n",
    "# Calling the PredictHQ API and Fetching Events\n",
    "\n",
    "In this step, we use PHQ's Python SDK Client to query and fetch events using the parameters we defined above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = []\n",
    "\n",
    "# Iterating through all the events that match our criteria and adding them to our results\n",
    "for event in phq.events.search(parameters).iter_all():\n",
    "    results.append(event.to_dict())\n",
    "\n",
    "# Converting the results to a DataFrame\n",
    "event_df = pd.DataFrame(results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='exploring_df'></a>\n",
    "# Exploring the Result DataFrame and Storing it\n",
    "We take a look at the result data and select the most important fields for our use case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cancelled</th>\n",
       "      <th>category</th>\n",
       "      <th>country</th>\n",
       "      <th>deleted_reason</th>\n",
       "      <th>description</th>\n",
       "      <th>duplicate_of_id</th>\n",
       "      <th>duration</th>\n",
       "      <th>end</th>\n",
       "      <th>first_seen</th>\n",
       "      <th>id</th>\n",
       "      <th>labels</th>\n",
       "      <th>location</th>\n",
       "      <th>place_hierarchies</th>\n",
       "      <th>postponed</th>\n",
       "      <th>relevance</th>\n",
       "      <th>scope</th>\n",
       "      <th>start</th>\n",
       "      <th>state</th>\n",
       "      <th>timezone</th>\n",
       "      <th>title</th>\n",
       "      <th>updated</th>\n",
       "      <th>aviation_rank</th>\n",
       "      <th>brand_safe</th>\n",
       "      <th>entities</th>\n",
       "      <th>local_rank</th>\n",
       "      <th>phq_attendance</th>\n",
       "      <th>predicted_end</th>\n",
       "      <th>private</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>performing-arts</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-01-01 01:30:00+00:00</td>\n",
       "      <td>2020-06-28 11:51:35+00:00</td>\n",
       "      <td>HjogymBLsUremag8sA</td>\n",
       "      <td>[entertainment, music, performing-arts]</td>\n",
       "      <td>[-87.622225, 41.89819]</td>\n",
       "      <td>[[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>locality</td>\n",
       "      <td>2022-01-01 01:30:00+00:00</td>\n",
       "      <td>active</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>SIX (Chicago)</td>\n",
       "      <td>2020-10-10 00:45:31+00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>[{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...</td>\n",
       "      <td>45</td>\n",
       "      <td>331</td>\n",
       "      <td>NaT</td>\n",
       "      <td>False</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>performing-arts</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2022-01-01 00:30:00+00:00</td>\n",
       "      <td>2020-09-29 15:22:48+00:00</td>\n",
       "      <td>PaKQgbAMYEnCPFfCXk</td>\n",
       "      <td>[entertainment, music, performing-arts]</td>\n",
       "      <td>[-87.633038, 41.884197]</td>\n",
       "      <td>[[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>locality</td>\n",
       "      <td>2022-01-01 00:30:00+00:00</td>\n",
       "      <td>active</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Disney’s Frozen (Chicago)</td>\n",
       "      <td>2020-11-06 03:22:52+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>[{'entity_id': '3wyTQaVW5h4JJGw9zQ5TvZ', 'name': 'Cadillac Palace Theatre', 'type': 'venue', 'fo...</td>\n",
       "      <td>63</td>\n",
       "      <td>1505</td>\n",
       "      <td>NaT</td>\n",
       "      <td>False</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>performing-arts</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-12-31 20:00:00+00:00</td>\n",
       "      <td>2020-05-22 17:27:36+00:00</td>\n",
       "      <td>fhhNm3mWf2ThKvhjYJ</td>\n",
       "      <td>[entertainment, music, performing-arts]</td>\n",
       "      <td>[-87.622225, 41.89819]</td>\n",
       "      <td>[[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>locality</td>\n",
       "      <td>2021-12-31 20:00:00+00:00</td>\n",
       "      <td>active</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>SIX (Chicago)</td>\n",
       "      <td>2020-10-10 00:49:52+00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>[{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...</td>\n",
       "      <td>45</td>\n",
       "      <td>331</td>\n",
       "      <td>NaT</td>\n",
       "      <td>False</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>performing-arts</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>Delivery delay until September 27th, 2019. Single Ticket purchases are limited to 9 tickets per ...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-12-31 19:00:00+00:00</td>\n",
       "      <td>2019-09-06 20:08:26+00:00</td>\n",
       "      <td>cT3DVGjWRrMKaTGauR</td>\n",
       "      <td>[entertainment, music, performing-arts]</td>\n",
       "      <td>[-87.633038, 41.884197]</td>\n",
       "      <td>[[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>locality</td>\n",
       "      <td>2021-12-31 19:00:00+00:00</td>\n",
       "      <td>active</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Disney’s Frozen (Chicago)</td>\n",
       "      <td>2020-08-13 16:04:41+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>[{'entity_id': '3wyTQaVW5h4JJGw9zQ5TvZ', 'name': 'Cadillac Palace Theatre', 'type': 'venue', 'fo...</td>\n",
       "      <td>63</td>\n",
       "      <td>1505</td>\n",
       "      <td>NaT</td>\n",
       "      <td>False</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>performing-arts</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-12-31 01:30:00+00:00</td>\n",
       "      <td>2020-02-15 23:09:53+00:00</td>\n",
       "      <td>KHYby2vDe9bfgdVm9V</td>\n",
       "      <td>[entertainment, music, performing-arts]</td>\n",
       "      <td>[-87.622225, 41.89819]</td>\n",
       "      <td>[[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>locality</td>\n",
       "      <td>2021-12-31 01:30:00+00:00</td>\n",
       "      <td>active</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>SIX (Chicago)</td>\n",
       "      <td>2021-03-17 07:08:21+00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>[{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...</td>\n",
       "      <td>49</td>\n",
       "      <td>331</td>\n",
       "      <td>NaT</td>\n",
       "      <td>False</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cancelled         category country deleted_reason  \\\n",
       "0      None  performing-arts      US           None   \n",
       "1      None  performing-arts      US           None   \n",
       "2      None  performing-arts      US           None   \n",
       "3      None  performing-arts      US           None   \n",
       "4      None  performing-arts      US           None   \n",
       "\n",
       "                                                                                           description  \\\n",
       "0                                                                                                        \n",
       "1                                                                                                        \n",
       "2                                                                                                        \n",
       "3  Delivery delay until September 27th, 2019. Single Ticket purchases are limited to 9 tickets per ...   \n",
       "4                                                                                                        \n",
       "\n",
       "  duplicate_of_id  duration                       end  \\\n",
       "0            None         0 2022-01-01 01:30:00+00:00   \n",
       "1            None         0 2022-01-01 00:30:00+00:00   \n",
       "2            None         0 2021-12-31 20:00:00+00:00   \n",
       "3            None         0 2021-12-31 19:00:00+00:00   \n",
       "4            None         0 2021-12-31 01:30:00+00:00   \n",
       "\n",
       "                 first_seen                  id  \\\n",
       "0 2020-06-28 11:51:35+00:00  HjogymBLsUremag8sA   \n",
       "1 2020-09-29 15:22:48+00:00  PaKQgbAMYEnCPFfCXk   \n",
       "2 2020-05-22 17:27:36+00:00  fhhNm3mWf2ThKvhjYJ   \n",
       "3 2019-09-06 20:08:26+00:00  cT3DVGjWRrMKaTGauR   \n",
       "4 2020-02-15 23:09:53+00:00  KHYby2vDe9bfgdVm9V   \n",
       "\n",
       "                                    labels                 location  \\\n",
       "0  [entertainment, music, performing-arts]   [-87.622225, 41.89819]   \n",
       "1  [entertainment, music, performing-arts]  [-87.633038, 41.884197]   \n",
       "2  [entertainment, music, performing-arts]   [-87.622225, 41.89819]   \n",
       "3  [entertainment, music, performing-arts]  [-87.633038, 41.884197]   \n",
       "4  [entertainment, music, performing-arts]   [-87.622225, 41.89819]   \n",
       "\n",
       "                                          place_hierarchies postponed  \\\n",
       "0  [[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]      None   \n",
       "1  [[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]      None   \n",
       "2  [[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]      None   \n",
       "3  [[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]      None   \n",
       "4  [[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]]      None   \n",
       "\n",
       "   relevance     scope                     start   state         timezone  \\\n",
       "0        1.0  locality 2022-01-01 01:30:00+00:00  active  America/Chicago   \n",
       "1        1.0  locality 2022-01-01 00:30:00+00:00  active  America/Chicago   \n",
       "2        1.0  locality 2021-12-31 20:00:00+00:00  active  America/Chicago   \n",
       "3        1.0  locality 2021-12-31 19:00:00+00:00  active  America/Chicago   \n",
       "4        1.0  locality 2021-12-31 01:30:00+00:00  active  America/Chicago   \n",
       "\n",
       "                       title                   updated  aviation_rank  \\\n",
       "0              SIX (Chicago) 2020-10-10 00:45:31+00:00            0.0   \n",
       "1  Disney’s Frozen (Chicago) 2020-11-06 03:22:52+00:00            NaN   \n",
       "2              SIX (Chicago) 2020-10-10 00:49:52+00:00            0.0   \n",
       "3  Disney’s Frozen (Chicago) 2020-08-13 16:04:41+00:00            NaN   \n",
       "4              SIX (Chicago) 2021-03-17 07:08:21+00:00            0.0   \n",
       "\n",
       "  brand_safe  \\\n",
       "0       None   \n",
       "1       None   \n",
       "2       None   \n",
       "3       None   \n",
       "4       None   \n",
       "\n",
       "                                                                                              entities  \\\n",
       "0  [{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...   \n",
       "1  [{'entity_id': '3wyTQaVW5h4JJGw9zQ5TvZ', 'name': 'Cadillac Palace Theatre', 'type': 'venue', 'fo...   \n",
       "2  [{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...   \n",
       "3  [{'entity_id': '3wyTQaVW5h4JJGw9zQ5TvZ', 'name': 'Cadillac Palace Theatre', 'type': 'venue', 'fo...   \n",
       "4  [{'entity_id': 'HFJ57uTXBSMQpDkX95u9GK', 'name': 'Broadway Playhouse', 'type': 'venue', 'formatt...   \n",
       "\n",
       "   local_rank  phq_attendance predicted_end  private  rank  \n",
       "0          45             331           NaT    False    40  \n",
       "1          63            1505           NaT    False    54  \n",
       "2          45             331           NaT    False    40  \n",
       "3          63            1505           NaT    False    54  \n",
       "4          49             331           NaT    False    40  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "event_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is important to understand the output data. The most useful fields are the following:\n",
    "- `id` The unique id of the event.\n",
    "- `title` The title of the event.\n",
    "- `description` The description of the event.\n",
    "- `duration` Duration of the event in seconds.\n",
    "- `start` The start time of the event.\n",
    "- `end` The end time of the event.\n",
    "- `predicted_end` The estimated end time of the event.\n",
    "- `first_seen` The time when we first received the event.\n",
    "- `category` The category the event belongs to. e.g. school-holidays, public-holidays, observances.\n",
    "- `labels` Labels of the event.\n",
    "- `location` Latitude and longitude of the event.\n",
    "- `place_hierarchies` The hierarchies of place_ids for places the event is located in.\n",
    "- `timezone` The timezone of the event's location.\n",
    "- `entities` The entities associated with the event.\n",
    "- `phq_attendance` The number of people expected to attend the event.\n",
    "- `rank` PHQ rank of the event.\n",
    "- `local_rank` Local rank of the event.\n",
    "- `aviation_rank` Aviation rank of the event."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Selecting the target fields\n",
    "# If running this cell gave you an exception, make sure you're using the `predicthq` library version specified in requirements.txt\n",
    "# or the version advised above if you're running this notebook on Colab. Then run this notebook again.\n",
    "event_df = event_df[\n",
    "    [\n",
    "        \"id\",\n",
    "        \"title\",\n",
    "        \"description\",\n",
    "        \"duration\",\n",
    "        \"start\",\n",
    "        \"end\",\n",
    "        \"predicted_end\",\n",
    "        \"first_seen\",\n",
    "        \"category\",\n",
    "        \"labels\",\n",
    "        \"location\",\n",
    "        \"place_hierarchies\",\n",
    "        \"timezone\",\n",
    "        \"entities\",\n",
    "        \"phq_attendance\",\n",
    "        \"rank\",\n",
    "        \"local_rank\",\n",
    "        \"aviation_rank\",\n",
    "    ]\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame saved to data/event_data/radius5km_41.881832_-87.623177_2019-01-01_2021-12-31_rank_gte_40.csv\n"
     ]
    }
   ],
   "source": [
    "# Creating a filename for our DataFrame and saving our final DataFrame as a CSV file\n",
    "if \"within\" in parameters:\n",
    "    file_name = (\n",
    "        f\"radius{radius}{radius_unit}_{latitude}_{longitude}_{start_time}_{end_time}_\"\n",
    "        + f\"{rank_type}_{filter_parameter}_{rank_threshold}\"\n",
    "    )\n",
    "else:\n",
    "    file_name = (\n",
    "        f\"place_ids_{'_'.join(place_ids)}_{start_time}_{end_time}_\"\n",
    "        + f\"{rank_type}_{filter_parameter}_{rank_threshold}\"\n",
    "    )\n",
    "\n",
    "event_df.to_csv(f\"data/event_data/{file_name}.csv\", index=False)\n",
    "print(f\"DataFrame saved to data/event_data/{file_name}.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='appendix'></a>\n",
    "## Appendix: Finding `place_id` "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a guide on how to link between store locations and `place_id`. We present 3 options:\n",
    "\n",
    " 1. Query `place_id` based on location name. Here the location name could be a city, a state, a country or a continent.\n",
    " 2. Query `place_hierarchies` based on latitude and longitude.\n",
    " 3. Query location name based on `place_id`.\n",
    "\n",
    "The full list of parameters that you could use in your query is documented at our [Places API page](https://docs.predicthq.com/resources/places/).<br>PredictHQ uses the [geonames places](https://www.geonames.org/) convention. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1) Query `place_id` based on location name\n",
    "\n",
    "By using PredictHQ's Places API, you can find the `place_id` for a specific location name. By calling the API and setting `q` to location name, the API will return the most relevant `place_id`. Taking the top `place_id` will provide the most relevant `place_id` the location name is in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example location names.\n",
    "locations = [\"Chicago\", \"Cook County\", \"United States\", \"North America\"]\n",
    "\n",
    "place_id_lookup = pd.DataFrame()\n",
    "\n",
    "for location in locations:\n",
    "    response = requests.get(\n",
    "        url=\"https://api.predicthq.com/v1/places/\",\n",
    "        headers={\n",
    "            \"Authorization\": f\"Bearer {ACCESS_TOKEN}\",\n",
    "            \"Accept\": \"application/json\",\n",
    "        },\n",
    "        params={\"q\": location},\n",
    "    )\n",
    "\n",
    "    data = response.json()\n",
    "    df = pd.json_normalize(data[\"results\"])\n",
    "    place_id_lookup = place_id_lookup.append(df.iloc[0], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4887398</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>locality</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4888671</td>\n",
       "      <td>Cook County</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6252001</td>\n",
       "      <td>United States</td>\n",
       "      <td>country</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6255149</td>\n",
       "      <td>North America</td>\n",
       "      <td>continent</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id           name       type\n",
       "0  4887398        Chicago   locality\n",
       "1  4888671    Cook County     county\n",
       "2  6252001  United States    country\n",
       "3  6255149  North America  continent"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "place_id_lookup[[\"id\", \"name\", \"type\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2) Query `place_hierarchies` based on  latitude and longitude\n",
    "\n",
    "By using PredictHQ's Places Hierarchies endpoint in the Place API, you can find the `place_hierarchies` for a specific latitude and longitude. By calling the API and setting `location.origin` to `latitude,longitude`, the API will return the most relevant `place_hierarchies`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example locations.\n",
    "latitude_longitudes = [[41.881832, -87.623177]]\n",
    "\n",
    "place_hierarchies_lookup = pd.DataFrame()\n",
    "\n",
    "for latitude_longitude in latitude_longitudes:\n",
    "    latitude, longitude = latitude_longitude\n",
    "    response = requests.get(\n",
    "        url=\"https://api.predicthq.com/v1/places/hierarchies\",\n",
    "        headers={\n",
    "            \"Authorization\": f\"Bearer {ACCESS_TOKEN}\",\n",
    "            \"Accept\": \"application/json\",\n",
    "        },\n",
    "        params={\"location.origin\": f\"{latitude},{longitude}\"},\n",
    "    )\n",
    "\n",
    "    data = response.json()\n",
    "    df = pd.DataFrame(data)\n",
    "    df[\"latitude\"] = latitude\n",
    "    df[\"longitude\"] = longitude\n",
    "    place_hierarchies_lookup = place_hierarchies_lookup.append(df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>place_hierarchies</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[6295630, 6255149, 6252001, 4896861, 4888671, 4887398]</td>\n",
       "      <td>41.881832</td>\n",
       "      <td>-87.623177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        place_hierarchies   latitude  \\\n",
       "0  [6295630, 6255149, 6252001, 4896861, 4888671, 4887398]  41.881832   \n",
       "\n",
       "   longitude  \n",
       "0 -87.623177  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "place_hierarchies_lookup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For each `latitude,longitude`, the response might include more than one hierarchy. The reason for this is we try to match the closest place's hierarchy but we also include the closest major city's hierarchy within a radius of 50km. This only applies if the level is below region and, if it exists, the major city's hierarchy will always be the second row of the DataFrame."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3) Query location name based on `place_id`\n",
    "\n",
    "By using PredictHQ's Places API, you can find the location name for a specific `place_id`. By calling the API and setting `id` to `place_id`, the API will return the most relevant location name. Taking the top location name will provide the most relevant location name the `place_id` is in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Example place_ids.\n",
    "place_ids = [\"6295630\", \"6255149\", \"6252001\", \"4896861\", \"4888671\", \"4887398\"]\n",
    "\n",
    "location_lookup = pd.DataFrame()\n",
    "\n",
    "for place_id in place_ids:\n",
    "    response = requests.get(\n",
    "        url=\"https://api.predicthq.com/v1/places/\",\n",
    "        headers={\n",
    "            \"Authorization\": f\"Bearer {ACCESS_TOKEN}\",\n",
    "            \"Accept\": \"application/json\",\n",
    "        },\n",
    "        # The id could be a comma-separated list of place_ids. In this example, the\n",
    "        # events are queried based on each place_id.\n",
    "        params={\"id\": place_id},\n",
    "    )\n",
    "\n",
    "    data = response.json()\n",
    "    df = pd.json_normalize(data[\"results\"])\n",
    "    location_lookup = location_lookup.append(df.iloc[0], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6295630</td>\n",
       "      <td>Earth</td>\n",
       "      <td>planet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6255149</td>\n",
       "      <td>North America</td>\n",
       "      <td>continent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6252001</td>\n",
       "      <td>United States</td>\n",
       "      <td>country</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4896861</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>region</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4888671</td>\n",
       "      <td>Cook County</td>\n",
       "      <td>county</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4887398</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>locality</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id           name       type\n",
       "0  6295630          Earth     planet\n",
       "1  6255149  North America  continent\n",
       "2  6252001  United States    country\n",
       "3  4896861       Illinois     region\n",
       "4  4888671    Cook County     county\n",
       "5  4887398        Chicago   locality"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "location_lookup[[\"id\", \"name\", \"type\"]]"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}