COVID_19_Spread.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pyplot import figure\n",
    "import adjustText as aT\n",
    "import geopandas as gpd\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"statewide_cases.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sanity checks\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['county'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[col for col in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "round(df.describe(), 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['newcountconfirmed'] < 0].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df['newcountdeaths'] < 0].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_county = df.groupby('county').nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove unassigned and out of county values\n",
    "df = df[df[\"county\"] != 'Unassigned']\n",
    "df = df[df[\"county\"] != 'Out Of Country']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge Datasets and Calculate Indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import Geography dataset\n",
    "fp = \"CA_Counties/CA_Counties_TIGER2016.shp\"\n",
    "map_df = gpd.read_file(fp)\n",
    "map_df.head()\n",
    "\n",
    "# Merge with original dataset\n",
    "merged = map_df.merge(df, how = 'left', left_on='NAME', right_on='county')\n",
    "\n",
    "# Select only desired variables\n",
    "merged2 = merged[['NAME', 'geometry', 'totalcountconfirmed', 'totalcountdeaths', 'newcountconfirmed', 'newcountdeaths', 'date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add County population data\n",
    "county_pop = pd.read_csv('california_county_pop.csv')\n",
    "\n",
    "# Drop growth rate column\n",
    "county_pop.drop('GrowthRate', axis=1, inplace=True)\n",
    "\n",
    "# Remove suffix from county names so can merge with DF\n",
    "county_pop['CTYNAME'].replace(' County', '', regex=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new master DF with geography and county populations\n",
    "merged3 = merged2.merge(county_pop, how = 'left', left_on='NAME', right_on='CTYNAME')\n",
    "\n",
    "# Drop extraneous name variable\n",
    "merged3.drop(['CTYNAME'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace negative values with 0\n",
    "merged3[merged3['newcountdeaths'] <0] = 0\n",
    "merged3[merged3['newcountconfirmed'] <0] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate incidence/prevalence/mortality\n",
    "merged3['incidence_rate'] = merged3['newcountconfirmed']/merged3['pop2018']\n",
    "merged3['mortality_rate'] = merged3['newcountdeaths']/merged3['pop2018']\n",
    "merged3['prevalence'] = merged3['totalcountconfirmed']/merged3['pop2018']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Subdivide Dataset into Months, Groupby and Calculate Means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Split dataset into 4 months\n",
    "month_1 = merged3[(merged4['date'] >= pd.to_datetime('2020-03-18')) & (merged3['date'] < pd.to_datetime('2020-04-18'))]\n",
    "month_2 = merged3[(merged4['date'] >= pd.to_datetime('2020-04-18')) & (merged3['date'] < pd.to_datetime('2020-05-18'))]\n",
    "month_3 = merged3[(merged4['date'] >= pd.to_datetime('2020-05-18')) & (merged3['date'] < pd.to_datetime('2020-06-18'))]\n",
    "month_4 = merged3[(merged4['date'] >= pd.to_datetime('2020-06-18')) & (merged3['date'] < pd.to_datetime('2020-07-18'))]                "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Groupby county name and calculate mean\n",
    "month_1_grp = month_1.groupby('NAME').mean()\n",
    "month_2_grp = month_2.groupby('NAME').mean()\n",
    "month_3_grp = month_3.groupby('NAME').mean()\n",
    "month_4_grp = month_4.groupby('NAME').mean()\n",
    "\n",
    "# Reset county indices\n",
    "month_1_grp.reset_index()\n",
    "month_2_grp.reset_index()\n",
    "month_3_grp.reset_index()\n",
    "month_4_grp.reset_index()\n",
    "\n",
    "# Replace lost geometry column\n",
    "month_1_grp = map_df.merge(month_1_grp, how = 'left', left_on='NAME', right_on='NAME')\n",
    "month_2_grp = map_df.merge(month_2_grp, how = 'left', left_on='NAME', right_on='NAME')\n",
    "month_3_grp = map_df.merge(month_3_grp, how = 'left', left_on='NAME', right_on='NAME')\n",
    "month_4_grp = map_df.merge(month_4_grp, how = 'left', left_on='NAME', right_on='NAME')\n",
    "\n",
    "# Eliminate unneeded variables\n",
    "month_1_grp = month_1_grp[['NAME', 'geometry', 'newcountconfirmed', 'newcountdeaths', 'totalcountconfirmed', 'newcountdeaths', 'pop2018', 'incidence_rate', 'mortality_rate', 'prevalence']]\n",
    "month_2_grp = month_2_grp[['NAME', 'geometry', 'newcountconfirmed', 'newcountdeaths', 'totalcountconfirmed', 'newcountdeaths', 'pop2018', 'incidence_rate', 'mortality_rate', 'prevalence']]\n",
    "month_3_grp = month_3_grp[['NAME', 'geometry', 'newcountconfirmed', 'newcountdeaths', 'totalcountconfirmed', 'newcountdeaths', 'pop2018', 'incidence_rate', 'mortality_rate', 'prevalence']]\n",
    "month_4_grp = month_4_grp[['NAME', 'geometry', 'newcountconfirmed', 'newcountdeaths', 'totalcountconfirmed', 'newcountdeaths', 'pop2018', 'incidence_rate', 'mortality_rate', 'prevalence']]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Choropleths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate centroids for labeling tasks\n",
    "month_1_grp['center'] = month_1_grp['geometry'].centroid\n",
    "month_2_grp['center'] = month_2_grp['geometry'].centroid\n",
    "month_3_grp['center'] = month_3_grp['geometry'].centroid\n",
    "month_4_grp['center'] = month_4_grp['geometry'].centroid\n",
    "\n",
    "# Create copies for use by geopandas\n",
    "month_1_points = month_1_grp.copy()\n",
    "month_2_points = month_2_grp.copy()\n",
    "month_3_points = month_3_grp.copy()\n",
    "month_4_points = month_4_grp.copy()\n",
    "\n",
    "# Set geometry\n",
    "month_1_points.set_geometry('center', inplace=True)\n",
    "month_2_points.set_geometry('center', inplace=True)\n",
    "month_3_points.set_geometry('center', inplace=True)\n",
    "month_4_points.set_geometry('center', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The code to create the choropleths and label them - algorithm labels county if its averaged monthly index in top 1/2 of Statewide average\n",
    "# Month 1 incidence\n",
    "vmin, vmax = min(merged4['incidence_rate']), max(merged4['incidence_rate'])\n",
    "ax = month_1.plot(column='incidence_rate', figsize = (10,10), cmap='Reds', linewidth=1.0, edgecolor='0.7', vmin=vmin, vmax=vmax, legend=True, norm=plt.Normalize(vmin=vmin, vmax=vmax))\n",
    "plt.axis('off')\n",
    "plt.title('Average Incidence', fontsize=17)\n",
    "texts = []\n",
    "\n",
    "for x, y, label, metric in zip(month_1_points.geometry.x, month_1_points.geometry.y, month_1_points['NAME'], month_1_points['incidence_rate']):\n",
    "        if metric >= month_1_grp['incidence_rate'].mean():\n",
    "            texts.append(plt.text(x,y,label,fontsize=8))\n",
    "        else:\n",
    "            pass\n",
    "\n",
    "aT.adjust_text(texts, ax=ax, arrowprops=dict(arrowstyle=\"->\", color='black'))"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}