dataset_raw/Update static features.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import io, requests, os \n",
    "import numpy as np\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "output_folder = 'CovidMay17-2022' # write the cleaned processed files here\n",
    "support_folder = 'Support files' # cache the raw source files here for future usage\n",
    "\n",
    "if not os.path.exists(output_folder):\n",
    "    os.makedirs(output_folder, exist_ok=True)\n",
    "\n",
    "if not os.path.exists(support_folder):\n",
    "    os.makedirs(support_folder, exist_ok=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Source files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Population Estimation\n",
    "\n",
    "PVI dashboard has age distribution data from 2018. So we updated that with [this age distribution data](https://www.census.gov/programs-surveys/popest/technical-documentation/research/evaluation-estimates/2020-evaluation-estimates/2010s-county-detail.html) from 2020. The data codebook is [here](https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/2010-2020/cc-est2020-agesex.pdf)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>SUMLEV</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>POPESTIMATE</th>\n",
       "      <th>POPEST_MALE</th>\n",
       "      <th>POPEST_FEM</th>\n",
       "      <th>UNDER5_TOT</th>\n",
       "      <th>UNDER5_MALE</th>\n",
       "      <th>UNDER5_FEM</th>\n",
       "      <th>AGE513_TOT</th>\n",
       "      <th>AGE513_MALE</th>\n",
       "      <th>AGE513_FEM</th>\n",
       "      <th>AGE1417_TOT</th>\n",
       "      <th>AGE1417_MALE</th>\n",
       "      <th>AGE1417_FEM</th>\n",
       "      <th>AGE1824_TOT</th>\n",
       "      <th>AGE1824_MALE</th>\n",
       "      <th>AGE1824_FEM</th>\n",
       "      <th>AGE16PLUS_TOT</th>\n",
       "      <th>AGE16PLUS_MALE</th>\n",
       "      <th>AGE16PLUS_FEM</th>\n",
       "      <th>AGE18PLUS_TOT</th>\n",
       "      <th>AGE18PLUS_MALE</th>\n",
       "      <th>AGE18PLUS_FEM</th>\n",
       "      <th>AGE1544_TOT</th>\n",
       "      <th>AGE1544_MALE</th>\n",
       "      <th>AGE1544_FEM</th>\n",
       "      <th>AGE2544_TOT</th>\n",
       "      <th>AGE2544_MALE</th>\n",
       "      <th>AGE2544_FEM</th>\n",
       "      <th>AGE4564_TOT</th>\n",
       "      <th>AGE4564_MALE</th>\n",
       "      <th>AGE4564_FEM</th>\n",
       "      <th>AGE65PLUS_TOT</th>\n",
       "      <th>AGE65PLUS_MALE</th>\n",
       "      <th>AGE65PLUS_FEM</th>\n",
       "      <th>AGE04_TOT</th>\n",
       "      <th>AGE04_MALE</th>\n",
       "      <th>AGE04_FEM</th>\n",
       "      <th>AGE59_TOT</th>\n",
       "      <th>AGE59_MALE</th>\n",
       "      <th>AGE59_FEM</th>\n",
       "      <th>AGE1014_TOT</th>\n",
       "      <th>AGE1014_MALE</th>\n",
       "      <th>AGE1014_FEM</th>\n",
       "      <th>AGE1519_TOT</th>\n",
       "      <th>AGE1519_MALE</th>\n",
       "      <th>AGE1519_FEM</th>\n",
       "      <th>AGE2024_TOT</th>\n",
       "      <th>AGE2024_MALE</th>\n",
       "      <th>AGE2024_FEM</th>\n",
       "      <th>AGE2529_TOT</th>\n",
       "      <th>AGE2529_MALE</th>\n",
       "      <th>AGE2529_FEM</th>\n",
       "      <th>AGE3034_TOT</th>\n",
       "      <th>AGE3034_MALE</th>\n",
       "      <th>AGE3034_FEM</th>\n",
       "      <th>AGE3539_TOT</th>\n",
       "      <th>AGE3539_MALE</th>\n",
       "      <th>AGE3539_FEM</th>\n",
       "      <th>AGE4044_TOT</th>\n",
       "      <th>AGE4044_MALE</th>\n",
       "      <th>AGE4044_FEM</th>\n",
       "      <th>AGE4549_TOT</th>\n",
       "      <th>AGE4549_MALE</th>\n",
       "      <th>AGE4549_FEM</th>\n",
       "      <th>AGE5054_TOT</th>\n",
       "      <th>AGE5054_MALE</th>\n",
       "      <th>AGE5054_FEM</th>\n",
       "      <th>AGE5559_TOT</th>\n",
       "      <th>AGE5559_MALE</th>\n",
       "      <th>AGE5559_FEM</th>\n",
       "      <th>AGE6064_TOT</th>\n",
       "      <th>AGE6064_MALE</th>\n",
       "      <th>AGE6064_FEM</th>\n",
       "      <th>AGE6569_TOT</th>\n",
       "      <th>AGE6569_MALE</th>\n",
       "      <th>AGE6569_FEM</th>\n",
       "      <th>AGE7074_TOT</th>\n",
       "      <th>AGE7074_MALE</th>\n",
       "      <th>AGE7074_FEM</th>\n",
       "      <th>AGE7579_TOT</th>\n",
       "      <th>AGE7579_MALE</th>\n",
       "      <th>AGE7579_FEM</th>\n",
       "      <th>AGE8084_TOT</th>\n",
       "      <th>AGE8084_MALE</th>\n",
       "      <th>AGE8084_FEM</th>\n",
       "      <th>AGE85PLUS_TOT</th>\n",
       "      <th>AGE85PLUS_MALE</th>\n",
       "      <th>AGE85PLUS_FEM</th>\n",
       "      <th>MEDIAN_AGE_TOT</th>\n",
       "      <th>MEDIAN_AGE_MALE</th>\n",
       "      <th>MEDIAN_AGE_FEM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>1</td>\n",
       "      <td>54571</td>\n",
       "      <td>26569</td>\n",
       "      <td>28002</td>\n",
       "      <td>3579</td>\n",
       "      <td>1866</td>\n",
       "      <td>1713</td>\n",
       "      <td>7418</td>\n",
       "      <td>3747</td>\n",
       "      <td>3671</td>\n",
       "      <td>3616</td>\n",
       "      <td>1842</td>\n",
       "      <td>1774</td>\n",
       "      <td>4617</td>\n",
       "      <td>2335</td>\n",
       "      <td>2282</td>\n",
       "      <td>41804</td>\n",
       "      <td>20046</td>\n",
       "      <td>21758</td>\n",
       "      <td>39958</td>\n",
       "      <td>19114</td>\n",
       "      <td>20844</td>\n",
       "      <td>22100</td>\n",
       "      <td>10867</td>\n",
       "      <td>11233</td>\n",
       "      <td>14730</td>\n",
       "      <td>7115</td>\n",
       "      <td>7615</td>\n",
       "      <td>14065</td>\n",
       "      <td>6843</td>\n",
       "      <td>7222</td>\n",
       "      <td>6546</td>\n",
       "      <td>2821</td>\n",
       "      <td>3725</td>\n",
       "      <td>3579</td>\n",
       "      <td>1866</td>\n",
       "      <td>1713</td>\n",
       "      <td>3991</td>\n",
       "      <td>2001</td>\n",
       "      <td>1990</td>\n",
       "      <td>4290</td>\n",
       "      <td>2171</td>\n",
       "      <td>2119</td>\n",
       "      <td>4290</td>\n",
       "      <td>2213</td>\n",
       "      <td>2077</td>\n",
       "      <td>3080</td>\n",
       "      <td>1539</td>\n",
       "      <td>1541</td>\n",
       "      <td>3157</td>\n",
       "      <td>1543</td>\n",
       "      <td>1614</td>\n",
       "      <td>3330</td>\n",
       "      <td>1594</td>\n",
       "      <td>1736</td>\n",
       "      <td>4157</td>\n",
       "      <td>2004</td>\n",
       "      <td>2153</td>\n",
       "      <td>4086</td>\n",
       "      <td>1974</td>\n",
       "      <td>2112</td>\n",
       "      <td>4332</td>\n",
       "      <td>2174</td>\n",
       "      <td>2158</td>\n",
       "      <td>3873</td>\n",
       "      <td>1866</td>\n",
       "      <td>2007</td>\n",
       "      <td>3083</td>\n",
       "      <td>1524</td>\n",
       "      <td>1559</td>\n",
       "      <td>2777</td>\n",
       "      <td>1279</td>\n",
       "      <td>1498</td>\n",
       "      <td>2277</td>\n",
       "      <td>1014</td>\n",
       "      <td>1263</td>\n",
       "      <td>1736</td>\n",
       "      <td>807</td>\n",
       "      <td>929</td>\n",
       "      <td>1251</td>\n",
       "      <td>546</td>\n",
       "      <td>705</td>\n",
       "      <td>731</td>\n",
       "      <td>295</td>\n",
       "      <td>436</td>\n",
       "      <td>551</td>\n",
       "      <td>159</td>\n",
       "      <td>392</td>\n",
       "      <td>37</td>\n",
       "      <td>35.9</td>\n",
       "      <td>37.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>2</td>\n",
       "      <td>54582</td>\n",
       "      <td>26576</td>\n",
       "      <td>28006</td>\n",
       "      <td>3582</td>\n",
       "      <td>1868</td>\n",
       "      <td>1714</td>\n",
       "      <td>7425</td>\n",
       "      <td>3752</td>\n",
       "      <td>3673</td>\n",
       "      <td>3617</td>\n",
       "      <td>1842</td>\n",
       "      <td>1775</td>\n",
       "      <td>4617</td>\n",
       "      <td>2335</td>\n",
       "      <td>2282</td>\n",
       "      <td>41804</td>\n",
       "      <td>20045</td>\n",
       "      <td>21759</td>\n",
       "      <td>39958</td>\n",
       "      <td>19114</td>\n",
       "      <td>20844</td>\n",
       "      <td>22103</td>\n",
       "      <td>10867</td>\n",
       "      <td>11236</td>\n",
       "      <td>14732</td>\n",
       "      <td>7115</td>\n",
       "      <td>7617</td>\n",
       "      <td>14067</td>\n",
       "      <td>6845</td>\n",
       "      <td>7222</td>\n",
       "      <td>6542</td>\n",
       "      <td>2819</td>\n",
       "      <td>3723</td>\n",
       "      <td>3582</td>\n",
       "      <td>1868</td>\n",
       "      <td>1714</td>\n",
       "      <td>3994</td>\n",
       "      <td>2004</td>\n",
       "      <td>1990</td>\n",
       "      <td>4294</td>\n",
       "      <td>2173</td>\n",
       "      <td>2121</td>\n",
       "      <td>4291</td>\n",
       "      <td>2213</td>\n",
       "      <td>2078</td>\n",
       "      <td>3080</td>\n",
       "      <td>1539</td>\n",
       "      <td>1541</td>\n",
       "      <td>3160</td>\n",
       "      <td>1545</td>\n",
       "      <td>1615</td>\n",
       "      <td>3329</td>\n",
       "      <td>1593</td>\n",
       "      <td>1736</td>\n",
       "      <td>4157</td>\n",
       "      <td>2004</td>\n",
       "      <td>2153</td>\n",
       "      <td>4086</td>\n",
       "      <td>1973</td>\n",
       "      <td>2113</td>\n",
       "      <td>4333</td>\n",
       "      <td>2176</td>\n",
       "      <td>2157</td>\n",
       "      <td>3874</td>\n",
       "      <td>1866</td>\n",
       "      <td>2008</td>\n",
       "      <td>3083</td>\n",
       "      <td>1524</td>\n",
       "      <td>1559</td>\n",
       "      <td>2777</td>\n",
       "      <td>1279</td>\n",
       "      <td>1498</td>\n",
       "      <td>2277</td>\n",
       "      <td>1014</td>\n",
       "      <td>1263</td>\n",
       "      <td>1734</td>\n",
       "      <td>806</td>\n",
       "      <td>928</td>\n",
       "      <td>1250</td>\n",
       "      <td>546</td>\n",
       "      <td>704</td>\n",
       "      <td>730</td>\n",
       "      <td>294</td>\n",
       "      <td>436</td>\n",
       "      <td>551</td>\n",
       "      <td>159</td>\n",
       "      <td>392</td>\n",
       "      <td>37</td>\n",
       "      <td>35.9</td>\n",
       "      <td>37.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>3</td>\n",
       "      <td>54761</td>\n",
       "      <td>26667</td>\n",
       "      <td>28094</td>\n",
       "      <td>3575</td>\n",
       "      <td>1862</td>\n",
       "      <td>1713</td>\n",
       "      <td>7400</td>\n",
       "      <td>3720</td>\n",
       "      <td>3680</td>\n",
       "      <td>3565</td>\n",
       "      <td>1819</td>\n",
       "      <td>1746</td>\n",
       "      <td>4670</td>\n",
       "      <td>2362</td>\n",
       "      <td>2308</td>\n",
       "      <td>42038</td>\n",
       "      <td>20190</td>\n",
       "      <td>21848</td>\n",
       "      <td>40221</td>\n",
       "      <td>19266</td>\n",
       "      <td>20955</td>\n",
       "      <td>22194</td>\n",
       "      <td>10939</td>\n",
       "      <td>11255</td>\n",
       "      <td>14815</td>\n",
       "      <td>7185</td>\n",
       "      <td>7630</td>\n",
       "      <td>14137</td>\n",
       "      <td>6872</td>\n",
       "      <td>7265</td>\n",
       "      <td>6599</td>\n",
       "      <td>2847</td>\n",
       "      <td>3752</td>\n",
       "      <td>3575</td>\n",
       "      <td>1862</td>\n",
       "      <td>1713</td>\n",
       "      <td>3964</td>\n",
       "      <td>1984</td>\n",
       "      <td>1980</td>\n",
       "      <td>4292</td>\n",
       "      <td>2163</td>\n",
       "      <td>2129</td>\n",
       "      <td>4228</td>\n",
       "      <td>2181</td>\n",
       "      <td>2047</td>\n",
       "      <td>3151</td>\n",
       "      <td>1573</td>\n",
       "      <td>1578</td>\n",
       "      <td>3188</td>\n",
       "      <td>1573</td>\n",
       "      <td>1615</td>\n",
       "      <td>3369</td>\n",
       "      <td>1614</td>\n",
       "      <td>1755</td>\n",
       "      <td>4142</td>\n",
       "      <td>2000</td>\n",
       "      <td>2142</td>\n",
       "      <td>4116</td>\n",
       "      <td>1998</td>\n",
       "      <td>2118</td>\n",
       "      <td>4321</td>\n",
       "      <td>2163</td>\n",
       "      <td>2158</td>\n",
       "      <td>3908</td>\n",
       "      <td>1884</td>\n",
       "      <td>2024</td>\n",
       "      <td>3119</td>\n",
       "      <td>1533</td>\n",
       "      <td>1586</td>\n",
       "      <td>2789</td>\n",
       "      <td>1292</td>\n",
       "      <td>1497</td>\n",
       "      <td>2289</td>\n",
       "      <td>1018</td>\n",
       "      <td>1271</td>\n",
       "      <td>1752</td>\n",
       "      <td>814</td>\n",
       "      <td>938</td>\n",
       "      <td>1259</td>\n",
       "      <td>552</td>\n",
       "      <td>707</td>\n",
       "      <td>743</td>\n",
       "      <td>299</td>\n",
       "      <td>444</td>\n",
       "      <td>556</td>\n",
       "      <td>164</td>\n",
       "      <td>392</td>\n",
       "      <td>37.1</td>\n",
       "      <td>36</td>\n",
       "      <td>37.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUMLEV  STATE  COUNTY   STNAME         CTYNAME  YEAR POPESTIMATE  \\\n",
       "0      50      1       1  Alabama  Autauga County     1       54571   \n",
       "1      50      1       1  Alabama  Autauga County     2       54582   \n",
       "2      50      1       1  Alabama  Autauga County     3       54761   \n",
       "\n",
       "  POPEST_MALE POPEST_FEM UNDER5_TOT UNDER5_MALE UNDER5_FEM AGE513_TOT  \\\n",
       "0       26569      28002       3579        1866       1713       7418   \n",
       "1       26576      28006       3582        1868       1714       7425   \n",
       "2       26667      28094       3575        1862       1713       7400   \n",
       "\n",
       "  AGE513_MALE AGE513_FEM AGE1417_TOT AGE1417_MALE AGE1417_FEM AGE1824_TOT  \\\n",
       "0        3747       3671        3616         1842        1774        4617   \n",
       "1        3752       3673        3617         1842        1775        4617   \n",
       "2        3720       3680        3565         1819        1746        4670   \n",
       "\n",
       "  AGE1824_MALE AGE1824_FEM AGE16PLUS_TOT AGE16PLUS_MALE AGE16PLUS_FEM  \\\n",
       "0         2335        2282         41804          20046         21758   \n",
       "1         2335        2282         41804          20045         21759   \n",
       "2         2362        2308         42038          20190         21848   \n",
       "\n",
       "  AGE18PLUS_TOT AGE18PLUS_MALE AGE18PLUS_FEM AGE1544_TOT AGE1544_MALE  \\\n",
       "0         39958          19114         20844       22100        10867   \n",
       "1         39958          19114         20844       22103        10867   \n",
       "2         40221          19266         20955       22194        10939   \n",
       "\n",
       "  AGE1544_FEM AGE2544_TOT AGE2544_MALE AGE2544_FEM AGE4564_TOT AGE4564_MALE  \\\n",
       "0       11233       14730         7115        7615       14065         6843   \n",
       "1       11236       14732         7115        7617       14067         6845   \n",
       "2       11255       14815         7185        7630       14137         6872   \n",
       "\n",
       "  AGE4564_FEM AGE65PLUS_TOT AGE65PLUS_MALE AGE65PLUS_FEM AGE04_TOT AGE04_MALE  \\\n",
       "0        7222          6546           2821          3725      3579       1866   \n",
       "1        7222          6542           2819          3723      3582       1868   \n",
       "2        7265          6599           2847          3752      3575       1862   \n",
       "\n",
       "  AGE04_FEM AGE59_TOT AGE59_MALE AGE59_FEM AGE1014_TOT AGE1014_MALE  \\\n",
       "0      1713      3991       2001      1990        4290         2171   \n",
       "1      1714      3994       2004      1990        4294         2173   \n",
       "2      1713      3964       1984      1980        4292         2163   \n",
       "\n",
       "  AGE1014_FEM AGE1519_TOT AGE1519_MALE AGE1519_FEM AGE2024_TOT AGE2024_MALE  \\\n",
       "0        2119        4290         2213        2077        3080         1539   \n",
       "1        2121        4291         2213        2078        3080         1539   \n",
       "2        2129        4228         2181        2047        3151         1573   \n",
       "\n",
       "  AGE2024_FEM AGE2529_TOT AGE2529_MALE AGE2529_FEM AGE3034_TOT AGE3034_MALE  \\\n",
       "0        1541        3157         1543        1614        3330         1594   \n",
       "1        1541        3160         1545        1615        3329         1593   \n",
       "2        1578        3188         1573        1615        3369         1614   \n",
       "\n",
       "  AGE3034_FEM AGE3539_TOT AGE3539_MALE AGE3539_FEM AGE4044_TOT AGE4044_MALE  \\\n",
       "0        1736        4157         2004        2153        4086         1974   \n",
       "1        1736        4157         2004        2153        4086         1973   \n",
       "2        1755        4142         2000        2142        4116         1998   \n",
       "\n",
       "  AGE4044_FEM AGE4549_TOT AGE4549_MALE AGE4549_FEM AGE5054_TOT AGE5054_MALE  \\\n",
       "0        2112        4332         2174        2158        3873         1866   \n",
       "1        2113        4333         2176        2157        3874         1866   \n",
       "2        2118        4321         2163        2158        3908         1884   \n",
       "\n",
       "  AGE5054_FEM AGE5559_TOT AGE5559_MALE AGE5559_FEM AGE6064_TOT AGE6064_MALE  \\\n",
       "0        2007        3083         1524        1559        2777         1279   \n",
       "1        2008        3083         1524        1559        2777         1279   \n",
       "2        2024        3119         1533        1586        2789         1292   \n",
       "\n",
       "  AGE6064_FEM AGE6569_TOT AGE6569_MALE AGE6569_FEM AGE7074_TOT AGE7074_MALE  \\\n",
       "0        1498        2277         1014        1263        1736          807   \n",
       "1        1498        2277         1014        1263        1734          806   \n",
       "2        1497        2289         1018        1271        1752          814   \n",
       "\n",
       "  AGE7074_FEM AGE7579_TOT AGE7579_MALE AGE7579_FEM AGE8084_TOT AGE8084_MALE  \\\n",
       "0         929        1251          546         705         731          295   \n",
       "1         928        1250          546         704         730          294   \n",
       "2         938        1259          552         707         743          299   \n",
       "\n",
       "  AGE8084_FEM AGE85PLUS_TOT AGE85PLUS_MALE AGE85PLUS_FEM MEDIAN_AGE_TOT  \\\n",
       "0         436           551            159           392             37   \n",
       "1         436           551            159           392             37   \n",
       "2         444           556            164           392           37.1   \n",
       "\n",
       "  MEDIAN_AGE_MALE MEDIAN_AGE_FEM  \n",
       "0            35.9           37.9  \n",
       "1            35.9           37.8  \n",
       "2              36           37.9  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = 'https://www2.census.gov/programs-surveys/popest/datasets/2010-2020/counties/asrh/CC-EST2020-AGESEX-ALL.csv'\n",
    "response = requests.get(url)\n",
    "file_object = io.StringIO(response.content.decode('latin1')) # contains letters undecodable for utf-8\n",
    "df = pd.read_csv(file_object, low_memory=False) # low_memory=False because features have mixed types\n",
    "\n",
    "# in case the download by code is not working\n",
    "# df = pd.read_csv(f'{output_folder}/CC-EST2020-AGESEX-ALL.csv', encoding='latin1', low_memory=False)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>Name</th>\n",
       "      <th>POPESTIMATE</th>\n",
       "      <th>POPEST_MALE</th>\n",
       "      <th>POPEST_FEM</th>\n",
       "      <th>UNDER5_TOT</th>\n",
       "      <th>UNDER5_MALE</th>\n",
       "      <th>UNDER5_FEM</th>\n",
       "      <th>AGE513_TOT</th>\n",
       "      <th>AGE513_MALE</th>\n",
       "      <th>AGE513_FEM</th>\n",
       "      <th>AGE1417_TOT</th>\n",
       "      <th>AGE1417_MALE</th>\n",
       "      <th>AGE1417_FEM</th>\n",
       "      <th>AGE1824_TOT</th>\n",
       "      <th>AGE1824_MALE</th>\n",
       "      <th>AGE1824_FEM</th>\n",
       "      <th>AGE16PLUS_TOT</th>\n",
       "      <th>AGE16PLUS_MALE</th>\n",
       "      <th>AGE16PLUS_FEM</th>\n",
       "      <th>AGE18PLUS_TOT</th>\n",
       "      <th>AGE18PLUS_MALE</th>\n",
       "      <th>AGE18PLUS_FEM</th>\n",
       "      <th>AGE1544_TOT</th>\n",
       "      <th>AGE1544_MALE</th>\n",
       "      <th>AGE1544_FEM</th>\n",
       "      <th>AGE2544_TOT</th>\n",
       "      <th>AGE2544_MALE</th>\n",
       "      <th>AGE2544_FEM</th>\n",
       "      <th>AGE4564_TOT</th>\n",
       "      <th>AGE4564_MALE</th>\n",
       "      <th>AGE4564_FEM</th>\n",
       "      <th>AGE65PLUS_TOT</th>\n",
       "      <th>AGE65PLUS_MALE</th>\n",
       "      <th>AGE65PLUS_FEM</th>\n",
       "      <th>AGE04_TOT</th>\n",
       "      <th>AGE04_MALE</th>\n",
       "      <th>AGE04_FEM</th>\n",
       "      <th>AGE59_TOT</th>\n",
       "      <th>AGE59_MALE</th>\n",
       "      <th>AGE59_FEM</th>\n",
       "      <th>AGE1014_TOT</th>\n",
       "      <th>AGE1014_MALE</th>\n",
       "      <th>AGE1014_FEM</th>\n",
       "      <th>AGE1519_TOT</th>\n",
       "      <th>AGE1519_MALE</th>\n",
       "      <th>AGE1519_FEM</th>\n",
       "      <th>AGE2024_TOT</th>\n",
       "      <th>AGE2024_MALE</th>\n",
       "      <th>AGE2024_FEM</th>\n",
       "      <th>AGE2529_TOT</th>\n",
       "      <th>AGE2529_MALE</th>\n",
       "      <th>AGE2529_FEM</th>\n",
       "      <th>AGE3034_TOT</th>\n",
       "      <th>AGE3034_MALE</th>\n",
       "      <th>AGE3034_FEM</th>\n",
       "      <th>AGE3539_TOT</th>\n",
       "      <th>AGE3539_MALE</th>\n",
       "      <th>AGE3539_FEM</th>\n",
       "      <th>AGE4044_TOT</th>\n",
       "      <th>AGE4044_MALE</th>\n",
       "      <th>AGE4044_FEM</th>\n",
       "      <th>AGE4549_TOT</th>\n",
       "      <th>AGE4549_MALE</th>\n",
       "      <th>AGE4549_FEM</th>\n",
       "      <th>AGE5054_TOT</th>\n",
       "      <th>AGE5054_MALE</th>\n",
       "      <th>AGE5054_FEM</th>\n",
       "      <th>AGE5559_TOT</th>\n",
       "      <th>AGE5559_MALE</th>\n",
       "      <th>AGE5559_FEM</th>\n",
       "      <th>AGE6064_TOT</th>\n",
       "      <th>AGE6064_MALE</th>\n",
       "      <th>AGE6064_FEM</th>\n",
       "      <th>AGE6569_TOT</th>\n",
       "      <th>AGE6569_MALE</th>\n",
       "      <th>AGE6569_FEM</th>\n",
       "      <th>AGE7074_TOT</th>\n",
       "      <th>AGE7074_MALE</th>\n",
       "      <th>AGE7074_FEM</th>\n",
       "      <th>AGE7579_TOT</th>\n",
       "      <th>AGE7579_MALE</th>\n",
       "      <th>AGE7579_FEM</th>\n",
       "      <th>AGE8084_TOT</th>\n",
       "      <th>AGE8084_MALE</th>\n",
       "      <th>AGE8084_FEM</th>\n",
       "      <th>AGE85PLUS_TOT</th>\n",
       "      <th>AGE85PLUS_MALE</th>\n",
       "      <th>AGE85PLUS_FEM</th>\n",
       "      <th>MEDIAN_AGE_TOT</th>\n",
       "      <th>MEDIAN_AGE_MALE</th>\n",
       "      <th>MEDIAN_AGE_FEM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>Alabama, Autauga</td>\n",
       "      <td>56130</td>\n",
       "      <td>27231</td>\n",
       "      <td>28899</td>\n",
       "      <td>3346</td>\n",
       "      <td>1739</td>\n",
       "      <td>1607</td>\n",
       "      <td>6566</td>\n",
       "      <td>3366</td>\n",
       "      <td>3200</td>\n",
       "      <td>3114</td>\n",
       "      <td>1553</td>\n",
       "      <td>1561</td>\n",
       "      <td>4416</td>\n",
       "      <td>2267</td>\n",
       "      <td>2149</td>\n",
       "      <td>44657</td>\n",
       "      <td>21353</td>\n",
       "      <td>23304</td>\n",
       "      <td>43104</td>\n",
       "      <td>20573</td>\n",
       "      <td>22531</td>\n",
       "      <td>21567</td>\n",
       "      <td>10606</td>\n",
       "      <td>10961</td>\n",
       "      <td>14798</td>\n",
       "      <td>7166</td>\n",
       "      <td>7632</td>\n",
       "      <td>14848</td>\n",
       "      <td>7161</td>\n",
       "      <td>7687</td>\n",
       "      <td>9042</td>\n",
       "      <td>3979</td>\n",
       "      <td>5063</td>\n",
       "      <td>3346</td>\n",
       "      <td>1739</td>\n",
       "      <td>1607</td>\n",
       "      <td>3494</td>\n",
       "      <td>1785</td>\n",
       "      <td>1709</td>\n",
       "      <td>3833</td>\n",
       "      <td>1961</td>\n",
       "      <td>1872</td>\n",
       "      <td>3622</td>\n",
       "      <td>1825</td>\n",
       "      <td>1797</td>\n",
       "      <td>3147</td>\n",
       "      <td>1615</td>\n",
       "      <td>1532</td>\n",
       "      <td>3822</td>\n",
       "      <td>1893</td>\n",
       "      <td>1929</td>\n",
       "      <td>3636</td>\n",
       "      <td>1756</td>\n",
       "      <td>1880</td>\n",
       "      <td>3743</td>\n",
       "      <td>1818</td>\n",
       "      <td>1925</td>\n",
       "      <td>3597</td>\n",
       "      <td>1699</td>\n",
       "      <td>1898</td>\n",
       "      <td>3820</td>\n",
       "      <td>1844</td>\n",
       "      <td>1976</td>\n",
       "      <td>3675</td>\n",
       "      <td>1811</td>\n",
       "      <td>1864</td>\n",
       "      <td>3912</td>\n",
       "      <td>1915</td>\n",
       "      <td>1997</td>\n",
       "      <td>3441</td>\n",
       "      <td>1591</td>\n",
       "      <td>1850</td>\n",
       "      <td>2790</td>\n",
       "      <td>1351</td>\n",
       "      <td>1439</td>\n",
       "      <td>2386</td>\n",
       "      <td>1032</td>\n",
       "      <td>1354</td>\n",
       "      <td>1796</td>\n",
       "      <td>749</td>\n",
       "      <td>1047</td>\n",
       "      <td>1142</td>\n",
       "      <td>494</td>\n",
       "      <td>648</td>\n",
       "      <td>928</td>\n",
       "      <td>353</td>\n",
       "      <td>575</td>\n",
       "      <td>39.3</td>\n",
       "      <td>37.8</td>\n",
       "      <td>40.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>Alabama, Baldwin</td>\n",
       "      <td>227989</td>\n",
       "      <td>110420</td>\n",
       "      <td>117569</td>\n",
       "      <td>12092</td>\n",
       "      <td>6319</td>\n",
       "      <td>5773</td>\n",
       "      <td>24636</td>\n",
       "      <td>12572</td>\n",
       "      <td>12064</td>\n",
       "      <td>11536</td>\n",
       "      <td>5787</td>\n",
       "      <td>5749</td>\n",
       "      <td>16067</td>\n",
       "      <td>8055</td>\n",
       "      <td>8012</td>\n",
       "      <td>185376</td>\n",
       "      <td>88563</td>\n",
       "      <td>96813</td>\n",
       "      <td>179725</td>\n",
       "      <td>85742</td>\n",
       "      <td>93983</td>\n",
       "      <td>77312</td>\n",
       "      <td>38054</td>\n",
       "      <td>39258</td>\n",
       "      <td>52651</td>\n",
       "      <td>25693</td>\n",
       "      <td>26958</td>\n",
       "      <td>62077</td>\n",
       "      <td>29384</td>\n",
       "      <td>32693</td>\n",
       "      <td>48930</td>\n",
       "      <td>22610</td>\n",
       "      <td>26320</td>\n",
       "      <td>12092</td>\n",
       "      <td>6319</td>\n",
       "      <td>5773</td>\n",
       "      <td>13356</td>\n",
       "      <td>6874</td>\n",
       "      <td>6482</td>\n",
       "      <td>14222</td>\n",
       "      <td>7179</td>\n",
       "      <td>7043</td>\n",
       "      <td>13371</td>\n",
       "      <td>6684</td>\n",
       "      <td>6687</td>\n",
       "      <td>11290</td>\n",
       "      <td>5677</td>\n",
       "      <td>5613</td>\n",
       "      <td>12517</td>\n",
       "      <td>6254</td>\n",
       "      <td>6263</td>\n",
       "      <td>12865</td>\n",
       "      <td>6256</td>\n",
       "      <td>6609</td>\n",
       "      <td>13524</td>\n",
       "      <td>6579</td>\n",
       "      <td>6945</td>\n",
       "      <td>13745</td>\n",
       "      <td>6604</td>\n",
       "      <td>7141</td>\n",
       "      <td>14328</td>\n",
       "      <td>6941</td>\n",
       "      <td>7387</td>\n",
       "      <td>14519</td>\n",
       "      <td>6963</td>\n",
       "      <td>7556</td>\n",
       "      <td>16420</td>\n",
       "      <td>7756</td>\n",
       "      <td>8664</td>\n",
       "      <td>16810</td>\n",
       "      <td>7724</td>\n",
       "      <td>9086</td>\n",
       "      <td>15519</td>\n",
       "      <td>7146</td>\n",
       "      <td>8373</td>\n",
       "      <td>13806</td>\n",
       "      <td>6532</td>\n",
       "      <td>7274</td>\n",
       "      <td>9457</td>\n",
       "      <td>4503</td>\n",
       "      <td>4954</td>\n",
       "      <td>5607</td>\n",
       "      <td>2554</td>\n",
       "      <td>3053</td>\n",
       "      <td>4541</td>\n",
       "      <td>1875</td>\n",
       "      <td>2666</td>\n",
       "      <td>43.8</td>\n",
       "      <td>42.5</td>\n",
       "      <td>45.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>Alabama, Barbour</td>\n",
       "      <td>24652</td>\n",
       "      <td>13018</td>\n",
       "      <td>11634</td>\n",
       "      <td>1315</td>\n",
       "      <td>673</td>\n",
       "      <td>642</td>\n",
       "      <td>2574</td>\n",
       "      <td>1275</td>\n",
       "      <td>1299</td>\n",
       "      <td>1164</td>\n",
       "      <td>593</td>\n",
       "      <td>571</td>\n",
       "      <td>1992</td>\n",
       "      <td>1162</td>\n",
       "      <td>830</td>\n",
       "      <td>20183</td>\n",
       "      <td>10777</td>\n",
       "      <td>9406</td>\n",
       "      <td>19599</td>\n",
       "      <td>10477</td>\n",
       "      <td>9122</td>\n",
       "      <td>9300</td>\n",
       "      <td>5525</td>\n",
       "      <td>3775</td>\n",
       "      <td>6441</td>\n",
       "      <td>3917</td>\n",
       "      <td>2524</td>\n",
       "      <td>6188</td>\n",
       "      <td>3234</td>\n",
       "      <td>2954</td>\n",
       "      <td>4978</td>\n",
       "      <td>2164</td>\n",
       "      <td>2814</td>\n",
       "      <td>1315</td>\n",
       "      <td>673</td>\n",
       "      <td>642</td>\n",
       "      <td>1351</td>\n",
       "      <td>659</td>\n",
       "      <td>692</td>\n",
       "      <td>1520</td>\n",
       "      <td>763</td>\n",
       "      <td>757</td>\n",
       "      <td>1394</td>\n",
       "      <td>726</td>\n",
       "      <td>668</td>\n",
       "      <td>1465</td>\n",
       "      <td>882</td>\n",
       "      <td>583</td>\n",
       "      <td>1762</td>\n",
       "      <td>1113</td>\n",
       "      <td>649</td>\n",
       "      <td>1648</td>\n",
       "      <td>1004</td>\n",
       "      <td>644</td>\n",
       "      <td>1559</td>\n",
       "      <td>899</td>\n",
       "      <td>660</td>\n",
       "      <td>1472</td>\n",
       "      <td>901</td>\n",
       "      <td>571</td>\n",
       "      <td>1552</td>\n",
       "      <td>869</td>\n",
       "      <td>683</td>\n",
       "      <td>1467</td>\n",
       "      <td>795</td>\n",
       "      <td>672</td>\n",
       "      <td>1659</td>\n",
       "      <td>827</td>\n",
       "      <td>832</td>\n",
       "      <td>1510</td>\n",
       "      <td>743</td>\n",
       "      <td>767</td>\n",
       "      <td>1499</td>\n",
       "      <td>689</td>\n",
       "      <td>810</td>\n",
       "      <td>1406</td>\n",
       "      <td>619</td>\n",
       "      <td>787</td>\n",
       "      <td>971</td>\n",
       "      <td>439</td>\n",
       "      <td>532</td>\n",
       "      <td>587</td>\n",
       "      <td>245</td>\n",
       "      <td>342</td>\n",
       "      <td>515</td>\n",
       "      <td>172</td>\n",
       "      <td>343</td>\n",
       "      <td>40.9</td>\n",
       "      <td>38.9</td>\n",
       "      <td>44.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    STATE  COUNTY  FIPS   STNAME         CTYNAME              Name  \\\n",
       "12      1       1  1001  Alabama  Autauga County  Alabama, Autauga   \n",
       "26      1       3  1003  Alabama  Baldwin County  Alabama, Baldwin   \n",
       "40      1       5  1005  Alabama  Barbour County  Alabama, Barbour   \n",
       "\n",
       "   POPESTIMATE POPEST_MALE POPEST_FEM UNDER5_TOT UNDER5_MALE UNDER5_FEM  \\\n",
       "12       56130       27231      28899       3346        1739       1607   \n",
       "26      227989      110420     117569      12092        6319       5773   \n",
       "40       24652       13018      11634       1315         673        642   \n",
       "\n",
       "   AGE513_TOT AGE513_MALE AGE513_FEM AGE1417_TOT AGE1417_MALE AGE1417_FEM  \\\n",
       "12       6566        3366       3200        3114         1553        1561   \n",
       "26      24636       12572      12064       11536         5787        5749   \n",
       "40       2574        1275       1299        1164          593         571   \n",
       "\n",
       "   AGE1824_TOT AGE1824_MALE AGE1824_FEM AGE16PLUS_TOT AGE16PLUS_MALE  \\\n",
       "12        4416         2267        2149         44657          21353   \n",
       "26       16067         8055        8012        185376          88563   \n",
       "40        1992         1162         830         20183          10777   \n",
       "\n",
       "   AGE16PLUS_FEM AGE18PLUS_TOT AGE18PLUS_MALE AGE18PLUS_FEM AGE1544_TOT  \\\n",
       "12         23304         43104          20573         22531       21567   \n",
       "26         96813        179725          85742         93983       77312   \n",
       "40          9406         19599          10477          9122        9300   \n",
       "\n",
       "   AGE1544_MALE AGE1544_FEM AGE2544_TOT AGE2544_MALE AGE2544_FEM AGE4564_TOT  \\\n",
       "12        10606       10961       14798         7166        7632       14848   \n",
       "26        38054       39258       52651        25693       26958       62077   \n",
       "40         5525        3775        6441         3917        2524        6188   \n",
       "\n",
       "   AGE4564_MALE AGE4564_FEM AGE65PLUS_TOT AGE65PLUS_MALE AGE65PLUS_FEM  \\\n",
       "12         7161        7687          9042           3979          5063   \n",
       "26        29384       32693         48930          22610         26320   \n",
       "40         3234        2954          4978           2164          2814   \n",
       "\n",
       "   AGE04_TOT AGE04_MALE AGE04_FEM AGE59_TOT AGE59_MALE AGE59_FEM AGE1014_TOT  \\\n",
       "12      3346       1739      1607      3494       1785      1709        3833   \n",
       "26     12092       6319      5773     13356       6874      6482       14222   \n",
       "40      1315        673       642      1351        659       692        1520   \n",
       "\n",
       "   AGE1014_MALE AGE1014_FEM AGE1519_TOT AGE1519_MALE AGE1519_FEM AGE2024_TOT  \\\n",
       "12         1961        1872        3622         1825        1797        3147   \n",
       "26         7179        7043       13371         6684        6687       11290   \n",
       "40          763         757        1394          726         668        1465   \n",
       "\n",
       "   AGE2024_MALE AGE2024_FEM AGE2529_TOT AGE2529_MALE AGE2529_FEM AGE3034_TOT  \\\n",
       "12         1615        1532        3822         1893        1929        3636   \n",
       "26         5677        5613       12517         6254        6263       12865   \n",
       "40          882         583        1762         1113         649        1648   \n",
       "\n",
       "   AGE3034_MALE AGE3034_FEM AGE3539_TOT AGE3539_MALE AGE3539_FEM AGE4044_TOT  \\\n",
       "12         1756        1880        3743         1818        1925        3597   \n",
       "26         6256        6609       13524         6579        6945       13745   \n",
       "40         1004         644        1559          899         660        1472   \n",
       "\n",
       "   AGE4044_MALE AGE4044_FEM AGE4549_TOT AGE4549_MALE AGE4549_FEM AGE5054_TOT  \\\n",
       "12         1699        1898        3820         1844        1976        3675   \n",
       "26         6604        7141       14328         6941        7387       14519   \n",
       "40          901         571        1552          869         683        1467   \n",
       "\n",
       "   AGE5054_MALE AGE5054_FEM AGE5559_TOT AGE5559_MALE AGE5559_FEM AGE6064_TOT  \\\n",
       "12         1811        1864        3912         1915        1997        3441   \n",
       "26         6963        7556       16420         7756        8664       16810   \n",
       "40          795         672        1659          827         832        1510   \n",
       "\n",
       "   AGE6064_MALE AGE6064_FEM AGE6569_TOT AGE6569_MALE AGE6569_FEM AGE7074_TOT  \\\n",
       "12         1591        1850        2790         1351        1439        2386   \n",
       "26         7724        9086       15519         7146        8373       13806   \n",
       "40          743         767        1499          689         810        1406   \n",
       "\n",
       "   AGE7074_MALE AGE7074_FEM AGE7579_TOT AGE7579_MALE AGE7579_FEM AGE8084_TOT  \\\n",
       "12         1032        1354        1796          749        1047        1142   \n",
       "26         6532        7274        9457         4503        4954        5607   \n",
       "40          619         787         971          439         532         587   \n",
       "\n",
       "   AGE8084_MALE AGE8084_FEM AGE85PLUS_TOT AGE85PLUS_MALE AGE85PLUS_FEM  \\\n",
       "12          494         648           928            353           575   \n",
       "26         2554        3053          4541           1875          2666   \n",
       "40          245         342           515            172           343   \n",
       "\n",
       "   MEDIAN_AGE_TOT MEDIAN_AGE_MALE MEDIAN_AGE_FEM  \n",
       "12           39.3            37.8           40.5  \n",
       "26           43.8            42.5           45.2  \n",
       "40           40.9            38.9           44.5  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[df['YEAR']==13] # category 13 is for year 2020 estimation\n",
    "df.drop(columns=['SUMLEV', 'YEAR'], inplace=True)\n",
    "df['Name'] = df['STNAME'] + ', ' + df['CTYNAME'].apply(lambda x: x.replace(' County', ''))\n",
    "df['FIPS'] = df['STATE'] * 1000 + df['COUNTY']\n",
    "\n",
    "identity_columns = ['STATE','COUNTY','FIPS','STNAME','CTYNAME','Name']\n",
    "other_columns = [col for col in df.columns if col not in identity_columns]\n",
    "df = df[identity_columns+other_columns]\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# https://www.census.gov/programs-surveys/geography/technical-documentation/county-changes/2010.html\n",
    "if df[df['FIPS'].isin([2063, 2066])].shape[0] == 2:\n",
    "    values = df[df['FIPS']==2063][other_columns].values[0] + df[df['FIPS']==2066][other_columns].values[0]\n",
    "    df.loc[len(df.index)] = [2, 261, 2261, 'Alaska', 'Valdez-Cordova', 'Valdez-Cordova County'] + list(values)\n",
    "    df = df[~df['FIPS'].isin([2063, 2066])]\n",
    "\n",
    "    df = df.sort_values(by=['FIPS'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(f'{support_folder}/Population.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sovial Vulnerability Index\n",
    "If the downloading bellow doesn't work, just directly go to [this link](https://www.atsdr.cdc.gov/placeandhealth/svi/data_documentation_download.html), select geography type counties for United States and download the csv file for year 2020. Then load that csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://svi.cdc.gov/Documents/Data/2020_SVI_Data/CSV/SVI2020_US_COUNTY.csv'\n",
    "response = requests.get(url)\n",
    "file_object = io.StringIO(response.content.decode('utf-8'))\n",
    "\n",
    "svi_filename = url.split('/')[-1]\n",
    "df = pd.read_csv(file_object, low_memory=False)\n",
    "df.to_csv(f\"{support_folder}/{svi_filename}\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>FIPS</th>\n",
       "      <th>AgeDist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1001</td>\n",
       "      <td>0.161090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1003</td>\n",
       "      <td>0.214616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1005</td>\n",
       "      <td>0.201931</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FIPS   AgeDist\n",
       "0  1001  0.161090\n",
       "1  1003  0.214616\n",
       "2  1005  0.201931"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(f'{support_folder}/Population.csv')\n",
    "df['AgeDist'] = df['AGE65PLUS_TOT'].astype(int) / df['POPESTIMATE'].astype(int)\n",
    "# PVI data came min max scaled\n",
    "df = df[['FIPS', 'AgeDist']]\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.round(4).to_csv(f'{output_folder}/Age Distribution.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Health disparity\n",
    "\n",
    "Corresponding column names in the SVI file is mentioned in ().\n",
    "\n",
    "| Component(s)\t| Update Freq.\t| Description/Rationale | Source |\n",
    "|:---:|:---:|:---:|:---:|\n",
    "| Uninsured|\tStatic\t|\tPercentage uninsured in the total civilian noninstitutionalized population estimate (EP_UNINSUR). Individuals without insurance are more likely to be undercounted in infection statistics, and may have more severe outcomes due to lack of treatment.|\t[2020 SVI](https://www.atsdr.cdc.gov/placeandhealth/svi/data_documentation_download.html)|\n",
    "| Socioeconomic Status|\tStatic\t|\tIntegrates data on estimated percent below poverty (EPL_POV150), percent civilian (age 16+) unemployed (historical) (EPL_UNEMP), housing cost-burdened occupied housing units (EPL_HBURD), income (EPL_PCI), and percent without a high school diploma (EPL_NOHSDP). Lower SES are more likely to be undercounted in infection statistics, and may have more severe outcomes due to lack of treatment.|\t[2020 SVI](https://www.atsdr.cdc.gov/placeandhealth/svi/data_documentation_download.html)|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ST</th>\n",
       "      <th>STATE</th>\n",
       "      <th>ST_ABBR</th>\n",
       "      <th>STCNTY</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>FIPS</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>AREA_SQMI</th>\n",
       "      <th>E_TOTPOP</th>\n",
       "      <th>M_TOTPOP</th>\n",
       "      <th>E_HU</th>\n",
       "      <th>M_HU</th>\n",
       "      <th>E_HH</th>\n",
       "      <th>M_HH</th>\n",
       "      <th>E_POV150</th>\n",
       "      <th>M_POV150</th>\n",
       "      <th>E_UNEMP</th>\n",
       "      <th>M_UNEMP</th>\n",
       "      <th>E_HBURD</th>\n",
       "      <th>M_HBURD</th>\n",
       "      <th>E_NOHSDP</th>\n",
       "      <th>M_NOHSDP</th>\n",
       "      <th>E_UNINSUR</th>\n",
       "      <th>M_UNINSUR</th>\n",
       "      <th>E_AGE65</th>\n",
       "      <th>M_AGE65</th>\n",
       "      <th>E_AGE17</th>\n",
       "      <th>M_AGE17</th>\n",
       "      <th>E_DISABL</th>\n",
       "      <th>M_DISABL</th>\n",
       "      <th>E_SNGPNT</th>\n",
       "      <th>M_SNGPNT</th>\n",
       "      <th>E_LIMENG</th>\n",
       "      <th>M_LIMENG</th>\n",
       "      <th>E_MINRTY</th>\n",
       "      <th>M_MINRTY</th>\n",
       "      <th>E_MUNIT</th>\n",
       "      <th>M_MUNIT</th>\n",
       "      <th>E_MOBILE</th>\n",
       "      <th>M_MOBILE</th>\n",
       "      <th>E_CROWD</th>\n",
       "      <th>M_CROWD</th>\n",
       "      <th>E_NOVEH</th>\n",
       "      <th>M_NOVEH</th>\n",
       "      <th>E_GROUPQ</th>\n",
       "      <th>M_GROUPQ</th>\n",
       "      <th>EP_POV150</th>\n",
       "      <th>MP_POV150</th>\n",
       "      <th>EP_UNEMP</th>\n",
       "      <th>MP_UNEMP</th>\n",
       "      <th>EP_HBURD</th>\n",
       "      <th>MP_HBURD</th>\n",
       "      <th>EP_NOHSDP</th>\n",
       "      <th>MP_NOHSDP</th>\n",
       "      <th>EP_UNINSUR</th>\n",
       "      <th>MP_UNINSUR</th>\n",
       "      <th>EP_AGE65</th>\n",
       "      <th>MP_AGE65</th>\n",
       "      <th>EP_AGE17</th>\n",
       "      <th>MP_AGE17</th>\n",
       "      <th>EP_DISABL</th>\n",
       "      <th>MP_DISABL</th>\n",
       "      <th>EP_SNGPNT</th>\n",
       "      <th>MP_SNGPNT</th>\n",
       "      <th>EP_LIMENG</th>\n",
       "      <th>MP_LIMENG</th>\n",
       "      <th>EP_MINRTY</th>\n",
       "      <th>MP_MINRTY</th>\n",
       "      <th>EP_MUNIT</th>\n",
       "      <th>MP_MUNIT</th>\n",
       "      <th>EP_MOBILE</th>\n",
       "      <th>MP_MOBILE</th>\n",
       "      <th>EP_CROWD</th>\n",
       "      <th>MP_CROWD</th>\n",
       "      <th>EP_NOVEH</th>\n",
       "      <th>MP_NOVEH</th>\n",
       "      <th>EP_GROUPQ</th>\n",
       "      <th>MP_GROUPQ</th>\n",
       "      <th>EPL_POV150</th>\n",
       "      <th>EPL_UNEMP</th>\n",
       "      <th>EPL_HBURD</th>\n",
       "      <th>EPL_NOHSDP</th>\n",
       "      <th>EPL_UNINSUR</th>\n",
       "      <th>SPL_THEME1</th>\n",
       "      <th>RPL_THEME1</th>\n",
       "      <th>EPL_AGE65</th>\n",
       "      <th>EPL_AGE17</th>\n",
       "      <th>EPL_DISABL</th>\n",
       "      <th>EPL_SNGPNT</th>\n",
       "      <th>EPL_LIMENG</th>\n",
       "      <th>SPL_THEME2</th>\n",
       "      <th>RPL_THEME2</th>\n",
       "      <th>EPL_MINRTY</th>\n",
       "      <th>SPL_THEME3</th>\n",
       "      <th>RPL_THEME3</th>\n",
       "      <th>EPL_MUNIT</th>\n",
       "      <th>EPL_MOBILE</th>\n",
       "      <th>EPL_CROWD</th>\n",
       "      <th>EPL_NOVEH</th>\n",
       "      <th>EPL_GROUPQ</th>\n",
       "      <th>SPL_THEME4</th>\n",
       "      <th>RPL_THEME4</th>\n",
       "      <th>SPL_THEMES</th>\n",
       "      <th>RPL_THEMES</th>\n",
       "      <th>F_POV150</th>\n",
       "      <th>F_UNEMP</th>\n",
       "      <th>F_HBURD</th>\n",
       "      <th>F_NOHSDP</th>\n",
       "      <th>F_UNINSUR</th>\n",
       "      <th>F_THEME1</th>\n",
       "      <th>F_AGE65</th>\n",
       "      <th>F_AGE17</th>\n",
       "      <th>F_DISABL</th>\n",
       "      <th>F_SNGPNT</th>\n",
       "      <th>F_LIMENG</th>\n",
       "      <th>F_THEME2</th>\n",
       "      <th>F_MINRTY</th>\n",
       "      <th>F_THEME3</th>\n",
       "      <th>F_MUNIT</th>\n",
       "      <th>F_MOBILE</th>\n",
       "      <th>F_CROWD</th>\n",
       "      <th>F_NOVEH</th>\n",
       "      <th>F_GROUPQ</th>\n",
       "      <th>F_THEME4</th>\n",
       "      <th>F_TOTAL</th>\n",
       "      <th>E_DAYPOP</th>\n",
       "      <th>E_NOINT</th>\n",
       "      <th>M_NOINT</th>\n",
       "      <th>E_AFAM</th>\n",
       "      <th>M_AFAM</th>\n",
       "      <th>E_HISP</th>\n",
       "      <th>M_HISP</th>\n",
       "      <th>E_ASIAN</th>\n",
       "      <th>M_ASIAN</th>\n",
       "      <th>E_AIAN</th>\n",
       "      <th>M_AIAN</th>\n",
       "      <th>E_NHPI</th>\n",
       "      <th>M_NHPI</th>\n",
       "      <th>E_TWOMORE</th>\n",
       "      <th>M_TWOMORE</th>\n",
       "      <th>E_OTHERRACE</th>\n",
       "      <th>M_OTHERRACE</th>\n",
       "      <th>EP_NOINT</th>\n",
       "      <th>MP_NOINT</th>\n",
       "      <th>EP_AFAM</th>\n",
       "      <th>MP_AFAM</th>\n",
       "      <th>EP_HISP</th>\n",
       "      <th>MP_HISP</th>\n",
       "      <th>EP_ASIAN</th>\n",
       "      <th>MP_ASIAN</th>\n",
       "      <th>EP_AIAN</th>\n",
       "      <th>MP_AIAN</th>\n",
       "      <th>EP_NHPI</th>\n",
       "      <th>MP_NHPI</th>\n",
       "      <th>EP_TWOMORE</th>\n",
       "      <th>MP_TWOMORE</th>\n",
       "      <th>EP_OTHERRACE</th>\n",
       "      <th>MP_OTHERRACE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1001</td>\n",
       "      <td>Autauga</td>\n",
       "      <td>1001</td>\n",
       "      <td>Autauga County, Alabama</td>\n",
       "      <td>594.455838</td>\n",
       "      <td>55639</td>\n",
       "      <td>0</td>\n",
       "      <td>23697</td>\n",
       "      <td>68</td>\n",
       "      <td>21559</td>\n",
       "      <td>366</td>\n",
       "      <td>12611</td>\n",
       "      <td>1349</td>\n",
       "      <td>736</td>\n",
       "      <td>185</td>\n",
       "      <td>5029</td>\n",
       "      <td>576</td>\n",
       "      <td>4273</td>\n",
       "      <td>562</td>\n",
       "      <td>4345</td>\n",
       "      <td>725</td>\n",
       "      <td>8490</td>\n",
       "      <td>85</td>\n",
       "      <td>13143</td>\n",
       "      <td>49</td>\n",
       "      <td>9658</td>\n",
       "      <td>856</td>\n",
       "      <td>1608</td>\n",
       "      <td>302</td>\n",
       "      <td>363</td>\n",
       "      <td>229</td>\n",
       "      <td>14479</td>\n",
       "      <td>578</td>\n",
       "      <td>918</td>\n",
       "      <td>292</td>\n",
       "      <td>4313</td>\n",
       "      <td>440</td>\n",
       "      <td>339</td>\n",
       "      <td>162</td>\n",
       "      <td>1167</td>\n",
       "      <td>320</td>\n",
       "      <td>578</td>\n",
       "      <td>157</td>\n",
       "      <td>22.9</td>\n",
       "      <td>2.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.7</td>\n",
       "      <td>23.3</td>\n",
       "      <td>2.6</td>\n",
       "      <td>11.3</td>\n",
       "      <td>1.5</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.3</td>\n",
       "      <td>15.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>23.6</td>\n",
       "      <td>0.1</td>\n",
       "      <td>17.7</td>\n",
       "      <td>1.6</td>\n",
       "      <td>7.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.4</td>\n",
       "      <td>26.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1.2</td>\n",
       "      <td>18.2</td>\n",
       "      <td>1.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>5.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.4624</td>\n",
       "      <td>0.1397</td>\n",
       "      <td>0.7078</td>\n",
       "      <td>0.5080</td>\n",
       "      <td>0.4586</td>\n",
       "      <td>2.2765</td>\n",
       "      <td>0.4045</td>\n",
       "      <td>0.1827</td>\n",
       "      <td>0.6986</td>\n",
       "      <td>0.6830</td>\n",
       "      <td>0.7922</td>\n",
       "      <td>0.4736</td>\n",
       "      <td>2.8301</td>\n",
       "      <td>0.7362</td>\n",
       "      <td>0.6337</td>\n",
       "      <td>0.6337</td>\n",
       "      <td>0.6337</td>\n",
       "      <td>0.6050</td>\n",
       "      <td>0.7486</td>\n",
       "      <td>0.4023</td>\n",
       "      <td>0.4764</td>\n",
       "      <td>0.1569</td>\n",
       "      <td>2.3892</td>\n",
       "      <td>0.4309</td>\n",
       "      <td>8.1295</td>\n",
       "      <td>0.5325</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>41810</td>\n",
       "      <td>7100</td>\n",
       "      <td>913</td>\n",
       "      <td>10849</td>\n",
       "      <td>345</td>\n",
       "      <td>1601</td>\n",
       "      <td>0</td>\n",
       "      <td>649</td>\n",
       "      <td>174</td>\n",
       "      <td>155</td>\n",
       "      <td>102</td>\n",
       "      <td>0</td>\n",
       "      <td>29</td>\n",
       "      <td>1124</td>\n",
       "      <td>374</td>\n",
       "      <td>101</td>\n",
       "      <td>101</td>\n",
       "      <td>12.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.5</td>\n",
       "      <td>0.6</td>\n",
       "      <td>2.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1003</td>\n",
       "      <td>Baldwin</td>\n",
       "      <td>1003</td>\n",
       "      <td>Baldwin County, Alabama</td>\n",
       "      <td>1589.835294</td>\n",
       "      <td>218289</td>\n",
       "      <td>0</td>\n",
       "      <td>116747</td>\n",
       "      <td>180</td>\n",
       "      <td>84047</td>\n",
       "      <td>1143</td>\n",
       "      <td>36413</td>\n",
       "      <td>2802</td>\n",
       "      <td>4027</td>\n",
       "      <td>714</td>\n",
       "      <td>19350</td>\n",
       "      <td>1526</td>\n",
       "      <td>14823</td>\n",
       "      <td>1344</td>\n",
       "      <td>20501</td>\n",
       "      <td>1932</td>\n",
       "      <td>44716</td>\n",
       "      <td>109</td>\n",
       "      <td>46993</td>\n",
       "      <td>0</td>\n",
       "      <td>30615</td>\n",
       "      <td>1565</td>\n",
       "      <td>3317</td>\n",
       "      <td>547</td>\n",
       "      <td>1593</td>\n",
       "      <td>486</td>\n",
       "      <td>37334</td>\n",
       "      <td>1866</td>\n",
       "      <td>19513</td>\n",
       "      <td>979</td>\n",
       "      <td>11893</td>\n",
       "      <td>868</td>\n",
       "      <td>1280</td>\n",
       "      <td>364</td>\n",
       "      <td>2627</td>\n",
       "      <td>397</td>\n",
       "      <td>2954</td>\n",
       "      <td>403</td>\n",
       "      <td>16.9</td>\n",
       "      <td>1.3</td>\n",
       "      <td>3.9</td>\n",
       "      <td>0.7</td>\n",
       "      <td>23.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.9</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.9</td>\n",
       "      <td>20.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14.2</td>\n",
       "      <td>0.7</td>\n",
       "      <td>3.9</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.2</td>\n",
       "      <td>17.1</td>\n",
       "      <td>0.9</td>\n",
       "      <td>16.7</td>\n",
       "      <td>0.8</td>\n",
       "      <td>10.2</td>\n",
       "      <td>0.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.4</td>\n",
       "      <td>3.1</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.1770</td>\n",
       "      <td>0.2861</td>\n",
       "      <td>0.8912</td>\n",
       "      <td>0.3647</td>\n",
       "      <td>0.5767</td>\n",
       "      <td>2.2957</td>\n",
       "      <td>0.4112</td>\n",
       "      <td>0.6496</td>\n",
       "      <td>0.4077</td>\n",
       "      <td>0.3689</td>\n",
       "      <td>0.1738</td>\n",
       "      <td>0.5150</td>\n",
       "      <td>2.1150</td>\n",
       "      <td>0.2724</td>\n",
       "      <td>0.5022</td>\n",
       "      <td>0.5022</td>\n",
       "      <td>0.5022</td>\n",
       "      <td>0.9574</td>\n",
       "      <td>0.4885</td>\n",
       "      <td>0.3600</td>\n",
       "      <td>0.1257</td>\n",
       "      <td>0.3157</td>\n",
       "      <td>2.2473</td>\n",
       "      <td>0.3612</td>\n",
       "      <td>7.1602</td>\n",
       "      <td>0.3552</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>218607</td>\n",
       "      <td>24453</td>\n",
       "      <td>2477</td>\n",
       "      <td>19027</td>\n",
       "      <td>744</td>\n",
       "      <td>9947</td>\n",
       "      <td>0</td>\n",
       "      <td>2033</td>\n",
       "      <td>352</td>\n",
       "      <td>1327</td>\n",
       "      <td>371</td>\n",
       "      <td>10</td>\n",
       "      <td>16</td>\n",
       "      <td>4250</td>\n",
       "      <td>871</td>\n",
       "      <td>740</td>\n",
       "      <td>740</td>\n",
       "      <td>11.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>4.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.9</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>1005</td>\n",
       "      <td>Barbour</td>\n",
       "      <td>1005</td>\n",
       "      <td>Barbour County, Alabama</td>\n",
       "      <td>885.007618</td>\n",
       "      <td>25026</td>\n",
       "      <td>0</td>\n",
       "      <td>12057</td>\n",
       "      <td>119</td>\n",
       "      <td>9322</td>\n",
       "      <td>338</td>\n",
       "      <td>8965</td>\n",
       "      <td>673</td>\n",
       "      <td>649</td>\n",
       "      <td>182</td>\n",
       "      <td>2305</td>\n",
       "      <td>298</td>\n",
       "      <td>4497</td>\n",
       "      <td>387</td>\n",
       "      <td>2362</td>\n",
       "      <td>311</td>\n",
       "      <td>4777</td>\n",
       "      <td>16</td>\n",
       "      <td>5222</td>\n",
       "      <td>29</td>\n",
       "      <td>4159</td>\n",
       "      <td>287</td>\n",
       "      <td>1029</td>\n",
       "      <td>217</td>\n",
       "      <td>433</td>\n",
       "      <td>205</td>\n",
       "      <td>13694</td>\n",
       "      <td>271</td>\n",
       "      <td>170</td>\n",
       "      <td>77</td>\n",
       "      <td>3644</td>\n",
       "      <td>347</td>\n",
       "      <td>350</td>\n",
       "      <td>192</td>\n",
       "      <td>1039</td>\n",
       "      <td>202</td>\n",
       "      <td>2910</td>\n",
       "      <td>264</td>\n",
       "      <td>40.6</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.9</td>\n",
       "      <td>1.9</td>\n",
       "      <td>24.7</td>\n",
       "      <td>3.1</td>\n",
       "      <td>25.3</td>\n",
       "      <td>2.2</td>\n",
       "      <td>10.7</td>\n",
       "      <td>1.4</td>\n",
       "      <td>19.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>20.9</td>\n",
       "      <td>0.1</td>\n",
       "      <td>18.8</td>\n",
       "      <td>1.4</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.3</td>\n",
       "      <td>1.8</td>\n",
       "      <td>0.9</td>\n",
       "      <td>54.7</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.6</td>\n",
       "      <td>30.2</td>\n",
       "      <td>2.8</td>\n",
       "      <td>3.8</td>\n",
       "      <td>2.1</td>\n",
       "      <td>11.1</td>\n",
       "      <td>2.1</td>\n",
       "      <td>11.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.9586</td>\n",
       "      <td>0.8071</td>\n",
       "      <td>0.5121</td>\n",
       "      <td>0.9688</td>\n",
       "      <td>0.6680</td>\n",
       "      <td>3.9146</td>\n",
       "      <td>0.9532</td>\n",
       "      <td>0.5216</td>\n",
       "      <td>0.3316</td>\n",
       "      <td>0.7581</td>\n",
       "      <td>0.9656</td>\n",
       "      <td>0.7460</td>\n",
       "      <td>3.3229</td>\n",
       "      <td>0.9453</td>\n",
       "      <td>0.8962</td>\n",
       "      <td>0.8962</td>\n",
       "      <td>0.8962</td>\n",
       "      <td>0.2565</td>\n",
       "      <td>0.9427</td>\n",
       "      <td>0.8549</td>\n",
       "      <td>0.9456</td>\n",
       "      <td>0.9513</td>\n",
       "      <td>3.9510</td>\n",
       "      <td>0.9949</td>\n",
       "      <td>12.0847</td>\n",
       "      <td>0.9927</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>27133</td>\n",
       "      <td>6249</td>\n",
       "      <td>675</td>\n",
       "      <td>11889</td>\n",
       "      <td>179</td>\n",
       "      <td>1110</td>\n",
       "      <td>0</td>\n",
       "      <td>122</td>\n",
       "      <td>25</td>\n",
       "      <td>81</td>\n",
       "      <td>63</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>334</td>\n",
       "      <td>149</td>\n",
       "      <td>157</td>\n",
       "      <td>157</td>\n",
       "      <td>28.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>47.5</td>\n",
       "      <td>0.7</td>\n",
       "      <td>4.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.6</td>\n",
       "      <td>0.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ST    STATE ST_ABBR  STCNTY   COUNTY  FIPS                 LOCATION  \\\n",
       "0   1  Alabama      AL    1001  Autauga  1001  Autauga County, Alabama   \n",
       "1   1  Alabama      AL    1003  Baldwin  1003  Baldwin County, Alabama   \n",
       "2   1  Alabama      AL    1005  Barbour  1005  Barbour County, Alabama   \n",
       "\n",
       "     AREA_SQMI  E_TOTPOP  M_TOTPOP    E_HU  M_HU   E_HH  M_HH  E_POV150  \\\n",
       "0   594.455838     55639         0   23697    68  21559   366     12611   \n",
       "1  1589.835294    218289         0  116747   180  84047  1143     36413   \n",
       "2   885.007618     25026         0   12057   119   9322   338      8965   \n",
       "\n",
       "   M_POV150  E_UNEMP  M_UNEMP  E_HBURD  M_HBURD  E_NOHSDP  M_NOHSDP  \\\n",
       "0      1349      736      185     5029      576      4273       562   \n",
       "1      2802     4027      714    19350     1526     14823      1344   \n",
       "2       673      649      182     2305      298      4497       387   \n",
       "\n",
       "   E_UNINSUR  M_UNINSUR  E_AGE65  M_AGE65  E_AGE17  M_AGE17  E_DISABL  \\\n",
       "0       4345        725     8490       85    13143       49      9658   \n",
       "1      20501       1932    44716      109    46993        0     30615   \n",
       "2       2362        311     4777       16     5222       29      4159   \n",
       "\n",
       "   M_DISABL  E_SNGPNT  M_SNGPNT  E_LIMENG  M_LIMENG  E_MINRTY  M_MINRTY  \\\n",
       "0       856      1608       302       363       229     14479       578   \n",
       "1      1565      3317       547      1593       486     37334      1866   \n",
       "2       287      1029       217       433       205     13694       271   \n",
       "\n",
       "   E_MUNIT  M_MUNIT  E_MOBILE  M_MOBILE  E_CROWD  M_CROWD  E_NOVEH  M_NOVEH  \\\n",
       "0      918      292      4313       440      339      162     1167      320   \n",
       "1    19513      979     11893       868     1280      364     2627      397   \n",
       "2      170       77      3644       347      350      192     1039      202   \n",
       "\n",
       "   E_GROUPQ  M_GROUPQ  EP_POV150  MP_POV150  EP_UNEMP  MP_UNEMP  EP_HBURD  \\\n",
       "0       578       157       22.9        2.4       2.9       0.7      23.3   \n",
       "1      2954       403       16.9        1.3       3.9       0.7      23.0   \n",
       "2      2910       264       40.6        3.0       6.9       1.9      24.7   \n",
       "\n",
       "   MP_HBURD  EP_NOHSDP  MP_NOHSDP  EP_UNINSUR  MP_UNINSUR  EP_AGE65  MP_AGE65  \\\n",
       "0       2.6       11.3        1.5         8.0         1.3      15.3       0.2   \n",
       "1       1.8        9.5        0.9         9.5         0.9      20.5       0.1   \n",
       "2       3.1       25.3        2.2        10.7         1.4      19.1       0.1   \n",
       "\n",
       "   EP_AGE17  MP_AGE17  EP_DISABL  MP_DISABL  EP_SNGPNT  MP_SNGPNT  EP_LIMENG  \\\n",
       "0      23.6       0.1       17.7        1.6        7.5        1.4        0.7   \n",
       "1      21.5       0.0       14.2        0.7        3.9        0.6        0.8   \n",
       "2      20.9       0.1       18.8        1.4       11.0        2.3        1.8   \n",
       "\n",
       "   MP_LIMENG  EP_MINRTY  MP_MINRTY  EP_MUNIT  MP_MUNIT  EP_MOBILE  MP_MOBILE  \\\n",
       "0        0.4       26.0        1.0       3.9       1.2       18.2        1.8   \n",
       "1        0.2       17.1        0.9      16.7       0.8       10.2        0.7   \n",
       "2        0.9       54.7        1.1       1.4       0.6       30.2        2.8   \n",
       "\n",
       "   EP_CROWD  MP_CROWD  EP_NOVEH  MP_NOVEH  EP_GROUPQ  MP_GROUPQ  EPL_POV150  \\\n",
       "0       1.6       0.8       5.4       1.5        1.0        0.3      0.4624   \n",
       "1       1.5       0.4       3.1       0.5        1.4        0.2      0.1770   \n",
       "2       3.8       2.1      11.1       2.1       11.6        1.1      0.9586   \n",
       "\n",
       "   EPL_UNEMP  EPL_HBURD  EPL_NOHSDP  EPL_UNINSUR  SPL_THEME1  RPL_THEME1  \\\n",
       "0     0.1397     0.7078      0.5080       0.4586      2.2765      0.4045   \n",
       "1     0.2861     0.8912      0.3647       0.5767      2.2957      0.4112   \n",
       "2     0.8071     0.5121      0.9688       0.6680      3.9146      0.9532   \n",
       "\n",
       "   EPL_AGE65  EPL_AGE17  EPL_DISABL  EPL_SNGPNT  EPL_LIMENG  SPL_THEME2  \\\n",
       "0     0.1827     0.6986      0.6830      0.7922      0.4736      2.8301   \n",
       "1     0.6496     0.4077      0.3689      0.1738      0.5150      2.1150   \n",
       "2     0.5216     0.3316      0.7581      0.9656      0.7460      3.3229   \n",
       "\n",
       "   RPL_THEME2  EPL_MINRTY  SPL_THEME3  RPL_THEME3  EPL_MUNIT  EPL_MOBILE  \\\n",
       "0      0.7362      0.6337      0.6337      0.6337     0.6050      0.7486   \n",
       "1      0.2724      0.5022      0.5022      0.5022     0.9574      0.4885   \n",
       "2      0.9453      0.8962      0.8962      0.8962     0.2565      0.9427   \n",
       "\n",
       "   EPL_CROWD  EPL_NOVEH  EPL_GROUPQ  SPL_THEME4  RPL_THEME4  SPL_THEMES  \\\n",
       "0     0.4023     0.4764      0.1569      2.3892      0.4309      8.1295   \n",
       "1     0.3600     0.1257      0.3157      2.2473      0.3612      7.1602   \n",
       "2     0.8549     0.9456      0.9513      3.9510      0.9949     12.0847   \n",
       "\n",
       "   RPL_THEMES  F_POV150  F_UNEMP  F_HBURD  F_NOHSDP  F_UNINSUR  F_THEME1  \\\n",
       "0      0.5325         0        0        0         0          0         0   \n",
       "1      0.3552         0        0        0         0          0         0   \n",
       "2      0.9927         1        0        0         1          0         2   \n",
       "\n",
       "   F_AGE65  F_AGE17  F_DISABL  F_SNGPNT  F_LIMENG  F_THEME2  F_MINRTY  \\\n",
       "0        0        0         0         0         0         0         0   \n",
       "1        0        0         0         0         0         0         0   \n",
       "2        0        0         0         1         0         1         0   \n",
       "\n",
       "   F_THEME3  F_MUNIT  F_MOBILE  F_CROWD  F_NOVEH  F_GROUPQ  F_THEME4  F_TOTAL  \\\n",
       "0         0        0         0        0        0         0         0        0   \n",
       "1         0        1         0        0        0         0         1        1   \n",
       "2         0        0         1        0        1         1         3        6   \n",
       "\n",
       "   E_DAYPOP  E_NOINT  M_NOINT  E_AFAM  M_AFAM  E_HISP  M_HISP  E_ASIAN  \\\n",
       "0     41810     7100      913   10849     345    1601       0      649   \n",
       "1    218607    24453     2477   19027     744    9947       0     2033   \n",
       "2     27133     6249      675   11889     179    1110       0      122   \n",
       "\n",
       "   M_ASIAN  E_AIAN  M_AIAN  E_NHPI  M_NHPI  E_TWOMORE  M_TWOMORE  E_OTHERRACE  \\\n",
       "0      174     155     102       0      29       1124        374          101   \n",
       "1      352    1327     371      10      16       4250        871          740   \n",
       "2       25      81      63       1       2        334        149          157   \n",
       "\n",
       "   M_OTHERRACE  EP_NOINT  MP_NOINT  EP_AFAM  MP_AFAM  EP_HISP  MP_HISP  \\\n",
       "0          101      12.9       0.0     19.5      0.6      2.9      0.0   \n",
       "1          740      11.4       0.0      8.7      0.3      4.6      0.0   \n",
       "2          157      28.3       0.3     47.5      0.7      4.4      0.0   \n",
       "\n",
       "   EP_ASIAN  MP_ASIAN  EP_AIAN  MP_AIAN  EP_NHPI  MP_NHPI  EP_TWOMORE  \\\n",
       "0       1.2       0.3      0.3      0.2      0.0      0.1         2.0   \n",
       "1       0.9       0.2      0.6      0.2      0.0      0.1         1.9   \n",
       "2       0.5       0.1      0.3      0.3      0.0      0.1         1.3   \n",
       "\n",
       "   MP_TWOMORE  EP_OTHERRACE  MP_OTHERRACE  \n",
       "0         0.7           0.2           0.3  \n",
       "1         0.4           0.3           0.2  \n",
       "2         0.6           0.6           0.4  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(f\"{support_folder}/SVI2020_US_COUNTY.csv\")\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['RPL_THEME1'] >= 0]\n",
    "feature_column = 'HealthDisp'\n",
    "df[feature_column] = df[['RPL_THEME1', 'EP_UNINSUR']].apply(np.mean, axis=1)\n",
    "df = df[['FIPS', feature_column]]\n",
    "\n",
    "# https://www.census.gov/programs-surveys/geography/technical-documentation/county-changes/2010.html\n",
    "if df[df['FIPS'].isin([2063, 2066])].shape[0] == 2:\n",
    "    values = df[df['FIPS']==2063][feature_column].values[0] + df[df['FIPS']==2066][feature_column].values[0]\n",
    "    \n",
    "    df.loc[len(df.index)] = [2261, values/2]\n",
    "    df = df[~df['FIPS'].isin([2063, 2066])]\n",
    "\n",
    "    df = df.sort_values(by=['FIPS'])\n",
    "\n",
    "df['FIPS'] = df['FIPS'].astype(int)\n",
    "df.round(3).to_csv(f'{output_folder}/Health Disparities.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Additional processing for TFT1 notebook\n",
    "This is done since the TFT1 notebook can not handle normal static files and all files need to be in dynamic format for it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_dynamic(df, feature_column, start, end):\n",
    "    total_days = (end - start).days + 1\n",
    "\n",
    "    values = df[feature_column].values\n",
    "    # min max scaling\n",
    "    # df[feature_column] = (values - values.min())/(values.max() - values.min())\n",
    "\n",
    "    dates = [start + pd.to_timedelta(day, unit='D') for day in range(total_days)]\n",
    "    values = df[feature_column].values\n",
    "\n",
    "    additional = pd.DataFrame({date:values for date in dates}, columns=dates)\n",
    "    df = pd.concat([df, additional],  axis=1)\n",
    "    df.drop(columns=[feature_column], inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = pd.to_datetime('2020-02-29')\n",
    "end = pd.to_datetime('2022-05-17')\n",
    "\n",
    "map = [('Health Disparities', 'HealthDisp'), ('Age Distribution', 'AgeDist')]\n",
    "for value in map:\n",
    "    filename, feature_column = value[0], value[1]\n",
    "    filepath = f'{output_folder}/{filename}.csv'\n",
    "\n",
    "    df = pd.read_csv(filepath)\n",
    "    df = convert_to_dynamic(df, feature_column, start, end)\n",
    "    df.round(4).to_csv(filepath, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.8 64-bit",
   "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.15"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "26de051ba29f2982a8de78e945f0abaf191376122a1563185a90213a26c5da77"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}