EDA_Final_Proj.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "EDA Final Proj.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "source": [
        "# Data Transformation for COVID-19 Data Visualization"
      ],
      "metadata": {
        "id": "MwI_PvfuPvYX"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "---"
      ],
      "metadata": {
        "id": "3wvaIXwpP57m"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Imports"
      ],
      "metadata": {
        "id": "MDhU-q0QP35x"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AIbdFROv0EX0"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import pickle"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gr030bxgG8lL",
        "outputId": "03bec65d-365b-488e-d887-ea56e1f186aa"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Global Variables and Helper Functions"
      ],
      "metadata": {
        "id": "wdgpxwKoQOdL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "path = 'drive/MyDrive/edav_final_project/'"
      ],
      "metadata": {
        "id": "MRKNEIxiGRbJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def save_data(df, fname):\n",
        "    with open(fname, 'wb') as outfile:\n",
        "        pickle.dump(df, outfile)\n",
        "\n",
        "def load_data(fname):\n",
        "    with open(fname, 'rb') as infile:\n",
        "        return pickle.load(infile)"
      ],
      "metadata": {
        "id": "TkfZFQ0VGWvM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def check_na(df):\n",
        "    return df.isna().sum()/len(df)"
      ],
      "metadata": {
        "id": "Kku6jzD2Yn9v"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Data Transformation"
      ],
      "metadata": {
        "id": "qWsIcB-kQWWJ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Retrieve Country Code and US State Code from the Index Table"
      ],
      "metadata": {
        "id": "xfl8WJuXRchT"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "index = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/index.csv')\n",
        "index.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 244
        },
        "id": "RKR6kbTtRp0r",
        "outputId": "b4fbdee1-0a38-4223-bbe7-1640f37eff4e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>location_key</th>\n",
              "      <th>place_id</th>\n",
              "      <th>wikidata_id</th>\n",
              "      <th>datacommons_id</th>\n",
              "      <th>country_code</th>\n",
              "      <th>country_name</th>\n",
              "      <th>subregion1_code</th>\n",
              "      <th>subregion1_name</th>\n",
              "      <th>subregion2_code</th>\n",
              "      <th>subregion2_name</th>\n",
              "      <th>locality_code</th>\n",
              "      <th>locality_name</th>\n",
              "      <th>iso_3166_1_alpha_2</th>\n",
              "      <th>iso_3166_1_alpha_3</th>\n",
              "      <th>aggregation_level</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AD</td>\n",
              "      <td>ChIJlfCemC71pRIRkn_qeNc-yQc</td>\n",
              "      <td>Q228</td>\n",
              "      <td>country/AND</td>\n",
              "      <td>AD</td>\n",
              "      <td>Andorra</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AD</td>\n",
              "      <td>AND</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AE</td>\n",
              "      <td>ChIJvRKrsd9IXj4RpwoIwFYv0zM</td>\n",
              "      <td>Q878</td>\n",
              "      <td>country/ARE</td>\n",
              "      <td>AE</td>\n",
              "      <td>United Arab Emirates</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AE</td>\n",
              "      <td>ARE</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AF</td>\n",
              "      <td>ChIJbQL_-LZu0TgReNqWvg1GtfM</td>\n",
              "      <td>Q889</td>\n",
              "      <td>country/AFG</td>\n",
              "      <td>AF</td>\n",
              "      <td>Afghanistan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AF</td>\n",
              "      <td>AFG</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AF_BAL</td>\n",
              "      <td>ChIJWZOescNSyzgRkmVJ8fmfR3k</td>\n",
              "      <td>Q121104</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AF</td>\n",
              "      <td>Afghanistan</td>\n",
              "      <td>BAL</td>\n",
              "      <td>Balkh</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AF</td>\n",
              "      <td>AFG</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AF_BAM</td>\n",
              "      <td>ChIJw-bKQwDr0jgRQiw8jHsQ-HI</td>\n",
              "      <td>Q171382</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AF</td>\n",
              "      <td>Afghanistan</td>\n",
              "      <td>BAM</td>\n",
              "      <td>Bamyan</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>AF</td>\n",
              "      <td>AFG</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  location_key  ... aggregation_level\n",
              "0           AD  ...                 0\n",
              "1           AE  ...                 0\n",
              "2           AF  ...                 0\n",
              "3       AF_BAL  ...                 1\n",
              "4       AF_BAM  ...                 1\n",
              "\n",
              "[5 rows x 15 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "country_key = index[index['aggregation_level']==0].location_key.unique()\n",
        "us_index = index[index['country_code']=='US']\n",
        "us_state_key = us_index[us_index['aggregation_level']==1].location_key.unique()"
      ],
      "metadata": {
        "id": "QAorDyyjRzML"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "save_data(country_key, path+\"country_key.pkl\")\n",
        "save_data(us_state_key, path+\"us_state_key.pkl\")"
      ],
      "metadata": {
        "id": "YS9Umx1aR0Gv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Epidemiology Table"
      ],
      "metadata": {
        "id": "2iwLCCApQeSS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# epidemiology table on COVID-19 cases, deaths, recoveries and tests\n",
        "epi_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv')\n",
        "epi_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "vIuOZGy30NbF",
        "outputId": "d5b31a11-90cb-4db8-b001-61df3d7b3694"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>new_confirmed</th>\n",
              "      <th>new_deceased</th>\n",
              "      <th>new_recovered</th>\n",
              "      <th>new_tested</th>\n",
              "      <th>cumulative_confirmed</th>\n",
              "      <th>cumulative_deceased</th>\n",
              "      <th>cumulative_recovered</th>\n",
              "      <th>cumulative_tested</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-01-01</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-01-02</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-01-03</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-01-04</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-01-05</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         date location_key  ...  cumulative_recovered  cumulative_tested\n",
              "0  2020-01-01           AD  ...                   NaN                NaN\n",
              "1  2020-01-02           AD  ...                   NaN                NaN\n",
              "2  2020-01-03           AD  ...                   NaN                NaN\n",
              "3  2020-01-04           AD  ...                   NaN                NaN\n",
              "4  2020-01-05           AD  ...                   NaN                NaN\n",
              "\n",
              "[5 rows x 10 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(epi_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Cz0kbhPd8L58",
        "outputId": "60dd085a-fb38-4a6c-e072-97fd383bb057"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "9510741"
            ]
          },
          "metadata": {},
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(epi_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "s0eZKuMX9Z3t",
        "outputId": "820df94f-6b45-4fbe-d435-7e7d94f774f7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                    0.000000\n",
              "location_key            0.000074\n",
              "new_confirmed           0.000846\n",
              "new_deceased            0.060718\n",
              "new_recovered           0.630742\n",
              "new_tested              0.714495\n",
              "cumulative_confirmed    0.013967\n",
              "cumulative_deceased     0.074094\n",
              "cumulative_recovered    0.630973\n",
              "cumulative_tested       0.727352\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "cols = ['date', 'location_key', 'new_confirmed', 'new_deceased', 'cumulative_confirmed', 'cumulative_deceased']"
      ],
      "metadata": {
        "id": "m_XkS33r_8NB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "epi_us_state = epi_full[epi_full['location_key'].isin(us_state_key)][cols]\n",
        "epi_country = epi_full[epi_full['location_key'].isin(country_key)][cols]"
      ],
      "metadata": {
        "id": "-XqMjtvkRM8b"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "epi_us_state.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "bi1KUkvVSeNp",
        "outputId": "e09f6a93-8c2c-4a87-ecfc-0320383cdd1e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>new_confirmed</th>\n",
              "      <th>new_deceased</th>\n",
              "      <th>cumulative_confirmed</th>\n",
              "      <th>cumulative_deceased</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>7437244</th>\n",
              "      <td>2020-01-22</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7437245</th>\n",
              "      <td>2020-01-23</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7437246</th>\n",
              "      <td>2020-01-24</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7437247</th>\n",
              "      <td>2020-01-25</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7437248</th>\n",
              "      <td>2020-01-26</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               date location_key  ...  cumulative_confirmed  cumulative_deceased\n",
              "7437244  2020-01-22        US_AK  ...                   0.0                  0.0\n",
              "7437245  2020-01-23        US_AK  ...                   0.0                  0.0\n",
              "7437246  2020-01-24        US_AK  ...                   0.0                  0.0\n",
              "7437247  2020-01-25        US_AK  ...                   0.0                  0.0\n",
              "7437248  2020-01-26        US_AK  ...                   0.0                  0.0\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 22
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(epi_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GKsufp5-Sx0g",
        "outputId": "62098ed2-937d-4077-bd5a-1b34fc0de0ed"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "38537"
            ]
          },
          "metadata": {},
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(epi_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ToHGyceZSWhE",
        "outputId": "a159e2ee-ac06-45ef-cafe-44db4b757cdc"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                    0.000000\n",
              "location_key            0.000000\n",
              "new_confirmed           0.000000\n",
              "new_deceased            0.000000\n",
              "cumulative_confirmed    0.000026\n",
              "cumulative_deceased     0.000208\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "epi_country.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "-Jv8XZYYSgQ9",
        "outputId": "91707187-7938-464e-dd43-e5147b5b393a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>new_confirmed</th>\n",
              "      <th>new_deceased</th>\n",
              "      <th>cumulative_confirmed</th>\n",
              "      <th>cumulative_deceased</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-01-01</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-01-02</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-01-03</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-01-04</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-01-05</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         date location_key  ...  cumulative_confirmed  cumulative_deceased\n",
              "0  2020-01-01           AD  ...                   0.0                  0.0\n",
              "1  2020-01-02           AD  ...                   0.0                  0.0\n",
              "2  2020-01-03           AD  ...                   0.0                  0.0\n",
              "3  2020-01-04           AD  ...                   0.0                  0.0\n",
              "4  2020-01-05           AD  ...                   0.0                  0.0\n",
              "\n",
              "[5 rows x 6 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(epi_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_aehtULQS0Fo",
        "outputId": "4cb2ae09-6dbc-45a8-f41a-269100167079"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "163609"
            ]
          },
          "metadata": {},
          "execution_count": 26
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(epi_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "J9_qxoRmSZcE",
        "outputId": "7a399623-ed83-4eca-fa13-f9e23a8d7a16"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                    0.000000\n",
              "location_key            0.004327\n",
              "new_confirmed           0.000544\n",
              "new_deceased            0.001241\n",
              "cumulative_confirmed    0.000538\n",
              "cumulative_deceased     0.001235\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 21
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "s"
      ],
      "metadata": {
        "id": "P6RgDV7TEtGh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "epi_us_state.to_csv(path+\"epi_us_state.csv\", index=False)\n",
        "epi_country.to_csv(path+\"epi_country.csv\", index=False)"
      ],
      "metadata": {
        "id": "A5-48dRCL8P1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Economy Table"
      ],
      "metadata": {
        "id": "5v2sM87CC8EO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# economy table on economy indicators\n",
        "econ_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/economy.csv')\n",
        "econ_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "xqauYkax1ayG",
        "outputId": "04454055-891d-4489-dcf4-c59364766104"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>location_key</th>\n",
              "      <th>gdp_usd</th>\n",
              "      <th>gdp_per_capita_usd</th>\n",
              "      <th>human_capital_index</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AD</td>\n",
              "      <td>3.154058e+09</td>\n",
              "      <td>40886.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AE</td>\n",
              "      <td>4.211423e+11</td>\n",
              "      <td>43103.0</td>\n",
              "      <td>0.659</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AF</td>\n",
              "      <td>1.910135e+10</td>\n",
              "      <td>502.0</td>\n",
              "      <td>0.389</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AG</td>\n",
              "      <td>1.727759e+09</td>\n",
              "      <td>17790.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>1.527808e+10</td>\n",
              "      <td>5352.0</td>\n",
              "      <td>0.621</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  location_key       gdp_usd  gdp_per_capita_usd  human_capital_index\n",
              "0           AD  3.154058e+09             40886.0                  NaN\n",
              "1           AE  4.211423e+11             43103.0                0.659\n",
              "2           AF  1.910135e+10               502.0                0.389\n",
              "3           AG  1.727759e+09             17790.0                  NaN\n",
              "4           AL  1.527808e+10              5352.0                0.621"
            ]
          },
          "metadata": {},
          "execution_count": 27
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(econ_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-f9iEjHNNSWK",
        "outputId": "a39476ff-07ae-47cf-e03f-0131d7aaf919"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key           0.002740\n",
              "gdp_usd                0.082192\n",
              "gdp_per_capita_usd     0.002740\n",
              "human_capital_index    0.572603\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 28
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(econ_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "pZys4TlcNhKw",
        "outputId": "e87b6667-eab7-41c1-cdc8-681a29564014"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "365"
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "econ_country = econ_full[econ_full['location_key'].isin(country_key)]"
      ],
      "metadata": {
        "id": "X2Lk7tD4PMne"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "econ_country.isna().sum()/len(econ_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1-U6YQirPZC_",
        "outputId": "67fdd907-752c-4708-e6d8-ae6b6c7daedb"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key           0.004926\n",
              "gdp_usd                0.000000\n",
              "gdp_per_capita_usd     0.000000\n",
              "human_capital_index    0.231527\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "econ_country.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "19yMM5X6Tge7",
        "outputId": "19636d39-6ef6-4190-c3c4-53a001bb9523"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>location_key</th>\n",
              "      <th>gdp_usd</th>\n",
              "      <th>gdp_per_capita_usd</th>\n",
              "      <th>human_capital_index</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AD</td>\n",
              "      <td>3.154058e+09</td>\n",
              "      <td>40886.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AE</td>\n",
              "      <td>4.211423e+11</td>\n",
              "      <td>43103.0</td>\n",
              "      <td>0.659</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AF</td>\n",
              "      <td>1.910135e+10</td>\n",
              "      <td>502.0</td>\n",
              "      <td>0.389</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AG</td>\n",
              "      <td>1.727759e+09</td>\n",
              "      <td>17790.0</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>1.527808e+10</td>\n",
              "      <td>5352.0</td>\n",
              "      <td>0.621</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  location_key       gdp_usd  gdp_per_capita_usd  human_capital_index\n",
              "0           AD  3.154058e+09             40886.0                  NaN\n",
              "1           AE  4.211423e+11             43103.0                0.659\n",
              "2           AF  1.910135e+10               502.0                0.389\n",
              "3           AG  1.727759e+09             17790.0                  NaN\n",
              "4           AL  1.527808e+10              5352.0                0.621"
            ]
          },
          "metadata": {},
          "execution_count": 32
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(econ_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Nd1TuubxTVtR",
        "outputId": "ad7abd44-6b40-431c-fab1-c1b242c45144"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "203"
            ]
          },
          "metadata": {},
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(econ_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ATmHbqaSTYZK",
        "outputId": "3e2044a4-8368-4947-a384-bc79b21e5b5a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key           0.004926\n",
              "gdp_usd                0.000000\n",
              "gdp_per_capita_usd     0.000000\n",
              "human_capital_index    0.231527\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "econ_country[econ_country.location_key.notna()].to_csv(path+'econ_country.csv', index=False)"
      ],
      "metadata": {
        "id": "vRAKqm38PqJL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Emergency Declaration Table"
      ],
      "metadata": {
        "id": "z0Okb-qBNTF2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# table with government emergency declarations & mitigation policies\n",
        "emergency_declaration_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/lawatlas-emergency-declarations.csv')\n",
        "emergency_declaration_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 360
        },
        "id": "YWB-dJJ71pTU",
        "outputId": "d3762587-845f-4199-ef92-23ceb5f414d8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>lawatlas_mitigation_policy</th>\n",
              "      <th>lawatlas_state_emergency</th>\n",
              "      <th>lawatlas_emerg_statewide</th>\n",
              "      <th>lawatlas_travel_requirement</th>\n",
              "      <th>lawatlas_traveler_type_all_people_entering_the_state</th>\n",
              "      <th>lawatlas_traveler_type_travelers_from_specified_states</th>\n",
              "      <th>lawatlas_traveler_type_travelers_from_specified_countries</th>\n",
              "      <th>lawatlas_traveler_type_general_international_travelers</th>\n",
              "      <th>lawatlas_traveler_type_all_air_travelers</th>\n",
              "      <th>lawatlas_requirement_type_traveler_must_self_quarantine</th>\n",
              "      <th>lawatlas_requirement_type_traveler_must_inform_others_of_travel</th>\n",
              "      <th>lawatlas_requirement_type_checkpoints_must_be_established</th>\n",
              "      <th>lawatlas_requirement_type_travel_requirement_must_be_posted</th>\n",
              "      <th>lawatlas_travel_statewide</th>\n",
              "      <th>lawatlas_home_requirement</th>\n",
              "      <th>lawatlas_home_except_engaging_in_essential_business_activities</th>\n",
              "      <th>lawatlas_home_except_obtaining_necessary_supplies</th>\n",
              "      <th>lawatlas_home_except_accessing_emergency_services</th>\n",
              "      <th>lawatlas_home_except_caring_for_a_person_outside_the_home</th>\n",
              "      <th>lawatlas_home_except_caring_for_a_pet_outside_the_home</th>\n",
              "      <th>lawatlas_home_except_engaging_in_outdoor_activities</th>\n",
              "      <th>lawatlas_home_except_attending_religious_services</th>\n",
              "      <th>lawatlas_home_except_engaging_in_essential_health_care_services</th>\n",
              "      <th>lawatlas_home_statewide</th>\n",
              "      <th>lawatlas_curfew_reg</th>\n",
              "      <th>lawatlas_mask_requirement</th>\n",
              "      <th>lawatlas_mask_statewide</th>\n",
              "      <th>lawatlas_business_close</th>\n",
              "      <th>lawatlas_business_type_all_non_essential_businesses</th>\n",
              "      <th>lawatlas_business_type_non_essential_retail_businesses</th>\n",
              "      <th>lawatlas_business_type_entertainment_businesses</th>\n",
              "      <th>lawatlas_business_type_personal_service_businesses</th>\n",
              "      <th>lawatlas_business_type_restaurants</th>\n",
              "      <th>lawatlas_business_type_bars</th>\n",
              "      <th>lawatlas_business_type_fitness_centers</th>\n",
              "      <th>lawatlas_essential_def_appliance_stores</th>\n",
              "      <th>lawatlas_essential_def_convenience_stores</th>\n",
              "      <th>lawatlas_essential_def_gas_stations</th>\n",
              "      <th>...</th>\n",
              "      <th>lawatlas_gathering_ban</th>\n",
              "      <th>lawatlas_gathering_type</th>\n",
              "      <th>lawatlas_gathering_statewide</th>\n",
              "      <th>lawatlas_med_restrict</th>\n",
              "      <th>lawatlas_med_except_delay_would_threaten_patients_health</th>\n",
              "      <th>lawatlas_med_except_delay_would_threaten_patients_life</th>\n",
              "      <th>lawatlas_med_except_procedure_needed_to_treat_emergency</th>\n",
              "      <th>lawatlas_med_except_procedure_does_not_deplete_hospital_capacity</th>\n",
              "      <th>lawatlas_med_except_procedure_does_not_deplete_personal_protective_equipment</th>\n",
              "      <th>lawatlas_med_except_family_planning_services</th>\n",
              "      <th>lawatlas_med_except_no_exception_specified</th>\n",
              "      <th>lawatlas_abortion_essential_new</th>\n",
              "      <th>lawatlas_med_statewide</th>\n",
              "      <th>lawatlas_correct_requirement</th>\n",
              "      <th>lawatlas_correct_facility_all_state_facilities</th>\n",
              "      <th>lawatlas_correct_facility_all_department_of_corrections_facilities</th>\n",
              "      <th>lawatlas_correct_facility_all_county_jails</th>\n",
              "      <th>lawatlas_correct_facility_juvenile_detention_centers</th>\n",
              "      <th>lawatlas_correct_type_intakes_suspended</th>\n",
              "      <th>lawatlas_correct_type_duty_to_receive_prisoners_suspended</th>\n",
              "      <th>lawatlas_correct_type_transfers_to_custody_suspended</th>\n",
              "      <th>lawatlas_correct_type_release_of_inmates</th>\n",
              "      <th>lawatlas_correct_type_rules_regarding_inmate_release_suspended</th>\n",
              "      <th>lawatlas_correct_type_release_notice_suspended</th>\n",
              "      <th>lawatlas_correct_type_cease_in_person_parole_hearings</th>\n",
              "      <th>lawatlas_correct_type_develop_process_for_virtual_parole_hearings</th>\n",
              "      <th>lawatlas_correct_type_visitation_suspended</th>\n",
              "      <th>lawatlas_correct_statewide</th>\n",
              "      <th>lawatlas_state_preempt</th>\n",
              "      <th>lawatlas_action_preempt_imposing_additional_social_distancing_limitations_on_essential_business</th>\n",
              "      <th>lawatlas_action_preempt_imposing_additional_restrictions_on_public_spaces</th>\n",
              "      <th>lawatlas_action_preempt_restricting_scope_of_services_of_an_essential_business</th>\n",
              "      <th>lawatlas_action_preempt_expanding_the_definition_of_non-essential_business</th>\n",
              "      <th>lawatlas_action_preempt_restricting_the_hours_of_operation_of_an_essential_business</th>\n",
              "      <th>lawatlas_action_preempt_imposing_restrictions_that_conflict_with_state_order</th>\n",
              "      <th>lawatlas_action_preempt_restricting_the_performance_of_an_essential_function</th>\n",
              "      <th>lawatlas_action_preempt_restricting_people_from_leaving_home</th>\n",
              "      <th>lawatlas_action_preempt_restricting_the_operations_of_schools</th>\n",
              "      <th>lawatlas_action_preempt_imposing_gathering_bans</th>\n",
              "      <th>lawatlas_local_allow</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-01-20</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-01-21</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-01-22</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-01-23</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-01-24</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>5 rows × 104 columns</p>\n",
              "</div>"
            ],
            "text/plain": [
              "         date  ... lawatlas_local_allow\n",
              "0  2020-01-20  ...                  NaN\n",
              "1  2020-01-21  ...                  NaN\n",
              "2  2020-01-22  ...                  NaN\n",
              "3  2020-01-23  ...                  NaN\n",
              "4  2020-01-24  ...                  NaN\n",
              "\n",
              "[5 rows x 104 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 36
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "state_mitigate = emergency_declaration_full[['date', 'location_key', 'lawatlas_mitigation_policy']]"
      ],
      "metadata": {
        "id": "fjANZODIUNDt"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "state_code = []\n",
        "first_mitigate_date = []\n",
        "for state in state_mitigate.location_key.unique():\n",
        "    state_code.append(state)\n",
        "    first = state_mitigate[state_mitigate['location_key']==state][state_mitigate[state_mitigate['location_key']==state].lawatlas_mitigation_policy==1].date.min()\n",
        "    first_mitigate_date.append(first)"
      ],
      "metadata": {
        "id": "qEKfP5hEVRjV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "len(state_mitigate.location_key.unique())"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "g_l4yxFPWKPg",
        "outputId": "03899cb7-176b-44e8-be1b-ec8b0cf14169"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "51"
            ]
          },
          "metadata": {},
          "execution_count": 39
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df_first_mitigate = pd.DataFrame({\"state_code\":state_code, \"first_mitigate_date\":first_mitigate_date})"
      ],
      "metadata": {
        "id": "DzgiLcuEV3Vh"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_first_mitigate.to_csv(path+\"first_mitigate_date.csv\", index=False)"
      ],
      "metadata": {
        "id": "dVO8mMdCWEJB"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "state_mitigate[state_mitigate['location_key']=='US_CA'][state_mitigate[state_mitigate['location_key']=='US_CA'].lawatlas_mitigation_policy==1].date.min()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "0gb4bF4KUdZn",
        "outputId": "30ceadd6-262d-4809-beec-e2b6ee77fabc"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'2020-03-04'"
            ]
          },
          "metadata": {},
          "execution_count": 181
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "J4uYGdEtUoxN",
        "outputId": "6249fa07-890c-47a8-c183-a4d714ce0b98"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'2020-07-01'"
            ]
          },
          "metadata": {},
          "execution_count": 174
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Health Table"
      ],
      "metadata": {
        "id": "TEbQZhV7Qpag"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# health table with health indicators by region\n",
        "health_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/health.csv')\n",
        "health_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        },
        "id": "-RGgo-sME_rS",
        "outputId": "2dc7833d-c46d-4e29-9095-772a0720df9f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>location_key</th>\n",
              "      <th>life_expectancy</th>\n",
              "      <th>smoking_prevalence</th>\n",
              "      <th>diabetes_prevalence</th>\n",
              "      <th>infant_mortality_rate</th>\n",
              "      <th>adult_male_mortality_rate</th>\n",
              "      <th>adult_female_mortality_rate</th>\n",
              "      <th>pollution_mortality_rate</th>\n",
              "      <th>comorbidity_mortality_rate</th>\n",
              "      <th>hospital_beds_per_1000</th>\n",
              "      <th>nurses_per_1000</th>\n",
              "      <th>physicians_per_1000</th>\n",
              "      <th>health_expenditure_usd</th>\n",
              "      <th>out_of_pocket_health_expenditure_usd</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>AD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>33.5</td>\n",
              "      <td>7.7</td>\n",
              "      <td>2.7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4.0128</td>\n",
              "      <td>3.3333</td>\n",
              "      <td>4040.786621</td>\n",
              "      <td>1688.121460</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>AE</td>\n",
              "      <td>77.814</td>\n",
              "      <td>28.9</td>\n",
              "      <td>16.3</td>\n",
              "      <td>6.5</td>\n",
              "      <td>69.555</td>\n",
              "      <td>44.863</td>\n",
              "      <td>54.7</td>\n",
              "      <td>16.8</td>\n",
              "      <td>NaN</td>\n",
              "      <td>5.7271</td>\n",
              "      <td>2.5278</td>\n",
              "      <td>1357.017456</td>\n",
              "      <td>256.034485</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>AF</td>\n",
              "      <td>64.486</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9.2</td>\n",
              "      <td>47.9</td>\n",
              "      <td>237.554</td>\n",
              "      <td>192.532</td>\n",
              "      <td>211.1</td>\n",
              "      <td>29.8</td>\n",
              "      <td>0.5</td>\n",
              "      <td>0.1755</td>\n",
              "      <td>0.2782</td>\n",
              "      <td>67.122650</td>\n",
              "      <td>50.665913</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>AG</td>\n",
              "      <td>76.885</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13.1</td>\n",
              "      <td>5.0</td>\n",
              "      <td>126.917</td>\n",
              "      <td>83.136</td>\n",
              "      <td>29.9</td>\n",
              "      <td>22.6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4.5171</td>\n",
              "      <td>2.9560</td>\n",
              "      <td>673.859680</td>\n",
              "      <td>235.749039</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>AL</td>\n",
              "      <td>78.900</td>\n",
              "      <td>28.7</td>\n",
              "      <td>9.0</td>\n",
              "      <td>7.8</td>\n",
              "      <td>93.315</td>\n",
              "      <td>49.486</td>\n",
              "      <td>68.0</td>\n",
              "      <td>17.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3.6495</td>\n",
              "      <td>1.2164</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  location_key  ...  out_of_pocket_health_expenditure_usd\n",
              "0           AD  ...                           1688.121460\n",
              "1           AE  ...                            256.034485\n",
              "2           AF  ...                             50.665913\n",
              "3           AG  ...                            235.749039\n",
              "4           AL  ...                                   NaN\n",
              "\n",
              "[5 rows x 14 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(health_full[health_full['location_key'].isin(us_state_key)])"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "HX88lXOxWpWP",
        "outputId": "a1ada9d0-a8d8-445d-9b8b-044580530446"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key                            0.0\n",
              "life_expectancy                         0.0\n",
              "smoking_prevalence                      1.0\n",
              "diabetes_prevalence                     1.0\n",
              "infant_mortality_rate                   1.0\n",
              "adult_male_mortality_rate               1.0\n",
              "adult_female_mortality_rate             1.0\n",
              "pollution_mortality_rate                1.0\n",
              "comorbidity_mortality_rate              1.0\n",
              "hospital_beds_per_1000                  1.0\n",
              "nurses_per_1000                         1.0\n",
              "physicians_per_1000                     1.0\n",
              "health_expenditure_usd                  1.0\n",
              "out_of_pocket_health_expenditure_usd    1.0\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 42
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "health_us_state = health_full[health_full['location_key'].isin(us_state_key)][['location_key', 'life_expectancy']]"
      ],
      "metadata": {
        "id": "abm4AAh4XI15"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "health_us_state.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "I43e4luCUR6i",
        "outputId": "5d5681ee-e8ea-4126-9c33-ac857e35548d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>location_key</th>\n",
              "      <th>life_expectancy</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>415</th>\n",
              "      <td>US_AK</td>\n",
              "      <td>78.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>442</th>\n",
              "      <td>US_AL</td>\n",
              "      <td>75.1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>510</th>\n",
              "      <td>US_AR</td>\n",
              "      <td>75.6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>586</th>\n",
              "      <td>US_AZ</td>\n",
              "      <td>78.7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>602</th>\n",
              "      <td>US_CA</td>\n",
              "      <td>80.8</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    location_key  life_expectancy\n",
              "415        US_AK             78.0\n",
              "442        US_AL             75.1\n",
              "510        US_AR             75.6\n",
              "586        US_AZ             78.7\n",
              "602        US_CA             80.8"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(health_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "caXXKXATUTqm",
        "outputId": "be565a18-a797-456c-d12d-4bdc958dc17b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "53"
            ]
          },
          "metadata": {},
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(health_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bjZxByaGUcnB",
        "outputId": "cca2a342-8f46-46cd-d717-e117ca3e8044"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key       0.0\n",
              "life_expectancy    0.0\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 52
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "health_us_state.to_csv(path+\"health_us_state.csv\", index=False)"
      ],
      "metadata": {
        "id": "0C2gPnD3XWN8"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "health_country = health_full[health_full['location_key'].isin(country_key)]"
      ],
      "metadata": {
        "id": "L8UBTAe6UQw_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(health_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Yk8Pf7zsW8Of",
        "outputId": "87305d64-8bdb-4e2b-b408-da791282e613"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "location_key                            0.004762\n",
              "life_expectancy                         0.023810\n",
              "smoking_prevalence                      0.304762\n",
              "diabetes_prevalence                     0.004762\n",
              "infant_mortality_rate                   0.080952\n",
              "adult_male_mortality_rate               0.100000\n",
              "adult_female_mortality_rate             0.100000\n",
              "pollution_mortality_rate                0.128571\n",
              "comorbidity_mortality_rate              0.128571\n",
              "hospital_beds_per_1000                  0.880952\n",
              "nurses_per_1000                         0.142857\n",
              "physicians_per_1000                     0.219048\n",
              "health_expenditure_usd                  0.114286\n",
              "out_of_pocket_health_expenditure_usd    0.114286\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 55
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "health_country.to_csv(path+\"health_country.csv\", index=False)"
      ],
      "metadata": {
        "id": "yaLMqqx5XjPo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Mobility Table"
      ],
      "metadata": {
        "id": "zBD9y9ztX7Re"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# mobility table with movement of people\n",
        "mobility_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/mobility.csv')\n",
        "mobility_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "b_eU_33jFV1M",
        "outputId": "2dc904b5-656d-4113-9379-43a7ca07f642"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>mobility_retail_and_recreation</th>\n",
              "      <th>mobility_grocery_and_pharmacy</th>\n",
              "      <th>mobility_parks</th>\n",
              "      <th>mobility_transit_stations</th>\n",
              "      <th>mobility_workplaces</th>\n",
              "      <th>mobility_residential</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-02-15</td>\n",
              "      <td>AE</td>\n",
              "      <td>0.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-02-16</td>\n",
              "      <td>AE</td>\n",
              "      <td>1.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-02-17</td>\n",
              "      <td>AE</td>\n",
              "      <td>-1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-02-18</td>\n",
              "      <td>AE</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-02-19</td>\n",
              "      <td>AE</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>-1.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         date location_key  ...  mobility_workplaces  mobility_residential\n",
              "0  2020-02-15           AE  ...                  2.0                   1.0\n",
              "1  2020-02-16           AE  ...                  2.0                   1.0\n",
              "2  2020-02-17           AE  ...                  2.0                   1.0\n",
              "3  2020-02-18           AE  ...                  2.0                   1.0\n",
              "4  2020-02-19           AE  ...                  2.0                   1.0\n",
              "\n",
              "[5 rows x 8 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 57
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(mobility_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YWSFsWroU2ji",
        "outputId": "d0eb93ad-31fe-454c-e9ac-5a8c42e688ae"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                              0.000000\n",
              "location_key                      0.000158\n",
              "mobility_retail_and_recreation    0.340447\n",
              "mobility_grocery_and_pharmacy     0.380990\n",
              "mobility_parks                    0.522293\n",
              "mobility_transit_stations         0.493417\n",
              "mobility_workplaces               0.022200\n",
              "mobility_residential              0.365202\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 58
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(mobility_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aB7d_iO7U5K_",
        "outputId": "ee66cd8c-14b9-4aa9-c970-b54ad8a77d1c"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "4189245"
            ]
          },
          "metadata": {},
          "execution_count": 60
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mobility_us_state = mobility_full[mobility_full.location_key.isin(us_state_key)]"
      ],
      "metadata": {
        "id": "ZkUxbjrGYZke"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "mobility_us_state.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 206
        },
        "id": "J8NPdGa-VC0R",
        "outputId": "5dd1d2f0-2670-47ad-978b-1c1e4e76e79b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>mobility_retail_and_recreation</th>\n",
              "      <th>mobility_grocery_and_pharmacy</th>\n",
              "      <th>mobility_parks</th>\n",
              "      <th>mobility_transit_stations</th>\n",
              "      <th>mobility_workplaces</th>\n",
              "      <th>mobility_residential</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>2454430</th>\n",
              "      <td>2020-02-15</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>5.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>14.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2454431</th>\n",
              "      <td>2020-02-16</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>15.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>29.0</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>-1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2454432</th>\n",
              "      <td>2020-02-17</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>3.0</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>9.0</td>\n",
              "      <td>-6.0</td>\n",
              "      <td>-33.0</td>\n",
              "      <td>8.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2454433</th>\n",
              "      <td>2020-02-18</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>2.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2454434</th>\n",
              "      <td>2020-02-19</td>\n",
              "      <td>US_AK</td>\n",
              "      <td>0.0</td>\n",
              "      <td>-2.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>-3.0</td>\n",
              "      <td>2.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               date location_key  ...  mobility_workplaces  mobility_residential\n",
              "2454430  2020-02-15        US_AK  ...                  1.0                   0.0\n",
              "2454431  2020-02-16        US_AK  ...                  1.0                  -1.0\n",
              "2454432  2020-02-17        US_AK  ...                -33.0                   8.0\n",
              "2454433  2020-02-18        US_AK  ...                 -2.0                   2.0\n",
              "2454434  2020-02-19        US_AK  ...                 -3.0                   2.0\n",
              "\n",
              "[5 rows x 8 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 64
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(mobility_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aTqkolyEYg3b",
        "outputId": "6a19f836-31db-409d-9e8e-1b880997db47"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                              0.000000\n",
              "location_key                      0.000000\n",
              "mobility_retail_and_recreation    0.000000\n",
              "mobility_grocery_and_pharmacy     0.000000\n",
              "mobility_parks                    0.014780\n",
              "mobility_transit_stations         0.003999\n",
              "mobility_workplaces               0.000000\n",
              "mobility_residential              0.000000\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 62
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(mobility_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "VBUNFN2uYwvW",
        "outputId": "5e79cf04-0b95-439a-aaa6-be9bf3b8cab1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "33762"
            ]
          },
          "metadata": {},
          "execution_count": 63
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mobility_us_state.date.min()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "cZFnYPeiY5V0",
        "outputId": "e46b8b26-b705-4de2-f01c-62c38135aa6e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'2020-02-15'"
            ]
          },
          "metadata": {},
          "execution_count": 65
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mobility_us_state.date.max()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "CX3p8fVZY8mu",
        "outputId": "c3fa8aa6-0dd6-42ab-ad3f-20b3d008e23d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'2021-12-07'"
            ]
          },
          "metadata": {},
          "execution_count": 66
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "mobility_us_state.to_csv(path+\"mobility_us_state.csv\", index=False)"
      ],
      "metadata": {
        "id": "epxHSNOuZAUE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Vaccination Table"
      ],
      "metadata": {
        "id": "_61BKKrDdN7E"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# vaccination table with population vaccination rates\n",
        "vaccinations_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/vaccinations.csv')\n",
        "vaccinations_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 313
        },
        "id": "OK2o00gAF7SM",
        "outputId": "d82bdb0d-31a8-420e-8235-0806063841e9"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>new_persons_vaccinated</th>\n",
              "      <th>cumulative_persons_vaccinated</th>\n",
              "      <th>new_persons_fully_vaccinated</th>\n",
              "      <th>cumulative_persons_fully_vaccinated</th>\n",
              "      <th>new_vaccine_doses_administered</th>\n",
              "      <th>cumulative_vaccine_doses_administered</th>\n",
              "      <th>new_persons_vaccinated_pfizer</th>\n",
              "      <th>cumulative_persons_vaccinated_pfizer</th>\n",
              "      <th>new_persons_fully_vaccinated_pfizer</th>\n",
              "      <th>cumulative_persons_fully_vaccinated_pfizer</th>\n",
              "      <th>new_vaccine_doses_administered_pfizer</th>\n",
              "      <th>cumulative_vaccine_doses_administered_pfizer</th>\n",
              "      <th>new_persons_vaccinated_moderna</th>\n",
              "      <th>cumulative_persons_vaccinated_moderna</th>\n",
              "      <th>new_persons_fully_vaccinated_moderna</th>\n",
              "      <th>cumulative_persons_fully_vaccinated_moderna</th>\n",
              "      <th>new_vaccine_doses_administered_moderna</th>\n",
              "      <th>cumulative_vaccine_doses_administered_moderna</th>\n",
              "      <th>new_persons_vaccinated_janssen</th>\n",
              "      <th>cumulative_persons_vaccinated_janssen</th>\n",
              "      <th>new_persons_fully_vaccinated_janssen</th>\n",
              "      <th>cumulative_persons_fully_vaccinated_janssen</th>\n",
              "      <th>new_vaccine_doses_administered_janssen</th>\n",
              "      <th>cumulative_vaccine_doses_administered_janssen</th>\n",
              "      <th>new_persons_vaccinated_sinovac</th>\n",
              "      <th>total_persons_vaccinated_sinovac</th>\n",
              "      <th>new_persons_fully_vaccinated_sinovac</th>\n",
              "      <th>total_persons_fully_vaccinated_sinovac</th>\n",
              "      <th>new_vaccine_doses_administered_sinovac</th>\n",
              "      <th>total_vaccine_doses_administered_sinovac</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2021-01-25</td>\n",
              "      <td>AD</td>\n",
              "      <td>NaN</td>\n",
              "      <td>576.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>576.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2021-02-01</td>\n",
              "      <td>AD</td>\n",
              "      <td>460.0</td>\n",
              "      <td>1036.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>460.0</td>\n",
              "      <td>1036.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2021-02-10</td>\n",
              "      <td>AD</td>\n",
              "      <td>255.0</td>\n",
              "      <td>1291.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>255.0</td>\n",
              "      <td>1291.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2021-02-12</td>\n",
              "      <td>AD</td>\n",
              "      <td>331.0</td>\n",
              "      <td>1622.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>331.0</td>\n",
              "      <td>1622.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2021-02-19</td>\n",
              "      <td>AD</td>\n",
              "      <td>519.0</td>\n",
              "      <td>2141.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>519.0</td>\n",
              "      <td>2141.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         date  ... total_vaccine_doses_administered_sinovac\n",
              "0  2021-01-25  ...                                      NaN\n",
              "1  2021-02-01  ...                                      NaN\n",
              "2  2021-02-10  ...                                      NaN\n",
              "3  2021-02-12  ...                                      NaN\n",
              "4  2021-02-19  ...                                      NaN\n",
              "\n",
              "[5 rows x 32 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 71
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "len(vaccinations_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LwZ5MwPIVTCi",
        "outputId": "ef00d957-7541-480a-f4b7-a76ca7f2d4e4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "1373770"
            ]
          },
          "metadata": {},
          "execution_count": 72
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(vaccinations_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rHMBrwwAdVcS",
        "outputId": "8755cf7f-1d59-49db-a315-0451c2eb5ecf"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                             0.000000\n",
              "location_key                                     0.000102\n",
              "new_persons_vaccinated                           0.550935\n",
              "cumulative_persons_vaccinated                    0.583284\n",
              "new_persons_fully_vaccinated                     0.012469\n",
              "cumulative_persons_fully_vaccinated              0.010842\n",
              "new_vaccine_doses_administered                   0.551156\n",
              "cumulative_vaccine_doses_administered            0.582598\n",
              "new_persons_vaccinated_pfizer                    0.994771\n",
              "cumulative_persons_vaccinated_pfizer             0.994771\n",
              "new_persons_fully_vaccinated_pfizer              0.983290\n",
              "cumulative_persons_fully_vaccinated_pfizer       0.983237\n",
              "new_vaccine_doses_administered_pfizer            0.988507\n",
              "cumulative_vaccine_doses_administered_pfizer     0.988465\n",
              "new_persons_vaccinated_moderna                   0.998323\n",
              "cumulative_persons_vaccinated_moderna            0.998323\n",
              "new_persons_fully_vaccinated_moderna             0.986841\n",
              "cumulative_persons_fully_vaccinated_moderna      0.986788\n",
              "new_vaccine_doses_administered_moderna           0.988507\n",
              "cumulative_vaccine_doses_administered_moderna    0.988465\n",
              "new_persons_vaccinated_janssen                   0.998323\n",
              "cumulative_persons_vaccinated_janssen            0.998323\n",
              "new_persons_fully_vaccinated_janssen             0.986841\n",
              "cumulative_persons_fully_vaccinated_janssen      0.986788\n",
              "new_vaccine_doses_administered_janssen           0.988507\n",
              "cumulative_vaccine_doses_administered_janssen    0.988465\n",
              "new_persons_vaccinated_sinovac                   0.996448\n",
              "total_persons_vaccinated_sinovac                 0.996448\n",
              "new_persons_fully_vaccinated_sinovac             1.000000\n",
              "total_persons_fully_vaccinated_sinovac           1.000000\n",
              "new_vaccine_doses_administered_sinovac           1.000000\n",
              "total_vaccine_doses_administered_sinovac         1.000000\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 73
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "vaccinations_country = vaccinations_full[vaccinations_full['location_key'].isin(country_key)]\n",
        "vaccinations_us_state = vaccinations_full[vaccinations_full['location_key'].isin(us_state_key)]"
      ],
      "metadata": {
        "id": "BgrNQqBAdc6O"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "vac_country_cols = ['date', 'location_key', 'new_persons_vaccinated', 'cumulative_persons_vaccinated', 'new_persons_fully_vaccinated', 'cumulative_persons_fully_vaccinated']"
      ],
      "metadata": {
        "id": "CufuZ8_jgNOa"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "vaccinations_country[vac_country_cols].to_csv(path+\"vaccinations_country.csv\", index=False)"
      ],
      "metadata": {
        "id": "iHaU7ZtvgS4X"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(vaccinations_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sk0ayd_SdxDi",
        "outputId": "49ff0f85-5f96-4417-8058-5618ac08d650"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                             0.000000\n",
              "location_key                                     0.004093\n",
              "new_persons_vaccinated                           0.038770\n",
              "cumulative_persons_vaccinated                    0.076896\n",
              "new_persons_fully_vaccinated                     0.096281\n",
              "cumulative_persons_fully_vaccinated              0.134524\n",
              "new_vaccine_doses_administered                   0.029384\n",
              "cumulative_vaccine_doses_administered            0.032893\n",
              "new_persons_vaccinated_pfizer                    0.991609\n",
              "cumulative_persons_vaccinated_pfizer             0.991609\n",
              "new_persons_fully_vaccinated_pfizer              0.983510\n",
              "cumulative_persons_fully_vaccinated_pfizer       0.983480\n",
              "new_vaccine_doses_administered_pfizer            0.991901\n",
              "cumulative_vaccine_doses_administered_pfizer     0.991872\n",
              "new_persons_vaccinated_moderna                   1.000000\n",
              "cumulative_persons_vaccinated_moderna            1.000000\n",
              "new_persons_fully_vaccinated_moderna             0.991901\n",
              "cumulative_persons_fully_vaccinated_moderna      0.991872\n",
              "new_vaccine_doses_administered_moderna           0.991901\n",
              "cumulative_vaccine_doses_administered_moderna    0.991872\n",
              "new_persons_vaccinated_janssen                   1.000000\n",
              "cumulative_persons_vaccinated_janssen            1.000000\n",
              "new_persons_fully_vaccinated_janssen             0.991901\n",
              "cumulative_persons_fully_vaccinated_janssen      0.991872\n",
              "new_vaccine_doses_administered_janssen           0.991901\n",
              "cumulative_vaccine_doses_administered_janssen    0.991872\n",
              "new_persons_vaccinated_sinovac                   0.991609\n",
              "total_persons_vaccinated_sinovac                 0.991609\n",
              "new_persons_fully_vaccinated_sinovac             1.000000\n",
              "total_persons_fully_vaccinated_sinovac           1.000000\n",
              "new_vaccine_doses_administered_sinovac           1.000000\n",
              "total_vaccine_doses_administered_sinovac         1.000000\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 77
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "vac_us_cols = ['date', 'location_key', 'new_persons_vaccinated', 'cumulative_persons_vaccinated', 'new_persons_fully_vaccinated', 'cumulative_persons_fully_vaccinated',\n",
        "               'new_persons_fully_vaccinated_pfizer', 'cumulative_persons_fully_vaccinated_pfizer',\n",
        "               'new_persons_fully_vaccinated_moderna', 'cumulative_persons_fully_vaccinated_moderna',\n",
        "               'new_persons_fully_vaccinated_janssen', 'cumulative_persons_fully_vaccinated_janssen']"
      ],
      "metadata": {
        "id": "ogsDI5UReR35"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "vaccinations_us_state[vac_us_cols].to_csv(path+\"vaccinations_us_state.csv\", index=False)"
      ],
      "metadata": {
        "id": "3cVmF9oIe74a"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(vaccinations_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1ChZT2dPd2z0",
        "outputId": "fd7ece8a-34a5-4e55-d723-7fba9ac22eec"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                             0.000000\n",
              "location_key                                     0.000000\n",
              "new_persons_vaccinated                           0.008337\n",
              "cumulative_persons_vaccinated                    0.005251\n",
              "new_persons_fully_vaccinated                     0.009366\n",
              "cumulative_persons_fully_vaccinated              0.008987\n",
              "new_vaccine_doses_administered                   0.003086\n",
              "cumulative_vaccine_doses_administered            0.000000\n",
              "new_persons_vaccinated_pfizer                    1.000000\n",
              "cumulative_persons_vaccinated_pfizer             1.000000\n",
              "new_persons_fully_vaccinated_pfizer              0.160197\n",
              "cumulative_persons_fully_vaccinated_pfizer       0.157165\n",
              "new_vaccine_doses_administered_pfizer            0.160197\n",
              "cumulative_vaccine_doses_administered_pfizer     0.157165\n",
              "new_persons_vaccinated_moderna                   1.000000\n",
              "cumulative_persons_vaccinated_moderna            1.000000\n",
              "new_persons_fully_vaccinated_moderna             0.160197\n",
              "cumulative_persons_fully_vaccinated_moderna      0.157165\n",
              "new_vaccine_doses_administered_moderna           0.160197\n",
              "cumulative_vaccine_doses_administered_moderna    0.157165\n",
              "new_persons_vaccinated_janssen                   1.000000\n",
              "cumulative_persons_vaccinated_janssen            1.000000\n",
              "new_persons_fully_vaccinated_janssen             0.160197\n",
              "cumulative_persons_fully_vaccinated_janssen      0.157165\n",
              "new_vaccine_doses_administered_janssen           0.160197\n",
              "cumulative_vaccine_doses_administered_janssen    0.157165\n",
              "new_persons_vaccinated_sinovac                   1.000000\n",
              "total_persons_vaccinated_sinovac                 1.000000\n",
              "new_persons_fully_vaccinated_sinovac             1.000000\n",
              "total_persons_fully_vaccinated_sinovac           1.000000\n",
              "new_vaccine_doses_administered_sinovac           1.000000\n",
              "total_vaccine_doses_administered_sinovac         1.000000\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 80
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Government Table"
      ],
      "metadata": {
        "id": "xCmfE5cXggJX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# government interventions and relative stringency table\n",
        "gov_response_full = pd.read_csv('https://storage.googleapis.com/covid19-open-data/v3/oxford-government-response.csv')\n",
        "gov_response_full.head()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 313
        },
        "id": "DaM1C-SyHKn4",
        "outputId": "79e32178-7bc6-4973-cc84-a63d280c9b3f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>date</th>\n",
              "      <th>location_key</th>\n",
              "      <th>school_closing</th>\n",
              "      <th>workplace_closing</th>\n",
              "      <th>cancel_public_events</th>\n",
              "      <th>restrictions_on_gatherings</th>\n",
              "      <th>public_transport_closing</th>\n",
              "      <th>stay_at_home_requirements</th>\n",
              "      <th>restrictions_on_internal_movement</th>\n",
              "      <th>international_travel_controls</th>\n",
              "      <th>income_support</th>\n",
              "      <th>debt_relief</th>\n",
              "      <th>fiscal_measures</th>\n",
              "      <th>international_support</th>\n",
              "      <th>public_information_campaigns</th>\n",
              "      <th>testing_policy</th>\n",
              "      <th>contact_tracing</th>\n",
              "      <th>emergency_investment_in_healthcare</th>\n",
              "      <th>investment_in_vaccines</th>\n",
              "      <th>facial_coverings</th>\n",
              "      <th>vaccination_policy</th>\n",
              "      <th>stringency_index</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>2020-01-01</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2020-01-02</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>2020-01-03</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>2020-01-04</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>2020-01-05</td>\n",
              "      <td>AD</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "         date location_key  ...  vaccination_policy  stringency_index\n",
              "0  2020-01-01           AD  ...                 0.0               0.0\n",
              "1  2020-01-02           AD  ...                 0.0               0.0\n",
              "2  2020-01-03           AD  ...                 0.0               0.0\n",
              "3  2020-01-04           AD  ...                 0.0               0.0\n",
              "4  2020-01-05           AD  ...                 0.0               0.0\n",
              "\n",
              "[5 rows x 22 columns]"
            ]
          },
          "metadata": {},
          "execution_count": 81
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(gov_response_full)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sVoDi_16Vjjq",
        "outputId": "28eae7ee-5c09-4e34-cea2-5423fed60e4e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                  0.000000\n",
              "location_key                          0.003295\n",
              "school_closing                        0.006045\n",
              "workplace_closing                     0.006650\n",
              "cancel_public_events                  0.006627\n",
              "restrictions_on_gatherings            0.007097\n",
              "public_transport_closing              0.006860\n",
              "stay_at_home_requirements             0.006636\n",
              "restrictions_on_internal_movement     0.006957\n",
              "international_travel_controls         0.011471\n",
              "income_support                        0.004291\n",
              "debt_relief                           0.008698\n",
              "fiscal_measures                       0.317594\n",
              "international_support                 0.318078\n",
              "public_information_campaigns          0.007660\n",
              "testing_policy                        0.008316\n",
              "contact_tracing                       0.009833\n",
              "emergency_investment_in_healthcare    0.318883\n",
              "investment_in_vaccines                0.202563\n",
              "facial_coverings                      0.007060\n",
              "vaccination_policy                    0.006808\n",
              "stringency_index                      0.006962\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 89
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "gov_response_country = gov_response_full[gov_response_full['location_key'].isin(country_key)]"
      ],
      "metadata": {
        "id": "RohssalziKK5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "gov_response_us_state = gov_response_full[gov_response_full['location_key'].isin(us_state_key)]"
      ],
      "metadata": {
        "id": "uJmhs0dBinoE"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "gov_response_country.to_csv(path+\"gov_response_country.csv\", index=False)\n",
        "gov_response_us_state.to_csv(path+\"gov_response_us_state.csv\", index=False)"
      ],
      "metadata": {
        "id": "BjzBLF74jX9C"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(gov_response_country)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JRbnxflsVdWI",
        "outputId": "7dc3a11c-bf34-4aad-ad76-a5c62f6c84ab"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                  0.000000\n",
              "location_key                          0.005439\n",
              "school_closing                        0.003211\n",
              "workplace_closing                     0.003864\n",
              "cancel_public_events                  0.003772\n",
              "restrictions_on_gatherings            0.004279\n",
              "public_transport_closing              0.003811\n",
              "stay_at_home_requirements             0.003764\n",
              "restrictions_on_internal_movement     0.004126\n",
              "international_travel_controls         0.003511\n",
              "income_support                        0.004586\n",
              "debt_relief                           0.005992\n",
              "fiscal_measures                       0.183436\n",
              "international_support                 0.182806\n",
              "public_information_campaigns          0.004748\n",
              "testing_policy                        0.005370\n",
              "contact_tracing                       0.007913\n",
              "emergency_investment_in_healthcare    0.183767\n",
              "investment_in_vaccines                0.038682\n",
              "facial_coverings                      0.003127\n",
              "vaccination_policy                    0.002996\n",
              "stringency_index                      0.004126\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 87
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "check_na(gov_response_us_state)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "qD5WRmEoVfrR",
        "outputId": "8ad5d819-1d2d-4c68-bd3d-c78394d2fb4e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "date                                  0.000000\n",
              "location_key                          0.000000\n",
              "school_closing                        0.000307\n",
              "workplace_closing                     0.000614\n",
              "cancel_public_events                  0.000838\n",
              "restrictions_on_gatherings            0.000866\n",
              "public_transport_closing              0.000754\n",
              "stay_at_home_requirements             0.000950\n",
              "restrictions_on_internal_movement     0.001396\n",
              "international_travel_controls         0.001648\n",
              "income_support                        0.002178\n",
              "debt_relief                           0.002877\n",
              "fiscal_measures                       0.663548\n",
              "international_support                 0.667123\n",
              "public_information_campaigns          0.001145\n",
              "testing_policy                        0.000754\n",
              "contact_tracing                       0.001452\n",
              "emergency_investment_in_healthcare    0.669022\n",
              "investment_in_vaccines                0.636709\n",
              "facial_coverings                      0.000950\n",
              "vaccination_policy                    0.001396\n",
              "stringency_index                      0.001201\n",
              "dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 88
        }
      ]
    }
  ]
}