1.CountyVaccine_Automation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "eight-humidity",
   "metadata": {},
   "source": [
    "# I. US County Race Population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "smoking-sauce",
   "metadata": {},
   "outputs": [],
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "common-attention",
   "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": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "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": 3,
   "id": "sunset-eligibility",
   "metadata": {},
   "outputs": [],
   "source": [
    "STATE_VACCINE = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "upset-birth",
   "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": "naughty-realtor",
   "metadata": {},
   "source": [
    "# II. API States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "boxed-final",
   "metadata": {},
   "source": [
    "## A. Illinois* (Done) XX\n",
    "\n",
    "\n",
    "https://www.dph.illinois.gov/covid19/vaccinedata?county=Illinois\n",
    "\n",
    "\n",
    "* Only Number\n",
    "\n",
    "\n",
    "* Admin-Count (People at least One)\n",
    "\n",
    "* Full-Count (Fully Covered)\n",
    "\n",
    "\n",
    "\n",
    "White_Num = White_noHis_Num\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "requested-disco",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime \n",
    "import numpy as np\n",
    "\n",
    "import requests\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "classical-scenario",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(102, 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>County</th>\n",
       "      <th>Hispanic or Latino-AdminCount</th>\n",
       "      <th>Hispanic or Latino-FullyVaccn</th>\n",
       "      <th>Asian-AdminCount</th>\n",
       "      <th>Asian-FullyVaccn</th>\n",
       "      <th>Other race-AdminCount</th>\n",
       "      <th>Other race-FullyVaccn</th>\n",
       "      <th>Black or African-American-AdminCount</th>\n",
       "      <th>Black or African-American-FullyVaccn</th>\n",
       "      <th>White-AdminCount</th>\n",
       "      <th>White-FullyVaccn</th>\n",
       "      <th>American Indian or Alaska Nati-AdminCount</th>\n",
       "      <th>American Indian or Alaska Nati-FullyVaccn</th>\n",
       "      <th>Native Hawaiian or Other Pacif-AdminCount</th>\n",
       "      <th>Native Hawaiian or Other Pacif-FullyVaccn</th>\n",
       "      <th>Unknown-AdminCount</th>\n",
       "      <th>Unknown-FullyVaccn</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams</td>\n",
       "      <td>533</td>\n",
       "      <td>288</td>\n",
       "      <td>376.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>153</td>\n",
       "      <td>88</td>\n",
       "      <td>1169</td>\n",
       "      <td>644</td>\n",
       "      <td>41784</td>\n",
       "      <td>22523</td>\n",
       "      <td>55.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>1040</td>\n",
       "      <td>548</td>\n",
       "      <td>2021-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alexander</td>\n",
       "      <td>9</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>511</td>\n",
       "      <td>236</td>\n",
       "      <td>894</td>\n",
       "      <td>423</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>102</td>\n",
       "      <td>50</td>\n",
       "      <td>2021-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bond</td>\n",
       "      <td>162</td>\n",
       "      <td>66</td>\n",
       "      <td>44.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>132</td>\n",
       "      <td>88</td>\n",
       "      <td>168</td>\n",
       "      <td>72</td>\n",
       "      <td>9149</td>\n",
       "      <td>4433</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>281</td>\n",
       "      <td>129</td>\n",
       "      <td>2021-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Boone</td>\n",
       "      <td>5531</td>\n",
       "      <td>2189</td>\n",
       "      <td>470.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>742</td>\n",
       "      <td>362</td>\n",
       "      <td>600</td>\n",
       "      <td>273</td>\n",
       "      <td>29523</td>\n",
       "      <td>13842</td>\n",
       "      <td>131.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1982</td>\n",
       "      <td>936</td>\n",
       "      <td>2021-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Brown</td>\n",
       "      <td>11</td>\n",
       "      <td>6</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>26</td>\n",
       "      <td>15</td>\n",
       "      <td>2774</td>\n",
       "      <td>1379</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68</td>\n",
       "      <td>33</td>\n",
       "      <td>2021-05-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      County  Hispanic or Latino-AdminCount  Hispanic or Latino-FullyVaccn  \\\n",
       "0      Adams                            533                            288   \n",
       "1  Alexander                              9                              4   \n",
       "2       Bond                            162                             66   \n",
       "3      Boone                           5531                           2189   \n",
       "4      Brown                             11                              6   \n",
       "\n",
       "   Asian-AdminCount  Asian-FullyVaccn  Other race-AdminCount  \\\n",
       "0             376.0             202.0                    153   \n",
       "1               NaN               NaN                      8   \n",
       "2              44.0              18.0                    132   \n",
       "3             470.0             218.0                    742   \n",
       "4               6.0               3.0                      1   \n",
       "\n",
       "   Other race-FullyVaccn  Black or African-American-AdminCount  \\\n",
       "0                     88                                  1169   \n",
       "1                      4                                   511   \n",
       "2                     88                                   168   \n",
       "3                    362                                   600   \n",
       "4                      1                                    26   \n",
       "\n",
       "   Black or African-American-FullyVaccn  White-AdminCount  White-FullyVaccn  \\\n",
       "0                                   644             41784             22523   \n",
       "1                                   236               894               423   \n",
       "2                                    72              9149              4433   \n",
       "3                                   273             29523             13842   \n",
       "4                                    15              2774              1379   \n",
       "\n",
       "   American Indian or Alaska Nati-AdminCount  \\\n",
       "0                                       55.0   \n",
       "1                                        5.0   \n",
       "2                                        3.0   \n",
       "3                                      131.0   \n",
       "4                                        6.0   \n",
       "\n",
       "   American Indian or Alaska Nati-FullyVaccn  \\\n",
       "0                                       28.0   \n",
       "1                                        1.0   \n",
       "2                                        1.0   \n",
       "3                                       63.0   \n",
       "4                                        3.0   \n",
       "\n",
       "   Native Hawaiian or Other Pacif-AdminCount  \\\n",
       "0                                       57.0   \n",
       "1                                        2.0   \n",
       "2                                       10.0   \n",
       "3                                       49.0   \n",
       "4                                        NaN   \n",
       "\n",
       "   Native Hawaiian or Other Pacif-FullyVaccn  Unknown-AdminCount  \\\n",
       "0                                       29.0                1040   \n",
       "1                                        1.0                 102   \n",
       "2                                        3.0                 281   \n",
       "3                                       23.0                1982   \n",
       "4                                        NaN                  68   \n",
       "\n",
       "   Unknown-FullyVaccn        Date  \n",
       "0                 548  2021-05-19  \n",
       "1                  50  2021-05-19  \n",
       "2                 129  2021-05-19  \n",
       "3                 936  2021-05-19  \n",
       "4                  33  2021-05-19  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "state = 'Illinois'\n",
    "\n",
    "\n",
    "def get_Illinois_Raw_and_postprocess(US_COUNTY_POPULATION):\n",
    "    \n",
    "\n",
    "    il_county_list = [i for i in US_COUNTY_POPULATION[US_COUNTY_POPULATION['State'] == 'Illinois']['County'].values]\n",
    "\n",
    "    il_county_list.sort()\n",
    "\n",
    "    L = []\n",
    "    error_list = []\n",
    "    for c in il_county_list:\n",
    "        # c = 'Mchenry'\n",
    "        api_url = 'https://idph.illinois.gov/DPHPublicInformation/api/covidvaccine/getVaccineAdministrationDemos?countyname={}'.format(c)\n",
    "\n",
    "        resp = requests.get(api_url)# .to_json()\n",
    "        data = resp.json() # resp\n",
    "        if sum([len(v) for k, v in data.items()]) == 0:\n",
    "            error_list.append(c)\n",
    "            print(c)\n",
    "            continue\n",
    "\n",
    "        time.sleep(0.1)\n",
    "        try:\n",
    "            D = {}\n",
    "            D['County'] = c\n",
    "            for d in data['Race']:\n",
    "                D[d['Race'] + '-AdminCount'] = d['AdministeredCount']\n",
    "                D[d['Race'] + '-FullyVaccn'] = d['PersonsFullyVaccinated']\n",
    "                date = d['Report_Date']\n",
    "\n",
    "            D['Date'] = date.split('T')[0]\n",
    "            L.append(D)\n",
    "\n",
    "        except:\n",
    "            print('No Data')\n",
    "            print(c)\n",
    "\n",
    "    IL_County_Vaccine_Race = pd.DataFrame(L)\n",
    "    return IL_County_Vaccine_Race\n",
    "\n",
    "\n",
    "IL_County_Vaccine_Race = get_Illinois_Raw_and_postprocess(US_COUNTY_POPULATION)\n",
    "# IL_County_Vaccine_Race.to_csv('FinalResult/Raw_Illinois.csv')\n",
    "print(IL_County_Vaccine_Race.shape)\n",
    "IL_County_Vaccine_Race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "multiple-karma",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'County': 'Adams',\n",
       " 'Hispanic or Latino-AdminCount': 533,\n",
       " 'Hispanic or Latino-FullyVaccn': 288,\n",
       " 'Asian-AdminCount': 376.0,\n",
       " 'Asian-FullyVaccn': 202.0,\n",
       " 'Other race-AdminCount': 153,\n",
       " 'Other race-FullyVaccn': 88,\n",
       " 'Black or African-American-AdminCount': 1169,\n",
       " 'Black or African-American-FullyVaccn': 644,\n",
       " 'White-AdminCount': 41784,\n",
       " 'White-FullyVaccn': 22523,\n",
       " 'American Indian or Alaska Nati-AdminCount': 55.0,\n",
       " 'American Indian or Alaska Nati-FullyVaccn': 28.0,\n",
       " 'Native Hawaiian or Other Pacif-AdminCount': 57.0,\n",
       " 'Native Hawaiian or Other Pacif-FullyVaccn': 29.0,\n",
       " 'Unknown-AdminCount': 1040,\n",
       " 'Unknown-FullyVaccn': 548,\n",
       " 'Date': '2021-05-19'}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "IL_County_Vaccine_Race.iloc[0].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "gorgeous-floor",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(102, 32)\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_Hispa</th>\n",
       "      <th>FullNumb_Other</th>\n",
       "      <th>FullNumb_White</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>Illinois</td>\n",
       "      <td>Adams</td>\n",
       "      <td>Adams, Illinois</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>28.0</td>\n",
       "      <td>202.0</td>\n",
       "      <td>644</td>\n",
       "      <td>288</td>\n",
       "      <td>88</td>\n",
       "      <td>22523</td>\n",
       "      <td>...</td>\n",
       "      <td>0.688392</td>\n",
       "      <td>190</td>\n",
       "      <td>395</td>\n",
       "      <td>615</td>\n",
       "      <td>830</td>\n",
       "      <td>2557</td>\n",
       "      <td>3554</td>\n",
       "      <td>60698</td>\n",
       "      <td>61991</td>\n",
       "      <td>65435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>Alexander</td>\n",
       "      <td>Alexander, Illinois</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>236</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>423</td>\n",
       "      <td>...</td>\n",
       "      <td>0.239037</td>\n",
       "      <td>26</td>\n",
       "      <td>62</td>\n",
       "      <td>17</td>\n",
       "      <td>36</td>\n",
       "      <td>1830</td>\n",
       "      <td>1917</td>\n",
       "      <td>3740</td>\n",
       "      <td>3874</td>\n",
       "      <td>5761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>Bond</td>\n",
       "      <td>Bond, Illinois</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>72</td>\n",
       "      <td>66</td>\n",
       "      <td>88</td>\n",
       "      <td>4433</td>\n",
       "      <td>...</td>\n",
       "      <td>0.615100</td>\n",
       "      <td>110</td>\n",
       "      <td>183</td>\n",
       "      <td>134</td>\n",
       "      <td>187</td>\n",
       "      <td>1041</td>\n",
       "      <td>1195</td>\n",
       "      <td>14874</td>\n",
       "      <td>15113</td>\n",
       "      <td>16426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>Boone</td>\n",
       "      <td>Boone, Illinois</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>63.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>273</td>\n",
       "      <td>2189</td>\n",
       "      <td>362</td>\n",
       "      <td>13842</td>\n",
       "      <td>...</td>\n",
       "      <td>0.596063</td>\n",
       "      <td>420</td>\n",
       "      <td>1006</td>\n",
       "      <td>785</td>\n",
       "      <td>1052</td>\n",
       "      <td>1503</td>\n",
       "      <td>1984</td>\n",
       "      <td>49530</td>\n",
       "      <td>50712</td>\n",
       "      <td>53544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>Brown</td>\n",
       "      <td>Brown, Illinois</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>15</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1379</td>\n",
       "      <td>...</td>\n",
       "      <td>0.537076</td>\n",
       "      <td>87</td>\n",
       "      <td>107</td>\n",
       "      <td>21</td>\n",
       "      <td>43</td>\n",
       "      <td>1242</td>\n",
       "      <td>1276</td>\n",
       "      <td>5165</td>\n",
       "      <td>5212</td>\n",
       "      <td>6578</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      State     County Geographic Area Name        Date  FullNumb_AIndA  \\\n",
       "0  Illinois      Adams      Adams, Illinois  2021-05-20            28.0   \n",
       "1  Illinois  Alexander  Alexander, Illinois  2021-05-20             1.0   \n",
       "2  Illinois       Bond       Bond, Illinois  2021-05-20             1.0   \n",
       "3  Illinois      Boone      Boone, Illinois  2021-05-20            63.0   \n",
       "4  Illinois      Brown      Brown, Illinois  2021-05-20             3.0   \n",
       "\n",
       "   FullNumb_Asian  FullNumb_Black  FullNumb_Hispa  FullNumb_Other  \\\n",
       "0           202.0             644             288              88   \n",
       "1             NaN             236               4               4   \n",
       "2            18.0              72              66              88   \n",
       "3           218.0             273            2189             362   \n",
       "4             3.0              15               6               1   \n",
       "\n",
       "   FullNumb_White  ...  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0           22523  ...    0.688392          190           395          615   \n",
       "1             423  ...    0.239037           26            62           17   \n",
       "2            4433  ...    0.615100          110           183          134   \n",
       "3           13842  ...    0.596063          420          1006          785   \n",
       "4            1379  ...    0.537076           87           107           21   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0           830         2557          3554        60698         61991   \n",
       "1            36         1830          1917         3740          3874   \n",
       "2           187         1041          1195        14874         15113   \n",
       "3          1052         1503          1984        49530         50712   \n",
       "4            43         1242          1276         5165          5212   \n",
       "\n",
       "   Total_Whole  \n",
       "0        65435  \n",
       "1         5761  \n",
       "2        16426  \n",
       "3        53544  \n",
       "4         6578  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "def standardize_Illinois(IL_County_Vaccine_Race, US_COUNTY_POPULATION):\n",
    "    \n",
    "    state = 'Illinois'\n",
    "\n",
    "    new_L = []\n",
    "\n",
    "\n",
    "    for idx, row in IL_County_Vaccine_Race.iterrows(): # change this\n",
    "        d = row.to_dict()\n",
    "\n",
    "        new_d = {}\n",
    "\n",
    "\n",
    "        # GEO Information\n",
    "        new_d['State'] = state\n",
    "        new_d['County'] = d['County']\n",
    "\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",
    "        # At Least One\n",
    "        new_d['Numb_White'] = d['White-AdminCount']\n",
    "        # new_d['Rate_White'] = np.nan\n",
    "\n",
    "        new_d['Numb_Black'] = d['Black or African-American-AdminCount']\n",
    "        # new_d['Rate_Black'] = np.nan\n",
    "\n",
    "        new_d['Numb_Asian'] = d['Asian-AdminCount']\n",
    "        # new_d['Rate_Asian'] = np.nan\n",
    "\n",
    "        new_d['Numb_AIndA'] = d['American Indian or Alaska Nati-AdminCount']\n",
    "        # new_d['Rate_AIndA'] = np.nan\n",
    "\n",
    "        new_d['Numb_Other'] = d['Other race-AdminCount']\n",
    "        \n",
    "       \n",
    "        \n",
    "        \n",
    "        \n",
    "        # Fully Vaccination\n",
    "        new_d['FullNumb_Hispa'] = d['Hispanic or Latino-FullyVaccn']\n",
    "        \n",
    "        new_d['FullNumb_White'] = d['White-FullyVaccn']\n",
    "        # new_d['Rate_White'] = np.nan\n",
    "\n",
    "        new_d['FullNumb_Black'] = d['Black or African-American-FullyVaccn']\n",
    "        # new_d['Rate_Black'] = np.nan\n",
    "\n",
    "        new_d['FullNumb_Asian'] = d['Asian-FullyVaccn']\n",
    "        # new_d['Rate_Asian'] = np.nan\n",
    "\n",
    "        new_d['FullNumb_AIndA'] = d['American Indian or Alaska Nati-FullyVaccn']\n",
    "        # new_d['Rate_AIndA'] = np.nan\n",
    "\n",
    "        new_d['FullNumb_Other'] = d['Other race-FullyVaccn']\n",
    "        \n",
    "        new_d['FullNumb_Hispa'] = d['Hispanic or Latino-FullyVaccn']\n",
    "        \n",
    "        \n",
    "        \n",
    "\n",
    "        new_d['Date'] = str(datetime.now().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",
    "    for Race in ['White', 'Black', 'Asian', 'AIndA']:\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",
    "    return T\n",
    "        \n",
    "        \n",
    "\n",
    "\n",
    "T = standardize_Illinois(IL_County_Vaccine_Race, US_COUNTY_POPULATION)\n",
    "# T.to_csv('FinalResult/T_' + state +'.csv', index = False)\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "greek-voluntary",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois']\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>102.0</td>\n",
       "      <td>0.130924</td>\n",
       "      <td>0.070560</td>\n",
       "      <td>0.011294</td>\n",
       "      <td>0.077300</td>\n",
       "      <td>0.131942</td>\n",
       "      <td>0.178640</td>\n",
       "      <td>0.472973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.298056</td>\n",
       "      <td>0.055363</td>\n",
       "      <td>0.113102</td>\n",
       "      <td>0.262241</td>\n",
       "      <td>0.301118</td>\n",
       "      <td>0.341900</td>\n",
       "      <td>0.445308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.282069</td>\n",
       "      <td>0.151872</td>\n",
       "      <td>0.018480</td>\n",
       "      <td>0.174668</td>\n",
       "      <td>0.280767</td>\n",
       "      <td>0.371927</td>\n",
       "      <td>1.013514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.609431</td>\n",
       "      <td>0.117727</td>\n",
       "      <td>0.239037</td>\n",
       "      <td>0.521963</td>\n",
       "      <td>0.619903</td>\n",
       "      <td>0.692829</td>\n",
       "      <td>0.920960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.167132</td>\n",
       "      <td>0.074430</td>\n",
       "      <td>-0.166198</td>\n",
       "      <td>0.129831</td>\n",
       "      <td>0.169090</td>\n",
       "      <td>0.216881</td>\n",
       "      <td>0.322707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.327363</td>\n",
       "      <td>0.154183</td>\n",
       "      <td>-0.380264</td>\n",
       "      <td>0.256996</td>\n",
       "      <td>0.336082</td>\n",
       "      <td>0.433222</td>\n",
       "      <td>0.626430</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black  102.0  0.130924  0.070560  0.011294  0.077300  0.131942   \n",
       "1   FullRate_White  102.0  0.298056  0.055363  0.113102  0.262241  0.301118   \n",
       "2       Rate_Black  102.0  0.282069  0.151872  0.018480  0.174668  0.280767   \n",
       "3       Rate_White  102.0  0.609431  0.117727  0.239037  0.521963  0.619903   \n",
       "4  FullRate_Parity  102.0  0.167132  0.074430 -0.166198  0.129831  0.169090   \n",
       "5      Rate_Parity  102.0  0.327363  0.154183 -0.380264  0.256996  0.336082   \n",
       "\n",
       "        75%       max  \n",
       "0  0.178640  0.472973  \n",
       "1  0.341900  0.445308  \n",
       "2  0.371927  1.013514  \n",
       "3  0.692829  0.920960  \n",
       "4  0.216881  0.322707  \n",
       "5  0.433222  0.626430  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "T['FullRate_Parity']  = T['FullRate_White'] - T['FullRate_Black']\n",
    "\n",
    "T['Rate_Parity']  = T['Rate_White'] - T['Rate_Black']\n",
    "\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sticky-fellowship",
   "metadata": {},
   "source": [
    "# III. Table States"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "authorized-empty",
   "metadata": {},
   "source": [
    "## B. Texas* (Done) XX\n",
    "\n",
    "download from: \n",
    "https://www.dshs.texas.gov/immunize/covid19/COVID-19-Vaccine-Data-by-County.xls\n",
    "\n",
    "\n",
    "which is from: https://www.dshs.texas.gov/coronavirus/immunize/vaccine.aspx\n",
    "\n",
    "\n",
    "Only Number, No Rate.\n",
    "\n",
    "\n",
    "Three conceptions:\n",
    "\n",
    "* Administered, \n",
    "\n",
    "* Vaccinated\n",
    "\n",
    "* Fully Vaccinated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "suited-gibraltar",
   "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 Name</th>\n",
       "      <th>Race/Ethnicity</th>\n",
       "      <th>People Vaccinated with at least One Dose</th>\n",
       "      <th>People Fully Vaccinated</th>\n",
       "      <th>Doses Administered</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>Asian</td>\n",
       "      <td>166</td>\n",
       "      <td>118</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>Black</td>\n",
       "      <td>3148</td>\n",
       "      <td>1041</td>\n",
       "      <td>3996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>1273</td>\n",
       "      <td>838</td>\n",
       "      <td>1988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>Other</td>\n",
       "      <td>1844</td>\n",
       "      <td>529</td>\n",
       "      <td>2321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1118</td>\n",
       "      <td>841</td>\n",
       "      <td>1913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1507</th>\n",
       "      <td>Other</td>\n",
       "      <td>Hispanic</td>\n",
       "      <td>41538</td>\n",
       "      <td>23896</td>\n",
       "      <td>62068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1508</th>\n",
       "      <td>Other</td>\n",
       "      <td>Other</td>\n",
       "      <td>12352</td>\n",
       "      <td>7935</td>\n",
       "      <td>19040</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1509</th>\n",
       "      <td>Other</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>14137</td>\n",
       "      <td>9046</td>\n",
       "      <td>21958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1510</th>\n",
       "      <td>Other</td>\n",
       "      <td>White</td>\n",
       "      <td>65941</td>\n",
       "      <td>42091</td>\n",
       "      <td>101985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1511</th>\n",
       "      <td>Grand Total</td>\n",
       "      <td>Total</td>\n",
       "      <td>12101315</td>\n",
       "      <td>9621201</td>\n",
       "      <td>20952077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1512 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      County Name Race/Ethnicity  People Vaccinated with at least One Dose  \\\n",
       "0        Anderson          Asian                                       166   \n",
       "1        Anderson          Black                                      3148   \n",
       "2        Anderson       Hispanic                                      1273   \n",
       "3        Anderson          Other                                      1844   \n",
       "4        Anderson        Unknown                                      1118   \n",
       "...           ...            ...                                       ...   \n",
       "1507        Other       Hispanic                                     41538   \n",
       "1508        Other          Other                                     12352   \n",
       "1509        Other        Unknown                                     14137   \n",
       "1510        Other          White                                     65941   \n",
       "1511  Grand Total          Total                                  12101315   \n",
       "\n",
       "      People Fully Vaccinated  Doses Administered  \n",
       "0                         118                 272  \n",
       "1                        1041                3996  \n",
       "2                         838                1988  \n",
       "3                         529                2321  \n",
       "4                         841                1913  \n",
       "...                       ...                 ...  \n",
       "1507                    23896               62068  \n",
       "1508                     7935               19040  \n",
       "1509                     9046               21958  \n",
       "1510                    42091              101985  \n",
       "1511                  9621201            20952077  \n",
       "\n",
       "[1512 rows x 5 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_Texas_Raw():\n",
    "    TEXAS_URL = 'https://www.dshs.texas.gov/immunize/covid19/COVID-19-Vaccine-Data-by-County.xls'\n",
    "    RawData = pd.read_excel(TEXAS_URL, sheet_name='By County, Race')\n",
    "    return RawData\n",
    "\n",
    "\n",
    "RawData = get_Texas_Raw()\n",
    "RawData.columns = [i.strip() for i in RawData.columns]\n",
    "RawData"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "stable-czech",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(256, 22)\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_DoesesAdmin</th>\n",
       "      <th>Asian_Vaccinated</th>\n",
       "      <th>Asian_FullVacc</th>\n",
       "      <th>Black_DoesesAdmin</th>\n",
       "      <th>Black_Vaccinated</th>\n",
       "      <th>Black_FullVacc</th>\n",
       "      <th>Hispanic_DoesesAdmin</th>\n",
       "      <th>Hispanic_Vaccinated</th>\n",
       "      <th>Hispanic_FullVacc</th>\n",
       "      <th>...</th>\n",
       "      <th>Other_FullVacc</th>\n",
       "      <th>Unknown_DoesesAdmin</th>\n",
       "      <th>Unknown_Vaccinated</th>\n",
       "      <th>Unknown_FullVacc</th>\n",
       "      <th>White_DoesesAdmin</th>\n",
       "      <th>White_Vaccinated</th>\n",
       "      <th>White_FullVacc</th>\n",
       "      <th>Total_DoesesAdmin</th>\n",
       "      <th>Total_Vaccinated</th>\n",
       "      <th>Total_FullVacc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Anderson</td>\n",
       "      <td>272.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>118.0</td>\n",
       "      <td>3996.0</td>\n",
       "      <td>3148.0</td>\n",
       "      <td>1041.0</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>1273.0</td>\n",
       "      <td>838.0</td>\n",
       "      <td>...</td>\n",
       "      <td>529.0</td>\n",
       "      <td>1913.0</td>\n",
       "      <td>1118.0</td>\n",
       "      <td>841.0</td>\n",
       "      <td>12994.0</td>\n",
       "      <td>8405.0</td>\n",
       "      <td>5685.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andrews</td>\n",
       "      <td>52.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>4443.0</td>\n",
       "      <td>2415.0</td>\n",
       "      <td>2039.0</td>\n",
       "      <td>...</td>\n",
       "      <td>103.0</td>\n",
       "      <td>2252.0</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>1120.0</td>\n",
       "      <td>3880.0</td>\n",
       "      <td>2094.0</td>\n",
       "      <td>1824.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Angelina</td>\n",
       "      <td>542.0</td>\n",
       "      <td>307.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>5491.0</td>\n",
       "      <td>3131.0</td>\n",
       "      <td>2580.0</td>\n",
       "      <td>7981.0</td>\n",
       "      <td>4532.0</td>\n",
       "      <td>3698.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1140.0</td>\n",
       "      <td>7422.0</td>\n",
       "      <td>4008.0</td>\n",
       "      <td>3519.0</td>\n",
       "      <td>22980.0</td>\n",
       "      <td>12906.0</td>\n",
       "      <td>11103.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Aransas</td>\n",
       "      <td>353.0</td>\n",
       "      <td>205.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>108.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2885.0</td>\n",
       "      <td>1670.0</td>\n",
       "      <td>1351.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>2328.0</td>\n",
       "      <td>1257.0</td>\n",
       "      <td>1086.0</td>\n",
       "      <td>11843.0</td>\n",
       "      <td>6749.0</td>\n",
       "      <td>5542.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Archer</td>\n",
       "      <td>17.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>137.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>...</td>\n",
       "      <td>463.0</td>\n",
       "      <td>1145.0</td>\n",
       "      <td>602.0</td>\n",
       "      <td>554.0</td>\n",
       "      <td>3231.0</td>\n",
       "      <td>1807.0</td>\n",
       "      <td>1665.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     County  Asian_DoesesAdmin  Asian_Vaccinated  Asian_FullVacc  \\\n",
       "0  Anderson              272.0             166.0           118.0   \n",
       "1   Andrews               52.0              31.0            21.0   \n",
       "2  Angelina              542.0             307.0           246.0   \n",
       "3   Aransas              353.0             205.0           156.0   \n",
       "4    Archer               17.0               9.0             9.0   \n",
       "\n",
       "   Black_DoesesAdmin  Black_Vaccinated  Black_FullVacc  Hispanic_DoesesAdmin  \\\n",
       "0             3996.0            3148.0          1041.0                1988.0   \n",
       "1               79.0              43.0            37.0                4443.0   \n",
       "2             5491.0            3131.0          2580.0                7981.0   \n",
       "3              108.0              65.0            46.0                2885.0   \n",
       "4               27.0              19.0            14.0                 225.0   \n",
       "\n",
       "   Hispanic_Vaccinated  Hispanic_FullVacc  ...  Other_FullVacc  \\\n",
       "0               1273.0              838.0  ...           529.0   \n",
       "1               2415.0             2039.0  ...           103.0   \n",
       "2               4532.0             3698.0  ...          1140.0   \n",
       "3               1670.0             1351.0  ...          1027.0   \n",
       "4                137.0              112.0  ...           463.0   \n",
       "\n",
       "   Unknown_DoesesAdmin  Unknown_Vaccinated  Unknown_FullVacc  \\\n",
       "0               1913.0              1118.0             841.0   \n",
       "1               2252.0              1274.0            1120.0   \n",
       "2               7422.0              4008.0            3519.0   \n",
       "3               2328.0              1257.0            1086.0   \n",
       "4               1145.0               602.0             554.0   \n",
       "\n",
       "   White_DoesesAdmin  White_Vaccinated  White_FullVacc  Total_DoesesAdmin  \\\n",
       "0            12994.0            8405.0          5685.0                NaN   \n",
       "1             3880.0            2094.0          1824.0                NaN   \n",
       "2            22980.0           12906.0         11103.0                NaN   \n",
       "3            11843.0            6749.0          5542.0                NaN   \n",
       "4             3231.0            1807.0          1665.0                NaN   \n",
       "\n",
       "   Total_Vaccinated  Total_FullVacc  \n",
       "0               NaN             NaN  \n",
       "1               NaN             NaN  \n",
       "2               NaN             NaN  \n",
       "3               NaN             NaN  \n",
       "4               NaN             NaN  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def postprocess_Texas_Raw(RawData):\n",
    "    RawDataGroups = RawData.groupby('County Name')\n",
    "    L = []\n",
    "    for county, record in RawDataGroups:\n",
    "        D = {}\n",
    "\n",
    "        D['County'] = county\n",
    "\n",
    "        for idx, row in record.iterrows():\n",
    "            d = row.to_dict()\n",
    "            # print(d)\n",
    "            race = d['Race/Ethnicity'] \n",
    "\n",
    "            D[race+'_DoesesAdmin'] = d['Doses Administered']\n",
    "            D[race+'_Vaccinated']  = d['People Vaccinated with at least One Dose']\n",
    "            D[race+'_FullVacc']     = d['People Fully Vaccinated']\n",
    "        L.append(D)\n",
    "\n",
    "\n",
    "    Texas_County_Vaccine_Race = pd.DataFrame(L)\n",
    "    \n",
    "    return Texas_County_Vaccine_Race\n",
    "\n",
    "\n",
    "Texas_County_Vaccine_Race = postprocess_Texas_Raw(RawData)\n",
    "# Texas_County_Vaccine_Race.to_csv('FinalResult/Post_Texas.csv')\n",
    "print(Texas_County_Vaccine_Race.shape)\n",
    "Texas_County_Vaccine_Race.head() # 254 counties + 1 total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "cubic-vacation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['County', 'Asian_DoesesAdmin', 'Asian_Vaccinated', 'Asian_FullVacc',\n",
       "       'Black_DoesesAdmin', 'Black_Vaccinated', 'Black_FullVacc',\n",
       "       'Hispanic_DoesesAdmin', 'Hispanic_Vaccinated', 'Hispanic_FullVacc',\n",
       "       'Other_DoesesAdmin', 'Other_Vaccinated', 'Other_FullVacc',\n",
       "       'Unknown_DoesesAdmin', 'Unknown_Vaccinated', 'Unknown_FullVacc',\n",
       "       'White_DoesesAdmin', 'White_Vaccinated', 'White_FullVacc',\n",
       "       'Total_DoesesAdmin', 'Total_Vaccinated', 'Total_FullVacc'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Texas_County_Vaccine_Race.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "enclosed-apollo",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Other, Texas', 'Grand Total, Texas']\n",
      "(254, 29)\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_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_Hispa</th>\n",
       "      <th>FullNumb_Other</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</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>Texas</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>Anderson, Texas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>118.0</td>\n",
       "      <td>1041.0</td>\n",
       "      <td>838.0</td>\n",
       "      <td>529.0</td>\n",
       "      <td>5685.0</td>\n",
       "      <td>0.219331</td>\n",
       "      <td>...</td>\n",
       "      <td>0.195306</td>\n",
       "      <td>428</td>\n",
       "      <td>747</td>\n",
       "      <td>538</td>\n",
       "      <td>757</td>\n",
       "      <td>12628</td>\n",
       "      <td>13200</td>\n",
       "      <td>43035</td>\n",
       "      <td>43927</td>\n",
       "      <td>57735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Andrews</td>\n",
       "      <td>Andrews, Texas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>21.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2039.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>1824.0</td>\n",
       "      <td>0.161538</td>\n",
       "      <td>...</td>\n",
       "      <td>0.118701</td>\n",
       "      <td>274</td>\n",
       "      <td>392</td>\n",
       "      <td>130</td>\n",
       "      <td>171</td>\n",
       "      <td>382</td>\n",
       "      <td>502</td>\n",
       "      <td>17641</td>\n",
       "      <td>17900</td>\n",
       "      <td>18705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Angelina</td>\n",
       "      <td>Angelina, Texas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>246.0</td>\n",
       "      <td>2580.0</td>\n",
       "      <td>3698.0</td>\n",
       "      <td>1140.0</td>\n",
       "      <td>11103.0</td>\n",
       "      <td>0.266522</td>\n",
       "      <td>...</td>\n",
       "      <td>0.183778</td>\n",
       "      <td>665</td>\n",
       "      <td>1040</td>\n",
       "      <td>923</td>\n",
       "      <td>1166</td>\n",
       "      <td>13595</td>\n",
       "      <td>14256</td>\n",
       "      <td>70226</td>\n",
       "      <td>71385</td>\n",
       "      <td>86715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Aransas</td>\n",
       "      <td>Aransas, Texas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>156.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>1351.0</td>\n",
       "      <td>1027.0</td>\n",
       "      <td>5542.0</td>\n",
       "      <td>0.317719</td>\n",
       "      <td>...</td>\n",
       "      <td>0.309006</td>\n",
       "      <td>308</td>\n",
       "      <td>542</td>\n",
       "      <td>491</td>\n",
       "      <td>564</td>\n",
       "      <td>406</td>\n",
       "      <td>554</td>\n",
       "      <td>21841</td>\n",
       "      <td>22252</td>\n",
       "      <td>23510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Texas</td>\n",
       "      <td>Archer</td>\n",
       "      <td>Archer, Texas</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>9.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>463.0</td>\n",
       "      <td>1665.0</td>\n",
       "      <td>0.219512</td>\n",
       "      <td>...</td>\n",
       "      <td>0.222045</td>\n",
       "      <td>131</td>\n",
       "      <td>200</td>\n",
       "      <td>41</td>\n",
       "      <td>66</td>\n",
       "      <td>96</td>\n",
       "      <td>153</td>\n",
       "      <td>8138</td>\n",
       "      <td>8264</td>\n",
       "      <td>8553</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   State    County Geographic Area Name        Date  FullNumb_Asian  \\\n",
       "0  Texas  Anderson      Anderson, Texas  2021-05-20           118.0   \n",
       "1  Texas   Andrews       Andrews, Texas  2021-05-20            21.0   \n",
       "2  Texas  Angelina      Angelina, Texas  2021-05-20           246.0   \n",
       "3  Texas   Aransas       Aransas, Texas  2021-05-20           156.0   \n",
       "4  Texas    Archer        Archer, Texas  2021-05-20             9.0   \n",
       "\n",
       "   FullNumb_Black  FullNumb_Hispa  FullNumb_Other  FullNumb_White  \\\n",
       "0          1041.0           838.0           529.0          5685.0   \n",
       "1            37.0          2039.0           103.0          1824.0   \n",
       "2          2580.0          3698.0          1140.0         11103.0   \n",
       "3            46.0          1351.0          1027.0          5542.0   \n",
       "4            14.0           112.0           463.0          1665.0   \n",
       "\n",
       "   FullRate_Asian  ...  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0        0.219331  ...    0.195306          428           747          538   \n",
       "1        0.161538  ...    0.118701          274           392          130   \n",
       "2        0.266522  ...    0.183778          665          1040          923   \n",
       "3        0.317719  ...    0.309006          308           542          491   \n",
       "4        0.219512  ...    0.222045          131           200           41   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0           757        12628         13200        43035         43927   \n",
       "1           171          382           502        17641         17900   \n",
       "2          1166        13595         14256        70226         71385   \n",
       "3           564          406           554        21841         22252   \n",
       "4            66           96           153         8138          8264   \n",
       "\n",
       "   Total_Whole  \n",
       "0        57735  \n",
       "1        18705  \n",
       "2        86715  \n",
       "3        23510  \n",
       "4         8553  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "def standardize_Texas(Texas_County_Vaccine_Race, US_COUNTY_POPULATION):\n",
    "    state = 'Texas'\n",
    "\n",
    "    new_L = []\n",
    "\n",
    "\n",
    "    for idx, row in Texas_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",
    "        new_d['Numb_White'] = d['White_Vaccinated']\n",
    "        \n",
    "        new_d['Numb_Black'] = d['Black_Vaccinated']\n",
    "        \n",
    "        new_d['Numb_Asian'] = d['Asian_Vaccinated']\n",
    "        \n",
    "        new_d['Numb_Other'] = d['Other_Vaccinated']\n",
    "        \n",
    "        new_d['Numb_Hispa'] = d['Hispanic_Vaccinated']\n",
    "        \n",
    "        \n",
    "        new_d['FullNumb_White'] = d['White_FullVacc']\n",
    "        \n",
    "        new_d['FullNumb_Black'] = d['Black_FullVacc']\n",
    "        \n",
    "        new_d['FullNumb_Asian'] = d['Asian_FullVacc']\n",
    "        \n",
    "        new_d['FullNumb_Other'] = d['Other_FullVacc']\n",
    "        \n",
    "        new_d['FullNumb_Hispa'] = d['Hispanic_FullVacc']\n",
    "        \n",
    "\n",
    "        new_d['Date'] = str(datetime.now().date())\n",
    "\n",
    "        new_L.append(new_d)\n",
    "\n",
    "\n",
    "    Data = pd.DataFrame(new_L)\n",
    "    # StateCountyVaccine[state] = Data\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",
    "\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",
    "        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",
    "    \n",
    "    return T\n",
    "\n",
    "\n",
    "\n",
    "T = standardize_Texas(Texas_County_Vaccine_Race, US_COUNTY_POPULATION)\n",
    "\n",
    "state = 'Texas'\n",
    "# T.to_csv('FinalResult/T_' + state +'.csv', index = False)\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "original-terrain",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois', 'Texas']\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>247.0</td>\n",
       "      <td>0.118574</td>\n",
       "      <td>0.065933</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.065640</td>\n",
       "      <td>0.113300</td>\n",
       "      <td>0.166311</td>\n",
       "      <td>0.281543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>254.0</td>\n",
       "      <td>0.148113</td>\n",
       "      <td>0.058892</td>\n",
       "      <td>0.018203</td>\n",
       "      <td>0.111210</td>\n",
       "      <td>0.150763</td>\n",
       "      <td>0.184150</td>\n",
       "      <td>0.320708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>247.0</td>\n",
       "      <td>0.165013</td>\n",
       "      <td>0.084993</td>\n",
       "      <td>0.014815</td>\n",
       "      <td>0.102421</td>\n",
       "      <td>0.164226</td>\n",
       "      <td>0.218315</td>\n",
       "      <td>0.697479</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>254.0</td>\n",
       "      <td>0.175577</td>\n",
       "      <td>0.066897</td>\n",
       "      <td>0.023512</td>\n",
       "      <td>0.131270</td>\n",
       "      <td>0.183936</td>\n",
       "      <td>0.216119</td>\n",
       "      <td>0.343335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>247.0</td>\n",
       "      <td>0.011505</td>\n",
       "      <td>0.087982</td>\n",
       "      <td>-0.660907</td>\n",
       "      <td>-0.037146</td>\n",
       "      <td>0.003101</td>\n",
       "      <td>0.052567</td>\n",
       "      <td>0.256978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>247.0</td>\n",
       "      <td>0.030255</td>\n",
       "      <td>0.063156</td>\n",
       "      <td>-0.112150</td>\n",
       "      <td>-0.013295</td>\n",
       "      <td>0.020850</td>\n",
       "      <td>0.065323</td>\n",
       "      <td>0.210501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black  247.0  0.118574  0.065933  0.000000  0.065640  0.113300   \n",
       "1   FullRate_White  254.0  0.148113  0.058892  0.018203  0.111210  0.150763   \n",
       "2       Rate_Black  247.0  0.165013  0.084993  0.014815  0.102421  0.164226   \n",
       "3       Rate_White  254.0  0.175577  0.066897  0.023512  0.131270  0.183936   \n",
       "4      Rate_Parity  247.0  0.011505  0.087982 -0.660907 -0.037146  0.003101   \n",
       "5  FullRate_Parity  247.0  0.030255  0.063156 -0.112150 -0.013295  0.020850   \n",
       "\n",
       "        75%       max  \n",
       "0  0.166311  0.281543  \n",
       "1  0.184150  0.320708  \n",
       "2  0.218315  0.697479  \n",
       "3  0.216119  0.343335  \n",
       "4  0.052567  0.256978  \n",
       "5  0.065323  0.210501  "
      ]
     },
     "execution_count": 15,
     "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": "sitting-world",
   "metadata": {},
   "source": [
    "## B. Pennsylvania* (Done)\n",
    "\n",
    "67 counties\n",
    "\n",
    "Ethnicity\n",
    "https://data.pa.gov/Health/COVID-19-Vaccinations-by-Ethnicity-Current-County-/7ruj-m7k6 \n",
    "\n",
    "Race\n",
    "https://data.pa.gov/Health/COVID-19-Vaccinations-by-Race-Current-County-Healt/x5z9-57ub\n",
    "\n",
    "\n",
    "Only Number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "entire-compression",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['County Name', 'Partially Covered African American',\n",
       "       'Partially Covered Asian', 'Partially Covered Native American',\n",
       "       'Partially Covered Pacific Islander',\n",
       "       'Partially Covered Multiple Other', 'Partially Covered White',\n",
       "       'Partially Covered  Unknown', 'Fully Covered African American',\n",
       "       'Fully Covered Asian', 'Fully Covered Native American',\n",
       "       'Fully Covered Pacific Islander', 'Fully Covered Multiple Other',\n",
       "       'Fully Covered White', 'Fully Covered Unknown', 'County',\n",
       "       'Partially Covered Hispanic', 'Partially Covered Not Hispanic',\n",
       "       'Partially Covered Unknown', 'Fully Covered Hispanic',\n",
       "       'Fully Covered Not Hispanic'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "Penn_Ethn_URL = 'https://data.pa.gov/api/views/7ruj-m7k6/rows.csv?accessType=DOWNLOAD'\n",
    "Penn_Race_URL = 'https://data.pa.gov/api/views/x5z9-57ub/rows.csv?accessType=DOWNLOAD'\n",
    "\n",
    "Ethn = pd.read_csv(Penn_Ethn_URL).rename(columns = {'Ethnicity':'Item', 'County Name': 'County'})\n",
    "Race = pd.read_csv(Penn_Race_URL).rename(columns = {'Race':'Item'})\n",
    "\n",
    "\n",
    "RawData = pd.concat([Race, Ethn])\n",
    "\n",
    "RawData.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "intended-wallpaper",
   "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 Name</th>\n",
       "      <th>Partially Covered African American</th>\n",
       "      <th>Partially Covered Asian</th>\n",
       "      <th>Partially Covered Native American</th>\n",
       "      <th>Partially Covered Pacific Islander</th>\n",
       "      <th>Partially Covered Multiple Other</th>\n",
       "      <th>Partially Covered White</th>\n",
       "      <th>Partially Covered  Unknown</th>\n",
       "      <th>Fully Covered African American</th>\n",
       "      <th>Fully Covered Asian</th>\n",
       "      <th>Fully Covered Native American</th>\n",
       "      <th>Fully Covered Pacific Islander</th>\n",
       "      <th>Fully Covered Multiple Other</th>\n",
       "      <th>Fully Covered White</th>\n",
       "      <th>Fully Covered Unknown</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BLAIR</td>\n",
       "      <td>131.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>567</td>\n",
       "      <td>6590</td>\n",
       "      <td>1272</td>\n",
       "      <td>215.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "      <td>3741</td>\n",
       "      <td>25534</td>\n",
       "      <td>6431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DAUPHIN</td>\n",
       "      <td>4678.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>3865</td>\n",
       "      <td>23819</td>\n",
       "      <td>6559</td>\n",
       "      <td>7856.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>9276</td>\n",
       "      <td>58212</td>\n",
       "      <td>8179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CENTRE</td>\n",
       "      <td>416.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>686</td>\n",
       "      <td>11499</td>\n",
       "      <td>3865</td>\n",
       "      <td>1625.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>2724</td>\n",
       "      <td>44190</td>\n",
       "      <td>11792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MONTGOMERY</td>\n",
       "      <td>13210.0</td>\n",
       "      <td>1062.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>530.0</td>\n",
       "      <td>10104</td>\n",
       "      <td>166008</td>\n",
       "      <td>29757</td>\n",
       "      <td>12819.0</td>\n",
       "      <td>2694.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>19342</td>\n",
       "      <td>167138</td>\n",
       "      <td>38225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LYCOMING</td>\n",
       "      <td>285.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>944</td>\n",
       "      <td>7679</td>\n",
       "      <td>1323</td>\n",
       "      <td>776.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>5613</td>\n",
       "      <td>25994</td>\n",
       "      <td>3056</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  County Name  Partially Covered African American  Partially Covered Asian  \\\n",
       "0       BLAIR                               131.0                     15.0   \n",
       "1     DAUPHIN                              4678.0                     78.0   \n",
       "2      CENTRE                               416.0                     49.0   \n",
       "3  MONTGOMERY                             13210.0                   1062.0   \n",
       "4    LYCOMING                               285.0                     12.0   \n",
       "\n",
       "   Partially Covered Native American  Partially Covered Pacific Islander  \\\n",
       "0                                NaN                                18.0   \n",
       "1                                5.0                               101.0   \n",
       "2                                8.0                                68.0   \n",
       "3                               47.0                               530.0   \n",
       "4                                NaN                                26.0   \n",
       "\n",
       "   Partially Covered Multiple Other  Partially Covered White  \\\n",
       "0                               567                     6590   \n",
       "1                              3865                    23819   \n",
       "2                               686                    11499   \n",
       "3                             10104                   166008   \n",
       "4                               944                     7679   \n",
       "\n",
       "   Partially Covered  Unknown  Fully Covered African American  \\\n",
       "0                        1272                           215.0   \n",
       "1                        6559                          7856.0   \n",
       "2                        3865                          1625.0   \n",
       "3                       29757                         12819.0   \n",
       "4                        1323                           776.0   \n",
       "\n",
       "   Fully Covered Asian  Fully Covered Native American  \\\n",
       "0                 18.0                            NaN   \n",
       "1                193.0                           16.0   \n",
       "2                198.0                           15.0   \n",
       "3               2694.0                          147.0   \n",
       "4                 14.0                            NaN   \n",
       "\n",
       "   Fully Covered Pacific Islander  Fully Covered Multiple Other  \\\n",
       "0                            24.0                          3741   \n",
       "1                           170.0                          9276   \n",
       "2                           204.0                          2724   \n",
       "3                           672.0                         19342   \n",
       "4                            25.0                          5613   \n",
       "\n",
       "   Fully Covered White  Fully Covered Unknown  \n",
       "0                25534                   6431  \n",
       "1                58212                   8179  \n",
       "2                44190                  11792  \n",
       "3               167138                  38225  \n",
       "4                25994                   3056  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Race.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "virtual-stamp",
   "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 Name</th>\n",
       "      <th>Partially Covered African American</th>\n",
       "      <th>Partially Covered Asian</th>\n",
       "      <th>Partially Covered Native American</th>\n",
       "      <th>Partially Covered Pacific Islander</th>\n",
       "      <th>Partially Covered Multiple Other</th>\n",
       "      <th>Partially Covered White</th>\n",
       "      <th>Partially Covered  Unknown</th>\n",
       "      <th>Fully Covered African American</th>\n",
       "      <th>Fully Covered Asian</th>\n",
       "      <th>Fully Covered Native American</th>\n",
       "      <th>Fully Covered Pacific Islander</th>\n",
       "      <th>Fully Covered Multiple Other</th>\n",
       "      <th>Fully Covered White</th>\n",
       "      <th>Fully Covered Unknown</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BLAIR</td>\n",
       "      <td>131.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.0</td>\n",
       "      <td>567</td>\n",
       "      <td>6590</td>\n",
       "      <td>1272</td>\n",
       "      <td>215.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "      <td>3741</td>\n",
       "      <td>25534</td>\n",
       "      <td>6431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DAUPHIN</td>\n",
       "      <td>4678.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>3865</td>\n",
       "      <td>23819</td>\n",
       "      <td>6559</td>\n",
       "      <td>7856.0</td>\n",
       "      <td>193.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>9276</td>\n",
       "      <td>58212</td>\n",
       "      <td>8179</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CENTRE</td>\n",
       "      <td>416.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>686</td>\n",
       "      <td>11499</td>\n",
       "      <td>3865</td>\n",
       "      <td>1625.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>2724</td>\n",
       "      <td>44190</td>\n",
       "      <td>11792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MONTGOMERY</td>\n",
       "      <td>13210.0</td>\n",
       "      <td>1062.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>530.0</td>\n",
       "      <td>10104</td>\n",
       "      <td>166008</td>\n",
       "      <td>29757</td>\n",
       "      <td>12819.0</td>\n",
       "      <td>2694.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>672.0</td>\n",
       "      <td>19342</td>\n",
       "      <td>167138</td>\n",
       "      <td>38225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LYCOMING</td>\n",
       "      <td>285.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>944</td>\n",
       "      <td>7679</td>\n",
       "      <td>1323</td>\n",
       "      <td>776.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>5613</td>\n",
       "      <td>25994</td>\n",
       "      <td>3056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>CLINTON</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>241</td>\n",
       "      <td>1934</td>\n",
       "      <td>326</td>\n",
       "      <td>35.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1185</td>\n",
       "      <td>8563</td>\n",
       "      <td>662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>NORTHUMBERLAND</td>\n",
       "      <td>79.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>359</td>\n",
       "      <td>5567</td>\n",
       "      <td>1010</td>\n",
       "      <td>707.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>1582</td>\n",
       "      <td>26323</td>\n",
       "      <td>2478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>SUSQUEHANNA</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>127</td>\n",
       "      <td>2320</td>\n",
       "      <td>236</td>\n",
       "      <td>28.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>502</td>\n",
       "      <td>8768</td>\n",
       "      <td>827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>LAWRENCE</td>\n",
       "      <td>155.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>440</td>\n",
       "      <td>4341</td>\n",
       "      <td>1001</td>\n",
       "      <td>422.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2395</td>\n",
       "      <td>19438</td>\n",
       "      <td>3800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>SCHUYLKILL</td>\n",
       "      <td>153.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>703</td>\n",
       "      <td>13791</td>\n",
       "      <td>2753</td>\n",
       "      <td>1020.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2447</td>\n",
       "      <td>41261</td>\n",
       "      <td>2981</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       County Name  Partially Covered African American  \\\n",
       "0            BLAIR                               131.0   \n",
       "1          DAUPHIN                              4678.0   \n",
       "2           CENTRE                               416.0   \n",
       "3       MONTGOMERY                             13210.0   \n",
       "4         LYCOMING                               285.0   \n",
       "..             ...                                 ...   \n",
       "63         CLINTON                                24.0   \n",
       "64  NORTHUMBERLAND                                79.0   \n",
       "65     SUSQUEHANNA                                15.0   \n",
       "66        LAWRENCE                               155.0   \n",
       "67      SCHUYLKILL                               153.0   \n",
       "\n",
       "    Partially Covered Asian  Partially Covered Native American  \\\n",
       "0                      15.0                                NaN   \n",
       "1                      78.0                                5.0   \n",
       "2                      49.0                                8.0   \n",
       "3                    1062.0                               47.0   \n",
       "4                      12.0                                NaN   \n",
       "..                      ...                                ...   \n",
       "63                      NaN                                NaN   \n",
       "64                      NaN                                NaN   \n",
       "65                      NaN                                NaN   \n",
       "66                      NaN                                NaN   \n",
       "67                      NaN                                NaN   \n",
       "\n",
       "    Partially Covered Pacific Islander  Partially Covered Multiple Other  \\\n",
       "0                                 18.0                               567   \n",
       "1                                101.0                              3865   \n",
       "2                                 68.0                               686   \n",
       "3                                530.0                             10104   \n",
       "4                                 26.0                               944   \n",
       "..                                 ...                               ...   \n",
       "63                                 NaN                               241   \n",
       "64                                11.0                               359   \n",
       "65                                 NaN                               127   \n",
       "66                                10.0                               440   \n",
       "67                                20.0                               703   \n",
       "\n",
       "    Partially Covered White  Partially Covered  Unknown  \\\n",
       "0                      6590                        1272   \n",
       "1                     23819                        6559   \n",
       "2                     11499                        3865   \n",
       "3                    166008                       29757   \n",
       "4                      7679                        1323   \n",
       "..                      ...                         ...   \n",
       "63                     1934                         326   \n",
       "64                     5567                        1010   \n",
       "65                     2320                         236   \n",
       "66                     4341                        1001   \n",
       "67                    13791                        2753   \n",
       "\n",
       "    Fully Covered African American  Fully Covered Asian  \\\n",
       "0                            215.0                 18.0   \n",
       "1                           7856.0                193.0   \n",
       "2                           1625.0                198.0   \n",
       "3                          12819.0               2694.0   \n",
       "4                            776.0                 14.0   \n",
       "..                             ...                  ...   \n",
       "63                            35.0                  NaN   \n",
       "64                           707.0                  6.0   \n",
       "65                            28.0                  6.0   \n",
       "66                           422.0                 10.0   \n",
       "67                          1020.0                 12.0   \n",
       "\n",
       "    Fully Covered Native American  Fully Covered Pacific Islander  \\\n",
       "0                             NaN                            24.0   \n",
       "1                            16.0                           170.0   \n",
       "2                            15.0                           204.0   \n",
       "3                           147.0                           672.0   \n",
       "4                             NaN                            25.0   \n",
       "..                            ...                             ...   \n",
       "63                            NaN                             NaN   \n",
       "64                            NaN                            16.0   \n",
       "65                            NaN                             5.0   \n",
       "66                            NaN                            16.0   \n",
       "67                            NaN                            22.0   \n",
       "\n",
       "    Fully Covered Multiple Other  Fully Covered White  Fully Covered Unknown  \n",
       "0                           3741                25534                   6431  \n",
       "1                           9276                58212                   8179  \n",
       "2                           2724                44190                  11792  \n",
       "3                          19342               167138                  38225  \n",
       "4                           5613                25994                   3056  \n",
       "..                           ...                  ...                    ...  \n",
       "63                          1185                 8563                    662  \n",
       "64                          1582                26323                   2478  \n",
       "65                           502                 8768                    827  \n",
       "66                          2395                19438                   3800  \n",
       "67                          2447                41261                   2981  \n",
       "\n",
       "[68 rows x 15 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "Pennsylvania_County_Vaccine_Race =  Race                         \n",
    "Pennsylvania_County_Vaccine_Race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "behind-attendance",
   "metadata": {},
   "outputs": [],
   "source": [
    "def standardize_Penn(Pennsylvania_County_Vaccine_Race, US_COUNTY_POPULATION):\n",
    "\n",
    "    state = 'Pennsylvania' ## \n",
    "\n",
    "    new_L = []\n",
    "\n",
    "    for idx, row in Pennsylvania_County_Vaccine_Race.iterrows(): ## \n",
    "        d = row.to_dict()\n",
    "\n",
    "        new_d = {}\n",
    "        new_d['State'] = state\n",
    "        new_d['County'] = d['County Name'].lower().capitalize()\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'] = d['Partially Covered White']                  + d['Fully Covered White']   \n",
    "        \n",
    "        new_d['Numb_Black'] = d['Partially Covered African American']       + d['Fully Covered African American']\n",
    "        \n",
    "        new_d['Numb_Asian'] = d['Partially Covered Asian']                  + d['Fully Covered Asian']                  \n",
    "         \n",
    "        new_d['FullNumb_White'] = d['Fully Covered White']   \n",
    "        \n",
    "        new_d['FullNumb_Black'] = d['Fully Covered African American']\n",
    "        \n",
    "        new_d['FullNumb_Asian'] = d['Fully Covered Asian']                  \n",
    "         \n",
    "\n",
    "\n",
    "        new_d['Date'] = str(datetime.now().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",
    "    print(T.columns)\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",
    "        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",
    "        \n",
    "    T = T[list(T.columns[:3]) + list(T.columns[3:].sort_values())]\n",
    "    \n",
    "    return Data, T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "stuffed-guide",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Out-of-state, Pennsylvania']\n",
      "Index(['State', 'County', 'Geographic Area Name', 'Numb_White', 'Numb_Black',\n",
      "       'Numb_Asian', 'FullNumb_White', 'FullNumb_Black', 'FullNumb_Asian',\n",
      "       'Date', 'Total_Whole', 'Total_White', 'Total_Black', 'Total_AIndA',\n",
      "       'Total_Asian', 'Total_WhiteC', 'Total_BlackC', 'Total_AIndAC',\n",
      "       'Total_AsianC'],\n",
      "      dtype='object')\n",
      "(67, 25)\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_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>FullRate_White</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>Pennsylvania</td>\n",
       "      <td>Blair</td>\n",
       "      <td>Blair, Pennsylvania</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>18.0</td>\n",
       "      <td>215.0</td>\n",
       "      <td>25534</td>\n",
       "      <td>0.021077</td>\n",
       "      <td>0.087327</td>\n",
       "      <td>0.219304</td>\n",
       "      <td>...</td>\n",
       "      <td>0.275904</td>\n",
       "      <td>197</td>\n",
       "      <td>571</td>\n",
       "      <td>854</td>\n",
       "      <td>1224</td>\n",
       "      <td>2462</td>\n",
       "      <td>3648</td>\n",
       "      <td>116432</td>\n",
       "      <td>118182</td>\n",
       "      <td>121829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Dauphin</td>\n",
       "      <td>Dauphin, Pennsylvania</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>193.0</td>\n",
       "      <td>7856.0</td>\n",
       "      <td>58212</td>\n",
       "      <td>0.013020</td>\n",
       "      <td>0.146943</td>\n",
       "      <td>0.292680</td>\n",
       "      <td>...</td>\n",
       "      <td>0.412438</td>\n",
       "      <td>1248</td>\n",
       "      <td>3027</td>\n",
       "      <td>14823</td>\n",
       "      <td>16481</td>\n",
       "      <td>53463</td>\n",
       "      <td>60954</td>\n",
       "      <td>198893</td>\n",
       "      <td>207697</td>\n",
       "      <td>278299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Centre</td>\n",
       "      <td>Centre, Pennsylvania</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>198.0</td>\n",
       "      <td>1625.0</td>\n",
       "      <td>44190</td>\n",
       "      <td>0.019017</td>\n",
       "      <td>0.263414</td>\n",
       "      <td>0.309692</td>\n",
       "      <td>...</td>\n",
       "      <td>0.390280</td>\n",
       "      <td>252</td>\n",
       "      <td>847</td>\n",
       "      <td>10412</td>\n",
       "      <td>11686</td>\n",
       "      <td>6169</td>\n",
       "      <td>7253</td>\n",
       "      <td>142690</td>\n",
       "      <td>145215</td>\n",
       "      <td>162385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Montgomery</td>\n",
       "      <td>Montgomery, Pennsylvania</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>2694.0</td>\n",
       "      <td>12819.0</td>\n",
       "      <td>167138</td>\n",
       "      <td>0.040038</td>\n",
       "      <td>0.154438</td>\n",
       "      <td>0.253489</td>\n",
       "      <td>...</td>\n",
       "      <td>0.505264</td>\n",
       "      <td>1977</td>\n",
       "      <td>5696</td>\n",
       "      <td>67286</td>\n",
       "      <td>73971</td>\n",
       "      <td>83004</td>\n",
       "      <td>94306</td>\n",
       "      <td>659350</td>\n",
       "      <td>676454</td>\n",
       "      <td>830915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>Lycoming</td>\n",
       "      <td>Lycoming, Pennsylvania</td>\n",
       "      <td>2021-05-20</td>\n",
       "      <td>14.0</td>\n",
       "      <td>776.0</td>\n",
       "      <td>25994</td>\n",
       "      <td>0.018373</td>\n",
       "      <td>0.142307</td>\n",
       "      <td>0.249451</td>\n",
       "      <td>...</td>\n",
       "      <td>0.323142</td>\n",
       "      <td>326</td>\n",
       "      <td>800</td>\n",
       "      <td>762</td>\n",
       "      <td>1099</td>\n",
       "      <td>5453</td>\n",
       "      <td>7301</td>\n",
       "      <td>104205</td>\n",
       "      <td>106608</td>\n",
       "      <td>113299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          State      County      Geographic Area Name        Date  \\\n",
       "0  Pennsylvania       Blair       Blair, Pennsylvania  2021-05-20   \n",
       "1  Pennsylvania     Dauphin     Dauphin, Pennsylvania  2021-05-20   \n",
       "2  Pennsylvania      Centre      Centre, Pennsylvania  2021-05-20   \n",
       "3  Pennsylvania  Montgomery  Montgomery, Pennsylvania  2021-05-20   \n",
       "4  Pennsylvania    Lycoming    Lycoming, Pennsylvania  2021-05-20   \n",
       "\n",
       "   FullNumb_Asian  FullNumb_Black  FullNumb_White  FullRate_Asian  \\\n",
       "0            18.0           215.0           25534        0.021077   \n",
       "1           193.0          7856.0           58212        0.013020   \n",
       "2           198.0          1625.0           44190        0.019017   \n",
       "3          2694.0         12819.0          167138        0.040038   \n",
       "4            14.0           776.0           25994        0.018373   \n",
       "\n",
       "   FullRate_Black  FullRate_White  ...  Rate_White  Total_AIndA  Total_AIndAC  \\\n",
       "0        0.087327        0.219304  ...    0.275904          197           571   \n",
       "1        0.146943        0.292680  ...    0.412438         1248          3027   \n",
       "2        0.263414        0.309692  ...    0.390280          252           847   \n",
       "3        0.154438        0.253489  ...    0.505264         1977          5696   \n",
       "4        0.142307        0.249451  ...    0.323142          326           800   \n",
       "\n",
       "   Total_Asian  Total_AsianC  Total_Black  Total_BlackC  Total_White  \\\n",
       "0          854          1224         2462          3648       116432   \n",
       "1        14823         16481        53463         60954       198893   \n",
       "2        10412         11686         6169          7253       142690   \n",
       "3        67286         73971        83004         94306       659350   \n",
       "4          762          1099         5453          7301       104205   \n",
       "\n",
       "   Total_WhiteC  Total_Whole  \n",
       "0        118182       121829  \n",
       "1        207697       278299  \n",
       "2        145215       162385  \n",
       "3        676454       830915  \n",
       "4        106608       113299  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Data, T = standardize_Penn(Pennsylvania_County_Vaccine_Race, US_COUNTY_POPULATION)\n",
    "state = 'Pennsylvania'\n",
    "# T.to_csv('FinalResult/T_' + state +'.csv', index = False)\n",
    "print(T.shape)\n",
    "T.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "interstate-newton",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois', 'Texas', 'Pennsylvania']\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>64.0</td>\n",
       "      <td>0.155059</td>\n",
       "      <td>0.094396</td>\n",
       "      <td>0.014620</td>\n",
       "      <td>0.099091</td>\n",
       "      <td>0.130514</td>\n",
       "      <td>0.181509</td>\n",
       "      <td>0.522936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.274974</td>\n",
       "      <td>0.067876</td>\n",
       "      <td>0.045139</td>\n",
       "      <td>0.230359</td>\n",
       "      <td>0.271743</td>\n",
       "      <td>0.310769</td>\n",
       "      <td>0.473257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.208662</td>\n",
       "      <td>0.089413</td>\n",
       "      <td>0.035088</td>\n",
       "      <td>0.152482</td>\n",
       "      <td>0.194572</td>\n",
       "      <td>0.263782</td>\n",
       "      <td>0.448995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0.360150</td>\n",
       "      <td>0.090239</td>\n",
       "      <td>0.066519</td>\n",
       "      <td>0.298920</td>\n",
       "      <td>0.359684</td>\n",
       "      <td>0.420880</td>\n",
       "      <td>0.547521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.149546</td>\n",
       "      <td>0.089310</td>\n",
       "      <td>-0.144379</td>\n",
       "      <td>0.109791</td>\n",
       "      <td>0.152998</td>\n",
       "      <td>0.196955</td>\n",
       "      <td>0.311353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>64.0</td>\n",
       "      <td>0.120361</td>\n",
       "      <td>0.087218</td>\n",
       "      <td>-0.196035</td>\n",
       "      <td>0.102430</td>\n",
       "      <td>0.132779</td>\n",
       "      <td>0.169651</td>\n",
       "      <td>0.261904</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   64.0  0.155059  0.094396  0.014620  0.099091  0.130514   \n",
       "1   FullRate_White   67.0  0.274974  0.067876  0.045139  0.230359  0.271743   \n",
       "2       Rate_Black   61.0  0.208662  0.089413  0.035088  0.152482  0.194572   \n",
       "3       Rate_White   67.0  0.360150  0.090239  0.066519  0.298920  0.359684   \n",
       "4      Rate_Parity   61.0  0.149546  0.089310 -0.144379  0.109791  0.152998   \n",
       "5  FullRate_Parity   64.0  0.120361  0.087218 -0.196035  0.102430  0.132779   \n",
       "\n",
       "        75%       max  \n",
       "0  0.181509  0.522936  \n",
       "1  0.310769  0.473257  \n",
       "2  0.263782  0.448995  \n",
       "3  0.420880  0.547521  \n",
       "4  0.196955  0.311353  \n",
       "5  0.169651  0.261904  "
      ]
     },
     "execution_count": 22,
     "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": "specialized-driving",
   "metadata": {},
   "source": [
    "## B. Indiana* (Done)\n",
    "\n",
    "https://hub.mph.in.gov/dataset/covid-19-vaccinations-demographics-by-county-and-district/resource/82d99020-093f-41ac-95c7-d3c335b8c2ba"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "perfect-sarah",
   "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>region</th>\n",
       "      <th>fips</th>\n",
       "      <th>item</th>\n",
       "      <th>first_dose_administered</th>\n",
       "      <th>second_dose_administered</th>\n",
       "      <th>single_dose_administered</th>\n",
       "      <th>all_doses_administered</th>\n",
       "      <th>fully_vaccinated</th>\n",
       "      <th>new_first_dose_administered</th>\n",
       "      <th>new_second_dose_administered</th>\n",
       "      <th>new_single_dose_administered</th>\n",
       "      <th>new_all_doses_administered</th>\n",
       "      <th>new_fully_vaccinated</th>\n",
       "      <th>current_as_of</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams</td>\n",
       "      <td>3</td>\n",
       "      <td>18001</td>\n",
       "      <td>Asian</td>\n",
       "      <td>48</td>\n",
       "      <td>43</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>91</td>\n",
       "      <td>43</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>5/20/2021 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adams</td>\n",
       "      <td>3</td>\n",
       "      <td>18001</td>\n",
       "      <td>Black or African American</td>\n",
       "      <td>21</td>\n",
       "      <td>17</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>42</td>\n",
       "      <td>21</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>5/20/2021 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adams</td>\n",
       "      <td>3</td>\n",
       "      <td>18001</td>\n",
       "      <td>Other Race</td>\n",
       "      <td>191</td>\n",
       "      <td>167</td>\n",
       "      <td>37</td>\n",
       "      <td>395</td>\n",
       "      <td>204</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>5</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>5/20/2021 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Adams</td>\n",
       "      <td>3</td>\n",
       "      <td>18001</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>173</td>\n",
       "      <td>138</td>\n",
       "      <td>162</td>\n",
       "      <td>473</td>\n",
       "      <td>300</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>Suppressed</td>\n",
       "      <td>5/20/2021 5:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Adams</td>\n",
       "      <td>3</td>\n",
       "      <td>18001</td>\n",
       "      <td>White</td>\n",
       "      <td>8315</td>\n",
       "      <td>7715</td>\n",
       "      <td>397</td>\n",
       "      <td>16427</td>\n",
       "      <td>8112</td>\n",
       "      <td>18</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>43</td>\n",
       "      <td>25</td>\n",
       "      <td>5/20/2021 5:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  county region   fips                       item first_dose_administered  \\\n",
       "0  Adams      3  18001                      Asian                      48   \n",
       "1  Adams      3  18001  Black or African American                      21   \n",
       "2  Adams      3  18001                 Other Race                     191   \n",
       "3  Adams      3  18001                    Unknown                     173   \n",
       "4  Adams      3  18001                      White                    8315   \n",
       "\n",
       "  second_dose_administered single_dose_administered all_doses_administered  \\\n",
       "0                       43               Suppressed                     91   \n",
       "1                       17               Suppressed                     42   \n",
       "2                      167                       37                    395   \n",
       "3                      138                      162                    473   \n",
       "4                     7715                      397                  16427   \n",
       "\n",
       "  fully_vaccinated new_first_dose_administered new_second_dose_administered  \\\n",
       "0               43                  Suppressed                   Suppressed   \n",
       "1               21                  Suppressed                   Suppressed   \n",
       "2              204                  Suppressed                   Suppressed   \n",
       "3              300                  Suppressed                   Suppressed   \n",
       "4             8112                          18                           20   \n",
       "\n",
       "  new_single_dose_administered new_all_doses_administered  \\\n",
       "0                   Suppressed                 Suppressed   \n",
       "1                   Suppressed                 Suppressed   \n",
       "2                   Suppressed                          5   \n",
       "3                   Suppressed                 Suppressed   \n",
       "4                            5                         43   \n",
       "\n",
       "  new_fully_vaccinated   current_as_of  \n",
       "0           Suppressed  5/20/2021 5:00  \n",
       "1           Suppressed  5/20/2021 5:00  \n",
       "2           Suppressed  5/20/2021 5:00  \n",
       "3           Suppressed  5/20/2021 5:00  \n",
       "4                   25  5/20/2021 5:00  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# path = 'TMP/Indiana_Mar26_county-vaccination-demographics.xlsx'\n",
    "# path = 'TMP/Indiana/Indiana_April6_county-vaccination-demographics.xlsx'\n",
    "\n",
    "url = 'https://hub.mph.in.gov/dataset/145a43b2-28e5-4bf1-ad86-123d07fddb55/resource/82d99020-093f-41ac-95c7-d3c335b8c2ba/download/county-vaccination-demographics.xlsx'\n",
    "\n",
    "RaceData = pd.read_excel(url, sheet_name = 'Race').rename(columns = {\"race\":\"item\"})\n",
    "EthnicityData = pd.read_excel(url, sheet_name = 'Ethnicity').rename(columns = {\"ethnicity\":\"item\"})\n",
    "Data = pd.concat([RaceData, EthnicityData])\n",
    "Data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "current-membrane",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Data.columns\n",
    "\n",
    "\n",
    "L= []\n",
    "\n",
    "for county, record in Data.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",
    "        race = d['item']\n",
    "        try:\n",
    "            D[race + '_' + 'AtLeastOneDose'] = float(d['all_doses_administered'])\n",
    "        except:\n",
    "            D[race + '_' + 'AtLeastOneDose'] = np.nan\n",
    "            \n",
    "        try:\n",
    "            D[race + '_' + 'FullNum'] = float(d['fully_vaccinated'])\n",
    "        except:\n",
    "            D[race + '_' + 'FullNum'] = np.nan\n",
    "    # print(D)\n",
    "    L.append(D)\n",
    "\n",
    "Indiana_County_Vaccine_Race = pd.DataFrame(L)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "behind-devices",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(94, 15)\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_AtLeastOneDose</th>\n",
       "      <th>Asian_FullNum</th>\n",
       "      <th>Black or African American_AtLeastOneDose</th>\n",
       "      <th>Black or African American_FullNum</th>\n",
       "      <th>Other Race_AtLeastOneDose</th>\n",
       "      <th>Other Race_FullNum</th>\n",
       "      <th>Unknown_AtLeastOneDose</th>\n",
       "      <th>Unknown_FullNum</th>\n",
       "      <th>White_AtLeastOneDose</th>\n",
       "      <th>White_FullNum</th>\n",
       "      <th>Hispanic or Latino_AtLeastOneDose</th>\n",
       "      <th>Hispanic or Latino_FullNum</th>\n",
       "      <th>Not Hispanic or Latino_AtLeastOneDose</th>\n",
       "      <th>Not Hispanic or Latino_FullNum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams</td>\n",
       "      <td>91.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>395.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>1118.0</td>\n",
       "      <td>630.0</td>\n",
       "      <td>16427.0</td>\n",
       "      <td>8112.0</td>\n",
       "      <td>485.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>15825.0</td>\n",
       "      <td>7812.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>10430.0</td>\n",
       "      <td>4686.0</td>\n",
       "      <td>17978.0</td>\n",
       "      <td>8409.0</td>\n",
       "      <td>13264.0</td>\n",
       "      <td>6096.0</td>\n",
       "      <td>26774.0</td>\n",
       "      <td>12774.0</td>\n",
       "      <td>210560.0</td>\n",
       "      <td>101525.0</td>\n",
       "      <td>9941.0</td>\n",
       "      <td>4459.0</td>\n",
       "      <td>226751.0</td>\n",
       "      <td>108560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bartholomew</td>\n",
       "      <td>5394.0</td>\n",
       "      <td>2539.0</td>\n",
       "      <td>1003.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>2680.0</td>\n",
       "      <td>1191.0</td>\n",
       "      <td>2356.0</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>56288.0</td>\n",
       "      <td>26895.0</td>\n",
       "      <td>2903.0</td>\n",
       "      <td>1253.0</td>\n",
       "      <td>62369.0</td>\n",
       "      <td>29738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Benton</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>4353.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>4243.0</td>\n",
       "      <td>1959.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Blackford</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>283.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>7258.0</td>\n",
       "      <td>3541.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>7189.0</td>\n",
       "      <td>3504.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        county  Asian_AtLeastOneDose  Asian_FullNum  \\\n",
       "0        Adams                  91.0           43.0   \n",
       "1        Allen               10430.0         4686.0   \n",
       "2  Bartholomew                5394.0         2539.0   \n",
       "3       Benton                  12.0            6.0   \n",
       "4    Blackford                  15.0            7.0   \n",
       "\n",
       "   Black or African American_AtLeastOneDose  \\\n",
       "0                                      42.0   \n",
       "1                                   17978.0   \n",
       "2                                    1003.0   \n",
       "3                                      24.0   \n",
       "4                                      11.0   \n",
       "\n",
       "   Black or African American_FullNum  Other Race_AtLeastOneDose  \\\n",
       "0                               21.0                      395.0   \n",
       "1                             8409.0                    13264.0   \n",
       "2                              469.0                     2680.0   \n",
       "3                               12.0                      146.0   \n",
       "4                                6.0                       85.0   \n",
       "\n",
       "   Other Race_FullNum  Unknown_AtLeastOneDose  Unknown_FullNum  \\\n",
       "0               204.0                  1118.0            630.0   \n",
       "1              6096.0                 26774.0          12774.0   \n",
       "2              1191.0                  2356.0           1110.0   \n",
       "3                85.0                   198.0             98.0   \n",
       "4                39.0                   283.0            141.0   \n",
       "\n",
       "   White_AtLeastOneDose  White_FullNum  Hispanic or Latino_AtLeastOneDose  \\\n",
       "0               16427.0         8112.0                              485.0   \n",
       "1              210560.0       101525.0                             9941.0   \n",
       "2               56288.0        26895.0                             2903.0   \n",
       "3                4353.0         2007.0                              217.0   \n",
       "4                7258.0         3541.0                               62.0   \n",
       "\n",
       "   Hispanic or Latino_FullNum  Not Hispanic or Latino_AtLeastOneDose  \\\n",
       "0                       238.0                                15825.0   \n",
       "1                      4459.0                               226751.0   \n",
       "2                      1253.0                                62369.0   \n",
       "3                       122.0                                 4243.0   \n",
       "4                        29.0                                 7189.0   \n",
       "\n",
       "   Not Hispanic or Latino_FullNum  \n",
       "0                          7812.0  \n",
       "1                        108560.0  \n",
       "2                         29738.0  \n",
       "3                          1959.0  \n",
       "4                          3504.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(Indiana_County_Vaccine_Race.shape)\n",
    "Indiana_County_Vaccine_Race.head() # 95 counties in Pennsylvania"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "married-motion",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(94, 15)\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_AtLeastOneDose</th>\n",
       "      <th>Asian_FullNum</th>\n",
       "      <th>Black or African American_AtLeastOneDose</th>\n",
       "      <th>Black or African American_FullNum</th>\n",
       "      <th>Other Race_AtLeastOneDose</th>\n",
       "      <th>Other Race_FullNum</th>\n",
       "      <th>Unknown_AtLeastOneDose</th>\n",
       "      <th>Unknown_FullNum</th>\n",
       "      <th>White_AtLeastOneDose</th>\n",
       "      <th>White_FullNum</th>\n",
       "      <th>Hispanic or Latino_AtLeastOneDose</th>\n",
       "      <th>Hispanic or Latino_FullNum</th>\n",
       "      <th>Not Hispanic or Latino_AtLeastOneDose</th>\n",
       "      <th>Not Hispanic or Latino_FullNum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Adams</td>\n",
       "      <td>91.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>395.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>1118.0</td>\n",
       "      <td>630.0</td>\n",
       "      <td>16427.0</td>\n",
       "      <td>8112.0</td>\n",
       "      <td>485.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>15825.0</td>\n",
       "      <td>7812.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Allen</td>\n",
       "      <td>10430.0</td>\n",
       "      <td>4686.0</td>\n",
       "      <td>17978.0</td>\n",
       "      <td>8409.0</td>\n",
       "      <td>13264.0</td>\n",
       "      <td>6096.0</td>\n",
       "      <td>26774.0</td>\n",
       "      <td>12774.0</td>\n",
       "      <td>210560.0</td>\n",
       "      <td>101525.0</td>\n",
       "      <td>9941.0</td>\n",
       "      <td>4459.0</td>\n",
       "      <td>226751.0</td>\n",
       "      <td>108560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bartholomew</td>\n",
       "      <td>5394.0</td>\n",
       "      <td>2539.0</td>\n",
       "      <td>1003.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>2680.0</td>\n",
       "      <td>1191.0</td>\n",
       "      <td>2356.0</td>\n",
       "      <td>1110.0</td>\n",
       "      <td>56288.0</td>\n",
       "      <td>26895.0</td>\n",
       "      <td>2903.0</td>\n",
       "      <td>1253.0</td>\n",
       "      <td>62369.0</td>\n",
       "      <td>29738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Benton</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>4353.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>217.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>4243.0</td>\n",
       "      <td>1959.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Blackford</td>\n",
       "      <td>15.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>283.0</td>\n",
       "      <td>141.0</td>\n",
       "      <td>7258.0</td>\n",
       "      <td>3541.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>7189.0</td>\n",
       "      <td>3504.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        county  Asian_AtLeastOneDose  Asian_FullNum  \\\n",
       "0        Adams                  91.0           43.0   \n",
       "1        Allen               10430.0         4686.0   \n",
       "2  Bartholomew                5394.0         2539.0   \n",
       "3       Benton                  12.0            6.0   \n",
       "4    Blackford                  15.0            7.0   \n",
       "\n",
       "   Black or African American_AtLeastOneDose  \\\n",
       "0                                      42.0   \n",
       "1                                   17978.0   \n",
       "2                                    1003.0   \n",
       "3                                      24.0   \n",
       "4                                      11.0   \n",
       "\n",
       "   Black or African American_FullNum  Other Race_AtLeastOneDose  \\\n",
       "0                               21.0                      395.0   \n",
       "1                             8409.0                    13264.0   \n",
       "2                              469.0                     2680.0   \n",
       "3                               12.0                      146.0   \n",
       "4                                6.0                       85.0   \n",
       "\n",
       "   Other Race_FullNum  Unknown_AtLeastOneDose  Unknown_FullNum  \\\n",
       "0               204.0                  1118.0            630.0   \n",
       "1              6096.0                 26774.0          12774.0   \n",
       "2              1191.0                  2356.0           1110.0   \n",
       "3                85.0                   198.0             98.0   \n",
       "4                39.0                   283.0            141.0   \n",
       "\n",
       "   White_AtLeastOneDose  White_FullNum  Hispanic or Latino_AtLeastOneDose  \\\n",
       "0               16427.0         8112.0                              485.0   \n",
       "1              210560.0       101525.0                             9941.0   \n",
       "2               56288.0        26895.0                             2903.0   \n",
       "3                4353.0         2007.0                              217.0   \n",
       "4                7258.0         3541.0                               62.0   \n",
       "\n",
       "   Hispanic or Latino_FullNum  Not Hispanic or Latino_AtLeastOneDose  \\\n",
       "0                       238.0                                15825.0   \n",
       "1                      4459.0                               226751.0   \n",
       "2                      1253.0                                62369.0   \n",
       "3                       122.0                                 4243.0   \n",
       "4                        29.0                                 7189.0   \n",
       "\n",
       "   Not Hispanic or Latino_FullNum  \n",
       "0                          7812.0  \n",
       "1                        108560.0  \n",
       "2                         29738.0  \n",
       "3                          1959.0  \n",
       "4                          3504.0  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Indiana_County_Vaccine_Race.to_csv('FinalResult/Indiana.csv')\n",
    "print(Indiana_County_Vaccine_Race.shape)\n",
    "Indiana_County_Vaccine_Race.head() # 95 counties in Pennsylvania"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "imposed-intensity",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Unknown, Indiana', 'Out Of State, Indiana']\n",
      "(92, 29)\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_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_Hispa</th>\n",
       "      <th>FullNumb_NaHis</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</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>Indiana</td>\n",
       "      <td>Adams</td>\n",
       "      <td>Adams, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>7812.0</td>\n",
       "      <td>8112.0</td>\n",
       "      <td>0.313869</td>\n",
       "      <td>...</td>\n",
       "      <td>0.469410</td>\n",
       "      <td>117</td>\n",
       "      <td>208</td>\n",
       "      <td>137</td>\n",
       "      <td>183</td>\n",
       "      <td>198</td>\n",
       "      <td>384</td>\n",
       "      <td>34995</td>\n",
       "      <td>35300</td>\n",
       "      <td>35777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Allen</td>\n",
       "      <td>Allen, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4686.0</td>\n",
       "      <td>8409.0</td>\n",
       "      <td>4459.0</td>\n",
       "      <td>108560.0</td>\n",
       "      <td>101525.0</td>\n",
       "      <td>0.268831</td>\n",
       "      <td>...</td>\n",
       "      <td>0.697219</td>\n",
       "      <td>1780</td>\n",
       "      <td>4346</td>\n",
       "      <td>17431</td>\n",
       "      <td>19785</td>\n",
       "      <td>45450</td>\n",
       "      <td>53961</td>\n",
       "      <td>302000</td>\n",
       "      <td>313605</td>\n",
       "      <td>379299</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Bartholomew</td>\n",
       "      <td>Bartholomew, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2539.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>1253.0</td>\n",
       "      <td>29738.0</td>\n",
       "      <td>26895.0</td>\n",
       "      <td>0.355951</td>\n",
       "      <td>...</td>\n",
       "      <td>0.773081</td>\n",
       "      <td>390</td>\n",
       "      <td>806</td>\n",
       "      <td>7133</td>\n",
       "      <td>7427</td>\n",
       "      <td>1974</td>\n",
       "      <td>2756</td>\n",
       "      <td>72810</td>\n",
       "      <td>74147</td>\n",
       "      <td>83779</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Benton</td>\n",
       "      <td>Benton, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>...</td>\n",
       "      <td>0.513386</td>\n",
       "      <td>28</td>\n",
       "      <td>69</td>\n",
       "      <td>21</td>\n",
       "      <td>31</td>\n",
       "      <td>97</td>\n",
       "      <td>165</td>\n",
       "      <td>8479</td>\n",
       "      <td>8595</td>\n",
       "      <td>8748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Blackford</td>\n",
       "      <td>Blackford, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3504.0</td>\n",
       "      <td>3541.0</td>\n",
       "      <td>0.104478</td>\n",
       "      <td>...</td>\n",
       "      <td>0.637898</td>\n",
       "      <td>31</td>\n",
       "      <td>111</td>\n",
       "      <td>67</td>\n",
       "      <td>98</td>\n",
       "      <td>90</td>\n",
       "      <td>172</td>\n",
       "      <td>11378</td>\n",
       "      <td>11560</td>\n",
       "      <td>11758</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     State       County  Geographic Area Name  Date  FullNumb_Asian  \\\n",
       "0  Indiana        Adams        Adams, Indiana   NaN            43.0   \n",
       "1  Indiana        Allen        Allen, Indiana   NaN          4686.0   \n",
       "2  Indiana  Bartholomew  Bartholomew, Indiana   NaN          2539.0   \n",
       "3  Indiana       Benton       Benton, Indiana   NaN             6.0   \n",
       "4  Indiana    Blackford    Blackford, Indiana   NaN             7.0   \n",
       "\n",
       "   FullNumb_Black  FullNumb_Hispa  FullNumb_NaHis  FullNumb_White  \\\n",
       "0            21.0           238.0          7812.0          8112.0   \n",
       "1          8409.0          4459.0        108560.0        101525.0   \n",
       "2           469.0          1253.0         29738.0         26895.0   \n",
       "3            12.0           122.0          1959.0          2007.0   \n",
       "4             6.0            29.0          3504.0          3541.0   \n",
       "\n",
       "   FullRate_Asian  ...  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0        0.313869  ...    0.469410          117           208          137   \n",
       "1        0.268831  ...    0.697219         1780          4346        17431   \n",
       "2        0.355951  ...    0.773081          390           806         7133   \n",
       "3        0.285714  ...    0.513386           28            69           21   \n",
       "4        0.104478  ...    0.637898           31           111           67   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0           183          198           384        34995         35300   \n",
       "1         19785        45450         53961       302000        313605   \n",
       "2          7427         1974          2756        72810         74147   \n",
       "3            31           97           165         8479          8595   \n",
       "4            98           90           172        11378         11560   \n",
       "\n",
       "   Total_Whole  \n",
       "0        35777  \n",
       "1       379299  \n",
       "2        83779  \n",
       "3         8748  \n",
       "4        11758  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "state = 'Indiana' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "\n",
    "for idx, row in Indiana_County_Vaccine_Race.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",
    "        'Lagrange': 'LaGrange',\n",
    "        'La Porte': 'LaPorte', \n",
    "        'De Kalb': 'DeKalb',\n",
    "        \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'] = float(d['White_AtLeastOneDose'])\n",
    "    \n",
    "    new_d['Numb_Black'] = d['Black or African American_AtLeastOneDose']\n",
    "    \n",
    "    new_d['Numb_Asian'] = d['Asian_AtLeastOneDose']\n",
    "    \n",
    "    new_d['Numb_NaHis'] = d['Not Hispanic or Latino_AtLeastOneDose']\n",
    "    \n",
    "    new_d['Numb_Hispa'] = d['Hispanic or Latino_AtLeastOneDose']\n",
    "    \n",
    "    \n",
    "    new_d['FullNumb_White'] = float(d['White_FullNum'])\n",
    "    \n",
    "    new_d['FullNumb_Black'] = d['Black or African American_FullNum']\n",
    "    \n",
    "    new_d['FullNumb_Asian'] = d['Asian_FullNum']\n",
    "    \n",
    "    new_d['FullNumb_NaHis'] = d['Not Hispanic or Latino_FullNum']\n",
    "    \n",
    "    new_d['FullNumb_Hispa'] = d['Hispanic or Latino_FullNum']\n",
    "    \n",
    "    \n",
    "    \n",
    "    new_d['Date'] = np.nan\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",
    "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",
    "    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()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "purple-classic",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois', 'Texas', 'Pennsylvania', 'Indiana']\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_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_Hispa</th>\n",
       "      <th>FullNumb_NaHis</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</th>\n",
       "      <th>...</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>Rate_Parity</th>\n",
       "      <th>FullRate_Parity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Adams</td>\n",
       "      <td>Adams, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>43.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>238.0</td>\n",
       "      <td>7812.0</td>\n",
       "      <td>8112.0</td>\n",
       "      <td>0.313869</td>\n",
       "      <td>...</td>\n",
       "      <td>208</td>\n",
       "      <td>137</td>\n",
       "      <td>183</td>\n",
       "      <td>198</td>\n",
       "      <td>384</td>\n",
       "      <td>34995</td>\n",
       "      <td>35300</td>\n",
       "      <td>35777</td>\n",
       "      <td>0.257289</td>\n",
       "      <td>0.125744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Allen</td>\n",
       "      <td>Allen, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4686.0</td>\n",
       "      <td>8409.0</td>\n",
       "      <td>4459.0</td>\n",
       "      <td>108560.0</td>\n",
       "      <td>101525.0</td>\n",
       "      <td>0.268831</td>\n",
       "      <td>...</td>\n",
       "      <td>4346</td>\n",
       "      <td>17431</td>\n",
       "      <td>19785</td>\n",
       "      <td>45450</td>\n",
       "      <td>53961</td>\n",
       "      <td>302000</td>\n",
       "      <td>313605</td>\n",
       "      <td>379299</td>\n",
       "      <td>0.301663</td>\n",
       "      <td>0.151159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Bartholomew</td>\n",
       "      <td>Bartholomew, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2539.0</td>\n",
       "      <td>469.0</td>\n",
       "      <td>1253.0</td>\n",
       "      <td>29738.0</td>\n",
       "      <td>26895.0</td>\n",
       "      <td>0.355951</td>\n",
       "      <td>...</td>\n",
       "      <td>806</td>\n",
       "      <td>7133</td>\n",
       "      <td>7427</td>\n",
       "      <td>1974</td>\n",
       "      <td>2756</td>\n",
       "      <td>72810</td>\n",
       "      <td>74147</td>\n",
       "      <td>83779</td>\n",
       "      <td>0.264975</td>\n",
       "      <td>0.131797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Benton</td>\n",
       "      <td>Benton, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>...</td>\n",
       "      <td>69</td>\n",
       "      <td>21</td>\n",
       "      <td>31</td>\n",
       "      <td>97</td>\n",
       "      <td>165</td>\n",
       "      <td>8479</td>\n",
       "      <td>8595</td>\n",
       "      <td>8748</td>\n",
       "      <td>0.265963</td>\n",
       "      <td>0.112991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Blackford</td>\n",
       "      <td>Blackford, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3504.0</td>\n",
       "      <td>3541.0</td>\n",
       "      <td>0.104478</td>\n",
       "      <td>...</td>\n",
       "      <td>111</td>\n",
       "      <td>67</td>\n",
       "      <td>98</td>\n",
       "      <td>90</td>\n",
       "      <td>172</td>\n",
       "      <td>11378</td>\n",
       "      <td>11560</td>\n",
       "      <td>11758</td>\n",
       "      <td>0.515675</td>\n",
       "      <td>0.244548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Washington</td>\n",
       "      <td>Washington, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>24.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>7063.0</td>\n",
       "      <td>7253.0</td>\n",
       "      <td>0.282353</td>\n",
       "      <td>...</td>\n",
       "      <td>208</td>\n",
       "      <td>85</td>\n",
       "      <td>157</td>\n",
       "      <td>143</td>\n",
       "      <td>246</td>\n",
       "      <td>27434</td>\n",
       "      <td>27718</td>\n",
       "      <td>28036</td>\n",
       "      <td>0.283822</td>\n",
       "      <td>0.145499</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Wayne</td>\n",
       "      <td>Wayne, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>271.0</td>\n",
       "      <td>789.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>19341.0</td>\n",
       "      <td>18303.0</td>\n",
       "      <td>0.380084</td>\n",
       "      <td>...</td>\n",
       "      <td>673</td>\n",
       "      <td>713</td>\n",
       "      <td>1041</td>\n",
       "      <td>3316</td>\n",
       "      <td>4618</td>\n",
       "      <td>59598</td>\n",
       "      <td>61394</td>\n",
       "      <td>65884</td>\n",
       "      <td>0.132073</td>\n",
       "      <td>0.069170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Wells</td>\n",
       "      <td>Wells, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>7642.0</td>\n",
       "      <td>7882.0</td>\n",
       "      <td>0.211640</td>\n",
       "      <td>...</td>\n",
       "      <td>201</td>\n",
       "      <td>189</td>\n",
       "      <td>248</td>\n",
       "      <td>249</td>\n",
       "      <td>429</td>\n",
       "      <td>27415</td>\n",
       "      <td>27754</td>\n",
       "      <td>28296</td>\n",
       "      <td>0.388513</td>\n",
       "      <td>0.195137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>White</td>\n",
       "      <td>White, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>262.0</td>\n",
       "      <td>8262.0</td>\n",
       "      <td>8445.0</td>\n",
       "      <td>0.227941</td>\n",
       "      <td>...</td>\n",
       "      <td>327</td>\n",
       "      <td>136</td>\n",
       "      <td>202</td>\n",
       "      <td>217</td>\n",
       "      <td>333</td>\n",
       "      <td>23236</td>\n",
       "      <td>23549</td>\n",
       "      <td>24102</td>\n",
       "      <td>0.606150</td>\n",
       "      <td>0.303537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>Whitley</td>\n",
       "      <td>Whitley, Indiana</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>97.0</td>\n",
       "      <td>10051.0</td>\n",
       "      <td>10330.0</td>\n",
       "      <td>0.277778</td>\n",
       "      <td>...</td>\n",
       "      <td>319</td>\n",
       "      <td>180</td>\n",
       "      <td>270</td>\n",
       "      <td>167</td>\n",
       "      <td>341</td>\n",
       "      <td>33030</td>\n",
       "      <td>33448</td>\n",
       "      <td>33964</td>\n",
       "      <td>0.242194</td>\n",
       "      <td>0.139093</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      State       County  Geographic Area Name  Date  FullNumb_Asian  \\\n",
       "0   Indiana        Adams        Adams, Indiana   NaN            43.0   \n",
       "1   Indiana        Allen        Allen, Indiana   NaN          4686.0   \n",
       "2   Indiana  Bartholomew  Bartholomew, Indiana   NaN          2539.0   \n",
       "3   Indiana       Benton       Benton, Indiana   NaN             6.0   \n",
       "4   Indiana    Blackford    Blackford, Indiana   NaN             7.0   \n",
       "..      ...          ...                   ...   ...             ...   \n",
       "87  Indiana   Washington   Washington, Indiana   NaN            24.0   \n",
       "88  Indiana        Wayne        Wayne, Indiana   NaN           271.0   \n",
       "89  Indiana        Wells        Wells, Indiana   NaN            40.0   \n",
       "90  Indiana        White        White, Indiana   NaN            31.0   \n",
       "91  Indiana      Whitley      Whitley, Indiana   NaN            50.0   \n",
       "\n",
       "    FullNumb_Black  FullNumb_Hispa  FullNumb_NaHis  FullNumb_White  \\\n",
       "0             21.0           238.0          7812.0          8112.0   \n",
       "1           8409.0          4459.0        108560.0        101525.0   \n",
       "2            469.0          1253.0         29738.0         26895.0   \n",
       "3             12.0           122.0          1959.0          2007.0   \n",
       "4              6.0            29.0          3504.0          3541.0   \n",
       "..             ...             ...             ...             ...   \n",
       "87            17.0            46.0          7063.0          7253.0   \n",
       "88           789.0           478.0         19341.0         18303.0   \n",
       "89            23.0           114.0          7642.0          7882.0   \n",
       "90            13.0           262.0          8262.0          8445.0   \n",
       "91            29.0            97.0         10051.0         10330.0   \n",
       "\n",
       "    FullRate_Asian  ...  Total_AIndAC  Total_Asian  Total_AsianC  Total_Black  \\\n",
       "0         0.313869  ...           208          137           183          198   \n",
       "1         0.268831  ...          4346        17431         19785        45450   \n",
       "2         0.355951  ...           806         7133          7427         1974   \n",
       "3         0.285714  ...            69           21            31           97   \n",
       "4         0.104478  ...           111           67            98           90   \n",
       "..             ...  ...           ...          ...           ...          ...   \n",
       "87        0.282353  ...           208           85           157          143   \n",
       "88        0.380084  ...           673          713          1041         3316   \n",
       "89        0.211640  ...           201          189           248          249   \n",
       "90        0.227941  ...           327          136           202          217   \n",
       "91        0.277778  ...           319          180           270          167   \n",
       "\n",
       "    Total_BlackC  Total_White  Total_WhiteC  Total_Whole  Rate_Parity  \\\n",
       "0            384        34995         35300        35777     0.257289   \n",
       "1          53961       302000        313605       379299     0.301663   \n",
       "2           2756        72810         74147        83779     0.264975   \n",
       "3            165         8479          8595         8748     0.265963   \n",
       "4            172        11378         11560        11758     0.515675   \n",
       "..           ...          ...           ...          ...          ...   \n",
       "87           246        27434         27718        28036     0.283822   \n",
       "88          4618        59598         61394        65884     0.132073   \n",
       "89           429        27415         27754        28296     0.388513   \n",
       "90           333        23236         23549        24102     0.606150   \n",
       "91           341        33030         33448        33964     0.242194   \n",
       "\n",
       "    FullRate_Parity  \n",
       "0          0.125744  \n",
       "1          0.151159  \n",
       "2          0.131797  \n",
       "3          0.112991  \n",
       "4          0.244548  \n",
       "..              ...  \n",
       "87         0.145499  \n",
       "88         0.069170  \n",
       "89         0.195137  \n",
       "90         0.303537  \n",
       "91         0.139093  \n",
       "\n",
       "[92 rows x 31 columns]"
      ]
     },
     "execution_count": 30,
     "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 = {}\n",
    "STATE_VACCINE[state] = T\n",
    "print([i for i in STATE_VACCINE])\n",
    "diagnose_Rate(T)\n",
    "\n",
    "T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "bearing-enemy",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois', 'Texas', 'Pennsylvania', 'Indiana']\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>89.0</td>\n",
       "      <td>0.175045</td>\n",
       "      <td>0.102368</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.110000</td>\n",
       "      <td>0.171994</td>\n",
       "      <td>0.210841</td>\n",
       "      <td>0.832898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.311703</td>\n",
       "      <td>0.062741</td>\n",
       "      <td>0.149345</td>\n",
       "      <td>0.267380</td>\n",
       "      <td>0.309197</td>\n",
       "      <td>0.345720</td>\n",
       "      <td>0.494419</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.371284</td>\n",
       "      <td>0.230152</td>\n",
       "      <td>0.053691</td>\n",
       "      <td>0.224396</td>\n",
       "      <td>0.351936</td>\n",
       "      <td>0.443820</td>\n",
       "      <td>1.937337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>92.0</td>\n",
       "      <td>0.635823</td>\n",
       "      <td>0.127857</td>\n",
       "      <td>0.307123</td>\n",
       "      <td>0.542751</td>\n",
       "      <td>0.630369</td>\n",
       "      <td>0.713156</td>\n",
       "      <td>0.976631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.268588</td>\n",
       "      <td>0.201569</td>\n",
       "      <td>-1.280970</td>\n",
       "      <td>0.213132</td>\n",
       "      <td>0.279290</td>\n",
       "      <td>0.353258</td>\n",
       "      <td>0.606150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>89.0</td>\n",
       "      <td>0.139888</td>\n",
       "      <td>0.089823</td>\n",
       "      <td>-0.515960</td>\n",
       "      <td>0.108180</td>\n",
       "      <td>0.145039</td>\n",
       "      <td>0.179915</td>\n",
       "      <td>0.303537</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black   89.0  0.175045  0.102368  0.050000  0.110000  0.171994   \n",
       "1   FullRate_White   92.0  0.311703  0.062741  0.149345  0.267380  0.309197   \n",
       "2       Rate_Black   90.0  0.371284  0.230152  0.053691  0.224396  0.351936   \n",
       "3       Rate_White   92.0  0.635823  0.127857  0.307123  0.542751  0.630369   \n",
       "4      Rate_Parity   90.0  0.268588  0.201569 -1.280970  0.213132  0.279290   \n",
       "5  FullRate_Parity   89.0  0.139888  0.089823 -0.515960  0.108180  0.145039   \n",
       "\n",
       "        75%       max  \n",
       "0  0.210841  0.832898  \n",
       "1  0.345720  0.494419  \n",
       "2  0.443820  1.937337  \n",
       "3  0.713156  0.976631  \n",
       "4  0.353258  0.606150  \n",
       "5  0.179915  0.303537  "
      ]
     },
     "execution_count": 31,
     "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": "therapeutic-defeat",
   "metadata": {},
   "source": [
    "## B. Virginia* (Done) XX\n",
    "\n",
    "https://data.virginia.gov/Government/VDH-COVID-19-PublicUseDataset-Vaccines-DosesAdmini/u5ru-3khs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "durable-aging",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING:root:Requests made without an app_token will be subject to strict throttling limits.\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "from  sodapy import Socrata\n",
    "\n",
    "\n",
    "# Today_Date = str(datetime.now().date())\n",
    "\n",
    "##########################################\n",
    "report_date = Today_Date + 'T00:00:00.000'\n",
    "##########################################\n",
    "\n",
    "client = Socrata(\"data.virginia.gov\", None)\n",
    "\n",
    "url = 'https://data.virginia.gov/resource/u5ru-3khs.json?report_date=' + report_date + 'T00:00:00.000?limit=3000'\n",
    "\n",
    "results = client.get(\"u5ru-3khs\", limit=5000, report_date = report_date)\n",
    "\n",
    "# Convert to pandas DataFrame\n",
    "RawData = pd.DataFrame.from_records(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "criminal-visibility",
   "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>report_date</th>\n",
       "      <th>fips</th>\n",
       "      <th>locality</th>\n",
       "      <th>health_district</th>\n",
       "      <th>health_region</th>\n",
       "      <th>vaccination_status</th>\n",
       "      <th>race_and_ethnicity</th>\n",
       "      <th>people_by_vaccination_status_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-05-20T00:00:00.000</td>\n",
       "      <td>51610</td>\n",
       "      <td>Falls Church</td>\n",
       "      <td>Fairfax</td>\n",
       "      <td>Northern</td>\n",
       "      <td>Not Reported</td>\n",
       "      <td>Latino</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-05-20T00:00:00.000</td>\n",
       "      <td>51071</td>\n",
       "      <td>Giles</td>\n",
       "      <td>New River</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>Not Reported</td>\n",
       "      <td>White</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-05-20T00:00:00.000</td>\n",
       "      <td>51075</td>\n",
       "      <td>Goochland</td>\n",
       "      <td>Chickahominy</td>\n",
       "      <td>Central</td>\n",
       "      <td>At Least One Dose</td>\n",
       "      <td>Latino</td>\n",
       "      <td>304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-05-20T00:00:00.000</td>\n",
       "      <td>51027</td>\n",
       "      <td>Buchanan</td>\n",
       "      <td>Cumberland Plateau</td>\n",
       "      <td>Southwest</td>\n",
       "      <td>Fully Vaccinated</td>\n",
       "      <td>Latino</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-05-20T00:00:00.000</td>\n",
       "      <td>51111</td>\n",
       "      <td>Lunenburg</td>\n",
       "      <td>Piedmont</td>\n",
       "      <td>Central</td>\n",
       "      <td>Fully Vaccinated</td>\n",
       "      <td>Other Race</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               report_date   fips      locality     health_district  \\\n",
       "0  2021-05-20T00:00:00.000  51610  Falls Church             Fairfax   \n",
       "1  2021-05-20T00:00:00.000  51071         Giles           New River   \n",
       "2  2021-05-20T00:00:00.000  51075     Goochland        Chickahominy   \n",
       "3  2021-05-20T00:00:00.000  51027      Buchanan  Cumberland Plateau   \n",
       "4  2021-05-20T00:00:00.000  51111     Lunenburg            Piedmont   \n",
       "\n",
       "  health_region vaccination_status race_and_ethnicity  \\\n",
       "0      Northern       Not Reported             Latino   \n",
       "1     Southwest       Not Reported              White   \n",
       "2       Central  At Least One Dose             Latino   \n",
       "3     Southwest   Fully Vaccinated             Latino   \n",
       "4       Central   Fully Vaccinated         Other Race   \n",
       "\n",
       "  people_by_vaccination_status_count  \n",
       "0                                  0  \n",
       "1                                  0  \n",
       "2                                304  \n",
       "3                                 41  \n",
       "4                                 27  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "while len(RawData) == 0:\n",
    "    print('Error in Date, Please Change Date:')\n",
    "    ##########################################\n",
    "    new_date_for_virgina = input()\n",
    "    report_date = new_date_for_virgina + 'T00:00:00.000'\n",
    "    ##########################################\n",
    "\n",
    "    client = Socrata(\"data.virginia.gov\", None)\n",
    "\n",
    "    url = 'https://data.virginia.gov/resource/u5ru-3khs.json?report_date=' + report_date + 'T00:00:00.000?limit=3000'\n",
    "\n",
    "    results = client.get(\"u5ru-3khs\", limit=5000, report_date = report_date)\n",
    "\n",
    "    # Convert to pandas DataFrame\n",
    "    RawData = pd.DataFrame.from_records(results)\n",
    "    \n",
    "RawData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "accompanied-injury",
   "metadata": {},
   "outputs": [],
   "source": [
    "RawDataGroups = RawData.groupby('locality')\n",
    "\n",
    "\n",
    "L = []\n",
    "for county, record in RawDataGroups:\n",
    "    D = {}\n",
    "\n",
    "    D['County'] = county\n",
    "\n",
    "    for idx, row in record.iterrows():\n",
    "        d = row.to_dict()\n",
    "        race = d['race_and_ethnicity'] \n",
    "        \n",
    "        d = row.to_dict()\n",
    "        # print(d)\n",
    "        D[race + '_' + d['vaccination_status']] = float(d['people_by_vaccination_status_count'])\n",
    "        \n",
    "\n",
    "    L.append(D)\n",
    "\n",
    "    \n",
    "Virginia_County_Vaccine_Race = pd.DataFrame(L)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "passing-flooring",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(135, 25)\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>Black_Not Reported</th>\n",
       "      <th>Latino_Not Reported</th>\n",
       "      <th>Other Race_At Least One Dose</th>\n",
       "      <th>Two or more races_Fully Vaccinated</th>\n",
       "      <th>Not Reported_Fully Vaccinated</th>\n",
       "      <th>Latino_Fully Vaccinated</th>\n",
       "      <th>Black_Fully Vaccinated</th>\n",
       "      <th>Other Race_Not Reported</th>\n",
       "      <th>Other Race_Fully Vaccinated</th>\n",
       "      <th>...</th>\n",
       "      <th>Native American_Not Reported</th>\n",
       "      <th>Two or more races_At Least One Dose</th>\n",
       "      <th>White_At Least One Dose</th>\n",
       "      <th>Latino_At Least One Dose</th>\n",
       "      <th>Black_At Least One Dose</th>\n",
       "      <th>Two or more races_Not Reported</th>\n",
       "      <th>Asian or Pacific Islander_Not Reported</th>\n",
       "      <th>White_Not Reported</th>\n",
       "      <th>Not Reported_Not Reported</th>\n",
       "      <th>Not Reported_At Least One Dose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Accomack</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2654.0</td>\n",
       "      <td>492.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7047.0</td>\n",
       "      <td>740.0</td>\n",
       "      <td>2479.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3479.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albemarle</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2838.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14687.0</td>\n",
       "      <td>2471.0</td>\n",
       "      <td>2896.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2388.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>37174.0</td>\n",
       "      <td>3336.0</td>\n",
       "      <td>3609.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21061.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alexandria</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3638.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28682.0</td>\n",
       "      <td>6786.0</td>\n",
       "      <td>3388.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2939.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17654.0</td>\n",
       "      <td>10323.0</td>\n",
       "      <td>4527.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39332.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alleghany</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2039.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3190.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>189.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2837.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Amelia</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>932.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2369.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>922.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1358.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       County  Black_Not Reported  Latino_Not Reported  \\\n",
       "0    Accomack                 0.0                  0.0   \n",
       "1   Albemarle                 0.0                  0.0   \n",
       "2  Alexandria                 0.0                  0.0   \n",
       "3   Alleghany                 0.0                  0.0   \n",
       "4      Amelia                 0.0                  0.0   \n",
       "\n",
       "   Other Race_At Least One Dose  Two or more races_Fully Vaccinated  \\\n",
       "0                          35.0                                 0.0   \n",
       "1                        2838.0                                 0.0   \n",
       "2                        3638.0                                 0.0   \n",
       "3                          75.0                                 0.0   \n",
       "4                          46.0                                 0.0   \n",
       "\n",
       "   Not Reported_Fully Vaccinated  Latino_Fully Vaccinated  \\\n",
       "0                         2654.0                    492.0   \n",
       "1                        14687.0                   2471.0   \n",
       "2                        28682.0                   6786.0   \n",
       "3                         2039.0                     82.0   \n",
       "4                          932.0                     74.0   \n",
       "\n",
       "   Black_Fully Vaccinated  Other Race_Not Reported  \\\n",
       "0                  2018.0                      0.0   \n",
       "1                  2896.0                      0.0   \n",
       "2                  3388.0                      0.0   \n",
       "3                   140.0                      0.0   \n",
       "4                   746.0                      0.0   \n",
       "\n",
       "   Other Race_Fully Vaccinated  ...  Native American_Not Reported  \\\n",
       "0                         26.0  ...                           0.0   \n",
       "1                       2388.0  ...                           0.0   \n",
       "2                       2939.0  ...                           0.0   \n",
       "3                         51.0  ...                           0.0   \n",
       "4                         34.0  ...                           0.0   \n",
       "\n",
       "   Two or more races_At Least One Dose  White_At Least One Dose  \\\n",
       "0                                  0.0                   7047.0   \n",
       "1                                  0.0                  37174.0   \n",
       "2                                  0.0                  17654.0   \n",
       "3                                  0.0                   3190.0   \n",
       "4                                  0.0                   2369.0   \n",
       "\n",
       "   Latino_At Least One Dose  Black_At Least One Dose  \\\n",
       "0                     740.0                   2479.0   \n",
       "1                    3336.0                   3609.0   \n",
       "2                   10323.0                   4527.0   \n",
       "3                     102.0                    189.0   \n",
       "4                     101.0                    922.0   \n",
       "\n",
       "   Two or more races_Not Reported  Asian or Pacific Islander_Not Reported  \\\n",
       "0                             0.0                                     0.0   \n",
       "1                             0.0                                     0.0   \n",
       "2                             0.0                                     0.0   \n",
       "3                             0.0                                     0.0   \n",
       "4                             0.0                                     0.0   \n",
       "\n",
       "   White_Not Reported  Not Reported_Not Reported  \\\n",
       "0                 0.0                        0.0   \n",
       "1                 0.0                        0.0   \n",
       "2                 0.0                        0.0   \n",
       "3                 0.0                        0.0   \n",
       "4                 0.0                        0.0   \n",
       "\n",
       "   Not Reported_At Least One Dose  \n",
       "0                          3479.0  \n",
       "1                         21061.0  \n",
       "2                         39332.0  \n",
       "3                          2837.0  \n",
       "4                          1358.0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Virginia_County_Vaccine_Race.to_csv('FinalResult/Vriginia.csv')\n",
    "print(Virginia_County_Vaccine_Race.shape)\n",
    "Virginia_County_Vaccine_Race.head() # 67 counties in Pennsylvania"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "eastern-supervisor",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['County', 'Black_Not Reported', 'Latino_Not Reported',\n",
       "       'Other Race_At Least One Dose', 'Two or more races_Fully Vaccinated',\n",
       "       'Not Reported_Fully Vaccinated', 'Latino_Fully Vaccinated',\n",
       "       'Black_Fully Vaccinated', 'Other Race_Not Reported',\n",
       "       'Other Race_Fully Vaccinated',\n",
       "       'Asian or Pacific Islander_Fully Vaccinated',\n",
       "       'Native American_At Least One Dose', 'Native American_Fully Vaccinated',\n",
       "       'White_Fully Vaccinated', 'Asian or Pacific Islander_At Least One Dose',\n",
       "       'Native American_Not Reported', 'Two or more races_At Least One Dose',\n",
       "       'White_At Least One Dose', 'Latino_At Least One Dose',\n",
       "       'Black_At Least One Dose', 'Two or more races_Not Reported',\n",
       "       'Asian or Pacific Islander_Not Reported', 'White_Not Reported',\n",
       "       'Not Reported_Not Reported', 'Not Reported_At Least One Dose'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Virginia_County_Vaccine_Race.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "numeric-castle",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "County                                         Accomack\n",
       "Black_Not Reported                                  0.0\n",
       "Latino_Not Reported                                 0.0\n",
       "Other Race_At Least One Dose                       35.0\n",
       "Two or more races_Fully Vaccinated                  0.0\n",
       "Not Reported_Fully Vaccinated                    2654.0\n",
       "Latino_Fully Vaccinated                           492.0\n",
       "Black_Fully Vaccinated                           2018.0\n",
       "Other Race_Not Reported                             0.0\n",
       "Other Race_Fully Vaccinated                        26.0\n",
       "Asian or Pacific Islander_Fully Vaccinated         79.0\n",
       "Native American_At Least One Dose                  25.0\n",
       "Native American_Fully Vaccinated                   25.0\n",
       "White_Fully Vaccinated                           6422.0\n",
       "Asian or Pacific Islander_At Least One Dose        95.0\n",
       "Native American_Not Reported                        0.0\n",
       "Two or more races_At Least One Dose                 0.0\n",
       "White_At Least One Dose                          7047.0\n",
       "Latino_At Least One Dose                          740.0\n",
       "Black_At Least One Dose                          2479.0\n",
       "Two or more races_Not Reported                      0.0\n",
       "Asian or Pacific Islander_Not Reported              0.0\n",
       "White_Not Reported                                  0.0\n",
       "Not Reported_Not Reported                           0.0\n",
       "Not Reported_At Least One Dose                   3479.0\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Virginia_County_Vaccine_Race.loc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "equal-argument",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Accomack', 'Albemarle', 'Alexandria', 'Alleghany', 'Amelia',\n",
       "       'Amherst', 'Appomattox', 'Arlington', 'Augusta', 'Bath', 'Bedford',\n",
       "       'Bland', 'Botetourt', 'Bristol', 'Brunswick', 'Buchanan',\n",
       "       'Buckingham', 'Buena Vista City', 'Campbell', 'Caroline',\n",
       "       'Carroll', 'Charles City', 'Charlotte', 'Charlottesville',\n",
       "       'Chesapeake', 'Chesterfield', 'Clarke', 'Colonial Heights',\n",
       "       'Covington', 'Craig', 'Culpeper', 'Cumberland', 'Danville',\n",
       "       'Dickenson', 'Dinwiddie', 'Emporia', 'Essex', 'Fairfax',\n",
       "       'Fairfax City', 'Falls Church', 'Fauquier', 'Floyd', 'Fluvanna',\n",
       "       'Franklin City', 'Franklin County', 'Frederick', 'Fredericksburg',\n",
       "       'Galax', 'Giles', 'Gloucester', 'Goochland', 'Grayson', 'Greene',\n",
       "       'Greensville', 'Halifax', 'Hampton', 'Hanover', 'Harrisonburg',\n",
       "       'Henrico', 'Henry', 'Highland', 'Hopewell', 'Isle of Wight',\n",
       "       'James City', 'King George', 'King William', 'King and Queen',\n",
       "       'Lancaster', 'Lee', 'Lexington', 'Loudoun', 'Louisa', 'Lunenburg',\n",
       "       'Lynchburg', 'Madison', 'Manassas City', 'Manassas Park',\n",
       "       'Martinsville', 'Mathews', 'Mecklenburg', 'Middlesex',\n",
       "       'Montgomery', 'Nelson', 'New Kent', 'Newport News', 'Norfolk',\n",
       "       'Northampton', 'Northumberland', 'Norton', 'Not Reported',\n",
       "       'Nottoway', 'Orange', 'Out-of-State', 'Page', 'Patrick',\n",
       "       'Petersburg', 'Pittsylvania', 'Poquoson', 'Portsmouth', 'Powhatan',\n",
       "       'Prince Edward', 'Prince George', 'Prince William', 'Pulaski',\n",
       "       'Radford', 'Rappahannock', 'Richmond City', 'Richmond County',\n",
       "       'Roanoke City', 'Roanoke County', 'Rockbridge', 'Rockingham',\n",
       "       'Russell', 'Salem', 'Scott', 'Shenandoah', 'Smyth', 'Southampton',\n",
       "       'Spotsylvania', 'Stafford', 'Staunton', 'Suffolk', 'Surry',\n",
       "       'Sussex', 'Tazewell', 'Virginia Beach', 'Warren', 'Washington',\n",
       "       'Waynesboro', 'Westmoreland', 'Williamsburg', 'Winchester', 'Wise',\n",
       "       'Wythe', 'York'], dtype=object)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Virginia_County_Vaccine_Race['County'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "african-neighbor",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not included Counties ['Out-of-state, Virginia', 'Not Reported, Virginia']\n",
      "(133, 25)\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_Asian</th>\n",
       "      <th>FullNumb_Black</th>\n",
       "      <th>FullNumb_White</th>\n",
       "      <th>FullRate_Asian</th>\n",
       "      <th>FullRate_Black</th>\n",
       "      <th>FullRate_White</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>Virginia</td>\n",
       "      <td>Accomack</td>\n",
       "      <td>Accomack, Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.0</td>\n",
       "      <td>2018.0</td>\n",
       "      <td>6422.0</td>\n",
       "      <td>0.307393</td>\n",
       "      <td>0.216896</td>\n",
       "      <td>0.293255</td>\n",
       "      <td>...</td>\n",
       "      <td>0.321796</td>\n",
       "      <td>239</td>\n",
       "      <td>447</td>\n",
       "      <td>257</td>\n",
       "      <td>348</td>\n",
       "      <td>9304</td>\n",
       "      <td>9632</td>\n",
       "      <td>21899</td>\n",
       "      <td>22411</td>\n",
       "      <td>32316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>Albemarle</td>\n",
       "      <td>Albemarle, Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1164.0</td>\n",
       "      <td>2896.0</td>\n",
       "      <td>31052.0</td>\n",
       "      <td>0.192365</td>\n",
       "      <td>0.273208</td>\n",
       "      <td>0.347384</td>\n",
       "      <td>...</td>\n",
       "      <td>0.415872</td>\n",
       "      <td>383</td>\n",
       "      <td>1000</td>\n",
       "      <td>6051</td>\n",
       "      <td>7189</td>\n",
       "      <td>10600</td>\n",
       "      <td>12008</td>\n",
       "      <td>89388</td>\n",
       "      <td>92002</td>\n",
       "      <td>109330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>Alexandria city</td>\n",
       "      <td>Alexandria city, Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1389.0</td>\n",
       "      <td>3388.0</td>\n",
       "      <td>14807.0</td>\n",
       "      <td>0.133841</td>\n",
       "      <td>0.093359</td>\n",
       "      <td>0.139256</td>\n",
       "      <td>...</td>\n",
       "      <td>0.166032</td>\n",
       "      <td>998</td>\n",
       "      <td>2243</td>\n",
       "      <td>10378</td>\n",
       "      <td>12886</td>\n",
       "      <td>36290</td>\n",
       "      <td>38626</td>\n",
       "      <td>106329</td>\n",
       "      <td>110857</td>\n",
       "      <td>159428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>Alleghany</td>\n",
       "      <td>Alleghany, Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>2598.0</td>\n",
       "      <td>0.304348</td>\n",
       "      <td>0.200573</td>\n",
       "      <td>0.188493</td>\n",
       "      <td>...</td>\n",
       "      <td>0.231445</td>\n",
       "      <td>44</td>\n",
       "      <td>119</td>\n",
       "      <td>46</td>\n",
       "      <td>74</td>\n",
       "      <td>698</td>\n",
       "      <td>890</td>\n",
       "      <td>13783</td>\n",
       "      <td>14040</td>\n",
       "      <td>14860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>Amelia</td>\n",
       "      <td>Amelia, Virginia</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20.0</td>\n",
       "      <td>746.0</td>\n",
       "      <td>1893.0</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>0.277530</td>\n",
       "      <td>0.188358</td>\n",
       "      <td>...</td>\n",
       "      <td>0.235721</td>\n",
       "      <td>79</td>\n",
       "      <td>161</td>\n",
       "      <td>80</td>\n",
       "      <td>124</td>\n",
       "      <td>2688</td>\n",
       "      <td>2827</td>\n",
       "      <td>10050</td>\n",
       "      <td>10264</td>\n",
       "      <td>13145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      State           County       Geographic Area Name  Date  FullNumb_Asian  \\\n",
       "0  Virginia         Accomack         Accomack, Virginia   NaN            79.0   \n",
       "1  Virginia        Albemarle        Albemarle, Virginia   NaN          1164.0   \n",
       "2  Virginia  Alexandria city  Alexandria city, Virginia   NaN          1389.0   \n",
       "3  Virginia        Alleghany        Alleghany, Virginia   NaN            14.0   \n",
       "4  Virginia           Amelia           Amelia, Virginia   NaN            20.0   \n",
       "\n",
       "   FullNumb_Black  FullNumb_White  FullRate_Asian  FullRate_Black  \\\n",
       "0          2018.0          6422.0        0.307393        0.216896   \n",
       "1          2896.0         31052.0        0.192365        0.273208   \n",
       "2          3388.0         14807.0        0.133841        0.093359   \n",
       "3           140.0          2598.0        0.304348        0.200573   \n",
       "4           746.0          1893.0        0.250000        0.277530   \n",
       "\n",
       "   FullRate_White  ...  Rate_White  Total_AIndA  Total_AIndAC  Total_Asian  \\\n",
       "0        0.293255  ...    0.321796          239           447          257   \n",
       "1        0.347384  ...    0.415872          383          1000         6051   \n",
       "2        0.139256  ...    0.166032          998          2243        10378   \n",
       "3        0.188493  ...    0.231445           44           119           46   \n",
       "4        0.188358  ...    0.235721           79           161           80   \n",
       "\n",
       "   Total_AsianC  Total_Black  Total_BlackC  Total_White  Total_WhiteC  \\\n",
       "0           348         9304          9632        21899         22411   \n",
       "1          7189        10600         12008        89388         92002   \n",
       "2         12886        36290         38626       106329        110857   \n",
       "3            74          698           890        13783         14040   \n",
       "4           124         2688          2827        10050         10264   \n",
       "\n",
       "   Total_Whole  \n",
       "0        32316  \n",
       "1       109330  \n",
       "2       159428  \n",
       "3        14860  \n",
       "4        13145  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print(len(L))\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "\n",
    "virginia_name_dict = {\n",
    " 'Colonial Heights, Virginia': ['Colonial Heights city, Virginia'],\n",
    " \n",
    " 'Portsmouth, Virginia': ['Portsmouth city, Virginia'],\n",
    "    \n",
    " 'Roanoke City, Virginia': ['Roanoke city, Virginia'],\n",
    "    \n",
    " 'Charlottesville, Virginia': ['Charlottesville city, Virginia'],\n",
    "    \n",
    " 'Waynesboro, Virginia': ['Waynesboro city, Virginia'],\n",
    "    \n",
    " 'Virginia Beach, Virginia': ['Virginia Beach city, Virginia'],\n",
    "    \n",
    " 'Suffolk, Virginia': ['Suffolk city, Virginia'],\n",
    "    \n",
    " 'Williamsburg, Virginia': ['Williamsburg city, Virginia'],\n",
    "    \n",
    " 'Roanoke County, Virginia': ['Roanoke, Virginia'],\n",
    "    \n",
    " 'Falls Church, Virginia': ['Falls Church city, Virginia'],\n",
    "    \n",
    " 'Franklin County, Virginia': ['Franklin, Virginia'],\n",
    "    \n",
    " 'Buena Vista City, Virginia': ['Buena Vista city, Virginia'],\n",
    " \n",
    " 'Lynchburg, Virginia': ['Lynchburg city, Virginia'],\n",
    "    \n",
    " 'Hampton, Virginia': ['Hampton city, Virginia'],\n",
    "    \n",
    " 'Fredericksburg, Virginia': ['Fredericksburg city, Virginia'],\n",
    "    \n",
    " 'Staunton, Virginia': ['Staunton city, Virginia'],\n",
    "    \n",
    " 'Galax, Virginia': ['Galax city, Virginia'],\n",
    "    \n",
    " 'Richmond County, Virginia': ['Richmond, Virginia'],\n",
    "    \n",
    " 'Petersburg, Virginia': ['Petersburg city, Virginia'],\n",
    "    \n",
    " 'Harrisonburg, Virginia': ['Harrisonburg city, Virginia'],\n",
    "    \n",
    " 'Richmond City, Virginia': ['Richmond city, Virginia'],\n",
    "    \n",
    " 'Covington, Virginia': ['Covington city, Virginia'],\n",
    "    \n",
    " 'Norfolk, Virginia': ['Norfolk city, Virginia'],\n",
    "    \n",
    " 'Fairfax City, Virginia': ['Fairfax city, Virginia'],\n",
    "    \n",
    " 'Bristol, Virginia': ['Bristol city, Virginia'],\n",
    "    \n",
    " 'Isle Of Wight, Virginia': ['Isle of Wight, Virginia'],\n",
    "    \n",
    " 'Lexington, Virginia': ['Lexington city, Virginia'],\n",
    "    \n",
    " 'Hopewell, Virginia': ['Hopewell city, Virginia'],\n",
    "    \n",
    " 'Alexandria, Virginia': ['Alexandria city, Virginia'],\n",
    "    \n",
    " 'Manassas City, Virginia': ['Manassas city, Virginia'],\n",
    "    \n",
    " 'Winchester, Virginia': ['Winchester city, Virginia'],\n",
    "    \n",
    " 'Manassas Park, Virginia': ['Manassas Park city, Virginia'],\n",
    "    \n",
    " 'Poquoson, Virginia': ['Poquoson city, Virginia'],\n",
    "    \n",
    " 'Emporia, Virginia': ['Emporia city, Virginia'],\n",
    "    \n",
    " 'Radford, Virginia': ['Radford city, Virginia'],\n",
    "    \n",
    " 'Salem, Virginia': ['Salem city, Virginia'],\n",
    "    \n",
    " 'Chesapeake, Virginia': ['Chesapeake city, Virginia'],\n",
    "    \n",
    " 'King And Queen, Virginia': ['King and Queen, Virginia'],\n",
    "    \n",
    " 'Franklin City, Virginia': ['Franklin city, Virginia'],\n",
    "    \n",
    " 'Norton, Virginia': ['Norton city, Virginia'],\n",
    "  \n",
    " 'Danville, Virginia': ['Danville city, Virginia'],\n",
    "    \n",
    " 'Newport News, Virginia': ['Newport News city, Virginia'],\n",
    "    \n",
    " 'Martinsville, Virginia': ['Martinsville city, Virginia']}\n",
    "\n",
    "RenameCounties = {k.replace(', Virginia', ''): v[0].replace(', Virginia', '') for k, v in virginia_name_dict.items()}\n",
    "\n",
    "state = 'Virginia' ## \n",
    "\n",
    "new_L = []\n",
    "\n",
    "\n",
    "for idx, row in Virginia_County_Vaccine_Race.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",
    "    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_At Least One Dose']\n",
    "    \n",
    "    new_d['Numb_Black'] = d['Black_At Least One Dose']\n",
    "    \n",
    "    new_d['Numb_Asian'] = d['Asian or Pacific Islander_At Least One Dose']\n",
    "    \n",
    "    \n",
    "    new_d['FullNumb_White'] = d['White_Fully Vaccinated']\n",
    "    \n",
    "    new_d['FullNumb_Black'] = d['Black_Fully Vaccinated']\n",
    "    \n",
    "    new_d['FullNumb_Asian'] = d['Asian or Pacific Islander_Fully Vaccinated']\n",
    "    \n",
    "    \n",
    "    \n",
    "    new_d['Date'] = np.nan\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', '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",
    "    \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",
    "print(T.shape)\n",
    "T.head()\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "spectacular-scoop",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Illinois', 'Texas', 'Pennsylvania', 'Indiana', '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>133.0</td>\n",
       "      <td>0.186399</td>\n",
       "      <td>0.072396</td>\n",
       "      <td>0.051606</td>\n",
       "      <td>0.132377</td>\n",
       "      <td>0.170360</td>\n",
       "      <td>0.229512</td>\n",
       "      <td>0.437086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FullRate_White</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.210906</td>\n",
       "      <td>0.063200</td>\n",
       "      <td>0.089883</td>\n",
       "      <td>0.163396</td>\n",
       "      <td>0.200643</td>\n",
       "      <td>0.243229</td>\n",
       "      <td>0.428161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rate_Black</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.226668</td>\n",
       "      <td>0.087424</td>\n",
       "      <td>0.067661</td>\n",
       "      <td>0.163379</td>\n",
       "      <td>0.211537</td>\n",
       "      <td>0.272340</td>\n",
       "      <td>0.608696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rate_White</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.244373</td>\n",
       "      <td>0.069999</td>\n",
       "      <td>0.105500</td>\n",
       "      <td>0.185471</td>\n",
       "      <td>0.235721</td>\n",
       "      <td>0.283663</td>\n",
       "      <td>0.479973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rate_Parity</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.017705</td>\n",
       "      <td>0.082831</td>\n",
       "      <td>-0.423878</td>\n",
       "      <td>-0.012011</td>\n",
       "      <td>0.028370</td>\n",
       "      <td>0.060885</td>\n",
       "      <td>0.275374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FullRate_Parity</td>\n",
       "      <td>133.0</td>\n",
       "      <td>0.024507</td>\n",
       "      <td>0.068099</td>\n",
       "      <td>-0.223975</td>\n",
       "      <td>-0.007011</td>\n",
       "      <td>0.030524</td>\n",
       "      <td>0.060157</td>\n",
       "      <td>0.292251</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name  count      mean       std       min       25%       50%  \\\n",
       "0   FullRate_Black  133.0  0.186399  0.072396  0.051606  0.132377  0.170360   \n",
       "1   FullRate_White  133.0  0.210906  0.063200  0.089883  0.163396  0.200643   \n",
       "2       Rate_Black  133.0  0.226668  0.087424  0.067661  0.163379  0.211537   \n",
       "3       Rate_White  133.0  0.244373  0.069999  0.105500  0.185471  0.235721   \n",
       "4      Rate_Parity  133.0  0.017705  0.082831 -0.423878 -0.012011  0.028370   \n",
       "5  FullRate_Parity  133.0  0.024507  0.068099 -0.223975 -0.007011  0.030524   \n",
       "\n",
       "        75%       max  \n",
       "0  0.229512  0.437086  \n",
       "1  0.243229  0.428161  \n",
       "2  0.272340  0.608696  \n",
       "3  0.283663  0.479973  \n",
       "4  0.060885  0.275374  \n",
       "5  0.060157  0.292251  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Virginia\tAccomack\tAccomack, Virginia\tNaN\t63.0\t1704.0\t5764.0\t0.245136\t0.183147\t0.263208\t239\t447\t257\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": "corresponding-pottery",
   "metadata": {},
   "source": [
    "# All in One"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "everyday-burning",
   "metadata": {},
   "source": [
    "## Read Previous Vaccine Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "smaller-folks",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import os\n",
    "# path = ''\n",
    "\n",
    "# print('Input Historical STATE_VACCINE')\n",
    "# print('For example: CountyVaccine/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": 39,
   "id": "centered-comfort",
   "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": "brave-dispute",
   "metadata": {},
   "source": [
    "## Show Parity for Each State"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "front-bonus",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ######################################\n",
    "\n",
    "# # Today_Date = str(datetime.now().date())\n",
    "# print(Today_Date)\n",
    "# ######################################\n",
    "\n",
    "\n",
    "# for state in STATE_VACCINE:\n",
    "    \n",
    "#     print(state)\n",
    "#     cols = ['name',  'date', 'count', 'mean', 'min','50%', 'max']\n",
    "    \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": "computational-breakdown",
   "metadata": {},
   "source": [
    "##  Show Some Specials Cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "aggressive-grass",
   "metadata": {},
   "outputs": [],
   "source": [
    "\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",
    "\n",
    "# DF[DF['Rate_Black'] > 0.8][data_cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "physical-newport",
   "metadata": {},
   "source": [
    "## Save Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "special-wisdom",
   "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_Autonomy.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 + '_Autonomy.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": "code",
   "execution_count": null,
   "id": "charged-hammer",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "continued-shower",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "directed-alloy",
   "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
}