repeatable_splitting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1> Repeatable splitting </h1>\n",
    "\n",
    "In this notebook, we will explore the impact of different ways of creating machine learning datasets.\n",
    "\n",
    "<p>\n",
    "\n",
    "Repeatability is important in machine learning. If you do the same thing now and 5 minutes from now and get different answers, then it makes experimentation difficult. In other words, you will find it difficult to gauge whether a change you made has resulted in an improvement or not."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import google.datalab.bigquery as bq"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Create a simple machine learning model </h3>\n",
    "\n",
    "The dataset that we will use is <a href=\"https://bigquery.cloud.google.com/table/bigquery-samples:airline_ontime_data.flights\">a BigQuery public dataset</a> of airline arrival data. Click on the link, and look at the column names. Switch to the Details tab to verify that the number of records is 70 million, and then switch to the Preview tab to look at a few rows.\n",
    "<p>\n",
    "We want to predict the arrival delay of an airline based on the departure delay. The model that we will use is a zero-bias linear model:\n",
    "$$ delay_{arrival} = \\alpha * delay_{departure} $$\n",
    "<p>\n",
    "To train the model is to estimate a good value for $\\alpha$. \n",
    "<p>\n",
    "One approach to estimate alpha is to use this formula:\n",
    "$$ \\alpha = \\frac{\\sum delay_{departure} delay_{arrival} }{  \\sum delay_{departure}^2 } $$\n",
    "Because we'd like to capture the idea that this relationship is different for flights from New York to Los Angeles vs. flights from Austin to Indianapolis (shorter flight, less busy airports), we'd compute a different $alpha$ for each airport-pair.  For simplicity, we'll do this model only for flights between Denver and Los Angeles."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Naive random split (not repeatable) </h2>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "\n",
    "   SELECT RAND() AS splitfield,\n",
    "   arrival_delay,\n",
    "   departure_delay\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       splitfield  arrival_delay  departure_delay\n",
      "0        0.545383           35.0             10.0\n",
      "1        0.487522           30.0             19.0\n",
      "2        0.938933           29.0             18.0\n",
      "3        0.891664           29.0             29.0\n",
      "4        0.547597           52.0             57.0\n",
      "5        0.950596           30.0             33.0\n",
      "6        0.709231           38.0             22.0\n",
      "7        0.110976           31.0             28.0\n",
      "8        0.211616           85.0             72.0\n",
      "9        0.007159           42.0             30.0\n",
      "10       0.394859           27.0             45.0\n",
      "11       0.117991           48.0             39.0\n",
      "12       0.652870           62.0             52.0\n",
      "13       0.750834           58.0             74.0\n",
      "14       0.717552          176.0            179.0\n",
      "15       0.150757           42.0             -3.0\n",
      "16       0.484473           68.0             80.0\n",
      "17       0.521613           29.0             14.0\n",
      "18       0.417239          113.0            111.0\n",
      "19       0.712180           44.0             62.0\n",
      "20       0.694397           30.0             23.0\n",
      "21       0.775271          138.0            146.0\n",
      "22       0.207167           42.0             18.0\n",
      "23       0.698096          -37.0             -4.0\n",
      "24       0.750750           72.0             89.0\n",
      "25       0.667388           42.0             30.0\n",
      "26       0.175174           50.0             53.0\n",
      "27       0.322397           36.0             -3.0\n",
      "28       0.776977           47.0             -2.0\n",
      "29       0.337911           41.0             14.0\n",
      "...           ...            ...              ...\n",
      "79659    0.307471           -1.0             11.0\n",
      "79660    0.007449           -1.0              4.0\n",
      "79661    0.788435           -1.0              0.0\n",
      "79662    0.583610           -1.0             17.0\n",
      "79663    0.564425           -1.0              5.0\n",
      "79664    0.630142           -1.0             -5.0\n",
      "79665    0.647154           -1.0             13.0\n",
      "79666    0.130939           -1.0              6.0\n",
      "79667    0.978668           -1.0              2.0\n",
      "79668    0.425515           -1.0            -12.0\n",
      "79669    0.082688           -1.0             -6.0\n",
      "79670    0.034992           -1.0             -9.0\n",
      "79671    0.398500           -1.0             -2.0\n",
      "79672    0.541944           -1.0             22.0\n",
      "79673    0.896194           -1.0              0.0\n",
      "79674    0.770968           -1.0              1.0\n",
      "79675    0.612196           -1.0             -1.0\n",
      "79676    0.368587           -1.0             -5.0\n",
      "79677    0.341233           -1.0              4.0\n",
      "79678    0.495844           -1.0             -8.0\n",
      "79679    0.062014           -1.0              6.0\n",
      "79680    0.566357           -1.0             -5.0\n",
      "79681    0.808060           -1.0             10.0\n",
      "79682    0.976514           -1.0             -1.0\n",
      "79683    0.815373           -1.0             -1.0\n",
      "79684    0.513992           -1.0             -2.0\n",
      "79685    0.484678           -1.0             -1.0\n",
      "79686    0.630123           -1.0              0.0\n",
      "79687    0.070028           -1.0              0.0\n",
      "79688    0.590823           -1.0             -7.0\n",
      "\n",
      "[79689 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "#prod = results['prod'][0]\n",
    "#print(prod)\n",
    "print(results)  # basically you are giving it an index using a random probability distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "   arrival_delay * departure_delay AS prod\n",
    "FROM\n",
    "(\n",
    "   SELECT RAND() AS splitfield,\n",
    "   arrival_delay,\n",
    "   departure_delay\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    ")\n",
    "WHERE\n",
    "  splitfield < 0.8\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "216.0\n",
      "         prod\n",
      "0       216.0\n",
      "1       -70.0\n",
      "2       350.0\n",
      "3       -50.0\n",
      "4       460.0\n",
      "5       -33.0\n",
      "6       -40.0\n",
      "7       570.0\n",
      "8       775.0\n",
      "9       522.0\n",
      "10      841.0\n",
      "11     2964.0\n",
      "12      -72.0\n",
      "13      -55.0\n",
      "14      990.0\n",
      "15      836.0\n",
      "16       63.0\n",
      "17       49.0\n",
      "18      868.0\n",
      "19      208.0\n",
      "20      -28.0\n",
      "21      -21.0\n",
      "22     1260.0\n",
      "23      112.0\n",
      "24      -36.0\n",
      "25      152.0\n",
      "26      322.0\n",
      "27     1215.0\n",
      "28      176.0\n",
      "29       99.0\n",
      "...       ...\n",
      "63595    90.0\n",
      "63596    90.0\n",
      "63597    90.0\n",
      "63598    90.0\n",
      "63599    90.0\n",
      "63600    90.0\n",
      "63601    90.0\n",
      "63602    90.0\n",
      "63603    90.0\n",
      "63604    90.0\n",
      "63605    90.0\n",
      "63606    90.0\n",
      "63607    90.0\n",
      "63608    90.0\n",
      "63609    90.0\n",
      "63610    90.0\n",
      "63611    90.0\n",
      "63612    90.0\n",
      "63613    90.0\n",
      "63614    90.0\n",
      "63615    90.0\n",
      "63616    90.0\n",
      "63617    90.0\n",
      "63618    90.0\n",
      "63619    90.0\n",
      "63620    90.0\n",
      "63621    90.0\n",
      "63622    90.0\n",
      "63623    90.0\n",
      "63624    90.0\n",
      "\n",
      "[63625 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "prod = results['prod'][0]\n",
    "print(prod)\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "   SUM(arrival_delay * departure_delay) AS sum\n",
    "FROM\n",
    "(\n",
    "   SELECT RAND() AS splitfield,\n",
    "   arrival_delay,\n",
    "   departure_delay\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    ")\n",
    "WHERE\n",
    "  splitfield < 0.8\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "62045782.0\n",
      "          sum\n",
      "0  62045782.0\n"
     ]
    }
   ],
   "source": [
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "sum = results['sum'][0]\n",
    "print(sum)\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "   SAFE_DIVIDE(SUM(arrival_delay * departure_delay), SUM(departure_delay * departure_delay)) AS alpha\n",
    "FROM\n",
    "(\n",
    "   SELECT RAND() AS splitfield,\n",
    "   arrival_delay,\n",
    "   departure_delay\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    ")\n",
    "WHERE\n",
    "  splitfield < 0.8\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.976171791442077\n"
     ]
    }
   ],
   "source": [
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "alpha = results['alpha'][0]\n",
    "print(alpha)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> What is wrong with calculating RMSE on the training and test data as follows? </h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bqtv\" id=\"3_155765394457\"><table><tr><th>dataset</th><th>arrival_delay</th><th>departure_delay</th></tr><tr><td>train</td><td>35.0</td><td>10.0</td></tr><tr><td>train</td><td>30.0</td><td>19.0</td></tr><tr><td>eval</td><td>29.0</td><td>18.0</td></tr><tr><td>train</td><td>29.0</td><td>29.0</td></tr><tr><td>eval</td><td>52.0</td><td>57.0</td></tr><tr><td>eval</td><td>30.0</td><td>33.0</td></tr><tr><td>train</td><td>38.0</td><td>22.0</td></tr><tr><td>train</td><td>31.0</td><td>28.0</td></tr><tr><td>train</td><td>85.0</td><td>72.0</td></tr><tr><td>eval</td><td>42.0</td><td>30.0</td></tr><tr><td>train</td><td>27.0</td><td>45.0</td></tr><tr><td>train</td><td>48.0</td><td>39.0</td></tr><tr><td>eval</td><td>62.0</td><td>52.0</td></tr><tr><td>train</td><td>58.0</td><td>74.0</td></tr><tr><td>train</td><td>176.0</td><td>179.0</td></tr><tr><td>train</td><td>42.0</td><td>-3.0</td></tr><tr><td>train</td><td>68.0</td><td>80.0</td></tr><tr><td>train</td><td>29.0</td><td>14.0</td></tr><tr><td>train</td><td>113.0</td><td>111.0</td></tr><tr><td>train</td><td>44.0</td><td>62.0</td></tr><tr><td>train</td><td>30.0</td><td>23.0</td></tr><tr><td>eval</td><td>138.0</td><td>146.0</td></tr><tr><td>train</td><td>42.0</td><td>18.0</td></tr><tr><td>train</td><td>-37.0</td><td>-4.0</td></tr><tr><td>eval</td><td>72.0</td><td>89.0</td></tr></table></div>\n",
       "    <br />(rows: 79689, time: 1.9s,     1GB processed, job: job_m7LOIa3Ugcd4Pkox6FqMgStBxu8G)<br />\n",
       "    <script src=\"/static/components/requirejs/require.js\"></script>\n",
       "    <script>\n",
       "      require.config({\n",
       "        paths: {\n",
       "          base: '/static/base',\n",
       "          d3: '//cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3',\n",
       "          plotly: 'https://cdn.plot.ly/plotly-1.5.1.min.js?noext',\n",
       "          jquery: '//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min'\n",
       "        },\n",
       "        map: {\n",
       "          '*': {\n",
       "            datalab: 'nbextensions/gcpdatalab'\n",
       "          }\n",
       "        },\n",
       "        shim: {\n",
       "          plotly: {\n",
       "            deps: ['d3', 'jquery'],\n",
       "            exports: 'plotly'\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "\n",
       "      require(['datalab/charting', 'datalab/element!3_155765394457', 'base/js/events',\n",
       "          'datalab/style!/nbextensions/gcpdatalab/charting.css'],\n",
       "        function(charts, dom, events) {\n",
       "          charts.render('gcharts', dom, events, 'paged_table', [], {\"rows\": [{\"c\": [{\"v\": \"train\"}, {\"v\": 35.0}, {\"v\": 10.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 30.0}, {\"v\": 19.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 29.0}, {\"v\": 18.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 29.0}, {\"v\": 29.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 52.0}, {\"v\": 57.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 30.0}, {\"v\": 33.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 38.0}, {\"v\": 22.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 31.0}, {\"v\": 28.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 85.0}, {\"v\": 72.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 42.0}, {\"v\": 30.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 27.0}, {\"v\": 45.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 48.0}, {\"v\": 39.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 62.0}, {\"v\": 52.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 58.0}, {\"v\": 74.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 176.0}, {\"v\": 179.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 42.0}, {\"v\": -3.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 68.0}, {\"v\": 80.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 29.0}, {\"v\": 14.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 113.0}, {\"v\": 111.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 44.0}, {\"v\": 62.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 30.0}, {\"v\": 23.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 138.0}, {\"v\": 146.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": 42.0}, {\"v\": 18.0}]}, {\"c\": [{\"v\": \"train\"}, {\"v\": -37.0}, {\"v\": -4.0}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 72.0}, {\"v\": 89.0}]}], \"cols\": [{\"id\": \"dataset\", \"label\": \"dataset\", \"type\": \"string\"}, {\"id\": \"arrival_delay\", \"label\": \"arrival_delay\", \"type\": \"number\"}, {\"id\": \"departure_delay\", \"label\": \"departure_delay\", \"type\": \"number\"}]},\n",
       "            {\n",
       "              pageSize: 25,\n",
       "              cssClassNames:  {\n",
       "                tableRow: 'gchart-table-row',\n",
       "                headerRow: 'gchart-table-headerrow',\n",
       "                oddTableRow: 'gchart-table-oddrow',\n",
       "                selectedTableRow: 'gchart-table-selectedrow',\n",
       "                hoverTableRow: 'gchart-table-hoverrow',\n",
       "                tableCell: 'gchart-table-cell',\n",
       "                headerCell: 'gchart-table-headercell',\n",
       "                rowNumberCell: 'gchart-table-rownumcell'\n",
       "              }\n",
       "            },\n",
       "            {source_index: 2, fields: 'dataset,arrival_delay,departure_delay'},\n",
       "            0,\n",
       "            79689);\n",
       "        }\n",
       "      );\n",
       "    </script>\n",
       "  "
      ],
      "text/plain": [
       "QueryResultsTable job_m7LOIa3Ugcd4Pkox6FqMgStBxu8G"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compute_rmse = \"\"\"\n",
    "#standardSQL\n",
    "\n",
    "  SELECT\n",
    "    IF (RAND() < 0.8, 'train', 'eval') AS dataset,\n",
    "    arrival_delay,\n",
    "    departure_delay\n",
    "  FROM\n",
    "    `bigquery-samples.airline_ontime_data.flights`\n",
    "  WHERE\n",
    "    departure_airport = 'DEN'\n",
    "    AND arrival_airport = 'LAX' \n",
    "\"\"\"\n",
    "bq.Query(compute_rmse).execute().result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bqtv\" id=\"4_155765395539\"><table><tr><th>dataset</th><th>rmse</th><th>num_flights</th></tr><tr><td>train</td><td>13.104918736934946</td><td>63491</td></tr><tr><td>eval</td><td>13.000627661275605</td><td>16198</td></tr></table></div>\n",
       "    <br />(rows: 2, time: 1.5s,     1GB processed, job: job_m6Coz-5UWrPsazUG8lWGl3BtJcEm)<br />\n",
       "    <script src=\"/static/components/requirejs/require.js\"></script>\n",
       "    <script>\n",
       "      require.config({\n",
       "        paths: {\n",
       "          base: '/static/base',\n",
       "          d3: '//cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3',\n",
       "          plotly: 'https://cdn.plot.ly/plotly-1.5.1.min.js?noext',\n",
       "          jquery: '//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min'\n",
       "        },\n",
       "        map: {\n",
       "          '*': {\n",
       "            datalab: 'nbextensions/gcpdatalab'\n",
       "          }\n",
       "        },\n",
       "        shim: {\n",
       "          plotly: {\n",
       "            deps: ['d3', 'jquery'],\n",
       "            exports: 'plotly'\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "\n",
       "      require(['datalab/charting', 'datalab/element!4_155765395539', 'base/js/events',\n",
       "          'datalab/style!/nbextensions/gcpdatalab/charting.css'],\n",
       "        function(charts, dom, events) {\n",
       "          charts.render('gcharts', dom, events, 'table', [], {\"rows\": [{\"c\": [{\"v\": \"train\"}, {\"v\": 13.104918736934946}, {\"v\": 63491}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 13.000627661275605}, {\"v\": 16198}]}], \"cols\": [{\"id\": \"dataset\", \"label\": \"dataset\", \"type\": \"string\"}, {\"id\": \"rmse\", \"label\": \"rmse\", \"type\": \"number\"}, {\"id\": \"num_flights\", \"label\": \"num_flights\", \"type\": \"number\"}]},\n",
       "            {\n",
       "              pageSize: 25,\n",
       "              cssClassNames:  {\n",
       "                tableRow: 'gchart-table-row',\n",
       "                headerRow: 'gchart-table-headerrow',\n",
       "                oddTableRow: 'gchart-table-oddrow',\n",
       "                selectedTableRow: 'gchart-table-selectedrow',\n",
       "                hoverTableRow: 'gchart-table-hoverrow',\n",
       "                tableCell: 'gchart-table-cell',\n",
       "                headerCell: 'gchart-table-headercell',\n",
       "                rowNumberCell: 'gchart-table-rownumcell'\n",
       "              }\n",
       "            },\n",
       "            {source_index: 3, fields: 'dataset,rmse,num_flights'},\n",
       "            0,\n",
       "            2);\n",
       "        }\n",
       "      );\n",
       "    </script>\n",
       "  "
      ],
      "text/plain": [
       "QueryResultsTable job_m6Coz-5UWrPsazUG8lWGl3BtJcEm"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compute_rmse = \"\"\"\n",
    "#standardSQL\n",
    "SELECT\n",
    "  dataset,\n",
    "  SQRT(AVG((arrival_delay - ALPHA * departure_delay)*(arrival_delay - ALPHA * departure_delay))) AS rmse,\n",
    "  COUNT(arrival_delay) AS num_flights\n",
    "FROM (\n",
    "  SELECT\n",
    "    IF (RAND() < 0.8, 'train', 'eval') AS dataset,\n",
    "    arrival_delay,\n",
    "    departure_delay\n",
    "  FROM\n",
    "    `bigquery-samples.airline_ontime_data.flights`\n",
    "  WHERE\n",
    "    departure_airport = 'DEN'\n",
    "    AND arrival_airport = 'LAX' )\n",
    "GROUP BY\n",
    "  dataset\n",
    "\"\"\"\n",
    "bq.Query(compute_rmse.replace('ALPHA', str(alpha))).execute().result()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hint:\n",
    "* Are you really getting the same training data in the compute_rmse query as in the compute_alpha query?\n",
    "* Do you get the same answers each time you rerun the compute_alpha and compute_rmse blocks?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> How do we correctly train and evaluate? </h3>\n",
    "<br/>\n",
    "Here's the right way to compute the RMSE using the actual training and held-out (evaluation) data. Note how much harder this feels.\n",
    "\n",
    "Although the calculations are now correct, the experiment is still not repeatable.\n",
    "\n",
    "Try running it several times; do you get the same answer?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "train_and_eval_rand = \"\"\"\n",
    "#standardSQL\n",
    "WITH\n",
    "  alldata AS (\n",
    "  SELECT\n",
    "    IF (RAND() < 0.8,\n",
    "      'train',\n",
    "      'eval') AS dataset,\n",
    "    arrival_delay,\n",
    "    departure_delay\n",
    "  FROM\n",
    "    `bigquery-samples.airline_ontime_data.flights`\n",
    "  WHERE\n",
    "    departure_airport = 'DEN'\n",
    "    AND arrival_airport = 'LAX' )\n",
    "SELECT\n",
    "  dataset,\n",
    "  COUNT(arrival_delay) AS num_flights\n",
    "FROM\n",
    "  alldata\n",
    "GROUP BY\n",
    "  dataset\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bqtv\" id=\"5_155765401320\"><table><tr><th>dataset</th><th>num_flights</th></tr><tr><td>train</td><td>63817</td></tr><tr><td>eval</td><td>15872</td></tr></table></div>\n",
       "    <br />(rows: 2, time: 0.9s,     1GB processed, job: job_J1dkPyiVrzJ78Jwm9rlPRdz76SLu)<br />\n",
       "    <script src=\"/static/components/requirejs/require.js\"></script>\n",
       "    <script>\n",
       "      require.config({\n",
       "        paths: {\n",
       "          base: '/static/base',\n",
       "          d3: '//cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3',\n",
       "          plotly: 'https://cdn.plot.ly/plotly-1.5.1.min.js?noext',\n",
       "          jquery: '//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min'\n",
       "        },\n",
       "        map: {\n",
       "          '*': {\n",
       "            datalab: 'nbextensions/gcpdatalab'\n",
       "          }\n",
       "        },\n",
       "        shim: {\n",
       "          plotly: {\n",
       "            deps: ['d3', 'jquery'],\n",
       "            exports: 'plotly'\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "\n",
       "      require(['datalab/charting', 'datalab/element!5_155765401320', 'base/js/events',\n",
       "          'datalab/style!/nbextensions/gcpdatalab/charting.css'],\n",
       "        function(charts, dom, events) {\n",
       "          charts.render('gcharts', dom, events, 'table', [], {\"rows\": [{\"c\": [{\"v\": \"train\"}, {\"v\": 63817}]}, {\"c\": [{\"v\": \"eval\"}, {\"v\": 15872}]}], \"cols\": [{\"id\": \"dataset\", \"label\": \"dataset\", \"type\": \"string\"}, {\"id\": \"num_flights\", \"label\": \"num_flights\", \"type\": \"number\"}]},\n",
       "            {\n",
       "              pageSize: 25,\n",
       "              cssClassNames:  {\n",
       "                tableRow: 'gchart-table-row',\n",
       "                headerRow: 'gchart-table-headerrow',\n",
       "                oddTableRow: 'gchart-table-oddrow',\n",
       "                selectedTableRow: 'gchart-table-selectedrow',\n",
       "                hoverTableRow: 'gchart-table-hoverrow',\n",
       "                tableCell: 'gchart-table-cell',\n",
       "                headerCell: 'gchart-table-headercell',\n",
       "                rowNumberCell: 'gchart-table-rownumcell'\n",
       "              }\n",
       "            },\n",
       "            {source_index: 4, fields: 'dataset,num_flights'},\n",
       "            0,\n",
       "            2);\n",
       "        }\n",
       "      );\n",
       "    </script>\n",
       "  "
      ],
      "text/plain": [
       "QueryResultsTable job_J1dkPyiVrzJ78Jwm9rlPRdz76SLu"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.Query(train_and_eval_rand).execute().result()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_and_eval_rand = \"\"\"\n",
    "#standardSQL\n",
    "WITH\n",
    "  alldata AS (\n",
    "  SELECT\n",
    "    IF (RAND() < 0.8,\n",
    "      'train',\n",
    "      'eval') AS dataset,\n",
    "    arrival_delay,\n",
    "    departure_delay\n",
    "  FROM\n",
    "    `bigquery-samples.airline_ontime_data.flights`\n",
    "  WHERE\n",
    "    departure_airport = 'DEN'\n",
    "    AND arrival_airport = 'LAX' ),\n",
    "  training AS (\n",
    "  SELECT\n",
    "    SAFE_DIVIDE( SUM(arrival_delay * departure_delay) , SUM(departure_delay * departure_delay)) AS alpha\n",
    "  FROM\n",
    "    alldata\n",
    "  WHERE\n",
    "    dataset = 'train' )\n",
    "SELECT\n",
    "  MAX(alpha) AS alpha,\n",
    "  dataset,\n",
    "  SQRT(AVG((arrival_delay - alpha * departure_delay)*(arrival_delay - alpha * departure_delay))) AS rmse,\n",
    "  COUNT(arrival_delay) AS num_flights\n",
    "FROM\n",
    "  alldata,\n",
    "  training\n",
    "GROUP BY\n",
    "  dataset\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bqtv\" id=\"6_155765402333\"><table><tr><th>alpha</th><th>dataset</th><th>rmse</th><th>num_flights</th></tr><tr><td>0.9760207869303448</td><td>train</td><td>13.084922918858021</td><td>63895</td></tr><tr><td>0.9760207869303448</td><td>eval</td><td>13.079169661146635</td><td>15794</td></tr></table></div>\n",
       "    <br />(rows: 2, time: 1.1s,     1GB processed, job: job_58vBPmeH0CCiABfvA6usGQjwOx2s)<br />\n",
       "    <script src=\"/static/components/requirejs/require.js\"></script>\n",
       "    <script>\n",
       "      require.config({\n",
       "        paths: {\n",
       "          base: '/static/base',\n",
       "          d3: '//cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3',\n",
       "          plotly: 'https://cdn.plot.ly/plotly-1.5.1.min.js?noext',\n",
       "          jquery: '//ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min'\n",
       "        },\n",
       "        map: {\n",
       "          '*': {\n",
       "            datalab: 'nbextensions/gcpdatalab'\n",
       "          }\n",
       "        },\n",
       "        shim: {\n",
       "          plotly: {\n",
       "            deps: ['d3', 'jquery'],\n",
       "            exports: 'plotly'\n",
       "          }\n",
       "        }\n",
       "      });\n",
       "\n",
       "      require(['datalab/charting', 'datalab/element!6_155765402333', 'base/js/events',\n",
       "          'datalab/style!/nbextensions/gcpdatalab/charting.css'],\n",
       "        function(charts, dom, events) {\n",
       "          charts.render('gcharts', dom, events, 'table', [], {\"rows\": [{\"c\": [{\"v\": 0.9760207869303448}, {\"v\": \"train\"}, {\"v\": 13.084922918858021}, {\"v\": 63895}]}, {\"c\": [{\"v\": 0.9760207869303448}, {\"v\": \"eval\"}, {\"v\": 13.079169661146635}, {\"v\": 15794}]}], \"cols\": [{\"id\": \"alpha\", \"label\": \"alpha\", \"type\": \"number\"}, {\"id\": \"dataset\", \"label\": \"dataset\", \"type\": \"string\"}, {\"id\": \"rmse\", \"label\": \"rmse\", \"type\": \"number\"}, {\"id\": \"num_flights\", \"label\": \"num_flights\", \"type\": \"number\"}]},\n",
       "            {\n",
       "              pageSize: 25,\n",
       "              cssClassNames:  {\n",
       "                tableRow: 'gchart-table-row',\n",
       "                headerRow: 'gchart-table-headerrow',\n",
       "                oddTableRow: 'gchart-table-oddrow',\n",
       "                selectedTableRow: 'gchart-table-selectedrow',\n",
       "                hoverTableRow: 'gchart-table-hoverrow',\n",
       "                tableCell: 'gchart-table-cell',\n",
       "                headerCell: 'gchart-table-headercell',\n",
       "                rowNumberCell: 'gchart-table-rownumcell'\n",
       "              }\n",
       "            },\n",
       "            {source_index: 5, fields: 'alpha,dataset,rmse,num_flights'},\n",
       "            0,\n",
       "            2);\n",
       "        }\n",
       "      );\n",
       "    </script>\n",
       "  "
      ],
      "text/plain": [
       "QueryResultsTable job_58vBPmeH0CCiABfvA6usGQjwOx2s"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bq.Query(train_and_eval_rand).execute().result()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Using HASH of date to split the data </h2>\n",
    "\n",
    "Let's split by date and train."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         date airline airline_code departure_airport departure_state  \\\n",
      "0  2012-11-12      F9        20436               DEN              CO   \n",
      "1  2012-11-01      F9        20436               DEN              CO   \n",
      "2  2012-11-12      F9        20436               DEN              CO   \n",
      "3  2012-11-12      F9        20436               DEN              CO   \n",
      "4  2012-11-21      F9        20436               DEN              CO   \n",
      "5  2012-01-10      F9        20436               DEN              CO   \n",
      "6  2012-11-12      F9        20436               DEN              CO   \n",
      "7  2012-01-08      F9        20436               DEN              CO   \n",
      "8  2012-11-01      F9        20436               DEN              CO   \n",
      "9  2012-11-12      F9        20436               DEN              CO   \n",
      "\n",
      "   departure_lat  departure_lon arrival_airport arrival_state  arrival_lat  \\\n",
      "0          39.86        -104.67             LAX            CA        33.94   \n",
      "1          39.86        -104.67             LAX            CA        33.94   \n",
      "2          39.86        -104.67             LAX            CA        33.94   \n",
      "3          39.86        -104.67             LAX            CA        33.94   \n",
      "4          39.86        -104.67             LAX            CA        33.94   \n",
      "5          39.86        -104.67             LAX            CA        33.94   \n",
      "6          39.86        -104.67             LAX            CA        33.94   \n",
      "7          39.86        -104.67             LAX            CA        33.94   \n",
      "8          39.86        -104.67             LAX            CA        33.94   \n",
      "9          39.86        -104.67             LAX            CA        33.94   \n",
      "\n",
      "   arrival_lon  departure_schedule  departure_actual  departure_delay  \\\n",
      "0       -118.4                2125              2137             12.0   \n",
      "1       -118.4                1302              1300             -2.0   \n",
      "2       -118.4                1302              1305              3.0   \n",
      "3       -118.4                 830               826             -4.0   \n",
      "4       -118.4                2120              2130             10.0   \n",
      "5       -118.4                1210              1206             -4.0   \n",
      "6       -118.4                1630              1633              3.0   \n",
      "7       -118.4                2125              2130              5.0   \n",
      "8       -118.4                1630              1637              7.0   \n",
      "9       -118.4                1812              1832             20.0   \n",
      "\n",
      "   arrival_schedule  arrival_actual  arrival_delay  \n",
      "0              2247            2305           18.0  \n",
      "1              1425            1418           -7.0  \n",
      "2              1425            1423           -2.0  \n",
      "3               951             959            8.0  \n",
      "4              2242            2317           35.0  \n",
      "5              1334            1327           -7.0  \n",
      "6              1752            1757            5.0  \n",
      "7              2254            2244          -10.0  \n",
      "8              1752            1804           12.0  \n",
      "9              1933            1956           23.0  \n"
     ]
    }
   ],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "    *\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    "  AND MOD(ABS(FARM_FINGERPRINT(date)), 10) < 8\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   f0_\n",
      "0  3725999277042568035\n",
      "1  4810755874896543010\n",
      "2  3725999277042568035\n",
      "3  3725999277042568035\n",
      "4  8090764422093330687\n",
      "5  4456799817030954973\n",
      "6  3725999277042568035\n",
      "7  4561376087784110635\n",
      "8  4810755874896543010\n",
      "9  3725999277042568035\n"
     ]
    }
   ],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "    FARM_FINGERPRINT(date)\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    "  AND MOD(ABS(FARM_FINGERPRINT(date)), 10) < 8\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   f0_\n",
      "0 -1556909689059132759\n",
      "1 -1556909689059132759\n",
      "2 -3426016097554679449\n",
      "3 -3426016097554679449\n",
      "4 -3426016097554679449\n",
      "5 -3426016097554679449\n",
      "6 -3145252244108862159\n",
      "7 -3145252244108862159\n",
      "8 -3145252244108862159\n",
      "9 -3145252244108862159\n"
     ]
    }
   ],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "    FARM_FINGERPRINT(date)\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    "  AND MOD(ABS(FARM_FINGERPRINT(date)), 10) > 8\n",
    "LIMIT 10\n",
    "\"\"\"\n",
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "print(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.9758039143620403\n"
     ]
    }
   ],
   "source": [
    "compute_alpha = \"\"\"\n",
    "#standardSQL\n",
    "SELECT \n",
    "   SAFE_DIVIDE(SUM(arrival_delay * departure_delay), SUM(departure_delay * departure_delay)) AS alpha\n",
    "FROM\n",
    "  `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "  departure_airport = 'DEN' AND arrival_airport = 'LAX'\n",
    "  AND MOD(ABS(FARM_FINGERPRINT(date)), 10) < 8\n",
    "\"\"\"\n",
    "results = bq.Query(compute_alpha).execute().result().to_dataframe()\n",
    "alpha = results['alpha'][0]\n",
    "print(alpha)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now use the alpha to compute RMSE. Because the alpha value is repeatable, we don't need to worry that the alpha in the compute_rmse will be different from the alpha computed in the compute_alpha."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  dataset       rmse  num_flights\n",
      "0    eval  12.764685        15671\n",
      "1   train  13.160712        64018\n"
     ]
    }
   ],
   "source": [
    "compute_rmse = \"\"\"\n",
    "#standardSQL\n",
    "SELECT\n",
    "  IF(MOD(ABS(FARM_FINGERPRINT(date)), 10) < 8, 'train', 'eval') AS dataset,\n",
    "  SQRT(AVG((arrival_delay - ALPHA * departure_delay)*(arrival_delay - ALPHA * departure_delay))) AS rmse,\n",
    "  COUNT(arrival_delay) AS num_flights\n",
    "FROM\n",
    "    `bigquery-samples.airline_ontime_data.flights`\n",
    "WHERE\n",
    "    departure_airport = 'DEN'\n",
    "    AND arrival_airport = 'LAX'\n",
    "GROUP BY\n",
    "  dataset\n",
    "\"\"\"\n",
    "print(bq.Query(compute_rmse.replace('ALPHA', str(alpha))).execute().result().to_dataframe().head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note also that the RMSE on the evaluation dataset more from the RMSE on the training dataset when we do the split correctly.  This should be expected; in the RAND() case, there was leakage between training and evaluation datasets, because there is high correlation between flights on the same day.\n",
    "<p>\n",
    "This is one of the biggest dangers with doing machine learning splits the wrong way -- <b> you will develop a false sense of confidence in how good your model is! </b>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Copyright 2018 Google Inc.\n",
    "Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at\n",
    "http://www.apache.org/licenses/LICENSE-2.0\n",
    "Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License."
   ]
  }
 ],
 "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.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}