{ "cells": [ { "cell_type": "code", "execution_count": 91, "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", "import matplotlib.animation as ani\n", "%matplotlib inline\n", "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 92, "metadata": { "Collapsed": "false" }, "outputs": [], "source": [ "chart_start_date = '2020-09-15'" ] }, { "cell_type": "code", "execution_count": 93, "metadata": { "Collapsed": "false" }, "outputs": [], "source": [ "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'" ] }, { "cell_type": "code", "execution_count": 94, "metadata": { "Collapsed": "false" }, "outputs": [ { "data": { "text/plain": [ "'Connected: covid@covid'" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql $connection_string" ] }, { "cell_type": "code", "execution_count": 109, "metadata": { "Collapsed": "false" }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://covid:***@localhost/covid\n", "390 rows affected.\n", "Returning data to local variable res\n" ] } ], "source": [ "%%sql res << select uk_data.date, \n", " uk_data.new_cases, uk_data_7.new_cases as new_cases_7,\n", " uk_data.new_tests, uk_data_7.new_tests as new_tests_7,\n", " uk_data.new_pcr_tests, uk_data_7.new_pcr_tests as new_pcr_7,\n", " uk_data.new_pillar_1_2_tests as new_pillar_12, uk_data_7.new_pillar_1_2_tests as new_pillar_12_7,\n", " uk_data.new_cases::float / uk_data.new_tests as fraction_positive,\n", " uk_data_7.new_cases / uk_data_7.new_tests as fraction_positive_7\n", " from uk_data left outer join uk_data_7 using (date)\n", " order by uk_data.date" ] }, { "cell_type": "code", "execution_count": 110, "metadata": { "Collapsed": "false" }, "outputs": [ { "data": { "text/html": [ "
\n", " | new_cases | \n", "new_cases_7 | \n", "new_tests | \n", "new_tests_7 | \n", "new_pcr_tests | \n", "new_pcr_7 | \n", "new_pillar_12 | \n", "new_pillar_12_7 | \n", "fraction_positive | \n", "fraction_positive_7 | \n", "
---|---|---|---|---|---|---|---|---|---|---|
date | \n", "\n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " | \n", " |
2021-01-17 | \n", "28875.0 | \n", "37337.855 | \n", "417965.0 | \n", "571242.56 | \n", "320122.0 | \n", "389469.84 | \n", "395737.0 | \n", "545088.10 | \n", "0.069085 | \n", "0.065363 | \n", "
2021-01-18 | \n", "44732.0 | \n", "35766.570 | \n", "559939.0 | \n", "566901.10 | \n", "262499.0 | \n", "377019.28 | \n", "537028.0 | \n", "542617.90 | \n", "0.079887 | \n", "0.063091 | \n", "
2021-01-19 | \n", "39311.0 | \n", "34143.285 | \n", "582938.0 | \n", "571336.56 | \n", "357850.0 | \n", "374771.16 | \n", "559061.0 | \n", "547402.44 | \n", "0.067436 | \n", "0.059760 | \n", "
2021-01-20 | \n", "35015.0 | \n", "32707.428 | \n", "645050.0 | \n", "570458.10 | \n", "434099.0 | \n", "372037.44 | \n", "617221.0 | \n", "546806.30 | \n", "0.054283 | \n", "0.057335 | \n", "
2021-01-21 | \n", "31430.0 | \n", "30620.428 | \n", "668989.0 | \n", "569716.70 | \n", "439408.0 | \n", "363750.16 | \n", "644537.0 | \n", "546626.56 | \n", "0.046981 | \n", "0.053747 | \n", "
2021-01-22 | \n", "29094.0 | \n", "24870.428 | \n", "631901.0 | \n", "567830.30 | \n", "401075.0 | \n", "354323.16 | \n", "608829.0 | \n", "545780.30 | \n", "0.046042 | \n", "0.043799 | \n", "
2021-01-23 | \n", "20495.0 | \n", "22463.666 | \n", "486425.0 | \n", "565312.30 | \n", "389209.0 | \n", "353735.34 | \n", "465231.0 | \n", "543566.80 | \n", "0.042134 | \n", "0.039737 | \n", "
2021-01-24 | \n", "14266.0 | \n", "NaN | \n", "412775.0 | \n", "NaN | \n", "262111.0 | \n", "NaN | \n", "394479.0 | \n", "NaN | \n", "0.034561 | \n", "NaN | \n", "
2021-01-25 | \n", "4482.0 | \n", "NaN | \n", "546734.0 | \n", "NaN | \n", "196510.0 | \n", "NaN | \n", "531104.0 | \n", "NaN | \n", "0.008198 | \n", "NaN | \n", "
2021-01-26 | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "NaN | \n", "