ETL_Code.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Data Cleanup and Analysis Code"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pandas as pd"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Transform"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Table 1 : 'US_States'"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "states_df =  pd.read_csv('Resources/Source_Data/US_States.csv')\r\n",
    "states_df\r\n",
    "\r\n",
    "#Trim leading and trailing spaces for string type data\r\n",
    "sdf_obj = states_df.select_dtypes(['object'])\r\n",
    "states_df[sdf_obj.columns] = sdf_obj.apply(lambda x: x.str.strip()) \r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# LOAD US_States.csv"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "# Check for duplicates \r\n",
    "# checking total States_Fips vs. total unique States_Fips. \r\n",
    "# If they are equal, then there are no duplicates\r\n",
    "States_Fips_List = states_df['State_Fips']\r\n",
    "States_Table_Count = States_Fips_List.count()\r\n",
    "Unique_States_Count = States_Fips_List.nunique()\r\n",
    "print (States_Table_Count, Unique_States_Count)\r\n",
    "\r\n",
    "# State Table is clean. No duplicate FIPS exist. \r\n",
    "# This table is ready for PostgreSQL Table\r\n"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "60 60\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "\r\n",
    "# Write to US_States.csv that can be imported in PostgreSQL\r\n",
    "states_df.to_csv('Resources/Transformed_Data/Us_States.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Transform"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Table 2 : 'US_Counties'"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "counties_df = pd.read_csv('Resources/Source_Data/US_Counties.csv', encoding='latin-1')\r\n",
    "#print(counties_df)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "# Some states have same county names. So counties are uniquly identified by County_FIPS which is a unique ID \r\n",
    "# called Federal Information Processing Standards"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "# Check for duplicates \r\n",
    "# I am checking total County_Fips vs. total unique County_Fips. \r\n",
    "# If they are equal, then there are no duplicates\r\n",
    "County_Fips_List = counties_df['County_Fips']\r\n",
    "County_Table_Count = County_Fips_List.count()\r\n",
    "Unique_Counties_Count = County_Fips_List.nunique()\r\n",
    "print (County_Table_Count, Unique_Counties_Count)\r\n",
    "\r\n",
    "\r\n",
    "# County Table is Clean. No Nulls, no duplicate FIPS exist"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "3292 3292\n"
     ]
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "#Trim leading and trailing spaces for string type data\r\n",
    "df_obj = counties_df.select_dtypes(['object'])\r\n",
    "counties_df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip()) \r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "source": [
    "# Merge County table with state \r\n",
    "county_table = counties_df.merge(states_df, how='left', left_on='State', right_on='Sabbr')\r\n",
    "county_table = county_table[['County_Fips', 'County', 'State_Fips']]\r\n",
    "county_table['County'] = county_table['County'].str.title()\r\n",
    "\r\n",
    "\r\n",
    "df_obj = county_table.select_dtypes(['object'])\r\n",
    "county_table[df_obj.columns] = df_obj.apply(lambda x: x.str.strip()) \r\n",
    "county_table_strip = county_table\r\n",
    "county_table_strip\r\n",
    "\r\n",
    "# This table is ready for PostGressql\r\n",
    "county_table_strip"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      County_Fips                    County  State_Fips\n",
       "0            1000                   Alabama           1\n",
       "1            1001            Autauga County           1\n",
       "2            1003            Baldwin County           1\n",
       "3            1005            Barbour County           1\n",
       "4            1007               Bibb County           1\n",
       "...           ...                       ...         ...\n",
       "3287        78010                  St Croix          78\n",
       "3288        78020                   St John          78\n",
       "3289        78030                 St Thomas          78\n",
       "3290         2063       Chugach Census Area           1\n",
       "3291         2066  Copper River Census Area           1\n",
       "\n",
       "[3292 rows x 3 columns]"
      ],
      "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_Fips</th>\n",
       "      <th>County</th>\n",
       "      <th>State_Fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1001</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1003</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1005</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1007</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3287</th>\n",
       "      <td>78010</td>\n",
       "      <td>St Croix</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3288</th>\n",
       "      <td>78020</td>\n",
       "      <td>St John</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3289</th>\n",
       "      <td>78030</td>\n",
       "      <td>St Thomas</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3290</th>\n",
       "      <td>2063</td>\n",
       "      <td>Chugach Census Area</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3291</th>\n",
       "      <td>2066</td>\n",
       "      <td>Copper River Census Area</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3292 rows × 3 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# LOAD Us_Counties.csv"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "source": [
    "\r\n",
    "# Write to Us_Counties.csv that can be imported in PostgreSQL\r\n",
    "county_table_strip.to_csv('Resources/Transformed_Data/Us_Counties.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# TRANSFORM"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Table 3 : 'US_Covid_Data'"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "source": [
    "# covid_df = pd.read_csv('Resources/Source_Data/US_Covid_Data.csv', parse_dates=['date'])\r\n",
    "# covid_df\r\n",
    "\r\n",
    "# This will connect to the raw file on their website to get yo to date data\r\n",
    "covid_df = pd.read_csv('https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv', parse_dates=['date'])\r\n",
    "#if we are reading it form the raw file from the source, we can write it to csv if we choose to save it locally\r\n",
    "covid_df.to_csv('Resources/Source_Data/US_Covid_Data.csv', index=False)\r\n",
    "covid_df"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              date      county       state     fips  cases  deaths\n",
       "0       2020-01-21   Snohomish  Washington  53061.0      1     0.0\n",
       "1       2020-01-22   Snohomish  Washington  53061.0      1     0.0\n",
       "2       2020-01-23   Snohomish  Washington  53061.0      1     0.0\n",
       "3       2020-01-24        Cook    Illinois  17031.0      1     0.0\n",
       "4       2020-01-24   Snohomish  Washington  53061.0      1     0.0\n",
       "...            ...         ...         ...      ...    ...     ...\n",
       "1598913 2021-08-08  Sweetwater     Wyoming  56037.0   5056    45.0\n",
       "1598914 2021-08-08       Teton     Wyoming  56039.0   3960    11.0\n",
       "1598915 2021-08-08       Uinta     Wyoming  56041.0   2498    14.0\n",
       "1598916 2021-08-08    Washakie     Wyoming  56043.0    949    26.0\n",
       "1598917 2021-08-08      Weston     Wyoming  56045.0    694     6.0\n",
       "\n",
       "[1598918 rows x 6 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Cook</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>17031.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598913</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56037.0</td>\n",
       "      <td>5056</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598914</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56039.0</td>\n",
       "      <td>3960</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598915</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56041.0</td>\n",
       "      <td>2498</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598916</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56043.0</td>\n",
       "      <td>949</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598917</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56045.0</td>\n",
       "      <td>694</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1598918 rows × 6 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "source": [
    "# Chack for Nulls in any colums\r\n",
    "covid_df.isna().any()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "date      False\n",
       "county    False\n",
       "state     False\n",
       "fips       True\n",
       "cases     False\n",
       "deaths     True\n",
       "dtype: bool"
      ]
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "source": [
    "# column 'deaths' can contain Nulls but not Fips \r\n",
    "# Identify these records for cleaning\r\n",
    "\r\n",
    "# if (covid_df['fips'].isnull().values.any()):\r\n",
    "#     print(covid_df[covid_df['fips'].isna()])\r\n",
    "\r\n",
    "covid_df['fips'].isnull().values.any()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "source": [
    "# If covid table has records that have state but an unknown county, we keep the data\r\n",
    "na_covid_df = covid_df[covid_df['fips'].isna()]\r\n",
    "#na_covid_df"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "source": [
    "# stripping 'state' column of any leading and trailing spaces\r\n",
    "\r\n",
    "#na_covid_df['state'] = na_covid_df['state'].str.strip()\r\n",
    "na_covid_df['state'].apply(lambda x: x.strip()) \r\n",
    "#na_covid_df\r\n",
    "\r\n",
    "na_covid_df = na_covid_df.merge(county_table_strip, how=\"left\", left_on = \"state\", right_on=\"County\")\r\n",
    "#na_covid_df\r\n",
    "\r\n",
    "na_covid_df_final = na_covid_df[['date', 'county', 'state', 'County_Fips', 'cases', 'deaths']]\r\n",
    "na_covid_df_final\r\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            date         county         state  County_Fips  cases  deaths\n",
       "0     2020-03-01  New York City      New York        36000      1     0.0\n",
       "1     2020-03-01        Unknown  Rhode Island        44000      2     0.0\n",
       "2     2020-03-02  New York City      New York        36000      1     0.0\n",
       "3     2020-03-02        Unknown  Rhode Island        44000      2     0.0\n",
       "4     2020-03-03  New York City      New York        36000      2     0.0\n",
       "...          ...            ...           ...          ...    ...     ...\n",
       "14976 2021-08-08        Unknown   Puerto Rico        72000   5941  2609.0\n",
       "14977 2021-08-08        Unknown  Rhode Island        44000  13011     8.0\n",
       "14978 2021-08-08        Unknown     Tennessee        47000   7918    95.0\n",
       "14979 2021-08-08        Unknown          Utah        49000   1262    24.0\n",
       "14980 2021-08-08        Unknown       Vermont        50000     19     0.0\n",
       "\n",
       "[14981 rows x 6 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>County_Fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>New York City</td>\n",
       "      <td>New York</td>\n",
       "      <td>36000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>New York City</td>\n",
       "      <td>New York</td>\n",
       "      <td>36000</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>New York City</td>\n",
       "      <td>New York</td>\n",
       "      <td>36000</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14976</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>72000</td>\n",
       "      <td>5941</td>\n",
       "      <td>2609.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14977</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>44000</td>\n",
       "      <td>13011</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14978</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>47000</td>\n",
       "      <td>7918</td>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14979</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Utah</td>\n",
       "      <td>49000</td>\n",
       "      <td>1262</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14980</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>Vermont</td>\n",
       "      <td>50000</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14981 rows × 6 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "source": [
    "# Records with Null Fips have been cleaned and fips from cencus table for --\r\n",
    "# -- state_unknown counties have been used\r\n",
    "# Renaming the column to match Us_Covid_Table\r\n",
    "\r\n",
    "na_covid_df_final = na_covid_df_final.rename(columns = {'County_Fips':'county_fips'})\r\n",
    "na_covid_df_final\r\n",
    "\r\n",
    "na_covid_df_final['county_fips'].isnull().values.any()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "source": [
    "# Non Null Covid Data\r\n",
    "non_na_covid_df = covid_df[covid_df['fips'].notna()]  \r\n",
    "non_na_covid_df\r\n",
    "\r\n",
    "non_na_covid_df = non_na_covid_df.rename(columns = {'fips':'county_fips'})\r\n",
    "non_na_covid_df"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              date      county       state  county_fips  cases  deaths\n",
       "0       2020-01-21   Snohomish  Washington      53061.0      1     0.0\n",
       "1       2020-01-22   Snohomish  Washington      53061.0      1     0.0\n",
       "2       2020-01-23   Snohomish  Washington      53061.0      1     0.0\n",
       "3       2020-01-24        Cook    Illinois      17031.0      1     0.0\n",
       "4       2020-01-24   Snohomish  Washington      53061.0      1     0.0\n",
       "...            ...         ...         ...          ...    ...     ...\n",
       "1598913 2021-08-08  Sweetwater     Wyoming      56037.0   5056    45.0\n",
       "1598914 2021-08-08       Teton     Wyoming      56039.0   3960    11.0\n",
       "1598915 2021-08-08       Uinta     Wyoming      56041.0   2498    14.0\n",
       "1598916 2021-08-08    Washakie     Wyoming      56043.0    949    26.0\n",
       "1598917 2021-08-08      Weston     Wyoming      56045.0    694     6.0\n",
       "\n",
       "[1584431 rows x 6 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Cook</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>17031.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598913</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Sweetwater</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56037.0</td>\n",
       "      <td>5056</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598914</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Teton</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56039.0</td>\n",
       "      <td>3960</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598915</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Uinta</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56041.0</td>\n",
       "      <td>2498</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598916</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Washakie</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56043.0</td>\n",
       "      <td>949</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598917</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>Weston</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>56045.0</td>\n",
       "      <td>694</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1584431 rows × 6 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "source": [
    "# Concatenate the clean dataframes\r\n",
    "vertical_stack = pd. concat([non_na_covid_df,na_covid_df_final], axis=0) \r\n",
    "vertical_stack.isnull().any()"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "date           False\n",
       "county         False\n",
       "state          False\n",
       "county_fips    False\n",
       "cases          False\n",
       "deaths          True\n",
       "dtype: bool"
      ]
     },
     "metadata": {},
     "execution_count": 17
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "source": [
    "vertical_stack['deaths'] = vertical_stack['deaths'].fillna(0)\r\n",
    "# Converting fips columns to Int\r\n",
    "vertical_stack.county_fips = vertical_stack.county_fips.astype(int)\r\n",
    "vertical_stack.deaths = vertical_stack.deaths.astype(int)\r\n"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "source": [
    "\r\n",
    "us_covid_table = vertical_stack[['date', 'county_fips', 'cases', 'deaths']]\r\n",
    "#us_covid_table = us_covid_table.sort_values(by='date', ascending=False)\r\n",
    "us_covid_table\r\n",
    "\r\n",
    "# This table is ready for Loading"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "            date  county_fips  cases  deaths\n",
       "0     2020-01-21        53061      1       0\n",
       "1     2020-01-22        53061      1       0\n",
       "2     2020-01-23        53061      1       0\n",
       "3     2020-01-24        17031      1       0\n",
       "4     2020-01-24        53061      1       0\n",
       "...          ...          ...    ...     ...\n",
       "14976 2021-08-08        72000   5941    2609\n",
       "14977 2021-08-08        44000  13011       8\n",
       "14978 2021-08-08        47000   7918      95\n",
       "14979 2021-08-08        49000   1262      24\n",
       "14980 2021-08-08        50000     19       0\n",
       "\n",
       "[1599412 rows x 4 columns]"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>17031</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14976</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>72000</td>\n",
       "      <td>5941</td>\n",
       "      <td>2609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14977</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>44000</td>\n",
       "      <td>13011</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14978</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>47000</td>\n",
       "      <td>7918</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14979</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>49000</td>\n",
       "      <td>1262</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14980</th>\n",
       "      <td>2021-08-08</td>\n",
       "      <td>50000</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1599412 rows × 4 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 19
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# LOAD US_Covid_Data.csv"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "source": [
    "\r\n",
    "# Write to US_Covid_Data.csv that can be imported in PostgreSQL\r\n",
    "us_covid_table.to_csv('Resources/Transformed_Data/US_Covid_Data.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Transform"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Table 4 : 'US_Census_Data_2020'"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "source": [
    "population_data = pd.read_csv('Resources/Source_Data/US_Census_Data_2020.csv', encoding='latin-1')\r\n",
    "# population_data.loc[population_data['STNAME'] == population_data['CTYNAME']]\r\n",
    "\r\n",
    "# County = 0 are records for State Totals\r\n",
    "# removed these because we can calculate the state totals from county information\r\n",
    "population_data = population_data.loc[population_data['COUNTY'] > 0]\r\n",
    "population_data"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      SUMLEV  REGION  DIVISION  STATE  COUNTY   STNAME            CTYNAME  \\\n",
       "1         50       3         6      1       1  Alabama     Autauga County   \n",
       "2         50       3         6      1       3  Alabama     Baldwin County   \n",
       "3         50       3         6      1       5  Alabama     Barbour County   \n",
       "4         50       3         6      1       7  Alabama        Bibb County   \n",
       "5         50       3         6      1       9  Alabama      Blount County   \n",
       "...      ...     ...       ...    ...     ...      ...                ...   \n",
       "3189      50       4         8     56      37  Wyoming  Sweetwater County   \n",
       "3190      50       4         8     56      39  Wyoming       Teton County   \n",
       "3191      50       4         8     56      41  Wyoming       Uinta County   \n",
       "3192      50       4         8     56      43  Wyoming    Washakie County   \n",
       "3193      50       4         8     56      45  Wyoming      Weston County   \n",
       "\n",
       "     CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  RNETMIG2011  \\\n",
       "1            54571              54582            54761  ...     6.236931   \n",
       "2           182265             182263           183121  ...    16.705437   \n",
       "3            27457              27454            27325  ...     0.329254   \n",
       "4            22915              22904            22858  ...    -4.912927   \n",
       "5            57322              57322            57372  ...     0.348029   \n",
       "...            ...                ...              ...  ...          ...   \n",
       "3189         43806              43806            43580  ...     0.776433   \n",
       "3190         21294              21298            21298  ...    -2.340824   \n",
       "3191         21118              21121            21090  ...   -17.908599   \n",
       "3192          8533               8528             8531  ...   -12.837122   \n",
       "3193          7208               7208             7198  ...    -8.786611   \n",
       "\n",
       "      RNETMIG2012  RNETMIG2013  RNETMIG2014  RNETMIG2015  RNETMIG2016  \\\n",
       "1       -5.971016    -3.773344     2.206640    -1.529706     4.954403   \n",
       "2       17.670696    22.924288    20.300088    17.902273    21.436499   \n",
       "3       -6.860371    -8.093425    -5.063857   -15.677998   -18.377839   \n",
       "4       -3.789130    -5.800695     1.420612     1.286202    -0.841769   \n",
       "5       -1.597971    -0.277742    -1.997117    -1.303543    -1.217158   \n",
       "...           ...          ...          ...          ...          ...   \n",
       "3189    15.410190    -4.433558   -12.751566   -13.455712   -17.688190   \n",
       "3190     2.322071    23.284369    12.672811     4.881876     1.035867   \n",
       "3191    -4.151853   -10.624866   -15.022486   -10.381621   -11.424990   \n",
       "3192    -3.084040    -1.307423   -19.048760     0.000000   -15.064998   \n",
       "3193   -10.410072     6.194130     0.420580     9.207589     0.970201   \n",
       "\n",
       "      RNETMIG2017  RNETMIG2018  RNETMIG2019  RNETMIG2020  \n",
       "1        0.993228    -0.018021     3.486011     6.290545  \n",
       "2       22.476720    24.846335    25.242507    26.401562  \n",
       "3      -25.138734    -8.790155    -6.257064     0.649799  \n",
       "4       -3.235672    -7.271592     0.268980    -7.199262  \n",
       "5        6.193186     0.242275     0.934175     1.192544  \n",
       "...           ...          ...          ...          ...  \n",
       "3189   -20.936101   -15.589918   -10.452355    -9.510457  \n",
       "3190    -1.543805   -13.120659     0.171505     0.383943  \n",
       "3191   -18.658892   -14.135663    -8.840598    -2.177625  \n",
       "3192   -16.056321   -16.101642    -7.638447    -6.801848  \n",
       "3193   -36.081748    -9.814534     0.000000   -17.176833  \n",
       "\n",
       "[3143 rows x 180 columns]"
      ],
      "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>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>...</th>\n",
       "      <th>RNETMIG2011</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "      <th>RNETMIG2016</th>\n",
       "      <th>RNETMIG2017</th>\n",
       "      <th>RNETMIG2018</th>\n",
       "      <th>RNETMIG2019</th>\n",
       "      <th>RNETMIG2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>54571</td>\n",
       "      <td>54582</td>\n",
       "      <td>54761</td>\n",
       "      <td>...</td>\n",
       "      <td>6.236931</td>\n",
       "      <td>-5.971016</td>\n",
       "      <td>-3.773344</td>\n",
       "      <td>2.206640</td>\n",
       "      <td>-1.529706</td>\n",
       "      <td>4.954403</td>\n",
       "      <td>0.993228</td>\n",
       "      <td>-0.018021</td>\n",
       "      <td>3.486011</td>\n",
       "      <td>6.290545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>182265</td>\n",
       "      <td>182263</td>\n",
       "      <td>183121</td>\n",
       "      <td>...</td>\n",
       "      <td>16.705437</td>\n",
       "      <td>17.670696</td>\n",
       "      <td>22.924288</td>\n",
       "      <td>20.300088</td>\n",
       "      <td>17.902273</td>\n",
       "      <td>21.436499</td>\n",
       "      <td>22.476720</td>\n",
       "      <td>24.846335</td>\n",
       "      <td>25.242507</td>\n",
       "      <td>26.401562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>27457</td>\n",
       "      <td>27454</td>\n",
       "      <td>27325</td>\n",
       "      <td>...</td>\n",
       "      <td>0.329254</td>\n",
       "      <td>-6.860371</td>\n",
       "      <td>-8.093425</td>\n",
       "      <td>-5.063857</td>\n",
       "      <td>-15.677998</td>\n",
       "      <td>-18.377839</td>\n",
       "      <td>-25.138734</td>\n",
       "      <td>-8.790155</td>\n",
       "      <td>-6.257064</td>\n",
       "      <td>0.649799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>22915</td>\n",
       "      <td>22904</td>\n",
       "      <td>22858</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.912927</td>\n",
       "      <td>-3.789130</td>\n",
       "      <td>-5.800695</td>\n",
       "      <td>1.420612</td>\n",
       "      <td>1.286202</td>\n",
       "      <td>-0.841769</td>\n",
       "      <td>-3.235672</td>\n",
       "      <td>-7.271592</td>\n",
       "      <td>0.268980</td>\n",
       "      <td>-7.199262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>57322</td>\n",
       "      <td>57322</td>\n",
       "      <td>57372</td>\n",
       "      <td>...</td>\n",
       "      <td>0.348029</td>\n",
       "      <td>-1.597971</td>\n",
       "      <td>-0.277742</td>\n",
       "      <td>-1.997117</td>\n",
       "      <td>-1.303543</td>\n",
       "      <td>-1.217158</td>\n",
       "      <td>6.193186</td>\n",
       "      <td>0.242275</td>\n",
       "      <td>0.934175</td>\n",
       "      <td>1.192544</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>3189</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>37</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Sweetwater County</td>\n",
       "      <td>43806</td>\n",
       "      <td>43806</td>\n",
       "      <td>43580</td>\n",
       "      <td>...</td>\n",
       "      <td>0.776433</td>\n",
       "      <td>15.410190</td>\n",
       "      <td>-4.433558</td>\n",
       "      <td>-12.751566</td>\n",
       "      <td>-13.455712</td>\n",
       "      <td>-17.688190</td>\n",
       "      <td>-20.936101</td>\n",
       "      <td>-15.589918</td>\n",
       "      <td>-10.452355</td>\n",
       "      <td>-9.510457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3190</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>39</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton County</td>\n",
       "      <td>21294</td>\n",
       "      <td>21298</td>\n",
       "      <td>21298</td>\n",
       "      <td>...</td>\n",
       "      <td>-2.340824</td>\n",
       "      <td>2.322071</td>\n",
       "      <td>23.284369</td>\n",
       "      <td>12.672811</td>\n",
       "      <td>4.881876</td>\n",
       "      <td>1.035867</td>\n",
       "      <td>-1.543805</td>\n",
       "      <td>-13.120659</td>\n",
       "      <td>0.171505</td>\n",
       "      <td>0.383943</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3191</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>41</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>21118</td>\n",
       "      <td>21121</td>\n",
       "      <td>21090</td>\n",
       "      <td>...</td>\n",
       "      <td>-17.908599</td>\n",
       "      <td>-4.151853</td>\n",
       "      <td>-10.624866</td>\n",
       "      <td>-15.022486</td>\n",
       "      <td>-10.381621</td>\n",
       "      <td>-11.424990</td>\n",
       "      <td>-18.658892</td>\n",
       "      <td>-14.135663</td>\n",
       "      <td>-8.840598</td>\n",
       "      <td>-2.177625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>43</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>8533</td>\n",
       "      <td>8528</td>\n",
       "      <td>8531</td>\n",
       "      <td>...</td>\n",
       "      <td>-12.837122</td>\n",
       "      <td>-3.084040</td>\n",
       "      <td>-1.307423</td>\n",
       "      <td>-19.048760</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-15.064998</td>\n",
       "      <td>-16.056321</td>\n",
       "      <td>-16.101642</td>\n",
       "      <td>-7.638447</td>\n",
       "      <td>-6.801848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>7208</td>\n",
       "      <td>7208</td>\n",
       "      <td>7198</td>\n",
       "      <td>...</td>\n",
       "      <td>-8.786611</td>\n",
       "      <td>-10.410072</td>\n",
       "      <td>6.194130</td>\n",
       "      <td>0.420580</td>\n",
       "      <td>9.207589</td>\n",
       "      <td>0.970201</td>\n",
       "      <td>-36.081748</td>\n",
       "      <td>-9.814534</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-17.176833</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3143 rows × 180 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "source": [
    "\r\n",
    "# converted state and county fields from int to str\r\n",
    "population_data['STATE'] = population_data['STATE'].astype('str')\r\n",
    "population_data['COUNTY'] = population_data['COUNTY'].astype('str')\r\n",
    "\r\n",
    "# filled with leading zeros to get the format required for fips_county\r\n",
    "population_data['STATE']=population_data['STATE'].apply(lambda x: x.zfill(2))\r\n",
    "population_data['COUNTY']=population_data['COUNTY'].apply(lambda x: x.zfill(3))\r\n",
    "\r\n",
    "# concatenated state and county fips to make a County_Fips Code that follows the Fips_County convention\r\n",
    "population_data['fips'] = population_data['STATE'] + population_data['COUNTY']\r\n",
    "# population_data['fips'] \r\n",
    "population_data\r\n"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      SUMLEV  REGION  DIVISION STATE COUNTY   STNAME            CTYNAME  \\\n",
       "1         50       3         6    01    001  Alabama     Autauga County   \n",
       "2         50       3         6    01    003  Alabama     Baldwin County   \n",
       "3         50       3         6    01    005  Alabama     Barbour County   \n",
       "4         50       3         6    01    007  Alabama        Bibb County   \n",
       "5         50       3         6    01    009  Alabama      Blount County   \n",
       "...      ...     ...       ...   ...    ...      ...                ...   \n",
       "3189      50       4         8    56    037  Wyoming  Sweetwater County   \n",
       "3190      50       4         8    56    039  Wyoming       Teton County   \n",
       "3191      50       4         8    56    041  Wyoming       Uinta County   \n",
       "3192      50       4         8    56    043  Wyoming    Washakie County   \n",
       "3193      50       4         8    56    045  Wyoming      Weston County   \n",
       "\n",
       "     CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  ...  RNETMIG2012  \\\n",
       "1            54571              54582            54761  ...    -5.971016   \n",
       "2           182265             182263           183121  ...    17.670696   \n",
       "3            27457              27454            27325  ...    -6.860371   \n",
       "4            22915              22904            22858  ...    -3.789130   \n",
       "5            57322              57322            57372  ...    -1.597971   \n",
       "...            ...                ...              ...  ...          ...   \n",
       "3189         43806              43806            43580  ...    15.410190   \n",
       "3190         21294              21298            21298  ...     2.322071   \n",
       "3191         21118              21121            21090  ...    -4.151853   \n",
       "3192          8533               8528             8531  ...    -3.084040   \n",
       "3193          7208               7208             7198  ...   -10.410072   \n",
       "\n",
       "      RNETMIG2013  RNETMIG2014  RNETMIG2015  RNETMIG2016  RNETMIG2017  \\\n",
       "1       -3.773344     2.206640    -1.529706     4.954403     0.993228   \n",
       "2       22.924288    20.300088    17.902273    21.436499    22.476720   \n",
       "3       -8.093425    -5.063857   -15.677998   -18.377839   -25.138734   \n",
       "4       -5.800695     1.420612     1.286202    -0.841769    -3.235672   \n",
       "5       -0.277742    -1.997117    -1.303543    -1.217158     6.193186   \n",
       "...           ...          ...          ...          ...          ...   \n",
       "3189    -4.433558   -12.751566   -13.455712   -17.688190   -20.936101   \n",
       "3190    23.284369    12.672811     4.881876     1.035867    -1.543805   \n",
       "3191   -10.624866   -15.022486   -10.381621   -11.424990   -18.658892   \n",
       "3192    -1.307423   -19.048760     0.000000   -15.064998   -16.056321   \n",
       "3193     6.194130     0.420580     9.207589     0.970201   -36.081748   \n",
       "\n",
       "      RNETMIG2018  RNETMIG2019  RNETMIG2020   fips  \n",
       "1       -0.018021     3.486011     6.290545  01001  \n",
       "2       24.846335    25.242507    26.401562  01003  \n",
       "3       -8.790155    -6.257064     0.649799  01005  \n",
       "4       -7.271592     0.268980    -7.199262  01007  \n",
       "5        0.242275     0.934175     1.192544  01009  \n",
       "...           ...          ...          ...    ...  \n",
       "3189   -15.589918   -10.452355    -9.510457  56037  \n",
       "3190   -13.120659     0.171505     0.383943  56039  \n",
       "3191   -14.135663    -8.840598    -2.177625  56041  \n",
       "3192   -16.101642    -7.638447    -6.801848  56043  \n",
       "3193    -9.814534     0.000000   -17.176833  56045  \n",
       "\n",
       "[3143 rows x 181 columns]"
      ],
      "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>REGION</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CTYNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>...</th>\n",
       "      <th>RNETMIG2012</th>\n",
       "      <th>RNETMIG2013</th>\n",
       "      <th>RNETMIG2014</th>\n",
       "      <th>RNETMIG2015</th>\n",
       "      <th>RNETMIG2016</th>\n",
       "      <th>RNETMIG2017</th>\n",
       "      <th>RNETMIG2018</th>\n",
       "      <th>RNETMIG2019</th>\n",
       "      <th>RNETMIG2020</th>\n",
       "      <th>fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>001</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>54571</td>\n",
       "      <td>54582</td>\n",
       "      <td>54761</td>\n",
       "      <td>...</td>\n",
       "      <td>-5.971016</td>\n",
       "      <td>-3.773344</td>\n",
       "      <td>2.206640</td>\n",
       "      <td>-1.529706</td>\n",
       "      <td>4.954403</td>\n",
       "      <td>0.993228</td>\n",
       "      <td>-0.018021</td>\n",
       "      <td>3.486011</td>\n",
       "      <td>6.290545</td>\n",
       "      <td>01001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>003</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>182265</td>\n",
       "      <td>182263</td>\n",
       "      <td>183121</td>\n",
       "      <td>...</td>\n",
       "      <td>17.670696</td>\n",
       "      <td>22.924288</td>\n",
       "      <td>20.300088</td>\n",
       "      <td>17.902273</td>\n",
       "      <td>21.436499</td>\n",
       "      <td>22.476720</td>\n",
       "      <td>24.846335</td>\n",
       "      <td>25.242507</td>\n",
       "      <td>26.401562</td>\n",
       "      <td>01003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>005</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Barbour County</td>\n",
       "      <td>27457</td>\n",
       "      <td>27454</td>\n",
       "      <td>27325</td>\n",
       "      <td>...</td>\n",
       "      <td>-6.860371</td>\n",
       "      <td>-8.093425</td>\n",
       "      <td>-5.063857</td>\n",
       "      <td>-15.677998</td>\n",
       "      <td>-18.377839</td>\n",
       "      <td>-25.138734</td>\n",
       "      <td>-8.790155</td>\n",
       "      <td>-6.257064</td>\n",
       "      <td>0.649799</td>\n",
       "      <td>01005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>007</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Bibb County</td>\n",
       "      <td>22915</td>\n",
       "      <td>22904</td>\n",
       "      <td>22858</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.789130</td>\n",
       "      <td>-5.800695</td>\n",
       "      <td>1.420612</td>\n",
       "      <td>1.286202</td>\n",
       "      <td>-0.841769</td>\n",
       "      <td>-3.235672</td>\n",
       "      <td>-7.271592</td>\n",
       "      <td>0.268980</td>\n",
       "      <td>-7.199262</td>\n",
       "      <td>01007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>50</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>01</td>\n",
       "      <td>009</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>57322</td>\n",
       "      <td>57322</td>\n",
       "      <td>57372</td>\n",
       "      <td>...</td>\n",
       "      <td>-1.597971</td>\n",
       "      <td>-0.277742</td>\n",
       "      <td>-1.997117</td>\n",
       "      <td>-1.303543</td>\n",
       "      <td>-1.217158</td>\n",
       "      <td>6.193186</td>\n",
       "      <td>0.242275</td>\n",
       "      <td>0.934175</td>\n",
       "      <td>1.192544</td>\n",
       "      <td>01009</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>3189</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>037</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Sweetwater County</td>\n",
       "      <td>43806</td>\n",
       "      <td>43806</td>\n",
       "      <td>43580</td>\n",
       "      <td>...</td>\n",
       "      <td>15.410190</td>\n",
       "      <td>-4.433558</td>\n",
       "      <td>-12.751566</td>\n",
       "      <td>-13.455712</td>\n",
       "      <td>-17.688190</td>\n",
       "      <td>-20.936101</td>\n",
       "      <td>-15.589918</td>\n",
       "      <td>-10.452355</td>\n",
       "      <td>-9.510457</td>\n",
       "      <td>56037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3190</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>039</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Teton County</td>\n",
       "      <td>21294</td>\n",
       "      <td>21298</td>\n",
       "      <td>21298</td>\n",
       "      <td>...</td>\n",
       "      <td>2.322071</td>\n",
       "      <td>23.284369</td>\n",
       "      <td>12.672811</td>\n",
       "      <td>4.881876</td>\n",
       "      <td>1.035867</td>\n",
       "      <td>-1.543805</td>\n",
       "      <td>-13.120659</td>\n",
       "      <td>0.171505</td>\n",
       "      <td>0.383943</td>\n",
       "      <td>56039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3191</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>041</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Uinta County</td>\n",
       "      <td>21118</td>\n",
       "      <td>21121</td>\n",
       "      <td>21090</td>\n",
       "      <td>...</td>\n",
       "      <td>-4.151853</td>\n",
       "      <td>-10.624866</td>\n",
       "      <td>-15.022486</td>\n",
       "      <td>-10.381621</td>\n",
       "      <td>-11.424990</td>\n",
       "      <td>-18.658892</td>\n",
       "      <td>-14.135663</td>\n",
       "      <td>-8.840598</td>\n",
       "      <td>-2.177625</td>\n",
       "      <td>56041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>043</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Washakie County</td>\n",
       "      <td>8533</td>\n",
       "      <td>8528</td>\n",
       "      <td>8531</td>\n",
       "      <td>...</td>\n",
       "      <td>-3.084040</td>\n",
       "      <td>-1.307423</td>\n",
       "      <td>-19.048760</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-15.064998</td>\n",
       "      <td>-16.056321</td>\n",
       "      <td>-16.101642</td>\n",
       "      <td>-7.638447</td>\n",
       "      <td>-6.801848</td>\n",
       "      <td>56043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "      <td>045</td>\n",
       "      <td>Wyoming</td>\n",
       "      <td>Weston County</td>\n",
       "      <td>7208</td>\n",
       "      <td>7208</td>\n",
       "      <td>7198</td>\n",
       "      <td>...</td>\n",
       "      <td>-10.410072</td>\n",
       "      <td>6.194130</td>\n",
       "      <td>0.420580</td>\n",
       "      <td>9.207589</td>\n",
       "      <td>0.970201</td>\n",
       "      <td>-36.081748</td>\n",
       "      <td>-9.814534</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-17.176833</td>\n",
       "      <td>56045</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3143 rows × 181 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "source": [
    "population_data = population_data[['fips','POPESTIMATE2016','POPESTIMATE2017','POPESTIMATE2018','POPESTIMATE2019',\r\n",
    "'POPESTIMATE2020', 'BIRTHS2016','BIRTHS2017','BIRTHS2018','BIRTHS2019','BIRTHS2020',\r\n",
    "'DEATHS2016','DEATHS2017','DEATHS2018','DEATHS2019','DEATHS2020']]\r\n",
    "population_data\r\n",
    "# Table is ready to load"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       fips  POPESTIMATE2016  POPESTIMATE2017  POPESTIMATE2018  \\\n",
       "1     01001            55302            55448            55533   \n",
       "2     01003           207787           212737           218071   \n",
       "3     01005            25828            25169            24887   \n",
       "4     01007            22590            22532            22300   \n",
       "5     01009            57487            57801            57770   \n",
       "...     ...              ...              ...              ...   \n",
       "3189  56037            44319            43663            43188   \n",
       "3190  56039            23255            23383            23261   \n",
       "3191  56041            20711            20449            20299   \n",
       "3192  56043             8180             8013             7886   \n",
       "3193  56045             7228             6962             6895   \n",
       "\n",
       "      POPESTIMATE2019  POPESTIMATE2020  BIRTHS2016  BIRTHS2017  BIRTHS2018  \\\n",
       "1               55769            56145         675         667         651   \n",
       "2              223565           229287        2286        2313        2297   \n",
       "3               24657            24589         284         276         273   \n",
       "4               22313            22136         299         267         243   \n",
       "5               57840            57879         675         681         675   \n",
       "...               ...              ...         ...         ...         ...   \n",
       "3189            42917            42673         626         571         529   \n",
       "3190            23385            23497         245         244         255   \n",
       "3191            20196            20215         312         272         267   \n",
       "3192             7824             7760         101          61          88   \n",
       "3193             6880             6743          89          58          67   \n",
       "\n",
       "      BIRTHS2019  BIRTHS2020  DEATHS2016  DEATHS2017  DEATHS2018  DEATHS2019  \\\n",
       "1            592         606         547         574         563         552   \n",
       "2           2322        2317        2021        2103        2325        2386   \n",
       "3            248         250         279         297         335         322   \n",
       "4            238         249         243         251         314         232   \n",
       "5            673         665         651         721         718         658   \n",
       "...          ...         ...         ...         ...         ...         ...   \n",
       "3189         481         481         303         312         331         301   \n",
       "3190         211         225          97          78          72          90   \n",
       "3191         232         232         143         151         130         156   \n",
       "3192          81          73          78         101          86          81   \n",
       "3193          53          53          69          69          68          67   \n",
       "\n",
       "      DEATHS2020  \n",
       "1            582  \n",
       "2           2543  \n",
       "3            334  \n",
       "4            266  \n",
       "5            697  \n",
       "...          ...  \n",
       "3189         319  \n",
       "3190         124  \n",
       "3191         171  \n",
       "3192          85  \n",
       "3193          72  \n",
       "\n",
       "[3143 rows x 16 columns]"
      ],
      "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>POPESTIMATE2016</th>\n",
       "      <th>POPESTIMATE2017</th>\n",
       "      <th>POPESTIMATE2018</th>\n",
       "      <th>POPESTIMATE2019</th>\n",
       "      <th>POPESTIMATE2020</th>\n",
       "      <th>BIRTHS2016</th>\n",
       "      <th>BIRTHS2017</th>\n",
       "      <th>BIRTHS2018</th>\n",
       "      <th>BIRTHS2019</th>\n",
       "      <th>BIRTHS2020</th>\n",
       "      <th>DEATHS2016</th>\n",
       "      <th>DEATHS2017</th>\n",
       "      <th>DEATHS2018</th>\n",
       "      <th>DEATHS2019</th>\n",
       "      <th>DEATHS2020</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01001</td>\n",
       "      <td>55302</td>\n",
       "      <td>55448</td>\n",
       "      <td>55533</td>\n",
       "      <td>55769</td>\n",
       "      <td>56145</td>\n",
       "      <td>675</td>\n",
       "      <td>667</td>\n",
       "      <td>651</td>\n",
       "      <td>592</td>\n",
       "      <td>606</td>\n",
       "      <td>547</td>\n",
       "      <td>574</td>\n",
       "      <td>563</td>\n",
       "      <td>552</td>\n",
       "      <td>582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01003</td>\n",
       "      <td>207787</td>\n",
       "      <td>212737</td>\n",
       "      <td>218071</td>\n",
       "      <td>223565</td>\n",
       "      <td>229287</td>\n",
       "      <td>2286</td>\n",
       "      <td>2313</td>\n",
       "      <td>2297</td>\n",
       "      <td>2322</td>\n",
       "      <td>2317</td>\n",
       "      <td>2021</td>\n",
       "      <td>2103</td>\n",
       "      <td>2325</td>\n",
       "      <td>2386</td>\n",
       "      <td>2543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01005</td>\n",
       "      <td>25828</td>\n",
       "      <td>25169</td>\n",
       "      <td>24887</td>\n",
       "      <td>24657</td>\n",
       "      <td>24589</td>\n",
       "      <td>284</td>\n",
       "      <td>276</td>\n",
       "      <td>273</td>\n",
       "      <td>248</td>\n",
       "      <td>250</td>\n",
       "      <td>279</td>\n",
       "      <td>297</td>\n",
       "      <td>335</td>\n",
       "      <td>322</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01007</td>\n",
       "      <td>22590</td>\n",
       "      <td>22532</td>\n",
       "      <td>22300</td>\n",
       "      <td>22313</td>\n",
       "      <td>22136</td>\n",
       "      <td>299</td>\n",
       "      <td>267</td>\n",
       "      <td>243</td>\n",
       "      <td>238</td>\n",
       "      <td>249</td>\n",
       "      <td>243</td>\n",
       "      <td>251</td>\n",
       "      <td>314</td>\n",
       "      <td>232</td>\n",
       "      <td>266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>01009</td>\n",
       "      <td>57487</td>\n",
       "      <td>57801</td>\n",
       "      <td>57770</td>\n",
       "      <td>57840</td>\n",
       "      <td>57879</td>\n",
       "      <td>675</td>\n",
       "      <td>681</td>\n",
       "      <td>675</td>\n",
       "      <td>673</td>\n",
       "      <td>665</td>\n",
       "      <td>651</td>\n",
       "      <td>721</td>\n",
       "      <td>718</td>\n",
       "      <td>658</td>\n",
       "      <td>697</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3189</th>\n",
       "      <td>56037</td>\n",
       "      <td>44319</td>\n",
       "      <td>43663</td>\n",
       "      <td>43188</td>\n",
       "      <td>42917</td>\n",
       "      <td>42673</td>\n",
       "      <td>626</td>\n",
       "      <td>571</td>\n",
       "      <td>529</td>\n",
       "      <td>481</td>\n",
       "      <td>481</td>\n",
       "      <td>303</td>\n",
       "      <td>312</td>\n",
       "      <td>331</td>\n",
       "      <td>301</td>\n",
       "      <td>319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3190</th>\n",
       "      <td>56039</td>\n",
       "      <td>23255</td>\n",
       "      <td>23383</td>\n",
       "      <td>23261</td>\n",
       "      <td>23385</td>\n",
       "      <td>23497</td>\n",
       "      <td>245</td>\n",
       "      <td>244</td>\n",
       "      <td>255</td>\n",
       "      <td>211</td>\n",
       "      <td>225</td>\n",
       "      <td>97</td>\n",
       "      <td>78</td>\n",
       "      <td>72</td>\n",
       "      <td>90</td>\n",
       "      <td>124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3191</th>\n",
       "      <td>56041</td>\n",
       "      <td>20711</td>\n",
       "      <td>20449</td>\n",
       "      <td>20299</td>\n",
       "      <td>20196</td>\n",
       "      <td>20215</td>\n",
       "      <td>312</td>\n",
       "      <td>272</td>\n",
       "      <td>267</td>\n",
       "      <td>232</td>\n",
       "      <td>232</td>\n",
       "      <td>143</td>\n",
       "      <td>151</td>\n",
       "      <td>130</td>\n",
       "      <td>156</td>\n",
       "      <td>171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3192</th>\n",
       "      <td>56043</td>\n",
       "      <td>8180</td>\n",
       "      <td>8013</td>\n",
       "      <td>7886</td>\n",
       "      <td>7824</td>\n",
       "      <td>7760</td>\n",
       "      <td>101</td>\n",
       "      <td>61</td>\n",
       "      <td>88</td>\n",
       "      <td>81</td>\n",
       "      <td>73</td>\n",
       "      <td>78</td>\n",
       "      <td>101</td>\n",
       "      <td>86</td>\n",
       "      <td>81</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>56045</td>\n",
       "      <td>7228</td>\n",
       "      <td>6962</td>\n",
       "      <td>6895</td>\n",
       "      <td>6880</td>\n",
       "      <td>6743</td>\n",
       "      <td>89</td>\n",
       "      <td>58</td>\n",
       "      <td>67</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>69</td>\n",
       "      <td>69</td>\n",
       "      <td>68</td>\n",
       "      <td>67</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3143 rows × 16 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "\r\n",
    "# LOAD US_Census_Data.csv\r\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "source": [
    "# Write to US_Census_Data.csv that can be imported in PostgreSQL\r\n",
    "population_data.to_csv('Resources/Transformed_Data/US_Census_Data.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Transform"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Table 5 : 'WHO_Flu_data'"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "who_flu_df = pd.read_csv('Resources/Source_Data/WHO_NREVSS_Clinical_Labs.csv')\r\n",
    "#who_flu_df\r\n",
    "\r\n",
    "# Drop Region Type\r\n",
    "who_flu_df.drop(columns=['REGION TYPE'], inplace=True) \r\n",
    "\r\n",
    "# Replace 'X' with -1\r\n",
    "who_flu_df.loc[who_flu_df['REGION'] == 'New York City', 'REGION'] = 'New York'\r\n",
    "who_flu_df.loc[who_flu_df['TOTAL SPECIMENS'] == 'X', 'TOTAL SPECIMENS'] = -1\r\n",
    "who_flu_df.loc[who_flu_df['TOTAL A'] == 'X', 'TOTAL A'] = -1\r\n",
    "who_flu_df.loc[who_flu_df['TOTAL B'] == 'X', 'TOTAL B'] = -1\r\n",
    "who_flu_df.loc[who_flu_df['PERCENT POSITIVE'] == 'X', 'PERCENT POSITIVE'] = -1\r\n",
    "who_flu_df.loc[who_flu_df['PERCENT A'] == 'X', 'PERCENT A'] = -1\r\n",
    "who_flu_df.loc[who_flu_df['PERCENT B'] == 'X', 'PERCENT B'] = -1\r\n",
    "who_flu_df"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "              REGION  YEAR  WEEK TOTAL SPECIMENS TOTAL A TOTAL B  \\\n",
       "0            Alabama  2019    40             512       2      13   \n",
       "1             Alaska  2019    40              -1      -1      -1   \n",
       "2            Arizona  2019    40             278       0       2   \n",
       "3           Arkansas  2019    40              89       0       0   \n",
       "4         California  2019    40            1776      18      10   \n",
       "...              ...   ...   ...             ...     ...     ...   \n",
       "5125       Wisconsin  2021    29              -1      -1      -1   \n",
       "5126         Wyoming  2021    29              -1      -1      -1   \n",
       "5127     Puerto Rico  2021    29               0       0       0   \n",
       "5128  Virgin Islands  2021    29              -1      -1      -1   \n",
       "5129        New York  2021    29              -1      -1      -1   \n",
       "\n",
       "     PERCENT POSITIVE PERCENT A PERCENT B  \n",
       "0                2.93      0.39      2.54  \n",
       "1                  -1        -1        -1  \n",
       "2                0.72         0      0.72  \n",
       "3                   0         0         0  \n",
       "4                1.58      1.01      0.56  \n",
       "...               ...       ...       ...  \n",
       "5125               -1        -1        -1  \n",
       "5126               -1        -1        -1  \n",
       "5127                0         0         0  \n",
       "5128               -1        -1        -1  \n",
       "5129               -1        -1        -1  \n",
       "\n",
       "[5130 rows x 9 columns]"
      ],
      "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>REGION</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>WEEK</th>\n",
       "      <th>TOTAL SPECIMENS</th>\n",
       "      <th>TOTAL A</th>\n",
       "      <th>TOTAL B</th>\n",
       "      <th>PERCENT POSITIVE</th>\n",
       "      <th>PERCENT A</th>\n",
       "      <th>PERCENT B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>512</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>2.93</td>\n",
       "      <td>0.39</td>\n",
       "      <td>2.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>278</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.72</td>\n",
       "      <td>0</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>1776</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>1.58</td>\n",
       "      <td>1.01</td>\n",
       "      <td>0.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5125</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5126</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5127</th>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128</th>\n",
       "      <td>Virgin Islands</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>New York</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5130 rows × 9 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "# Add State_Fips and Remove Region\r\n",
    "# Strip spaced\r\n",
    "who_flu_df['REGION'].apply(lambda x: x.strip()) \r\n",
    "\r\n",
    "# Merge with US_States\r\n",
    "who_flu_table = who_flu_df.merge(states_df, how='left', left_on='REGION', right_on='Sname')\r\n",
    "who_flu_table = who_flu_table[['State_Fips','YEAR','WEEK','TOTAL SPECIMENS','TOTAL A','TOTAL B','PERCENT POSITIVE',\r\n",
    "'PERCENT A','PERCENT B']]\r\n",
    "who_flu_table\r\n",
    "# Table is ready to load"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      State_Fips  YEAR  WEEK TOTAL SPECIMENS TOTAL A TOTAL B PERCENT POSITIVE  \\\n",
       "0              1  2019    40             512       2      13             2.93   \n",
       "1              2  2019    40              -1      -1      -1               -1   \n",
       "2              4  2019    40             278       0       2             0.72   \n",
       "3              5  2019    40              89       0       0                0   \n",
       "4              6  2019    40            1776      18      10             1.58   \n",
       "...          ...   ...   ...             ...     ...     ...              ...   \n",
       "5125          55  2021    29              -1      -1      -1               -1   \n",
       "5126          56  2021    29              -1      -1      -1               -1   \n",
       "5127          72  2021    29               0       0       0                0   \n",
       "5128          78  2021    29              -1      -1      -1               -1   \n",
       "5129          36  2021    29              -1      -1      -1               -1   \n",
       "\n",
       "     PERCENT A PERCENT B  \n",
       "0         0.39      2.54  \n",
       "1           -1        -1  \n",
       "2            0      0.72  \n",
       "3            0         0  \n",
       "4         1.01      0.56  \n",
       "...        ...       ...  \n",
       "5125        -1        -1  \n",
       "5126        -1        -1  \n",
       "5127         0         0  \n",
       "5128        -1        -1  \n",
       "5129        -1        -1  \n",
       "\n",
       "[5130 rows x 9 columns]"
      ],
      "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_Fips</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>WEEK</th>\n",
       "      <th>TOTAL SPECIMENS</th>\n",
       "      <th>TOTAL A</th>\n",
       "      <th>TOTAL B</th>\n",
       "      <th>PERCENT POSITIVE</th>\n",
       "      <th>PERCENT A</th>\n",
       "      <th>PERCENT B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>512</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>2.93</td>\n",
       "      <td>0.39</td>\n",
       "      <td>2.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>278</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.72</td>\n",
       "      <td>0</td>\n",
       "      <td>0.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>89</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>2019</td>\n",
       "      <td>40</td>\n",
       "      <td>1776</td>\n",
       "      <td>18</td>\n",
       "      <td>10</td>\n",
       "      <td>1.58</td>\n",
       "      <td>1.01</td>\n",
       "      <td>0.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5125</th>\n",
       "      <td>55</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5126</th>\n",
       "      <td>56</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5127</th>\n",
       "      <td>72</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5128</th>\n",
       "      <td>78</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5129</th>\n",
       "      <td>36</td>\n",
       "      <td>2021</td>\n",
       "      <td>29</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5130 rows × 9 columns</p>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# LOAD WHO_Flu_Data.csv"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "# Write to WHO_Flu_Data.csv that can be imported in PostgreSQL\r\n",
    "who_flu_table.to_csv('Resources/Transformed_Data/WHO_Flu_Data.csv', index=False)"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python",
   "version": "3.8.5",
   "mimetype": "text/x-python",
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "pygments_lexer": "ipython3",
   "nbconvert_exporter": "python",
   "file_extension": ".py"
  },
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.8.5 64-bit ('base': conda)"
  },
  "interpreter": {
   "hash": "eb0a5deb85624cf39522bd9ea766efe3b585b85d5e69ded4a98e5c69611b9570"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}