Drawing Insights with Big Query ML.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drawing Insights with Big Query ML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Google Big Query is a managed, scalable data warehouse that enables analysis over large sets of data.  Big Query ML extends this functionality by enabling the use of Machine Learning with a standard SQL language by easily allowing you to train models, evaluate, and predict.\n",
    "\n",
    "A model in Big Query ML represents what an ML System has previously learned from training data.  \n",
    "There are many types of models supported such as:\n",
    "- Linear regression\n",
    "- Binary logistic regression\n",
    "- Multiclass logistic regression\n",
    "- K-means clustering \n",
    "- Matrix Factorization\n",
    "- Time series\n",
    "- Boosted Tree \n",
    "- Deep Neural Network \n",
    "- AutoML Tables \n",
    "- TensorFlow "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab: Getting Started in Big Query ML with Linear Regression\n",
    "\n",
    "In this lab we will look at using NOAA temperature data for a set of years to predict future average temperature values using [Linear Regression.](https://en.wikipedia.org/wiki/Linear_regression#:~:text=In%20statistics%2C%20linear%20regression%20is,is%20called%20simple%20linear%20regression.)\n",
    "\n",
    "1) First we will create a dataset to store the model. [Click here to open the Big Query UI](https://console.cloud.google.com/bigquery?_ga=2.44329517.868398427.1595536877-1775816527.1575499949)\n",
    "\n",
    "2) Click your project name, which begins with `cal-`, in the lower left under `Resources`\n",
    "\n",
    "![Project name](./assets/lab10/lab10_selectproject.png)\n",
    "\n",
    "3) Click `Create Dataset` \n",
    "\n",
    "![create dataset](./assets/lab10/lab10_createdataset.png)\n",
    "\n",
    "4) Name the dataset `bigqueryml_ca` and click `Create dataset`.\n",
    "\n",
    "5) Next we will train our linear regression model using the code block below. This model is trained to predict on the `Temp_f` column."
   ]
  },
  {
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL\n",
    "  `bigqueryml_ca.temperature_model`\n",
    "OPTIONS\n",
    "  ( model_type='linear_reg',\n",
    "    data_split_method='NO_SPLIT',\n",
    "    input_label_cols=['Temp_f'],\n",
    "    max_iterations=15) AS\n",
    "SELECT AVG(b.temp) as Temp_f, cast(b.year as int64) as year\n",
    "FROM `bigquery-public-data.noaa_gsod.stations` a\n",
    "JOIN `bigquery-public-data.noaa_gsod.gsod*` b\n",
    "ON a.usaf=b.stn AND a.wban=b.wban \n",
    "WHERE a.country='US' and a.state='AK' AND b.year > '1970' AND b.year < '2020'\n",
    "Group By b.year, a.state"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6) Create and populate a table to evaluate the data against an unknown set years to see how the model predicts the result. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE TABLE bigqueryml_ca.sample_data (\n",
    "year INT64,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2020);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2030);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2040);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2050);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2060);\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Due to a Big Query Restriction on insert statements, please wait 10 sec and execute the next block."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2070);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2080);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2090);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2100);\n",
    "\n",
    "INSERT bigqueryml_ca.sample_data (year) VALUES(2110);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we can determine how our table evalutes against the full set of NOAA weather data for the State of Alaska.  This provides details like r^2 and mean absolute error.  The R^2 value tells us how well our model fits our data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_absolute_error</th>\n",
       "      <th>mean_squared_error</th>\n",
       "      <th>mean_squared_log_error</th>\n",
       "      <th>median_absolute_error</th>\n",
       "      <th>r2_score</th>\n",
       "      <th>explained_variance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.337384</td>\n",
       "      <td>12.880044</td>\n",
       "      <td>0.018</td>\n",
       "      <td>1.571725</td>\n",
       "      <td>0.011046</td>\n",
       "      <td>0.059528</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
       "0             2.337384           12.880044                   0.018   \n",
       "\n",
       "   median_absolute_error  r2_score  explained_variance  \n",
       "0               1.571725  0.011046            0.059528  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM\n",
    "ML.EVALUATE(\n",
    "  MODEL `bigqueryml_ca.temperature_model`,\n",
    "  # Table to evaluate against\n",
    "  (\n",
    "    SELECT AVG(b.temp) as Temp_f, cast(b.year as int64) as year\n",
    "    FROM `bigquery-public-data.noaa_gsod.stations` a\n",
    "    JOIN `bigquery-public-data.noaa_gsod.gsod*` b\n",
    "    ON a.usaf=b.stn AND a.wban=b.wban \n",
    "    WHERE a.country='US' and a.state='AK' AND b.year < '2020'\n",
    "    Group By b.year, a.state\n",
    "  )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7) Finally predict the average temp_f based on a set of years in the `sample_data` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>predicted_Temp_f</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35.201994</td>\n",
       "      <td>2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>36.301745</td>\n",
       "      <td>2030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.401496</td>\n",
       "      <td>2040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38.501247</td>\n",
       "      <td>2050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39.600998</td>\n",
       "      <td>2060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>40.700749</td>\n",
       "      <td>2070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>41.800500</td>\n",
       "      <td>2080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>42.900251</td>\n",
       "      <td>2090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>44.000002</td>\n",
       "      <td>2100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>45.099753</td>\n",
       "      <td>2110</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   predicted_Temp_f  year\n",
       "0         35.201994  2020\n",
       "1         36.301745  2030\n",
       "2         37.401496  2040\n",
       "3         38.501247  2050\n",
       "4         39.600998  2060\n",
       "5         40.700749  2070\n",
       "6         41.800500  2080\n",
       "7         42.900251  2090\n",
       "8         44.000002  2100\n",
       "9         45.099753  2110"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ML.PREDICT (MODEL `bigqueryml_ca.temperature_model`,\n",
    "    (\n",
    "    SELECT year\n",
    "    FROM `bigqueryml_ca.sample_data`\n",
    "  ) \n",
    ")\n",
    "\n",
    "ORDER BY year asc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see the average temperature is trending upwards for the State of Alaska. \n",
    "\n",
    "Finally we will save the dataset for average temperature vs years into a dataframe, and then add in the machine learning result.  We will combine the data into a data frame and plot the data to observe the linear regression visually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery avg_temperature\n",
    "SELECT AVG(b.temp) as Temp_f, cast(b.year as int64) as year\n",
    "FROM `bigquery-public-data.noaa_gsod.stations` a\n",
    "JOIN `bigquery-public-data.noaa_gsod.gsod*` b\n",
    "ON a.usaf=b.stn AND a.wban=b.wban \n",
    "WHERE a.country='US' and a.state='AK' AND b.year < '2020'\n",
    "Group By b.year, a.state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery mlresult\n",
    "SELECT\n",
    "  year, predicted_Temp_f as Temp_f\n",
    "FROM\n",
    "  ML.PREDICT (MODEL `bigqueryml_ca.temperature_model`,\n",
    "    (\n",
    "    SELECT cast(b.year as int64) as year\n",
    "    FROM `bigquery-public-data.noaa_gsod.stations` a\n",
    "    JOIN `bigquery-public-data.noaa_gsod.gsod*` b\n",
    "    ON a.usaf=b.stn AND a.wban=b.wban \n",
    "    WHERE a.country='US' and a.state='AK' AND b.year < '2020'\n",
    "    ) \n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "frames = [avg_temperature, mlresult]\n",
    "result = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYEAAAEGCAYAAACD7ClEAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAgAElEQVR4nO3dfZiddX3n8ff3JMMkEjSYDBpJZqOl7EptCDICGq1uhC5Fi9uiLlaUrl2zdrUVnwLUXmtRa0n00krx2jYX2MXqbhsNCIVSiiLaUAmdQBKgiA97IQRTgZGUDJKTmcx3/7jvIScn55w5D/fvfvy8rmuuzNzn6Ttncn7f3/39Pdzm7oiISDXVsg5ARESyoyQgIlJhSgIiIhWmJCAiUmFKAiIiFTY/6wC6tXTpUl+5cmXWYYiIFMr27dufcPeRdrcXJgmsXLmS8fHxrMMQESkUM/txp9tVDhIRqTAlARGRClMSEBGpMCUBEZEKUxIQEamwoEnAzBaY2V1mttPM7jezy+Ljq83sTjPbYWbjZnZayDhERIpoYrLOzkf2MjFZD/YaoaeI1oG17j5pZkPAVjO7Gfg4cJm732xm5wAbgdcFjkVEpDCu3/EoF2/ZxVCtxtTMDBvPW8W5q49P/HWCngl4ZDL+cSj+8vjrufHx5wE/CRmHiEiRTEzWuXjLLvZPzbCvPs3+qRnWb9kV5Iwg+GIxM5sHbAdOAL7g7tvM7CLgFjP7DFEielWbx64D1gGMjo6GDlVEJBd2P/kMQ7Ua+5l59thQrcbuJ59hyaLhRF8r+MCwux9099XAcuA0M3sZ8LvAB9x9BfAB4Oo2j93k7mPuPjYy0nbVs4hIqSw/diFTMzOHHZuamWH5sQsTf63UZge5+17gduBs4ELg2vimrwIaGBYRiS1ZNMzG81axYKjGMcPzWTBUY+N5qxI/C4DA5SAzGwGm3H2vmS0EzgQ2EI0BvJYoKawFfhAyDhGRvJiYrLP7yWdYfuzCjo36uauPZ80JS7u67yBCjwksA66JxwVqwGZ3v9HM9gKfN7P5wH7iur+ISJn1OuNnyaLhYI3/rKBJwN13Aae0OL4VODXka4uI5EnjjJ/ZAd/1W3ax5oSlwRv6TrRiWEQkBbMzfhrNzvjJkpKAiEgK0pzx0wslARGRFKQ546cXhbmymIhI0aU146cXSgJNup2+JSLSjzRm/PRCSaBBWhs2iYjkhcYEYmlu2CQikhdKArG8Tt8SEQlJSSCW1+lbIiIhKQnE8jp9S0QkJA0MN8jj9C0RkZCUBJrkbfqWiEhIKgeJiFSYkoCISIUpCYiIJGhiss7OR/YWZo2RxgRERBJSxF0HdCYgIpKAou46oCQgIpKAou46oCQgIpKAou46EDQJmNkCM7vLzHaa2f1mdlnDbb9nZg/GxzeGjENEJLSi7joQemC4Dqx190kzGwK2mtnNwELgTcAqd6+b2XGB4xARCa6Iuw4ETQLu7sBk/ONQ/OXA7wKXu3s9vt9jIeMQEUlL0XYdCD4mYGbzzGwH8Bhwq7tvA04EXmNm28zs22b2itBxtFO0Ob0iIkkKvk7A3Q8Cq81sMXCdmb0sft1jgTOAVwCbzewl8ZnDs8xsHbAOYHR0NPHYijinV0QkSanNDnL3vcDtwNnAbuBaj9wFzABLWzxmk7uPufvYyMhIovEUdU6viEiSQs8OGonPADCzhcCZwPeArwNr4+MnAkcBT4SMpVlR5/SKSLHkveQcuhy0DLjGzOYRJZzN7n6jmR0FfNHM7gMOABc2l4JCK+qcXhEpjiKUnEPPDtoFnNLi+AHggpCvPZfZOb3rm/5ARRrVF5H8aiw57yfqcK7fsos1JyzNVTtT6Q3kijinV0SKYbbkPJsA4FDJOU9tTaWTABRvTq+IFENRSs7aO0hEJICibCNR+TMBEZFQilByVhIQEQko7yVnJQERkZxaeclNz37/0OVvCPIaSgIiIhmamKwfVi5qbPgbrbzkpiCJQElARCQjzYvJ9k/NdLx/iESgJCAiudTcQy6bVovJsqAkICK5U4TtFvrVrtyTFa0TEJFcKfMOv4MmAI0JiEjpFWW7hW4l1fPX7CARCS4PdfiibLfQTpLlnlANfyOVg0QEiOrwazbcxgVXbWPNhtu4YcejmcTR7XYLedynP4kE0Ph7p/E3sJS38e/b2NiYj4+PZx2GSFfy0KPuxcRknTUbbjtsiuKCoRp3XLw2s/g7vYd5GjhOsue/YKiW+N/AzLa7+1i721UOEklYnhqobuWxDt9uu4Ws9+kPVe7Z+cheLrhqW+p/AyUBkQRl3UD1q0h1+CwTVhIJoF2dP6u/gcYERBJU1GtXF2Hb49kxgKOPmpd6Y7nykpuCJgDI7m+gMwGRBBWpR90sz9seN5fY3jq2nM3ju1O5NOwgjX+vs3uy+BsoCXRQtME9yV7Rr12dx22PW5XYNo/v5sb3vZqnDxwM8vlMs+FvlvbfIGgSMLMFwHeA4fi1vubuH2u4/cPAp4ERd38iZCy9KuLgnuRDnnvURdRuDODpAwc5ecXiRF4j7wu6Qgo9JlAH1rr7ycBq4GwzOwPAzFYAZwEPB46hZ2Veti7pWLJomJNXLFYCSEDoEtsgCeDz569OfV5/0oKeCXi0CGEy/nEo/ppdmPA5YD1wfcgY+pHH6XIiVdVtia2X8m0Se/g0rq0o0kywZsHHBMxsHrAdOAH4grtvM7NzgUfdfaeZdXrsOmAdwOjoaOhQn1XkwT2RLIUaR5urxDZX+TbE3P6ydBaDJwF3PwisNrPFwHVmtgr4KPCrXTx2E7AJohXDQQNtUPTBPSm3vE5YCD2O1u/isVBTO8vSWUxtdpC77zWz24E3AS8GZs8ClgN3m9lp7v6vacUzFw3uSR7ldcJClovkWvXI90/NcOonv5HI87cb7C1LZzH07KARYCpOAAuBM4EN7n5cw30eAsbyNjsI8jldTrKRh953tw1tFrFmWRpp1SMfRC8zfMrQWQx9JrAMuCYeF6gBm939xsCvKZKovPS+u2los4o1y9LIkkXDc16bdy6DTO0semcx9OygXcApc9xnZcgYRAaRp72A5mpos4y1XWkEoo3R8ragq1ER5/YnSSuGRTpo1/u+/ydP8byFQ6mWAOaqQWc9W6W5NLL1h0+wZsNtQc5KslzRWzZKAiIdtOp9PzM1zbu/NM5R89IvD3WqQXdbkgk5ZjBbGglxVqKGPwwlAZEOmnvfBw4eZMahPj1DfTqb8lC7GnQ3s1XSGjNI4qxE5Z50KAmIzKGx9/1vzxzgvV+5h6mD08/enqcFQp3OFNIcMxh0oDiJFb2DyMNssLQoCYh0obHMkfcFQu3OFNIcM+hnDn3Ihr+XRj0vs8HSoiQg0oMiLxBKexrnXHPoQ12msVkvjXq7s6WTlj032LbVWVMSEOlRURcIZZHA2p2VhL5K16xeS2CtzpZ8xjnnz7YynMFEgDQoCYj0IU8LhHopdWSZwNLq+TfqtQTW6mypftAB50BGEwFCUxIQKbB+6tdpJrCsZ/j0WgJrPluqTx+kVrPDViTnaSJAEpQERAoqT6uZW8l6hg/0VwJrPFs6+qh5vPHKrYfdnreJAINSEhApqKxXCLeSVM9/wVCNicl6Ir9HPyWwxrOlok4E6JaSgEhB5WE/+yTr/I2STmaDlMCKOhGgW6GvMSwigcyWOhqvcZtmLzVUAoD8lVzKfM1onQmI5Fyn2T/d9FKTXP2aZLmncbB1fg3m1WqH7ceUZoNbpRXCzZQERHKsm9k/nUodSa5+TXKg94Ydjx5RZ8+q5FK1FcLNzD21S/cOZGxszMfHx7MOQyQ1E5N11my47bAe84KhGndcvLarRnLQx0N+tnIIJYn3KO/MbLu7j7W7XWcCIjk16Oyffh6f5oKuPCy4y+MMq7QpCfQgDz0XqY5BZ//0+vg8zOtPWx5mWGVNSaBLVa8bSvoG3eunm8cP2vBv/8MzC90hKvKGgEnpOCZgZsPuXu/7yc0WAN8BhokSztfc/WNm9mng14EDwI+A/+ruezs9V5ZjAlWoG8qR8nLmN2gczY9PquRzzPB8vvzfTufkFYsTeb4s5eVvHcKgYwLfBV5uZn/l7u/o4/XrwFp3nzSzIWCrmd0M3Apc6u7TZrYBuBS4uI/nT0UZ6oZl/k8eQp7O/AatnTc+Psmaf5nKJnkYn8jKXEngKDO7EHiVmf1m843ufm2nB3t0mjEZ/zgUf7m7/0PD3e4E3tx9yOkret0wTw1aEeR9T55eJdXwX3H+6kqXTcpqriTwHuDtwGKi8k0jBzomAQAzmwdsB04AvuDu25ru8i7gb9o8dh2wDmB0dHSulwqmyHXDsjVoaSj6mV/InTvLvH1CVXVMAu6+laiEM+7uV7e7n5md5e63tnmOg8BqM1sMXGdmL3P3++LHfRSYBr7S5rGbgE0QjQl08wuFUtT9Q4reoGUhjTO/UOW50DN8qlw2KauuZgd1SgCxDUR1/k7PsdfMbgfOBu6Ly0xvBF7vBVmxVsQPQNFLWVno58wvy2vYZr1nvxRbUlNEreVBsxFgKk4AC4EzgQ1mdjbRQPBr3f3nCcUgLRS5lJWlXs78kriGbb/luSrO7ZdkJZUE2vXklwHXxOMCNWCzu99oZj8kmjZ6q5kB3Onu70koFmlS1FJW1ro580viGra9lufU8EuSgi4Wc/ddwCktjp8Q8nWzkudpmEUsZRVBEtewnas8l8W1eaU6kkoCDyX0PIXVT523l6SR5wRTZYNew3au8px6/RJaV7uIxit//wfwaqLSz1bgf7n7/rDhHZLnXUT7WVHcS9LQPP98a7Ut8iAdADX8kqSkdhH9ErAP+LP457cBfwW8ZbDwyqHbksDsB//oo+Z1XUfWPP/8G/QatqAZPknTmXP3uk0C/97dT274+VtmtjNEQEXUTUmgsTdfnz5IrXb4hKp2dWTN8y+GQcZcsuz5l7Gx1Jlzb7pNAveY2RnufieAmZ0O3BEurGKZq87bqjfPwcPLcO3qyJrnX0556PmXsbHUmXPvuk0CpwPvNLOH459HgQfM7F6ivYBWBYmuQDqVBFr15ofnGW7GcJtrqjb20Mo8z7+MPdFW8tDoN+q2sSza30dnzr3rNgmcHTSKkmhXEmjVm7eacdP7Xs3TBw4e8QFr1UO74+K1hfowdqOMPdFW8jjQ201jWcS/j86ce1fr5k7u/mPgKeB5wJLZL3f/cXybdDBbLlowVOOY4fksGKqx8bxVnPCCYzh5xeIjel6zPbR99Wn2T82wfssugCPuW2Ttfs+Jyb4vX5ErKy+56dmvQVxx/uqEIjrcXI1lUf8+7T5rZfnchNDVmYCZfQL4baILwMwWsx1YGyas8ul2BklVTmfL9nsmuaCrUah69lzjWEX++2iFfG+6LQe9FfgFdz8QMpiyaa6ndjODpCqns2X6PZNIAA9d/gZ2PrKXC67axr769LPHQza8nRrLov99tEK+e90mgfuIrinwWMBYSqXfempVNnxL4/cMOagZYiuHLBredo1lVf4fSvcrhseA64mSwbNFQXc/N1xoh8vziuFmSVyTuGizMvoV6vcMOaiZRJ2/XSz9rD4OqSr/D8ssqRXD1xBdM+BeYGaO+1ZeEvXUqpzOhvg9201/PGnZc1vOxupGkj3/TnX+vNWzq/L/sMq6TQJPuPsVQSMpkaLXU4uuVRIGOOeKf2R4/ry2PezmXm9SDf8xw/N7qvOr4ZU0dZsEtpvZnwA3cHg56O4gURVc6CtTSWetkvBsae7Awagxbu6NN5ePGkt5vWqc1z9bGmykDkH29Hk7pNskMHtNgDMajmmKaAehrkwlc2tOwvWDM5g79YatOhp74y239ehRuwVdGmDNn0E/b2VLIF0NDOdBkQaGe5HEIHLaivIhaNy19Y1Xbj3iPR6kt9+omxW9c71nRXlPi27Qz1sRO2yJDAyb2QuATwEvcvdfM7OTgFd2cQF6mUMWi3IGaXCK9CForK0398YHTQC9buXQqc5fpPe06Ab5vJV1c7quto0A/jdwC/Ci+OfvAxeFCKhq0h5Evn7Ho6zZcBsXXLWNNRtu44Ydjx5xn4nJOjsf2XvEFgHtthL44U/3tbx/npy7+vjD4h5Eknv5FHV7hqIa5PM2m0AazSaQIut4JmBm8919Gljq7pvN7FIAd582s4OpRFhyadaMu+nJdOqV9jvrJitFuDZvkbdnKKJBPm9lnfU3VznoLuDlwNNmtoR43yAzOwP4t7mePL4s5XeA4fi1vubuHzOz5wN/A6wkuj7xW939yT5/h8JLa274XA3OXEmin1k3WUlqK4fQytqw5Fm/n7eyDvLPlQRmL3/1QaLpob9gZncAI8Cbu3j+OrDW3SfNbAjYamY3A78JfNPdLzezS4BLgIv7+g0ylORgXhpzw+dqcOZKEr3OuklbEXr+zcrasORdv5+3vC3mS8JcSWDEzD4Yf38d8HdEiaEOnAns6vRgj6YeTcY/DsVfDrwJeF18/BrgdgqWBIo4mDdXg9NNr7TxQzA766bxKmlZ9WLzuGd/t8rYsJRZ2RbzzZUE5gGLOHRGMOs53b6Amc0DtgMnAF9w921m9gJ33wPg7nvM7Lg2j10HrAMYHR3t9iWDK/IsgU4NTre90k6zbtLuxQ7S+OfpouxFbVg0tbX45koCe9z944O8gLsfBFab2WLgOjN7WQ+P3QRsgmidwCBxJKnog3mdGpxee6Vp92LzdpnGKivi2bAcqdsxgYG5+14zu53oUpU/NbNl8VnAMgq2RXUeBvNC9sB67ZWm1YstS6+/DIp8NiyHmysJvH6QJzezEWAqTgALicYRNhANMl8IXB7/e/0gr5O2rAfzqtQDU8OfT0U/G5ZDOiYBd//ZgM+/DLgmHheoAZvd/UYz+y6w2cx+B3gYeMuAr5O6rAbzyt4DU7mnGPJwNizJ6HYDub64+y4ObT7XeHyCAc8y8iCLwbwy98CKMrdfsj8bluQETQKSvLL1wIo4t18imtpaDkoCBVP0Hpga/XIp6tRWOURJoICK2gNTuUckf5QECqooPTD1/EXyTUlAEpdUw5/3i+tIb7S6OJ+UBCRRgyaABUO1Qo51SGdVWttSNEoCMrAk5/art1g+ZV/bUnRKAjKQpFf0FmWsQ7pX5rUtZaAkID3TVg7Si7KtbSkbJQGZU1LlnivOX53I80ixFH1tS9kpCUhHSU7xVB24uoq6tqUKlATkCElcpWvnI3u54Kpt7KtPP3tcdeBq03hPPikJSJAFXaoDixSDkkDFhdrKQXVgkWJQEiiJXubXp7WVg+rAIvmnJFAC3azGzOpiLaoDi+SbkkDBdbMaM4mBXhEpJyWBgmu3GvPUT34jkedXAhApNyWBgms1C6dxWmavrjh/da439tLeQiLJqoV8cjNbYWbfMrMHzOx+M3t/fHy1md1pZjvMbNzMTgsZR5ktWTTM/qmZue/YpfVbdjExWU/s+ZJ0/Y5HWbPhNi64ahtrNtzGDTseHej5Jibr7Hxkb25/X5E0hD4TmAY+5O53m9kxwHYzuxXYCFzm7jeb2Tnxz68LHEupJDXQe8zw/FQXdPXbk096J0ptbSwSCZoE3H0PsCf+fp+ZPQAcDzjw3PhuzwN+EjKOsklqoHdiss6aDbcddlvIBV2DNLxJ7kSprY1FDkltTMDMVgKnANuAi4BbzOwzRCWpV7V5zDpgHcDo6GgqceZViBk+aS7oGrThTXIFsrY2FjkklSRgZouALcBF7v6UmX0S+IC7bzGztwJXA2c2P87dNwGbAMbGxjyNWPMiTwu6khiMHbThTTJhaUsLkUOCJwEzGyJKAF9x92vjwxcC74+//ypwVeg4iiTtef2dFnQlVTtPouFNagWytrQQOSRoEjAzI+rlP+Dun2246SfAa4HbgbXAD0LGUQR5XNCVZO08qYY3qRXI2tJCJBL6TGAN8A7gXjPbER/7A+DdwOfNbD6wn7juXzVZbeXQraRr53lreLWlhUj42UFbAWtz86khXzvv8tjzbxaidq6GVyRftGI4RXnv+TdT7Vyk/JQEAipao99K3ko4IpIsJYFAilDu6ZZKOCLlpSSQoDL0/EWkWpQEElKmnv+gtNOnSHEoCQxADf+RtDGbSLEoCfQgra0cikobs4kUj5JAl9Trn5s2ZhMpHiWBDtTw90Ybs4kUj5JAE83w6Z8Wl4kUj5JAA/X8B6fFZSLFUvkkoJ5/8rS4TKQ4KpcEqtLoa66+iHSjUkmgKuUezdUXkW6VPglUpec/S3P1RaQXpU4CVen5N9JcfRHpRWmTQL8JoIgNfyPN1ReRXtSyDiBPip4A4NBc/QVDNY4Zns+CoZrm6otIW6U9E+hGGRr9VjRXX0S6FTQJmNkK4EvAC4EZYJO7fz6+7feA9wHTwE3uvj7J137o8je0LAmVteFvprn6ItKN0OWgaeBD7v5S4AzgvWZ2kpn9R+BNwCp3/yXgMyFevLnBr0oCqKqJyTo7H9nLxGQ961BECiPomYC77wH2xN/vM7MHgOOBdwOXu3s9vu2xUDGo4a8GrY0Q6U9qA8NmthI4BdgGnAi8xsy2mdm3zewVacUh5dO4NmJffZr9UzOs37JLZwQiXUglCZjZImALcJG7P0V0BnIsUYnoI8BmM7MWj1tnZuNmNv7444+nEaoU0OzaiEazayNEpLPgScDMhogSwFfc/dr48G7gWo/cRTRovLT5se6+yd3H3H1sZGQkdKhSUFobIdK/oEkg7t1fDTzg7p9tuOnrwNr4PicCRwFPhIxFyktrI0T6F3qdwBrgHcC9ZrYjPvYHwBeBL5rZfcAB4EJ398CxSIlpbYRIf0LPDtoKHFHrj10Q8rWlerQ2QqR32jZCRKTClARERCpMSUBEpMKUBEREKkxJQESkwpQEREQqTElARKTClARERCpMSUBEpMKUBEREKkxJQESkwpQEREQqTElARKTClARERCpMSUBEpMKUBEREKkxJQESkwpQEREQqTElARKTClASk0CYm6+x8ZC8Tk/WsQxEppKAXmjezFcCXgBcCM8Amd/98w+0fBj4NjLj7EyFjkfK5fsejXLxlF0O1GlMzM2w8bxXnrj4+67BECiX0mcA08CF3fylwBvBeMzsJnk0QZwEPB45BSmhiss7FW3axf2qGffVp9k/NsH7LLp0RiPQoaBJw9z3ufnf8/T7gAWC2q/Y5YD3gIWOQctr95DMM1Q7/7ztUq7H7yWcyikikmFIbEzCzlcApwDYzOxd41N13zvGYdWY2bmbjjz/+eApRSlEsP3YhUzMzhx2bmplh+bELM4pIpJhSSQJmtgjYAlxEVCL6KPA/53qcu29y9zF3HxsZGQkcpRTJkkXDbDxvFQuGahwzPJ8FQzU2nreKJYuGsw5NpFCCDgwDmNkQUQL4irtfa2a/DLwY2GlmAMuBu83sNHf/19DxSHmcu/p41pywlN1PPsPyYxcqAYj0IfTsIAOuBh5w988CuPu9wHEN93kIGNPsIOnHkkXDavxFBhC6HLQGeAew1sx2xF/nBH5NERHpUtAzAXffCtgc91kZMgYREWlPK4ZFRCpMSUBEpMKUBEREKszci7Fg18weB37c58OXAnmdfZTX2PIaF+Q3trzGBfmNLa9xQX5j6zWuf+fubRdaFSYJDMLMxt19LOs4WslrbHmNC/IbW17jgvzGlte4IL+xJR2XykEiIhWmJCAiUmFVSQKbsg6gg7zGlte4IL+x5TUuyG9seY0L8htbonFVYkxARERaq8qZgIiItKAkICJSYYVMAmb2RTN7zMzuazh2spl918zuNbO/NbPnNj1m1Mwm4+sazx47Nb7/D83sinjX09RiM7OVZvZMw+Z6f56X2OLbVsW33R/fviBEbD2+Z29veL92mNmMma0OEVcfsQ2Z2TXx8QfM7NKGx2T5nh1lZn8ZH99pZq8LGNcKM/tW/Pvfb2bvj48/38xuNbMfxP8e2/CYS+PXf9DM/lNeYjOzJfH9J83syqbnSiy2PuI6y8y2x6+/3czWDhSXuxfuC/gV4OXAfQ3H/hl4bfz9u4BPND1mC/BV4MMNx+4CXkm0yd3NwK+lGRuwsvF+Tc+TdWzzgV3AyfHPS4B5IWLr5+8ZH/9l4P/l6D37LeCv4++fAzwErMz6PQPeC/xl/P1xwHagFiiuZcDL4++PAb4PnARsBC6Jj18CbIi/PwnYCQwTXWfkRwH/n/Ua29HAq4H3AFc2PVdisfUR1ynAi+LvX0Z0lca+4xroA5LlF00NKPAUhwa6VwD/0nDbfwY+DfwRcRKI3/jvNdznbcBfpBlb8/2a/lNkHds5wJfTiq2Xv2fDfT4F/HGO3rO3AX9LlECXxB/m52f9ngFfAC5ouN83gdNCvmcNz3k9cBbwILCs4W/1YPz9pcClDfe/hagRyzy2hvv9Ng1JIHRs3cYVHzdggiiJ9hVXIctBbdwHnBt//xaiDwFmdjRwMXBZ0/2PB3Y3/Lw7PpZabLEXm9k9ZvZtM3tNjmI7EXAzu8XM7jaz9SnH1uk9m/VfgP+bclydYvsa8DSwB3gY+Iy7/yzF2NrFtRN4k5nNN7MXA6fGtwWNyxquKw68wN33AMT/zl5Y6njgkRYx5CG2doLF1kdc5wH3uHu937jKlATeBbzXzLYTnVIdiI9fBnzO3Seb7t+qVhZqvmy72PYAo+5+CvBB4P/Eddw8xDaf6FT47fG/v2Fmr08xtnZxAWBmpwM/d/fZmnge3rPTgIPAi4hKGx8ys5ekGFu7uL5I1CCMA38K/BPRtb6DxWUN1xV396c63bVNDHmIre1TtDg2cGy9xmVmvwRsAP77IHEFv8ZwWtz9e8CvApjZicAb4ptOB95sZhuBxcCMme0nerOXNzzFcuAnacYWZ+96/P12M/sRUQ98d9axxTF82+PLfprZ3xHVoL+cRmwd4pp1PofOAmbjzfo9+y3g7919CnjMzO4AxoB/TCO2Dv/PpoEPzN7PzP4J+AHwZIi4rOm64vHhn5rZMnffY2bLgMfi47s5/CxvNoYgf88eY2sn8dh6jcvMlgPXAe909x8NEldpzgTM7Lj43xrwh8CfA7j7a9x9pUdXMPtT4FPufmV8erXPzM6IR9DfSVSLSy02Mxsxs3nx9y8BfpFooJRq7+cAAAK1SURBVDPz2Ihqs6vM7DlmNh94LVGNOZXYOsQ1e+wtwF/PHsvJe/Yw0aVULS5DnkFUo830PYv/hkfH358FTLt7kL9l/DyHXVc8dgNwYfz9hQ2vcwNwvpkNx6WqXwTuyklsLSUdW69xmdli4CaisZQ7Bo4ryYGWtL6IeoB7gCmi7Pc7wPuJBuK+D1xOPEDW9Lg/4vDZQWNEddQfAVe2ekzI2IjqefcT1WzvBn49L7HF978gju8+YGOo2PqI63XAnS2eJ+u/5yKiGWj3A/8CfCQP7xnRAPKDwAPAN4i2Fg4V16uJShC7gB3x1zlEA+XfJDoD+Sbw/IbHfDR+/QdpmM2Sk9geAn4GTMbv80lJx9ZrXEQJ/umG++4Ajus3Lm0bISJSYaUpB4mISO+UBEREKkxJQESkwpQEREQqTElARKTClARERCpMSUAkJbMLA0XyRElApAUz+4TF+7rHP/+xmf2+mX3EzP7ZzHaZ2WUNt3/dor3d7zezdQ3HJ83s42a2jWh3TJFcURIQae1q4iX78TYM5wM/JdrW4DRgNXCqmf1KfP93ufupRCs2f9/MlsTHjyba7vl0d9+a5i8g0o3SbCAnkiR3f8jMJszsFOAFwD3AK4g2aLsnvtsioqTwHaKG/zfi4yvi4xNEu4puSTN2kV4oCYi0dxXRBUVeSLQd8+uBP3H3v2i8k0WXazwTeKW7/9zMbgcWxDfvd/eDaQUs0iuVg0Tauw44m+gM4Jb4613xvu+Y2fHxzp3PA56ME8B/INo9VKQQdCYg0oa7HzCzbwF74978P5jZS4HvRjv1Mkm00+rfA+8xs11EO2HemVXMIr3SLqIibcQDwncDb3H3H2Qdj0gIKgeJtGBmJwE/BL6pBCBlpjMBEZEK05mAiEiFKQmIiFSYkoCISIUpCYiIVJiSgIhIhf1/O9MxxAnTNiEAAAAASUVORK5CYII=",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "%matplotlib inline\n",
    "result.plot(kind='scatter', x='year', y='Temp_f');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Modify the code blocks above to find your own insights in the NOAA dataset!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lab: Exploring Binary Logistic Regression in Big Query ML"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The next algorithm we will look at is Binary Logistic Regression which is used to predict the odds of an event outcome based on a set of independent variables.  For this example we will look at Census data as it pertains to income bracket vs several other independent variables.  For this example we will split the dataset into 4 chunks, where we train the model on 3 of them and evaluate on the last.  We use the `FARM_FINGERPRINT` function to create a hash on each row, limited to one of four chunks."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1) First train the model to target based on `income_bracket` -- Note the namespace of where we create the model is the same as above.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "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": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE MODEL\n",
    "  `bigqueryml_ca.census_log_reg`\n",
    "OPTIONS\n",
    "  ( model_type=\"logistic_reg\",\n",
    "    input_label_cols=[\"income_bracket\"] ) AS\n",
    "SELECT\n",
    "  * EXCEPT(DATASET_ID)\n",
    "FROM (\n",
    "  SELECT\n",
    "    ABS(MOD(FARM_FINGERPRINT(CONCAT( CAST(age AS STRING), CAST(education AS STRING), CAST(marital_status AS STRING), CAST(race AS STRING), CAST(sex AS STRING), CAST(income_bracket AS STRING), CAST(occupation AS STRING), CAST(capital_gain AS STRING), CAST(functional_weight AS STRING))),4)) AS DATASET_ID,\n",
    "    education, marital_status, race, sex, income_bracket\n",
    "  FROM\n",
    "    `bigquery-public-data.ml_datasets.census_adult_income` )\n",
    "WHERE\n",
    "  DATASET_ID IN (0,1,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2) Once again lets evaluate how our model performed. This time we have selected a `DATASET_ID=3` which is our evaluation dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>precision</th>\n",
       "      <th>recall</th>\n",
       "      <th>accuracy</th>\n",
       "      <th>f1_score</th>\n",
       "      <th>log_loss</th>\n",
       "      <th>roc_auc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.729508</td>\n",
       "      <td>0.418495</td>\n",
       "      <td>0.822731</td>\n",
       "      <td>0.531873</td>\n",
       "      <td>0.383854</td>\n",
       "      <td>0.846077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   precision    recall  accuracy  f1_score  log_loss   roc_auc\n",
       "0   0.729508  0.418495  0.822731  0.531873  0.383854  0.846077"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM\n",
    "ML.EVALUATE(\n",
    "  MODEL `bigqueryml_ca.census_log_reg`,\n",
    "  # Table to evaluate against\n",
    "  (\n",
    "    SELECT * EXCEPT(DATASET_ID)\n",
    "    FROM (\n",
    "      SELECT\n",
    "        ABS(MOD(FARM_FINGERPRINT(CONCAT( CAST(age AS STRING), CAST(education AS STRING), CAST(marital_status AS STRING), CAST(race AS STRING), CAST(sex AS STRING), CAST(income_bracket AS STRING), CAST(occupation AS STRING), CAST(capital_gain AS STRING), CAST(functional_weight AS STRING))),4)) AS DATASET_ID,\n",
    "        education, marital_status, race, sex, income_bracket\n",
    "       FROM\n",
    "        `bigquery-public-data.ml_datasets.census_adult_income` )\n",
    "    WHERE DATASET_ID = 3\n",
    "))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3) Predict based on the evaluation set.  Below we will look at what results were predicted to have an income > 50K and less than or equal to 50K."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>predicted_income_bracket</th>\n",
       "      <th>predicted_income_bracket_probs</th>\n",
       "      <th>education</th>\n",
       "      <th>marital_status</th>\n",
       "      <th>race</th>\n",
       "      <th>sex</th>\n",
       "      <th>income_bracket</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.582074637204209}...</td>\n",
       "      <td>Masters</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Asian-Pac-Islander</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6316362206121827...</td>\n",
       "      <td>Masters</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6316362206121827...</td>\n",
       "      <td>Masters</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6316362206121827...</td>\n",
       "      <td>Masters</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6316362206121827...</td>\n",
       "      <td>Masters</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1093</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6382412417126396...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1094</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.6382412417126396...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1095</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.5889900661516415...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Asian-Pac-Islander</td>\n",
       "      <td>Male</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1096</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.5468895953293567...</td>\n",
       "      <td>Doctorate</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1097</th>\n",
       "      <td>&gt;50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.5354546192052568...</td>\n",
       "      <td>Prof-school</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Male</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1098 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     predicted_income_bracket  \\\n",
       "0                        >50K   \n",
       "1                        >50K   \n",
       "2                        >50K   \n",
       "3                        >50K   \n",
       "4                        >50K   \n",
       "...                       ...   \n",
       "1093                     >50K   \n",
       "1094                     >50K   \n",
       "1095                     >50K   \n",
       "1096                     >50K   \n",
       "1097                     >50K   \n",
       "\n",
       "                         predicted_income_bracket_probs     education  \\\n",
       "0     [{'label': ' >50K', 'prob': 0.582074637204209}...       Masters   \n",
       "1     [{'label': ' >50K', 'prob': 0.6316362206121827...       Masters   \n",
       "2     [{'label': ' >50K', 'prob': 0.6316362206121827...       Masters   \n",
       "3     [{'label': ' >50K', 'prob': 0.6316362206121827...       Masters   \n",
       "4     [{'label': ' >50K', 'prob': 0.6316362206121827...       Masters   \n",
       "...                                                 ...           ...   \n",
       "1093  [{'label': ' >50K', 'prob': 0.6382412417126396...     Bachelors   \n",
       "1094  [{'label': ' >50K', 'prob': 0.6382412417126396...     Bachelors   \n",
       "1095  [{'label': ' >50K', 'prob': 0.5889900661516415...     Bachelors   \n",
       "1096  [{'label': ' >50K', 'prob': 0.5468895953293567...     Doctorate   \n",
       "1097  [{'label': ' >50K', 'prob': 0.5354546192052568...   Prof-school   \n",
       "\n",
       "           marital_status                 race      sex income_bracket  \n",
       "0      Married-civ-spouse   Asian-Pac-Islander   Female          <=50K  \n",
       "1      Married-civ-spouse                White   Female           >50K  \n",
       "2      Married-civ-spouse                White   Female           >50K  \n",
       "3      Married-civ-spouse                White   Female           >50K  \n",
       "4      Married-civ-spouse                White   Female           >50K  \n",
       "...                   ...                  ...      ...            ...  \n",
       "1093   Married-civ-spouse                White     Male          <=50K  \n",
       "1094   Married-civ-spouse                White     Male          <=50K  \n",
       "1095   Married-civ-spouse   Asian-Pac-Islander     Male          <=50K  \n",
       "1096             Divorced                White     Male           >50K  \n",
       "1097             Divorced                White     Male          <=50K  \n",
       "\n",
       "[1098 rows x 7 columns]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "## > 50K result\n",
    "\n",
    "SELECT * FROM\n",
    "ML.PREDICT(\n",
    "  MODEL `bigqueryml_ca.census_log_reg`,\n",
    "  # Table to evaluate against\n",
    "  (\n",
    "    SELECT * EXCEPT(DATASET_ID)\n",
    "    FROM (\n",
    "      SELECT\n",
    "        ABS(MOD(FARM_FINGERPRINT(CONCAT( CAST(age AS STRING), CAST(education AS STRING), CAST(marital_status AS STRING), CAST(race AS STRING), CAST(sex AS STRING), CAST(income_bracket AS STRING), CAST(occupation AS STRING), CAST(capital_gain AS STRING), CAST(functional_weight AS STRING))),4)) AS DATASET_ID,\n",
    "        education, marital_status, race, sex, income_bracket\n",
    "       FROM\n",
    "        `bigquery-public-data.ml_datasets.census_adult_income` )\n",
    "    WHERE DATASET_ID = 3\n",
    "))\n",
    "WHERE predicted_income_bracket = ' >50K'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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>predicted_income_bracket</th>\n",
       "      <th>predicted_income_bracket_probs</th>\n",
       "      <th>education</th>\n",
       "      <th>marital_status</th>\n",
       "      <th>race</th>\n",
       "      <th>sex</th>\n",
       "      <th>income_bracket</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1078972789223815...</td>\n",
       "      <td>9th</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.0894516736279238...</td>\n",
       "      <td>9th</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Asian-Pac-Islander</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1078972789223815...</td>\n",
       "      <td>9th</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.0795958775430775...</td>\n",
       "      <td>9th</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Black</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.0795958775430775...</td>\n",
       "      <td>9th</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Black</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6851</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1698556025871480...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6852</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1698556025871480...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6853</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1698556025871480...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6854</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1698556025871480...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&gt;50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6855</th>\n",
       "      <td>&lt;=50K</td>\n",
       "      <td>[{'label': ' &gt;50K', 'prob': 0.1698556025871480...</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>White</td>\n",
       "      <td>Female</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6856 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     predicted_income_bracket  \\\n",
       "0                       <=50K   \n",
       "1                       <=50K   \n",
       "2                       <=50K   \n",
       "3                       <=50K   \n",
       "4                       <=50K   \n",
       "...                       ...   \n",
       "6851                    <=50K   \n",
       "6852                    <=50K   \n",
       "6853                    <=50K   \n",
       "6854                    <=50K   \n",
       "6855                    <=50K   \n",
       "\n",
       "                         predicted_income_bracket_probs   education  \\\n",
       "0     [{'label': ' >50K', 'prob': 0.1078972789223815...         9th   \n",
       "1     [{'label': ' >50K', 'prob': 0.0894516736279238...         9th   \n",
       "2     [{'label': ' >50K', 'prob': 0.1078972789223815...         9th   \n",
       "3     [{'label': ' >50K', 'prob': 0.0795958775430775...         9th   \n",
       "4     [{'label': ' >50K', 'prob': 0.0795958775430775...         9th   \n",
       "...                                                 ...         ...   \n",
       "6851  [{'label': ' >50K', 'prob': 0.1698556025871480...   Bachelors   \n",
       "6852  [{'label': ' >50K', 'prob': 0.1698556025871480...   Bachelors   \n",
       "6853  [{'label': ' >50K', 'prob': 0.1698556025871480...   Bachelors   \n",
       "6854  [{'label': ' >50K', 'prob': 0.1698556025871480...   Bachelors   \n",
       "6855  [{'label': ' >50K', 'prob': 0.1698556025871480...   Bachelors   \n",
       "\n",
       "           marital_status                 race      sex income_bracket  \n",
       "0      Married-civ-spouse                White   Female          <=50K  \n",
       "1      Married-civ-spouse   Asian-Pac-Islander   Female          <=50K  \n",
       "2      Married-civ-spouse                White   Female          <=50K  \n",
       "3      Married-civ-spouse                Black   Female          <=50K  \n",
       "4      Married-civ-spouse                Black   Female          <=50K  \n",
       "...                   ...                  ...      ...            ...  \n",
       "6851             Divorced                White   Female          <=50K  \n",
       "6852             Divorced                White   Female          <=50K  \n",
       "6853             Divorced                White   Female           >50K  \n",
       "6854             Divorced                White   Female           >50K  \n",
       "6855             Divorced                White   Female          <=50K  \n",
       "\n",
       "[6856 rows x 7 columns]"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "\n",
    "## < 50K result\n",
    "\n",
    "SELECT * FROM\n",
    "ML.PREDICT(\n",
    "  MODEL `bigqueryml_ca.census_log_reg`,\n",
    "  # Table to evaluate against\n",
    "  (\n",
    "    SELECT * EXCEPT(DATASET_ID)\n",
    "    FROM (\n",
    "      SELECT\n",
    "        ABS(MOD(FARM_FINGERPRINT(CONCAT( CAST(age AS STRING), CAST(education AS STRING), CAST(marital_status AS STRING), CAST(race AS STRING), CAST(sex AS STRING), CAST(income_bracket AS STRING), CAST(occupation AS STRING), CAST(capital_gain AS STRING), CAST(functional_weight AS STRING))),4)) AS DATASET_ID,\n",
    "        education, marital_status, race, sex, income_bracket\n",
    "       FROM\n",
    "        `bigquery-public-data.ml_datasets.census_adult_income` )\n",
    "    WHERE DATASET_ID = 3\n",
    "))\n",
    "WHERE predicted_income_bracket = ' <=50K'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: The model is not 100% accurate, but if we were to supply `education, marital_status, race, sex` we should be able to predict the income bracket correctly 82% of the time!\n",
    "\n",
    "Create your own dataset to predict against, or modify the parameters of the model to increase accuracy to go further."
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "common-cpu.m50",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:m50"
  },
  "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}