test_results_dash.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 TEST RESULTS DASHBOARD UPDATES\n",
    "### <a href=\"https://hdma-sdsu.github.io/index.html\">COVID-19 Research Hub</a>\n",
    "Center for Human Dynamics in the Mobile Age (HDMA) at SDSU <br>\n",
    "Jessica Embury"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### IMPORT STATEMENTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from arcgis.gis import GIS\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "import json\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "import sys\n",
    "import tabula\n",
    "import webbrowser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### VARIABLES: DATES, PATHS, AGOL IDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "######################\n",
    "# USER ENTERED DATES #\n",
    "######################\n",
    "most_recent_date = '12/16/20'  # file folder date\n",
    "data_date = '12/12/20' # date through\n",
    "prior_date = '12/05/20'  # date through of prior week\n",
    "day_after_prior = '12/6/20'  # one date after the prior date\n",
    "\n",
    "#########\n",
    "# PATHS #\n",
    "#########\n",
    "# pdf file path\n",
    "file = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/{}20/Summary_of_All_Tests_Reported_by_Zip_Code_of_Residence.pdf'.format(most_recent_date.replace('/', '-'))\n",
    "\n",
    "# csv files\n",
    "new_csv = '../covid_data/tests/covid_tests_total_{}.csv'.format(data_date.replace('/',''))  # this week's total results\n",
    "change_csv = '../covid_data/tests/covid_tests_change_{}.csv'.format(data_date.replace('/',''))  # this week's new results\n",
    "prior_csv = '../covid_data/tests/covid_tests_total_{}.csv'.format(prior_date.replace('/',''))  # last week's total results\n",
    "zips_csv = './data/zip_coords_pop.csv'  # zip code spatial data\n",
    "total_upload = './data/sd_zips_covid_test_results.csv'  # csv for upload to AGOL\n",
    "change_upload = './data/sd_zips_covid_tests_new.csv'  # csv for upload to AGOL\n",
    "\n",
    "############\n",
    "# AGOL IDs #\n",
    "############\n",
    "total_layer_id = '8ae9d6bc96ff4ac7b6240696c77fc4b6'\n",
    "weekly_layer_id = '812242e64749440baf7e1281533f464a'\n",
    "\n",
    "total_map_id = 'f218f594126140ed939d2b5e7baa8baa'\n",
    "weekly_map_id = 'ad5c0361faa44573adcb9c5b3a8dedb0'\n",
    "hpi_map_id = 'd1d1cb9a1fa14f66800e4497ac07cd02'\n",
    "\n",
    "total_dash = 'https://arcg.is/11nm9S'\n",
    "weekly_dash = 'https://arcg.is/1qmieu'\n",
    "hpi_dash = 'https://experience.arcgis.com/experience/55f0ed2ab7394decae0de7b86a12d26f'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CUMULATIVE TEST RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### USE TABULA TO CONVERT PDF TO TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Percent</td>\n",
       "      <td>Tests per</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Percent</td>\n",
       "      <td>Tests per</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zip Code</td>\n",
       "      <td>Positive</td>\n",
       "      <td>Negative Indeterminate</td>\n",
       "      <td>Total</td>\n",
       "      <td>Postive</td>\n",
       "      <td>100,000*</td>\n",
       "      <td>Zip Code</td>\n",
       "      <td>Positive Negative Indeterminate</td>\n",
       "      <td>Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Positive</td>\n",
       "      <td>100,000*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91901</td>\n",
       "      <td>591</td>\n",
       "      <td>8,780 41</td>\n",
       "      <td>9,412</td>\n",
       "      <td>6.3%</td>\n",
       "      <td>52,330</td>\n",
       "      <td>92071</td>\n",
       "      <td>1,520 35,881</td>\n",
       "      <td>181 37,582</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0%</td>\n",
       "      <td>64,559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91902</td>\n",
       "      <td>693</td>\n",
       "      <td>10,450 70</td>\n",
       "      <td>11,213</td>\n",
       "      <td>6.2%</td>\n",
       "      <td>63,598</td>\n",
       "      <td>92075</td>\n",
       "      <td>177 7,964</td>\n",
       "      <td>30 8,171</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.2%</td>\n",
       "      <td>62,965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>20</td>\n",
       "      <td>646 1</td>\n",
       "      <td>667</td>\n",
       "      <td>3.0%</td>\n",
       "      <td>*</td>\n",
       "      <td>92078</td>\n",
       "      <td>1,264 24,468</td>\n",
       "      <td>143 25,875</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.9%</td>\n",
       "      <td>50,763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>92065</td>\n",
       "      <td>1,114</td>\n",
       "      <td>14,136 77</td>\n",
       "      <td>15,327</td>\n",
       "      <td>7.3%</td>\n",
       "      <td>42,274</td>\n",
       "      <td>Subtotal</td>\n",
       "      <td>117,654 1,914,135</td>\n",
       "      <td>10,317 2,042,106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.8%</td>\n",
       "      <td>60,926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>92066</td>\n",
       "      <td>12</td>\n",
       "      <td>93 0</td>\n",
       "      <td>105</td>\n",
       "      <td>11.4%</td>\n",
       "      <td>*</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>92067</td>\n",
       "      <td>169</td>\n",
       "      <td>6,624 8</td>\n",
       "      <td>6,801</td>\n",
       "      <td>2.5%</td>\n",
       "      <td>*</td>\n",
       "      <td>**Other</td>\n",
       "      <td>6,022 97,411</td>\n",
       "      <td>248 103,681</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.8%</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>92069</td>\n",
       "      <td>1,806</td>\n",
       "      <td>20,643 189</td>\n",
       "      <td>22,638</td>\n",
       "      <td>8.0%</td>\n",
       "      <td>44,240</td>\n",
       "      <td>***Unknown</td>\n",
       "      <td>5,349 118,958</td>\n",
       "      <td>341 124,648</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.3%</td>\n",
       "      <td>*</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>92070</td>\n",
       "      <td>24</td>\n",
       "      <td>639 1</td>\n",
       "      <td>664</td>\n",
       "      <td>3.6%</td>\n",
       "      <td>*</td>\n",
       "      <td>Total</td>\n",
       "      <td>129,025 2,130,504</td>\n",
       "      <td>10,906 2,270,435</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.7%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1                       2   ...  9         10         11\n",
       "0        NaN       NaN                     NaN  ... NaN   Percent  Tests per\n",
       "1   Zip Code  Positive  Negative Indeterminate  ... NaN  Positive   100,000*\n",
       "2      91901       591                8,780 41  ... NaN      4.0%     64,559\n",
       "3      91902       693               10,450 70  ... NaN      2.2%     62,965\n",
       "4      91905        20                   646 1  ... NaN      4.9%     50,763\n",
       "..       ...       ...                     ...  ...  ..       ...        ...\n",
       "56     92065     1,114               14,136 77  ... NaN      5.8%     60,926\n",
       "57     92066        12                    93 0  ... NaN       NaN        NaN\n",
       "58     92067       169                 6,624 8  ... NaN      5.8%          *\n",
       "59     92069     1,806              20,643 189  ... NaN      4.3%          *\n",
       "60     92070        24                   639 1  ... NaN      5.7%        NaN\n",
       "\n",
       "[61 rows x 12 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = tabula.read_pdf(file, pages = \"all\", multiple_tables = True)\n",
    "tables[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CREATE TABLE WITH LEFT COLUMN ZIPS, CLEAN DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "59\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>Zip</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Tests Per 100K</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91901</td>\n",
       "      <td>591</td>\n",
       "      <td>9,412</td>\n",
       "      <td>6.3%</td>\n",
       "      <td>52,330</td>\n",
       "      <td>8,780</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91902</td>\n",
       "      <td>693</td>\n",
       "      <td>11,213</td>\n",
       "      <td>6.2%</td>\n",
       "      <td>63,598</td>\n",
       "      <td>10,450</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>20</td>\n",
       "      <td>667</td>\n",
       "      <td>3.0%</td>\n",
       "      <td>*</td>\n",
       "      <td>646</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>91906</td>\n",
       "      <td>121</td>\n",
       "      <td>2,219</td>\n",
       "      <td>5.5%</td>\n",
       "      <td>*</td>\n",
       "      <td>2,076</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>91910</td>\n",
       "      <td>4,749</td>\n",
       "      <td>59,574</td>\n",
       "      <td>8.0%</td>\n",
       "      <td>71,062</td>\n",
       "      <td>54,566</td>\n",
       "      <td>259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip Positive   Total  ... Tests Per 100K Negative Indeterminate\n",
       "2  91901      591   9,412  ...         52,330    8,780            41\n",
       "3  91902      693  11,213  ...         63,598   10,450            70\n",
       "4  91905       20     667  ...              *      646             1\n",
       "5  91906      121   2,219  ...              *    2,076            22\n",
       "6  91910    4,749  59,574  ...         71,062   54,566           259\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename columns\n",
    "t1 = tables[0][[0, 1, 2, 3, 4, 5]]\n",
    "t1 = t1.rename(columns = {0: 'Zip', 1: 'Positive', 2: 'Neg Indet', 3: 'Total', \n",
    "                         4: 'Percent Positive', 5: 'Tests Per 100K'})\n",
    "\n",
    "# drop bad rows\n",
    "t1 = t1.drop(t1.index[0:2])\n",
    "\n",
    "# create new columns for clean data\n",
    "t1['Negative'] = 'NaN'\n",
    "t1['Indeterminate'] = 'NaN'\n",
    "\n",
    "# clean data\n",
    "for i, row in t1.iterrows():\n",
    "    temp = t1['Neg Indet'][i]\n",
    "    temp_list = temp.split( )\n",
    "    t1['Negative'][i] = temp_list[0]\n",
    "    t1['Indeterminate'][i] = temp_list[1]\n",
    "    if len(temp_list) == 3:\n",
    "        t1['Total'][i] = temp_list[2]\n",
    "\n",
    "# delete bad columns\n",
    "del t1['Neg Indet']\n",
    "\n",
    "print(len(t1))\n",
    "t1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CREATE TABLE WITH RIGHT COLUMN ZIPS, CLEAN DATA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "58\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>Zip</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Tests Per 100K</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>92071</td>\n",
       "      <td>1,520</td>\n",
       "      <td>37,582</td>\n",
       "      <td>4.0%</td>\n",
       "      <td>64,559</td>\n",
       "      <td>35,881</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92075</td>\n",
       "      <td>177</td>\n",
       "      <td>8,171</td>\n",
       "      <td>2.2%</td>\n",
       "      <td>62,965</td>\n",
       "      <td>7,964</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>92078</td>\n",
       "      <td>1,264</td>\n",
       "      <td>25,875</td>\n",
       "      <td>4.9%</td>\n",
       "      <td>50,763</td>\n",
       "      <td>24,468</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>92081</td>\n",
       "      <td>716</td>\n",
       "      <td>14,480</td>\n",
       "      <td>4.9%</td>\n",
       "      <td>44,024</td>\n",
       "      <td>13,643</td>\n",
       "      <td>121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>92082</td>\n",
       "      <td>479</td>\n",
       "      <td>8,675</td>\n",
       "      <td>5.5%</td>\n",
       "      <td>49,070</td>\n",
       "      <td>8,155</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip Positive   Total  ... Tests Per 100K Negative Indeterminate\n",
       "2  92071    1,520  37,582  ...         64,559   35,881           181\n",
       "3  92075      177   8,171  ...         62,965    7,964            30\n",
       "4  92078    1,264  25,875  ...         50,763   24,468           143\n",
       "5  92081      716  14,480  ...         44,024   13,643           121\n",
       "6  92082      479   8,675  ...         49,070    8,155            41\n",
       "\n",
       "[5 rows x 7 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# rename columns\n",
    "t2 = tables[0][[6, 7, 8, 9, 10, 11]]\n",
    "t2 = t2.rename(columns = {6: 'Zip', 7: 'Pos Neg', 8: 'Indet Total', 9: 'Total', \n",
    "                         10: 'Percent Positive', 11: 'Tests Per 100K'})\n",
    "\n",
    "# delete bad rows\n",
    "t2 = t2.drop(t2.index[0:2])\n",
    "t2 = t2.drop(t2.index[55])\n",
    "\n",
    "# create new columns for clean data\n",
    "t2['Negative'] = 'NaN'\n",
    "t2['Indeterminate'] = 'NaN'\n",
    "\n",
    "# clean data\n",
    "for i, row in t2.iterrows():\n",
    "    temp = t2['Indet Total'][i]\n",
    "    temp_list = str(temp).split( )\n",
    "    t2['Indeterminate'][i] = temp_list[0]\n",
    "    t2['Total'][i] = temp_list[1]\n",
    "    \n",
    "    temp2 = t2['Pos Neg'][i]\n",
    "    temp_list2 = temp2.split( )\n",
    "    t2['Pos Neg'][i] = temp_list2[0]\n",
    "    t2['Negative'][i] = temp_list2[1]\n",
    "\n",
    "t2 = t2.rename(columns = {'Pos Neg':'Positive'})\n",
    "\n",
    "# delete bad columns\n",
    "del t2['Indet Total']\n",
    "\n",
    "print(len(t2))\n",
    "t2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### MERGE INTO ONE DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "117\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>Zip</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Tests Per 100K</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>591</td>\n",
       "      <td>8780</td>\n",
       "      <td>41</td>\n",
       "      <td>9412</td>\n",
       "      <td>6.3</td>\n",
       "      <td>52,330</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>693</td>\n",
       "      <td>10450</td>\n",
       "      <td>70</td>\n",
       "      <td>11213</td>\n",
       "      <td>6.2</td>\n",
       "      <td>63,598</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>20</td>\n",
       "      <td>646</td>\n",
       "      <td>1</td>\n",
       "      <td>667</td>\n",
       "      <td>3</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>121</td>\n",
       "      <td>2076</td>\n",
       "      <td>22</td>\n",
       "      <td>2219</td>\n",
       "      <td>5.5</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>4749</td>\n",
       "      <td>54566</td>\n",
       "      <td>259</td>\n",
       "      <td>59574</td>\n",
       "      <td>8</td>\n",
       "      <td>71,062</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip Positive Negative  ... Percent Positive Tests Per 100K      Date\n",
       "0  91901      591     8780  ...              6.3         52,330  12/12/20\n",
       "1  91902      693    10450  ...              6.2         63,598  12/12/20\n",
       "2  91905       20      646  ...                3              *  12/12/20\n",
       "3  91906      121     2076  ...              5.5              *  12/12/20\n",
       "4  91910     4749    54566  ...                8         71,062  12/12/20\n",
       "\n",
       "[5 rows x 8 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge t1 and t2\n",
    "t1 = t1.append(t2, ignore_index=True)\n",
    "\n",
    "# new dataframe with properly ordered columns\n",
    "df = t1[['Zip', 'Positive', 'Negative', 'Indeterminate', 'Total', 'Percent Positive', 'Tests Per 100K']]\n",
    "df = df.rename(columns = {'ZIP':'Zip'})\n",
    "\n",
    "# format data\n",
    "for i,row in df.iterrows():\n",
    "    df['Positive'][i] = int(df['Positive'][i].replace(',',''))\n",
    "    df['Negative'][i] = int(df['Negative'][i].replace(',',''))\n",
    "    df['Indeterminate'][i] = int(df['Indeterminate'][i].replace(',',''))   \n",
    "    df['Total'][i] = int(df['Total'][i].replace(',',''))\n",
    "    df['Percent Positive'][i] = round((df['Positive'][i]/df['Total'][i]*100), 1)\n",
    "\n",
    "# add date column\n",
    "df['Date'] = data_date\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### ADD COMMUNITY NAMES AND COORDINATES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>2018_population</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Tests Per 100K</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>591</td>\n",
       "      <td>8780</td>\n",
       "      <td>41</td>\n",
       "      <td>9412</td>\n",
       "      <td>6.3</td>\n",
       "      <td>52,330</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>693</td>\n",
       "      <td>10450</td>\n",
       "      <td>70</td>\n",
       "      <td>11213</td>\n",
       "      <td>6.2</td>\n",
       "      <td>63,598</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>20</td>\n",
       "      <td>646</td>\n",
       "      <td>1</td>\n",
       "      <td>667</td>\n",
       "      <td>3</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>Campo</td>\n",
       "      <td>3686</td>\n",
       "      <td>32.660427</td>\n",
       "      <td>-116.469681</td>\n",
       "      <td>121</td>\n",
       "      <td>2076</td>\n",
       "      <td>22</td>\n",
       "      <td>2219</td>\n",
       "      <td>5.5</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>82682</td>\n",
       "      <td>32.636413</td>\n",
       "      <td>-117.065653</td>\n",
       "      <td>4749</td>\n",
       "      <td>54566</td>\n",
       "      <td>259</td>\n",
       "      <td>59574</td>\n",
       "      <td>8</td>\n",
       "      <td>71,062</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip    Community  ...  Tests Per 100K      Date\n",
       "0  91901       Alpine  ...          52,330  12/12/20\n",
       "1  91902       Bonita  ...          63,598  12/12/20\n",
       "2  91905    Boulevard  ...               *  12/12/20\n",
       "3  91906        Campo  ...               *  12/12/20\n",
       "4  91910  Chula Vista  ...          71,062  12/12/20\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dataframe with zip code spatial data\n",
    "zips = pd.read_csv(zips_csv)\n",
    "zips['Zip'] = zips['Zip'].astype(str)\n",
    "\n",
    "# merge test data with zip code data\n",
    "zips = zips.merge(df, on='Zip')\n",
    "zips.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CALCULATE POSITIVE/100K"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>Population 2018</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Tests Per 100K</th>\n",
       "      <th>Date</th>\n",
       "      <th>Positive Cases Per 100K</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>591</td>\n",
       "      <td>8780</td>\n",
       "      <td>41</td>\n",
       "      <td>9412</td>\n",
       "      <td>6.3</td>\n",
       "      <td>52,330</td>\n",
       "      <td>12/12/20</td>\n",
       "      <td>3304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>693</td>\n",
       "      <td>10450</td>\n",
       "      <td>70</td>\n",
       "      <td>11213</td>\n",
       "      <td>6.2</td>\n",
       "      <td>63,598</td>\n",
       "      <td>12/12/20</td>\n",
       "      <td>3988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>20</td>\n",
       "      <td>646</td>\n",
       "      <td>1</td>\n",
       "      <td>667</td>\n",
       "      <td>3</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "      <td>993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>Campo</td>\n",
       "      <td>3686</td>\n",
       "      <td>32.660427</td>\n",
       "      <td>-116.469681</td>\n",
       "      <td>121</td>\n",
       "      <td>2076</td>\n",
       "      <td>22</td>\n",
       "      <td>2219</td>\n",
       "      <td>5.5</td>\n",
       "      <td>*</td>\n",
       "      <td>12/12/20</td>\n",
       "      <td>3283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>82682</td>\n",
       "      <td>32.636413</td>\n",
       "      <td>-117.065653</td>\n",
       "      <td>4749</td>\n",
       "      <td>54566</td>\n",
       "      <td>259</td>\n",
       "      <td>59574</td>\n",
       "      <td>8</td>\n",
       "      <td>71,062</td>\n",
       "      <td>12/12/20</td>\n",
       "      <td>5744</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip    Community  ...      Date  Positive Cases Per 100K\n",
       "0  91901       Alpine  ...  12/12/20                     3304\n",
       "1  91902       Bonita  ...  12/12/20                     3988\n",
       "2  91905    Boulevard  ...  12/12/20                      993\n",
       "3  91906        Campo  ...  12/12/20                     3283\n",
       "4  91910  Chula Vista  ...  12/12/20                     5744\n",
       "\n",
       "[5 rows x 13 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate positive per 100k\n",
    "zips = zips.rename(columns={'2018_population':'Population 2018'})\n",
    "zips['Positive Cases Per 100K'] = ''\n",
    "for i, row in zips.iterrows():\n",
    "    if zips['Population 2018'][i] >= 1000:\n",
    "        zips['Positive Cases Per 100K'][i] = round(zips['Positive'][i]/zips['Population 2018'][i]*100000, 0)\n",
    "    else:\n",
    "        zips['Positive Cases Per 100K'][i] = '*'\n",
    "\n",
    "# save as csv file\n",
    "zips.to_csv(new_csv, index=False)\n",
    "zips.to_csv(total_upload, index=False)  # for AGOL upload\n",
    "zips.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WEEKLY TEST RESULTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### COMBINE CUMULATIVE TEST DATA (MOST RECENT WEEK AND PRIOR WEEK)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>Population 2018</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>old pos</th>\n",
       "      <th>old neg</th>\n",
       "      <th>old indet</th>\n",
       "      <th>old total</th>\n",
       "      <th>old date</th>\n",
       "      <th>new pos</th>\n",
       "      <th>new neg</th>\n",
       "      <th>new indet</th>\n",
       "      <th>new total</th>\n",
       "      <th>new date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>480</td>\n",
       "      <td>8198</td>\n",
       "      <td>33</td>\n",
       "      <td>8711</td>\n",
       "      <td>12/05/20</td>\n",
       "      <td>591</td>\n",
       "      <td>8780</td>\n",
       "      <td>41</td>\n",
       "      <td>9412</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>601</td>\n",
       "      <td>9722</td>\n",
       "      <td>64</td>\n",
       "      <td>10387</td>\n",
       "      <td>12/05/20</td>\n",
       "      <td>693</td>\n",
       "      <td>10450</td>\n",
       "      <td>70</td>\n",
       "      <td>11213</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>14</td>\n",
       "      <td>613</td>\n",
       "      <td>1</td>\n",
       "      <td>628</td>\n",
       "      <td>12/05/20</td>\n",
       "      <td>20</td>\n",
       "      <td>646</td>\n",
       "      <td>1</td>\n",
       "      <td>667</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>Campo</td>\n",
       "      <td>3686</td>\n",
       "      <td>32.660427</td>\n",
       "      <td>-116.469681</td>\n",
       "      <td>103</td>\n",
       "      <td>1981</td>\n",
       "      <td>21</td>\n",
       "      <td>2105</td>\n",
       "      <td>12/05/20</td>\n",
       "      <td>121</td>\n",
       "      <td>2076</td>\n",
       "      <td>22</td>\n",
       "      <td>2219</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>82682</td>\n",
       "      <td>32.636413</td>\n",
       "      <td>-117.065653</td>\n",
       "      <td>4129</td>\n",
       "      <td>50816</td>\n",
       "      <td>250</td>\n",
       "      <td>55195</td>\n",
       "      <td>12/05/20</td>\n",
       "      <td>4749</td>\n",
       "      <td>54566</td>\n",
       "      <td>259</td>\n",
       "      <td>59574</td>\n",
       "      <td>12/12/20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip    Community  Population 2018  ...  new indet  new total  new date\n",
       "0  91901       Alpine            17885  ...         41       9412  12/12/20\n",
       "1  91902       Bonita            17375  ...         70      11213  12/12/20\n",
       "2  91905    Boulevard             2014  ...          1        667  12/12/20\n",
       "3  91906        Campo             3686  ...         22       2219  12/12/20\n",
       "4  91910  Chula Vista            82682  ...        259      59574  12/12/20\n",
       "\n",
       "[5 rows x 15 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dataframe with culumative results from prior week\n",
    "prior = pd.read_csv(prior_csv)\n",
    "\n",
    "prior = prior.rename(columns = {'Positive':'old pos', 'Negative':'old neg', 'Indeterminate':'old indet', \n",
    "                                'Total':'old total', 'Date':'old date'    \n",
    "})\n",
    "\n",
    "# create dataframe with cumulative results from this week\n",
    "new = pd.read_csv(new_csv)\n",
    "new = new.rename(columns = {'Positive':'new pos', 'Negative':'new neg', 'Indeterminate':'new indet', \n",
    "                                'Total':'new total', 'Date':'new date'    \n",
    "})\n",
    "\n",
    "# combine into one dataframe for weekly values\n",
    "change = prior[['Zip','Community','Population 2018','Latitude','Longitude','old pos','old neg', 'old indet', 'old total', 'old date']].merge(\n",
    "    new[['Zip', 'new pos', 'new neg', 'new indet', 'new total', 'new date']], on='Zip')\n",
    "\n",
    "change.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CALCULATE NEW WEEKLY VALUES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>Population 2018</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Date Range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>111</td>\n",
       "      <td>582</td>\n",
       "      <td>8</td>\n",
       "      <td>701</td>\n",
       "      <td>15.8</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>92</td>\n",
       "      <td>728</td>\n",
       "      <td>6</td>\n",
       "      <td>826</td>\n",
       "      <td>11.1</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>6</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>15.4</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>Campo</td>\n",
       "      <td>3686</td>\n",
       "      <td>32.660427</td>\n",
       "      <td>-116.469681</td>\n",
       "      <td>18</td>\n",
       "      <td>95</td>\n",
       "      <td>1</td>\n",
       "      <td>114</td>\n",
       "      <td>15.8</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>82682</td>\n",
       "      <td>32.636413</td>\n",
       "      <td>-117.065653</td>\n",
       "      <td>620</td>\n",
       "      <td>3750</td>\n",
       "      <td>9</td>\n",
       "      <td>4379</td>\n",
       "      <td>14.2</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip    Community  ...  Percent Positive          Date Range\n",
       "0  91901       Alpine  ...              15.8  12/6/20 - 12/12/20\n",
       "1  91902       Bonita  ...              11.1  12/6/20 - 12/12/20\n",
       "2  91905    Boulevard  ...              15.4  12/6/20 - 12/12/20\n",
       "3  91906        Campo  ...              15.8  12/6/20 - 12/12/20\n",
       "4  91910  Chula Vista  ...              14.2  12/6/20 - 12/12/20\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add columns for weekly values\n",
    "change['Positive'] = 0\n",
    "change['Negative'] = 0\n",
    "change['Indeterminate'] = 0\n",
    "change['Total'] = 0\n",
    "change['Percent Positive'] = 0\n",
    "change['Date Range'] = '{} - {}'.format(day_after_prior, data_date)\n",
    "\n",
    "# calculate weekly values\n",
    "for i, row in change.iterrows():\n",
    "    change['Positive'][i] = change['new pos'][i] - change['old pos'][i]\n",
    "    change['Negative'][i] = change['new neg'][i] - change['old neg'][i]\n",
    "    change['Indeterminate'][i] = change['new indet'][i] - change['old indet'][i]\n",
    "    change['Total'][i] = change['new total'][i] - change['old total'][i]\n",
    "    change['Percent Positive'] = round(change['Positive']/change['Total']*100, 1)\n",
    "\n",
    "# delete old/new cumulative values\n",
    "del change['old pos']\n",
    "del change['old neg']\n",
    "del change['old indet']\n",
    "del change['old total']\n",
    "del change['old date']\n",
    "del change['new pos']\n",
    "del change['new neg']\n",
    "del change['new indet']\n",
    "del change['new total']\n",
    "del change['new date']\n",
    "\n",
    "change.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CALCULATE POSITIVE/100K"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>Population 2018</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Positive</th>\n",
       "      <th>Negative</th>\n",
       "      <th>Indeterminate</th>\n",
       "      <th>Total</th>\n",
       "      <th>Percent Positive</th>\n",
       "      <th>Date Range</th>\n",
       "      <th>Positive Cases Per 100K</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>111</td>\n",
       "      <td>582</td>\n",
       "      <td>8</td>\n",
       "      <td>701</td>\n",
       "      <td>15.8</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "      <td>621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>92</td>\n",
       "      <td>728</td>\n",
       "      <td>6</td>\n",
       "      <td>826</td>\n",
       "      <td>11.1</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "      <td>529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>6</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>39</td>\n",
       "      <td>15.4</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91906</td>\n",
       "      <td>Campo</td>\n",
       "      <td>3686</td>\n",
       "      <td>32.660427</td>\n",
       "      <td>-116.469681</td>\n",
       "      <td>18</td>\n",
       "      <td>95</td>\n",
       "      <td>1</td>\n",
       "      <td>114</td>\n",
       "      <td>15.8</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "      <td>488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91910</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>82682</td>\n",
       "      <td>32.636413</td>\n",
       "      <td>-117.065653</td>\n",
       "      <td>620</td>\n",
       "      <td>3750</td>\n",
       "      <td>9</td>\n",
       "      <td>4379</td>\n",
       "      <td>14.2</td>\n",
       "      <td>12/6/20 - 12/12/20</td>\n",
       "      <td>750</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip    Community  ...          Date Range  Positive Cases Per 100K\n",
       "0  91901       Alpine  ...  12/6/20 - 12/12/20                      621\n",
       "1  91902       Bonita  ...  12/6/20 - 12/12/20                      529\n",
       "2  91905    Boulevard  ...  12/6/20 - 12/12/20                      298\n",
       "3  91906        Campo  ...  12/6/20 - 12/12/20                      488\n",
       "4  91910  Chula Vista  ...  12/6/20 - 12/12/20                      750\n",
       "\n",
       "[5 rows x 12 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate positive per 100k\n",
    "change['Positive Cases Per 100K'] = ''\n",
    "for i, row in change.iterrows():\n",
    "    if change['Population 2018'][i] >= 1000:\n",
    "        change['Positive Cases Per 100K'][i] = round(change['Positive'][i]/change['Population 2018'][i]*100000, 0)\n",
    "    else:\n",
    "        change['Positive Cases Per 100K'][i] = '*'\n",
    "\n",
    "# save as csv\n",
    "change.to_csv(change_csv, index=False)\n",
    "change.to_csv(change_upload, index=False)  # for AGOL upload\n",
    "change.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPDATE AGOL DASHBOARDS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CONNECT TO AGOL ACCOUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "gis = GIS(\"pro\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### FUNCTIONS FOR MAP MODIFICATIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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": [
    "#### OVERWRITE FEATURE LAYERS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#########\n",
    "# TOTAL #\n",
    "#########\n",
    "\n",
    "#get feature layer \n",
    "total_layer = gis.content.get(total_layer_id)\n",
    "total_layer\n",
    "\n",
    "total_layer_collection = FeatureLayerCollection.fromitem(total_layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "total_layer_collection.manager.overwrite(total_upload)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##########\n",
    "# WEEKLY #\n",
    "##########\n",
    "\n",
    "#get feature layer \n",
    "weekly_layer = gis.content.get(weekly_layer_id)\n",
    "weekly_layer\n",
    "\n",
    "weekly_layer_collection = FeatureLayerCollection.fromitem(weekly_layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "weekly_layer_collection.manager.overwrite(change_upload)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### UPDATE MAP SYMBOLOGY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "61074\n",
      "<Item title:\"San Diego Zip Code COVID-19 Testing: Total Tests and Percent Positive\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#########\n",
    "# TOTAL #\n",
    "#########\n",
    "\n",
    "#get max for graduated point symbology\n",
    "total_max = zips['Total'].max()\n",
    "total_max = numpy.int64(total_max)\n",
    "print(total_max)\n",
    "\n",
    "#get map data\n",
    "total_map = get_map(total_map_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "total_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = total_max.item()\n",
    "total_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = total_max.item()\n",
    "\n",
    "#update map to save changes\n",
    "total_map_update = update_map(total_map_id, total_map)\n",
    "total_map_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4998\n",
      "<Item title:\"San Diego Zip Code COVID-19 Testing: New Tests and Percent Positive\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##########\n",
    "# WEEKLY #\n",
    "##########\n",
    "\n",
    "#get max for graduated point symbology\n",
    "weekly_max = change['Total'].max()\n",
    "print(weekly_max)\n",
    "\n",
    "#get map data\n",
    "weekly_map = get_map(weekly_map_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "weekly_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = weekly_max.item()\n",
    "weekly_map['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = weekly_max.item()\n",
    "\n",
    "#update map to save changes\n",
    "weekly_map_update = update_map(weekly_map_id, weekly_map)\n",
    "weekly_map_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"Healthy Places Index (HPI) and COVID-19 Test Results\" type:Web Map owner:jembury8568_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###########\n",
    "# HPI MAP #\n",
    "###########\n",
    "\n",
    "#get map data\n",
    "hpi_map = get_map(hpi_map_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "hpi_map['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = total_max.item()\n",
    "hpi_map['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = total_max.item()\n",
    "\n",
    "hpi_map['operationalLayers'][8]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = weekly_max.item()\n",
    "hpi_map['operationalLayers'][8]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = weekly_max.item()\n",
    "\n",
    "#update map to save changes\n",
    "hpi_map_update = update_map(hpi_map_id, hpi_map)\n",
    "hpi_map_update\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### CHECK DASHBOARDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "webbrowser.open(total_dash, new=2)\n",
    "webbrowser.open(weekly_dash, new=2)\n",
    "webbrowser.open(hpi_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
}