1.CountyVaccine_Manual.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "square-transition",
   "metadata": {},
   "source": [
    "# I. US County Race Population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eleven-moldova",
   "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>GEOID</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Total_Whole</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_AsianC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>Autauga, Alabama</td>\n",
       "      <td>55869</td>\n",
       "      <td>42607</td>\n",
       "      <td>11237</td>\n",
       "      <td>266</td>\n",
       "      <td>656</td>\n",
       "      <td>43563</td>\n",
       "      <td>11784</td>\n",
       "      <td>575</td>\n",
       "      <td>950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>Baldwin, Alabama</td>\n",
       "      <td>223234</td>\n",
       "      <td>195198</td>\n",
       "      <td>19593</td>\n",
       "      <td>1742</td>\n",
       "      <td>2380</td>\n",
       "      <td>199103</td>\n",
       "      <td>21354</td>\n",
       "      <td>3603</td>\n",
       "      <td>3351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Barbour, Alabama</td>\n",
       "      <td>24686</td>\n",
       "      <td>12134</td>\n",
       "      <td>11906</td>\n",
       "      <td>170</td>\n",
       "      <td>116</td>\n",
       "      <td>12380</td>\n",
       "      <td>12117</td>\n",
       "      <td>297</td>\n",
       "      <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb</td>\n",
       "      <td>Bibb, Alabama</td>\n",
       "      <td>22394</td>\n",
       "      <td>17191</td>\n",
       "      <td>4763</td>\n",
       "      <td>103</td>\n",
       "      <td>48</td>\n",
       "      <td>17420</td>\n",
       "      <td>4907</td>\n",
       "      <td>207</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount</td>\n",
       "      <td>Blount, Alabama</td>\n",
       "      <td>57826</td>\n",
       "      <td>55412</td>\n",
       "      <td>978</td>\n",
       "      <td>370</td>\n",
       "      <td>185</td>\n",
       "      <td>56167</td>\n",
       "      <td>1276</td>\n",
       "      <td>820</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   GEOID    State   County Geographic Area Name  Total_Whole  Total_White  \\\n",
       "0   1001  Alabama  Autauga     Autauga, Alabama        55869        42607   \n",
       "1   1003  Alabama  Baldwin     Baldwin, Alabama       223234       195198   \n",
       "2   1005  Alabama  Barbour     Barbour, Alabama        24686        12134   \n",
       "3   1007  Alabama     Bibb        Bibb, Alabama        22394        17191   \n",
       "4   1009  Alabama   Blount      Blount, Alabama        57826        55412   \n",
       "\n",
       "   Total_Black  Total_AIndA  Total_Asian  Total_WhiteC  Total_BlackC  \\\n",
       "0        11237          266          656         43563         11784   \n",
       "1        19593         1742         2380        199103         21354   \n",
       "2        11906          170          116         12380         12117   \n",
       "3         4763          103           48         17420          4907   \n",
       "4          978          370          185         56167          1276   \n",
       "\n",
       "   Total_AIndAC  Total_AsianC  \n",
       "0           575           950  \n",
       "1          3603          3351  \n",
       "2           297           152  \n",
       "3           207            97  \n",
       "4           820           312  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "from datetime import datetime  \n",
    "\n",
    "\n",
    "def diagnose_Rate(T, **kwargs):\n",
    "    info_list = []\n",
    "    for col in T.columns:\n",
    "        if col in ['Rate_Black', 'Rate_White', 'Rate_Parity', 'FullRate_Black', 'FullRate_White', 'FullRate_Parity']:\n",
    "            d = {'name': col}\n",
    "            for k, v in kwargs.items():\n",
    "                d[k] = v\n",
    "                \n",
    "            for k,v in T[col].describe().to_dict().items():\n",
    "                d[k] = v\n",
    "            info_list.append(d)\n",
    "\n",
    "    Report = pd.DataFrame(info_list)\n",
    "    return Report\n",
    "\n",
    "\n",
    "\n",
    "path = 'DataMerge/Data/C-AnnualCountyPopulation/US_COUNTY_POPULATION.csv'\n",
    "US_COUNTY_POPULATION = pd.read_csv(path)\n",
    "US_COUNTY_POPULATION.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "painful-intersection",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "STATE_VACCINE = {}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "essential-feeling",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter the date of collecting data (Today's data in YYYY-MM-DD):\n",
      "2021-05-20\n",
      "Hello, 2021-05-20\n"
     ]
    }
   ],
   "source": [
    "print(\"Enter the date of collecting data (Today's data in YYYY-MM-DD):\")\n",
    "Today_Date = input()\n",
    "\n",
    "assert Today_Date == str(datetime.now().date())\n",
    "\n",
    "print('Hello, ' + Today_Date)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "desirable-commitment",
   "metadata": {},
   "source": [
    "# II. API States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "recovered-musical",
   "metadata": {},
   "source": [
    "## [!] A. California* (Done) XX\n",
    "\n",
    "\n",
    "https://covid19.ca.gov/vaccines/#California-vaccines-dashboard\n",
    "\n",
    "\n",
    "https://data.ca.gov/dataset/covid-19-vaccine-progress-dashboard-data \n",
    "\n",
    "\n",
    "https://data.ca.gov/dataset/covid-19-vaccine-progress-dashboard-data/resource/c020ef6b-2116-4775-b11d-9df2875096ab # click here\n",
    "\n",
    "\n",
    "\n",
    "Statewide COVID-19 Vaccines Administered By CountyCSV\n",
    "\n",
    "\n",
    "Direct Vaccine Rate \n",
    "\n",
    "No Fully Rate information\n",
    "\n",
    "\n",
    "\n",
    "Combine Race and Ethnicty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "indoor-passenger",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CountyVaccine/TMP/May20/California/covid19vaccinesbycounty.csv'"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'CountyVaccine/TMP/May20/California/covid19vaccinesbycounty.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "intellectual-silly",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter California Manually collected Data:  CountyVaccine/TMP/May20/California/covid19vaccinesbycounty (4).csv\n",
      "Wrong file path...\n",
      "Enter California Manually collected Data:  CountyVaccine/TMP/May20/California/covid19vaccinesbycounty.csv\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/California/covid19vaccinesbycounty.csv\n",
      "There is no information for current Date: 2021-05-20\n",
      "Please Enter a new One:2021-05-19\n",
      "We get California Information for Date: 2021-05-19\n",
      "(62, 4)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>county</th>\n",
       "      <th>california_flag</th>\n",
       "      <th>cumulative_at_least_one_dose</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>All CA Counties</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20328003</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>All CA and Non-CA Counties</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20733486</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Outside California</td>\n",
       "      <td>Not in California</td>\n",
       "      <td>400563</td>\n",
       "      <td>Outside California, Not in California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>Not in California</td>\n",
       "      <td>4919</td>\n",
       "      <td>Unknown, Not in California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>California</td>\n",
       "      <td>1032515</td>\n",
       "      <td>Alameda, California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Tulare</td>\n",
       "      <td>California</td>\n",
       "      <td>164270</td>\n",
       "      <td>Tulare, California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Tuolumne</td>\n",
       "      <td>California</td>\n",
       "      <td>22749</td>\n",
       "      <td>Tuolumne, California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>California</td>\n",
       "      <td>451254</td>\n",
       "      <td>Ventura, California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Yolo</td>\n",
       "      <td>California</td>\n",
       "      <td>119605</td>\n",
       "      <td>Yolo, California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>California</td>\n",
       "      <td>22740</td>\n",
       "      <td>Yuba, California</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>62 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        county    california_flag  \\\n",
       "0              All CA Counties                NaN   \n",
       "1   All CA and Non-CA Counties                NaN   \n",
       "2           Outside California  Not in California   \n",
       "3                      Unknown  Not in California   \n",
       "4                      Alameda         California   \n",
       "..                         ...                ...   \n",
       "57                      Tulare         California   \n",
       "58                    Tuolumne         California   \n",
       "59                     Ventura         California   \n",
       "60                        Yolo         California   \n",
       "61                        Yuba         California   \n",
       "\n",
       "    cumulative_at_least_one_dose                   Geographic Area Name  \n",
       "0                       20328003                                    NaN  \n",
       "1                       20733486                                    NaN  \n",
       "2                         400563  Outside California, Not in California  \n",
       "3                           4919             Unknown, Not in California  \n",
       "4                        1032515                    Alameda, California  \n",
       "..                           ...                                    ...  \n",
       "57                        164270                     Tulare, California  \n",
       "58                         22749                   Tuolumne, California  \n",
       "59                        451254                    Ventura, California  \n",
       "60                        119605                       Yolo, California  \n",
       "61                         22740                       Yuba, California  \n",
       "\n",
       "[62 rows x 4 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "from datetime import datetime \n",
    "import numpy as np\n",
    "import requests\n",
    "import time\n",
    " \n",
    "Date = Today_Date\n",
    "    \n",
    "while True:\n",
    "    Path = input('Enter California Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n",
    "\n",
    "\n",
    "\n",
    "# CalInfo: county total vaccine number each date\n",
    "CalInfo = pd.read_csv(Path)\n",
    "CalInfo['administered_date'] = pd.to_datetime(CalInfo['administered_date'])\n",
    "CalInfo = CalInfo[CalInfo['administered_date'] == Date].reset_index(drop = True)\n",
    "\n",
    "\n",
    "while len(CalInfo) == 0:\n",
    "    print('There is no information for current Date:', Date)\n",
    "    Date = input('Please Enter a new One:')\n",
    "    CalInfo = pd.read_csv(Path)\n",
    "    CalInfo = CalInfo[CalInfo['administered_date'] == Date].reset_index(drop = True)\n",
    "    \n",
    "\n",
    "print('We get California Information for Date:', Date)\n",
    "CalInfo = CalInfo[['county', 'california_flag', 'cumulative_at_least_one_dose']]\n",
    "CalInfo['Geographic Area Name'] = CalInfo['county'] + ', ' + CalInfo['california_flag']\n",
    "print(CalInfo.shape)\n",
    "\n",
    "\n",
    "CalInfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "opponent-bunch",
   "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>County</th>\n",
       "      <th>Latest_Date</th>\n",
       "      <th>American Indian or Alaska Native (AI/AN)</th>\n",
       "      <th>Asian American</th>\n",
       "      <th>Black</th>\n",
       "      <th>Latino</th>\n",
       "      <th>Multi-race</th>\n",
       "      <th>Native Hawaiian or Other Pacific Islander (NHPI)</th>\n",
       "      <th>White</th>\n",
       "      <th>Other</th>\n",
       "      <th>Unknown</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.003037</td>\n",
       "      <td>0.322841</td>\n",
       "      <td>0.067144</td>\n",
       "      <td>0.139323</td>\n",
       "      <td>0.012683</td>\n",
       "      <td>0.007546</td>\n",
       "      <td>0.301939</td>\n",
       "      <td>0.086290</td>\n",
       "      <td>0.059195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alpine</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>0.017032</td>\n",
       "      <td>0.012165</td>\n",
       "      <td>0.002433</td>\n",
       "      <td>0.012165</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.192214</td>\n",
       "      <td>0.010949</td>\n",
       "      <td>0.753041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amador</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.008574</td>\n",
       "      <td>0.017790</td>\n",
       "      <td>0.005144</td>\n",
       "      <td>0.082789</td>\n",
       "      <td>0.001179</td>\n",
       "      <td>0.003644</td>\n",
       "      <td>0.739792</td>\n",
       "      <td>0.091041</td>\n",
       "      <td>0.050048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Butte</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.012459</td>\n",
       "      <td>0.044084</td>\n",
       "      <td>0.010821</td>\n",
       "      <td>0.118308</td>\n",
       "      <td>0.013414</td>\n",
       "      <td>0.002051</td>\n",
       "      <td>0.703553</td>\n",
       "      <td>0.058818</td>\n",
       "      <td>0.036493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Calaveras</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.005031</td>\n",
       "      <td>0.017243</td>\n",
       "      <td>0.005713</td>\n",
       "      <td>0.073899</td>\n",
       "      <td>0.000839</td>\n",
       "      <td>0.002411</td>\n",
       "      <td>0.619811</td>\n",
       "      <td>0.082075</td>\n",
       "      <td>0.192977</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      County Latest_Date  American Indian or Alaska Native (AI/AN)  \\\n",
       "0    Alameda  2021-05-18                                  0.003037   \n",
       "1     Alpine  2021-05-17                                  0.017032   \n",
       "2     Amador  2021-05-18                                  0.008574   \n",
       "3      Butte  2021-05-18                                  0.012459   \n",
       "4  Calaveras  2021-05-18                                  0.005031   \n",
       "\n",
       "   Asian American     Black    Latino  Multi-race  \\\n",
       "0        0.322841  0.067144  0.139323    0.012683   \n",
       "1        0.012165  0.002433  0.012165    0.000000   \n",
       "2        0.017790  0.005144  0.082789    0.001179   \n",
       "3        0.044084  0.010821  0.118308    0.013414   \n",
       "4        0.017243  0.005713  0.073899    0.000839   \n",
       "\n",
       "   Native Hawaiian or Other Pacific Islander (NHPI)     White     Other  \\\n",
       "0                                          0.007546  0.301939  0.086290   \n",
       "1                                          0.000000  0.192214  0.010949   \n",
       "2                                          0.003644  0.739792  0.091041   \n",
       "3                                          0.002051  0.703553  0.058818   \n",
       "4                                          0.002411  0.619811  0.082075   \n",
       "\n",
       "    Unknown  \n",
       "0  0.059195  \n",
       "1  0.753041  \n",
       "2  0.050048  \n",
       "3  0.036493  \n",
       "4  0.192977  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "def get_California_Raw_and_postprocess(US_COUNTY_POPULATION):\n",
    "    state = 'California'\n",
    "    ca_county_list = [i.lower().replace(' ','') for i in US_COUNTY_POPULATION[US_COUNTY_POPULATION['State'] == 'California']['County'].values]\n",
    "\n",
    "    L = []\n",
    "    error_list = []\n",
    "    for i in ca_county_list:\n",
    "\n",
    "        api_url = 'https://files.covid19.ca.gov/data/vaccine-equity/race-ethnicity/vaccines_by_race_ethnicity_{}.json'.format(i)\n",
    "\n",
    "        resp = requests.get(api_url)# .to_json()\n",
    "        data = resp.json() # resp\n",
    "\n",
    "        try:\n",
    "\n",
    "            d = {}\n",
    "            d['County'] = data['meta']['REGION']\n",
    "            d['Latest_Date'] = data['meta']['LATEST_ADMIN_DATE']\n",
    "            for i in data['data']:\n",
    "                d[i['CATEGORY']] = i['METRIC_VALUE']\n",
    "            L.append(d)\n",
    "\n",
    "        except:\n",
    "            print(i)\n",
    "            error_list.append(i)\n",
    "\n",
    "    CA_County_Vaccine_Race = pd.DataFrame(L)\n",
    "    \n",
    "    return CA_County_Vaccine_Race\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def standardize_California(CA_County_Vaccine_Race, US_COUNTY_POPULATION):\n",
    "    \n",
    "    # IL_County_Vaccine_Race = pd.DataFrame(L)\n",
    "    state = 'California'\n",
    "\n",
    "    new_L = []\n",
    "\n",
    "\n",
    "    for idx, row in CA_County_Vaccine_Race.iterrows():\n",
    "        d = row.to_dict()\n",
    "\n",
    "        new_d = {}\n",
    "\n",
    "\n",
    "        new_d['State'] = state\n",
    "        new_d['County'] = d['County']\n",
    "        \n",
    "        RenameCounties = {\n",
    "            # 'Mckean': 'McKean'\n",
    "        }\n",
    "        if new_d['County'] in RenameCounties:\n",
    "            new_d['County'] = RenameCounties[new_d['County']]\n",
    "\n",
    "        new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "\n",
    "\n",
    "\n",
    "        # new_d['Numb_White'] = np.nan\n",
    "        new_d['Rate_White_V'] = d['White']\n",
    "\n",
    "        # new_d['Numb_Black'] = np.nan\n",
    "        new_d['Rate_Black_V'] = d['Black']\n",
    "\n",
    "        # new_d['Numb_Asian'] = np.nan\n",
    "        new_d['Rate_Asian_V'] = d['Asian American']\n",
    "\n",
    "        # new_d['Numb_AIndA'] = np.nan\n",
    "        new_d['Rate_AIndA_V'] = d['American Indian or Alaska Native (AI/AN)']\n",
    "\n",
    "        # new_d['Numb_Other'] = np.nan\n",
    "        new_d['Rate_Other_V'] = d['Other']\n",
    "\n",
    "        new_d['Date'] = d['Latest_Date']\n",
    "\n",
    "        new_L.append(new_d)\n",
    "\n",
    "\n",
    "    Data = pd.DataFrame(new_L)\n",
    "\n",
    "    not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "    if len(not_included) > 0:\n",
    "        print('Not included Counties', not_included)\n",
    "\n",
    "    \n",
    "    T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "        \n",
    "        \n",
    "    T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "    \n",
    "    return T\n",
    "    \n",
    "\n",
    "    \n",
    "\n",
    "CA_County_Vaccine_Race = get_California_Raw_and_postprocess(US_COUNTY_POPULATION)\n",
    "\n",
    "# CA_County_Vaccine_Race.to_csv('FinalResult/Post_California.csv')\n",
    "# print(CA_County_Vaccine_Race.shape)\n",
    "CA_County_Vaccine_Race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "negative-cuisine",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 18)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Rate_AIndA_V</th>\n",
       "      <th>Rate_Asian_V</th>\n",
       "      <th>Rate_Black_V</th>\n",
       "      <th>Rate_Other_V</th>\n",
       "      <th>Rate_White_V</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.003037</td>\n",
       "      <td>0.322841</td>\n",
       "      <td>0.067144</td>\n",
       "      <td>0.086290</td>\n",
       "      <td>0.301939</td>\n",
       "      <td>17797</td>\n",
       "      <td>37797</td>\n",
       "      <td>540391</td>\n",
       "      <td>594001</td>\n",
       "      <td>184368</td>\n",
       "      <td>214392</td>\n",
       "      <td>823593</td>\n",
       "      <td>899377</td>\n",
       "      <td>1671329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>Alpine, California</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>0.017032</td>\n",
       "      <td>0.012165</td>\n",
       "      <td>0.002433</td>\n",
       "      <td>0.010949</td>\n",
       "      <td>0.192214</td>\n",
       "      <td>290</td>\n",
       "      <td>310</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>767</td>\n",
       "      <td>813</td>\n",
       "      <td>1129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>California</td>\n",
       "      <td>Amador</td>\n",
       "      <td>Amador, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.008574</td>\n",
       "      <td>0.017790</td>\n",
       "      <td>0.005144</td>\n",
       "      <td>0.091041</td>\n",
       "      <td>0.739792</td>\n",
       "      <td>925</td>\n",
       "      <td>1644</td>\n",
       "      <td>665</td>\n",
       "      <td>1043</td>\n",
       "      <td>1066</td>\n",
       "      <td>1374</td>\n",
       "      <td>35636</td>\n",
       "      <td>36844</td>\n",
       "      <td>39752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>Butte</td>\n",
       "      <td>Butte, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.012459</td>\n",
       "      <td>0.044084</td>\n",
       "      <td>0.010821</td>\n",
       "      <td>0.058818</td>\n",
       "      <td>0.703553</td>\n",
       "      <td>5547</td>\n",
       "      <td>9925</td>\n",
       "      <td>10984</td>\n",
       "      <td>14403</td>\n",
       "      <td>4158</td>\n",
       "      <td>7087</td>\n",
       "      <td>187742</td>\n",
       "      <td>197213</td>\n",
       "      <td>219186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>Calaveras, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.005031</td>\n",
       "      <td>0.017243</td>\n",
       "      <td>0.005713</td>\n",
       "      <td>0.082075</td>\n",
       "      <td>0.619811</td>\n",
       "      <td>905</td>\n",
       "      <td>1858</td>\n",
       "      <td>859</td>\n",
       "      <td>1399</td>\n",
       "      <td>498</td>\n",
       "      <td>836</td>\n",
       "      <td>41752</td>\n",
       "      <td>43344</td>\n",
       "      <td>45905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State     County   Geographic Area Name        Date  Rate_AIndA_V  \\\n",
       "0  California    Alameda    Alameda, California  2021-05-18      0.003037   \n",
       "1  California     Alpine     Alpine, California  2021-05-17      0.017032   \n",
       "2  California     Amador     Amador, California  2021-05-18      0.008574   \n",
       "3  California      Butte      Butte, California  2021-05-18      0.012459   \n",
       "4  California  Calaveras  Calaveras, California  2021-05-18      0.005031   \n",
       "\n",
       "   Rate_Asian_V  Rate_Black_V  Rate_Other_V  Rate_White_V  Total_AIndA  \\\n",
       "0      0.322841      0.067144      0.086290      0.301939        17797   \n",
       "1      0.012165      0.002433      0.010949      0.192214          290   \n",
       "2      0.017790      0.005144      0.091041      0.739792          925   \n",
       "3      0.044084      0.010821      0.058818      0.703553         5547   \n",
       "4      0.017243      0.005713      0.082075      0.619811          905   \n",
       "\n",
       "   Total_AIndAC  Total_Asian  Total_AsianC  Total_Black  Total_BlackC  \\\n",
       "0         37797       540391        594001       184368        214392   \n",
       "1           310           18            27            4            26   \n",
       "2          1644          665          1043         1066          1374   \n",
       "3          9925        10984         14403         4158          7087   \n",
       "4          1858          859          1399          498           836   \n",
       "\n",
       "   Total_White  Total_WhiteC  Total_Whole  \n",
       "0       823593        899377      1671329  \n",
       "1          767           813         1129  \n",
       "2        35636         36844        39752  \n",
       "3       187742        197213       219186  \n",
       "4        41752         43344        45905  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T = standardize_California(CA_County_Vaccine_Race, US_COUNTY_POPULATION)\n",
    "state = 'California'\n",
    "\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "willing-finance",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties [nan, 'Unknown, Not in California', 'Outside California, Not in California']\n"
     ]
    }
   ],
   "source": [
    "not_included = list(set(T['Geographic Area Name'].unique()) - set(CalInfo['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "    \n",
    "not_included = list(set(CalInfo['Geographic Area Name'].unique()) - set(T['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fatty-investing",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(58, 19)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Rate_AIndA_V</th>\n",
       "      <th>Rate_Asian_V</th>\n",
       "      <th>Rate_Black_V</th>\n",
       "      <th>Rate_Other_V</th>\n",
       "      <th>Rate_White_V</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "      <th>cumulative_at_least_one_dose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.003037</td>\n",
       "      <td>0.322841</td>\n",
       "      <td>0.067144</td>\n",
       "      <td>0.086290</td>\n",
       "      <td>0.301939</td>\n",
       "      <td>17797</td>\n",
       "      <td>37797</td>\n",
       "      <td>540391</td>\n",
       "      <td>594001</td>\n",
       "      <td>184368</td>\n",
       "      <td>214392</td>\n",
       "      <td>823593</td>\n",
       "      <td>899377</td>\n",
       "      <td>1671329</td>\n",
       "      <td>1032515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>Alpine, California</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>0.017032</td>\n",
       "      <td>0.012165</td>\n",
       "      <td>0.002433</td>\n",
       "      <td>0.010949</td>\n",
       "      <td>0.192214</td>\n",
       "      <td>290</td>\n",
       "      <td>310</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>767</td>\n",
       "      <td>813</td>\n",
       "      <td>1129</td>\n",
       "      <td>823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>California</td>\n",
       "      <td>Amador</td>\n",
       "      <td>Amador, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.008574</td>\n",
       "      <td>0.017790</td>\n",
       "      <td>0.005144</td>\n",
       "      <td>0.091041</td>\n",
       "      <td>0.739792</td>\n",
       "      <td>925</td>\n",
       "      <td>1644</td>\n",
       "      <td>665</td>\n",
       "      <td>1043</td>\n",
       "      <td>1066</td>\n",
       "      <td>1374</td>\n",
       "      <td>35636</td>\n",
       "      <td>36844</td>\n",
       "      <td>39752</td>\n",
       "      <td>18699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>Butte</td>\n",
       "      <td>Butte, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.012459</td>\n",
       "      <td>0.044084</td>\n",
       "      <td>0.010821</td>\n",
       "      <td>0.058818</td>\n",
       "      <td>0.703553</td>\n",
       "      <td>5547</td>\n",
       "      <td>9925</td>\n",
       "      <td>10984</td>\n",
       "      <td>14403</td>\n",
       "      <td>4158</td>\n",
       "      <td>7087</td>\n",
       "      <td>187742</td>\n",
       "      <td>197213</td>\n",
       "      <td>219186</td>\n",
       "      <td>85244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>Calaveras, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>0.005031</td>\n",
       "      <td>0.017243</td>\n",
       "      <td>0.005713</td>\n",
       "      <td>0.082075</td>\n",
       "      <td>0.619811</td>\n",
       "      <td>905</td>\n",
       "      <td>1858</td>\n",
       "      <td>859</td>\n",
       "      <td>1399</td>\n",
       "      <td>498</td>\n",
       "      <td>836</td>\n",
       "      <td>41752</td>\n",
       "      <td>43344</td>\n",
       "      <td>45905</td>\n",
       "      <td>19161</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State     County   Geographic Area Name        Date  Rate_AIndA_V  \\\n",
       "0  California    Alameda    Alameda, California  2021-05-18      0.003037   \n",
       "1  California     Alpine     Alpine, California  2021-05-17      0.017032   \n",
       "2  California     Amador     Amador, California  2021-05-18      0.008574   \n",
       "3  California      Butte      Butte, California  2021-05-18      0.012459   \n",
       "4  California  Calaveras  Calaveras, California  2021-05-18      0.005031   \n",
       "\n",
       "   Rate_Asian_V  Rate_Black_V  Rate_Other_V  Rate_White_V  Total_AIndA  \\\n",
       "0      0.322841      0.067144      0.086290      0.301939        17797   \n",
       "1      0.012165      0.002433      0.010949      0.192214          290   \n",
       "2      0.017790      0.005144      0.091041      0.739792          925   \n",
       "3      0.044084      0.010821      0.058818      0.703553         5547   \n",
       "4      0.017243      0.005713      0.082075      0.619811          905   \n",
       "\n",
       "   Total_AIndAC  Total_Asian  Total_AsianC  Total_Black  Total_BlackC  \\\n",
       "0         37797       540391        594001       184368        214392   \n",
       "1           310           18            27            4            26   \n",
       "2          1644          665          1043         1066          1374   \n",
       "3          9925        10984         14403         4158          7087   \n",
       "4          1858          859          1399          498           836   \n",
       "\n",
       "   Total_White  Total_WhiteC  Total_Whole  cumulative_at_least_one_dose  \n",
       "0       823593        899377      1671329                       1032515  \n",
       "1          767           813         1129                           823  \n",
       "2        35636         36844        39752                         18699  \n",
       "3       187742        197213       219186                         85244  \n",
       "4        41752         43344        45905                         19161  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T2 = pd.merge(T, CalInfo[['Geographic Area Name', 'cumulative_at_least_one_dose']] , on = 'Geographic Area Name')\n",
    "print(T2.shape)\n",
    "T2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "quick-circle",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['State', 'County', 'Geographic Area Name', 'Date', 'Rate_AIndA_V',\n",
       "       'Rate_Asian_V', 'Rate_Black_V', 'Rate_Other_V', 'Rate_White_V',\n",
       "       'Total_AIndA', 'Total_AIndAC', 'Total_Asian', 'Total_AsianC',\n",
       "       'Total_Black', 'Total_BlackC', 'Total_White', 'Total_WhiteC',\n",
       "       'Total_Whole', 'cumulative_at_least_one_dose'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T2.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "respiratory-banana",
   "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>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Numb_White</th>\n",
       "      <th>Rate_White</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Rate_Black</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "      <th>cumulative_at_least_one_dose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>California</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>Alameda, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>311756.546585</td>\n",
       "      <td>0.378532</td>\n",
       "      <td>69327.187160</td>\n",
       "      <td>0.376026</td>\n",
       "      <td>17797</td>\n",
       "      <td>37797</td>\n",
       "      <td>540391</td>\n",
       "      <td>594001</td>\n",
       "      <td>184368</td>\n",
       "      <td>214392</td>\n",
       "      <td>823593</td>\n",
       "      <td>899377</td>\n",
       "      <td>1671329</td>\n",
       "      <td>1032515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>Alpine</td>\n",
       "      <td>Alpine, California</td>\n",
       "      <td>2021-05-17</td>\n",
       "      <td>158.192122</td>\n",
       "      <td>0.206248</td>\n",
       "      <td>2.002359</td>\n",
       "      <td>0.500590</td>\n",
       "      <td>290</td>\n",
       "      <td>310</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>26</td>\n",
       "      <td>767</td>\n",
       "      <td>813</td>\n",
       "      <td>1129</td>\n",
       "      <td>823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>California</td>\n",
       "      <td>Amador</td>\n",
       "      <td>Amador, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>13833.370608</td>\n",
       "      <td>0.388185</td>\n",
       "      <td>96.187656</td>\n",
       "      <td>0.090232</td>\n",
       "      <td>925</td>\n",
       "      <td>1644</td>\n",
       "      <td>665</td>\n",
       "      <td>1043</td>\n",
       "      <td>1066</td>\n",
       "      <td>1374</td>\n",
       "      <td>35636</td>\n",
       "      <td>36844</td>\n",
       "      <td>39752</td>\n",
       "      <td>18699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>Butte</td>\n",
       "      <td>Butte, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>59973.671932</td>\n",
       "      <td>0.319447</td>\n",
       "      <td>922.425324</td>\n",
       "      <td>0.221844</td>\n",
       "      <td>5547</td>\n",
       "      <td>9925</td>\n",
       "      <td>10984</td>\n",
       "      <td>14403</td>\n",
       "      <td>4158</td>\n",
       "      <td>7087</td>\n",
       "      <td>187742</td>\n",
       "      <td>197213</td>\n",
       "      <td>219186</td>\n",
       "      <td>85244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>Calaveras</td>\n",
       "      <td>Calaveras, California</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>11876.198571</td>\n",
       "      <td>0.284446</td>\n",
       "      <td>109.466793</td>\n",
       "      <td>0.219813</td>\n",
       "      <td>905</td>\n",
       "      <td>1858</td>\n",
       "      <td>859</td>\n",
       "      <td>1399</td>\n",
       "      <td>498</td>\n",
       "      <td>836</td>\n",
       "      <td>41752</td>\n",
       "      <td>43344</td>\n",
       "      <td>45905</td>\n",
       "      <td>19161</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State     County   Geographic Area Name        Date     Numb_White  \\\n",
       "0  California    Alameda    Alameda, California  2021-05-18  311756.546585   \n",
       "1  California     Alpine     Alpine, California  2021-05-17     158.192122   \n",
       "2  California     Amador     Amador, California  2021-05-18   13833.370608   \n",
       "3  California      Butte      Butte, California  2021-05-18   59973.671932   \n",
       "4  California  Calaveras  Calaveras, California  2021-05-18   11876.198571   \n",
       "\n",
       "   Rate_White    Numb_Black  Rate_Black  Total_AIndA  Total_AIndAC  \\\n",
       "0    0.378532  69327.187160    0.376026        17797         37797   \n",
       "1    0.206248      2.002359    0.500590          290           310   \n",
       "2    0.388185     96.187656    0.090232          925          1644   \n",
       "3    0.319447    922.425324    0.221844         5547          9925   \n",
       "4    0.284446    109.466793    0.219813          905          1858   \n",
       "\n",
       "   Total_Asian  Total_AsianC  Total_Black  Total_BlackC  Total_White  \\\n",
       "0       540391        594001       184368        214392       823593   \n",
       "1           18            27            4            26          767   \n",
       "2          665          1043         1066          1374        35636   \n",
       "3        10984         14403         4158          7087       187742   \n",
       "4          859          1399          498           836        41752   \n",
       "\n",
       "   Total_WhiteC  Total_Whole  cumulative_at_least_one_dose  \n",
       "0        899377      1671329                       1032515  \n",
       "1           813         1129                           823  \n",
       "2         36844        39752                         18699  \n",
       "3        197213       219186                         85244  \n",
       "4         43344        45905                         19161  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for Race in ['White', 'Black']:\n",
    "    T2['Numb_' + Race]        = T2['cumulative_at_least_one_dose'] * T2['Rate_' + Race + '_V']\n",
    "    T2['Rate_' + Race]       = T2['Numb_' + Race] / T2['Total_' + Race]\n",
    "    # T2['Rate_' + Race + 'C'] = T2['Num_' + Race] / T2['Total_' + Race + 'C']\n",
    "\n",
    "    \n",
    "cols = ['State', 'County', 'Geographic Area Name', 'Date', 'Numb_White',\n",
    "       'Rate_White', 'Numb_Black', 'Rate_Black',\n",
    "       'Total_AIndA', 'Total_AIndAC', 'Total_Asian', 'Total_AsianC',\n",
    "       'Total_Black', 'Total_BlackC', 'Total_White', 'Total_WhiteC',\n",
    "       'Total_Whole', 'cumulative_at_least_one_dose', ]\n",
    "T = T2[cols]\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "described-trial",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California']\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-13-266ad6d67fc6>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0.288675</td>\n",
       "      <td>0.110941</td>\n",
       "      <td>0.050642</td>\n",
       "      <td>0.189658</td>\n",
       "      <td>0.300151</td>\n",
       "      <td>0.378843</td>\n",
       "      <td>0.570614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0.231785</td>\n",
       "      <td>0.104841</td>\n",
       "      <td>0.025853</td>\n",
       "      <td>0.161450</td>\n",
       "      <td>0.222143</td>\n",
       "      <td>0.310835</td>\n",
       "      <td>0.500590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0.056890</td>\n",
       "      <td>0.113748</td>\n",
       "      <td>-0.294342</td>\n",
       "      <td>-0.012783</td>\n",
       "      <td>0.033863</td>\n",
       "      <td>0.127926</td>\n",
       "      <td>0.338948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name  count      mean       std       min       25%       50%  \\\n",
       "0   Rate_White   58.0  0.288675  0.110941  0.050642  0.189658  0.300151   \n",
       "1   Rate_Black   58.0  0.231785  0.104841  0.025853  0.161450  0.222143   \n",
       "2  Rate_Parity   58.0  0.056890  0.113748 -0.294342 -0.012783  0.033863   \n",
       "\n",
       "        75%       max  \n",
       "0  0.378843  0.570614  \n",
       "1  0.310835  0.500590  \n",
       "2  0.127926  0.338948  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "# T['Rate_ParityC'] = T['Rate_WhiteC'] - T['Rate_BlackC']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "laden-taxation",
   "metadata": {},
   "source": [
    "# III. Table States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "patent-bikini",
   "metadata": {},
   "source": [
    "## [!] B. Tennessee* (Done)\n",
    "\n",
    "\n",
    "https://www.tn.gov/health/cedep/ncov/data/downloadable-datasets.html\n",
    "\n",
    "Covid Vaccine County Demographics\n",
    "\n",
    "\n",
    "95 counties\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "partial-projector",
   "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>COUNTY</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>CAT_DETAIL</th>\n",
       "      <th>RECIPIENT_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>127449</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>ETHN</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>6545.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127450</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>SEX</td>\n",
       "      <td>F</td>\n",
       "      <td>28994.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127451</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>SEX</td>\n",
       "      <td>M</td>\n",
       "      <td>23029.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127452</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>SEX</td>\n",
       "      <td>O</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127453</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>WILSON</td>\n",
       "      <td>SEX</td>\n",
       "      <td>U</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATE  COUNTY CATEGORY CAT_DETAIL  RECIPIENT_COUNT\n",
       "127449 2021-05-20  WILSON     ETHN    UNKNOWN           6545.0\n",
       "127450 2021-05-20  WILSON      SEX          F          28994.0\n",
       "127451 2021-05-20  WILSON      SEX          M          23029.0\n",
       "127452 2021-05-20  WILSON      SEX          O             46.0\n",
       "127453 2021-05-20  WILSON      SEX          U             80.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "url = 'https://www.tn.gov/content/dam/tn/health/documents/cedep/novel-coronavirus/datasets/COVID_VACCINE_COUNTY_DEMOGRAPHICS.XLSX'\n",
    "RawData = pd.read_excel(url)\n",
    "RawData.tail()  # RawData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "opened-azerbaijan",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We get Tennessee Information for Date: 2021-05-20\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>CAT_DETAIL</th>\n",
       "      <th>RECIPIENT_COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>126317</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>RACE</td>\n",
       "      <td>ASIAN</td>\n",
       "      <td>364.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126318</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>RACE</td>\n",
       "      <td>BLACK OR AFRICAN AMERICAN</td>\n",
       "      <td>697.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126319</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>RACE</td>\n",
       "      <td>OTHER/MULTIRACIAL</td>\n",
       "      <td>5373.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126320</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>RACE</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>2705.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126321</th>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>RACE</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>23039.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             DATE    COUNTY CATEGORY                 CAT_DETAIL  \\\n",
       "126317 2021-05-20  ANDERSON     RACE                      ASIAN   \n",
       "126318 2021-05-20  ANDERSON     RACE  BLACK OR AFRICAN AMERICAN   \n",
       "126319 2021-05-20  ANDERSON     RACE          OTHER/MULTIRACIAL   \n",
       "126320 2021-05-20  ANDERSON     RACE                    UNKNOWN   \n",
       "126321 2021-05-20  ANDERSON     RACE                      WHITE   \n",
       "\n",
       "        RECIPIENT_COUNT  \n",
       "126317            364.0  \n",
       "126318            697.0  \n",
       "126319           5373.0  \n",
       "126320           2705.0  \n",
       "126321          23039.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "######################################################\n",
    "Date = Today_Date \n",
    "######################################################  \n",
    "\n",
    "\n",
    "# RawData2 = RawData\n",
    "\n",
    "while (RawData['DATE'] == Date).sum() == 0:\n",
    "    print('There is no information for current Date:', Date)\n",
    "    Date = input('Please Enter a new One:')\n",
    "\n",
    "    \n",
    "    \n",
    "\n",
    "print('We get Tennessee Information for Date:', Date)\n",
    "RawData = RawData[RawData['DATE'] == Date]\n",
    "\n",
    "RawData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "received-public",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "RawData = RawData[RawData['CATEGORY'].isin(['RACE', 'ETHN'])]\n",
    "\n",
    "\n",
    "L= []\n",
    "\n",
    "for county, record in RawData.groupby('COUNTY'):\n",
    "    # print(county)\n",
    "    D = {}\n",
    "    D['County'] = county\n",
    "\n",
    "    for idx, row in record.iterrows():\n",
    "        d = row.to_dict()\n",
    "        # print(d)\n",
    "        \n",
    "        item = d['CAT_DETAIL']\n",
    "        if item == 'UNKNOWN': \n",
    "            item = d['CATEGORY'] + '-' + item\n",
    "        \n",
    "        D[item] = d['RECIPIENT_COUNT']\n",
    "    # print(D)\n",
    "    L.append(D)\n",
    "\n",
    "Tennessee_County_Vaccine_Race = pd.DataFrame(L)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "taken-north",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(96, 9)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>ASIAN</th>\n",
       "      <th>BLACK OR AFRICAN AMERICAN</th>\n",
       "      <th>OTHER/MULTIRACIAL</th>\n",
       "      <th>RACE-UNKNOWN</th>\n",
       "      <th>WHITE</th>\n",
       "      <th>HISPANIC OR LATINO</th>\n",
       "      <th>NOT HISPANIC OR LATINO</th>\n",
       "      <th>ETHN-UNKNOWN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>364.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>5373.0</td>\n",
       "      <td>2705.0</td>\n",
       "      <td>23039.0</td>\n",
       "      <td>960.0</td>\n",
       "      <td>27150.0</td>\n",
       "      <td>4068.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BEDFORD</td>\n",
       "      <td>139.0</td>\n",
       "      <td>785.0</td>\n",
       "      <td>1897.0</td>\n",
       "      <td>1025.0</td>\n",
       "      <td>8982.0</td>\n",
       "      <td>968.0</td>\n",
       "      <td>10376.0</td>\n",
       "      <td>1484.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BENTON</td>\n",
       "      <td>40.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>611.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>4619.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>5274.0</td>\n",
       "      <td>261.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BLEDSOE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>436.0</td>\n",
       "      <td>321.0</td>\n",
       "      <td>3002.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>3241.0</td>\n",
       "      <td>511.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BLOUNT</td>\n",
       "      <td>387.0</td>\n",
       "      <td>824.0</td>\n",
       "      <td>6265.0</td>\n",
       "      <td>3370.0</td>\n",
       "      <td>40292.0</td>\n",
       "      <td>967.0</td>\n",
       "      <td>44699.0</td>\n",
       "      <td>5472.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     County  ASIAN  BLACK OR AFRICAN AMERICAN  OTHER/MULTIRACIAL  \\\n",
       "0  ANDERSON  364.0                      697.0             5373.0   \n",
       "1   BEDFORD  139.0                      785.0             1897.0   \n",
       "2    BENTON   40.0                      229.0              611.0   \n",
       "3   BLEDSOE    NaN                       47.0              436.0   \n",
       "4    BLOUNT  387.0                      824.0             6265.0   \n",
       "\n",
       "   RACE-UNKNOWN    WHITE  HISPANIC OR LATINO  NOT HISPANIC OR LATINO  \\\n",
       "0        2705.0  23039.0               960.0                 27150.0   \n",
       "1        1025.0   8982.0               968.0                 10376.0   \n",
       "2         129.0   4619.0                93.0                  5274.0   \n",
       "3         321.0   3002.0                56.0                  3241.0   \n",
       "4        3370.0  40292.0               967.0                 44699.0   \n",
       "\n",
       "   ETHN-UNKNOWN  \n",
       "0        4068.0  \n",
       "1        1484.0  \n",
       "2         261.0  \n",
       "3         511.0  \n",
       "4        5472.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Tennessee_County_Vaccine_Race.to_csv('FinalResult/Tennessee.csv')\n",
    "print(Tennessee_County_Vaccine_Race.shape)\n",
    "Tennessee_County_Vaccine_Race.head() # 95 counties in Tennessee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "declared-brown",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32178.0"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "697.0 + 5373.0 + 2705.0 + 23039.0 + 364.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "cubic-water",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "32178.0"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "960.0 + 27150.0 + 4068.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "speaking-uncertainty",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Out Of State, Tennessee']\n",
      "(95, 19)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>Numb_Asian</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Numb_White</th>\n",
       "      <th>Rate_Asian</th>\n",
       "      <th>Rate_Black</th>\n",
       "      <th>Rate_White</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>Anderson, Tennessee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>364.0</td>\n",
       "      <td>697.0</td>\n",
       "      <td>23039.0</td>\n",
       "      <td>0.310580</td>\n",
       "      <td>0.220989</td>\n",
       "      <td>0.326906</td>\n",
       "      <td>371</td>\n",
       "      <td>911</td>\n",
       "      <td>1172</td>\n",
       "      <td>1480</td>\n",
       "      <td>3154</td>\n",
       "      <td>4161</td>\n",
       "      <td>70476</td>\n",
       "      <td>72149</td>\n",
       "      <td>76978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>Bedford</td>\n",
       "      <td>Bedford, Tennessee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>139.0</td>\n",
       "      <td>785.0</td>\n",
       "      <td>8982.0</td>\n",
       "      <td>0.267308</td>\n",
       "      <td>0.186949</td>\n",
       "      <td>0.207662</td>\n",
       "      <td>580</td>\n",
       "      <td>843</td>\n",
       "      <td>520</td>\n",
       "      <td>669</td>\n",
       "      <td>4199</td>\n",
       "      <td>4922</td>\n",
       "      <td>43253</td>\n",
       "      <td>44280</td>\n",
       "      <td>49713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>Benton</td>\n",
       "      <td>Benton, Tennessee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>4619.0</td>\n",
       "      <td>0.408163</td>\n",
       "      <td>0.546539</td>\n",
       "      <td>0.302152</td>\n",
       "      <td>91</td>\n",
       "      <td>220</td>\n",
       "      <td>98</td>\n",
       "      <td>136</td>\n",
       "      <td>419</td>\n",
       "      <td>536</td>\n",
       "      <td>15287</td>\n",
       "      <td>15532</td>\n",
       "      <td>16160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>Bledsoe</td>\n",
       "      <td>Bledsoe, Tennessee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47.0</td>\n",
       "      <td>3002.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.040517</td>\n",
       "      <td>0.221599</td>\n",
       "      <td>96</td>\n",
       "      <td>188</td>\n",
       "      <td>45</td>\n",
       "      <td>71</td>\n",
       "      <td>1160</td>\n",
       "      <td>1261</td>\n",
       "      <td>13547</td>\n",
       "      <td>13750</td>\n",
       "      <td>15064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>Blount</td>\n",
       "      <td>Blount, Tennessee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>387.0</td>\n",
       "      <td>824.0</td>\n",
       "      <td>40292.0</td>\n",
       "      <td>0.297692</td>\n",
       "      <td>0.204975</td>\n",
       "      <td>0.322951</td>\n",
       "      <td>564</td>\n",
       "      <td>1487</td>\n",
       "      <td>1300</td>\n",
       "      <td>1754</td>\n",
       "      <td>4020</td>\n",
       "      <td>5075</td>\n",
       "      <td>124762</td>\n",
       "      <td>126972</td>\n",
       "      <td>133088</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       State    County Geographic Area Name  Date  Numb_Asian  Numb_Black  \\\n",
       "0  Tennessee  Anderson  Anderson, Tennessee   NaN       364.0       697.0   \n",
       "1  Tennessee   Bedford   Bedford, Tennessee   NaN       139.0       785.0   \n",
       "2  Tennessee    Benton    Benton, Tennessee   NaN        40.0       229.0   \n",
       "3  Tennessee   Bledsoe   Bledsoe, Tennessee   NaN         NaN        47.0   \n",
       "4  Tennessee    Blount    Blount, Tennessee   NaN       387.0       824.0   \n",
       "\n",
       "   Numb_White  Rate_Asian  Rate_Black  Rate_White  Total_AIndA  Total_AIndAC  \\\n",
       "0     23039.0    0.310580    0.220989    0.326906          371           911   \n",
       "1      8982.0    0.267308    0.186949    0.207662          580           843   \n",
       "2      4619.0    0.408163    0.546539    0.302152           91           220   \n",
       "3      3002.0         NaN    0.040517    0.221599           96           188   \n",
       "4     40292.0    0.297692    0.204975    0.322951          564          1487   \n",
       "\n",
       "   Total_Asian  Total_AsianC  Total_Black  Total_BlackC  Total_White  \\\n",
       "0         1172          1480         3154          4161        70476   \n",
       "1          520           669         4199          4922        43253   \n",
       "2           98           136          419           536        15287   \n",
       "3           45            71         1160          1261        13547   \n",
       "4         1300          1754         4020          5075       124762   \n",
       "\n",
       "   Total_WhiteC  Total_Whole  \n",
       "0         72149        76978  \n",
       "1         44280        49713  \n",
       "2         15532        16160  \n",
       "3         13750        15064  \n",
       "4        126972       133088  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "\n",
    "state = 'Tennessee' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "\n",
    "for idx, row in Tennessee_County_Vaccine_Race.iterrows(): ## \n",
    "    d = row.to_dict()\n",
    "    \n",
    "    new_d = {}\n",
    "    \n",
    "    \n",
    "    new_d['State'] = state\n",
    "    new_d['County'] = ' '.join([i.lower().capitalize() for i in d['County'].split(' ')])\n",
    "    RenameCounties = {\n",
    "        'Dekalb': 'DeKalb',\n",
    "        'Mcminn': 'McMinn', \n",
    "        'Mcnairy': 'McNairy',\n",
    "        # 'Mccormick': 'McCormick'\n",
    "    }\n",
    "    if new_d['County'] in RenameCounties:\n",
    "        new_d['County'] = RenameCounties[new_d['County']]\n",
    "\n",
    "    new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "        \n",
    "        \n",
    "    new_d['Numb_White'] = d['WHITE']\n",
    "    \n",
    "    new_d['Numb_Black'] = d['BLACK OR AFRICAN AMERICAN']\n",
    "    \n",
    "    \n",
    "    new_d['Numb_Asian'] = d['ASIAN']\n",
    "    new_d['Rate_Asian'] = np.nan\n",
    "    \n",
    "    \n",
    "    new_d['Date'] = np.nan\n",
    "    \n",
    "    new_L.append(new_d)\n",
    "    \n",
    "    \n",
    "Data = pd.DataFrame(new_L)\n",
    "not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "\n",
    "T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "\n",
    "for Race in ['White', 'Black', 'Asian']:\n",
    "    Rate = T['Numb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['Rate_' + Race] = Rate\n",
    "\n",
    "T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "\n",
    "\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "technical-genealogy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California', 'Tennessee']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.232856</td>\n",
       "      <td>0.119754</td>\n",
       "      <td>0.040517</td>\n",
       "      <td>0.171909</td>\n",
       "      <td>0.209536</td>\n",
       "      <td>0.257416</td>\n",
       "      <td>0.863014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.276508</td>\n",
       "      <td>0.058253</td>\n",
       "      <td>0.146253</td>\n",
       "      <td>0.238588</td>\n",
       "      <td>0.264556</td>\n",
       "      <td>0.303104</td>\n",
       "      <td>0.457310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.046551</td>\n",
       "      <td>0.115989</td>\n",
       "      <td>-0.546870</td>\n",
       "      <td>0.015471</td>\n",
       "      <td>0.063743</td>\n",
       "      <td>0.108698</td>\n",
       "      <td>0.196795</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name  count      mean       std       min       25%       50%  \\\n",
       "0   Rate_Black   90.0  0.232856  0.119754  0.040517  0.171909  0.209536   \n",
       "1   Rate_White   95.0  0.276508  0.058253  0.146253  0.238588  0.264556   \n",
       "2  Rate_Parity   90.0  0.046551  0.115989 -0.546870  0.015471  0.063743   \n",
       "\n",
       "        75%       max  \n",
       "0  0.257416  0.863014  \n",
       "1  0.303104  0.457310  \n",
       "2  0.108698  0.196795  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "# T['Rate_ParityC'] = T['Rate_WhiteC'] - T['Rate_BlackC']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dried-praise",
   "metadata": {},
   "source": [
    "## [!] B. North Carolina* (Done)\n",
    "\n",
    "Download the Data First\n",
    "\n",
    "https://covid19.ncdhhs.gov/dashboard/data-behind-dashboards"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "funky-class",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty.xlsx'"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty.xlsx'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "understanding-anderson",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter North Carolina Manually collected Data:  CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty.xlsx\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty.xlsx\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['Index', 'County ', 'Week of', 'American Indian or Alaskan Native',\n",
       "       'Asian or Pacific Islander', 'Black or African American', 'White',\n",
       "       'Other', 'Suppressed', 'Missing or Undisclosed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "Date = Today_Date\n",
    "import os\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter North Carolina Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n",
    "\n",
    "\n",
    "FirstData = pd.read_excel(Path)\n",
    "FirstData.columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "sudden-charger",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter North Carolina Manually collected Data:  CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty-full.xlsx\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/NorthCarolina/Vaccination_Race-Fed-Cnty-full.xlsx\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['Index', 'County ', 'Week of', 'American Indian or Alaskan Native',\n",
       "       'Asian or Pacific Islander', 'Black or African American', 'White',\n",
       "       'Other', 'Suppressed', 'Missing or Undisclosed'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "Date = Today_Date\n",
    "import os\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter North Carolina Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n",
    "\n",
    "\n",
    "FullData = pd.read_excel(Path)\n",
    "FullData.columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "alternative-contamination",
   "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>County</th>\n",
       "      <th>Black or African American</th>\n",
       "      <th>American Indian or Alaskan Native</th>\n",
       "      <th>White</th>\n",
       "      <th>Asian or Pacific Islander</th>\n",
       "      <th>Black or African American-Full</th>\n",
       "      <th>American Indian or Alaskan Native-Full</th>\n",
       "      <th>White-Full</th>\n",
       "      <th>Asian or Pacific Islander-Full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alamance</td>\n",
       "      <td>1590.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>6760.0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>5980.0</td>\n",
       "      <td>244.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexander</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>756.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>665.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alleghany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>659.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>588.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anson</td>\n",
       "      <td>211.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>292.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>161.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>237.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ashe</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1426.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>Wayne</td>\n",
       "      <td>1732.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2981.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>1453.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>2555.0</td>\n",
       "      <td>128.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Wilkes</td>\n",
       "      <td>154.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2969.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>111.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2362.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Wilson</td>\n",
       "      <td>1644.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>2249.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>1426.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1964.0</td>\n",
       "      <td>138.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>Yadkin</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1310.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1201.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>Yancey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>303.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>252.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        County  Black or African American  American Indian or Alaskan Native  \\\n",
       "0     Alamance                     1590.0                               61.0   \n",
       "1    Alexander                       38.0                                NaN   \n",
       "2    Alleghany                        NaN                                NaN   \n",
       "3        Anson                      211.0                                NaN   \n",
       "4         Ashe                        NaN                                NaN   \n",
       "..         ...                        ...                                ...   \n",
       "97       Wayne                     1732.0                               37.0   \n",
       "98      Wilkes                      154.0                                NaN   \n",
       "99      Wilson                     1644.0                               32.0   \n",
       "100     Yadkin                       58.0                                NaN   \n",
       "101     Yancey                        NaN                                NaN   \n",
       "\n",
       "      White  Asian or Pacific Islander  Black or African American-Full  \\\n",
       "0    6760.0                      300.0                          1218.0   \n",
       "1     756.0                        NaN                            38.0   \n",
       "2     659.0                        NaN                             NaN   \n",
       "3     292.0                        NaN                           161.0   \n",
       "4    1426.0                        NaN                             NaN   \n",
       "..      ...                        ...                             ...   \n",
       "97   2981.0                      174.0                          1453.0   \n",
       "98   2969.0                        NaN                           111.0   \n",
       "99   2249.0                      170.0                          1426.0   \n",
       "100  1310.0                        NaN                            58.0   \n",
       "101   303.0                        NaN                             NaN   \n",
       "\n",
       "     American Indian or Alaskan Native-Full  White-Full  \\\n",
       "0                                      73.0      5980.0   \n",
       "1                                       NaN       665.0   \n",
       "2                                       NaN       588.0   \n",
       "3                                       NaN       237.0   \n",
       "4                                       NaN      1320.0   \n",
       "..                                      ...         ...   \n",
       "97                                     35.0      2555.0   \n",
       "98                                      NaN      2362.0   \n",
       "99                                     31.0      1964.0   \n",
       "100                                     NaN      1201.0   \n",
       "101                                     NaN       252.0   \n",
       "\n",
       "     Asian or Pacific Islander-Full  \n",
       "0                             244.0  \n",
       "1                               NaN  \n",
       "2                               NaN  \n",
       "3                               NaN  \n",
       "4                               NaN  \n",
       "..                              ...  \n",
       "97                            128.0  \n",
       "98                              NaN  \n",
       "99                            138.0  \n",
       "100                             NaN  \n",
       "101                             NaN  \n",
       "\n",
       "[102 rows x 9 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "columns = ['County ', 'Week of', 'American Indian or Alaskan Native',\n",
    "       'Asian or Pacific Islander', 'Black or African American',\n",
    "       'Missing or Undisclosed', 'Other', 'Suppressed', 'White']\n",
    "\n",
    "\n",
    "NorthCarolina = DataFirst[columns]\n",
    "Groups = NorthCarolina.groupby('County ')\n",
    "\n",
    "CL = []\n",
    "\n",
    "\n",
    "for County, df in Groups:\n",
    "    D = {}\n",
    "    D['County'] = County\n",
    "    d = df.sum().to_dict() # Here is a sum\n",
    "    D['Black or African American'] = d['Black or African American']\n",
    "    D['American Indian or Alaskan Native'] = d['American Indian or Alaskan Native']\n",
    "    D['White'] = d['White']\n",
    "    D['Asian or Pacific Islander'] = d['Asian or Pacific Islander']\n",
    "    \n",
    "    CL.append(D)\n",
    "    \n",
    "NCFirst = pd.DataFrame(CL)\n",
    "NCFirst = NCFirst.replace(0.0, np.nan) ### Changes here\n",
    "\n",
    "\n",
    "\n",
    "NorthCarolina = FullData[columns]\n",
    "Groups = NorthCarolina.groupby('County ')\n",
    "\n",
    "CL = []\n",
    "for County, df in Groups:\n",
    "    D = {}\n",
    "    D['County'] = County\n",
    "    d = df.sum().to_dict() # Here is a sum\n",
    "    D['Black or African American-Full'] = d['Black or African American']\n",
    "    D['American Indian or Alaskan Native-Full'] = d['American Indian or Alaskan Native']\n",
    "    D['White-Full'] = d['White']\n",
    "    D['Asian or Pacific Islander-Full'] = d['Asian or Pacific Islander']\n",
    "    CL.append(D)\n",
    "    \n",
    "NCFull = pd.DataFrame(CL)\n",
    "NCFull = NCFull.replace(0.0, np.nan) ### Changes here\n",
    "\n",
    "\n",
    "NC = NCFirst.merge(NCFull, on = 'County')\n",
    "NC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "unlimited-express",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Out Of State, North Carolina', 'Missing, North Carolina']\n",
      "(100, 27)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>FullNumb_AIndA</th>\n",
       "      <th>FullNumb_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>...</th>\n",
       "      <th>Rate_White</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alamance</td>\n",
       "      <td>Alamance, North Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.0</td>\n",
       "      <td>244.0</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>5980.0</td>\n",
       "      <td>0.082488</td>\n",
       "      <td>0.034437</td>\n",
       "      <td>...</td>\n",
       "      <td>0.054212</td>\n",
       "      <td>2466</td>\n",
       "      <td>3610</td>\n",
       "      <td>2958</td>\n",
       "      <td>3794</td>\n",
       "      <td>35369</td>\n",
       "      <td>37943</td>\n",
       "      <td>124696</td>\n",
       "      <td>128147</td>\n",
       "      <td>169509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Alexander, North Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>38.0</td>\n",
       "      <td>665.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.017164</td>\n",
       "      <td>...</td>\n",
       "      <td>0.022205</td>\n",
       "      <td>199</td>\n",
       "      <td>376</td>\n",
       "      <td>385</td>\n",
       "      <td>470</td>\n",
       "      <td>2214</td>\n",
       "      <td>2630</td>\n",
       "      <td>34047</td>\n",
       "      <td>34625</td>\n",
       "      <td>37497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Alleghany</td>\n",
       "      <td>Alleghany, North Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>588.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.061994</td>\n",
       "      <td>59</td>\n",
       "      <td>144</td>\n",
       "      <td>89</td>\n",
       "      <td>107</td>\n",
       "      <td>197</td>\n",
       "      <td>269</td>\n",
       "      <td>10630</td>\n",
       "      <td>10766</td>\n",
       "      <td>11137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Anson</td>\n",
       "      <td>Anson, North Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>161.0</td>\n",
       "      <td>237.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.013591</td>\n",
       "      <td>...</td>\n",
       "      <td>0.025198</td>\n",
       "      <td>227</td>\n",
       "      <td>368</td>\n",
       "      <td>360</td>\n",
       "      <td>419</td>\n",
       "      <td>11846</td>\n",
       "      <td>12094</td>\n",
       "      <td>11588</td>\n",
       "      <td>11960</td>\n",
       "      <td>24446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Ashe</td>\n",
       "      <td>Ashe, North Carolina</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1320.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.054114</td>\n",
       "      <td>114</td>\n",
       "      <td>269</td>\n",
       "      <td>138</td>\n",
       "      <td>176</td>\n",
       "      <td>262</td>\n",
       "      <td>416</td>\n",
       "      <td>26352</td>\n",
       "      <td>26643</td>\n",
       "      <td>27203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            State     County       Geographic Area Name  Date  FullNumb_AIndA  \\\n",
       "0  North Carolina   Alamance   Alamance, North Carolina   NaN            73.0   \n",
       "1  North Carolina  Alexander  Alexander, North Carolina   NaN             NaN   \n",
       "2  North Carolina  Alleghany  Alleghany, North Carolina   NaN             NaN   \n",
       "3  North Carolina      Anson      Anson, North Carolina   NaN             NaN   \n",
       "4  North Carolina       Ashe       Ashe, North Carolina   NaN             NaN   \n",
       "\n",
       "   FullNumb_Asian  FullNumb_Black  FullNumb_White  FullRate_Asian  \\\n",
       "0           244.0          1218.0          5980.0        0.082488   \n",
       "1             NaN            38.0           665.0             NaN   \n",
       "2             NaN             NaN           588.0             NaN   \n",
       "3             NaN           161.0           237.0             NaN   \n",
       "4             NaN             NaN          1320.0             NaN   \n",
       "\n",
       "   FullRate_Black  ...  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0        0.034437  ...    0.054212         2466          3610         2958   \n",
       "1        0.017164  ...    0.022205          199           376          385   \n",
       "2             NaN  ...    0.061994           59           144           89   \n",
       "3        0.013591  ...    0.025198          227           368          360   \n",
       "4             NaN  ...    0.054114          114           269          138   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0          3794        35369         37943       124696        128147   \n",
       "1           470         2214          2630        34047         34625   \n",
       "2           107          197           269        10630         10766   \n",
       "3           419        11846         12094        11588         11960   \n",
       "4           176          262           416        26352         26643   \n",
       "\n",
       "   Total_Whole  \n",
       "0       169509  \n",
       "1        37497  \n",
       "2        11137  \n",
       "3        24446  \n",
       "4        27203  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "state = 'North Carolina' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "\n",
    "for idx, row in NC.iterrows(): ## \n",
    "    d = row.to_dict()\n",
    "    # print(d)\n",
    "    new_d = {}\n",
    "    \n",
    "    if type(d['County']) != str:\n",
    "        print(d)\n",
    "        continue \n",
    "        \n",
    "    new_d['State'] = state\n",
    "    new_d['County'] = ' '.join([i.lower().capitalize() for i in d['County'].split(' ')])\n",
    "    RenameCounties = {\n",
    "        # 'Mckean': 'McKean'\n",
    "        'Mcdowell': 'McDowell'\n",
    "    }\n",
    "    if new_d['County'] in RenameCounties:\n",
    "        new_d['County'] = RenameCounties[new_d['County']]\n",
    "\n",
    "    new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "    \n",
    "    \n",
    "    new_d['Numb_White'] = d['White']\n",
    "    \n",
    "    new_d['Numb_Black'] = d['Black or African American']\n",
    "    \n",
    "    new_d['Numb_Asian'] = d['Asian or Pacific Islander']\n",
    "    \n",
    "    new_d['Numb_AIndA'] = d['American Indian or Alaskan Native']\n",
    "    \n",
    "    \n",
    "    new_d['FullNumb_White'] = d['White-Full']\n",
    "    \n",
    "    new_d['FullNumb_Black'] = d['Black or African American-Full']\n",
    "    \n",
    "    new_d['FullNumb_Asian'] = d['Asian or Pacific Islander-Full']\n",
    "    \n",
    "    new_d['FullNumb_AIndA'] = d['American Indian or Alaskan Native-Full']\n",
    "    \n",
    "    \n",
    "    new_d['Date'] = np.nan\n",
    "    \n",
    "    new_L.append(new_d)\n",
    "    \n",
    "    \n",
    "Data = pd.DataFrame(new_L)\n",
    "not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "\n",
    "T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "\n",
    "for Race in ['White', 'Black', 'Asian']:\n",
    "    Rate = T['Numb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['Rate_' + Race] = Rate\n",
    "    Rate = T['FullNumb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['FullRate_' + Race] = Rate\n",
    "\n",
    "#     Rate = T['Numb_' + Race] / T['Total_' + Race + 'C']\n",
    "#     Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "#     T['Rate_' + Race + 'C'] = Rate\n",
    "\n",
    "\n",
    "T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "elder-salad",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California', 'Tennessee', 'North Carolina']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FullRate_Black</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0.035986</td>\n",
       "      <td>0.012234</td>\n",
       "      <td>0.013591</td>\n",
       "      <td>0.028193</td>\n",
       "      <td>0.036004</td>\n",
       "      <td>0.041853</td>\n",
       "      <td>0.076923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0.043566</td>\n",
       "      <td>0.021088</td>\n",
       "      <td>0.009223</td>\n",
       "      <td>0.028549</td>\n",
       "      <td>0.039678</td>\n",
       "      <td>0.054595</td>\n",
       "      <td>0.109376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0.043876</td>\n",
       "      <td>0.015192</td>\n",
       "      <td>0.013072</td>\n",
       "      <td>0.033490</td>\n",
       "      <td>0.044344</td>\n",
       "      <td>0.050431</td>\n",
       "      <td>0.095272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0.049925</td>\n",
       "      <td>0.023384</td>\n",
       "      <td>0.010101</td>\n",
       "      <td>0.034352</td>\n",
       "      <td>0.046812</td>\n",
       "      <td>0.061616</td>\n",
       "      <td>0.122033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.011277</td>\n",
       "      <td>0.016732</td>\n",
       "      <td>-0.011943</td>\n",
       "      <td>-0.000102</td>\n",
       "      <td>0.006590</td>\n",
       "      <td>0.016734</td>\n",
       "      <td>0.062283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.011952</td>\n",
       "      <td>0.016571</td>\n",
       "      <td>-0.011192</td>\n",
       "      <td>0.000828</td>\n",
       "      <td>0.007348</td>\n",
       "      <td>0.015933</td>\n",
       "      <td>0.058177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   78.0  0.035986  0.012234  0.013591  0.028193  0.036004   \n",
       "1   FullRate_White   97.0  0.043566  0.021088  0.009223  0.028549  0.039678   \n",
       "2       Rate_Black   78.0  0.043876  0.015192  0.013072  0.033490  0.044344   \n",
       "3       Rate_White   97.0  0.049925  0.023384  0.010101  0.034352  0.046812   \n",
       "4      Rate_Parity   75.0  0.011277  0.016732 -0.011943 -0.000102  0.006590   \n",
       "5  FullRate_Parity   75.0  0.011952  0.016571 -0.011192  0.000828  0.007348   \n",
       "\n",
       "        75%       max  \n",
       "0  0.041853  0.076923  \n",
       "1  0.054595  0.109376  \n",
       "2  0.050431  0.095272  \n",
       "3  0.061616  0.122033  \n",
       "4  0.016734  0.062283  \n",
       "5  0.015933  0.058177  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "T['FullRate_Parity']  = T['FullRate_White'] - T['FullRate_Black']\n",
    "# T['Rate_ParityC'] = T['Rate_WhiteC'] - T['Rate_BlackC']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "affected-wrong",
   "metadata": {},
   "source": [
    "# IV. OCR States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rapid-stanley",
   "metadata": {},
   "source": [
    "## [!] C. West Virginia* (Done)\n",
    "\n",
    "\n",
    "Copy Response from Browsers\n",
    "\n",
    "https://dhhr.wv.gov/COVID-19/Pages/default.aspx\n",
    "\n",
    "url = 'https://wabi-us-gov-virginia-api.analysis.usgovcloudapi.net/public/reports/querydata?synchronous=true'\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "cardiovascular-uniform",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CountyVaccine/TMP/May20/WestVirginia/WV5_20-full.json'"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'CountyVaccine/TMP/May20/WestVirginia/WV5_20.json'\n",
    "'CountyVaccine/TMP/May20/WestVirginia/WV5_20-full.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "virtual-camping",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter West Virginia Manually collected Data:  CountyVaccine/TMP/May20/WestVirginia/WV5_20.json\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/WestVirginia/WV5_20.json\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "Date = Today_Date\n",
    "import os\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter West Virginia Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "temporal-clothing",
   "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>County</th>\n",
       "      <th>Date</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Numb_White</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Barbour</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>21</td>\n",
       "      <td>4366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Berkeley</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>2337</td>\n",
       "      <td>28453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boone</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>48</td>\n",
       "      <td>6547</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Braxton</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>16</td>\n",
       "      <td>5061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brooke</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>54</td>\n",
       "      <td>7285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cabell</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1422</td>\n",
       "      <td>35776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Calhoun</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Clay</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>6</td>\n",
       "      <td>2844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Doddridge</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>26</td>\n",
       "      <td>3627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Fayette</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>702</td>\n",
       "      <td>16688</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gilmer</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>17</td>\n",
       "      <td>2189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grant</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>20</td>\n",
       "      <td>3824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Greenbrier</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>359</td>\n",
       "      <td>13492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Hampshire</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>52</td>\n",
       "      <td>6785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Hancock</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>232</td>\n",
       "      <td>12891</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Hardy</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>182</td>\n",
       "      <td>5281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Harrison</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>338</td>\n",
       "      <td>22752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Jackson</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>24</td>\n",
       "      <td>10253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jefferson</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1222</td>\n",
       "      <td>16667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Kanawha</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>5017</td>\n",
       "      <td>75564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Lewis</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>26</td>\n",
       "      <td>6499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Lincoln</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>17</td>\n",
       "      <td>6980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Logan</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>222</td>\n",
       "      <td>13172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Marion</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>642</td>\n",
       "      <td>20513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Marshall</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>40</td>\n",
       "      <td>10043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mason</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>34</td>\n",
       "      <td>7021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>McDowell</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>523</td>\n",
       "      <td>5359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Mercer</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>961</td>\n",
       "      <td>19769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Mineral</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>190</td>\n",
       "      <td>8350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Mingo</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>85</td>\n",
       "      <td>5560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Monongalia</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1305</td>\n",
       "      <td>38876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Monroe</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>34</td>\n",
       "      <td>4399</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Morgan</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>17</td>\n",
       "      <td>4457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Nicholas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>15</td>\n",
       "      <td>9096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>462</td>\n",
       "      <td>18095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Pendleton</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>51</td>\n",
       "      <td>2929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Pleasants</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>20</td>\n",
       "      <td>2475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pocahontas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18</td>\n",
       "      <td>3410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Preston</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>46</td>\n",
       "      <td>10647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Putnam</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>203</td>\n",
       "      <td>20081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Raleigh</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1601</td>\n",
       "      <td>24987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Randolph</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>88</td>\n",
       "      <td>8701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Ritchie</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18</td>\n",
       "      <td>3169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Roane</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>5</td>\n",
       "      <td>4825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Summers</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>73</td>\n",
       "      <td>4321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Taylor</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>26</td>\n",
       "      <td>5529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Tucker</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>6</td>\n",
       "      <td>3303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Tyler</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Upshur</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>67</td>\n",
       "      <td>8412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wayne</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>95</td>\n",
       "      <td>14122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Webster</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Wetzel</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>12</td>\n",
       "      <td>5640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Wirt</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>5</td>\n",
       "      <td>1833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Wood</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>293</td>\n",
       "      <td>29570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>42</td>\n",
       "      <td>7935</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        County        Date  Numb_Black  Numb_White\n",
       "0      Barbour  2021-05-20          21        4366\n",
       "1     Berkeley  2021-05-20        2337       28453\n",
       "2        Boone  2021-05-20          48        6547\n",
       "3      Braxton  2021-05-20          16        5061\n",
       "4       Brooke  2021-05-20          54        7285\n",
       "5       Cabell  2021-05-20        1422       35776\n",
       "6      Calhoun  2021-05-20           1        2134\n",
       "7         Clay  2021-05-20           6        2844\n",
       "8    Doddridge  2021-05-20          26        3627\n",
       "9      Fayette  2021-05-20         702       16688\n",
       "10      Gilmer  2021-05-20          17        2189\n",
       "11       Grant  2021-05-20          20        3824\n",
       "12  Greenbrier  2021-05-20         359       13492\n",
       "13   Hampshire  2021-05-20          52        6785\n",
       "14     Hancock  2021-05-20         232       12891\n",
       "15       Hardy  2021-05-20         182        5281\n",
       "16    Harrison  2021-05-20         338       22752\n",
       "17     Jackson  2021-05-20          24       10253\n",
       "18   Jefferson  2021-05-20        1222       16667\n",
       "19     Kanawha  2021-05-20        5017       75564\n",
       "20       Lewis  2021-05-20          26        6499\n",
       "21     Lincoln  2021-05-20          17        6980\n",
       "22       Logan  2021-05-20         222       13172\n",
       "23      Marion  2021-05-20         642       20513\n",
       "24    Marshall  2021-05-20          40       10043\n",
       "25       Mason  2021-05-20          34        7021\n",
       "26    McDowell  2021-05-20         523        5359\n",
       "27      Mercer  2021-05-20         961       19769\n",
       "28     Mineral  2021-05-20         190        8350\n",
       "29       Mingo  2021-05-20          85        5560\n",
       "30  Monongalia  2021-05-20        1305       38876\n",
       "31      Monroe  2021-05-20          34        4399\n",
       "32      Morgan  2021-05-20          17        4457\n",
       "33    Nicholas  2021-05-20          15        9096\n",
       "34        Ohio  2021-05-20         462       18095\n",
       "35   Pendleton  2021-05-20          51        2929\n",
       "36   Pleasants  2021-05-20          20        2475\n",
       "37  Pocahontas  2021-05-20          18        3410\n",
       "38     Preston  2021-05-20          46       10647\n",
       "39      Putnam  2021-05-20         203       20081\n",
       "40     Raleigh  2021-05-20        1601       24987\n",
       "41    Randolph  2021-05-20          88        8701\n",
       "42     Ritchie  2021-05-20          18        3169\n",
       "43       Roane  2021-05-20           5        4825\n",
       "44     Summers  2021-05-20          73        4321\n",
       "45      Taylor  2021-05-20          26        5529\n",
       "46      Tucker  2021-05-20           6        3303\n",
       "47       Tyler  2021-05-20           1        2508\n",
       "48      Upshur  2021-05-20          67        8412\n",
       "49       Wayne  2021-05-20          95       14122\n",
       "50     Webster  2021-05-20           1        2809\n",
       "51      Wetzel  2021-05-20          12        5640\n",
       "52        Wirt  2021-05-20           5        1833\n",
       "53        Wood  2021-05-20         293       29570\n",
       "54     Wyoming  2021-05-20          42        7935"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "import requests\n",
    "import json\n",
    "\n",
    "\n",
    "date = Today_Date\n",
    "\n",
    "\n",
    "def get_WestVirginia_Raw(date, response_data, prefix = ''):\n",
    "    \n",
    "\n",
    "    # data = json.loads(response_data)\n",
    "    data = response_data\n",
    "\n",
    "    tmp = data['results'][0]['result']['data']['dsr']['DS'][0]\n",
    "    # [i for i in tmp]\n",
    "\n",
    "    tmp2 = tmp['PH'][1]['DM1']\n",
    "    # len(tmp2)\n",
    "    # tmp2\n",
    "\n",
    "\n",
    "    L = []\n",
    "    for county_info in tmp2:\n",
    "        d= {}\n",
    "        d['County'] = county_info['G0']\n",
    "        d['Date' ] =  date\n",
    "        try:\n",
    "            info = [race['C'] for race in county_info['X'][:-1]]\n",
    "            # print(info)\n",
    "\n",
    "            d[prefix + 'Numb_Black'] = info[0][0]\n",
    "            d[prefix + 'Numb_White'] = info[-1][0]\n",
    "            L.append(d)\n",
    "        except:\n",
    "            print(d['County'])\n",
    "            print(county_info['X'])\n",
    "\n",
    "\n",
    "    WestVirginia = pd.DataFrame(L)\n",
    "    return WestVirginia\n",
    "    \n",
    "    \n",
    "    \n",
    "with open(Path, 'r') as f:\n",
    "    response_data = json.load(f)\n",
    "    \n",
    "    \n",
    "\n",
    "\n",
    "    \n",
    "    \n",
    "WestVirginiaFirst = get_WestVirginia_Raw(date, response_data)\n",
    "\n",
    "WestVirginiaFirst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "renewable-subscriber",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter West Virginia Manually collected Data:  CountyVaccine/TMP/May20/WestVirginia/WV5_20-full.json\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/WestVirginia/WV5_20-full.json\n"
     ]
    }
   ],
   "source": [
    "Date = Today_Date\n",
    "import os\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter West Virginia Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "FullPath = Path   \n",
    "print('\\nThe Path we have:')\n",
    "print(FullPath)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "distinguished-constitutional",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>Date</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Barbour</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18</td>\n",
       "      <td>3554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Berkeley</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1858</td>\n",
       "      <td>23690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boone</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>38</td>\n",
       "      <td>5316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Braxton</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>14</td>\n",
       "      <td>4172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brooke</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>48</td>\n",
       "      <td>6545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Cabell</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1157</td>\n",
       "      <td>30473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Calhoun</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>1637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Clay</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>3</td>\n",
       "      <td>2361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Doddridge</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>20</td>\n",
       "      <td>3280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Fayette</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>621</td>\n",
       "      <td>14431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Gilmer</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>13</td>\n",
       "      <td>1896</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Grant</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>16</td>\n",
       "      <td>3512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Greenbrier</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>317</td>\n",
       "      <td>12105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Hampshire</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>42</td>\n",
       "      <td>5909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Hancock</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>194</td>\n",
       "      <td>11465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Hardy</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>165</td>\n",
       "      <td>4794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Harrison</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>283</td>\n",
       "      <td>20175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Jackson</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18</td>\n",
       "      <td>9190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Jefferson</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1006</td>\n",
       "      <td>14206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Kanawha</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>4112</td>\n",
       "      <td>62747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Lewis</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>22</td>\n",
       "      <td>5657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Lincoln</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>10</td>\n",
       "      <td>6043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Logan</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>185</td>\n",
       "      <td>11096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Marion</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>525</td>\n",
       "      <td>17798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Marshall</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>31</td>\n",
       "      <td>9104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mason</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>21</td>\n",
       "      <td>5804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>McDowell</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>448</td>\n",
       "      <td>4563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Mercer</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>760</td>\n",
       "      <td>16407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Mineral</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>167</td>\n",
       "      <td>7405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Mingo</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>69</td>\n",
       "      <td>4706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Monongalia</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1089</td>\n",
       "      <td>34908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Monroe</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>30</td>\n",
       "      <td>4097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Morgan</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>17</td>\n",
       "      <td>3696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Nicholas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>12</td>\n",
       "      <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>393</td>\n",
       "      <td>16013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Pendleton</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>46</td>\n",
       "      <td>2659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Pleasants</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>19</td>\n",
       "      <td>2230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pocahontas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>16</td>\n",
       "      <td>3153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Preston</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>38</td>\n",
       "      <td>9652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Putnam</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>152</td>\n",
       "      <td>16406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Raleigh</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1310</td>\n",
       "      <td>20967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Randolph</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>66</td>\n",
       "      <td>7489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Ritchie</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>16</td>\n",
       "      <td>2854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Roane</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>2</td>\n",
       "      <td>4191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Summers</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>69</td>\n",
       "      <td>3994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Taylor</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>17</td>\n",
       "      <td>5026</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Tucker</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>6</td>\n",
       "      <td>3002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Tyler</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Upshur</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>49</td>\n",
       "      <td>7140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wayne</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>73</td>\n",
       "      <td>11835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Webster</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Wetzel</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>11</td>\n",
       "      <td>5034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Wirt</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>4</td>\n",
       "      <td>1568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Wood</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>227</td>\n",
       "      <td>25537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>37</td>\n",
       "      <td>6896</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        County        Date  FullNumb_Black  FullNumb_White\n",
       "0      Barbour  2021-05-20              18            3554\n",
       "1     Berkeley  2021-05-20            1858           23690\n",
       "2        Boone  2021-05-20              38            5316\n",
       "3      Braxton  2021-05-20              14            4172\n",
       "4       Brooke  2021-05-20              48            6545\n",
       "5       Cabell  2021-05-20            1157           30473\n",
       "6      Calhoun  2021-05-20               1            1637\n",
       "7         Clay  2021-05-20               3            2361\n",
       "8    Doddridge  2021-05-20              20            3280\n",
       "9      Fayette  2021-05-20             621           14431\n",
       "10      Gilmer  2021-05-20              13            1896\n",
       "11       Grant  2021-05-20              16            3512\n",
       "12  Greenbrier  2021-05-20             317           12105\n",
       "13   Hampshire  2021-05-20              42            5909\n",
       "14     Hancock  2021-05-20             194           11465\n",
       "15       Hardy  2021-05-20             165            4794\n",
       "16    Harrison  2021-05-20             283           20175\n",
       "17     Jackson  2021-05-20              18            9190\n",
       "18   Jefferson  2021-05-20            1006           14206\n",
       "19     Kanawha  2021-05-20            4112           62747\n",
       "20       Lewis  2021-05-20              22            5657\n",
       "21     Lincoln  2021-05-20              10            6043\n",
       "22       Logan  2021-05-20             185           11096\n",
       "23      Marion  2021-05-20             525           17798\n",
       "24    Marshall  2021-05-20              31            9104\n",
       "25       Mason  2021-05-20              21            5804\n",
       "26    McDowell  2021-05-20             448            4563\n",
       "27      Mercer  2021-05-20             760           16407\n",
       "28     Mineral  2021-05-20             167            7405\n",
       "29       Mingo  2021-05-20              69            4706\n",
       "30  Monongalia  2021-05-20            1089           34908\n",
       "31      Monroe  2021-05-20              30            4097\n",
       "32      Morgan  2021-05-20              17            3696\n",
       "33    Nicholas  2021-05-20              12            8000\n",
       "34        Ohio  2021-05-20             393           16013\n",
       "35   Pendleton  2021-05-20              46            2659\n",
       "36   Pleasants  2021-05-20              19            2230\n",
       "37  Pocahontas  2021-05-20              16            3153\n",
       "38     Preston  2021-05-20              38            9652\n",
       "39      Putnam  2021-05-20             152           16406\n",
       "40     Raleigh  2021-05-20            1310           20967\n",
       "41    Randolph  2021-05-20              66            7489\n",
       "42     Ritchie  2021-05-20              16            2854\n",
       "43       Roane  2021-05-20               2            4191\n",
       "44     Summers  2021-05-20              69            3994\n",
       "45      Taylor  2021-05-20              17            5026\n",
       "46      Tucker  2021-05-20               6            3002\n",
       "47       Tyler  2021-05-20               1            2264\n",
       "48      Upshur  2021-05-20              49            7140\n",
       "49       Wayne  2021-05-20              73           11835\n",
       "50     Webster  2021-05-20               1            2282\n",
       "51      Wetzel  2021-05-20              11            5034\n",
       "52        Wirt  2021-05-20               4            1568\n",
       "53        Wood  2021-05-20             227           25537\n",
       "54     Wyoming  2021-05-20              37            6896"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(FullPath, 'r') as f:\n",
    "    response_data_full = json.load(f)\n",
    "\n",
    "\n",
    "WestVirginiaFull = get_WestVirginia_Raw(date, response_data_full, prefix = 'Full')\n",
    "# WestVirginia.to_csv('FinalResult/Post_WestVirginia.csv')\n",
    "WestVirginiaFull"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "presidential-profit",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(55, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>County</th>\n",
       "      <th>Date_x</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Numb_White</th>\n",
       "      <th>Date_y</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Barbour</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>21</td>\n",
       "      <td>4366</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18</td>\n",
       "      <td>3554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Berkeley</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>2337</td>\n",
       "      <td>28453</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1858</td>\n",
       "      <td>23690</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boone</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>48</td>\n",
       "      <td>6547</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>38</td>\n",
       "      <td>5316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Braxton</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>16</td>\n",
       "      <td>5061</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>14</td>\n",
       "      <td>4172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brooke</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>54</td>\n",
       "      <td>7285</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>48</td>\n",
       "      <td>6545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     County      Date_x  Numb_Black  Numb_White      Date_y  FullNumb_Black  \\\n",
       "0   Barbour  2021-05-20          21        4366  2021-05-20              18   \n",
       "1  Berkeley  2021-05-20        2337       28453  2021-05-20            1858   \n",
       "2     Boone  2021-05-20          48        6547  2021-05-20              38   \n",
       "3   Braxton  2021-05-20          16        5061  2021-05-20              14   \n",
       "4    Brooke  2021-05-20          54        7285  2021-05-20              48   \n",
       "\n",
       "   FullNumb_White  \n",
       "0            3554  \n",
       "1           23690  \n",
       "2            5316  \n",
       "3            4172  \n",
       "4            6545  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "WestVirginia = pd.merge(WestVirginiaFirst, WestVirginiaFull, on = 'County')\n",
    "print(WestVirginia.shape)\n",
    "WestVirginia.head() # 55 counties\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "african-bulgarian",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(55, 20)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>FullRate_White</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Numb_White</th>\n",
       "      <th>Rate_Black</th>\n",
       "      <th>Rate_White</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>Barbour, West Virginia</td>\n",
       "      <td>18</td>\n",
       "      <td>3554</td>\n",
       "      <td>0.108434</td>\n",
       "      <td>0.224072</td>\n",
       "      <td>21</td>\n",
       "      <td>4366</td>\n",
       "      <td>0.126506</td>\n",
       "      <td>0.275266</td>\n",
       "      <td>106</td>\n",
       "      <td>225</td>\n",
       "      <td>50</td>\n",
       "      <td>91</td>\n",
       "      <td>166</td>\n",
       "      <td>280</td>\n",
       "      <td>15861</td>\n",
       "      <td>16103</td>\n",
       "      <td>16441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>Berkeley</td>\n",
       "      <td>Berkeley, West Virginia</td>\n",
       "      <td>1858</td>\n",
       "      <td>23690</td>\n",
       "      <td>0.197660</td>\n",
       "      <td>0.226725</td>\n",
       "      <td>2337</td>\n",
       "      <td>28453</td>\n",
       "      <td>0.248617</td>\n",
       "      <td>0.272309</td>\n",
       "      <td>300</td>\n",
       "      <td>1074</td>\n",
       "      <td>1328</td>\n",
       "      <td>2011</td>\n",
       "      <td>9400</td>\n",
       "      <td>11814</td>\n",
       "      <td>104488</td>\n",
       "      <td>107962</td>\n",
       "      <td>119171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>Boone</td>\n",
       "      <td>Boone, West Virginia</td>\n",
       "      <td>38</td>\n",
       "      <td>5316</td>\n",
       "      <td>0.238994</td>\n",
       "      <td>0.252362</td>\n",
       "      <td>48</td>\n",
       "      <td>6547</td>\n",
       "      <td>0.301887</td>\n",
       "      <td>0.310800</td>\n",
       "      <td>33</td>\n",
       "      <td>95</td>\n",
       "      <td>27</td>\n",
       "      <td>41</td>\n",
       "      <td>159</td>\n",
       "      <td>260</td>\n",
       "      <td>21065</td>\n",
       "      <td>21228</td>\n",
       "      <td>21457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>Braxton</td>\n",
       "      <td>Braxton, West Virginia</td>\n",
       "      <td>14</td>\n",
       "      <td>4172</td>\n",
       "      <td>0.141414</td>\n",
       "      <td>0.307126</td>\n",
       "      <td>16</td>\n",
       "      <td>5061</td>\n",
       "      <td>0.161616</td>\n",
       "      <td>0.372571</td>\n",
       "      <td>61</td>\n",
       "      <td>135</td>\n",
       "      <td>33</td>\n",
       "      <td>62</td>\n",
       "      <td>99</td>\n",
       "      <td>178</td>\n",
       "      <td>13584</td>\n",
       "      <td>13751</td>\n",
       "      <td>13957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>Brooke</td>\n",
       "      <td>Brooke, West Virginia</td>\n",
       "      <td>48</td>\n",
       "      <td>6545</td>\n",
       "      <td>0.147692</td>\n",
       "      <td>0.309676</td>\n",
       "      <td>54</td>\n",
       "      <td>7285</td>\n",
       "      <td>0.166154</td>\n",
       "      <td>0.344689</td>\n",
       "      <td>41</td>\n",
       "      <td>148</td>\n",
       "      <td>110</td>\n",
       "      <td>157</td>\n",
       "      <td>325</td>\n",
       "      <td>516</td>\n",
       "      <td>21135</td>\n",
       "      <td>21444</td>\n",
       "      <td>21939</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           State    County     Geographic Area Name  FullNumb_Black  \\\n",
       "0  West Virginia   Barbour   Barbour, West Virginia              18   \n",
       "1  West Virginia  Berkeley  Berkeley, West Virginia            1858   \n",
       "2  West Virginia     Boone     Boone, West Virginia              38   \n",
       "3  West Virginia   Braxton   Braxton, West Virginia              14   \n",
       "4  West Virginia    Brooke    Brooke, West Virginia              48   \n",
       "\n",
       "   FullNumb_White  FullRate_Black  FullRate_White  Numb_Black  Numb_White  \\\n",
       "0            3554        0.108434        0.224072          21        4366   \n",
       "1           23690        0.197660        0.226725        2337       28453   \n",
       "2            5316        0.238994        0.252362          48        6547   \n",
       "3            4172        0.141414        0.307126          16        5061   \n",
       "4            6545        0.147692        0.309676          54        7285   \n",
       "\n",
       "   Rate_Black  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0    0.126506    0.275266          106           225           50   \n",
       "1    0.248617    0.272309          300          1074         1328   \n",
       "2    0.301887    0.310800           33            95           27   \n",
       "3    0.161616    0.372571           61           135           33   \n",
       "4    0.166154    0.344689           41           148          110   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0            91          166           280        15861         16103   \n",
       "1          2011         9400         11814       104488        107962   \n",
       "2            41          159           260        21065         21228   \n",
       "3            62           99           178        13584         13751   \n",
       "4           157          325           516        21135         21444   \n",
       "\n",
       "   Total_Whole  \n",
       "0        16441  \n",
       "1       119171  \n",
       "2        21457  \n",
       "3        13957  \n",
       "4        21939  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "state = 'West Virginia' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "for idx, row in WestVirginia.iterrows(): ## \n",
    "    d = row.to_dict()\n",
    "    # print(d)\n",
    "    new_d = {}\n",
    "    \n",
    "        \n",
    "    new_d['State'] = state\n",
    "    new_d['County'] = ' '.join([i.lower().capitalize() for i in d['County'].split(' ')])\n",
    "    RenameCounties = {\n",
    "        # 'Mckean': 'McKean'\n",
    "     'Mcdowell': 'McDowell'\n",
    "    }\n",
    "    if new_d['County'] in RenameCounties:\n",
    "        new_d['County'] = RenameCounties[new_d['County']]\n",
    "\n",
    "    new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "\n",
    "    new_d['Numb_White'] = d['Numb_White']\n",
    "    # new_d['Rate_White'] = np.nan\n",
    "    \n",
    "    new_d['Numb_Black'] = d['Numb_Black']\n",
    "    \n",
    "    \n",
    "    new_d['FullNumb_White'] = d['FullNumb_White']\n",
    "    # new_d['Rate_White'] = np.nan\n",
    "    \n",
    "    new_d['FullNumb_Black'] = d['FullNumb_Black']\n",
    "    \n",
    "    \n",
    "    # new_d['Date'] = d['Date']\n",
    "    \n",
    "    new_L.append(new_d)\n",
    "    \n",
    "    \n",
    "Data = pd.DataFrame(new_L)\n",
    "\n",
    "\n",
    "not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "\n",
    "T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "\n",
    "for Race in ['White', 'Black']:\n",
    "    Rate = T['Numb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['Rate_' + Race] = Rate\n",
    "    \n",
    "    Rate = T['FullNumb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['FullRate_' + Race] = Rate\n",
    "\n",
    "\n",
    "\n",
    "T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "effective-identifier",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California', 'Tennessee', 'North Carolina', 'West Virginia']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FullRate_Black</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.186924</td>\n",
       "      <td>0.093325</td>\n",
       "      <td>0.015366</td>\n",
       "      <td>0.115246</td>\n",
       "      <td>0.187739</td>\n",
       "      <td>0.259972</td>\n",
       "      <td>0.379310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.316548</td>\n",
       "      <td>0.054320</td>\n",
       "      <td>0.208516</td>\n",
       "      <td>0.285238</td>\n",
       "      <td>0.309981</td>\n",
       "      <td>0.352190</td>\n",
       "      <td>0.447993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.227316</td>\n",
       "      <td>0.106432</td>\n",
       "      <td>0.020095</td>\n",
       "      <td>0.142190</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>0.311304</td>\n",
       "      <td>0.418502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.363615</td>\n",
       "      <td>0.056346</td>\n",
       "      <td>0.246356</td>\n",
       "      <td>0.328596</td>\n",
       "      <td>0.359165</td>\n",
       "      <td>0.402854</td>\n",
       "      <td>0.492912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.136299</td>\n",
       "      <td>0.096159</td>\n",
       "      <td>-0.053133</td>\n",
       "      <td>0.079568</td>\n",
       "      <td>0.130063</td>\n",
       "      <td>0.208274</td>\n",
       "      <td>0.326974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.129623</td>\n",
       "      <td>0.084744</td>\n",
       "      <td>-0.043269</td>\n",
       "      <td>0.080867</td>\n",
       "      <td>0.121919</td>\n",
       "      <td>0.196869</td>\n",
       "      <td>0.300573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   55.0  0.186924  0.093325  0.015366  0.115246  0.187739   \n",
       "1   FullRate_White   55.0  0.316548  0.054320  0.208516  0.285238  0.309981   \n",
       "2       Rate_Black   55.0  0.227316  0.106432  0.020095  0.142190  0.230769   \n",
       "3       Rate_White   55.0  0.363615  0.056346  0.246356  0.328596  0.359165   \n",
       "4      Rate_Parity   55.0  0.136299  0.096159 -0.053133  0.079568  0.130063   \n",
       "5  FullRate_Parity   55.0  0.129623  0.084744 -0.043269  0.080867  0.121919   \n",
       "\n",
       "        75%       max  \n",
       "0  0.259972  0.379310  \n",
       "1  0.352190  0.447993  \n",
       "2  0.311304  0.418502  \n",
       "3  0.402854  0.492912  \n",
       "4  0.208274  0.326974  \n",
       "5  0.196869  0.300573  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# West Virginia\tBarbour\tBarbour, West Virginia\t2021-03-26\t10\t2839\t0.060241\t0.035714\t0.178992\t0.176303\t106\t225\t50\n",
    "\n",
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "T['FullRate_Parity']  = T['FullRate_White'] - T['FullRate_Black']\n",
    "# T['Rate_ParityC'] = T['Rate_WhiteC'] - T['Rate_BlackC']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bibliographic-prime",
   "metadata": {},
   "source": [
    "# VI. Manual States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "addressed-philadelphia",
   "metadata": {},
   "source": [
    "## [!] E. Maine* (Done)\n",
    "\n",
    "\n",
    "White_Num = White_noHis_Num + White_His_Num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "offshore-peter",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CountyVaccine/TMP/May20/Maine/Maine 5_20.xlsx'"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'CountyVaccine/TMP/May20/Maine/Maine 5_20.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "popular-cooper",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter Maine Manually collected Data:  CountyVaccine/TMP/May20/Maine/Maine 5_20.xlsx\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/Maine/Maine 5_20.xlsx\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "\n",
    "Date = Today_Date\n",
    "\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter Maine Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "pacific-softball",
   "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>County</th>\n",
       "      <th>Black</th>\n",
       "      <th>White</th>\n",
       "      <th>Black-full</th>\n",
       "      <th>White-full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDROSCOGGIN</td>\n",
       "      <td>1193</td>\n",
       "      <td>36828</td>\n",
       "      <td>852</td>\n",
       "      <td>33960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AROOSTOOK</td>\n",
       "      <td>135</td>\n",
       "      <td>22121</td>\n",
       "      <td>115</td>\n",
       "      <td>22853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CUMBERLAND</td>\n",
       "      <td>3659</td>\n",
       "      <td>141819</td>\n",
       "      <td>2723</td>\n",
       "      <td>132266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FRANKLIN</td>\n",
       "      <td>39</td>\n",
       "      <td>10803</td>\n",
       "      <td>25</td>\n",
       "      <td>10761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HANCOCK</td>\n",
       "      <td>216</td>\n",
       "      <td>24623</td>\n",
       "      <td>151</td>\n",
       "      <td>23906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>KENNEBEC</td>\n",
       "      <td>394</td>\n",
       "      <td>44858</td>\n",
       "      <td>298</td>\n",
       "      <td>42332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>KNOX</td>\n",
       "      <td>109</td>\n",
       "      <td>18511</td>\n",
       "      <td>78</td>\n",
       "      <td>18425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LINCOLN</td>\n",
       "      <td>81</td>\n",
       "      <td>17604</td>\n",
       "      <td>58</td>\n",
       "      <td>16441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>OXFORD</td>\n",
       "      <td>112</td>\n",
       "      <td>21604</td>\n",
       "      <td>86</td>\n",
       "      <td>20233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PENOBSCOT</td>\n",
       "      <td>590</td>\n",
       "      <td>58142</td>\n",
       "      <td>518</td>\n",
       "      <td>56571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PISCATAQUIS</td>\n",
       "      <td>14</td>\n",
       "      <td>6158</td>\n",
       "      <td>10</td>\n",
       "      <td>5943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SAGADAHOC</td>\n",
       "      <td>154</td>\n",
       "      <td>15943</td>\n",
       "      <td>105</td>\n",
       "      <td>14532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SOMERSET</td>\n",
       "      <td>68</td>\n",
       "      <td>14301</td>\n",
       "      <td>62</td>\n",
       "      <td>16337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WALDO</td>\n",
       "      <td>81</td>\n",
       "      <td>15685</td>\n",
       "      <td>62</td>\n",
       "      <td>15650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>WASHINGTON</td>\n",
       "      <td>38</td>\n",
       "      <td>8548</td>\n",
       "      <td>44</td>\n",
       "      <td>10579</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>YORK</td>\n",
       "      <td>795</td>\n",
       "      <td>85551</td>\n",
       "      <td>602</td>\n",
       "      <td>80260</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          County  Black   White  Black-full  White-full\n",
       "0   ANDROSCOGGIN   1193   36828         852       33960\n",
       "1      AROOSTOOK    135   22121         115       22853\n",
       "2     CUMBERLAND   3659  141819        2723      132266\n",
       "3       FRANKLIN     39   10803          25       10761\n",
       "4        HANCOCK    216   24623         151       23906\n",
       "5       KENNEBEC    394   44858         298       42332\n",
       "6           KNOX    109   18511          78       18425\n",
       "7        LINCOLN     81   17604          58       16441\n",
       "8         OXFORD    112   21604          86       20233\n",
       "9      PENOBSCOT    590   58142         518       56571\n",
       "10   PISCATAQUIS     14    6158          10        5943\n",
       "11     SAGADAHOC    154   15943         105       14532\n",
       "12      SOMERSET     68   14301          62       16337\n",
       "13         WALDO     81   15685          62       15650\n",
       "14    WASHINGTON     38    8548          44       10579\n",
       "15          YORK    795   85551         602       80260"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "try:\n",
    "    Data = pd.read_csv(Path)\n",
    "except:\n",
    "    Data = pd.read_excel(Path)\n",
    "    \n",
    "Data = Data[Data.columns[1:]]\n",
    "Maine = Data\n",
    "# Maine.to_csv('FinalResult/Maine.csv')\n",
    "Maine = Maine.rename(columns = {'Black or African American': 'Black'})\n",
    "Maine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "republican-survivor",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(16, 20)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>FullRate_White</th>\n",
       "      <th>Numb_Black</th>\n",
       "      <th>Numb_White</th>\n",
       "      <th>Rate_Black</th>\n",
       "      <th>Rate_White</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maine</td>\n",
       "      <td>Androscoggin</td>\n",
       "      <td>Androscoggin, Maine</td>\n",
       "      <td>852</td>\n",
       "      <td>33960</td>\n",
       "      <td>0.174877</td>\n",
       "      <td>0.341949</td>\n",
       "      <td>1193</td>\n",
       "      <td>36828</td>\n",
       "      <td>0.244869</td>\n",
       "      <td>0.370828</td>\n",
       "      <td>457</td>\n",
       "      <td>1251</td>\n",
       "      <td>1014</td>\n",
       "      <td>1685</td>\n",
       "      <td>4872</td>\n",
       "      <td>6145</td>\n",
       "      <td>99313</td>\n",
       "      <td>101811</td>\n",
       "      <td>108277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Maine</td>\n",
       "      <td>Aroostook</td>\n",
       "      <td>Aroostook, Maine</td>\n",
       "      <td>115</td>\n",
       "      <td>22853</td>\n",
       "      <td>0.144472</td>\n",
       "      <td>0.359510</td>\n",
       "      <td>135</td>\n",
       "      <td>22121</td>\n",
       "      <td>0.169598</td>\n",
       "      <td>0.347995</td>\n",
       "      <td>1293</td>\n",
       "      <td>1922</td>\n",
       "      <td>343</td>\n",
       "      <td>516</td>\n",
       "      <td>796</td>\n",
       "      <td>1063</td>\n",
       "      <td>63567</td>\n",
       "      <td>64576</td>\n",
       "      <td>67055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Maine</td>\n",
       "      <td>Cumberland</td>\n",
       "      <td>Cumberland, Maine</td>\n",
       "      <td>2723</td>\n",
       "      <td>132266</td>\n",
       "      <td>0.288118</td>\n",
       "      <td>0.487360</td>\n",
       "      <td>3659</td>\n",
       "      <td>141819</td>\n",
       "      <td>0.387155</td>\n",
       "      <td>0.522560</td>\n",
       "      <td>1037</td>\n",
       "      <td>2945</td>\n",
       "      <td>7057</td>\n",
       "      <td>9344</td>\n",
       "      <td>9451</td>\n",
       "      <td>11622</td>\n",
       "      <td>271393</td>\n",
       "      <td>277223</td>\n",
       "      <td>295003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maine</td>\n",
       "      <td>Franklin</td>\n",
       "      <td>Franklin, Maine</td>\n",
       "      <td>25</td>\n",
       "      <td>10761</td>\n",
       "      <td>0.149701</td>\n",
       "      <td>0.368288</td>\n",
       "      <td>39</td>\n",
       "      <td>10803</td>\n",
       "      <td>0.233533</td>\n",
       "      <td>0.369725</td>\n",
       "      <td>146</td>\n",
       "      <td>440</td>\n",
       "      <td>160</td>\n",
       "      <td>259</td>\n",
       "      <td>167</td>\n",
       "      <td>299</td>\n",
       "      <td>29219</td>\n",
       "      <td>29696</td>\n",
       "      <td>30199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Maine</td>\n",
       "      <td>Hancock</td>\n",
       "      <td>Hancock, Maine</td>\n",
       "      <td>151</td>\n",
       "      <td>23906</td>\n",
       "      <td>0.266314</td>\n",
       "      <td>0.453891</td>\n",
       "      <td>216</td>\n",
       "      <td>24623</td>\n",
       "      <td>0.380952</td>\n",
       "      <td>0.467505</td>\n",
       "      <td>261</td>\n",
       "      <td>664</td>\n",
       "      <td>684</td>\n",
       "      <td>878</td>\n",
       "      <td>567</td>\n",
       "      <td>805</td>\n",
       "      <td>52669</td>\n",
       "      <td>53436</td>\n",
       "      <td>54987</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   State        County Geographic Area Name  FullNumb_Black  FullNumb_White  \\\n",
       "0  Maine  Androscoggin  Androscoggin, Maine             852           33960   \n",
       "1  Maine     Aroostook     Aroostook, Maine             115           22853   \n",
       "2  Maine    Cumberland    Cumberland, Maine            2723          132266   \n",
       "3  Maine      Franklin      Franklin, Maine              25           10761   \n",
       "4  Maine       Hancock       Hancock, Maine             151           23906   \n",
       "\n",
       "   FullRate_Black  FullRate_White  Numb_Black  Numb_White  Rate_Black  \\\n",
       "0        0.174877        0.341949        1193       36828    0.244869   \n",
       "1        0.144472        0.359510         135       22121    0.169598   \n",
       "2        0.288118        0.487360        3659      141819    0.387155   \n",
       "3        0.149701        0.368288          39       10803    0.233533   \n",
       "4        0.266314        0.453891         216       24623    0.380952   \n",
       "\n",
       "   Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  Total_AsianC  \\\n",
       "0    0.370828          457          1251         1014          1685   \n",
       "1    0.347995         1293          1922          343           516   \n",
       "2    0.522560         1037          2945         7057          9344   \n",
       "3    0.369725          146           440          160           259   \n",
       "4    0.467505          261           664          684           878   \n",
       "\n",
       "   Total_Black  Total_BlackC  Total_White  Total_WhiteC  Total_Whole  \n",
       "0         4872          6145        99313        101811       108277  \n",
       "1          796          1063        63567         64576        67055  \n",
       "2         9451         11622       271393        277223       295003  \n",
       "3          167           299        29219         29696        30199  \n",
       "4          567           805        52669         53436        54987  "
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "state = 'Maine' ## \n",
    "\n",
    "new_L = []\n",
    "for idx, row in Maine.iterrows(): ## \n",
    "    d = row.to_dict()\n",
    "    \n",
    "    # print(d)\n",
    "    \n",
    "    new_d = {}\n",
    "    \n",
    "    \n",
    "    new_d['State'] = state\n",
    "    new_d['County'] = ' '.join([i.lower().capitalize() for i in d['County'].split(' ')])\n",
    "    new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "    \n",
    "    new_d['Numb_White'] = d['White'] if d['White']!= '-' else np.nan\n",
    "    # new_d['Rate_White'] = np.nan\n",
    "    \n",
    "    new_d['Numb_Black'] = d['Black'] if d['Black']!= '-' else np.nan\n",
    "    # new_d['Rate_Black'] = np.nan\n",
    "    \n",
    "    \n",
    "    new_d['FullNumb_White'] = d['White-full'] if d['White-full']!= '-' else np.nan\n",
    "    # new_d['Rate_White'] = np.nan\n",
    "    \n",
    "    new_d['FullNumb_Black'] = d['Black-full'] if d['Black-full']!= '-' else np.nan\n",
    "    # new_d['Rate_Black'] = np.nan\n",
    "\n",
    "    \n",
    "    # new_d['Date'] = np.nan\n",
    "    \n",
    "    new_L.append(new_d)\n",
    "    \n",
    "    \n",
    "Data = pd.DataFrame(new_L)\n",
    "not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "\n",
    "T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "\n",
    "for Race in ['White', 'Black']:\n",
    "    Rate = T['Numb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['Rate_' + Race] = Rate\n",
    "    \n",
    "    Rate = T['FullNumb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['FullRate_' + Race] = Rate\n",
    "\n",
    "\n",
    "T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "integrated-order",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California', 'Tennessee', 'North Carolina', 'West Virginia', 'Maine']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FullRate_Black</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.232383</td>\n",
       "      <td>0.069406</td>\n",
       "      <td>0.096154</td>\n",
       "      <td>0.187682</td>\n",
       "      <td>0.245216</td>\n",
       "      <td>0.279520</td>\n",
       "      <td>0.346952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.400520</td>\n",
       "      <td>0.052156</td>\n",
       "      <td>0.335752</td>\n",
       "      <td>0.362237</td>\n",
       "      <td>0.380987</td>\n",
       "      <td>0.430221</td>\n",
       "      <td>0.490498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.303934</td>\n",
       "      <td>0.099100</td>\n",
       "      <td>0.134615</td>\n",
       "      <td>0.227781</td>\n",
       "      <td>0.331019</td>\n",
       "      <td>0.382503</td>\n",
       "      <td>0.465257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.408698</td>\n",
       "      <td>0.069694</td>\n",
       "      <td>0.293909</td>\n",
       "      <td>0.370552</td>\n",
       "      <td>0.395338</td>\n",
       "      <td>0.464380</td>\n",
       "      <td>0.525195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.104764</td>\n",
       "      <td>0.069486</td>\n",
       "      <td>-0.014566</td>\n",
       "      <td>0.072174</td>\n",
       "      <td>0.117208</td>\n",
       "      <td>0.141047</td>\n",
       "      <td>0.248012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.168137</td>\n",
       "      <td>0.063326</td>\n",
       "      <td>0.045754</td>\n",
       "      <td>0.139952</td>\n",
       "      <td>0.169023</td>\n",
       "      <td>0.215925</td>\n",
       "      <td>0.273114</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   16.0  0.232383  0.069406  0.096154  0.187682  0.245216   \n",
       "1   FullRate_White   16.0  0.400520  0.052156  0.335752  0.362237  0.380987   \n",
       "2       Rate_Black   16.0  0.303934  0.099100  0.134615  0.227781  0.331019   \n",
       "3       Rate_White   16.0  0.408698  0.069694  0.293909  0.370552  0.395338   \n",
       "4      Rate_Parity   16.0  0.104764  0.069486 -0.014566  0.072174  0.117208   \n",
       "5  FullRate_Parity   16.0  0.168137  0.063326  0.045754  0.139952  0.169023   \n",
       "\n",
       "        75%       max  \n",
       "0  0.279520  0.346952  \n",
       "1  0.430221  0.490498  \n",
       "2  0.382503  0.465257  \n",
       "3  0.464380  0.525195  \n",
       "4  0.141047  0.248012  \n",
       "5  0.215925  0.273114  "
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "T['FullRate_Parity']  = T['FullRate_White'] - T['FullRate_Black']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "interior-corporation",
   "metadata": {},
   "source": [
    "## [!]  E. New Jersey* (Done) XX\n",
    "\n",
    "Yes.\n",
    "\n",
    "White_Num = White_noHispanic_Num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "successful-medication",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'CountyVaccine/TMP/May20/NewJersey/New Jersey 5_20.xlsx'"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'CountyVaccine/TMP/May20/NewJersey/New Jersey 5_20.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "burning-switzerland",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter **New Jersey** Manually collected Data:  CountyVaccine/TMP/May20/NewJersey/New Jersey 5_20.xlsx\n",
      "File Exists!\n",
      "Enter \"Yes\" if the file contains the information of the date you wanted:  Yes\n",
      "\n",
      "The Path we have:\n",
      "CountyVaccine/TMP/May20/NewJersey/New Jersey 5_20.xlsx\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "\n",
    "Date = Today_Date\n",
    "\n",
    "\n",
    "while True:\n",
    "    Path = input('Enter **New Jersey** Manually collected Data:  ')\n",
    "    if os.path.isfile(Path):\n",
    "        print('File Exists!')\n",
    "        x = input('Enter \"Yes\" if the file contains the information of the date you wanted:  ')\n",
    "        if x =='Yes':\n",
    "            break\n",
    "    else:\n",
    "        print('Wrong file path...')\n",
    "        \n",
    "        \n",
    "print('\\nThe Path we have:')\n",
    "print(Path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "retained-friendly",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['State full', 'County', 'Black-full', 'White-full'], dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State full</th>\n",
       "      <th>County</th>\n",
       "      <th>Black-full</th>\n",
       "      <th>White-full</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>ATLANTIC</td>\n",
       "      <td>19667</td>\n",
       "      <td>135807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>BERGEN</td>\n",
       "      <td>28169</td>\n",
       "      <td>447742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>BURLINGTON</td>\n",
       "      <td>25252</td>\n",
       "      <td>281348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>CAMDEN</td>\n",
       "      <td>42832</td>\n",
       "      <td>273819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>CAPE MAY</td>\n",
       "      <td>1900</td>\n",
       "      <td>74425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   State full      County  Black-full  White-full\n",
       "0  New Jersey    ATLANTIC       19667      135807\n",
       "1  New Jersey      BERGEN       28169      447742\n",
       "2  New Jersey  BURLINGTON       25252      281348\n",
       "3  New Jersey      CAMDEN       42832      273819\n",
       "4  New Jersey    CAPE MAY        1900       74425"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    Data = pd.read_csv(Path)\n",
    "except:\n",
    "    Data = pd.read_excel(Path)\n",
    "    \n",
    "if len(Data.columns) == 6:\n",
    "    Data = Data[Data.columns[1:]]\n",
    "\n",
    "\n",
    "print(Data.columns)\n",
    "\n",
    "# Data.columns = ['State full', 'County', 'People with at least one vaccine dose','Black', 'White']\n",
    "NewJersey = Data\n",
    "\n",
    "# NewJersey.to_csv('FinalResult/NewJersey.csv')\n",
    "\n",
    "NewJersey.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "loved-fence",
   "metadata": {},
   "outputs": [],
   "source": [
    "# NewJersey[['Black', 'White'] ].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "anticipated-radar",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(21, 17)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Geographic Area Name</th>\n",
       "      <th>Date</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>FullRate_White</th>\n",
       "      <th>Total_AIndA</th>\n",
       "      <th>Total_AIndAC</th>\n",
       "      <th>Total_Asian</th>\n",
       "      <th>Total_AsianC</th>\n",
       "      <th>Total_Black</th>\n",
       "      <th>Total_BlackC</th>\n",
       "      <th>Total_White</th>\n",
       "      <th>Total_WhiteC</th>\n",
       "      <th>Total_Whole</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Atlantic</td>\n",
       "      <td>Atlantic, New Jersey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19667</td>\n",
       "      <td>135807</td>\n",
       "      <td>0.435294</td>\n",
       "      <td>0.722443</td>\n",
       "      <td>1782</td>\n",
       "      <td>3466</td>\n",
       "      <td>21282</td>\n",
       "      <td>23764</td>\n",
       "      <td>45181</td>\n",
       "      <td>49558</td>\n",
       "      <td>187983</td>\n",
       "      <td>194066</td>\n",
       "      <td>263670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Bergen</td>\n",
       "      <td>Bergen, New Jersey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28169</td>\n",
       "      <td>447742</td>\n",
       "      <td>0.411040</td>\n",
       "      <td>0.659121</td>\n",
       "      <td>4508</td>\n",
       "      <td>8451</td>\n",
       "      <td>158020</td>\n",
       "      <td>168160</td>\n",
       "      <td>68531</td>\n",
       "      <td>78683</td>\n",
       "      <td>679302</td>\n",
       "      <td>697546</td>\n",
       "      <td>932202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Burlington</td>\n",
       "      <td>Burlington, New Jersey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25252</td>\n",
       "      <td>281348</td>\n",
       "      <td>0.309783</td>\n",
       "      <td>0.867057</td>\n",
       "      <td>1520</td>\n",
       "      <td>4758</td>\n",
       "      <td>24118</td>\n",
       "      <td>28721</td>\n",
       "      <td>81515</td>\n",
       "      <td>90134</td>\n",
       "      <td>324486</td>\n",
       "      <td>335601</td>\n",
       "      <td>445349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Camden</td>\n",
       "      <td>Camden, New Jersey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>42832</td>\n",
       "      <td>273819</td>\n",
       "      <td>0.391010</td>\n",
       "      <td>0.783376</td>\n",
       "      <td>3304</td>\n",
       "      <td>6565</td>\n",
       "      <td>30391</td>\n",
       "      <td>34188</td>\n",
       "      <td>109542</td>\n",
       "      <td>118445</td>\n",
       "      <td>349537</td>\n",
       "      <td>360646</td>\n",
       "      <td>506471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>Cape May</td>\n",
       "      <td>Cape May, New Jersey</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1900</td>\n",
       "      <td>74425</td>\n",
       "      <td>0.426679</td>\n",
       "      <td>0.881322</td>\n",
       "      <td>336</td>\n",
       "      <td>723</td>\n",
       "      <td>943</td>\n",
       "      <td>1366</td>\n",
       "      <td>4453</td>\n",
       "      <td>5514</td>\n",
       "      <td>84447</td>\n",
       "      <td>86071</td>\n",
       "      <td>92039</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State      County    Geographic Area Name  Date  FullNumb_Black  \\\n",
       "0  New Jersey    Atlantic    Atlantic, New Jersey   NaN           19667   \n",
       "1  New Jersey      Bergen      Bergen, New Jersey   NaN           28169   \n",
       "2  New Jersey  Burlington  Burlington, New Jersey   NaN           25252   \n",
       "3  New Jersey      Camden      Camden, New Jersey   NaN           42832   \n",
       "4  New Jersey    Cape May    Cape May, New Jersey   NaN            1900   \n",
       "\n",
       "   FullNumb_White  FullRate_Black  FullRate_White  Total_AIndA  Total_AIndAC  \\\n",
       "0          135807        0.435294        0.722443         1782          3466   \n",
       "1          447742        0.411040        0.659121         4508          8451   \n",
       "2          281348        0.309783        0.867057         1520          4758   \n",
       "3          273819        0.391010        0.783376         3304          6565   \n",
       "4           74425        0.426679        0.881322          336           723   \n",
       "\n",
       "   Total_Asian  Total_AsianC  Total_Black  Total_BlackC  Total_White  \\\n",
       "0        21282         23764        45181         49558       187983   \n",
       "1       158020        168160        68531         78683       679302   \n",
       "2        24118         28721        81515         90134       324486   \n",
       "3        30391         34188       109542        118445       349537   \n",
       "4          943          1366         4453          5514        84447   \n",
       "\n",
       "   Total_WhiteC  Total_Whole  \n",
       "0        194066       263670  \n",
       "1        697546       932202  \n",
       "2        335601       445349  \n",
       "3        360646       506471  \n",
       "4         86071        92039  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "state = 'New Jersey' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "\n",
    "for idx, row in NewJersey.iterrows(): ## \n",
    "    d = row.to_dict()\n",
    "    \n",
    "    new_d = {}\n",
    "    \n",
    "    \n",
    "    new_d['State'] = state\n",
    "    new_d['County'] = ' '.join([i.lower().capitalize() for i in d['County'].split(' ')])\n",
    "    new_d['Geographic Area Name'] = new_d['County'] + ', ' + new_d['State']\n",
    "    \n",
    "    # new_d['Numb_White'] = d['People with at least one vaccine dose'] *  d['White'] / 100\n",
    "    # new_d['Numb_Black'] = d['People with at least one vaccine dose'] *  d['Black'] / 100\n",
    "    \n",
    "    new_d['FullNumb_White'] = d['White-full']\n",
    "    new_d['FullNumb_Black'] = d['Black-full']\n",
    "    \n",
    "    \n",
    "    # new_d['Numb_Asian'] = d['First Dose Total'] *  d['Asian %'] / 100ba wo\n",
    "    # new_d['Numb_Hispa'] = d['First Dose Total'] * d['Hispanic/Latinx %'] / 100\n",
    "    \n",
    "    new_d['Date'] = np.nan\n",
    "    \n",
    "    new_L.append(new_d)\n",
    "    \n",
    "    \n",
    "Data = pd.DataFrame(new_L)\n",
    "not_included = list(set(Data['Geographic Area Name'].unique()) - set(US_COUNTY_POPULATION['Geographic Area Name'].unique()))\n",
    "if len(not_included) > 0:\n",
    "    print('Not included Counties', not_included)\n",
    "\n",
    "\n",
    "T = pd.merge(Data, US_COUNTY_POPULATION[US_COUNTY_POPULATION.columns[3:]], on = 'Geographic Area Name')\n",
    "\n",
    "\n",
    "for Race in ['White', 'Black']:\n",
    "    Rate = T['FullNumb_' + Race] / T['Total_' + Race]\n",
    "    Rate = Rate.replace([-np.inf, np.inf], np.nan)\n",
    "    T['FullRate_' + Race] = Rate\n",
    "\n",
    "T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "\n",
    "\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "upset-convergence",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['California', 'Tennessee', 'North Carolina', 'West Virginia', 'Maine', 'New Jersey']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>FullRate_Black</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.423625</td>\n",
       "      <td>0.090209</td>\n",
       "      <td>0.259612</td>\n",
       "      <td>0.371882</td>\n",
       "      <td>0.418672</td>\n",
       "      <td>0.450308</td>\n",
       "      <td>0.654130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.684364</td>\n",
       "      <td>0.139186</td>\n",
       "      <td>0.360144</td>\n",
       "      <td>0.608698</td>\n",
       "      <td>0.703249</td>\n",
       "      <td>0.770550</td>\n",
       "      <td>0.881322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.260740</td>\n",
       "      <td>0.111197</td>\n",
       "      <td>0.049982</td>\n",
       "      <td>0.184595</td>\n",
       "      <td>0.260791</td>\n",
       "      <td>0.287149</td>\n",
       "      <td>0.557274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   21.0  0.423625  0.090209  0.259612  0.371882  0.418672   \n",
       "1   FullRate_White   21.0  0.684364  0.139186  0.360144  0.608698  0.703249   \n",
       "2  FullRate_Parity   21.0  0.260740  0.111197  0.049982  0.184595  0.260791   \n",
       "\n",
       "        75%       max  \n",
       "0  0.450308  0.654130  \n",
       "1  0.770550  0.881322  \n",
       "2  0.287149  0.557274  "
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['FullRate_Parity']  = T['FullRate_White'] - T['FullRate_Black']\n",
    "# T['Rate_ParityC'] = T['Rate_WhiteC'] - T['Rate_BlackC']\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "noble-advertising",
   "metadata": {},
   "source": [
    "# All in One"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "national-transcript",
   "metadata": {},
   "source": [
    "## Read Previous Vaccine Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "hairy-combat",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import os\n",
    "# path = ''\n",
    "\n",
    "# print('Input Historical STATE_VACCINE')\n",
    "# print('For example: RaceVaccineBackup/STATE_VACCINE_2021-03-23.p')\n",
    "\n",
    "# L = []\n",
    "# while True:\n",
    "    \n",
    "#     path = input()\n",
    "    \n",
    "#     if os.path.isfile(path):\n",
    "#         print('Path Exists!')\n",
    "#         L.append(path)\n",
    "#     elif path != '':\n",
    "#         print('Wrong Path')\n",
    "#     else:\n",
    "#         break\n",
    "\n",
    "# print('End')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "grave-performer",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pickle\n",
    "\n",
    "# Historical_Path = {i.split('_')[-1].replace('.p', ''): i for i in L }\n",
    "\n",
    "# Historical_Data = {}\n",
    "# for date, path in Historical_Path.items():\n",
    "#     with open(path, 'rb' ) as f:\n",
    "#         Historical_Data[date] = pickle.load(f)\n",
    "    \n",
    "    \n",
    "# [i for i in Historical_Data]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "quick-softball",
   "metadata": {},
   "source": [
    "## Show Parity for Each State"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "educated-medium",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ######################################\n",
    "\n",
    "# print(Today_Date)\n",
    "# ######################################\n",
    "\n",
    "# for state in STATE_VACCINE:\n",
    "    \n",
    "#     print(state)\n",
    "#     cols = ['name',  'date', 'count', 'mean', 'min','50%', 'max']\n",
    "\n",
    "    \n",
    "#     for date, Old_STATE_VACCINE in Historical_Data.items():\n",
    "#         x = diagnose_Rate(Old_STATE_VACCINE[state], state = state, date = date)\n",
    "#         print(x[cols])\n",
    "    \n",
    "#     x = diagnose_Rate(STATE_VACCINE[state], state = state, date = Today_Date)\n",
    "    \n",
    "#     print(x[cols])\n",
    "#     print('\\n')\n",
    "    \n",
    "    \n",
    "# # pd.concat(L).to_csv('compare.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "vertical-arnold",
   "metadata": {},
   "source": [
    "##  Show Some Specials Cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "cooperative-rogers",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # state = 'Texas'\n",
    "# DF = pd.concat([v for k,v in STATE_VACCINE.items()]) \n",
    "\n",
    "# data_cols = ['State', 'County', 'Geographic Area Name',\n",
    "#              'Total_Black', 'Total_White',\n",
    "#              'Numb_Black', 'Numb_White','Rate_Black','Rate_White', 'Total_Whole', 'Rate_Parity']\n",
    "\n",
    "# DF[DF['Rate_Black'] > 0.3][data_cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "skilled-equality",
   "metadata": {},
   "source": [
    "## Save Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "hundred-broadcast",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The data will be saved here, do you agree?\n",
      "CountyVaccine/Workplace/STATE_VACCINE_2021-05-20_Manual.p\n",
      "If yes, please enter: Yes\n",
      "Yes\n",
      "Successfully Saved!\n"
     ]
    }
   ],
   "source": [
    "\n",
    "import pickle \n",
    "\n",
    "path = 'CountyVaccine/Workplace/STATE_VACCINE_' + Today_Date + '_Manual.p'\n",
    "\n",
    "while True:\n",
    "    print('The data will be saved here, do you agree?' )\n",
    "    print(path)\n",
    "    print('If yes, please enter: Yes')\n",
    "    x = input()\n",
    "    if x =='Yes':\n",
    "        break\n",
    "\n",
    "\n",
    "with open(path, 'wb') as f:\n",
    "    pickle.dump(STATE_VACCINE, f)\n",
    "    \n",
    "    \n",
    "print('Successfully Saved!')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "relative-vessel",
   "metadata": {},
   "source": [
    "# Merge Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "encouraging-bleeding",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CountyVaccine/Workplace/STATE_VACCINE_2021-05-20_Manual.p\n",
      "CountyVaccine/Workplace/STATE_VACCINE_2021-05-20_Tableau.p\n",
      "CountyVaccine/Workplace/STATE_VACCINE_2021-05-20_Autonomy.p\n"
     ]
    }
   ],
   "source": [
    "def merge_data(date):\n",
    "    D = {}\n",
    "    for suffix in ['_Manual.p', '_Tableau.p', '_Autonomy.p']:\n",
    "        path = 'CountyVaccine/Workplace/STATE_VACCINE_' + date + suffix\n",
    "        print(path)\n",
    "        with open(path, 'rb') as f:\n",
    "            s = pickle.load(f)\n",
    "            \n",
    "        for k, v in s.items():\n",
    "            D[k] = v\n",
    "            \n",
    "    return D\n",
    "    \n",
    "date = Today_Date\n",
    "path = 'STATE_VACCINE_' + date + '.p'\n",
    "D = merge_data(date)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "level-worthy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save data to: STATE_VACCINE_2021-05-20.p\n"
     ]
    }
   ],
   "source": [
    "STATE_VACCINE = D\n",
    "\n",
    "with open(path, 'wb') as f:\n",
    "    pickle.dump(STATE_VACCINE, f)\n",
    "\n",
    "print('save data to:', path)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "solid-tampa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['California',\n",
       " 'Tennessee',\n",
       " 'North Carolina',\n",
       " 'West Virginia',\n",
       " 'Maine',\n",
       " 'New Jersey',\n",
       " 'New York',\n",
       " 'Ohio',\n",
       " 'Oregon',\n",
       " 'South Carolina',\n",
       " 'Wisconsin',\n",
       " 'Illinois',\n",
       " 'Texas',\n",
       " 'Pennsylvania',\n",
       " 'Indiana',\n",
       " 'Virginia']"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[i for i in STATE_VACCINE]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "verified-fifth",
   "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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}