NYC Covid Project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f439f07d-1a28-47a4-9685-84bdcbc20da5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "016b9a7d-177c-48b1-b806-826adc70cffb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Datetime setup and trial\n",
    "\n",
    "''''from datetime import datetime\n",
    "\n",
    "#Date string ingested as \"Mar-20\" (month - 2-digit year)\n",
    "\n",
    "#s= ['3/2020']\n",
    "s = Hospital_by_modzcta['date']\n",
    "d=pd.to_datetime(s, format='%m/%Y')'''\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "id": "906258cc-21cd-42b5-a43f-6cfd4ebbeb06",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Ingest data\n",
    "weekly_hosp_rate_race= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-race.csv\")\n",
    "weekly_hosp_rate_age= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-age.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "b620a26c-afca-4e6b-89e5-f63e31fcdf0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MODZCTA</th>\n",
       "      <th>label</th>\n",
       "      <th>lon</th>\n",
       "      <th>lat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10001</td>\n",
       "      <td>10001, 10118</td>\n",
       "      <td>-73.997137</td>\n",
       "      <td>40.750693</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10002</td>\n",
       "      <td>10002</td>\n",
       "      <td>-73.986176</td>\n",
       "      <td>40.715781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10003</td>\n",
       "      <td>10003</td>\n",
       "      <td>-73.989164</td>\n",
       "      <td>40.731825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10004</td>\n",
       "      <td>10004</td>\n",
       "      <td>-74.013106</td>\n",
       "      <td>40.703675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10005</td>\n",
       "      <td>10005</td>\n",
       "      <td>-74.008861</td>\n",
       "      <td>40.706092</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MODZCTA         label        lon        lat\n",
       "0    10001  10001, 10118 -73.997137  40.750693\n",
       "1    10002         10002 -73.986176  40.715781\n",
       "2    10003         10003 -73.989164  40.731825\n",
       "3    10004         10004 -74.013106  40.703675\n",
       "4    10005         10005 -74.008861  40.706092"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# bring all files of interest here\n",
    "\n",
    "import pandas as pd\n",
    "import requests\n",
    "#modzcta to zcta points with lat and log\n",
    "modzcta_lat_lon= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/covid-maps/main/modzcta-geo/zcta_points.csv\")\n",
    "\n",
    "VariantEPIData=pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/variant-epi-data.csv\")\n",
    "CasesByModZCTA = pd.read_csv('https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/caserate-by-modzcta.csv')\n",
    "\n",
    "cases_sequenced = pd.read_csv('https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/cases-sequenced.csv')\n",
    "                              \n",
    "Tests = pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/tests.csv\")\n",
    "\n",
    "Hospital_by_modzcta= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/hosprate-by-modzcta.csv\")\n",
    "deathrate_by_modzcta= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/deathrate-by-modzcta.csv\")\n",
    "positivecaserate_by_modzcta= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/percentpositive-by-modzcta.csv\")\n",
    "test_by_modzcta= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/testrate-by-modzcta.csv\")\n",
    "variant_classification= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/variant-classification.csv\")\n",
    "cases_sequenced= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/cases-sequenced.csv\")\n",
    "hosp_by_day= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/hosp-by-day.csv\")\n",
    "coverage_by_modzcta_allages= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/covid-vaccine-data/main/people/coverage-by-modzcta-allages.csv\")\n",
    "doses_by_residency_age= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/covid-vaccine-data/main/doses/doses-by-residency-age.csv\")\n",
    "weekly_hosp_rate_race= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-race.csv\")\n",
    "weekly_hosp_rate_age= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-age.csv\")\n",
    "sydromic_data_covid_like_Illness= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/covid-like-illness-by-boro.csv\")\n",
    "\n",
    "\n",
    "modzcta_lat_lon.head(5)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2c42ad6b-b4f3-4220-9db5-89e203500751",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>Case_Rate_Location</th>\n",
       "      <th>Case_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/08/2020</td>\n",
       "      <td>CASERATE_CITY</td>\n",
       "      <td>20.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08/15/2020</td>\n",
       "      <td>CASERATE_CITY</td>\n",
       "      <td>21.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08/22/2020</td>\n",
       "      <td>CASERATE_CITY</td>\n",
       "      <td>20.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>08/29/2020</td>\n",
       "      <td>CASERATE_CITY</td>\n",
       "      <td>21.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/05/2020</td>\n",
       "      <td>CASERATE_CITY</td>\n",
       "      <td>21.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending Case_Rate_Location  Case_Count\n",
       "0  08/08/2020      CASERATE_CITY       20.69\n",
       "1  08/15/2020      CASERATE_CITY       21.91\n",
       "2  08/22/2020      CASERATE_CITY       20.82\n",
       "3  08/29/2020      CASERATE_CITY       21.50\n",
       "4  09/05/2020      CASERATE_CITY       21.71"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Convert Cities to rows and grouped by date sort\n",
    "\n",
    "CasesByModZCTA_1 = CasesByModZCTA.melt(id_vars=[\"week_ending\"],\n",
    "        var_name = \"Case_Rate_Location\", \n",
    "        value_name = \"Case_Count\")\n",
    "CasesByModZCTA_1.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "64a21c63-1c2e-4717-b009-44b58ac50412",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>Case_Rate_Location</th>\n",
       "      <th>Case_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12073</th>\n",
       "      <td>10/09/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>176.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12074</th>\n",
       "      <td>10/16/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>176.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12075</th>\n",
       "      <td>10/23/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>147.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12076</th>\n",
       "      <td>10/30/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>58.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12077</th>\n",
       "      <td>11/06/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>29.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      week_ending Case_Rate_Location  Case_Count\n",
       "12073  10/09/2021              11697      176.85\n",
       "12074  10/16/2021              11697      176.85\n",
       "12075  10/23/2021              11697      147.38\n",
       "12076  10/30/2021              11697       58.95\n",
       "12077  11/06/2021              11697       29.48"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove the first 9 characters from case_rate_location\n",
    "#res_str = str.replace('e', '', 1)  , in case I want to replace\n",
    "\n",
    "CasesByModZCTA_1['Case_Rate_Location'] = CasesByModZCTA_1['Case_Rate_Location'].str[9:]\n",
    "CasesByModZCTA_1.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6a2f223b-d92c-445c-999a-6396aa33f9ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>Case_Rate_Location</th>\n",
       "      <th>Case_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12073</th>\n",
       "      <td>10/09/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>176.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12074</th>\n",
       "      <td>10/16/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>176.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12075</th>\n",
       "      <td>10/23/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>147.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12076</th>\n",
       "      <td>10/30/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>58.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12077</th>\n",
       "      <td>11/06/2021</td>\n",
       "      <td>11697</td>\n",
       "      <td>29.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      week_ending Case_Rate_Location  Case_Count\n",
       "12073  10/09/2021              11697      176.85\n",
       "12074  10/16/2021              11697      176.85\n",
       "12075  10/23/2021              11697      147.38\n",
       "12076  10/30/2021              11697       58.95\n",
       "12077  11/06/2021              11697       29.48"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#store Case_rate location strings into separate DF\n",
    "#if Case_Rate_Location is string then Case_Rate_LocationDF = \n",
    "CasesByModZCTA_1.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "22949131-6546-4927-893f-7fc1dbfebabc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>TOTAL_TESTS</th>\n",
       "      <th>POSITIVE_TESTS</th>\n",
       "      <th>PERCENT_POSITIVE</th>\n",
       "      <th>TOTAL_TESTS_7DAYS_AVG</th>\n",
       "      <th>POSITIVE_TESTS_7DAYS_AVG</th>\n",
       "      <th>PERCENT_POSITIVE_7DAYS_AVG</th>\n",
       "      <th>TOTAL_ANTIGEN_TESTS</th>\n",
       "      <th>TOTAL_ANTIGEN_TESTS_7DAYS_AVG</th>\n",
       "      <th>INCOMPLETE</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>Tests_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>2020</td>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>40</td>\n",
       "      <td>5</td>\n",
       "      <td>0.1250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>2020</td>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-05</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0606</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>2020</td>\n",
       "      <td>2085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-06</td>\n",
       "      <td>88</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0909</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>66</td>\n",
       "      <td>2020</td>\n",
       "      <td>2086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>76</td>\n",
       "      <td>8</td>\n",
       "      <td>0.1053</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>2020</td>\n",
       "      <td>2087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE  TOTAL_TESTS  POSITIVE_TESTS  PERCENT_POSITIVE  \\\n",
       "0 2020-03-03           15               1            0.0667   \n",
       "1 2020-03-04           40               5            0.1250   \n",
       "2 2020-03-05           66               4            0.0606   \n",
       "3 2020-03-06           88               8            0.0909   \n",
       "4 2020-03-07           76               8            0.1053   \n",
       "\n",
       "   TOTAL_TESTS_7DAYS_AVG  POSITIVE_TESTS_7DAYS_AVG  \\\n",
       "0                      0                         0   \n",
       "1                      0                         0   \n",
       "2                      0                         0   \n",
       "3                      0                         0   \n",
       "4                      0                         0   \n",
       "\n",
       "   PERCENT_POSITIVE_7DAYS_AVG  TOTAL_ANTIGEN_TESTS  \\\n",
       "0                         0.0                    0   \n",
       "1                         0.0                    0   \n",
       "2                         0.0                    0   \n",
       "3                         0.0                    0   \n",
       "4                         0.0                    1   \n",
       "\n",
       "   TOTAL_ANTIGEN_TESTS_7DAYS_AVG  INCOMPLETE  dayofyear  year  Tests_ID  \n",
       "0                              0           0         63  2020      2083  \n",
       "1                              0           0         64  2020      2084  \n",
       "2                              0           0         65  2020      2085  \n",
       "3                              0           0         66  2020      2086  \n",
       "4                              0           0         67  2020      2087  "
      ]
     },
     "execution_count": 252,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "Tests['DATE']=pd.to_datetime(Tests['DATE'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "Tests['dayofyear'] = Tests['DATE'].dt.dayofyear\n",
    "Tests['year'] = Tests['DATE'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "Tests['Tests_ID'] = Tests['dayofyear'] + Tests['year']\n",
    "\n",
    "\n",
    "\n",
    "Tests.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "6d62d827-1d3d-4e10-bcc3-50f9533a788f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sequence week</th>\n",
       "      <th>B.1.526 +E484K count</th>\n",
       "      <th>B.1.526 +E484K  percent</th>\n",
       "      <th>B.1.526 no E484K count</th>\n",
       "      <th>B.1.526 no E484K percent</th>\n",
       "      <th>B.1.1.7 count</th>\n",
       "      <th>B.1.1.7 percent</th>\n",
       "      <th>P.1 count</th>\n",
       "      <th>P.1 percent</th>\n",
       "      <th>B.1.427 count</th>\n",
       "      <th>...</th>\n",
       "      <th>B.1.617.2 count</th>\n",
       "      <th>B.1.617.2 percent</th>\n",
       "      <th>B.1.621 count</th>\n",
       "      <th>B.1.621 percent</th>\n",
       "      <th>Other count</th>\n",
       "      <th>Other percent</th>\n",
       "      <th>incomplete</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>VariantEPIData_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>486</td>\n",
       "      <td>91</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-09</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>787</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>2021</td>\n",
       "      <td>2030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-16</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>45</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>331</td>\n",
       "      <td>80</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>2021</td>\n",
       "      <td>2037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-23</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>55</td>\n",
       "      <td>11</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>398</td>\n",
       "      <td>83</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>2021</td>\n",
       "      <td>2044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-30</td>\n",
       "      <td>61</td>\n",
       "      <td>8</td>\n",
       "      <td>124</td>\n",
       "      <td>16</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>540</td>\n",
       "      <td>70</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>2021</td>\n",
       "      <td>2051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Sequence week  B.1.526 +E484K count  B.1.526 +E484K  percent  \\\n",
       "0    2021-01-02                     3                        1   \n",
       "1    2021-01-09                     8                        1   \n",
       "2    2021-01-16                     9                        2   \n",
       "3    2021-01-23                    13                        3   \n",
       "4    2021-01-30                    61                        8   \n",
       "\n",
       "   B.1.526 no E484K count  B.1.526 no E484K percent  B.1.1.7 count  \\\n",
       "0                      32                         6              4   \n",
       "1                      50                         6              8   \n",
       "2                      45                        11             13   \n",
       "3                      55                        11              8   \n",
       "4                     124                        16             23   \n",
       "\n",
       "   B.1.1.7 percent  P.1 count  P.1 percent  B.1.427 count  ...  \\\n",
       "0                1          0            0              1  ...   \n",
       "1                1          0            0              6  ...   \n",
       "2                3          0            0              5  ...   \n",
       "3                2          0            0              1  ...   \n",
       "4                3          0            0             12  ...   \n",
       "\n",
       "   B.1.617.2 count  B.1.617.2 percent  B.1.621 count  B.1.621 percent  \\\n",
       "0                0                  0              0                0   \n",
       "1                0                  0              0                0   \n",
       "2                0                  0              0                0   \n",
       "3                0                  0              0                0   \n",
       "4                0                  0              0                0   \n",
       "\n",
       "   Other count  Other percent  incomplete  dayofyear  year  VariantEPIData_ID  \n",
       "0          486             91         NaN          2  2021               2023  \n",
       "1          787             90         NaN          9  2021               2030  \n",
       "2          331             80         NaN         16  2021               2037  \n",
       "3          398             83         NaN         23  2021               2044  \n",
       "4          540             70         NaN         30  2021               2051  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "VariantEPIData['Sequence week']=pd.to_datetime(VariantEPIData['Sequence week'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "VariantEPIData['dayofyear'] = VariantEPIData['Sequence week'].dt.dayofyear\n",
    "VariantEPIData['year'] = VariantEPIData['Sequence week'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "VariantEPIData['VariantEPIData_ID'] = VariantEPIData['dayofyear'] + VariantEPIData['year']\n",
    "\n",
    "\n",
    "VariantEPIData.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "id": "29a8fe74-7961-4e56-bf43-924e95fb49e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>PCTPOS_CITY</th>\n",
       "      <th>PCTPOS_BX</th>\n",
       "      <th>PCTPOS_BK</th>\n",
       "      <th>PCTPOS_MN</th>\n",
       "      <th>PCTPOS_QN</th>\n",
       "      <th>PCTPOS_SI</th>\n",
       "      <th>PCTPOS_10001</th>\n",
       "      <th>PCTPOS_10002</th>\n",
       "      <th>PCTPOS_10003</th>\n",
       "      <th>...</th>\n",
       "      <th>PCTPOS_11432</th>\n",
       "      <th>PCTPOS_11433</th>\n",
       "      <th>PCTPOS_11434</th>\n",
       "      <th>PCTPOS_11435</th>\n",
       "      <th>PCTPOS_11436</th>\n",
       "      <th>PCTPOS_11691</th>\n",
       "      <th>PCTPOS_11692</th>\n",
       "      <th>PCTPOS_11693</th>\n",
       "      <th>PCTPOS_11694</th>\n",
       "      <th>PCTPOS_11697</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/08/2020</td>\n",
       "      <td>1.25</td>\n",
       "      <td>1.65</td>\n",
       "      <td>1.28</td>\n",
       "      <td>0.90</td>\n",
       "      <td>1.23</td>\n",
       "      <td>1.42</td>\n",
       "      <td>0.75</td>\n",
       "      <td>1.93</td>\n",
       "      <td>0.26</td>\n",
       "      <td>...</td>\n",
       "      <td>1.92</td>\n",
       "      <td>1.47</td>\n",
       "      <td>0.71</td>\n",
       "      <td>1.38</td>\n",
       "      <td>0.88</td>\n",
       "      <td>1.65</td>\n",
       "      <td>1.36</td>\n",
       "      <td>0.85</td>\n",
       "      <td>1.43</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08/15/2020</td>\n",
       "      <td>1.22</td>\n",
       "      <td>1.64</td>\n",
       "      <td>1.23</td>\n",
       "      <td>0.87</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1.43</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.56</td>\n",
       "      <td>0.17</td>\n",
       "      <td>...</td>\n",
       "      <td>2.82</td>\n",
       "      <td>2.63</td>\n",
       "      <td>0.76</td>\n",
       "      <td>1.54</td>\n",
       "      <td>1.21</td>\n",
       "      <td>1.15</td>\n",
       "      <td>1.25</td>\n",
       "      <td>0.84</td>\n",
       "      <td>0.94</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08/22/2020</td>\n",
       "      <td>1.03</td>\n",
       "      <td>1.32</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.77</td>\n",
       "      <td>1.13</td>\n",
       "      <td>1.17</td>\n",
       "      <td>0.37</td>\n",
       "      <td>1.03</td>\n",
       "      <td>0.26</td>\n",
       "      <td>...</td>\n",
       "      <td>1.63</td>\n",
       "      <td>1.15</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1.57</td>\n",
       "      <td>0.84</td>\n",
       "      <td>1.32</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.68</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>08/29/2020</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.53</td>\n",
       "      <td>1.18</td>\n",
       "      <td>0.70</td>\n",
       "      <td>1.05</td>\n",
       "      <td>1.02</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.43</td>\n",
       "      <td>0.34</td>\n",
       "      <td>...</td>\n",
       "      <td>1.20</td>\n",
       "      <td>0.86</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.19</td>\n",
       "      <td>0.82</td>\n",
       "      <td>2.10</td>\n",
       "      <td>0.51</td>\n",
       "      <td>0.82</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/05/2020</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1.02</td>\n",
       "      <td>1.11</td>\n",
       "      <td>0.62</td>\n",
       "      <td>1.03</td>\n",
       "      <td>1.22</td>\n",
       "      <td>1.03</td>\n",
       "      <td>0.52</td>\n",
       "      <td>0.38</td>\n",
       "      <td>...</td>\n",
       "      <td>1.42</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1.26</td>\n",
       "      <td>0.49</td>\n",
       "      <td>1.87</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.98</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 184 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending  PCTPOS_CITY  PCTPOS_BX  PCTPOS_BK  PCTPOS_MN  PCTPOS_QN  \\\n",
       "0  08/08/2020         1.25       1.65       1.28       0.90       1.23   \n",
       "1  08/15/2020         1.22       1.64       1.23       0.87       1.20   \n",
       "2  08/22/2020         1.03       1.32       1.00       0.77       1.13   \n",
       "3  08/29/2020         1.06       1.53       1.18       0.70       1.05   \n",
       "4  09/05/2020         0.95       1.02       1.11       0.62       1.03   \n",
       "\n",
       "   PCTPOS_SI  PCTPOS_10001  PCTPOS_10002  PCTPOS_10003  ...  PCTPOS_11432  \\\n",
       "0       1.42          0.75          1.93          0.26  ...          1.92   \n",
       "1       1.43          0.95          0.56          0.17  ...          2.82   \n",
       "2       1.17          0.37          1.03          0.26  ...          1.63   \n",
       "3       1.02          0.52          0.43          0.34  ...          1.20   \n",
       "4       1.22          1.03          0.52          0.38  ...          1.42   \n",
       "\n",
       "   PCTPOS_11433  PCTPOS_11434  PCTPOS_11435  PCTPOS_11436  PCTPOS_11691  \\\n",
       "0          1.47          0.71          1.38          0.88          1.65   \n",
       "1          2.63          0.76          1.54          1.21          1.15   \n",
       "2          1.15          0.99          1.57          0.84          1.32   \n",
       "3          0.86          1.06          1.19          0.82          2.10   \n",
       "4          0.88          0.56          1.26          0.49          1.87   \n",
       "\n",
       "   PCTPOS_11692  PCTPOS_11693  PCTPOS_11694  PCTPOS_11697  \n",
       "0          1.36          0.85          1.43          2.47  \n",
       "1          1.25          0.84          0.94          0.00  \n",
       "2          0.13          0.00          2.68          1.06  \n",
       "3          0.51          0.82          0.95          1.12  \n",
       "4          0.57          0.37          0.98          3.39  \n",
       "\n",
       "[5 rows x 184 columns]"
      ]
     },
     "execution_count": 254,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "positivecaserate_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "id": "2a16bd56-cc8d-4a2a-a7b1-2226b6dd1a11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>08/08/2020</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>08/15/2020</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08/22/2020</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>08/29/2020</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/05/2020</td>\n",
       "      <td>CITY</td>\n",
       "      <td>0.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending MODZCTA_Location  Percent\n",
       "0  08/08/2020             CITY     1.25\n",
       "1  08/15/2020             CITY     1.22\n",
       "2  08/22/2020             CITY     1.03\n",
       "3  08/29/2020             CITY     1.06\n",
       "4  09/05/2020             CITY     0.95"
      ]
     },
     "execution_count": 255,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "positivecaserate_by_modzcta = positivecaserate_by_modzcta.melt(id_vars =['week_ending'],\n",
    "    var_name=\"MODZCTA_Location\",\n",
    "    value_name = \"Percent\")  \n",
    "\n",
    "\n",
    "\n",
    "#remove the first 9 characters from case_rate_location\n",
    "#res_str = str.replace('e', '', 1)  , in case I want to replace\n",
    "\n",
    "positivecaserate_by_modzcta['MODZCTA_Location'] = positivecaserate_by_modzcta['MODZCTA_Location'].str[7:]\n",
    "positivecaserate_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "id": "993bbc9d-b47c-499b-a7dd-73ae3e20b3db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>Percent</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>positivecaserate_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-08-08</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.25</td>\n",
       "      <td>221</td>\n",
       "      <td>2020</td>\n",
       "      <td>2241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.22</td>\n",
       "      <td>228</td>\n",
       "      <td>2020</td>\n",
       "      <td>2248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-22</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.03</td>\n",
       "      <td>235</td>\n",
       "      <td>2020</td>\n",
       "      <td>2255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-29</td>\n",
       "      <td>CITY</td>\n",
       "      <td>1.06</td>\n",
       "      <td>242</td>\n",
       "      <td>2020</td>\n",
       "      <td>2262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-09-05</td>\n",
       "      <td>CITY</td>\n",
       "      <td>0.95</td>\n",
       "      <td>249</td>\n",
       "      <td>2020</td>\n",
       "      <td>2269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending MODZCTA_Location  Percent  dayofyear  year  positivecaserate_ID\n",
       "0  2020-08-08             CITY     1.25        221  2020                 2241\n",
       "1  2020-08-15             CITY     1.22        228  2020                 2248\n",
       "2  2020-08-22             CITY     1.03        235  2020                 2255\n",
       "3  2020-08-29             CITY     1.06        242  2020                 2262\n",
       "4  2020-09-05             CITY     0.95        249  2020                 2269"
      ]
     },
     "execution_count": 256,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "positivecaserate_by_modzcta['week_ending']=pd.to_datetime(positivecaserate_by_modzcta['week_ending'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "positivecaserate_by_modzcta['dayofyear'] = positivecaserate_by_modzcta['week_ending'].dt.dayofyear\n",
    "positivecaserate_by_modzcta['year'] = positivecaserate_by_modzcta['week_ending'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "positivecaserate_by_modzcta['positivecaserate_ID'] = positivecaserate_by_modzcta['dayofyear'] + positivecaserate_by_modzcta['year']\n",
    "\n",
    "positivecaserate_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "id": "88bf7029-35b9-4ea0-b088-7839596675e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>not_sequenced</th>\n",
       "      <th>sequenced</th>\n",
       "      <th>cases</th>\n",
       "      <th>pct_sequenced</th>\n",
       "      <th>incomplete</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>positivecaserate_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>26786</td>\n",
       "      <td>532</td>\n",
       "      <td>27377</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-09</td>\n",
       "      <td>35421</td>\n",
       "      <td>871</td>\n",
       "      <td>36292</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>2021</td>\n",
       "      <td>2030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-16</td>\n",
       "      <td>32610</td>\n",
       "      <td>415</td>\n",
       "      <td>33084</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16</td>\n",
       "      <td>2021</td>\n",
       "      <td>2037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-23</td>\n",
       "      <td>30058</td>\n",
       "      <td>481</td>\n",
       "      <td>30529</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "      <td>2021</td>\n",
       "      <td>2044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-30</td>\n",
       "      <td>28414</td>\n",
       "      <td>775</td>\n",
       "      <td>29193</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30</td>\n",
       "      <td>2021</td>\n",
       "      <td>2051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending  not_sequenced  sequenced  cases  pct_sequenced  incomplete  \\\n",
       "0  2021-01-02          26786        532  27377              2         NaN   \n",
       "1  2021-01-09          35421        871  36292              2         NaN   \n",
       "2  2021-01-16          32610        415  33084              1         NaN   \n",
       "3  2021-01-23          30058        481  30529              2         NaN   \n",
       "4  2021-01-30          28414        775  29193              3         NaN   \n",
       "\n",
       "   dayofyear  year  positivecaserate_ID  \n",
       "0          2  2021                 2023  \n",
       "1          9  2021                 2030  \n",
       "2         16  2021                 2037  \n",
       "3         23  2021                 2044  \n",
       "4         30  2021                 2051  "
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "cases_sequenced['week_ending']=pd.to_datetime(cases_sequenced['week_ending'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "cases_sequenced['dayofyear'] = cases_sequenced['week_ending'].dt.dayofyear\n",
    "cases_sequenced['year'] = cases_sequenced['week_ending'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "cases_sequenced['positivecaserate_ID'] = cases_sequenced['dayofyear'] + cases_sequenced['year']\n",
    "\n",
    "cases_sequenced.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "id": "86b33ad9-d949-4f76-8460-1a75d55a33b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>TOTAL_TESTS</th>\n",
       "      <th>POSITIVE_TESTS</th>\n",
       "      <th>PERCENT_POSITIVE</th>\n",
       "      <th>TOTAL_TESTS_7DAYS_AVG</th>\n",
       "      <th>POSITIVE_TESTS_7DAYS_AVG</th>\n",
       "      <th>PERCENT_POSITIVE_7DAYS_AVG</th>\n",
       "      <th>TOTAL_ANTIGEN_TESTS</th>\n",
       "      <th>TOTAL_ANTIGEN_TESTS_7DAYS_AVG</th>\n",
       "      <th>INCOMPLETE</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>Tests_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0667</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>2020</td>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>40</td>\n",
       "      <td>5</td>\n",
       "      <td>0.1250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>2020</td>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-05</td>\n",
       "      <td>66</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0606</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>2020</td>\n",
       "      <td>2085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-06</td>\n",
       "      <td>88</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0909</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>66</td>\n",
       "      <td>2020</td>\n",
       "      <td>2086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>76</td>\n",
       "      <td>8</td>\n",
       "      <td>0.1053</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>67</td>\n",
       "      <td>2020</td>\n",
       "      <td>2087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE  TOTAL_TESTS  POSITIVE_TESTS  PERCENT_POSITIVE  \\\n",
       "0 2020-03-03           15               1            0.0667   \n",
       "1 2020-03-04           40               5            0.1250   \n",
       "2 2020-03-05           66               4            0.0606   \n",
       "3 2020-03-06           88               8            0.0909   \n",
       "4 2020-03-07           76               8            0.1053   \n",
       "\n",
       "   TOTAL_TESTS_7DAYS_AVG  POSITIVE_TESTS_7DAYS_AVG  \\\n",
       "0                      0                         0   \n",
       "1                      0                         0   \n",
       "2                      0                         0   \n",
       "3                      0                         0   \n",
       "4                      0                         0   \n",
       "\n",
       "   PERCENT_POSITIVE_7DAYS_AVG  TOTAL_ANTIGEN_TESTS  \\\n",
       "0                         0.0                    0   \n",
       "1                         0.0                    0   \n",
       "2                         0.0                    0   \n",
       "3                         0.0                    0   \n",
       "4                         0.0                    1   \n",
       "\n",
       "   TOTAL_ANTIGEN_TESTS_7DAYS_AVG  INCOMPLETE  dayofyear  year  Tests_ID  \n",
       "0                              0           0         63  2020      2083  \n",
       "1                              0           0         64  2020      2084  \n",
       "2                              0           0         65  2020      2085  \n",
       "3                              0           0         66  2020      2086  \n",
       "4                              0           0         67  2020      2087  "
      ]
     },
     "execution_count": 258,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "Tests['DATE']=pd.to_datetime(Tests['DATE'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "Tests['dayofyear'] = Tests['DATE'].dt.dayofyear\n",
    "Tests['year'] = Tests['DATE'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "Tests['Tests_ID'] = Tests['dayofyear'] + Tests['year']\n",
    "\n",
    "Tests.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "id": "6029778f-905d-4531-a333-1aa76e0981ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>Hosp_Rate</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>Hospital_by_modzcta_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>269.6</td>\n",
       "      <td>61</td>\n",
       "      <td>2020</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>442.5</td>\n",
       "      <td>92</td>\n",
       "      <td>2020</td>\n",
       "      <td>2112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>67.5</td>\n",
       "      <td>122</td>\n",
       "      <td>2020</td>\n",
       "      <td>2142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>22.4</td>\n",
       "      <td>153</td>\n",
       "      <td>2020</td>\n",
       "      <td>2173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>18.7</td>\n",
       "      <td>183</td>\n",
       "      <td>2020</td>\n",
       "      <td>2203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date MODZCTA_Location  Hosp_Rate  dayofyear  year  \\\n",
       "0 2020-03-01            Bronx      269.6         61  2020   \n",
       "1 2020-04-01            Bronx      442.5         92  2020   \n",
       "2 2020-05-01            Bronx       67.5        122  2020   \n",
       "3 2020-06-01            Bronx       22.4        153  2020   \n",
       "4 2020-07-01            Bronx       18.7        183  2020   \n",
       "\n",
       "   Hospital_by_modzcta_ID  \n",
       "0                    2081  \n",
       "1                    2112  \n",
       "2                    2142  \n",
       "3                    2173  \n",
       "4                    2203  "
      ]
     },
     "execution_count": 285,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Hospital_by_modzcta = Hospital_by_modzcta.melt(id_vars =['date'],\n",
    "    var_name=\"MODZCTA_Location\",\n",
    "    value_name = \"Hosp_Rate\")  \n",
    "\n",
    "\n",
    "\n",
    "#remove the first 9 characters from case_rate_location\n",
    "#res_str = str.replace('e', '', 1)  , in case I want to replace\n",
    "\n",
    "Hospital_by_modzcta['MODZCTA_Location'] = Hospital_by_modzcta['MODZCTA_Location'].str[9:]\n",
    "Hospital_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 294,
   "id": "b9b0abea-0054-4b9d-93b9-cc826d717721",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>Hosp_Rate</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>Hospital_by_modzcta_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>269.6</td>\n",
       "      <td>61</td>\n",
       "      <td>2020</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>442.5</td>\n",
       "      <td>92</td>\n",
       "      <td>2020</td>\n",
       "      <td>2112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>67.5</td>\n",
       "      <td>122</td>\n",
       "      <td>2020</td>\n",
       "      <td>2142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>22.4</td>\n",
       "      <td>153</td>\n",
       "      <td>2020</td>\n",
       "      <td>2173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>18.7</td>\n",
       "      <td>183</td>\n",
       "      <td>2020</td>\n",
       "      <td>2203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date MODZCTA_Location  Hosp_Rate  dayofyear  year  \\\n",
       "0 2020-03-01            Bronx      269.6         61  2020   \n",
       "1 2020-04-01            Bronx      442.5         92  2020   \n",
       "2 2020-05-01            Bronx       67.5        122  2020   \n",
       "3 2020-06-01            Bronx       22.4        153  2020   \n",
       "4 2020-07-01            Bronx       18.7        183  2020   \n",
       "\n",
       "   Hospital_by_modzcta_ID  \n",
       "0                    2081  \n",
       "1                    2112  \n",
       "2                    2142  \n",
       "3                    2173  \n",
       "4                    2203  "
      ]
     },
     "execution_count": 294,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "#this one has month and day, so add a day to it. \n",
    "\n",
    "#See Excel conversion notes\n",
    "\n",
    "Hospital_by_modzcta['date']=pd.to_datetime(Hospital_by_modzcta['date'], format='%m/%Y')\n",
    "\n",
    "#s = Hospital_by_modzcta['date']\n",
    "#d=pd.to_datetime(s, format='%b-%y')\n",
    "\n",
    "# get day of year and year\n",
    "Hospital_by_modzcta['dayofyear'] = Hospital_by_modzcta['date'].dt.dayofyear\n",
    "Hospital_by_modzcta['year'] = Hospital_by_modzcta['date'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "Hospital_by_modzcta['Hospital_by_modzcta_ID'] = Hospital_by_modzcta['dayofyear'] + Hospital_by_modzcta['year']\n",
    "\n",
    "\n",
    "Hospital_by_modzcta.head(5) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "id": "35e5ca42-7bdb-4545-8fa2-ccd814b4bd2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>death_Rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3/2020</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>38.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4/2020</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>238.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/2020</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>46.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6/2020</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>11.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7/2020</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>4.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     date MODZCTA_Location  death_Rate\n",
       "0  3/2020            Bronx        38.7\n",
       "1  4/2020            Bronx       238.1\n",
       "2  5/2020            Bronx        46.4\n",
       "3  6/2020            Bronx        11.6\n",
       "4  7/2020            Bronx         4.7"
      ]
     },
     "execution_count": 288,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deathrate_by_modzcta = deathrate_by_modzcta.melt(id_vars =['date'],\n",
    "    var_name=\"MODZCTA_Location\",\n",
    "    value_name = \"death_Rate\")  \n",
    "\n",
    "\n",
    "\n",
    "#remove the first 9 characters from case_rate_location\n",
    "#res_str = str.replace('e', '', 1)  , in case I want to replace\n",
    "\n",
    "deathrate_by_modzcta['MODZCTA_Location'] = deathrate_by_modzcta['MODZCTA_Location'].str[10:]\n",
    "deathrate_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 289,
   "id": "ab4711f4-d7c8-4205-acde-3f5b42f6225c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>death_Rate</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>deathrate_by_modzcta_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>38.7</td>\n",
       "      <td>61</td>\n",
       "      <td>2020</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-04-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>238.1</td>\n",
       "      <td>92</td>\n",
       "      <td>2020</td>\n",
       "      <td>2112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-05-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>46.4</td>\n",
       "      <td>122</td>\n",
       "      <td>2020</td>\n",
       "      <td>2142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>11.6</td>\n",
       "      <td>153</td>\n",
       "      <td>2020</td>\n",
       "      <td>2173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>4.7</td>\n",
       "      <td>183</td>\n",
       "      <td>2020</td>\n",
       "      <td>2203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date MODZCTA_Location  death_Rate  dayofyear  year  \\\n",
       "0 2020-03-01            Bronx        38.7         61  2020   \n",
       "1 2020-04-01            Bronx       238.1         92  2020   \n",
       "2 2020-05-01            Bronx        46.4        122  2020   \n",
       "3 2020-06-01            Bronx        11.6        153  2020   \n",
       "4 2020-07-01            Bronx         4.7        183  2020   \n",
       "\n",
       "   deathrate_by_modzcta_ID  \n",
       "0                     2081  \n",
       "1                     2112  \n",
       "2                     2142  \n",
       "3                     2173  \n",
       "4                     2203  "
      ]
     },
     "execution_count": 289,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "#this one has month and day, so add a day to it. \n",
    "\n",
    "#See Excel conversion notes\n",
    "\n",
    "deathrate_by_modzcta['date']=pd.to_datetime(deathrate_by_modzcta['date'], format='%m/%Y')\n",
    "\n",
    "#s = Hospital_by_modzcta['date']\n",
    "#d=pd.to_datetime(s, format='%b-%y')\n",
    "\n",
    "# get day of year and year\n",
    "deathrate_by_modzcta['dayofyear'] = deathrate_by_modzcta['date'].dt.dayofyear\n",
    "deathrate_by_modzcta['year'] = deathrate_by_modzcta['date'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID\n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "deathrate_by_modzcta['deathrate_by_modzcta_ID'] = deathrate_by_modzcta['dayofyear'] + deathrate_by_modzcta['year']\n",
    "\n",
    "\n",
    "deathrate_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 290,
   "id": "49665f53-29f0-4eca-92b9-ad78644c5648",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                       datetime64[ns]\n",
       "MODZCTA_Location                   object\n",
       "death_Rate                        float64\n",
       "dayofyear                           int64\n",
       "year                                int64\n",
       "deathrate_by_modzcta_ID             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 290,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "deathrate_by_modzcta.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 291,
   "id": "4366542d-1b8e-4798-babf-050650340880",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>TESTRATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-08-08</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2116.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2223.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-22</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2447.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-29</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2468.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-09-05</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2738.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending MODZCTA_Location  TESTRATE\n",
       "0  2020-08-08             CITY   2116.67\n",
       "1  2020-08-15             CITY   2223.77\n",
       "2  2020-08-22             CITY   2447.83\n",
       "3  2020-08-29             CITY   2468.65\n",
       "4  2020-09-05             CITY   2738.76"
      ]
     },
     "execution_count": 291,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_by_modzcta= test_by_modzcta.melt(id_vars =['week_ending'],\n",
    "    var_name=\"MODZCTA_Location\",\n",
    "    value_name = \"TESTRATE\")  \n",
    "\n",
    "\n",
    "\n",
    "#remove the first 9 characters from case_rate_location\n",
    "#res_str = str.replace('e', '', 1)  , in case I want to replace\n",
    "\n",
    "test_by_modzcta['MODZCTA_Location'] = test_by_modzcta['MODZCTA_Location'].str[9:]\n",
    "test_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 292,
   "id": "3f9952f2-063e-486c-b565-743b04116cd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>MODZCTA_Location</th>\n",
       "      <th>TESTRATE</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>test_by_modzcta_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-08-08</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2116.67</td>\n",
       "      <td>221</td>\n",
       "      <td>2020</td>\n",
       "      <td>2241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-08-15</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2223.77</td>\n",
       "      <td>228</td>\n",
       "      <td>2020</td>\n",
       "      <td>2248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-08-22</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2447.83</td>\n",
       "      <td>235</td>\n",
       "      <td>2020</td>\n",
       "      <td>2255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-08-29</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2468.65</td>\n",
       "      <td>242</td>\n",
       "      <td>2020</td>\n",
       "      <td>2262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-09-05</td>\n",
       "      <td>CITY</td>\n",
       "      <td>2738.76</td>\n",
       "      <td>249</td>\n",
       "      <td>2020</td>\n",
       "      <td>2269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  week_ending MODZCTA_Location  TESTRATE  dayofyear  year  test_by_modzcta_ID\n",
       "0  2020-08-08             CITY   2116.67        221  2020                2241\n",
       "1  2020-08-15             CITY   2223.77        228  2020                2248\n",
       "2  2020-08-22             CITY   2447.83        235  2020                2255\n",
       "3  2020-08-29             CITY   2468.65        242  2020                2262\n",
       "4  2020-09-05             CITY   2738.76        249  2020                2269"
      ]
     },
     "execution_count": 292,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "test_by_modzcta['week_ending']=pd.to_datetime(test_by_modzcta['week_ending'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "test_by_modzcta['dayofyear'] = test_by_modzcta['week_ending'].dt.dayofyear\n",
    "                                               \n",
    "test_by_modzcta['year'] = test_by_modzcta['week_ending'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "test_by_modzcta['test_by_modzcta_ID'] = test_by_modzcta['dayofyear'] + test_by_modzcta['year']\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "test_by_modzcta.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e15fb1c-457b-4c1b-8921-3a1ce5469661",
   "metadata": {},
   "source": [
    "## Keep going!!!\n",
    "STOPPED HERE!\n",
    "variant_classification= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/variant-classification.csv\")\n",
    "cases_sequenced= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/variants/cases-sequenced.csv\")\n",
    "hosp_by_day= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/hosp-by-day.csv\")\n",
    "coverage_by_modzcta_allages= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/covid-vaccine-data/main/people/coverage-by-modzcta-allages.csv\")\n",
    "doses_by_residency_age= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/covid-vaccine-data/main/doses/doses-by-residency-age.csv\")\n",
    "weekly_hosp_rate_race= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-race.csv\")\n",
    "weekly_hosp_rate_age= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/weekly-hosp-rate-age.csv\")\n",
    "sydromic_data_covid_like_Illness= pd.read_csv(\"https://raw.githubusercontent.com/nychealth/coronavirus-data/master/trends/covid-like-illness-by-boro.csv\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "7f5d1ed5-ab8c-4fb7-841f-c27be0d843e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_of_interest</th>\n",
       "      <th>HOSPITALIZED_COUNT</th>\n",
       "      <th>HOSP_COUNT_7DAY_AVG</th>\n",
       "      <th>BX_HOSPITALIZED_COUNT</th>\n",
       "      <th>BX_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>BK_HOSPITALIZED_COUNT</th>\n",
       "      <th>BK_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>MN_HOSPITALIZED_COUNT</th>\n",
       "      <th>MN_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>QN_HOSPITALIZED_COUNT</th>\n",
       "      <th>QN_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>SI_HOSPITALIZED_COUNT</th>\n",
       "      <th>SI_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>INCOMPLETE</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>hosp_by_day_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>2020</td>\n",
       "      <td>2080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61</td>\n",
       "      <td>2020</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>2020</td>\n",
       "      <td>2082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>2020</td>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>2020</td>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  date_of_interest  HOSPITALIZED_COUNT  HOSP_COUNT_7DAY_AVG  \\\n",
       "0       2020-02-29                   1                    0   \n",
       "1       2020-03-01                   1                    0   \n",
       "2       2020-03-02                   2                    0   \n",
       "3       2020-03-03                   7                    0   \n",
       "4       2020-03-04                   2                    0   \n",
       "\n",
       "   BX_HOSPITALIZED_COUNT  BX_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      1                               0   \n",
       "2                      0                               0   \n",
       "3                      1                               0   \n",
       "4                      0                               0   \n",
       "\n",
       "   BK_HOSPITALIZED_COUNT  BK_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      1                               0   \n",
       "1                      0                               0   \n",
       "2                      2                               0   \n",
       "3                      3                               0   \n",
       "4                      1                               0   \n",
       "\n",
       "   MN_HOSPITALIZED_COUNT  MN_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      0                               0   \n",
       "2                      0                               0   \n",
       "3                      1                               0   \n",
       "4                      1                               0   \n",
       "\n",
       "   QN_HOSPITALIZED_COUNT  QN_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      0                               0   \n",
       "2                      0                               0   \n",
       "3                      2                               0   \n",
       "4                      0                               0   \n",
       "\n",
       "   SI_HOSPITALIZED_COUNT  SI_HOSPITALIZED_COUNT_7DAY_AVG  INCOMPLETE  \\\n",
       "0                      0                               0           0   \n",
       "1                      0                               0           0   \n",
       "2                      0                               0           0   \n",
       "3                      0                               0           0   \n",
       "4                      0                               0           0   \n",
       "\n",
       "   dayofyear  year  hosp_by_day_ID  \n",
       "0         60  2020            2080  \n",
       "1         61  2020            2081  \n",
       "2         62  2020            2082  \n",
       "3         63  2020            2083  \n",
       "4         64  2020            2084  "
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "hosp_by_day['date_of_interest']=pd.to_datetime(hosp_by_day['date_of_interest'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "hosp_by_day['dayofyear'] = hosp_by_day['date_of_interest'].dt.dayofyear\n",
    "                                               \n",
    "hosp_by_day['year'] = hosp_by_day['date_of_interest'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "hosp_by_day['hosp_by_day_ID'] = hosp_by_day['dayofyear'] + hosp_by_day['year']\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "hosp_by_day.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "id": "9f9c2bd8-cc3c-4fde-a846-a0fe5e70468c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>NEIGHBORHOOD_NAME</th>\n",
       "      <th>BOROUGH</th>\n",
       "      <th>MODZCTA</th>\n",
       "      <th>Label</th>\n",
       "      <th>AGE_GROUP</th>\n",
       "      <th>POP_DENOMINATOR</th>\n",
       "      <th>COUNT_PARTIALLY_CUMULATIVE</th>\n",
       "      <th>COUNT_FULLY_CUMULATIVE</th>\n",
       "      <th>COUNT_1PLUS_CUMULATIVE</th>\n",
       "      <th>PERC_PARTIALLY</th>\n",
       "      <th>PERC_FULLY</th>\n",
       "      <th>PERC_1PLUS</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>coverage_by_modzcta_allages_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>Chelsea/NoMad/West Chelsea</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10001</td>\n",
       "      <td>10001, 10118</td>\n",
       "      <td>All ages</td>\n",
       "      <td>27613.09</td>\n",
       "      <td>6507</td>\n",
       "      <td>29934</td>\n",
       "      <td>36441</td>\n",
       "      <td>23.56</td>\n",
       "      <td>108.41</td>\n",
       "      <td>131.97</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>Chinatown/Lower East Side</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10002</td>\n",
       "      <td>10002</td>\n",
       "      <td>All ages</td>\n",
       "      <td>75322.71</td>\n",
       "      <td>4626</td>\n",
       "      <td>61592</td>\n",
       "      <td>66218</td>\n",
       "      <td>6.14</td>\n",
       "      <td>81.77</td>\n",
       "      <td>87.91</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>East Village/Gramercy/Greenwich Village</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10003</td>\n",
       "      <td>10003</td>\n",
       "      <td>All ages</td>\n",
       "      <td>53977.81</td>\n",
       "      <td>3489</td>\n",
       "      <td>38815</td>\n",
       "      <td>42304</td>\n",
       "      <td>6.46</td>\n",
       "      <td>71.91</td>\n",
       "      <td>78.37</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>Financial District</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10004</td>\n",
       "      <td>10004</td>\n",
       "      <td>All ages</td>\n",
       "      <td>2972.12</td>\n",
       "      <td>481</td>\n",
       "      <td>3726</td>\n",
       "      <td>4207</td>\n",
       "      <td>16.18</td>\n",
       "      <td>125.37</td>\n",
       "      <td>141.55</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>Financial District</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>10005</td>\n",
       "      <td>10005</td>\n",
       "      <td>All ages</td>\n",
       "      <td>8757.23</td>\n",
       "      <td>814</td>\n",
       "      <td>7708</td>\n",
       "      <td>8522</td>\n",
       "      <td>9.30</td>\n",
       "      <td>88.02</td>\n",
       "      <td>97.31</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE                        NEIGHBORHOOD_NAME    BOROUGH  MODZCTA  \\\n",
       "0 2021-11-14               Chelsea/NoMad/West Chelsea  Manhattan    10001   \n",
       "1 2021-11-14                Chinatown/Lower East Side  Manhattan    10002   \n",
       "2 2021-11-14  East Village/Gramercy/Greenwich Village  Manhattan    10003   \n",
       "3 2021-11-14                       Financial District  Manhattan    10004   \n",
       "4 2021-11-14                       Financial District  Manhattan    10005   \n",
       "\n",
       "          Label AGE_GROUP  POP_DENOMINATOR  COUNT_PARTIALLY_CUMULATIVE  \\\n",
       "0  10001, 10118  All ages         27613.09                        6507   \n",
       "1         10002  All ages         75322.71                        4626   \n",
       "2         10003  All ages         53977.81                        3489   \n",
       "3         10004  All ages          2972.12                         481   \n",
       "4         10005  All ages          8757.23                         814   \n",
       "\n",
       "   COUNT_FULLY_CUMULATIVE  COUNT_1PLUS_CUMULATIVE  PERC_PARTIALLY  PERC_FULLY  \\\n",
       "0                   29934                   36441           23.56      108.41   \n",
       "1                   61592                   66218            6.14       81.77   \n",
       "2                   38815                   42304            6.46       71.91   \n",
       "3                    3726                    4207           16.18      125.37   \n",
       "4                    7708                    8522            9.30       88.02   \n",
       "\n",
       "   PERC_1PLUS  dayofyear  year  coverage_by_modzcta_allages_ID  \n",
       "0      131.97        318  2021                            2339  \n",
       "1       87.91        318  2021                            2339  \n",
       "2       78.37        318  2021                            2339  \n",
       "3      141.55        318  2021                            2339  \n",
       "4       97.31        318  2021                            2339  "
      ]
     },
     "execution_count": 276,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "coverage_by_modzcta_allages['DATE']=pd.to_datetime(coverage_by_modzcta_allages['DATE'], format='%Y/%m/%d')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "coverage_by_modzcta_allages['dayofyear'] = coverage_by_modzcta_allages['DATE'].dt.dayofyear\n",
    "                                               \n",
    "coverage_by_modzcta_allages['year'] = coverage_by_modzcta_allages['DATE'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "coverage_by_modzcta_allages['coverage_by_modzcta_allages_ID'] = coverage_by_modzcta_allages['dayofyear'] + coverage_by_modzcta_allages['year']\n",
    "\n",
    "\n",
    "\n",
    "coverage_by_modzcta_allages.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "id": "fc7cb96e-31f5-410a-bb23-e7e6dd478133",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_of_interest</th>\n",
       "      <th>HOSPITALIZED_COUNT</th>\n",
       "      <th>HOSP_COUNT_7DAY_AVG</th>\n",
       "      <th>BX_HOSPITALIZED_COUNT</th>\n",
       "      <th>BX_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>BK_HOSPITALIZED_COUNT</th>\n",
       "      <th>BK_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>MN_HOSPITALIZED_COUNT</th>\n",
       "      <th>MN_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>QN_HOSPITALIZED_COUNT</th>\n",
       "      <th>QN_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>SI_HOSPITALIZED_COUNT</th>\n",
       "      <th>SI_HOSPITALIZED_COUNT_7DAY_AVG</th>\n",
       "      <th>INCOMPLETE</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>hosp_by_day_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-29</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>2020</td>\n",
       "      <td>2080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61</td>\n",
       "      <td>2020</td>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>62</td>\n",
       "      <td>2020</td>\n",
       "      <td>2082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>63</td>\n",
       "      <td>2020</td>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>64</td>\n",
       "      <td>2020</td>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  date_of_interest  HOSPITALIZED_COUNT  HOSP_COUNT_7DAY_AVG  \\\n",
       "0       2020-02-29                   1                    0   \n",
       "1       2020-03-01                   1                    0   \n",
       "2       2020-03-02                   2                    0   \n",
       "3       2020-03-03                   7                    0   \n",
       "4       2020-03-04                   2                    0   \n",
       "\n",
       "   BX_HOSPITALIZED_COUNT  BX_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      1                               0   \n",
       "2                      0                               0   \n",
       "3                      1                               0   \n",
       "4                      0                               0   \n",
       "\n",
       "   BK_HOSPITALIZED_COUNT  BK_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      1                               0   \n",
       "1                      0                               0   \n",
       "2                      2                               0   \n",
       "3                      3                               0   \n",
       "4                      1                               0   \n",
       "\n",
       "   MN_HOSPITALIZED_COUNT  MN_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      0                               0   \n",
       "2                      0                               0   \n",
       "3                      1                               0   \n",
       "4                      1                               0   \n",
       "\n",
       "   QN_HOSPITALIZED_COUNT  QN_HOSPITALIZED_COUNT_7DAY_AVG  \\\n",
       "0                      0                               0   \n",
       "1                      0                               0   \n",
       "2                      0                               0   \n",
       "3                      2                               0   \n",
       "4                      0                               0   \n",
       "\n",
       "   SI_HOSPITALIZED_COUNT  SI_HOSPITALIZED_COUNT_7DAY_AVG  INCOMPLETE  \\\n",
       "0                      0                               0           0   \n",
       "1                      0                               0           0   \n",
       "2                      0                               0           0   \n",
       "3                      0                               0           0   \n",
       "4                      0                               0           0   \n",
       "\n",
       "   dayofyear  year  hosp_by_day_ID  \n",
       "0         60  2020            2080  \n",
       "1         61  2020            2081  \n",
       "2         62  2020            2082  \n",
       "3         63  2020            2083  \n",
       "4         64  2020            2084  "
      ]
     },
     "execution_count": 277,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hosp_by_day.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "5f64d045-f78b-4496-9bc3-6b87d9fa050e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>RESIDENCY</th>\n",
       "      <th>ADMIN_ALLDOSES_CUMULATIVE_Under18</th>\n",
       "      <th>ADMIN_ALLDOSES_CUMULATIVE_18plus</th>\n",
       "      <th>ADMIN_ALLDOSES_CUMULATIVE_Allages</th>\n",
       "      <th>PERC_ADMIN_ALLDOSES_Allages</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>coverage_by_modzcta_allages_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>NON-NYC</td>\n",
       "      <td>43567</td>\n",
       "      <td>1383878</td>\n",
       "      <td>1427445</td>\n",
       "      <td>11.66</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>NYC</td>\n",
       "      <td>744141</td>\n",
       "      <td>10042182</td>\n",
       "      <td>10786323</td>\n",
       "      <td>88.10</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>203</td>\n",
       "      <td>29980</td>\n",
       "      <td>30183</td>\n",
       "      <td>0.25</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-11-14</td>\n",
       "      <td>Total</td>\n",
       "      <td>787911</td>\n",
       "      <td>11456040</td>\n",
       "      <td>12243951</td>\n",
       "      <td>100.00</td>\n",
       "      <td>318</td>\n",
       "      <td>2021</td>\n",
       "      <td>2339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        DATE RESIDENCY  ADMIN_ALLDOSES_CUMULATIVE_Under18  \\\n",
       "0 2021-11-14   NON-NYC                              43567   \n",
       "1 2021-11-14       NYC                             744141   \n",
       "2 2021-11-14   UNKNOWN                                203   \n",
       "3 2021-11-14     Total                             787911   \n",
       "\n",
       "   ADMIN_ALLDOSES_CUMULATIVE_18plus  ADMIN_ALLDOSES_CUMULATIVE_Allages  \\\n",
       "0                           1383878                            1427445   \n",
       "1                          10042182                           10786323   \n",
       "2                             29980                              30183   \n",
       "3                          11456040                           12243951   \n",
       "\n",
       "   PERC_ADMIN_ALLDOSES_Allages  dayofyear  year  \\\n",
       "0                        11.66        318  2021   \n",
       "1                        88.10        318  2021   \n",
       "2                         0.25        318  2021   \n",
       "3                       100.00        318  2021   \n",
       "\n",
       "   coverage_by_modzcta_allages_ID  \n",
       "0                            2339  \n",
       "1                            2339  \n",
       "2                            2339  \n",
       "3                            2339  "
      ]
     },
     "execution_count": 279,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert date to datetime\n",
    "\n",
    "doses_by_residency_age['DATE']=pd.to_datetime(doses_by_residency_age['DATE'], format='%Y/%m/%d')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "doses_by_residency_age['dayofyear'] = doses_by_residency_age['DATE'].dt.dayofyear\n",
    "                                               \n",
    "doses_by_residency_age['year'] = doses_by_residency_age['DATE'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "doses_by_residency_age['coverage_by_modzcta_allages_ID'] = doses_by_residency_age['dayofyear'] + doses_by_residency_age['year']\n",
    "\n",
    "doses_by_residency_age.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 313,
   "id": "494055c3-a10f-4a94-bbea-3f6795e522d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>all_race_ethnicity</th>\n",
       "      <th>Asian_Pacific_Islander</th>\n",
       "      <th>Black_African_American</th>\n",
       "      <th>Hispanic_Latino</th>\n",
       "      <th>White</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>coverage_by_modzcta_allages_ID</th>\n",
       "      <th>weekly_hosp_rate_race_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>2021-10-09</td>\n",
       "      <td>4.79</td>\n",
       "      <td>2.34</td>\n",
       "      <td>5.29</td>\n",
       "      <td>5.03</td>\n",
       "      <td>3.88</td>\n",
       "      <td>282</td>\n",
       "      <td>2021</td>\n",
       "      <td>2303</td>\n",
       "      <td>2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2021-10-16</td>\n",
       "      <td>4.31</td>\n",
       "      <td>2.36</td>\n",
       "      <td>4.33</td>\n",
       "      <td>4.07</td>\n",
       "      <td>3.75</td>\n",
       "      <td>289</td>\n",
       "      <td>2021</td>\n",
       "      <td>2310</td>\n",
       "      <td>2310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2021-10-23</td>\n",
       "      <td>3.45</td>\n",
       "      <td>1.59</td>\n",
       "      <td>4.51</td>\n",
       "      <td>3.35</td>\n",
       "      <td>2.60</td>\n",
       "      <td>296</td>\n",
       "      <td>2021</td>\n",
       "      <td>2317</td>\n",
       "      <td>2317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>2021-10-30</td>\n",
       "      <td>2.75</td>\n",
       "      <td>1.19</td>\n",
       "      <td>2.63</td>\n",
       "      <td>2.35</td>\n",
       "      <td>2.49</td>\n",
       "      <td>303</td>\n",
       "      <td>2021</td>\n",
       "      <td>2324</td>\n",
       "      <td>2324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2021-11-06</td>\n",
       "      <td>1.79</td>\n",
       "      <td>0.41</td>\n",
       "      <td>2.26</td>\n",
       "      <td>2.08</td>\n",
       "      <td>1.29</td>\n",
       "      <td>310</td>\n",
       "      <td>2021</td>\n",
       "      <td>2331</td>\n",
       "      <td>2331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week_ending  all_race_ethnicity  Asian_Pacific_Islander  \\\n",
       "84  2021-10-09                4.79                    2.34   \n",
       "85  2021-10-16                4.31                    2.36   \n",
       "86  2021-10-23                3.45                    1.59   \n",
       "87  2021-10-30                2.75                    1.19   \n",
       "88  2021-11-06                1.79                    0.41   \n",
       "\n",
       "    Black_African_American  Hispanic_Latino  White  dayofyear  year  \\\n",
       "84                    5.29             5.03   3.88        282  2021   \n",
       "85                    4.33             4.07   3.75        289  2021   \n",
       "86                    4.51             3.35   2.60        296  2021   \n",
       "87                    2.63             2.35   2.49        303  2021   \n",
       "88                    2.26             2.08   1.29        310  2021   \n",
       "\n",
       "    coverage_by_modzcta_allages_ID  weekly_hosp_rate_race_ID  \n",
       "84                            2303                      2303  \n",
       "85                            2310                      2310  \n",
       "86                            2317                      2317  \n",
       "87                            2324                      2324  \n",
       "88                            2331                      2331  "
      ]
     },
     "execution_count": 313,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#convert date to datetime\n",
    "\n",
    "weekly_hosp_rate_race['week_ending']=pd.to_datetime(weekly_hosp_rate_race['week_ending'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "weekly_hosp_rate_race['dayofyear'] = weekly_hosp_rate_race['week_ending'].dt.dayofyear\n",
    "                                               \n",
    "weekly_hosp_rate_race['year'] = weekly_hosp_rate_race['week_ending'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "weekly_hosp_rate_race['weekly_hosp_rate_race_ID'] = weekly_hosp_rate_race['dayofyear'] + weekly_hosp_rate_race['year']\n",
    "\n",
    "\n",
    "weekly_hosp_rate_race.tail(5)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "id": "71332b4d-a40c-4867-a198-0fb5f3f9535b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>week_ending</th>\n",
       "      <th>age_all_ages</th>\n",
       "      <th>age_0_4</th>\n",
       "      <th>age_5_12</th>\n",
       "      <th>age_13_17</th>\n",
       "      <th>age_18_24</th>\n",
       "      <th>age_25_34</th>\n",
       "      <th>age_35_44</th>\n",
       "      <th>age_45_54</th>\n",
       "      <th>age_55_64</th>\n",
       "      <th>age_65_74</th>\n",
       "      <th>age_75up</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>coverage_by_modzcta_allages_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>2021-10-09</td>\n",
       "      <td>5.00</td>\n",
       "      <td>2.48</td>\n",
       "      <td>1.61</td>\n",
       "      <td>1.62</td>\n",
       "      <td>1.70</td>\n",
       "      <td>2.49</td>\n",
       "      <td>4.22</td>\n",
       "      <td>4.77</td>\n",
       "      <td>6.91</td>\n",
       "      <td>9.04</td>\n",
       "      <td>18.70</td>\n",
       "      <td>282</td>\n",
       "      <td>2021</td>\n",
       "      <td>2303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>2021-10-16</td>\n",
       "      <td>4.46</td>\n",
       "      <td>1.34</td>\n",
       "      <td>1.07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.84</td>\n",
       "      <td>2.83</td>\n",
       "      <td>2.81</td>\n",
       "      <td>4.96</td>\n",
       "      <td>6.01</td>\n",
       "      <td>8.35</td>\n",
       "      <td>16.92</td>\n",
       "      <td>289</td>\n",
       "      <td>2021</td>\n",
       "      <td>2310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2021-10-23</td>\n",
       "      <td>3.62</td>\n",
       "      <td>2.29</td>\n",
       "      <td>0.67</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.56</td>\n",
       "      <td>1.68</td>\n",
       "      <td>1.67</td>\n",
       "      <td>2.33</td>\n",
       "      <td>5.71</td>\n",
       "      <td>10.57</td>\n",
       "      <td>12.64</td>\n",
       "      <td>296</td>\n",
       "      <td>2021</td>\n",
       "      <td>2317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>2021-10-30</td>\n",
       "      <td>2.84</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.94</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.99</td>\n",
       "      <td>1.28</td>\n",
       "      <td>1.06</td>\n",
       "      <td>1.85</td>\n",
       "      <td>4.30</td>\n",
       "      <td>6.40</td>\n",
       "      <td>13.71</td>\n",
       "      <td>303</td>\n",
       "      <td>2021</td>\n",
       "      <td>2324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2021-11-06</td>\n",
       "      <td>2.24</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.67</td>\n",
       "      <td>1.41</td>\n",
       "      <td>2.24</td>\n",
       "      <td>3.00</td>\n",
       "      <td>5.84</td>\n",
       "      <td>10.15</td>\n",
       "      <td>310</td>\n",
       "      <td>2021</td>\n",
       "      <td>2331</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   week_ending  age_all_ages  age_0_4  age_5_12  age_13_17  age_18_24  \\\n",
       "84  2021-10-09          5.00     2.48      1.61       1.62       1.70   \n",
       "85  2021-10-16          4.46     1.34      1.07        NaN       1.84   \n",
       "86  2021-10-23          3.62     2.29      0.67        NaN       1.56   \n",
       "87  2021-10-30          2.84      NaN      0.94        NaN       0.99   \n",
       "88  2021-11-06          2.24     0.95      0.00        NaN        NaN   \n",
       "\n",
       "    age_25_34  age_35_44  age_45_54  age_55_64  age_65_74  age_75up  \\\n",
       "84       2.49       4.22       4.77       6.91       9.04     18.70   \n",
       "85       2.83       2.81       4.96       6.01       8.35     16.92   \n",
       "86       1.68       1.67       2.33       5.71      10.57     12.64   \n",
       "87       1.28       1.06       1.85       4.30       6.40     13.71   \n",
       "88       0.67       1.41       2.24       3.00       5.84     10.15   \n",
       "\n",
       "    dayofyear  year  coverage_by_modzcta_allages_ID  \n",
       "84        282  2021                            2303  \n",
       "85        289  2021                            2310  \n",
       "86        296  2021                            2317  \n",
       "87        303  2021                            2324  \n",
       "88        310  2021                            2331  "
      ]
     },
     "execution_count": 314,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#convert date to datetime\n",
    "\n",
    "weekly_hosp_rate_age['week_ending']=pd.to_datetime(weekly_hosp_rate_age['week_ending'], format='%m/%d/%Y')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "weekly_hosp_rate_age['dayofyear'] = weekly_hosp_rate_age['week_ending'].dt.dayofyear\n",
    "                                               \n",
    "weekly_hosp_rate_age['year'] = weekly_hosp_rate_age['week_ending'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "weekly_hosp_rate_age['coverage_by_modzcta_allages_ID'] = weekly_hosp_rate_age['dayofyear'] + weekly_hosp_rate_age['year']\n",
    "\n",
    "\n",
    "weekly_hosp_rate_age.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 322,
   "id": "0500c879-a258-4695-aef1-c3ee2ce0c435",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Admit_0_4</th>\n",
       "      <th>Admit_5_12</th>\n",
       "      <th>Admit_13_17</th>\n",
       "      <th>Admit_18_24</th>\n",
       "      <th>Admit_25_34</th>\n",
       "      <th>Admit_35_44</th>\n",
       "      <th>Admit_45_54</th>\n",
       "      <th>Admit_55_64</th>\n",
       "      <th>...</th>\n",
       "      <th>Visit_25_34</th>\n",
       "      <th>Visit_35_44</th>\n",
       "      <th>Visit_45_54</th>\n",
       "      <th>Visit_55_64</th>\n",
       "      <th>Visit_65_74</th>\n",
       "      <th>Visit_75</th>\n",
       "      <th>Visit_All_ages</th>\n",
       "      <th>dayofyear</th>\n",
       "      <th>year</th>\n",
       "      <th>sydromic_data_covid_like_Illness_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2.928572</td>\n",
       "      <td>1.913609</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.464771</td>\n",
       "      <td>2.174093</td>\n",
       "      <td>1.653995</td>\n",
       "      <td>2.788094</td>\n",
       "      <td>4.350853</td>\n",
       "      <td>...</td>\n",
       "      <td>12.609737</td>\n",
       "      <td>10.475303</td>\n",
       "      <td>10.037137</td>\n",
       "      <td>9.323256</td>\n",
       "      <td>6.861803</td>\n",
       "      <td>8.603631</td>\n",
       "      <td>17.386665</td>\n",
       "      <td>32</td>\n",
       "      <td>2020</td>\n",
       "      <td>2052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>4.272591</td>\n",
       "      <td>0.394225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.465385</td>\n",
       "      <td>0.210607</td>\n",
       "      <td>0.833739</td>\n",
       "      <td>2.638531</td>\n",
       "      <td>2.432701</td>\n",
       "      <td>...</td>\n",
       "      <td>8.213691</td>\n",
       "      <td>5.002432</td>\n",
       "      <td>7.585777</td>\n",
       "      <td>6.950574</td>\n",
       "      <td>5.381894</td>\n",
       "      <td>9.040599</td>\n",
       "      <td>10.918256</td>\n",
       "      <td>32</td>\n",
       "      <td>2020</td>\n",
       "      <td>2052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>1.299731</td>\n",
       "      <td>1.004882</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.550164</td>\n",
       "      <td>1.714200</td>\n",
       "      <td>0.501499</td>\n",
       "      <td>1.626475</td>\n",
       "      <td>...</td>\n",
       "      <td>5.226558</td>\n",
       "      <td>7.713900</td>\n",
       "      <td>2.507497</td>\n",
       "      <td>7.590215</td>\n",
       "      <td>8.200026</td>\n",
       "      <td>11.422883</td>\n",
       "      <td>8.411611</td>\n",
       "      <td>32</td>\n",
       "      <td>2020</td>\n",
       "      <td>2052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Queens</td>\n",
       "      <td>2.832681</td>\n",
       "      <td>1.014117</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.562669</td>\n",
       "      <td>0.819486</td>\n",
       "      <td>0.319538</td>\n",
       "      <td>0.972290</td>\n",
       "      <td>1.328515</td>\n",
       "      <td>...</td>\n",
       "      <td>6.009566</td>\n",
       "      <td>6.071219</td>\n",
       "      <td>6.481932</td>\n",
       "      <td>6.310447</td>\n",
       "      <td>1.990159</td>\n",
       "      <td>10.221813</td>\n",
       "      <td>12.286597</td>\n",
       "      <td>32</td>\n",
       "      <td>2020</td>\n",
       "      <td>2052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.557539</td>\n",
       "      <td>...</td>\n",
       "      <td>6.164183</td>\n",
       "      <td>10.115656</td>\n",
       "      <td>1.525134</td>\n",
       "      <td>6.076719</td>\n",
       "      <td>4.406742</td>\n",
       "      <td>6.315524</td>\n",
       "      <td>8.820213</td>\n",
       "      <td>32</td>\n",
       "      <td>2020</td>\n",
       "      <td>2052</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Date        Borough  Admit_0_4  Admit_5_12  Admit_13_17  Admit_18_24  \\\n",
       "0 2020-02-01          Bronx   2.928572    1.913609          0.0     3.464771   \n",
       "1 2020-02-01       Brooklyn   4.272591    0.394225          0.0     0.465385   \n",
       "2 2020-02-01      Manhattan   1.299731    1.004882          0.0     0.000000   \n",
       "3 2020-02-01         Queens   2.832681    1.014117          0.0     0.562669   \n",
       "4 2020-02-01  Staten Island   0.000000    0.000000          0.0     0.000000   \n",
       "\n",
       "   Admit_25_34  Admit_35_44  Admit_45_54  Admit_55_64  ...  Visit_25_34  \\\n",
       "0     2.174093     1.653995     2.788094     4.350853  ...    12.609737   \n",
       "1     0.210607     0.833739     2.638531     2.432701  ...     8.213691   \n",
       "2     0.550164     1.714200     0.501499     1.626475  ...     5.226558   \n",
       "3     0.819486     0.319538     0.972290     1.328515  ...     6.009566   \n",
       "4     0.000000     0.000000     0.000000     4.557539  ...     6.164183   \n",
       "\n",
       "   Visit_35_44  Visit_45_54  Visit_55_64  Visit_65_74   Visit_75  \\\n",
       "0    10.475303    10.037137     9.323256     6.861803   8.603631   \n",
       "1     5.002432     7.585777     6.950574     5.381894   9.040599   \n",
       "2     7.713900     2.507497     7.590215     8.200026  11.422883   \n",
       "3     6.071219     6.481932     6.310447     1.990159  10.221813   \n",
       "4    10.115656     1.525134     6.076719     4.406742   6.315524   \n",
       "\n",
       "   Visit_All_ages  dayofyear  year  sydromic_data_covid_like_Illness_ID  \n",
       "0       17.386665         32  2020                                 2052  \n",
       "1       10.918256         32  2020                                 2052  \n",
       "2        8.411611         32  2020                                 2052  \n",
       "3       12.286597         32  2020                                 2052  \n",
       "4        8.820213         32  2020                                 2052  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 322,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "#convert date to datetime\n",
    "\n",
    "sydromic_data_covid_like_Illness['Date']=pd.to_datetime(sydromic_data_covid_like_Illness['Date'], format='%Y/%m/%d')\n",
    "\n",
    "\n",
    "\n",
    "# get day of year and year\n",
    "sydromic_data_covid_like_Illness['dayofyear'] = sydromic_data_covid_like_Illness['Date'].dt.dayofyear\n",
    "                                               \n",
    "sydromic_data_covid_like_Illness['year'] = sydromic_data_covid_like_Illness['Date'].dt.year\n",
    "\n",
    "#add the dayofyear and year numbers to get ID \n",
    "#example: (day) 221 + (year) 2020 = (ID) 2241 \n",
    "sydromic_data_covid_like_Illness['sydromic_data_covid_like_Illness_ID'] = sydromic_data_covid_like_Illness['dayofyear'] + sydromic_data_covid_like_Illness['year']\n",
    "\n",
    "\n",
    "\n",
    "sydromic_data_covid_like_Illness.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 319,
   "id": "8835ada3-b578-4630-b5e6-f58201a60d84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Borough</th>\n",
       "      <th>Admit_0_4</th>\n",
       "      <th>Admit_5_12</th>\n",
       "      <th>Admit_13_17</th>\n",
       "      <th>Admit_18_24</th>\n",
       "      <th>Admit_25_34</th>\n",
       "      <th>Admit_35_44</th>\n",
       "      <th>Admit_45_54</th>\n",
       "      <th>Admit_55_64</th>\n",
       "      <th>...</th>\n",
       "      <th>Visit_5_12</th>\n",
       "      <th>Visit_13_17</th>\n",
       "      <th>Visit_18_24</th>\n",
       "      <th>Visit_25_34</th>\n",
       "      <th>Visit_35_44</th>\n",
       "      <th>Visit_45_54</th>\n",
       "      <th>Visit_55_64</th>\n",
       "      <th>Visit_65_74</th>\n",
       "      <th>Visit_75</th>\n",
       "      <th>Visit_All_ages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>2.928572</td>\n",
       "      <td>1.913609</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.464771</td>\n",
       "      <td>2.174093</td>\n",
       "      <td>1.653995</td>\n",
       "      <td>2.788094</td>\n",
       "      <td>4.350853</td>\n",
       "      <td>...</td>\n",
       "      <td>33.807088</td>\n",
       "      <td>9.609795</td>\n",
       "      <td>18.016807</td>\n",
       "      <td>12.609737</td>\n",
       "      <td>10.475303</td>\n",
       "      <td>10.037137</td>\n",
       "      <td>9.323256</td>\n",
       "      <td>6.861803</td>\n",
       "      <td>8.603631</td>\n",
       "      <td>17.386665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>4.272591</td>\n",
       "      <td>0.394225</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.465385</td>\n",
       "      <td>0.210607</td>\n",
       "      <td>0.833739</td>\n",
       "      <td>2.638531</td>\n",
       "      <td>2.432701</td>\n",
       "      <td>...</td>\n",
       "      <td>27.201559</td>\n",
       "      <td>4.218840</td>\n",
       "      <td>11.634625</td>\n",
       "      <td>8.213691</td>\n",
       "      <td>5.002432</td>\n",
       "      <td>7.585777</td>\n",
       "      <td>6.950574</td>\n",
       "      <td>5.381894</td>\n",
       "      <td>9.040599</td>\n",
       "      <td>10.918256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>1.299731</td>\n",
       "      <td>1.004882</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.550164</td>\n",
       "      <td>1.714200</td>\n",
       "      <td>0.501499</td>\n",
       "      <td>1.626475</td>\n",
       "      <td>...</td>\n",
       "      <td>23.112286</td>\n",
       "      <td>1.783450</td>\n",
       "      <td>8.188235</td>\n",
       "      <td>5.226558</td>\n",
       "      <td>7.713900</td>\n",
       "      <td>2.507497</td>\n",
       "      <td>7.590215</td>\n",
       "      <td>8.200026</td>\n",
       "      <td>11.422883</td>\n",
       "      <td>8.411611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Queens</td>\n",
       "      <td>2.832681</td>\n",
       "      <td>1.014117</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.562669</td>\n",
       "      <td>0.819486</td>\n",
       "      <td>0.319538</td>\n",
       "      <td>0.972290</td>\n",
       "      <td>1.328515</td>\n",
       "      <td>...</td>\n",
       "      <td>34.987030</td>\n",
       "      <td>12.868160</td>\n",
       "      <td>9.565378</td>\n",
       "      <td>6.009566</td>\n",
       "      <td>6.071219</td>\n",
       "      <td>6.481932</td>\n",
       "      <td>6.310447</td>\n",
       "      <td>1.990159</td>\n",
       "      <td>10.221813</td>\n",
       "      <td>12.286597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>Staten Island</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.557539</td>\n",
       "      <td>...</td>\n",
       "      <td>17.380614</td>\n",
       "      <td>3.335803</td>\n",
       "      <td>7.479633</td>\n",
       "      <td>6.164183</td>\n",
       "      <td>10.115656</td>\n",
       "      <td>1.525134</td>\n",
       "      <td>6.076719</td>\n",
       "      <td>4.406742</td>\n",
       "      <td>6.315524</td>\n",
       "      <td>8.820213</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Date        Borough  Admit_0_4  Admit_5_12  Admit_13_17  Admit_18_24  \\\n",
       "0  2020-02-01          Bronx   2.928572    1.913609          0.0     3.464771   \n",
       "1  2020-02-01       Brooklyn   4.272591    0.394225          0.0     0.465385   \n",
       "2  2020-02-01      Manhattan   1.299731    1.004882          0.0     0.000000   \n",
       "3  2020-02-01         Queens   2.832681    1.014117          0.0     0.562669   \n",
       "4  2020-02-01  Staten Island   0.000000    0.000000          0.0     0.000000   \n",
       "\n",
       "   Admit_25_34  Admit_35_44  Admit_45_54  Admit_55_64  ...  Visit_5_12  \\\n",
       "0     2.174093     1.653995     2.788094     4.350853  ...   33.807088   \n",
       "1     0.210607     0.833739     2.638531     2.432701  ...   27.201559   \n",
       "2     0.550164     1.714200     0.501499     1.626475  ...   23.112286   \n",
       "3     0.819486     0.319538     0.972290     1.328515  ...   34.987030   \n",
       "4     0.000000     0.000000     0.000000     4.557539  ...   17.380614   \n",
       "\n",
       "   Visit_13_17  Visit_18_24  Visit_25_34  Visit_35_44  Visit_45_54  \\\n",
       "0     9.609795    18.016807    12.609737    10.475303    10.037137   \n",
       "1     4.218840    11.634625     8.213691     5.002432     7.585777   \n",
       "2     1.783450     8.188235     5.226558     7.713900     2.507497   \n",
       "3    12.868160     9.565378     6.009566     6.071219     6.481932   \n",
       "4     3.335803     7.479633     6.164183    10.115656     1.525134   \n",
       "\n",
       "   Visit_55_64  Visit_65_74   Visit_75  Visit_All_ages  \n",
       "0     9.323256     6.861803   8.603631       17.386665  \n",
       "1     6.950574     5.381894   9.040599       10.918256  \n",
       "2     7.590215     8.200026  11.422883        8.411611  \n",
       "3     6.310447     1.990159  10.221813       12.286597  \n",
       "4     6.076719     4.406742   6.315524        8.820213  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 319,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "sydromic_data_covid_like_Illness.head(5)\n",
    "#CasesByModZCTA_1.to_csv('Downloads/caserate-by-modzcta_1.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caf46fb2-d750-406c-9126-2784edc88417",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}