bigquery-geotab-bqml.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a copy of my public kaggle kernel: https://www.kaggle.com/joatom/bigquery-geotab-bqml.\n",
    "\n",
    "This solution led to the **1st Place** in the category **BigQuery ML Models built in SQL**.",
    "\n",
    "# Introduction\n",
    "This kernel includes all features and models that led to my final submission to the [BigQuery-GeoTab Competition](https://www.kaggle.com/c/bigquery-geotab-intersection-congestion).\n",
    "\n",
    "My personal objective of the competition is to tryout BigQuery (BQ) including the basic ML features. Therefore the kernel relies as much as possible on BQ. All features are generated in BQ with varying SQL-techniques. The prediction model is also build in BQ. \n",
    "\n",
    "Since the ML features in BQ are currently limited to Lineare Regression, KMeans and Logistic Regression there is a subcompetition within the competition, that ranks top BQML submissions separatly. (There is also a second subcompetition for using TensorFlow with BigQuery.) This kernel aims towards the BQML subcompetition. \n",
    "\n",
    "The kernel uses all the provided ML algorithms:\n",
    "- KMeans for Geo-Spatial Clustering\n",
    "- Logistic Regression for Adversarial Validation\n",
    "- Linear Regression for Prediction Model\n",
    "\n",
    "\n",
    "## Summary\n",
    "Features:\n",
    "- intersectionid + city (*i_id*)\n",
    "- city, hour, weekend, month\n",
    "- path, entry_street_name, exit_street_name\n",
    "- 20 clusters of intersections per city\n",
    "- Distance to the 5 nearest cluster center\n",
    "- 8 directions of turn (left, right, centered, uturn, centered-left, ...)\n",
    "    - Directions, entry- and exit-heading are not embedded directly. Instead they are translated into degrees (e.g. centered = 90deg). \n",
    "    - Afterwards the degree features are split additionaly into two features sin(feature_x_deg) and cos(feature_x_deg).\n",
    "    - Besides the direction of the turn, sin and cos feature are added for entry heading and exit heading \n",
    "- number of intersections per cluster\n",
    "- number of observations per city and hour\n",
    "- zipcode\n",
    "- population of zipcode\n",
    "- population of zipcode per intersection\n",
    "- intersections per zipcode\n",
    "- flag, if entry and exit are the same_street\n",
    "- intersection observation frequency\n",
    "- approching street length\n",
    "- approching street length fallback flag, if approching street length was derived from a more general approach due of missing data\n",
    "\n",
    "Features that didn't improve the model:\n",
    "- daytime, season (removed from model, since not improving)\n",
    "- road_type_entry, road_type_exit (removed from model, since not improving)\n",
    "\n",
    "Train-Valid-Split:\n",
    "- out of adversarial validation (drop 25 % of not test like data from train) (used by detailed model)\n",
    "- split by id range (used by general model)\n",
    "\n",
    "Train-Valid-Split that didn't improved the model:\n",
    "- remove outliers (target > Q99) from train (removed from model, since not improving)\n",
    "\n",
    "Model:\n",
    "- Linear Regression\n",
    "    - Detailed Model for known path and intersections in train and test\n",
    "    - General Model for unknown path and intersections\n",
    "\n",
    "## External data\n",
    "The kernel uses zipcodes and population data from the public *bigquery-public-data*-set:\n",
    "- bigquery-public-data.utility_us.zipcode_area\n",
    "- bigquery-public-data.census_bureau_usa.population_by_zip_2010\n",
    "\n",
    "The dataset can be included in the bigquery console (*add ressources*). I shared a preparation of the data (including references to intersections and a csv file for python users) [here](https://www.kaggle.com/joatom/bqml-population-of-zip-code-per-intersection).\n",
    "\n",
    "## Credits\n",
    "Some of the ideas are inspired by the following kernels. Please visit them 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\n",
    "- Intersection encoding in https://www.kaggle.com/danofer/baseline-feature-engineering-geotab-69-5-lb\n",
    "- Adversarial Validation: https://www.kaggle.com/tunguz/adversarial-geotab\n",
    "- Road encoding: https://www.kaggle.com/bgmello/how-one-percentile-affect-the-others)\n",
    "- Same_Street feature: https://www.kaggle.com/ragnar123/feature-engineering-and-forward-feature-selection\n",
    "- Remove Path and Intersection for general model: https://www.kaggle.com/gaborfodor/4-xgboost-general\n",
    "- Approching Street Lengt inspired by EntryLength: https://www.kaggle.com/dan3dewey/bbq-intersection-congestion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": 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,
    "_kg_hide-output": true
   },
   "outputs": [],
   "source": [
    "%load_ext google.cloud.bigquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "This query will process: 9.04 MB\n",
      "Total bytes processed (first execution): 0.00 MB\n",
      "Total bytes processed (cached): 0.00 MB\n"
     ]
    }
   ],
   "source": [
    "## Example to estimate quota usage\n",
    "\n",
    "# https://stackoverflow.com/questions/47172150/google-cloud-bigquery-python-how-to-get-the-bytes-processed-by-query\n",
    "\n",
    "sql = \"\"\"\n",
    "    SELECT t.city AS city\n",
    "      FROM `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "      LIMIT 20\"\"\"\n",
    "\n",
    "job_config = bigquery.QueryJobConfig()\n",
    "job_config.dry_run = True\n",
    "\n",
    "query_job = client.query(sql, job_config)\n",
    "\n",
    "print (\"This query will process: {0:.2f} MB\".format(\n",
    "    query_job.total_bytes_processed/1024/1024))\n",
    "\n",
    "\n",
    "query_job = client.query(sql)\n",
    "query_job.result()\n",
    "print (\"Total bytes processed (first execution): {0:.2f} MB\".format(\n",
    "    query_job.total_bytes_processed/1024/1024))\n",
    "\n",
    "\n",
    "query_job = client.query(sql)\n",
    "query_job.result()\n",
    "print (\"Total bytes processed (cached): {0:.2f} MB\".format(\n",
    "    query_job.total_bytes_processed/1024/1024))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": true
   },
   "outputs": [],
   "source": [
    "def run_sql(sql, dry=False):\n",
    "    if dry:\n",
    "        job_config = bigquery.QueryJobConfig()\n",
    "        job_config.dry_run = dry\n",
    "        query_job = client.query(sql, job_config)\n",
    "        print (\"This query will process: {0:.2f} MB\".format(query_job.total_bytes_processed/1024/1024))\n",
    "    else: \n",
    "        query_job = client.query(sql)\n",
    "        query_job.result()\n",
    "        print (\"Total bytes processed (cached): {0:.2f} MB\".format(query_job.total_bytes_processed/1024/1024))\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Some basic variables\n",
    "To reduce consumption of BQ quota the tables and models are only rebuild if necessary. To rebuild the entire project set **model_changed = True**. Be aware your google account might get charged. \n",
    "\n",
    "If you run on the free sandbox environment you don't have enough quota to run the entire kernel at once. You can then precede the kernel a few days after when your quota has been refreshed. In this case make sure to set the model_changed flag to **False** for the parts you already have built."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for documentation set model_changed here globaly, for development set later in the appropriate cells\n",
    "model_changed = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There will by 12 Models. One for each percentile (20, 50, 80) per category (TotalTimeStopped, DistanceToFirstStop) per model typ (general or detailed)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "mod_names= ['TotalTimeStopped_p20','TotalTimeStopped_p50','TotalTimeStopped_p80',\n",
    "            'DistanceToFirstStop_p20','DistanceToFirstStop_p50','DistanceToFirstStop_p80']\n",
    "\n",
    "# general model will look like [x+'_general' for x in mod_names]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Distinct cities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing query with job ID: ea53ba03-3f34-4cf1-b9b4-70eb88b84ef3\n",
      "Query executing: 0.74s\n",
      "Query complete after 1.04s\n"
     ]
    }
   ],
   "source": [
    "%%bigquery city_df --verbose\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": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Boston', 'Philadelphia', 'Atlanta', 'Chicago']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities = list(city_df['city'])\n",
    "cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cluster Intersections per City\n",
    "Build 20 geo spatial clusters of intersections per city:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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 = 20) AS\n",
    "    SELECT\n",
    "        latitude,\n",
    "        longitude\n",
    "    FROM\n",
    "      `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "    WHERE city = '\"\"\"+c+\"\"\"'\n",
    "    UNION DISTINCT\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).result()\n",
    "\n",
    "    print('Done with',c)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A basic analysis of the clusters can be found in an earlier kernel [here](https://www.kaggle.com/joatom/bqml-incl-intersection-clusters)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Match closest clusters to intersection\n",
    "Now match the five closest cluster center to an intersection. Add distance from intersection to the five cluster center. Count number of intersection of the five surrouding clusters.\n",
    "\n",
    "Safe this for further usage in the *CITY_CLUSTER* table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table CITY_CLUSTER created.\n"
     ]
    }
   ],
   "source": [
    "sql=\"\"\n",
    "\n",
    "for c in cities:\n",
    "    sql+= \"\"\"\n",
    "        SELECT\n",
    "          city,\n",
    "          intersectionid,\n",
    "          CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 1 THEN centroid_id END) AS string)) city_cluster_1,\n",
    "          CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 2 THEN centroid_id END) AS string)) city_cluster_2,\n",
    "          CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 3 THEN centroid_id END) AS string)) city_cluster_3,\n",
    "          CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 4 THEN centroid_id END) AS string)) city_cluster_4,\n",
    "          CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 5 THEN centroid_id END) AS string)) city_cluster_5,\n",
    "          MAX(CASE WHEN rk = 1 THEN distance END) dist_to_cluster_center_1,\n",
    "          MAX(CASE WHEN rk = 2 THEN distance END) dist_to_cluster_center_2,\n",
    "          MAX(CASE WHEN rk = 3 THEN distance END) dist_to_cluster_center_3,\n",
    "          MAX(CASE WHEN rk = 4 THEN distance END) dist_to_cluster_center_4,\n",
    "          MAX(CASE WHEN rk = 5 THEN distance END) dist_to_cluster_center_5,\n",
    "          COUNT(DISTINCT CONCAT(city, CAST(intersectionid AS string))) OVER(PARTITION BY CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 1 THEN centroid_id END) AS string))) intersections_per_cluster_1,\n",
    "          COUNT(DISTINCT CONCAT(city, CAST(intersectionid AS string))) OVER(PARTITION BY CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 2 THEN centroid_id END) AS string))) intersections_per_cluster_2,\n",
    "          COUNT(DISTINCT CONCAT(city, CAST(intersectionid AS string))) OVER(PARTITION BY CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 3 THEN centroid_id END) AS string))) intersections_per_cluster_3,\n",
    "          COUNT(DISTINCT CONCAT(city, CAST(intersectionid AS string))) OVER(PARTITION BY CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 4 THEN centroid_id END) AS string))) intersections_per_cluster_4,\n",
    "          COUNT(DISTINCT CONCAT(city, CAST(intersectionid AS string))) OVER(PARTITION BY CONCAT(city,\"_\", CAST(MAX(CASE WHEN rk = 5 THEN centroid_id END) AS string))) intersections_per_cluster_5\n",
    "        FROM (\n",
    "          SELECT\n",
    "            b.*,\n",
    "            RANK()OVER(PARTITION BY intersectionid ORDER BY b.distance) rk,\n",
    "            a.* EXCEPT(centroid_id,\n",
    "              NEAREST_CENTROIDS_DISTANCE)\n",
    "          FROM\n",
    "            ML.PREDICT(MODEL `bqml_example.model_cluster_\"\"\"+c+\"\"\"`,\n",
    "              (\n",
    "              SELECT\n",
    "                longitude,\n",
    "                latitude,\n",
    "                intersectionid,\n",
    "                city\n",
    "              FROM\n",
    "                `kaggle-competition-datasets.geotab_intersection_congestion.train`\n",
    "              WHERE city = '\"\"\"+c+\"\"\"'\n",
    "              UNION DISTINCT\n",
    "              SELECT\n",
    "                longitude,\n",
    "                latitude,\n",
    "                intersectionid,\n",
    "                city\n",
    "              FROM\n",
    "                `kaggle-competition-datasets.geotab_intersection_congestion.test`\n",
    "              WHERE city = '\"\"\"+c+\"\"\"'))a\n",
    "          CROSS JOIN\n",
    "            UNNEST(NEAREST_CENTROIDS_DISTANCE)b)\n",
    "        GROUP BY\n",
    "          city,\n",
    "          intersectionid\n",
    "          \n",
    "        UNION ALL\"\"\"\n",
    "    \n",
    "sql = sql[:-len(\"UNION ALL\")]\n",
    "\n",
    "\n",
    "#model_changed = False\n",
    "\n",
    "if model_changed:\n",
    "    sql=\"DROP TABLE IF EXISTS `bqml_example.city_cluster`\"\n",
    "    job_result=client.query(sql).result()\n",
    "\n",
    "sql = \"CREATE TABLE IF NOT EXISTS `bqml_example.city_cluster` as \" + sql\n",
    "\n",
    "job_result=client.query(sql).result()\n",
    "print('Table CITY_CLUSTER created.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Train and Test data with one cluster\n",
    "The following is a former version of the intersection clustering which combines train and test data with one cluster (the cluster where the intersection is located in). The query results are safed in the tables *CITY_CLUSTER_TRAIN* and *CITY_CLUSTER_TEST* which are used later on for adversarial validation. (Note: The two tables could probably be replaced by *CITY_CLUSTER* after a few changes to the adversarial validation model)."
   ]
  },
  {
   "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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done creating CITY_CLUSTER_TRAIN and CITY_CLUSTER_TEST\n"
     ]
    }
   ],
   "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",
    "print('Done creating CITY_CLUSTER_TRAIN and CITY_CLUSTER_TEST')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Complex features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Population of zipcode and observation frequency per intersection\n",
    "Retrieve population per zipcode. This kernel does not use age or gender specific information of the underlying data. \n",
    "\n",
    "Extract the features\n",
    "\n",
    "- population\n",
    "- zipcode,\n",
    "- intersections_per_zipcode,\n",
    "- pop_intersec_ratio (population / number of intersections of zipcode),\n",
    "- zip_code_na (if zipcode is unavailable for intersection) and\n",
    "- num_observations (number of intersections in train and test)."
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE TABLE IF NOT EXISTS\n",
    "  `bqml_example.zipcode_population` AS\n",
    "WITH\n",
    "\n",
    "  # population per zipcode\n",
    "\n",
    "  zip_info AS(\n",
    "  SELECT\n",
    "    SUM(pop.population) AS population,\n",
    "    zipcd.zipcode,\n",
    "    CASE zipcd.state_code\n",
    "      WHEN 'MA' THEN 'Boston'\n",
    "      WHEN 'IL' THEN 'Chicago'\n",
    "      WHEN 'GA' THEN 'Atlanta'\n",
    "      WHEN 'PA' THEN 'Philadelphia'\n",
    "  END\n",
    "    city,\n",
    "    zipcd.zipcode_geom\n",
    "  FROM\n",
    "    `bigquery-public-data.utility_us.zipcode_area` zipcd,\n",
    "    `bigquery-public-data.census_bureau_usa.population_by_zip_2010` pop\n",
    "  WHERE\n",
    "    zipcd.state_code IN ('MA',\n",
    "      'IL',\n",
    "      'PA',\n",
    "      'GA')\n",
    "    AND ( zipcd.city LIKE '%Atlanta%'\n",
    "      OR zipcd.city LIKE '%Boston%'\n",
    "      OR zipcd.city LIKE '%Chicago%'\n",
    "      OR zipcd.city LIKE '%Philadelphia%' )\n",
    "    AND SUBSTR(CONCAT('000000', pop.zipcode),-5) = zipcd.zipcode\n",
    "  GROUP BY\n",
    "    zipcd.zipcode,\n",
    "    CASE zipcd.state_code\n",
    "      WHEN 'MA' THEN 'Boston'\n",
    "      WHEN 'IL' THEN 'Chicago'\n",
    "      WHEN 'GA' THEN 'Atlanta'\n",
    "      WHEN 'PA' THEN 'Philadelphia'\n",
    "  END\n",
    "    ,\n",
    "    zipcd.zipcode_geom),\n",
    "  \n",
    "  # spatial test and train data\n",
    "  \n",
    "  train_and_test AS (\n",
    "  SELECT \n",
    "    t_all.intersectionId,\n",
    "    t_all.longitude,\n",
    "    t_all.latitude,\n",
    "    t_all.city,\n",
    "    count(1) num_observations\n",
    "  FROM (\n",
    "      SELECT\n",
    "        tr.intersectionId,\n",
    "        tr.longitude,\n",
    "        tr.latitude,\n",
    "        tr.city\n",
    "      FROM\n",
    "        `kaggle-competition-datasets.geotab_intersection_congestion.train` tr\n",
    "      UNION ALL\n",
    "      SELECT\n",
    "        ts.intersectionId,\n",
    "        ts.longitude,\n",
    "        ts.latitude,\n",
    "        ts.city\n",
    "      FROM\n",
    "        `kaggle-competition-datasets.geotab_intersection_congestion.test` ts\n",
    "      ) t_all\n",
    "  GROUP BY\n",
    "    t_all.intersectionId,\n",
    "    t_all.longitude,\n",
    "    t_all.latitude,\n",
    "    t_all.city\n",
    "  ),\n",
    "  \n",
    "  # Zipcode and Population per Intersection\n",
    "  \n",
    "  pop_per_intersection AS (\n",
    "  SELECT\n",
    "    t.intersectionId,\n",
    "    zi.population,\n",
    "    zi.zipcode,\n",
    "    t.city,\n",
    "    COUNT(DISTINCT t.intersectionId) OVER (PARTITION BY zi.zipcode) AS intersections_per_zipcode,\n",
    "    round(zi.population / COUNT(DISTINCT t.intersectionId) OVER (PARTITION BY zi.zipcode)) pop_intersec_ratio\n",
    "  FROM\n",
    "    train_and_test t,\n",
    "    zip_info zi\n",
    "  WHERE\n",
    "    t.city = zi.city\n",
    "    AND ST_CONTAINS( ST_GEOGFROMTEXT(zi.zipcode_geom),\n",
    "      ST_GeogPoint(longitude,\n",
    "        latitude)))\n",
    "  \n",
    "# fill empty zipcodes and population\n",
    "\n",
    "SELECT\n",
    "  t.city,\n",
    "  t.intersectionId,\n",
    "  coalesce(p.population,\n",
    "    round(AVG(p.population) OVER(PARTITION BY t.city))) AS population,\n",
    "  coalesce(p.zipcode, 'N/A') AS zipcode,\n",
    "  coalesce(p.intersections_per_zipcode,\n",
    "    round(AVG(p.intersections_per_zipcode) OVER(PARTITION BY t.city))) AS intersections_per_zipcode,\n",
    "    coalesce(pop_intersec_ratio,round(AVG(p.pop_intersec_ratio) OVER(PARTITION BY t.city))) AS  pop_intersec_ratio,\n",
    "  CASE\n",
    "    WHEN p.zipcode IS NULL THEN 1\n",
    "  ELSE\n",
    "    0\n",
    "  END AS zip_code_na,\n",
    "  t.num_observations\n",
    "FROM\n",
    "  train_and_test t\n",
    "LEFT OUTER JOIN\n",
    "  pop_per_intersection p\n",
    "ON\n",
    "  (t.city = p.city\n",
    "    AND t.intersectionId = p.intersectionId);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Length of intersection approaching street\n",
    "(The idea of *EntryLength* feature is derived from https://www.kaggle.com/dan3dewey/bbq-intersection-congestion.)\n",
    "\n",
    "**Step 1**\n",
    "\n",
    "To an entry street/heading find all data sets that contain this entry point as an exit street / heading. For example:\n",
    "- Entry: Broadway / NW\n",
    "- search all Exit Broadway / NW\n",
    "Calculate the distances between matching entry and exit. Make the weak assumption that the distance equals the street length. Pick shortest distance as approaching street length (*app_st_length*).\n"
   ]
  },
  {
   "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",
    "\n",
    "create table if not exists `bqml_example.approching_street`  as \n",
    "\n",
    "WITH\n",
    "  # entry and exit data per intersaction from train and test\n",
    "  tt AS (\n",
    "  SELECT\n",
    "    DISTINCT t.IntersectionId,\n",
    "    t.Longitude,\n",
    "    t.Latitude,\n",
    "    t.City,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "  UNION DISTINCT\n",
    "  SELECT\n",
    "    DISTINCT t.IntersectionId,\n",
    "    t.Longitude,\n",
    "    t.Latitude,\n",
    "    t.City,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.test` t ),\n",
    "  # calculate all distances to intersection where exitheading and exitstreet are the same as the current intersections entry\n",
    "  # assuming the oncoming street follows the same direction\n",
    "  dist_to_t AS(\n",
    "  SELECT\n",
    "    t.*,\n",
    "    st_distance(ST_GEOGPOINT(t.longitude,\n",
    "        t.latitude),\n",
    "      ST_GEOGPOINT(t_from.longitude,\n",
    "        t_from.latitude))dist\n",
    "  FROM\n",
    "    tt AS t,\n",
    "    tt AS t_from\n",
    "  WHERE\n",
    "    t.city=t_from.city\n",
    "    AND t.entrystreetname = t_from.ExitStreetName\n",
    "    AND t.EntryHeading = t_from.ExitHeading\n",
    "    AND t.Longitude <> t_from.Longitude\n",
    "    AND t.Latitude <> t_from.Latitude)\n",
    "  # get length of street approching intersection\n",
    "  # assuming same direction\n",
    "SELECT\n",
    "  t.intersectionid,\n",
    "  t.city,\n",
    "  t.ExitStreetName,\n",
    "  t.ExitHeading,\n",
    "  t.EntryStreetName,\n",
    "  t.EntryHeading,\n",
    "  MIN(dist) app_st_length\n",
    "FROM\n",
    "  dist_to_t t\n",
    "GROUP BY\n",
    "  t.intersectionid,\n",
    "  t.city,\n",
    "  t.ExitStreetName,\n",
    "  t.ExitHeading,\n",
    "  t.EntryStreetName,\n",
    "  t.EntryHeading;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step 2**\n",
    "\n",
    "Gather stats to decide how to impute missing *app_st_length*. \n",
    "Examine deviation from existing *app_st_length* to min and average values over several groups (City, City-EntryStreet, City-ExitStreet, City-Intersection).\n",
    "Calculate StdDev, Max and Mean for the deviations. Decide for a well balanced fallback solution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>stddev_en_avg</th>\n",
       "      <th>stddev_en_min</th>\n",
       "      <th>stddev_i_avg</th>\n",
       "      <th>stddev_i_min</th>\n",
       "      <th>stddev_ex_avg</th>\n",
       "      <th>stddev_ex_min</th>\n",
       "      <th>stddev_c_avg</th>\n",
       "      <th>stddev_c_min</th>\n",
       "      <th>MIN_en_avg</th>\n",
       "      <th>MIN_en_min</th>\n",
       "      <th>...</th>\n",
       "      <th>MAX_c_avg</th>\n",
       "      <th>MAX_c_min</th>\n",
       "      <th>AVG_en_avg</th>\n",
       "      <th>AVG_en_min</th>\n",
       "      <th>AVG_i_avg</th>\n",
       "      <th>AVG_i_min</th>\n",
       "      <th>AVG_ex_avg</th>\n",
       "      <th>AVG_ex_min</th>\n",
       "      <th>AVG_c_avg</th>\n",
       "      <th>AVG_c_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>580.529651</td>\n",
       "      <td>709.251672</td>\n",
       "      <td>519.111678</td>\n",
       "      <td>698.163281</td>\n",
       "      <td>627.989989</td>\n",
       "      <td>753.505526</td>\n",
       "      <td>692.549956</td>\n",
       "      <td>773.571695</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>15080.70123</td>\n",
       "      <td>15433.3</td>\n",
       "      <td>254.079831</td>\n",
       "      <td>276.170553</td>\n",
       "      <td>203.519534</td>\n",
       "      <td>188.406458</td>\n",
       "      <td>291.615807</td>\n",
       "      <td>315.035038</td>\n",
       "      <td>341.253946</td>\n",
       "      <td>401.926742</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   stddev_en_avg  stddev_en_min  stddev_i_avg  stddev_i_min  stddev_ex_avg  \\\n",
       "0     580.529651     709.251672    519.111678    698.163281     627.989989   \n",
       "\n",
       "   stddev_ex_min  stddev_c_avg  stddev_c_min  MIN_en_avg  MIN_en_min  ...  \\\n",
       "0     753.505526    692.549956    773.571695         0.0         0.0  ...   \n",
       "\n",
       "     MAX_c_avg  MAX_c_min  AVG_en_avg  AVG_en_min   AVG_i_avg   AVG_i_min  \\\n",
       "0  15080.70123    15433.3  254.079831  276.170553  203.519534  188.406458   \n",
       "\n",
       "   AVG_ex_avg  AVG_ex_min   AVG_c_avg   AVG_c_min  \n",
       "0  291.615807  315.035038  341.253946  401.926742  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "WITH\n",
    "  # get train and test data\n",
    "  tt AS(\n",
    "  SELECT\n",
    "    DISTINCT t.intersectionid,\n",
    "    t.city,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "  UNION DISTINCT\n",
    "  SELECT\n",
    "    DISTINCT t.intersectionid,\n",
    "    t.city,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.test` t ),\n",
    "  # gather deviation betweewn app_st_length and min/avg values of app_st_length for several partitions\n",
    "  devs AS(\n",
    "  SELECT\n",
    "    #t.*,\n",
    "    #round(a.app_st_length, 1) as app_st_length,\n",
    "    ABS(ROUND(a.app_st_length, 1)-AVG(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.EntryStreetName ))AS en_avg,\n",
    "    ABS(ROUND(a.app_st_length, 1)-MIN(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.EntryStreetName ))AS en_min,\n",
    "    ABS(ROUND(a.app_st_length, 1)-AVG(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.IntersectionId ))AS i_avg,\n",
    "    ABS(ROUND(a.app_st_length, 1)-MIN(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.IntersectionId ))AS i_min,\n",
    "    ABS(ROUND(a.app_st_length, 1)-AVG(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.ExitStreetName ))AS ex_avg,\n",
    "    ABS(ROUND(a.app_st_length, 1)- MIN(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city, t.ExitStreetName ))AS ex_min,\n",
    "    ABS(ROUND(a.app_st_length, 1)-AVG(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city ))AS c_avg,\n",
    "    ABS(ROUND(a.app_st_length, 1)-MIN(ROUND(a.app_st_length, 1) ) OVER (PARTITION BY t.city ))AS c_min\n",
    "  FROM\n",
    "    tt t\n",
    "  LEFT JOIN\n",
    "    `bqml_example.approching_street` a\n",
    "  ON\n",
    "    (t.intersectionid = a.intersectionid\n",
    "      AND t.city = a.city\n",
    "      AND t.ExitStreetName = a.ExitStreetName\n",
    "      AND t.ExitHeading = a.ExitHeading\n",
    "      AND t.EntryStreetName = a.EntryStreetName\n",
    "      AND t.EntryHeading = a.EntryHeading) )\n",
    "  # gather Stddev, Max and Mean for the deviations to decide which order the fallbacks to use\n",
    "SELECT\n",
    "  stddev(en_avg) stddev_en_avg,\n",
    "  stddev(en_min) stddev_en_min,\n",
    "  stddev(i_avg) stddev_i_avg,\n",
    "  stddev(i_min) stddev_i_min,\n",
    "  stddev(ex_avg) stddev_ex_avg,\n",
    "  stddev(ex_min) stddev_ex_min,\n",
    "  stddev(c_avg) stddev_c_avg,\n",
    "  stddev(c_min) stddev_c_min,\n",
    "  MIN(en_avg) MIN_en_avg,\n",
    "  MIN(en_min) MIN_en_min,\n",
    "  MIN(i_avg) MIN_i_avg,\n",
    "  MIN(i_min) MIN_i_min,\n",
    "  MIN(ex_avg) MIN_ex_avg,\n",
    "  MIN(ex_min) MIN_ex_min,\n",
    "  MIN(c_avg) MIN_c_avg,\n",
    "  MIN(c_min) MIN_c_min,\n",
    "  MAX(en_avg) MAX_en_avg,\n",
    "  MAX(en_min) MAX_en_min,\n",
    "  MAX(i_avg) MAX_i_avg,\n",
    "  MAX(i_min) MAX_i_min,\n",
    "  MAX(ex_avg) MAX_ex_avg,\n",
    "  MAX(ex_min) MAX_ex_min,\n",
    "  MAX(c_avg) MAX_c_avg,\n",
    "  MAX(c_min) MAX_c_min,\n",
    "  AVG(en_avg) AVG_en_avg,\n",
    "  AVG(en_min) AVG_en_min,\n",
    "  AVG(i_avg) AVG_i_avg,\n",
    "  AVG(i_min) AVG_i_min,\n",
    "  AVG(ex_avg) AVG_ex_avg,\n",
    "  AVG(ex_min) AVG_ex_min,\n",
    "  AVG(c_avg) AVG_c_avg,\n",
    "  AVG(c_min) AVG_c_min\n",
    "FROM\n",
    "  devs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The fallback order for missing ap_st_length is:\n",
    "1. I_AVG (due to small stddev and avg)\n",
    "- EN_AVG\n",
    "- EX_AVG\n",
    "- C_AVG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, the table *APPROCHING_STREET_IMPUTED* with the approaching street length and the fallback for missing values is created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "create table if not exists `bqml_example.approching_street_imputed` as\n",
    "\n",
    "# Get stats to decide which app_st_length fallback to use\n",
    "WITH\n",
    "  # get train and test data\n",
    "  tt AS(\n",
    "  SELECT\n",
    "    DISTINCT t.intersectionid,\n",
    "    t.city,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "  UNION DISTINCT\n",
    "  SELECT\n",
    "    DISTINCT t.intersectionid,\n",
    "    t.city,\n",
    "    t.ExitStreetName,\n",
    "    t.ExitHeading,\n",
    "    t.EntryStreetName,\n",
    "    t.EntryHeading\n",
    "  FROM\n",
    "    `kaggle-competition-datasets.geotab_intersection_congestion.test` t )\n",
    "  # measures: app_st_length incl. fallbacks\n",
    "SELECT\n",
    "  t.intersectionid,\n",
    "  t.city,\n",
    "  t.ExitStreetName,\n",
    "  t.ExitHeading,\n",
    "  t.EntryStreetName,\n",
    "  t.EntryHeading,\n",
    "  ROUND(coalesce(a.app_st_length,\n",
    "      # fallback I_AVG\n",
    "      AVG(a.app_st_length) OVER (PARTITION BY t.city, t.IntersectionId),\n",
    "      # fallback EN_AVG\n",
    "      AVG(a.app_st_length) OVER (PARTITION BY t.city, t.EntryStreetName),\n",
    "      # fallback EX_AVG\n",
    "      AVG(a.app_st_length) OVER (PARTITION BY t.city, t.ExitStreetName),\n",
    "      # fallback C_AVG\n",
    "      AVG(a.app_st_length) OVER (PARTITION BY t.city)), 1) AS app_st_length,\n",
    "  CASE\n",
    "    WHEN a.app_st_length IS NULL THEN 1\n",
    "  ELSE\n",
    "  0\n",
    "END\n",
    "  app_st_length_fallback\n",
    "FROM\n",
    "  tt t\n",
    "LEFT JOIN\n",
    "  `bqml_example.approching_street` a\n",
    "ON\n",
    "  (t.intersectionid = a.intersectionid\n",
    "    AND t.city = a.city\n",
    "    AND t.ExitStreetName = a.ExitStreetName\n",
    "    AND t.ExitHeading = a.ExitHeading\n",
    "    AND t.EntryStreetName = a.EntryStreetName\n",
    "    AND t.EntryHeading = a.EntryHeading);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Functions for direction features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function converts exit and entry headings into turn directions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE FUNCTION `bqml_example.direction`(entryheading string, exitheading string) \n",
    "AS (\n",
    " case \n",
    "   when entryheading = exitheading THEN\n",
    "    \"C\"\n",
    "   when (\"N\" in (entryheading, exitheading) and \"S\" in (entryheading, exitheading)) \n",
    "        OR \n",
    "        (\"E\" in (entryheading, exitheading) and \"W\" in (entryheading, exitheading)) \n",
    "        OR \n",
    "        (\"NE\" in (entryheading, exitheading) and \"SW\" in (entryheading, exitheading))  \n",
    "        OR \n",
    "        (\"SE\" in (entryheading, exitheading) and \"NW\" in (entryheading, exitheading)) \n",
    "   THEN\n",
    "    \"U\" \n",
    "   when (entryheading=\"N\" and exitheading = \"W\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"S\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"SE\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"E\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"N\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"NW\") \n",
    "   THEN\n",
    "    \"L\" \n",
    "   when (entryheading=\"N\" and exitheading = \"E\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"N\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"NW\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"W\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"S\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"SE\") \n",
    "   THEN\n",
    "    \"R\" \n",
    "   when (entryheading=\"N\" and exitheading = \"NW\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"W\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"S\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"SE\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"E\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"N\") \n",
    "   THEN\n",
    "    \"CL\" \n",
    "   when (entryheading=\"N\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"N\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"NW\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"W\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"S\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"SE\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"E\") \n",
    "   THEN\n",
    "    \"CR\" \n",
    "   when (entryheading=\"N\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"S\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"SE\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"E\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"N\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"NW\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"W\") \n",
    "   THEN\n",
    "    \"UL\" \n",
    "   when (entryheading=\"N\" and exitheading = \"SE\") \n",
    "        OR(entryheading=\"NW\" and exitheading = \"E\") \n",
    "        OR(entryheading=\"W\" and exitheading = \"NE\") \n",
    "        OR(entryheading=\"SW\" and exitheading = \"N\") \n",
    "        OR(entryheading=\"S\" and exitheading = \"NW\") \n",
    "        OR(entryheading=\"SE\" and exitheading = \"W\") \n",
    "        OR(entryheading=\"E\" and exitheading = \"SW\") \n",
    "        OR(entryheading=\"NE\" and exitheading = \"S\") \n",
    "   THEN\n",
    "    \"UR\" \n",
    "   else null end\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function converts turn directions into degrees, where Center is 90 degrees and uturn 270 degrees."
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 18,
     "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": [
    "This function converts road names into road categories. (it's not used in the final model) "
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE FUNCTION `bqml_example.road_encode`(road string) AS (\n",
    " case \n",
    "    when upper(road) like '%ROAD%' then 'ROAD'\n",
    "    when upper(road) like '%STREET%' then 'STREET'\n",
    "    when upper(road) like '%AVENUE%' then 'AVENUE'\n",
    "    when upper(road) like '%DRIVE%' then 'DRIVE'\n",
    "    when upper(road) like '%BROAD%' then 'BROAD'\n",
    "    when upper(road) like '%BOULEVARD%' then 'BOULEVARD'\n",
    "    else 'OTHERS'\n",
    "  end\n",
    ");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test-Validation-Split\n",
    "## Adversarial Validation\n",
    "(The approach follows the basic idea of this kernel: https://www.kaggle.com/tunguz/adversarial-geotab)\n",
    "\n",
    "**Step 1**\n",
    "\n",
    "Try to figure out how similar the test and train data are. For that pick some general features (in the SELECT-part) for test and train and concatenate (UNION ALL) the data. Set the variable *TARGET = 0* for train data and *TARGET = 1* for test. Build a classifier (Logistic Regression model) to predict *TARGET*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "#model_changed = False\n",
    "\n",
    "sql=\"\"\"\n",
    "CREATE MODEL IF NOT EXISTS `bqml_example.model_adversarial`\n",
    "    OPTIONS(MODEL_TYPE='logistic_reg', labels= ['target']) AS\n",
    "SELECT * FROM\n",
    "(SELECT 0 as target,\n",
    "       concat(cc.city,cast(t.intersectionid as string)) i_id,\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",
    "UNION ALL\n",
    "SELECT 1 as target,\n",
    "       concat(cc.city,cast(t.intersectionid as string)) i_id,\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",
    "\n",
    "if model_changed:\n",
    "    run_sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing query with job ID: 331ea70d-63b2-4471-87e3-779ec3c2fb39\n",
      "Query executing: 0.30s\n",
      "Query complete after 0.63s\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>0.633497</td>\n",
       "      <td>0.633057</td>\n",
       "      <td>0.2</td>\n",
       "      <td>22546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.585200</td>\n",
       "      <td>0.584335</td>\n",
       "      <td>0.4</td>\n",
       "      <td>27641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.549985</td>\n",
       "      <td>0.549326</td>\n",
       "      <td>0.8</td>\n",
       "      <td>26598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.511294</td>\n",
       "      <td>0.511748</td>\n",
       "      <td>1.6</td>\n",
       "      <td>42316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.491327</td>\n",
       "      <td>0.491644</td>\n",
       "      <td>3.2</td>\n",
       "      <td>32426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.476464</td>\n",
       "      <td>0.477831</td>\n",
       "      <td>0.8</td>\n",
       "      <td>25973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0.472095</td>\n",
       "      <td>0.473339</td>\n",
       "      <td>1.6</td>\n",
       "      <td>28363</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  0.633497   0.633057            0.2        22546\n",
       "1             0          1  0.585200   0.584335            0.4        27641\n",
       "2             0          2  0.549985   0.549326            0.8        26598\n",
       "3             0          3  0.511294   0.511748            1.6        42316\n",
       "4             0          4  0.491327   0.491644            3.2        32426\n",
       "5             0          5  0.476464   0.477831            0.8        25973\n",
       "6             0          6  0.472095   0.473339            1.6        28363"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery --verbose\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL `bqml_example.model_adversarial`)\n",
    "ORDER BY iteration "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Step 2**\n",
    "\n",
    "Evaluate the model to see if it can distinquish between test or train. If not (e.g. accuracy = 0.5) the data is assumed to be similar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#model_changed = False\n",
    "\n",
    "sql=\"\"\"SELECT\n",
    "          *\n",
    "        FROM ML.EVALUATE(MODEL `bqml_example.model_adversarial`, (\n",
    "        (SELECT 0 as target,\n",
    "       concat(cc.city,cast(t.intersectionid as string)) i_id,\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",
    "UNION ALL\n",
    "SELECT 1 as target,\n",
    "       concat(cc.city,cast(t.intersectionid as string)) i_id,\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",
    "        ))\"\"\"\n",
    "\n",
    "if model_changed:\n",
    "    client.query(sql).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Results of adversarial validation:\n",
    "\n",
    "* precision: 0.74410540861649255\n",
    "* recall: 0.99994428055521567\n",
    "* accuracy: 0.7622297087132579\n",
    "* f1_score: 0.85326003277995766\n",
    "* log_loss: 0.47265372942498035\n",
    "* roc_auc: 0.756451\n",
    "\n",
    "==> train and test don't look similar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Step 3 **\n",
    "\n",
    "Since train and test data don't look similar we try to get the test-alike train data.\n",
    "\n",
    "Create table with adversarial probability of train beeing classified as test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "#model_changed = False\n",
    "\n",
    "sql=\"\"\"\n",
    "create table if not exists `bqml_example.testalike_av` as\n",
    "SELECT\n",
    "          rowid,\n",
    "          (SELECT prob FROM UNNEST(predicted_target_probs) WHERE label=0 LIMIT 1) as prob_train,\n",
    "          (SELECT prob FROM UNNEST(predicted_target_probs) WHERE label=1 LIMIT 1) as prob_test\n",
    "        FROM ML.PREDICT(MODEL `bqml_example.model_adversarial`, (\n",
    "        (\n",
    "SELECT concat(cc.city,cast(t.intersectionid as string)) i_id,\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",
    "       t.rowid\n",
    " FROM `bqml_example.city_cluster_train` cc,\n",
    "      `kaggle-competition-datasets.geotab_intersection_congestion.train` t\n",
    "WHERE t.rowid = cc.rowid)\n",
    "        ))\"\"\"\n",
    "\n",
    "if model_changed:\n",
    "    run_sql(sql) #,dry=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Examin the test-alike probability of the train data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Executing query with job ID: 49dfc408-f7f0-417b-9218-ec2c652fd837\n",
      "Query executing: 0.36s\n",
      "Query complete after 0.66s\n"
     ]
    }
   ],
   "source": [
    "%%bigquery df_av --verbose\n",
    "select cc.city, cc.city_cluster, av.prob_test \n",
    "  from `bqml_example.testalike_av` av,\n",
    "       `bqml_example.city_cluster_train` cc\n",
    " where cc.rowid = av.rowid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[<matplotlib.axes._subplots.AxesSubplot object at 0x7f20403494a8>]],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAY0AAAEICAYAAACj2qi6AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAGjRJREFUeJzt3X+Q3XV97/HnWyiSEiEBdC9NosESrZFUJHshHcd2IwwERIO30IGhErxopgx6vdf0NrHVSy/CNdZLqbSKN5WU4GgjpXVI+SGNkR1LBxSiSArUsmIKIRgK+SGriEbf94/ziT2uZ3M+e7K75+A+HzNn9vv9fD/fz+edc7Lnle+PcxKZiSRJNV7U7QIkSS8choYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSGNk4jIiDiu23VIE8nQkLooIi6KiLvGaaytEXHqeIwljcbQkCpExMHdrkHqBYaGprTyr/P3R8RDEbErIv4qIg6NiIGI2BYRKyPiO8Bflf7vioihiNgZERsi4ldGDHlmRDwaEU9HxEcjYtTfsYh4DfBJ4DciYjgidpf2F0fE/42IxyJiR0R8MiKmlW1HR8QtEbG71PCPEfGiiPg08HLg78tYfzARz5dkaEhwAXA68KvAq4APlPb/BBwJvAJYHhFvAj4M/A5wDPBvwPoRY70N6AdOBJYC/3W0STPzYeD3gLszc3pmziibPlLqOAE4DpgF/K+ybQWwDXgp0Af8YWOofDvwGPCWMtafjP1pkNozNCT4i8x8PDN3AlcC55f2nwCXZebzmfkcjXBZm5lfy8zngffTOEqY2zTWRzJzZ2Y+BvxZ01hVIiKAdwH/o4zzLPB/gPNKlx/RCKxXZOaPMvMf0y+Q0yQyNCR4vGn534B9p5z+PTN/0LTtV8p2ADJzGHiGxpFAu7FqvRT4ZWBzOQW1G/hCaQf4KDAE/EM5DbZqjONLB8TQkGBO0/LLge1leeS/4LfTOFUFQEQcBhwFPFEx1mhGzvE08Bzw2sycUR5HZOZ0gMx8NjNXZOYrgbcA74uIU0YZSxp3hoYEl0bE7Ig4ksY1gs+N0u+zwDsi4oSIeDGN00ZfycytTX3+Z0TMjIg5wHv3M9Y+O4DZEXEIQGb+BPhL4OqIeBlARMyKiNPL8lkRcVw5jfVd4MflsW+sV47pTy6NkaEhNcLgH4BHy+OKVp0ycxPwQeBvgSdpXDg/b0S3m4HNwP3ArcB1beb+EvAg8J2IeLq0raRxCuqeiPgu8EXg1WXbvLI+DNwNfCIzB8u2DwMfKKe1fr/NvFJHwmtomsoiYivwzsz8YrdrkV4IPNKQJFUzNKQJVj6cN9zi8clu1yaNlaenJEnVPNKQJFX7hfsStqOPPjrnzp3b0b7f+973OOyww8a3oHFibZ2xts5YW2deyLVt3rz56cx86agd9snMX6jHwoULs1N33nlnx/tONGvrjLV1xto680KuDbgvK95jPT0lSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqvYL9zUiknrH3FW3jvuYKxbs5aKKcbeufvO4z63KI42ImBERN0XEv0TEwxHxGxFxZERsjIhHys+ZpW9ExDURMRQRD0TEiU3jLCv9H4mIZU3tCyNiS9nnmvJfWTLaHJKk7qg9PfUx4AuZ+WvA64CHgVXApsycB2wq6wBn0PgvKecBy4FroREAwGXAycBJwGVNIXBt6btvvyWlfbQ5JEld0DY0IuJw4Dcp/9dxZv4wM3cDS4F1pds64OyyvBS4oXwH1j3AjIg4Bjgd2JiZOzNzF7ARWFK2HZ6Zd5cvzbphxFit5pAkdUHb/4QpIk4A1gAP0TjK2Ay8F3giM2c09duVmTMj4hZgdWbeVdo3ASuBAeDQzLyitH8QeA4YLP1PLe1vBFZm5lkRsbvVHC1qXE7jSIW+vr6F69ev7+S5YHh4mOnTp3e070Szts5YW2fGq7YtT+wZh2p+Vt802PFc+34LZh0x7nO380J+TRcvXrw5M/vbjVNzIfxg4ETgPZn5lYj4GPs/TRQt2rKD9mqZuYZGsNHf358DAwNj2f2nBgcH6XTfiWZtnbG2zoxXbTUXrMdqxYK9XLWl/VvX1gsGxn3udqbCa1pzTWMbsC0zv1LWb6IRIjvKqSXKz6ea+s9p2n82sL1N++wW7exnDklSF7QNjcz8DvB4RLy6NJ1C41TVBmDfHVDLgJvL8gbgwnIX1SJgT2Y+CdwBnBYRM8sF8NOAO8q2ZyNiUblr6sIRY7WaQ5LUBbWf03gP8JmIOAR4FHgHjcC5MSIuBh4Dzi19bwPOBIaA75e+ZObOiPgQcG/pd3lm7izLlwDXA9OA28sDYPUoc0iSuqAqNDLzfqDVBZJTWvRN4NJRxlkLrG3Rfh9wfIv2Z1rNIUnqDr9GRJJUzdCQJFUzNCRJ1QwNSVI1Q0OSVM3QkCRVMzQkSdUMDUlSNUNDklTN0JAkVTM0JEnVDA1JUjVDQ5JUzdCQJFUzNCRJ1QwNSVI1Q0OSVM3QkCRVMzQkSdUMDUlSNUNDklTN0JAkVTM0JEnVDA1JUjVDQ5JUrSo0ImJrRGyJiPsj4r7SdmREbIyIR8rPmaU9IuKaiBiKiAci4sSmcZaV/o9ExLKm9oVl/KGyb+xvDklSd4zlSGNxZp6Qmf1lfRWwKTPnAZvKOsAZwLzyWA5cC40AAC4DTgZOAi5rCoFrS999+y1pM4ckqQsO5PTUUmBdWV4HnN3UfkM23APMiIhjgNOBjZm5MzN3ARuBJWXb4Zl5d2YmcMOIsVrNIUnqgmi8T7fpFPFtYBeQwP/LzDURsTszZzT12ZWZMyPiFmB1Zt5V2jcBK4EB4NDMvKK0fxB4Dhgs/U8t7W8EVmbmWaPN0aK+5TSOVOjr61u4fv36Dp4KGB4eZvr06R3tO9GsrTPW1pnxqm3LE3vGoZqf1TcNdjzXvt+CWUeM+9ztvJBf08WLF29uOpM0qoMr53tDZm6PiJcBGyPiX/bTN1q0ZQft1TJzDbAGoL+/PwcGBsay+08NDg7S6b4Tzdo6Y22dGa/aLlp164EXM8KKBXu5akv7t66tFwyM+9ztTIXXtOr0VGZuLz+fAj5P45rEjnJqifLzqdJ9GzCnaffZwPY27bNbtLOfOSRJXdA2NCLisIh4yb5l4DTgn4ENwL47oJYBN5flDcCF5S6qRcCezHwSuAM4LSJmlgvgpwF3lG3PRsSictfUhSPGajWHJKkLak5P9QGfL3fBHgx8NjO/EBH3AjdGxMXAY8C5pf9twJnAEPB94B0AmbkzIj4E3Fv6XZ6ZO8vyJcD1wDTg9vIAWD3KHJKkLmgbGpn5KPC6Fu3PAKe0aE/g0lHGWgusbdF+H3B87RySpO7wE+GSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqmZoSJKqVYdGRBwUEV+PiFvK+rER8ZWIeCQiPhcRh5T2F5f1obJ9btMY7y/t34yI05val5S2oYhY1dTecg5JUneM5UjjvcDDTesfAa7OzHnALuDi0n4xsCszjwOuLv2IiPnAecBrgSXAJ0oQHQR8HDgDmA+cX/rubw5JUhdUhUZEzAbeDHyqrAfwJuCm0mUdcHZZXlrWKdtPKf2XAusz8/nM/DYwBJxUHkOZ+Whm/hBYDyxtM4ckqQtqjzT+DPgD4Cdl/Shgd2buLevbgFlleRbwOEDZvqf0/2n7iH1Ga9/fHJKkLji4XYeIOAt4KjM3R8TAvuYWXbPNttHaWwXX/vq3qnE5sBygr6+PwcHBVt3aGh4e7njfiWZtnbG2zoxXbSsW7G3faYz6ptWN243ndiq8pm1DA3gD8NaIOBM4FDicxpHHjIg4uBwJzAa2l/7bgDnAtog4GDgC2NnUvk/zPq3an97PHD8jM9cAawD6+/tzYGCg4o/18wYHB+l034lmbZ2xts6MV20Xrbr1wIsZYcWCvVy1pf1b19YLBsZ97namwmva9vRUZr4/M2dn5lwaF7K/lJkXAHcC55Ruy4Cby/KGsk7Z/qXMzNJ+Xrm76lhgHvBV4F5gXrlT6pAyx4ayz2hzSJK64EA+p7ESeF9EDNG4/nBdab8OOKq0vw9YBZCZDwI3Ag8BXwAuzcwfl6OIdwN30Lg768bSd39zSJK6oOb01E9l5iAwWJYfpXHn08g+PwDOHWX/K4ErW7TfBtzWor3lHJKk7vAT4ZKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkam1DIyIOjYivRsQ3IuLBiPjfpf3YiPhKRDwSEZ+LiENK+4vL+lDZPrdprPeX9m9GxOlN7UtK21BErGpqbzmHJKk7ao40ngfelJmvA04AlkTEIuAjwNWZOQ/YBVxc+l8M7MrM44CrSz8iYj5wHvBaYAnwiYg4KCIOAj4OnAHMB84vfdnPHJKkLmgbGtkwXFZ/qTwSeBNwU2lfB5xdlpeWdcr2UyIiSvv6zHw+M78NDAEnlcdQZj6amT8E1gNLyz6jzSFJ6oKDazqVo4HNwHE0jgq+BezOzL2lyzZgVlmeBTwOkJl7I2IPcFRpv6dp2OZ9Hh/RfnLZZ7Q5Rta3HFgO0NfXx+DgYM0f6+cMDw93vO9Es7bOWFtnxqu2FQv2tu80Rn3T6sbtxnM7FV7TqtDIzB8DJ0TEDODzwGtadSs/Y5Rto7W3OtrZX/9W9a0B1gD09/fnwMBAq25tDQ4O0um+E83aOmNtnRmv2i5adeuBFzPCigV7uWpL+7eurRcMjPvc7UyF13RMd09l5m5gEFgEzIiIfa/cbGB7Wd4GzAEo248Adja3j9hntPan9zOHJKkLau6eemk5wiAipgGnAg8DdwLnlG7LgJvL8oayTtn+pczM0n5eubvqWGAe8FXgXmBeuVPqEBoXyzeUfUabQ5LUBTWnp44B1pXrGi8CbszMWyLiIWB9RFwBfB24rvS/Dvh0RAzROMI4DyAzH4yIG4GHgL3ApeW0FxHxbuAO4CBgbWY+WMZaOcockqQuaBsamfkA8PoW7Y/SuPNpZPsPgHNHGetK4MoW7bcBt9XOIUnqDj8RLkmqZmhIkqoZGpKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmq1jY0ImJORNwZEQ9HxIMR8d7SfmREbIyIR8rPmaU9IuKaiBiKiAci4sSmsZaV/o9ExLKm9oURsaXsc01ExP7mkCR1R82Rxl5gRWa+BlgEXBoR84FVwKbMnAdsKusAZwDzymM5cC00AgC4DDgZOAm4rCkEri199+23pLSPNockqQvahkZmPpmZXyvLzwIPA7OApcC60m0dcHZZXgrckA33ADMi4hjgdGBjZu7MzF3ARmBJ2XZ4Zt6dmQncMGKsVnNIkrogGu/TlZ0j5gJfBo4HHsvMGU3bdmXmzIi4BVidmXeV9k3ASmAAODQzryjtHwSeAwZL/1NL+xuBlZl5VkTsbjVHi7qW0zhSoa+vb+H69eur/0zNhoeHmT59ekf7TjRr64y1dWa8atvyxJ5xqOZn9U2DHc+177dg1hHjPnc7L+TXdPHixZszs7/dOAfXThgR04G/Bf57Zn63XHZo2bVFW3bQXi0z1wBrAPr7+3NgYGAsu//Un3/mZq6663sd7Xsgtq5+c9s+g4ODdPrnmmjW1pmpUNtFq2498GJGWLFgL1dtaf/WtfWCgXGfu52p8JpW3T0VEb9EIzA+k5l/V5p3lFNLlJ9PlfZtwJym3WcD29u0z27Rvr85JEldUHP3VADXAQ9n5p82bdoA7LsDahlwc1P7heUuqkXAnsx8ErgDOC0iZpYL4KcBd5Rtz0bEojLXhSPGajWHJKkLak5PvQF4O7AlIu4vbX8IrAZujIiLgceAc8u224AzgSHg+8A7ADJzZ0R8CLi39Ls8M3eW5UuA64FpwO3lwX7mkCR1QdvQKBe0R7uAcUqL/glcOspYa4G1Ldrvo3FxfWT7M63mkCR1h58IlyRVMzQkSdUMDUlSNUNDklTN0JAkVTM0JEnVDA1JUjVDQ5JUzdCQJFUzNCRJ1QwNSVI1Q0OSVM3QkCRVMzQkSdUMDUlSNUNDklTN0JAkVTM0JEnVDA1JUjVDQ5JUzdCQJFUzNCRJ1QwNSVI1Q0OSVM3QkCRVaxsaEbE2Ip6KiH9uajsyIjZGxCPl58zSHhFxTUQMRcQDEXFi0z7LSv9HImJZU/vCiNhS9rkmImJ/c0iSuqfmSON6YMmItlXApsycB2wq6wBnAPPKYzlwLTQCALgMOBk4CbisKQSuLX337bekzRySpC5pGxqZ+WVg54jmpcC6srwOOLup/YZsuAeYERHHAKcDGzNzZ2buAjYCS8q2wzPz7sxM4IYRY7WaQ5LUJdF4r27TKWIucEtmHl/Wd2fmjKbtuzJzZkTcAqzOzLtK+yZgJTAAHJqZV5T2DwLPAYOl/6ml/Y3Aysw8a7Q5RqlvOY2jFfr6+hauX79+TE/CPk/t3MOO5zra9YAsmHVE2z7Dw8NMnz59EqoZO2vrzFSobcsTe8ahmp/VN42q39Oa36vx9kJ+TRcvXrw5M/vbjXPwuFYF0aItO2gfk8xcA6wB6O/vz4GBgbEOAcCff+Zmrtoy3k9Je1svGGjbZ3BwkE7/XBPN2jozFWq7aNWtB17MCCsW7K36Pa35vRpvU+E17fTuqR3l1BLl51OlfRswp6nfbGB7m/bZLdr3N4ckqUs6DY0NwL47oJYBNze1X1juoloE7MnMJ4E7gNMiYma5AH4acEfZ9mxELCp3TV04YqxWc0iSuqTtMV5E/DWNaxJHR8Q2GndBrQZujIiLgceAc0v324AzgSHg+8A7ADJzZ0R8CLi39Ls8M/ddXL+Exh1a04Dby4P9zCFJ6pK2oZGZ54+y6ZQWfRO4dJRx1gJrW7TfBxzfov2ZVnNIkrrHT4RLkqoZGpKkaoaGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSapmaEiSqhkakqRqhoYkqZqhIUmqZmhIkqoZGpKkaoaGJKmaoSFJqtb+f2eX9II3d9WtY+q/YsFeLhrjPpoaPNKQJFUzNCRJ1QwNSVI1Q0OSVM3QkCRVMzQkSdUMDUlSNT+nIekX0lg/mzIeVizYy8Ckzzq5ev5IIyKWRMQ3I2IoIlZ1ux5Jmsp6OjQi4iDg48AZwHzg/IiY392qJGnq6unQAE4ChjLz0cz8IbAeWNrlmiRpyorM7HYNo4qIc4AlmfnOsv524OTMfPeIfsuB5WX11cA3O5zyaODpDvedaNbWGWvrjLV15oVc2ysy86XtBun1C+HRou3nUi4z1wBrDniyiPsys/9Ax5kI1tYZa+uMtXVmKtTW66entgFzmtZnA9u7VIskTXm9Hhr3AvMi4tiIOAQ4D9jQ5Zokacrq6dNTmbk3It4N3AEcBKzNzAcncMoDPsU1gaytM9bWGWvrzC98bT19IVyS1Ft6/fSUJKmHGBqSpGpTMjTafTVJRPxmRHwtIvaWz4r0Um3vi4iHIuKBiNgUEa/oodp+LyK2RMT9EXHXZH56v/brZiLinIjIiJi02yIrnreLIuLfy/N2f0S8s1dqK31+p/ydezAiPtsrtUXE1U3P2b9GxO4equ3lEXFnRHy9/K6e2SN1vaK8bzwQEYMRMXvMk2TmlHrQuKD+LeCVwCHAN4D5I/rMBX4duAE4p8dqWwz8clm+BPhcD9V2eNPyW4Ev9Eptpd9LgC8D9wD9vVIbcBHwF5P192yMtc0Dvg7MLOsv65XaRvR/D40bZXqiNhoXnS8py/OBrT1S198Ay8rym4BPj3WeqXik0farSTJza2Y+APykB2u7MzO/X1bvofHZlV6p7btNq4fR4oOY3aqt+BDwJ8APJqmusdTWDTW1vQv4eGbuAsjMp3qotmbnA389KZXV1ZbA4WX5CCbn82U1dc0HNpXlO1tsb2sqhsYs4PGm9W2lrReMtbaLgdsntKL/UFVbRFwaEd+i8eb833qltoh4PTAnM2+ZpJr2qX1Nf7ucMrgpIua02D4Ramp7FfCqiPiniLgnIpb0UG1A45QLcCzwpUmoC+pq+2PgdyNiG3AbjSOhXqjrG8Bvl+W3AS+JiKPGMslUDI2qrybpkuraIuJ3gX7goxNaUdOULdpafaXLxzPzV4GVwAcmvKqG/dYWES8CrgZWTFI9zWqet78H5mbmrwNfBNZNeFUNNbUdTOMU1QCNf81/KiJmTHBdMLbf0/OAmzLzxxNYT7Oa2s4Hrs/M2cCZwKfL38Nu1/X7wG9FxNeB3wKeAPaOZZKpGBq9/NUkVbVFxKnAHwFvzczne6m2JuuBsye0ov/QrraXAMcDgxGxFVgEbJiki+Ftn7fMfKbpdfxLYOEk1FVVW+lzc2b+KDO/TePLQOf1SG37nMfknZqCutouBm4EyMy7gUNpfGFgV+vKzO2Z+V8y8/U03kPIzD1jmmUyLhz10oPGv5wepXE4u+9i0WtH6Xs9k3shvG1twOtpXOya12vPW3NNwFuA+3qlthH9B5m8C+E1z9sxTctvA+7podqWAOvK8tE0Tn8c1Qu1lX6vBrZSPqjcQ8/b7cBFZfk1NN68J7TGyrqOBl5Ulq8ELh/zPJP1RPfSg8bh4r+WN98/Km2X0/iXO8B/ppHa3wOeAR7sodq+COwA7i+PDT1U28eAB0tdd+7vjXuyaxvRd9JCo/J5+3B53r5Rnrdf66HaAvhT4CFgC3Ber9RW1v8YWD1ZNY3heZsP/FN5Te8HTuuRus4BHil9PgW8eKxz+DUikqRqU/GahiSpQ4aGJKmaoSFJqmZoSJKqGRqSpGqGhiSpmqEhSar2/wHsraM4U+0vmAAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_av.hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[<matplotlib.axes._subplots.AxesSubplot object at 0x7f203e2f3a20>,\n",
       "        <matplotlib.axes._subplots.AxesSubplot object at 0x7f203e609780>],\n",
       "       [<matplotlib.axes._subplots.AxesSubplot object at 0x7f203e525cf8>,\n",
       "        <matplotlib.axes._subplots.AxesSubplot object at 0x7f203e2622b0>]],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZgAAAENCAYAAAAykHOlAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvnQurowAAIABJREFUeJzt3Xu0HFWZ/vHvYyCKQEwwgQVJIIxEBS8EiBCXoyIIBFCCjigwSgYZowjOeBsNLn8DgijoEmcYEY0SCSIERIEokZCJeEEJ5gQZMSImQiCHcElIuImokPf3R+2WSqfPvat79+H5rHXW6X5rV9V7OrXzVu2qrlJEYGZm1mzPa3cCZmY2PLnAmJlZJVxgzMysEi4wZmZWCRcYMzOrhAuMmZlVwgVmmJK0WtKb252HmT13ucB0GEk/kbRR0vNLsYslfbai9f2LpJuqWLbZUKSdqD9LeiL1ieskTRziMkPSHs3K8bnOBaaDSJoEvB4I4Ki2JmOWh7dGxHbAzsCDwP+0OR8rcYHpLCcAS4GLgZkAkmYB/wx8Iu3J/aB+Jkn7S7pZ0iOS7pf0FUkjS9ND0gckrUx7gheosCfwNeC1admPpPZHSvq1pMckrZF0RuV/uVkvIuIp4CpgLwBJL5J0iaR1ku6R9GlJz0vT9pD0U0mPSlov6YoU/1la3P+l7f1dKf4+SaskbZC0QNIutfX21Hda+sdnzAWms5wAfCf9HCZpp4iYk95/ISK2i4i3NpjvGeAjwFjgtcDBwAfr2rwFeA2wN/BO4LCIuAP4AHBzWvbo1PZPKZfRwJHAyZKObuLfaTYgkl4IvItiBwyKI5kXAf8AvJFiez0xTTsLuAEYA0xIbYmIN6Tpe6ft/QpJBwGfp+gTOwP3APPrVr9F32n239epXGA6hKR/BHYDroyI5cAfgeP7M29ELI+IpRHxdESsBr5O0enKzomIRyLiXuBGYEovy/tJRNweEZsi4jfA5Q2WZ9YK16Qj68eAQ4AvShpBUWxOi4jH0zb/JeA9aZ6/UfSlXSLiqYjo7RzjPwNzI+LWiPgLcBrFEf2kUpt+953nGheYzjETuCEi1qf3l6VYnyS9VNIPJT0g6THgcxRHM2UPlF4/CWzXy/IOkHRjGn54lOIop355Zq1wdDqyfj5wKvBTiqOSkRRHGzX3AOPT608AAn4laYWk9/ay/F3Ky4mIJ4CHS8uCAfSd5xoXmA4gaRuKQ+83piLxAMWQ196S9qY46d+bC4HfA5MjYhTwKYoO1h+Nln0ZsACYGBEvojhP43Fna5uIeCYivk8xHDyNZ49SanYF7kttH4iI90XELsD7ga/2cuXY2vJyJG0LvLi2LOudC0xnOJqi4+xFcfg9BdgT+DnF2PKDFGPNPdmeYgjhCUkvB04ewLofBCaULwpIy9sQEU9J2p9+DtWZVSVdlDKD4rzKb4ErgbMlbS9pN+CjwKWp7TGSJqRZN1LsRD2T3tf3pcuAEyVNSV8N+BxwSxp2sz64wHSGmcC3IuLetPf1QEQ8AHyFYoz4ImCvdJXYNQ3m/zhFEXgc+AZwxQDW/WNgBfCApNrw3AeBMyU9DvwnRWc2a4cfSHqCYgfqbGBmRKwAPkRxMcpdwE0UhWJumuc1wC1pvgXAv0fE3WnaGcC81JfeGRFLgP8HfA+4H3gJcGxL/rJhQH7gmJmZVcFHMGZmVgkXGDMzq4QLjJmZVcIFxszMKuECY2Zmldiq3Qk029ixY2PSpEntTsOGmeXLl6+PiHHtzmMg3BesCgPpC8OuwEyaNImurq52p2HDjKR7+m6VF/cFq8JA+oKHyMzMrBIuMGZmVolhN0Rmw8uk2dcNar7V5xzZ5EzMmmcw23UnbtM+gjEzs0q4wJiZWSVcYMzMrBIuMGZmVok+C4ykuZIekvTbUmwHSYslrUy/x6S4JJ0vaZWk30jatzTPzNR+paSZpfh+km5P85wvSb2tw8zMOkN/jmAuBqbXxWYDSyJiMrAkvQc4HJicfmZRPKoXSTsApwMHAPsDp5cKxoWpbW2+6X2sw8zMOkCfBSYifgZsqAvPAOal1/MoHulbi18ShaXAaEk7A4cBiyNiQ0RsBBYD09O0URFxcxRPPrukblmN1mFmZh1gsOdgdoqI+wHS7x1TfDywptSuO8V6i3c3iPe2ji1ImiWpS1LXunXrBvknmZlZMzX7JL8axGIQ8QGJiDkRMTUipo4b11H3IzQzG7YGW2AeTMNbpN8PpXg3MLHUbgKwto/4hAbx3tZhZmYdYLAFZgFQuxJsJnBtKX5CuppsGvBoGt5aBBwqaUw6uX8osChNe1zStHT12Al1y2q0DjMz6wB93otM0uXAgcBYSd0UV4OdA1wp6STgXuCY1HwhcASwCngSOBEgIjZIOgtYltqdGRG1CwdOprhSbRvgR+mHXtZhZmYdoM8CExHH9TDp4AZtAzilh+XMBeY2iHcBr2wQf7jROszMrDP4m/xmZlYJFxizAfCdLcz6zwXGbGAuxne2MOsXFxizAfCdLcz6zwXGbOiyubOF72phOXGBMatOy+9s4btaWE5cYMyGzne2MGvABcZs6HxnC7MG+vyipZk9y3e2MOs/FxizAfCdLcz6z0NkZmZWCRcYMzOrhAuMmZlVwgXGzMwq4QJjZmaVcIExM7NKuMCYmVklXGDMzKwSLjBmZlYJFxgzM6uEC4yZmVXCBcbMzCrhAmNmZpVwgTEzs0q4wJiZWSVcYMzMrBIuMGZmVgkXGDMzq8SQCoyk1ZJul3SbpK4U20HSYkkr0+8xKS5J50taJek3kvYtLWdmar9S0sxSfL+0/FVpXg0lXzMza51mHMG8KSKmRMTU9H42sCQiJgNL0nuAw4HJ6WcWcCEUBQk4HTgA2B84vVaUUptZpfmmNyFfMzNrgSqGyGYA89LrecDRpfglUVgKjJa0M3AYsDgiNkTERmAxMD1NGxURN0dEAJeUlmVmZpkbaoEJ4AZJyyXNSrGdIuJ+gPR7xxQfD6wpzdudYr3FuxvEtyBplqQuSV3r1q0b4p9kZmbNsNUQ539dRKyVtCOwWNLve2nb6PxJDCK+ZTBiDjAHYOrUqQ3bmJlZaw3pCCYi1qbfDwFXU5xDeTANb5F+P5SadwMTS7NPANb2EZ/QIG6WJV/0Yra5QRcYSdtK2r72GjgU+C2wAKh1ipnAten1AuCE1LGmAY+mIbRFwKGSxqTOdyiwKE17XNK01JFOKC3LLFe+6MUsGcoQ2U7A1Wknaivgsoi4XtIy4EpJJwH3Asek9guBI4BVwJPAiQARsUHSWcCy1O7MiNiQXp8MXAxsA/wo/Zh1khnAgen1POAnwCcpXfQCLJVUu+jlQNJFLwCSahe9/IR00UuK1y56cZ+wbA26wETEXcDeDeIPAwc3iAdwSg/LmgvMbRDvAl452BzNWqx20UsAX0/nBje76CWdr4SKLnpJF9vMAth1112b8TeZDdpQT/Kb2bPaftGLL3ixnPhWMWZN4otezDbnAmPWBL7oxWxLHiIzaw5f9GJWxwXGrAk67aKXSbOvG9R8q885shmrt+cID5GZmVklnlNHMIPZa/Mem5nZ4PgIxszMKuECY2ZmlXCBMTOzSrjAmJlZJVxgzMysEi4wZmZWCRcYMzOrhAuMmZlVwgXGzMwq4QJjZmaVcIExM7NKuMCYmVklXGDMzKwSLjBmZlYJFxgzM6uEC4yZmVXCBcbMzCrhAmNmZpVwgTEzs0ps1e4E7Llh0uzr2p2CWdN5u+6dj2DMzKwS2RcYSdMl3SlplaTZ7c7HrJ3cH6yTZF1gJI0ALgAOB/YCjpO0V3uzMmsP9wfrNLmfg9kfWBURdwFImg/MAH7X1qzM2sP94TlssOd7Vp9zZJMz6b/cC8x4YE3pfTdwQH0jSbOAWentE5LubFYCOrfHSWOB9c1aTwVyzq/y3Hr5d+tLT7ntNuglNk+f/aHKvgBbfK65bWM55ZNNLjq36bn0uy/kXmDUIBZbBCLmAHOqT+dZkroiYmor1zkQOefn3Aatz/7Qyr6Q22eVUz7OpZD1ORiKPbSJpfcTgLVtysWs3dwfrKPkXmCWAZMl7S5pJHAssKDNOZm1i/uDdZSsh8gi4mlJpwKLgBHA3IhY0ea0alo6JDcIOefn3AYhw/6Q22eVUz7OBVDEFqc0zMzMhiz3ITIzM+tQLjBmZlYJFxgzM6uEC8wwJGnfdufQE0mjJO0naUy7c2lE0th252ADl8s2n+P23c5t2gWmHyRNlDRf0s8lfUrS1qVp17Q5t33rfvYDFkjaJ4dOJ+nS2gYu6TBgBXAucJukY9qc2+GS7pZ0U/q8VgC3SOqWdHA7c8tNTn0gp20+t+07t23aV5H1g6TFwPeApcBJwH7AWyPiYUm/joh92pjbppTXX0rhaSkWEXFQWxJLJN0eEa9Kr38JHB8Rq1OnXBIRe7cxt9uA44DRwA+BIyNiqaQ9ge9ERNsLdC5y6gM5bfO5bd+5bdNZfw8mI+Mi4mvp9YckvRv4maSjaHDrmhZ7J/Ah4IsRsRBA0t0R8ab2pvV3z5M0KiIeAzYB9wJExHpJ7d7+NkXEHQCSnoyIpQARcYckH91vLqc+kNM2n9v2ndU23e4O3im2lvSCiHgKICIulfQAxRfetm1nYhFxlaTrgbMknQh8jPYXvbLPADdKugD4BfBdSdcCBwHXtzUzeETS+4FRwEZJHwGuBN4MPNHWzPKTTR/IbJvPbfvOapv2EFk/pH+kWyPip3XxfYAvRMQh7clsc5KmAF8GXhERO7Y7nxpJewDvA15KsVPTDVwTEYvanNdE4NMUe56foRhaOAm4B/h4bU/Q8u0DOWzzOW3fuW3TLjDDjCQB26dDdrNhz9t8vjzOPESS3tLuHMqi8Bjkl1u9nPPLObfctPuzynWbzykXaE8+LjBD95p2J9CLnHODvPPLObfc5PRZOZeetTwfD5H1k6SXUzyedjzFCcW1wIIcxulzzg3yzi/n3HKT02flXDojHx/B9IOkTwLzKZ4o+CuK53IIuFzSbOfWs5zzyzm33OT0WTmXDsrHRzB9k/QHiqtU/lYXHwmsiIjJ7cks79xSHtnml3Nuucnps3IunZOPj2D6ZxOwS4P4zmlaO+WcG+SdX8655Sanz8q59CyrfPxFy/75MLBE0kpgTYrtCuwBnNq2rAo55wZ555dzbrnJ6bNyLh2Sj4fI+indZmF/ihNnovgy1bKIeKatiZF3bpB3fjnnlpucPivn0hn5uMCYmVklfA7GzMwq4QJjZmaVcIExM7NKuMB0MElnSLq0l+krJB3YwpTMhkzSTyT9aw/TdpX0hKQRfbXtx3oulvTZfrZdLenNQ20r6fWS7hxInp3MBaYDSDpeUlfqWPdL+pGkf+xrvoh4RUT8pAUpmg1Y+o/4z2m7flDStyRt19s8EXFvRGzXqVf5RcTPI+Jl7c6jVVxgMifpo8B/AZ8DdqK4pv2rFPcaMut0b42I7YB9KW7G+Ok252NN5AKTMUkvAs4ETomI70fEnyLibxHxg4j4j9RspKRLJD2ehsSmlub/+6G6pBGSPiXpj6nt8vRwIiT9t6Q1kh5L8deXlrGNpHmSNkq6Q9InJHWXpu+ZhikeSes/qiUfjg0rEXEf8CPglSm0m6RfpG31BhXPuEfSJEmhBo8jlvQSST+W9LCk9ZK+I2l0afo+km5Ny7wCeEHd/G+RdFvaln8p6dWNck1D01dJuiIt61ZJe9c1myLpN5IeTe1ekOY9sK7/zC71yd9JetsgPr5sucDk7bUUneDqXtocRXFzu9HAAuArPbT7KMXT7Y6geJzqe4En07RlwBRgB+Ayise+1jrf6cAk4B+AQ4B31xYoaWvgB8ANwI4Uz0n/jqTnzBCANUfa2TkC+HUKHQ+cSLFdjQQ+3p/FAJ+nuFXKnsBE4Iy0/JHANcC3Kbbz7wL/VFr/vsBc4P3Ai4GvAwskPb+Hdc1Iy6j1mWtSf6h5JzAd2B14NfAvPSznj8DrgRdRPIHyUkk79+Nv7QguMHl7MbA+Ip7upc1NEbEwjUl/G6jfk6r5V+DTEXFnekDT/0XEw1A8Xz0iHo6IpyPiS8DzgVqReCfwuYjYGBHdwPmlZU4DtgPOiYi/RsSPgR9SFDKz/rhG0iPATcBPKYaCAb4VEX+IiD9TPFN+Sl8LiohVEbE4Iv4SEeuA84A3psnTgK2B/0qjAFdR7FjVvA/4ekTcEhHPRMQ84C9pvkaWR8RV6aaS51HsCJbbnh8RayNiA8VOWMP8I+K7qd2miLgCWEnxLfxhwfciy9vDwFhJW/VSZB4ovX4SeEEP7SdS7C1tQdLHKArQLhTPjxgFjE2Td+HZexpR93oXYE1ElG+idw/FLSrM+uPoiPjfckASbLld93ryP823I8UO0OuB7Sl2oDemybsA98Xmty65p/R6N2CmpA+VYiNpfONIKPWDiNiUhr3Kbevzb7gcSSdQjC5MSqHteLbvdTwfweTtZuAp4OgmLGsN8JL6YDrf8kmKI5UxETEaeJRiuAHgfmBCaZaJpddrgYnp3kc1uwL3NSFfs4H6PMUO0qsjYhTFcG55Ox6vVL2SXUuv1wBnR8To0s8LI+LyHtb1936Qtv8JFP2h3yTtBnyD4iaUL05977elnDueC0zGIuJR4D+BCyQdLemFkraWdLikLwxwcd8EzpI0WYVXS3oxxZ7e08A6YCtJ/0lxBFNzJXCapDGSxrP5HVlvAf4EfCLldSDwVopzQmattj3wBPBI2lb/ozTtZort/N8kbSXp7Ww+FPUN4AOSDkj9Y1tJR0ravod17Sfp7eligw9TDKctHWC+21IUxHUAkk7k2YschgUXmMxFxHkUh9CfptgQ11D8J3/NABd1HkWxuAF4DLgI2AZYRHH1zh8ohgyeYvNhsDMp7sZ6N/C/wFUUnYmI+CvFRQaHA+spLp8+ISJ+P8DczJrhMxSXOz8KXAd8vzYhbatvpzjZvhF4V930LorzMF9J01fR84l5gGvTMjYC7wHeXv+Qr75ExO+AL1EUvweBVwG/GMgycue7KduASDoZODYi3thnY7NhSNIZwB4R8e6+2j7X+QjGeiVpZ0mvk/S8dPnxx+j9smkzM8BXkVnfRlJ8J2B34BGK8ytfbWtGZtYRPERmZmaV8BCZmZlVwgXGzMwqMezOwYwdOzYmTZrU7jRsmFm+fPn6iBjX7jwGwn3BqjCQvjDsCsykSZPo6upqdxo2zEi6J/2eC7wFeCgiXpliZ1B8h2Jdav6piFiYpp0GnAQ8A/xbRCxK8enAfwMjgG9GxDkpvjvFhRQ7ALcC74mIv6abLl4C7EdxC6F3RcTq3nJ2X7Aq1PpCf3iIzGxgLqa4S269L0fElPRTKy57AccCr0jzfFXFYxNGABdQfEF1L+C41Bbg3LSsyRRf4jspxU8CNkbEHsCXUzuzrLnAmA1ARPwM2NDP5jOA+enuvndTfDt8//SzKiLuSt8wnw/MSPfJOojibgkA83j2PnQz0nvS9IPr7qtllp1hN0RmeZo0+7pBzbf6nCObnEllTk13xu0CPhYRGynuKl2+P1U3z95pek1d/ACKxzM8UroTdrn9+No8EfG0pEdT+/UV/C2WoU7sQ4M+gpH0svT0t9rPY5I+nJ72dl8pfkRpntMkrZJ0p6TDSvHpKbZK0uxSfHdJt0hamZ4KN3Lwf6pZZS6kuFP1FIq79n4pxRsdYcQg4r0tazOSZknqktS1bt26BrOYtc6gC0x6cNWUiJhCceLxSZ69hUjV49Fm2YiIB9NDqjZR3JW3dpfebjZ/vEHtlu49xdcDo/Xs44DLt4D/+zxp+otoMFQXEXMiYmpETB03rqMuerNhqFlDZAcDf4yIe3oZFv77eDRwt6TaeDSk8WgASbXx6DsoxqOPT23mUTz+9MIm5WzWFJJ2joj709u3UTzTA4pHWF8m6TyKB05NBn5FcTQyOV0xdh/FjtfxERGSbgTeQXFeZibFXXtry5pJcefddwA/rnt4lnWQwQ53dZpmFZhjgfKDeaoej96MpFnALIBdd921UROzppB0OXAgxZNGu4HTgQMlTaEYslpN8Vx3ImKFpCuB31E8i+SU9GhrJJ1K8aiEEcDciFiRVvFJYL6kz1I8n/6iFL8I+HbaMdtA0efMsjbkApPOixwFnJZCFwJnUXS2syjGo99Lz2PIjYbp+hqP3jwYMQeYAzB16lTv1VllIuK4BuGLGsRq7c8Gzm4QXwgsbBC/iwbPZI+Ip4BjBpSsWZs14wjmcODWiHgQivHo2gRJ3wB+mN72NO5MD/G/j0eno5gBP5LUzMzapxnfgzmO0vCYpJ1L0+rHo4+V9Pw09lwbj15GGo9OR0PHAgvS+HJtPBo2H482M7PMDekIRtILgUNIY87JF1owHm1mZpkbUoGJiCcpTsaXY+/ppX1TxqPNzCx/vlWMmZlVwgXGzMwq4QJjZmaVcIExM7NKuMCYmVklXGDMzKwSLjBmZlYJFxgzM6uEC4yZmVXCBcbMzCrhAmNmZpVwgTEzs0q4wJiZWSVcYMzMrBIuMGYDIGmupIck/bYU20HSYkkr0+8xKS5J50taJek3kvYtzTMztV8paWYpvp+k29M850tSb+swy5kLjNnAXAxMr4vNBpZExGRgSXoPxePEJ6efWcCFUBQL4HTgAIrnHZ1eKhgXpra1+ab3sQ6zbA2pwEhanfa2bpPUlWKV782ZtUtE/AzYUBeeAcxLr+cBR5fil0RhKTA6PVL8MGBxRGyIiI3AYmB6mjYqIm5Ojwy/pG5ZjdZhlq1mHMG8KSKmRMTU9L4Ve3NmOdkpIu4HSL93TPHxwJpSu+4U6y3e3SDe2zrMslXFEFkr9ubMOkGjI+4YRLz/K5RmSeqS1LVu3bqBzGrWdEMtMAHcIGm5pFkp1oq9ObOcPJh2iEi/H0rxbmBiqd0EYG0f8QkN4r2tYzMRMScipkbE1HHjxg3pjzIbqqEWmNdFxL4Uw1+nSHpDL20r25vzXpu12QKgdu5wJnBtKX5COv84DXg07XQtAg6VNCYNBx8KLErTHpc0LZ1vPKFuWY3WYZatIRWYiFibfj8EXE1xDqUVe3P1eXivzVpC0uXAzcDLJHVLOgk4BzhE0krgkPQeYCFwF7AK+AbwQYCI2ACcBSxLP2emGMDJwDfTPH8EfpTiPa3DLFtbDXZGSdsCz4uIx9PrQ4EzeXZP6xy23Js7VdJ8ihP6j0bE/ZIWAZ8rndg/FDgtIjZIejzt+d1CsTf3P4PN16wZIuK4HiYd3KBtAKf0sJy5wNwG8S7glQ3iDzdah1nOBl1ggJ2Aq9OVw1sBl0XE9ZKWAVemPbt7gWNS+4XAERR7Zk8CJ0KxNyeptjcHW+7NXQxsQ7EnV9ubMzOzzA26wETEXcDeDeIN97SauTdnZkMzafZ1g5pv9TlHNjkTG878TX4zM6uEC4yZmVXCBcbMzCrhAmNmZpVwgTEzs0q4wJiZWSVcYMzMrBIuMGZmVgkXGDMzq4QLjJmZVcIFxszMKuECY2ZmlXCBMTOzSrjAmJlZJYbyPJiOM5hblPv25GZmg+MjGDMzq4QLjFmTSFot6XZJt0nqSrEdJC2WtDL9HpPiknS+pFWSfiNp39JyZqb2KyXNLMX3S8tfleZV6/9Ks/4bdIGRNFHSjZLukLRC0r+n+BmS7kud7DZJR5TmOS11jjslHVaKT0+xVZJml+K7S7oldbQrJI0cbL5mLfKmiJgSEVPT+9nAkoiYDCxJ7wEOByann1nAhVAUJOB04ABgf+D0WlFKbWaV5pte/Z9jNnhDOYJ5GvhYROwJTANOkbRXmvbl1MmmRMRCgDTtWOAVFB3jq5JGSBoBXEDR4fYCjist59y0rMnARuCkIeRr1g4zgHnp9Tzg6FL8kigsBUZL2hk4DFgcERsiYiOwGJiepo2KiJvT48cvKS3LLEuDLjARcX9E3JpePw7cAYzvZZYZwPyI+EtE3A2sothD2x9YFRF3RcRfgfnAjHT4fxBwVZq/3DnNchTADZKWS5qVYjtFxP1Q9BlgxxQfD6wpzdudYr3FuxvEzbLVlHMwkiYB+wC3pNCpaVx5bunwfqAd6sXAIxHxdF3cLFevi4h9KY7GT5H0hl7aNjp/EoOIb75QaZakLkld69at60/OZpUZcoGRtB3wPeDDEfEYxTjxS4ApwP3Al2pNG8w+5A6VcnCnsraLiLXp90PA1RRH5w+m4S3S74dS825gYmn2CcDaPuITGsTrc5gTEVMjYuq4ceOa8WeZDdqQCoykrSmKy3ci4vsAEfFgRDwTEZuAb1B0Mhh4h1pPMS69VV18C+5U1m6StpW0fe01cCjwW2ABULsSbCZwbXq9ADghXU02DXg0DaEtAg6VNCYd/R8KLErTHpc0LQ0fn1BallmWBv1Fy7SRXwTcERHnleI718acgbdRdDIoOtRlks4DdqG4CuZXFEcqkyXtDtxHcSHA8RERkm4E3kFxXqbcOc1ysxNwdbpyeCvgsoi4XtIy4EpJJwH3Asek9guBIyjORT4JnAgQERsknQUsS+3OjIgN6fXJwMXANsCP0o9ZtobyTf7XAe8Bbpd0W4p9iuIqsCkUw1mrgfcDRMQKSVcCv6O4Au2UiHgGQNKpFHtuI4C5EbEiLe+TwHxJnwV+TVHQzLITEXcBezeIPwwc3CAewCk9LGsuMLdBvAt45ZCTNWuRQReYiLiJxudJFvYyz9nA2Q3iCxvNlzrt/vVxMzPLn7/Jb2ZmlXCBMTOzSrjAmJlZJVxgzMysEi4wZmZWiefUA8esOQbz4DYze+7xEYyZmVXCBcbMzCrhAmNmZpVwgTEzs0q4wJiZWSVcYMzMrBIuMGZmVgkXGDMzq4QLjJmZVcLf5LesDfauAavPObLJmZjZQPkIxszMKpF9gZE0XdKdklZJmt3ufMzayf3BOknWBUbSCOAC4HBgL+A4SXu1Nyuz9nB/sE6TdYEB9gdWRcRdEfFXYD4wo805mbWL+4N1lNxP8o8H1pTedwMHtCmXYce33e847g/WUXIvMGoQiy0aSbOAWentE5LubFoC5272diywvlnLHqKccoG88hmrc5uey25NXt5g9NkfquwLsEV/gLz+3cucV9Lg36yRgeTV776Qe4HpBiYJ3kaEAAAD9klEQVSW3k8A1tY3iog5wJyqk5HUFRFTq15Pf+SUC+SVT065NFmf/aFVfaEm18/aeQ1MVXnlfg5mGTBZ0u6SRgLHAgvanJNZu7g/WEfJ+ggmIp6WdCqwCBgBzI2IFW1Oy6wt3B+s02RdYAAiYiGwsN15JC0beuiHnHKBvPLJKZemyqw/QL6ftfMamEryUsQW58zNzMyGLPdzMGZm1qFcYMzMrBIuMGZmVgkXmA4lad925wAgaZSk/SSNaXcuNZLGtjsHa59c+ka9HPtKTVV9xgWmB5ImSpov6eeSPiVp69K0a1qcy751P/sBCyTt0+rOJOnS2sYo6TBgBXAucJukY1qZS8rhcEl3S7opfR4rgFskdUs6uNX5DFc59Ye6vLLpGw1yy6qvlPJqWZ/xVWQ9kLQY+B6wFDgJ2A94a0Q8LOnXEbFPC3PZlPL4Syk8LcUiIg5qYS63R8Sr0utfAsdHxOrUkZZExN6tyiXlcBtwHDAa+CFwZEQslbQn8J2IyHJvttPk1B/q8sqmb9TLra+U8mpZn8n+ezBtNC4ivpZef0jSu4GfSTqKBvdDq9g7gQ8BX0zfg0DS3RHxphbnAfA8SaMi4jFgE3AvQESsl9SO7WlTRNwBIOnJiFia8rlDko/Qmyen/lCWU9+ol1tfqWlZn3GB6dnWkl4QEU8BRMSlkh6g+Bb1tq1MJCKuknQ9cJakE4GP0b5O/RngRkkXAL8AvivpWuAg4Po25POIpPcDo4CNkj4CXAm8GXiiDfkMV9n0h7LM+ka93PpKTcv6jIfIepA+9Fsj4qd18X2AL0TEIW3KawrwZeAVEbFjm3LYA3gf8FKKnZRu4JqIWNSGXCYCn6bYQ/wMxaH/ScA9wMdre2o2NLn2h7pc2t436uXUV0o5tazPuMB0IEkCtk+H3maWuG/kxWPUgyDpLe1cfxQeyyGXspxygfzyGa5y+pxz7Rv1cs2t2Xm5wAzOa9qdQIlz6Vlu+QxXuX7OueYF+ebW1Lw8RNYLSS+neOb5eIoTh2uBBe0Y13cunZPPcJXr55xrXpBvbq3Ky0cwPZD0SWA+xWNqf0XxsCcBl0ua7Vzan0uO+QxXuX7OueaVc26tzMtHMD2Q9AeKq1H+VhcfCayIiMnOpb255JjPcJXr55xrXimHLHNrZV4+gunZJmCXBvGd0zTn0v5cIL98hqtcP+dc84J8c2tZXv6iZc8+DCyRtBJYk2K7AnsApzqXLHLJMZ/hKtfPOde8IN/cWpaXh8h6kW6bsD/FiTBRfElqWUQ841zyyCXHfIarXD/nXPOCfHNrVV4uMGZmVgmfgzEzs0q4wJiZWSVcYMzMrBIuMGZmVgkXGDMzq8T/B9dOfwxLi0yDAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 4 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_av.hist(by='city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>prob_test</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>857409.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>0.552628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>0.232949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>0.098977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>0.636675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>0.673020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>0.687935</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>0.881125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           prob_test\n",
       "count  857409.000000\n",
       "mean        0.552628\n",
       "std         0.232949\n",
       "min         0.098977\n",
       "25%         0.636675\n",
       "50%         0.673020\n",
       "75%         0.687935\n",
       "max         0.881125"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_av.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Step 4 **\n",
    "\n",
    "There are about 200000 train data entries with a 0.1 probability to be similar to test. Looking at the histograms we dismiss every data entry with less then 0.5 test-probability for the detailed model later on.\n",
    "The 0.5 threshold out of adversarial validation only worked on the detailed model. The general model did a simple validation-split by row number."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove Outliers from train\n",
    "**!! This approach unfortunately didn't advance the model and was left out for the final model !!**\n",
    "\n",
    "The main idea is to analyse the target values (TotalTimeStopped and DistanceToFirstStop) and considere values above the 99.x-quantil as outliers and use this value as either a cutoff (clip)-value for the predicted targets or remove them from the train data. In both cases the model didn't improve.\n",
    "\n",
    "** Step 1 **\n",
    "\n",
    "Mark rows with target value > 99.5-percentile. Group by City-Cluster-1 to calculate quantil.\n",
    "\n",
    "(I needed to split SQL because of CPU-usage restriction on free tier.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TotalTimeStopped\n",
    "using q=0.995"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery tts_q\n",
    "SELECT\n",
    "c.city_cluster_1,\n",
    "count(1)cnt,\n",
    "min(TotalTimeStopped_p20) tts20_min,\n",
    "max(TotalTimeStopped_p20) tts20_max,\n",
    "avg(TotalTimeStopped_p20) tts20_avg,\n",
    "stddev(TotalTimeStopped_p20) tts20_std,\n",
    "APPROX_QUANTILES(TotalTimeStopped_p20,201)[SAFE_ORDINAL(200)] tts20_q99,\n",
    "\n",
    "min(TotalTimeStopped_p50) tts50_min,\n",
    "max(TotalTimeStopped_p50) tts50_max,\n",
    "avg(TotalTimeStopped_p50) tts50_avg,\n",
    "stddev(TotalTimeStopped_p50) tts50_std,\n",
    "APPROX_QUANTILES(TotalTimeStopped_p50,201)[SAFE_ORDINAL(200)] tts50_q99,\n",
    "\n",
    "min(TotalTimeStopped_p80) tts80_min,\n",
    "max(TotalTimeStopped_p80) tts80_max,\n",
    "avg(TotalTimeStopped_p80) tts80_avg,\n",
    "stddev(TotalTimeStopped_p80) tts80_std,\n",
    "APPROX_QUANTILES(TotalTimeStopped_p80,201)[SAFE_ORDINAL(200)] tts80_q99\n",
    "\n",
    "FROM\n",
    "  `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "  `bqml_example.city_cluster` c\n",
    "WHERE\n",
    "  c.city = c.city\n",
    "  AND t.intersectionid=t.intersectionid\n",
    "GROUP BY\n",
    "  c.city_cluster_1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>cnt</th>\n",
       "      <th>tts20_min</th>\n",
       "      <th>tts20_max</th>\n",
       "      <th>tts20_avg</th>\n",
       "      <th>tts20_std</th>\n",
       "      <th>tts20_q99</th>\n",
       "      <th>tts50_min</th>\n",
       "      <th>tts50_max</th>\n",
       "      <th>tts50_avg</th>\n",
       "      <th>tts50_std</th>\n",
       "      <th>tts50_q99</th>\n",
       "      <th>tts80_min</th>\n",
       "      <th>tts80_max</th>\n",
       "      <th>tts80_avg</th>\n",
       "      <th>tts80_std</th>\n",
       "      <th>tts80_q99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>6.838909e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>35.087500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>67.825000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>122.075000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>4.858926e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.791057e-15</td>\n",
       "      <td>8.496213e-08</td>\n",
       "      <td>0.284349</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.994287e-15</td>\n",
       "      <td>1.866452e-07</td>\n",
       "      <td>0.382364</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.159491e-14</td>\n",
       "      <td>3.374253e-07</td>\n",
       "      <td>0.347687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>8.574090e+06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>67.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>121.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>3.150978e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>122.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>6.001863e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>122.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>9.109971e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>35.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>122.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2.555079e+08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>1.731272e+00</td>\n",
       "      <td>7.080013e+00</td>\n",
       "      <td>36.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>343.0</td>\n",
       "      <td>7.681874e+00</td>\n",
       "      <td>1.555341e+01</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>689.0</td>\n",
       "      <td>2.294807e+01</td>\n",
       "      <td>2.811812e+01</td>\n",
       "      <td>123.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                cnt  tts20_min  tts20_max     tts20_avg     tts20_std  \\\n",
       "count  8.000000e+01       80.0       80.0  8.000000e+01  8.000000e+01   \n",
       "mean   6.838909e+07        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "std    4.858926e+07        0.0        0.0  2.791057e-15  8.496213e-08   \n",
       "min    8.574090e+06        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "25%    3.150978e+07        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "50%    6.001863e+07        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "75%    9.109971e+07        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "max    2.555079e+08        0.0      273.0  1.731272e+00  7.080013e+00   \n",
       "\n",
       "       tts20_q99  tts50_min  tts50_max     tts50_avg     tts50_std  tts50_q99  \\\n",
       "count  80.000000       80.0       80.0  8.000000e+01  8.000000e+01  80.000000   \n",
       "mean   35.087500        0.0      343.0  7.681874e+00  1.555341e+01  67.825000   \n",
       "std     0.284349        0.0        0.0  9.994287e-15  1.866452e-07   0.382364   \n",
       "min    35.000000        0.0      343.0  7.681874e+00  1.555341e+01  67.000000   \n",
       "25%    35.000000        0.0      343.0  7.681874e+00  1.555341e+01  68.000000   \n",
       "50%    35.000000        0.0      343.0  7.681874e+00  1.555341e+01  68.000000   \n",
       "75%    35.000000        0.0      343.0  7.681874e+00  1.555341e+01  68.000000   \n",
       "max    36.000000        0.0      343.0  7.681874e+00  1.555341e+01  68.000000   \n",
       "\n",
       "       tts80_min  tts80_max     tts80_avg     tts80_std   tts80_q99  \n",
       "count       80.0       80.0  8.000000e+01  8.000000e+01   80.000000  \n",
       "mean         0.0      689.0  2.294807e+01  2.811812e+01  122.075000  \n",
       "std          0.0        0.0  3.159491e-14  3.374253e-07    0.347687  \n",
       "min          0.0      689.0  2.294807e+01  2.811812e+01  121.000000  \n",
       "25%          0.0      689.0  2.294807e+01  2.811812e+01  122.000000  \n",
       "50%          0.0      689.0  2.294807e+01  2.811812e+01  122.000000  \n",
       "75%          0.0      689.0  2.294807e+01  2.811812e+01  122.000000  \n",
       "max          0.0      689.0  2.294807e+01  2.811812e+01  123.000000  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tts_q.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since stddev of q99 is small I use the same filter values for all city clusters.\n",
    "\n",
    "Define filter to remove outliers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TotalTimeStopped_20 filter: 36\n",
      "TotalTimeStopped_50 filter: 68\n",
      "TotalTimeStopped_80 filter: 123\n"
     ]
    }
   ],
   "source": [
    "tts20_outlier_filter = tts_q['tts20_q99'].max()\n",
    "tts50_outlier_filter = tts_q['tts50_q99'].max()\n",
    "tts80_outlier_filter = tts_q['tts80_q99'].max()\n",
    "\n",
    "print('TotalTimeStopped_20 filter:', tts20_outlier_filter)\n",
    "print('TotalTimeStopped_50 filter:', tts50_outlier_filter)\n",
    "print('TotalTimeStopped_80 filter:', tts80_outlier_filter)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DistanceToFirstStop\n",
    "q=0.995"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery dts_q20\n",
    "SELECT\n",
    "c.city_cluster_1,\n",
    "count(1)cnt,\n",
    "min(DistanceToFirstStop_p20) dts20_min,\n",
    "max(DistanceToFirstStop_p20) dts20_max,\n",
    "avg(DistanceToFirstStop_p20) dts20_avg,\n",
    "stddev(DistanceToFirstStop_p20) dts20_std,\n",
    "APPROX_QUANTILES(DistanceToFirstStop_p20,201)[SAFE_ORDINAL(200)] dts20_q99\n",
    "FROM\n",
    "  `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "  `bqml_example.city_cluster` c\n",
    "WHERE\n",
    "  c.city = c.city\n",
    "  AND t.intersectionid=t.intersectionid\n",
    "GROUP BY\n",
    "  c.city_cluster_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>cnt</th>\n",
       "      <th>dts20_min</th>\n",
       "      <th>dts20_max</th>\n",
       "      <th>dts20_avg</th>\n",
       "      <th>dts20_std</th>\n",
       "      <th>dts20_q99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>6.838909e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800324e+01</td>\n",
       "      <td>94.367500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>4.858926e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.221562e-15</td>\n",
       "      <td>3.360467e-07</td>\n",
       "      <td>0.155714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>8.574090e+06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800324e+01</td>\n",
       "      <td>94.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>3.150978e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800324e+01</td>\n",
       "      <td>94.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>6.001863e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800324e+01</td>\n",
       "      <td>94.400000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>9.109971e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800324e+01</td>\n",
       "      <td>94.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2.555079e+08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1902.7</td>\n",
       "      <td>6.564450e+00</td>\n",
       "      <td>2.800325e+01</td>\n",
       "      <td>94.800000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                cnt  dts20_min  dts20_max     dts20_avg     dts20_std  \\\n",
       "count  8.000000e+01       80.0       80.0  8.000000e+01  8.000000e+01   \n",
       "mean   6.838909e+07        0.0     1902.7  6.564450e+00  2.800324e+01   \n",
       "std    4.858926e+07        0.0        0.0  9.221562e-15  3.360467e-07   \n",
       "min    8.574090e+06        0.0     1902.7  6.564450e+00  2.800324e+01   \n",
       "25%    3.150978e+07        0.0     1902.7  6.564450e+00  2.800324e+01   \n",
       "50%    6.001863e+07        0.0     1902.7  6.564450e+00  2.800324e+01   \n",
       "75%    9.109971e+07        0.0     1902.7  6.564450e+00  2.800324e+01   \n",
       "max    2.555079e+08        0.0     1902.7  6.564450e+00  2.800325e+01   \n",
       "\n",
       "       dts20_q99  \n",
       "count  80.000000  \n",
       "mean   94.367500  \n",
       "std     0.155714  \n",
       "min    94.000000  \n",
       "25%    94.300000  \n",
       "50%    94.400000  \n",
       "75%    94.500000  \n",
       "max    94.800000  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dts_q20.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery dts_q50\n",
    "SELECT\n",
    "c.city_cluster_1,\n",
    "count(1)cnt,\n",
    "min(DistanceToFirstStop_p50) dts50_min,\n",
    "max(DistanceToFirstStop_p50) dts50_max,\n",
    "avg(DistanceToFirstStop_p50) dts50_avg,\n",
    "stddev(DistanceToFirstStop_p50) dts50_std,\n",
    "APPROX_QUANTILES(DistanceToFirstStop_p50,201)[SAFE_ORDINAL(200)] dts50_q99\n",
    "FROM\n",
    "  `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "  `bqml_example.city_cluster` c\n",
    "WHERE\n",
    "  c.city = c.city\n",
    "  AND t.intersectionid=t.intersectionid\n",
    "GROUP BY\n",
    "  c.city_cluster_1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>cnt</th>\n",
       "      <th>dts50_min</th>\n",
       "      <th>dts50_max</th>\n",
       "      <th>dts50_avg</th>\n",
       "      <th>dts50_std</th>\n",
       "      <th>dts50_q99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>6.838909e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>321.066250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>4.858926e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.131811e-14</td>\n",
       "      <td>8.606605e-07</td>\n",
       "      <td>1.327408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>8.574090e+06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>318.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>3.150978e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>319.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>6.001863e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>321.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>9.109971e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>322.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2.555079e+08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3099.5</td>\n",
       "      <td>2.825585e+01</td>\n",
       "      <td>7.172005e+01</td>\n",
       "      <td>323.800000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                cnt  dts50_min  dts50_max     dts50_avg     dts50_std  \\\n",
       "count  8.000000e+01       80.0       80.0  8.000000e+01  8.000000e+01   \n",
       "mean   6.838909e+07        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "std    4.858926e+07        0.0        0.0  3.131811e-14  8.606605e-07   \n",
       "min    8.574090e+06        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "25%    3.150978e+07        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "50%    6.001863e+07        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "75%    9.109971e+07        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "max    2.555079e+08        0.0     3099.5  2.825585e+01  7.172005e+01   \n",
       "\n",
       "        dts50_q99  \n",
       "count   80.000000  \n",
       "mean   321.066250  \n",
       "std      1.327408  \n",
       "min    318.500000  \n",
       "25%    319.875000  \n",
       "50%    321.000000  \n",
       "75%    322.000000  \n",
       "max    323.800000  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dts_q50.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery dts_q80\n",
    "SELECT\n",
    "c.city_cluster_1,\n",
    "count(1)cnt,\n",
    "min(DistanceToFirstStop_p80) dts80_min,\n",
    "max(DistanceToFirstStop_p80) dts80_max,\n",
    "avg(DistanceToFirstStop_p80) dts80_avg,\n",
    "stddev(DistanceToFirstStop_p80) dts80_std,\n",
    "APPROX_QUANTILES(DistanceToFirstStop_p80,201)[SAFE_ORDINAL(200)] dts80_q99\n",
    "FROM\n",
    "  `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "  `bqml_example.city_cluster` c\n",
    "WHERE\n",
    "  c.city = c.city\n",
    "  AND t.intersectionid=t.intersectionid\n",
    "GROUP BY\n",
    "  c.city_cluster_1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>cnt</th>\n",
       "      <th>dts80_min</th>\n",
       "      <th>dts80_max</th>\n",
       "      <th>dts80_avg</th>\n",
       "      <th>dts80_std</th>\n",
       "      <th>dts80_q99</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.0</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>8.000000e+01</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>6.838909e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682673</td>\n",
       "      <td>828.427500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>4.858926e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.949014e-12</td>\n",
       "      <td>8.655946e-14</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>2.118751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>8.574090e+06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682671</td>\n",
       "      <td>824.600000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>3.150978e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682672</td>\n",
       "      <td>826.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>6.001863e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682672</td>\n",
       "      <td>828.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>9.109971e+07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682673</td>\n",
       "      <td>830.025000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>2.555079e+08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.064300e+03</td>\n",
       "      <td>8.192264e+01</td>\n",
       "      <td>152.682680</td>\n",
       "      <td>833.800000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                cnt  dts80_min     dts80_max     dts80_avg   dts80_std  \\\n",
       "count  8.000000e+01       80.0  8.000000e+01  8.000000e+01   80.000000   \n",
       "mean   6.838909e+07        0.0  4.064300e+03  8.192264e+01  152.682673   \n",
       "std    4.858926e+07        0.0  5.949014e-12  8.655946e-14    0.000002   \n",
       "min    8.574090e+06        0.0  4.064300e+03  8.192264e+01  152.682671   \n",
       "25%    3.150978e+07        0.0  4.064300e+03  8.192264e+01  152.682672   \n",
       "50%    6.001863e+07        0.0  4.064300e+03  8.192264e+01  152.682672   \n",
       "75%    9.109971e+07        0.0  4.064300e+03  8.192264e+01  152.682673   \n",
       "max    2.555079e+08        0.0  4.064300e+03  8.192264e+01  152.682680   \n",
       "\n",
       "        dts80_q99  \n",
       "count   80.000000  \n",
       "mean   828.427500  \n",
       "std      2.118751  \n",
       "min    824.600000  \n",
       "25%    826.875000  \n",
       "50%    828.200000  \n",
       "75%    830.025000  \n",
       "max    833.800000  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dts_q80.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since stddev of q99 is small I use the same filter values for all city clusters.\n",
    "\n",
    "Define filter to remove outliers:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DistanceToFirstStop_20 filter: 94.8\n",
      "DistanceToFirstStop_50 filter: 323.8\n",
      "DistanceToFirstStop_80 filter: 833.8\n"
     ]
    }
   ],
   "source": [
    "dts20_outlier_filter = dts_q20['dts20_q99'].max()\n",
    "dts50_outlier_filter = dts_q50['dts50_q99'].max()\n",
    "dts80_outlier_filter = dts_q80['dts80_q99'].max()\n",
    "\n",
    "print('DistanceToFirstStop_20 filter:', dts20_outlier_filter)\n",
    "print('DistanceToFirstStop_50 filter:', dts50_outlier_filter)\n",
    "print('DistanceToFirstStop_80 filter:', dts80_outlier_filter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'TotalTimeStopped_p20': 36,\n",
       " 'TotalTimeStopped_p50': 68,\n",
       " 'TotalTimeStopped_p80': 123,\n",
       " 'DistanceToFirstStop_p20': 94.8,\n",
       " 'DistanceToFirstStop_p50': 323.8,\n",
       " 'DistanceToFirstStop_p80': 833.8}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outliers_filter = {'TotalTimeStopped_p20':tts20_outlier_filter,\n",
    "                   'TotalTimeStopped_p50':tts50_outlier_filter,\n",
    "                   'TotalTimeStopped_p80':tts80_outlier_filter,\n",
    "                   'DistanceToFirstStop_p20':dts20_outlier_filter,\n",
    "                   'DistanceToFirstStop_p50':dts50_outlier_filter,\n",
    "                   'DistanceToFirstStop_p80':dts80_outlier_filter}\n",
    "\n",
    "outliers_filter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Build models\n",
    "(The idea of creating a general and a detailed model depending on intersection and street informations was derived from https://www.kaggle.com/gaborfodor/4-xgboost-general)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Detailed model\n",
    "The detailed prediction model is trained with data where the intersections and streets are also known in the test data set.\n",
    "The entire dataset above the 0.5 adversarial threshold is used for train. Usualy I'd keep some amount for validation. But I dismissed it (as well as cross validatin) because of limited BQ quota. I only tried a couple of different hyper parameters for the same reason. The default settings fit best."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 0 ns, sys: 0 ns, total: 0 ns\n",
      "Wall time: 12.6 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "#model_changed = False\n",
    "\n",
    "if model_changed:\n",
    "    print(\"Let's go\")\n",
    "\n",
    "    for mn in mod_names:\n",
    "        sql=\"\"\"\n",
    "        CREATE OR REPLACE MODEL `bqml_example.model_\"\"\"+mn+\"\"\"`\n",
    "        OPTIONS(MODEL_TYPE='linear_reg') AS \n",
    "        SELECT  t.\"\"\"+mn+\"\"\" as label,\n",
    "                concat(t.city,cast(t.intersectionid as string)) i_id,\n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                --(case \n",
    "                --    when t.hour between 6 and 9 then 'RUSH_HOUR_MORNING'\n",
    "                --    when t.hour between 10 and 15 then 'MIDDAY'\n",
    "                --    when t.hour between 16 and 19 then 'RUSH_HOUR_EVENING'\n",
    "                --    else 'NIGHT'\n",
    "                --end) daytime,\n",
    "                --(case \n",
    "                --    when t.month between 4 and 6 then 'SPRING'\n",
    "                --    when t.month between 7 and 9 then 'SUMMER'\n",
    "                --    when t.month between 10 and 11 then 'FALL'\n",
    "                --    else 'WINTER'\n",
    "                --end) season,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                concat(t.city,t.path) as path,\n",
    "                concat(t.city,t.entryStreetName) as entry_street_name,\n",
    "                concat(t.city,t.exitStreetName) as exit_street_name,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "          FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "               `bqml_example.testalike_av` av,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND t.rowid = av.rowid\n",
    "           AND av.prob_test > 0.5\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')\n",
    "        \"\"\"\n",
    "        \n",
    "        client.query(sql).result()\n",
    "\n",
    "        print('Done with',mn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## General Model\n",
    "\n",
    "For the test data that doesn't match intersections, path, entry and exit streets from train I build a more general model. BATCH_GRADIENT_DESCENT needs to be forced on this model, otherwise with the default optimize strategie the outliers in the prediction are very high. For this model the train-validation-split is done by rowid (split by adversiarial validation didn't improve the general model)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 0 ns, sys: 0 ns, total: 0 ns\n",
      "Wall time: 12.9 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "#model_changed = False\n",
    "\n",
    "if model_changed:\n",
    "    print(\"Let's go\")\n",
    "\n",
    "    for mn in mod_names:\n",
    "        sql=\"\"\"\n",
    "        CREATE OR REPLACE MODEL `bqml_example.model_\"\"\"+mn+\"\"\"_general`\n",
    "        OPTIONS(MODEL_TYPE='linear_reg',L2_REG=0.2,\n",
    "                LS_INIT_LEARN_RATE=0.4,\n",
    "                OPTIMIZE_STRATEGY='BATCH_GRADIENT_DESCENT') AS \n",
    "        SELECT  t.\"\"\"+mn+\"\"\" as label,\n",
    "                t.Longitude,\n",
    "                t.Latitude, \n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "          FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "               `bqml_example.testalike_av` av,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND t.rowid = av.rowid\n",
    "           and t.rowid < 2600000\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')\n",
    "        \"\"\"\n",
    "        #\n",
    "         #  AND av.prob_test > 0.5\n",
    "         #  AND t.\"\"\"+mn+\"\"\" <= \"\"\"+str(outliers_filter[mn])+\"\"\"\n",
    "        #between 0.5 and 0.85\n",
    "    #< 2600000;\n",
    "        client.query(sql).result()\n",
    "\n",
    "        print('Done with',mn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get training statistics\n",
    "For example TotalTimeStopped_p20 is shown in the notebook. Evaluation on the other models was done for convenience in the BigQuery console."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 40 ms, sys: 8 ms, total: 48 ms\n",
      "Wall time: 844 ms\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>43.395014</td>\n",
       "      <td>43.713144</td>\n",
       "      <td>0.1</td>\n",
       "      <td>16236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>40.068919</td>\n",
       "      <td>40.717962</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>38.095899</td>\n",
       "      <td>39.215629</td>\n",
       "      <td>0.1</td>\n",
       "      <td>28831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>36.331866</td>\n",
       "      <td>38.082470</td>\n",
       "      <td>0.2</td>\n",
       "      <td>35575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>35.297734</td>\n",
       "      <td>37.378882</td>\n",
       "      <td>0.1</td>\n",
       "      <td>24200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>34.657681</td>\n",
       "      <td>36.942462</td>\n",
       "      <td>0.1</td>\n",
       "      <td>24270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>34.214005</td>\n",
       "      <td>36.721812</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25094</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  43.395014  43.713144            0.1        16236\n",
       "1             0          1  40.068919  40.717962            0.1        25969\n",
       "2             0          2  38.095899  39.215629            0.1        28831\n",
       "3             0          3  36.331866  38.082470            0.2        35575\n",
       "4             0          4  35.297734  37.378882            0.1        24200\n",
       "5             0          5  34.657681  36.942462            0.1        24270\n",
       "6             0          6  34.214005  36.721812            0.1        25094"
      ]
     },
     "execution_count": 41,
     "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": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 48 ms, sys: 0 ns, total: 48 ms\n",
      "Wall time: 810 ms\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>54.175246</td>\n",
       "      <td>53.323952</td>\n",
       "      <td>0.1</td>\n",
       "      <td>16001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>53.004843</td>\n",
       "      <td>52.204704</td>\n",
       "      <td>0.2</td>\n",
       "      <td>20132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>52.483496</td>\n",
       "      <td>51.640820</td>\n",
       "      <td>0.1</td>\n",
       "      <td>21480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>52.174864</td>\n",
       "      <td>51.334427</td>\n",
       "      <td>0.2</td>\n",
       "      <td>20154</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  54.175246  53.323952            0.1        16001\n",
       "1             0          1  53.004843  52.204704            0.2        20132\n",
       "2             0          2  52.483496  51.640820            0.1        21480\n",
       "3             0          3  52.174864  51.334427            0.2        20154"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.TRAINING_INFO(MODEL `bqml_example.model_TotalTimeStopped_p20_general`)\n",
    "ORDER BY iteration "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 40 ms, sys: 8 ms, total: 48 ms\n",
      "Wall time: 750 ms\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>i_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4334.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>city_cluster_1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>city_cluster_2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>city_cluster_3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>city_cluster_4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>city_cluster_5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>hour</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>12.479479</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>6.084951</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>month</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>9.111451</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>1.990438</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>direction_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.753090</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.428696</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>direction_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.001146</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.499074</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>entryheading_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.002221</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.678791</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>entryheading_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.005179</td>\n",
       "      <td>-0.000000</td>\n",
       "      <td>0.734311</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>exitheading_sin</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.005388</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.680448</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>exitheading_cos</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-0.006107</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.732752</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>dist_to_cluster_center_1</td>\n",
       "      <td>0.001996</td>\n",
       "      <td>1.993450</td>\n",
       "      <td>0.240137</td>\n",
       "      <td>0.202432</td>\n",
       "      <td>0.186169</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>dist_to_cluster_center_2</td>\n",
       "      <td>0.129615</td>\n",
       "      <td>2.518300</td>\n",
       "      <td>0.494100</td>\n",
       "      <td>0.436281</td>\n",
       "      <td>0.269905</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>dist_to_cluster_center_3</td>\n",
       "      <td>0.199029</td>\n",
       "      <td>2.888238</td>\n",
       "      <td>0.631968</td>\n",
       "      <td>0.562043</td>\n",
       "      <td>0.335704</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>dist_to_cluster_center_4</td>\n",
       "      <td>0.233253</td>\n",
       "      <td>3.347821</td>\n",
       "      <td>0.752216</td>\n",
       "      <td>0.676223</td>\n",
       "      <td>0.382903</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>dist_to_cluster_center_5</td>\n",
       "      <td>0.316810</td>\n",
       "      <td>3.959717</td>\n",
       "      <td>0.870767</td>\n",
       "      <td>0.813942</td>\n",
       "      <td>0.422785</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>intersections_per_cluster_1</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>298.000000</td>\n",
       "      <td>94.749530</td>\n",
       "      <td>86.000000</td>\n",
       "      <td>53.640162</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>intersections_per_cluster_2</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>348.000000</td>\n",
       "      <td>107.968913</td>\n",
       "      <td>108.000000</td>\n",
       "      <td>61.578012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>intersections_per_cluster_3</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>498.000000</td>\n",
       "      <td>124.266199</td>\n",
       "      <td>103.000000</td>\n",
       "      <td>90.105333</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>intersections_per_cluster_4</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>355.000000</td>\n",
       "      <td>119.480321</td>\n",
       "      <td>116.000000</td>\n",
       "      <td>70.210930</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>intersections_per_cluster_5</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>468.000000</td>\n",
       "      <td>125.854037</td>\n",
       "      <td>119.000000</td>\n",
       "      <td>90.837428</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>observation_ratio_per_city</td>\n",
       "      <td>0.003216</td>\n",
       "      <td>0.085305</td>\n",
       "      <td>0.049038</td>\n",
       "      <td>0.048695</td>\n",
       "      <td>0.015603</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>population</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341748.000000</td>\n",
       "      <td>100871.165946</td>\n",
       "      <td>89724.000000</td>\n",
       "      <td>58187.730792</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>zipcode</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>117.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>intersections_per_zipcode</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>128.000000</td>\n",
       "      <td>58.222004</td>\n",
       "      <td>55.000000</td>\n",
       "      <td>32.855453</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>pop_intersec_ratio</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>141666.000000</td>\n",
       "      <td>2486.789134</td>\n",
       "      <td>1866.000000</td>\n",
       "      <td>4287.788373</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>zip_code_na</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.020791</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.142685</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>path</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13446.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>entry_street_name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1512.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>exit_street_name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1494.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>same_street</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.686709</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.463832</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>num_observations</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3443.000000</td>\n",
       "      <td>860.883803</td>\n",
       "      <td>751.000000</td>\n",
       "      <td>566.560548</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>app_st_length</td>\n",
       "      <td>8.600000</td>\n",
       "      <td>13268.500000</td>\n",
       "      <td>345.265057</td>\n",
       "      <td>174.800000</td>\n",
       "      <td>610.879594</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>app_st_length_fallback</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.095250</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.293561</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          input        min            max           mean  \\\n",
       "0                          i_id        NaN            NaN            NaN   \n",
       "1                city_cluster_1        NaN            NaN            NaN   \n",
       "2                city_cluster_2        NaN            NaN            NaN   \n",
       "3                city_cluster_3        NaN            NaN            NaN   \n",
       "4                city_cluster_4        NaN            NaN            NaN   \n",
       "5                city_cluster_5        NaN            NaN            NaN   \n",
       "6                          city        NaN            NaN            NaN   \n",
       "7                          hour   0.000000      23.000000      12.479479   \n",
       "8                       weekend        NaN            NaN            NaN   \n",
       "9                         month   1.000000      12.000000       9.111451   \n",
       "10                    direction        NaN            NaN            NaN   \n",
       "11                 entryheading        NaN            NaN            NaN   \n",
       "12                  exitheading        NaN            NaN            NaN   \n",
       "13                direction_sin  -1.000000       1.000000       0.753090   \n",
       "14                direction_cos  -1.000000       1.000000      -0.001146   \n",
       "15             entryheading_sin  -1.000000       1.000000      -0.002221   \n",
       "16             entryheading_cos  -1.000000       1.000000       0.005179   \n",
       "17              exitheading_sin  -1.000000       1.000000       0.005388   \n",
       "18              exitheading_cos  -1.000000       1.000000      -0.006107   \n",
       "19     dist_to_cluster_center_1   0.001996       1.993450       0.240137   \n",
       "20     dist_to_cluster_center_2   0.129615       2.518300       0.494100   \n",
       "21     dist_to_cluster_center_3   0.199029       2.888238       0.631968   \n",
       "22     dist_to_cluster_center_4   0.233253       3.347821       0.752216   \n",
       "23     dist_to_cluster_center_5   0.316810       3.959717       0.870767   \n",
       "24  intersections_per_cluster_1  10.000000     298.000000      94.749530   \n",
       "25  intersections_per_cluster_2   3.000000     348.000000     107.968913   \n",
       "26  intersections_per_cluster_3   1.000000     498.000000     124.266199   \n",
       "27  intersections_per_cluster_4   1.000000     355.000000     119.480321   \n",
       "28  intersections_per_cluster_5   1.000000     468.000000     125.854037   \n",
       "29   observation_ratio_per_city   0.003216       0.085305       0.049038   \n",
       "30                   population   0.000000  341748.000000  100871.165946   \n",
       "31                      zipcode        NaN            NaN            NaN   \n",
       "32    intersections_per_zipcode   1.000000     128.000000      58.222004   \n",
       "33           pop_intersec_ratio   0.000000  141666.000000    2486.789134   \n",
       "34                  zip_code_na   0.000000       1.000000       0.020791   \n",
       "35                         path        NaN            NaN            NaN   \n",
       "36            entry_street_name        NaN            NaN            NaN   \n",
       "37             exit_street_name        NaN            NaN            NaN   \n",
       "38                  same_street   0.000000       1.000000       0.686709   \n",
       "39             num_observations   2.000000    3443.000000     860.883803   \n",
       "40                app_st_length   8.600000   13268.500000     345.265057   \n",
       "41       app_st_length_fallback   0.000000       1.000000       0.095250   \n",
       "\n",
       "          median        stddev  category_count  null_count  \n",
       "0            NaN           NaN          4334.0         NaN  \n",
       "1            NaN           NaN            60.0         NaN  \n",
       "2            NaN           NaN            65.0         NaN  \n",
       "3            NaN           NaN            68.0         NaN  \n",
       "4            NaN           NaN            70.0         NaN  \n",
       "5            NaN           NaN            72.0         NaN  \n",
       "6            NaN           NaN             4.0         NaN  \n",
       "7      13.000000      6.084951             NaN         0.0  \n",
       "8            NaN           NaN             2.0         NaN  \n",
       "9       9.000000      1.990438             NaN         0.0  \n",
       "10           NaN           NaN             8.0         NaN  \n",
       "11           NaN           NaN             8.0         NaN  \n",
       "12           NaN           NaN             8.0         NaN  \n",
       "13      1.000000      0.428696             NaN         0.0  \n",
       "14      0.000000      0.499074             NaN         0.0  \n",
       "15      0.000000      0.678791             NaN         0.0  \n",
       "16     -0.000000      0.734311             NaN         0.0  \n",
       "17      0.000000      0.680448             NaN         0.0  \n",
       "18      0.000000      0.732752             NaN         0.0  \n",
       "19      0.202432      0.186169             NaN         0.0  \n",
       "20      0.436281      0.269905             NaN         0.0  \n",
       "21      0.562043      0.335704             NaN         0.0  \n",
       "22      0.676223      0.382903             NaN         0.0  \n",
       "23      0.813942      0.422785             NaN         0.0  \n",
       "24     86.000000     53.640162             NaN         0.0  \n",
       "25    108.000000     61.578012             NaN         0.0  \n",
       "26    103.000000     90.105333             NaN         0.0  \n",
       "27    116.000000     70.210930             NaN         0.0  \n",
       "28    119.000000     90.837428             NaN         0.0  \n",
       "29      0.048695      0.015603             NaN         0.0  \n",
       "30  89724.000000  58187.730792             NaN         0.0  \n",
       "31           NaN           NaN           117.0         NaN  \n",
       "32     55.000000     32.855453             NaN         0.0  \n",
       "33   1866.000000   4287.788373             NaN         0.0  \n",
       "34      0.000000      0.142685             NaN         0.0  \n",
       "35           NaN           NaN         13446.0         NaN  \n",
       "36           NaN           NaN          1512.0         NaN  \n",
       "37           NaN           NaN          1494.0         NaN  \n",
       "38      1.000000      0.463832             NaN         0.0  \n",
       "39    751.000000    566.560548             NaN         0.0  \n",
       "40    174.800000    610.879594             NaN         0.0  \n",
       "41      0.000000      0.293561             NaN         0.0  "
      ]
     },
     "execution_count": 43,
     "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": 44,
   "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>i_id</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Chicago2222', 'weight': 0.98220...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>city_cluster_1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Boston_2', 'weight': 0.00870124...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>city_cluster_2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Philadelphia_9', 'weight': 0.02...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>city_cluster_3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Atlanta_1', 'weight': -0.036220...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>city_cluster_4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Philadelphia_16', 'weight': 0.0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>city_cluster_5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Philadelphia_4', 'weight': 0.08...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>city</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'Chicago', 'weight': 0.135174662...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>hour</td>\n",
       "      <td>0.079943</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>weekend</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'false', 'weight': 0.16234420417...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>month</td>\n",
       "      <td>-0.039554</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>direction</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'R', 'weight': 0.060841090293120...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>entryheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'S', 'weight': 0.110888871952353...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>exitheading</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'N', 'weight': 0.095133571782609...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>direction_sin</td>\n",
       "      <td>-0.184110</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>direction_cos</td>\n",
       "      <td>-0.173047</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>entryheading_sin</td>\n",
       "      <td>-0.014921</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>entryheading_cos</td>\n",
       "      <td>0.009543</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>exitheading_sin</td>\n",
       "      <td>-0.021322</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>exitheading_cos</td>\n",
       "      <td>0.005554</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>dist_to_cluster_center_1</td>\n",
       "      <td>0.004261</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>dist_to_cluster_center_2</td>\n",
       "      <td>0.006704</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>dist_to_cluster_center_3</td>\n",
       "      <td>0.006884</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>dist_to_cluster_center_4</td>\n",
       "      <td>0.008288</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>dist_to_cluster_center_5</td>\n",
       "      <td>0.007671</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>intersections_per_cluster_1</td>\n",
       "      <td>-0.009070</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>intersections_per_cluster_2</td>\n",
       "      <td>-0.030661</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>intersections_per_cluster_3</td>\n",
       "      <td>0.001305</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>intersections_per_cluster_4</td>\n",
       "      <td>-0.000014</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>intersections_per_cluster_5</td>\n",
       "      <td>-0.009305</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>observation_ratio_per_city</td>\n",
       "      <td>0.113940</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>population</td>\n",
       "      <td>0.043841</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>zipcode</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': '02135', 'weight': 0.12151213557...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>intersections_per_zipcode</td>\n",
       "      <td>-0.017184</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>pop_intersec_ratio</td>\n",
       "      <td>0.015409</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>zip_code_na</td>\n",
       "      <td>0.002536</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>path</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'AtlantaNorth Avenue Northeast_W...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>entry_street_name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'ChicagoNorth Cicero Avenue', 'w...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>exit_street_name</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[{'category': 'PhiladelphiaNorth 11th Street',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>same_street</td>\n",
       "      <td>-0.182903</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>num_observations</td>\n",
       "      <td>0.216608</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>app_st_length</td>\n",
       "      <td>0.134590</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>app_st_length_fallback</td>\n",
       "      <td>0.082413</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>__INTERCEPT__</td>\n",
       "      <td>0.112217</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                processed_input    weight  \\\n",
       "0                          i_id       NaN   \n",
       "1                city_cluster_1       NaN   \n",
       "2                city_cluster_2       NaN   \n",
       "3                city_cluster_3       NaN   \n",
       "4                city_cluster_4       NaN   \n",
       "5                city_cluster_5       NaN   \n",
       "6                          city       NaN   \n",
       "7                          hour  0.079943   \n",
       "8                       weekend       NaN   \n",
       "9                         month -0.039554   \n",
       "10                    direction       NaN   \n",
       "11                 entryheading       NaN   \n",
       "12                  exitheading       NaN   \n",
       "13                direction_sin -0.184110   \n",
       "14                direction_cos -0.173047   \n",
       "15             entryheading_sin -0.014921   \n",
       "16             entryheading_cos  0.009543   \n",
       "17              exitheading_sin -0.021322   \n",
       "18              exitheading_cos  0.005554   \n",
       "19     dist_to_cluster_center_1  0.004261   \n",
       "20     dist_to_cluster_center_2  0.006704   \n",
       "21     dist_to_cluster_center_3  0.006884   \n",
       "22     dist_to_cluster_center_4  0.008288   \n",
       "23     dist_to_cluster_center_5  0.007671   \n",
       "24  intersections_per_cluster_1 -0.009070   \n",
       "25  intersections_per_cluster_2 -0.030661   \n",
       "26  intersections_per_cluster_3  0.001305   \n",
       "27  intersections_per_cluster_4 -0.000014   \n",
       "28  intersections_per_cluster_5 -0.009305   \n",
       "29   observation_ratio_per_city  0.113940   \n",
       "30                   population  0.043841   \n",
       "31                      zipcode       NaN   \n",
       "32    intersections_per_zipcode -0.017184   \n",
       "33           pop_intersec_ratio  0.015409   \n",
       "34                  zip_code_na  0.002536   \n",
       "35                         path       NaN   \n",
       "36            entry_street_name       NaN   \n",
       "37             exit_street_name       NaN   \n",
       "38                  same_street -0.182903   \n",
       "39             num_observations  0.216608   \n",
       "40                app_st_length  0.134590   \n",
       "41       app_st_length_fallback  0.082413   \n",
       "42                __INTERCEPT__  0.112217   \n",
       "\n",
       "                                     category_weights  \n",
       "0   [{'category': 'Chicago2222', 'weight': 0.98220...  \n",
       "1   [{'category': 'Boston_2', 'weight': 0.00870124...  \n",
       "2   [{'category': 'Philadelphia_9', 'weight': 0.02...  \n",
       "3   [{'category': 'Atlanta_1', 'weight': -0.036220...  \n",
       "4   [{'category': 'Philadelphia_16', 'weight': 0.0...  \n",
       "5   [{'category': 'Philadelphia_4', 'weight': 0.08...  \n",
       "6   [{'category': 'Chicago', 'weight': 0.135174662...  \n",
       "7                                                  []  \n",
       "8   [{'category': 'false', 'weight': 0.16234420417...  \n",
       "9                                                  []  \n",
       "10  [{'category': 'R', 'weight': 0.060841090293120...  \n",
       "11  [{'category': 'S', 'weight': 0.110888871952353...  \n",
       "12  [{'category': 'N', 'weight': 0.095133571782609...  \n",
       "13                                                 []  \n",
       "14                                                 []  \n",
       "15                                                 []  \n",
       "16                                                 []  \n",
       "17                                                 []  \n",
       "18                                                 []  \n",
       "19                                                 []  \n",
       "20                                                 []  \n",
       "21                                                 []  \n",
       "22                                                 []  \n",
       "23                                                 []  \n",
       "24                                                 []  \n",
       "25                                                 []  \n",
       "26                                                 []  \n",
       "27                                                 []  \n",
       "28                                                 []  \n",
       "29                                                 []  \n",
       "30                                                 []  \n",
       "31  [{'category': '02135', 'weight': 0.12151213557...  \n",
       "32                                                 []  \n",
       "33                                                 []  \n",
       "34                                                 []  \n",
       "35  [{'category': 'AtlantaNorth Avenue Northeast_W...  \n",
       "36  [{'category': 'ChicagoNorth Cicero Avenue', 'w...  \n",
       "37  [{'category': 'PhiladelphiaNorth 11th Street',...  \n",
       "38                                                 []  \n",
       "39                                                 []  \n",
       "40                                                 []  \n",
       "41                                                 []  \n",
       "42                                                 []  "
      ]
     },
     "execution_count": 44,
     "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": [
    "## Evaluating the model\n",
    "Since I didn't leave out an appropriate validation set (e.g. top 10% of the test-alike train data) the evaluation is not a good indicator for the leaderboard. I rather used it briefly to check for slight improvments to the previous run. This is definitely a shortcoming of this kernel.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### General model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": 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>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>3.163201</td>\n",
       "      <td>58.088947</td>\n",
       "      <td>1.15869</td>\n",
       "      <td>1.313035</td>\n",
       "      <td>0.018705</td>\n",
       "      <td>0.023481</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             3.163201           58.088947                 1.15869   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               1.313035  0.018705            0.023481  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Using data below adversarial threshold as validation set :-/ \n",
    "sql=\"\"\"SELECT\n",
    "          *\n",
    "        FROM ML.EVALUATE(MODEL `bqml_example.model_TotalTimeStopped_p20`, (\n",
    "        SELECT  t.TotalTimeStopped_p20 as label, \n",
    "                concat(t.city,cast(t.intersectionid as string)) i_id,\n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                --(case \n",
    "                --    when t.hour between 6 and 9 then 'RUSH_HOUR_MORNING'\n",
    "                --   when t.hour between 10 and 15 then 'MIDDAY'\n",
    "                --    when t.hour between 16 and 19 then 'RUSH_HOUR_EVENING'\n",
    "                --    else 'NIGHT'\n",
    "                --end) daytime,\n",
    "                --(case \n",
    "                --    when t.month between 4 and 6 then 'SPRING'\n",
    "                --    when t.month between 7 and 9 then 'SUMMER'\n",
    "                --    when t.month between 10 and 11 then 'FALL'\n",
    "                --    else 'WINTER'\n",
    "                --end) season,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                concat(t.city,t.path) as path,\n",
    "                concat(t.city,t.entryStreetName) as entry_street_name,\n",
    "                concat(t.city,t.exitStreetName) as exit_street_name,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "  FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "               `bqml_example.testalike_av` av,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND t.rowid = av.rowid\n",
    "           AND av.prob_test <= 0.5\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')))\"\"\"\n",
    "\n",
    "client.query(sql).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### General model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "_kg_hide-input": true,
    "_kg_hide-output": 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>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.543738</td>\n",
       "      <td>29.014786</td>\n",
       "      <td>1.130309</td>\n",
       "      <td>1.28713</td>\n",
       "      <td>0.020022</td>\n",
       "      <td>0.021582</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.543738           29.014786                1.130309   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0                1.28713  0.020022            0.021582  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# validation set by rowid\n",
    "sql=\"\"\"SELECT\n",
    "          *\n",
    "        FROM ML.EVALUATE(MODEL `bqml_example.model_TotalTimeStopped_p20_general`, (\n",
    "        SELECT  t.TotalTimeStopped_p20 as label, \n",
    "                t.Longitude,\n",
    "                t.Latitude, \n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "  FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.train` t,\n",
    "               `bqml_example.testalike_av` av,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND t.rowid = av.rowid\n",
    "           and t.rowid >= 2600000\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')\n",
    "           ))\"\"\"\n",
    "\n",
    "#AND av.prob_test <= 0.5\n",
    "\n",
    "client.query(sql).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predict outcomes\n",
    "Predict the target values and generate the output file. Use detailed model for test data with intersection_id, path, exit and entry street existing in train. Use general model otherwise. Clip negative values to 0.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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",
    "    ## Detailed model\n",
    "    \n",
    "    sql=\"\"\"\n",
    "    SELECT\n",
    "      RowId,\n",
    "      case when predicted_label < 0 then \n",
    "          0 \n",
    "      else \n",
    "          predicted_label \n",
    "      end as \"\"\"+mn+\"\"\"\n",
    "    FROM\n",
    "      ML.PREDICT(MODEL `bqml_example.model_\"\"\"+mn+\"\"\"`,\n",
    "        (\n",
    "        SELECT  t.RowId, \n",
    "                concat(t.city,cast(t.intersectionid as string)) i_id,\n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                --(case \n",
    "                --    when t.hour between 6 and 9 then 'RUSH_HOUR_MORNING'\n",
    "                --    when t.hour between 10 and 15 then 'MIDDAY'\n",
    "                --    when t.hour between 16 and 19 then 'RUSH_HOUR_EVENING'\n",
    "                --    else 'NIGHT'\n",
    "                --end) daytime,\n",
    "                --(case \n",
    "                --    when t.month between 4 and 6 then 'SPRING'\n",
    "                --    when t.month between 7 and 9 then 'SUMMER'\n",
    "                --    when t.month between 10 and 11 then 'FALL'\n",
    "                --    else 'WINTER'\n",
    "                --end) season,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                concat(t.city,t.path) as path,\n",
    "                concat(t.city,t.entryStreetName) as entry_street_name,\n",
    "                concat(t.city,t.exitStreetName) as exit_street_name,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "          FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.test` t,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')\n",
    "           AND    EXISTS (\n",
    "                  SELECT\n",
    "                    1\n",
    "                  FROM\n",
    "                    `kaggle-competition-datasets.geotab_intersection_congestion.train` n\n",
    "                  WHERE\n",
    "                    n.IntersectionId = t.IntersectionId\n",
    "                    and n.EntryStreetName = t.EntryStreetName\n",
    "                    AND n.ExitStreetName = t.ExitStreetName\n",
    "                    AND n.Path = t.path)\n",
    "                          \n",
    "          \"\"\"+lmt+\"\"\"))\n",
    "        ORDER BY RowId ASC\"\"\"\n",
    "    \n",
    "    df=client.query(sql).to_dataframe()\n",
    "\n",
    "    ## general model\n",
    "    \n",
    "    sql=\"\"\"\n",
    "    SELECT\n",
    "      RowId,\n",
    "      case when predicted_label < 0 then \n",
    "          0 \n",
    "      else \n",
    "          predicted_label \n",
    "      end as \"\"\"+mn+\"\"\"\n",
    "    FROM\n",
    "      ML.PREDICT(MODEL `bqml_example.model_\"\"\"+mn+\"\"\"_general`,\n",
    "        (\n",
    "        SELECT  t.RowId, \n",
    "                t.Longitude,\n",
    "                t.Latitude, \n",
    "                cc.city_cluster_1,\n",
    "                cc.city_cluster_2,\n",
    "                cc.city_cluster_3,\n",
    "                cc.city_cluster_4,\n",
    "                cc.city_cluster_5,\n",
    "                t.city,\n",
    "                t.hour,\n",
    "                t.weekend,\n",
    "                t.month,\n",
    "                `bqml_example.direction`(t.entryheading, t.exitheading) direction,\n",
    "                --`bqml_example.road_encode`(t.entrystreetname) road_type_entry,\n",
    "                --`bqml_example.road_encode`(t.exitstreetname) road_type_exit,\n",
    "                t.entryheading,\n",
    "                t.exitheading,\n",
    "                round(sin(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_sin,\n",
    "                round(cos(bqml_example.direction2degree(`bqml_example.direction`(t.entryheading, t.exitheading))*ACOS(-1)/180),6) direction_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.entryheading)*ACOS(-1)/180),6) entryheading_cos,\n",
    "                round(sin(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_sin,\n",
    "                round(cos(bqml_example.direction2degree(t.exitheading)*ACOS(-1)/180),6) exitheading_cos,\n",
    "                round(cc.dist_to_cluster_center_1,8) dist_to_cluster_center_1,\n",
    "                round(cc.dist_to_cluster_center_2,8) dist_to_cluster_center_2,\n",
    "                round(cc.dist_to_cluster_center_3,8) dist_to_cluster_center_3,\n",
    "                round(cc.dist_to_cluster_center_4,8) dist_to_cluster_center_4,\n",
    "                round(cc.dist_to_cluster_center_5,8) dist_to_cluster_center_5,\n",
    "                intersections_per_cluster_1,\n",
    "                intersections_per_cluster_2,\n",
    "                intersections_per_cluster_3,\n",
    "                intersections_per_cluster_4,\n",
    "                intersections_per_cluster_5,\n",
    "                count(1)over(partition by t.city, t.hour) / count(1)over(partition by t.city) observation_ratio_per_city,\n",
    "                zp.population,\n",
    "                zp.zipcode,\n",
    "                zp.intersections_per_zipcode,\n",
    "                zp.pop_intersec_ratio,\n",
    "                zp.zip_code_na,\n",
    "                case when t.entryStreetName = t.exitStreetName then\n",
    "                    1\n",
    "                else\n",
    "                    0\n",
    "                end as same_street,\n",
    "                zp.num_observations,\n",
    "                a.app_st_length,\n",
    "                a.app_st_length_fallback\n",
    "          FROM `bqml_example.city_cluster` cc,\n",
    "               `kaggle-competition-datasets.geotab_intersection_congestion.test` t,\n",
    "               `bqml_example.zipcode_population` zp,\n",
    "               `bqml_example.approching_street_imputed` a\n",
    "         WHERE t.city = cc.city\n",
    "           AND t.intersectionid = cc.intersectionid\n",
    "           AND t.intersectionid = zp.intersectionid\n",
    "           AND t.city = zp.city\n",
    "           AND ifnull(t.intersectionid, -99 ) = ifnull(a.intersectionid,-99)\n",
    "           AND t.city = a.city\n",
    "           AND ifnull(t.ExitStreetName,'#_#') = ifnull(a.ExitStreetName,'#_#')\n",
    "           AND ifnull(t.ExitHeading,'#_#') = ifnull(a.ExitHeading,'#_#')\n",
    "           AND ifnull(t.EntryStreetName,'#_#') = ifnull(a.EntryStreetName,'#_#')\n",
    "           AND ifnull(t.EntryHeading,'#_#') = ifnull(a.EntryHeading,'#_#')\n",
    "           AND    NOT EXISTS (\n",
    "                  SELECT\n",
    "                    1\n",
    "                  FROM\n",
    "                    `kaggle-competition-datasets.geotab_intersection_congestion.train` n\n",
    "                  WHERE\n",
    "                    n.IntersectionId = t.IntersectionId\n",
    "                    and n.EntryStreetName = t.EntryStreetName\n",
    "                    AND n.ExitStreetName = t.ExitStreetName\n",
    "                    AND n.Path = t.path)\n",
    "                            \n",
    "          \"\"\"+lmt+\"\"\"))\n",
    "        ORDER BY RowId ASC\"\"\"\n",
    "        \n",
    "    df=df.append(client.query(sql).to_dataframe())\n",
    "        \n",
    "    return df\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": 48,
   "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>1.149341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1_0</td>\n",
       "      <td>0.273937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2_0</td>\n",
       "      <td>0.198945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3_0</td>\n",
       "      <td>0.244206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4_0</td>\n",
       "      <td>0.163601</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>8.534094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>96</td>\n",
       "      <td>96_0</td>\n",
       "      <td>0.324148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>97</td>\n",
       "      <td>97_0</td>\n",
       "      <td>0.369409</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>98</td>\n",
       "      <td>98_0</td>\n",
       "      <td>2.491887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>99</td>\n",
       "      <td>99_0</td>\n",
       "      <td>8.572179</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   TargetId    Target\n",
       "0       0_0  1.149341\n",
       "1       1_0  0.273937\n",
       "2       2_0  0.198945\n",
       "3       3_0  0.244206\n",
       "4       4_0  0.163601\n",
       "..      ...       ...\n",
       "95     95_0  8.534094\n",
       "96     96_0  0.324148\n",
       "97     97_0  0.369409\n",
       "98     98_0  2.491887\n",
       "99     99_0  8.572179\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "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": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('submission.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Thanks for reading sofar. Please consider upvoting the kernel if it was usefull.**"
   ]
  }
 ],
 "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
}