Aggregate State Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import urllib.request, json \n",
    "\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = pd.read_csv('./us-states.csv')\n",
    "# df = df[df.date == max(df.date)]\n",
    "# df = df[(df.state != 'Guam') & (df.state != 'Puerto Rico') & (df.state != 'Virgin Islands')]\n",
    "# df.drop(['fips', 'date'], axis=1, inplace=True)\n",
    "# df.columns = ['State', 'Infected', 'Deaths']\n",
    "# df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Live Infected, Deaths, COVID19 Testing data\n",
    "(could maybe use this data for number of infections and deaths per state)  \n",
    "http://coronavirusapi.com/states.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "states = {\n",
    "        'AK': 'Alaska',\n",
    "        'AL': 'Alabama',\n",
    "        'AR': 'Arkansas',\n",
    "        'AZ': 'Arizona',\n",
    "        'CA': 'California',\n",
    "        'CO': 'Colorado',\n",
    "        'CT': 'Connecticut',\n",
    "        'DC': 'District of Columbia',\n",
    "        'DE': 'Delaware',\n",
    "        'FL': 'Florida',\n",
    "        'GA': 'Georgia',\n",
    "        'GU': 'Guam',\n",
    "        'HI': 'Hawaii',\n",
    "        'IA': 'Iowa',\n",
    "        'ID': 'Idaho',\n",
    "        'IL': 'Illinois',\n",
    "        'IN': 'Indiana',\n",
    "        'KS': 'Kansas',\n",
    "        'KY': 'Kentucky',\n",
    "        'LA': 'Louisiana',\n",
    "        'MA': 'Massachusetts',\n",
    "        'MD': 'Maryland',\n",
    "        'ME': 'Maine',\n",
    "        'MI': 'Michigan',\n",
    "        'MN': 'Minnesota',\n",
    "        'MO': 'Missouri',\n",
    "        'MS': 'Mississippi',\n",
    "        'MT': 'Montana',\n",
    "        'NA': 'National',\n",
    "        'NC': 'North Carolina',\n",
    "        'ND': 'North Dakota',\n",
    "        'NE': 'Nebraska',\n",
    "        'NH': 'New Hampshire',\n",
    "        'NJ': 'New Jersey',\n",
    "        'NM': 'New Mexico',\n",
    "        'NV': 'Nevada',\n",
    "        'NY': 'New York',\n",
    "        'OH': 'Ohio',\n",
    "        'OK': 'Oklahoma',\n",
    "        'OR': 'Oregon',\n",
    "        'PA': 'Pennsylvania',\n",
    "        'PR': 'Puerto Rico',\n",
    "        'RI': 'Rhode Island',\n",
    "        'SC': 'South Carolina',\n",
    "        'SD': 'South Dakota',\n",
    "        'TN': 'Tennessee',\n",
    "        'TX': 'Texas',\n",
    "        'UT': 'Utah',\n",
    "        'VA': 'Virginia',\n",
    "        'VT': 'Vermont',\n",
    "        'WA': 'Washington',\n",
    "        'WI': 'Wisconsin',\n",
    "        'WV': 'West Virginia',\n",
    "        'WY': 'Wyoming'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Tested</th>\n",
       "      <th>Infected</th>\n",
       "      <th>Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>5022</td>\n",
       "      <td>171</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>13078</td>\n",
       "      <td>1676</td>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>11143</td>\n",
       "      <td>819</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>27160</td>\n",
       "      <td>2019</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>126700</td>\n",
       "      <td>12026</td>\n",
       "      <td>276.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Tested  Infected  Deaths\n",
       "0      Alaska    5022       171     5.0\n",
       "1     Alabama   13078      1676    45.0\n",
       "2    Arkansas   11143       819    14.0\n",
       "3     Arizona   27160      2019    52.0\n",
       "4  California  126700     12026   276.0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('http://coronavirusapi.com/states.csv')\n",
    "df['name,'] = df['name,'].apply(lambda r: states[r])\n",
    "df.columns = ['State', 'Tested', 'Infected', 'Deaths']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Total Population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pop = pd.read_csv('./predictor_data/population.csv')\n",
    "df_pop = df_pop[['State', 'Pop', 'density']]\n",
    "df_pop.columns =['State', 'Population', 'Pop Density']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_pop, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Gini coefficient"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/gini.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ICU beds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_of_interest = ['state', 'all_icu']\n",
    "df_icu = pd.read_excel('./predictor_data/icu_beds.xlsx', usecols=cols_of_interest)\n",
    "df_icu = df_icu.groupby('state').sum().reset_index()\n",
    "df_icu.columns = ['State', 'ICU Beds']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_icu, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Income per capita"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(51, 9)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/income_per_capita.csv'), on='State', how='inner')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### GDP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/state_gdp.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Unemployment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/unemployment.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sex = pd.read_csv('./predictor_data/sex.csv')\n",
    "df_sex['Sex Ratio'] = df_sex.Male / df_sex.Female \n",
    "df_sex = df_sex[['Location', 'Sex Ratio']]\n",
    "df_sex.columns = ['State', 'Sex Ratio']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_sex, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Smoking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_smoke = pd.read_csv('./predictor_data/smoking.csv')\n",
    "df_smoke.columns = ['State', 'Smoking Rate']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_smoke, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Flu and Pneumonia Death Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_flu = pd.read_csv('./predictor_data/Influenza_Pneumonia Mortality by State.csv')\n",
    "df_flu = df_flu[df_flu.YEAR == 2018]\n",
    "df_flu = df_flu[['STATE', 'RATE']]\n",
    "df_flu.columns = ['State', 'Flu Deaths']\n",
    "df_flu['State'] = df_flu['State'].apply(lambda r: states[r])\n",
    "dc_df = pd.DataFrame([['District of Columbia', 11.2]], columns=['State', 'Flu Deaths']) # https://www.kff.org/other/state-indicator/influenza-and-pneumonia-death-rate/\n",
    "df_flu = df_flu.append(dc_df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_flu, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Chronic Lower Respiratory Disease Death Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_resp = pd.read_csv('./predictor_data/Chronic Lower Respiratory Disease Mortality by State.csv')\n",
    "df_resp = df_resp[df_resp.YEAR == 2018]\n",
    "df_resp = df_resp[['STATE', 'RATE']]\n",
    "df_resp.columns = ['State', 'Respiratory Deaths']\n",
    "df_resp['State'] = df_resp['State'].apply(lambda r: states[r])\n",
    "dc_df = pd.DataFrame([['District of Columbia', 19.6]], columns=['State', 'Respiratory Deaths']) # https://www.cdc.gov/nchs/pressroom/states/dc/dc.htm\n",
    "df_resp = df_resp.append(dc_df, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_resp, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Doctors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_doc = pd.read_csv('./predictor_data/doctors.csv')\n",
    "df_doc = df_doc[['Location', 'Total']]\n",
    "df_doc.columns = ['State', 'Physicians']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_doc, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number Hospitals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_hosp = pd.read_csv('./predictor_data/hospitals.csv')\n",
    "df_hosp.columns = ['State', 'Hospitals']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_hosp, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Health Spending per capita"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_spend = pd.read_csv('./predictor_data/health_spending.csv')\n",
    "df_spend.columns = ['State', 'Health Spending']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_spend, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pollution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "pollution_url = 'https://www.americashealthrankings.org/api/v1/measures/metrics/201?measure_id=147'\n",
    "with urllib.request.urlopen(pollution_url) as url:\n",
    "    data = json.loads(url.read().decode())\n",
    "pollution = [{'State': states[i['StateCode']], 'Pollution':float(i['Value'])} for i in data['items'] if i['StateCode'] != 'ALL']\n",
    "df_pol = pd.DataFrame(pollution)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_pol, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Airport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(34, 2)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_air = pd.read_csv('./predictor_data/airports.csv')\n",
    "df_air['State'].value_counts()\n",
    "df_air = df_air['State'].value_counts().rename_axis('State').reset_index(name='Med-Large Airports')\n",
    "df_air['State'] = df_air['State'].apply(lambda r: states[r])\n",
    "df_air.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_air, on='State', how='left')\n",
    "df = df.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Avg Temperature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/temperature.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Urbanization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/urbanization.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Age Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_age = pd.read_csv('./predictor_data/age.csv')\n",
    "df_age['State'] = df_age['Location']\n",
    "df_age['Age 0-25'] = df_age['Children 0-18'] + df_age['Adults 19-25']\n",
    "df_age['Age 26-54'] = df_age['Adults 26-34'] + df_age['Adults 35-54']\n",
    "df_age['Age 55+'] = df_age['Adults 55-64'] + df_age['65+']\n",
    "df_age.drop(['Location', 'Children 0-18', 'Adults 19-25', 'Adults 26-34', 'Adults 35-54', 'Total', 'Footnotes', 'Adults 55-64', '65+'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(df_age, on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### School Closure Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(pd.read_csv('./predictor_data/school_closures.csv'), on='State', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Print!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Tested</th>\n",
       "      <th>Infected</th>\n",
       "      <th>Deaths</th>\n",
       "      <th>Population</th>\n",
       "      <th>Pop Density</th>\n",
       "      <th>Gini</th>\n",
       "      <th>ICU Beds</th>\n",
       "      <th>Income</th>\n",
       "      <th>GDP</th>\n",
       "      <th>Unemployment</th>\n",
       "      <th>Sex Ratio</th>\n",
       "      <th>Smoking Rate</th>\n",
       "      <th>Flu Deaths</th>\n",
       "      <th>Respiratory Deaths</th>\n",
       "      <th>Physicians</th>\n",
       "      <th>Hospitals</th>\n",
       "      <th>Health Spending</th>\n",
       "      <th>Pollution</th>\n",
       "      <th>Med-Large Airports</th>\n",
       "      <th>Temperature</th>\n",
       "      <th>Urban</th>\n",
       "      <th>Age 0-25</th>\n",
       "      <th>Age 26-54</th>\n",
       "      <th>Age 55+</th>\n",
       "      <th>School Closure Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>5022</td>\n",
       "      <td>171</td>\n",
       "      <td>5.0</td>\n",
       "      <td>734002</td>\n",
       "      <td>1.2863</td>\n",
       "      <td>0.4081</td>\n",
       "      <td>119</td>\n",
       "      <td>59687</td>\n",
       "      <td>73205</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1.054688</td>\n",
       "      <td>21.0</td>\n",
       "      <td>12.1</td>\n",
       "      <td>35.3</td>\n",
       "      <td>1900</td>\n",
       "      <td>21</td>\n",
       "      <td>11064</td>\n",
       "      <td>6.4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>26.6</td>\n",
       "      <td>66.0</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.25</td>\n",
       "      <td>03/19/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>13078</td>\n",
       "      <td>1676</td>\n",
       "      <td>45.0</td>\n",
       "      <td>4908621</td>\n",
       "      <td>96.9221</td>\n",
       "      <td>0.4847</td>\n",
       "      <td>1533</td>\n",
       "      <td>42334</td>\n",
       "      <td>45219</td>\n",
       "      <td>2.7</td>\n",
       "      <td>0.930145</td>\n",
       "      <td>20.9</td>\n",
       "      <td>21.4</td>\n",
       "      <td>58.0</td>\n",
       "      <td>12205</td>\n",
       "      <td>101</td>\n",
       "      <td>7281</td>\n",
       "      <td>8.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>62.8</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.31</td>\n",
       "      <td>03/16/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>11143</td>\n",
       "      <td>819</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3038999</td>\n",
       "      <td>58.4030</td>\n",
       "      <td>0.4719</td>\n",
       "      <td>732</td>\n",
       "      <td>42566</td>\n",
       "      <td>42454</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.956630</td>\n",
       "      <td>22.3</td>\n",
       "      <td>18.0</td>\n",
       "      <td>61.7</td>\n",
       "      <td>7150</td>\n",
       "      <td>88</td>\n",
       "      <td>7408</td>\n",
       "      <td>7.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60.4</td>\n",
       "      <td>56.2</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.37</td>\n",
       "      <td>0.30</td>\n",
       "      <td>03/17/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>27160</td>\n",
       "      <td>2019</td>\n",
       "      <td>52.0</td>\n",
       "      <td>7378494</td>\n",
       "      <td>64.9550</td>\n",
       "      <td>0.4713</td>\n",
       "      <td>1559</td>\n",
       "      <td>43650</td>\n",
       "      <td>48055</td>\n",
       "      <td>4.5</td>\n",
       "      <td>0.966965</td>\n",
       "      <td>15.6</td>\n",
       "      <td>12.4</td>\n",
       "      <td>41.2</td>\n",
       "      <td>17806</td>\n",
       "      <td>83</td>\n",
       "      <td>6452</td>\n",
       "      <td>9.7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>60.3</td>\n",
       "      <td>89.8</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.30</td>\n",
       "      <td>03/16/20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>126700</td>\n",
       "      <td>12026</td>\n",
       "      <td>276.0</td>\n",
       "      <td>39937489</td>\n",
       "      <td>256.3727</td>\n",
       "      <td>0.4899</td>\n",
       "      <td>7338</td>\n",
       "      <td>62586</td>\n",
       "      <td>74205</td>\n",
       "      <td>3.9</td>\n",
       "      <td>0.975113</td>\n",
       "      <td>11.3</td>\n",
       "      <td>15.6</td>\n",
       "      <td>30.9</td>\n",
       "      <td>112906</td>\n",
       "      <td>359</td>\n",
       "      <td>7549</td>\n",
       "      <td>12.8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>59.4</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.33</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.26</td>\n",
       "      <td>03/19/20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Tested  Infected  Deaths  Population  Pop Density    Gini  \\\n",
       "0      Alaska    5022       171     5.0      734002       1.2863  0.4081   \n",
       "1     Alabama   13078      1676    45.0     4908621      96.9221  0.4847   \n",
       "2    Arkansas   11143       819    14.0     3038999      58.4030  0.4719   \n",
       "3     Arizona   27160      2019    52.0     7378494      64.9550  0.4713   \n",
       "4  California  126700     12026   276.0    39937489     256.3727  0.4899   \n",
       "\n",
       "   ICU Beds  Income    GDP  Unemployment  Sex Ratio  Smoking Rate  Flu Deaths  \\\n",
       "0       119   59687  73205           5.8   1.054688          21.0        12.1   \n",
       "1      1533   42334  45219           2.7   0.930145          20.9        21.4   \n",
       "2       732   42566  42454           3.5   0.956630          22.3        18.0   \n",
       "3      1559   43650  48055           4.5   0.966965          15.6        12.4   \n",
       "4      7338   62586  74205           3.9   0.975113          11.3        15.6   \n",
       "\n",
       "   Respiratory Deaths  Physicians  Hospitals  Health Spending  Pollution  \\\n",
       "0                35.3        1900         21            11064        6.4   \n",
       "1                58.0       12205        101             7281        8.1   \n",
       "2                61.7        7150         88             7408        7.1   \n",
       "3                41.2       17806         83             6452        9.7   \n",
       "4                30.9      112906        359             7549       12.8   \n",
       "\n",
       "   Med-Large Airports  Temperature  Urban  Age 0-25  Age 26-54  Age 55+  \\\n",
       "0                 1.0         26.6   66.0      0.36       0.39     0.25   \n",
       "1                 1.0         62.8   59.0      0.33       0.37     0.31   \n",
       "2                 0.0         60.4   56.2      0.34       0.37     0.30   \n",
       "3                 1.0         60.3   89.8      0.33       0.36     0.30   \n",
       "4                 9.0         59.4   95.0      0.33       0.40     0.26   \n",
       "\n",
       "  School Closure Date  \n",
       "0            03/19/20  \n",
       "1            03/16/20  \n",
       "2            03/17/20  \n",
       "3            03/16/20  \n",
       "4            03/19/20  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.to_csv('./prod/COVID19_state.csv', index=False)\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}