weekly_covid_vacc_update.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Weekly COVID-19 Vaccine Dashboard Update\n",
    "Center for Human Dynamics in the Mobile Age (HDMA) at San Diego State University <br>\n",
    "\n",
    "Jessica Embury"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### IMPORTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import modules\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",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import webbrowser\n",
    "\n",
    "#ignore pandas slice warning\n",
    "pd.options.mode.chained_assignment = None  # default='warn'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DATE, FILE PATHS, & AGOL IDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "###########################\n",
    "###SET DATA THROUGH DATE###\n",
    "###########################\n",
    "first_date_in_range = '07/07/21' # Change to 1st day in 7-day range using 'MM/DD/YY' format\n",
    "most_recent_date = '07/13/2021'  # Change to data through date using 'MM/DD/YYYY' format\n",
    "max_cases = 14117  # number of max covid cases in any sd zip code\n",
    "\n",
    "##########################\n",
    "###SET PATHS IN AND OUT###\n",
    "##########################\n",
    "#PATHS IN\n",
    "#additional needed zip info - community name and population\n",
    "zips_info_path = './data/zip_coords_pop.csv'\n",
    "\n",
    "#paths out\n",
    "path_out = './data/sd_zip_vaccines.csv' #feature overwrite\n",
    "path_out2 = './data/sd_zip_vaccines_alldates.csv' # date columns\n",
    "path_out3 = '../covid_data/vaccines/sd_zip_vaccines_{}.csv'.format(most_recent_date.replace('/', '')) # overwrite data archive\n",
    "path_out4 = './data/sd_zip_vaccines_7d.csv' #feature overwrite for 7-day layer\n",
    "path_db = 'C:/Users/jesse/Dropbox/Mapping-Vulearable-Pop-Tasks/SD-County-Data/COVID-Data-Share-at-HDMA-Center/Vaccination/SD_Zip_COVID_Vaccine_{}.csv'.format(most_recent_date.replace('/', '')) # dropbox\n",
    "\n",
    "vacc_count_map_id = '3a5a90a0b43b42bb823ab4136b8d58e9'\n",
    "conf_cases_map_id = '9b08e97e950f4cd79743e3a8217d4508'\n",
    "vacc_dash = 'https://arcg.is/15ePOz'\n",
    "\n",
    "vacc_7d_layer_id = 'dc90f581b4914e52b382b086423fb6b7'\n",
    "vacc_7d_map_id = '19e5ea7a388c4eb18c70392b7d8f6a03'\n",
    "vacc_7d_dash = 'https://arcg.is/b4iHr'\n",
    "\n",
    "vacc_hpi_id = '694f95f80e6849608470e5ecfc1448d2'\n",
    "vacc_hpi_dash = 'https://experience.arcgis.com/experience/16370187fbb64720b468483638c02e78/'\n",
    "\n",
    "vacc_hpi_7d_id = '7ed91cc1f7454df6b750c784b7ce70d3'\n",
    "vacc_hpi_7d_dash = 'https://experience.arcgis.com/experience/1cc7b911604e4a1ab3bd0fbe95227b35/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MANUAL COPY PDF TO CSV, THEN FORMAT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113\n",
      "113\n",
      "113\n",
      "113\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip Code</th>\n",
       "      <th>Vacc Count</th>\n",
       "      <th>Rate Per 1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>8,293</td>\n",
       "      <td>537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92069</td>\n",
       "      <td>26,894</td>\n",
       "      <td>640.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91902</td>\n",
       "      <td>12,265</td>\n",
       "      <td>826.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92070</td>\n",
       "      <td>543</td>\n",
       "      <td>**</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>634</td>\n",
       "      <td>373</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Zip Code Vacc Count Rate Per 1000\n",
       "0    91901      8,293           537\n",
       "1    92069     26,894         640.8\n",
       "2    91902     12,265         826.4\n",
       "3    92070        543            **\n",
       "4    91905        634           373"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# save info from cumulative vacc by zip PDF to csv file. Copy/paste data will result in all data in one column.\n",
    "# separate data from raw unformatted csv\n",
    "raw = pd.read_csv('./data/zip_vacc_raw.csv')\n",
    "\n",
    "# create empty DF\n",
    "cols = ['Zip Code', 'Vacc Count', 'Rate Per 1000']\n",
    "t1 = pd.DataFrame(columns = cols)\n",
    "\n",
    "# zip code\n",
    "raw1 = raw.iloc[0::3, :].reset_index()\n",
    "print(len(raw1))\n",
    "# vacc count\n",
    "raw2 = raw.iloc[1::3, :].reset_index()\n",
    "print(len(raw2))\n",
    "# rate\n",
    "raw3 = raw.iloc[2::3, :].reset_index()\n",
    "print(len(raw3))\n",
    "\n",
    "# add raw data to t1 data frame\n",
    "for i in range(113):\n",
    "    temp = [raw1['vacc'][i], raw2['vacc'][i], raw3['vacc'][i]]\n",
    "    t1 = t1.append({'Zip Code': temp[0], 'Vacc Count': temp[1], 'Rate Per 1000': temp[2]}, ignore_index=True)\n",
    "\n",
    "print(len(t1))\n",
    "t1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.int32'>\n",
      "<class 'numpy.float64'>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip Code</th>\n",
       "      <th>Vacc Count</th>\n",
       "      <th>Rate Per 1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>8293</td>\n",
       "      <td>537.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92069</td>\n",
       "      <td>26894</td>\n",
       "      <td>640.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91902</td>\n",
       "      <td>12265</td>\n",
       "      <td>826.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92070</td>\n",
       "      <td>543</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>634</td>\n",
       "      <td>373.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Zip Code  Vacc Count  Rate Per 1000\n",
       "0    91901        8293          537.0\n",
       "1    92069       26894          640.8\n",
       "2    91902       12265          826.4\n",
       "3    92070         543            0.0\n",
       "4    91905         634          373.0"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#format numbers in counts column\n",
    "t1 = t1.fillna(0)\n",
    "\n",
    "for i, row in t1.iterrows():\n",
    "    try:\n",
    "        t1['Vacc Count'][i] = str(t1['Vacc Count'][i].replace(',', '')).replace('***','0')\n",
    "        t1['Vacc Count'][i] = int(t1['Vacc Count'][i])\n",
    "    except:\n",
    "        print(t1['Zip Code'][i], t1['Vacc Count'][i])\n",
    "    \n",
    "    try:\n",
    "        t1['Rate Per 1000'][i] = str(t1['Rate Per 1000'][i].replace('***', '0').replace('**', '0').replace(',','')).replace('*','')\n",
    "        t1['Rate Per 1000'][i] = float(t1['Rate Per 1000'][i])\n",
    "    except:\n",
    "        print(t1['Zip Code'][i], t1['Rate Per 1000'][i])\n",
    "\n",
    "t1['Vacc Count'] = t1['Vacc Count'].astype(int)\n",
    "print(type(t1['Vacc Count'][0]))\n",
    "\n",
    "t1['Rate Per 1000'] = t1['Rate Per 1000'].astype(float)\n",
    "print(type(t1['Rate Per 1000'][0]))\n",
    "\n",
    "t1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>2018_population</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Vacc Count</th>\n",
       "      <th>Rate Per 1000</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>17885</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>8293</td>\n",
       "      <td>537.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92069</td>\n",
       "      <td>San Marcos</td>\n",
       "      <td>49734</td>\n",
       "      <td>33.178965</td>\n",
       "      <td>-117.158328</td>\n",
       "      <td>26894</td>\n",
       "      <td>640.8</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>17375</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>12265</td>\n",
       "      <td>826.4</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92070</td>\n",
       "      <td>Santa Ysabel</td>\n",
       "      <td>879</td>\n",
       "      <td>33.188952</td>\n",
       "      <td>-116.715840</td>\n",
       "      <td>543</td>\n",
       "      <td>0.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>2014</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>634</td>\n",
       "      <td>373.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>92065</td>\n",
       "      <td>Ramona</td>\n",
       "      <td>35628</td>\n",
       "      <td>33.062768</td>\n",
       "      <td>-116.843980</td>\n",
       "      <td>16377</td>\n",
       "      <td>529.9</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>92536</td>\n",
       "      <td>Aguanga</td>\n",
       "      <td>491</td>\n",
       "      <td>33.398676</td>\n",
       "      <td>-116.720547</td>\n",
       "      <td>237</td>\n",
       "      <td>0.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>92066</td>\n",
       "      <td>Ranchita</td>\n",
       "      <td>342</td>\n",
       "      <td>33.193275</td>\n",
       "      <td>-116.490088</td>\n",
       "      <td>134</td>\n",
       "      <td>0.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>92672</td>\n",
       "      <td>San Clemente</td>\n",
       "      <td>4529</td>\n",
       "      <td>33.389143</td>\n",
       "      <td>-117.571496</td>\n",
       "      <td>1259</td>\n",
       "      <td>353.6</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>92067</td>\n",
       "      <td>Rancho Santa Fe</td>\n",
       "      <td>9895</td>\n",
       "      <td>33.018435</td>\n",
       "      <td>-117.224304</td>\n",
       "      <td>7794</td>\n",
       "      <td>906.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>113 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Zip        Community  ...  Rate Per 1000        Date\n",
       "0    91901           Alpine  ...          537.0  07/13/2021\n",
       "1    92069       San Marcos  ...          640.8  07/13/2021\n",
       "2    91902           Bonita  ...          826.4  07/13/2021\n",
       "3    92070     Santa Ysabel  ...            0.0  07/13/2021\n",
       "4    91905        Boulevard  ...          373.0  07/13/2021\n",
       "..     ...              ...  ...            ...         ...\n",
       "108  92065           Ramona  ...          529.9  07/13/2021\n",
       "109  92536          Aguanga  ...            0.0  07/13/2021\n",
       "110  92066         Ranchita  ...            0.0  07/13/2021\n",
       "111  92672     San Clemente  ...          353.6  07/13/2021\n",
       "112  92067  Rancho Santa Fe  ...          906.0  07/13/2021\n",
       "\n",
       "[113 rows x 8 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# supplemental zip code data\n",
    "zips = pd.read_csv(zips_info_path)\n",
    "zips.head()\n",
    "\n",
    "# merge vaccine info with zip code info\n",
    "t1['Zip Code'] = t1['Zip Code'].astype(str)\n",
    "zips['Zip'] = zips['Zip'].astype(str)\n",
    "\n",
    "df = pd.merge(zips, t1, how = 'right', left_on = 'Zip', right_on = 'Zip Code')\n",
    "del df['Zip Code']\n",
    "\n",
    "# add data date\n",
    "df['Date'] = most_recent_date\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113\n",
      "113\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>2/23/2021</th>\n",
       "      <th>2/24/2021</th>\n",
       "      <th>2/25/2021</th>\n",
       "      <th>2/26/2021</th>\n",
       "      <th>2/27/2021</th>\n",
       "      <th>2/28/2021</th>\n",
       "      <th>3/1/2021</th>\n",
       "      <th>3/2/2021</th>\n",
       "      <th>3/3/2021</th>\n",
       "      <th>3/4/2021</th>\n",
       "      <th>3/5/2021</th>\n",
       "      <th>3/6/2021</th>\n",
       "      <th>3/7/2021</th>\n",
       "      <th>3/8/2021</th>\n",
       "      <th>3/9/2021</th>\n",
       "      <th>3/10/2021</th>\n",
       "      <th>3/11/2021</th>\n",
       "      <th>3/12/2021</th>\n",
       "      <th>3/13/2021</th>\n",
       "      <th>3/14/2021</th>\n",
       "      <th>3/15/2021</th>\n",
       "      <th>3/16/2021</th>\n",
       "      <th>3/17/2021</th>\n",
       "      <th>3/18/2021</th>\n",
       "      <th>3/19/2021</th>\n",
       "      <th>3/20/2021</th>\n",
       "      <th>3/21/2021</th>\n",
       "      <th>3/22/2021</th>\n",
       "      <th>3/23/2021</th>\n",
       "      <th>3/24/2021</th>\n",
       "      <th>3/25/2021</th>\n",
       "      <th>3/26/2021</th>\n",
       "      <th>3/27/2021</th>\n",
       "      <th>3/28/2021</th>\n",
       "      <th>3/29/2021</th>\n",
       "      <th>3/30/2021* (No update)</th>\n",
       "      <th>...</th>\n",
       "      <th>5/23/2021</th>\n",
       "      <th>5/24/2021</th>\n",
       "      <th>5/25/2021</th>\n",
       "      <th>5/26/2021</th>\n",
       "      <th>5/27/2021</th>\n",
       "      <th>5/28/2021</th>\n",
       "      <th>5/29/2021</th>\n",
       "      <th>5/30/2021* (No update)</th>\n",
       "      <th>5/31/2021</th>\n",
       "      <th>6/1/2021</th>\n",
       "      <th>6/2/2021</th>\n",
       "      <th>6/3/2021</th>\n",
       "      <th>6/4/2021</th>\n",
       "      <th>6/5/2021</th>\n",
       "      <th>6/6/2021</th>\n",
       "      <th>6/7/2021</th>\n",
       "      <th>6/8/2021</th>\n",
       "      <th>6/9/2021</th>\n",
       "      <th>6/10/2021</th>\n",
       "      <th>6/11/2021</th>\n",
       "      <th>6/12/2021</th>\n",
       "      <th>6/13/2021</th>\n",
       "      <th>6/14/2021</th>\n",
       "      <th>6/15/2021</th>\n",
       "      <th>6/16/2021* (No update)</th>\n",
       "      <th>06/17/2021</th>\n",
       "      <th>06/18/2021</th>\n",
       "      <th>06/19/2021</th>\n",
       "      <th>06/20/2021</th>\n",
       "      <th>06/21/2021</th>\n",
       "      <th>06/22/2021</th>\n",
       "      <th>06/23/2021</th>\n",
       "      <th>06/24/2021</th>\n",
       "      <th>06/25/2021</th>\n",
       "      <th>06/26/2021</th>\n",
       "      <th>06/27/2021</th>\n",
       "      <th>06/28/2021</th>\n",
       "      <th>06/29/2021</th>\n",
       "      <th>07/06/2021</th>\n",
       "      <th>07/13/2021</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>32.805710</td>\n",
       "      <td>-116.695537</td>\n",
       "      <td>3095.0</td>\n",
       "      <td>3167.0</td>\n",
       "      <td>3210.0</td>\n",
       "      <td>3238.0</td>\n",
       "      <td>3263.0</td>\n",
       "      <td>3290.0</td>\n",
       "      <td>3327.0</td>\n",
       "      <td>3358.0</td>\n",
       "      <td>3440</td>\n",
       "      <td>3470</td>\n",
       "      <td>3517</td>\n",
       "      <td>3526</td>\n",
       "      <td>3547</td>\n",
       "      <td>3571</td>\n",
       "      <td>3650</td>\n",
       "      <td>3725</td>\n",
       "      <td>3771</td>\n",
       "      <td>3831</td>\n",
       "      <td>3903</td>\n",
       "      <td>3925</td>\n",
       "      <td>3963</td>\n",
       "      <td>4015</td>\n",
       "      <td>4081</td>\n",
       "      <td>4146</td>\n",
       "      <td>4200</td>\n",
       "      <td>4259</td>\n",
       "      <td>4305</td>\n",
       "      <td>4337</td>\n",
       "      <td>4390</td>\n",
       "      <td>4429</td>\n",
       "      <td>4478</td>\n",
       "      <td>4517</td>\n",
       "      <td>4566</td>\n",
       "      <td>4589</td>\n",
       "      <td>4620</td>\n",
       "      <td>4620</td>\n",
       "      <td>...</td>\n",
       "      <td>7343</td>\n",
       "      <td>7352</td>\n",
       "      <td>7393</td>\n",
       "      <td>7412</td>\n",
       "      <td>7450</td>\n",
       "      <td>7473</td>\n",
       "      <td>7494</td>\n",
       "      <td>7494</td>\n",
       "      <td>7518</td>\n",
       "      <td>7529</td>\n",
       "      <td>7598</td>\n",
       "      <td>7712</td>\n",
       "      <td>7730</td>\n",
       "      <td>7746</td>\n",
       "      <td>7817</td>\n",
       "      <td>7830</td>\n",
       "      <td>7849</td>\n",
       "      <td>7879</td>\n",
       "      <td>7893</td>\n",
       "      <td>7903</td>\n",
       "      <td>7917</td>\n",
       "      <td>7923</td>\n",
       "      <td>7935</td>\n",
       "      <td>7952</td>\n",
       "      <td>7952</td>\n",
       "      <td>7988</td>\n",
       "      <td>7999</td>\n",
       "      <td>8009</td>\n",
       "      <td>8022</td>\n",
       "      <td>8025</td>\n",
       "      <td>8044</td>\n",
       "      <td>8054</td>\n",
       "      <td>8069</td>\n",
       "      <td>8080</td>\n",
       "      <td>8100</td>\n",
       "      <td>8108</td>\n",
       "      <td>8113</td>\n",
       "      <td>8140</td>\n",
       "      <td>8211</td>\n",
       "      <td>8293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92069</td>\n",
       "      <td>San Marcos</td>\n",
       "      <td>33.178965</td>\n",
       "      <td>-117.158328</td>\n",
       "      <td>5478.0</td>\n",
       "      <td>5582.0</td>\n",
       "      <td>5686.0</td>\n",
       "      <td>5764.0</td>\n",
       "      <td>5897.0</td>\n",
       "      <td>6049.0</td>\n",
       "      <td>6212.0</td>\n",
       "      <td>6380.0</td>\n",
       "      <td>6554</td>\n",
       "      <td>6689</td>\n",
       "      <td>6817</td>\n",
       "      <td>6918</td>\n",
       "      <td>7065</td>\n",
       "      <td>7192</td>\n",
       "      <td>7329</td>\n",
       "      <td>7437</td>\n",
       "      <td>7658</td>\n",
       "      <td>7757</td>\n",
       "      <td>7947</td>\n",
       "      <td>7984</td>\n",
       "      <td>8060</td>\n",
       "      <td>8172</td>\n",
       "      <td>8310</td>\n",
       "      <td>8508</td>\n",
       "      <td>8659</td>\n",
       "      <td>8889</td>\n",
       "      <td>9031</td>\n",
       "      <td>9138</td>\n",
       "      <td>9332</td>\n",
       "      <td>9496</td>\n",
       "      <td>9698</td>\n",
       "      <td>9877</td>\n",
       "      <td>10068</td>\n",
       "      <td>10171</td>\n",
       "      <td>10316</td>\n",
       "      <td>10316</td>\n",
       "      <td>...</td>\n",
       "      <td>21529</td>\n",
       "      <td>21563</td>\n",
       "      <td>21718</td>\n",
       "      <td>21858</td>\n",
       "      <td>21961</td>\n",
       "      <td>22058</td>\n",
       "      <td>22165</td>\n",
       "      <td>22165</td>\n",
       "      <td>22271</td>\n",
       "      <td>22303</td>\n",
       "      <td>23130</td>\n",
       "      <td>24196</td>\n",
       "      <td>24286</td>\n",
       "      <td>24377</td>\n",
       "      <td>24585</td>\n",
       "      <td>24660</td>\n",
       "      <td>24782</td>\n",
       "      <td>24975</td>\n",
       "      <td>25083</td>\n",
       "      <td>25140</td>\n",
       "      <td>25194</td>\n",
       "      <td>25232</td>\n",
       "      <td>25334</td>\n",
       "      <td>25429</td>\n",
       "      <td>25429</td>\n",
       "      <td>25555</td>\n",
       "      <td>25608</td>\n",
       "      <td>25683</td>\n",
       "      <td>25717</td>\n",
       "      <td>25745</td>\n",
       "      <td>25825</td>\n",
       "      <td>25894</td>\n",
       "      <td>25970</td>\n",
       "      <td>26043</td>\n",
       "      <td>26111</td>\n",
       "      <td>26143</td>\n",
       "      <td>26174</td>\n",
       "      <td>26231</td>\n",
       "      <td>26505</td>\n",
       "      <td>26894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91902</td>\n",
       "      <td>Bonita</td>\n",
       "      <td>32.671583</td>\n",
       "      <td>-117.015068</td>\n",
       "      <td>4463.0</td>\n",
       "      <td>4531.0</td>\n",
       "      <td>4580.0</td>\n",
       "      <td>4650.0</td>\n",
       "      <td>4712.0</td>\n",
       "      <td>4782.0</td>\n",
       "      <td>4876.0</td>\n",
       "      <td>4968.0</td>\n",
       "      <td>5065</td>\n",
       "      <td>5129</td>\n",
       "      <td>5206</td>\n",
       "      <td>5221</td>\n",
       "      <td>5263</td>\n",
       "      <td>5330</td>\n",
       "      <td>5448</td>\n",
       "      <td>5560</td>\n",
       "      <td>5614</td>\n",
       "      <td>5676</td>\n",
       "      <td>5789</td>\n",
       "      <td>5835</td>\n",
       "      <td>5888</td>\n",
       "      <td>5954</td>\n",
       "      <td>6067</td>\n",
       "      <td>6165</td>\n",
       "      <td>6241</td>\n",
       "      <td>6346</td>\n",
       "      <td>6416</td>\n",
       "      <td>6466</td>\n",
       "      <td>6561</td>\n",
       "      <td>6637</td>\n",
       "      <td>6704</td>\n",
       "      <td>6783</td>\n",
       "      <td>6843</td>\n",
       "      <td>6888</td>\n",
       "      <td>6931</td>\n",
       "      <td>6931</td>\n",
       "      <td>...</td>\n",
       "      <td>10860</td>\n",
       "      <td>10888</td>\n",
       "      <td>10911</td>\n",
       "      <td>10971</td>\n",
       "      <td>10996</td>\n",
       "      <td>11071</td>\n",
       "      <td>11105</td>\n",
       "      <td>11105</td>\n",
       "      <td>11137</td>\n",
       "      <td>11149</td>\n",
       "      <td>11278</td>\n",
       "      <td>11437</td>\n",
       "      <td>11459</td>\n",
       "      <td>11486</td>\n",
       "      <td>11593</td>\n",
       "      <td>11608</td>\n",
       "      <td>11637</td>\n",
       "      <td>11663</td>\n",
       "      <td>11687</td>\n",
       "      <td>11699</td>\n",
       "      <td>11719</td>\n",
       "      <td>11729</td>\n",
       "      <td>11742</td>\n",
       "      <td>11784</td>\n",
       "      <td>11784</td>\n",
       "      <td>11818</td>\n",
       "      <td>11846</td>\n",
       "      <td>11864</td>\n",
       "      <td>11879</td>\n",
       "      <td>11894</td>\n",
       "      <td>11912</td>\n",
       "      <td>11941</td>\n",
       "      <td>11973</td>\n",
       "      <td>12001</td>\n",
       "      <td>12017</td>\n",
       "      <td>12029</td>\n",
       "      <td>12037</td>\n",
       "      <td>12054</td>\n",
       "      <td>12141</td>\n",
       "      <td>12265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92070</td>\n",
       "      <td>Santa Ysabel</td>\n",
       "      <td>33.188952</td>\n",
       "      <td>-116.715840</td>\n",
       "      <td>210.0</td>\n",
       "      <td>213.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>224.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>231.0</td>\n",
       "      <td>234</td>\n",
       "      <td>231</td>\n",
       "      <td>232</td>\n",
       "      <td>232</td>\n",
       "      <td>234</td>\n",
       "      <td>236</td>\n",
       "      <td>240</td>\n",
       "      <td>243</td>\n",
       "      <td>242</td>\n",
       "      <td>244</td>\n",
       "      <td>250</td>\n",
       "      <td>247</td>\n",
       "      <td>252</td>\n",
       "      <td>258</td>\n",
       "      <td>261</td>\n",
       "      <td>261</td>\n",
       "      <td>267</td>\n",
       "      <td>271</td>\n",
       "      <td>274</td>\n",
       "      <td>280</td>\n",
       "      <td>287</td>\n",
       "      <td>290</td>\n",
       "      <td>292</td>\n",
       "      <td>301</td>\n",
       "      <td>303</td>\n",
       "      <td>304</td>\n",
       "      <td>310</td>\n",
       "      <td>310</td>\n",
       "      <td>...</td>\n",
       "      <td>474</td>\n",
       "      <td>474</td>\n",
       "      <td>475</td>\n",
       "      <td>475</td>\n",
       "      <td>476</td>\n",
       "      <td>478</td>\n",
       "      <td>482</td>\n",
       "      <td>482</td>\n",
       "      <td>484</td>\n",
       "      <td>484</td>\n",
       "      <td>492</td>\n",
       "      <td>507</td>\n",
       "      <td>508</td>\n",
       "      <td>509</td>\n",
       "      <td>511</td>\n",
       "      <td>511</td>\n",
       "      <td>513</td>\n",
       "      <td>516</td>\n",
       "      <td>516</td>\n",
       "      <td>517</td>\n",
       "      <td>517</td>\n",
       "      <td>518</td>\n",
       "      <td>521</td>\n",
       "      <td>523</td>\n",
       "      <td>523</td>\n",
       "      <td>529</td>\n",
       "      <td>530</td>\n",
       "      <td>530</td>\n",
       "      <td>531</td>\n",
       "      <td>531</td>\n",
       "      <td>532</td>\n",
       "      <td>532</td>\n",
       "      <td>535</td>\n",
       "      <td>535</td>\n",
       "      <td>536</td>\n",
       "      <td>537</td>\n",
       "      <td>537</td>\n",
       "      <td>538</td>\n",
       "      <td>540</td>\n",
       "      <td>543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>Boulevard</td>\n",
       "      <td>32.718365</td>\n",
       "      <td>-116.305469</td>\n",
       "      <td>221.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>234.0</td>\n",
       "      <td>236</td>\n",
       "      <td>238</td>\n",
       "      <td>246</td>\n",
       "      <td>247</td>\n",
       "      <td>247</td>\n",
       "      <td>244</td>\n",
       "      <td>250</td>\n",
       "      <td>252</td>\n",
       "      <td>250</td>\n",
       "      <td>252</td>\n",
       "      <td>269</td>\n",
       "      <td>269</td>\n",
       "      <td>271</td>\n",
       "      <td>293</td>\n",
       "      <td>297</td>\n",
       "      <td>304</td>\n",
       "      <td>311</td>\n",
       "      <td>311</td>\n",
       "      <td>312</td>\n",
       "      <td>314</td>\n",
       "      <td>317</td>\n",
       "      <td>333</td>\n",
       "      <td>334</td>\n",
       "      <td>336</td>\n",
       "      <td>337</td>\n",
       "      <td>339</td>\n",
       "      <td>341</td>\n",
       "      <td>341</td>\n",
       "      <td>...</td>\n",
       "      <td>530</td>\n",
       "      <td>536</td>\n",
       "      <td>536</td>\n",
       "      <td>539</td>\n",
       "      <td>540</td>\n",
       "      <td>540</td>\n",
       "      <td>542</td>\n",
       "      <td>542</td>\n",
       "      <td>546</td>\n",
       "      <td>546</td>\n",
       "      <td>563</td>\n",
       "      <td>580</td>\n",
       "      <td>585</td>\n",
       "      <td>587</td>\n",
       "      <td>588</td>\n",
       "      <td>588</td>\n",
       "      <td>594</td>\n",
       "      <td>596</td>\n",
       "      <td>598</td>\n",
       "      <td>600</td>\n",
       "      <td>600</td>\n",
       "      <td>600</td>\n",
       "      <td>604</td>\n",
       "      <td>606</td>\n",
       "      <td>606</td>\n",
       "      <td>608</td>\n",
       "      <td>610</td>\n",
       "      <td>610</td>\n",
       "      <td>610</td>\n",
       "      <td>610</td>\n",
       "      <td>612</td>\n",
       "      <td>613</td>\n",
       "      <td>615</td>\n",
       "      <td>615</td>\n",
       "      <td>619</td>\n",
       "      <td>619</td>\n",
       "      <td>619</td>\n",
       "      <td>623</td>\n",
       "      <td>627</td>\n",
       "      <td>634</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 133 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip     Community   Latitude  ...  06/29/2021  07/06/2021  07/13/2021\n",
       "0  91901        Alpine  32.805710  ...        8140        8211        8293\n",
       "1  92069    San Marcos  33.178965  ...       26231       26505       26894\n",
       "2  91902        Bonita  32.671583  ...       12054       12141       12265\n",
       "3  92070  Santa Ysabel  33.188952  ...         538         540         543\n",
       "4  91905     Boulevard  32.718365  ...         623         627         634\n",
       "\n",
       "[5 rows x 133 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prep data for dated column csv\n",
    "df2 = pd.read_csv(path_out2)\n",
    "print(len(df2))\n",
    "df2['Zip'] = df2['Zip'].astype(str)\n",
    "df2 = df2.merge(df[['Zip', 'Vacc Count']], how = \"right\", on = \"Zip\")\n",
    "print(len(df2))\n",
    "df2 = df2.rename(columns = {'Vacc Count': most_recent_date})\n",
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip</th>\n",
       "      <th>7-Day Vacc Count</th>\n",
       "      <th>7-Day Date Range</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>91901</td>\n",
       "      <td>82</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>92069</td>\n",
       "      <td>389</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>91902</td>\n",
       "      <td>124</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92070</td>\n",
       "      <td>3</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>91905</td>\n",
       "      <td>7</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Zip  7-Day Vacc Count     7-Day Date Range\n",
       "0  91901                82  07/07/21 - 07/13/21\n",
       "1  92069               389  07/07/21 - 07/13/21\n",
       "2  91902               124  07/07/21 - 07/13/21\n",
       "3  92070                 3  07/07/21 - 07/13/21\n",
       "4  91905                 7  07/07/21 - 07/13/21"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate 7-day changes\n",
    "df3 = df2[['Zip', df2.columns[-2], df2.columns[-1]]]\n",
    "df3 = df3.fillna(0)\n",
    "df3['7-Day Vacc Count'] = df3[df3.columns[2]].astype(int) - df3[df3.columns[1]].astype(int)\n",
    "\n",
    "# add 7-day date range column\n",
    "df3['7-Day Date Range'] = '{} - {}'.format(first_date_in_range , df2.columns[-1].replace('2021', '21'))\n",
    "\n",
    "del df3[df3.columns[1]]\n",
    "del df3[df3.columns[1]]\n",
    "\n",
    "print(len(df3))\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Zip</th>\n",
       "      <th>Community</th>\n",
       "      <th>2018_population</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Vacc Count</th>\n",
       "      <th>Rate Per 1000</th>\n",
       "      <th>Date</th>\n",
       "      <th>7-Day Vacc Count</th>\n",
       "      <th>7-Day Date Range</th>\n",
       "      <th>7-Day Rate Per 1000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>92065</td>\n",
       "      <td>Ramona</td>\n",
       "      <td>35628</td>\n",
       "      <td>33.062768</td>\n",
       "      <td>-116.843980</td>\n",
       "      <td>16377</td>\n",
       "      <td>529.9</td>\n",
       "      <td>07/13/2021</td>\n",
       "      <td>162</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "      <td>4.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>92536</td>\n",
       "      <td>Aguanga</td>\n",
       "      <td>491</td>\n",
       "      <td>33.398676</td>\n",
       "      <td>-116.720547</td>\n",
       "      <td>237</td>\n",
       "      <td>0.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "      <td>4</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>92066</td>\n",
       "      <td>Ranchita</td>\n",
       "      <td>342</td>\n",
       "      <td>33.193275</td>\n",
       "      <td>-116.490088</td>\n",
       "      <td>134</td>\n",
       "      <td>0.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "      <td>-1</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>92672</td>\n",
       "      <td>San Clemente</td>\n",
       "      <td>4529</td>\n",
       "      <td>33.389143</td>\n",
       "      <td>-117.571496</td>\n",
       "      <td>1259</td>\n",
       "      <td>353.6</td>\n",
       "      <td>07/13/2021</td>\n",
       "      <td>8</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>92067</td>\n",
       "      <td>Rancho Santa Fe</td>\n",
       "      <td>9895</td>\n",
       "      <td>33.018435</td>\n",
       "      <td>-117.224304</td>\n",
       "      <td>7794</td>\n",
       "      <td>906.0</td>\n",
       "      <td>07/13/2021</td>\n",
       "      <td>51</td>\n",
       "      <td>07/07/21 - 07/13/21</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Zip        Community  ...     7-Day Date Range  7-Day Rate Per 1000\n",
       "108  92065           Ramona  ...  07/07/21 - 07/13/21                  4.5\n",
       "109  92536          Aguanga  ...  07/07/21 - 07/13/21                  0.0\n",
       "110  92066         Ranchita  ...  07/07/21 - 07/13/21                  0.0\n",
       "111  92672     San Clemente  ...  07/07/21 - 07/13/21                  0.0\n",
       "112  92067  Rancho Santa Fe  ...  07/07/21 - 07/13/21                  0.0\n",
       "\n",
       "[5 rows x 11 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge 7-day vaccines to main df\n",
    "df = df.merge(df3, how = 'left', on = 'Zip')\n",
    "df.head()\n",
    "\n",
    "# calculate 7 day rates\n",
    "df['7-Day Rate Per 1000'] = 0.0\n",
    "\n",
    "#7d rate per 1000\n",
    "for i, row in df.iterrows():\n",
    "    if df['2018_population'][i] >= 10000:\n",
    "        df['7-Day Rate Per 1000'][i] = round(df['7-Day Vacc Count'][i]/df['2018_population'][i]*1000, 1)\n",
    "\n",
    "print(len(df))\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# save feature overwrite info\n",
    "df.to_csv(path_out, index=False)\n",
    "df.to_csv(path_out3, index=False)\n",
    "df.to_csv(path_out4, index=False)\n",
    "\n",
    "# save dated columns to csv\n",
    "df2.to_csv(path_out2, index=False)\n",
    "df2.to_csv(path_db, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "62150 999.0 897 25.5\n"
     ]
    }
   ],
   "source": [
    "# get max count and rate for map updates\n",
    "max_count = df['Vacc Count'].max()\n",
    "max_rate = df['Rate Per 1000'].max()\n",
    "max_7d = df['7-Day Vacc Count'].max()\n",
    "max_7d_rate = df['7-Day Rate Per 1000'].max()\n",
    "print(max_count, max_rate, max_7d, max_7d_rate)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CONNECT TO AGOL ACCOUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "gis = GIS(\"pro\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FUNCTIONS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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",
    "    #Include the below line for prettified JSON\n",
    "    #print(json.dumps(data, indent=4, sort_keys=True))\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",
    "    # Set the item_properties to include the desired update\n",
    "    properties = {\"text\": json.dumps(data)}\n",
    "    # 'Commit' the updates to the Item\n",
    "    update = m.update(item_properties=properties)\n",
    "    \n",
    "    return update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FEATURE LAYER OVERWRITE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'success': True}"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get feature layer containing updated data for maps associated with the 7-day COVID-19 vaccines\n",
    "layer = gis.content.get(vacc_7d_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_out4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### UPDATE MAP SYMBOLOGY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"SD COVID-19 Vaccination Counts\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TOTAL VACC MAP\n",
    "#get map data\n",
    "vacc1_data = get_map(vacc_count_map_id)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX VACC COUNT\n",
    "vacc1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc1_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "\n",
    "vacc1_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc1_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "\n",
    "#update map to save changes\n",
    "vacc1_update = update_map(vacc_count_map_id, vacc1_data)\n",
    "vacc1_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 Confirmed Cases - vacc dash\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TOTAL COVID MAP\n",
    "#get map data\n",
    "vacc2_data = get_map(conf_cases_map_id)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX VACC COUNT\n",
    "vacc2_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_cases\n",
    "vacc2_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_cases\n",
    "\n",
    "vacc2_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_cases\n",
    "vacc2_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_cases\n",
    "\n",
    "#update map to save changes\n",
    "vacc2_update = update_map(conf_cases_map_id, vacc2_data)\n",
    "vacc2_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"SD COVID-19 Vaccination Counts (7-Day)\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 7 DAY VACC MAP\n",
    "#get map data\n",
    "vacc3_data = get_map(vacc_7d_map_id)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX VACC COUNT\n",
    "vacc3_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc3_data['operationalLayers'][4]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d.item()\n",
    "\n",
    "vacc3_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc3_data['operationalLayers'][5]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d.item()\n",
    "\n",
    "#update map to save changes\n",
    "vacc3_update = update_map(vacc_7d_map_id, vacc3_data)\n",
    "vacc3_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 Vaccines (Total) and Healthy Places Index (HPI)\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TOTAL VACCINE & HPI Dashboard\n",
    "#get map data\n",
    "vacc4_data = get_map(vacc_hpi_id)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX VACC COUNT\n",
    "# [7], [8], [9] --> size only, no color ramp\n",
    "vacc4_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "\n",
    "vacc4_data['operationalLayers'][10]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][10]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "\n",
    "vacc4_data['operationalLayers'][11]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][11]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "\n",
    "#size info\n",
    "vacc4_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_count.item()\n",
    "# color info\n",
    "vacc4_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_rate.item()\n",
    "vacc4_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_rate.item()\n",
    "\n",
    "#size info\n",
    "vacc4_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_count.item()\n",
    "# color info\n",
    "vacc4_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_rate.item()\n",
    "vacc4_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_rate.item()\n",
    "\n",
    "#size info\n",
    "vacc4_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_count.item()\n",
    "vacc4_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_count.item()\n",
    "# color info\n",
    "vacc4_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_rate.item()\n",
    "vacc4_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_rate.item()\n",
    "\n",
    "#update map to save changes\n",
    "vacc4_update = update_map(vacc_hpi_id, vacc4_data)\n",
    "vacc4_update"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Item title:\"COVID-19 Vaccines (7-Day) and Healthy Places Index (HPI)\" type:Web Map owner:jembury_SDSUGeo>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 7-DAY VACCINE & HPI Dashboard\n",
    "#get map data\n",
    "vacc5_data = get_map(vacc_hpi_7d_id)\n",
    "\n",
    "#adjust symbology for graduated points to reflect new max/min\n",
    "#MAX VACC COUNT\n",
    "# [7], [8], [9] --> size only, no color ramp\n",
    "vacc5_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][9]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d.item()\n",
    "\n",
    "vacc5_data['operationalLayers'][10]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][10]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d.item()\n",
    "\n",
    "vacc5_data['operationalLayers'][11]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][11]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d.item()\n",
    "\n",
    "#size info\n",
    "vacc5_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_7d.item()\n",
    "# color info\n",
    "vacc5_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_7d_rate.item()\n",
    "vacc5_data['operationalLayers'][12]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d_rate.item()\n",
    "\n",
    "#size info\n",
    "vacc5_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_7d.item()\n",
    "# color info\n",
    "vacc5_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_7d_rate.item()\n",
    "vacc5_data['operationalLayers'][13]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d_rate.item()\n",
    "\n",
    "#size info\n",
    "vacc5_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][0]['maxSliderValue'] = max_7d.item()\n",
    "vacc5_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][1]['maxDataValue'] = max_7d.item()\n",
    "# color info\n",
    "vacc5_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['authoringInfo']['visualVariables'][1]['maxSliderValue'] = max_7d_rate.item()\n",
    "vacc5_data['operationalLayers'][14]['layerDefinition']['drawingInfo']['renderer']['visualVariables'][0]['maxDataValue'] = max_7d_rate.item()\n",
    "\n",
    "#update map to save changes\n",
    "vacc5_update = update_map(vacc_hpi_7d_id, vacc5_data)\n",
    "vacc5_update"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### VIEW UPDATED DASHBOARD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "webbrowser.open(vacc_dash, new=2)\n",
    "webbrowser.open(vacc_7d_dash, new=2)\n",
    "webbrowser.open(vacc_hpi_dash, new=2)\n",
    "webbrowser.open(vacc_hpi_7d_dash, new=2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ArcGISPro",
   "language": "Python",
   "name": "python3"
  },
  "language_info": {
   "file_extension": ".py",
   "name": "python",
   "version": "3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}