snf_dash_update.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 SNF Dashboard Update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download data from https://www.cdph.ca.gov/Programs/CID/DCDC/Pages/COVID-19/SNFsCOVID_19.aspx"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IMPORTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libraries\n",
    "from arcgis.gis import GIS\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "import sys\n",
    "import webbrowser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SAVE DATA FROM THIS WEBSITE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Scroll down to Tableau dashboard\n",
    "2. Check \"report date\" for data_date and file_date variables below\n",
    "3. Select all San Diego County records in the table\n",
    "4. Click the \"Download\" Button, then the \"Data\" button in the pop-up\n",
    "5. Click \"download all rows as a text file\" then rename file to match naming convention \n",
    "6. Current naming convention is \"raw_snf_covid_MMDDYY.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Open CDPH's COVID-19 SNF site in browser\n",
    "# Save San Diego County records to CSV\n",
    "webbrowser.open('https://www.cdph.ca.gov/Programs/CID/DCDC/Pages/COVID-19/SNFsCOVID_19.aspx', new=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ENTER FILE DATES BEFORE RUNNING CELLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "092121\n"
     ]
    }
   ],
   "source": [
    "##############################\n",
    "###SET DATES BEFORE RUNNING###\n",
    "##############################\n",
    "#data_date = 'MM/DD/YYYY'\n",
    "data_date ='09/21/2021'\n",
    "\n",
    "#file_date = 'MMDDYY'\n",
    "file_date = data_date.replace('2021', '21').replace('/','')\n",
    "print(file_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FILE PATHS AND AGOL IDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "#specify file paths\n",
    "#paths in\n",
    "path_in = '../covid_data/snfs/raw/raw_snf_covid_{}.csv'.format(file_date)\n",
    "path_snf = './data/snf_basic_info.csv'\n",
    "\n",
    "#paths out\n",
    "path_out = '../covid_data/snfs/snf_covid_{}.csv'.format(file_date)\n",
    "path_out_active = '../covid_data/snfs/snf_covid_active_{}.csv'.format(file_date)\n",
    "db_out = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/CA-Nursing-Facilities-Data/snf_covid_{}.csv'.format(file_date)\n",
    "\n",
    "#AGOL IDs\n",
    "\n",
    "# Cumulative Cases/Deaths dashboard (4 maps)\n",
    "snf_layer1 = \"55b29be4e904457f994713697f8f073b\" # feature layer\n",
    "res1 = \"16dce30762734f579f7849bdafc8c019\" # map\n",
    "res2 = \"a8319de392f64e4c836d42190ed10205\" # map\n",
    "hcw1 = \"11543f486f414409b3106b98eb3e5fe4\" # map\n",
    "hcw2 = \"d2cbaba1d61e43dab61aeb2fb2bd26de\" # map\n",
    "snf_dashboard1 = 'https://arcg.is/0bjCe1' # url to dashboard\n",
    "\n",
    "# Active Cases dashboard (2 maps)\n",
    "layer_id = '93dd409735d149b7b8ad484a78c23c9b' # feature layer\n",
    "active_res_id = '27c0923fe74c4b7494d85e9e56b239f2' # map\n",
    "active_hcw_id = '0286554fbd184deb99f752dc686b0705' # map\n",
    "snf_dashboard2 = 'https://arcg.is/1zDKnz' # url to dashboard"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATA PROCESSING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "86\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>FACILITY_ID</th>\n",
       "      <th>HCW_DEATHS_LABEL</th>\n",
       "      <th>RES_DEATHS_LABEL</th>\n",
       "      <th>HCW_CUM_LABEL</th>\n",
       "      <th>RES_CUM_LABEL</th>\n",
       "      <th>CURRENT_ACTIVE_CASES_RESIDENTS_</th>\n",
       "      <th>CURRENT_ACTIVE_HCW</th>\n",
       "      <th>HCW_DEATHS_PTS</th>\n",
       "      <th>HCW_DEATHS_STATS</th>\n",
       "      <th>RES_DEATHS_PTS</th>\n",
       "      <th>RES_DEATHS_STATS</th>\n",
       "      <th>HCW_CUM_PTS</th>\n",
       "      <th>HCW_CUM_STATS</th>\n",
       "      <th>RES_CUM_PTS</th>\n",
       "      <th>RES_CUM_STATS</th>\n",
       "      <th>DATA_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>80000056</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>56</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>63</td>\n",
       "      <td>63</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>90000102</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>28</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>80000070</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>63</td>\n",
       "      <td>55</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>63</td>\n",
       "      <td>55</td>\n",
       "      <td>55</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>90000029</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>21</td>\n",
       "      <td>36</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>21</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>80000077</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>58</td>\n",
       "      <td>68</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>58</td>\n",
       "      <td>58</td>\n",
       "      <td>68</td>\n",
       "      <td>68</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FACILITY_ID HCW_DEATHS_LABEL  ... RES_CUM_STATS   DATA_DATE\n",
       "0     80000056                0  ...            63  09/21/2021\n",
       "1     90000102                0  ...            27  09/21/2021\n",
       "2     80000070                0  ...            55  09/21/2021\n",
       "3     90000029                0  ...            36  09/21/2021\n",
       "4     80000077                0  ...            68  09/21/2021\n",
       "\n",
       "[5 rows x 16 columns]"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###################################################\n",
    "###PREP WEBSITE DATA FOR MERGE WITH SPATIAL DATA###\n",
    "###################################################\n",
    "\n",
    "#read csv\n",
    "df = pd.read_csv(path_in)\n",
    "\n",
    "# replacing blank spaces with '_' in column names \n",
    "df.columns =[column.replace(\" \", \"_\") for column in df.columns]\n",
    "  \n",
    "#delete unnecessary columns from df\n",
    "del df['COUNTY']\n",
    "del df['County_(copy)']\n",
    "del df['FACILITY_NAME']\n",
    "# del df['Measure_Names']\n",
    "del df['NEW_CONFIRMED_POSITIVE_HCW___']\n",
    "del df['NEW_CONFIRMED_POSITIVE_RESIDENTS_']\n",
    "# del df['Measure_Values']\n",
    "del df['AVAILABLE_BEDS_CAPABLE_OF_ISOLATION']\n",
    "del df['AVAILABLE_BEDS']\n",
    "\n",
    "#rename columns to match feature layer\n",
    "df = df.rename(columns = {'Facility_Id':'FACID','COVID-RELATED_HCW_DEATHS_':'HCW_DEATHS_LABEL', \n",
    "                          'COVID-RELATED_RESIDENT_DEATHS':'RES_DEATHS_LABEL', \n",
    "                          'CUMULATIVE_POSITIVE_HCW_':'HCW_CUM_LABEL', \n",
    "                          'CUMULATIVE_POSITIVE_RESIDENTS_':'RES_CUM_LABEL'})\n",
    "\n",
    "#duplicate columns \n",
    "#3 columns for each attribute: \n",
    "# label (includes <11), points (<11 as 1 for symbology), stats (<11 as 0 for calcs)\n",
    "df['HCW_DEATHS_PTS'] = df['HCW_DEATHS_LABEL']\n",
    "df['HCW_DEATHS_STATS'] = df['HCW_DEATHS_LABEL']\n",
    "\n",
    "df['RES_DEATHS_PTS'] = df['RES_DEATHS_LABEL']\n",
    "df['RES_DEATHS_STATS'] = df['RES_DEATHS_LABEL']\n",
    "\n",
    "df['HCW_CUM_PTS'] = df['HCW_CUM_LABEL']\n",
    "df['HCW_CUM_STATS'] = df['HCW_CUM_LABEL']\n",
    "\n",
    "df['RES_CUM_PTS'] = df['RES_CUM_LABEL']\n",
    "df['RES_CUM_STATS'] = df['RES_CUM_LABEL']\n",
    "\n",
    "df['DATA_DATE'] = data_date\n",
    "\n",
    "#format duplicated rows according to rules\n",
    "#3 columns for each attribute: \n",
    "# label (includes <11), points (<11 as 1 for symbology), stats (<11 as 0 for calcs)\n",
    "for i, row in df.iterrows():\n",
    "    if(df['HCW_DEATHS_PTS'][i] == '<11'):\n",
    "        df['HCW_DEATHS_PTS'][i] = 1\n",
    "    if(df['HCW_DEATHS_STATS'][i] == '<11'):\n",
    "        df['HCW_DEATHS_STATS'][i] = 0   \n",
    "    if(df['RES_DEATHS_PTS'][i] == '<11'):\n",
    "        df['RES_DEATHS_PTS'][i] = 1\n",
    "    if(df['RES_DEATHS_STATS'][i] == '<11'):\n",
    "        df['RES_DEATHS_STATS'][i] = 0 \n",
    "    if(df['HCW_CUM_PTS'][i] == '<11'):\n",
    "        df['HCW_CUM_PTS'][i] = 1\n",
    "    if(df['HCW_CUM_STATS'][i] == '<11'):\n",
    "        df['HCW_CUM_STATS'][i] = 0   \n",
    "    if(df['RES_CUM_PTS'][i] == '<11'):\n",
    "        df['RES_CUM_PTS'][i] = 1\n",
    "    if(df['RES_CUM_STATS'][i] == '<11'):\n",
    "        df['RES_CUM_STATS'][i] = 0 \n",
    "\n",
    "#cast columns from string to int for feature layer\n",
    "df['HCW_DEATHS_PTS'] = df['HCW_DEATHS_PTS'].astype(int)\n",
    "df['HCW_DEATHS_STATS'] = df['HCW_DEATHS_STATS'].astype(int)\n",
    "df['RES_DEATHS_PTS'] = df['RES_DEATHS_PTS'].astype(int)\n",
    "df['RES_DEATHS_STATS'] = df['RES_DEATHS_STATS'].astype(int)\n",
    "df['HCW_CUM_PTS'] = df['HCW_CUM_PTS'].astype(int)\n",
    "df['HCW_CUM_STATS'] = df['HCW_CUM_STATS'].astype(int)\n",
    "df['RES_CUM_PTS'] = df['RES_CUM_PTS'].astype(int)\n",
    "df['RES_CUM_STATS'] = df['RES_CUM_STATS'].astype(int)\n",
    "\n",
    "print(len(df))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 288 4059 4671\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>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FACID</th>\n",
       "      <th>FACILITY_NAME</th>\n",
       "      <th>SRA</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>HCW_DEATHS_LABEL</th>\n",
       "      <th>RES_DEATHS_LABEL</th>\n",
       "      <th>HCW_CUM_LABEL</th>\n",
       "      <th>RES_CUM_LABEL</th>\n",
       "      <th>CURRENT_ACTIVE_CASES_RESIDENTS_</th>\n",
       "      <th>CURRENT_ACTIVE_HCW</th>\n",
       "      <th>HCW_DEATHS_PTS</th>\n",
       "      <th>HCW_DEATHS_STATS</th>\n",
       "      <th>RES_DEATHS_PTS</th>\n",
       "      <th>RES_DEATHS_STATS</th>\n",
       "      <th>HCW_CUM_PTS</th>\n",
       "      <th>HCW_CUM_STATS</th>\n",
       "      <th>RES_CUM_PTS</th>\n",
       "      <th>RES_CUM_STATS</th>\n",
       "      <th>DATA_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-117.151726</td>\n",
       "      <td>32.800797</td>\n",
       "      <td>SAN DIEGO</td>\n",
       "      <td>80000056</td>\n",
       "      <td>ABBY GARDENS HEALTHCARE CENTER</td>\n",
       "      <td>KEARNY MESA</td>\n",
       "      <td>8060 Frost St, San Diego, CA 92123</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>56</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>63</td>\n",
       "      <td>63</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-116.995140</td>\n",
       "      <td>32.739490</td>\n",
       "      <td>SAN DIEGO</td>\n",
       "      <td>90000102</td>\n",
       "      <td>AMAYA SPRINGS HEALTH CARE CENTER</td>\n",
       "      <td>SPRING VALLEY</td>\n",
       "      <td>8625 Lamar St, Spring Valley, CA 91977</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>28</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Longitude   Latitude     COUNTY  ...  RES_CUM_PTS RES_CUM_STATS   DATA_DATE\n",
       "0 -117.151726  32.800797  SAN DIEGO  ...           63            63  09/21/2021\n",
       "1 -116.995140  32.739490  SAN DIEGO  ...           27            27  09/21/2021\n",
       "\n",
       "[2 rows x 22 columns]"
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###########################################\n",
    "###MERGE WEBSITE & SPATIAL (COORDS) DATA###\n",
    "###########################################\n",
    "\n",
    "#read SNF file (addresses and coordinates)\n",
    "snf = pd.read_csv(path_snf)\n",
    "\n",
    "#merge website and spatial data\n",
    "df = df.rename(columns = {'FACILITY_ID':'FACID'})\n",
    "snf = snf.merge(df, on = 'FACID')\n",
    "\n",
    "#########################################\n",
    "###DETERMINE COUNTY TOTALS AND ADD ROW###\n",
    "#########################################\n",
    "\n",
    "#get county wide totals\n",
    "hcw_deaths_sum = 0\n",
    "res_deaths_sum = 0\n",
    "hcw_cum_sum = 0\n",
    "res_cum_sum = 0\n",
    "\n",
    "for i,row in snf.iterrows():\n",
    "    hcw_deaths_sum = hcw_deaths_sum + snf['HCW_DEATHS_STATS'][i]\n",
    "    res_deaths_sum = res_deaths_sum + snf['RES_DEATHS_STATS'][i]\n",
    "    hcw_cum_sum = hcw_cum_sum + snf['HCW_CUM_STATS'][i]\n",
    "    res_cum_sum = res_cum_sum + snf['RES_CUM_STATS'][i]\n",
    "\n",
    "print(hcw_deaths_sum, res_deaths_sum, hcw_cum_sum, res_cum_sum)\n",
    "snf.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "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>Facility</th>\n",
       "      <th>SRA</th>\n",
       "      <th>Address</th>\n",
       "      <th>FACID</th>\n",
       "      <th>Date</th>\n",
       "      <th>HCW Accum Deaths</th>\n",
       "      <th>HCW Accum Cases</th>\n",
       "      <th>HCW Active Cases</th>\n",
       "      <th>Res Accum Deaths</th>\n",
       "      <th>Res Accum Cases</th>\n",
       "      <th>Res Active Cases</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABBY GARDENS HEALTHCARE CENTER</td>\n",
       "      <td>KEARNY MESA</td>\n",
       "      <td>8060 Frost St, San Diego, CA 92123</td>\n",
       "      <td>80000056</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>-117.151726</td>\n",
       "      <td>32.800797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMAYA SPRINGS HEALTH CARE CENTER</td>\n",
       "      <td>SPRING VALLEY</td>\n",
       "      <td>8625 Lamar St, Spring Valley, CA 91977</td>\n",
       "      <td>90000102</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>-116.995140</td>\n",
       "      <td>32.739490</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Facility            SRA  ...   Longitude   Latitude\n",
       "0    ABBY GARDENS HEALTHCARE CENTER    KEARNY MESA  ... -117.151726  32.800797\n",
       "1  AMAYA SPRINGS HEALTH CARE CENTER  SPRING VALLEY  ... -116.995140  32.739490\n",
       "\n",
       "[2 rows x 13 columns]"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#######################################\n",
    "# CREATE NEW DF WITH ACTIVE CASE INFO #\n",
    "#######################################\n",
    "# must create active df before deleting active case columns from the snf df\n",
    "\n",
    "# columns for the 'Active Cases' feature layer (2 maps dashboard)\n",
    "active = snf[['FACILITY_NAME', 'SRA', 'ADDRESS', 'FACID', 'DATA_DATE', 'HCW_DEATHS_LABEL', \n",
    "              'HCW_CUM_LABEL', 'CURRENT_ACTIVE_HCW', 'RES_DEATHS_LABEL', 'RES_CUM_LABEL', \n",
    "              'CURRENT_ACTIVE_CASES_RESIDENTS_', 'Longitude', 'Latitude']]\n",
    "\n",
    "# rename columns to match schema\n",
    "active = active.rename(columns = {'FACILITY_NAME':'Facility', 'ADDRESS':'Address', 'DATA_DATE':'Date', \n",
    "                                  'HCW_DEATHS_LABEL':'HCW Accum Deaths', 'HCW_CUM_LABEL':'HCW Accum Cases', \n",
    "                                  'CURRENT_ACTIVE_HCW':'HCW Active Cases', \n",
    "                                  'RES_DEATHS_LABEL':'Res Accum Deaths', 'RES_CUM_LABEL':'Res Accum Cases', \n",
    "                                  'CURRENT_ACTIVE_CASES_RESIDENTS_':'Res Active Cases'})\n",
    "\n",
    "active.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "87\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>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FACID</th>\n",
       "      <th>FACILITY_NAME</th>\n",
       "      <th>SRA</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>HCW_DEATHS_LABEL</th>\n",
       "      <th>RES_DEATHS_LABEL</th>\n",
       "      <th>HCW_CUM_LABEL</th>\n",
       "      <th>RES_CUM_LABEL</th>\n",
       "      <th>HCW_DEATHS_PTS</th>\n",
       "      <th>HCW_DEATHS_STATS</th>\n",
       "      <th>RES_DEATHS_PTS</th>\n",
       "      <th>RES_DEATHS_STATS</th>\n",
       "      <th>HCW_CUM_PTS</th>\n",
       "      <th>HCW_CUM_STATS</th>\n",
       "      <th>RES_CUM_PTS</th>\n",
       "      <th>RES_CUM_STATS</th>\n",
       "      <th>DATA_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>-117.098743</td>\n",
       "      <td>32.66279</td>\n",
       "      <td>SAN DIEGO</td>\n",
       "      <td>90000042</td>\n",
       "      <td>WINDSOR GARDENS CONVALESCENT CENTER OF SAN DIEGO</td>\n",
       "      <td>NATIONAL CITY</td>\n",
       "      <td>220 E 24th St, National City, CA 91950</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>17</td>\n",
       "      <td>54</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>-118.000000</td>\n",
       "      <td>33.00000</td>\n",
       "      <td>SAN DIEGO</td>\n",
       "      <td>999</td>\n",
       "      <td>ALL SKILLED NURSING FACILITIES</td>\n",
       "      <td>ALL SRAs</td>\n",
       "      <td>Totals do not include suppressed data for faci...</td>\n",
       "      <td>0</td>\n",
       "      <td>288</td>\n",
       "      <td>4059</td>\n",
       "      <td>4671</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>288</td>\n",
       "      <td>0</td>\n",
       "      <td>4059</td>\n",
       "      <td>0</td>\n",
       "      <td>4671</td>\n",
       "      <td>09/21/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Longitude  Latitude     COUNTY  ...  RES_CUM_PTS RES_CUM_STATS   DATA_DATE\n",
       "85 -117.098743  32.66279  SAN DIEGO  ...          100           100  09/21/2021\n",
       "86 -118.000000  33.00000  SAN DIEGO  ...            0          4671  09/21/2021\n",
       "\n",
       "[2 rows x 20 columns]"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#########################################\n",
    "# Format and save orginal SNF dataframe #\n",
    "#########################################\n",
    "# finish and save DF for the 'Cumulative Cases/Deaths' feature layer (4 maps dashboard)\n",
    "\n",
    "# del active columns for snf df\n",
    "del snf['CURRENT_ACTIVE_HCW']\n",
    "del snf['CURRENT_ACTIVE_CASES_RESIDENTS_']\n",
    "\n",
    "#create new row for county wide totals - snf df\n",
    "new_row = {'Longitude':-118 , 'Latitude':33 , 'COUNTY': 'SAN DIEGO', 'FACID':999, 'FACILITY_NAME':' ALL SKILLED NURSING FACILITIES', 'SRA':'ALL SRAs', 'ADDRESS':'Totals do not include suppressed data for facilities with <11 confirmed cases or related deaths.', 'DATA_DATE':data_date, 'HCW_DEATHS_LABEL':hcw_deaths_sum, 'RES_DEATHS_LABEL':res_deaths_sum, 'HCW_CUM_LABEL':hcw_cum_sum, 'RES_CUM_LABEL':res_cum_sum, 'HCW_DEATHS_STATS':hcw_deaths_sum, 'RES_DEATHS_STATS':res_deaths_sum, 'HCW_CUM_STATS':hcw_cum_sum, 'RES_CUM_STATS':res_cum_sum, 'HCW_DEATHS_PTS':0, 'RES_DEATHS_PTS':0, 'HCW_CUM_PTS':0, 'RES_CUM_PTS':0}\n",
    "\n",
    "#append row to dataframe\n",
    "snf = snf.append(new_row, ignore_index=True)\n",
    "\n",
    "#save as csv file for feature layer overwrite\n",
    "snf.to_csv(path_out, index = False)\n",
    "\n",
    "print(len(snf))\n",
    "snf.tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "86\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>Facility</th>\n",
       "      <th>SRA</th>\n",
       "      <th>Address</th>\n",
       "      <th>FACID</th>\n",
       "      <th>Date</th>\n",
       "      <th>HCW Accum Deaths</th>\n",
       "      <th>HCW Accum Cases</th>\n",
       "      <th>HCW Active Cases</th>\n",
       "      <th>Res Accum Deaths</th>\n",
       "      <th>Res Accum Cases</th>\n",
       "      <th>Res Active Cases</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>HCW Death Points</th>\n",
       "      <th>HCW Death Stats</th>\n",
       "      <th>HCW Accum Points</th>\n",
       "      <th>HCW Accum Stats</th>\n",
       "      <th>HCW Active Points</th>\n",
       "      <th>HCW Active Stats</th>\n",
       "      <th>Res Death Points</th>\n",
       "      <th>Res Death Stats</th>\n",
       "      <th>Res Accum Points</th>\n",
       "      <th>Res Accum Stats</th>\n",
       "      <th>Res Active Points</th>\n",
       "      <th>Res Active Stats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABBY GARDENS HEALTHCARE CENTER</td>\n",
       "      <td>KEARNY MESA</td>\n",
       "      <td>8060 Frost St, San Diego, CA 92123</td>\n",
       "      <td>80000056</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>-117.151726</td>\n",
       "      <td>32.800797</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMAYA SPRINGS HEALTH CARE CENTER</td>\n",
       "      <td>SPRING VALLEY</td>\n",
       "      <td>8625 Lamar St, Spring Valley, CA 91977</td>\n",
       "      <td>90000102</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>-116.995140</td>\n",
       "      <td>32.739490</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           Facility  ... Res Active Stats\n",
       "0    ABBY GARDENS HEALTHCARE CENTER  ...                0\n",
       "1  AMAYA SPRINGS HEALTH CARE CENTER  ...                0\n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###########################\n",
    "# Format Active dataframe #\n",
    "###########################\n",
    "\n",
    "# add columns for point symbology and stats calcs for suppressed data to active df\n",
    "active['HCW Death Points'] = active['HCW Accum Deaths']\n",
    "active['HCW Death Stats'] = active['HCW Accum Deaths']\n",
    "active['HCW Accum Points'] = active['HCW Accum Cases']\n",
    "active['HCW Accum Stats'] = active['HCW Accum Cases']\n",
    "active['HCW Active Points'] = active['HCW Active Cases']\n",
    "active['HCW Active Stats'] = active['HCW Active Cases']\n",
    "\n",
    "active['Res Death Points'] = active['Res Accum Deaths']\n",
    "active['Res Death Stats'] = active['Res Accum Deaths']\n",
    "active['Res Accum Points'] = active['Res Accum Cases']\n",
    "active['Res Accum Stats'] = active['Res Accum Cases']\n",
    "active['Res Active Points'] = active['Res Active Cases']\n",
    "active['Res Active Stats'] = active['Res Active Cases']\n",
    "\n",
    "# for points, change <11 to 1\n",
    "# for stats, change <11 to 0\n",
    "for i, row in active.iterrows():\n",
    "    if active['HCW Death Points'][i] == '<11':\n",
    "        active['HCW Death Points'][i] = 1\n",
    "    if active['HCW Death Stats'][i] == '<11':\n",
    "        active['HCW Death Stats'][i] = 0\n",
    "    if active['HCW Accum Points'][i] == '<11':\n",
    "        active['HCW Accum Points'][i] = 1\n",
    "    if active['HCW Accum Stats'][i] == '<11':\n",
    "        active['HCW Accum Stats'][i] = 0\n",
    "    if active['HCW Active Points'][i] == '<11':\n",
    "        active['HCW Active Points'][i] = 1\n",
    "    if active['HCW Active Stats'][i] == '<11':\n",
    "        active['HCW Active Stats'][i] = 0\n",
    "        \n",
    "    if active['Res Death Points'][i] == '<11':\n",
    "        active['Res Death Points'][i] = 1\n",
    "    if active['Res Death Stats'][i] == '<11':\n",
    "        active['Res Death Stats'][i] = 0\n",
    "    if active['Res Accum Points'][i] == '<11':\n",
    "        active['Res Accum Points'][i] = 1\n",
    "    if active['Res Accum Stats'][i] == '<11':\n",
    "        active['Res Accum Stats'][i] = 0\n",
    "    if active['Res Active Points'][i] == '<11':\n",
    "        active['Res Active Points'][i] = 1\n",
    "    if active['Res Active Stats'][i] == '<11':\n",
    "        active['Res Active Stats'][i] = 0\n",
    "        \n",
    "# change points, stats columns to int type\n",
    "active['HCW Death Points'] = active['HCW Death Points'].astype(int)\n",
    "active['HCW Death Stats'] = active['HCW Death Stats'].astype(int)\n",
    "active['HCW Accum Points'] = active['HCW Accum Points'].astype(int)\n",
    "active['HCW Accum Stats'] = active['HCW Accum Stats'].astype(int)\n",
    "active['HCW Active Points'] = active['HCW Active Points'].astype(int)\n",
    "active['HCW Active Stats'] = active['HCW Active Stats'].astype(int)\n",
    "\n",
    "active['Res Death Points'] = active['Res Death Points'].astype(int)\n",
    "active['Res Death Stats'] = active['Res Death Stats'].astype(int)\n",
    "active['Res Accum Points'] = active['Res Accum Points'].astype(int)\n",
    "active['Res Accum Stats'] = active['Res Accum Stats'].astype(int)\n",
    "active['Res Active Points'] = active['Res Active Points'].astype(int)\n",
    "active['Res Active Stats'] = active['Res Active Stats'].astype(int)\n",
    "\n",
    "print(len(active))\n",
    "active.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "87\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>Facility</th>\n",
       "      <th>SRA</th>\n",
       "      <th>Address</th>\n",
       "      <th>FACID</th>\n",
       "      <th>Date</th>\n",
       "      <th>HCW Accum Deaths</th>\n",
       "      <th>HCW Accum Cases</th>\n",
       "      <th>HCW Active Cases</th>\n",
       "      <th>Res Accum Deaths</th>\n",
       "      <th>Res Accum Cases</th>\n",
       "      <th>Res Active Cases</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>HCW Death Points</th>\n",
       "      <th>HCW Death Stats</th>\n",
       "      <th>HCW Accum Points</th>\n",
       "      <th>HCW Accum Stats</th>\n",
       "      <th>HCW Active Points</th>\n",
       "      <th>HCW Active Stats</th>\n",
       "      <th>Res Death Points</th>\n",
       "      <th>Res Death Stats</th>\n",
       "      <th>Res Accum Points</th>\n",
       "      <th>Res Accum Stats</th>\n",
       "      <th>Res Active Points</th>\n",
       "      <th>Res Active Stats</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>WINDSOR GARDENS CONVALESCENT CENTER OF SAN DIEGO</td>\n",
       "      <td>NATIONAL CITY</td>\n",
       "      <td>220 E 24th St, National City, CA 91950</td>\n",
       "      <td>90000042</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>-117.098743</td>\n",
       "      <td>32.66279</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>54</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>All Skilled Nursing Facilities</td>\n",
       "      <td>All SRAs</td>\n",
       "      <td>*Totals do not include suppressed data for fac...</td>\n",
       "      <td>999</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0*</td>\n",
       "      <td>4059*</td>\n",
       "      <td>0*</td>\n",
       "      <td>288*</td>\n",
       "      <td>4671*</td>\n",
       "      <td>13*</td>\n",
       "      <td>-118.000000</td>\n",
       "      <td>33.00000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4059</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>288</td>\n",
       "      <td>0</td>\n",
       "      <td>4671</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Facility  ... Res Active Stats\n",
       "85  WINDSOR GARDENS CONVALESCENT CENTER OF SAN DIEGO  ...                0\n",
       "86                    All Skilled Nursing Facilities  ...               13\n",
       "\n",
       "[2 rows x 25 columns]"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###################################\n",
    "# Append summary row for all SRAs #\n",
    "###################################\n",
    "\n",
    "#create new row for county wide totals - active df\n",
    "new_row = {'Facility':'All Skilled Nursing Facilities', \n",
    "           'SRA':'All SRAs', \n",
    "           'Address':'*Totals do not include suppressed data for facilities with <11 related deaths, accumulated cases, or active cases.', \n",
    "           'FACID': 999, \n",
    "           'Date': data_date, \n",
    "           'HCW Accum Deaths': str(active['HCW Death Stats'].sum()) + '*',\n",
    "           'HCW Accum Cases': str(active['HCW Accum Stats'].sum()) + '*',\n",
    "           'HCW Active Cases': str(active['HCW Active Stats'].sum()) + '*',\n",
    "           'Res Accum Deaths': str(active['Res Death Stats'].sum()) + '*',\n",
    "           'Res Accum Cases': str(active['Res Accum Stats'].sum()) + '*',\n",
    "           'Res Active Cases': str(active['Res Active Stats'].sum()) + '*',\n",
    "           'Latitude': 33,\n",
    "           'Longitude': -118,\n",
    "           'HCW Death Points': 0,\n",
    "           'HCW Death Stats': active['HCW Death Stats'].sum(),\n",
    "           'HCW Accum Points': 0,\n",
    "           'HCW Accum Stats': active['HCW Accum Stats'].sum(),\n",
    "           'HCW Active Points': 0,\n",
    "           'HCW Active Stats': active['HCW Active Stats'].sum(),\n",
    "           'Res Death Points': 0,\n",
    "           'Res Death Stats': active['Res Death Stats'].sum(),\n",
    "           'Res Accum Points': 0,\n",
    "           'Res Accum Stats': active['Res Accum Stats'].sum(),\n",
    "           'Res Active Points': 0,\n",
    "           'Res Active Stats': active['Res Active Stats'].sum()\n",
    "          }\n",
    "\n",
    "#append row to dataframe\n",
    "active = active.append(new_row, ignore_index=True)\n",
    "\n",
    "#########################\n",
    "# Save Active dataframe #\n",
    "#########################\n",
    "\n",
    "#save as csv file for feature layer overwrite\n",
    "active.to_csv(path_out_active, index = False)\n",
    "\n",
    "print(len(active))\n",
    "active.tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "87\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>Facility</th>\n",
       "      <th>SRA</th>\n",
       "      <th>Address</th>\n",
       "      <th>FACID</th>\n",
       "      <th>Date</th>\n",
       "      <th>HCW Accum Deaths</th>\n",
       "      <th>HCW Accum Cases</th>\n",
       "      <th>HCW Active Cases</th>\n",
       "      <th>Res Accum Deaths</th>\n",
       "      <th>Res Accum Cases</th>\n",
       "      <th>Res Active Cases</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>WINDSOR GARDENS CONVALESCENT CENTER OF SAN DIEGO</td>\n",
       "      <td>NATIONAL CITY</td>\n",
       "      <td>220 E 24th St, National City, CA 91950</td>\n",
       "      <td>90000042</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>&lt;11</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>-117.098743</td>\n",
       "      <td>32.66279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>All Skilled Nursing Facilities</td>\n",
       "      <td>All SRAs</td>\n",
       "      <td>*Totals do not include suppressed data for fac...</td>\n",
       "      <td>999</td>\n",
       "      <td>09/21/2021</td>\n",
       "      <td>0*</td>\n",
       "      <td>4059*</td>\n",
       "      <td>0*</td>\n",
       "      <td>288*</td>\n",
       "      <td>4671*</td>\n",
       "      <td>13*</td>\n",
       "      <td>-118.000000</td>\n",
       "      <td>33.00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Facility  ...  Latitude\n",
       "85  WINDSOR GARDENS CONVALESCENT CENTER OF SAN DIEGO  ...  32.66279\n",
       "86                    All Skilled Nursing Facilities  ...  33.00000\n",
       "\n",
       "[2 rows x 13 columns]"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#####################################\n",
    "# Subset Active df for Dropbox save #\n",
    "#####################################\n",
    "\n",
    "active2 = active.iloc[:,0:13]\n",
    "active2.to_csv(db_out, index = False)\n",
    "\n",
    "print(len(active2))\n",
    "active2.tail(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CONNECT TO AGOL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "#connect to ArcGIS account\n",
    "gis = GIS(\"pro\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FUNCTIONS FOR MAP MODIFICATIONS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Code Reference: https://community.esri.com/groups/arcgis-python-api/blog/2019/04/09/updating-layer-symbology-with-the-arcgis-api-for-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_map (map_id, data):\n",
    "    '''\n",
    "    UPDATE MAP TO SAVE CHANGES\n",
    "    '''\n",
    "\n",
    "    m = gis.content.get(map_id)\n",
    "    \n",
    "    # Set the item_properties to include the desired update\n",
    "    properties = {\"text\": json.dumps(data)}\n",
    "\n",
    "    # 'Commit' the updates to the Item\n",
    "    update = m.update(item_properties=properties)\n",
    "\n",
    "    # Print item_data to see that changes are reflected\n",
    "    #new_data = m.get_data()\n",
    "    #print(\"***********************NEW DEFINITION**********************\")\n",
    "    #print(json.dumps(new_data, indent=4, sort_keys=True))\n",
    "    \n",
    "    return update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CUMULATIVE CASES AND DEATHS DASHBOARD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GET MAX VALUES FOR MAP SYMBOL UPDATES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Max Res Cum: 4671\n",
      "Max HCW Cum: 4059\n",
      "Max Cum: 4671\n",
      "Max Res Deaths: 288\n",
      "Max HCW Deaths: 0\n",
      "Max Deaths: 288\n"
     ]
    }
   ],
   "source": [
    "# get max deaths and max cumulative cases for point symbology corrections\n",
    "\n",
    "# CASES\n",
    "max_res_cum = snf['RES_CUM_STATS'].max()\n",
    "print('Max Res Cum: {}'.format(max_res_cum))\n",
    "\n",
    "max_hcw_cum = snf['HCW_CUM_STATS'].max()\n",
    "print('Max HCW Cum: {}'.format(max_hcw_cum))\n",
    "\n",
    "# Determine if HCW max or resident max is larger, use for both maps\n",
    "max_cum = 0\n",
    "if(max_res_cum > max_hcw_cum):\n",
    "    max_cum = max_res_cum\n",
    "else:\n",
    "    max_cum = max_hcw_cum\n",
    "print('Max Cum: {}'.format(max_cum))\n",
    "\n",
    "# DEATHS\n",
    "max_res_deaths = snf['RES_DEATHS_STATS'].max()\n",
    "print('Max Res Deaths: {}'.format(max_res_deaths))\n",
    "\n",
    "max_hcw_deaths = snf['HCW_DEATHS_STATS'].max()\n",
    "print('Max HCW Deaths: {}'.format(max_hcw_deaths))\n",
    "\n",
    "# Determine if HCW max or resident max is larger, use for both maps\n",
    "max_deaths = 0\n",
    "if(max_res_deaths > max_hcw_deaths):\n",
    "    max_deaths = max_res_deaths\n",
    "else:\n",
    "    max_deaths = max_hcw_deaths\n",
    "print('Max Deaths: {}'.format(max_deaths))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### OVERWRITE FEATURE LAYER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#############################\n",
    "###OVERWRITE FEATURE LAYER###\n",
    "#############################\n",
    "\n",
    "#get feature layer containing updated data for maps associated with the COVID-19 dashboards\n",
    "layer = gis.content.get(snf_layer1)\n",
    "layer\n",
    "\n",
    "layer_collection = FeatureLayerCollection.fromitem(layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "layer_collection.manager.overwrite(path_out)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UPDATE MAPS (x4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 and San Diego County SNF Residents\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#residents - cumulative cases\n",
    "res1_data = get_map(res1)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "res1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_cum.item()\n",
    "res1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_cum.item()\n",
    "\n",
    "#update map to save changes\n",
    "res1_update = update_map(res1, res1_data)\n",
    "res1_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 and San Diego County SNF Resident Deaths\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#residents - deaths\n",
    "res2_data = get_map(res2)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "res2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_deaths.item()\n",
    "res2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_deaths.item()\n",
    "\n",
    "#update map to save changes\n",
    "res2_update = update_map(res2, res2_data)\n",
    "res2_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 and San Diego County SNF Healthcare Workers\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#healthcare workers - cumulative cases\n",
    "hcw1_data = get_map(hcw1)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "hcw1_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_cum.item()\n",
    "hcw1_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_cum.item()\n",
    "\n",
    "#update map to save changes\n",
    "hcw1_update = update_map(hcw1, hcw1_data)\n",
    "hcw1_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 and San Diego County SNF Healthcare Worker Deaths\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#healthcare workers - deaths\n",
    "hcw2_data = get_map(hcw2)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "hcw2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_deaths.item()\n",
    "hcw2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_deaths.item()\n",
    "\n",
    "#update map to save changes\n",
    "hcw2_update = update_map(hcw2, hcw2_data)\n",
    "hcw2_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ACTIVE CASES DASHBOARD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GET MAX VALUES FOR MAP SYMBOL UPDATES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Max Active: Res 13/ HCW 0; Max Accum.: Res 286/ HCW 145\n",
      "Max Active: 13; Max Accum: 286\n"
     ]
    }
   ],
   "source": [
    "#######################################\n",
    "# get max values for symbology update #\n",
    "#######################################\n",
    "active = active.query(\"FACID != 999\")\n",
    "\n",
    "max_active_hcw = active['HCW Active Stats'].max()\n",
    "max_active_res = active['Res Active Stats'].max()\n",
    "\n",
    "max_accum_hcw = active['HCW Accum Stats'].max()\n",
    "max_accum_res = active['Res Accum Stats'].max()\n",
    "\n",
    "print('Max Active: Res {}/ HCW {}; Max Accum.: Res {}/ HCW {}'.format(max_active_res, max_active_hcw, max_accum_res, max_accum_hcw))\n",
    "\n",
    "max_active = 0\n",
    "if(max_active_res > max_active_hcw):\n",
    "    max_active = max_active_res\n",
    "else:\n",
    "    max_active = max_active_hcw\n",
    "    \n",
    "max_accum = 0\n",
    "if(max_accum_res > max_accum_hcw):\n",
    "    max_accum = max_accum_res\n",
    "else:\n",
    "    max_accum = max_accum_hcw\n",
    "    \n",
    "print('Max Active: {}; Max Accum: {}'.format(max_active, max_accum))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### OVERWRITE FEATURE LAYER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "###########################\n",
    "# Overwrite Feature Layer #\n",
    "###########################\n",
    "\n",
    "#get feature layer containing updated data for maps associated with the COVID-19 dashboards\n",
    "layer = gis.content.get(layer_id)\n",
    "layer\n",
    "\n",
    "layer_collection = FeatureLayerCollection.fromitem(layer)\n",
    "\n",
    "#call the overwrite() method which can be accessed using the manager property\n",
    "layer_collection.manager.overwrite(path_out_active)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UPDATE MAPS (x2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 190,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"Active COVID-19 in SD County SNFs - Residents\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 190,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#########################\n",
    "# Update Active Res Map #\n",
    "#########################\n",
    "\n",
    "#get data\n",
    "active_res_data = get_map(active_res_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "active_res_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_active.item()\n",
    "active_res_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_active.item()\n",
    "\n",
    "active_res_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_accum.item()\n",
    "active_res_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_accum.item()\n",
    "\n",
    "#update map to save changes\n",
    "active_res_update = update_map(active_res_id, active_res_data)\n",
    "active_res_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"Active COVID-19 in SD County SNFs - HCW\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#########################\n",
    "# Update Active HCW Map #\n",
    "#########################\n",
    "\n",
    "#get data\n",
    "active_hcw_data = get_map(active_hcw_id)\n",
    "\n",
    "#set max value for graduated points symbols\n",
    "active_hcw_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_active.item()\n",
    "active_hcw_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_active.item()\n",
    "\n",
    "active_hcw_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_accum.item()\n",
    "active_hcw_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_accum.item()\n",
    "\n",
    "#update map to save changes\n",
    "active_hcw_update = update_map(active_hcw_id, active_hcw_data)\n",
    "active_hcw_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# VERIFY COVID-19 SNF DASHBOARD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Open COVID-19 SNF dashboards in browser\n",
    "webbrowser.open(snf_dashboard1, new=2)\n",
    "webbrowser.open(snf_dashboard2, 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
}