{ "cells": [ { "cell_type": "code", "execution_count": 56, "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 sqlalchemy\n", "\n", "import matplotlib as mpl\n", "import matplotlib.pyplot as plt\n", "%matplotlib inline\n", "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 57, "metadata": { "Collapsed": "false" }, "outputs": [], "source": [ "connection_string = 'postgresql://covid:3NbjJTkT63@localhost/covid'" ] }, { "cell_type": "code", "execution_count": 58, "metadata": { "Collapsed": "false" }, "outputs": [ { "data": { "text/plain": [ "'Connected: covid@covid'" ] }, "execution_count": 58, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql $connection_string" ] }, { "cell_type": "code", "execution_count": 59, "metadata": {}, "outputs": [], "source": [ "conn = sqlalchemy.create_engine(connection_string)" ] }, { "cell_type": "code", "execution_count": 69, "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_deaths, uk_data_7.new_deaths as new_deaths_7\n", " from uk_data left outer join uk_data_7 using (date)\n", " order by uk_data.date" ] }, { "cell_type": "code", "execution_count": 70, "metadata": { "Collapsed": "false" }, "outputs": [ { "data": { "text/html": [ "
\n", " | new_cases | \n", "new_cases_7 | \n", "new_deaths | \n", "new_deaths_7 | \n", "
---|---|---|---|---|
date | \n", "\n", " | \n", " | \n", " | \n", " |
2021-01-17 | \n", "28875.0 | \n", "37337.855 | \n", "671 | \n", "1217.5714 | \n", "
2021-01-18 | \n", "44732.0 | \n", "35766.570 | \n", "599 | \n", "1223.5714 | \n", "
2021-01-19 | \n", "39311.0 | \n", "34143.285 | \n", "1610 | \n", "1240.8572 | \n", "
2021-01-20 | \n", "35015.0 | \n", "32707.428 | \n", "1820 | \n", "1248.4286 | \n", "
2021-01-21 | \n", "31430.0 | \n", "30620.428 | \n", "1290 | \n", "1239.7142 | \n", "
2021-01-22 | \n", "29094.0 | \n", "24870.428 | \n", "1401 | \n", "1238.7142 | \n", "
2021-01-23 | \n", "20495.0 | \n", "22463.666 | \n", "1348 | \n", "1241.7142 | \n", "
2021-01-24 | \n", "14266.0 | \n", "NaN | \n", "610 | \n", "NaN | \n", "
2021-01-25 | \n", "4482.0 | \n", "NaN | \n", "592 | \n", "NaN | \n", "
2021-01-26 | \n", "NaN | \n", "NaN | \n", "1631 | \n", "NaN | \n", "