{ "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": 192, "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": [ "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\n", "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 193, "metadata": { "Collapsed": "false" }, "outputs": [], "source": [ "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'" ] }, { "cell_type": "code", "execution_count": 194, "metadata": { "Collapsed": "false" }, "outputs": [ { "data": { "text/plain": [ "'Connected: covid@covid'" ] }, "execution_count": 194, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql $connection_string" ] }, { "cell_type": "code", "execution_count": 195, "metadata": { "Collapsed": "false" }, "outputs": [], "source": [ "# DEATH_COUNT_THRESHOLD = 10\n", "COUNTRIES_CORE = tuple('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": "markdown", "metadata": { "Collapsed": "false" }, "source": [ "# Write results to summary file" ] }, { "cell_type": "code", "execution_count": 196, "metadata": { "Collapsed": "false" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://covid:***@localhost/covid\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "datetime.date(2021, 1, 26)" ] }, "execution_count": 196, "metadata": {}, "output_type": "execute_result" } ], "source": [ "last_uk_date = %sql select date from uk_data order by date desc limit 1\n", "last_uk_date = last_uk_date[0][0]\n", "last_uk_date" ] }, { "cell_type": "code", "execution_count": 197, "metadata": { "Collapsed": "false" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://covid:***@localhost/covid\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "datetime.date(2021, 1, 18)" ] }, "execution_count": 197, "metadata": {}, "output_type": "execute_result" } ], "source": [ "last_intl_date = %sql select report_date from weekly_cases order by report_date desc limit 1\n", "last_intl_date = last_intl_date[0][0]\n", "last_intl_date" ] }, { "cell_type": "code", "execution_count": 198, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://covid:***@localhost/covid\n", "390 rows affected.\n", "Returning data to local variable results\n" ] } ], "source": [ "%%sql results << select date, new_cases, new_deaths \n", "from uk_data \n", "order by date" ] }, { "cell_type": "code", "execution_count": 199, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | new_cases | \n", "new_deaths | \n", "
---|---|---|
date | \n", "\n", " | \n", " |
2021-01-17 | \n", "28875.0 | \n", "671 | \n", "
2021-01-18 | \n", "44732.0 | \n", "599 | \n", "
2021-01-19 | \n", "39311.0 | \n", "1610 | \n", "
2021-01-20 | \n", "35015.0 | \n", "1820 | \n", "
2021-01-21 | \n", "31430.0 | \n", "1290 | \n", "
2021-01-22 | \n", "29094.0 | \n", "1401 | \n", "
2021-01-23 | \n", "20495.0 | \n", "1348 | \n", "
2021-01-24 | \n", "14266.0 | \n", "610 | \n", "
2021-01-25 | \n", "4482.0 | \n", "592 | \n", "
2021-01-26 | \n", "NaN | \n", "1631 | \n", "