COVID_Visualization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "non-C4vxWD8O"
   },
   "source": [
    "# Comparative visualization of COVID-19 over time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "X5GprS_IZirh"
   },
   "source": [
    "## Prepare required Python packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DKUZ5QCfWD8j"
   },
   "outputs": [],
   "source": [
    "%pip install numpy pandas plotly --upgrade"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "i00FtsiWWD8T"
   },
   "source": [
    "## Load Python packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "WXzzRuAdWD8T"
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.express as px"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "YRskZBV7WD8V"
   },
   "source": [
    "## Postprocessing functions\n",
    "\n",
    "Define functions to modify/analyze the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "ygJ50h1BOMyJ"
   },
   "outputs": [],
   "source": [
    "def isolate(df,labels):\n",
    "    \"\"\"Isolate columns indicated by dictionary \"labels\" from dataframe \"df\" as a new dataframe.\"\"\"\n",
    "    \n",
    "    return pd.DataFrame(dict((a,df[b]) for (a,b) in labels.items() ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def resample(df,interval='1D'):\n",
    "    \"\"\"Resample dataframe to interval.\"\"\"\n",
    "\n",
    "    df['date'] = pd.to_datetime(df['date'],utc=True)\n",
    "    df.set_index('date', inplace=True)\n",
    "    df = df.astype({'cases':float,'deaths':float})\n",
    "    return df.groupby('location').resample(interval).mean().interpolate().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "eQESSB8bWD8W"
   },
   "outputs": [],
   "source": [
    "def add_rate(df):\n",
    "    \"\"\"Split dataset by location and calculate the daily difference in cases and deaths.\"\"\"\n",
    "\n",
    "    label = 'perDay'\n",
    "    mydf = df.copy()\n",
    "    \n",
    "    for what in ['cases',\n",
    "                 'deaths',\n",
    "                 'cases_avg',\n",
    "                 'deaths_avg',\n",
    "                ]:\n",
    "\n",
    "        if what in df.columns:\n",
    "            mydf[f'{what}_{label}'] = 0\n",
    "\n",
    "            for where in df['location'].unique():\n",
    "                here = df[ df['location'] == where ][what]\n",
    "                mydf.loc[here.index,f'{what}_{label}'] = here-np.hstack((np.zeros(1),here[:-1]))\n",
    "\n",
    "    return mydf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "dpk1QSM3OL0w"
   },
   "outputs": [],
   "source": [
    "def add_active(df,period=20):\n",
    "    \"\"\"Accummulate (active) cases during rolling period.\"\"\"\n",
    "\n",
    "    label = 'active'\n",
    "    mydf = df.copy()\n",
    "    \n",
    "    for what in ['cases',\n",
    "                 'cases_avg',\n",
    "                ]:\n",
    "\n",
    "        if what in df.columns:\n",
    "            mydf[f'{what}_{label}'] = 0\n",
    "            \n",
    "            for where in df['location'].unique():\n",
    "                here = df[ df['location'] == where ][what]\n",
    "                p = min(len(here.values),period)\n",
    "                mydf.loc[here.index,f'{what}_{label}'] = here-np.hstack((np.zeros(p),here[:-p]))\n",
    "    \n",
    "    return mydf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "xfbPBij4WD8Y"
   },
   "outputs": [],
   "source": [
    "def add_normalization(df):\n",
    "    \"\"\"Normalize values by population.\"\"\"\n",
    "    \n",
    "    label = 'normalized'\n",
    "    mydf = df.copy()\n",
    "\n",
    "    \n",
    "    for what in ['cases',\n",
    "                 'cases_avg',\n",
    "                 'cases_avg_active',\n",
    "                 'cases_avg_perDay',\n",
    "                 'cases_active',\n",
    "                 'cases_active_avg',\n",
    "                 'cases_perDay',\n",
    "                 'cases_perDay_avg',\n",
    "                 'deaths',\n",
    "                 'deaths_avg',\n",
    "                 'deaths_avg_new',\n",
    "                 'deaths_perDay',\n",
    "                 'deaths_perDay_avg',\n",
    "                ]:\n",
    "\n",
    "        if what in df.columns:\n",
    "            mydf[f'{what}_{label}'] = 0\n",
    "\n",
    "            for where in df['location'].unique():\n",
    "                here = df[ df['location'] == where ][what]\n",
    "                mydf.loc[here.index,f'{what}_{label}'] = here/df.iloc[here.index]['population']\n",
    "\n",
    "    return mydf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "oeCS3Wv1OVzD"
   },
   "outputs": [],
   "source": [
    "def add_averaging(df,period=7,center=True):\n",
    "    \"\"\"Average over period.\"\"\"\n",
    "    \n",
    "    label = 'avg'\n",
    "    mydf = df.copy()\n",
    "    \n",
    "    for what in ['cases',\n",
    "                 'cases_active',\n",
    "                 'cases_active_normalized',\n",
    "                 'cases_perDay',\n",
    "                 'cases_perDay_normalized',\n",
    "                 'deaths',\n",
    "                 'deaths_perDay',\n",
    "                 'deaths_perDay_normalized',\n",
    "                ]:\n",
    "\n",
    "        if what in df.columns:\n",
    "            mydf[f'{what}_{label}'] = 0\n",
    "            \n",
    "            for where in df['location'].unique():\n",
    "                here = df[ df['location'] == where ][what]\n",
    "                p = min(len(here.values),period)\n",
    "                mydf.loc[here.index,f'{what}_{label}'] = here.rolling(p,center=center).mean()\n",
    "\n",
    "    return mydf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "emRfTD_LLviJ"
   },
   "outputs": [],
   "source": [
    "def datasets(source,echo=True):\n",
    "    \"\"\"Report (or print if echo==True) the available data sets of data frame \"source\".\"\"\"\n",
    "    \n",
    "    exclusions = set(['date',\n",
    "                      'population',\n",
    "                      'location',\n",
    "                     ])\n",
    "    valid = set(source.columns) - exclusions\n",
    "    if echo:\n",
    "        print('\\n'.join(sorted(list(valid))))\n",
    "    else:\n",
    "        return valid\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cm3MuwYOOYOW"
   },
   "outputs": [],
   "source": [
    "def plot(df,what,width=800,height=600,y_min=None,y_max=None):\n",
    "    \"\"\"Generate a semilog plot of the time series of \"what\".\"\"\"\n",
    "\n",
    "  \n",
    "    def lastValid(series,index=False):\n",
    "        for i,item in enumerate(series[::-1]):\n",
    "            if not (np.isnan(item) or item == 0) : return len(series)-(i+1) if index else item\n",
    "        return -1 if index else np.nan\n",
    "\n",
    "    possible = datasets(df,echo=False)\n",
    "    requested = set(what if isinstance(what,list) else [what])\n",
    "    valid = requested & possible\n",
    "    if bool(valid ^ requested):\n",
    "        print(f'invalid data: {valid^requested}')\n",
    "\n",
    "    if valid ^ requested != requested:\n",
    "        valid = list(valid)\n",
    "        y_min = max(1e-9,min(abs(df[valid]).min())) if y_min is None else y_min\n",
    "        y_max = max(abs(df[valid]).max()) if y_max is None else y_max\n",
    "\n",
    "        p = px.line(df,\n",
    "                    x='date',\n",
    "                    y=valid,\n",
    "                    color='location',\n",
    "                    color_discrete_sequence=px.colors.qualitative.Alphabet,\n",
    "                    line_group='variable',\n",
    "                    hover_name='location',\n",
    "                    hover_data=['population',],\n",
    "                    log_y=True,\n",
    "                    range_y=[y_min,y_max],\n",
    "                    )\n",
    "        annotations = []\n",
    "        p.for_each_trace(\n",
    "            lambda trace: annotations.append(dict(x=trace.x[lastValid(trace.y,index=True)],\n",
    "                                                  y=np.log10(lastValid(trace.y)),\n",
    "                                                  text=trace.name,\n",
    "                                                  clicktoshow='onoff',\n",
    "                                                  visible=False,\n",
    "                                                  opacity=0.5,\n",
    "                                            )),\n",
    "        )\n",
    "\n",
    "        p.update_xaxes(title_text='<b>date</b>')\n",
    "        p.update_yaxes(title_text='<br>'.join(map(lambda x:f'<b>{x}</b>',valid)))\n",
    "        p.update_layout(annotations=annotations,\n",
    "                        template='plotly_white',\n",
    "                        height=height,\n",
    "                        width=width,\n",
    "                       )\n",
    "\n",
    "        p.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "QBve1RVxWD8a"
   },
   "source": [
    "## Data acquisition\n",
    "\n",
    "Read data and transform into minimum required format that contains\n",
    "\n",
    "* date\n",
    "* location\n",
    "* population\n",
    "* cases\n",
    "* [deaths]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "q4aeoRdGWD8b"
   },
   "outputs": [],
   "source": [
    "World = isolate(pd.read_csv('https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/owid-covid-data.csv',\n",
    "                            parse_dates=['date']),\n",
    "                {'date':       'date',\n",
    "                 'location':   'location',\n",
    "                 'population': 'population',\n",
    "                 'cases':      'total_cases',\n",
    "                 'deaths':     'total_deaths',\n",
    "                })\n",
    "\n",
    "US = isolate(pd.read_csv('https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-states.csv',\n",
    "                         parse_dates=['date']).\n",
    "                sort_values(by=['state','date'],ignore_index=True),\n",
    "             {'date':     'date',\n",
    "              'location': 'state',\n",
    "              'cases':    'cases',\n",
    "              'deaths':   'deaths',   \n",
    "             })\n",
    "\n",
    "US['population'] = US['location'].map(\n",
    "    {\n",
    "        'California':    39937489,\n",
    "        'Texas':         29472295,\n",
    "        'Florida':       21992985,\n",
    "        'New York':      19440469,\n",
    "        'Pennsylvania':  12820878,\n",
    "        'Illinois':      12659682,\n",
    "        'Ohio':          11747694,\n",
    "        'Georgia':       10736059,\n",
    "        'North Carolina':10611862,\n",
    "        'Michigan':      10045029,\n",
    "        'New Jersey':     8936574,\n",
    "        'Virginia':       8626207,\n",
    "        'Washington':     7797095,\n",
    "        'Arizona':        7378494,\n",
    "        'Massachusetts':  6976597,\n",
    "        'Tennessee':      6897576,\n",
    "        'Indiana':        6745354,\n",
    "        'Missouri':       6169270,\n",
    "        'Maryland':       6083116,\n",
    "        'Wisconsin':      5851754,\n",
    "        'Colorado':       5845526,\n",
    "        'Minnesota':      5700671,\n",
    "        'South Carolina': 5210095,\n",
    "        'Alabama':        4908621,\n",
    "        'Louisiana':      4645184,\n",
    "        'Kentucky':       4499692,\n",
    "        'Oregon':         4301089,\n",
    "        'Oklahoma':       3954821,\n",
    "        'Connecticut':    3563077,\n",
    "        'Utah':           3282115,\n",
    "        'Iowa':           3179849,\n",
    "        'Nevada':         3139658,\n",
    "        'Arkansas':       3038999,\n",
    "        'Puerto Rico':    3032165,\n",
    "        'Mississippi':    2989260,\n",
    "        'Kansas':         2910357,\n",
    "        'New Mexico':     2096640,\n",
    "        'Nebraska':       1952570,\n",
    "        'Idaho':          1826156,\n",
    "        'West Virginia':  1778070,\n",
    "        'Hawaii':         1412687,\n",
    "        'New Hampshire':  1371246,\n",
    "        'Maine':          1345790,\n",
    "        'Montana':        1086759,\n",
    "        'Rhode Island':   1056161,\n",
    "        'Delaware':        982895,\n",
    "        'South Dakota':    903027,\n",
    "        'North Dakota':    761723,\n",
    "        'Alaska':          734002,\n",
    "        'District of Columbia':720687,\n",
    "        'Vermont':         628061,\n",
    "        'Wyoming':         567025,\n",
    "    }\n",
    ")\n",
    "\n",
    "df = pd.read_csv('https://raw.githubusercontent.com/jgehrcke/covid-19-germany-gae/master/data.csv',\n",
    "                 parse_dates=['time_iso8601']).\\\n",
    "        rename(columns={'sum_cases': 'DE_cases',\n",
    "                        'sum_deaths':'DE_deaths'}).\\\n",
    "        melt('time_iso8601',var_name='tmp')\n",
    "df['location'] = df['tmp'].apply(lambda x: (x.split('_'))[0])\n",
    "df['type'] = df.pop('tmp').apply(lambda x: (x.split('_'))[-1])\n",
    "tmp = df[ df['location'] != 'source' ].set_index(['time_iso8601','location','type']).unstack(level=-1)\n",
    "\n",
    "Germany = isolate(pd.DataFrame({'deaths': tmp[('value',  'deaths')],\n",
    "                                 'cases': tmp[('value',  'cases')],\n",
    "                               }).reset_index(),\n",
    "             {'date':     'time_iso8601',\n",
    "              'location': 'location',\n",
    "              'cases':    'cases',\n",
    "              'deaths':   'deaths',\n",
    "             })\n",
    "\n",
    "\n",
    "Germany['population'] = Germany['location'].map(\n",
    "    {\n",
    "        'DE-BW':11069533,\n",
    "        'DE-BY':13076721,\n",
    "        'DE-BE': 3644826,\n",
    "        'DE-BB': 2511917,\n",
    "        'DE-HB':  682986,\n",
    "        'DE-HH': 1841179,\n",
    "        'DE-HE': 6265809,\n",
    "        'DE-NI': 7982448,\n",
    "        'DE-MV': 1609675,\n",
    "        'DE-NW':17932651,\n",
    "        'DE-RP': 4084844,\n",
    "        'DE-SL':  990509,\n",
    "        'DE-SN': 4077937,\n",
    "        'DE-ST': 2208321,\n",
    "        'DE-SH': 2896712,\n",
    "        'DE-TH': 2143145,\n",
    "        'DE':   83019213,\n",
    "    }\n",
    ")\n",
    "\n",
    "World = add_normalization(\n",
    "      add_averaging(\n",
    "      add_rate(\n",
    "      add_active(World)\n",
    "      )\n",
    "      )\n",
    "      )\n",
    "\n",
    "US = add_normalization(\n",
    "      add_averaging(\n",
    "      add_rate(\n",
    "      add_active(US)\n",
    "      )\n",
    "      )\n",
    "      )\n",
    "\n",
    "Germany = add_normalization(\n",
    "      add_averaging(\n",
    "      add_rate(\n",
    "      add_active(resample(Germany))\n",
    "      )\n",
    "      )\n",
    "      )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "2a6Z3sF-WD8f"
   },
   "source": [
    "## Visualization of results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iO8k5AzSLEw3"
   },
   "source": [
    "### Data sources\n",
    "\n",
    "There are presently three separate data sources available:\n",
    "\n",
    "*   World\n",
    "*   US\n",
    "*   Germany"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data source subsets\n",
    "\n",
    "Specific subsets of those data sources can be created using the `query` method.\n",
    "\n",
    "#### Examples\n",
    "\n",
    "*   Select three particular US states:\n",
    "```python\n",
    "US.query('location in [\"California\",\"Texas\",\"New York\"]')\n",
    "```\n",
    "\n",
    "*   Restrict to countries with more than 100 million people:\n",
    "```python\n",
    "World.query('population > 100e6')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Available data sets\n",
    "\n",
    "Use the function `datasets` to interrogate what data can be plotted for each source."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "cCLQgJVoLp84"
   },
   "outputs": [],
   "source": [
    "datasets(US)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "rOJQ-BZHMWPW"
   },
   "source": [
    "### Plotting\n",
    "\n",
    "The function `plot` can take a single item or list of available data sets to create a visualization of the time evolution for one data source.\n",
    "\n",
    "Visibility of curves in the resulting graph can be toggled by a single click on their respective legend. A double click on a legend item toggles between *only this curve* and *all curves* being shown.\n",
    "\n",
    "#### Examples\n",
    "\n",
    "*   Daily change of cases smoothed over seven days and normalized by population size for all reporting countries in the world:\n",
    "```\n",
    "plot(World,'cases_perDay_avg_normalized')\n",
    "```\n",
    "*   Comparison of cumulated cases *and* deaths, i.e. two separate data sets, across German Bundesländer (states) normalized by population size:\n",
    "```\n",
    "plot(Germany,['cases_normalized','deaths_normalized'])\n",
    "```\n",
    "*   Daily change of deaths smoothed over seven days and normalized by population size for US states North Carolina and South Carolina:\n",
    "```\n",
    "plot(US.query('location in [\"North Carolina\",\"South Carolina\"]'),'deaths_perDay_avg_normalized')\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Iqv8B9TeWD8f"
   },
   "outputs": [],
   "source": [
    "plot(World,\n",
    "     [\n",
    "      'cases_active_normalized',\n",
    "     ],\n",
    "     y_min=1e-6,\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "COVID_Visualization.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}