covid19_dashboard_update.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID-19 Dashboard Updates\n",
    "Center for Human Dynamics in the Mobile Age (HDMA) at San Diego State University\n",
    "\n",
    "Jessica Embury"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MODULES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import modules\n",
    "import arcgis\n",
    "from arcgis.gis import GIS\n",
    "from arcgis import geometry\n",
    "from arcgis.features import GeoAccessor, GeoSeriesAccessor\n",
    "from arcgis.features import FeatureLayerCollection\n",
    "from arcgis.features import FeatureLayer\n",
    "from arcgis.mapping import WebMap\n",
    "import pandas as pd\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "import json\n",
    "from copy import deepcopy\n",
    "import time\n",
    "import numpy as np\n",
    "import sys\n",
    "import webbrowser\n",
    "from copy import deepcopy\n",
    "import os \n",
    "import requests"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CONFIRM USER VARIABLES BEFORE RUNNING CELLS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##########################\n",
    "###SET PATHS IN AND OUT###\n",
    "##########################\n",
    "#PATHS IN\n",
    "#additional needed zip info - community name and population\n",
    "zips_info_path = './data/zips_info.csv'\n",
    "\n",
    "#PATHS OUT\n",
    "#wide df with a column for each date\n",
    "wide_df_path = './data/covid_accumulated.csv'\n",
    "\n",
    "#path out for dash updates, feature layer overwrite\n",
    "path_out= './data/covid_percents_upload.csv'\n",
    "path_out2 = '../covid_data/percents/covid_percents_{}.csv'\n",
    "\n",
    "#path to csv used to append new column to feature layer 2 for cumulative cases dashboard\n",
    "append_csv_path = './data/sd_zip_cumulative_covid_append.csv'\n",
    "\n",
    "#upload to dropbox\n",
    "dropbox_cumulative = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/SD_Zipcode_COVID_{}.csv'\n",
    "\n",
    "# 7 day dash layer overwrite\n",
    "seven_path = 'sandiego_covid_upload_7day.csv'\n",
    "\n",
    "######################################\n",
    "###ARCGIS ACCOUNT LOGIN INFORMATION###\n",
    "######################################\n",
    "\n",
    "#reference for authentication schemes: https://developers.arcgis.com/python/guide/working-with-different-authentication-schemes/\n",
    "\n",
    "#portal =''\n",
    "#username=''\n",
    "#password=''\n",
    "\n",
    "pro = \"pro\"\n",
    "\n",
    "################################\n",
    "###ARCGIS DETAILS FOR UPDATES###\n",
    "################################\n",
    "#URL TO SD COUNTY'S COVID-19 FEATURE SERVICE\n",
    "sd_dashboard_service = 'https://services1.arcgis.com/1vIhDJwtG5eNmiqX/ArcGIS/rest/services/CovidDashUpdate/FeatureServer'\n",
    "\n",
    "#feature layer ID to overwrite\n",
    "feature_layer = \"65333d10997d4eb7bf921e11472ae35d\"\n",
    "feature_layer2 = '2a2645b5f569461d916122c3e16d96f3'\n",
    "\n",
    "#csv to append to feature_layer2\n",
    "append_csv = '49be034d6b7a406ca291cb44e94e1be1'\n",
    "\n",
    "#map IDs for symbology update\n",
    "#heatmaps\n",
    "hm1 = \"3bb63e3ff08243cebf465919191863a0\"\n",
    "hm2 = \"2c8a7e1fe2114bfea6317ae4c3268514\"\n",
    "\n",
    "#rates maps\n",
    "rc1 = \"81ff6fce8f7840f8818ae1651db49fc7\"\n",
    "rc2 = \"f0d7afc488ae4d048c2413159ab70921\"\n",
    "rc3 = \"a24f481d8e954411a40d6b9a18465f86\"\n",
    "rc4 = \"a9fe5732f5ac4ac395f66bdd2275d2ec\"\n",
    "\n",
    "#confirmed cases map\n",
    "cc = \"763a114f5f114139af5517ac4c785bd8\"\n",
    "\n",
    "# seven day data and maps\n",
    "seven_layer = 'f6558646808b4b88ba1d77e984b9f7e8'\n",
    "seven_map = 'cd2e0028e1f049e7bd268d06c26cfe22'\n",
    "seven_map_mobile = 'ee46036487c3491caf69209d2a67940c'\n",
    "\n",
    "#urls for dashboards (to verify)\n",
    "heatmap_dash = 'https://arcg.is/1br8v9'\n",
    "rates_dash = 'https://arcg.is/0Gy0mj'\n",
    "cumulative_dash = 'https://arcg.is/1zXq1m'\n",
    "cumulative_mobile = 'https://arcg.is/1WLnjG'\n",
    "seven_dash = 'https://experience.arcgis.com/experience/a630917e020440ba9a598bf1c32b7a74'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CONNECT TO ARCGIS ACCOUNT\n",
    "Reference for authentication schemes: https://developers.arcgis.com/python/guide/working-with-different-authentication-schemes/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "gis = GIS(pro)\n",
    "\n",
    "#gis = GIS(portal, username, password)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### COLLECT AND FORMAT COVID-19 DATA FROM SD COUNTY'S FEATURE SERVICE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Retrieve the current layer from SDCounty dashboard, convert to a DF, convert to 4326, and transform to a DF by zip codes (using Pivot), resulting in a wide table\n",
    "db_item = FeatureLayerCollection(sd_dashboard_service)\n",
    "\n",
    "# convert the layer with counts by zips into a df, in 4326\n",
    "raw = db_item.layers[0].query(out_sr = 4326, return_geometry=True).sdf\n",
    "\n",
    "# create the Lat and Lon columns in this df.\n",
    "raw['Latitude'] = np.nan\n",
    "raw['Longitude'] = np.nan\n",
    "\n",
    "for i, row in raw.iterrows():\n",
    "    temp = raw['SHAPE'][i]\n",
    "    lat = temp['y']\n",
    "    lon = temp['x']\n",
    "    raw['Latitude'][i] = lat\n",
    "    raw['Longitude'][i] = lon\n",
    "    \n",
    "# generate better time stamps\n",
    "raw['Date'] = raw['UpdateDate']\n",
    "raw['Date'] = raw['Date'].dt.strftime(\"%m/%d/%y\")\n",
    "raw = raw.drop_duplicates(['ZipText','Date'])\n",
    "    \n",
    "# generate a wide table for the current file, with a column for every date\n",
    "wide_df = raw.pivot(index='ZipText', columns='Date', values='Case_Count')\n",
    "wide_df['Zipcode'] = wide_df.index\n",
    "\n",
    "#GET CURRENT DATE FOR EXPORTED CSVs, CASE RATE AND INCREASE CALCULATIONS\n",
    "least_recent_date = raw['UpdateDate'].min()\n",
    "least_recent_date = least_recent_date.strftime(\"%m/%d/%y\")\n",
    "most_recent_date = raw['UpdateDate'].max()\n",
    "most_recent_date = most_recent_date.strftime(\"%m/%d/%y\")\n",
    "print(most_recent_date)\n",
    "\n",
    "#output wide table as CSV for records\n",
    "wide_df.to_csv(wide_df_path) #.format(most_recent_date.replace('/', '')))\n",
    "\n",
    "wide = pd.read_csv(wide_df_path) #.format(most_recent_date.replace('/', '')))\n",
    "\n",
    "#convert zipcode column to string type\n",
    "for i, row in wide.iterrows():\n",
    "    temp = str(wide['Zipcode'][i])\n",
    "    wide['Zipcode'][i] = temp\n",
    "    \n",
    "del wide['ZipText'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#format and export wide df to dropbox\n",
    "wide_dropbox = wide['Zipcode'].reset_index()\n",
    "wide_dropbox = wide_dropbox.merge(wide, on = 'Zipcode')\n",
    "wide_dropbox = wide_dropbox.fillna(0)\n",
    "wide_dropbox = wide_dropbox.query('Zipcode > 0')\n",
    "wide_dropbox = wide_dropbox.astype(int)\n",
    "del wide_dropbox['index']\n",
    "del wide_dropbox['Unnamed: 1']\n",
    "wide_dropbox.to_csv(dropbox_cumulative.format(most_recent_date.replace('/','')), index=False)\n",
    "wide_dropbox"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ASSIGN ZIP COORDS TO DF FOR LATER USE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#COORDS\n",
    "#get set of zip code coordinates to add to update df\n",
    "coords = raw[['ZipText', 'Latitude', 'Longitude']]\n",
    "coords = coords.rename(columns={'ZipText':'Zipcode'})\n",
    "coords = coords.drop_duplicates(['Zipcode'])\n",
    "\n",
    "#convert zipcode column to numeric for merging\n",
    "coords['Zipcode'] = pd.to_numeric(coords['Zipcode'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SAVE SD COUNTY COVID-19 PDF FILES TO DROPBOX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#specify date (folder name) if different than current feature service date\n",
    "#most_recent_date = '12/23/20'\n",
    "\n",
    "#specify directory for new folder\n",
    "pdf_path = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/{}20'.format(most_recent_date.replace('/', '-'))\n",
    "\n",
    "#create folder if it doesn't exist\n",
    "if not os.path.exists(pdf_path):\n",
    "    os.makedirs(pdf_path)\n",
    "\n",
    "#base url for pdfs\n",
    "url = 'https://www.sandiegocounty.gov/content/dam/sdc/hhsa/programs/phs/Epidemiology/'\n",
    "\n",
    "#pdf names\n",
    "pdfs = ['COVID-19%20Percentage%20Positive.pdf', 'COVID-19%20Cases%20by%20Date%20of%20Illness%20Onset.pdf', \n",
    "        'COVID-19%20Daily%20Update_City%20of%20Residence.pdf', 'COVID-19%20City%20of%20Residence_MAP.pdf', \n",
    "        'COVID-19%20Race%20and%20Ethnicity%20Summary.pdf', 'COVID-19%20Summary%20of%20Cases%20by%20Zip%20Code.pdf', \n",
    "        'COVID-19%20Hospitalizations%20by%20Date%20Admitted.pdf', 'COVID-19%20Hospitalizations%20Summary_ALL.pdf', \n",
    "        'COVID-19%20Deaths%20by%20Date%20of%20Death.pdf', 'COVID-19%20Deaths%20by%20Demographics.pdf', \n",
    "        'COVID-19_Daily_Status_Update.pdf', 'COVID-19%20Watch.pdf', 'Summary_County_of_San_Diego_Supported_Tests_by_Race_Ethnicity.pdf',\n",
    "        'Summary_of_All_Tests_Reported_by_Race_Ethnicity.pdf', 'Summary_of_All_Tests_Reported_by_Zip_Code_of_Residence.pdf', \n",
    "        'Summary_Tests_Among_San_Diego_County_Residents_by_Race_Ethnicity.pdf']\n",
    "\n",
    "#for each pdf in list, get and save\n",
    "for i in range(len(pdfs)):\n",
    "    response = requests.get(url + pdfs[i], stream=True)\n",
    "\n",
    "    with open(pdf_path + '/' + pdfs[i].replace('%20', ' '), 'wb') as f:\n",
    "        f.write(response.content)\n",
    "\n",
    "#pdf with a different url\n",
    "response = requests.get(url + '/covid19/MediaBriefingSlides/mediaBriefingSlides.pdf', stream=True)\n",
    "with open(pdf_path + '/' + 'mediaBriefingSlides.pdf', 'wb') as f:\n",
    "        f.write(response.content)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MERGE WIDE DF WITH COORDINATES DF AND COMMUNITY/POPULATION CSV DATA\n",
    "### SUBSET DF WITH ONLY COLUMNS FOR HDMA RATES/PERCENTS FEATURE LAYER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#import additional needed zip info\n",
    "zips_info = pd.read_csv(zips_info_path)\n",
    "\n",
    "for i, row in zips_info.iterrows():\n",
    "    temp = str(zips_info['Zipcode'][i])\n",
    "    zips_info['Zipcode'][i] = temp\n",
    "\n",
    "#merge with coords to add lat and lon\n",
    "zips_info = zips_info.merge(coords, on=\"Zipcode\")\n",
    "\n",
    "#merge wide df with coords and extra zip code info\n",
    "wide = wide.merge(zips_info, on=\"Zipcode\")\n",
    "\n",
    "#create new df for use feature layer overwrite\n",
    "cols = ['Zipcode', 'Community', 'Latitude', 'Longitude', '2018_population']\n",
    "df = wide[cols]\n",
    "\n",
    "df['Date'] = most_recent_date\n",
    "\n",
    "df['Confirmed Cases'] = wide[most_recent_date]\n",
    "df['Confirmed Cases'] = df['Confirmed Cases'].fillna(0)\n",
    "df['Confirmed Cases'] = df['Confirmed Cases'].astype(int)\n",
    "\n",
    "df['Rate Per 100K'] = (df['Confirmed Cases']/df['2018_population']*100000).round(2)\n",
    "\n",
    "df = df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TEMPORARY DF TO CALCULATE CASE INCREASE AND RATES OF CHANGE, MERGE TO MAIN DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#CREATE DF\n",
    "date_df = wide[['Zipcode', wide.columns[-13], wide.columns[-12], wide.columns[-11], wide.columns[-10], wide.columns[-9], wide.columns[-8], wide.columns[-7], wide.columns[-6]]]\n",
    "date_df = date_df.fillna(0)\n",
    "#date_df.head()\n",
    "\n",
    "#CREATE COLUMN FOR CASE INCREASES AND CALCULATE\n",
    "date_df['Daily Increased'] = 0\n",
    "\n",
    "for i, row in date_df.iterrows():\n",
    "    day1 = int(date_df.iloc[:,-2][i])\n",
    "    day2 = int(date_df.iloc[:,-3][i])\n",
    "    date_df['Daily Increased'][i] = (day1-day2)\n",
    "\n",
    "#CREATE COLUMN FOR DAILY CHANGE RATE AND CALCULATE\n",
    "date_df['Daily Change Rate*1000'] = 0.0\n",
    "\n",
    "for i, row in date_df.iterrows():\n",
    "    day1 = int(date_df.iloc[:,-3][i])\n",
    "    day2 = int(date_df.iloc[:,-4][i])\n",
    "    if(day2 != 0):\n",
    "        date_df['Daily Change Rate*1000'][i] = round(((day1 - day2)/day2)*1000, 2)\n",
    "\n",
    "#CREATE COLUMN FOR 7 DAY ROLLING RATE OF CHANGE AND CALCULATE\n",
    "date_df['7 Days Rolling Change*1000'] = 0.0\n",
    "\n",
    "rolling_rate_day_list_cols = [[-4,-5],[-5,-6],[-6,-7],[-7,-8],[-8,-9],[-9,-10],[-10,-11]]\n",
    "\n",
    "for i, row in date_df.iterrows():\n",
    "    all_days=0\n",
    "    \n",
    "    for x in range(len(rolling_rate_day_list_cols)):\n",
    "        day1 = int(date_df.iloc[:,rolling_rate_day_list_cols[x][0]][i])\n",
    "        day2 = int(date_df.iloc[:,rolling_rate_day_list_cols[x][1]][i])\n",
    "\n",
    "        if(day2 != 0):\n",
    "            one_day = ((day1 - day2)/day2)*1000\n",
    "            all_days = all_days + one_day\n",
    "           \n",
    "    date_df['7 Days Rolling Change*1000'][i] = round(all_days/len(rolling_rate_day_list_cols), 2)\n",
    "    \n",
    "\n",
    "# create column for 7 day case increase and calculate\n",
    "date_df['7 Day Case Increase'] = 0\n",
    "\n",
    "for i, row in date_df.iterrows(): \n",
    "    seven_change = date_df.iloc[:,8][i] - date_df.iloc[:,1][i]\n",
    "    date_df['7 Day Case Increase'][i] = int(seven_change)\n",
    "\n",
    "#MERGE NEW COLUMNS WITH MAIN DF\n",
    "date_df_subset = date_df[['Zipcode', 'Daily Increased', 'Daily Change Rate*1000', '7 Days Rolling Change*1000', '7 Day Case Increase']]\n",
    "\n",
    "df =df.merge(date_df_subset, on='Zipcode')\n",
    "#print(len(df))\n",
    "#df.head()\n",
    "\n",
    "#SUBSET DATA TO POPULATION >= 5000 AND CONFIRMED CASES >= 10\n",
    "df = df[df['2018_population'] >= 5000]\n",
    "df = df[df['Confirmed Cases'] >= 10]\n",
    "#print(len(df))\n",
    "#df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CALCULATE NEW/CUMULATIVE CASE PERCENTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get cumulative case and daily increase totals for county\n",
    "sum_confirmed = 0\n",
    "sum_daily = 0\n",
    "\n",
    "for i, row in df.iterrows():\n",
    "    sum_confirmed = sum_confirmed + df['Confirmed Cases'][i]\n",
    "    sum_daily = sum_daily + df['Daily Increased'][i]\n",
    "    \n",
    "#add new columns and calculate percent of total and percent of daily new for each zip code\n",
    "df['percent_total'] = (df['Confirmed Cases']/sum_confirmed)*100\n",
    "df['percent_daily'] = (df['Daily Increased']/sum_daily)*100\n",
    "\n",
    "#round columns to 2 decimal places\n",
    "df['percent_total'] = df['percent_total'].round(2)\n",
    "df['percent_daily'] = df['percent_daily'].round(2)\n",
    "\n",
    "#no decimals for zipcode column\n",
    "df['Zipcode'] = df['Zipcode'].apply(np.int64)\n",
    "\n",
    "#df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### OUTPUT CSV FOR RECORDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#save as csv files\n",
    "df.to_csv(path_out, index = False) \n",
    "df.to_csv(path_out2.format(most_recent_date.replace('/','')), index = False)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPDATE MAP SYMBOLOGY FOR RATES AND HEATMAP DASHBOARDS\n",
    "\n",
    "Reference: https://community.esri.com/groups/arcgis-python-api/blog/2019/04/09/updating-layer-symbology-with-the-arcgis-api-for-python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FEATURE LAYER OVERWRITE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get feature layer containing updated data for maps associated with the COVID-19 dashboards\n",
    "layer = gis.content.get(feature_layer)\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)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FUNCTIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_map (map_id):\n",
    "    '''\n",
    "    GET MAP DATA FOR SYMBOLOGY CHANGES\n",
    "    '''\n",
    "    \n",
    "    m = gis.content.get(map_id)\n",
    "\n",
    "    data = m.get_data()\n",
    "    \n",
    "    #Include the below line for prettified JSON\n",
    "    #print(json.dumps(data, indent=4, sort_keys=True))\n",
    "\n",
    "    print(m)\n",
    "    \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",
    "    \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",
    "    return update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FIND MAX/MIN VALUES FOR MAP SYMBOLOGY CHANGES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_confirmed = df['Confirmed Cases'].max()\n",
    "\n",
    "max_increased = df['Daily Increased'].max()\n",
    "max_decreased = 0 - max_increased\n",
    "\n",
    "min_rate100k = df['Rate Per 100K'].min()\n",
    "max_rate100k = df['Rate Per 100K'].max()\n",
    "\n",
    "min_7dayrate = df['7 Days Rolling Change*1000'].min()\n",
    "max_7dayrate = df['7 Days Rolling Change*1000'].max()\n",
    "\n",
    "max_7day = df['7 Day Case Increase'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RATES OF CHANGE DASHBOARD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rates map1: confirmed cases\n",
    "\n",
    "#get map data\n",
    "rc1_data = get_map(rc1)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX CONFIRMED\n",
    "rc1_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_confirmed.item()\n",
    "rc1_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_confirmed.item()\n",
    "\n",
    "#MAX CONFIRMED\n",
    "rc1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_confirmed.item()\n",
    "rc1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_confirmed.item()\n",
    "\n",
    "#update map to save changes\n",
    "rc1_update = update_map(rc1, rc1_data)\n",
    "rc1_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rates map2: daily case increases\n",
    "\n",
    "#get map data\n",
    "rc2_data = get_map(rc2)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX DAILY INCREASE\n",
    "rc2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_increased.item()\n",
    "rc2_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_increased.item()\n",
    "\n",
    "#MAX DAILY INCREASE\n",
    "rc2_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_increased.item()\n",
    "rc2_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_increased.item()\n",
    "\n",
    "#update map to save changes\n",
    "rc2_update = update_map(rc2, rc2_data)\n",
    "rc2_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rates map3: cases per 100k residents\n",
    "\n",
    "#get map data\n",
    "rc3_data = get_map(rc3)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX RATE PER 100K\n",
    "rc3_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_rate100k.item()\n",
    "rc3_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_rate100k.item()\n",
    "\n",
    "#MIN RATE PER 100K\n",
    "rc3_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = min_rate100k.item()\n",
    "rc3_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = min_rate100k.item()\n",
    "\n",
    "#MAX RATE PER 100K\n",
    "rc3_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_rate100k.item()\n",
    "rc3_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_rate100k.item()\n",
    "\n",
    "#MIN RATE PER 100K\n",
    "rc3_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = min_rate100k.item()\n",
    "rc3_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = min_rate100k.item()\n",
    "\n",
    "#update map to save changes\n",
    "rc3_update = update_map(rc3, rc3_data)\n",
    "rc3_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rates map4: 7 day rolling rate of change\n",
    "\n",
    "#get map data\n",
    "rc4_data = get_map(rc4)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX 7 DAY RATE\n",
    "rc4_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7dayrate.item()\n",
    "rc4_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7dayrate.item()\n",
    "\n",
    "#MIN 7 DAY RATE\n",
    "rc4_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = min_7dayrate.item()\n",
    "rc4_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = min_7dayrate.item()\n",
    "\n",
    "#MAX 7 DAY RATE\n",
    "rc4_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7dayrate.item()\n",
    "rc4_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7dayrate.item()\n",
    "\n",
    "#MIN 7 DAY RATE\n",
    "rc4_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = min_7dayrate.item()\n",
    "rc4_data['operationalLayers'][3]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = min_7dayrate.item()\n",
    "\n",
    "#update map to save changes\n",
    "rc4_update = update_map(rc4, rc4_data)\n",
    "rc4_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HEATMAPS DASHBOARD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#heatmap1: daily case increases\n",
    "\n",
    "#get map data\n",
    "hm1_data = get_map(hm1)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#DAILY INCREASED POINTS\n",
    "hm1_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_increased.item()\n",
    "hm1_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_increased.item()\n",
    "\n",
    "#DAILY DECREASED POINTS\n",
    "hm1_data['operationalLayers'][8]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = max_decreased.item()\n",
    "hm1_data['operationalLayers'][8]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = max_decreased.item()\n",
    "\n",
    "#update map to save changes\n",
    "hm1_update = update_map(hm1, hm1_data)\n",
    "hm1_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#heatmap2: 7 day rolling rate of change\n",
    "\n",
    "#get map data\n",
    "hm2_data = get_map(hm2)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX RATE\n",
    "hm2_data['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7dayrate.item()\n",
    "hm2_data['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7dayrate.item()\n",
    "\n",
    "#MIN RATE\n",
    "hm2_data['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['minSliderValue'] = min_7dayrate.item()\n",
    "hm2_data['operationalLayers'][7]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['minDataValue'] = min_7dayrate.item()\n",
    "\n",
    "#update map to save changes\n",
    "hm2_update = update_map(hm2, hm2_data)\n",
    "hm2_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPDATE CUMULATIVE CASE DASHBOARD"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### APPEND DATA TO CUMULATIVE CASES FEATURE LAYER\n",
    "Reference: https://developers.arcgis.com/python/guide/appending-features/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get feature layer containing updated data for maps associated with the CUMULATIVE COVID-19 dashboard\n",
    "layer2 = gis.content.get(feature_layer2)\n",
    "layer2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#List all the current fields in the layer so you can use one as a field template.\n",
    "cum_covid_lyr = layer2.layers[0]\n",
    "cum_covid_lyr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#reformat date for use in appending: append_source = column name in csv, append_field = column name in feature layer, append_alias = column alias\n",
    "split = most_recent_date.split('/')\n",
    "\n",
    "append_source = ''\n",
    "\n",
    "for i in range(len(split)):\n",
    "    if(split[i][0] is '0'):\n",
    "        split[i] = split[i][1:]\n",
    "    if(i == 0):\n",
    "        append_source += split[i]\n",
    "    elif(i == (len(split)-1)): \n",
    "        append_source += '_' + split[i]\n",
    "    else:\n",
    "        append_source += '_' + split[i]\n",
    "\n",
    "append_field = 'F' + append_source + '20'\n",
    "append_alias = append_source.replace('_','/') + '20'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a dictionary from a deep copy of a field in the feature layer, and update the values of this dictionary to reflect a new field.\n",
    "new_field = dict(deepcopy(cum_covid_lyr.properties.fields[5]))\n",
    "new_field['name'] = append_field\n",
    "new_field['alias'] = append_alias\n",
    "new_field['length'] = \"10\"\n",
    "print(new_field)\n",
    "\n",
    "#Update feature layer definition with the new field using the add_to_definition() method.\n",
    "field_list = [new_field]\n",
    "cum_covid_lyr.manager.add_to_definition({\"fields\":field_list})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#only need to add index to 'Zipcode' once - cell kept for reference\n",
    "\n",
    "#Add a unique index to the new attribute field, needed to append\n",
    "#flds = [f.fields.lower() for f in cum_covid_lyr.properties.indexes if f.isUnique]\n",
    "\n",
    "#for fld in cum_covid_lyr.properties.fields:\n",
    "#    if fld.name.lower() in flds:\n",
    "#        print(f\"{fld.name:30}{fld.type:25}isUnique\")\n",
    "#    else:\n",
    "#        print(f\"{fld.name:30}{fld.type:25}\")\n",
    "\n",
    "#Create a copy of one index, then edit it to reflect values for a new index. Then add that to the layer definition.\n",
    "#name_idx = dict(deepcopy(cum_covid_lyr.properties['indexes'][0]))\n",
    "#name_idx['name'] = 'Zipcode'\n",
    "#name_idx['fields'] = 'Zipcode'\n",
    "#name_idx['isUnique'] = True\n",
    "#name_idx['description'] = 'index_name'\n",
    "#name_idx\n",
    "\n",
    "#index_list = [name_idx]\n",
    "#cum_covid_lyr.manager.add_to_definition({\"indexes\":index_list})\n",
    "\n",
    "#Verify the index was added\n",
    "#layer2 = gis.content.get(feature_layer2)\n",
    "#layer2\n",
    "\n",
    "#flds = [f.fields.lower() for f in cum_covid_lyr.properties.indexes if f.isUnique]\n",
    "\n",
    "#for fld in cum_covid_lyr.properties.fields:\n",
    "#    if fld.name.lower() in flds:\n",
    "#        print(f\"{fld.name:30}{fld.type:25}isUnique\")\n",
    "#    else:\n",
    "#        print(f\"{fld.name:30}{fld.type:25}\")      "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#update csv item to contain new date column for addition to the feature layer\n",
    "append_df2 = wide_df[['Zipcode', '{}'.format(most_recent_date)]]\n",
    "append_df2 = append_df2.rename(columns = {'{}'.format(most_recent_date): '{}'.format(append_source)})\n",
    "\n",
    "append_df2 = append_df2[append_df2.index.notnull()]\n",
    "append_df2 = append_df2.fillna(0)\n",
    "append_df2['{}'.format(append_source)] = append_df2['{}'.format(append_source)].astype(int)\n",
    "append_df2.to_csv(append_csv_path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "append_csv_item = gis.content.get(append_csv)\n",
    "#append_csv_item\n",
    "append_csv_item.update({}, append_csv_path)\n",
    "\n",
    "#get *append_csv_info* when appending a new column for source_info\n",
    "append_csv_info = gis.content.analyze(item=append_csv, file_type='csv', location_type='none')\n",
    "#append_csv_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#append new date column to feature layer from csv item\n",
    "cum_covid_lyr.append(item_id= append_csv,\n",
    "                      upload_format = 'csv',\n",
    "                      field_mappings = [{\"name\":\"{}\".format(append_field), \"source\":\"{}\".format(append_source)},\n",
    "                                        {\"name\":\"Zipcode\", \"source\":\"Zipcode\"}],\n",
    "                      source_info = append_csv_info['publishParameters'],\n",
    "                      update_geometry=False,\n",
    "                      append_fields=[\"{}\".format(append_field), \"Zipcode\"],\n",
    "                      skip_inserts=True,\n",
    "                      upsert_matching_field=\"Zipcode\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MODIFY CONFIRMED CASES WEB MAP SYMBOLOGY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#confirmed cases map\n",
    "\n",
    "#get map data\n",
    "cc_data = get_map(cc)\n",
    "\n",
    "#set symbol to new date field, adjust max symbology\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['field'] = append_field\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['field'] = append_field\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_confirmed.item()\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_confirmed.item()\n",
    "\n",
    "#set labeling to new date field\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['labelingInfo'][0]['labelExpressionInfo']['expression'] = '$feature[\"{}\"]'.format(append_field)\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['labelingInfo'][0]['labelExpressionInfo']['value'] = ('{' + append_field + '}')\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['labelingInfo'][0]['fieldInfos'][0]['fieldName'] = append_field\n",
    "\n",
    "#set filter to new date\n",
    "cc_data['operationalLayers'][2]['layerDefinition']['definitionExpression'] = ('{} > 0'.format(append_field))\n",
    "\n",
    "#adjust last date in popup\n",
    "new_date = cc_data['operationalLayers'][2]['popupInfo']['fieldInfos'][-1].copy()\n",
    "new_date['fieldName'] = append_field\n",
    "new_date['label'] = append_alias\n",
    "cc_data['operationalLayers'][2]['popupInfo']['fieldInfos'][-1]['visible'] = False\n",
    "cc_data['operationalLayers'][2]['popupInfo']['fieldInfos'].append(new_date)\n",
    "#cc_data['operationalLayers'][2]['popupInfo']['fieldInfos']\n",
    "\n",
    "#add new date to popup chart\n",
    "popup_chart=cc_data['operationalLayers'][2]['popupInfo']['mediaInfos'][0]['value']['fields']\n",
    "popup_chart.append(append_field)\n",
    "cc_data['operationalLayers'][2]['popupInfo']['mediaInfos'][0]['value']['fields'] = popup_chart\n",
    "\n",
    "#update map to save changes\n",
    "cc_update = update_map(cc, cc_data)\n",
    "cc_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# UPDATE 7 DAY DASHBOARD\n",
    "\n",
    "### OVERWRITE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get feature layer containing updated data for maps associated with the COVID-19 dashboards\n",
    "layer = gis.content.get(seven_layer)\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)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MAP SYMBOLOGY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#seven day map\n",
    "\n",
    "#get map data\n",
    "sm_data = get_map(seven_map)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX DAILY INCREASE\n",
    "sm_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7day.item()\n",
    "sm_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7day.item()\n",
    "\n",
    "#update map to save changes\n",
    "sm_update = update_map(seven_map, sm_data)\n",
    "sm_update\n",
    "\n",
    "#seven day map mobile\n",
    "\n",
    "#get map data\n",
    "smm_data = get_map(seven_map_mobile)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX DAILY INCREASE\n",
    "smm_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7day.item()\n",
    "smm_data['operationalLayers'][2]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7day.item()\n",
    "\n",
    "#update map to save changes\n",
    "smm_update = update_map(seven_map_mobile, smm_data)\n",
    "smm_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# VIEW UPDATED DASHBOARDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Open heatmaps dashboard in browser\n",
    "webbrowser.open(heatmap_dash, new=2)\n",
    "\n",
    "#Open rates of change dashboard in browser\n",
    "webbrowser.open(rates_dash, new=2)\n",
    "\n",
    "#open cumulative/growth chart map\n",
    "webbrowser.open(cumulative_dash, new=2)\n",
    "\n",
    "#open seven day cases dash\n",
    "webbrowser.open(seven_dash, new=2)\n",
    "\n",
    "#County-wide information snapshot\n",
    "print(\"Date: {}, Number of Zip Codes: {}, Total Cases: {}, Daily Increase: {}\".format(most_recent_date, len(df), sum_confirmed, sum_daily))\n",
    "#df.head()"
   ]
  },
  {
   "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
}