covid_hosp_mort_update.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 Hospitalization, ICU & Mortality Dashboard Update\n",
    "Center for Human Dynamics in the Mobile Age (HDMA) at San Diego State University\n",
    "\n",
    "Jessica Embury"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "import arcgis\n",
    "from arcgis.gis import GIS\n",
    "from arcgis import geometry\n",
    "from arcgis.features import GeoAccessor, GeoSeriesAccessor\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "from arcgis.features import FeatureLayer\n",
    "from arcgis.mapping import WebMap\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "import json\n",
    "import sys\n",
    "import webbrowser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Entered Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "###############\n",
    "###SET DATES###\n",
    "###############\n",
    "file_date = '26DEC2020'\n",
    "formatted_date = '122620'\n",
    "\n",
    "##########################\n",
    "###SET PATHS IN AND OUT###\n",
    "##########################\n",
    "#PATHS IN\n",
    "sra_in = './data/sra_info.csv'\n",
    "hosp_in = '../covid_data/hosp_icu_mort/COVID19_HospRate_{}.csv'.format(file_date)\n",
    "icu_in = '../covid_data/hosp_icu_mort/COVID19_ICURate_{}.csv'.format(file_date)\n",
    "mort_in = '../covid_data/hosp_icu_mort/COVID19_MortalityRate_{}.csv'.format(file_date)\n",
    "\n",
    "#PATHS OUT\n",
    "hosp_out = './data/covid19_hosp_upload.csv'\n",
    "icu_out = './data/covid_icu_upload.csv'\n",
    "mort_out = './data/covid_mort_upload.csv'\n",
    "\n",
    "#Cumulative Dates Files\n",
    "hosp_dates = './data/covid19_hospitalization.csv'\n",
    "icu_dates = './data/covid_icu.csv'\n",
    "mort_dates = './data/covid_mortality.csv'\n",
    "\n",
    "hosp_db = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/Hosp-ICU-Mortalities/covid19_hospitalization_{}.csv'.format(formatted_date)\n",
    "icu_db = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/Hosp-ICU-Mortalities/covid19_icu_{}.csv'.format(formatted_date)\n",
    "mort_db = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/Hosp-ICU-Mortalities/covid19_mortalities_{}.csv'.format(formatted_date)\n",
    "\n",
    "######################################\n",
    "###ARCGIS ACCOUNT LOGIN INFORMATION###\n",
    "######################################\n",
    "#reference for authentication schemes: https://developers.arcgis.com/python/guide/working-with-different-authentication-schemes/\n",
    "pro = \"pro\"\n",
    "#portal =''\n",
    "#username=''\n",
    "#password=''\n",
    "\n",
    "################################\n",
    "###ARCGIS DETAILS FOR UPDATES###\n",
    "################################\n",
    "hosp_layer_id = '2f3443de1a9345d79316484d6c273342'\n",
    "hosp_map_id = '3b5af1a898ae49e3825bac21badea77c'\n",
    "hosp_csv_id = '0413f1006e94418b8ecd3af609ec5dff'\n",
    "\n",
    "icu_layer_id ='164de458148f4c9db3708cc8e99ddbc8'\n",
    "icu_map_id = '128d6eb1547c4958818a47464da1a079'\n",
    "icu_csv_id = '7dc2c321393e402b9931d5595fae8678'\n",
    "\n",
    "mort_layer_id = '1bf213866fad441db609577cad03c7d9'\n",
    "mort_map_id = '64f43b6d62ef46c18bf6db55fce5afce'\n",
    "mort_csv_id = '11047e66ca6f483d8873078ef392e18c'\n",
    "\n",
    "dash = 'https://arcg.is/0amqvz'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to ArcGIS Account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "gis = GIS(pro)\n",
    "#gis = GIS(portal, username, password)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Functions for Map Modifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_map (map_id):\n",
    "    '''\n",
    "    GET MAP DATA FOR SYMBOLOGY CHANGES\n",
    "    '''    \n",
    "    m = gis.content.get(map_id)\n",
    "    data = m.get_data()\n",
    "    print(m)    \n",
    "    return data\n",
    "\n",
    "def update_map (map_id, data):\n",
    "    '''\n",
    "    UPDATE MAP TO SAVE CHANGES\n",
    "    '''\n",
    "    m = gis.content.get(map_id)  \n",
    "    # Set the item_properties to include the desired update\n",
    "    properties = {\"text\": json.dumps(data)}\n",
    "    # 'Commit' the updates to the Item\n",
    "    update = m.update(item_properties=properties)    \n",
    "    return update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HOSPITALIZATION"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Format Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name   latitude   longitude\n",
       "0        1       Central San Diego  32.722644 -117.141073\n",
       "1        2               Peninsula  32.742274 -117.216035\n",
       "2        3                Coronado  32.657317 -117.143062\n",
       "3        4           National City  32.665847 -117.099737\n",
       "4        5  Southeastern San Diego  32.685705 -117.038621"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#BASE SRA DATA\n",
    "sra = pd.read_csv(sra_in)\n",
    "print(len(sra))\n",
    "sra.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12/26/2020\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Geography</th>\n",
       "      <th>Allocated Hospitalization Count (Raw)</th>\n",
       "      <th>COVID-19 Total Hospitalization Rate (Suppressed)</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alpine</td>\n",
       "      <td>20.8</td>\n",
       "      <td>129.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anza-Borrego Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carlsbad</td>\n",
       "      <td>76.0</td>\n",
       "      <td>60.1</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Central Region</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>220.9</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>365.5</td>\n",
       "      <td>173.3</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Geography  ...        Date\n",
       "0                Alpine  ...  12/26/2020\n",
       "1  Anza-Borrego Springs  ...  12/26/2020\n",
       "2              Carlsbad  ...  12/26/2020\n",
       "3        Central Region  ...  12/26/2020\n",
       "4     Central San Diego  ...  12/26/2020\n",
       "\n",
       "[5 rows x 4 columns]"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hosp = pd.read_csv(hosp_in)\n",
    "hosp = hosp.rename(columns = {'date':'Date'})\n",
    "date = hosp['Date'][0]\n",
    "print(date)\n",
    "hosp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_name</th>\n",
       "      <th>hosp_count</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alpine</td>\n",
       "      <td>20.8</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anza-Borrego Springs</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carlsbad</td>\n",
       "      <td>76.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Central Region</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>365.5</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               sra_name  hosp_count        Date\n",
       "0                Alpine        20.8  12/26/2020\n",
       "1  Anza-Borrego Springs         0.0  12/26/2020\n",
       "2              Carlsbad        76.0  12/26/2020\n",
       "3        Central Region      1138.0  12/26/2020\n",
       "4     Central San Diego       365.5  12/26/2020"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del hosp['COVID-19 Total Hospitalization Rate (Suppressed)']\n",
    "hosp = hosp.rename(columns = {'Allocated Hospitalization Count (Raw)':'hosp_count', 'Geography':'sra_name'})\n",
    "hosp = hosp.fillna(0)\n",
    "hosp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>hosp_count</th>\n",
       "      <th>Date</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>365.5</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>365.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "      <td>30.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "      <td>11.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "      <td>221.1</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>221.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "      <td>468.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name   latitude  ...  hosp_count        Date  label\n",
       "0        1       Central San Diego  32.722644  ...       365.5  12/26/2020  365.5\n",
       "1        2               Peninsula  32.742274  ...        30.0  12/26/2020   30.0\n",
       "2        3                Coronado  32.657317  ...        11.0  12/26/2020   11.0\n",
       "3        4           National City  32.665847  ...       221.1  12/26/2020  221.1\n",
       "4        5  Southeastern San Diego  32.685705  ...       468.0  12/26/2020  468.0\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge to sra df with coordinates\n",
    "hosp_merge = sra.merge(hosp, how='left', on='sra_name')\n",
    "hosp_merge['label'] = hosp_merge['hosp_count'].astype(str)\n",
    "print(len(hosp_merge))\n",
    "hosp_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export csv for feature layer overwrite\n",
    "hosp_merge.to_csv(hosp_out, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_name</th>\n",
       "      <th>12/26/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>365.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Peninsula</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Coronado</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>National City</td>\n",
       "      <td>221.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 sra_name  12/26/2020\n",
       "0       Central San Diego       365.5\n",
       "1               Peninsula        30.0\n",
       "2                Coronado        11.0\n",
       "3           National City       221.1\n",
       "4  Southeastern San Diego       468.0"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#save csv with a column for every date\n",
    "#prepare new data for addition to existing csv\n",
    "hosp_temp = hosp_merge[['sra_name','hosp_count']]\n",
    "hosp_temp = hosp_temp.rename(columns = {'hosp_count':'{}'.format(date)})\n",
    "hosp_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>6/3/2020</th>\n",
       "      <th>6/10/2020</th>\n",
       "      <th>6/17/2020</th>\n",
       "      <th>6/24/2020</th>\n",
       "      <th>7/1/2020</th>\n",
       "      <th>7/8/2020</th>\n",
       "      <th>7/14/2020</th>\n",
       "      <th>7/28/2020</th>\n",
       "      <th>8/4/2020</th>\n",
       "      <th>8/11/2020</th>\n",
       "      <th>8/18/2020</th>\n",
       "      <th>8/24/2020</th>\n",
       "      <th>8/29/2020</th>\n",
       "      <th>9/5/2020</th>\n",
       "      <th>9/19/2020</th>\n",
       "      <th>9/26/2020</th>\n",
       "      <th>10/3/2020</th>\n",
       "      <th>10/10/2020</th>\n",
       "      <th>10/17/2020</th>\n",
       "      <th>10/24/2020</th>\n",
       "      <th>10/31/2020</th>\n",
       "      <th>11/7/2020</th>\n",
       "      <th>11/14/2020</th>\n",
       "      <th>11/21/2020</th>\n",
       "      <th>11/28/2020</th>\n",
       "      <th>12/12/2020</th>\n",
       "      <th>12/26/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>94.39</td>\n",
       "      <td>102.22</td>\n",
       "      <td>109.7</td>\n",
       "      <td>114.9</td>\n",
       "      <td>127.6</td>\n",
       "      <td>138.8</td>\n",
       "      <td>150.6</td>\n",
       "      <td>176.5</td>\n",
       "      <td>189.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>198.1</td>\n",
       "      <td>199.9</td>\n",
       "      <td>205.7</td>\n",
       "      <td>207.7</td>\n",
       "      <td>217.5</td>\n",
       "      <td>225.6</td>\n",
       "      <td>238.9</td>\n",
       "      <td>242.1</td>\n",
       "      <td>248.0</td>\n",
       "      <td>250.8</td>\n",
       "      <td>262.4</td>\n",
       "      <td>269.5</td>\n",
       "      <td>282.0</td>\n",
       "      <td>293.2</td>\n",
       "      <td>304.1</td>\n",
       "      <td>335.2</td>\n",
       "      <td>365.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "      <td>7.67</td>\n",
       "      <td>7.67</td>\n",
       "      <td>9.7</td>\n",
       "      <td>9.7</td>\n",
       "      <td>10.6</td>\n",
       "      <td>10.6</td>\n",
       "      <td>12.3</td>\n",
       "      <td>14.8</td>\n",
       "      <td>14.8</td>\n",
       "      <td>16.2</td>\n",
       "      <td>16.7</td>\n",
       "      <td>17.7</td>\n",
       "      <td>17.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>18.7</td>\n",
       "      <td>19.7</td>\n",
       "      <td>21.1</td>\n",
       "      <td>22.6</td>\n",
       "      <td>23.6</td>\n",
       "      <td>24.0</td>\n",
       "      <td>26.9</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "      <td>81.06</td>\n",
       "      <td>82.99</td>\n",
       "      <td>88.8</td>\n",
       "      <td>97.5</td>\n",
       "      <td>103.3</td>\n",
       "      <td>110.0</td>\n",
       "      <td>117.7</td>\n",
       "      <td>124.6</td>\n",
       "      <td>131.3</td>\n",
       "      <td>137.1</td>\n",
       "      <td>139.0</td>\n",
       "      <td>141.9</td>\n",
       "      <td>145.8</td>\n",
       "      <td>150.6</td>\n",
       "      <td>156.4</td>\n",
       "      <td>163.2</td>\n",
       "      <td>164.1</td>\n",
       "      <td>166.1</td>\n",
       "      <td>167.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>173.8</td>\n",
       "      <td>177.7</td>\n",
       "      <td>180.6</td>\n",
       "      <td>186.4</td>\n",
       "      <td>193.1</td>\n",
       "      <td>205.7</td>\n",
       "      <td>221.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "      <td>118.17</td>\n",
       "      <td>125.60</td>\n",
       "      <td>141.4</td>\n",
       "      <td>150.3</td>\n",
       "      <td>168.2</td>\n",
       "      <td>184.6</td>\n",
       "      <td>200.2</td>\n",
       "      <td>245.2</td>\n",
       "      <td>263.0</td>\n",
       "      <td>274.8</td>\n",
       "      <td>284.4</td>\n",
       "      <td>289.9</td>\n",
       "      <td>294.9</td>\n",
       "      <td>304.7</td>\n",
       "      <td>312.7</td>\n",
       "      <td>320.6</td>\n",
       "      <td>323.6</td>\n",
       "      <td>333.5</td>\n",
       "      <td>341.0</td>\n",
       "      <td>346.9</td>\n",
       "      <td>352.3</td>\n",
       "      <td>356.7</td>\n",
       "      <td>362.3</td>\n",
       "      <td>374.9</td>\n",
       "      <td>388.6</td>\n",
       "      <td>429.6</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  12/12/2020  12/26/2020\n",
       "0        1       Central San Diego  ...       335.2       365.5\n",
       "1        2               Peninsula  ...        26.9        30.0\n",
       "2        3                Coronado  ...        11.0        11.0\n",
       "3        4           National City  ...       205.7       221.1\n",
       "4        5  Southeastern San Diego  ...       429.6       468.0\n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#add new date column to existing csv\n",
    "hosp_cum = pd.read_csv(hosp_dates)\n",
    "hosp_cum = hosp_cum.merge(hosp_temp, on='sra_name')\n",
    "hosp_cum.to_csv(hosp_dates, index=False)\n",
    "hosp_cum.to_csv(hosp_db, index=False)\n",
    "hosp_cum.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite Hospitalization Feature Layer and CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite master csv with all dates data\n",
    "csv_item = gis.content.get(hosp_csv_id)\n",
    "csv_item.update({}, hosp_dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get feature layer containing updated data \n",
    "hosp_layer = gis.content.get(hosp_layer_id)\n",
    "hosp_layer\n",
    "\n",
    "hosp_layer_collection = FeatureLayerCollection.fromitem(hosp_layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "hosp_layer_collection.manager.overwrite(hosp_out)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify Hospitalization Map Symbology"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 Hospitalizations by San Diego SRA\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get hospitalization max for graduated point symbology\n",
    "hosp_max = hosp_merge['hosp_count'].max()\n",
    "\n",
    "#get map data\n",
    "hosp_map = get_map(hosp_map_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "hosp_map['operationalLayers'][1]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = hosp_max.item()\n",
    "hosp_map['operationalLayers'][1]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = hosp_max.item()\n",
    "hosp_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = hosp_max.item()\n",
    "hosp_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = hosp_max.item()\n",
    "\n",
    "#update map to save changes\n",
    "hosp_map_update = update_map(hosp_map_id, hosp_map)\n",
    "hosp_map_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# MORTALITY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Format Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12/26/2020\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Geography</th>\n",
       "      <th>Allocated Death Count (Raw)</th>\n",
       "      <th>COVID-19 Total Mortality Rate (Suppressed)</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alpine</td>\n",
       "      <td>5.4</td>\n",
       "      <td>33.7</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anza-Borrego Springs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carlsbad</td>\n",
       "      <td>15.0</td>\n",
       "      <td>11.8</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Central Region</td>\n",
       "      <td>251.0</td>\n",
       "      <td>48.7</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>87.4</td>\n",
       "      <td>41.5</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Geography  ...        Date\n",
       "0                Alpine  ...  12/26/2020\n",
       "1  Anza-Borrego Springs  ...  12/26/2020\n",
       "2              Carlsbad  ...  12/26/2020\n",
       "3        Central Region  ...  12/26/2020\n",
       "4     Central San Diego  ...  12/26/2020\n",
       "\n",
       "[5 rows x 4 columns]"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mort = pd.read_csv(mort_in)\n",
    "mort = mort.rename(columns = {'date':'Date'})\n",
    "date = mort['Date'][0]\n",
    "print(date)\n",
    "\n",
    "mort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_name</th>\n",
       "      <th>mort_count</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alpine</td>\n",
       "      <td>5.4</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anza-Borrego Springs</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Carlsbad</td>\n",
       "      <td>15.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Central Region</td>\n",
       "      <td>251.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>87.4</td>\n",
       "      <td>12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               sra_name  mort_count        Date\n",
       "0                Alpine         5.4  12/26/2020\n",
       "1  Anza-Borrego Springs         0.0  12/26/2020\n",
       "2              Carlsbad        15.0  12/26/2020\n",
       "3        Central Region       251.0  12/26/2020\n",
       "4     Central San Diego        87.4  12/26/2020"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del mort['COVID-19 Total Mortality Rate (Suppressed)']\n",
    "mort = mort.rename(columns = {'Allocated Death Count (Raw)':'mort_count', 'Geography':'sra_name'})\n",
    "mort = mort.fillna(0)\n",
    "mort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>mort_count</th>\n",
       "      <th>Date</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>87.4</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>87.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "      <td>7.2</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "      <td>5.0</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "      <td>71.4</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>71.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "      <td>115.8</td>\n",
       "      <td>12/26/2020</td>\n",
       "      <td>115.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name   latitude  ...  mort_count        Date  label\n",
       "0        1       Central San Diego  32.722644  ...        87.4  12/26/2020   87.4\n",
       "1        2               Peninsula  32.742274  ...         7.2  12/26/2020    7.2\n",
       "2        3                Coronado  32.657317  ...         5.0  12/26/2020    5.0\n",
       "3        4           National City  32.665847  ...        71.4  12/26/2020   71.4\n",
       "4        5  Southeastern San Diego  32.685705  ...       115.8  12/26/2020  115.8\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#merge to sra df with coordinates\n",
    "mort_merge = sra.merge(mort, how='left', on='sra_name')\n",
    "mort_merge['label'] = mort_merge['mort_count'].astype(str)\n",
    "print(len(mort_merge))\n",
    "mort_merge.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {},
   "outputs": [],
   "source": [
    "#export csv for feature layer overwrite\n",
    "mort_merge.to_csv(mort_out, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_name</th>\n",
       "      <th>12/26/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>87.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Peninsula</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Coronado</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>National City</td>\n",
       "      <td>71.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>115.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 sra_name  12/26/2020\n",
       "0       Central San Diego        87.4\n",
       "1               Peninsula         7.2\n",
       "2                Coronado         5.0\n",
       "3           National City        71.4\n",
       "4  Southeastern San Diego       115.8"
      ]
     },
     "execution_count": 217,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#save csv with a column for every date\n",
    "#prepare new data for addition to existing csv\n",
    "mort_temp = mort_merge[['sra_name','mort_count']]\n",
    "mort_temp = mort_temp.rename(columns = {'mort_count':'{}'.format(date)})\n",
    "mort_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sra_num</th>\n",
       "      <th>sra_name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>6/3/20</th>\n",
       "      <th>6/10/2020</th>\n",
       "      <th>6/17/2020</th>\n",
       "      <th>6/24/2020</th>\n",
       "      <th>7/1/2020</th>\n",
       "      <th>7/8/2020</th>\n",
       "      <th>7/14/2020</th>\n",
       "      <th>7/28/2020</th>\n",
       "      <th>8/4/2020</th>\n",
       "      <th>8/11/2020</th>\n",
       "      <th>8/18/2020</th>\n",
       "      <th>8/24/2020</th>\n",
       "      <th>8/29/2020</th>\n",
       "      <th>9/5/2020</th>\n",
       "      <th>9/19/2020</th>\n",
       "      <th>9/26/2020</th>\n",
       "      <th>10/3/2020</th>\n",
       "      <th>10/10/2020</th>\n",
       "      <th>10/17/2020</th>\n",
       "      <th>10/24/2020</th>\n",
       "      <th>10/31/2020</th>\n",
       "      <th>11/7/2020</th>\n",
       "      <th>11/14/20</th>\n",
       "      <th>11/21/20</th>\n",
       "      <th>11/28/20</th>\n",
       "      <th>12/12/2020</th>\n",
       "      <th>12/26/2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Central San Diego</td>\n",
       "      <td>32.722644</td>\n",
       "      <td>-117.141073</td>\n",
       "      <td>15.584</td>\n",
       "      <td>16.985</td>\n",
       "      <td>19.1</td>\n",
       "      <td>21.8</td>\n",
       "      <td>23.5</td>\n",
       "      <td>25.6</td>\n",
       "      <td>28.8</td>\n",
       "      <td>35.8</td>\n",
       "      <td>38.5</td>\n",
       "      <td>40.5</td>\n",
       "      <td>40.9</td>\n",
       "      <td>41.3</td>\n",
       "      <td>41.3</td>\n",
       "      <td>42.3</td>\n",
       "      <td>43.6</td>\n",
       "      <td>44.2</td>\n",
       "      <td>50.3</td>\n",
       "      <td>50.7</td>\n",
       "      <td>52.4</td>\n",
       "      <td>53.8</td>\n",
       "      <td>57.2</td>\n",
       "      <td>59.5</td>\n",
       "      <td>64.1</td>\n",
       "      <td>67.1</td>\n",
       "      <td>69.3</td>\n",
       "      <td>76.7</td>\n",
       "      <td>87.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Peninsula</td>\n",
       "      <td>32.742274</td>\n",
       "      <td>-117.216035</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.2</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>5.7</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "      <td>6.1</td>\n",
       "      <td>7.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Coronado</td>\n",
       "      <td>32.657317</td>\n",
       "      <td>-117.143062</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>National City</td>\n",
       "      <td>32.665847</td>\n",
       "      <td>-117.099737</td>\n",
       "      <td>21.224</td>\n",
       "      <td>23.154</td>\n",
       "      <td>24.1</td>\n",
       "      <td>25.1</td>\n",
       "      <td>26.0</td>\n",
       "      <td>32.8</td>\n",
       "      <td>34.7</td>\n",
       "      <td>38.6</td>\n",
       "      <td>41.5</td>\n",
       "      <td>41.5</td>\n",
       "      <td>43.4</td>\n",
       "      <td>43.4</td>\n",
       "      <td>43.4</td>\n",
       "      <td>46.3</td>\n",
       "      <td>48.3</td>\n",
       "      <td>48.3</td>\n",
       "      <td>50.2</td>\n",
       "      <td>51.2</td>\n",
       "      <td>52.1</td>\n",
       "      <td>52.1</td>\n",
       "      <td>53.1</td>\n",
       "      <td>53.1</td>\n",
       "      <td>54.1</td>\n",
       "      <td>55.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>59.9</td>\n",
       "      <td>71.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Southeastern San Diego</td>\n",
       "      <td>32.685705</td>\n",
       "      <td>-117.038621</td>\n",
       "      <td>16.742</td>\n",
       "      <td>17.457</td>\n",
       "      <td>19.4</td>\n",
       "      <td>20.3</td>\n",
       "      <td>24.4</td>\n",
       "      <td>32.1</td>\n",
       "      <td>41.5</td>\n",
       "      <td>63.4</td>\n",
       "      <td>64.7</td>\n",
       "      <td>66.8</td>\n",
       "      <td>70.3</td>\n",
       "      <td>73.0</td>\n",
       "      <td>73.3</td>\n",
       "      <td>77.2</td>\n",
       "      <td>81.1</td>\n",
       "      <td>82.7</td>\n",
       "      <td>83.4</td>\n",
       "      <td>84.4</td>\n",
       "      <td>85.5</td>\n",
       "      <td>88.2</td>\n",
       "      <td>89.1</td>\n",
       "      <td>89.1</td>\n",
       "      <td>89.4</td>\n",
       "      <td>94.2</td>\n",
       "      <td>95.8</td>\n",
       "      <td>104.9</td>\n",
       "      <td>115.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name   latitude  ...  11/28/20  12/12/2020  12/26/2020\n",
       "0        1       Central San Diego  32.722644  ...      69.3        76.7        87.4\n",
       "1        2               Peninsula  32.742274  ...       6.1         6.1         7.2\n",
       "2        3                Coronado  32.657317  ...       0.0         5.0         5.0\n",
       "3        4           National City  32.665847  ...      56.0        59.9        71.4\n",
       "4        5  Southeastern San Diego  32.685705  ...      95.8       104.9       115.8\n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#add new date column to existing csv\n",
    "mort_cum = pd.read_csv(mort_dates)\n",
    "mort_cum = mort_cum.merge(mort_temp, on='sra_name')\n",
    "mort_cum.to_csv(mort_dates, index=False)\n",
    "mort_cum.to_csv(mort_db, index=False)\n",
    "mort_cum.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite Mortality Feature Layer and CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite master csv with all dates data\n",
    "csv_item = gis.content.get(mort_csv_id)\n",
    "csv_item.update({}, mort_dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 220,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get feature layer containing updated data \n",
    "mort_layer = gis.content.get(mort_layer_id)\n",
    "mort_layer\n",
    "\n",
    "mort_layer_collection = FeatureLayerCollection.fromitem(mort_layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "mort_layer_collection.manager.overwrite(mort_out)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify Mortality Map Symbology"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 Mortality by San Diego SRA\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get mortality max for graduated point symbology\n",
    "mort_max = mort_merge['mort_count'].max()\n",
    "\n",
    "#get map data\n",
    "mort_map = get_map(mort_map_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "mort_map['operationalLayers'][1]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = mort_max.item()\n",
    "mort_map['operationalLayers'][1]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = mort_max.item()\n",
    "mort_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = mort_max.item()\n",
    "mort_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = mort_max.item()\n",
    "\n",
    "#update map to save changes\n",
    "mort_map_update = update_map(mort_map_id, mort_map)\n",
    "mort_map_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 222,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "webbrowser.open(dash, new=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ArcGISPro",
   "language": "Python",
   "name": "python3"
  },
  "language_info": {
   "file_extension": ".py",
   "name": "python",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}