{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false"
   },
   "source": [
    "Data from [European Centre for Disease Prevention and Control](https://www.ecdc.europa.eu/en/publications-data/download-todays-data-geographic-distribution-covid-19-cases-worldwide)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.types import Integer, Text, String, DateTime, Date, Float\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 315,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "import itertools\n",
    "import collections\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from scipy.stats import gmean\n",
    "import datetime\n",
    "\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 316,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 317,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 318,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: covid@covid'"
      ]
     },
     "execution_count": 318,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $connection_string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "eng = create_engine(connection_string)\n",
    "engine = eng.execution_options(isolation_level=\"AUTOCOMMIT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "DEATH_COUNT_THRESHOLD = 10\n",
    "COUNTRIES_CORE = 'IT DE UK ES IE FR BE'.split()\n",
    "COUNTRIES_NORDIC = 'SE NO DK FI UK'.split()\n",
    "COUNTRIES_FRIENDS = 'IT UK ES BE SI MX'.split()\n",
    "# COUNTRIES_FRIENDS = 'IT UK ES BE SI PT'.split()\n",
    "\n",
    "COUNTRIES_AMERICAS = ['AG', 'AR', 'AW', 'BS', 'BB', 'BZ', 'BM', 'BO', 'BR', 'VG', 'KY', # excluding Canada and USA\n",
    "       'CL', 'CO', 'CR', 'CU', 'CW', 'DM', 'DO', 'EC', 'SV', 'GL', 'GD', 'GT',\n",
    "       'GY', 'HT', 'HN', 'JM', 'MX', 'MS', 'NI', 'PA', 'PY', 'PE', 'PR', 'KN',\n",
    "       'LC', 'VC', 'SX', 'SR', 'TT', 'TC', 'VI', 'UY', 'VE']\n",
    "COUNTRIES_OF_INTEREST = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS))\n",
    "COUNTRIES_ALL = list(set(COUNTRIES_CORE + COUNTRIES_FRIENDS + COUNTRIES_NORDIC + COUNTRIES_AMERICAS))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 321,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100  623k  100  623k    0     0   927k      0 --:--:-- --:--:-- --:--:--  925k\n"
     ]
    }
   ],
   "source": [
    "!curl https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ > covid.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# First col is a date, treat geoId of NA as 'Namibia', not \"NA\" value\n",
    "raw_data = pd.read_csv('covid.csv', \n",
    "                       parse_dates=[0], dayfirst=True,\n",
    "                       keep_default_na=False, na_values = [''],\n",
    "#                        dtype = {'day': np.int64, \n",
    "#                                 'month': np.int64, \n",
    "#                                 'year': np.int64, \n",
    "#                                 'cases': np.int64, \n",
    "#                                 'deaths': np.int64, \n",
    "#                                 'countriesAndTerritories': str, \n",
    "#                                 'geoId': str, \n",
    "#                                 'countryterritoryCode': str, \n",
    "#                                 'popData2019': np.int64, \n",
    "#                                 'continentExp': str, \n",
    "#                                 }\n",
    "                      )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 323,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100050"
      ]
     },
     "execution_count": 323,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 324,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "raw_data.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 325,
   "metadata": {
    "Collapsed": "false"
   },
   "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>dateRep</th>\n",
       "      <th>year_week</th>\n",
       "      <th>cases_weekly</th>\n",
       "      <th>deaths_weekly</th>\n",
       "      <th>countriesAndTerritories</th>\n",
       "      <th>geoId</th>\n",
       "      <th>countryterritoryCode</th>\n",
       "      <th>popData2019</th>\n",
       "      <th>continentExp</th>\n",
       "      <th>notification_rate_per_100000_population_14-days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-18</td>\n",
       "      <td>2021-02</td>\n",
       "      <td>557</td>\n",
       "      <td>45</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "      <td>3.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-11</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>675</td>\n",
       "      <td>71</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "      <td>4.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>2020-53</td>\n",
       "      <td>902</td>\n",
       "      <td>60</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "      <td>7.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>2020-52</td>\n",
       "      <td>1994</td>\n",
       "      <td>88</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "      <td>7.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>2020-51</td>\n",
       "      <td>740</td>\n",
       "      <td>111</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "      <td>6.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     dateRep year_week  cases_weekly  deaths_weekly countriesAndTerritories  \\\n",
       "0 2021-01-18   2021-02           557             45             Afghanistan   \n",
       "1 2021-01-11   2021-01           675             71             Afghanistan   \n",
       "2 2021-01-04   2020-53           902             60             Afghanistan   \n",
       "3 2020-12-28   2020-52          1994             88             Afghanistan   \n",
       "4 2020-12-21   2020-51           740            111             Afghanistan   \n",
       "\n",
       "  geoId countryterritoryCode  popData2019 continentExp  \\\n",
       "0    AF                  AFG   38041757.0         Asia   \n",
       "1    AF                  AFG   38041757.0         Asia   \n",
       "2    AF                  AFG   38041757.0         Asia   \n",
       "3    AF                  AFG   38041757.0         Asia   \n",
       "4    AF                  AFG   38041757.0         Asia   \n",
       "\n",
       "   notification_rate_per_100000_population_14-days  \n",
       "0                                             3.24  \n",
       "1                                             4.15  \n",
       "2                                             7.61  \n",
       "3                                             7.19  \n",
       "4                                             6.56  "
      ]
     },
     "execution_count": 325,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 326,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "raw_data.rename(columns={'dateRep':'report_date', 'geoId': 'geo_id',\n",
    "                        'countriesAndTerritories': 'country_name',\n",
    "                        'countryterritoryCode': 'country_territory_code',\n",
    "                        'popData2019': 'population_2019',\n",
    "                        'continentExp': 'continent'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 327,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['report_date', 'year_week', 'cases_weekly', 'deaths_weekly',\n",
       "       'country_name', 'geo_id', 'country_territory_code', 'population_2019',\n",
       "       'continent', 'notification_rate_per_100000_population_14-days'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 327,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 328,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "report_date                                        datetime64[ns]\n",
       "year_week                                                  object\n",
       "cases_weekly                                                int64\n",
       "deaths_weekly                                               int64\n",
       "country_name                                               object\n",
       "geo_id                                                     object\n",
       "country_territory_code                                     object\n",
       "population_2019                                           float64\n",
       "continent                                                  object\n",
       "notification_rate_per_100000_population_14-days           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 328,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 329,
   "metadata": {
    "Collapsed": "false"
   },
   "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>report_date</th>\n",
       "      <th>cases_weekly</th>\n",
       "      <th>deaths_weekly</th>\n",
       "      <th>geo_id</th>\n",
       "      <th>notification_rate_per_100000_population_14-days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-18</td>\n",
       "      <td>557</td>\n",
       "      <td>45</td>\n",
       "      <td>AF</td>\n",
       "      <td>3.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-11</td>\n",
       "      <td>675</td>\n",
       "      <td>71</td>\n",
       "      <td>AF</td>\n",
       "      <td>4.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-04</td>\n",
       "      <td>902</td>\n",
       "      <td>60</td>\n",
       "      <td>AF</td>\n",
       "      <td>7.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-12-28</td>\n",
       "      <td>1994</td>\n",
       "      <td>88</td>\n",
       "      <td>AF</td>\n",
       "      <td>7.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-12-21</td>\n",
       "      <td>740</td>\n",
       "      <td>111</td>\n",
       "      <td>AF</td>\n",
       "      <td>6.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10000</th>\n",
       "      <td>2020-04-20</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>ZW</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10001</th>\n",
       "      <td>2020-04-13</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>ZW</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10002</th>\n",
       "      <td>2020-04-06</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>ZW</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10003</th>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>ZW</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10004</th>\n",
       "      <td>2020-03-23</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>ZW</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10005 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      report_date  cases_weekly  deaths_weekly geo_id  \\\n",
       "0      2021-01-18           557             45     AF   \n",
       "1      2021-01-11           675             71     AF   \n",
       "2      2021-01-04           902             60     AF   \n",
       "3      2020-12-28          1994             88     AF   \n",
       "4      2020-12-21           740            111     AF   \n",
       "...           ...           ...            ...    ...   \n",
       "10000  2020-04-20            11              0     ZW   \n",
       "10001  2020-04-13             5              2     ZW   \n",
       "10002  2020-04-06             2              0     ZW   \n",
       "10003  2020-03-30             5              1     ZW   \n",
       "10004  2020-03-23             2              0     ZW   \n",
       "\n",
       "       notification_rate_per_100000_population_14-days  \n",
       "0                                                 3.24  \n",
       "1                                                 4.15  \n",
       "2                                                 7.61  \n",
       "3                                                 7.19  \n",
       "4                                                 6.56  \n",
       "...                                                ...  \n",
       "10000                                             0.11  \n",
       "10001                                             0.05  \n",
       "10002                                             0.05  \n",
       "10003                                             0.05  \n",
       "10004                                             0.00  \n",
       "\n",
       "[10005 rows x 5 columns]"
      ]
     },
     "execution_count": 329,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 330,
   "metadata": {
    "Collapsed": "false"
   },
   "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>country_name</th>\n",
       "      <th>geo_id</th>\n",
       "      <th>country_territory_code</th>\n",
       "      <th>population_2019</th>\n",
       "      <th>continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AF</td>\n",
       "      <td>AFG</td>\n",
       "      <td>38041757.0</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10000</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10001</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10002</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10003</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10004</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>14645473.0</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10005 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      country_name geo_id country_territory_code  population_2019 continent\n",
       "0      Afghanistan     AF                    AFG       38041757.0      Asia\n",
       "1      Afghanistan     AF                    AFG       38041757.0      Asia\n",
       "2      Afghanistan     AF                    AFG       38041757.0      Asia\n",
       "3      Afghanistan     AF                    AFG       38041757.0      Asia\n",
       "4      Afghanistan     AF                    AFG       38041757.0      Asia\n",
       "...            ...    ...                    ...              ...       ...\n",
       "10000     Zimbabwe     ZW                    ZWE       14645473.0    Africa\n",
       "10001     Zimbabwe     ZW                    ZWE       14645473.0    Africa\n",
       "10002     Zimbabwe     ZW                    ZWE       14645473.0    Africa\n",
       "10003     Zimbabwe     ZW                    ZWE       14645473.0    Africa\n",
       "10004     Zimbabwe     ZW                    ZWE       14645473.0    Africa\n",
       "\n",
       "[10005 rows x 5 columns]"
      ]
     },
     "execution_count": 330,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_data[['country_name', 'geo_id', 'country_territory_code',\n",
    "       'population_2019', 'continent']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 331,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "raw_data[['report_date', 'cases_weekly', 'deaths_weekly', 'geo_id', 'notification_rate_per_100000_population_14-days']].to_sql(\n",
    "    'weekly_cases',\n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    index=False,\n",
    "    chunksize=500,\n",
    "    dtype={\n",
    "        \"report_date\": Date,\n",
    "        \"cases_weekly\": Integer,\n",
    "        \"deaths_weekly\": Integer,\n",
    "        \"geo_id\": String,\n",
    "        \"notification_rate_per_100000_population_14-days\": Float\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 332,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "raw_data[['country_name', 'geo_id', 'country_territory_code',\n",
    "       'population_2019', 'continent']].drop_duplicates().to_sql(\n",
    "    'countries',\n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    index=False,\n",
    "    chunksize=500,\n",
    "    dtype={\n",
    "        \"country_name\": Text,\n",
    "        \"geo_id\": String,\n",
    "        \"country_territory_code\": String,\n",
    "        \"population_2019\": Integer,\n",
    "        \"continent\": Text\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {
    "Collapsed": "false",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# %sql select geo_id from weekly_cases limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql alter table weekly_cases add primary key (geo_id, report_date);\n",
    "# alter table countries add primary key (geo_id);\n",
    "# alter table weekly_cases add foreign key (geo_id) references countries(geo_id);\n",
    "# alter table weekly_cases add culm_cases integer;\n",
    "# alter table weekly_cases add culm_deaths integer;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as connection:\n",
    "    connection.execute('alter table weekly_cases add primary key (geo_id, report_date)')\n",
    "    connection.execute('alter table countries add primary key (geo_id);')\n",
    "    connection.execute('alter table weekly_cases add foreign key (geo_id) references countries(geo_id);')\n",
    "    connection.execute('alter table weekly_cases add culm_cases integer;')\n",
    "    connection.execute('alter table weekly_cases add culm_deaths integer;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %sql select report_date, cases_weekly, country_name from weekly_cases join countries using (geo_id) order by report_date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %sql select report_date, cases_weekly, country_name from weekly_cases join countries on weekly_cases.geo_id = countries.geo_id order by report_date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>report_date</th>\n",
       "            <th>cases_weekly</th>\n",
       "            <th>deaths_weekly</th>\n",
       "            <th>geo_id</th>\n",
       "            <th>notification_rate_per_100000_population_14-days</th>\n",
       "            <th>culm_cases</th>\n",
       "            <th>culm_deaths</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2021-01-18</td>\n",
       "            <td>557</td>\n",
       "            <td>45</td>\n",
       "            <td>AF</td>\n",
       "            <td>3.24</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-11</td>\n",
       "            <td>675</td>\n",
       "            <td>71</td>\n",
       "            <td>AF</td>\n",
       "            <td>4.15</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-04</td>\n",
       "            <td>902</td>\n",
       "            <td>60</td>\n",
       "            <td>AF</td>\n",
       "            <td>7.61</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-12-28</td>\n",
       "            <td>1994</td>\n",
       "            <td>88</td>\n",
       "            <td>AF</td>\n",
       "            <td>7.19</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-12-21</td>\n",
       "            <td>740</td>\n",
       "            <td>111</td>\n",
       "            <td>AF</td>\n",
       "            <td>6.56</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-12-14</td>\n",
       "            <td>1757</td>\n",
       "            <td>71</td>\n",
       "            <td>AF</td>\n",
       "            <td>9.01</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-12-07</td>\n",
       "            <td>1672</td>\n",
       "            <td>137</td>\n",
       "            <td>AF</td>\n",
       "            <td>7.22</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-11-30</td>\n",
       "            <td>1073</td>\n",
       "            <td>68</td>\n",
       "            <td>AF</td>\n",
       "            <td>6.42</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-11-23</td>\n",
       "            <td>1368</td>\n",
       "            <td>69</td>\n",
       "            <td>AF</td>\n",
       "            <td>6.66</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2020-11-16</td>\n",
       "            <td>1164</td>\n",
       "            <td>61</td>\n",
       "            <td>AF</td>\n",
       "            <td>4.65</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2021, 1, 18), 557, 45, 'AF', 3.24, None, None),\n",
       " (datetime.date(2021, 1, 11), 675, 71, 'AF', 4.15, None, None),\n",
       " (datetime.date(2021, 1, 4), 902, 60, 'AF', 7.61, None, None),\n",
       " (datetime.date(2020, 12, 28), 1994, 88, 'AF', 7.19, None, None),\n",
       " (datetime.date(2020, 12, 21), 740, 111, 'AF', 6.56, None, None),\n",
       " (datetime.date(2020, 12, 14), 1757, 71, 'AF', 9.01, None, None),\n",
       " (datetime.date(2020, 12, 7), 1672, 137, 'AF', 7.22, None, None),\n",
       " (datetime.date(2020, 11, 30), 1073, 68, 'AF', 6.42, None, None),\n",
       " (datetime.date(2020, 11, 23), 1368, 69, 'AF', 6.66, None, None),\n",
       " (datetime.date(2020, 11, 16), 1164, 61, 'AF', 4.65, None, None)]"
      ]
     },
     "execution_count": 338,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from weekly_cases limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql\n",
    "# with culm as \n",
    "#     (select report_date, geo_id,\n",
    "#         sum(cases_weekly) over (partition by geo_id \n",
    "#                                 order by report_date) as culm_data\n",
    "#      from weekly_cases\n",
    "#     )\n",
    "# update weekly_cases\n",
    "#     set culm_cases = culm_data\n",
    "#     from culm\n",
    "#     where weekly_cases.report_date = culm.report_date and\n",
    "#           weekly_cases.geo_id = culm.geo_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "query_string = '''with culm as \n",
    "    (select report_date, geo_id,\n",
    "        sum(cases_weekly) over (partition by geo_id \n",
    "                                order by report_date) as culm_data\n",
    "     from weekly_cases\n",
    "    )\n",
    "update weekly_cases\n",
    "    set culm_cases = culm_data\n",
    "    from culm\n",
    "    where weekly_cases.report_date = culm.report_date and\n",
    "          weekly_cases.geo_id = culm.geo_id'''\n",
    "with engine.connect() as connection:\n",
    "    connection.execute(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql\n",
    "# with culm as \n",
    "#     (select report_date, geo_id,\n",
    "#         sum(deaths_weekly) over (partition by geo_id \n",
    "#                                 order by report_date) as culm_data\n",
    "#      from weekly_cases\n",
    "#     )\n",
    "# update weekly_cases\n",
    "#     set culm_deaths = culm_data\n",
    "#     from culm\n",
    "#     where weekly_cases.report_date = culm.report_date and\n",
    "#           weekly_cases.geo_id = culm.geo_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "query_string = '''with culm as \n",
    "    (select report_date, geo_id,\n",
    "        sum(deaths_weekly) over (partition by geo_id \n",
    "                                order by report_date) as culm_data\n",
    "     from weekly_cases\n",
    "    )\n",
    "update weekly_cases\n",
    "    set culm_deaths = culm_data\n",
    "    from culm\n",
    "    where weekly_cases.report_date = culm.report_date and\n",
    "          weekly_cases.geo_id = culm.geo_id'''\n",
    "with engine.connect() as connection:\n",
    "    connection.execute(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100 26053  100 26053    0     0  54277      0 --:--:-- --:--:-- --:--:-- 54277\n"
     ]
    }
   ],
   "source": [
    "uk_query_string = (\n",
    "\"https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&\"\n",
    "\"metric=covidOccupiedMVBeds&\"\n",
    "\"metric=newAdmissions&\"\n",
    "\"metric=newCasesBySpecimenDate&\"\n",
    "\"metric=hospitalCases&\"\n",
    "\"metric=newDeaths28DaysByPublishDate&\"\n",
    "\"format=csv\"\n",
    ")\n",
    "\n",
    "!curl \"$uk_query_string\" > uk_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100 20223  100 20223    0     0  78688      0 --:--:-- --:--:-- --:--:-- 78383\n"
     ]
    }
   ],
   "source": [
    "test_query_string = (\n",
    "\"https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&\"\n",
    "\"metric=newPCRTestsByPublishDate&\"\n",
    "\"metric=newTestsByPublishDate&\"\n",
    "\"metric=newPillarOneTwoTestsByPublishDate&\"\n",
    "\"format=csv\"\n",
    ")\n",
    "!curl \"$test_query_string\" > test_data.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# hospital_query_string = (\n",
    "# \"https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&\"\n",
    "# \"metric=newAdmissions&\"\n",
    "# \"format=csv\"\n",
    "# )\n",
    "# !curl \"$hospital_query_string\" > hospital_admissions.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# hospital_query_string = (\n",
    "# \"https://api.coronavirus.data.gov.uk/v1/data?\"\n",
    "# \"filters=areaName=United%2520Kingdom;areaType=overview&\"\n",
    "# \"structure=%7B%22date%22:%22date%22,%22areaName%22:%22areaName%22,%22areaType%22:%22areaType%22,\"\n",
    "# \"%22newAdmissions%22:%22newAdmissions%22,%22cumAdmissions%22:%22cumAdmissions%22%7D&format=csv\"\n",
    "# )\n",
    "    \n",
    "# !curl \"$hospital_query_string\" | gunzip > hospital_admissions.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "metadata": {
    "Collapsed": "false"
   },
   "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>date</th>\n",
       "      <th>areaType</th>\n",
       "      <th>areaCode</th>\n",
       "      <th>areaName</th>\n",
       "      <th>covidOccupiedMVBeds</th>\n",
       "      <th>newAdmissions</th>\n",
       "      <th>newCasesBySpecimenDate</th>\n",
       "      <th>hospitalCases</th>\n",
       "      <th>newDeaths28DaysByPublishDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-26</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-25</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>4032.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4482.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-24</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>4077.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14266.0</td>\n",
       "      <td>37561.0</td>\n",
       "      <td>610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-23</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>4066.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20495.0</td>\n",
       "      <td>37266.0</td>\n",
       "      <td>1348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>4076.0</td>\n",
       "      <td>3341.0</td>\n",
       "      <td>29094.0</td>\n",
       "      <td>38144.0</td>\n",
       "      <td>1401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>2020-01-06</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>387</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>388</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>389</th>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>393.0</td>\n",
       "      <td>364.0</td>\n",
       "      <td>821.0</td>\n",
       "      <td>4907.0</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>390 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date  areaType   areaCode        areaName  covidOccupiedMVBeds  \\\n",
       "0   2021-01-26  overview  K02000001  United Kingdom                  NaN   \n",
       "1   2021-01-25  overview  K02000001  United Kingdom               4032.0   \n",
       "2   2021-01-24  overview  K02000001  United Kingdom               4077.0   \n",
       "3   2021-01-23  overview  K02000001  United Kingdom               4066.0   \n",
       "4   2021-01-22  overview  K02000001  United Kingdom               4076.0   \n",
       "..         ...       ...        ...             ...                  ...   \n",
       "385 2020-01-06  overview  K02000001  United Kingdom                  NaN   \n",
       "386 2020-01-05  overview  K02000001  United Kingdom                  NaN   \n",
       "387 2020-01-04  overview  K02000001  United Kingdom                  NaN   \n",
       "388 2020-01-03  overview  K02000001  United Kingdom                  NaN   \n",
       "389 2020-06-14  overview  K02000001  United Kingdom                393.0   \n",
       "\n",
       "     newAdmissions  newCasesBySpecimenDate  hospitalCases  \\\n",
       "0              NaN                     NaN            NaN   \n",
       "1              NaN                  4482.0            NaN   \n",
       "2              NaN                 14266.0        37561.0   \n",
       "3              NaN                 20495.0        37266.0   \n",
       "4           3341.0                 29094.0        38144.0   \n",
       "..             ...                     ...            ...   \n",
       "385            NaN                     NaN            NaN   \n",
       "386            NaN                     NaN            NaN   \n",
       "387            NaN                     NaN            NaN   \n",
       "388            NaN                     NaN            NaN   \n",
       "389          364.0                   821.0         4907.0   \n",
       "\n",
       "     newDeaths28DaysByPublishDate  \n",
       "0                            1631  \n",
       "1                             592  \n",
       "2                             610  \n",
       "3                            1348  \n",
       "4                            1401  \n",
       "..                            ...  \n",
       "385                             0  \n",
       "386                             0  \n",
       "387                             0  \n",
       "388                             0  \n",
       "389                            27  \n",
       "\n",
       "[390 rows x 9 columns]"
      ]
     },
     "execution_count": 347,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uk_data = pd.read_csv('uk_data.csv', \n",
    "                       parse_dates=[0], dayfirst=True)\n",
    "uk_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "metadata": {
    "Collapsed": "false"
   },
   "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>date</th>\n",
       "      <th>areaType</th>\n",
       "      <th>areaCode</th>\n",
       "      <th>areaName</th>\n",
       "      <th>newPCRTestsByPublishDate</th>\n",
       "      <th>newTestsByPublishDate</th>\n",
       "      <th>newPillarOneTwoTestsByPublishDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-25</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>196510.0</td>\n",
       "      <td>546734</td>\n",
       "      <td>531104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-24</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>262111.0</td>\n",
       "      <td>412775</td>\n",
       "      <td>394479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-23</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>389209.0</td>\n",
       "      <td>486425</td>\n",
       "      <td>465231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>401075.0</td>\n",
       "      <td>631901</td>\n",
       "      <td>608829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-21</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>439408.0</td>\n",
       "      <td>668989</td>\n",
       "      <td>644537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>2020-04-03</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14629</td>\n",
       "      <td>14293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>2020-04-02</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13623</td>\n",
       "      <td>13457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11947</td>\n",
       "      <td>11924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>299</th>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11896</td>\n",
       "      <td>11896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>300</th>\n",
       "      <td>2020-06-14</td>\n",
       "      <td>overview</td>\n",
       "      <td>K02000001</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>69529.0</td>\n",
       "      <td>83965</td>\n",
       "      <td>66688</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>301 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date  areaType   areaCode        areaName  newPCRTestsByPublishDate  \\\n",
       "0   2021-01-25  overview  K02000001  United Kingdom                  196510.0   \n",
       "1   2021-01-24  overview  K02000001  United Kingdom                  262111.0   \n",
       "2   2021-01-23  overview  K02000001  United Kingdom                  389209.0   \n",
       "3   2021-01-22  overview  K02000001  United Kingdom                  401075.0   \n",
       "4   2021-01-21  overview  K02000001  United Kingdom                  439408.0   \n",
       "..         ...       ...        ...             ...                       ...   \n",
       "296 2020-04-03  overview  K02000001  United Kingdom                       NaN   \n",
       "297 2020-04-02  overview  K02000001  United Kingdom                       NaN   \n",
       "298 2020-04-01  overview  K02000001  United Kingdom                       NaN   \n",
       "299 2020-03-31  overview  K02000001  United Kingdom                       NaN   \n",
       "300 2020-06-14  overview  K02000001  United Kingdom                   69529.0   \n",
       "\n",
       "     newTestsByPublishDate  newPillarOneTwoTestsByPublishDate  \n",
       "0                   546734                             531104  \n",
       "1                   412775                             394479  \n",
       "2                   486425                             465231  \n",
       "3                   631901                             608829  \n",
       "4                   668989                             644537  \n",
       "..                     ...                                ...  \n",
       "296                  14629                              14293  \n",
       "297                  13623                              13457  \n",
       "298                  11947                              11924  \n",
       "299                  11896                              11896  \n",
       "300                  83965                              66688  \n",
       "\n",
       "[301 rows x 7 columns]"
      ]
     },
     "execution_count": 348,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data = pd.read_csv('test_data.csv', \n",
    "                       parse_dates=[0], dayfirst=True)\n",
    "test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'areaType', 'areaCode', 'areaName', 'newPCRTestsByPublishDate',\n",
       "       'newTestsByPublishDate', 'newPillarOneTwoTestsByPublishDate'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 349,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 350,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "uk_data = uk_data.merge(test_data[['date', 'newPCRTestsByPublishDate',\n",
    "       'newTestsByPublishDate', 'newPillarOneTwoTestsByPublishDate']], how='outer', on='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 351,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'areaType', 'areaCode', 'areaName', 'covidOccupiedMVBeds',\n",
       "       'newAdmissions', 'newCasesBySpecimenDate', 'hospitalCases',\n",
       "       'newDeaths28DaysByPublishDate', 'newPCRTestsByPublishDate',\n",
       "       'newTestsByPublishDate', 'newPillarOneTwoTestsByPublishDate'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 351,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uk_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 352,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "uk_data.rename(\n",
    "    columns={\n",
    "        'covidOccupiedMVBeds': 'ventilator_beds',\n",
    "        'newCasesBySpecimenDate': 'new_cases',\n",
    "        'hospitalCases': 'hospital_cases', \n",
    "        'newDeaths28DaysByPublishDate': 'new_deaths',\n",
    "        'newAdmissions': 'new_admissions',\n",
    "        'newPCRTestsByPublishDate': 'new_pcr_tests',\n",
    "        'newTestsByPublishDate': 'new_tests',\n",
    "        'newPillarOneTwoTestsByPublishDate': 'new_pillar_1_2_tests'\n",
    "    }, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 353,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                    datetime64[ns]\n",
       "areaType                        object\n",
       "areaCode                        object\n",
       "areaName                        object\n",
       "ventilator_beds                float64\n",
       "new_admissions                 float64\n",
       "new_cases                      float64\n",
       "hospital_cases                 float64\n",
       "new_deaths                       int64\n",
       "new_pcr_tests                  float64\n",
       "new_tests                      float64\n",
       "new_pillar_1_2_tests           float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 353,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "uk_data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 354,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'areaType', 'areaCode', 'areaName', 'ventilator_beds',\n",
       "       'new_admissions', 'new_cases', 'hospital_cases', 'new_deaths',\n",
       "       'new_pcr_tests', 'new_tests', 'new_pillar_1_2_tests'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 354,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    " uk_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 355,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "uk_data[['date', \n",
    "         'hospital_cases', 'ventilator_beds',\n",
    "         'new_cases', 'new_deaths', \n",
    "         'hospital_cases', 'new_admissions',\n",
    "         'new_pcr_tests', 'new_tests', 'new_pillar_1_2_tests'\n",
    "        ]].to_sql(\n",
    "    'uk_data',\n",
    "    engine,\n",
    "    if_exists='replace',\n",
    "    index=False,\n",
    "    chunksize=500,\n",
    "    dtype={\n",
    "        \"date\": Date,\n",
    "        \"hospital_cases\": Integer,\n",
    "        \"ventilator_beds\": Integer,\n",
    "        \"new_cases\": Integer,\n",
    "        \"hospital_cases\": Integer,\n",
    "        \"new_deaths\": Integer,\n",
    "        \"new_admissions\": Integer,\n",
    "        'new_pcr_tests': Integer, \n",
    "        'new_tests': Integer, \n",
    "        'new_pillar_1_2_tests': Integer\n",
    "    }\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 356,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %sql select * from uk_data order by date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "query_string = '''drop table if exists uk_data_7;\n",
    "create table uk_data_7 \n",
    "(date date primary key,\n",
    " hospital_cases real,\n",
    " ventilator_beds real,\n",
    " new_cases real,\n",
    " new_deaths real,\n",
    " new_admissions real,\n",
    " new_pcr_tests real,\n",
    " new_tests real,\n",
    " new_pillar_1_2_tests real\n",
    ");'''\n",
    "\n",
    "with engine.connect() as connection:\n",
    "    connection.execute(query_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "metadata": {},
   "outputs": [],
   "source": [
    "update_string = '''with ownd as (\n",
    "    select date,\n",
    "        avg(hospital_cases) over wnd as w_hospital_cases,\n",
    "        avg(ventilator_beds) over wnd as w_ventilator_beds,\n",
    "        avg(new_cases) over wnd as w_new_cases,\n",
    "        avg(new_deaths) over wnd as w_new_deaths,\n",
    "        avg(new_admissions) over wnd as w_new_admissions,\n",
    "        avg(new_pcr_tests) over wnd as w_new_pcr_tests,\n",
    "        avg(new_tests) over wnd as w_new_tests,\n",
    "        avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,\n",
    "        count(*) over wnd as w_size\n",
    "    from uk_data\n",
    "    window wnd as (\n",
    "        order by uk_data.date\n",
    "        rows between 3 preceding and 3 following\n",
    "    )\n",
    ")\n",
    "insert into uk_data_7(date, \n",
    "    hospital_cases, \n",
    "    ventilator_beds, \n",
    "    new_cases,\n",
    "    new_deaths,\n",
    "    new_admissions, \n",
    "    new_pcr_tests, \n",
    "    new_tests, \n",
    "    new_pillar_1_2_tests\n",
    "    )\n",
    "(select date,\n",
    "        w_hospital_cases,\n",
    "        w_ventilator_beds,\n",
    "        w_new_cases,\n",
    "        w_new_deaths,\n",
    "        w_new_admissions,\n",
    "        w_new_pcr_tests,\n",
    "        w_new_tests,\n",
    "        w_new_pillar_1_2_tests\n",
    "    from ownd\n",
    "    where w_size = 7\n",
    ")'''\n",
    "with engine.connect() as connection:\n",
    "    connection.execute(update_string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 398,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql insert into uk_data_7(date, ventilator_beds, new_cases, hospital_cases, new_deaths, new_admissions)\n",
    "# values (\n",
    "#     select date, \n",
    "#     avg(ventilator_beds) over (order by date rows between 6 preceding and current row)\n",
    "#     from uk_data\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 399,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# query_string = '''insert into uk_data_7(date, hospital_cases)\n",
    "#     select uk_data.date, \n",
    "#         avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as hospital_cases\n",
    "#     from uk_data'''\n",
    "# with engine.connect() as connection:\n",
    "#     connection.execute(query_string)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 360,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql \n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 6 preceding and current row) as nc7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set ventilator_beds = nc7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 361,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 361,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.ventilator_beds) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set ventilator_beds = nc7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 362,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 362,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as nc7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_cases = nc7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 363,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 363,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.hospital_cases) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set hospital_cases = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 364,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 364,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_deaths) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_deaths = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 365,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 365,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_admissions) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_admissions = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 366,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 366,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_pcr_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_pcr_tests = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 367,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "390 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 367,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_tests = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql\n",
    "# with m7 as \n",
    "#     (select uk_data.date as date7, \n",
    "#         avg(uk_data.new_pillar_1_2_tests) over (order by uk_data.date rows between 3 preceding and 3 following) as d7\n",
    "#      from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_pillar_1_2_tests = d7\n",
    "#     from m7\n",
    "#     where uk_data_7.date = m7.date7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 310,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with wnd as\n",
    "#     (   select date, \n",
    "#              avg(new_pillar_1_2_tests)\n",
    "#                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests\n",
    "#         from uk_data\n",
    "#     )\n",
    "# update uk_data_7\n",
    "#     set new_pillar_1_2_tests = wnd.a_new_pillar_1_2_tests\n",
    "#     from wnd\n",
    "#     where uk_data_7.date = wnd.date\n",
    "#     and (select count(*) from wnd) = 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 379,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>date</th>\n",
       "            <th>new_pillar_1_2_tests</th>\n",
       "            <th>a_new_pillar_1_2_tests</th>\n",
       "            <th>a_count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2021-01-26</td>\n",
       "            <td>None</td>\n",
       "            <td>463604.666666666667</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-25</td>\n",
       "            <td>None</td>\n",
       "            <td>499910.750000000000</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-24</td>\n",
       "            <td>None</td>\n",
       "            <td>528836.000000000000</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-23</td>\n",
       "            <td>None</td>\n",
       "            <td>543566.833333333333</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-22</td>\n",
       "            <td>None</td>\n",
       "            <td>545780.285714285714</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-21</td>\n",
       "            <td>None</td>\n",
       "            <td>546626.571428571429</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-20</td>\n",
       "            <td>None</td>\n",
       "            <td>546806.285714285714</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-19</td>\n",
       "            <td>None</td>\n",
       "            <td>547402.428571428571</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-18</td>\n",
       "            <td>None</td>\n",
       "            <td>542617.857142857143</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-17</td>\n",
       "            <td>None</td>\n",
       "            <td>545088.142857142857</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2021, 1, 26), None, Decimal('463604.666666666667'), 3),\n",
       " (datetime.date(2021, 1, 25), None, Decimal('499910.750000000000'), 4),\n",
       " (datetime.date(2021, 1, 24), None, Decimal('528836.000000000000'), 5),\n",
       " (datetime.date(2021, 1, 23), None, Decimal('543566.833333333333'), 6),\n",
       " (datetime.date(2021, 1, 22), None, Decimal('545780.285714285714'), 7),\n",
       " (datetime.date(2021, 1, 21), None, Decimal('546626.571428571429'), 7),\n",
       " (datetime.date(2021, 1, 20), None, Decimal('546806.285714285714'), 7),\n",
       " (datetime.date(2021, 1, 19), None, Decimal('547402.428571428571'), 7),\n",
       " (datetime.date(2021, 1, 18), None, Decimal('542617.857142857143'), 7),\n",
       " (datetime.date(2021, 1, 17), None, Decimal('545088.142857142857'), 7)]"
      ]
     },
     "execution_count": 379,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with wnd as\n",
    "#     (   select date, \n",
    "#              avg(new_pillar_1_2_tests)\n",
    "#                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_new_pillar_1_2_tests,\n",
    "#              count(new_pillar_1_2_tests)\n",
    "#                 over (order by uk_data.date rows between 3 preceding and 3 following) as a_count\n",
    "#         from uk_data\n",
    "#     )\n",
    "# select uk_data_7.date, new_pillar_1_2_tests, wnd.a_new_pillar_1_2_tests, wnd.a_count\n",
    "#     from uk_data_7, wnd\n",
    "#     where uk_data_7.date = wnd.date\n",
    "#     order by uk_data_7.date desc limit 10\n",
    "    \n",
    "# select date, \n",
    "#     count(*) over wnd as w_size\n",
    "# from uk_data\n",
    "# window wnd as (\n",
    "#     order by uk_data.date\n",
    "#     rows between 3 preceding and 3 following\n",
    "# );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": [
    "# %%sql\n",
    "# select date, \n",
    "#     count(*) over wnd as w_size\n",
    "# from uk_data\n",
    "# window wnd as (\n",
    "#     order by uk_data.date\n",
    "#     rows between 3 preceding and 3 following\n",
    "# )\n",
    "# order by date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 407,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with ownd as (\n",
    "#     select date,\n",
    "#         avg(hospital_cases) over wnd as w_hospital_cases,\n",
    "#         avg(ventilator_beds) over wnd as w_ventilator_beds,\n",
    "#         avg(new_cases) over wnd as w_new_cases,\n",
    "#         avg(new_deaths) over wnd as w_new_deaths,\n",
    "#         avg(new_admissions) over wnd as w_new_admissions,\n",
    "#         avg(new_pcr_tests) over wnd as w_new_pcr_tests,\n",
    "#         avg(new_tests) over wnd as w_new_tests,\n",
    "#         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,\n",
    "#         count(*) over wnd as w_size\n",
    "#     from uk_data\n",
    "#     window wnd as (\n",
    "#         order by uk_data.date\n",
    "#         rows between 3 preceding and 3 following\n",
    "#     ))\n",
    "# insert into uk_data_7(date, \n",
    "#     hospital_cases, \n",
    "#     ventilator_beds, \n",
    "#     new_cases,\n",
    "#     new_deaths,\n",
    "#     new_admissions, \n",
    "#     new_pcr_tests, \n",
    "#     new_tests, \n",
    "#     new_pillar_1_2_tests\n",
    "#     )\n",
    "# (select date,\n",
    "#         avg(hospital_cases) over wnd as w_hospital_cases,\n",
    "#         avg(ventilator_beds) over wnd as w_ventilator_beds,\n",
    "#         avg(new_cases) over wnd as w_new_cases,\n",
    "#         avg(new_deaths) over wnd as w_new_deaths,\n",
    "#         avg(new_admissions) over wnd as w_new_admissions,\n",
    "#         avg(new_pcr_tests) over wnd as w_new_pcr_tests,\n",
    "#         avg(new_tests) over wnd as w_new_tests,\n",
    "#         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,\n",
    "#         count(*) over wnd as w_size\n",
    "#     from uk_data\n",
    "#     window wnd as (\n",
    "#         order by uk_data.date\n",
    "#         rows between 3 preceding and 3 following\n",
    "#     )\n",
    "# )\n",
    "#     set date = ownd.date,\n",
    "#         hospital_cases = w_hospital_cases,\n",
    "#         ventilator_beds = w_ventilator_beds,\n",
    "#         new_cases = w_new_cases,\n",
    "#         new_deaths = w_new_deaths,\n",
    "#         new_admissions = w_new_admissions,\n",
    "#         new_pcr_tests = w_new_pcr_tests,\n",
    "#         new_tests = w_new_tests,\n",
    "#         new_pillar_1_2_tests = w_new_pillar_1_2_tests\n",
    "#     from ownd\n",
    "#     where w_size = 7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 417,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "384 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 417,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# %%sql\n",
    "# with ownd as (\n",
    "#     select date,\n",
    "#         avg(hospital_cases) over wnd as w_hospital_cases,\n",
    "#         avg(ventilator_beds) over wnd as w_ventilator_beds,\n",
    "#         avg(new_cases) over wnd as w_new_cases,\n",
    "#         avg(new_deaths) over wnd as w_new_deaths,\n",
    "#         avg(new_admissions) over wnd as w_new_admissions,\n",
    "#         avg(new_pcr_tests) over wnd as w_new_pcr_tests,\n",
    "#         avg(new_tests) over wnd as w_new_tests,\n",
    "#         avg(new_pillar_1_2_tests) over wnd as w_new_pillar_1_2_tests,\n",
    "#         count(*) over wnd as w_size\n",
    "#     from uk_data\n",
    "#     window wnd as (\n",
    "#         order by uk_data.date\n",
    "#         rows between 3 preceding and 3 following\n",
    "#     )\n",
    "# )\n",
    "# insert into uk_data_7(date, \n",
    "#     hospital_cases, \n",
    "#     ventilator_beds, \n",
    "#     new_cases,\n",
    "#     new_deaths,\n",
    "#     new_admissions, \n",
    "#     new_pcr_tests, \n",
    "#     new_tests, \n",
    "#     new_pillar_1_2_tests\n",
    "#     )\n",
    "# (select date,\n",
    "#         w_hospital_cases,\n",
    "#         w_ventilator_beds,\n",
    "#         w_new_cases,\n",
    "#         w_new_deaths,\n",
    "#         w_new_admissions,\n",
    "#         w_new_pcr_tests,\n",
    "#         w_new_tests,\n",
    "#         w_new_pillar_1_2_tests\n",
    "#     from ownd\n",
    "#     where w_size = 7\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 427,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>date</th>\n",
       "            <th>hospital_cases</th>\n",
       "            <th>ventilator_beds</th>\n",
       "            <th>new_cases</th>\n",
       "            <th>new_deaths</th>\n",
       "            <th>new_admissions</th>\n",
       "            <th>new_pcr_tests</th>\n",
       "            <th>new_tests</th>\n",
       "            <th>new_pillar_1_2_tests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2021-01-23</td>\n",
       "            <td>37915.6</td>\n",
       "            <td>4027.3333</td>\n",
       "            <td>22463.666</td>\n",
       "            <td>1241.7142</td>\n",
       "            <td>3651.6667</td>\n",
       "            <td>353735.34</td>\n",
       "            <td>565312.3</td>\n",
       "            <td>543566.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-22</td>\n",
       "            <td>38057.168</td>\n",
       "            <td>4015.8572</td>\n",
       "            <td>24870.428</td>\n",
       "            <td>1238.7142</td>\n",
       "            <td>3771.75</td>\n",
       "            <td>354323.16</td>\n",
       "            <td>567830.3</td>\n",
       "            <td>545780.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-21</td>\n",
       "            <td>38217.715</td>\n",
       "            <td>3999.2856</td>\n",
       "            <td>30620.428</td>\n",
       "            <td>1239.7142</td>\n",
       "            <td>3828.2</td>\n",
       "            <td>363750.16</td>\n",
       "            <td>569716.7</td>\n",
       "            <td>546626.56</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-20</td>\n",
       "            <td>38294.0</td>\n",
       "            <td>3969.8572</td>\n",
       "            <td>32707.428</td>\n",
       "            <td>1248.4286</td>\n",
       "            <td>3811.0</td>\n",
       "            <td>372037.44</td>\n",
       "            <td>570458.1</td>\n",
       "            <td>546806.3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-19</td>\n",
       "            <td>38348.43</td>\n",
       "            <td>3939.5715</td>\n",
       "            <td>34143.285</td>\n",
       "            <td>1240.8572</td>\n",
       "            <td>3825.8572</td>\n",
       "            <td>374771.16</td>\n",
       "            <td>571336.56</td>\n",
       "            <td>547402.44</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-18</td>\n",
       "            <td>38338.145</td>\n",
       "            <td>3898.5715</td>\n",
       "            <td>35766.57</td>\n",
       "            <td>1223.5714</td>\n",
       "            <td>3872.8572</td>\n",
       "            <td>377019.28</td>\n",
       "            <td>566901.1</td>\n",
       "            <td>542617.9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-17</td>\n",
       "            <td>38292.285</td>\n",
       "            <td>3857.4285</td>\n",
       "            <td>37337.855</td>\n",
       "            <td>1217.5714</td>\n",
       "            <td>3939.0</td>\n",
       "            <td>389469.84</td>\n",
       "            <td>571242.56</td>\n",
       "            <td>545088.1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-16</td>\n",
       "            <td>38119.855</td>\n",
       "            <td>3810.7144</td>\n",
       "            <td>38824.0</td>\n",
       "            <td>1181.0</td>\n",
       "            <td>3967.2856</td>\n",
       "            <td>395066.72</td>\n",
       "            <td>569445.0</td>\n",
       "            <td>541938.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-15</td>\n",
       "            <td>37858.715</td>\n",
       "            <td>3746.2856</td>\n",
       "            <td>40268.57</td>\n",
       "            <td>1128.5714</td>\n",
       "            <td>4029.2856</td>\n",
       "            <td>403526.72</td>\n",
       "            <td>570188.7</td>\n",
       "            <td>540199.9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-14</td>\n",
       "            <td>37505.57</td>\n",
       "            <td>3667.2856</td>\n",
       "            <td>41989.145</td>\n",
       "            <td>1118.5714</td>\n",
       "            <td>4065.2856</td>\n",
       "            <td>412496.44</td>\n",
       "            <td>567178.7</td>\n",
       "            <td>537417.1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2021, 1, 23), 37915.6, 4027.3333, 22463.666, 1241.7142, 3651.6667, 353735.34, 565312.3, 543566.8),\n",
       " (datetime.date(2021, 1, 22), 38057.168, 4015.8572, 24870.428, 1238.7142, 3771.75, 354323.16, 567830.3, 545780.3),\n",
       " (datetime.date(2021, 1, 21), 38217.715, 3999.2856, 30620.428, 1239.7142, 3828.2, 363750.16, 569716.7, 546626.56),\n",
       " (datetime.date(2021, 1, 20), 38294.0, 3969.8572, 32707.428, 1248.4286, 3811.0, 372037.44, 570458.1, 546806.3),\n",
       " (datetime.date(2021, 1, 19), 38348.43, 3939.5715, 34143.285, 1240.8572, 3825.8572, 374771.16, 571336.56, 547402.44),\n",
       " (datetime.date(2021, 1, 18), 38338.145, 3898.5715, 35766.57, 1223.5714, 3872.8572, 377019.28, 566901.1, 542617.9),\n",
       " (datetime.date(2021, 1, 17), 38292.285, 3857.4285, 37337.855, 1217.5714, 3939.0, 389469.84, 571242.56, 545088.1),\n",
       " (datetime.date(2021, 1, 16), 38119.855, 3810.7144, 38824.0, 1181.0, 3967.2856, 395066.72, 569445.0, 541938.0),\n",
       " (datetime.date(2021, 1, 15), 37858.715, 3746.2856, 40268.57, 1128.5714, 4029.2856, 403526.72, 570188.7, 540199.9),\n",
       " (datetime.date(2021, 1, 14), 37505.57, 3667.2856, 41989.145, 1118.5714, 4065.2856, 412496.44, 567178.7, 537417.1)]"
      ]
     },
     "execution_count": 427,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from uk_data_7 order by date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 428,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://covid:***@localhost/covid\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>date</th>\n",
       "            <th>hospital_cases</th>\n",
       "            <th>ventilator_beds</th>\n",
       "            <th>new_cases</th>\n",
       "            <th>new_deaths</th>\n",
       "            <th>new_admissions</th>\n",
       "            <th>new_pcr_tests</th>\n",
       "            <th>new_tests</th>\n",
       "            <th>new_pillar_1_2_tests</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2021-01-26</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>1631</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-25</td>\n",
       "            <td>None</td>\n",
       "            <td>4032</td>\n",
       "            <td>4482</td>\n",
       "            <td>592</td>\n",
       "            <td>None</td>\n",
       "            <td>196510</td>\n",
       "            <td>546734</td>\n",
       "            <td>531104</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-24</td>\n",
       "            <td>37561</td>\n",
       "            <td>4077</td>\n",
       "            <td>14266</td>\n",
       "            <td>610</td>\n",
       "            <td>None</td>\n",
       "            <td>262111</td>\n",
       "            <td>412775</td>\n",
       "            <td>394479</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-23</td>\n",
       "            <td>37266</td>\n",
       "            <td>4066</td>\n",
       "            <td>20495</td>\n",
       "            <td>1348</td>\n",
       "            <td>None</td>\n",
       "            <td>389209</td>\n",
       "            <td>486425</td>\n",
       "            <td>465231</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-22</td>\n",
       "            <td>38144</td>\n",
       "            <td>4076</td>\n",
       "            <td>29094</td>\n",
       "            <td>1401</td>\n",
       "            <td>3341</td>\n",
       "            <td>401075</td>\n",
       "            <td>631901</td>\n",
       "            <td>608829</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-21</td>\n",
       "            <td>37957</td>\n",
       "            <td>3960</td>\n",
       "            <td>31430</td>\n",
       "            <td>1290</td>\n",
       "            <td>3598</td>\n",
       "            <td>439408</td>\n",
       "            <td>668989</td>\n",
       "            <td>644537</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-20</td>\n",
       "            <td>38650</td>\n",
       "            <td>3953</td>\n",
       "            <td>35015</td>\n",
       "            <td>1820</td>\n",
       "            <td>4016</td>\n",
       "            <td>434099</td>\n",
       "            <td>645050</td>\n",
       "            <td>617221</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-19</td>\n",
       "            <td>38765</td>\n",
       "            <td>3947</td>\n",
       "            <td>39311</td>\n",
       "            <td>1610</td>\n",
       "            <td>4132</td>\n",
       "            <td>357850</td>\n",
       "            <td>582938</td>\n",
       "            <td>559061</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-18</td>\n",
       "            <td>39181</td>\n",
       "            <td>3916</td>\n",
       "            <td>44732</td>\n",
       "            <td>599</td>\n",
       "            <td>4054</td>\n",
       "            <td>262499</td>\n",
       "            <td>559939</td>\n",
       "            <td>537028</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2021-01-17</td>\n",
       "            <td>38095</td>\n",
       "            <td>3871</td>\n",
       "            <td>28875</td>\n",
       "            <td>671</td>\n",
       "            <td>3725</td>\n",
       "            <td>320122</td>\n",
       "            <td>417965</td>\n",
       "            <td>395737</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2021, 1, 26), None, None, None, 1631, None, None, None, None),\n",
       " (datetime.date(2021, 1, 25), None, 4032, 4482, 592, None, 196510, 546734, 531104),\n",
       " (datetime.date(2021, 1, 24), 37561, 4077, 14266, 610, None, 262111, 412775, 394479),\n",
       " (datetime.date(2021, 1, 23), 37266, 4066, 20495, 1348, None, 389209, 486425, 465231),\n",
       " (datetime.date(2021, 1, 22), 38144, 4076, 29094, 1401, 3341, 401075, 631901, 608829),\n",
       " (datetime.date(2021, 1, 21), 37957, 3960, 31430, 1290, 3598, 439408, 668989, 644537),\n",
       " (datetime.date(2021, 1, 20), 38650, 3953, 35015, 1820, 4016, 434099, 645050, 617221),\n",
       " (datetime.date(2021, 1, 19), 38765, 3947, 39311, 1610, 4132, 357850, 582938, 559061),\n",
       " (datetime.date(2021, 1, 18), 39181, 3916, 44732, 599, 4054, 262499, 559939, 537028),\n",
       " (datetime.date(2021, 1, 17), 38095, 3871, 28875, 671, 3725, 320122, 417965, 395737)]"
      ]
     },
     "execution_count": 428,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from uk_data order by date desc limit 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "Collapsed": "false"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "formats": "ipynb,md"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}