covid.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML, display\n",
    "\n",
    "HTML('''<script>\n",
    "code_show=true;\n",
    "function code_toggle() {\n",
    "  if (code_show){\n",
    "  $('div.input').hide();\n",
    "  } else {\n",
    "  $('div.input').show();\n",
    "  }\n",
    "  code_show = !code_show\n",
    "}\n",
    "$( document ).ready(code_toggle);\n",
    "</script>\n",
    "<form action=\"javascript:code_toggle()\">\n",
    "<input type=\"submit\" value=\"Reveal/Hide Code\"></form>''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Environment creation commands:\n",
    "# conda create -y -n py3 python=3.7\n",
    "# conda activate py3\n",
    "# conda install -y nb_conda\n",
    "# pip install altair vega_datasets altair_saver selenium\n",
    "# pip install matplotlib seaborn datetime\n",
    "# pip install python-pptx\n",
    "# pip install pycountry_convert requests\n",
    "# # Check version of ChromeDriver according to version of Chrome\n",
    "# wget https://chromedriver.storage.googleapis.com/87.0.4280.20/chromedriver_mac64.zip\n",
    "# unzip chromedriver_mac64.zip\n",
    "# rm chromedriver_mac64.zip\n",
    "# mv chromedriver /usr/local/bin/\n",
    "\n",
    "# jupyter notebook covid.ipynb\n",
    "\n",
    "# # Environment removal after the test\n",
    "# conda deactivate py3\n",
    "# conda env remove -y -n py3\n",
    "\n",
    "# # Would like to be able to use texture (patterns) to help interpretation or add further information\n",
    "# # can draw texture using svg, but not yet managed to save to image file, so not yet working with automatic slide generation flow\n",
    "# https://github.com/altair-viz/altair/issues/2103"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Package imports\n",
    "import os\n",
    "import re\n",
    "\n",
    "import altair as alt\n",
    "from altair_saver import save\n",
    "from vega_datasets import data\n",
    "\n",
    "import json\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "\n",
    "import datetime\n",
    "\n",
    "import pycountry_convert\n",
    "from pycountry_convert import country_alpha2_to_continent_code\n",
    "\n",
    "import requests\n",
    "from urllib.request import urlopen\n",
    "from http import HTTPStatus\n",
    "\n",
    "# Package configurations\n",
    "alt.data_transformers.disable_max_rows()\n",
    "\n",
    "# Configuration for notebook\n",
    "aggregate_states = True # option to roll-up multiple states to a top level per country view\n",
    "show_charts_for_all_countries = False # option to show full long list of charts for all countries\n",
    "num_cases_threshold = 1500 # optional number of cases threshold to filter out what would otherwise be 'noisy' statistics; value is None, or integer threashold\n",
    "countries_to_aggregate = ['Australia', 'Canada', 'China']\n",
    "\n",
    "run_date_str = \"{:%d-%b-%Y}\".format(datetime.datetime.now())\n",
    "\n",
    "# Slide export configuration\n",
    "export_slides = True\n",
    "slides_file_name = 'COVID_Summary.pptx'\n",
    "template_pptx = 'widescreen.pptx' # input slide template, to help set aspect ratio\n",
    "saved_image_name = 'image.png'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def display_h1(myString):\n",
    "    \"\"\"display as heading 1\"\"\"\n",
    "\n",
    "    display(HTML('<h1>%s</h1>' % myString))\n",
    "\n",
    "def display_h2(myString):\n",
    "    \"\"\"display as heading 2\"\"\"\n",
    "\n",
    "    display(HTML('<h2>%s</h2>' % myString))\n",
    "\n",
    "def display_table(myDF):\n",
    "    \"\"\"display as formatted table\"\"\"\n",
    "\n",
    "    total_id = 'totalID'\n",
    "    header_id = 'headerID'\n",
    "    data_in_html = myDF.to_html(index=False)\n",
    "    tr_style = '<style>.dataframe td { text-align: left; }</style>'\n",
    "    style_in_html = \"\"\"<style>\n",
    "        table#{total_table} {{color='black';font-size:13px; text-align:center; border:0.2px solid black;\n",
    "                             border-collapse:collapse; max-width: 900px; word-wrap:break-word; white-space:pre-wrap; height='250'; }}\n",
    "        thead#{header_table} {{background-color: #4D4D4D; color:#ffffff}}\n",
    "        </style>\"\"\".format(total_table=total_id, header_table=header_id)\n",
    "    data_in_html = re.sub(r'<table',\n",
    "                          r'<table id=%s ' % total_id, data_in_html)\n",
    "    data_in_html = re.sub(r'<thead', r'<thead id=%s ' % header_id, data_in_html)\n",
    "\n",
    "    display(HTML(style_in_html + tr_style + data_in_html))\n",
    "\n",
    "def display_set_full_width():\n",
    "    \"\"\"use the full screen width\"\"\"\n",
    "\n",
    "    display(HTML(\"\"\"\n",
    "    <style>\n",
    "        div#notebook-container    { width: 100%; }\n",
    "        div#menubar-container     { width: 65%; }\n",
    "        div#maintoolbar-container { width: 99%; }\n",
    "    </style>\n",
    "    \"\"\"))\n",
    "    \n",
    "display_set_full_width()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_remove_trailing_zeros(df, metric):\n",
    "    df_last_index_list = df[df[metric] != 0]\n",
    "    if len(df_last_index_list):\n",
    "        df_last_index = df_last_index_list.index[-1]\n",
    "        df = df[:df_last_index]\n",
    "\n",
    "    return df\n",
    "\n",
    "def df_delta_from_cumulative(df, metric, category, new_metric):\n",
    "    if category:\n",
    "        df = df.sort_values(by=['date', category], ascending=True)\n",
    "        last_date = df.groupby([category]).apply(lambda x: x.iloc[-1]).iloc[0]['date']\n",
    "        penultimate_date = df.groupby([category]).apply(lambda x: x.iloc[-2]).iloc[0]['date']\n",
    "        if last_date > penultimate_date:\n",
    "            step = +1\n",
    "        else:\n",
    "            step = -1\n",
    "\n",
    "        df[new_metric] = df.groupby([category])[metric].transform(lambda x: x.diff(periods=step)).clip(lower=0).fillna(0)\n",
    "    else:\n",
    "        df = df.sort_values(by=['date'], ascending=True)\n",
    "\n",
    "        last_date = df['date'].iloc[-1]\n",
    "        penultimate_date = df['date'].iloc[-2]\n",
    "        if last_date > penultimate_date:\n",
    "            step = +1\n",
    "        else:\n",
    "            step = -1\n",
    "\n",
    "        df[new_metric] = df[metric].transform(lambda x: x.diff(periods=step)).clip(lower=0).fillna(0)\n",
    "        \n",
    "    return df\n",
    "\n",
    "def unpack_list_of_dict(potential_list_of_dict, key_name, value_name):\n",
    "    value = np.nan\n",
    "    if type(potential_list_of_dict) is list:\n",
    "        value = {entry.get(key_name):entry.get(value_name) for entry in potential_list_of_dict}\n",
    "    return value\n",
    "    \n",
    "def df_create_categories(df, index_col, columns, category_field_name, value_field_name):\n",
    "    df = df.filter(columns)\n",
    "    df = df.melt(index_col, var_name=category_field_name, value_name=value_field_name)\n",
    "    df = df[df[value_field_name].notnull()]\n",
    "    df = df.groupby([index_col, category_field_name]).agg({value_field_name:'sum'})\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load country information\n",
    "# https://github.com/CSSEGISandData/COVID-19\n",
    "def load_country_data():\n",
    "    base_url = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data'\n",
    "    file_name = f'UID_ISO_FIPS_LookUp_Table.csv'\n",
    "    url = os.path.join(base_url, file_name)\n",
    "    df = pd.read_csv(url)\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_continent_from_iso2_code(iso2):\n",
    "    try:\n",
    "        continent = country_alpha2_to_continent_code(iso2)\n",
    "    except:\n",
    "        continent = 'Unknown'\n",
    "        \n",
    "    return continent\n",
    "\n",
    "def add_continent(df):\n",
    "    df_countries = load_country_data()\n",
    "    df_countries = df_countries.filter(['Country_Region', 'Province_State', 'Combined_Key', 'iso2', 'iso3', 'Population'])\n",
    "\n",
    "    # fix missing space in Johns Hopkins online data before merge\n",
    "    combined_key_replace_dict = {'Northwest Territories,Canada': 'Northwest Territories, Canada'}\n",
    "    df_countries['Combined_Key'] = df_countries['Combined_Key'].replace(combined_key_replace_dict)\n",
    "    \n",
    "    df = df.merge(df_countries, how='left', on='Combined_Key')\n",
    "    df = df.drop_duplicates()\n",
    "\n",
    "    df['continent'] = df['iso2'].apply(lambda x: get_continent_from_iso2_code(x))\n",
    "\n",
    "    df.loc[df['iso2']=='SX', 'continent'] = 'NA' # add missing lookup for Sint Marten (Dutch part)\n",
    "    df.loc[df['Combined_Key']=='Namibia', 'continent'] = 'AF' # add missing lookup for Namibia\n",
    "    \n",
    "    continent_dict = {'AN':'Antarctica', 'AS':'Asia', 'EU':'Europe', 'SA':'South America', 'NA':'North America', 'OC':'Oceania', 'AF':'Africa'}\n",
    "\n",
    "    countries_with_unknown_continent = df[~df['continent'].isin(list(continent_dict.keys()))]['Combined_Key'].unique()\n",
    "    if len(countries_with_unknown_continent) > 0:\n",
    "        print('Countries with unknown continent:', countries_with_unknown_continent)\n",
    "        country_to_continent_dict = {'Timor-Leste':'AS', 'Holy See':'EU'} # 'Holy See' is Vatican City\n",
    "        df.loc[df['Combined_Key'].isin(list(country_to_continent_dict.keys())), 'continent'] = df['Combined_Key'].map(country_to_continent_dict)\n",
    "        print('Applying country to continent correction list:', country_to_continent_dict)\n",
    "\n",
    "    df['continent'] = df['continent'].replace(continent_dict)\n",
    "\n",
    "    return df\n",
    "\n",
    "# load data series\n",
    "# - valid 'name' options are: 'confirmed', 'deaths', or 'recovered'\n",
    "# - valid 'selection' options are: 'global' or 'US'\n",
    "# https://github.com/CSSEGISandData/COVID-19\n",
    "def load_individual_timeseries(name, selection='global', agg_states=True, keep_latlong=True):\n",
    "    base_url = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series'\n",
    "    file_name = f'time_series_covid19_{name}_{selection}.csv'\n",
    "    \n",
    "    if selection == 'global':\n",
    "        index_col_list = ['Country/Region', 'Province/State', 'Lat', 'Long']\n",
    "    else: # 'US'\n",
    "        if name == 'confirmed':\n",
    "            index_col_list = ['Country_Region', 'Province_State', 'Lat', 'Long_', 'UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Admin2', 'Combined_Key']\n",
    "        else: # 'deaths'\n",
    "            index_col_list = ['Country_Region', 'Province_State', 'Lat', 'Long_', 'UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Admin2', 'Combined_Key', 'Population']\n",
    "\n",
    "    url = os.path.join(base_url, file_name)\n",
    "    df = pd.read_csv(url, index_col=index_col_list)\n",
    "    df.columns.name = 'date'\n",
    "    \n",
    "    if not keep_latlong:\n",
    "        df = df.reset_index(['Lat', 'Long'], drop=True)\n",
    "        column_list = ['country', 'state', 'cases']\n",
    "    else:\n",
    "        if selection == 'US':\n",
    "            if name == 'confirmed':\n",
    "                columns_to_drop = ['UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Combined_Key']\n",
    "            else:\n",
    "                columns_to_drop = ['UID', 'iso2', 'iso3', 'code3', 'FIPS', 'Combined_Key', 'Population']\n",
    "\n",
    "            df = df.reset_index(columns_to_drop, drop=True)\n",
    "\n",
    "            column_list = ['country', 'state', 'lat', 'long', 'admin2', 'cases']\n",
    "\n",
    "        else:\n",
    "            column_list = ['country', 'state', 'lat', 'long', 'cases']\n",
    "\n",
    "    df = df.stack()\n",
    "    df = df.reset_index()\n",
    "    df = df.set_index('date')\n",
    "         \n",
    "    df.index = pd.to_datetime(df.index)\n",
    "    df.columns = column_list\n",
    "    \n",
    "    df['cases'] = df['cases'].astype(int)\n",
    "\n",
    "    # Aggregate large countries split by states\n",
    "    if agg_states:\n",
    "        if selection == 'global':\n",
    "            df_non_states = df[~df['country'].isin(countries_to_aggregate)]\n",
    "            df_states     = df[ df['country'].isin(countries_to_aggregate)]\n",
    "            df_states     = df_states.groupby(['country', 'date']).agg({'lat':'mean', 'long':'mean', 'cases':'sum'})\n",
    "            df_states     = df_states.reset_index(['country'])\n",
    "            df = pd.concat([df_non_states, df_states])\n",
    "        else: # 'US'\n",
    "            df = df.groupby(['country', 'state', 'date']).agg({'lat':'mean', 'long':'mean', 'cases':'sum'})\n",
    "        \n",
    "        df = df.reset_index()\n",
    "\n",
    "#     cruise_ship_list_to_drop = ['Diamond Princess', 'MS Zaandam'] # drop the cruise ships from the data\n",
    "#     print('Excluding cases from cruise ships:', cruise_ship_list_to_drop)\n",
    "#     df = df[~df['country'].isin(cruise_ship_list_to_drop)]\n",
    "    \n",
    "    df['state'] = df['state'].fillna('')\n",
    "\n",
    "    category = ['country', 'state']\n",
    "\n",
    "    return df\n",
    "\n",
    "# - valid 'selection' options are: 'global' or 'US'\n",
    "def load_covid_data(selection='global', agg_states=False, p_crit=.05, filter_n_days_100=None, keep_latlong=True):\n",
    "    df = load_individual_timeseries('confirmed', selection=selection, agg_states=agg_states, keep_latlong=keep_latlong)\n",
    "    df = df.rename(columns={'cases': 'cumConfirmed'})\n",
    "\n",
    "    df['state'] = df['state'].fillna('')\n",
    "\n",
    "    # Add deaths\n",
    "    df_deaths = load_individual_timeseries('deaths', selection=selection)\n",
    "    df_d = df_deaths.set_index(['country', 'state'], append=True)[['cases']]\n",
    "    df_d.columns = ['cumDeaths']\n",
    "\n",
    "    df = (df.set_index(['country', 'state'], append=True)\n",
    "            .join(df_d)\n",
    "            .reset_index(['country', 'state'])\n",
    "    )\n",
    "    \n",
    "    cruise_ship_list_to_drop = ['Diamond Princess', 'MS Zaandam'] # drop the cruise ships from the data\n",
    "    print('Excluding cases from cruise ships:', cruise_ship_list_to_drop)\n",
    "    df = df[~df['country'].isin(cruise_ship_list_to_drop)]\n",
    "    \n",
    "    df['Combined_Key'] = (df['state'] + \", \" + df['country']).str.strip(', ')\n",
    "\n",
    "    df = df.reset_index()\n",
    "\n",
    "    df = add_continent(df) # consider to add continent for time series data too\n",
    "    \n",
    "    df = df_delta_from_cumulative(df, 'cumConfirmed', 'Combined_Key', 'confirmed')\n",
    "    df = df_delta_from_cumulative(df, 'cumDeaths', 'Combined_Key', 'deaths')\n",
    "    df = df.drop(['cumConfirmed', 'cumDeaths'], axis='columns')\n",
    "\n",
    "    df['deathsRate']    = df['deaths']    / df['Population'] * 100E3 # per 100k population\n",
    "    df['confirmedRate'] = df['confirmed'] / df['Population'] * 100E3 # per 100k population\n",
    "\n",
    "    return df\n",
    "\n",
    "# - valid 'selection' options are: 'global' or 'US'\n",
    "def load_covid_data_us(agg_states=True, keep_latlong=True):\n",
    "    selection = 'US'\n",
    "    df = load_individual_timeseries('confirmed', selection=selection, agg_states=agg_states, keep_latlong=keep_latlong)\n",
    "    df = df.rename(columns={'cases': 'cumConfirmed'})\n",
    "\n",
    "    df['state'] = df['state'].fillna('')\n",
    "    if not agg_states:\n",
    "        df['admin2'] = df['admin2'].fillna('')\n",
    "        index_col_list = ['country', 'state', 'admin2']\n",
    "    else:\n",
    "        index_col_list = ['country', 'state']\n",
    "        \n",
    "    # Add deaths\n",
    "    df_deaths = load_individual_timeseries('deaths', selection=selection)\n",
    "    df_d = df_deaths.set_index(index_col_list, append=True)[['cases']]\n",
    "    df_d.columns = ['cumDeaths']\n",
    "\n",
    "    df = (df.set_index(index_col_list, append=True)\n",
    "            .join(df_d)\n",
    "            .reset_index(index_col_list)\n",
    "    )\n",
    "    \n",
    "    df = df.reset_index()\n",
    "\n",
    "    df = df_delta_from_cumulative(df, 'cumConfirmed', 'state', 'confirmed')\n",
    "    df = df_delta_from_cumulative(df, 'cumDeaths', 'state', 'deaths')\n",
    "    df = df.drop(['cumConfirmed', 'cumDeaths'], axis='columns')\n",
    "    \n",
    "    df['Population'] = 0 # placeholder for now\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_totals_from_cumulative_time_series(df):\n",
    "    agg_dict = dict.fromkeys(df, 'last')\n",
    "    agg_dict.update(dict.fromkeys(['cumConfirmed', 'cumDeaths'], 'last'))\n",
    "    df_totals = df.copy().groupby(['country', 'state']).agg(agg_dict)\n",
    "    df_totals = df_totals.rename(columns={'cumConfirmed':'confirmed', 'cumDeaths':'deaths'})\n",
    "    df_totals = df_totals.reset_index(drop=True) # to ensure important fields are accessible in columns rather than hidden in index\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def get_totals_from_time_series(df):\n",
    "    agg_dict = dict.fromkeys(df, 'last')\n",
    "    agg_dict.update(dict.fromkeys(['confirmed', 'deaths'], 'sum'))\n",
    "    df_totals = df.copy().groupby(['country', 'state']).agg(agg_dict)\n",
    "    df_totals = df_totals.reset_index(drop=True) # to ensure important fields are accessible in columns rather than hidden in index\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def get_continent_totals(df):\n",
    "    df_totals = df.copy().drop(['country', 'state'], axis=1)\n",
    "    df_totals = df_totals.groupby(['continent']).agg({'lat':'mean', 'long':'mean', 'confirmed':'sum', 'deaths':'sum', 'Population':'sum'})\n",
    "    df_totals = df_totals.reset_index() # to ensure important fields are accessible in columns rather than hidden in index\n",
    "    \n",
    "    df_totals['deathsRate']    = df_totals['deaths']    / df_totals['Population'] * 100E3 # per 100k population\n",
    "    df_totals['confirmedRate'] = df_totals['confirmed'] / df_totals['Population'] * 100E3 # per 100k population\n",
    "    \n",
    "    return df_totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display_h1('COVID Data Visualisation')\n",
    "display_h2('Load COVID data')\n",
    "\n",
    "print('Loading World data')\n",
    "df_covid_time_series_world = load_covid_data(agg_states=aggregate_states, filter_n_days_100=1, keep_latlong=True)\n",
    "df_totals_world = get_totals_from_time_series(df_covid_time_series_world)\n",
    "\n",
    "# optionally filter by num cases, to exclude what would otherwise be 'noisy' statistics\n",
    "if num_cases_threshold:\n",
    "    selected_list = df_totals_world.loc[df_totals_world['confirmed']>=num_cases_threshold, 'Combined_Key']\n",
    "    exclude_list  = df_totals_world.loc[df_totals_world['confirmed']<num_cases_threshold, 'Combined_Key']\n",
    "    print('- excluding cases <', num_cases_threshold)\n",
    "    print('- excluded list:', list(exclude_list))\n",
    "    df_totals_world = df_totals_world[df_totals_world['Combined_Key'].isin(selected_list)]\n",
    "    df_covid_time_series_world = df_covid_time_series_world[df_covid_time_series_world['Combined_Key'].isin(selected_list)]\n",
    "    \n",
    "df_unknown_continents = df_covid_time_series_world[df_covid_time_series_world['continent']=='Unknown']['Combined_Key']\n",
    "if len(df_unknown_continents):\n",
    "    print('- unknown continents:', df_unknown_continents.unique())\n",
    "    \n",
    "print('')\n",
    "\n",
    "print('Loading US data')\n",
    "df_covid_time_series_us = load_covid_data_us()\n",
    "\n",
    "print('')\n",
    "\n",
    "print('Finished loading data')\n",
    "\n",
    "print('')\n",
    "\n",
    "latest_date = df_covid_time_series_world['date'].max()\n",
    "latest_date_str = \"{:%d-%b-%Y}\".format(latest_date)\n",
    "print('Most recent data: ', latest_date_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://coronavirus-staging.data.gov.uk/details/about-data\n",
    "# https://coronavirus-staging.data.gov.uk/details/interactive-map\n",
    "\n",
    "def df_from_uk_gov(area_type = 'overview', area_name = None, latest=False):\n",
    "    ENDPOINT = \"https://api.coronavirus.data.gov.uk/v1/data\"\n",
    "    cases_field = 'cumCasesByPublishDate' if area_type == 'nation' else 'cumCasesBySpecimenDate'\n",
    "    deaths_field = 'cumDeathsByDeathDate'\n",
    "\n",
    "    # areaType: Area type as string\n",
    "    # areaName: Area name as string\n",
    "    # areaCode: Area Code as string\n",
    "    # date: Date as string [YYYY-MM-DD]\n",
    "    filters = [\n",
    "        f\"areaType={ area_type }\" # overview, nation, region, nhsRegion, utla, ltla\n",
    "    ]\n",
    "    if area_name: filters = filters + [f\"areaName={ area_name }\"]\n",
    "\n",
    "    # Some metrics are not available for specific areaType values.\n",
    "    # For instance, we have newCasesByPublishDate and cumCasesByPublishDate only available for areaType=nation but not for region,utla, or ltla.\n",
    "    # Conversely, we have newCasesBySpecimenDate and cumCasesBySpecimenDate available for region, utla, and ltla but not for nation.\n",
    "    structure = {\n",
    "        \"date\": \"date\",\n",
    "        \"type\": \"areaType\",\n",
    "        \"name\": \"areaName\",\n",
    "        \"code\": \"areaCode\",\n",
    "        \"cases\": cases_field,\n",
    "        \"deaths\": deaths_field\n",
    "    }\n",
    "\n",
    "    api_params = {\n",
    "        \"filters\": str.join(\";\", filters),\n",
    "        \"structure\": dumps(structure, separators=(\",\", \":\"))\n",
    "    }\n",
    "    if latest: api_params[\"latestBy\"] = deaths_field # must be a metrics field included in 'structure'\n",
    "\n",
    "    page_number = 1\n",
    "    df_overall = pd.DataFrame() # initial value\n",
    "    finished = False\n",
    "    while not finished:\n",
    "        #         print('- query page ', page_number)\n",
    "        # Adding page number to query params\n",
    "        api_params[\"page\"] = page_number\n",
    "\n",
    "        response = get(ENDPOINT, params=api_params, timeout=100)\n",
    "\n",
    "        if response.status_code >= HTTPStatus.BAD_REQUEST:\n",
    "            raise RuntimeError(f'Request failed: { response.text }')\n",
    "        elif response.status_code == HTTPStatus.NO_CONTENT:\n",
    "            print('No data returned from query')\n",
    "            break\n",
    "\n",
    "        df = pd.DataFrame() # initial value\n",
    "        data = response.json()\n",
    "        # {'length': 216, 'maxPageLimit': 1000, 'data': [{'date': '2020-08-07', 'name': 'England', 'code': 'E92000001', 'cases': {'daily': 796, 'cumulative': 266645}, 'deaths': {'daily': None, 'cumulative': None}}, {'date': '2020-08-06', 'name': 'England', 'code': 'E92000001', 'cases': {'daily': 826, 'cumulative': 265849}, 'deaths': {'daily': 14, 'cumulative': 41886}},\n",
    "        df = pd.DataFrame.from_dict(data['data'])\n",
    "        df_overall = pd.concat([df_overall, df])\n",
    "\n",
    "        # check if more to read\n",
    "        length = data['length']\n",
    "        page_limit = data['maxPageLimit']\n",
    "        if (length < page_limit) or (page_limit == 1):\n",
    "            finished = True\n",
    "        else:\n",
    "            page_number += 1\n",
    "\n",
    "    return df_overall\n",
    "    \n",
    "def df_from_uk_gov_v1(metrics_list, area_type='overview', area_code=None, timeout=200):\n",
    "    url = 'https://api.coronavirus.data.gov.uk/v1/data'\n",
    "    metrics_string = '&metric='.join(metrics_list)\n",
    "    format = 'json'\n",
    "\n",
    "    api_params = {}\n",
    "    area_code = 'england'\n",
    "\n",
    "#     filters = [\n",
    "#         f\"areaType={area_type}\"\n",
    "#     ]\n",
    "#     api_params.filters = filters\n",
    "#     if area_code is not None:\n",
    "#         api_params.filters = [f'areaName={area_code}']\n",
    "    api_params = 'filters=areaType=nation;areaName=england'\n",
    "    api_params = api_params+'&structure={\"date\":\"date\",\"newCases\":\"newCasesByPublishDate\"}'\n",
    "    response = requests.get(url, params=api_params, timeout=timeout)\n",
    "\n",
    "    df = pd.DataFrame()\n",
    "    if response.status_code >= HTTPStatus.BAD_REQUEST:\n",
    "        raise RuntimeError(f'Request failed: { response.text }')\n",
    "    elif response.status_code == HTTPStatus.NO_CONTENT:\n",
    "        print('-- No data returned from query')\n",
    "    else: # ok\n",
    "        data = response.json()\n",
    "        display(data)\n",
    "        df = pd.DataFrame.from_dict(data['body'])\n",
    "\n",
    "    return df\n",
    "\n",
    "def df_from_uk_gov_v2(metrics_list, area_type='overview', area_code=None, timeout=200):\n",
    "    url = 'https://api.coronavirus.data.gov.uk/v2/data'\n",
    "    metrics_string = '&metric='.join(metrics_list)\n",
    "    format = 'json'\n",
    "\n",
    "    api_params = 'areaType='+area_type\n",
    "    if area_code is not None:\n",
    "        api_params = api_params+'&'+'areaCode='+area_code\n",
    "    api_params = api_params+'&metric='+metrics_string\n",
    "    api_params = api_params+'&format='+format\n",
    "    response = requests.get(url, params=api_params, timeout=timeout)\n",
    "\n",
    "    df = pd.DataFrame()\n",
    "    try:\n",
    "        if response.status_code >= HTTPStatus.BAD_REQUEST:\n",
    "            raise RuntimeError(f'Request failed: { response.text }')\n",
    "        elif response.status_code == HTTPStatus.NO_CONTENT:\n",
    "            print('-- No data returned from query')\n",
    "        else: # ok\n",
    "            data = response.json()\n",
    "\n",
    "            df = pd.DataFrame.from_dict(data['body'])\n",
    "            \n",
    "    except Exception as e:\n",
    "        print('-- df_from_uk_gov_v2 failed')\n",
    "        print(e)\n",
    "\n",
    "    return df\n",
    "\n",
    "def load_covid_data_uk_v1(metrics_dict, area_type='overview', area_code=None):\n",
    "    metrics_list = [metric for metric, new_name in metrics_dict.items()]\n",
    "\n",
    "    df = df_from_uk_gov_v1(metrics_list, area_type=area_type, area_code=area_code, timeout=200)\n",
    "    if not df.empty:\n",
    "        df = df.rename(columns=metrics_dict)\n",
    "        df['date'] = pd.to_datetime(df['date'])\n",
    "\n",
    "    return df\n",
    "\n",
    "def load_covid_data_uk_v2(metrics_dict, area_type='overview', area_code=None):\n",
    "    metrics_list = [metric for metric, new_name in metrics_dict.items()]\n",
    "\n",
    "    df = pd.DataFrame()\n",
    "    df = df_from_uk_gov_v2(metrics_list, area_type=area_type, area_code=area_code, timeout=200)\n",
    "    if not df.empty:\n",
    "        df = df.rename(columns=metrics_dict)\n",
    "        df['date'] = pd.to_datetime(df['date'])\n",
    "\n",
    "    return df\n",
    "\n",
    "def df_from_json_file(url):\n",
    "    data = urlopen(url).read()\n",
    "    raw_json = json.loads(data)\n",
    "\n",
    "    formatted_json = [feature['attributes'] for feature in raw_json['features']]\n",
    "    df = pd.DataFrame(formatted_json)\n",
    "\n",
    "    return df\n",
    "\n",
    "def add_region_mapping_uk(df):\n",
    "    df = df.copy()\n",
    "    \n",
    "    # Local Authority District to Region (April 2019) Lookup in England\n",
    "    # https://geoportal.statistics.gov.uk/datasets/local-authority-district-to-region-april-2019-lookup-in-england/geoservice\n",
    "    #  LAD19CD    LAD19NM    RGN19CD    RGN19NM    FID  \n",
    "    url = 'https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/LAD19_RGN19_EN_LU/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json'\n",
    "    columns_to_drop = ['FID']\n",
    "    df_region_lookup = df_from_json_file(url)\n",
    "    df_region_lookup = df_region_lookup.drop(columns_to_drop, axis=1)\n",
    "\n",
    "    # Lower Tier Local Authority to Upper Tier Local Authority (April 2019) Lookup in England and Wales\n",
    "    #  LTLA19CD    LTLA19NM    UTLA19CD    UTLA19NM    FID  \n",
    "    url = 'https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/LTLA19_UTLA19_EW_LU/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json'\n",
    "    df_authority_lookup = df_from_json_file(url)\n",
    "    columns_to_drop = ['FID']\n",
    "    df_authority_lookup = df_authority_lookup.drop(columns_to_drop, axis=1)\n",
    "    df_authority_lookup = df_authority_lookup.drop_duplicates()\n",
    "    \n",
    "    local_authority_tiers_to_regions = df_authority_lookup.merge(df_region_lookup, how='left', left_on='LTLA19CD', right_on='LAD19CD')\n",
    "\n",
    "    # Ward to Local Authority District to County to Region to Country (December 2019) Lookup in United Kingdom\n",
    "    # https://geoportal.statistics.gov.uk/datasets/ward-to-local-authority-district-to-county-to-region-to-country-december-2019-lookup-in-united-kingdom/geoservice\n",
    "    #  WD19CD    WD19NM    LAD19CD    LAD19NM    CTY19CD    CTY19NM  RGN19CD    RGN19NM    CTRY19CD    CTRY19NM    FID  \n",
    "    url = 'https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/WD19_LAD19_CTY19_OTH_UK_LU/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json'\n",
    "    df_county_lookup = df_from_json_file(url)\n",
    "    columns_to_filter = ['LAD19CD', 'LAD19NM', 'CTY19CD', 'CTY19NM', 'RGN19CD', 'RGN19NM', 'CTRY19CD', 'CTRY19NM']\n",
    "    df_county_lookup = df_county_lookup.filter(columns_to_filter)\n",
    "    df_county_lookup = df_county_lookup.drop_duplicates()\n",
    "\n",
    "    columns_to_filter = ['RGN19CD', 'CTRY19CD', 'CTRY19NM']\n",
    "    region_to_country = df_county_lookup.filter(columns_to_filter)\n",
    "    region_to_country = region_to_country.drop_duplicates()\n",
    "    local_authority_tiers_to_regions_to_countries = local_authority_tiers_to_regions.merge(region_to_country, how='left', on='RGN19CD')\n",
    "\n",
    "    # Create dictionaries to map field values\n",
    "    LTLA19CD_to_RGN19NM_dict = dict(set(zip(local_authority_tiers_to_regions_to_countries['LTLA19CD'], local_authority_tiers_to_regions_to_countries['RGN19NM'])))\n",
    "    UTLA19CD_to_RGN19NM_dict = dict(set(zip(local_authority_tiers_to_regions_to_countries['UTLA19CD'], local_authority_tiers_to_regions_to_countries['RGN19NM'])))\n",
    "    RGN19CD_to_CTRY19NM_dict = dict(set(zip(local_authority_tiers_to_regions_to_countries['RGN19CD'], local_authority_tiers_to_regions_to_countries['CTRY19NM'])))\n",
    "    RGN19NM_to_CTRY19NM_dict = dict(set(zip(local_authority_tiers_to_regions_to_countries['RGN19NM'], local_authority_tiers_to_regions_to_countries['CTRY19NM'])))\n",
    "    \n",
    "    # Lookup location hierarchy\n",
    "    df.loc[df['areaType']=='region', 'regionName'] = df['areaName']\n",
    "    df.loc[df['areaType']=='utla', 'regionName'] = df['areaCode'].map(UTLA19CD_to_RGN19NM_dict)\n",
    "    df.loc[df['areaType']=='ltla', 'regionName'] = df['areaCode'].map(LTLA19CD_to_RGN19NM_dict)\n",
    "    df['CountryName'] = df['regionName'].map(RGN19NM_to_CTRY19NM_dict)\n",
    "    df.loc[df['areaType']=='nation', 'countryName'] = df['areaName']\n",
    "\n",
    "    return df\n",
    "\n",
    "def add_msoa_region_mapping_uk(df):\n",
    "    df = df.copy()\n",
    "\n",
    "    uk_msoa_lookup_file = 'uk_msoa_lookup_table.csv'\n",
    "    uk_msoa_lookup_table = pd.read_csv(uk_msoa_lookup_file)\n",
    "\n",
    "    MSOA11CD_to_LTLA_dict = dict(set(zip(uk_msoa_lookup_table['MSOA'], uk_msoa_lookup_table['LTLA_areaName'])))\n",
    "    MSOA11CD_to_RGN_dict = dict(set(zip(uk_msoa_lookup_table['MSOA'], uk_msoa_lookup_table['region_areaName'])))\n",
    "\n",
    "    df['LTLAName']   = df['areaCode'].map(MSOA11CD_to_LTLA_dict)\n",
    "    df['RegionName'] = df['areaCode'].map(MSOA11CD_to_RGN_dict)\n",
    "\n",
    "    return df\n",
    "\n",
    "# get UK population for specified area codes\n",
    "def df_get_uk_population():\n",
    "    # UK population - mid 2019\n",
    "    # Could not find a useable online source, so downloaded and extracted to CSV file\n",
    "    # https://www.ons.gov.uk/file?uri=%2fpeoplepopulationandcommunity%2fpopulationandmigration%2fpopulationestimates%2fdatasets%2fpopulationestimatesforukenglandandwalesscotlandandnorthernireland%2fmid2019april2020localauthoritydistrictcodes/ukmidyearestimates20192020ladcodes.xls\n",
    "    # Extracted subset of data to local CSV file\n",
    "    uk_population_file_2019 = 'UK_Population_Mid_2019.csv'\n",
    "    df_uk_population_2019 = pd.read_csv(uk_population_file_2019, thousands=',')\n",
    "\n",
    "    return df_uk_population_2019\n",
    "\n",
    "# Load UK Metrics\n",
    "## Area type\n",
    "area_overview = 'overview'\n",
    "area_nation   = 'nation'\n",
    "area_region   = 'region'\n",
    "area_utla     = 'utla'\n",
    "area_ltla     = 'ltla'\n",
    "area_msoa     = 'msoa'\n",
    "\n",
    "## Area code\n",
    "area_code_all = None\n",
    "area_code_England = 'E92000001'\n",
    "\n",
    "## Metrics\n",
    "overall_metrics_dict  = {'newCasesBySpecimenDate':'confirmed', 'newDeaths28DaysByDeathDate':'deaths',\n",
    "                         'newCasesBySpecimenDateRollingRate':'confirmedRate', 'newDeaths28DaysByDeathDateRollingRate':'deathsRate'}\n",
    "region_metrics_dict   = {'newCasesBySpecimenDate':'confirmed', 'newDeaths28DaysByDeathDate':'deaths',\n",
    "#                          'newCasesBySpecimenDateRollingRate':'confirmedRate', 'newDeaths28DaysByDeathDateRate':'deathsRate'}\n",
    "                         'newCasesBySpecimenDateRollingRate':'confirmedRate', 'newDeaths28DaysByDeathDateRollingRate':'deathsRate'}\n",
    "hospital_metrics_dict = {'newAdmissions':'hospitalAdmissions', 'covidOccupiedMVBeds':'covidOccupiedMVBeds', 'hospitalCases':'hospitalCases'}\n",
    "hospital_age_metrics_dict = {'cumAdmissionsByAge':'cumAdmissionsByAge'}\n",
    "vaccine_metrics_dict  = {'cumPeopleVaccinatedFirstDoseByPublishDate':      'cumVaccineFirstDose',\n",
    "                         'cumPeopleVaccinatedSecondDoseByPublishDate':     'cumVaccineSecondDose',\n",
    "                         'cumPeopleVaccinatedThirdInjectionByPublishDate': 'cumVaccineThirdDose',\n",
    "                        }\n",
    "# Nation & UK overview data:\n",
    "## older data using: cumPeopleVaccinatedFirstDoseByVaccinationDate\n",
    "## looks like might have moved on 11-Jan to: cumPeopleVaccinatedFirstDoseByPublishDate\n",
    "## https://api.coronavirus.data.gov.uk/v2/data?areaType=overview&metric=newPeopleVaccinatedFirstDoseByPublishDate&format=csv\n",
    "test_metrics_dict     = {'uniquePeopleTestedBySpecimenDateRollingSum':'peopleTested'}\n",
    "msoa_metrics_dict     = {'newCasesBySpecimenDateRollingRate':'value'}\n",
    "cases_age_metrics_dict= {'newCasesBySpecimenDateAgeDemographics':'confirmedAge'}\n",
    "deaths_age_metrics_dict= {'newDeaths28DaysByDeathDateAgeDemographics':'deathsAge'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "print('Loading UK data:')\n",
    "print('- overview')\n",
    "df_uk_overview = load_covid_data_uk_v2(metrics_dict=overall_metrics_dict, area_type=area_nation, area_code=area_code_all)\n",
    "print('- hospital')\n",
    "df_uk_hospital_england = load_covid_data_uk_v2(metrics_dict=hospital_metrics_dict, area_type=area_nation, area_code=area_code_England)\n",
    "df_uk_hospital_age_england = load_covid_data_uk_v2(metrics_dict=hospital_age_metrics_dict, area_type=area_nation, area_code=area_code_England)\n",
    "print('- vaccinations')\n",
    "df_uk_vaccinations = load_covid_data_uk_v2(metrics_dict=vaccine_metrics_dict, area_type=area_nation, area_code=area_code_all)\n",
    "# if not df_uk_vaccinations.empty:\n",
    "#     df_uk_vaccinations['cumVaccineFirstDose']  = df_uk_vaccinations['cumPeopleVaccinatedFirstDoseByVaccinationDate']\n",
    "#     df_uk_vaccinations['cumVaccineSecondDose'] = df_uk_vaccinations['cumPeopleVaccinatedSecondDoseByVaccinationDate']\n",
    "#     df_uk_vaccinations['cumVaccineThirdDose']  = df_uk_vaccinations['cumPeopleVaccinatedThirdInjectionByVaccinationDate']\n",
    "    \n",
    "# #     df_uk_vaccinations['cumVaccineFirstDose']  = df_uk_vaccinations['cumPeopleVaccinatedFirstDoseByPublishDate'].fillna(\n",
    "# #                                                     df_uk_vaccinations['cumPeopleVaccinatedFirstDoseByVaccinationDate'])\n",
    "# #     df_uk_vaccinations['cumVaccineSecondDose'] = df_uk_vaccinations['cumPeopleVaccinatedSecondDoseByPublishDate'].fillna(\n",
    "# #                                                     df_uk_vaccinations['cumPeopleVaccinatedSecondDoseByVaccinationDate'])\n",
    "# #     df_uk_vaccinations['cumVaccineThirdDose']  = df_uk_vaccinations['cumPeopleVaccinatedThirdInjectionByPublishDate'].fillna(\n",
    "# #                                                     df_uk_vaccinations['cumPeopleVaccinatedThirdInjectionByVaccinationDate'])\n",
    "#     df_uk_vaccinations = df_uk_vaccinations.drop(['cumPeopleVaccinatedFirstDoseByVaccinationDate',  'cumPeopleVaccinatedFirstDoseByPublishDate',\n",
    "#                                                   'cumPeopleVaccinatedSecondDoseByVaccinationDate', 'cumPeopleVaccinatedSecondDoseByPublishDate',\n",
    "#                                                   'cumPeopleVaccinatedThirdInjectionByVaccinationDate', 'cumPeopleVaccinatedThirdInjectionByPublishDate'],\n",
    "#                                                  axis='columns', errors='ignore')\n",
    "print('- LTLA')\n",
    "df_uk_ltla = load_covid_data_uk_v2(metrics_dict=region_metrics_dict, area_type=area_ltla, area_code=area_code_all)\n",
    "df_uk_ltla_regions = add_region_mapping_uk(df_uk_ltla)\n",
    "print('- MSOA')\n",
    "df_uk_msoa = load_covid_data_uk_v2(metrics_dict=msoa_metrics_dict, area_type=area_msoa, area_code=area_code_all)\n",
    "df_uk_msoa_with_regions = add_msoa_region_mapping_uk(df_uk_msoa)\n",
    "\n",
    "print('- overall cases by age')\n",
    "df_uk_age = load_covid_data_uk_v2(metrics_dict=cases_age_metrics_dict, area_type=area_nation, area_code=area_code_all)\n",
    "df_deaths_uk_age = load_covid_data_uk_v2(metrics_dict=deaths_age_metrics_dict, area_type=area_nation, area_code=area_code_all)\n",
    "if not df_uk_age.empty:\n",
    "    df_uk_age['casesByAge'] = df_uk_age['confirmedAge'].apply(lambda x : unpack_list_of_dict(x, 'age', 'cases'))\n",
    "    df_uk_age['casesRateByAge'] = df_uk_age['confirmedAge'].apply(lambda x : unpack_list_of_dict(x, 'age', 'rollingRate'))\n",
    "    df_uk_age['deathsByAge'] = df_deaths_uk_age['deathsAge'].apply(lambda x : unpack_list_of_dict(x, 'age', 'deaths'))\n",
    "    df_uk_age['deathsRateByAge'] = df_deaths_uk_age['deathsAge'].apply(lambda x : unpack_list_of_dict(x, 'age', 'rollingRate'))\n",
    "    df_uk_age = df_uk_age.drop(['confirmedAge', 'deathsAge'], axis='columns', errors='ignore')\n",
    "\n",
    "print('- UK population 2019')\n",
    "df_uk_population_2019 = df_get_uk_population()\n",
    "\n",
    "#population_uk = df_uk_population_2019.loc[df_uk_population_2019['Area name']=='UNITED KINGDOM', 'Population'].values[0]\n",
    "population_uk = 66796807\n",
    "population_great_britain = 64903140\n",
    "population_england_and_wales = 59439840\n",
    "population_england = 56286961\n",
    "population_scotland = population_great_britain - population_england_and_wales\n",
    "population_wales = population_england_and_wales - population_england\n",
    "population_northern_ireland = population_uk - population_great_britain\n",
    "\n",
    "# print('UK', population_uk)\n",
    "# print('England', population_england)\n",
    "# print('Scotland', population_scotland)\n",
    "# print('Wales', population_wales)\n",
    "# print('Northern Ireland', population_northern_ireland)\n",
    "\n",
    "df_uk_vaccinations.loc[df_uk_vaccinations['areaName']=='England', 'Population'] = population_england\n",
    "df_uk_vaccinations.loc[df_uk_vaccinations['areaName']=='Scotland', 'Population'] = population_scotland\n",
    "df_uk_vaccinations.loc[df_uk_vaccinations['areaName']=='Wales', 'Population'] = population_wales\n",
    "df_uk_vaccinations.loc[df_uk_vaccinations['areaName']=='Northern Ireland', 'Population'] =  population_northern_ireland\n",
    "\n",
    "print('Finished loading UK data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print('Loading World-wide vaccination data')\n",
    "\n",
    "def get_vaccine_trend_from_field_list(df, data_field, field):\n",
    "    df = df.copy()\n",
    "    \n",
    "    index_cols = df.columns\n",
    "    df[field] = df[data_field].apply(lambda x : unpack_list_of_dict(x, 'date', field))\n",
    "\n",
    "    df_field_list = pd.DataFrame(df[field].to_list())\n",
    "    df = pd.concat([df[index_cols], df_field_list], axis='columns')\n",
    "\n",
    "    df = df.melt(index_cols, var_name='date', value_name=field)\n",
    "\n",
    "    df = df.drop([data_field], axis='columns')\n",
    "\n",
    "    return df\n",
    "    \n",
    "def get_country_population():\n",
    "    url = 'https://github.com/owid/covid-19-data/blob/master/scripts/input/un/population_latest.csv?raw=true'\n",
    "    df = pd.read_csv(url)\n",
    "    \n",
    "    return df\n",
    "\n",
    "def df_from_world_in_data_csv():\n",
    "    url = 'https://github.com/owid/covid-19-data/blob/master/public/data/vaccinations/vaccinations.csv?raw=true'\n",
    "\n",
    "    df = pd.read_csv(\n",
    "        url, \n",
    "        parse_dates=['date'])\n",
    "\n",
    "    df = df.sort_values(by=['date'], ascending=True)\n",
    "\n",
    "    iso_codes_to_drop = df[df['iso_code'].str.startswith('OWID_')]['iso_code'].unique()\n",
    "    df = df[~df['iso_code'].str.startswith('OWID_')]\n",
    "\n",
    "    df['iso2'] = df['iso_code'].apply(lambda x: pycountry_convert.country_alpha3_to_country_alpha2(x))\n",
    "    df['continent'] = df['iso2'].apply(lambda x: get_continent_from_iso2_code(x))\n",
    "    df = df.drop(['iso2', 'iso_code'], axis='columns')\n",
    "\n",
    "    continent_dict = {'AN':'Antarctica', 'AS':'Asia', 'EU':'Europe', 'SA':'South America', 'NA':'North America', 'OC':'Oceania', 'AF':'Africa'}\n",
    "    \n",
    "    countries_with_unknown_continent = df[~df['continent'].isin(list(continent_dict.keys()))]['location'].unique()\n",
    "    if len(countries_with_unknown_continent) > 0:\n",
    "        print('Countries with unknown continent:', countries_with_unknown_continent)\n",
    "        country_to_continent_dict = {'Timor':'AS'}\n",
    "        df.loc[df['location'].isin(list(country_to_continent_dict.keys())), 'continent'] = df['location'].map(country_to_continent_dict)\n",
    "        print('Applying country to continent correction list:', country_to_continent_dict)\n",
    "        \n",
    "    df['continent'] = df['continent'].replace(continent_dict)\n",
    "    df = df.rename(columns={'location':'country'})\n",
    "\n",
    "    df_pop = get_country_population()\n",
    "    \n",
    "    country_population_dict = dict(set(zip(df_pop['entity'], df_pop['population'])))\n",
    "    df['population'] = df['country'].map(country_population_dict)\n",
    "\n",
    "    return df\n",
    "\n",
    "df_world_vaccines = df_from_world_in_data_csv()\n",
    "# display(df_world_vaccines.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "__version__ = \"0.1.00\"\n",
    "\n",
    "import os # For file path handling  \n",
    "\n",
    "# to obtain the pptx module:\n",
    "#   pip install --user python-pptx\n",
    "import pptx\n",
    "from pptx.util import Mm\n",
    "from pptx.util import Length\n",
    "\n",
    "\n",
    "class PPT():\n",
    "    OUTPUT_FILE_NAME = 'test_slides.pptx'\n",
    "    IMAGE_FILE_NAME = 'test_chart.png'\n",
    "    DIRECTORY_NAME = '.' # option to put in sub-directory\n",
    "    \n",
    "    # folder structure\n",
    "    generated_chart = os.path.join(DIRECTORY_NAME, IMAGE_FILE_NAME)\n",
    "    generated_ppt = os.path.join(DIRECTORY_NAME, OUTPUT_FILE_NAME)\n",
    "    \n",
    "    # slide template enumerations\n",
    "    from enum import IntEnum\n",
    "    class slide_template_layout(IntEnum):\n",
    "        TITLE = 0\n",
    "        TITLE_AND_CONTENT = 1\n",
    "        TITLE_AND_MAX_CONTENT = 2\n",
    "        \n",
    "    placeholders = {\n",
    "        slide_template_layout.TITLE:{'title':{'id':0, 'type':'text'}, 'subtitle':{'id':1, 'type':'text'}},\n",
    "        slide_template_layout.TITLE_AND_CONTENT:{'title':{'id':0, 'type':'text'}, 'content':{'id':1, 'type':'content'}, 'footer':{'id':10, 'type':'text'}},\n",
    "        slide_template_layout.TITLE_AND_MAX_CONTENT:{'title':{'id':0, 'type':'text'}, 'content':{'id':1, 'type':'content'}, 'footer':{'id':10, 'type':'text'}},\n",
    "    }\n",
    "    \n",
    "    def __init__(self, template=None, outfile=None, placeholders=None, slide_width_mm=None, slide_height_mm=None):\n",
    "        self.template = template\n",
    "        self.outfile = outfile if outfile else self.generated_ppt\n",
    "        self.placeholders = placeholders if placeholders else self.placeholders\n",
    "        \n",
    "        if template:\n",
    "            print('Opening slides:', self.template)\n",
    "\n",
    "        self.ppt = pptx.Presentation(self.template)\n",
    "        self.slides = self.ppt.slides\n",
    "        \n",
    "        if (not slide_width_mm is None) and (not slide_height_mm is None):\n",
    "            self.ppt.slide_width = Mm(slide_width_mm)\n",
    "            self.ppt.slide_height = Mm(slide_height_mm)\n",
    "        \n",
    "    # Add slide\n",
    "    def add_slide(self, layout):\n",
    "        slide_layout = self.ppt.slide_layouts[layout]\n",
    "        slide = self.slides.add_slide(slide_layout)\n",
    "        return slide\n",
    "    \n",
    "    def set_text(self, slide, placeholder_name, text):\n",
    "        try:\n",
    "            layout = self.ppt.slide_layouts.index(slide.slide_layout)\n",
    "            placeholder_id = self.placeholders.get(layout,{}).get(placeholder_name,{}).get('id',None)\n",
    "            placeholder = slide.placeholders[placeholder_id]\n",
    "            placeholder.text = text\n",
    "        except Exception as e:\n",
    "            print('Failed to add text to placeholder: ', placeholder_name)\n",
    "            print(e)\n",
    "            pass\n",
    "        \n",
    "    def add_textbox(self, slide, text, left_mm, top_mm, width_mm, height_mm):\n",
    "        txtBox = slide.shapes.add_textbox(Mm(left_mm), Mm(top_mm), Mm(width_mm), Mm(height_mm))\n",
    "        txtBox.text_frame.text = text\n",
    "    \n",
    "    def get_slide_placeholder_size(self, slide, placeholder_name):\n",
    "        try:\n",
    "            layout = self.ppt.slide_layouts.index(slide.slide_layout)\n",
    "            placeholder_id = self.placeholders.get(layout,{}).get(placeholder_name,{}).get('id',None)\n",
    "            placeholder = slide.placeholders[placeholder_id]\n",
    "            sizes = (placeholder.left.mm, placeholder.top.mm, placeholder.width.mm, placeholder.height.mm)\n",
    "        except Exception as e:\n",
    "            print('Failed to get placeholder: ', placeholder_name)\n",
    "            print(e)\n",
    "            pass\n",
    "        return sizes\n",
    "    \n",
    "    def get_slide_size(self):\n",
    "        slide_width = self.ppt.slide_width\n",
    "        slide_height = self.ppt.slide_height\n",
    "        return (Length(slide_width).mm, Length(slide_height).mm)\n",
    "\n",
    "    def add_image(self, slide, placeholder_name, image_file, keep_aspect_ratio=True, position='centre', delete_placeholder=True):\n",
    "        try:\n",
    "            # read image file to check actual aspect ratio\n",
    "            img = plt.imread(image_file)\n",
    "            img_height, img_width = img.shape[0], img.shape[1]\n",
    "            image_aspect_ratio = img_width/img_height\n",
    "            \n",
    "            # get placeholder details, including aspect ratio\n",
    "            layout = self.ppt.slide_layouts.index(slide.slide_layout)\n",
    "            placeholder_id = self.placeholders.get(layout,{}).get(placeholder_name,{}).get('id',None)\n",
    "            placeholder = slide.placeholders[placeholder_id]\n",
    "            placeholder_aspect_ratio = placeholder.width/placeholder.height\n",
    "            \n",
    "            # default positions; later calculate offset for centering if appropriate\n",
    "            x_pos = placeholder.left\n",
    "            y_pos = placeholder.top\n",
    "            width = placeholder.width\n",
    "            height = placeholder.height\n",
    "            \n",
    "            # determine which dimensions to use\n",
    "            if keep_aspect_ratio: # only set 1 dimension of image, to keep aspect ratio\n",
    "                use_image_width=True\n",
    "                if image_aspect_ratio < placeholder_aspect_ratio:\n",
    "                    use_image_width=False\n",
    "\n",
    "                if use_image_width:\n",
    "                    if position=='centre':\n",
    "                        new_img_height = img_height/img_width*width\n",
    "                        y_pos = y_pos + (height - new_img_height)/2\n",
    "                    pic = slide.shapes.add_picture(image_file, x_pos, y_pos, width=width)\n",
    "                else:\n",
    "                    if position=='centre':\n",
    "                        new_img_width = img_width/img_height*height\n",
    "                        x_pos = x_pos + (width - new_img_width)/2\n",
    "                    pic = slide.shapes.add_picture(image_file, x_pos, y_pos, height=height)\n",
    "                    \n",
    "            else: # set both dimensions of image, which can therefore change aspect ratio\n",
    "                pic = slide.shapes.add_picture(image_file, x_pos, y_pos, width=width, height=height)\n",
    "                \n",
    "            if delete_placeholder:\n",
    "                sp = placeholder._sp # get shape xml element\n",
    "                sp.getparent().remove(sp) # remove shape\n",
    "\n",
    "        except Exception as e:\n",
    "            print('Failed to add image to placeholder: ', placeholder_name, ', image filename:', image_file)\n",
    "            print(e)\n",
    "            pass\n",
    "\n",
    "    def save(self, outfile=None):\n",
    "        outfile = outfile if outfile else self.outfile\n",
    "        if not outfile:\n",
    "            print('Need to specify filename to save; existing')\n",
    "            quit()\n",
    "        self.ppt.save(outfile)\n",
    "\n",
    "    def slide_describe(self, slide, layout_name):\n",
    "        print('Slide layout:', layout_name)\n",
    "        try:\n",
    "            set_title(slide, layout_name)\n",
    "        except:\n",
    "            pass\n",
    "\n",
    "        txBox = slide.shapes.add_textbox(0, 0, 100, 100)\n",
    "        tf = txBox.text_frame\n",
    "        tf.text = f'{layout_name}; Placeholders:'\n",
    "        for placeholder in slide.placeholders:\n",
    "            size_description = (\"left=%d, top=%d, width=%d, height=%d (mm)\" % (placeholder.left.mm, placeholder.top.mm, placeholder.width.mm, placeholder.height.mm))\n",
    "            num_name = '%d; %s' % (placeholder.placeholder_format.idx, placeholder.name)\n",
    "            name_name_size = num_name + \" : \" + size_description\n",
    "\n",
    "            p = tf.add_paragraph()\n",
    "            p.text = name_name_size\n",
    "\n",
    "    def new_slide_describe(self, layout):\n",
    "        slide = self.add_slide(layout)\n",
    "        try:\n",
    "            layout_name = self.ppt.slide_layouts[layout].name #layout.name\n",
    "        except:\n",
    "            layout_name = ''\n",
    "        self.slide_describe(slide, layout_name)\n",
    "\n",
    "    def add_describe_template(self, known_layouts_only = True):\n",
    "        # to do: derive slide list to populate from template\n",
    "        if not known_layouts_only:\n",
    "            for slide_layout_num, slide_layout in enumerate(self.ppt.slide_layouts):\n",
    "                self.new_slide_describe(slide_layout_num)\n",
    "        else:\n",
    "            self.new_slide_describe(self.slide_template_layout.TITLE)\n",
    "            self.new_slide_describe(self.slide_template_layout.TITLE_AND_CONTENT)\n",
    "            self.new_slide_describe(self.slide_template_layout.TITLE_AND_MAX_CONTENT)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "annotate_template = False\n",
    "if annotate_template:\n",
    "    print('Describe slide template')\n",
    "    prs = PPT(template_pptx) # create new presentation using default template\n",
    "    prs.add_describe_template(known_layouts_only=False)\n",
    "    prs.save('template_annotated.pptx')\n",
    "    \n",
    "if export_slides:\n",
    "    # Create new presentation from (empty) template\n",
    "    prs = PPT(template_pptx) # create new presentation using default template"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'COVID-19 Summary'\n",
    "display_h1(slide_title)\n",
    "text_str = 'Analysis run: '+ run_date_str + '\\n'\n",
    "text_str = text_str + 'Latest data: '+ latest_date_str + '\\n'\n",
    "text_str = text_str + 'Total confirmed cases: {:,d}'.format(df_totals_world['confirmed'].sum().astype(int)) + '\\n'\n",
    "text_str = text_str + 'Total deaths: {:,d}'.format(df_totals_world['deaths'].sum().astype(int)) + '\\n'\n",
    "text_str = text_str + '\\n'\n",
    "text_str = text_str + 'COVID-19 data: https://github.com/CSSEGISandData/COVID-19' + '\\n'\n",
    "text_str = text_str + 'Mortality data: https://www.mortality.org' + '\\n'\n",
    "text_str = text_str + 'UK data: https://coronavirus.data.gov.uk' +'\\n'\n",
    "text_str = text_str + 'Vaccine data: https://ourworldindata.org/covid-vaccinations' + '\\n'\n",
    "text_str = text_str + '\\n'\n",
    "\n",
    "text_str = text_str + 'Analysis & summary by Colin Osborne: https://github.com/colin0brass/covid' + '\\n'\n",
    "# text_str = text_str + 'Country/region included in trends if confirmed cases above: {:,d}'.format(num_cases_threshold)\n",
    "\n",
    "print(text_str)\n",
    "\n",
    "if export_slides:\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.set_text(slide, placeholder_name='content', text=text_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trend_chart(df, metric='deaths', category=None, area_chart=True, plot_7_day_average=True,\n",
    "                x_min=None, x_max=None, y_min=None, y_max=None, y_range_from_avg=True, filter_last_weeks=None,\n",
    "                forward_fill=False, remove_trailing_zeros=True, per_population_percent=False,\n",
    "                chart_width=350, chart_height=150,\n",
    "                row_title='', column_title=''):\n",
    "    df = df.copy()\n",
    "\n",
    "    index_col_list = ['date']\n",
    "    if category:\n",
    "        category_list=['date', category]\n",
    "        \n",
    "        if forward_fill:\n",
    "            df[metric] = df.groupby([category], sort=False)[metric].apply(lambda x: x.ffill())\n",
    "\n",
    "        if per_population_percent:\n",
    "            df = df.groupby(category_list).agg({metric:'sum', 'Population':'sum'})\n",
    "            df = df.reset_index()\n",
    "            df[metric] = df[metric] / df['Population'] * 100\n",
    "        else:\n",
    "            df = df.groupby(category_list).agg({metric:'sum'})\n",
    "            df = df.reset_index()\n",
    "\n",
    "    if remove_trailing_zeros:\n",
    "        df = df_remove_trailing_zeros(df, metric)\n",
    "        \n",
    "    df[metric] = df[metric].fillna(0)\n",
    "    if plot_7_day_average:\n",
    "        line_metric = 'weekAvg'\n",
    "        if category:\n",
    "            df[line_metric] = df.groupby(category)[metric].rolling(window=7, min_periods=7).mean().reset_index(0, drop=True)\n",
    "        else:\n",
    "            df[line_metric] = df[metric].rolling(window=7, min_periods=7).mean().reset_index(0, drop=True)\n",
    "    else:\n",
    "        line_metric = metric\n",
    "\n",
    "    if y_range_from_avg:\n",
    "        metric_for_y_range = line_metric\n",
    "    else:\n",
    "        metric_for_y_range = metric\n",
    "        \n",
    "    x_min = df['date'].min() if not x_min else x_min\n",
    "    x_max = df['date'].max() if not x_max else x_max\n",
    "    \n",
    "    if filter_last_weeks:\n",
    "        x_min = x_max - datetime.timedelta(weeks=filter_last_weeks)\n",
    "        df = df[df['date']>=x_min]\n",
    "\n",
    "    if y_max is None: y_max = df[metric_for_y_range].max()\n",
    "    if y_min is None: y_min = df[metric_for_y_range].min()\n",
    "\n",
    "    chart = alt.Chart()\n",
    "    if area_chart:\n",
    "        chart = alt.Chart().mark_area().encode(\n",
    "            x=alt.X('date:T', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "            y   = metric+':Q',\n",
    "            tooltip=[alt.Tooltip('date:T'),\n",
    "                     alt.Tooltip(line_metric+':Q', format=',d'),\n",
    "                     alt.Tooltip(metric+':Q', format=',d')]\n",
    "        )\n",
    "\n",
    "        line = alt.Chart().mark_line(\n",
    "            color='red',\n",
    "        ).encode(\n",
    "            x=alt.X('date:T', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "            y=alt.Y(line_metric+':Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], clamp=True)),\n",
    "            tooltip=[alt.Tooltip('date:T'),\n",
    "                     alt.Tooltip(line_metric+':Q', format=',d'),\n",
    "                     alt.Tooltip(metric+':Q', format=',d')]\n",
    "        )\n",
    "        \n",
    "        chart = alt.layer(chart, line, data=df)\n",
    "\n",
    "    else: # not area chart\n",
    "        line = alt.Chart().mark_line(\n",
    "        ).encode(\n",
    "            x=alt.X('date:T', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "            y=alt.Y(line_metric+':Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], clamp=True)),\n",
    "            color=alt.Color(category+':N', sort=None),\n",
    "            tooltip=[alt.Tooltip('date:T'),\n",
    "                     alt.Tooltip(category+':N'),\n",
    "                     alt.Tooltip(line_metric+':Q', format=',d')]\n",
    "        )\n",
    "        \n",
    "        chart = alt.layer(line, data=df)\n",
    "        \n",
    "    chart = chart.properties(title=column_title, height=chart_height, width=chart_width)\n",
    "\n",
    "    return chart\n",
    "\n",
    "def bar_chart(df, metric='confirmed', sort_field='deaths', numbers_overlaid=False, category=None, per_population_percent=False, \n",
    "              x_min=None, x_max=None, chart_width=200, chart_height=150, rollup='latest', forward_fill=False, row_title='', column_title=''):\n",
    "    df = df.copy()\n",
    "    \n",
    "    if category:\n",
    "        category_list=['date', category]\n",
    "        \n",
    "        if forward_fill:\n",
    "            df[metric] = df.groupby([category], sort=False)[metric].apply(lambda x: x.ffill())\n",
    "\n",
    "        if per_population_percent:\n",
    "            if sort_field in df.columns:\n",
    "                df = df.groupby(category_list).agg({metric:'sum', sort_field:'sum', 'Population':'sum'})\n",
    "            else:\n",
    "                df = df.groupby(category_list).agg({metric:'sum', 'Population':'sum'})\n",
    "            df = df.reset_index()\n",
    "            df[metric] = df[metric] / df['Population'] * 100\n",
    "        else:\n",
    "            if sort_field in df.columns:\n",
    "                df = df.groupby(category_list).agg({metric:'sum', sort_field:'sum'})\n",
    "            else:\n",
    "                df = df.groupby(category_list).agg({metric:'sum'})\n",
    "            df = df.reset_index()\n",
    "        \n",
    "    if rollup=='latest':\n",
    "#         df = df[df['date']==df['date'].max()] # last value\n",
    "        df = df.groupby(category).last() # last\n",
    "        df = df.reset_index()\n",
    "    elif rollup=='total':\n",
    "        df = df.groupby(category).sum() # total\n",
    "        df = df.reset_index()\n",
    "    else:\n",
    "        print('Unknown rollup option:', rollup)\n",
    "        \n",
    "    x_min = 0 # df[metric].min() if not x_min else x_min\n",
    "    x_max = df[metric].max() if not x_max else x_max\n",
    "\n",
    "    sort_order = None # initial value\n",
    "    if sort_field and sort_field in df.columns and category is not None:\n",
    "        sort_order = list(df.sort_values(by=sort_field, ascending=False)[category].unique())\n",
    "\n",
    "    if numbers_overlaid:\n",
    "        bars = alt.Chart(df).mark_bar(\n",
    "        ).encode(\n",
    "            x=alt.X(metric+':Q', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "            y=alt.Y(category+':N', sort=sort_order, title=row_title), # sort='-x' to sort locally in this chart, or 'None' for no sort\n",
    "    #         color=alt.Color(f'{category}:N', sort=None, legend=None), # removed since this overrode the text colour below\n",
    "            tooltip=[alt.Tooltip(category+':N'),\n",
    "                     alt.Tooltip(metric+':Q', format=',d')]\n",
    "        )\n",
    "\n",
    "        text = bars.mark_text(\n",
    "            align = 'right',\n",
    "            baseline = 'middle',\n",
    "            dx = -15,\n",
    "            color = 'white',\n",
    "            fontSize=20\n",
    "        ).encode(\n",
    "            text=alt.Text(f'{metric}:Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "        )\n",
    "    else:\n",
    "        bars = alt.Chart(df).mark_bar(\n",
    "        ).encode(\n",
    "            x=alt.X(metric+':Q', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "            y=alt.Y(category+':N', sort=sort_order, title=row_title), # sort='-x' to sort locally in this chart, or 'None' for no sort\n",
    "            color=alt.Color(category+':N', sort=None, legend=None, scale=alt.Scale(scheme='category20')),\n",
    "            tooltip=[alt.Tooltip(category+':N'),\n",
    "                     alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "        )\n",
    "\n",
    "        text = bars.mark_text(\n",
    "            align    = 'left',\n",
    "            baseline = 'middle',\n",
    "            dx       = 3 # Nudges text to right so it doesn't appear on top of the bar\n",
    "        ).encode(\n",
    "            text=alt.Text(f'{metric}:Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "        )\n",
    "    \n",
    "    chart = (bars + text)\n",
    "    chart = chart.properties(title=column_title, height=chart_height, width=chart_width)\n",
    "    \n",
    "    return chart\n",
    "\n",
    "def configure_trend_chart(chart):\n",
    "    chart = chart.configure_axisY(\n",
    "        titleColor='blue',\n",
    "        titleFontSize=20,\n",
    "    )\n",
    "    \n",
    "    chart = chart.configure_title(\n",
    "        fontSize=20,\n",
    "        anchor='middle',\n",
    "        color='blue'\n",
    "    )\n",
    "    \n",
    "    return chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'Covid-19 Worldwide'\n",
    "display_h1(slide_title)\n",
    "\n",
    "def df_world_time_series_summary(df):\n",
    "    df_totals = df.copy().drop(['continent', 'Combined_Key', 'country', 'state'], axis=1)\n",
    "    df_totals = df_totals.groupby(['date']).agg({'lat':'mean', 'long':'mean', \n",
    "                                                 'confirmed':'sum', 'deaths':'sum', \n",
    "                                                 'Population':'sum'})\n",
    "    df_totals['confirmedRate'] = df_totals['confirmed'] / df_totals['Population'] * 100E3 # per 100k population\n",
    "    df_totals['deathsRate']    = df_totals['deaths']    / df_totals['Population'] * 100E3 # per 100k population\n",
    "    df_totals['areaType'] = 'World'\n",
    "    df_totals = df_totals.reset_index()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def df_continents_time_series_summary(df):\n",
    "    df_totals = df.copy().drop(['Combined_Key', 'country', 'state'], axis=1)\n",
    "    df_totals = df_totals.groupby(['date', 'continent']).agg({'lat':'mean', 'long':'mean', \n",
    "                                                              'confirmed':'sum', 'deaths':'sum', \n",
    "                                                              'Population':'sum'})\n",
    "\n",
    "    df_totals['confirmedRate'] = df_totals['confirmed'] / df_totals['Population'] * 100E3 # per 100k population\n",
    "    df_totals['deathsRate']    = df_totals['deaths']    / df_totals['Population'] * 100E3 # per 100k population\n",
    "\n",
    "    df_totals = df_totals.reset_index()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def get_world_overview_chart(df):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 110\n",
    "    chart_height = 150\n",
    "    \n",
    "    df_totals = get_totals_from_time_series(df)\n",
    "\n",
    "    df_continent_totals_absolute       = get_continent_totals(df_totals)\n",
    "\n",
    "    df_world_time_series = df_world_time_series_summary(df)\n",
    "    df_continents_time_series = df_continents_time_series_summary(df)\n",
    "\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    trend_confirmed_cases  = trend_chart(df_world_time_series, metric='confirmed', category=None, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='World', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent  = trend_chart(df_world_time_series, metric='confirmed', category=None, x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths = trend_chart(df_world_time_series, metric='deaths',    category=None, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent = trend_chart(df_world_time_series, metric='deaths',    category=None, x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Deaths 4wks')\n",
    "    total_confirmed_cases  = bar_chart(df_world_time_series, metric='confirmed', category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df_world_time_series, metric='deaths',   category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths')\n",
    "    trend_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    trend_confirmed_cases   = trend_chart(df_continents_time_series, metric='confirmed', category='continent', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='Continents', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent   = trend_chart(df_continents_time_series, metric='confirmed', category='continent', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths  = trend_chart(df_continents_time_series, metric='deaths',    category='continent', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent  = trend_chart(df_continents_time_series, metric='deaths',    category='continent', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='', column_title='Deaths 4wks')\n",
    "    total_confirmed_cases  = bar_chart(df_continents_time_series, metric='confirmed', category='continent', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df_continents_time_series, metric='deaths',   category='continent', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths')\n",
    "    continents_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    trend_confirmed_cases   = trend_chart(df_continents_time_series, metric='confirmedRate', category='continent', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          row_title='Continents', column_title='Daily Cases per 100k')\n",
    "    trend_confirmed_cases_recent   = trend_chart(df_continents_time_series, metric='confirmedRate', category='continent', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          row_title='', column_title='Cases 4wks/100k')\n",
    "    trend_confirmed_deaths  = trend_chart(df_continents_time_series, metric='deathsRate',    category='continent', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          row_title='', column_title='Daily Deaths per 100k')\n",
    "    trend_confirmed_deaths_recent  = trend_chart(df_continents_time_series, metric='deathsRate',    category='continent', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          row_title='', column_title='Deaths 4wks/100k')\n",
    "    total_confirmed_cases  = bar_chart(df_continents_time_series, metric='confirmedRate', sort_field='deathsRate', category='continent', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases per 100k')\n",
    "    total_confirmed_deaths  = bar_chart(df_continents_time_series, metric='deathsRate',   sort_field='deathsRate', category='continent', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths per 100k')\n",
    "    continents_rate_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    chart = trend_row & continents_row & continents_rate_row\n",
    "    \n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart(chart)\n",
    "\n",
    "    return chart\n",
    "\n",
    "footer = 'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "chart = get_world_overview_chart(df_covid_time_series_world)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'COVID-19 Trend - Absolute'\n",
    "display_h1(slide_title)\n",
    "\n",
    "top_n = 10\n",
    "\n",
    "def trend_dashboard_charts(df, top_n):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 100\n",
    "    chart_height = 110\n",
    "    \n",
    "    sort_field = 'deaths'\n",
    "    \n",
    "    df_totals = get_totals_from_time_series(df)\n",
    "    df_continents = df_continents_time_series_summary(df)\n",
    "\n",
    "    chart = alt.vconcat()\n",
    "    first_row = True\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    for continent in df['continent'].unique():\n",
    "        df_filtered = df[df['continent']==continent]\n",
    "        if not top_n is None:\n",
    "            df_totals_filtered = df_totals.copy()\n",
    "            df_totals_filtered = df_totals_filtered[df_totals_filtered['continent']==continent]\n",
    "            df_totals_filtered = df_totals_filtered.sort_values(by=sort_field, ascending=False)\n",
    "            df_totals_filtered = df_totals_filtered[:top_n]\n",
    "            combined_key_top_n = df_totals_filtered['Combined_Key']\n",
    "            df_filtered = df_filtered[df_filtered['Combined_Key'].isin(combined_key_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "        trend_confirmed_cases  = trend_chart(df_filtered, metric='confirmed', category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title=continent, column_title='Daily Cases' if first_row else '')\n",
    "        trend_confirmed_cases_recent  = trend_chart(df_filtered, metric='confirmed', category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Cases 4wks' if first_row else '')\n",
    "\n",
    "        trend_confirmed_deaths = trend_chart(df_filtered, metric='deaths',    category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Daily Deaths' if first_row else '')\n",
    "        trend_confirmed_deaths_recent = trend_chart(df_filtered, metric='deaths',    category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Deaths 4wks' if first_row else '')\n",
    "\n",
    "        total_confirmed_cases  = bar_chart(df_filtered, metric='confirmed', category='Combined_Key', rollup='total', numbers_overlaid=False,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Cases' if first_row else '')\n",
    "        total_confirmed_deaths  = bar_chart(df_filtered, metric='deaths',   category='Combined_Key', rollup='total', numbers_overlaid=False,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                            row_title='', column_title='Total Deaths' if first_row else '')\n",
    "\n",
    "        first_row = False\n",
    "\n",
    "        row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "        chart = chart & row\n",
    "\n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart(chart)\n",
    "\n",
    "    \n",
    "    return chart\n",
    "\n",
    "footer = f'Per continent, top {top_n} by total deaths; trend charts using 7 day rolling mean'\n",
    "\n",
    "chart = trend_dashboard_charts(df_covid_time_series_world, top_n)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'COVID-19 Trend - per 100k population'\n",
    "display_h1(slide_title)\n",
    "\n",
    "top_n = 10\n",
    "\n",
    "def trend_dashboard_charts(df, top_n):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 150 #100\n",
    "    chart_height = 110\n",
    "    \n",
    "    sort_field = 'deaths'\n",
    "    \n",
    "    df_totals = get_totals_from_time_series(df)\n",
    "    df_continents = df_continents_time_series_summary(df)\n",
    "\n",
    "    chart = alt.vconcat()\n",
    "    first_row = True\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    for continent in df['continent'].unique():\n",
    "        df_filtered = df[df['continent']==continent]\n",
    "        if not top_n is None:\n",
    "            df_totals_filtered = df_totals.copy()\n",
    "            df_totals_filtered = df_totals_filtered[df_totals_filtered['continent']==continent]\n",
    "            df_totals_filtered = df_totals_filtered.sort_values(by=sort_field, ascending=False)\n",
    "            df_totals_filtered = df_totals_filtered[:top_n]\n",
    "            combined_key_top_n = df_totals_filtered['Combined_Key']\n",
    "            df_filtered = df_filtered[df_filtered['Combined_Key'].isin(combined_key_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "        trend_confirmed_cases  = trend_chart(df_filtered, metric='confirmedRate', category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title=continent, column_title='Daily Cases per 100k' if first_row else '')\n",
    "        trend_confirmed_cases_recent  = trend_chart(df_filtered, metric='confirmedRate', category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Cases 4wks/100k' if first_row else '')\n",
    "\n",
    "        trend_confirmed_deaths = trend_chart(df_filtered, metric='deathsRate',    category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Daily Deaths per 100k' if first_row else '')\n",
    "        trend_confirmed_deaths_recent = trend_chart(df_filtered, metric='deathsRate',    category='Combined_Key', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=False, row_title='', column_title='Deaths 4wks/100k' if first_row else '')\n",
    "\n",
    "        total_confirmed_cases  = bar_chart(df_filtered, metric='confirmedRate', sort_field='deathsRate', category='Combined_Key', rollup='total', numbers_overlaid=False,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Cases per 100k' if first_row else '')\n",
    "        total_confirmed_deaths  = bar_chart(df_filtered, metric='deathsRate', sort_field='deathsRate', category='Combined_Key', rollup='total', numbers_overlaid=False,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                            row_title='', column_title='Total Deaths per 100k' if first_row else '')\n",
    "\n",
    "        first_row = False\n",
    "\n",
    "        row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "        chart = chart & row\n",
    "\n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart(chart)\n",
    "\n",
    "    \n",
    "    return chart\n",
    "\n",
    "footer = f'Per continent, top {top_n} by total deaths; trend charts using 7 day rolling mean'\n",
    "\n",
    "chart = trend_dashboard_charts(df_covid_time_series_world, top_n)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'COVID-19 Vaccinations - World View'\n",
    "display_h1(slide_title)\n",
    "\n",
    "country_population_threshold_to_include = 1000000\n",
    "\n",
    "def vaccine_trend_dashboard_charts_horizontal(df, country_population_threshold_to_include):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 100\n",
    "    chart_height_small = 110\n",
    "    chart_height_medium = 170\n",
    "    chart_height_large = 300\n",
    "    \n",
    "#     sort_field = 'deaths'\n",
    "    \n",
    "    chart = alt.hconcat()\n",
    "    first_col = True\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    if country_population_threshold_to_include:\n",
    "        df = df[df['population'] > country_population_threshold_to_include]\n",
    "\n",
    "    y_max_vaccinated = df['people_vaccinated_per_hundred'].max()\n",
    "    y_max_fully_vaccinated = df['people_fully_vaccinated_per_hundred'].max()\n",
    "    \n",
    "    for continent in df['continent'].unique():\n",
    "        df_filtered = df[df['continent']==continent]\n",
    "        \n",
    "        trend_people_vaccinated  = trend_chart(df_filtered, metric='people_vaccinated_per_hundred', category='country', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max_vaccinated,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height_small, \n",
    "                                             plot_7_day_average=False, forward_fill=True,\n",
    "                                             area_chart=False, row_title='% Vaccinated' if first_col else '', column_title=continent)\n",
    "\n",
    "        trend_people_fully_vaccinated  = trend_chart(df_filtered, metric='people_fully_vaccinated_per_hundred', category='country', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max_fully_vaccinated,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height_small, \n",
    "                                             plot_7_day_average=False, forward_fill=True,\n",
    "                                             area_chart=False, row_title='% Fully Vacc.' if first_col else '', column_title='')\n",
    "\n",
    "        total_people_vaccinated  = bar_chart(df_filtered, metric='people_vaccinated_per_hundred', sort_field='people_vaccinated_per_hundred', category='country', \n",
    "                                             rollup='latest', numbers_overlaid=False, forward_fill=True,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                             row_title='% Vaccinated' if first_col else '', column_title='')\n",
    "\n",
    "        total_people_fully_vaccinated  = bar_chart(df_filtered, metric='people_fully_vaccinated_per_hundred', sort_field='people_fully_vaccinated_per_hundred', category='country', \n",
    "                                             rollup='latest', numbers_overlaid=False, forward_fill=True,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                             row_title='% Fully Vaccinated' if first_col else '', column_title='')\n",
    "\n",
    "        first_col = False\n",
    "\n",
    "        col = trend_people_vaccinated & trend_people_fully_vaccinated & total_people_vaccinated & total_people_fully_vaccinated\n",
    "        chart = chart | col\n",
    "\n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart(chart)\n",
    "\n",
    "    \n",
    "    return chart\n",
    "\n",
    "footer = f'Per continent, vaccinations per country with population at least {country_population_threshold_to_include/1000000} million. Vaccinated is with at least 1 dose.'\n",
    "\n",
    "chart = vaccine_trend_dashboard_charts_horizontal(df_world_vaccines, country_population_threshold_to_include)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'Covid-19 UK'\n",
    "display_h1(slide_title)\n",
    "\n",
    "def configure_trend_chart_uk(chart):\n",
    "    chart = chart.configure_axisY(\n",
    "        titleColor='blue',\n",
    "        titleFontSize=20,\n",
    "    )\n",
    "    \n",
    "    chart = chart.configure_title(\n",
    "        fontSize=20,\n",
    "        anchor='middle',\n",
    "        color='blue'\n",
    "    )\n",
    "    \n",
    "    return chart\n",
    "\n",
    "def get_uk_overview_chart(df):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 110\n",
    "    chart_height = 150\n",
    "\n",
    "    df['date]'] = pd.to_datetime(df['date'])\n",
    "\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    trend_confirmed_cases  = trend_chart(df, metric='confirmed', category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='UK', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent  = trend_chart(df, metric='confirmed', category='areaType', x_min=x_min, x_max=x_max,  y_min=0,filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths = trend_chart(df, metric='deaths',    category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent = trend_chart(df, metric='deaths',    category='areaType', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Daily Deaths')\n",
    "    total_confirmed_cases  = bar_chart(df, metric='confirmed', category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                       chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df, metric='deaths',   category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                        chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths')\n",
    "    uk_trend_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    trend_confirmed_cases   = trend_chart(df, metric='confirmed', category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='Nations', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent   = trend_chart(df, metric='confirmed', category='areaName', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          area_chart=False, row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths  = trend_chart(df, metric='deaths',    category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          area_chart=False, row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent  = trend_chart(df, metric='deaths',    category='areaName', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          area_chart=False, row_title='', column_title='Deaths 4wks')\n",
    "    total_confirmed_cases  = bar_chart(df, metric='confirmed', category='areaName', rollup='total', numbers_overlaid=False,\n",
    "                                       chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df, metric='deaths',   category='areaName', rollup='total', numbers_overlaid=False,\n",
    "                                        chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths')\n",
    "    uk_nations_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    trend_confirmed_cases   = trend_chart(df, metric='confirmedRate', category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                          row_title='Nations', column_title='Daily Cases per 100k')\n",
    "    trend_confirmed_cases_recent   = trend_chart(df, metric='confirmedRate', category='areaName', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                          row_title='', column_title='Cases 4wks/100k')\n",
    "    trend_confirmed_deaths  = trend_chart(df, metric='deathsRate',    category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                          row_title='', column_title='Daily Deaths per 100k')\n",
    "    trend_confirmed_deaths_recent  = trend_chart(df, metric='deathsRate',    category='areaName', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                          area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                          row_title='', column_title='Deaths 4wks/100k')\n",
    "    total_confirmed_cases  = bar_chart(df, metric='confirmedRate', sort_field='deathsRate', category='areaName', rollup='total', numbers_overlaid=False,\n",
    "                                       chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Total Cases per 100k')\n",
    "    total_confirmed_deaths  = bar_chart(df, metric='deathsRate',   sort_field='deathsRate', category='areaName', rollup='total', numbers_overlaid=False,\n",
    "                                        chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                        row_title='', column_title='Total Deaths per 100k')\n",
    "    uk_nations_rate_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    chart = uk_trend_row & uk_nations_row & uk_nations_rate_row\n",
    "    \n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart_uk(chart)\n",
    "\n",
    "    return chart\n",
    "\n",
    "footer = 'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "print('England: Data include only pillar 1 cases until 2 July, from when pillar 2 cases are also included.')\n",
    "\n",
    "chart = get_uk_overview_chart(df_uk_overview)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Authorities with Highest Local Case Rates'\n",
    "display_h1(slide_title)\n",
    "footer = 'Case rates per 100k population, from highest weekly Middle Layer Super Output Areas (MSOA) data'\n",
    "\n",
    "chart_height = 200\n",
    "chart_width  = 300\n",
    "\n",
    "def df_latest_max_with_delta(df, series_field='date', group_for_max='areaName', group_for_delta='areaName', value_field='value'):\n",
    "    df = df.copy()\n",
    "\n",
    "    df = df.groupby(group_for_max).agg({value_field: 'max'}).reset_index()\n",
    "\n",
    "    df['delta'] = df.groupby(group_for_delta)[value_field].transform(lambda x: x.diff())\n",
    "    latest_date = df[series_field].max()\n",
    "    df = df[df[series_field]==latest_date]\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_rate_bar_chart(df, category='index', metric='value', colour_field='delta', x_min=None, x_max=None, colour_field_max=None, row_title='', column_title='', top_n=20):\n",
    "    df = df.copy()\n",
    "\n",
    "    df = df.reset_index() # to ensure important fields are accessible in columns rather than hidden in index\n",
    "\n",
    "    sort_order=None\n",
    "    if not top_n is None:\n",
    "        sort_order = list(df.sort_values(by=metric, ascending=False)[category].unique())\n",
    "        sort_order = sort_order[:top_n]\n",
    "        df = df[df[category].isin(sort_order)]\n",
    "\n",
    "    x_min = 0\n",
    "    if not x_max:\n",
    "        x_max = df[metric].max()\n",
    "        \n",
    "    if not colour_field_max:\n",
    "        colour_field_max = df[colour_field].max()\n",
    "\n",
    "    legend_list = ['-800 or less', '-400 to -800', '0 to -400', '0 to +400', '+400 to +800', '+800 or more']\n",
    "    # CSS colours: https://www.w3schools.com/colors/colors_shades.asp\n",
    "    legend_colour_list = ['green', 'darkseagreen', 'palegreen', 'moccasin', 'orange', 'red']\n",
    "\n",
    "    df['legend'] = pd.cut(df[colour_field], \n",
    "                          [-np.inf, -800, -400, 0, 400, 800, +np.inf], \n",
    "                          labels=legend_list)\n",
    "    \n",
    "    bars = alt.Chart().mark_bar(\n",
    "    ).encode(\n",
    "        x=alt.X(f'{metric}:Q', scale=alt.Scale(domain=[x_min, x_max])),\n",
    "        y=alt.Y(f'{category}:N', sort=sort_order, title=row_title), # sort='-x' to sort locally in this chart \n",
    "        color=alt.Color('legend:N', \n",
    "                        scale=alt.Scale(domain=legend_list, range=legend_colour_list),\n",
    "                        legend=alt.Legend(title=\"Change since previous week\"),\n",
    "                       ),\n",
    "\n",
    "        tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d'),\n",
    "                 alt.Tooltip(f'{colour_field}:Q', format=',d')]\n",
    "\n",
    "    )\n",
    "\n",
    "    text = bars.mark_text(\n",
    "        align='left',\n",
    "        baseline='middle',\n",
    "        dx=3  # Nudges text to right so it doesn't appear on top of the bar\n",
    "    ).encode(\n",
    "        text=alt.Text(f'{metric}:Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "    )\n",
    "    \n",
    "    chart = alt.layer(bars, text, data=df)\n",
    "    chart = chart.properties(title=column_title, height=chart_height, width=chart_width)\n",
    "    \n",
    "    return chart\n",
    "\n",
    "def facet_wrap(subplts, plots_per_row):\n",
    "    rows = [subplts[i:i+plots_per_row] for i in range(0, len(subplts), plots_per_row)]\n",
    "    compound_chart = alt.vconcat()\n",
    "    for r in rows:\n",
    "        rowplot = alt.vconcat() #start a new row\n",
    "        for item in r:\n",
    "            rowplot |= item #add suplot to current row as a new column\n",
    "        compound_chart &= rowplot # add the entire row of plots as a new row\n",
    "\n",
    "    return compound_chart\n",
    "\n",
    "def configure_chart(chart):\n",
    "    chart = chart.configure_axisY(\n",
    "        titleColor='blue',\n",
    "        titleFontSize=20,\n",
    "    )\n",
    "    \n",
    "    chart = chart.configure_title(\n",
    "        fontSize=20,\n",
    "        anchor='middle',\n",
    "        color='blue'\n",
    "    )\n",
    "    return chart\n",
    "\n",
    "def get_rate_charts_per_region(df, top_n=20, plots_per_row=3):\n",
    "    df = df_latest_max_with_delta(df, group_for_max=['date', 'RegionName', 'LTLAName'], group_for_delta=['RegionName', 'LTLAName'])\n",
    "    \n",
    "    sort_field = 'value'\n",
    "    category_field = 'LTLAName'\n",
    "    \n",
    "    selection_list_top_n = []\n",
    "    if top_n is None:\n",
    "        df_filtered = df.copy()\n",
    "    else:\n",
    "        for region in df['RegionName'].unique():\n",
    "            df_filtered = df[df['RegionName']==region]\n",
    "            if (not df_filtered.empty) and (sort_field in df_filtered):\n",
    "                df_filtered = df_filtered.sort_values(by=sort_field, ascending=False)\n",
    "                df_filtered = df_filtered[:top_n]\n",
    "                selection_top_n = list(df_filtered[category_field].unique())\n",
    "                selection_list_top_n = selection_list_top_n + selection_top_n\n",
    "        df_filtered = df[df[category_field].isin(selection_list_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "    value_max = df_filtered['value'].max()\n",
    "    colour_field_max = 1000#df_filtered['delta'].max()\n",
    "\n",
    "    rates_chart_list = []\n",
    "    for region in df['RegionName'].unique():\n",
    "        df_facet = df_filtered[df_filtered['RegionName']==region]\n",
    "        sorted_order = df_facet.sort_values(by=sort_field, ascending=False)[category_field].unique()\n",
    "\n",
    "        rates_chart = get_rate_bar_chart(df_facet, category=category_field, metric='value', colour_field='delta', x_max=value_max, colour_field_max=colour_field_max, column_title=region, top_n=top_n)\n",
    "        rates_chart_list.append(rates_chart)\n",
    "\n",
    "    chart = facet_wrap(rates_chart_list, plots_per_row=plots_per_row)#.properties(title='MSOA Rates per Region')\n",
    "    chart = configure_chart(chart)\n",
    "\n",
    "    return chart\n",
    "    \n",
    "chart = get_rate_charts_per_region(df_uk_msoa_with_regions, top_n=10)\n",
    "print('Latest date:', df_uk_msoa_with_regions['date'].max())\n",
    "chart.display()\n",
    "\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Cases by Age Group'\n",
    "display_h1(slide_title)\n",
    "\n",
    "footer = 'Cases by age group, as 7 day rolling average'\n",
    "\n",
    "def df_get_case_age_breakdown_trend(df, field): # maleCasesValue, maleCasesRate, femaleCasesValue, femaleCasesRate\n",
    "    df = df.copy()\n",
    "    df = df.reset_index()\n",
    "    \n",
    "    df = df[df[field].notnull()]\n",
    "    df_age_data = pd.DataFrame(df[field].to_list())\n",
    "#     df_age_data = df_age_data.rename(columns={'5_to_9':'05_to_09', '0_to_4':'00_to_04'})\n",
    "    df_age_data = df_age_data.drop(['00_59', '60+'], axis='columns', errors='ignore')\n",
    "    cols = sorted(df_age_data.columns.tolist())\n",
    "    df_age_data = df_age_data[cols]\n",
    "    \n",
    "    index_cols = ['date']\n",
    "    df = pd.concat([df[index_cols], df_age_data], axis='columns')\n",
    "    \n",
    "    df = df.melt('date', var_name='ageRange', value_name='value')\n",
    "    df = df.set_index(index_cols)\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_age_trend_chart(df):\n",
    "    chart_width = 300\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 110\n",
    "    chart_height = 300\n",
    "\n",
    "    df_age_cases = df_get_case_age_breakdown_trend(df, 'casesByAge')\n",
    "    df_age_deaths = df_get_case_age_breakdown_trend(df, 'deathsByAge')\n",
    "    chart_age_cases = trend_chart(df_age_cases, metric='value', category='ageRange', y_min=0,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, \n",
    "                                  chart_width=chart_width, chart_height=chart_height, \n",
    "                                  row_title='Absolute', column_title='Daily Cases')\n",
    "    chart_age_cases_recent = trend_chart(df_age_cases, metric='value', category='ageRange', y_min=0, filter_last_weeks=4,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, \n",
    "                                  chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Cases 4wks')\n",
    "    chart_age_deaths = trend_chart(df_age_deaths, metric='value', category='ageRange', y_min=0,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, \n",
    "                                  chart_width=chart_width, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Daily Deaths')\n",
    "    chart_age_deaths_recent = trend_chart(df_age_deaths, metric='value', category='ageRange', y_min=0, filter_last_weeks=4,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, \n",
    "                                  chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Deaths 4wks')\n",
    "    total_age_cases = bar_chart(df_age_cases, metric='value', category='ageRange', rollup='total', numbers_overlaid=False,\n",
    "                                  chart_width=chart_width_small, chart_height=chart_height,\n",
    "                                  row_title='', column_title='Total Cases')\n",
    "    total_age_deaths = bar_chart(df_age_deaths, metric='value', category='ageRange', rollup='total', numbers_overlaid=False,\n",
    "                                  chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Total Deaths')\n",
    "    row1 = chart_age_cases | chart_age_cases_recent | chart_age_deaths | chart_age_deaths_recent | total_age_cases | total_age_deaths\n",
    "\n",
    "    \n",
    "    df_age_cases = df_get_case_age_breakdown_trend(df, 'casesRateByAge')\n",
    "    df_age_deaths = df_get_case_age_breakdown_trend(df, 'deathsRateByAge')\n",
    "    chart_age_cases = trend_chart(df_age_cases, metric='value', category='ageRange', y_min=0,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                  chart_width=chart_width, chart_height=chart_height, \n",
    "                                  row_title='Per 100k Population', column_title='Daily Cases per 100k')\n",
    "    chart_age_cases_recent = trend_chart(df_age_cases, metric='value', category='ageRange', y_min=0, filter_last_weeks=4,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                  chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Cases 4wks/100k')\n",
    "    chart_age_deaths = trend_chart(df_age_deaths, metric='value', category='ageRange', y_min=0,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                  chart_width=chart_width, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Daily Deaths per 100k')\n",
    "    chart_age_deaths_recent = trend_chart(df_age_deaths, metric='value', category='ageRange', y_min=0, filter_last_weeks=4,\n",
    "                                  area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                  chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Deaths 4wks/100k')\n",
    "    total_age_cases = bar_chart(df_age_cases, metric='value', category='ageRange', rollup='total', numbers_overlaid=False,\n",
    "                                  chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Total Cases per 100k')\n",
    "    total_age_deaths = bar_chart(df_age_deaths, metric='value', category='ageRange', rollup='total', numbers_overlaid=False,\n",
    "                                  chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                  row_title='', column_title='Total Deaths per 100k')\n",
    "    row2 = chart_age_cases | chart_age_cases_recent | chart_age_deaths | chart_age_deaths_recent | total_age_cases | total_age_deaths\n",
    "    \n",
    "    chart = row1 & row2\n",
    "    \n",
    "    chart = configure_trend_chart(chart)\n",
    "    \n",
    "    return chart\n",
    "\n",
    "if not df_uk_age.empty:\n",
    "    chart = get_age_trend_chart(df_uk_age)\n",
    "    chart.display()\n",
    "\n",
    "    print(footer)\n",
    "\n",
    "    if export_slides:\n",
    "        chart.save(saved_image_name)\n",
    "\n",
    "        # Add a new slide ready for chart and set title text\n",
    "        slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "        prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "        prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "        prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Hospitals'\n",
    "display_h1(slide_title)\n",
    "footer = 'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "print('England: Data include only pillar 1 cases until 2 July, from when pillar 2 cases are also included.')\n",
    "\n",
    "def df_get_case_age_breakdown_trend_hospital(df, field): # maleCasesValue, maleCasesRate, femaleCasesValue, femaleCasesRate\n",
    "    df = df.copy()\n",
    "    df = df.reset_index()\n",
    "    \n",
    "    df = df.filter(['date', field])[df[field].notnull()]\n",
    "    df = pd.concat([df.drop([field], axis=1), df[field].apply(pd.Series)], axis=1)\n",
    "    df = df.rename(columns={'6_to_17':'06_to_17', '0_to_5':'00_to_05'})\n",
    "\n",
    "    cols = sorted(df.columns.tolist())\n",
    "    df = df[cols]\n",
    "    \n",
    "    index_cols = ['date']\n",
    "    df = df.melt('date', var_name='ageRange', value_name='value')\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_uk_hospital_chart(df, df_age):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 500\n",
    "    chart_width_small = 300\n",
    "    chart_width_smaller = 110\n",
    "    chart_height      = 150\n",
    "    \n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    df_hospital_cases = df_create_categories(df, index_col='date', columns=['date', 'hospitalCases', 'covidOccupiedMVBeds'], category_field_name='category', value_field_name='value')\n",
    "\n",
    "    df['cumAdmissionsByAgeValue'] = df_age[\"cumAdmissionsByAge\"].apply(lambda x : unpack_list_of_dict(x, 'age', 'value') )\n",
    "    df_hospital_cases_age = df_get_case_age_breakdown_trend_hospital(df, 'cumAdmissionsByAgeValue')\n",
    "    df_hospital_cases_age = df_delta_from_cumulative(df_hospital_cases_age, metric='value', category='ageRange', new_metric='delta_value')\n",
    "\n",
    "    trend_hospital_admissions  = trend_chart(df, metric='hospitalAdmissions', category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             y_range_from_avg=False,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             row_title='Total', column_title='Daily Admissions')\n",
    "    trend_hospital_admissions_recent  = trend_chart(df, metric='hospitalAdmissions', category='areaType', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             y_range_from_avg=False,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             row_title='', column_title='Admissions 4wks')\n",
    "    total_hospital_admissions  = bar_chart(df, metric='hospitalAdmissions', category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Admissions')\n",
    "    hospital_admissions_row = trend_hospital_admissions | trend_hospital_admissions_recent | total_hospital_admissions\n",
    "    \n",
    "    trend_hospital_admissions_age = trend_chart(df_hospital_cases_age, metric='delta_value', category='ageRange', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                                area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True, \n",
    "                                                chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                                row_title='By Age Group', column_title='Daily Admissions')\n",
    "    trend_hospital_admissions_age_recent = trend_chart(df_hospital_cases_age, metric='delta_value', category='ageRange', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                                area_chart=False, remove_trailing_zeros=True, plot_7_day_average=True, \n",
    "                                                chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                                row_title='', column_title='Admissions 4wks')\n",
    "    total_hospital_admissions_age = bar_chart(df_hospital_cases_age, metric='delta_value', category='ageRange', rollup='total', numbers_overlaid=False,\n",
    "                                              chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                              row_title='', column_title='')\n",
    "    hospital_admissions_age_row = trend_hospital_admissions_age | trend_hospital_admissions_age_recent | total_hospital_admissions_age\n",
    "    \n",
    "    trend_hospital_cases   = trend_chart(df_hospital_cases, metric='value', category='category', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='Total', column_title='Hospital Cases')\n",
    "    trend_hospital_cases_recent   = trend_chart(df_hospital_cases, metric='value', category='category', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                         area_chart=False, remove_trailing_zeros=True, plot_7_day_average=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Cases 4wks')\n",
    "    latest_hospital_cases  = bar_chart(df_hospital_cases, metric='value', sort_field='value', category='category', rollup='latest', numbers_overlaid=False,\n",
    "                                       chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                       row_title='', column_title='Latest')\n",
    "    hospital_cases_row = trend_hospital_cases | trend_hospital_cases_recent | latest_hospital_cases\n",
    "    \n",
    "    chart = hospital_admissions_row & hospital_admissions_age_row & hospital_cases_row\n",
    "    \n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart_uk(chart)\n",
    "\n",
    "    return chart\n",
    "\n",
    "if not df_uk_hospital_england.empty:\n",
    "    chart = get_uk_hospital_chart(df_uk_hospital_england, df_uk_hospital_age_england)\n",
    "    chart.display()\n",
    "    print(footer)\n",
    "\n",
    "    if export_slides:\n",
    "        chart.save(saved_image_name)\n",
    "\n",
    "        # Add a new slide ready for chart and set title text\n",
    "        slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "        prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "        prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "        prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'UK Covid Vaccinations'\n",
    "display_h1(slide_title)\n",
    "footer = 'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "# def df_cumulative_from_delta(df, metric, category, new_metric):\n",
    "def get_uk_vaccinations_chart(df):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 200\n",
    "    chart_width_small = 150\n",
    "    chart_height      = 100\n",
    "    \n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    trend_total_first  = trend_chart(df, metric='cumVaccineFirstDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='UK', column_title='Cumulative First Dose')\n",
    "    trend_total_second  = trend_chart(df, metric='cumVaccineSecondDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='', column_title='Cumulative Second Dose')\n",
    "    trend_total_third   = trend_chart(df, metric='cumVaccineThirdDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='', column_title='Cumulative Third Dose')\n",
    "    total_first  = bar_chart(df, metric='cumVaccineFirstDose', category='areaType', rollup='latest', numbers_overlaid=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total First Dose')\n",
    "    total_second  = bar_chart(df, metric='cumVaccineSecondDose', category='areaType', rollup='latest', numbers_overlaid=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Second Dose')\n",
    "    total_third   = bar_chart(df, metric='cumVaccineThirdDose', category='areaType', rollup='latest', numbers_overlaid=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Third Dose')\n",
    "\n",
    "    total_row = trend_total_first | trend_total_second |  trend_total_third| total_first | total_second | total_third\n",
    "    \n",
    "    trend_nation_total_first   = trend_chart(df, metric='cumVaccineFirstDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='Nations', column_title='')\n",
    "    trend_nation_total_second  = trend_chart(df, metric='cumVaccineSecondDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='', column_title='')\n",
    "    trend_nation_total_third   = trend_chart(df, metric='cumVaccineThirdDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False,\n",
    "                               row_title='', column_title='')\n",
    "    total_nation_first   = bar_chart(df, metric='cumVaccineFirstDose', category='areaName', \n",
    "                              sort_field='cumVaccineFirstDose', rollup='latest', numbers_overlaid=False,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_second  = bar_chart(df, metric='cumVaccineSecondDose', category='areaName', \n",
    "                              sort_field='cumVaccineSecondDose', rollup='latest', numbers_overlaid=False,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_third   = bar_chart(df, metric='cumVaccineThirdDose', category='areaName', \n",
    "                              sort_field='cumVaccineSecondDose', rollup='latest', numbers_overlaid=False,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_row = trend_nation_total_first | trend_nation_total_second | trend_nation_total_third | total_nation_first | total_nation_second | total_nation_third\n",
    "\n",
    "\n",
    "    y_max = 100\n",
    "    trend_total_percent_first   = trend_chart(df, metric='cumVaccineFirstDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='UK', column_title='Cumulative % 1st Dose')\n",
    "    trend_total_percent_second  = trend_chart(df, metric='cumVaccineSecondDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='', column_title='Cumulative % 2nd Dose')\n",
    "    trend_total_percent_third   = trend_chart(df, metric='cumVaccineThirdDose', category='areaType', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='', column_title='Cumulative % 3rd Dose')\n",
    "    total_percent_first   = bar_chart(df, metric='cumVaccineFirstDose', category='areaType', rollup='latest', numbers_overlaid=True, per_population_percent=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total % 1st Dose')\n",
    "    total_percent_second  = bar_chart(df, metric='cumVaccineSecondDose', category='areaType', rollup='latest', numbers_overlaid=True, per_population_percent=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total % 2nd Dose')\n",
    "    total_percent_third   = bar_chart(df, metric='cumVaccineThirdDose', category='areaType', rollup='latest', numbers_overlaid=True, per_population_percent=True,\n",
    "                                           chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total % 3rd Dose')\n",
    "    total_row_percent = trend_total_percent_first | trend_total_percent_second | trend_total_percent_third | total_percent_first | total_percent_second | total_percent_third\n",
    "\n",
    "    trend_nation_total_percent_first   = trend_chart(df, metric='cumVaccineFirstDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='Nations', column_title='')\n",
    "    trend_nation_total_percent_second  = trend_chart(df, metric='cumVaccineSecondDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='', column_title='')\n",
    "    trend_nation_total_percent_third   = trend_chart(df, metric='cumVaccineThirdDose', category='areaName', x_min=x_min, x_max=x_max, y_min=0, y_max=y_max,\n",
    "                               chart_width=chart_width_large, chart_height=chart_height, \n",
    "                               area_chart=False, plot_7_day_average=False, per_population_percent=True,\n",
    "                               row_title='', column_title='')\n",
    "    total_nation_percent_first   = bar_chart(df, metric='cumVaccineFirstDose', category='areaName', \n",
    "                              sort_field='cumVaccineFirstDose', rollup='latest', numbers_overlaid=False, per_population_percent=True,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_percent_second  = bar_chart(df, metric='cumVaccineSecondDose', category='areaName', \n",
    "                              sort_field='cumVaccineSecondDose', rollup='latest', numbers_overlaid=False, per_population_percent=True,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_percent_third   = bar_chart(df, metric='cumVaccineThirdDose', category='areaName', \n",
    "                              sort_field='cumVaccineThirdDose', rollup='latest', numbers_overlaid=False, per_population_percent=True,\n",
    "                               chart_width=chart_width_small, chart_height=chart_height, \n",
    "                               row_title='', column_title='')\n",
    "    total_nation_row_percent = trend_nation_total_percent_first | trend_nation_total_percent_second | trend_nation_total_percent_third | total_nation_percent_first | total_nation_percent_second | total_nation_percent_third\n",
    "\n",
    "    chart = total_row & total_nation_row & total_row_percent & total_nation_row_percent\n",
    "    \n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart_uk(chart)\n",
    "\n",
    "    return chart\n",
    "\n",
    "if len(df_uk_vaccinations)>0:\n",
    "    chart = get_uk_vaccinations_chart(df_uk_vaccinations)\n",
    "    chart.display()\n",
    "else:\n",
    "    print(\"No vaccinations data found\")\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Lower Tier Authorities'\n",
    "display_h1(slide_title)\n",
    "top_n = 15\n",
    "\n",
    "chart_height = 250\n",
    "trend_chart_width = 350\n",
    "totals_chart_width = 150\n",
    "\n",
    "trend_chart_width_large = 200 #300\n",
    "trend_chart_width_small = 150\n",
    "trend_chart_width_smaller = 100 #100\n",
    "# chart_height = 110\n",
    "\n",
    "\n",
    "def facet_wrap(subplts, plots_per_row):\n",
    "    rows = [subplts[i:i+plots_per_row] for i in range(0, len(subplts), plots_per_row)]\n",
    "    compound_chart = alt.vconcat()\n",
    "    for r in rows:\n",
    "        rowplot = alt.vconcat() #start a new row\n",
    "        for item in r:\n",
    "            rowplot |= item #add suplot to current row as a new column\n",
    "        compound_chart &= rowplot # add the entire row of plots as a new row\n",
    "\n",
    "    return compound_chart\n",
    "\n",
    "def df_get_daily_average_and_totals(df, index_col_list, metric):\n",
    "    df_daily = df.copy()\n",
    "    df_daily = df_daily.dropna(axis='index', subset=[metric])\n",
    "    df_daily = df_daily.set_index(index_col_list, append=True)\n",
    "    df_daily[metric] = df_daily.groupby(index_col_list)[metric].transform(lambda x: x.diff()).clip(lower=0)\n",
    "    df_totals = pd.DataFrame()\n",
    "    if not df_daily.empty:\n",
    "        df_totals = df_daily.groupby(index_col_list).agg({metric:'sum'})\n",
    "        df_daily['weekAvg'] = df_daily.groupby(index_col_list)[metric].rolling(window=7, min_periods=1).mean().reset_index(0, drop=True)\n",
    "    return df_daily.reset_index(), df_totals.reset_index()\n",
    "    \n",
    "def uk_regions_trend_dashboard_charts(df, filter_area_name_list=None, normalised=False, multiple_facets=True, plots_per_row=3, scale_type='linear', x_metric='date', top_n=None, chart_height=250, trend_chart_width=350, totals_chart_width=200):\n",
    "    df = df.copy()\n",
    "    \n",
    "    df = df[df['regionName'].notnull()]\n",
    "    \n",
    "    if not normalised:\n",
    "        confirmed_field = 'confirmed'\n",
    "        deaths_field = 'deaths'\n",
    "        normalised_title_string = ''\n",
    "        plot_7_day_average=True,\n",
    "    else:\n",
    "        confirmed_field = 'confirmedRate'\n",
    "        deaths_field = 'deathsRate' # not in data yet\n",
    "        normalised_title_string = ' per 100k Population'\n",
    "        plot_7_day_average=False,\n",
    "    sort_field = confirmed_field\n",
    "    \n",
    "    chart = alt.vconcat()\n",
    "    first_row = True\n",
    "    if filter_area_name_list:\n",
    "        df = df[df['areaName'].isin(filter_area_name_list)]\n",
    "    \n",
    "    x_min = df[x_metric].min()\n",
    "    x_max = df[x_metric].max()\n",
    "    \n",
    "    if not multiple_facets: # simple approach for now\n",
    "        df['regionName'] = 'Combined'\n",
    "        \n",
    "    authority_list_top_n = []\n",
    "    if top_n is None:\n",
    "        df_filtered = df.copy()\n",
    "    else:\n",
    "        for region in df['regionName'].unique():\n",
    "            df_filtered = df[df['regionName']==region]\n",
    "            if (not df_filtered.empty) and (sort_field in df_filtered):\n",
    "                df_filtered_daily, df_filtered_totals = df_get_daily_average_and_totals(df_filtered, index_col_list=['areaName'], metric=confirmed_field)\n",
    "                if not df_filtered_daily.empty:\n",
    "                    df_filtered_totals = df_filtered_totals.sort_values(by=sort_field, ascending=False)\n",
    "                    df_filtered_totals = df_filtered_totals[:top_n]\n",
    "                    authority_top_n = list(df_filtered_totals['areaName'].unique())\n",
    "                    authority_list_top_n = authority_list_top_n + authority_top_n\n",
    "        df_filtered = df[df['areaName'].isin(authority_list_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "    chart = alt.Chart()\n",
    "    df_daily, df_totals = df_get_daily_average_and_totals(df_filtered, index_col_list=['areaName'], metric=confirmed_field)\n",
    "    if not df_daily.empty:\n",
    "        y_max = df_filtered[confirmed_field].max()\n",
    "        total_max = df_totals[confirmed_field].max()\n",
    "    \n",
    "        trend_chart_list = []\n",
    "        totals_chart_list = []\n",
    "        for region in df['regionName'].unique():\n",
    "            df_facet = df_filtered[df_filtered['regionName']==region]\n",
    "            sorted_order = df_facet.sort_values(by=sort_field, ascending=False)['areaName'].unique()\n",
    "            trend_region = trend_chart(df_facet, metric=confirmed_field, category='areaName',\n",
    "                                       x_min=x_min, x_max=x_max, y_min=0, y_max=y_max, plot_7_day_average=plot_7_day_average,\n",
    "                                       chart_width=trend_chart_width_large, chart_height=chart_height, \n",
    "                                       remove_trailing_zeros=True, \n",
    "                                       area_chart=False, row_title='', column_title=region)\n",
    "            trend_region_recent  = trend_chart(df_facet, metric=confirmed_field, category='areaName', \n",
    "                                       x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                       chart_width=trend_chart_width_smaller, chart_height=chart_height, \n",
    "                                    area_chart=False, row_title='', column_title=region + ' 4wks' if first_row else '')\n",
    "            totals_region = bar_chart(df_facet, metric=confirmed_field, category='areaName',  sort_field=sort_field,\n",
    "                                      rollup='total', numbers_overlaid=False,\n",
    "                                      chart_width=totals_chart_width, chart_height=chart_height, \n",
    "                                      row_title='', column_title=region)\n",
    "\n",
    "            trend_chart_list.append(trend_region)\n",
    "            trend_chart_list.append(trend_region_recent)\n",
    "            totals_chart_list.append(totals_region)\n",
    "\n",
    "        trend_facet_chart = facet_wrap(trend_chart_list, plots_per_row=2*plots_per_row).properties(title='Daily Cases'+normalised_title_string)\n",
    "        totals_facet_chart = facet_wrap(totals_chart_list, plots_per_row=plots_per_row).properties(title='Total Cases'+normalised_title_string)\n",
    "\n",
    "        chart = trend_facet_chart | totals_facet_chart\n",
    "\n",
    "    return chart\n",
    "\n",
    "include_uk_local_authorities_absolute = True\n",
    "if not include_uk_local_authorities_absolute:\n",
    "    print('Not configured to show UK local authorities')\n",
    "else:\n",
    "    footer = f'Area charts and bar charts from daily data; line charts from 7 day rolling mean; highest {top_n} authorities by total confirmed cases'\n",
    "\n",
    "    chart = uk_regions_trend_dashboard_charts(df_uk_ltla_regions, normalised=False, top_n=top_n, chart_height=chart_height, trend_chart_width=trend_chart_width, totals_chart_width=totals_chart_width)\n",
    "    chart.display()\n",
    "    print(footer)\n",
    "\n",
    "    if export_slides:\n",
    "        chart.save(saved_image_name)\n",
    "\n",
    "        # Add a new slide ready for chart and set title text\n",
    "        slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "        prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "        prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "        prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Lower Tier Authorities per 100k Population'\n",
    "display_h1(slide_title)\n",
    "\n",
    "include_uk_local_authorities_per_population = True\n",
    "if not include_uk_local_authorities_per_population:\n",
    "    print('Not configured to show UK local authorities')\n",
    "else:\n",
    "    chart_height = 250\n",
    "    trend_chart_width = 350\n",
    "    totals_chart_width = 200\n",
    "\n",
    "    trend_chart_width_large = 200 #300\n",
    "    trend_chart_width_small = 150\n",
    "    trend_chart_width_smaller = 100 #100\n",
    "    # chart_height = 110\n",
    "\n",
    "    footer = f'Area charts and bar charts from daily data; line charts from 7 day rolling mean; highest {top_n} authorities by total confirmed cases'\n",
    "\n",
    "    chart = uk_regions_trend_dashboard_charts(df_uk_ltla_regions, normalised=True, top_n=top_n, chart_height=chart_height, trend_chart_width=trend_chart_width, totals_chart_width=totals_chart_width)\n",
    "    chart.display()\n",
    "    print(footer)\n",
    "\n",
    "    if export_slides:\n",
    "        chart.save(saved_image_name)\n",
    "\n",
    "        # Add a new slide ready for chart and set title text\n",
    "        slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "        prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "        prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "        prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'England Selected Areas'\n",
    "display_h1(slide_title)\n",
    "\n",
    "include_england_selected_areas = False\n",
    "if not include_england_selected_areas:\n",
    "    print('Not configured to show England selected areas')\n",
    "else:\n",
    "    chart_height = 300\n",
    "    trend_chart_width = 1000\n",
    "    totals_chart_width = 600\n",
    "\n",
    "    footer = f'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "    filter_area_name_list=['New Forest', 'South Cambridgeshire', 'Cambridge', 'Southampton', 'Huntingdonshire', 'East Suffolk', 'Leicester']\n",
    "    chart_abs = uk_regions_trend_dashboard_charts(df_uk_ltla_regions, filter_area_name_list=filter_area_name_list, normalised=False, multiple_facets=False, top_n=top_n, chart_height=chart_height, trend_chart_width=trend_chart_width, totals_chart_width=totals_chart_width)\n",
    "    chart_norm = uk_regions_trend_dashboard_charts(df_uk_ltla_regions, filter_area_name_list=filter_area_name_list, normalised=True, multiple_facets=False, top_n=top_n, chart_height=chart_height, trend_chart_width=trend_chart_width, totals_chart_width=totals_chart_width)\n",
    "    chart = chart_abs & chart_norm\n",
    "    chart.display()\n",
    "    print(footer)\n",
    "\n",
    "    if export_slides:\n",
    "        chart.save(saved_image_name)\n",
    "\n",
    "        # Add a new slide ready for chart and set title text\n",
    "        slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "        prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "        prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "        prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'Covid-19 US'\n",
    "display_h1(slide_title)\n",
    "\n",
    "top_n = 20\n",
    "\n",
    "def df_us_time_series_summary(df):\n",
    "    df_totals = df.copy().drop(['country', 'state'], axis=1)\n",
    "\n",
    "    df_totals = df_totals.groupby(['date']).agg({'lat':'mean', 'long':'mean', \n",
    "                                                 'confirmed':'sum', 'deaths':'sum', \n",
    "                                                 'Population':'sum'})\n",
    "    df_totals['areaType'] = 'US'\n",
    "    df_totals = df_totals.reset_index()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def df_states_time_series_summary(df):\n",
    "    df_totals = df.copy()\n",
    "    df_totals = df_totals.groupby(['date', 'state']).agg({'lat':'mean', 'long':'mean', \n",
    "                                                              'confirmed':'sum', 'deaths':'sum', \n",
    "                                                              'Population':'sum'})\n",
    "\n",
    "    df_totals = df_totals.reset_index()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "def get_us_overview_chart(df, top_n = 20):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 350\n",
    "    chart_width_small = 200\n",
    "    chart_width_smaller = 100\n",
    "    chart_height_large = 350\n",
    "    chart_height = 100\n",
    "\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    sort_field = 'deaths'\n",
    "    if not top_n is None:\n",
    "        df_totals = df.copy().groupby(['state']).agg({'confirmed':'sum', 'deaths':'sum'}).reset_index()\n",
    "        df_totals = df_totals.sort_values(by=sort_field, ascending=False)\n",
    "        df_totals = df_totals[:top_n]\n",
    "        state_top_n = df_totals['state']\n",
    "        df_filtered = df[df['state'].isin(state_top_n)] # would like to put it in the order of highest totals\n",
    "    else:\n",
    "        df_filtered = df\n",
    "\n",
    "    df_us = df_us_time_series_summary(df)\n",
    "    df_states = df_states_time_series_summary(df_filtered)\n",
    "\n",
    "\n",
    "    trend_confirmed_cases  = trend_chart(df_us, metric='confirmed', category=None, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='US', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent  = trend_chart(df_us, metric='confirmed', category=None, x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths = trend_chart(df_us, metric='deaths',    category=None, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent = trend_chart(df_us, metric='deaths',    category=None, x_min=x_min, x_max=x_max,  y_min=0,filter_last_weeks=4,\n",
    "                                         y_range_from_avg=False,\n",
    "                                         chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Deaths 4wks')\n",
    "    total_confirmed_cases  = bar_chart(df_us, metric='confirmed', category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df_us, metric='deaths',   category='areaType', rollup='total', numbers_overlaid=True,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                         row_title='', column_title='Total Deaths')\n",
    "    trend_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    trend_confirmed_cases   = trend_chart(df_states, metric='confirmed', category='state', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height,\n",
    "                                          area_chart=False, row_title='States', column_title='Daily Cases')\n",
    "    trend_confirmed_cases_recent   = trend_chart(df_states, metric='confirmed', category='state', x_min=x_min, x_max=x_max,  y_min=0, filter_last_weeks=4,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height,\n",
    "                                          area_chart=False, row_title='', column_title='Cases 4wks')\n",
    "    trend_confirmed_deaths  = trend_chart(df_states, metric='deaths',    category='state', x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          chart_width=chart_width_large, chart_height=chart_height,\n",
    "                                          area_chart=False, row_title='', column_title='Daily Deaths')\n",
    "    trend_confirmed_deaths_recent  = trend_chart(df_states, metric='deaths',    category='state', x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                          remove_trailing_zeros=True,\n",
    "                                          chart_width=chart_width_smaller, chart_height=chart_height,\n",
    "                                          area_chart=False, row_title='', column_title='Deaths 4wks')\n",
    "    latest_confirmed_cases  = bar_chart(df_us, metric='confirmed', category='areaType', rollup='latest', numbers_overlaid=True,\n",
    "                                          chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                          row_title='', column_title='Latest Cases')\n",
    "    latest_confirmed_deaths  = bar_chart(df_us, metric='deaths',   category='areaType', rollup='latest', numbers_overlaid=True,\n",
    "                                          chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                          row_title='', column_title='Latest Deaths')\n",
    "    states_row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "    \n",
    "    total_confirmed_cases  = bar_chart(df_states, metric='confirmed', category='state', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                       row_title='', column_title='Total Cases')\n",
    "    total_confirmed_deaths  = bar_chart(df_states, metric='deaths',   category='state', rollup='total', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                        row_title='', column_title='Total Deaths')\n",
    "    latest_confirmed_cases  = bar_chart(df_states, metric='confirmed', category='state', rollup='latest', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                       row_title='', column_title='Latest Cases')\n",
    "    latest_confirmed_deaths  = bar_chart(df_states, metric='deaths',   category='state', rollup='latest', numbers_overlaid=False,\n",
    "                                         chart_width=chart_width_small, chart_height=chart_height_large, \n",
    "                                        row_title='', column_title='Latest Deaths')\n",
    "    latest_row = total_confirmed_cases | total_confirmed_deaths | latest_confirmed_cases | latest_confirmed_deaths\n",
    "    \n",
    "    chart = trend_row & states_row & latest_row\n",
    "    \n",
    "    chart = configure_trend_chart(chart)\n",
    "\n",
    "    return chart\n",
    "\n",
    "footer = 'Area charts and bar charts from daily data; line charts from 7 day rolling mean'\n",
    "\n",
    "chart = get_us_overview_chart(df_covid_time_series_us, top_n = top_n)\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'Covid-19 US - States'\n",
    "display_h1(slide_title)\n",
    "\n",
    "chart_width_small = 120\n",
    "chart_height = 120\n",
    "\n",
    "top_n = 20\n",
    "\n",
    "def trend_chart_us_states(df, scale_type='linear', x_metric='date', metric='deaths', sort_field='deaths', x_min=None, x_max=None, row_title='', column_title=''):\n",
    "    df = df.copy()\n",
    "    df = df.reset_index()\n",
    "\n",
    "    x_metric_type = ':T' # for date\n",
    "    if x_metric == 'days_since_100':\n",
    "        df = df[df['days_since_100']>=0]\n",
    "        x_metric_type = ':Q' # for count of days\n",
    "        x_min = 0 # no need for negative x-axis\n",
    "        \n",
    "    df = df.groupby(['state', 'date']).agg({'confirmed':'sum', 'deaths':'sum'})\n",
    "    df = df.reset_index()\n",
    "\n",
    "    df['weekAvg'] = df.groupby('state')[metric].rolling(window=7, min_periods=1).mean().reset_index(0, drop=True)\n",
    "    \n",
    "    y_max = df[metric].max()\n",
    "    y_min = df[metric].min()\n",
    "    if scale_type == 'log':\n",
    "        y_min = max(y_min, 0.1)\n",
    "        small_non_zero_value = 0.001\n",
    "        df.loc[df['weekAvg']==0, 'weekAvg'] = small_non_zero_value # to enable log calculation without errors\n",
    "\n",
    "    chart = alt.Chart().mark_area(\n",
    "    ).encode(\n",
    "        x   = alt.X('date:T', title=None),\n",
    "        y   = metric+':Q',\n",
    "        tooltip=[alt.Tooltip('date:T'),\n",
    "                 alt.Tooltip(metric+':Q', format=',d')]\n",
    "    ).properties(height=chart_height, width=chart_width_small)\n",
    "\n",
    "    line = alt.Chart().mark_line(\n",
    "        color='red',\n",
    "    ).encode(\n",
    "        x='date:T',\n",
    "#         y=alt.Y('weekAvg:Q', title=row_title, scale=alt.Scale(type=scale_type, domain=[y_min, y_max], clamp=True)),\n",
    "        y=alt.Y('weekAvg:Q', title=row_title, scale=alt.Scale(type=scale_type, clamp=True)),\n",
    "        tooltip=[alt.Tooltip('date:T'),\n",
    "                 alt.Tooltip('weekAvg:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    chart = alt.layer(chart, line, data=df).facet(alt.Column('state:N', title=None), columns=5).resolve_scale(y='independent')\n",
    "        \n",
    "    chart = chart.properties(title=column_title)\n",
    "\n",
    "    return chart\n",
    "\n",
    "def configure_trend_chart_us_states(chart):\n",
    "    chart = chart.configure_headerRow(\n",
    "        title=None,\n",
    "    )\n",
    "    \n",
    "    chart = chart.configure_header(\n",
    "        labelFontSize=16\n",
    "    )\n",
    "\n",
    "    chart = chart.configure_axisY(\n",
    "        titleColor='blue',\n",
    "        titleFontSize=20,\n",
    "    )\n",
    "    \n",
    "    chart = chart.configure_title(\n",
    "        fontSize=20,\n",
    "        anchor='middle',\n",
    "        color='blue'\n",
    "    )\n",
    "    \n",
    "    return chart\n",
    "\n",
    "def us_states_chart_summary(df, top_n = 20, sort_field = 'deaths', scale_type = 'linear'):\n",
    "    df = df.copy()\n",
    "    \n",
    "    if not top_n is None:\n",
    "        df_totals = df.copy().groupby(['state']).agg({'confirmed':'sum', 'deaths':'sum'}).reset_index()\n",
    "        df_totals = df_totals.sort_values(by=sort_field, ascending=False)\n",
    "        df_totals = df_totals[:top_n]\n",
    "        state_top_n = df_totals['state']\n",
    "        df = df[df['state'].isin(state_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "    us_states_trend_confirmed = trend_chart_us_states(df, scale_type=scale_type, metric='confirmed', sort_field='confirmed', row_title='', column_title='Daily Cases')\n",
    "    us_states_trend_deaths    = trend_chart_us_states(df, scale_type=scale_type, metric='deaths', sort_field='deaths', row_title='', column_title='Daily Deaths')\n",
    "\n",
    "    chart = alt.hconcat(us_states_trend_confirmed, us_states_trend_deaths).configure_concat(spacing=100)\n",
    "    chart = configure_trend_chart_us_states(chart)\n",
    "    \n",
    "    return chart\n",
    "    \n",
    "footer = f'Area charts and bar charts from daily data; line charts from 7 day rolling mean; highest {top_n} states by total deaths'\n",
    "\n",
    "chart = us_states_chart_summary(df_covid_time_series_us, top_n = top_n, sort_field = 'deaths')\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "slide_title = 'Trend Charts - Selected Countries'\n",
    "display_h1(slide_title)\n",
    "small_subset = True\n",
    "measure_daily = True\n",
    "independent_y_scales = True\n",
    "\n",
    "if small_subset:\n",
    "#     Combined_Key_list = ['India', 'Brazil', 'Sweden', 'United Kingdom', 'US', 'Ireland', 'Israel']\n",
    "    Combined_Key_list = ['India', 'Poland', 'Sweden', 'United Kingdom', 'US', 'Ireland', 'Israel']\n",
    "#     Combined_Key_list = ['India', 'Brazil', 'Sweden', 'United Kingdom', 'US', 'Israel']\n",
    "\n",
    "    print('Country list:', Combined_Key_list, latest_date_str)\n",
    "    df_covid_time_series_filtered = df_covid_time_series_world[df_covid_time_series_world['Combined_Key'].isin(Combined_Key_list)].copy()\n",
    "else:\n",
    "    df_covid_time_series_filtered = df_covid_time_series_world.copy()\n",
    "\n",
    "def trend_country_dashboard_charts(df, top_n=None, category=None):\n",
    "    df = df.copy()\n",
    "    \n",
    "    chart_width_large = 350\n",
    "    chart_width_small = 150\n",
    "    chart_width_smaller = 110\n",
    "    chart_height = 80\n",
    "    \n",
    "    sort_field = 'deaths'\n",
    "    \n",
    "    df_totals = get_totals_from_time_series(df)\n",
    "    df_continents = df_continents_time_series_summary(df)\n",
    "\n",
    "    chart = alt.vconcat()\n",
    "    first_row = True\n",
    "    x_min = df['date'].min()\n",
    "    x_max = df['date'].max()\n",
    "    \n",
    "    deaths_max = df_totals['deaths'].max()\n",
    "    cases_max = df_totals['confirmed'].max()\n",
    "    \n",
    "    for category_item in df[category].unique():\n",
    "        df_filtered = df[df[category]==category_item]\n",
    "        if not top_n is None:\n",
    "            df_totals_filtered = df_totals.copy()\n",
    "            df_totals_filtered = df_totals_filtered[df_totals_filtered[category]==category_item]\n",
    "            df_totals_filtered = df_totals_filtered.sort_values(by=sort_field, ascending=False)\n",
    "            df_totals_filtered = df_totals_filtered[:top_n]\n",
    "            combined_key_top_n = df_totals_filtered['Combined_Key']\n",
    "            df_filtered = df_filtered[df_filtered['Combined_Key'].isin(combined_key_top_n)] # would like to put it in the order of highest totals\n",
    "\n",
    "        trend_confirmed_cases  = trend_chart(df_filtered, metric='confirmed', category=category, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=True, y_range_from_avg=False,\n",
    "                                             row_title=category_item, column_title='Daily Cases' if first_row else '')\n",
    "        trend_confirmed_cases_recent  = trend_chart(df_filtered, metric='confirmed', category=category, x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=True, y_range_from_avg=False,\n",
    "                                             row_title='', column_title='Cases 4wks' if first_row else '')\n",
    "        trend_confirmed_deaths = trend_chart(df_filtered, metric='deaths',    category=category, x_min=x_min, x_max=x_max, y_min=0,\n",
    "                                             chart_width=chart_width_large, chart_height=chart_height, \n",
    "                                             area_chart=True, y_range_from_avg=False,\n",
    "                                             row_title='', column_title='Daily Deaths' if first_row else '')\n",
    "        trend_confirmed_deaths_recent = trend_chart(df_filtered, metric='deaths',    category=category, x_min=x_min, x_max=x_max, y_min=0, filter_last_weeks=4,\n",
    "                                             chart_width=chart_width_smaller, chart_height=chart_height, \n",
    "                                             area_chart=True, y_range_from_avg=False,\n",
    "                                             row_title='', column_title='Deaths 4wks' if first_row else '')\n",
    "\n",
    "        total_confirmed_cases  = bar_chart(df_filtered, metric='confirmed', category=category, rollup='total', numbers_overlaid=False,\n",
    "                                             x_min=0, x_max=cases_max,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                           row_title='', column_title='Total Cases' if first_row else '')\n",
    "        total_confirmed_deaths  = bar_chart(df_filtered, metric='deaths',   category=category, rollup='total', numbers_overlaid=False,\n",
    "                                             x_min=0, x_max=deaths_max,\n",
    "                                             chart_width=chart_width_small, chart_height=chart_height, \n",
    "                                            row_title='', column_title='Total Deaths' if first_row else '')\n",
    "\n",
    "        first_row = False\n",
    "\n",
    "        row = trend_confirmed_cases | trend_confirmed_cases_recent | trend_confirmed_deaths | trend_confirmed_deaths_recent | total_confirmed_cases | total_confirmed_deaths\n",
    "        chart = chart & row\n",
    "\n",
    "    chart = chart.resolve_scale(color='independent')\n",
    "    chart = configure_trend_chart(chart)\n",
    "    \n",
    "    return chart\n",
    "\n",
    "footer = 'Blue area chart is daily data; red line is 7 day rolling mean'\n",
    "\n",
    "chart = trend_country_dashboard_charts(df_covid_time_series_filtered, category='Combined_Key')\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "current_year = 2020\n",
    "clip_positive_only = True\n",
    "\n",
    "# load country information\n",
    "# https://www.mortality.org/\n",
    "# https://www.mortality.org/Public/STMF_DOC/STMFNote.pdf\n",
    "# https://www.mortality.org/Public/HMD-countries-codes.pdf\n",
    "def load_short_term_mortality_timeseries_detailed():\n",
    "#     url = 'https://www.mortality.org/Public/STMF/Outputs/stmf.csv'\n",
    "    url = 'https://www.mortality.org/File/GetDocument/Public/STMF/Outputs/stmf.csv'\n",
    "    df = pd.read_csv(url)\n",
    "    \n",
    "    # Drop redundant first row and set column names\n",
    "    df = df.reset_index()\n",
    "    df.columns = df.iloc[0]\n",
    "    df = df.drop(df.index[0])\n",
    "\n",
    "    # Set data types\n",
    "    df['Week'] = df['Week'].astype(int)\n",
    "    df['Year'] = df['Year'].astype(int)\n",
    "    df['DTotal'] = df['DTotal'].astype(float).astype(int)\n",
    "    df[['D0_14', 'D15_64', 'D65_74', 'D75_84', 'D85p']] = df[['D0_14', 'D15_64', 'D65_74', 'D75_84', 'D85p']].astype(float)\n",
    "    df['RTotal'] = df['RTotal'].astype(float)\n",
    "\n",
    "    # - UK England & Wales Total Population GBRTENW\n",
    "    # - DE Total population DEUTNP\n",
    "    country_code_replace_dict = {'DEUTNP': 'DEU', 'GBRTENW': 'GBR', 'FRATNP': 'FRA'}\n",
    "    df['CountryCode'] = df['CountryCode'].replace(country_code_replace_dict)\n",
    "    country_code_to_name_dict = pycountry_convert.map_country_alpha3_to_country_name()\n",
    "    country_code_to_name_dict['GBR_SCO'] = 'Scotland'\n",
    "    country_code_to_name_dict['GBR_NIR'] = 'Northern Ireland'\n",
    "    country_code_to_name_dict['NZL_NP'] = 'New Zealand'\n",
    "    df['Country'] = df['CountryCode'].map(country_code_to_name_dict)\n",
    "    \n",
    "    country_unknown = df[df['Country'].isnull()]['CountryCode'].drop_duplicates()\n",
    "    if len(country_unknown):\n",
    "        print('Unknown country codes:', country_unknown)\n",
    "\n",
    "    return df\n",
    "\n",
    "def load_short_term_mortality_data_detailed():\n",
    "    df = load_short_term_mortality_timeseries_detailed()\n",
    "    \n",
    "    df = df.set_index(['Country', 'Year', 'Week', 'Sex'])\n",
    "    df = df[~(df.index.get_level_values('Sex')=='b')] # drop the combined figure for both sexes\n",
    "    \n",
    "    df = df.filter(['D0_14', 'D15_64', 'D65_74', 'D75_84', 'D85p'])\n",
    "    df = df.rename(columns={'D0_14':'Age 0-14', 'D15_64':'Age 15-64', 'D65_74':'Age 65-74', 'D75_84':'Age 75-84', 'D85p':'Age 85+'})\n",
    "\n",
    "    df = df.reset_index()\n",
    "#     df = pd.melt(df, id_vars=['Continent', 'Country', 'Year', 'Week', 'Sex'], var_name='AgeRange', value_name='Value')\n",
    "    df = pd.melt(df, id_vars=['Country', 'Year', 'Week', 'Sex'], var_name='AgeRange', value_name='Value')\n",
    "#     df = df.set_index(['Continent', 'Country', 'Year', 'Week', 'Sex', 'AgeRange'])\n",
    "    df = df.set_index(['Country', 'Year', 'Week', 'Sex', 'AgeRange'])\n",
    "    \n",
    "    df_duplicated = df.loc[df.index.duplicated(keep=False)]\n",
    "    if len(df_duplicated)>0:\n",
    "        print('Duplicated index rows:')\n",
    "        print(df_duplicated)\n",
    "#         df = df.loc[~df.index.duplicated(keep='first')] # remove any duplicate index rows (e.g. for Vela at multiple frequencies)\n",
    "\n",
    "    df = df.unstack(level=['AgeRange', 'Sex'])\n",
    "    df = df.sort_index(axis='columns', level='AgeRange')\n",
    "\n",
    "    return df\n",
    "\n",
    "def get_excess_deaths_detailed(df, current_year, clip_positive_only):\n",
    "    df = df.copy()\n",
    "    \n",
    "    df_previous_years = df.copy()\n",
    "    df_previous_years = df_previous_years[df_previous_years.index.get_level_values('Year')<current_year]\n",
    "    df_previous_years = df_previous_years.groupby(level=df_previous_years.index.names.difference(['Year']), axis=0).mean()\n",
    "\n",
    "    df_excess_deaths = df.copy()\n",
    "    df_excess_deaths = df_excess_deaths[df_excess_deaths.index.get_level_values('Year')==current_year]\n",
    "    df_excess_deaths = df_excess_deaths.reset_index(level='Year', drop=True) # don't need this any more\n",
    "    df_excess_deaths = df_excess_deaths - df_previous_years\n",
    "    \n",
    "    df_excess_deaths = df_excess_deaths.clip(lower=0) if clip_positive_only else df_excess_deaths\n",
    "    \n",
    "    return df_excess_deaths\n",
    "\n",
    "def get_totals_from_time_series_detailed(df):\n",
    "    df_totals = df.copy().groupby(['Country']).sum()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "display_h1('Mortality Analysis')\n",
    "\n",
    "df_stmf_detailed = load_short_term_mortality_data_detailed()\n",
    "df_excess_deaths_detailed = get_excess_deaths_detailed(df_stmf_detailed, current_year=current_year, clip_positive_only=clip_positive_only)\n",
    "df_excess_deaths_totals_detailed = get_totals_from_time_series_detailed(df_excess_deaths_detailed)\n",
    "\n",
    "df = df_stmf_detailed.reset_index()\n",
    "print('Countries available:', df['Country'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# independent_y_scales = True\n",
    "# chart_height = 100\n",
    "# chart_width = 200\n",
    "# current_year = 2020\n",
    "# clip_positive_only = True\n",
    "\n",
    "# def trend_chart_deaths_overall(df, current_year, scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "#     df = df.copy()\n",
    "    \n",
    "#     week_max = df[df['Year']==current_year]['Week'].max()\n",
    "#     week_trust_margin = 3\n",
    "#     week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "#     y_min = 0 # max(df[metric].min(), 0)\n",
    "#     y_max = df[metric].max()\n",
    "    \n",
    "#     line = alt.Chart().mark_line( #mark_area\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < 2020\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'mean({metric}):Q', format=',d')]\n",
    "#     )\n",
    "    \n",
    "#     line_2020 = alt.Chart().mark_line(\n",
    "#         strokeDash=[5,5],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week <= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "\n",
    "#     line_2020_recent = alt.Chart().mark_line(\n",
    "#         strokeDash=[1,3],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week >= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "\n",
    "#     band = alt.Chart().mark_area(\n",
    "#         opacity = 0.3,\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < 2020\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'min({metric}):Q'),\n",
    "#         y2=alt.Y2(f'max({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'max({metric}):Q', title='Max', format=',d'),\n",
    "#                  alt.Tooltip(f'mean({metric}):Q', title='Mean', format=',d'),\n",
    "#                  alt.Tooltip(f'min({metric}):Q', title='Min', format=',d')]\n",
    "#     )\n",
    "    \n",
    "#     chart = alt.layer(line, band, line_2020, line_2020_recent, data=df)\n",
    "#     chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "#     return chart\n",
    "\n",
    "# # def trend_chart_deaths_age_range(df, current_year, category='Country', sex='', scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "# #     df = df.copy()\n",
    "# #     df = df.reset_index()\n",
    "    \n",
    "# #     week_max = df[df['Year']==current_year]['Week'].max()\n",
    "# #     week_trust_margin = 3\n",
    "# #     week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "# #     y_min = max(df[metric].min(), 0)\n",
    "# #     y_max = df[metric].max()\n",
    "    \n",
    "# #     line = alt.Chart().mark_line( #mark_area\n",
    "# #     ).transform_filter(\n",
    "# #         alt.datum.Year < 2020\n",
    "# #     ).encode(\n",
    "# #         x=alt.X('Week:Q'),\n",
    "# #         y=alt.Y(f'mean({metric}):Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "# #         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "# #                  alt.Tooltip(f'mean({metric}):Q', format=',d')]\n",
    "# #     )\n",
    "    \n",
    "# #     line_2020 = alt.Chart().mark_line(\n",
    "# #         strokeDash=[5,5],\n",
    "# #     ).transform_filter(\n",
    "# #         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "# #         (alt.datum.Week <= week_num_trust),\n",
    "# #     ).encode(\n",
    "# #         x=alt.X('Week:Q'),\n",
    "# #         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "# #         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "# #                  alt.Tooltip('Week:Q'),\n",
    "# #                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "# #     )\n",
    "\n",
    "# #     line_2020_recent = alt.Chart().mark_line(\n",
    "# #         strokeDash=[1,3],\n",
    "# #     ).transform_filter(\n",
    "# #         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "# #         (alt.datum.Week >= week_num_trust),\n",
    "# #     ).encode(\n",
    "# #         x=alt.X('Week:Q'),\n",
    "# #         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "# #         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "# #                  alt.Tooltip('Week:Q'),\n",
    "# #                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "# #     )\n",
    "\n",
    "# #     band = alt.Chart().mark_area(\n",
    "# #         opacity = 0.3,\n",
    "# #     ).transform_filter(\n",
    "# #         alt.datum.Year < 2020\n",
    "# #     ).encode(\n",
    "# #         x=alt.X('Week:Q'),\n",
    "# #         y=alt.Y(f'min({metric}):Q'),\n",
    "# #         y2=alt.Y2(f'max({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "# #         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "# #                  alt.Tooltip('Week:Q'),\n",
    "# #                  alt.Tooltip(f'max({metric}):Q', title='Max', format=',d'),\n",
    "# #                  alt.Tooltip(f'mean({metric}):Q', title='Mean', format=',d'),\n",
    "# #                  alt.Tooltip(f'min({metric}):Q', title='Min', format=',d')]\n",
    "# #     )\n",
    "    \n",
    "# #     chart = alt.layer(line, band, line_2020, line_2020_recent, data=df)\n",
    "# #     chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "# #     return chart\n",
    "\n",
    "# def trend_chart_excess_deaths_age_range(df, category='Country', sex='', scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "#     df = df.copy()\n",
    "#     df = df.reset_index()\n",
    "    \n",
    "#     week_max = df['Week'].max()\n",
    "#     week_trust_margin = 3\n",
    "#     week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "\n",
    "#     y_min = df[metric].min()\n",
    "#     y_max = df[metric].max()\n",
    "    \n",
    "#     line = alt.Chart().mark_line(\n",
    "#         strokeDash=[5,5],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week <= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'{metric}:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "        \n",
    "#     line_recent = alt.Chart().mark_line(\n",
    "#         strokeDash=[1,3],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week >= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'{metric}:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "\n",
    "#     chart = alt.layer(line, line_recent, data=df)\n",
    "#     chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "#     return chart\n",
    "\n",
    "# # category: 'continent', 'country'\n",
    "# def get_bar_chart_coloured_age_range(df, category='Country', sex='', scale_type='linear', metric='DTotal', row_title='', column_title='', x_min=None, x_max=None, row_height=200, chart_width=200):\n",
    "#     x_min = 0 if not x_min else x_min\n",
    "#     x_max = df[metric].max() if not x_max else x_max\n",
    "    \n",
    "#     bars = alt.Chart(df).mark_bar().transform_filter(\n",
    "#         (alt.datum.Sex == sex)\n",
    "#     ).encode(\n",
    "#         x=alt.X(f'{metric}:Q', title=metric, scale=alt.Scale(domain=[x_min, x_max], type=scale_type)),\n",
    "#         y=alt.Y(f'{category}:N', sort=None, title=row_title), # sort='-x' to sort locally in this chart\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     ).properties(\n",
    "#         height=row_height, width=chart_width,\n",
    "#     )\n",
    "\n",
    "#     text = bars.mark_text(\n",
    "#         align='left',\n",
    "#         baseline='middle',\n",
    "#         dx=3  # Nudges text to right so it doesn't appear on top of the bar\n",
    "#     ).encode(\n",
    "#         text=alt.Text(metric+':Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "#     )\n",
    "    \n",
    "#     chart = (bars + text)\n",
    "#     chart = chart.properties(title=column_title)\n",
    "\n",
    "#     return chart\n",
    "\n",
    "# # category: 'continent', 'country'\n",
    "# def get_bar_chart_coloured_overall(df, scale_type='linear', metric='DTotal', row_title='', column_title='', x_min=None, x_max=None, row_height=200, chart_width=200):\n",
    "#     x_min = 0 if not x_min else x_min\n",
    "#     x_max = df[metric].max() if not x_max else x_max\n",
    "    \n",
    "#     bars = alt.Chart(df).mark_bar().encode(\n",
    "#         x=alt.X(f'{metric}:Q', title=metric, scale=alt.Scale(domain=[x_min, x_max], type=scale_type)),\n",
    "# #         y=alt.Y(category+':N', sort=None, title=row_title), # sort='-x' to sort locally in this chart\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     ).properties(\n",
    "#         height=row_height, width=chart_width,\n",
    "#     )\n",
    "\n",
    "#     text = bars.mark_text(\n",
    "#         align='left',\n",
    "#         baseline='middle',\n",
    "#         dx=3  # Nudges text to right so it doesn't appear on top of the bar\n",
    "#     ).encode(\n",
    "#         text=alt.Text(f'{metric}:Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "#     )\n",
    "    \n",
    "#     chart = (bars + text)\n",
    "#     chart = chart.properties(title=column_title)\n",
    "\n",
    "#     return chart\n",
    "\n",
    "# def trend_dashboard_charts_age_range(df, df_excess, df_totals, current_year, clip_positive_only, top_n=None, metric='Value', scale_type='linear', country_filter_list=None, row_height=200, chart_width=200):\n",
    "#     df_overall = df.copy()\n",
    "#     df_overall = df_overall.stack(['AgeRange', 'Sex'])\n",
    "#     df_overall = df_overall.groupby(level=df_overall.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "#     df_overall = df_overall.reset_index()\n",
    "#     df_overall = df_overall[df_overall['Country'].isin(country_filter_list)] if country_filter_list else df_overall\n",
    "    \n",
    "#     df = df.copy()\n",
    "#     df = df.stack(['AgeRange', 'Sex'])\n",
    "#     df = df.reset_index()\n",
    "#     df = df[df['Country'].isin(country_filter_list)] if country_filter_list else df\n",
    "    \n",
    "#     df_excess = df_excess.copy()\n",
    "#     df_excess = df_excess.stack(['AgeRange', 'Sex'])\n",
    "#     df_excess = df_excess.clip(lower=0) if clip_positive_only else df_excess\n",
    "#     df_excess = df_excess.reset_index()\n",
    "#     df_excess = df_excess[df_excess['Country'].isin(country_filter_list)] if country_filter_list else df_excess\n",
    "\n",
    "#     df_totals = df_totals\n",
    "#     df_totals = df_totals.stack(['AgeRange', 'Sex'])\n",
    "#     df_totals = df_totals.clip(lower=0) if clip_positive_only else df_totals\n",
    "#     df_totals_overall = df_totals.groupby(level=df_totals.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "#     df_totals = df_totals.reset_index()\n",
    "#     df_totals_overall = df_totals_overall.reset_index()\n",
    "#     df_totals = df_totals[df_totals['Country'].isin(country_filter_list)] if country_filter_list else df_totals\n",
    "    \n",
    "#     chart = alt.vconcat()\n",
    "#     first_row = True\n",
    "    \n",
    "#     category='Country'\n",
    "    \n",
    "#     excess_deaths_max_per_category = None #df_totals[metric].max()\n",
    "#     excess_deaths_overall_max = df_totals_overall[metric].max()\n",
    "\n",
    "#     country_list = df['Country'].unique()\n",
    "#     for country in country_list:\n",
    "#         df_overall_filtered = df_overall[df_overall['Country']==country]\n",
    "#         df_filtered = df[df['Country']==country]\n",
    "#         df_excess_filtered = df_excess[df_excess['Country']==country]\n",
    "#         df_totals_filtered = df_totals[df_totals['Country']==country]\n",
    "#         df_totals_overall_filtered = df_totals_overall[df_totals_overall['Country']==country]\n",
    "\n",
    "#         trend_deaths_overall = trend_chart_deaths_overall(df_overall_filtered, current_year=current_year, metric=metric,\n",
    "#                                           row_title=country, column_title='Deaths' if first_row else '',\n",
    "#                                           row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         trend_excess_deaths_male = trend_chart_excess_deaths_age_range(df_excess_filtered, category='AgeRange', sex='m', metric=metric,\n",
    "#                                                         row_title='', column_title='Excess Deaths Male' if first_row else '',\n",
    "#                                                         row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         trend_excess_deaths_female = trend_chart_excess_deaths_age_range(df_excess_filtered, category='AgeRange', sex='f', metric=metric,\n",
    "#                                                         row_title='', column_title='Excess Deaths Female' if first_row else '',\n",
    "#                                                         row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         chart_deaths_male    = get_bar_chart_coloured_age_range(df_totals_filtered, category='AgeRange', sex='m', metric=metric, \n",
    "#                                                         row_title='', column_title='Total Excess Deaths Male' if first_row else '',\n",
    "#                                                         x_min=0, x_max=excess_deaths_max_per_category,\n",
    "#                                                         row_height=row_height, chart_width=chart_width)\n",
    "        \n",
    "#         chart_deaths_female    = get_bar_chart_coloured_age_range(df_totals_filtered, category='AgeRange', sex='f', metric=metric, \n",
    "#                                                         row_title='', column_title='Total Excess Deaths Female' if first_row else '',\n",
    "#                                                         x_min=0, x_max=excess_deaths_max_per_category,\n",
    "#                                                         row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         chart_deaths_overall    = get_bar_chart_coloured_overall(df_totals_overall_filtered, metric=metric, \n",
    "#                                                         row_title='', column_title='Total Excess Deaths' if first_row else '',\n",
    "#                                                         x_min=0, x_max=excess_deaths_overall_max,\n",
    "#                                                         row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         first_row = False\n",
    "\n",
    "#         row = trend_deaths_overall | trend_excess_deaths_male | trend_excess_deaths_female | chart_deaths_male | chart_deaths_female | chart_deaths_overall\n",
    "#         chart = (chart & row).resolve_scale(color='independent')\n",
    "    \n",
    "#     return chart\n",
    "\n",
    "\n",
    "# def configure_trend_chart_continents(chart):\n",
    "#     chart = chart.configure_axisY(\n",
    "#         titleColor='blue',\n",
    "#         titleFontSize=20,\n",
    "#     )\n",
    "#     chart = chart.configure_axisX(\n",
    "# #         title=None\n",
    "#     )\n",
    "\n",
    "#     chart = chart.configure_title(\n",
    "#         fontSize=20,\n",
    "#         anchor='middle',\n",
    "#         color='blue'\n",
    "#     )\n",
    "#     return chart\n",
    "\n",
    "# country_filter_list = ['Spain', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "\n",
    "# slide_title = 'Excess Deaths '+str(current_year) + ' - Selected Countries'\n",
    "# display_h2(slide_title)\n",
    "# print('Dashed line is for current year; dotted is for last 3 weeks since data might not yet be final')\n",
    "# print('Shaded area is min/max range for previous years, with mean as solid line')\n",
    "# print('Where current year is significantly lower than previous years, it is likely to be due to data not yet available')\n",
    "\n",
    "# footer = 'Dashed line is current year; dotted is last 3wks (might not be complete); shaded is min/max for previous years, with mean as solid line'\n",
    "# chart = trend_dashboard_charts_age_range(df_stmf_detailed, df_excess_deaths_detailed, df_excess_deaths_totals_detailed, \n",
    "#                                          current_year=current_year, clip_positive_only=clip_positive_only,\n",
    "#                                          country_filter_list=country_filter_list,\n",
    "#                                          row_height=chart_height, chart_width=chart_width)\n",
    "# chart = configure_trend_chart_continents(chart)\n",
    "\n",
    "# chart.display()\n",
    "# print(footer)\n",
    "\n",
    "# if export_slides:\n",
    "#     chart.save(saved_image_name)\n",
    "\n",
    "#     # Add a new slide ready for chart and set title text\n",
    "#     slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "#     prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "#     prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "#     prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# independent_y_scales = True\n",
    "# chart_height = 350\n",
    "# chart_width = 150\n",
    "# current_year = 2020\n",
    "# clip_positive_only = True\n",
    "\n",
    "# def trend_chart_deaths_overall_cumulative(df, current_year, scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "#     df = df.copy()\n",
    "    \n",
    "#     df['cumulative'] = df.groupby(['Year'])[metric].apply(lambda x: x.cumsum())\n",
    "#     metric='cumulative'\n",
    "    \n",
    "#     week_max = df[df['Year']==current_year]['Week'].max()\n",
    "#     week_trust_margin = 3\n",
    "#     week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "#     y_min = 0 # max(df[metric].min(), 0)\n",
    "#     y_max = df[metric].max()\n",
    "    \n",
    "#     line = alt.Chart().mark_line(\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < 2020\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip(f'mean({metric}):Q', format=',d')]\n",
    "#     )\n",
    "    \n",
    "#     line_2020 = alt.Chart().mark_line(\n",
    "#         strokeDash=[5,5],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week <= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "\n",
    "#     line_2020_recent = alt.Chart().mark_line(\n",
    "#         strokeDash=[1,3],\n",
    "#     ).transform_filter(\n",
    "#         (alt.datum.Year == 2020) &\n",
    "# #         (alt.datum.Sex == sex) & \n",
    "#         (alt.datum.Week >= week_num_trust),\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'mean({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "#     )\n",
    "\n",
    "#     band = alt.Chart().mark_area(\n",
    "#         opacity = 0.3,\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < 2020\n",
    "#     ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "#         y=alt.Y(f'min({metric}):Q'),\n",
    "#         y2=alt.Y2(f'max({metric}):Q'),\n",
    "# #         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "#         tooltip=[alt.Tooltip('Week:Q'),\n",
    "#                  alt.Tooltip(f'max({metric}):Q', title='Max', format=',d'),\n",
    "#                  alt.Tooltip(f'mean({metric}):Q', title='Mean', format=',d'),\n",
    "#                  alt.Tooltip(f'min({metric}):Q', title='Min', format=',d')]\n",
    "#     )\n",
    "    \n",
    "#     chart = alt.layer(line, band, line_2020, line_2020_recent, data=df)\n",
    "#     chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "#     return chart\n",
    "\n",
    "# def trend_dashboard_charts_cumulative(df, df_excess, df_totals, current_year, clip_positive_only, top_n=None, metric='Value', scale_type='linear', country_filter_list=None, row_height=200, chart_width=200):\n",
    "#     df_overall = df.copy()\n",
    "#     df_overall = df_overall.stack(['AgeRange', 'Sex'])\n",
    "#     df_overall = df_overall.groupby(level=df_overall.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "#     df_overall = df_overall.reset_index()\n",
    "#     df_overall = df_overall[df_overall['Country'].isin(country_filter_list)] if country_filter_list else df_overall\n",
    "    \n",
    "#     df = df.copy()\n",
    "#     df = df.stack(['AgeRange', 'Sex'])\n",
    "#     df = df.reset_index()\n",
    "#     df = df[df['Country'].isin(country_filter_list)] if country_filter_list else df\n",
    "\n",
    "#     chart = alt.hconcat()\n",
    "#     first_row = True\n",
    "    \n",
    "#     category='Country'\n",
    "    \n",
    "#     excess_deaths_max_per_category = None #df_totals[metric].max()\n",
    "\n",
    "#     country_list = df['Country'].unique()\n",
    "#     for country in country_list:\n",
    "#         df_overall_filtered = df_overall[df_overall['Country']==country]\n",
    "\n",
    "#         trend_deaths_overall = trend_chart_deaths_overall_cumulative(df_overall_filtered, current_year=current_year, metric=metric,\n",
    "#                                           row_title=country, column_title='Deaths' if first_row else '',\n",
    "#                                           row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "#         first_row = False\n",
    "\n",
    "#         chart = chart | trend_deaths_overall\n",
    "    \n",
    "#     return chart\n",
    "\n",
    "\n",
    "# country_filter_list = ['Spain', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "\n",
    "# slide_title = 'Cumulative Deaths '+str(current_year) + ' - Selected Countries'\n",
    "# display_h2(slide_title)\n",
    "# print('Dashed line is for current year; dotted is for last 3 weeks since data might not yet be final')\n",
    "# print('Shaded area is min/max range for previous years, with mean as solid line')\n",
    "# print('Where current year is significantly lower than previous years, it is likely to be due to data not yet available')\n",
    "\n",
    "# footer = 'Dashed line is current year; dotted is last 3wks (might not be complete); shaded is min/max for previous years, with mean as solid line'\n",
    "# chart = trend_dashboard_charts_cumulative(df_stmf_detailed, df_excess_deaths_detailed, df_excess_deaths_totals_detailed, \n",
    "#                                          current_year=current_year, clip_positive_only=clip_positive_only,\n",
    "#                                          country_filter_list=country_filter_list,\n",
    "#                                          row_height=chart_height, chart_width=chart_width)\n",
    "# chart = configure_trend_chart_continents(chart)\n",
    "\n",
    "# chart.display()\n",
    "# print(footer)\n",
    "\n",
    "# if export_slides:\n",
    "#     chart.save(saved_image_name)\n",
    "\n",
    "#     # Add a new slide ready for chart and set title text\n",
    "#     slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "#     prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "#     prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "#     prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "since_year = 2020\n",
    "\n",
    "def get_excess_deaths_detailed_multi_year(df, since_year, clip_positive_only):\n",
    "    df = df.copy()\n",
    "    \n",
    "    df_previous_years = df.copy()\n",
    "    df_previous_years = df_previous_years[df_previous_years.index.get_level_values('Year')<since_year]\n",
    "    df_previous_years = df_previous_years.groupby(level=df_previous_years.index.names.difference(['Year']), axis=0).mean()\n",
    "\n",
    "    df_excess_deaths = df.copy()\n",
    "    df_excess_deaths = df_excess_deaths[df_excess_deaths.index.get_level_values('Year')>=since_year]\n",
    "    \n",
    "    years = sorted(df_excess_deaths.index.get_level_values('Year').unique())\n",
    "    df_excess_deaths_multi_year = pd.DataFrame()\n",
    "    for year in years:\n",
    "        df2 = df_excess_deaths[df_excess_deaths.index.get_level_values('Year')==year]\n",
    "        df2 = df2.reset_index(level='Year', drop=True)\n",
    "        df2 = df2 - df_previous_years\n",
    "        df2 = df2.clip(lower=0) if clip_positive_only else df2\n",
    "        df2['Year'] = year\n",
    "        df2 = df2.set_index(['Year'], append=True)\n",
    "        df_excess_deaths_multi_year = pd.concat([df_excess_deaths_multi_year, df2])\n",
    "\n",
    "    return df_excess_deaths_multi_year\n",
    "\n",
    "def get_totals_from_time_series_detailed_multi_year(df):\n",
    "    df_totals = df.copy().groupby(['Country']).sum()\n",
    "\n",
    "    return df_totals\n",
    "\n",
    "display_h1('Mortality Analysis - since '+str(since_year))\n",
    "\n",
    "# df_stmf_detailed = load_short_term_mortality_data_detailed()\n",
    "df_stmf_detailed_multi_year = df_stmf_detailed.copy()\n",
    "\n",
    "since_year = 2020\n",
    "\n",
    "df_excess_deaths_detailed_multi_year = get_excess_deaths_detailed_multi_year(df_stmf_detailed, since_year=since_year, clip_positive_only=clip_positive_only)\n",
    "df_excess_deaths_totals_detailed_multi_year = get_totals_from_time_series_detailed_multi_year(df_excess_deaths_detailed_multi_year)\n",
    "\n",
    "# country_filter_list = ['Spain', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "# df = df_excess_deaths_detailed_multi_year\n",
    "# df = df.reset_index()\n",
    "# df = df[df['Country'].isin(country_filter_list)] if country_filter_list else df\n",
    "# display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "independent_y_scales = True\n",
    "chart_height = 100\n",
    "chart_width = 200\n",
    "since_year = 2020\n",
    "clip_positive_only = True\n",
    "\n",
    "def trend_chart_deaths_overall_multi_year(df, since_year, scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "    df = df.copy()\n",
    "    \n",
    "    latest_year = df['Year'].max()\n",
    "\n",
    "    week_max = df[df['Year']==latest_year]['Week'].max()\n",
    "    week_trust_margin = 3\n",
    "    week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "    \n",
    "    df_previous_years = df[df['Year'] < since_year].copy()\n",
    "    df_previous_years = df_previous_years.groupby(['Week']).agg({metric: ['min', 'mean', 'max']})\n",
    "    df_previous_years.columns = df_previous_years.columns.droplevel(0)\n",
    "#     display(df_previous_years.head())\n",
    "    \n",
    "    df = df[df['Year'] >= since_year].copy()\n",
    "    df = pd.merge(df, df_previous_years, on='Week')\n",
    "#     display(df.head())\n",
    "    \n",
    "    df['ISO'] = df['Year'].astype(str) + '-W' + df['Week'].astype(str) + '-1'\n",
    "    df['date'] = df['ISO'].map(lambda x: datetime.datetime.strptime(x, \"%G-W%V-%u\"))\n",
    "    df = df.drop(['ISO'], axis='columns')\n",
    "#     display(df.head())\n",
    "    \n",
    "    y_min = 0 # max(df[metric].min(), 0)\n",
    "    y_max = df[metric].max()\n",
    "    \n",
    "    line = alt.Chart().mark_line( #mark_area\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < since_year\n",
    "    ).encode(\n",
    "        x=alt.X('date:T'),\n",
    "#         y=alt.Y(f'mean({metric}):Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "        y=alt.Y('mean:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('mean:Q', format=',d')]\n",
    "    )\n",
    "    \n",
    "    line_since_year = alt.Chart().mark_line(\n",
    "        strokeDash=[5,5],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Year < latest_year) |\n",
    "#         (alt.datum.Sex == sex) & \n",
    "        (alt.datum.Week <= week_num_trust),\n",
    "    ).encode(\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'mean({metric}):Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('Year:Q'),\n",
    "                 alt.Tooltip('Week:Q'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    line_recent = alt.Chart().mark_line(\n",
    "        strokeDash=[1,3],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Year == latest_year) &\n",
    "#         (alt.datum.Sex == sex) & \n",
    "        (alt.datum.Week >= week_num_trust),\n",
    "    ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'mean({metric}):Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('Year:Q'),\n",
    "                 alt.Tooltip('Week:Q'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    band = alt.Chart().mark_area(\n",
    "        opacity = 0.3,\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < since_year\n",
    "    ).encode(\n",
    "        x=alt.X('date:T'),\n",
    "#         y=alt.Y(f'min({metric}):Q'),\n",
    "#         y2=alt.Y2(f'max({metric}):Q'),\n",
    "        y=alt.Y('min:Q'),\n",
    "        y2=alt.Y2('max:Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('Year:Q'),\n",
    "                 alt.Tooltip('Week:Q'),\n",
    "                 alt.Tooltip('max:Q', title='Max', format=',d'),\n",
    "                 alt.Tooltip('mean:Q', title='Mean', format=',d'),\n",
    "                 alt.Tooltip('min:Q', title='Min', format=',d')]\n",
    "    )\n",
    "    \n",
    "    chart = alt.layer(line, band, line_since_year, line_recent, data=df)\n",
    "    chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "    return chart\n",
    "\n",
    "def trend_chart_excess_deaths_age_range_multi_year(df, category='Country', sex='', scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "    df = df.copy()\n",
    "    df = df.reset_index()\n",
    "    \n",
    "    latest_year = df['Year'].max()\n",
    "\n",
    "    week_max = df[df['Year']==latest_year]['Week'].max()\n",
    "    week_trust_margin = 3\n",
    "    week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "\n",
    "    df['ISO'] = df['Year'].astype(str) + '-W' + df['Week'].astype(str) + '-1'\n",
    "    df['date'] = df['ISO'].map(lambda x: datetime.datetime.strptime(x, \"%G-W%V-%u\"))\n",
    "    df = df.drop(['ISO'], axis='columns')\n",
    "#     display(df.head())\n",
    "\n",
    "    y_min = df[metric].min()\n",
    "    y_max = df[metric].max()\n",
    "    \n",
    "    line = alt.Chart().mark_line(\n",
    "        strokeDash=[5,5],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Sex == sex) #& \n",
    "#         (alt.datum.Week <= week_num_trust),\n",
    "    ).encode(\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'{metric}:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "        color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "        \n",
    "    line_recent = alt.Chart().mark_line(\n",
    "        strokeDash=[1,3],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Sex == sex) & \n",
    "        (alt.datum.Year == latest_year) &\n",
    "        (alt.datum.Week >= week_num_trust),\n",
    "    ).encode(\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'{metric}:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "        color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    chart = alt.layer(line, line_recent, data=df)\n",
    "    chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "    return chart\n",
    "\n",
    "# category: 'continent', 'country'\n",
    "def get_bar_chart_coloured_age_range(df, category='Country', sex='', scale_type='linear', metric='DTotal', row_title='', column_title='', x_min=None, x_max=None, row_height=200, chart_width=200):\n",
    "    x_min = 0 if not x_min else x_min\n",
    "    x_max = df[metric].max() if not x_max else x_max\n",
    "    \n",
    "    bars = alt.Chart(df).mark_bar().transform_filter(\n",
    "        (alt.datum.Sex == sex)\n",
    "    ).encode(\n",
    "        x=alt.X(f'{metric}:Q', title=metric, scale=alt.Scale(domain=[x_min, x_max], type=scale_type)),\n",
    "        y=alt.Y(f'{category}:N', sort=None, title=row_title), # sort='-x' to sort locally in this chart\n",
    "        color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip(f'{category}:N'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    ).properties(\n",
    "        height=row_height, width=chart_width,\n",
    "    )\n",
    "\n",
    "    text = bars.mark_text(\n",
    "        align='left',\n",
    "        baseline='middle',\n",
    "        dx=3  # Nudges text to right so it doesn't appear on top of the bar\n",
    "    ).encode(\n",
    "        text=alt.Text(metric+':Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "    )\n",
    "    \n",
    "    chart = (bars + text)\n",
    "    chart = chart.properties(title=column_title)\n",
    "\n",
    "    return chart\n",
    "\n",
    "# category: 'continent', 'country'\n",
    "def get_bar_chart_coloured_overall(df, scale_type='linear', metric='DTotal', row_title='', column_title='', x_min=None, x_max=None, row_height=200, chart_width=200):\n",
    "    x_min = 0 if not x_min else x_min\n",
    "    x_max = df[metric].max() if not x_max else x_max\n",
    "    \n",
    "    bars = alt.Chart(df).mark_bar().encode(\n",
    "        x=alt.X(f'{metric}:Q', title=metric, scale=alt.Scale(domain=[x_min, x_max], type=scale_type)),\n",
    "#         y=alt.Y(category+':N', sort=None, title=row_title), # sort='-x' to sort locally in this chart\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    ).properties(\n",
    "        height=row_height, width=chart_width,\n",
    "    )\n",
    "\n",
    "    text = bars.mark_text(\n",
    "        align='left',\n",
    "        baseline='middle',\n",
    "        dx=3  # Nudges text to right so it doesn't appear on top of the bar\n",
    "    ).encode(\n",
    "        text=alt.Text(f'{metric}:Q', format=',d') #,.2r gives rounded to 2 significant figures\n",
    "    )\n",
    "    \n",
    "    chart = (bars + text)\n",
    "    chart = chart.properties(title=column_title)\n",
    "\n",
    "    return chart\n",
    "\n",
    "def configure_trend_chart_continents(chart):\n",
    "    chart = chart.configure_axisY(\n",
    "        titleColor='blue',\n",
    "        titleFontSize=20,\n",
    "    )\n",
    "    chart = chart.configure_axisX(\n",
    "#         title=None\n",
    "    )\n",
    "\n",
    "    chart = chart.configure_title(\n",
    "        fontSize=20,\n",
    "        anchor='middle',\n",
    "        color='blue'\n",
    "    )\n",
    "    return chart\n",
    "\n",
    "def trend_dashboard_charts_age_range_multi_year(df, df_excess, df_totals, since_year, clip_positive_only, top_n=None, metric='Value', scale_type='linear', country_filter_list=None, row_height=200, chart_width=200):\n",
    "#     display(df.head())\n",
    "    df_overall = df.copy()\n",
    "    df_overall = df_overall.stack(['AgeRange', 'Sex'])\n",
    "    df_overall = df_overall.groupby(level=df_overall.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "    df_overall = df_overall.reset_index()\n",
    "    df_overall = df_overall[df_overall['Country'].isin(country_filter_list)] if country_filter_list else df_overall\n",
    "    \n",
    "    df = df.copy()\n",
    "    df = df.stack(['AgeRange', 'Sex'])\n",
    "    df = df.reset_index()\n",
    "    df = df[df['Country'].isin(country_filter_list)] if country_filter_list else df\n",
    "    \n",
    "    df_excess = df_excess.copy()\n",
    "    df_excess = df_excess.stack(['AgeRange', 'Sex'])\n",
    "    df_excess = df_excess.clip(lower=0) if clip_positive_only else df_excess\n",
    "    df_excess = df_excess.reset_index()\n",
    "    df_excess = df_excess[df_excess['Country'].isin(country_filter_list)] if country_filter_list else df_excess\n",
    "\n",
    "    df_totals = df_totals\n",
    "    df_totals = df_totals.stack(['AgeRange', 'Sex'])\n",
    "    df_totals = df_totals.clip(lower=0) if clip_positive_only else df_totals\n",
    "    df_totals_overall = df_totals.groupby(level=df_totals.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "    df_totals = df_totals.reset_index()\n",
    "    df_totals_overall = df_totals_overall.reset_index()\n",
    "    df_totals = df_totals[df_totals['Country'].isin(country_filter_list)] if country_filter_list else df_totals\n",
    "    \n",
    "    chart = alt.vconcat()\n",
    "    first_row = True\n",
    "    \n",
    "    category='Country'\n",
    "    \n",
    "    excess_deaths_max_per_category = None #df_totals[metric].max()\n",
    "    excess_deaths_overall_max = df_totals_overall[metric].max()\n",
    "\n",
    "    country_list = df['Country'].unique()\n",
    "    for country in country_list:\n",
    "        df_overall_filtered = df_overall[df_overall['Country']==country]\n",
    "        df_filtered = df[df['Country']==country]\n",
    "        df_excess_filtered = df_excess[df_excess['Country']==country]\n",
    "        df_totals_filtered = df_totals[df_totals['Country']==country]\n",
    "        df_totals_overall_filtered = df_totals_overall[df_totals_overall['Country']==country]\n",
    "        \n",
    "#         display(df_overall_filtered.head())\n",
    "\n",
    "        trend_deaths_overall = trend_chart_deaths_overall_multi_year(df_overall_filtered, since_year=since_year, metric=metric,\n",
    "                                          row_title=country, column_title='Deaths' if first_row else '',\n",
    "                                          row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        trend_excess_deaths_male = trend_chart_excess_deaths_age_range_multi_year(df_excess_filtered, category='AgeRange', sex='m', metric=metric,\n",
    "                                                        row_title='', column_title='Excess Deaths Male' if first_row else '',\n",
    "                                                        row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        trend_excess_deaths_female = trend_chart_excess_deaths_age_range_multi_year(df_excess_filtered, category='AgeRange', sex='f', metric=metric,\n",
    "                                                        row_title='', column_title='Excess Deaths Female' if first_row else '',\n",
    "                                                        row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        chart_deaths_male    = get_bar_chart_coloured_age_range(df_totals_filtered, category='AgeRange', sex='m', metric=metric, \n",
    "                                                        row_title='', column_title='Total Excess Deaths Male' if first_row else '',\n",
    "                                                        x_min=0, x_max=excess_deaths_max_per_category,\n",
    "                                                        row_height=row_height, chart_width=chart_width)\n",
    "        \n",
    "        chart_deaths_female    = get_bar_chart_coloured_age_range(df_totals_filtered, category='AgeRange', sex='f', metric=metric, \n",
    "                                                        row_title='', column_title='Total Excess Deaths Female' if first_row else '',\n",
    "                                                        x_min=0, x_max=excess_deaths_max_per_category,\n",
    "                                                        row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        chart_deaths_overall    = get_bar_chart_coloured_overall(df_totals_overall_filtered, metric=metric, \n",
    "                                                        row_title='', column_title='Total Excess Deaths' if first_row else '',\n",
    "                                                        x_min=0, x_max=excess_deaths_overall_max,\n",
    "                                                        row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        first_row = False\n",
    "\n",
    "        row = trend_deaths_overall | trend_excess_deaths_male | trend_excess_deaths_female | chart_deaths_male | chart_deaths_female | chart_deaths_overall\n",
    "        chart = (chart & row).resolve_scale(color='independent')\n",
    "    \n",
    "    return chart\n",
    "\n",
    "# country_filter_list = ['Spain', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "country_filter_list = ['Spain', 'Poland', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "\n",
    "slide_title = 'Excess Deaths - Since '+str(since_year) + ' - Selected Countries'\n",
    "display_h2(slide_title)\n",
    "print(f'Dashed line is since {since_year}; dotted is for last 3 weeks since data might not yet be final')\n",
    "print('Shaded area is min/max range for previous years, with mean as solid line')\n",
    "print('Where current year is significantly lower than previous years, it is likely to be due to data not yet available')\n",
    "\n",
    "footer = f'Dashed line is since {since_year}; dotted is last 3wks (might not be complete); shaded is min/max for previous years, with mean as solid line'\n",
    "\n",
    "chart = trend_dashboard_charts_age_range_multi_year(df_stmf_detailed_multi_year, df_excess_deaths_detailed_multi_year, df_excess_deaths_totals_detailed_multi_year, \n",
    "                                         since_year=since_year, clip_positive_only=clip_positive_only,\n",
    "                                         country_filter_list=country_filter_list,\n",
    "                                         row_height=chart_height, chart_width=chart_width)\n",
    "chart = configure_trend_chart_continents(chart)\n",
    "\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "independent_y_scales = True\n",
    "chart_height = 350\n",
    "chart_width = 150\n",
    "since_year = 2020\n",
    "clip_positive_only = True\n",
    "\n",
    "def trend_chart_deaths_overall_cumulative_multi_year(df, since_year, scale_type='linear', metric='DTotal', row_title='', column_title='', row_height=200, chart_width=200):\n",
    "    df = df.copy()\n",
    "    \n",
    "#     df['cumulative'] = df.groupby(['Year'])[metric].apply(lambda x: x.cumsum())\n",
    "#     metric='cumulative'\n",
    "    \n",
    "    latest_year = df['Year'].max()\n",
    "\n",
    "    week_max = df[df['Year']==latest_year]['Week'].max()\n",
    "    week_trust_margin = 3\n",
    "    week_num_trust = max(week_max-week_trust_margin, 0)\n",
    "    \n",
    "\n",
    "    \n",
    "    \n",
    "    df_previous_years = df[df['Year'] < since_year].copy()\n",
    "    previous_years_list = df_previous_years['Year'].unique()\n",
    "\n",
    "#     df_previous_years['cumulative'] = df.groupby(['Year'])[metric].apply(lambda x: x.cumsum())\n",
    "    df_previous_years = df_previous_years.pivot(index='Week', columns='Year')[metric]\n",
    "#     df_previous_years.columns = pd.MultiIndex.from_product([['years'], df_previous_years.columns])\n",
    "#     display(df_previous_years.head())\n",
    "    \n",
    "#     df_previous_years = df_previous_years.groupby(['Week']).agg({metric: ['min', 'mean', 'max']})\n",
    "#     df_previous_years = df_previous_years.groupby(['Week']).agg({'cumulative': ['min', 'mean', 'max']})\n",
    "#     df_previous_years.columns = df_previous_years.columns.droplevel(0) # remove 'value' from multi-level column names from groupby function\n",
    "#     display(df_previous_years.head())\n",
    "    \n",
    "    df = df[df['Year'] >= since_year].copy()\n",
    "#     df.columns = pd.MultiIndex.from_product([['Original'], df.columns])\n",
    "    df = pd.merge(df, df_previous_years, on='Week')\n",
    "    df = df.sort_values(by=['Year', 'Week'], ascending=True)\n",
    "    df[metric] = df.groupby(['Country'])[metric].apply(lambda x: x.cumsum())\n",
    "    df[previous_years_list] = df.groupby(['Country'])[previous_years_list].apply(lambda x: x.cumsum())\n",
    "    df['min'] = df[previous_years_list].min(axis='columns')\n",
    "    df['mean'] = df[previous_years_list].mean(axis='columns')\n",
    "    df['max'] = df[previous_years_list].max(axis='columns')\n",
    "    df = df.drop(previous_years_list, axis='columns')\n",
    "#     display(df.head())\n",
    "    \n",
    "    df['ISO'] = df['Year'].astype(str) + '-W' + df['Week'].astype(str) + '-1'\n",
    "    df['date'] = df['ISO'].map(lambda x: datetime.datetime.strptime(x, \"%G-W%V-%u\"))\n",
    "    df = df.drop(['ISO'], axis='columns')\n",
    "#     display(df.head())\n",
    "\n",
    "\n",
    "#     df = df.sort_values(by=['date'], ascending=True)\n",
    "    \n",
    "#     df['min'] = df.groupby(['Country'])['min'].apply(lambda x: x.cumsum())\n",
    "#     df['mean'] = df.groupby(['Country'])['mean'].apply(lambda x: x.cumsum())\n",
    "#     df['max'] = df.groupby(['Country'])['max'].apply(lambda x: x.cumsum())\n",
    "#     metric='cumulative'\n",
    "    \n",
    "#     display(df)\n",
    "\n",
    "\n",
    "\n",
    "    \n",
    "    y_min = 0 # max(df[metric].min(), 0)\n",
    "    y_max = df[metric].max()\n",
    "    \n",
    "    line = alt.Chart().mark_line(\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < since_year\n",
    "    ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y('mean:Q', title=row_title, scale=alt.Scale(domain=[y_min, y_max], type=scale_type)),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('mean:Q', format=',d')]\n",
    "    )\n",
    "    \n",
    "    line_since_year = alt.Chart().mark_line(\n",
    "        strokeDash=[5,5],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Year < latest_year) |\n",
    "#         (alt.datum.Sex == sex) & \n",
    "        (alt.datum.Week <= week_num_trust),\n",
    "    ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'mean({metric}):Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('date:T'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    line_recent = alt.Chart().mark_line(\n",
    "        strokeDash=[1,3],\n",
    "    ).transform_filter(\n",
    "        (alt.datum.Year == latest_year) &\n",
    "#         (alt.datum.Sex == sex) & \n",
    "        (alt.datum.Week >= week_num_trust),\n",
    "    ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y(f'mean({metric}):Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('date:T'),\n",
    "                 alt.Tooltip(f'{metric}:Q', format=',d')]\n",
    "    )\n",
    "\n",
    "    band = alt.Chart().mark_area(\n",
    "        opacity = 0.3,\n",
    "#     ).transform_filter(\n",
    "#         alt.datum.Year < 2020\n",
    "    ).encode(\n",
    "#         x=alt.X('Week:Q'),\n",
    "        x=alt.X('date:T'),\n",
    "        y=alt.Y('min:Q'),\n",
    "        y2=alt.Y2('max:Q'),\n",
    "#         color=alt.Color(f'{category}:N', sort=None, legend=None),\n",
    "        tooltip=[alt.Tooltip('Week:Q'),\n",
    "                 alt.Tooltip('max:Q', title='Max', format=',d'),\n",
    "                 alt.Tooltip(f'mean({metric}):Q', title='Mean', format=',d'),\n",
    "                 alt.Tooltip('min:Q', title='Min', format=',d')]\n",
    "    )\n",
    "    \n",
    "    chart = alt.layer(line, band, line_since_year, line_recent, data=df)\n",
    "#     chart = alt.layer(line, band, line_recent, data=df)\n",
    "    chart = chart.properties(title=column_title, height=row_height, width=chart_width)\n",
    "\n",
    "    return chart\n",
    "\n",
    "def trend_dashboard_charts_cumulative_multi_year(df, df_excess, df_totals, since_year, clip_positive_only, top_n=None, metric='Value', scale_type='linear', country_filter_list=None, row_height=200, chart_width=200):\n",
    "    df_overall = df.copy()\n",
    "    df_overall = df_overall.stack(['AgeRange', 'Sex'])\n",
    "    df_overall = df_overall.groupby(level=df_overall.index.names.difference(['AgeRange', 'Sex']), axis=0).sum()\n",
    "    df_overall = df_overall.reset_index()\n",
    "    df_overall = df_overall[df_overall['Country'].isin(country_filter_list)] if country_filter_list else df_overall\n",
    "    \n",
    "    df = df.copy()\n",
    "    df = df.stack(['AgeRange', 'Sex'])\n",
    "    df = df.reset_index()\n",
    "    df = df[df['Country'].isin(country_filter_list)] if country_filter_list else df\n",
    "\n",
    "    chart = alt.hconcat()\n",
    "    first_row = True\n",
    "    \n",
    "    category='Country'\n",
    "    \n",
    "    excess_deaths_max_per_category = None #df_totals[metric].max()\n",
    "\n",
    "    country_list = df['Country'].unique()\n",
    "    for country in country_list:\n",
    "        df_overall_filtered = df_overall[df_overall['Country']==country]\n",
    "\n",
    "        trend_deaths_overall = trend_chart_deaths_overall_cumulative_multi_year(df_overall_filtered, since_year=since_year, metric=metric,\n",
    "                                          row_title=country, column_title='Deaths' if first_row else '',\n",
    "                                          row_height=row_height, chart_width=chart_width)\n",
    "\n",
    "        first_row = False\n",
    "\n",
    "        chart = chart | trend_deaths_overall\n",
    "    \n",
    "    return chart\n",
    "\n",
    "\n",
    "country_filter_list = ['Spain', 'Sweden', 'United Kingdom', 'United States', 'Israel'] # 'Russian Federation'\n",
    "\n",
    "slide_title = 'Cumulative Deaths - Since '+str(since_year) + ' - Selected Countries'\n",
    "display_h2(slide_title)\n",
    "print(f'Dashed line is since {since_year}; dotted is for last 3 weeks since data might not yet be final')\n",
    "print('Shaded area is min/max range for previous years, with mean as solid line')\n",
    "print('Where current year is significantly lower than previous years, it is likely to be due to data not yet available')\n",
    "\n",
    "footer = f'Dashed line is since {since_year}; dotted is last 3wks (might not be complete); shaded is min/max for previous years, with mean as solid line'\n",
    "chart = trend_dashboard_charts_cumulative_multi_year(df_stmf_detailed_multi_year, df_excess_deaths_detailed_multi_year, df_excess_deaths_totals_detailed_multi_year, \n",
    "                                         since_year=since_year, clip_positive_only=clip_positive_only,\n",
    "                                         country_filter_list=country_filter_list,\n",
    "                                         row_height=chart_height, chart_width=chart_width)\n",
    "chart = configure_trend_chart_continents(chart)\n",
    "\n",
    "chart.display()\n",
    "print(footer)\n",
    "\n",
    "if export_slides:\n",
    "    chart.save(saved_image_name)\n",
    "\n",
    "    # Add a new slide ready for chart and set title text\n",
    "    slide = prs.add_slide(layout = prs.slide_template_layout.TITLE_AND_MAX_CONTENT)\n",
    "    prs.set_text(slide, placeholder_name='title', text=slide_title)\n",
    "    prs.add_image(slide, placeholder_name='content', image_file=saved_image_name)\n",
    "    prs.set_text(slide, placeholder_name='footer', text=footer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if export_slides:\n",
    "    # save slides\n",
    "    print('Saving slides')\n",
    "    prs.save(slides_file_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}