data-generator.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Predictive Maintenance Data",
      "version": "0.3.2",
      "provenance": [],
      "collapsed_sections": [],
      "toc_visible": true,
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/davidcavazos/predictive-maintenance/blob/master/data-generator.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "metadata": {
        "id": "DGU8LFQcjc0M",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Predictive Maintenance Data"
      ]
    },
    {
      "metadata": {
        "id": "2_D0oJxNaYzk",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "This notebook will guide you through the process of generating statistically significant random data. This data will then be used to train a BigQuery Machine Learning model to predict a target value."
      ]
    },
    {
      "metadata": {
        "id": "Q4PFXufajhNA",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Setup"
      ]
    },
    {
      "metadata": {
        "id": "z9fidNixagEw",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Before you begin, please fill up your Google Cloud project ID and your Cloud Storage bucket name without the `gs://`."
      ]
    },
    {
      "metadata": {
        "id": "028LmRwPtbqe",
        "colab_type": "code",
        "cellView": "form",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "project = \"\" #@param {type:\"string\"}\n",
        "bucket = \"\" #@param {type:\"string\"}\n",
        "dataset = \"water_utilities\" #@param {type:\"string\"}\n",
        "table_training = \"pipes_training\" #@param {type:\"string\"}\n",
        "table = \"pipes\" #@param {type:\"string\"}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "GhTugQR4jXoO",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "import logging\n",
        "from pprint import pprint\n",
        "\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "\n",
        "def run(command):\n",
        "  print(f\">> {command}\")\n",
        "  !{command}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "1BjoSDLOWKTJ",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Configure bigquery to your project.\n",
        "run(f\"gcloud config set project {project}\")\n",
        "bq = f\"bq --project {project}\""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "l-FplU9a7ayC",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "# Create a dataset.\n",
        "run(f\"{bq} mk --dataset {dataset}\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "2uvgYcCbsXd1",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Data Generator"
      ]
    },
    {
      "metadata": {
        "id": "kkmD0pPPWSA9",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "There are different types of fields we can have. For this simple example, we'll only deal with categorical, numerical and dates.\n",
        "\n",
        "We'll first create a base class `Field` that other fields will inherit from."
      ]
    },
    {
      "metadata": {
        "id": "h_PtvRWmsYmi",
        "colab_type": "code",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "class Field:\n",
        "  def __init__(self, name):\n",
        "    self.name = name\n",
        "\n",
        "  def generate_random(self, seed=None):\n",
        "    raise NotImplementedError('Field.generate_random(seed)')\n",
        "\n",
        "  def estimate_label(self, value):\n",
        "    raise NotImplementedError('Field.estimate_label(value)')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "n21KCuGLWtEg",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "The `generate_random()` method will allow us to create a random instance of this field. That is, a random element from the categories, or a random number within a range, or a random date within a range.\n",
        "\n",
        "The `estimate_label()` method will allow us to *evaluate* a value and get a *score* from `0` to `1`. This value will help us calculate the value we want the model to learn to predict, called *label*. They don't have to be strictly between `0` and `1`, but as long as they are mostly smallish numbers.\n",
        "\n",
        "We will use the scores from all the fields in a pipe to calculate the *label*, which is the value we want to predict. In this case, our label is the number of years between repairs."
      ]
    },
    {
      "metadata": {
        "id": "xJh5WlZTSV_o",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## CategoricalField"
      ]
    },
    {
      "metadata": {
        "id": "DPj9KfU8Whx_",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "For categorical fields, we have a finite number of options.\n",
        "\n",
        "To estimate the label, we will assume the values have a linear relationship. That is, the first value will be `0`, the next ones will increment by fixed steps until the last one which will be `1`.\n",
        "\n",
        "You could also make them follow a normal distrbution to get values from `0` to `1`, making different values have different weights."
      ]
    },
    {
      "metadata": {
        "id": "95An5_sFxHcP",
        "colab_type": "code",
        "outputId": "93837b30-4637-4f39-dbd9-f7c0eca02c8d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 153
        }
      },
      "cell_type": "code",
      "source": [
        "class CategoricalField(Field):\n",
        "  def __init__(self, name, categories):\n",
        "    super().__init__(name)\n",
        "    self.categories = categories\n",
        "\n",
        "  def generate_random(self):\n",
        "    # We only need to make a random choice from all possible categories.\n",
        "    return np.random.choice(self.categories)\n",
        "\n",
        "  def estimate_label(self, value):\n",
        "    # We will assume the last values have more weight than the last,\n",
        "    # so the first value will give 0 and the last value will give 1.\n",
        "    try:\n",
        "      return self.categories.index(value) / (len(self.categories) - 1)\n",
        "    except Exception as e:\n",
        "      logging.error(f\"{e}: value={value}, valid={self.categories}\")\n",
        "      return 0.5\n",
        "\n",
        "np.random.seed(42)\n",
        "field = CategoricalField('Test field name', ['A', 'B', 'C', 'D', 'E'])\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in field.categories:\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:'X' is not in list: value=X, valid=['A', 'B', 'C', 'D', 'E']\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: D\n",
            "  A label: 0.0\n",
            "  B label: 0.25\n",
            "  C label: 0.5\n",
            "  D label: 0.75\n",
            "  E label: 1.0\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "dMQ0Nksyl4nM",
        "colab_type": "code",
        "outputId": "73400540-73f6-46f5-bbef-b284db8398af",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 173
        }
      },
      "cell_type": "code",
      "source": [
        "materials = [\n",
        "    'pvc',\n",
        "    'ceramic',\n",
        "    'copper',\n",
        "    'galvanized iron',\n",
        "    'cast iron',\n",
        "]\n",
        "\n",
        "class Material(CategoricalField):\n",
        "  def __init__(self):\n",
        "    super().__init__('material', materials)\n",
        "\n",
        "  def estimate_label(self, value):\n",
        "    return 1 - super().estimate_label(value)\n",
        "\n",
        "np.random.seed(42)\n",
        "field = Material()\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in field.categories:\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:'X' is not in list: value=X, valid=['pvc', 'ceramic', 'copper', 'galvanized iron', 'cast iron']\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: galvanized iron\n",
            "  pvc label: 1.0\n",
            "  ceramic label: 0.75\n",
            "  copper label: 0.5\n",
            "  galvanized iron label: 0.25\n",
            "  cast iron label: 0.0\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "l--Z3gL3mCy-",
        "colab_type": "code",
        "outputId": "ec34f07d-86f3-4054-be67-888f6caf3662",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 394
        }
      },
      "cell_type": "code",
      "source": [
        "# Diameter in inches\n",
        "diameters = [\n",
        "  1/8, 1/4, 3/8, 1/2, 3/4,\n",
        "  1, 1 + 1/4, 1 + 1/2,\n",
        "  2, 2 + 1/2,\n",
        "  3, 4, 5, 6, 8, 10, 12,\n",
        "]\n",
        "\n",
        "class Diameter(CategoricalField):\n",
        "  def __init__(self):\n",
        "    super().__init__('diameter_in_inches', diameters)\n",
        "\n",
        "  def estimate_label(self, value):\n",
        "    # If the diameter is not one of the predefined categories, we would still\n",
        "    # like to get a weight that is proportional to where it would have fit\n",
        "    # within the rest of the numbers.\n",
        "    if value in self.categories:\n",
        "      return super().estimate_label(value)\n",
        "    try:\n",
        "      return np.searchsorted(self.categories, value) / len(self.categories)\n",
        "    except Exception as e:\n",
        "      logging.error(f\"{e}: value={value}, valid={self.categories}\")\n",
        "      return 0.5\n",
        "\n",
        "\n",
        "np.random.seed(42)\n",
        "field = Diameter()\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in field.categories:\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  9 label: {field.estimate_label(9)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:Cannot cast array data from dtype('float64') to dtype('<U32') according to the rule 'safe': value=X, valid=[0.125, 0.25, 0.375, 0.5, 0.75, 1, 1.25, 1.5, 2, 2.5, 3, 4, 5, 6, 8, 10, 12]\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: 1.25\n",
            "  0.125 label: 0.0\n",
            "  0.25 label: 0.0625\n",
            "  0.375 label: 0.125\n",
            "  0.5 label: 0.1875\n",
            "  0.75 label: 0.25\n",
            "  1 label: 0.3125\n",
            "  1.25 label: 0.375\n",
            "  1.5 label: 0.4375\n",
            "  2 label: 0.5\n",
            "  2.5 label: 0.5625\n",
            "  3 label: 0.625\n",
            "  4 label: 0.6875\n",
            "  5 label: 0.75\n",
            "  6 label: 0.8125\n",
            "  8 label: 0.875\n",
            "  10 label: 0.9375\n",
            "  12 label: 1.0\n",
            "  9 label: 0.8823529411764706\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "0MePjHa3SYl1",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## NumericalField"
      ]
    },
    {
      "metadata": {
        "id": "x--i-3Mpct09",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "For numbers, we'll also just use a linear uniform distribution, but you could also use any kind of distribution or function that best fits your data."
      ]
    },
    {
      "metadata": {
        "id": "q7Qf6vEqzHeK",
        "colab_type": "code",
        "outputId": "752aacfe-ee39-4e69-eaaa-36ddaecb296e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 292
        }
      },
      "cell_type": "code",
      "source": [
        "class NumericalField(Field):\n",
        "  def __init__(self, name, min_value, max_value):\n",
        "    super().__init__(name)\n",
        "    self.min_value = min_value\n",
        "    self.max_value = max_value\n",
        "\n",
        "  def generate_random(self):\n",
        "    # We'll just get a uniform distribution between two values.\n",
        "    return np.random.uniform(self.min_value, self.max_value)\n",
        "\n",
        "  def estimate_label(self, value):\n",
        "    # The first value will be 0 and the last will be 1.\n",
        "    try:\n",
        "      return (value - self.min_value) / (self.max_value - self.min_value)\n",
        "    except Exception as e:\n",
        "      logging.error(f\"{e}: value={value}, range=({self.min_value}, {self.max_value})\")\n",
        "      return 0.5\n",
        "\n",
        "np.random.seed(42)\n",
        "field = NumericalField('Test field name', 5, 10)\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in np.linspace(field.min_value, field.max_value, 10):\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  0.0 label: {field.estimate_label(0.0)}\")\n",
        "print(f\"  15.0 label: {field.estimate_label(15.0)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:unsupported operand type(s) for -: 'str' and 'int': value=X, range=(5, 10)\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: 6.872700594236813\n",
            "  5.0 label: 0.0\n",
            "  5.555555555555555 label: 0.11111111111111108\n",
            "  6.111111111111111 label: 0.22222222222222215\n",
            "  6.666666666666667 label: 0.33333333333333337\n",
            "  7.222222222222222 label: 0.4444444444444445\n",
            "  7.777777777777778 label: 0.5555555555555556\n",
            "  8.333333333333334 label: 0.6666666666666667\n",
            "  8.88888888888889 label: 0.7777777777777779\n",
            "  9.444444444444445 label: 0.888888888888889\n",
            "  10.0 label: 1.0\n",
            "  0.0 label: -1.0\n",
            "  15.0 label: 2.0\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "eV6XdBSKpjtX",
        "colab_type": "code",
        "outputId": "1ea12430-0113-43eb-a520-e0b4935311a7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 275
        }
      },
      "cell_type": "code",
      "source": [
        "# Length in miles\n",
        "length_min = 0.1\n",
        "length_max = 10\n",
        "\n",
        "class Length(NumericalField):\n",
        "  def __init__(self):\n",
        "    super().__init__('length_in_miles', length_min, length_max)\n",
        "\n",
        "np.random.seed(42)\n",
        "field = Length()\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in np.linspace(field.min_value, field.max_value, 10):\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  15.0 label: {field.estimate_label(15.0)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:unsupported operand type(s) for -: 'str' and 'float': value=X, range=(0.1, 10)\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: 3.807947176588889\n",
            "  0.1 label: 0.0\n",
            "  1.2000000000000002 label: 0.11111111111111112\n",
            "  2.3000000000000003 label: 0.22222222222222224\n",
            "  3.4000000000000004 label: 0.33333333333333337\n",
            "  4.5 label: 0.4444444444444445\n",
            "  5.6 label: 0.5555555555555556\n",
            "  6.7 label: 0.6666666666666667\n",
            "  7.800000000000001 label: 0.7777777777777779\n",
            "  8.9 label: 0.888888888888889\n",
            "  10.0 label: 1.0\n",
            "  15.0 label: 1.505050505050505\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "BeAJWRxtSaxv",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## DateField"
      ]
    },
    {
      "metadata": {
        "id": "XbsKfBehYpBu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Dates are a bit more complicated since they have years, months, days, hours, minutes, seconds and milliseconds. And not even taking into account timezones and leap seconds.\n",
        "\n",
        "Fortunately there is a simple solution. We can transform them to *Unix time*, which is basically a number representing the number of seconds that have passed since January 1, 1970. The standard library already has functions that take care of this conversion for us, taking leap seconds and everything into account.\n",
        "\n",
        "Transformed as a number, we can now use the same method as with numbers going between 0 and the current time."
      ]
    },
    {
      "metadata": {
        "id": "xPb0Emf-RLOi",
        "colab_type": "code",
        "outputId": "8e426c7a-9bd9-46ae-af0f-318861efc56d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 258
        }
      },
      "cell_type": "code",
      "source": [
        "from datetime import datetime\n",
        "\n",
        "class DateField(NumericalField):\n",
        "  def __init__(self, name):\n",
        "    super().__init__(name, 0, datetime.now().timestamp())\n",
        "\n",
        "np.random.seed(42)\n",
        "field = DateField('Test field name')\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in np.linspace(field.min_value, field.max_value, 10):\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:unsupported operand type(s) for -: 'str' and 'int': value=X, range=(0, 1554441094.707533)\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: 582200552.3529836\n",
            "  0.0 label: 0.0\n",
            "  172715677.18972588 label: 0.1111111111111111\n",
            "  345431354.37945175 label: 0.2222222222222222\n",
            "  518147031.5691776 label: 0.3333333333333333\n",
            "  690862708.7589035 label: 0.4444444444444444\n",
            "  863578385.9486294 label: 0.5555555555555556\n",
            "  1036294063.1383553 label: 0.6666666666666666\n",
            "  1209009740.3280811 label: 0.7777777777777778\n",
            "  1381725417.517807 label: 0.8888888888888888\n",
            "  1554441094.707533 label: 1.0\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "c0IOKbePjvVs",
        "colab_type": "code",
        "outputId": "d13e5cc1-8cea-4dc8-c957-cc8161f81133",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 258
        }
      },
      "cell_type": "code",
      "source": [
        "class InstallationDate(DateField):\n",
        "  def __init__(self):\n",
        "    super().__init__('installation_date')\n",
        "\n",
        "np.random.seed(42)\n",
        "field = InstallationDate()\n",
        "print(f\"random: {field.generate_random()}\")\n",
        "for value in np.linspace(field.min_value, field.max_value, 10):\n",
        "  print(f\"  {value} label: {field.estimate_label(value)}\")\n",
        "print(f\"  X label: {field.estimate_label('X')}\")"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "ERROR:root:unsupported operand type(s) for -: 'str' and 'int': value=X, range=(0, 1554441095.319178)\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "stream",
          "text": [
            "random: 582200552.5820693\n",
            "  0.0 label: 0.0\n",
            "  172715677.25768647 label: 0.11111111111111112\n",
            "  345431354.51537293 label: 0.22222222222222224\n",
            "  518147031.77305937 label: 0.3333333333333333\n",
            "  690862709.0307459 label: 0.4444444444444445\n",
            "  863578386.2884324 label: 0.5555555555555556\n",
            "  1036294063.5461187 label: 0.6666666666666666\n",
            "  1209009740.8038054 label: 0.7777777777777779\n",
            "  1381725418.0614917 label: 0.888888888888889\n",
            "  1554441095.319178 label: 1.0\n",
            "  X label: 0.5\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "zv9mnF6s9t1x",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Schema"
      ]
    },
    {
      "metadata": {
        "id": "BzdIIYrrZd_Y",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We'll now put together all the fields into a schema. This will allow us to access the columns and to generate new pipes.\n",
        "\n",
        "Note that we also have a `columns_training()` method, which basically adds a label column. This `label` column is the value the Machine Learning model will try to learn to predict.\n",
        "\n",
        "In this case we'll name the number of years between repairs as the **`lifespan`**."
      ]
    },
    {
      "metadata": {
        "id": "0nHy9OSoUsOq",
        "colab_type": "code",
        "outputId": "47b66cc7-e2b3-4e66-c416-2c9d13ecf653",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 156
        }
      },
      "cell_type": "code",
      "source": [
        "import re\n",
        "\n",
        "class Schema:\n",
        "  def __init__(self, fields, id_name='id', label_name='label'):\n",
        "    self.fields = fields\n",
        "    self.id_name = id_name\n",
        "    self.label_name = label_name\n",
        "\n",
        "  def columns(self):\n",
        "    return [self.id_name] + [field.name for field in self.fields]\n",
        "\n",
        "  def columns_training(self):\n",
        "    return self.columns() + [self.label_name]\n",
        "\n",
        "  def generate_random(self, element_id, seed=None):\n",
        "    np.random.seed(seed)\n",
        "    pipe = {field.name: field.generate_random() for field in self.fields}\n",
        "    pipe[self.id_name] = element_id\n",
        "    return pipe\n",
        "\n",
        "fields = [\n",
        "    Material(),\n",
        "    InstallationDate(),\n",
        "    Diameter(),\n",
        "    Length(),\n",
        "]\n",
        "schema = Schema(fields, id_name='pipe_id', label_name='lifespan')\n",
        "\n",
        "print(schema.columns())\n",
        "print(schema.columns_training())\n",
        "schema.generate_random(0, seed=42)"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "['pipe_id', 'material', 'installation_date', 'diameter_in_inches', 'length_in_miles']\n",
            "['pipe_id', 'material', 'installation_date', 'diameter_in_inches', 'length_in_miles', 'lifespan']\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'diameter_in_inches': 3.0,\n",
              " 'installation_date': 1477829388.597788,\n",
              " 'length_in_miles': 7.818940902700415,\n",
              " 'material': 'galvanized iron',\n",
              " 'pipe_id': 0}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "metadata": {
        "id": "cu1MZx5s7rUP",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Training the model"
      ]
    },
    {
      "metadata": {
        "id": "QZw0yldBaQ7P",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We're almost ready to generate the data, but we're still missing the data labels. We  need to calculate the value we want the model to learn to predict. And we'll use the `estimate_label()` methods we created earlier."
      ]
    },
    {
      "metadata": {
        "id": "TWTyB9grfQBN",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Data labeling"
      ]
    },
    {
      "metadata": {
        "id": "rgnmc2HrdYrQ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "First, we'll calculate a *weight* from `0` to `1` using the `estimate_label()` methods. We can then scale this into any other range. Eventually, we'll scale it to values from `0` to `100`, since it's roughly a good estimate of the lifespan of pipes.\n",
        "\n",
        "Remember, the weights don't have to be strictly between `0` and `1`, because in the end the model might predict `-0.1` or `1.2`, or even an occasional `3.14`, but most values will be between `0` and `1`. So predictions can fall outside our predefined range of `0` to `100` years."
      ]
    },
    {
      "metadata": {
        "id": "Gbh0f75FbnPZ",
        "colab_type": "code",
        "outputId": "23a06b6c-df9a-4127-ac8b-64834590151d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        }
      },
      "cell_type": "code",
      "source": [
        "pipe = schema.generate_random(0, seed=42)\n",
        "\n",
        "lifespan_weight = 0.0\n",
        "print(\"field: value; weight * lifespan = weighted_lifespan\")\n",
        "for weight, field in zip(weights, schema.fields):\n",
        "  label = field.estimate_label(pipe[field.name])\n",
        "  weighted_label = weight * label\n",
        "\n",
        "  print(f\"  {field.name}: {weight} * {label} = {weighted_label}\")\n",
        "  lifespan_weight += weighted_label\n",
        "print(f\"lifespan_weight: {lifespan_weight}\")"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "field: value; weight * lifespan = weighted_lifespan\n",
            "  material: 0.4 * 0.25 = 0.1\n",
            "  installation_date: 0.3 * 0.9507143064099162 = 0.2852142919229748\n",
            "  diameter_in_inches: 0.2 * 0.625 = 0.125\n",
            "  length_in_miles: 0.1 * 0.7796910002727693 = 0.07796910002727693\n",
            "lifespan_weight: 0.5881833919502518\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "tsr8w1OykPG8",
        "colab_type": "code",
        "outputId": "ac68cd83-ac42-4417-f2bc-47323c747657",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 119
        }
      },
      "cell_type": "code",
      "source": [
        "def calculate_lifespan_years(schema, pipe):\n",
        "  # We'll assume the fields are sorted by importance.\n",
        "  # That is, the first field will have the largest weight to lifespan, and the\n",
        "  # last field will have the least weight.\n",
        "  indices = np.array(range(len(schema.fields), 0, -1))\n",
        "  weights = indices / indices.sum()\n",
        "\n",
        "  # Calculate a weighted sum for the lifespan estimate of each field.\n",
        "  lifespan_weight = 0.0\n",
        "  for weight, field in zip(weights, schema.fields):\n",
        "    lifespan_weight += weight * field.estimate_label(pipe[field.name])\n",
        "\n",
        "  # Return in a scale from 0 to 100 years.\n",
        "  return 100.0 * lifespan_weight\n",
        "\n",
        "pipe = schema.generate_random(0, seed=42)\n",
        "pprint(pipe)\n",
        "\n",
        "lifespan_years = calculate_lifespan_years(schema, pipe)\n",
        "print(f\"lifespan_years: {lifespan_years}\")"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "{'diameter_in_inches': 3.0,\n",
            " 'installation_date': 1477809099.538208,\n",
            " 'length_in_miles': 7.818940902700415,\n",
            " 'material': 'galvanized iron',\n",
            " 'pipe_id': 0}\n",
            "lifespan_years: 58.81833919502518\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "77Uqekx3pioV",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Generating the training data"
      ]
    },
    {
      "metadata": {
        "id": "C42RbNQafVlx",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We're finally ready to generate our training data!\n",
        "\n",
        "We'll just write it to a CSV file since we can easily load that into BigQuery."
      ]
    },
    {
      "metadata": {
        "id": "JYfraO7vj10V",
        "colab_type": "code",
        "cellView": "form",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "training_points = 1000000 #@param {type:\"integer\"}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "sKfhVnNoe1bt",
        "colab_type": "code",
        "outputId": "81258c7a-7f02-47b3-c128-64f9089c8a18",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 204
        }
      },
      "cell_type": "code",
      "source": [
        "import csv\n",
        "\n",
        "np.random.seed(42)\n",
        "\n",
        "training_data_file = 'data.training.csv'\n",
        "with open(training_data_file, 'w') as f:\n",
        "  writer = csv.DictWriter(f, schema.columns_training())\n",
        "  writer.writerow({col: col for col in schema.columns_training()})\n",
        "\n",
        "  for i in range(training_points):\n",
        "    # Get all the fields in the schema plus the lifespan (the model label).\n",
        "    pipe = schema.generate_random(f\"pipe {i}\")\n",
        "    pipe[schema.label_name] = calculate_lifespan_years(schema, pipe)\n",
        "    writer.writerow(pipe)\n",
        "\n",
        "!ls -lh {training_data_file}\n",
        "!head {training_data_file}"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "-rw-r--r-- 1 root root 78M Apr  5 00:08 data.training.csv\n",
            "pipe_id,material,installation_date,diameter_in_inches,length_in_miles,lifespan\n",
            "pipe 0,cast iron,733799626.8085197,4.0,1.1394253942982249,28.96211521315033\n",
            "pipe 1,copper,466256102.76490426,3.0,5.052582032218517,46.50126052701041\n",
            "pipe 2,cast iron,388383361.23934716,10.0,3.842743531008332,30.02627261672147\n",
            "pipe 3,pvc,982745238.7999034,0.25,2.773272528118681,62.91706791419678\n",
            "pipe 4,cast iron,538849234.2931997,6.0,4.886764052620875,31.484800986829608\n",
            "pipe 5,pvc,408205690.55501634,1.0,6.534157697395788,60.62743989862044\n",
            "pipe 6,copper,700831520.0825703,1.25,7.060759651579949,48.05698285764014\n",
            "pipe 7,pvc,1396897870.094761,1.25,7.998648315122436,82.43829078778198\n",
            "pipe 8,ceramic,993421118.277087,5.0,0.9596218247364392,65.04113991145189\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "Gj3vyEU9e2F0",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Uploading data to BigQuery"
      ]
    },
    {
      "metadata": {
        "id": "Kg9Az0mafjTk",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "First, we'll copy it to Cloud Storage so BigQuery can load it."
      ]
    },
    {
      "metadata": {
        "id": "vIWJvCGYpSyW",
        "colab_type": "code",
        "outputId": "0c5a9109-b085-4951-c5f9-95c826bb4e12",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 85
        }
      },
      "cell_type": "code",
      "source": [
        "gcs_training_data_file = f\"gs://{bucket}/{training_data_file}\"\n",
        "run(f\"gsutil cp {training_data_file} {gcs_training_data_file}\")"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            ">> gsutil cp data.training.csv gs://pdm-2018/data.training.csv\n",
            "Copying file://data.training.csv [Content-Type=text/csv]...\n",
            "\\\n",
            "Operation completed over 1 objects/77.5 MiB.                                     \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "l9nC_kDvpm4v",
        "colab_type": "code",
        "outputId": "8a63af0e-659d-4602-ac53-eeb21ea4915b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 71
        }
      },
      "cell_type": "code",
      "source": [
        "# Load the data into the table.\n",
        "run(f\"{bq} load --source_format=CSV --autodetect --replace \"\n",
        "    f\"{dataset}.{table_training} {gcs_training_data_file}\")"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            ">> bq --project pdm-2018 load --source_format=CSV --autodetect --replace water_utilities.pipes_training gs://pdm-2018/data.training.csv\n",
            "Waiting on bqjob_r2646c0881d246eea_00000169ead4a083_1 ... (15s) Current status: DONE   \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "vLs8_fLixCKw",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Training a BigQuery ML Model"
      ]
    },
    {
      "metadata": {
        "id": "AGsAeOP4fxBu",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "First, let's get 3 rows to see how our data looks like."
      ]
    },
    {
      "metadata": {
        "id": "5NayVOUJxFH2",
        "colab_type": "code",
        "outputId": "674ca008-f591-4aa1-88d9-ffdeb22d41df",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT * FROM `water_utilities.pipes_training` LIMIT 3"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>pipe_id</th>\n",
              "      <th>material</th>\n",
              "      <th>installation_date</th>\n",
              "      <th>diameter_in_inches</th>\n",
              "      <th>length_in_miles</th>\n",
              "      <th>lifespan</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>pipe 62</td>\n",
              "      <td>pvc</td>\n",
              "      <td>1.100928e+09</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.158857</td>\n",
              "      <td>71.307151</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>pipe 76</td>\n",
              "      <td>pvc</td>\n",
              "      <td>1.609211e+08</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5.175855</td>\n",
              "      <td>58.232873</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>pipe 100</td>\n",
              "      <td>pvc</td>\n",
              "      <td>1.511691e+09</td>\n",
              "      <td>2.0</td>\n",
              "      <td>0.216494</td>\n",
              "      <td>79.293020</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    pipe_id material  installation_date  diameter_in_inches  length_in_miles  \\\n",
              "0   pipe 62      pvc       1.100928e+09                 2.0         0.158857   \n",
              "1   pipe 76      pvc       1.609211e+08                 2.0         5.175855   \n",
              "2  pipe 100      pvc       1.511691e+09                 2.0         0.216494   \n",
              "\n",
              "    lifespan  \n",
              "0  71.307151  \n",
              "1  58.232873  \n",
              "2  79.293020  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 53
        }
      ]
    },
    {
      "metadata": {
        "id": "RtznLhbLf4A_",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Next, we can create and train a BigQuery ML model with a very simple SQL [CREATE MODEL statement](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-create). It is very similar to creating a table, and we'll be able to access it as if it were a table. The only difference is that rather than having the values *stored* in the database, they will be computed as needed.\n",
        "\n",
        "We'll use linear regression to train our model since we're predicting a number. If you're predicting a classification, you would want to use logistic regression (`logistic_reg`) instead."
      ]
    },
    {
      "metadata": {
        "id": "dQw61HFTyia_",
        "colab_type": "code",
        "outputId": "ba5073cf-71a1-4323-86a9-f354b8edef49",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 32
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "CREATE OR REPLACE MODEL\n",
        "  `water_utilities.pipe_lifespan`\n",
        "OPTIONS (\n",
        "  model_type='linear_reg',\n",
        "  input_label_cols=['lifespan']\n",
        ")\n",
        "AS SELECT * FROM `water_utilities.pipes_training`"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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: []"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 54
        }
      ]
    },
    {
      "metadata": {
        "id": "YwPoF88j1wlb",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Evaluating the model"
      ]
    },
    {
      "metadata": {
        "id": "PmnDxNSEgqR8",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "So, something happened, but we don't really know any details of it. How can we measure if the model is doing good or bad?\n",
        "\n",
        "Fortunately, BigQuery ML provides us with an [ML.EVALUATE function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-evaluate) which will give us more details on the error and variance of our model."
      ]
    },
    {
      "metadata": {
        "id": "wlswwo7a0zfF",
        "colab_type": "code",
        "outputId": "98248fce-3780-45f3-a368-9413b93a2d99",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 80
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT *\n",
        "FROM\n",
        "  ML.EVALUATE(\n",
        "    MODEL `water_utilities.pipe_lifespan`,\n",
        "    (SELECT * FROM `water_utilities.pipes_training`)\n",
        "  )"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>4.168218</td>\n",
              "      <td>24.778829</td>\n",
              "      <td>0.025295</td>\n",
              "      <td>3.979697</td>\n",
              "      <td>0.922795</td>\n",
              "      <td>0.968233</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   mean_absolute_error  mean_squared_error  mean_squared_log_error  \\\n",
              "0             4.168218           24.778829                0.025295   \n",
              "\n",
              "   median_absolute_error  r2_score  explained_variance  \n",
              "0               3.979697  0.922795            0.968233  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 55
        }
      ]
    },
    {
      "metadata": {
        "id": "RxkeudV20xQo",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "## Getting predictions"
      ]
    },
    {
      "metadata": {
        "id": "7ZLLkD5OjCZS",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We can get prediction results with a simple SELECT statement using the [ML.PREDICT function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict), and access it as if it were another table."
      ]
    },
    {
      "metadata": {
        "id": "GhG6yOWx35Pa",
        "colab_type": "code",
        "outputId": "4b680aa3-ee22-41c8-a30f-93b558b05e96",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT\n",
        "  predicted_lifespan\n",
        "FROM\n",
        "  ML.PREDICT(\n",
        "    MODEL `water_utilities.pipe_lifespan`,\n",
        "    (SELECT * FROM `water_utilities.pipes_training` LIMIT 3)\n",
        "  )"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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_lifespan</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>71.917459</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>60.427110</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>78.944235</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   predicted_lifespan\n",
              "0           71.917459\n",
              "1           60.427110\n",
              "2           78.944235"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 56
        }
      ]
    },
    {
      "metadata": {
        "id": "oZ8I0ghajRhQ",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Let's compare some of our generated labels with the predicted values. They are surprisingly similar, so it's looking good!"
      ]
    },
    {
      "metadata": {
        "id": "taY7dOjO16o3",
        "colab_type": "code",
        "outputId": "1e57dd59-8821-4334-91ce-482d6d44e547",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 359
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT\n",
        "  lifespan,\n",
        "  predicted_lifespan\n",
        "FROM\n",
        "  ML.PREDICT(\n",
        "    MODEL `water_utilities.pipe_lifespan`,\n",
        "    (SELECT * FROM `water_utilities.pipes_training` LIMIT 10)\n",
        "  )"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>lifespan</th>\n",
              "      <th>predicted_lifespan</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>71.307151</td>\n",
              "      <td>71.917459</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>58.232873</td>\n",
              "      <td>60.427110</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>79.293020</td>\n",
              "      <td>78.944235</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>75.898893</td>\n",
              "      <td>75.978790</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>72.430876</td>\n",
              "      <td>72.922252</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>69.135648</td>\n",
              "      <td>70.014202</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>69.630158</td>\n",
              "      <td>70.466734</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>79.228437</td>\n",
              "      <td>78.892358</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>56.831197</td>\n",
              "      <td>59.188136</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>71.027719</td>\n",
              "      <td>71.674389</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    lifespan  predicted_lifespan\n",
              "0  71.307151           71.917459\n",
              "1  58.232873           60.427110\n",
              "2  79.293020           78.944235\n",
              "3  75.898893           75.978790\n",
              "4  72.430876           72.922252\n",
              "5  69.135648           70.014202\n",
              "6  69.630158           70.466734\n",
              "7  79.228437           78.892358\n",
              "8  56.831197           59.188136\n",
              "9  71.027719           71.674389"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 57
        }
      ]
    },
    {
      "metadata": {
        "id": "jrrLjzLpBTlC",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "# Generating the app data"
      ]
    },
    {
      "metadata": {
        "id": "FRMQPWpdjzN7",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "We'll now generate a second dataset. This will be to populate the app since we don't have real data, but you could skip this step if you already have data.\n",
        "\n",
        "For this dataset, however, we no longer need the label. And we'll also add `last_repair_date` and `repair_cost` columns. We'll add the predicted lifespan to the last repair date to get the predicted break date.\n",
        "\n",
        "We'll also create a simple repair cost estimate to test the budget analysis on the app."
      ]
    },
    {
      "metadata": {
        "colab_type": "code",
        "cellView": "form",
        "id": "2w-4NfhwtNmD",
        "colab": {}
      },
      "cell_type": "code",
      "source": [
        "total_pipes = 1000 #@param {type:\"integer\"}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "MW1ORyle3Vqh",
        "colab_type": "code",
        "outputId": "2860222f-3ca4-4729-9b77-237da2a82e3c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 224
        }
      },
      "cell_type": "code",
      "source": [
        "import csv\n",
        "\n",
        "np.random.seed(42)\n",
        "\n",
        "data_file = 'data.csv'\n",
        "with open(data_file, 'w') as f:\n",
        "  columns = schema.columns() + ['last_repair_date', 'repair_cost']\n",
        "  writer = csv.DictWriter(f, columns)\n",
        "  writer.writerow({col: col for col in columns})\n",
        "\n",
        "  for i in range(total_pipes):\n",
        "    pipe = schema.generate_random(f\"pipe {i}\", seed=i)\n",
        "\n",
        "    # The last repair date is any date between the installation date and now.\n",
        "    pipe['last_repair_date'] = np.random.uniform(pipe['installation_date'], datetime.now().timestamp())\n",
        "\n",
        "    # The repair cost will be around $10,000 and affected by the pipe length.\n",
        "    pipe['repair_cost'] = np.log(pipe['length_in_miles']) * 10000\n",
        "\n",
        "    writer.writerow(pipe)\n",
        "\n",
        "!ls -lh {data_file}\n",
        "!head {data_file}"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "-rw-r--r-- 1 root root 95K Apr  5 05:16 data.csv\n",
            "pipe_id,material,installation_date,diameter_in_inches,length_in_miles,last_repair_date,repair_cost\n",
            "pipe 0,cast iron,921542038.2105968,0.125,6.067357423109274,1266398254.849563,18029.231598850518\n",
            "pipe 1,galvanized iron,1119701995.1824014,2.5,3.0930924690552137,1183502563.236167,11291.71389491165\n",
            "pipe 2,pvc,287699188.47648627,2.0,5.541658530999221,839140437.1262035,17122.938297823643\n",
            "pipe 3,copper,109937660.01058717,0.5,2.979956915238149,847830044.2121457,10919.0884243902\n",
            "pipe 4,copper,1399963002.5420372,1.0,9.729575163652354,1510386628.9287405,22751.702327217958\n",
            "pipe 5,galvanized iron,85774252.39892076,1.25,3.7009952812958358,1524252941.7219849,13086.017784223459\n",
            "pipe 6,copper,1472795751.060424,3.0,0.7361205854475242,1503013571.9554102,-3063.613347501248\n",
            "pipe 7,cast iron,1212337822.33122,0.5,9.784406681077966,1368195049.3324254,22807.89963452958\n",
            "pipe 8,galvanized iron,17276743.26681634,2.5,8.705025948117807,833289348.0773457,21639.005540448663\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "ksjFYJMKkod4",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Next, we'll also copy it to Cloud Storage and upload it into BigQuery to another table."
      ]
    },
    {
      "metadata": {
        "id": "h81jAkVk4krI",
        "colab_type": "code",
        "outputId": "098712a3-ef9a-4d18-baa7-0f3b0a7e798b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 105
        }
      },
      "cell_type": "code",
      "source": [
        "gcs_data_file = f\"gs://{bucket}/{data_file}\"\n",
        "run(f\"gsutil cp {data_file} {gcs_data_file}\")"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            ">> gsutil cp data.csv gs://pdm-2018/data.csv\n",
            "Copying file://data.csv [Content-Type=text/csv]...\n",
            "/ [1 files][ 94.8 KiB/ 94.8 KiB]                                                \n",
            "Operation completed over 1 objects/94.8 KiB.                                     \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "Pd9-SZTd4YUL",
        "colab_type": "code",
        "outputId": "4ddfe870-1390-4a50-e062-7f44b65cfa88",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 71
        }
      },
      "cell_type": "code",
      "source": [
        "# Load the data into the table.\n",
        "run(f\"{bq} load --source_format=CSV --autodetect --replace \"\n",
        "    f\"{dataset}.{table} {gcs_data_file}\")"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            ">> bq --project pdm-2018 load --source_format=CSV --autodetect --replace water_utilities.pipes gs://pdm-2018/data.csv\n",
            "Waiting on bqjob_rc86524071141114_00000169ebee053a_1 ... (1s) Current status: DONE   \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "id": "rU7UGe5M5XQi",
        "colab_type": "code",
        "outputId": "1793890f-2028-4b5d-e346-21374c31583f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 142
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT * FROM `water_utilities.pipes` LIMIT 3"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>pipe_id</th>\n",
              "      <th>material</th>\n",
              "      <th>installation_date</th>\n",
              "      <th>diameter_in_inches</th>\n",
              "      <th>length_in_miles</th>\n",
              "      <th>last_repair_date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>pipe 2</td>\n",
              "      <td>pvc</td>\n",
              "      <td>2.876952e+08</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5.541659</td>\n",
              "      <td>8.391293e+08</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>pipe 57</td>\n",
              "      <td>pvc</td>\n",
              "      <td>4.830868e+08</td>\n",
              "      <td>2.0</td>\n",
              "      <td>8.081632</td>\n",
              "      <td>1.487070e+09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>pipe 75</td>\n",
              "      <td>pvc</td>\n",
              "      <td>1.107097e+09</td>\n",
              "      <td>2.0</td>\n",
              "      <td>3.333048</td>\n",
              "      <td>1.266758e+09</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   pipe_id material  installation_date  diameter_in_inches  length_in_miles  \\\n",
              "0   pipe 2      pvc       2.876952e+08                 2.0         5.541659   \n",
              "1  pipe 57      pvc       4.830868e+08                 2.0         8.081632   \n",
              "2  pipe 75      pvc       1.107097e+09                 2.0         3.333048   \n",
              "\n",
              "   last_repair_date  \n",
              "0      8.391293e+08  \n",
              "1      1.487070e+09  \n",
              "2      1.266758e+09  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 43
        }
      ]
    },
    {
      "metadata": {
        "id": "FAjQkEa4kvKH",
        "colab_type": "text"
      },
      "cell_type": "markdown",
      "source": [
        "Finally, we can get predictions on our newly created dataset."
      ]
    },
    {
      "metadata": {
        "id": "uFgDXg1u5can",
        "colab_type": "code",
        "outputId": "3c0fa8cd-ac78-4009-d2e8-05250056f64c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 162
        }
      },
      "cell_type": "code",
      "source": [
        "%%bigquery --project {project}\n",
        "SELECT\n",
        "  pipe_id,\n",
        "  material,\n",
        "  diameter_in_inches,\n",
        "  length_in_miles,\n",
        "  installation_date,\n",
        "  last_repair_date,\n",
        "  last_repair_date + predicted_lifespan*60*60*24*365 AS predicted_break_date\n",
        "FROM\n",
        "  ML.PREDICT(\n",
        "    MODEL `water_utilities.pipe_lifespan`,\n",
        "    (SELECT * FROM `water_utilities.pipes` LIMIT 3)\n",
        "  )"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>pipe_id</th>\n",
              "      <th>material</th>\n",
              "      <th>diameter_in_inches</th>\n",
              "      <th>length_in_miles</th>\n",
              "      <th>installation_date</th>\n",
              "      <th>last_repair_date</th>\n",
              "      <th>predicted_break_date</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>pipe 2</td>\n",
              "      <td>pvc</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5.541659</td>\n",
              "      <td>2.876992e+08</td>\n",
              "      <td>8.391404e+08</td>\n",
              "      <td>2.382652e+09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>pipe 57</td>\n",
              "      <td>pvc</td>\n",
              "      <td>2.0</td>\n",
              "      <td>8.081632</td>\n",
              "      <td>4.830934e+08</td>\n",
              "      <td>1.487089e+09</td>\n",
              "      <td>3.280129e+09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>pipe 75</td>\n",
              "      <td>pvc</td>\n",
              "      <td>2.0</td>\n",
              "      <td>3.333048</td>\n",
              "      <td>1.107112e+09</td>\n",
              "      <td>1.266775e+09</td>\n",
              "      <td>3.435509e+09</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   pipe_id material  diameter_in_inches  length_in_miles  installation_date  \\\n",
              "0   pipe 2      pvc                 2.0         5.541659       2.876992e+08   \n",
              "1  pipe 57      pvc                 2.0         8.081632       4.830934e+08   \n",
              "2  pipe 75      pvc                 2.0         3.333048       1.107112e+09   \n",
              "\n",
              "   last_repair_date  predicted_break_date  \n",
              "0      8.391404e+08          2.382652e+09  \n",
              "1      1.487089e+09          3.280129e+09  \n",
              "2      1.266775e+09          3.435509e+09  "
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 27
        }
      ]
    }
  ]
}