stats_ski_resorts_universities.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "bc837883-63d4-4a01-98b4-f93e2ab951e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Imports\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from IPython.display import Image\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "3ddde778-3786-4916-abfd-18a3dbe499bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('input_data/covid_confirmed_usafacts.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a121286d-c053-4b9d-abcc-3935f5cf00e0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/joanna_len/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3165: DtypeWarning: Columns (1) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    }
   ],
   "source": [
    "df2 = pd.read_csv('input_data/COVID-19_Vaccinations_in_the_United_States_County.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "537a4a03-5cb4-4543-96fd-5a5039e7a119",
   "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>FIPS</th>\n",
       "      <th>MMWR_week</th>\n",
       "      <th>Recip_County</th>\n",
       "      <th>Recip_State</th>\n",
       "      <th>Completeness_pct</th>\n",
       "      <th>Administered_Dose1_Recip</th>\n",
       "      <th>Administered_Dose1_Pop_Pct</th>\n",
       "      <th>Administered_Dose1_Recip_5Plus</th>\n",
       "      <th>Administered_Dose1_Recip_5PlusPop_Pct</th>\n",
       "      <th>...</th>\n",
       "      <th>Booster_Doses_Vax_Pct_UR_Equity</th>\n",
       "      <th>Booster_Doses_12PlusVax_Pct_UR_Equity</th>\n",
       "      <th>Booster_Doses_18PlusVax_Pct_UR_Equity</th>\n",
       "      <th>Booster_Doses_65PlusVax_Pct_UR_Equity</th>\n",
       "      <th>Census2019</th>\n",
       "      <th>Census2019_5PlusPop</th>\n",
       "      <th>Census2019_5to17Pop</th>\n",
       "      <th>Census2019_12PlusPop</th>\n",
       "      <th>Census2019_18PlusPop</th>\n",
       "      <th>Census2019_65PlusPop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>05/27/2022</td>\n",
       "      <td>19193</td>\n",
       "      <td>21</td>\n",
       "      <td>Woodbury County</td>\n",
       "      <td>IA</td>\n",
       "      <td>97.4</td>\n",
       "      <td>65713.0</td>\n",
       "      <td>63.7</td>\n",
       "      <td>65710.0</td>\n",
       "      <td>68.5</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>103107.0</td>\n",
       "      <td>95901.0</td>\n",
       "      <td>19500.0</td>\n",
       "      <td>85511.0</td>\n",
       "      <td>76401.0</td>\n",
       "      <td>15857.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>05/27/2022</td>\n",
       "      <td>16031</td>\n",
       "      <td>21</td>\n",
       "      <td>Cassia County</td>\n",
       "      <td>ID</td>\n",
       "      <td>97.8</td>\n",
       "      <td>12203.0</td>\n",
       "      <td>50.8</td>\n",
       "      <td>12202.0</td>\n",
       "      <td>55.2</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>24030.0</td>\n",
       "      <td>22097.0</td>\n",
       "      <td>5654.0</td>\n",
       "      <td>19113.0</td>\n",
       "      <td>16443.0</td>\n",
       "      <td>3473.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>05/27/2022</td>\n",
       "      <td>17081</td>\n",
       "      <td>21</td>\n",
       "      <td>Jefferson County</td>\n",
       "      <td>IL</td>\n",
       "      <td>98.1</td>\n",
       "      <td>19659.0</td>\n",
       "      <td>52.2</td>\n",
       "      <td>19649.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>37684.0</td>\n",
       "      <td>35297.0</td>\n",
       "      <td>6009.0</td>\n",
       "      <td>32021.0</td>\n",
       "      <td>29288.0</td>\n",
       "      <td>7332.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>05/27/2022</td>\n",
       "      <td>17147</td>\n",
       "      <td>21</td>\n",
       "      <td>Piatt County</td>\n",
       "      <td>IL</td>\n",
       "      <td>98.1</td>\n",
       "      <td>10903.0</td>\n",
       "      <td>66.7</td>\n",
       "      <td>10885.0</td>\n",
       "      <td>70.6</td>\n",
       "      <td>...</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>16344.0</td>\n",
       "      <td>15422.0</td>\n",
       "      <td>2684.0</td>\n",
       "      <td>14036.0</td>\n",
       "      <td>12738.0</td>\n",
       "      <td>3240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05/27/2022</td>\n",
       "      <td>17163</td>\n",
       "      <td>21</td>\n",
       "      <td>St. Clair County</td>\n",
       "      <td>IL</td>\n",
       "      <td>98.1</td>\n",
       "      <td>186528.0</td>\n",
       "      <td>71.8</td>\n",
       "      <td>186389.0</td>\n",
       "      <td>76.6</td>\n",
       "      <td>...</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>259686.0</td>\n",
       "      <td>243453.0</td>\n",
       "      <td>44316.0</td>\n",
       "      <td>219779.0</td>\n",
       "      <td>199137.0</td>\n",
       "      <td>42597.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742788</th>\n",
       "      <td>12/13/2020</td>\n",
       "      <td>13295</td>\n",
       "      <td>51</td>\n",
       "      <td>Walker County</td>\n",
       "      <td>GA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>69761.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60008.0</td>\n",
       "      <td>54746.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742789</th>\n",
       "      <td>12/13/2020</td>\n",
       "      <td>55123</td>\n",
       "      <td>51</td>\n",
       "      <td>Vernon County</td>\n",
       "      <td>WI</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30822.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25596.0</td>\n",
       "      <td>22811.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742790</th>\n",
       "      <td>12/13/2020</td>\n",
       "      <td>30101</td>\n",
       "      <td>51</td>\n",
       "      <td>Toole County</td>\n",
       "      <td>MT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4736.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4055.0</td>\n",
       "      <td>3753.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742791</th>\n",
       "      <td>12/13/2020</td>\n",
       "      <td>72027</td>\n",
       "      <td>51</td>\n",
       "      <td>Camuy Municipio</td>\n",
       "      <td>PR</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30504.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27205.0</td>\n",
       "      <td>25034.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1742792</th>\n",
       "      <td>12/13/2020</td>\n",
       "      <td>48201</td>\n",
       "      <td>51</td>\n",
       "      <td>Harris County</td>\n",
       "      <td>TX</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4713325.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3877793.0</td>\n",
       "      <td>3467445.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1742793 rows × 66 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Date   FIPS  MMWR_week      Recip_County Recip_State  \\\n",
       "0        05/27/2022  19193         21   Woodbury County          IA   \n",
       "1        05/27/2022  16031         21     Cassia County          ID   \n",
       "2        05/27/2022  17081         21  Jefferson County          IL   \n",
       "3        05/27/2022  17147         21      Piatt County          IL   \n",
       "4        05/27/2022  17163         21  St. Clair County          IL   \n",
       "...             ...    ...        ...               ...         ...   \n",
       "1742788  12/13/2020  13295         51     Walker County          GA   \n",
       "1742789  12/13/2020  55123         51     Vernon County          WI   \n",
       "1742790  12/13/2020  30101         51      Toole County          MT   \n",
       "1742791  12/13/2020  72027         51   Camuy Municipio          PR   \n",
       "1742792  12/13/2020  48201         51     Harris County          TX   \n",
       "\n",
       "         Completeness_pct  Administered_Dose1_Recip  \\\n",
       "0                    97.4                   65713.0   \n",
       "1                    97.8                   12203.0   \n",
       "2                    98.1                   19659.0   \n",
       "3                    98.1                   10903.0   \n",
       "4                    98.1                  186528.0   \n",
       "...                   ...                       ...   \n",
       "1742788               0.0                       0.0   \n",
       "1742789               0.0                       0.0   \n",
       "1742790               0.0                       0.0   \n",
       "1742791               0.0                       0.0   \n",
       "1742792               0.0                       0.0   \n",
       "\n",
       "         Administered_Dose1_Pop_Pct  Administered_Dose1_Recip_5Plus  \\\n",
       "0                              63.7                         65710.0   \n",
       "1                              50.8                         12202.0   \n",
       "2                              52.2                         19649.0   \n",
       "3                              66.7                         10885.0   \n",
       "4                              71.8                        186389.0   \n",
       "...                             ...                             ...   \n",
       "1742788                         0.0                             NaN   \n",
       "1742789                         0.0                             NaN   \n",
       "1742790                         0.0                             NaN   \n",
       "1742791                         0.0                             NaN   \n",
       "1742792                         0.0                             NaN   \n",
       "\n",
       "         Administered_Dose1_Recip_5PlusPop_Pct  ...  \\\n",
       "0                                         68.5  ...   \n",
       "1                                         55.2  ...   \n",
       "2                                         55.7  ...   \n",
       "3                                         70.6  ...   \n",
       "4                                         76.6  ...   \n",
       "...                                        ...  ...   \n",
       "1742788                                    NaN  ...   \n",
       "1742789                                    NaN  ...   \n",
       "1742790                                    NaN  ...   \n",
       "1742791                                    NaN  ...   \n",
       "1742792                                    NaN  ...   \n",
       "\n",
       "         Booster_Doses_Vax_Pct_UR_Equity  \\\n",
       "0                                    3.0   \n",
       "1                                    6.0   \n",
       "2                                    7.0   \n",
       "3                                    4.0   \n",
       "4                                    3.0   \n",
       "...                                  ...   \n",
       "1742788                              NaN   \n",
       "1742789                              NaN   \n",
       "1742790                              NaN   \n",
       "1742791                              NaN   \n",
       "1742792                              NaN   \n",
       "\n",
       "         Booster_Doses_12PlusVax_Pct_UR_Equity  \\\n",
       "0                                          3.0   \n",
       "1                                          6.0   \n",
       "2                                          8.0   \n",
       "3                                          4.0   \n",
       "4                                          3.0   \n",
       "...                                        ...   \n",
       "1742788                                    NaN   \n",
       "1742789                                    NaN   \n",
       "1742790                                    NaN   \n",
       "1742791                                    NaN   \n",
       "1742792                                    NaN   \n",
       "\n",
       "         Booster_Doses_18PlusVax_Pct_UR_Equity  \\\n",
       "0                                          3.0   \n",
       "1                                          6.0   \n",
       "2                                          8.0   \n",
       "3                                          4.0   \n",
       "4                                          3.0   \n",
       "...                                        ...   \n",
       "1742788                                    NaN   \n",
       "1742789                                    NaN   \n",
       "1742790                                    NaN   \n",
       "1742791                                    NaN   \n",
       "1742792                                    NaN   \n",
       "\n",
       "         Booster_Doses_65PlusVax_Pct_UR_Equity  Census2019  \\\n",
       "0                                          3.0    103107.0   \n",
       "1                                          6.0     24030.0   \n",
       "2                                          7.0     37684.0   \n",
       "3                                          4.0     16344.0   \n",
       "4                                          3.0    259686.0   \n",
       "...                                        ...         ...   \n",
       "1742788                                    NaN     69761.0   \n",
       "1742789                                    NaN     30822.0   \n",
       "1742790                                    NaN      4736.0   \n",
       "1742791                                    NaN     30504.0   \n",
       "1742792                                    NaN   4713325.0   \n",
       "\n",
       "         Census2019_5PlusPop  Census2019_5to17Pop  Census2019_12PlusPop  \\\n",
       "0                    95901.0              19500.0               85511.0   \n",
       "1                    22097.0               5654.0               19113.0   \n",
       "2                    35297.0               6009.0               32021.0   \n",
       "3                    15422.0               2684.0               14036.0   \n",
       "4                   243453.0              44316.0              219779.0   \n",
       "...                      ...                  ...                   ...   \n",
       "1742788                  NaN                  NaN               60008.0   \n",
       "1742789                  NaN                  NaN               25596.0   \n",
       "1742790                  NaN                  NaN                4055.0   \n",
       "1742791                  NaN                  NaN               27205.0   \n",
       "1742792                  NaN                  NaN             3877793.0   \n",
       "\n",
       "         Census2019_18PlusPop  Census2019_65PlusPop  \n",
       "0                     76401.0               15857.0  \n",
       "1                     16443.0                3473.0  \n",
       "2                     29288.0                7332.0  \n",
       "3                     12738.0                3240.0  \n",
       "4                    199137.0               42597.0  \n",
       "...                       ...                   ...  \n",
       "1742788               54746.0                   NaN  \n",
       "1742789               22811.0                   NaN  \n",
       "1742790                3753.0                   NaN  \n",
       "1742791               25034.0                   NaN  \n",
       "1742792             3467445.0                   NaN  \n",
       "\n",
       "[1742793 rows x 66 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ce8b2a9-8fa4-459c-866b-559f3b612f01",
   "metadata": {},
   "source": [
    "### Counties with ski resorts\n",
    "1. Summit County - Colorado [FIPS: 08117]\n",
    "2. Eagle County - Colorado [FIPS: 08037]\n",
    "3. Pitkin County - Colorado [FIPS: 08097]\n",
    "4. San Miguel County - Colorado [FIPS: 08113]\n",
    "5. Summit County - Utah [FIPS: 49043]\n",
    "6. Teton County - Wyoming [FIPS: 56039]\n",
    "7. Gallatin County and Madison County - Montana [FIPS: 30031, FIPS: 30057]\n",
    "8. Fresno County - Pallisades California [FIPS: 06019]\n",
    "9. Blaine County -  Idaho [FIPS: 16013]\n",
    "10. Taos County - New Mexico [FIPS: 35055]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "f29d94e5-1c69-43cc-81f5-61bd888abf1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "names = [\n",
    "    '1. Summit County - Colorado [FIPS: 08117]',\n",
    "'2. Eagle County - Colorado [FIPS: 08037]',\n",
    "'3. Pitkin County - Colorado [FIPS: 08097]',\n",
    "'4. San Miguel County - Colorado [FIPS: 08113]',\n",
    "'5. Summit County - Utah [FIPS: 49043]',\n",
    "'6. Teton County - Wyoming [FIPS: 56039]',\n",
    "'7. Gallatin County and[FIPS: 30031]',\n",
    "    ' Madison County - Montana [FIPS: 30057]',\n",
    "'8. Fresno County - Pallisades California [FIPS: 06019]',\n",
    "'9. Blaine County -  Idaho [FIPS: 16013]',\n",
    "'10. Taos County - New Mexico [FIPS: 35055]'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "c05f4f6d-eebe-46ab-90ce-d991f2689bb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "fips_ski = [8117, 8037, 8097, 8113, 49043, 56039, 30031, 30057, 6019, 16013, 35055]\n",
    "fips_ski2 = ['08117', '08037', '08097', '08113', '49043', '56039', '30031', '30057', '06019', '16013', '35055']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a2ceb30-723a-4722-b66b-a802e497b46a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "a03c13a4-1f9e-4fc8-b8f7-bddc19d2d503",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "42.6\n",
      "nan\n",
      "42.6\n",
      "43.9\n",
      "43.0\n",
      "47.3\n"
     ]
    }
   ],
   "source": [
    "# 30057\n",
    "idx = 7\n",
    "df2_tmp = df2.loc[df2['Date'] == '12/14/2021']\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Series_Complete_Pop_Pct'].values[0])\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Booster_Doses_Vax_Pct'].values[0])\n",
    "df2_tmp = df2.loc[df2['Date'] == '12/15/2021']\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Series_Complete_Pop_Pct'].values[0])\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Booster_Doses_Vax_Pct'].values[0])\n",
    "df2_tmp = df2.loc[df2['Date'] == '12/31/2021']\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Series_Complete_Pop_Pct'].values[0])\n",
    "print(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Booster_Doses_Vax_Pct'].values[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "57af5b6f-c189-43f9-8dbe-ab375787ed0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ski resorts\n",
      "______\n",
      "1. Summit County - Colorado [FIPS: 08117]\n",
      "New cases in April 272\n",
      "Fully vaccinated 1st April 5.8\n",
      "---\n",
      "New cases 15 December to 15 January272\n",
      "Fully vaccinated 15th December 80.0\n",
      "Booster 15th December 32.2\n",
      "______\n",
      "2. Eagle County - Colorado [FIPS: 08037]\n",
      "New cases in April 459\n",
      "Fully vaccinated 1st April 7.3\n",
      "---\n",
      "New cases 15 December to 15 January459\n",
      "Fully vaccinated 15th December 77.4\n",
      "Booster 15th December 32.4\n",
      "______\n",
      "3. Pitkin County - Colorado [FIPS: 08097]\n",
      "New cases in April 123\n",
      "Fully vaccinated 1st April 3.9\n",
      "---\n",
      "New cases 15 December to 15 January123\n",
      "Fully vaccinated 15th December 78.1\n",
      "Booster 15th December 34.9\n",
      "______\n",
      "4. San Miguel County - Colorado [FIPS: 08113]\n",
      "New cases in April 23\n",
      "Fully vaccinated 1st April 10.8\n",
      "---\n",
      "New cases 15 December to 15 January23\n",
      "Fully vaccinated 15th December 77.9\n",
      "Booster 15th December 43.9\n",
      "______\n",
      "5. Summit County - Utah [FIPS: 49043]\n",
      "New cases in April 167\n",
      "Fully vaccinated 1st April 18.9\n",
      "---\n",
      "New cases 15 December to 15 January167\n",
      "Fully vaccinated 15th December 81.6\n",
      "Booster 15th December 33.9\n",
      "______\n",
      "6. Teton County - Wyoming [FIPS: 56039]\n",
      "New cases in April 112\n",
      "Fully vaccinated 1st April 26.0\n",
      "---\n",
      "New cases 15 December to 15 January112\n",
      "Fully vaccinated 15th December 87.2\n",
      "Booster 15th December 35.4\n",
      "______\n",
      "7. Gallatin County and[FIPS: 30031]\n",
      "New cases in April 1248\n",
      "Fully vaccinated 1st April 14.8\n",
      "---\n",
      "New cases 15 December to 15 January1248\n",
      "Fully vaccinated 15th December 55.4\n",
      "Booster 15th December 36.7\n",
      "______\n",
      " Madison County - Montana [FIPS: 30057]\n",
      "New cases in April 54\n",
      "Fully vaccinated 1st April 16.7\n",
      "---\n",
      "New cases 15 December to 15 January54\n",
      "Fully vaccinated 15th December 42.6\n",
      "Booster 15th December 43.9\n",
      "______\n",
      "8. Fresno County - Pallisades California [FIPS: 06019]\n",
      "New cases in April 1645\n",
      "Fully vaccinated 1st April 15.0\n",
      "---\n",
      "New cases 15 December to 15 January1645\n",
      "Fully vaccinated 15th December 55.7\n",
      "Booster 15th December 26.0\n",
      "______\n",
      "9. Blaine County -  Idaho [FIPS: 16013]\n",
      "New cases in April 95\n",
      "Fully vaccinated 1st April 28.5\n",
      "---\n",
      "New cases 15 December to 15 January95\n",
      "Fully vaccinated 15th December 70.1\n",
      "Booster 15th December 40.7\n",
      "______\n",
      "10. Taos County - New Mexico [FIPS: 35055]\n",
      "New cases in April 38\n",
      "Fully vaccinated 1st April 16.3\n",
      "---\n",
      "New cases 15 December to 15 January38\n",
      "Fully vaccinated 15th December 77.9\n",
      "Booster 15th December 43.7\n"
     ]
    }
   ],
   "source": [
    "print (\"Ski resorts\")\n",
    "for idx, x in enumerate(fips_ski):\n",
    "    print(\"______\")\n",
    "    print(names[idx])\n",
    "    print(\"New cases in April \" + str(df.loc[df['countyFIPS'] == x]['2021-04-30'].values[0] - df.loc[df['countyFIPS'] == x]['2021-04-01'].values[0]))\n",
    "    df2_tmp = df2.loc[df2['Date'] == '04/01/2021']\n",
    "    print(\"Fully vaccinated 1st April \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Series_Complete_Pop_Pct'].values[0]))\n",
    "    print(\"---\")\n",
    "    print(\"New cases 15 December to 15 January\" + str(df.loc[df['countyFIPS'] == x]['2021-04-30'].values[0] - df.loc[df['countyFIPS'] == x]['2021-04-01'].values[0]))\n",
    "    df2_tmp = df2.loc[df2['Date'] == '12/15/2021']\n",
    "    print(\"Fully vaccinated 15th December \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Series_Complete_Pop_Pct'].values[0]))\n",
    "    print(\"Booster 15th December \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_ski2[idx]]['Booster_Doses_Vax_Pct'].values[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69fe5331-cace-43e2-8db8-260c59a6f625",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "0a24f553-939c-4568-9d25-e77e0bf0fae7",
   "metadata": {},
   "source": [
    "### Counties with universities\n",
    "1. Santa Clara County - California - Stanford [06085]\n",
    "2. St. Clair County - Michigan - Yale [26147]\n",
    "3. Mercer County - New Jersey - Princeton [34021]\n",
    "4. Alameda County - California - Berkeley [06001]\n",
    "5. Pasadena - Los Angeles County, California - CalTech [06037]\n",
    "6. Tompkins County - New York - Cornell [36109]\n",
    "7. Champaign County - Illinois - Univ Illin [17019]\n",
    "8. Tippecanoe County - Indiana - Purdue [18157]\n",
    "9. Charlottesville - Virginia - UVa [51540]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "58e70b45-1d06-4cab-8583-3fa15a1dbd2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "fips_uni = [6085, 26147, 34021, 6001, 6037, 36109, 17019, 18157, 51540]\n",
    "fips_uni2 = ['06085', '26147', '34021', '06001', '06037', '36109', '17019', '18157', '51540']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "965cb53d-469b-4b4d-8ebb-2ac3de726cb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "names = [\n",
    "'1. Santa Clara County - California - Stanford [06085]',\n",
    "'2. St. Clair County - Michigan - Yale [26147]',\n",
    "'3. Mercer County - New Jersey - Princeton [34021]',\n",
    "'4. Alameda County - California - Berkeley [06001]',\n",
    "'5. Pasadena - Los Angeles County, California - CalTech [06037]',\n",
    "'6. Tompkins County - New York - Cornell [36109]',\n",
    "'7. Champaign County - Illinois - Univ Illin [17019]',\n",
    "'8. Tippecanoe County - Indiana - Purdue [18157]',\n",
    "'9. Charlottesville - Virginia - UVa [51540]'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a026ee17-b578-4174-b295-17409a4cfb6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "print (\"Universities\")\n",
    "for idx, x in enumerate(fips_uni):\n",
    "    print(\"______\")\n",
    "    print(names[idx])\n",
    "    print(\"New cases in April \" + str(df.loc[df['countyFIPS'] == x]['2021-04-30'].values[0] - df.loc[df['countyFIPS'] == x]['2021-04-01'].values[0]))\n",
    "    df2_tmp = df2.loc[df2['Date'] == '04/01/2021']\n",
    "    print(\"Fully vaccinated 1st April \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_uni2[idx]]['Series_Complete_Pop_Pct'].values[0]))\n",
    "    print(\"---\")\n",
    "    print(\"New cases 15 December to 15 January\" + str(df.loc[df['countyFIPS'] == x]['2021-04-30'].values[0] - df.loc[df['countyFIPS'] == x]['2021-04-01'].values[0]))\n",
    "    df2_tmp = df2.loc[df2['Date'] == '12/15/2021']\n",
    "    print(\"Fully vaccinated 15th December \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_uni2[idx]]['Series_Complete_Pop_Pct'].values[0]))\n",
    "    print(\"Booster 15th December \" + str(df2_tmp.loc[df2_tmp['FIPS'] == fips_uni2[idx]]['Booster_Doses_Vax_Pct'].values[0]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e52da5a-e6cd-4c19-a7d8-0b1158f29526",
   "metadata": {},
   "source": [
    "### Population density"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "698ca3e4-e92e-4f3b-a039-8834c630b847",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pop = pd.read_csv('input_data/covid_county_population_usafacts.csv')\n",
    "\n",
    "pop_ski = list()\n",
    "for x in fips_ski:\n",
    "    pop_ski.append(df_pop.loc[df_pop['countyFIPS'] == x]['population'].item())\n",
    "\n",
    "pop_uni = list()\n",
    "for x in fips_uni:\n",
    "    pop_uni.append(df_pop.loc[df_pop['countyFIPS'] == x]['population'].item())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "47607b98-122f-41bd-8df6-0efc4bd3250c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_area = pd.read_csv('input_data/county_complete.csv')\n",
    "\n",
    "area_ski = list()\n",
    "for fips in fips_ski:\n",
    "    area_ski.append(df_area.loc[df_area['fips'] == fips]['area_2010'].item())\n",
    "    \n",
    "area_uni = list()\n",
    "for fips in fips_uni:\n",
    "    area_uni.append(df_area.loc[df_area['fips'] == fips]['area_2010'].item())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "b15e16c0-a0c2-47d8-a9b5-c9d22334b318",
   "metadata": {},
   "outputs": [],
   "source": [
    "dens_ski = [round(pop / area_ski[idx], 2) for idx, pop in enumerate(pop_ski)]\n",
    "dens_uni = [round(pop / area_uni[idx], 2) for idx, pop in enumerate(pop_uni)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "cd0245a6-76b8-4662-96bd-30cc8d849899",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[50.97, 32.73, 18.3, 6.36, 22.52, 5.87, 43.97, 2.4, 167.69, 8.71, 14.85]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dens_ski"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "5ffbd028-018f-4d53-bd7e-8ec4207ce4ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1494.34, 220.65, 1636.22, 2261.55, 2473.98, 215.27, 210.47, 391.61, 4615.82]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dens_uni"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93898b12-d3a8-4071-9bd5-bce18d1b32d4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}