covid_hosp_mort_weekly.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Weekly COVID-19 Hospitalization & 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": 241,
   "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\n",
    "from copy import deepcopy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Entered Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [],
   "source": [
    "###############\n",
    "###SET DATES###\n",
    "###############\n",
    "old_date = '12/12/2020'\n",
    "new_date = '12/26/2020'\n",
    "\n",
    "##########################\n",
    "###SET PATHS IN AND OUT###\n",
    "##########################\n",
    "#PATHS IN\n",
    "#Cumulative Dates Files\n",
    "hosp_dates = './data/covid19_hospitalization.csv'\n",
    "mort_dates = './data/covid_mortality.csv'\n",
    "\n",
    "#PATHS OUT\n",
    "hosp_out = './data/covid19_hospitalization_change_upload.csv'\n",
    "mort_out = './data/covid19_mortality_change_upload.csv' \n",
    "\n",
    "hosp_cum_out = './data/covid19_hospitalization_change.csv'\n",
    "mort_cum_out = './data/covid19_mortality_change.csv'\n",
    "\n",
    "hosp_append_out = './data/covid19_hospitalization_append.csv'\n",
    "mort_append_out = './data/covid19_mortality_append.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/weekly_covid19_hospitalization_{}.csv'.format(new_date.replace('/',''))\n",
    "mort_db = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/Hosp-ICU-Mortalities/weekly_covid19_mortalities_{}.csv'.format(new_date.replace('/',''))\n",
    "\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 = '7391a05451dd46c2bca824b5462f2bb3'\n",
    "hosp_cum_layer_id = '60ff04b16e604005a2dc22eec9021892'\n",
    "hosp_map_id = '4903c250ec914dac8b35c680ddb6dd80'\n",
    "hosp_cum_csv_id = 'd2af94b59d5844e7841fbfa62f244759'\n",
    "hosp_append_csv_id = '370cd585e3504a52b64b685496368ea3'\n",
    "\n",
    "mort_layer_id = 'd9d780a386c94125b5a114170ccc6a93'\n",
    "mort_cum_layer_id = '49479d1460824824bda5ba2b3c0d0af2'\n",
    "mort_map_id = '27b40ea7731b4871a3ce8142d1367c42'\n",
    "mort_cum_csv_id = '126b5a2407324cf98cc044d71d03deaf'\n",
    "mort_append_csv_id = 'b67c5962bce943bfb9d208b403d51443'\n",
    "\n",
    "dash = 'https://experience.arcgis.com/experience/1b37559545cc47beb83f7df3a808e399/'\n",
    "dash_edit = 'https://sdsugeo.maps.arcgis.com/apps/opsdashboard/index.html#/203c186d66504b03bad8d408c345b846?mode=edit'\n",
    "dash_mobile_edit = 'https://sdsugeo.maps.arcgis.com/apps/opsdashboard/index.html#/5727cdca71d146c3a068bc29a8c2e244?mode=edit'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect to ArcGIS Account"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "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": 244,
   "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": 245,
   "metadata": {
    "scrolled": false
   },
   "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": 245,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read csv with all accumulated case numbers\n",
    "hosp = pd.read_csv(hosp_dates)\n",
    "hosp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {
    "scrolled": false
   },
   "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>12/12/2020</th>\n",
       "      <th>12/26/2020</th>\n",
       "      <th>12/12/20 - 12/26/20</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>335.2</td>\n",
       "      <td>365.5</td>\n",
       "      <td>30.3</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>26.9</td>\n",
       "      <td>30.0</td>\n",
       "      <td>3.1</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>11.0</td>\n",
       "      <td>0.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>205.7</td>\n",
       "      <td>221.1</td>\n",
       "      <td>15.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>429.6</td>\n",
       "      <td>468.0</td>\n",
       "      <td>38.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  12/26/2020  12/12/20 - 12/26/20\n",
       "0        1       Central San Diego  ...       365.5                 30.3\n",
       "1        2               Peninsula  ...        30.0                  3.1\n",
       "2        3                Coronado  ...        11.0                  0.0\n",
       "3        4           National City  ...       221.1                 15.4\n",
       "4        5  Southeastern San Diego  ...       468.0                 38.4\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 246,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calculate change for new week\n",
    "hosp_new_week = hosp[['sra_num', 'sra_name', 'latitude', 'longitude', old_date, new_date]]\n",
    "hosp_new_week['{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))] = hosp_new_week[new_date] - hosp_new_week[old_date]\n",
    "hosp_new_week.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {
    "scrolled": false
   },
   "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 - 6/10/2020</th>\n",
       "      <th>6/10/2020 - 6/17/2020</th>\n",
       "      <th>6/17/2020 - 6/24/2020</th>\n",
       "      <th>6/24/2020 - 7/1/2020</th>\n",
       "      <th>7/1/2020 - 7/8/2020</th>\n",
       "      <th>7/8/2020 - 7/14/2020</th>\n",
       "      <th>7/14/2020 - 7/28/2020</th>\n",
       "      <th>7/28/2020 - 8/4/2020</th>\n",
       "      <th>8/4/2020 - 8/11/2020</th>\n",
       "      <th>8/11/2020 - 8/18/2020</th>\n",
       "      <th>8/18/2020 - 8/24/2020</th>\n",
       "      <th>8/24/2020 - 8/29/2020</th>\n",
       "      <th>8/29/2020 - 9/5/2020</th>\n",
       "      <th>9/5/2020 - 9/19/2020</th>\n",
       "      <th>9/19/2020 - 9/26/2020</th>\n",
       "      <th>9/26/2020 - 10/3/2020</th>\n",
       "      <th>10/3/2020 - 10/10/2020</th>\n",
       "      <th>10/10/2020 - 10/17/2020</th>\n",
       "      <th>10/17/2020 - 10/24/2020</th>\n",
       "      <th>10/24/2020 - 10/31/2020</th>\n",
       "      <th>10/31/2020 - 11/7/2020</th>\n",
       "      <th>11/7/2020 - 11/14/20</th>\n",
       "      <th>11/14/20 - 11/21/20</th>\n",
       "      <th>11/21/20 - 11/28/20</th>\n",
       "      <th>11/28/20 - 12/12/20</th>\n",
       "      <th>12/13/20 - 12/26/20</th>\n",
       "      <th>12/12/20 - 12/26/20</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>7.83</td>\n",
       "      <td>7.48</td>\n",
       "      <td>5.2</td>\n",
       "      <td>12.7</td>\n",
       "      <td>11.2</td>\n",
       "      <td>11.8</td>\n",
       "      <td>25.9</td>\n",
       "      <td>12.5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.8</td>\n",
       "      <td>8.1</td>\n",
       "      <td>13.3</td>\n",
       "      <td>3.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>2.8</td>\n",
       "      <td>11.6</td>\n",
       "      <td>7.1</td>\n",
       "      <td>12.5</td>\n",
       "      <td>11.2</td>\n",
       "      <td>10.9</td>\n",
       "      <td>31.1</td>\n",
       "      <td>30.3</td>\n",
       "      <td>30.3</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.00</td>\n",
       "      <td>2.03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.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>1.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>3.1</td>\n",
       "      <td>3.1</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>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.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>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1.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>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.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>1.93</td>\n",
       "      <td>5.81</td>\n",
       "      <td>8.7</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>7.7</td>\n",
       "      <td>6.9</td>\n",
       "      <td>6.7</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>3.9</td>\n",
       "      <td>4.8</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.8</td>\n",
       "      <td>0.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>5.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>12.6</td>\n",
       "      <td>15.4</td>\n",
       "      <td>15.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>7.43</td>\n",
       "      <td>15.80</td>\n",
       "      <td>8.9</td>\n",
       "      <td>17.9</td>\n",
       "      <td>16.4</td>\n",
       "      <td>15.6</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17.8</td>\n",
       "      <td>11.8</td>\n",
       "      <td>9.6</td>\n",
       "      <td>5.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>9.8</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.9</td>\n",
       "      <td>7.5</td>\n",
       "      <td>5.9</td>\n",
       "      <td>5.4</td>\n",
       "      <td>4.4</td>\n",
       "      <td>5.6</td>\n",
       "      <td>12.6</td>\n",
       "      <td>13.7</td>\n",
       "      <td>41.0</td>\n",
       "      <td>38.4</td>\n",
       "      <td>38.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  12/13/20 - 12/26/20  12/12/20 - 12/26/20\n",
       "0        1       Central San Diego  ...                 30.3                 30.3\n",
       "1        2               Peninsula  ...                  3.1                  3.1\n",
       "2        3                Coronado  ...                  0.0                  0.0\n",
       "3        4           National City  ...                 15.4                 15.4\n",
       "4        5  Southeastern San Diego  ...                 38.4                 38.4\n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 247,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#add new week column to cumulative weekly change csv file\n",
    "hosp_new = pd.read_csv(hosp_cum_out)\n",
    "hosp_new = hosp_new.merge(hosp_new_week[['sra_num', '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))]], on='sra_num')\n",
    "hosp_new.to_csv(hosp_cum_out, index=False)\n",
    "hosp_new.to_csv(hosp_db, index=False)\n",
    "hosp_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "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>12/12/20 - 12/26/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>30.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>3.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>15.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>38.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num  12/12/20 - 12/26/20\n",
       "0        1                 30.3\n",
       "1        2                  3.1\n",
       "2        3                  0.0\n",
       "3        4                 15.4\n",
       "4        5                 38.4"
      ]
     },
     "execution_count": 248,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# format and save append csv for new column\n",
    "hosp_append = hosp_new_week[['sra_num', '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))]]\n",
    "hosp_append.to_csv(hosp_append_out, index=False)\n",
    "hosp_append.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "scrolled": false
   },
   "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>new_hosp_admissions</th>\n",
       "      <th>date_range</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>30.3</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>3.1</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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.0</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>15.4</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>38.4</td>\n",
       "      <td>12/12/2020 - 12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num  ...               date_range\n",
       "0        1  ...  12/12/2020 - 12/26/2020\n",
       "1        2  ...  12/12/2020 - 12/26/2020\n",
       "2        3  ...  12/12/2020 - 12/26/2020\n",
       "3        4  ...  12/12/2020 - 12/26/2020\n",
       "4        5  ...  12/12/2020 - 12/26/2020\n",
       "\n",
       "[5 rows x 6 columns]"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#format and save csv for layer overwrite\n",
    "hosp_new_week = hosp_new_week.rename(columns = {'{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20')):'new_hosp_admissions'})\n",
    "hosp_new_week['date_range'] = '{} - {}'.format(old_date, new_date)\n",
    "\n",
    "del hosp_new_week[old_date]\n",
    "del hosp_new_week[new_date]\n",
    "\n",
    "hosp_new_week.to_csv(hosp_out, index=False)\n",
    "hosp_new_week.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite Hospitalization Feature Layer and CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite master csv with all dates data\n",
    "csv_item = gis.content.get(hosp_cum_csv_id)\n",
    "csv_item.update({}, hosp_cum_out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite append csv with all dates data\n",
    "csv_item = gis.content.get(hosp_append_csv_id)\n",
    "csv_item.update({}, hosp_append_out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 252,
     "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": [
    "### Append new column to cumulative layer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com//home/item.html?id=60ff04b16e604005a2dc22eec9021892' target='_blank'>\n",
       "                        <img src='data:image/png;base64,R0lGODlhBQAFAID/AMDAwAAAACH5BAEAAAAALAAAAAAFAAUAQAIEhI+pWAA7' width='200' height='133' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com//home/item.html?id=60ff04b16e604005a2dc22eec9021892' target='_blank'><b>COVID19 Weekly Hospital Admits</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://www.arcgis.com//home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by jembury8568_SDSUGeo\n",
       "                        <br/>Last Modified: December 03, 2020\n",
       "                        <br/>0 comments, 404 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"COVID19 Weekly Hospital Admits\" type:Feature Layer Collection owner:jembury8568_SDSUGeo>"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get feature layer containing updated data for maps associated with the CUMULATIVE COVID-19 dashboard\n",
    "layer2 = gis.content.get(hosp_cum_layer_id)\n",
    "layer2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<FeatureLayer url:\"https://services1.arcgis.com/SIYkiqjmENweC50g/arcgis/rest/services/COVID19_Weekly_Hospital_Admits/FeatureServer/0\">"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#List all the current fields in the layer so you can use one as a field template.\n",
    "cum_hosp_lyr = layer2.layers[0]\n",
    "cum_hosp_lyr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12/12/20 - 12/26/20\n",
      "F12_12_20___12_26_20\n",
      "12_12_20___12_26_20\n"
     ]
    }
   ],
   "source": [
    "#reformat date for use in appending: append_source = column name in csv, append_field = column name in feature layer, append_alias = column alias\n",
    "append_alias = '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))\n",
    "append_field = 'F{}___{}'.format(old_date.replace('2020','20').replace('/','_'), new_date.replace('2020','20').replace('/','_'))\n",
    "append_source = '{}___{}'.format(old_date.replace('2020','20').replace('/','_'), new_date.replace('2020','20').replace('/','_'))\n",
    "\n",
    "print(append_alias)\n",
    "print(append_field)\n",
    "print(append_source)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'F12_12_20___12_26_20', 'type': 'esriFieldTypeDouble', 'actualType': 'float', 'alias': '12/12/20 - 12/26/20', 'sqlType': 'sqlTypeFloat', 'nullable': True, 'editable': True, 'domain': None, 'defaultValue': None, 'length': '10'}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 256,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create a dictionary from a deep copy of a field in the feature layer, and update the values of this dictionary to reflect a new field.\n",
    "new_field = dict(deepcopy(cum_hosp_lyr.properties.fields[5]))\n",
    "new_field['name'] = append_field\n",
    "new_field['alias'] = append_alias\n",
    "new_field['length'] = \"10\"\n",
    "print(new_field)\n",
    "\n",
    "#Update feature layer definition with the new field using the add_to_definition() method.\n",
    "field_list = [new_field]\n",
    "cum_hosp_lyr.manager.add_to_definition({\"fields\":field_list})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get *append_csv_info* when appending a new column for source_info\n",
    "append_csv_info = gis.content.analyze(item=hosp_append_csv_id, file_type='csv', location_type='none')\n",
    "#append_csv_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#append new date column to feature layer from csv item\n",
    "cum_hosp_lyr.append(item_id= hosp_append_csv_id,\n",
    "                      upload_format = 'csv',\n",
    "                      field_mappings = [{\"name\":\"{}\".format(append_field), \"source\":\"{}\".format(append_source)},\n",
    "                                        {\"name\":\"sra_num\", \"source\":\"sra_num\"}],\n",
    "                      source_info = append_csv_info['publishParameters'],\n",
    "                      update_geometry=False,\n",
    "                      append_fields=[\"{}\".format(append_field), \"sra_num\"],\n",
    "                      skip_inserts=True,\n",
    "                      upsert_matching_field=\"sra_num\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify Hospitalization Map Symbology"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39.599999999999966\n",
      "<Item title:\"New Weekly COVID-19 Hospital Admits by SD SRA\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get hospitalization max for graduated point symbology\n",
    "hosp_max = hosp_new_week['new_hosp_admissions'].max()\n",
    "print(hosp_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": 260,
   "metadata": {
    "scrolled": false
   },
   "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": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#read csv with all accumulated case numbers\n",
    "mort = pd.read_csv(mort_dates)\n",
    "mort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "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>12/12/2020</th>\n",
       "      <th>12/26/2020</th>\n",
       "      <th>12/12/20 - 12/26/20</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>76.7</td>\n",
       "      <td>87.4</td>\n",
       "      <td>10.7</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>6.1</td>\n",
       "      <td>7.2</td>\n",
       "      <td>1.1</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>5.0</td>\n",
       "      <td>0.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>59.9</td>\n",
       "      <td>71.4</td>\n",
       "      <td>11.5</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>104.9</td>\n",
       "      <td>115.8</td>\n",
       "      <td>10.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  12/26/2020  12/12/20 - 12/26/20\n",
       "0        1       Central San Diego  ...        87.4                 10.7\n",
       "1        2               Peninsula  ...         7.2                  1.1\n",
       "2        3                Coronado  ...         5.0                  0.0\n",
       "3        4           National City  ...        71.4                 11.5\n",
       "4        5  Southeastern San Diego  ...       115.8                 10.9\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 263,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calculate change for new week\n",
    "mort_new_week = mort[['sra_num', 'sra_name', 'latitude', 'longitude', old_date, new_date]]\n",
    "mort_new_week['{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))] = mort_new_week[new_date] - mort_new_week[old_date]\n",
    "mort_new_week.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {
    "scrolled": false
   },
   "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 - 6/10/2020</th>\n",
       "      <th>6/10/2020 - 6/17/2020</th>\n",
       "      <th>6/17/2020 - 6/24/2020</th>\n",
       "      <th>6/24/2020 - 7/1/2020</th>\n",
       "      <th>7/1/2020 - 7/8/2020</th>\n",
       "      <th>7/8/2020 - 7/14/2020</th>\n",
       "      <th>7/14/2020 - 7/28/2020</th>\n",
       "      <th>7/28/2020 - 8/4/2020</th>\n",
       "      <th>8/4/2020 - 8/11/2020</th>\n",
       "      <th>8/11/2020 - 8/18/2020</th>\n",
       "      <th>8/18/2020 - 8/24/2020</th>\n",
       "      <th>8/24/2020 - 8/29/2020</th>\n",
       "      <th>8/29/2020 - 9/5/2020</th>\n",
       "      <th>9/5/2020 - 9/19/2020</th>\n",
       "      <th>9/19/2020 - 9/26/2020</th>\n",
       "      <th>9/26/2020 - 10/3/2020</th>\n",
       "      <th>10/3/2020 - 10/10/2020</th>\n",
       "      <th>10/10/2020 - 10/17/2020</th>\n",
       "      <th>10/17/2020 - 10/24/2020</th>\n",
       "      <th>10/24/2020 - 10/31/2020</th>\n",
       "      <th>10/31/2020 - 11/7/2020</th>\n",
       "      <th>11/7/2020 - 11/14/20</th>\n",
       "      <th>11/14/20 - 11/21/20</th>\n",
       "      <th>11/21/20 - 11/28/20</th>\n",
       "      <th>11/28/20 - 12/12/20</th>\n",
       "      <th>12/12/20 - 12/26/20</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>1.401</td>\n",
       "      <td>2.115</td>\n",
       "      <td>2.7</td>\n",
       "      <td>1.7</td>\n",
       "      <td>2.1</td>\n",
       "      <td>3.2</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.6</td>\n",
       "      <td>6.1</td>\n",
       "      <td>0.4</td>\n",
       "      <td>1.7</td>\n",
       "      <td>1.4</td>\n",
       "      <td>3.4</td>\n",
       "      <td>2.3</td>\n",
       "      <td>4.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.2</td>\n",
       "      <td>7.4</td>\n",
       "      <td>10.7</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>5.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</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.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.1</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>5.0</td>\n",
       "      <td>0.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>1.930</td>\n",
       "      <td>0.946</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>6.8</td>\n",
       "      <td>1.9</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.9</td>\n",
       "      <td>11.5</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>0.715</td>\n",
       "      <td>1.943</td>\n",
       "      <td>0.9</td>\n",
       "      <td>4.1</td>\n",
       "      <td>7.7</td>\n",
       "      <td>9.4</td>\n",
       "      <td>21.9</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>2.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>3.9</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>2.7</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>9.1</td>\n",
       "      <td>10.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  11/28/20 - 12/12/20  12/12/20 - 12/26/20\n",
       "0        1       Central San Diego  ...                  7.4                 10.7\n",
       "1        2               Peninsula  ...                  0.0                  1.1\n",
       "2        3                Coronado  ...                  5.0                  0.0\n",
       "3        4           National City  ...                  3.9                 11.5\n",
       "4        5  Southeastern San Diego  ...                  9.1                 10.9\n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 264,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#add new week column to cumulative weekly change csv file\n",
    "mort_new = pd.read_csv(mort_cum_out)\n",
    "mort_new = mort_new.merge(mort_new_week[['sra_num', '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))]], on='sra_num')\n",
    "mort_new.to_csv(mort_cum_out, index=False)\n",
    "mort_new.to_csv(mort_db, index=False)\n",
    "mort_new.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "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>12/12/20 - 12/26/20</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>11.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>10.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num  12/12/20 - 12/26/20\n",
       "0        1                 10.7\n",
       "1        2                  1.1\n",
       "2        3                  0.0\n",
       "3        4                 11.5\n",
       "4        5                 10.9"
      ]
     },
     "execution_count": 265,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# format and save append csv for new column\n",
    "mort_append = mort_new_week[['sra_num', '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))]]\n",
    "mort_append.to_csv(mort_append_out, index=False)\n",
    "mort_append.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "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>new_mortalities</th>\n",
       "      <th>date_range</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>10.7</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>1.1</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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.0</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>11.5</td>\n",
       "      <td>12/12/2020 - 12/26/2020</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>10.9</td>\n",
       "      <td>12/12/2020 - 12/26/2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sra_num                sra_name  ...  new_mortalities               date_range\n",
       "0        1       Central San Diego  ...             10.7  12/12/2020 - 12/26/2020\n",
       "1        2               Peninsula  ...              1.1  12/12/2020 - 12/26/2020\n",
       "2        3                Coronado  ...              0.0  12/12/2020 - 12/26/2020\n",
       "3        4           National City  ...             11.5  12/12/2020 - 12/26/2020\n",
       "4        5  Southeastern San Diego  ...             10.9  12/12/2020 - 12/26/2020\n",
       "\n",
       "[5 rows x 6 columns]"
      ]
     },
     "execution_count": 267,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#format and save csv for layer overwrite\n",
    "mort_new_week = mort_new_week.rename(columns = {'{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20')):'new_mortalities'})\n",
    "mort_new_week['date_range'] = '{} - {}'.format(old_date, new_date)\n",
    "\n",
    "del mort_new_week[old_date]\n",
    "del mort_new_week[new_date]\n",
    "\n",
    "mort_new_week.to_csv(mort_out, index=False)\n",
    "mort_new_week.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Overwrite Mortality Feature Layer and CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 268,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite master csv with all dates data\n",
    "csv_item = gis.content.get(mort_cum_csv_id)\n",
    "csv_item.update({}, mort_cum_out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 269,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#overwrite append csv with all dates data\n",
    "csv_item = gis.content.get(mort_append_csv_id)\n",
    "csv_item.update({}, mort_append_out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 270,
     "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": [
    "### Append new column to cumulative layer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div class=\"item_container\" style=\"height: auto; overflow: hidden; border: 1px solid #cfcfcf; border-radius: 2px; background: #f6fafa; line-height: 1.21429em; padding: 10px;\">\n",
       "                    <div class=\"item_left\" style=\"width: 210px; float: left;\">\n",
       "                       <a href='https://www.arcgis.com//home/item.html?id=49479d1460824824bda5ba2b3c0d0af2' target='_blank'>\n",
       "                        <img src='data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMgAAACFCAYAAAAenrcsAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAJNElEQVR4Xu3d63NU9QHGcf8FO9PpTJ2pbZ2+aNGxtB1najsKOL1oESkoM5YKFRAI10JAIGgwFpBQuQQ0WChIAwqReEmVm1ImQKxEoMWCxZBNsrvZe/Z+Pbt7Nk+zWyeSX8iPTXJ292z2+czwgvMw5M1+Obuc3bO3gYgGdZt4gIi+wkCIJBgIkQQDIZJgIEQSDIRIgoEQSTAQIgkGQiTBQIgkGAiRBAMhkmAgRBIMhEiCgRRIMhqBY98WBDsN4kQ6UtKB9PT0IOFxIZVIiFPOdTfsARqXwbrgEaSUuDiTTpR0IM5DtVD2r4Bla4U45Vzg4sdom34H7K+92FuquJJelHQgxupV8FWNgXnJVHEalmTAh2TQLx6mIlbSgagxBYFLZ5Dwe8RpyOJOK0J1lfBu+B3i3S5xpiJV0oFoKWYywFnxIzjL70O8yyzOVKQYiEZ6UimEPr+IyPXL6Vf/4kxFioEQSTAQIgkGQiTBQIgkGAiRBAMhkmAgRBIMpMSpiSSCnR3iYfoSA8lC+l2/aiiAnmRSnHIi1fvzrDsrYNm1PufXHK0vL0No4Y/h/PCYOBEYSFacb72G6P4VsG5bK0454W5pgX/tWDie+g4iHSZx1lTb/AlwLroDjj2vihOBgWTFuGklfFU/gGnxFHHKGcO6Mphq14mHNZeKxxHpMoqH6UsMJAuqEof33DEkfG5x6qNGwnAd3IFQR5s4URFjIBpxpT8heGItbIsnZoKi0YGBaCRwoRntM7+V+YRg+kU9jQ4MhEiCgRBJMBAiCQZCJMFAiCQYyBDEYzEErn3O21iVEAaSJTWZhHvTPMTWPwr/hWZxRsxihGX1NLhOvitOWfF+eha+yxfEw1RgDCRLyUgU1oqJsK/4HvwfNoozHHU1UOqmwD7nwcz9tobC+9m/EV43Do7ZYxE1W8S5JIWdDsR8XvFw3jGQIUhfIY+67OLhDMXlROszD6K7+aQ43ZLi8aN93r0wL/w5kkOMazQKdbvhL38ItnnjEQ9HxTmvGIhOpC++87XN/wWvfQHL/Ltgnn4XFEdh71LJQEiXEsFg5isiCo2BEEkwECIJBkIkwUA0kr55dfoXjS4MRAOJeBy+XWvhrF0HlZGMKgxEA+7mMwhtHAfnM/cg2touznmTOYslE/z6BQ0xEA2o8STc79XCd/T13gdpYR6cqqLAt7cSvlfnI/DpOXGmYWIgo0QyHIGt4tdwV94N3/G3xZmGiYHkWbL39UqgrVU8rIlQ79/rPlGPVJ5ucFcKGEieWTcvQXTJT2A//r44kQ4xkDxrK3sIzqXfhmP3zn7He1QVgaYP4DtznHdF0REGkmfpEBLhgHgYSmcrlHerENo4CTFTpzhTgTAQnUh43DAufwDmVQ8jGfCLMxUIA9ERvuVdfxgIkQQDyYXMqYDngtGAgWgs0nkdgdrl6G58k0+XRgEGojHH61uQODAN1oW/QioaE2cqMgxEYzGrCY5DLyD0ySlxoiLEQHIh8/ojP0+wuv95Gp5/nRcPk0YYSBHzXvkPwmvuh33G3YhZbn47opHynHwLnk1zEeowiFNJYCBFTPEFYZh9D4xlP4OqJMRZE+0zxkKpfRhda1eIU0lgIBqItF6F/8RhqDq4TY3WfM0n4KqrhGK3ilNJYCAjlAwGEdw2B4n3n4O38bA491FjUdgO7UbYUZoPtGLFQEaoJ6nCsnUpjAvHIHzlojj3cTb8FdjzOLrKfoNUPDdPh0h7DEQLPT3oUeUfUvKdb0LbrG/Ase3ZrC6yu082wLR0EiI2nnEKiYHkURZd9GmfdR/iNeNhqlojToOKR8IIXrs66M+51U4DMRCd8pw7hS/++BgSSnZX49VEAt6X5iLx8jT4W86I8y13ujkGMkqokQhsz0+GY9UYBG7y/SW32unmGMgIBDvaMv8zpSbi4iSV/rYqa/1exAb5rpFhS6WQiEUGfyfxrXYagIEMUzIchqdyMhI7JsN7tKHflr7Tomf3C3D8ZT1S6sA7LZo2LQdemQTjoqniRDrDQIYp/cA3rZ+D9qe/ifCVS/02d/NZBP90PxyzxyB6feBbNBwfNMDw5O2w7dkgToNSe1+LWA/uQthhEyfKIQaSA2oiBecb1fA0vKLZsxlX33WUR3gdJY8YSJHIXEeZ+XXYt2Z3HYW0wUB0JPTfy/DsKIe36ag4UYEwEB3pWD0T6t9+C+Pvx4sTFQgDybP0XRN9Rw/A/coKxCymflu4rRX23eUIXuKFPL1gIBoItV2DdeVUuJuOidMACb8f7hcfRaR2InxH6sR5gHRQvBVp4TAQDXSueRrJ+hno/MMEcRog/Vj3nKyHfd9KxG+4UBgzt8P/0TtI3vDWkojdBs/G6bCufqI3rGDfccofBqKBmMuF68umIHB14Nvdo12dsK+eAuff68Wpn67yiUgdnA3zS5V9xwJXrsG24E5Yn7wTMZvzhj9N+cJAcsx5oAbKkZmwzZsANaaIcx/zlnKYF42B/+zpfse7m07B+1lLv2OUPwwkxxSnA4byKXC3NIlTP+m7vqdiUfFw0YtYzeiq31e0b7FnIJRTxkW/QLLqpzDX/FmcigID0QG1959Xe/USWLZWFO2/tIMxbl4Gw7SvwX/+Y3EqCgxEBzznP0Fw/QNwzvo+Iu2d4jwsUZMBnp3L4D5+RJxoCBhInkUsRnQd3jvgTGHcXgnLGzXC0eGz79+C+L7HYC37Je8RPAIMJMe8H70NZ3UZwsaOzO+/ek6+WfiT2kpfpbfuWozAWb6vayQYSI4ZnvohlB0T0PXcyszvjZuXw/D47fC3FOdz8lLDQHLM/Y9G2LaXQRniDeO8p96Bc/OCvjMPFQYD0SnDjHuh1IxH1/PPihPlEQMpEE/6DFE9+BnCfboR1m3ze888/IhtITGQAsm8Ntk+jmcInWMgBZJ+bWLZMhcxniF0jYEQSTAQIgkGojPJ6Oh7R28xYyA64mqsg3/DE7DurBInKhAGoiPG6tVwV3wXpqW8JaleMBAdSX9e3fHem4gH/eJEBcJAiCQYCJEEAyGSYCBEEgyESIKBEEkwECIJBkIkwUCIJBgIkQQDIZJgIEQSDIRIgoEQSTAQIgkGQiTBQIgkGAiRBAMhkmAgRBIMhEiCgRBJMBAiCQZCJMFAiCQYCJEEAyGS+B+fmuCSaeY1cwAAAABJRU5ErkJggg==' width='200' height='133' class=\"itemThumbnail\">\n",
       "                       </a>\n",
       "                    </div>\n",
       "\n",
       "                    <div class=\"item_right\"     style=\"float: none; width: auto; overflow: hidden;\">\n",
       "                        <a href='https://www.arcgis.com//home/item.html?id=49479d1460824824bda5ba2b3c0d0af2' target='_blank'><b>COVID19 Weekly Mortalities</b>\n",
       "                        </a>\n",
       "                        <br/><img src='https://www.arcgis.com//home/js/jsapi/esri/css/images/item_type_icons/featureshosted16.png' style=\"vertical-align:middle;\">Feature Layer Collection by jembury8568_SDSUGeo\n",
       "                        <br/>Last Modified: November 20, 2020\n",
       "                        <br/>0 comments, 357 views\n",
       "                    </div>\n",
       "                </div>\n",
       "                "
      ],
      "text/plain": [
       "<Item title:\"COVID19 Weekly Mortalities\" type:Feature Layer Collection owner:jembury8568_SDSUGeo>"
      ]
     },
     "execution_count": 271,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get feature layer containing updated data for maps associated with the CUMULATIVE COVID-19 dashboard\n",
    "layer2 = gis.content.get(mort_cum_layer_id)\n",
    "layer2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<FeatureLayer url:\"https://services1.arcgis.com/SIYkiqjmENweC50g/arcgis/rest/services/COVID19_Weekly_Mortalities/FeatureServer/0\">"
      ]
     },
     "execution_count": 272,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#List all the current fields in the layer so you can use one as a field template.\n",
    "cum_mort_lyr = layer2.layers[0]\n",
    "cum_mort_lyr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12/12/20 - 12/26/20\n",
      "F12_12_20___12_26_20\n",
      "12_12_20___12_26_20\n"
     ]
    }
   ],
   "source": [
    "#reformat date for use in appending: append_source = column name in csv, append_field = column name in feature layer, append_alias = column alias\n",
    "append_alias = '{} - {}'.format(old_date.replace('2020','20'), new_date.replace('2020','20'))\n",
    "append_field = 'F{}___{}'.format(old_date.replace('2020','20').replace('/','_'), new_date.replace('2020','20').replace('/','_'))\n",
    "append_source = '{}___{}'.format(old_date.replace('2020','20').replace('/','_'), new_date.replace('2020','20').replace('/','_'))\n",
    "\n",
    "print(append_alias)\n",
    "print(append_field)\n",
    "print(append_source)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'name': 'F12_12_20___12_26_20', 'type': 'esriFieldTypeDouble', 'actualType': 'float', 'alias': '12/12/20 - 12/26/20', 'sqlType': 'sqlTypeFloat', 'nullable': True, 'editable': True, 'domain': None, 'defaultValue': None, 'length': '10'}\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create a dictionary from a deep copy of a field in the feature layer, and update the values of this dictionary to reflect a new field.\n",
    "new_field = dict(deepcopy(cum_mort_lyr.properties.fields[5]))\n",
    "new_field['name'] = append_field\n",
    "new_field['alias'] = append_alias\n",
    "new_field['length'] = \"10\"\n",
    "print(new_field)\n",
    "\n",
    "#Update feature layer definition with the new field using the add_to_definition() method.\n",
    "field_list = [new_field]\n",
    "cum_mort_lyr.manager.add_to_definition({\"fields\":field_list})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get *append_csv_info* when appending a new column for source_info\n",
    "append_csv_info = gis.content.analyze(item=mort_append_csv_id, file_type='csv', location_type='none')\n",
    "#append_csv_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#append new date column to feature layer from csv item\n",
    "cum_mort_lyr.append(item_id= mort_append_csv_id,\n",
    "                      upload_format = 'csv',\n",
    "                      field_mappings = [{\"name\":\"{}\".format(append_field), \"source\":\"{}\".format(append_source)},\n",
    "                                        {\"name\":\"sra_num\", \"source\":\"sra_num\"}],\n",
    "                      source_info = append_csv_info['publishParameters'],\n",
    "                      update_geometry=False,\n",
    "                      append_fields=[\"{}\".format(append_field), \"sra_num\"],\n",
    "                      skip_inserts=True,\n",
    "                      upsert_matching_field=\"sra_num\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modify Mortality Map Symbology"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19.200000000000003\n",
      "<Item title:\"New Weekly COVID-19 Mortalities by SD SRA\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 277,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get mortitalization max for graduated point symbology\n",
    "mort_max = mort_new_week['new_mortalities'].max()\n",
    "print(mort_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": "markdown",
   "metadata": {},
   "source": [
    "# DASHBOARDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 278,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "webbrowser.open(dash, new=2)\n",
    "webbrowser.open(dash_edit, new=2)\n",
    "webbrowser.open(dash_mobile_edit, 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
}