bqml-incl-intersection-clusters.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a copy of my public kaggle kernel: https://www.kaggle.com/joatom/bqml-incl-intersection-clusters",
    "\n",
    "# Intro\n",
    "\n",
    "The kernel relies as much as possible on BigQuery (BQ). All features are generated in BQ. And the prediction model is also build in BQ.\n",
    "\n",
    "## Summary\n",
    "Features:\n",
    "- city, hour, weekend, month\n",
    "- 10 clusters of Intersections per city\n",
    "- Distance to the nearest cluster\n",
    "- 8 directions of turn (left, right, centered, uturn, centered-left, ...)\n",
    "- Directions, Entry- and Exit-Heading are embedded and also they are translated into degrees (centered = 90deg; North = 90deg). Afterwards the degree features are split into two features sin(feature_x_deg) and cos(feature_x_deg).\n",
    "\n",
    "Model:\n",
    "- Linear Regression\n",
    "\n",
    "\n",
    "## Next TODOs\n",
    "- reasonable Train-Valid-Split strategy\n",
    "\n",
    "## Credits\n",
    "Some of the ideas are inspired by the following kernels. Please visit and give them upvotes if you like them.\n",
    "- This kernel is a forked from [BigQuery Machine Learning Tutorial](https://www.kaggle.com/rtatman/bigquery-machine-learning-tutorial).\n",
    "- The direction features are like the Flow feature in https://www.kaggle.com/jpmiller/intersection-level-eda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_kg_hide-input": true
   },
   "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>RowId</th>\n",
       "      <th>IntersectionId</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>EntryStreetName</th>\n",
       "      <th>ExitStreetName</th>\n",
       "      <th>EntryHeading</th>\n",
       "      <th>ExitHeading</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Weekend</th>\n",
       "      <th>...</th>\n",
       "      <th>TimeFromFirstStop_p40</th>\n",
       "      <th>TimeFromFirstStop_p50</th>\n",
       "      <th>TimeFromFirstStop_p60</th>\n",
       "      <th>TimeFromFirstStop_p80</th>\n",
       "      <th>DistanceToFirstStop_p20</th>\n",
       "      <th>DistanceToFirstStop_p40</th>\n",
       "      <th>DistanceToFirstStop_p50</th>\n",
       "      <th>DistanceToFirstStop_p60</th>\n",
       "      <th>DistanceToFirstStop_p80</th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2079854</td>\n",
       "      <td>33</td>\n",
       "      <td>42.34636</td>\n",
       "      <td>-71.09095</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>8</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>31</td>\n",
       "      <td>40</td>\n",
       "      <td>44</td>\n",
       "      <td>59</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25.5</td>\n",
       "      <td>32.3</td>\n",
       "      <td>51.8</td>\n",
       "      <td>68.9</td>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2079857</td>\n",
       "      <td>33</td>\n",
       "      <td>42.34636</td>\n",
       "      <td>-71.09095</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>17</td>\n",
       "      <td>29</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>21.8</td>\n",
       "      <td>40.4</td>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2079872</td>\n",
       "      <td>33</td>\n",
       "      <td>42.34636</td>\n",
       "      <td>-71.09095</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>22</td>\n",
       "      <td>33</td>\n",
       "      <td>38</td>\n",
       "      <td>60</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43.2</td>\n",
       "      <td>47.3</td>\n",
       "      <td>51.1</td>\n",
       "      <td>65.7</td>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2079893</td>\n",
       "      <td>33</td>\n",
       "      <td>42.34636</td>\n",
       "      <td>-71.09095</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>12</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "      <td>18</td>\n",
       "      <td>31</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>22.5</td>\n",
       "      <td>44.4</td>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2079896</td>\n",
       "      <td>33</td>\n",
       "      <td>42.34636</td>\n",
       "      <td>-71.09095</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>Fenway</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>34</td>\n",
       "      <td>65</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.4</td>\n",
       "      <td>52.9</td>\n",
       "      <td>68.9</td>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     RowId  IntersectionId  Latitude  Longitude EntryStreetName  \\\n",
       "0  2079854              33  42.34636  -71.09095          Fenway   \n",
       "1  2079857              33  42.34636  -71.09095          Fenway   \n",
       "2  2079872              33  42.34636  -71.09095          Fenway   \n",
       "3  2079893              33  42.34636  -71.09095          Fenway   \n",
       "4  2079896              33  42.34636  -71.09095          Fenway   \n",
       "\n",
       "  ExitStreetName EntryHeading ExitHeading  Hour  Weekend  ...  \\\n",
       "0         Fenway            N           N     8    False  ...   \n",
       "1         Fenway            N           N    13    False  ...   \n",
       "2         Fenway            N           N    15    False  ...   \n",
       "3         Fenway            N           N    12    False  ...   \n",
       "4         Fenway            N           N    16    False  ...   \n",
       "\n",
       "   TimeFromFirstStop_p40 TimeFromFirstStop_p50  TimeFromFirstStop_p60  \\\n",
       "0                     31                    40                     44   \n",
       "1                      0                    10                     17   \n",
       "2                     22                    33                     38   \n",
       "3                      0                    14                     18   \n",
       "4                      0                    23                     34   \n",
       "\n",
       "   TimeFromFirstStop_p80  DistanceToFirstStop_p20  DistanceToFirstStop_p40  \\\n",
       "0                     59                      0.0                     25.5   \n",
       "1                     29                      0.0                      0.0   \n",
       "2                     60                      0.0                     43.2   \n",
       "3                     31                      0.0                      0.0   \n",
       "4                     65                      0.0                      0.0   \n",
       "\n",
       "   DistanceToFirstStop_p50  DistanceToFirstStop_p60  DistanceToFirstStop_p80  \\\n",
       "0                     32.3                     51.8                     68.9   \n",
       "1                      9.9                     21.8                     40.4   \n",
       "2                     47.3                     51.1                     65.7   \n",
       "3                     15.6                     22.5                     44.4   \n",
       "4                     26.4                     52.9                     68.9   \n",
       "\n",
       "     City  \n",
       "0  Boston  \n",
       "1  Boston  \n",
       "2  Boston  \n",
       "3  Boston  \n",
       "4  Boston  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace 'kaggle-competitions-project' with YOUR OWN project id here --  \n",
    "PROJECT_ID = 'kaggle-bq-geotag' #\n",
    "#PROJECT_ID='kaggle-competitions-project'\n",
    "\n",
    "from google.cloud import bigquery\n",
    "client = bigquery.Client(project=PROJECT_ID, location=\"US\")\n",
    "dataset = client.create_dataset('bqml_example', exists_ok=True)\n",
    "\n",
    "from google.cloud.bigquery import magics\n",
    "from kaggle.gcp import KaggleKernelCredentials\n",
    "magics.context.credentials = KaggleKernelCredentials()\n",
    "magics.context.project = PROJECT_ID\n",
    "\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# create a reference to our table\n",
    "table = client.get_table(\"kaggle-competition-datasets.geotab_intersection_congestion.train\")\n",
    "\n",
    "# look at five rows from our dataset\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_kg_hide-input": true
   },
   "outputs": [],
   "source": [
    "%load_ext google.cloud.bigquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "mod_names= ['TotalTimeStopped_p20','TotalTimeStopped_p50','TotalTimeStopped_p80',\n",
    "            'DistanceToFirstStop_p20','DistanceToFirstStop_p50','DistanceToFirstStop_p80']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cluster Intersections per City"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery city_df\n",
    "SELECT t.city\n",
    "  FROM `kaggle-competition-datasets.geotab_intersection_congestion.test` t\n",
    "UNION DISTINCT\n",
    "SELECT t.city\n",
    "  FROM `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = list(city_df['city'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done with Boston\n",
      "Done with Philadelphia\n",
      "Done with Atlanta\n",
      "Done with Chicago\n"
     ]
    }
   ],
   "source": [
    "model_changed = False\n",
    "\n",
    "if model_changed:\n",
    "    for c in cities:\n",
    "        sql=\"\"\"DROP MODEL `bqml_example.model_cluster_\"\"\"+c+\"\"\"`\"\"\"\n",
    "        client.query(sql)\n",
    "\n",
    "        print('Dropped',c)\n",
    "    \n",
    "    \n",
    "for c in cities:\n",
    "    sql=\"\"\"CREATE MODEL IF NOT EXISTS `bqml_example.model_cluster_\"\"\"+c+\"\"\"`\n",
    "    OPTIONS(model_type='kmeans',\n",
    "            NUM_CLUSTERS = 10) AS\n",
    "    SELECT\n",
    "        latitude,\n",
    "        longitude\n",
    "    FROM\n",
    "      `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "    WHERE city = '\"\"\"+c+\"\"\"'\n",
    "    UNION ALL\n",
    "    SELECT\n",
    "        latitude,\n",
    "        longitude\n",
    "    FROM\n",
    "      `kaggle-competition-datasets.geotab_intersection_congestion.test` t\n",
    "    WHERE city = '\"\"\"+c+\"\"\"'\"\"\"\n",
    "\n",
    "    client.query(sql)\n",
    "\n",
    "    print('Done with',c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checkout the clusters\n",
    "Using Boston as an example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "_kg_hide-input": true
   },
   "outputs": [],
   "source": [
    "%%bigquery eda_df\n",
    "WITH city_cluster AS (\n",
    "    SELECT (SELECT MIN(d.DISTANCE) FROM UNNEST(NEAREST_CENTROIDS_DISTANCE) d) AS dist_to_cluster_center, \n",
    "           CONCAT(m.city,\"_\",CAST(m.CENTROID_ID AS STRING)) AS city_cluster,\n",
    "           m.* EXCEPT (nearest_centroids_distance, CENTROID_ID) \n",
    "      FROM ML.PREDICT(MODEL `bqml_example.model_cluster_boston`, \n",
    "                   (SELECT t.RowId,\n",
    "                   t.city,\n",
    "                           t.IntersectionId,\n",
    "                           t.Latitude,\n",
    "                           t.Longitude,\n",
    "                           #t.EntryStreetName,\n",
    "                           #t.ExitStreetName,\n",
    "                           t.EntryHeading,\n",
    "                           t.ExitHeading,\n",
    "                           t.Hour,\n",
    "                           t.Weekend,\n",
    "                           t.Month,\n",
    "                           #t.Path,\n",
    "                           t.TotalTimeStopped_p20,\n",
    "                           #t.TotalTimeStopped_p40,\n",
    "                           t.TotalTimeStopped_p50,\n",
    "                           #t.TotalTimeStopped_p60,\n",
    "                           t.TotalTimeStopped_p80,\n",
    "                           #t.TimeFromFirstStop_p20,\n",
    "                           #t.TimeFromFirstStop_p40,\n",
    "                           #t.TimeFromFirstStop_p50,\n",
    "                           #t.TimeFromFirstStop_p60,\n",
    "                           #t.TimeFromFirstStop_p80,\n",
    "                           t.DistanceToFirstStop_p20,\n",
    "                           #t.DistanceToFirstStop_p40,\n",
    "                           t.DistanceToFirstStop_p50,\n",
    "                           #t.DistanceToFirstStop_p60,\n",
    "                           t.DistanceToFirstStop_p80,\n",
    "                           'TRAIN' AS source\n",
    "                     FROM `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "                    WHERE city = 'Boston' \n",
    "                    #  AND rowid in(2209678,2209692)\n",
    "                    UNION ALL\n",
    "                    SELECT t.RowId,\n",
    "                    t.city,\n",
    "                           t.IntersectionId,\n",
    "                           t.Latitude,\n",
    "                           t.Longitude,\n",
    "                           #t.EntryStreetName,\n",
    "                           #t.ExitStreetName,\n",
    "                           t.EntryHeading,\n",
    "                           t.ExitHeading,\n",
    "                           t.Hour,\n",
    "                           t.Weekend,\n",
    "                           t.Month,\n",
    "                           #t.Path,\n",
    "                           null as TotalTimeStopped_p20,\n",
    "                           #null as TotalTimeStopped_p40,\n",
    "                           null as TotalTimeStopped_p50,\n",
    "                           #null as TotalTimeStopped_p60,\n",
    "                           null as TotalTimeStopped_p80,\n",
    "                           #null as TimeFromFirstStop_p20,\n",
    "                           #null as TimeFromFirstStop_p40,\n",
    "                           #null as TimeFromFirstStop_p50,\n",
    "                           #null as TimeFromFirstStop_p60,\n",
    "                           #null as TimeFromFirstStop_p80,\n",
    "                           null as DistanceToFirstStop_p20,\n",
    "                           #null as DistanceToFirstStop_p40,\n",
    "                           null as DistanceToFirstStop_p50,\n",
    "                           #null as DistanceToFirstStop_p60,\n",
    "                           null as DistanceToFirstStop_p80,\n",
    "                           'TEST' AS source\n",
    "                     FROM `kaggle-competition-datasets.geotab_intersection_congestion.test` t\n",
    "                    WHERE city = 'Boston' \n",
    "                    #  AND rowid in(2209678,2209692)\n",
    "                    )) m\n",
    ")\n",
    "SELECT cc.source,\n",
    "       cc.city,\n",
    "       cc.city_cluster, \n",
    "       count(1) cnt, \n",
    "       avg(cc.dist_to_cluster_center) avg_dist_to_cluster_center, \n",
    "       stddev(cc.dist_to_cluster_center) stddev_dist_to_cluster_center, \n",
    "       min(cc.dist_to_cluster_center) min_dist_to_cluster_center, \n",
    "       max(cc.dist_to_cluster_center) max_dist_to_cluster_center,\n",
    "       avg(avg(cc.dist_to_cluster_center)*(count(1)-1)) over(partition by cc.city) avg_dist_to_cluster_center_over_city\n",
    "  FROM city_cluster cc\n",
    " GROUP BY cc.source, \n",
    "          cc.city, \n",
    "          cc.city_cluster;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "_kg_hide-input": true
   },
   "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>source</th>\n",
       "      <th>city</th>\n",
       "      <th>city_cluster</th>\n",
       "      <th>cnt</th>\n",
       "      <th>avg_dist_to_cluster_center</th>\n",
       "      <th>stddev_dist_to_cluster_center</th>\n",
       "      <th>min_dist_to_cluster_center</th>\n",
       "      <th>max_dist_to_cluster_center</th>\n",
       "      <th>avg_dist_to_cluster_center_over_city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_1</td>\n",
       "      <td>42271</td>\n",
       "      <td>0.466131</td>\n",
       "      <td>0.211686</td>\n",
       "      <td>0.039200</td>\n",
       "      <td>1.314788</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_10</td>\n",
       "      <td>50372</td>\n",
       "      <td>0.450509</td>\n",
       "      <td>0.158993</td>\n",
       "      <td>0.013984</td>\n",
       "      <td>0.847548</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_2</td>\n",
       "      <td>66518</td>\n",
       "      <td>0.350224</td>\n",
       "      <td>0.142395</td>\n",
       "      <td>0.014462</td>\n",
       "      <td>0.722907</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_3</td>\n",
       "      <td>45069</td>\n",
       "      <td>0.489135</td>\n",
       "      <td>0.182818</td>\n",
       "      <td>0.087381</td>\n",
       "      <td>0.829767</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_4</td>\n",
       "      <td>22680</td>\n",
       "      <td>0.464697</td>\n",
       "      <td>0.176813</td>\n",
       "      <td>0.079621</td>\n",
       "      <td>0.888461</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_5</td>\n",
       "      <td>69299</td>\n",
       "      <td>0.456774</td>\n",
       "      <td>0.175848</td>\n",
       "      <td>0.072452</td>\n",
       "      <td>0.893111</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_6</td>\n",
       "      <td>30369</td>\n",
       "      <td>0.462176</td>\n",
       "      <td>0.208277</td>\n",
       "      <td>0.014275</td>\n",
       "      <td>0.974957</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_7</td>\n",
       "      <td>27394</td>\n",
       "      <td>0.327744</td>\n",
       "      <td>0.149575</td>\n",
       "      <td>0.015647</td>\n",
       "      <td>0.758689</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_8</td>\n",
       "      <td>17778</td>\n",
       "      <td>0.374388</td>\n",
       "      <td>0.207027</td>\n",
       "      <td>0.023661</td>\n",
       "      <td>0.920327</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>TEST</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_9</td>\n",
       "      <td>34092</td>\n",
       "      <td>0.668244</td>\n",
       "      <td>0.308577</td>\n",
       "      <td>0.037550</td>\n",
       "      <td>1.563004</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_1</td>\n",
       "      <td>21176</td>\n",
       "      <td>0.469175</td>\n",
       "      <td>0.216125</td>\n",
       "      <td>0.039200</td>\n",
       "      <td>1.314788</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_10</td>\n",
       "      <td>18963</td>\n",
       "      <td>0.373282</td>\n",
       "      <td>0.147626</td>\n",
       "      <td>0.105447</td>\n",
       "      <td>0.702504</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_2</td>\n",
       "      <td>33332</td>\n",
       "      <td>0.346793</td>\n",
       "      <td>0.147453</td>\n",
       "      <td>0.014462</td>\n",
       "      <td>0.766661</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_3</td>\n",
       "      <td>7836</td>\n",
       "      <td>0.405513</td>\n",
       "      <td>0.175154</td>\n",
       "      <td>0.124338</td>\n",
       "      <td>0.799498</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_4</td>\n",
       "      <td>11615</td>\n",
       "      <td>0.461237</td>\n",
       "      <td>0.175275</td>\n",
       "      <td>0.079621</td>\n",
       "      <td>0.888461</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_5</td>\n",
       "      <td>35770</td>\n",
       "      <td>0.457464</td>\n",
       "      <td>0.176007</td>\n",
       "      <td>0.072452</td>\n",
       "      <td>0.893111</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_6</td>\n",
       "      <td>15723</td>\n",
       "      <td>0.461670</td>\n",
       "      <td>0.210807</td>\n",
       "      <td>0.014275</td>\n",
       "      <td>0.974957</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_7</td>\n",
       "      <td>14064</td>\n",
       "      <td>0.327678</td>\n",
       "      <td>0.149007</td>\n",
       "      <td>0.015647</td>\n",
       "      <td>0.758689</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_8</td>\n",
       "      <td>6107</td>\n",
       "      <td>0.258794</td>\n",
       "      <td>0.215106</td>\n",
       "      <td>0.035895</td>\n",
       "      <td>0.837815</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>TRAIN</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Boston_9</td>\n",
       "      <td>17464</td>\n",
       "      <td>0.669781</td>\n",
       "      <td>0.310499</td>\n",
       "      <td>0.037550</td>\n",
       "      <td>1.563004</td>\n",
       "      <td>13049.52078</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   source    city city_cluster    cnt  avg_dist_to_cluster_center  \\\n",
       "8    TEST  Boston     Boston_1  42271                    0.466131   \n",
       "2    TEST  Boston    Boston_10  50372                    0.450509   \n",
       "5    TEST  Boston     Boston_2  66518                    0.350224   \n",
       "1    TEST  Boston     Boston_3  45069                    0.489135   \n",
       "6    TEST  Boston     Boston_4  22680                    0.464697   \n",
       "0    TEST  Boston     Boston_5  69299                    0.456774   \n",
       "3    TEST  Boston     Boston_6  30369                    0.462176   \n",
       "7    TEST  Boston     Boston_7  27394                    0.327744   \n",
       "9    TEST  Boston     Boston_8  17778                    0.374388   \n",
       "4    TEST  Boston     Boston_9  34092                    0.668244   \n",
       "15  TRAIN  Boston     Boston_1  21176                    0.469175   \n",
       "16  TRAIN  Boston    Boston_10  18963                    0.373282   \n",
       "13  TRAIN  Boston     Boston_2  33332                    0.346793   \n",
       "14  TRAIN  Boston     Boston_3   7836                    0.405513   \n",
       "18  TRAIN  Boston     Boston_4  11615                    0.461237   \n",
       "10  TRAIN  Boston     Boston_5  35770                    0.457464   \n",
       "12  TRAIN  Boston     Boston_6  15723                    0.461670   \n",
       "19  TRAIN  Boston     Boston_7  14064                    0.327678   \n",
       "17  TRAIN  Boston     Boston_8   6107                    0.258794   \n",
       "11  TRAIN  Boston     Boston_9  17464                    0.669781   \n",
       "\n",
       "    stddev_dist_to_cluster_center  min_dist_to_cluster_center  \\\n",
       "8                        0.211686                    0.039200   \n",
       "2                        0.158993                    0.013984   \n",
       "5                        0.142395                    0.014462   \n",
       "1                        0.182818                    0.087381   \n",
       "6                        0.176813                    0.079621   \n",
       "0                        0.175848                    0.072452   \n",
       "3                        0.208277                    0.014275   \n",
       "7                        0.149575                    0.015647   \n",
       "9                        0.207027                    0.023661   \n",
       "4                        0.308577                    0.037550   \n",
       "15                       0.216125                    0.039200   \n",
       "16                       0.147626                    0.105447   \n",
       "13                       0.147453                    0.014462   \n",
       "14                       0.175154                    0.124338   \n",
       "18                       0.175275                    0.079621   \n",
       "10                       0.176007                    0.072452   \n",
       "12                       0.210807                    0.014275   \n",
       "19                       0.149007                    0.015647   \n",
       "17                       0.215106                    0.035895   \n",
       "11                       0.310499                    0.037550   \n",
       "\n",
       "    max_dist_to_cluster_center  avg_dist_to_cluster_center_over_city  \n",
       "8                     1.314788                           13049.52078  \n",
       "2                     0.847548                           13049.52078  \n",
       "5                     0.722907                           13049.52078  \n",
       "1                     0.829767                           13049.52078  \n",
       "6                     0.888461                           13049.52078  \n",
       "0                     0.893111                           13049.52078  \n",
       "3                     0.974957                           13049.52078  \n",
       "7                     0.758689                           13049.52078  \n",
       "9                     0.920327                           13049.52078  \n",
       "4                     1.563004                           13049.52078  \n",
       "15                    1.314788                           13049.52078  \n",
       "16                    0.702504                           13049.52078  \n",
       "13                    0.766661                           13049.52078  \n",
       "14                    0.799498                           13049.52078  \n",
       "18                    0.888461                           13049.52078  \n",
       "10                    0.893111                           13049.52078  \n",
       "12                    0.974957                           13049.52078  \n",
       "19                    0.758689                           13049.52078  \n",
       "17                    0.837815                           13049.52078  \n",
       "11                    1.563004                           13049.52078  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eda_df.sort_values(by=['source','city_cluster']).head(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "_kg_hide-input": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]), <a list of 10 Text xticklabel objects>)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZUAAAEtCAYAAADAwv0jAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3XmYFOW5/vHvMzPsi+wKjAoquKGAjgpR4xYQ44IaNRoNaDBoNIpJTNQYoz+jORrNMccTg3EFjYJbPC5BCaBEjbgMEVlEBBV0AAUBkZ2BeX5/1DvQAzMwS3dXzfT9ua6+uuvtt6vu7ml4uuqtxdwdERGRdMiLO4CIiDQcKioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2BXEHyLYOHTp4t27d4o4hIlJvTJ069St371idvjlXVLp160ZxcXHcMURE6g0zW1Ddvtr8JSIiaaOiIiIiaZOxomJm+5rZtJTbN2Z2lZm1M7MJZjY33LcN/c3M7jazeWY23cwOSZnX0NB/rpkNTWk/1MxmhNfcbWaWqfcjIiI7l7ExFXefA/QBMLN8YCHwLHAtMMndbzOza8P0NcBJQI9wOwIYCRxhZu2AG4EiwIGpZva8u68IfYYDbwHjgEHAS5l6TyLScJWWllJSUsL69evjjhKbpk2bUlhYSKNGjWo9j2wN1J8AfOzuC8xsMHBsaB8NTCYqKoOBRzy6wMtbZtbGzDqHvhPcfTmAmU0ABpnZZKC1u08J7Y8Ap6OiIiK1UFJSQqtWrejWrRu5uNHD3Vm2bBklJSV079691vPJ1pjKucCY8HhXd18MEO47hfauwOcprykJbTtqL6mkPSNWb9jEPa/O45dPvc+ED77M1GJEJCbr16+nffv2OVlQAMyM9u3b13lNLeNrKmbWGDgNuG5nXStp81q0V5ZhONFmMvbYY4+dxKjcj0cXM+WTZQA8NbWEO8/uzVmHFtZqXiKSTLlaUMql4/1nY03lJOA/7l7+8/7LsFmLcL8ktJcAu6e8rhBYtJP2wkrat+Pu97l7kbsXdexYreN3KliwbM2WglJu7Duf1Xg+IiINXTaKynls3fQF8DxQvgfXUOC5lPYhYS+wfsDKsHlsPDDQzNqGPcUGAuPDc6vMrF/Y62tIyrzSqmWTAgryKlbwNs1rP5AlItJQZbSomFlzYADw95Tm24ABZjY3PHdbaB8HfALMA+4HLgMIA/S/A94Nt5vLB+2BnwAPhNd8TIYG6du3bMLwb++1ZbplkwKuOL5HJhYlIrLFpk2b4o5QYxkdU3H3tUD7bdqWEe0Ntm1fBy6vYj4PAQ9V0l4M9EpL2J341aD9OOXgLsxftoZv7d2eNs0bZ2OxIlKPrFmzhnPOOYeSkhI2b97MDTfcQIcOHbj66qvZtGkThx12GCNHjqRJkyZbThnVoUMHiouLufrqq5k8eTI33XQTixYtYv78+XTo0IFHH32Ua665hvHjx2Nm/PjHP+aKK65g6tSp/PznP2f16tV06NCBUaNG0blz57g/gtw791ddHNClNQd0aR13DBFJqJdffpkuXbrwj3/8A4CVK1fSq1cvJk2aRM+ePRkyZAgjR47kqquu2uF8pk6dyhtvvEGzZs0YOXIkn376Ke+99x4FBQUsX76c0tJSrrjiCp577jk6duzIE088wfXXX89DD2332zvrdJoWEZE0Oeigg5g4cSLXXHMNr7/+OvPnz6d79+707NkTgKFDh/Laa6/tdD6nnXYazZo1A2DixIlceumlFBRE6wDt2rVjzpw5zJw5kwEDBtCnTx9uueUWSkpKdjTLrNGaikgDsWHTZhrn5+X8brFx6tmzJ1OnTmXcuHFcd911DBw4sMq+BQUFlJWVAWx3bEiLFi22PHb37f6m7s6BBx7IlClT0pg+PbSmIlLPrVxXysWj32W/G17myNteYdJsHZwbl0WLFtG8eXMuuOACrr76at58803mz5/PvHnzAHj00Uc55phjgOgyHFOnTgXgmWeeqXKeAwcO5N57790yaL98+XL23Xdfli5duqWolJaWMmvWrEy+tWpTURGp5/408SMmzl6COyxauZ6rxk5jzYb6t9dQQzBjxgwOP/xw+vTpw6233sott9zCww8/zNlnn81BBx1EXl4el156KQA33ngjI0aM4OijjyY/P7/KeV588cXsscceHHzwwfTu3ZvHH3+cxo0b8/TTT3PNNdfQu3dv+vTpw5tvvpmtt7lDFu10lTuKiopcF+mShuSskW9SvGBFhbYXrziKXl13iSlR/TR79mz233//uGPErrLPwcymuntRdV6vNRWReq7fXhX22qd9i8b02LVlTGkk12mgXqSe++nx+7Bi7UZemvkFu7drzm9POYAmBVVvThHJJBUVkXquaaN8bj3jIG4946C4o4ho85eIiKSPioqIiKSNioqIiKSNxlRERGqorMx5/v1FPPjGpyxeuY7OuzRj2FHdOa13F/LyandGg2XLlnHCCdG5dr/44gvy8/Mpv/7T+++/T+/evbf0Pffcc7n22mt58cUXueGGGygrK6O0tJQRI0bw1Vdf8dRTTwHRcTMHHRSNtf3oRz/iyiuvrMvbrhYdpyIiQvWPUykrcy7921TemPcVazdu3tLerFE+R/fowL0XHFrrwlLupptuomXLllx99dUAtGzZktWrV1foU1payp577sk777xDYWEhGzZsYP78+ey7775b+lT2up3RcSoiIln0/PuLtisoAOtKN/P63K94YXqlF6BNu1WrVrFp0ybat4+OU2rSpEmFghIXFRURkRp48I1Ptyso5daVbuaB1z9N+zLXrVtHnz59ttyeeOIJ2rVrx2mnncaee+7Jeeedx2OPPbblBJVx0piKiEgNLF65rk7P10azZs2YNm3adu0PPPAAM2bMYOLEidx5551MmDCBUaNGpX35NaE1FRGRGui8S7M6PZ9uBx10ED/72c+YMGHCDs92nC0qKiIiNTDsqO40a1T5aXCaNcrn4qO7ZyXH6tWrmTx58pbpadOmseeee2Zl2TuizV8iIjVwWu8ujJuxmNfnfsW60u33/jr14C5pX2b5mEq5QYMGcf311/OHP/yBSy65hGbNmtGiRYvYN32BioqISI3k5Rn3XnAoL0xfxAOvbz1O5eKju3PqwbU/TiXVTTfdVGF68+bKdwwYN27cDudT092J00FFRUSkhvLyjMF9ujK4T9e4oyRORsdUzKyNmT1tZh+a2Wwz629m7cxsgpnNDfdtQ18zs7vNbJ6ZTTezQ1LmMzT0n2tmQ1PaDzWzGeE1d5suzi0iEqtMD9T/D/Cyu+8H9AZmA9cCk9y9BzApTAOcBPQIt+HASAAzawfcCBwBHA7cWF6IQp/hKa8blOH3IyIiO5CxomJmrYFvAw8CuPtGd/8aGAyMDt1GA6eHx4OBRzzyFtDGzDoDJwIT3H25u68AJgCDwnOt3X2KR+eaeSRlXiIiEoNMrqnsBSwFHjaz98zsATNrAezq7osBwn2n0L8r8HnK60tC247aSyppFxGRmGSyqBQAhwAj3b0vsIatm7oqU9l4iNeiffsZmw03s2IzK166dOmOU4uISK1lsqiUACXu/naYfpqoyHwZNl0R7pek9N895fWFwKKdtBdW0r4dd7/P3Yvcvaj8VNIiIrVWVgbTn4S/HgN37BPdT38yaq+lZcuWbTm312677UbXrl23TJsZffr0oVevXpx66ql8/fXXFV5711130bRpU1auXLmlbfLkyZxyyikAjBo1iry8PKZPn77l+V69ejF//vxa561KxoqKu38BfG5m5afNPAH4AHgeKN+DayjwXHj8PDAk7AXWD1gZNo+NBwaaWdswQD8QGB+eW2Vm/cJeX0NS5iUikhllZfDEBfDCCFg8DdYsje5fGAFP/rDWhaV9+/ZMmzaNadOmcemll/Kzn/1sy3SLFi2YNm0aM2fOpF27dtxzzz0VXjtmzBgOO+wwnn322SrnX1hYyK233lqrbDWR6b2/rgAeM7PpQB/g98BtwAAzmwsMCNMA44BPgHnA/cBlAO6+HPgd8G643RzaAH4CPBBe8zHwUobfj4jkuplPwyevQunaiu2la+HjV2BmZs+/1b9/fxYuXLhl+uOPP2b16tXccsstjBkzpsrXnXLKKcyaNYs5c+ZkNF9GD35092lAZRd2OaGSvg5cXsV8HgIeqqS9GOhVx5giItU35Z7tC0q50rUw5c9w8NkZWfTmzZuZNGkSw4YN29I2ZswYzjvvPI4++mjmzJnDkiVL6NSp03avzcvL41e/+hW///3vGT169HbPp4tOKCkiUhPfLKzb87VQfu6v9u3bs3z5cgYMGLDlubFjx3LuueeSl5fHmWeeueVSwpX5wQ9+wFtvvcWnn6b/mi/lVFRERGqi9U6OXNjZ87VQfj2VBQsWsHHjxi1jKtOnT2fu3LkMGDCAbt26MXbs2B1uAisoKOAXv/gFt99+e9ozllNRERGpif6XQ6PmlT/XqDn0/2nGFr3LLrtw9913c+edd1JaWsqYMWO46aabmD9/PvPnz2fRokUsXLiQBQsWVDmPCy+8kIkTJ5KpwytUVEREaqLXWbDXcdsXlkbNYe/jodf3Mrr4vn370rt3b8aOHcvYsWM544wzKjx/xhlnMHbs2Cpf37hxY6688kqWLFlSZZ+6sGh8PHcUFRV5cXFx3DFEJGFmz57N/vvvX73OZWXRXl5T/hyNobTuGq2h9Poe5NXv3+qVfQ5mNtXdK9vpajs69b2ISE3l5UV7eGVoL6/6rH6XVBERSRQVFRGRINeGA7aVjvevoiIiAjRt2pRly5blbGFxd5YtW0bTpk3rNB+NqYiIEJ0bq6SkJGO72tYHTZs2pbCwcOcdd0BFRUQEaNSoEd27d487Rr2nzV8iIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KioiIpI2KipSK2s2bOKzZWvjjiEiCZPRomJm881shplNM7Pi0NbOzCaY2dxw3za0m5ndbWbzzGy6mR2SMp+hof9cMxua0n5omP+88FrL5PuRyJh3PuOwWyfy7Tte5ZT/fZ0l36yPO5KIJEQ21lSOc/c+KZeivBaY5O49gElhGuAkoEe4DQdGQlSEgBuBI4DDgRvLC1HoMzzldYMy/3Zy24o1G7nx+Vms3bgZgJkLv+FPk+bGnEpEkiKOzV+DgdHh8Wjg9JT2RzzyFtDGzDoDJwIT3H25u68AJgCDwnOt3X2KRxdAeCRlXpIhJSvWsXFTWYW2j5esjimNiCRNpouKA/80s6lmNjy07eruiwHCfafQ3hX4POW1JaFtR+0llbRvx8yGm1mxmRXn8rUS0mH/zq3o2qZZhbYBB+waUxoRSZpMX0/lSHdfZGadgAlm9uEO+lY2HuK1aN++0f0+4D6AoqKi3LysW5oU5Ocx+keHc+f4OXy+Yi3fPagzPzpS16AQkUhGi4q7Lwr3S8zsWaIxkS/NrLO7Lw6bsJaE7iXA7ikvLwQWhfZjt2mfHNoLK+kvGbZPp5bc+8ND444hIgmUsc1fZtbCzFqVPwYGAjOB54HyPbiGAs+Fx88DQ8JeYP2AlWHz2HhgoJm1DQP0A4Hx4blVZtYv7PU1JGVeIiISg0yuqewKPBv28i0AHnf3l83sXeBJMxsGfAacHfqPA74LzAPWAhcBuPtyM/sd8G7od7O7Lw+PfwKMApoBL4WbiIjExKIdp3JHUVGRFxcXxx1DRKTeMLOpKYeF7JCOqBcRkbRRURERkbRRURERkbRRURERkbRRURERkbRRURERkbRRURERkbTJ9Lm/JM2eKv6cZ99bSKdWTfjp8T3Yp1PLuCOJiGyholKPPP/+In759PQt029+vIzXfnUcTRvlx5hKRGQrbf6qR16asbjC9JJVG5i6YEVMaUREtqeiUo8Utq14HRMztru2iYhInFRU6pFLjtmb/XZrBUB+nnHFcfvQrUOLmFOJiGylMZV6pEPLJrw04mhmL15Fh5aN6dS6adyRREQqUFGpZ8yMA7q0jjuGiEiltPlLRETSRkVFRCQD1mzYxFerN8QdI+u0+UtEJM1GTv6Y/5n0EetLy/jO/p343/MOoVnj3DieTGsqIiJpNG/Jam5/+UPWl5YBMHH2EkZPmR9rpmxSURERSaO5X67aru2jL7Zva6hUVERE0uiIvdrTfJtNXcfu1ymmNNmnMRURkTRq16IxD194GHdN/Iiv15ZyTtHunNa7S9yxskZFRUQkzY7Yqz1jh/ePO0YsMr75y8zyzew9M3sxTHc3s7fNbK6ZPWFmjUN7kzA9LzzfLWUe14X2OWZ2Ykr7oNA2z8yuzfR7ERGRHcvGmMoIYHbK9O3AXe7eA1gBDAvtw4AV7r4PcFfoh5kdAJwLHAgMAv4SClU+cA9wEnAAcF7oKyIiMcloUTGzQuBk4IEwbcDxwNOhy2jg9PB4cJgmPH9C6D8YGOvuG9z9U2AecHi4zXP3T9x9IzA29BURkZhkek3lT8CvgLIw3R742t03hekSoGt43BX4HCA8vzL039K+zWuqat+OmQ03s2IzK166dGld35OIiFQhY0XFzE4Blrj71NTmSrr6Tp6rafv2je73uXuRuxd17NhxB6lFRKQuMrn315HAaWb2XaAp0JpozaWNmRWEtZFCYFHoXwLsDpSYWQGwC7A8pb1c6muqahcRkRhUa03FzEZUpy2Vu1/n7oXu3o1ooP0Vdz8feBU4K3QbCjwXHj8fpgnPv+LuHtrPDXuHdQd6AO8A7wI9wt5kjcMynq/O+xERkcyo7uavoZW0XVjLZV4D/NzM5hGNmTwY2h8E2of2nwPXArj7LOBJ4APgZeByd98c1nR+Cown2rvsydBXRERiYtHKQBVPmp0H/AA4Cng95alWwGZ3/05m46VfUVGRFxcXxx1DRKTeMLOp7l5Unb47G1N5E1gMdAD+mNK+Cpheu3giItJQ7bCouPsCYAGQm+cbEBGRGqnuQP2Z4bQqK83sGzNbZWbfZDqciIjUL9XdpfgPwKnuPnunPUVEJGdVd++vL1VQgk0b404gIpJY1V1TKTazJ4D/AzaUN7r73zOSKok+fgVeuAq+/gx6DIQz7oXm7eJOJSKSKNVdU2kNrAUGAqeG2ymZCpU4pevh6WHw9QLAYe54eOWWuFOJiCROdddU8oAR7v41gJm1peIuxg3bivmwbnnFtoVTK+0qIpLLqrumcnB5QQFw9xVA38xESqD2e0OrzhXbuh0VTxYRkQSrblHJC2snAJhZO3LpUsT5jeD7j0HXImjaBvpeAMddH3cqEZHEqW5h+CPwppk9TXR6+XOAWzOWKokKD4UfT4o7hSTMzIUruWP8HL5YuZ7T+nThsmP3Jrq2nEhuqlZRcfdHzKyY6KqNBpzp7h9kNJlIwq0v3czQh95h2ZpoN/M7xs+hVdMChvTvFm8wkRhVexNWKCIqJCLB+59/vaWglHvlwyXxFJUvP4A5/4C23eGAwdEmW5EY5M64iEiade/YgoI8Y1PZ1jN999y1VfaDfPwKPHY2lIWrdM96Fs59LPs5RMj8NepFGqxOrZpy02kH0qJxPgCHd2/HZcfunf0gU/6ytaAAfPgiLPs4+zlE0JqKSJ1c0G9PzjykK9+s28RuuzSNJ4RV8tuwsjaRLNA3T6SOmjcuiK+gAPS/HPJSxlAOOB3adY8vj+Q0ramI1Hd7HQOXTYE546BtN9gvd86gJMmjoiLSEHToAR1GxJ1CRJu/REQkfVRUREQkbVRUpN5atb6Ul2Ys5v3Pv955ZxHJiowVFTNrambvmNn7ZjbLzP5faO9uZm+Ha94/YWaNQ3uTMD0vPN8tZV7XhfY5ZnZiSvug0DbPzK7N1HuR5Jn75Sq+/YdX+clj/2HwPf/m18/OiDuSiJDZNZUNwPHu3hvoAwwys37A7cBd7t4DWAEMC/2HASvcfR/grtAPMzsAOBc4EBgE/MXM8s0sH7gHOAk4ADgv9JUcMPJfH7NibemW6cff/oz5X62JMZGIQAaLikdWh8lG4eZEJ6V8OrSPBk4PjweHacLzJ1h0utfBwFh33+DunwLzgMPDbZ67f+LuG4Gxoa/kgG/WlW7ftn77NhHJroyOqYQ1imnAEmAC8DHwtbuXn1OiBOgaHncFPgcIz68E2qe2b/OaqtoryzHczIrNrHjp0qXpeGsSs+8ftkeF6QO7tOagrrvElEZEymX0OBV33wz0MbM2wLPA/pV1C/eVXYTCd9BeWUH0Stpw9/uA+wCKiooq7SP1y4ADduVvw47ghfcX0aVNM4Z+a09dx0QkAbJy8KO7f21mk4F+QBszKwhrI4XAotCtBNgdKDGzAmAXYHlKe7nU11TVLjngqB4dOKpHh7hjiEiKTO791TGsoWBmzYDvALOBV4GzQrehwHPh8fNhmvD8K+7uof3csHdYd6AH8A7wLtAj7E3WmGgw//lMvR8REdm5TK6pdAZGh7208oAn3f1FM/sAGGtmtwDvAQ+G/g8Cj5rZPKI1lHMB3H2WmT1JdIGwTcDlYbMaZvZTYDyQDzzk7rMy+H5ERGQnLFoZyB1FRUVeXFwcdwwRyaAFy9bQsVUTmjfW6Q3TwcymuntRdfrqExeRBqNkxVqGjSpmzperaNWkgN+d3ovT+1a6U6hkiE7TIiINxh3j5zDny1UArNqwid/830zWbty0k1dJOqmoiEiD8fHS1RWmV2/YxBcr18eUJjepqIhIg/Gd/XetML13xxZ079AipjS5SWMqItJg/PS4fXCHCR98SfeOLbjmxP10UGyWae8vERHZoZrs/aXNXyIikjYqKiIikjYqKiIikjYqKiIikjYqKiIikjYqKiIikjYqKiIikjYqKiIikjY6ol5E0mLl2lIef+czlq7awOl9u3BwYZu4I0kMVFREpM42lznfv28KH34RnSH4kSnzefzH/Ti8e7t4g0nWafOXiNTZu/OXbykoAJvKnDHvfBZjIomLioqI1FnzxvnVapOGT0VFROrs4MI2DDxg62nn2zZvxLCjuseYSOKiMRURSYu//vBQ/j1vGUtXr+f4fXdll+aN4o4Unw/HwaSbYd1y6PtDOP43kCOn4FdREZG0MDOO6tEh7hjx+2YxPDUUNm+Mpl+/E9ruCYcMiTdXlmjzl4hIOpW8s7WglJv/RjxZYpCxomJmu5vZq2Y228xmmdmI0N7OzCaY2dxw3za0m5ndbWbzzGy6mR2SMq+hof9cMxua0n6omc0Ir7nbdIk3EYlb595g2/zX2uWQyvs2QJlcU9kE/MLd9wf6AZeb2QHAtcAkd+8BTArTACcBPcJtODASoiIE3AgcARwO3FheiEKf4SmvG5TB9yMisnNtu8Gp/wPN20NeI+h7ARw2LO5UWZOxMRV3XwwsDo9XmdlsoCswGDg2dBsNTAauCe2PeHR947fMrI2ZdQ59J7j7cgAzmwAMMrPJQGt3nxLaHwFOB17K1HsSEamWQ4ZAnwugbBMUNI47TVZlZUzFzLoBfYG3gV1DwSkvPJ1Ct67A5ykvKwltO2ovqaS9suUPN7NiMyteunRpXd+OAGzaCGu+ijuFSHLl5eVcQYEsFBUzawk8A1zl7t/sqGslbV6L9u0b3e9z9yJ3L+rYsePOIsvOTH8K/tgT7tgbHj4Z1iyLO5GIJERGi4qZNSIqKI+5+99D85dhsxbhfkloLwF2T3l5IbBoJ+2FlbRLJq37Gp6/AtatiKYXvAH/ui3eTCKSGJnc+8uAB4HZ7v7fKU89D5TvwTUUeC6lfUjYC6wfsDJsHhsPDDSztmGAfiAwPjy3ysz6hWUNSZmXZMryT2DTuoptX34QTxYRSZxMHvx4JPBDYIaZTQttvwZuA540s2HAZ8DZ4blxwHeBecBa4CIAd19uZr8D3g39bi4ftAd+AowCmhEN0GuQPtN27QUtd4PVX2xt2+f4+PKISJXmLVnN63OX0nPXVhy5T3YOTLVoZ6vcUVRU5MXFxXHHqN8WT4cJv4WvF8ABp8Nx10N+jp6cYXMpzH4BvlkE+50M7XS+K0mGiR98ySV/m8rmsuj/+GFHdeeGUw6o1bzMbKq7F1Wrr4qKSB08djbM/Wf0uKAZXPgPKDw03kwiwJl/+Tf/+ezrLdON8o3i3wxgl2Y1PydbTYqKTtMiUltfzNxaUCAaa3rrnvjyiKQo3VxxhWFzmVNWlvmVCBUVkdrysuq1icTgR0d1qzB9et+utG2R+eNmcnRDuEgadD4Yuh8Dn/4rms5vDEdcGm8mkeCMvoV0bdOcyXOW0HPXVpxycOesLFdFReq3tcuhccv4jlw+/ymY+Qx8sxD2Hwwde8aTQ6QSh3dvx+Hd22V1mSoqUj+t+xqevgg+fgWatYUT/wv6nJf9HAVNoM8Psr9ckYTSmIrUT6//MSooEB3d/8IInS5GJAFUVKR++nJmxenNG+Crj+LJIiJbqKhI/bT3NkfxN28PXfrEk0VEttCYitRP/S6LNnvNfAZaF8KAm6FRs7hTieQ8FRWpn/Ly4YTfRjeRchtWwYs/hznjoP3ecPJ/Q2G1DgSXNNHmLxFpOF65BWY8CRtXw+L34YkLYPOmuFPlFBUVEWk4Fvy74vSqxdHlGiRrVFREpOEoPKzidIuO0LZbLFFylYqKiDQcJ/wW9j0ZLA/a7wNnPZyT14mPkwbqRaThaNYWznscysogT7+Z46BPXUQaHhWU2OiTF5H0KCuDeRNh2pjoRJ+Sk7T5S0TS44nzo+NDINoMNWwCdOgRbybJOq2piEjdLfzP1oIC0dkO3vpLfHkkNioqIlJ3pWu3b9tYSZs0eCoqIlJ3e/SHXXttnc4rgEMvjC2OxCdjRcXMHjKzJWY2M6WtnZlNMLO54b5taDczu9vM5pnZdDM7JOU1Q0P/uWY2NKX9UDObEV5zt5lZpt6LiOxEXj5c+I/oxJ79fwoXT4I9+8edSmKQyTWVUcCgbdquBSa5ew9gUpgGOAnoEW7DgZEQFSHgRuAI4HDgxvJCFPoMT3ndtssSkWxq1gaOHAEn3qrLEOSwjBUVd38N2Ha/wsHA6PB4NHB6SvsjHnkLaGNmnYETgQnuvtzdVwATgEEzhfpNAAAS1klEQVThudbuPsXdHXgkZV4iIhKTbI+p7OruiwHCfafQ3hX4PKVfSWjbUXtJJe2VMrPhZlZsZsVLly6t85sQEZHKJWWgvrLxEK9Fe6Xc/T53L3L3oo4dO9YyooiI7Ey2i8qXYdMV4X5JaC8Bdk/pVwgs2kl7YSXtIiISo2wXleeB8j24hgLPpbQPCXuB9QNWhs1j44GBZtY2DNAPBMaH51aZWb+w19eQlHmJiEhMMnaaFjMbAxwLdDCzEqK9uG4DnjSzYcBnwNmh+zjgu8A8YC1wEYC7Lzez3wHvhn43u3v54P9PiPYwawa8FG4N36z/g+lPQMtOcNTPdK0IEUkUi3aeyh1FRUVeXFwcd4zamf1CdHnUcq0L4cr3dL0IEckoM5vq7kXV6ZuUgXqpjhlPVZz+pgQ+mxJPFhGRSqio1CetK9lrunWX7OcQEamCikp98q0rod3eYcKi02Ho1OIikiC6nkp90roz/PRdKCmGlh2h3V5xJxKRJPtmEXzyL+i0H3Tpm5VFqqjUN3n5sMcRcacQkaT7ZDI8dg5s3hBNH3MNHPfrjC9Wm79ERBqiybdvLSgAb/wJ1n+T8cWqqIiINEQbV1Wc3rwxumWYioqISENUNKzi9P6nQosOGV+sxlRERBqioougVWeY+0/otD8cMiQri1VRERFpqPYdFN2ySJu/REQkbVRUREQkbVRUREQkbVRUREQkbVRUREQkbVRUREQkbVRUREQkbVRUREQkbXLucsJmthRYUIdZdAC+SlOcukhCjiRkgGTkSEIGSEaOJGSAZORIQgaoe4493b1jdTrmXFGpKzMrru61mht6jiRkSEqOJGRISo4kZEhKjiRkyHYObf4SEZG0UVEREZG0UVGpufviDhAkIUcSMkAyciQhAyQjRxIyQDJyJCEDZDGHxlRERCRttKYiIiJpo6IiIiJpo6IiIiJpo6IiIiLbMTOrzetUVDLIzHY1s70SkKNp3BkqU9svbR2W183MzszmMqvI0WSb6az/OzSzxtleZmXMrG0CMuxrZjfHnSMJzKyVmbUAcHevzb9RFZUMMbP9gJeALjHn2Bf4u5n9l5ldH2eWkGcPM+tnZr08i7semtmBwPNAKzPLT2nPdmHbFxhrZr80s2sB3L0sm4XFzHoCj5rZ78xsRLaWW0mOjsAnZnZZjBn2BZ4CTjWzQ2LMsZeZfSuOHxgpGfYDJgB/MbMHICosNZ2PikoGmNk+wDPAXe7+Rrb/40rJ0RMYC7wMvAPsZ2bN48gS8uwLvAsMAf5hZpeb2d5ZWG4T4Fqiv8dooKz8udr+Gqtljt2Jvhf/BCYBR5jZsyFHVgpL+Bs8C0wBPgZ6m9lJmV5uFVoBK4Cfx1FYwmfxCHA/8C/gxGxnCDn2Aj4MOWIpLOG7ORZ4ELgZaGJmLVOer3amgvTHy21mVgAMBT4HngvNN5rZaqDA3W/LUo4mwMXA3e7+sJl1Aq4HfmBm5u73ZyNHSp484FzgDne/08z6AZcArc3sKXefl8HFbwz3H4X7x8PfY1fgPHdfY2Z57l5W+cvTph0w2d1HApjZX4G/mdkz7v69TC8/bPK6FLjX3f83bBbtBexPtFadVe7+iZn9CZgD3G1mq4iKbiN3X5nJZYcfV7cBI919lJkVAU+Y2Wvu/u9MLnubHHlAf+AqwIHfAL83szey8H1M1QF4zd3vD9+LQ4BfmFkXd78k/Oix6qy5aE0lzdx9E9Hq9NvANWY2DdgNWAP0M7ORWcqxAfhjKCj5wF3Ap8B64MTy1dtsCf9AlhH9Om/h7m8BfwT2AU6CzGyKSvmHUAzsHTYBLgR+Dawj2iRGlv4BNwJOM7PeYboL0a9CM7NLMr1wd98IPEC05oq7rwfeBPpmetnbSvnl25fo/6FvA78jWnP5dqaX7+5rgctDQcl392KitZa+2+TLdI4yok1OY8OPjZeJvptHZ3ncy4FjzOxXwFtEWxReBLqZWfn3pVqbwlRUMsDdpxP94moLvOzul4YvzG+BRln8wn4ZHuYDj7j72e7+N+D/Ef1Hlu011ZeBEqIvbxN3nwncC1xlZodnYowlZZ4fAlcAfYBR7r7U3b8PbDSzbulebhVZioE7gWfN7H5gBPAE8CSwS5YyzHL3uSlN64l+9BDGui7MRg6i/8QARgG7hO/qJqIfHu2zlGFxuC//QTEHuNTM2pX/Ms9GCHdfAnwdHv+JrYWlu5kdWz72luEM04g2EX9ItFn0InefCgwCVofxr2pRUcmQUFhuIfr1Va4Q2INosDhb2/HN3Te6+/hKcrTIZI5KBh8/BRYRbbs+3sxauvu7RNtyO2UqB0B4/7cBBwP9zayTmfUBugKbM7ls2LoW5u53A4OB24EB4T/TfGB3MyvI5vciPFwIfBDG30YSbbbNuJRivwb4rZnNB+4AvkW0ubh7tjKk3I8lWnO7Iay9ZG1HktQxtVBYHgGeJvrR8UEml53y3XwJmAhsAMrXpo8C9qImQyXurlsWbsCxwHTguzHnOAF4P9M5whdxIzArfDEbhfYC4Brgv4GHgLOILh7UP4NZLOXxYOAvRGtIbwODs/jZWyVtxxH9x35iTN+HbkS/kmcBp8aU4SaiTVHl021iyJAX7s8I38usZ9gmx4FEa24nV/XdyWCGnwCriH6EfQicUpPX64SSWWBmnYF7gAfcfVx1B7wykGNP4K/A/7r7PzKVI/ziOo9ok44T/Uf+e+Atd98YxngOBr4LdAbGufu4dOfYJtOW92pm7UJze3efG+Pfoy1wGTDV3V/O9vJDhu5E29DPd/eJMWVo4tEYIOG7URbH3yMsvwWwl7vPiGP5IUNjop1almXy3+lOMgwEmoQMb9Ykg4pKHdTogzZr7+7LwuO07mlUwxwd3X1pJnJss5xOwCZ3X25mVxFtm/0v4G2PBojL+zVx9w0pq+C1/kJW93PYtl/Mf48W7r4m3TlqmKHQ3UvC38Bi/Cwy9SOnut+LCp9/zN+Lxh7tWJHV70VVz9ckg4pKHZnZCUSbtiYBs8r/w96mT767by6/jyNH+A8jL9M5tlnmli9iKCwnAVcSrZ30d/f/Svd/JDX8e2SyqMb+vUjKd6I+fBbbZMjp70VdM2igvhbKf1VbtG/7H4j2nhkKXGxmXbfpW/7HaQP82sxax5GDrf95pD1HVXzHg4+zQnudC0od/h6/ievvkanvRVK+E3X4LK6P47PQ96LKDDX/e9RkAEa3CoNZhwD/JgwwE/0K/wNwHbB7aMsP97sArwHfbqg5dpIx44OPSfkckpAjCRmSkiMJGZKSI1sZtKZSe6VAD+Ai2LI73iSiA9ouMrNmvrXaPwv82t1fa8A5quTRGktj4FCiva0yMfiYlM8hCTmSkCEpOZKQISk5spMh3RW5od7YOv60G1AYHvciqvy/Tel3ErBfeNyU6CDItP3iSEqObfNUs2/jlMd5DeFzSEKOJGRISo4kZEhKjrgyaKC+BszsdODnRB/8RGA8sITooLF/u/v12/TPA/Zw9/kNMUfK/GMZfEzK55CEHEnIkJQcSciQlByxZEhXZW7oN2BPwpl+gb2BXxIdHFRIdL6gd4nOY1VeqOv0S7we5CiffxEwlegMqw8TbZ/tuk3f8u20bYAbgNYN6HOIPUcSMiQlRxIyJCVHXBnS/mE2lBsVN9U0JzqNyBSgRWjbHRgHXBimWzXkHFVky9rgY1I+hyTkSEKGpORIQoak5EhCBncN1FcqrAJeaGZnWHR+qHuJBrmmAUPNrK27f050uvDdwu56qxtqjh3IysBfUj6HJORIQoak5EhChqTkSEKGLTJRqRrCDehIdGK1L4Geoe0copPePQFcAMwHjsuFHGG5sQ0+JuVzSEKOJGRISo4kZEhKjiRkcNeaynbKDxIClhOdPbcAOCa0PUN0qu43iY65+LG7v9qQc6Rydw8Df08SXaL490SnKb+Y6KzDt4Z+L7n7h+FlG4FfeC13j0zK55CEHEnIkJQcSciQlBxJyFBBJitWfbux9Zf4icDZRCdUKyQ6i+614bmD2GYguqHmqCRXVgf+kvI5JCFHEjIkJUcSMiQlRxIybJcpWwuqLzeiEx/OAY5JaduX6Ip0fyY6FfS3ciEHCRj4S8LnkJQcSciQlBxJyJCUHEnIUCFPthaU9Btg4T/OfxKuHwAMAK4muoZ3V6JrvB+TIznygOFE15foQ3TerrZE+7dfBrQN/a4gumJc+RluG9rnEHuOJGRISo4kZEhKjiRkqOymgx+3YWbXEe0q25RoFTIPWO7uP0vngXv1IYdFlxAtIbqI09Hu/pGZnQMcRnTlyBeIrm55kWdum3Xsn0NSciQhQ1JyJCFDUnIkIUOFPLlcVMyi80+ZWV+i3WDnEVX+PYHP3X2qmZ1INHZwBrDaM/CBJSVHJXnyia6CdwrR9tn7Q9t+wHeI9gJ7xd0npHm5Of/3SEKGpORIQoak5EhChp3K5mpREm9E2yM/JbrE7HTgEqBLeO44YAbhrLo5kiPWgb8EfQ6x50hChqTkSEKGpORIQoYd5otrwUm4EZ025Eng+DB9PPAg8D2gJXAj4VrupHG8IKk5UvLEMvCXlM8hCTmSkCEpOZKQISk5kpBhpxnjWGhcN6LdYM8kOv16edv9wAi2nk7kfOBfRNslmzfkHJXkyurAX1I+hyTkSEKGpORIQoak5EhChprecubgRzPrCTwHHAlcY2aXhqfeINo22S9MzyA6i2cTd1/bUHNUxiNrgVeJTu3wAvADos1dw919IXCbu/+rrstKyueQhBxJyJCUHEnIkJQcSchQK3FXtWzcgAOA94BTw/QFRLvF9gDygZuBMUSrldOBMxtyjm0ylY+h9AVOJtrM1Rc4HTg0PHci0WmzW5GGVeqkfA5JyJGEDEnJkYQMScmRhAy1zh53gKy8STgKKEuZnk50YrXpwD1Eq417AacBfUOftG+PTEqOSnJldeAvKZ9DEnIkIUNSciQhQ1JyJCFDrbPHHSBrbzQ6yeEnRL+4fxvaGgMfAdfkWo6UPLEM/CXlc0hCjiRkSEqOJGRISo4kZKjNLaeOU7HoCoXjiU4/UhbahgFt3P2PuZDDzPYGegOb3f250HY/MBP4s0enqj+f6Gj644CmnqHttPp7JCtDUnIkIUNSciQhQ03lzEA9gLtPIlpd/AjAzPYhOkhoRi7kSNrAX67/PZKWISk5kpAhKTmSkKHG4l5ViuNGNIawFpgFDMqFHCR44C8X/x5JzpCUHEnIkJQcSchQ3VtObf5KFVYrW7v7s7mQw8yOAl5z97wwPR1YSHTsyetEJ4bsRnTRrc/d/b3yU0JkMldKvpz6eyQ9Q1JyJCFDUnIkIUN15GxRKZfN/zjjzmFmJxHtOfIJUYG52cwaE42nPOjut2dy+dWRS3+P+pAhKTmSkCEpOZKQYUdyvqjkmvo48Cci9UdODdRLPR34E5F6oyDuAJJ97j7OzMrMbC3RQY9Xufs/484lIvWfNn/lsPoy8Cci9YeKiiR+4E9E6g8VFRERSRsN1IuISNqoqIiISNqoqIiISNqoqIiISNqoqIjUgJldamZDwuMLzaxLGud9oZn9uZav/XW6cojUhYqKSA24+73u/kiYvJDokgFJUOOiYmb5mQgiuU1FRWQHzGyImU03s/fN7FEzu8nMrjazs4Ai4DEzm2ZmJ5vZsymvG2Bmf9/BfAeZ2X/CfCdV8vyosIzy6dXhvrOZvRaWOdPMjjaz24Bmoe2x0O8CM3sntP21vICY2Wozu9nM3gb6p+tzEimnoiJSBTM7ELie6FLLvYER5c+5+9NAMXC+u/cBxgH7m1nH0OUi4OEq5tsRuB/4Xpjv2TWI9QNgfFhmb2Cau18LrHP3Pu5+vpntD3wfODL02wycH17fApjp7ke4+xs1WK5ItejcXyJVOx542t2/AnD35WZWaUd3dzN7FLjAzB4mWgsYUsV8+xFdeuDT8vnWINO7wENm1gj4P3efVkmfE4BDgXdD3mZEV/KEqMA8U4PlidSIiopI1QyoySknHgZeANYDT7n7pjrMdxNhS4JFlaExgLu/ZmbfBk4GHjWzO1LGeFLnP9rdr6tkvuvdfXO13o1ILWjzl0jVJgHnmFl7ADNrt83zq4BW5RPuvghYBPwGGLWD+U4BjjGz7lXMF2A+0doGwGCgUei7J7DE3e8HHgQOCX1Kw9pLee6zzKxT+fzD60QyTmsqIlVw91lmdivwLzPbDLxH9J99uVHAvWa2Dujv7uuAx4CO7v7BDua71MyGA383szyiTVMDtul2P/Ccmb1DVCTWhPZjgV+aWSmwmq2b2O4DppvZf8K4ym+Af4b5lwKXAwtq8zmI1IROKCmSRuE4k/fc/cG4s4jEQUVFJE3MbCrRGsUAd98Qdx6ROKioiGRQOB6kyTbNP3R3Xb5ZGiQVFRERSRvt/SUiImmjoiIiImmjoiIiImmjoiIiImnz/wEerRMBR4AIkwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.swarmplot(x='city_cluster',y='cnt', data=eda_df ,hue='source')\n",
    "\n",
    "plt.xticks(rotation=45)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "_kg_hide-input": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]), <a list of 10 Text xticklabel objects>)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYYAAAEtCAYAAAAMW8fIAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3Xl8FeXZ//HPlZAQCJsgLhDZVHBh1WiLimtxRRSX/ty11SrVutZWfWwrj61ttbb10Vpt3bC2Ai61YqVu1KW4QpRVRJGlAiqbIDshuX5/zCScExKSE845M8n5vl+v80pmzpyZbyYnuc7MPXPf5u6IiIhUyYs6gIiIxIsKg4iIJFFhEBGRJCoMIiKSRIVBRESSqDCIiEgSFQYREUmiwiAiIklUGEREJEmLqAM0xs477+w9evSIOoaISJNSVla23N0717dckywMPXr0YMqUKVHHEBFpUsxsYUOW06kkERFJosIgIiJJVBhERCRJk2xjEBGpS3l5OYsWLWLjxo1RR4lMUVERJSUlFBQUNOr1Kgwi0qwsWrSItm3b0qNHD8ws6jhZ5+6sWLGCRYsW0bNnz0atQ6eSRKRZ2bhxI506dcrJogBgZnTq1GmHjphUGCS3fLUAHjsNbu8JT1wA65YDsKWikllLVrN6Q3m0+SQtcrUoVNnRn1+nkiS3PH0JLJocfP/hs+DOJ0f+kYsemcziVRsoKsjjlyP6cdoBJdHmFImQjhgkd5Rv3FoUqiz4D7e/8BGLV20AYGN5JbeMn8XG8ooIAorEgwqD5I6CIui8b/K83Qfy35Xrk2at2biFr9ZvzmIwae62bNkSdYSUqDBIbhlxP3TeJ/i+64Ew7Hcc33f3pEUG7NGB3du3iiCcxMW6des46aSTGDBgAH379mXcuHFMnDiRQYMG0a9fP7773e+yadMmIOiiZ/nyoK1qypQpHHnkkQCMGjWKSy+9lGOPPZYLLriAiooKrr/+evr160f//v255557ACgrK+OII47gwAMP5LjjjuPzzz+P5GdOpDYGyS1dBsIV78Lm9VDYGoCrj3GKCvL49+yl7L1rW64dunfEISVqL7zwAl26dOH5558HYPXq1fTt25eJEyfSu3dvLrjgAu677z6uueaa7a6nrKyMSZMm0apVK+677z7mz5/PBx98QIsWLVi5ciXl5eVceeWVPPvss3Tu3Jlx48Zx88038/DDD2fjx6yTCoPkprAoAOTnGZcfuReXH7lXhIEkTvr168f111/PDTfcwLBhw2jXrh09e/akd+/eAFx44YXce++99RaG4cOH06pVcPT5yiuvMHLkSFq0CP7tduzYkZkzZzJz5kyGDh0KQEVFBbvvvnud68sWFQYRkRp69+5NWVkZEyZM4KabbuLYY4+tc9kWLVpQWVkJsM29A8XFxdXfu/s2l5G6O/vvvz9vv/12GtPvOLUxiIjUsGTJElq3bs15553H9ddfz1tvvcWCBQuYO3cuAI899hhHHHEEELQxlJWVAfD000/Xuc5jjz2W+++/v7oheuXKlfTp04dly5ZVF4by8nJmzZqVyR+tQVQYRERqmDFjBgcffDADBw7ktttu4xe/+AWPPPIIZ555Jv369SMvL4+RI0cCcMstt3D11VczZMgQ8vPz61znJZdcQrdu3ejfvz8DBgzg8ccfp7CwkKeeeoobbriBAQMGMHDgQN56661s/Zh1MnePOkPKSktLXQP1iEhtZs+ezb777lv/gs1cbfvBzMrcvbS+1+qIQUREkqgwiIhIEhUGERFJosIgIiJJVBhERCSJCoOIiCTRnc8ikrMqK53x05bw0KT5fL56A7u3b8XFh/Vk+IAu5OU1brCbFStWcMwxxwDwxRdfkJ+fT+fOnQGYNm0aAwYMqF72rLPO4sYbb+Sf//wnP/3pT6msrKS8vJyrr76a5cuX8+STTwLBfRX9+vUD4Lvf/S5XXXXVjvzY9dJ9DCLSrDT0PobKSmfkX8uYNHc56zdvHX+jVUE+Q/bemfvPO7DRxaHKqFGjaNOmDddffz0Abdq0Ye3atUnLlJeX0717d9577z1KSkrYtGkTCxYsoE+fPtXL1Pa6+ug+BhGRFI2ftmSbogCwobyC/3yynOemL8lKjjVr1rBlyxY6deoEQMuWLZOKQhRUGEQkJz00af42RaHKhvIKHvzP/LRvc8OGDQwcOLD6MW7cODp27Mjw4cPp3r07Z599Nn/729+qO+WLitoYRCQnfb56ww493xitWrVi6tSp28x/8MEHmTFjBq+88gp33nknL7/8MqNHj0779htKRwwikpPqG6Uv26P49evXj2uvvZaXX355u720ZoMKg4jkpIsP60mrgtp7Q21VkM8lQ3pmJcfatWt57bXXqqenTp1K9+7ds7LtuuhUkojkpOEDujBhxuf855PlbCjf9qqkk/t3Sfs2q9oYqhx//PHcfPPN3HHHHVx22WW0atWK4uLiSE8jgQqDiOSovDzj/vMO5LnpS3jwP1vvY7hkSE9O7t/4+xgSjRo1Kmm6oqL2xu4JEyZsdz2pXqq6o1QYRCRn5eUZpwzsyikDu0YdJVbUxiAiIklUGEREJIkKg4iIJFFhEBGRJCoMIiKSJOOFwcyON7M5ZjbXzG6sY5lvm9mHZjbLzB7PdCYREQAqK2H6E/CnI+A3ewVfpz8RzG+kFStWVPeFtNtuu9G1a9fqaTNj4MCB9O3bl5NPPplVq1Ylvfb3v/89RUVFrF69unrea6+9xrBhwwAYPXo0eXl5TJ8+vfr5vn37smDBgkbnrU1GC4OZ5QP3AicA+wFnm9l+NZbZG7gJONTd9weuyWQmEREg+Oc/7jx47mr4fCqsWxZ8fe5qeOL8RheHTp06MXXqVKZOncrIkSO59tprq6eLi4uZOnUqM2fOpGPHjtx7771Jrx0zZgwHHXQQzzzzTJ3rLykp4bbbbmtUtobK9BHDwcBcd5/n7puBscApNZb5HnCvu38F4O5LM5xJRARmPgXzXoXy9cnzy9fDp/+GmZntr2jw4MEsXry4evrTTz9l7dq1/OIXv2DMmDF1vm7YsGHMmjWLOXPmZCxbpgtDV+CzhOlF4bxEvYHeZvammb1jZsdnOJOICLx977ZFoUr5enj7DxnbdEVFBRMnTmT48OHV88aMGcPZZ5/NkCFDmDNnDkuX1v4ZOS8vjx//+Mf88pe/zFi+TBeG2u4przlkXAtgb+BI4GzgQTPrsM2KzC41sylmNmXZsmVpDyoiOebrxTv2fCNU9ZXUqVMnVq5cydChQ6ufGzt2LGeddRZ5eXmcdtpp1cN61uacc87hnXfeYf789I8ZAQ0sDGaWb2avNGL9i4A9EqZLgJrDIi0CnnX3cnefD8whKBRJ3P3P7l7q7qVV46eKiDRau3q6wajv+UaoGo9h4cKFbN68ubqNYfr06XzyyScMHTqUHj16MHbs2O2eTmrRogU//OEPuf3229OeERpYGNy9AlhvZu1TXP9kYG8z62lmhcBZwPgay/wDOArAzHYmOLU0L8XtiIikZvAVUNC69ucKWsPgH2Rs0+3bt+fuu+/mzjvvpLy8nDFjxjBq1CgWLFjAggULWLJkCYsXL2bhwoV1ruOiiy7ilVdeIRNnUFI5lbQRmGFmD5nZ3VWP7b3A3bcAPwBeBGYDT7j7LDO71cyqTq69CKwwsw+BV4EfufuK1H8UEZEU9D0Deh21bXEoaA17Hg19T8/o5gcNGsSAAQMYO3YsY8eOZcSIEUnPjxgxgrFjx9b5+sLCQq666qo62yJ2hLnXPOVfx4JmF9Y2390fTWuiBigtLfUpU6Zke7Mi0gTMnj2bfffdt2ELV1YGVx+9/YegTaFd1+BIoe/pkNe07/+tbT+YWZm7l9b32gZ3u+3uj5pZK6Cbu2fuOikRkWzJy4P+ZwYPqdbgkmhmJwNTgRfC6YFmVrO9QEREmrhUjpVGEdywtgrA3acC2RkUVUQkBQ09Rd5c7ejPn0ph2OLuq2vMy+29LyKxU1RUxIoVK3K2OLg7K1asoKioqNHrSGVoz5lmdg6QH/ZvdBXwVqO3LCKSASUlJSxatCgjl3E2FUVFRZSUlDT69akUhiuBm4FNwOMEl5n+vNFbFhHJgIKCAnr21FnuHZFKYTjJ3W8mKA4AmNmZQN33bYuISJOTShvDTQ2cJyIiTVi9RwxmdgJwItC1xp3O7YAtmQomIiLRaMippCXAFGA4UJYwfw1wbSZCiYhIdOotDO4+DZhmZo+7e3kWMomISIRSaXw+2MxGAd3D1xng7t4rE8FERCQaqRSGhwhOHZUBFZmJIyIiUUulMKx2939lLImIiMRCKoXhVTP7DfB3gpvcAHD399OeSkREIpNKYfhG+DWxL28Hjk5fHBERiVoq4zEclckgIiISD6mMx7BrOKznv8Lp/czs4sxFExGRKKTSJcZogo7zuoTTHwPXpDuQiIhEK5XCsLO7PwFUArj7FnTZqohIs5NKYVhnZp0IB+cxs28CNQfuERGRJi6Vq5KuA8YDe5rZm0Bn4IyMpBIRkcikclXS+2Z2BNCHoDuMOeo7SUSk+UnlqqQrgDbuPsvdZwJtzOzyzEUTEZEopNLG8D13X1U14e5fAd9LfyQREYlSKoUhz8ysasLM8oHC9EcSEZEopdL4/CLwhJndT3Bl0kjghYykEhGRyKRSGG4ALgW+T9D4/BLwYCZCiYhIdFK5KqkSuD98bMPMnnb309MVTEREopFKG0N9NJKbiEgzkM7C4Glcl4iIRCSdhUFERJqBdBYGq38RERGJuwYVBjPLN7O/1rPYDWnIIyIiEWtQYXD3CqCzmdV5Q5u7v5S2VCIiEplU7mNYALxpZuOBdVUz3f136Q4lIiLRSaUwLAkfeUDbzMQREZGopXKD2/8CmFmxu6+rb3kREWmaUul2e7CZfQjMDqcHmNkfM5ZMREQikcrlqncBxwErANx9GnB4fS8ys+PNbI6ZzTWzG2t5/iIzW2ZmU8PHJSlkEhGRNEuljQF3/yyh522Aiu0tH3bNfS8wFFgETDaz8e7+YY1Fx7n7D1LJIiIimZHKEcNnZnYI4GZWaGbXE55W2o6DgbnuPs/dNwNjgVMamVVERLIglcIwErgC6Erw6X8gUN/Qnl2BzxKmF4XzajrdzKab2VNmtkdtKzKzS81siplNWbZsWQqxRUQkFakUhj7ufq677+ruu7j7ecC+9bymtm4yana29xzQw937A68Aj9a2Inf/s7uXuntp586dU4gtIiKpSKUw3NPAeYkWAYlHACUE90JUc/cV7r4pnHwAODCFTE3WvGVreW/+SrZUVEYdRUQkSb2Nz2Y2GDiEoEuM6xKeagfk1/PyycDeZtYTWAycBZxTY/27u/vn4eRw6m+3aPJGjZ/F6LcWANCjU2vGXjqY3doXRRtKRCTUkCOGQqANQRFpm/D4Gjhjey909y3ADwjGi54NPOHus8zsVjMbHi52lZnNMrNpwFXARY35QZqKuUvXVBcFgAUr1vPnN+ZFF0hEpIZ6jxjc/XXgdTMb7e4LAcwsD2jj7l834PUTgAk15v0s4fubgJtSDd5Uffn1plrmbYwgiYhI7VJpY/iVmbUzs2LgQ2COmf0oQ7marYN6dKRrh1ZJ804Z2CWiNCIi20qlMOwXHiGcSnAE0A04PyOpmrHCFnmMu+ybnP/N7hy3/6786fwDOXb/3aKOJSJSLZU7nwvMrICgMPzB3cvNTOM8N0LJTq35+al9o44hIlKrVI4Y/kQwJkMx8IaZdSdogBYRkWYklW637wbuTpi10MyOSn8kERGJUoMLg5n9rI6nbk1TFhERiYFU2hgSB+cpAoaRAzejiUgO2LwOXv0lLJgEJaVw9E+g1U5Rp4pMKqeSfps4bWZ3AuPTnkhEJNuevx6mPR58//lU+HoJnD2Gxas2MHn+Svp2bc9eu7SJNmMWpTQeQw2tgV7pCiIi2ffklM94dc5S9tqlLd8b0pO2RQVRR4rGR88nT3/8Ai/NWMTlY6azpTK4+PLWU/bngsE9sp8tAqm0Mcxga8+o+UBn1L4gKVi6ZiPPTfucVgX5DB/YhTYtw7ffynmw/ivoMgjyUrlQTnbEA2/M47YJVWeDv+D9hV/x10u+EUyWb4SCHOq/q1MvWPLB1ukO3fntK/OqiwLAnS/O4dxvdCc/r7ZOo5uXVI4YhiV8vwX4MuwLSaRei1dt4OR7JrFy3WYAHn5zPv+88jCKXvoxTH4wWGjXvnDBeCjuFGHS3PH0+4uSpifNXc7SxQvY5cXvw3/fgk57w6n3wR4HRZQwi068E8aeC2u/gFYd4eS7+HpcedIiG8or2FJZSX5efX2HNn31fjwzs45m1hFYk/DYALQL58fXhq+CxqSNq6NOkvPGTf6suigAzF26llfeem9rUQD4cia8e18E6XJT57Ytk6ZbFeRT/PqooCgArPgEnr4YKnOga/iSUrh2Jlz+Llw3G3odyTkHd0ta5LRBJbRs0fyLAjTsiKGM4BRSXYPuxLOd4aMJwZu6fD0UtoFvP8qmHkdx2/OzmTDjC/bo2IqfDtuPA7rl7pUHkVu/ctt5qxdnP0eOum5ob6Z+too1G7eQZ/DDY3tTPGVK8kKrFsKGlVC8czQhsym/AHbZp3ryymP2plun1rz96Qr6dm3PWQfVOrhks2TuTa9Xi9LSUp9S8w1c0139YNV/t07v3Iff9/kr/zfxk+pZnYoLeeumo3PmU0CUFq/awLC7/8NX64PD8z07F/P8ZYMouu9AWJcwVOu5T8Pe34ooZe5Zs7GcKQu/Yq/ObdijY2t4ZiRMG7N1gV32g8vfji6gpJWZlbl7aX3LpdL4PAL4t7uvDqc7AEe6+z8aHzND3OHrz5Pnfb2Etz9dkTRrxbrNzPliDf1LOmQxXG7q2qEVL15zOOOnLaF1YQtOHrA7RUUF8J1/waS7YP0KGHSeikKWtS0q4Kg+u2ydcfyvoGIzfPrvoM3npN9FF04ik0rj8y3u/kzVhLuvMrNbgPgVBjPoezpMH7t1Xr/T6Ut73luw9fRFcWE+vTrnzrXJidZt2sJPn53Jyx9+Sc+di/nf4fszKMOn1XZpV8QlQ2qcedx5bzj13oxuV1LQaic44+GoU0jEUikMtTVU78h9EJl18l2wUw9YNBm6D4ZDruLq8jwWrFjHq3OWsmvbIm49Zf+tl0zmmN+9/DF/fz84nz990WpG/rWMSTccTUG+LhcVyXWp/FecYma/A+4laHS+kqBhOp4KWsFRyQPDtW8BD190EBs2V9CyRR55OXA9cl3enZ98Wu3LrzexcMU69tqlbUSJRCQuUvl4eCWwGRgHPEFwyeoVmQiVaa0K83O6KAAM3CO5XaVjcWHQ+CgiOS+VvpLWATfW9byZ3ePuV6YllWTcj47dhy9Wb2TiR0vp1rE1vxzRT1dniQiQ3jaCQ9O4Lsmw9q0LePDCg9hSUUkLtSuISAL9R8hxKgoiUpP+K4iISJJ0Fobcbs0VEWkmUi4MZtbWzGq7K+z/0pBHREQi1uDCYGb9zOwDYCbwoZmVmVnfqufdfXQG8omISJalcsTwJ+A6d+/u7t2AHwJ/zkwsERGJSiqFodjdX62acPfXgOK0JxIRkUilch/DPDP7KfBYOH0eMD/9kUREJEqpHDF8l2Cc57+Hj52BizKQSUREIpTKEcO33P2qxBlmdibwZHojiYhIlFI5YripgfNERKQJq/eIwcxOAE4EuprZ3QlPtQO2ZCqYiIhEoyGnkpYAU4DhJI+/sAa4NhOhREQkOvUWBnefBkwzs8fdvbyu5czsaXc/Pa3pREQk6xrcxrC9ohDqVc/zIiLSBKRzPAZP47qat3UrYPKDsPZL6HdmMCa1iEhMpLMwSENUVsDoE2HZR8F02SNw/j+g1xHR5hIRCWW8220zO97M5pjZXDPb3tCgZ5iZm1lpGjPFz3/f3loUALwS3v9LdHlERGpIpXfVq+uZd0Mtz+cD9wInAPsBZ5vZfrUs1xa4Cni3oXmarKL2DZsnIhKRVI4YLqxl3kVV37j7S7U8fzAw193nuftmYCxwSi3L/Ry4A9iYQp6mabd+0Dfh4q3izjD4iujyiIjU0JAb3M4GzgF6mtn4hKfaASvqeXlX4LOE6UXAN2qsfxCwh7v/08yu306OS4FLAbp161Zf7Hg742E46JKg8Xmvb0HLtlEnEhGp1pDG57eAzwk6zfttwvw1wPR6Xltbu0P11Utmlgf8ngZ0xufufyYc/6G0tLTpXwHV/ZCoE4iI1KohN7gtBBaa2beADe5eaWa9gX2AGfW8fBGwR8J0CcGd1FXaAn2B18wMYDdgvJkNd/cpDf8xREQkXVJpY3gDKDKzrsBE4DvA6HpeMxnY28x6mlkhcBZQfTrK3Ve7+87u3sPdewDvACoKIiIRSqUwmLuvB04D7nH3EQRXGtXJ3bcAPwBeBGYDT7j7LDO71cyGNza0iIhkTio3uJmZDQbOBS5u6OvdfQIwoca8n9Wx7JEp5BERkQxI5YjhGoLxF54JP/X3Al6t5zUiItLENPiIwd1fB15PmJ5HcFOaiIg0Iw25j+Eud7/GzJ6jlo7y3F1tBSIizUhDjhgeC7/emckgIiISDw1pPC4Lv75e37LSxGz4ChZMgo57wq7bvcBMRCLy0qwv+ONrn7KlspKLD+vJiEElGd9mQ04lzWA7Yy24e/+0JpLsWFwGfzkVNn0dTB/+Izj6J9FmEpEkc5eu4ft/e5+KyuBf8LXjplGyU2sO6tExo9ttyFVJw4CTgRfCx7nhYwLwVOaiSUa9fsfWogAw6a5gACERiY3XP15eXRSqvDZnaca329AuMTCzQ9390ISnbjSzN4FbMxVOMmjDV8nTleVBoSjuFE0eEdlGn1237WCzdy3z0i2V+xiKzeywqgkzOwQoTn8kyYpB5ydP9xgCHXtGk0VEanXY3jtz8WE9Kcg38gzOPLCEYf27ZHy75t6wjkrN7EDgYaA9QZvDauC77v5+5uLVrrS01KdMUXdKO2z2c/DR89BpTzj4MihqF3UiEanF1xvLqax0OrQu3KH1mFmZu9c7SmYqN7iVAQPMrB1BQVldY4MXuvujqUeVyOx7cvAQkVhrV1SQ1e2lPOazu39dsyiEthn6U0REmp6UC8N21DYoj4iINDHpLAxNf1Q1ERHREYOIiCRLZTyG+ryZxnWJNGsVlc4bHy/jq/WbOWbfXWnfKruNiyLb0+DCYGbX1TJ7NVDm7lPd/QfpiyXSfLk7Fz3yHv/5ZDkAO7cp5JnLD2WPjq0jTiYSSOVUUikwEugaPi4FjgQeMLMfpz+aSPP03vyV1UUBYPnazYx+awEs+QDGnQ9/OQVmPRNdQMl5qZxK6gQc4O5rAczsFoK+kg4HyoA70h9PpPnZUF6xzbz169fB6BGweU0wY95rUNQB9jwqu+FESO2IoRuwOWG6HOju7huATWlNJdKMHbrXzuzZeWtvMoX5eZzd+b9bi0KV2eOznEwkkMoRw+PAO2b2bDh9MjDGzIqBD9OeTKSZKsjP46mRhzB28mesWr+ZUwd1Zd8tcxIGzg3t1COKeCIN7ysJqvtLOozg0tRJ7h5Jh0XqK0mapQk/gvceABy6HQLnPgEtM9+TpuSOtPeVZGb/B4xz9//boWQiUrsTfwOHXg2b10HnPlGnkRyWyqmk94GfmFlv4BmCIqGP7SLp1D7zwzaK1KfBjc/u/qi7nwgcDHwM3G5mn2QsmYiIRKIxXWLsBewD9AA+SmsaERGJXCptDLcDpwGfAuOAn7v7qkwFE5HcMGHG5zw55TM6Frfk+0fuyV67tIEvP4S3/wDl66H0Yug5JOqYOSWVNob5wCFAL6Al0N/McPc3MpJMRJq9ibO/5PK/bR0E8vWPl/LG5f1o/fDxsCkc9uXD8XDJK9D1gIhS5p5UTiVVAP8GXgD+F3gRGJWBTCKSI/45/fOk6eVrN/P2W69tLQoAXgGz/p7VXLkulcJwFXAQsNDdjwIGAcsykkpEckKXDkXbzuvccdsF23bJQhqpkkph2OjuGwHMrKW7fwToYmsRabSLD+vFPrsFN/GZwXcO7cG+B30L9h2+daEuB8AB50eUMDel0sawyMw6AP8AXjazr4AlmYklIrmgY3Eh/7p6CDMWr2an1oVbux7/f4/BFzOhfAOUlAZVQ7KmwYXB3UeE344ys1eB9gTtDSIijWZm9C/psO0Tu/XNfhgBGjmCm7vX7O5LRESaiXSO+SwiIs2ACoOIiCRRYRARkSQqDCIikkSFQUREkmS8MJjZ8WY2x8zmmtmNtTw/0sxmmNlUM5tkZvtlOpOIiNQto4XBzPKBe4ETgP2As2v5x/+4u/dz94HAHcDvMplJRES2L9NHDAcDc919nrtvBsYCpyQu4O5fJ0wWAw0fhFpERNKuUTe4paAr8FnC9CLgGzUXMrMrgOuAQuDo2lZkZpcClwJ069Yt7UFFRCSQ6SOG2jo42eaIwN3vdfc9gRuAn9S2Inf/s7uXuntp586d0xxTRESqZLowLAL2SJguYfsd740FTs1oIhER2a5MF4bJwN5m1tPMCoGzgPGJC5jZ3gmTJwGfZDiTiIhsR0bbGNx9i5n9gGC0t3zgYXefZWa3AlPcfTzwAzP7FlAOfAVcmMlMIiKyfZlufMbdJwATasz7WcL3V2c6g4iINJzufBYRkSQqDCIikkSFQUREkqgwiIhIEhUGERFJosIgIiJJVBhERCSJCoOIiCRRYRARkSQqDCIikkSFQUQk7iq2QEV51janwiAiEmev/wZu7w6/KoEXbwbP/CCXKgwiInG18G149ReweS1s2Qhv/wFmP5fxzaowiIjE1ZIPGjYvzVQYRETiqsehbDNCcs/DM75ZFQYRkbjafQCc+kfouCd06AbH3w57HpXxzWZ8oB4REdkBA88JHlmkIwYREUmiwiAiIklUGEREJIkKg4iIJFFhEBGRJCoMIiKSRIVBRESSqDCIiEgS8yz01JduZrYMWLiDq9kZWJ6GOE09A8QjRxwyQDxyxCEDxCNHHDJAPHKkI0N3d+9c30JNsjCkg5lNcffSXM8QlxxxyBCXHHHIEJccccgQlxzZzKBTSSIikkSFQUREkuRyYfhz1AGIRwaIR444ZIB45IhDBohHjjj/dEvnAAAQQElEQVRkgHjkyFqGnG1jEBGR2uXyEYOIiNRChUFERJKoMIiISBIVBhGRZszMrP6lkqkw1MPMdjWzXjHIURR1hto05k2Xhm32MLPTsr3dGhla1piO5G/JzAqj2G6NDDtFnQHAzPqY2a1R54gDM2trZsUA7u6p/p2qMGyHme0D/AvoEnGOPsDfzexXZnZzlFnCPN3M7Jtm1tezfFmbme0PjAfamll+wvysFajw9zHWzH5kZjcCuHtltouDmfUGHjOzn5vZ1dncdkKGzsA8M7s8iu0n5OgDPAmcbGYHRJShl5kdEtWHhIQc+wAvA380swchKA6prEOFoQ5mthfwNPB7d58UxSfjMEdvYCzwAvAesI+ZtY4iS5inDzAZuAB43syuMLM9s7TtlsCNBL+TR4HKquca86mokRn2IHhfvARMBL5hZs+EGbJWHMLfwzPA28CnwAAzOyEb266hLfAVcF1UxSHcF38BHgBeB46LIEMv4KMwQ2TFIXx/jgUeAm4FWppZm4TnG5SrRWbiNW1m1gK4EPgMeDacfYuZrQVauPuvs5SjJXAJcLe7P2JmuwA3A+eYmbn7A9nIkZAnDzgL+I2732lm3wQuA9qZ2ZPuPjfDETaHXz8Ovz4e/k52Bc5293VmlufulbW/PC06Aq+5+30AZvYn4K9m9rS7n57hbRNusxAYCdzv7veEpxn7AvsSHOFmjbvPM7O7gDnA3Wa2hqBwFrj76kxvP/yQ9GvgPncfbWalwDgze8Pd38z09sMMecBg4BrAgZ8AvzSzSdl4P9SwM/CGuz8Qvi8OAH5oZl3c/bLww4vVdwShI4ZauPsWgsPSd4EbzGwqsBuwDvimmd2XpRybgN+GRSEf+D0wH9gIHFd1mJgt4Zt8BcGn5GJ3fwf4LbAXcAJk7pROwpt5CrBneEptMfA/wAaC00tk4Q+xABhuZgPC6S4En8zMzC7L8LYBcPfNwIMER5G4+0bgLWBQNrZfJeHT5yCC/yWHAz8nOII4PBsZ3H09cEVYFPLdfQrB0cOgGhkzmaGS4NTN2PADwwsE78shEbQBOXCEmf0YeIfg6P6fQA8zq3q/1HtaSYWhDu4+neCTz07AC+4+Mvyl/wwoyNahort/GX6bD/zF3c90978C/0vwzyjbR30vAIsI3nwt3X0mcD9wjZkdnKk2h4T1fgRcCQwERrv7Mnf/f8BmM+uRiW3XyDEFuBN4xsweAK4GxgFPAO0zvf2EHLPc/ZOEWRsJPrwQtv9clI0Y4dfRQPvwvbqF4MNDpyxsv8rn4deqDwVzgJFm1rHqE3KmA7j7UmBV+P1dbC0OPc3syKq2qCzkmEpwuvUjglOM33H3MuB4YG3YJlQvFYbtCIvDLwg+BVUpAboRNH5mpd0h/LS82d1frCVHcSZz1NKgNh9YQnAe92gza+PukwnOa+6SqRxVwn3wa6A/MNjMdjGzgUBXoCKT267az+5+N3AKcDswNPyHmA/sYWYtstwQXrWtxcCHYZvUfQSnQTMqoVivA35mZguA3wCHEJx67ZnpDIk5Er6OJTiC+ml4FJGVCyQS25jC4vAX4CmCDw4fZnr7Ce/PfwGvAJuAqiPbw4BeNLT5wN31aOADOBKYDpwYcY5jgGmZzhG+kTYDs8I3VkE4vwVwA/A74GHgDIIBRAZnOI8lfH8K8EeCo5V3gVOytO+tlnlHEfxjPi7C90QPgk+ss4CTI9j+KIJTOlXTHSLaD3nh1xHhezPrORIy7E9wBHVSXe+dDOf4PrCG4IPUR8Cwhr5Wneg1kJntDtwLPOjuExrSgJOhHN2BPwH3uPvzmcoRfvI5m+D0iBP8I/4l8I67bw7bPPoDJwK7AxPcfUK6c9SSq/rnNbOO4exO7v5JFL8TC67hvxwoc/cXsrntGjl6EpxTPtfdX4lg+y09aBMjfG9URvH3kZCnGOjl7jMi2n4hwYUaKzL5d9qAHMcCLcMcbzU0R84XhlR+YWbWyd1XhN+n9eqXFHN0dvdlmchRYzu7AFvcfaWZXUNwnvJXwLseNHhWLdfS3TclHMru0JuqwW/eGsulc1+k+Psodvd16c7QiBwl7r4o/D1YRPsiY/8AU3hfJP0OInxfFHpwoUDW3xd1Pd/QHDlfGADM7BiC00QTgVlV/3RrLJPv7hVVX6PIEf7B52U6R41tVr+RwuJwAnAVwVHCYHf/VSb+GaT4O8lIcdT7ouEZwmUi3xc1cuT0+2JHcuRs43PVp1sLrnu+g+CKjguBS8ysa41lq3ZuB+B/zKxdFDnY+sef9hx18e03qM0K56elKOzA7+Qn6doXel80LkONfXFzVPtC74s6c6T2O2loY0RzfBDc/PEmYaMpwafhO4CbgD3Cefnh1/bAG8DhzTVHPRmz0qAWh30RhwxxyRGHDHHJEYcM2cqRs0cMoXJgb+A7UH2Z10SCm5a+Y2atfGvFfQb4H3d/oxnnqJMHRw6FwIEEVwBlqkEtDvsiDhnikiMOGeKSIw4ZspMj3dUszg+2tqnsBpSE3/clqL4/S1juBGCf8Psighvd0lb545KjZp4GLluY8H1ec9gXccgQlxxxyBCXHHHIEFWOnGt8NrNTgesIdtwrwIvAUoKbgt5095trLJ8HdHP3Bc0xR8L6I2tQi8O+iEOGuOSIQ4a45IhDhkhypKuqNYUH0J2wh1JgT+BHBDd/lBD0rTKZoN+fqoK5w5+IY56jav2lQBlBz5CPEJyr7Fpj2apzlh2AnwLtmsu+iEOGuOSIQ4a45IhDhqhypP2HiNOD5NMerQm6bHgbKA7n7QFMAC4Kp9s25xx1ZMtqg1oc9kUcMsQlRxwyxCVHHDLEJUezbXwOD6UuMrMRFvSlcz9Bo81U4EIz28ndPyPopni38DKwtc01x3ZkrUEtDvsiDhnikiMOGeKSIw4Z4pQj7dUuTg+gM0FHUl8CvcN53ybo6GsccB6wADgqF3KE2420QS0O+yIOGeKSIw4Z4pIjDhnikiNjP1yUj4R/fvnAowTdAH8vYd7+BN0l/4qgd8xmnaOWXKcSnBJ6j6D/oyMIBnl5DbitluXzgB5NfV/EIUNccsQhQ1xyxCFDnHK4N8PCkLBzjwPOJOhAqoSg988bw+f6UaNxtbnmqCVX9huyYrAv4pAhLjnikCEuOeKQIU45qvNkYyPZfhB09jYHOCJhXh+CkaX+QNAF7SG5kIMYNGTFaF9EniEuOeKQIS454pAhTjncm1lhACz85/cSYd/jwFDgeoJTJV0Jxkw+Ikdy5AGXEvRNP5Cgn6OdCK59vhzYKVzuSoJRn6p65WxW+yIOGeKSIw4Z4pIjDhnilCPx0SxvcDOzmwguwywiOBTLA1a6+7Xpvjkr7jksGMpvEcEgLkPc/WMz+zZwEMEIcM8RjFL3HXd/NYM54rAvIs8QlxxxyBCXHHHIEKccQNMvDFX99ZjZIIJLLOcSVN/uwGfuXmZmxxGcSx8BrPUM/NBxyVFLnnyCkayGEZyrfCCctw/wLYKrk/7t7i9nYNuR7Ys4ZIhLjjhkiEuOOGSIU446ZevQJJMPgnNz8wmGepwOXAZ0CZ87CphB2BtojuSIvCErDvsiDhnikiMOGeKSIw4Z4pSj1mxRbDTNO7cD8ARwdDh9NPAQcDrQBriFcGxk0nz+PI45arzpImnIisO+iEOGuOSIQ4a45IhDhjjlqDNftjeYhh26J3AaCYO/E/TxczVbu244F3id4Bxd6+aco5ZcWW/IisO+iEOGuOSIQ4a45IhDhjjlaOijSXWJYWa9gWeBQ4EbzGxk+NQkgvN03wynZxD0PNjS3dc31xy18cB64FWCW+ifA84hOHV0qbsvBn7t7q+nY3tx2BdxyBCXHHHIEJccccgQpxwpibIqpVhx9wM+AE4Op88juORyb4K7Am8FxhAcnk0HTmvOOWpkqmpTGAScRHDKaBDBHc4Hhs8dR9Bdb1vSdGgah30RhwxxyRGHDHHJEYcMccqRcu6oA6Swgw8DKhOmpxN0JDUduJfg8KsXMBwYFC6T9nNzcclRS66sN2TFYV/EIUNccsQhQ1xyxCFDnHKknDvqACnu5BOAeQSffH8WzisEPgZuyLUcCXkia8iKw76IQ4a45IhDhrjkiEOGOOVIKXPUARqxk48hGIw+L2HexcAPcyUHMWvIisPvJA4Z4pIjDhnikiMOGeKUo8F5ow7QyJ18IjA3/H4vgksvj82FHEBvYCbwW+AtYGQ4/0LgduDQcLo/8CTQqrnuizhmiEuOOGSIS444ZIhTjgZljTrADuzk44H1wCzg+FzIQcwbsuLwO4lDhrjkiEOGuOSIQ4Y45ajv0aS7xLBgAPt27v5MLuQws8OAN9w9L5yeDiwmuDfhPwSd4fUgGHjnM3f/oOrW+0zmqpEx8t9JHDLEJUccMsQlRxwyxCnH9jTpwlAl2//8osxhZicQXM0wj6BI3GpmhQSnlx5y99szuf2GisPvJA4Z4pIjDhnikiMOGeKUozbNojDkmvATx4sEYy1UhvMuBjq4+28jDSciTV6TuvNZAu4+keC6548BzGwvgl4YZ0SZS0SahxZRB5DGcfcJZlZpZusJbmy7xt1fijqXiDR9OpXUxDWFhiwRaVpUGJqJODdkiUjTosIgIiJJ1PgsIiJJVBhERCSJCoOIiCRRYRARkSQqDJJzzGykmV0Qfn+RmXVJ47ovMrM/NPK1/5OuHCI7QoVBco673+/ufwknLyIYdzcOUi4MZpafiSCS21QYpNkzswvMbLqZTTOzx8xslJldb2ZnAKXA38xsqpmdZGbPJLxuqJn9fTvrPd7M3g/XO7GW50eH26iaXht+3d3M3gi3OdPMhpjZr4FW4by/hcudZ2bvhfP+VFUEzGytmd1qZu8Cg9O1n0SqqDBIs2Zm+wM3Ewx7OoBglDsA3P0pYApwrrsPBCYA+5pZ53CR7wCP1LHezgSj5p0ervfMFGKdA7wYbnMAMNXdbwQ2uPtAdz/XzPYF/h/BwEsDgQqCUfkAioGZ7v4Nd5+UwnZFGkR9JUlzdzTwlLsvB3D3lWZW64Lu7mb2GHCemT1C8Gn8gjrW+02Cbs/nV603hUyTgYfNrAD4h7tPrWWZY4ADgclh3lbA0vC5CuDpFLYnkhIVBmnuDEjl9v5HgOeAjcCT7r5lB9a7hfCo3IL/7oUA7v6GmR0OnAQ8Zma/SWjzSFz/o+5+Uy3r3ejuFQ36aUQaQaeSpLmbCHzbzDoBmFnHGs+vAdpWTbj7EmAJ8BNg9HbW+zZwhJn1rGO9AAsIPvUDnAIUhMt2B5a6+wPAQ8AB4TLl4VFEVe4zzGyXqvWHrxPJOB0xSLPm7rPM7DbgdTOrIBgze0HCIqOB+81sAzDY3TcAfwM6u/uH21nvMjO7FPi7meURnOYZWmOxB4Bnzew9gn/068L5RwI/MrNyYC1bT1f9GZhuZu+H7Qw/AV4K118OXAEsbMx+EEmFOtETqSG8D+EDd38o6iwiUVBhEElgZmUEn+yHuvumqPOIREGFQaQe4f0CLWvMPt/dNZSqNEsqDCIikkRXJYmISBIVBhERSaLCICIiSVQYREQkyf8H1U+LoEzJI14AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.swarmplot(x='city_cluster',y='avg_dist_to_cluster_center', data=eda_df ,hue='source')\n",
    "\n",
    "plt.xticks(rotation=45)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create city_cluster table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "_kg_hide-input": true
   },
   "outputs": [],
   "source": [
    "def feature_sql(model_name, rowid_split, incl_rowid, tab): \n",
    "    \n",
    "    if incl_rowid:\n",
    "        rowid = \"t.RowId,\"\n",
    "    else:\n",
    "        rowid = \"\"\n",
    "    \n",
    "    if tab == 'test':\n",
    "        label = \"\"\n",
    "    elif model_name == 'ALL':\n",
    "        label = \"\"\"t.TotalTimeStopped_p20,\n",
    "                t.TotalTimeStopped_p50,\n",
    "                t.TotalTimeStopped_p80,\n",
    "                t.DistanceToFirstStop_p20,\n",
    "                t.DistanceToFirstStop_p50,\n",
    "                t.DistanceToFirstStop_p80,\"\"\"\n",
    "    else:\n",
    "        label = \"\"\"t.\"\"\"+model_name+\"\"\" as label,\"\"\"\n",
    "    \n",
    "    sql = \"\"\n",
    "    \n",
    "    for c in cities:\n",
    "        features = \"\"\"SELECT \"\"\"+label+\"\"\"\n",
    "                             \"\"\"+rowid+\"\"\"\n",
    "                             t.city,\n",
    "                             t.EntryHeading,\n",
    "                             t.ExitHeading,\n",
    "                             t.Hour,\n",
    "                             t.Weekend,\n",
    "                             t.Month,\n",
    "                             t.Latitude,\n",
    "                             t.Longitude,\n",
    "                             case \n",
    "                                 when t.entryheading = t.exitheading THEN\n",
    "                                  \"C\"\n",
    "                                 when (\"N\" in (t.entryheading, t.exitheading) and \"S\" in (t.entryheading, t.exitheading)) \n",
    "                                      OR \n",
    "                                      (\"E\" in (t.entryheading, t.exitheading) and \"W\" in (t.entryheading, t.exitheading)) \n",
    "                                      OR \n",
    "                                      (\"NE\" in (t.entryheading, t.exitheading) and \"SW\" in (t.entryheading, t.exitheading))  \n",
    "                                      OR \n",
    "                                      (\"SE\" in (t.entryheading, t.exitheading) and \"NW\" in (t.entryheading, t.exitheading)) \n",
    "                                 THEN\n",
    "                                  \"U\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"W\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"S\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"SE\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"E\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"N\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"NW\") \n",
    "                                 THEN\n",
    "                                  \"L\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"E\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"N\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"NW\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"W\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"S\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"SE\") \n",
    "                                 THEN\n",
    "                                  \"R\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"NW\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"W\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"S\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"SE\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"E\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"N\") \n",
    "                                 THEN\n",
    "                                  \"CL\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"N\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"NW\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"W\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"S\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"SE\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"E\") \n",
    "                                 THEN\n",
    "                                  \"CR\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"S\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"SE\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"E\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"N\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"NW\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"W\") \n",
    "                                 THEN\n",
    "                                  \"UL\" \n",
    "                                 when (t.entryheading=\"N\" and t.exitheading = \"SE\") \n",
    "                                      OR(t.entryheading=\"NW\" and t.exitheading = \"E\") \n",
    "                                      OR(t.entryheading=\"W\" and t.exitheading = \"NE\") \n",
    "                                      OR(t.entryheading=\"SW\" and t.exitheading = \"N\") \n",
    "                                      OR(t.entryheading=\"S\" and t.exitheading = \"NW\") \n",
    "                                      OR(t.entryheading=\"SE\" and t.exitheading = \"W\") \n",
    "                                      OR(t.entryheading=\"E\" and t.exitheading = \"SW\") \n",
    "                                      OR(t.entryheading=\"NE\" and t.exitheading = \"S\") \n",
    "                                 THEN\n",
    "                                  \"UR\" \n",
    "                               else null end direction\n",
    "                       FROM `kaggle-competition-datasets.geotab_intersection_congestion.\"\"\"+tab+\"\"\"` t\n",
    "                      WHERE city = '\"\"\"+c+\"\"\"' \n",
    "                       AND rowid \"\"\"+rowid_split\n",
    "                            \n",
    "        sql += \"\"\"\n",
    "               SELECT (SELECT MIN(d.DISTANCE) FROM UNNEST(NEAREST_CENTROIDS_DISTANCE) d) AS dist_to_cluster_center, \n",
    "                      CONCAT(m.city,\"_\",CAST(m.CENTROID_ID AS STRING)) AS city_cluster,\n",
    "                      m.* EXCEPT (nearest_centroids_distance, CENTROID_ID,Latitude,Longitude) \n",
    "                 FROM ML.PREDICT(MODEL `bqml_example.model_cluster_\"\"\"+c+\"\"\"`, \n",
    "                              (\"\"\"+features+\"\"\")) m\n",
    "               UNION ALL\"\"\"\n",
    "        \n",
    "        \n",
    "    return sql[:-len(\"UNION ALL\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "model_changed = False\n",
    "\n",
    "if model_changed:\n",
    "    sql=\"DROP TABLE IF EXISTS `bqml_example.city_cluster_train`\"\n",
    "    job_result=client.query(sql).result()\n",
    "    sql=\"DROP TABLE IF EXISTS `bqml_example.city_cluster_test`\"\n",
    "    job_result=client.query(sql).result()\n",
    "\n",
    "    \n",
    "sql = \"CREATE TABLE IF NOT EXISTS `bqml_example.city_cluster_train` as \" + feature_sql('ALL','=rowid', True, 'train')\n",
    "job_result=client.query(sql).result()\n",
    "sql = \"CREATE TABLE IF NOT EXISTS `bqml_example.city_cluster_test` as \" + feature_sql('ALL','=rowid', True, 'test')\n",
    "job_result=client.query(sql).result()\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>label</th>\n",
       "      <th>dist_to_cluster_center</th>\n",
       "      <th>city_cluster</th>\n",
       "      <th>city</th>\n",
       "      <th>EntryHeading</th>\n",
       "      <th>ExitHeading</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Weekend</th>\n",
       "      <th>Month</th>\n",
       "      <th>direction</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>6</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>8</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>9</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>7</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>10</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>15</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>32</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>18</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>11</td>\n",
       "      <td>False</td>\n",
       "      <td>12</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>14</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>9</td>\n",
       "      <td>False</td>\n",
       "      <td>7</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>8</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>7</td>\n",
       "      <td>False</td>\n",
       "      <td>11</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>16</td>\n",
       "      <td>False</td>\n",
       "      <td>7</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>10</td>\n",
       "      <td>False</td>\n",
       "      <td>8</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>47</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>8</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>6</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>0.506579</td>\n",
       "      <td>Chicago_6</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>E</td>\n",
       "      <td>E</td>\n",
       "      <td>13</td>\n",
       "      <td>False</td>\n",
       "      <td>10</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    label  dist_to_cluster_center city_cluster     city EntryHeading  \\\n",
       "0       0                0.506579    Chicago_6  Chicago            E   \n",
       "1       0                0.506579    Chicago_6  Chicago            E   \n",
       "2       0                0.506579    Chicago_6  Chicago            E   \n",
       "3      16                0.506579    Chicago_6  Chicago            E   \n",
       "4       0                0.506579    Chicago_6  Chicago            E   \n",
       "5       0                0.506579    Chicago_6  Chicago            E   \n",
       "6       0                0.506579    Chicago_6  Chicago            E   \n",
       "7      18                0.506579    Chicago_6  Chicago            E   \n",
       "8      32                0.506579    Chicago_6  Chicago            E   \n",
       "9       0                0.506579    Chicago_6  Chicago            E   \n",
       "10      0                0.506579    Chicago_6  Chicago            E   \n",
       "11      0                0.506579    Chicago_6  Chicago            E   \n",
       "12      0                0.506579    Chicago_6  Chicago            E   \n",
       "13      0                0.506579    Chicago_6  Chicago            E   \n",
       "14      0                0.506579    Chicago_6  Chicago            E   \n",
       "15      0                0.506579    Chicago_6  Chicago            E   \n",
       "16      0                0.506579    Chicago_6  Chicago            E   \n",
       "17     47                0.506579    Chicago_6  Chicago            E   \n",
       "18      0                0.506579    Chicago_6  Chicago            E   \n",
       "19      0                0.506579    Chicago_6  Chicago            E   \n",
       "\n",
       "   ExitHeading  Hour  Weekend  Month direction  \n",
       "0            E    10    False      6         C  \n",
       "1            E    14    False      9         C  \n",
       "2            E     6    False     12         C  \n",
       "3            E     8    False     12         C  \n",
       "4            E     9    False     11         C  \n",
       "5            E    15    False      7         C  \n",
       "6            E     3    False     10         C  \n",
       "7            E    15    False     12         C  \n",
       "8            E    18    False     12         C  \n",
       "9            E    11    False     12         C  \n",
       "10           E    14    False     11         C  \n",
       "11           E     9    False      7         C  \n",
       "12           E    17    False      8         C  \n",
       "13           E    10    False     11         C  \n",
       "14           E     7    False     11         C  \n",
       "15           E    16    False      7         C  \n",
       "16           E    10    False      8         C  \n",
       "17           E     8    False      9         C  \n",
       "18           E    13    False      6         C  \n",
       "19           E    13    False     10         C  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT t.TotalTimeStopped_p20 as label, cc.* except (rowid, TotalTimeStopped_p20,TotalTimeStopped_p50,TotalTimeStopped_p80,DistanceToFirstStop_p20, DistanceToFirstStop_p50, DistanceToFirstStop_p80) \n",
    "  FROM `bqml_example.city_cluster_train` cc,\n",
    "       `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    " WHERE cc.rowid = t.rowid\n",
    " LIMIT 20;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE FUNCTION `bqml_example.direction2degree`(dir string) AS (\n",
    " case dir\n",
    "   when \"C\" then\n",
    "    90\n",
    "   when 'CL' then\n",
    "    135\n",
    "   when \"L\" then\n",
    "    180\n",
    "   when 'UL' then\n",
    "    225\n",
    "   when \"U\" then\n",
    "    270\n",
    "   when 'UR' then\n",
    "    315\n",
    "   when \"R\" then\n",
    "    0\n",
    "   when 'CR' then\n",
    "    45\n",
    "    \n",
    "   when \"N\" then\n",
    "    90\n",
    "   when 'NW' then\n",
    "    135\n",
    "   when \"W\" then\n",
    "    180\n",
    "   when 'SW' then\n",
    "    225\n",
    "   when \"S\" then\n",
    "    270\n",
    "   when 'SE' then\n",
    "    315\n",
    "   when \"E\" then\n",
    "    0\n",
    "   when 'NE' then\n",
    "    45\n",
    " end\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Train"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Drop TotalTimeStopped_p20\n",
      "Drop TotalTimeStopped_p50\n",
      "Drop TotalTimeStopped_p80\n",
      "Drop DistanceToFirstStop_p20\n",
      "Drop DistanceToFirstStop_p50\n",
      "Drop DistanceToFirstStop_p80\n",
      "Done with TotalTimeStopped_p20\n",
      "Done with TotalTimeStopped_p50\n",
      "Done with TotalTimeStopped_p80\n",
      "Done with DistanceToFirstStop_p20\n",
      "Done with DistanceToFirstStop_p50\n",
      "Done with DistanceToFirstStop_p80\n",
      "CPU times: user 356 ms, sys: 160 ms, total: 516 ms\n",
      "Wall time: 13min 40s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "model_changed = True\n",
    "\n",
    "if model_changed:\n",
    "    for mn in mod_names:\n",
    "        sql=\"DROP MODEL IF EXISTS `bqml_example.model_\"+mn+\"`\"\n",
    "        client.query(sql).result()\n",
    "\n",
    "        print('Drop',mn)\n",
    "\n",
    "for mn in mod_names:\n",
    "    \n",
    "    sql=\"\"\"\n",
    "    CREATE MODEL IF NOT EXISTS `bqml_example.model_\"\"\"+mn+\"\"\"`\n",
    "    OPTIONS(MODEL_TYPE='linear_reg', \n",
    "            L2_REG=0.1,\n",
    "            LS_INIT_LEARN_RATE=0.4) AS \n",
    "    SELECT  t.\"\"\"+mn+\"\"\" as label,\n",
    "            cc.city_cluster,\n",
    "            cc.city,\n",
    "            cc.hour,\n",
    "            cc.weekend,\n",
    "            cc.month,\n",
    "            cc.direction,\n",
    "            cc.entryheading,\n",
    "            cc.exitheading,\n",
    "            round(sin(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_sin,\n",
    "            round(cos(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_cos,\n",
    "            round(sin(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "            round(cos(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "            round(sin(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "            round(cos(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "            round(cc.dist_to_cluster_center,8) dist_to_cluster_center\n",
    "      FROM `bqml_example.city_cluster_train` cc,\n",
    "           `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "     WHERE t.rowid = cc.rowid\n",
    "       AND cc.rowid < 2600000;\n",
    "    \"\"\"\n",
    "\n",
    "    client.query(sql).result()\n",
    "\n",
    "    print('Done with',mn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get training statistics\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 44 ms, sys: 4 ms, total: 48 ms\n",
      "Wall time: 2.81 s\n"
     ]
    },
    {
     "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>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>53.376111</td>\n",
       "      <td>58.268749</td>\n",
       "      <td>None</td>\n",
       "      <td>17846</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration       loss  eval_loss learning_rate  duration_ms\n",
       "0             0          0  53.376111  58.268749          None        17846"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL `bqml_example.model_TotalTimeStopped_p20`)\n",
    "ORDER BY iteration "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 48 ms, sys: 0 ns, total: 48 ms\n",
      "Wall time: 1.59 s\n"
     ]
    },
    {
     "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>input</th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>stddev</th>\n",
       "      <th>category_count</th>\n",
       "      <th>null_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>city_cluster</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>city</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>hour</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>12.341548</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>5.924782</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>weekend</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>month</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>9.094857</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>1.996294</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>direction</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>entryheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>exitheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>direction_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.760110</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.424616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>direction_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.000330</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.491869</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>entryheading_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.000639</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.679798</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>entryheading_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.009468</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>0.733339</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>exitheading_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.002760</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.681191</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>exitheading_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.001598</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.732100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>dist_to_cluster_center</td>\n",
       "      <td>0.003929</td>\n",
       "      <td>1.563004</td>\n",
       "      <td>0.377209</td>\n",
       "      <td>0.354977</td>\n",
       "      <td>0.198366</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     input       min        max       mean     median  \\\n",
       "0             city_cluster       NaN        NaN        NaN        NaN   \n",
       "1                     city       NaN        NaN        NaN        NaN   \n",
       "2                     hour  0.000000  23.000000  12.341548  12.000000   \n",
       "3                  weekend       NaN        NaN        NaN        NaN   \n",
       "4                    month  1.000000  12.000000   9.094857   9.000000   \n",
       "5                direction       NaN        NaN        NaN        NaN   \n",
       "6             entryheading       NaN        NaN        NaN        NaN   \n",
       "7              exitheading       NaN        NaN        NaN        NaN   \n",
       "8            direction_sin -1.000000   1.000000   0.760110   1.000000   \n",
       "9            direction_cos -1.000000   1.000000  -0.000330   0.000000   \n",
       "10        entryheading_sin -1.000000   1.000000  -0.000639   0.000000   \n",
       "11        entryheading_cos -1.000000   1.000000   0.009468  -0.000000   \n",
       "12         exitheading_sin -1.000000   1.000000   0.002760   0.000000   \n",
       "13         exitheading_cos -1.000000   1.000000  -0.001598   0.000000   \n",
       "14  dist_to_cluster_center  0.003929   1.563004   0.377209   0.354977   \n",
       "\n",
       "      stddev  category_count  null_count  \n",
       "0        NaN            39.0           0  \n",
       "1        NaN             4.0           0  \n",
       "2   5.924782             NaN           0  \n",
       "3        NaN             2.0           0  \n",
       "4   1.996294             NaN           0  \n",
       "5        NaN             8.0           0  \n",
       "6        NaN             8.0           0  \n",
       "7        NaN             8.0           0  \n",
       "8   0.424616             NaN           0  \n",
       "9   0.491869             NaN           0  \n",
       "10  0.679798             NaN           0  \n",
       "11  0.733339             NaN           0  \n",
       "12  0.681191             NaN           0  \n",
       "13  0.732100             NaN           0  \n",
       "14  0.198366             NaN           0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.FEATURE_INFO(MODEL `bqml_example.model_TotalTimeStopped_p20`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>processed_input</th>\n",
       "      <th>weight</th>\n",
       "      <th>category_weights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>city_cluster</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Boston_2', 'weight': -0.8926060...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>city</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Philadelphia', 'weight': 0.2615...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>hour</td>\n",
       "      <td>0.212911</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>weekend</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'false', 'weight': 0.68905702461...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>month</td>\n",
       "      <td>-0.087331</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>direction</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'C', 'weight': -0.08696935022589...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>entryheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'E', 'weight': 0.066067094842256...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>exitheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'E', 'weight': 0.036743184335222...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>direction_sin</td>\n",
       "      <td>-0.922568</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>direction_cos</td>\n",
       "      <td>-0.737986</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>entryheading_sin</td>\n",
       "      <td>-0.068031</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>entryheading_cos</td>\n",
       "      <td>0.113080</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>exitheading_sin</td>\n",
       "      <td>-0.055392</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>exitheading_cos</td>\n",
       "      <td>-0.021086</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>dist_to_cluster_center</td>\n",
       "      <td>0.013285</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>__INTERCEPT__</td>\n",
       "      <td>0.883898</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           processed_input    weight  \\\n",
       "0             city_cluster       NaN   \n",
       "1                     city       NaN   \n",
       "2                     hour  0.212911   \n",
       "3                  weekend       NaN   \n",
       "4                    month -0.087331   \n",
       "5                direction       NaN   \n",
       "6             entryheading       NaN   \n",
       "7              exitheading       NaN   \n",
       "8            direction_sin -0.922568   \n",
       "9            direction_cos -0.737986   \n",
       "10        entryheading_sin -0.068031   \n",
       "11        entryheading_cos  0.113080   \n",
       "12         exitheading_sin -0.055392   \n",
       "13         exitheading_cos -0.021086   \n",
       "14  dist_to_cluster_center  0.013285   \n",
       "15           __INTERCEPT__  0.883898   \n",
       "\n",
       "                                     category_weights  \n",
       "0   [{'category': 'Boston_2', 'weight': -0.8926060...  \n",
       "1   [{'category': 'Philadelphia', 'weight': 0.2615...  \n",
       "2                                                  []  \n",
       "3   [{'category': 'false', 'weight': 0.68905702461...  \n",
       "4                                                  []  \n",
       "5   [{'category': 'C', 'weight': -0.08696935022589...  \n",
       "6   [{'category': 'E', 'weight': 0.066067094842256...  \n",
       "7   [{'category': 'E', 'weight': 0.036743184335222...  \n",
       "8                                                  []  \n",
       "9                                                  []  \n",
       "10                                                 []  \n",
       "11                                                 []  \n",
       "12                                                 []  \n",
       "13                                                 []  \n",
       "14                                                 []  \n",
       "15                                                 []  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.WEIGHTS(MODEL  `bqml_example.model_TotalTimeStopped_p20`,\n",
    "    STRUCT(true AS standardize))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evaluate your model\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2.686802</td>\n",
       "      <td>29.483438</td>\n",
       "      <td>1.14179</td>\n",
       "      <td>1.473028</td>\n",
       "      <td>0.004152</td>\n",
       "      <td>0.010661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.686802           29.483438                 1.14179   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               1.473028  0.004152            0.010661  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"\"\"SELECT\n",
    "          *\n",
    "        FROM ML.EVALUATE(MODEL `bqml_example.model_TotalTimeStopped_p20`, (\n",
    "        SELECT  t.TotalTimeStopped_p20 as label, \n",
    "                cc.city_cluster,\n",
    "                cc.city,\n",
    "                cc.hour,\n",
    "                cc.weekend,\n",
    "                cc.month,\n",
    "                cc.direction,\n",
    "                cc.entryheading,\n",
    "                cc.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center,8) dist_to_cluster_center\n",
    "          FROM `bqml_example.city_cluster_train` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "         WHERE t.rowid = cc.rowid\n",
    "         AND t.rowid > 2600000))\"\"\"\n",
    "\n",
    "client.query(sql).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predict outcomes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start 0\n",
      "Done with TotalTimeStopped_p20\n",
      "Start 1\n",
      "Done with TotalTimeStopped_p50\n",
      "Start 2\n",
      "Done with TotalTimeStopped_p80\n",
      "Start 3\n",
      "Done with DistanceToFirstStop_p20\n",
      "Start 4\n",
      "Done with DistanceToFirstStop_p50\n",
      "Start 5\n",
      "Done with DistanceToFirstStop_p80\n"
     ]
    }
   ],
   "source": [
    "def pred(mn, debug=False):\n",
    "    \n",
    "    if debug:\n",
    "        lmt='LIMIT 10'\n",
    "    else:\n",
    "        lmt=''\n",
    "    \n",
    "    sql=\"\"\"\n",
    "    SELECT\n",
    "      RowId,\n",
    "      predicted_label as \"\"\"+mn+\"\"\"\n",
    "    FROM\n",
    "      ML.PREDICT(MODEL `bqml_example.model_\"\"\"+mn+\"\"\"`,\n",
    "        (\n",
    "        SELECT  cc.RowId, \n",
    "                cc.city_cluster,\n",
    "                cc.city,\n",
    "                cc.hour,\n",
    "                cc.weekend,\n",
    "                cc.month,\n",
    "                cc.direction,\n",
    "                cc.entryheading,\n",
    "                cc.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.direction)*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(cc.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center,8) dist_to_cluster_center\n",
    "          FROM `bqml_example.city_cluster_test` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.test` t\n",
    "         WHERE t.rowid = cc.rowid\n",
    "          \"\"\"+lmt+\"\"\"))\n",
    "        ORDER BY RowId ASC\"\"\"\n",
    "\n",
    "    return client.query(sql).to_dataframe()\n",
    "    \n",
    "df=None\n",
    "for i, mn in enumerate(mod_names):\n",
    "    if i == 0:\n",
    "        print('Start', i)\n",
    "        df = pred(mn)\n",
    "        df['RowId'] = df['RowId'].apply(str) + '_'+str(i)\n",
    "        df.rename(columns={'RowId': 'TargetId', mn: 'Target'}, inplace=True)\n",
    "    else:\n",
    "        print('Start', i)\n",
    "        df_temp = pred(mn)\n",
    "        df_temp['RowId'] = df_temp['RowId'].apply(str) + '_'+str(i)\n",
    "        df_temp.rename(columns={'RowId': 'TargetId', mn: 'Target'}, inplace=True)\n",
    "        df=df.append(df_temp)\n",
    "\n",
    "    print('Done with',mn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11522010, 2)\n"
     ]
    },
    {
     "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>TargetId</th>\n",
       "      <th>Target</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0_0</td>\n",
       "      <td>4.171987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1_0</td>\n",
       "      <td>0.323337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2_0</td>\n",
       "      <td>0.200592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3_0</td>\n",
       "      <td>0.359272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4_0</td>\n",
       "      <td>0.236528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>95</td>\n",
       "      <td>95_0</td>\n",
       "      <td>4.747800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>96</td>\n",
       "      <td>96_0</td>\n",
       "      <td>0.408395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>97</td>\n",
       "      <td>97_0</td>\n",
       "      <td>0.567075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>98</td>\n",
       "      <td>98_0</td>\n",
       "      <td>1.159531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>99</td>\n",
       "      <td>99_0</td>\n",
       "      <td>4.783735</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   TargetId    Target\n",
       "0       0_0  4.171987\n",
       "1       1_0  0.323337\n",
       "2       2_0  0.200592\n",
       "3       3_0  0.359272\n",
       "4       4_0  0.236528\n",
       "..      ...       ...\n",
       "95     95_0  4.747800\n",
       "96     96_0  0.408395\n",
       "97     97_0  0.567075\n",
       "98     98_0  1.159531\n",
       "99     99_0  4.783735\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df.shape)\n",
    "df.head(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output as CSV\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('submission.csv', index=False)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}