X-Git-Url: https://git.njae.me.uk/?p=covid19.git;a=blobdiff_plain;f=data_import.ipynb;fp=data_import.ipynb;h=0000000000000000000000000000000000000000;hp=31d7ef97604b9a192a59c8d9656f3e4e7c52dfb0;hb=6fc69eb203d25465f8ccef719bc4d662dc66863a;hpb=4feace1e2a5076f3efa0fd65b31411ac511efbb1

diff --git a/data_import.ipynb b/data_import.ipynb
deleted file mode 100644
index 31d7ef9..0000000
--- a/data_import.ipynb
+++ /dev/null
@@ -1,2933 +0,0 @@
-{
- "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
-}