 milestone3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "psychological-shannon",
   "metadata": {},
   "source": [
    "### Table Locations Pipeline [1/2]: Remove duplicate locations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ultimate-federal",
   "metadata": {},
   "source": [
    "##### Due to requiring two Apache Beam pipelines for table locations, the python files are named locations_beam_1.py and locations_beam_2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "younger-riding",
   "metadata": {},
   "source": [
    "#### Get unique locations from duplicate latitude/longitude in table locations into locations_Beam"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "opposed-visit",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run locations_beam_1.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "advisory-healing",
   "metadata": {},
   "source": [
    "#### Create the locations table without the duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "muslim-witch",
   "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": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.new_locations AS\n",
    "SELECT *\n",
    "FROM datamart.locations AS l\n",
    "WHERE l.id NOT IN (SELECT lo.id\n",
    "                    FROM datamart.locations AS lo\n",
    "                    WHERE (lo.latitude IN (SELECT lat.latitude FROM datamart.locations AS lat WHERE lo.latitude=lat.latitude AND lo.id!=lat.id) \n",
    "                    AND longitude IN (SELECT long.longitude FROM datamart.locations AS long WHERE lo.longitude=long.longitude AND lo.id!=long.id) \n",
    "                    AND lo.latitude!=0 \n",
    "                    AND lo.longitude!=0)\n",
    "                    AND lo.id NOT IN (SELECT b.id FROM datamart.locations_Beam AS b))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "average-moment",
   "metadata": {},
   "source": [
    "#### Check that only duplicates were removed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "miniature-oasis",
   "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>orginal_count</th>\n",
       "      <th>num_duplicates</th>\n",
       "      <th>new_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4413</td>\n",
       "      <td>108</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   orginal_count  num_duplicates  new_count\n",
       "0           4413             108       4305"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS orginal_count, \n",
    "                    (SELECT count(*)\n",
    "                    FROM datamart.locations AS lo\n",
    "                    WHERE (lo.latitude IN (SELECT lat.latitude FROM datamart.locations AS lat WHERE lo.latitude=lat.latitude AND lo.id!=lat.id) \n",
    "                    AND longitude IN (SELECT long.longitude FROM datamart.locations AS long WHERE lo.longitude=long.longitude AND lo.id!=long.id) \n",
    "                    AND lo.latitude!=0 \n",
    "                    AND lo.longitude!=0)\n",
    "                    AND lo.id NOT IN (SELECT b.id FROM datamart.locations_Beam AS b)) AS num_duplicates,\n",
    "                    (SELECT count(*)\n",
    "                    FROM datamart.new_locations) AS new_count\n",
    "FROM datamart.locations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "informational-mystery",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "tired-orchestra",
   "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>distinct_PK</th>\n",
       "      <th>locations_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  locations_count\n",
       "0         4305             4305"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.new_locations) AS locations_count\n",
    "FROM (\n",
    "    SELECT DISTINCT id\n",
    "    FROM datamart.new_locations\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "adolescent-integrity",
   "metadata": {},
   "source": [
    "### SQL Transforms: Update location_id of the orphaned records in table cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "neither-london",
   "metadata": {},
   "source": [
    "##### Note: No Apache Beam pipeline was written for table cases, therefore the cases table is being modified directly rather than making a new table called cases_Beam"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "insured-paragraph",
   "metadata": {},
   "source": [
    "#### Create a junction table to match abandoned ids to kept ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "worth-swiss",
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.abandoned_junction AS\n",
    "SELECT lo.id, lo.latitude, lo.longitude\n",
    "FROM datamart.locations AS lo\n",
    "WHERE (lo.latitude IN (SELECT lat.latitude FROM datamart.locations AS lat WHERE lo.latitude=lat.latitude AND lo.id!=lat.id) \n",
    "AND longitude IN (SELECT long.longitude FROM datamart.locations AS long WHERE lo.longitude=long.longitude AND lo.id!=long.id) \n",
    "AND lo.latitude!=0 \n",
    "AND lo.longitude!=0)\n",
    "AND lo.id NOT IN (SELECT b.id FROM datamart.locations_Beam AS b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "attended-uncertainty",
   "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>keep_id</th>\n",
       "      <th>rid_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80692389010230126</td>\n",
       "      <td>7902239636534001922</td>\n",
       "      <td>13.444300</td>\n",
       "      <td>144.793700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80692389010230126</td>\n",
       "      <td>-1927967959511792155</td>\n",
       "      <td>13.444300</td>\n",
       "      <td>144.793700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>80692389010230126</td>\n",
       "      <td>-7958264304576551061</td>\n",
       "      <td>13.444300</td>\n",
       "      <td>144.793700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-6125909817942919488</td>\n",
       "      <td>-8744039356309243705</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-6125909817942919488</td>\n",
       "      <td>3620381022065474036</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>-4001783368444537860</td>\n",
       "      <td>-286247227594574592</td>\n",
       "      <td>37.854472</td>\n",
       "      <td>-111.441876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>-4001783368444537860</td>\n",
       "      <td>-4115263533047990398</td>\n",
       "      <td>37.854472</td>\n",
       "      <td>-111.441876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>-3162112320542996385</td>\n",
       "      <td>8800898412804184444</td>\n",
       "      <td>60.244297</td>\n",
       "      <td>-151.538888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>4683806234748901016</td>\n",
       "      <td>-6268268396950388964</td>\n",
       "      <td>62.313050</td>\n",
       "      <td>-149.574174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>6631201407879228136</td>\n",
       "      <td>2609427688395820275</td>\n",
       "      <td>37.322500</td>\n",
       "      <td>-113.477600</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>108 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 keep_id               rid_id   latitude   longitude\n",
       "0      80692389010230126  7902239636534001922  13.444300  144.793700\n",
       "1      80692389010230126 -1927967959511792155  13.444300  144.793700\n",
       "2      80692389010230126 -7958264304576551061  13.444300  144.793700\n",
       "3   -6125909817942919488 -8744039356309243705  15.097900  145.673900\n",
       "4   -6125909817942919488  3620381022065474036  15.097900  145.673900\n",
       "..                   ...                  ...        ...         ...\n",
       "103 -4001783368444537860  -286247227594574592  37.854472 -111.441876\n",
       "104 -4001783368444537860 -4115263533047990398  37.854472 -111.441876\n",
       "105 -3162112320542996385  8800898412804184444  60.244297 -151.538888\n",
       "106  4683806234748901016 -6268268396950388964  62.313050 -149.574174\n",
       "107  6631201407879228136  2609427688395820275  37.322500 -113.477600\n",
       "\n",
       "[108 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT b.id AS keep_id, a.id AS rid_id, a.latitude, a.longitude\n",
    "FROM datamart.locations_Beam AS b\n",
    "JOIN datamart.abandoned_junction AS a ON b.latitude=a.latitude AND b.longitude=a.longitude\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "innovative-tactics",
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.abandoned_junction AS\n",
    "SELECT b.id AS keep_id, a.id AS rid_id\n",
    "FROM datamart.locations_Beam AS b\n",
    "JOIN datamart.abandoned_junction AS a ON b.latitude=a.latitude AND b.longitude=a.longitude"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "pacific-wesley",
   "metadata": {},
   "source": [
    "#### Update table cases: change location_id from the abandoned location_id to the kept ones"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "handmade-alliance",
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.cases AS c\n",
    "SET location_id = (SELECT keep_id FROM datamart.abandoned_junction AS a WHERE c.location_id=a.rid_id)\n",
    "WHERE c.location_id IN (SELECT lo.id\n",
    "                        FROM datamart.locations AS lo\n",
    "                        WHERE (lo.latitude IN (SELECT lat.latitude FROM datamart.locations AS lat WHERE lo.latitude=lat.latitude AND lo.id!=lat.id) \n",
    "                        AND longitude IN (SELECT long.longitude FROM datamart.locations AS long WHERE lo.longitude=long.longitude AND lo.id!=long.id) \n",
    "                        AND lo.latitude!=0 \n",
    "                        AND lo.longitude!=0)\n",
    "                        AND lo.id NOT IN (SELECT b.id FROM datamart.locations_Beam AS b))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acting-yugoslavia",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cubic-training",
   "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>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475736</td>\n",
       "      <td>1475736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475736      1475736"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "religious-alexandria",
   "metadata": {},
   "source": [
    "##### remove duplicates based on confirmed, deaths, and recovered cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "divided-destiny",
   "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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.cases AS\n",
    "SELECT location_id, last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM(SELECT *, rank() over (partition by location_id, last_update order by confirmed DESC, deaths DESC, recovered DESC ) as rank\n",
    "FROM datamart.cases)\n",
    "WHERE rank = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "partial-stable",
   "metadata": {},
   "source": [
    "#### PK re-check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "express-retrieval",
   "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>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475736</td>\n",
       "      <td>1475736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475736      1475736"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "finished-interference",
   "metadata": {},
   "source": [
    "#### FK check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "collect-offering",
   "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>foreign_key_violations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   foreign_key_violations\n",
       "0                       0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS foreign_key_violations\n",
    "FROM datamart.cases AS c\n",
    "LEFT JOIN datamart.new_locations AS l ON c.location_id=l.id\n",
    "WHERE l.id IS null"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unlike-residence",
   "metadata": {},
   "source": [
    "### Table Locations Pipeline [2/2]: Standardize state names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "disabled-oxygen",
   "metadata": {},
   "source": [
    "##### Due to requiring two Apache Beam pipelines for table locations, the python files are named locations_beam_1.py and locations_beam_2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "english-wichita",
   "metadata": {},
   "source": [
    "#### Get locations with abreviated state names from table locations and standardize them into locations_Beam"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fixed-executive",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run locations_beam_2.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "amended-extent",
   "metadata": {},
   "source": [
    "#### Create complete locations_Beam table by replacing the unstandardize state rows with standardize rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "latter-perry",
   "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": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.new_locations AS\n",
    "SELECT *\n",
    "FROM datamart.new_locations AS l\n",
    "WHERE l.id NOT IN (SELECT b.id\n",
    "                    FROM datamart.locations_Beam AS b)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "behavioral-strategy",
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.locations_Beam AS\n",
    "SELECT * \n",
    "FROM datamart.new_locations\n",
    "UNION ALL\n",
    "SELECT *\n",
    "FROM datamart.locations_Beam"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "great-parks",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "charming-partition",
   "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>distinct_PK</th>\n",
       "      <th>locations_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  locations_count\n",
       "0         4305             4305"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.locations_Beam) AS locations_count\n",
    "FROM (\n",
    "    SELECT DISTINCT id\n",
    "    FROM datamart.locations_Beam\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "assigned-prior",
   "metadata": {},
   "source": [
    "### SQL Transforms on table locations_Beam"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "amended-pregnancy",
   "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>state</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA (From Diamond Princess)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D.C.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NE (From Diamond Princess)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TX (From Diamond Princess)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Washington</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Wuhan Evacuee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          state  count\n",
       "0                          None      1\n",
       "1    CA (From Diamond Princess)      1\n",
       "2                          D.C.      1\n",
       "3    NE (From Diamond Princess)      1\n",
       "4    TX (From Diamond Princess)      1\n",
       "..                          ...    ...\n",
       "64                   Washington     53\n",
       "65                West Virginia     57\n",
       "66                    Wisconsin     76\n",
       "67                Wuhan Evacuee      1\n",
       "68                      Wyoming     27\n",
       "\n",
       "[69 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT distinct state, count(*) AS count\n",
    "FROM datamart.locations_Beam\n",
    "WHERE country='US'\n",
    "GROUP BY state\n",
    "ORDER BY state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "distinguished-digit",
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Beam\n",
    "SET state='District of Columbia'\n",
    "WHERE state=' D.C.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "residential-terrorism",
   "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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Beam\n",
    "SET state='Texas', combined_key='Diamond Princess, Lackland, Texas, US'\n",
    "WHERE state=' TX (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "employed-macedonia",
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Beam\n",
    "SET state='Nebraska', combined_key='Diamond Princess, Omaha, Nebraska, US'\n",
    "WHERE state=' NE (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bigger-yahoo",
   "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": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Beam\n",
    "SET state='California', combined_key='Diamond Princess, Travis, California, US'\n",
    "WHERE state=' CA (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "natural-arlington",
   "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>state</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Washington</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Wuhan Evacuee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  count\n",
       "0             None      1\n",
       "1          Alabama     71\n",
       "2           Alaska     37\n",
       "3   American Samoa      1\n",
       "4          Arizona     22\n",
       "..             ...    ...\n",
       "60      Washington     53\n",
       "61   West Virginia     57\n",
       "62       Wisconsin     76\n",
       "63   Wuhan Evacuee      1\n",
       "64         Wyoming     27\n",
       "\n",
       "[65 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT distinct state, count(*) AS count\n",
    "FROM datamart.locations_Beam\n",
    "WHERE country='US'\n",
    "GROUP BY state\n",
    "ORDER BY state"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "affecting-microphone",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "terminal-soccer",
   "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>distinct_PK</th>\n",
       "      <th>locations_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  locations_count\n",
       "0         4305             4305"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.locations_Beam) AS locations_count\n",
    "FROM (\n",
    "    SELECT DISTINCT id\n",
    "    FROM datamart.locations_Beam\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "gothic-arizona",
   "metadata": {},
   "source": [
    "### Temp tables were deleted via BigQuery's UI"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "common-cpu.mnightly-2021-01-20-debian-10-test",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:mnightly-2021-01-20-debian-10-test"
  },
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}