fetch_test.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Notebook for testing changes to hospitalization data fetch script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_date(date):\n",
    "    \"\"\"Convert date to datetime with handling for Excel date numbers\"\"\"\n",
    "    try:\n",
    "        date = int(date)\n",
    "        return pd.to_datetime('1899-12-30') + pd.to_timedelta(date, 'D')\n",
    "    except:\n",
    "        return pd.to_datetime(date[0:10])\n",
    "    \n",
    "def fix_date_index(df):\n",
    "    \"\"\"Convert date index strings to datetime objects\"\"\"\n",
    "    date_index = df.index.levels[2].to_series()\n",
    "    date_index_fixed = date_index.apply(parse_date) + pd.DateOffset(1)\n",
    "    df.index = df.index.set_levels(date_index_fixed, level=2)\n",
    "    \n",
    "def generate_date_range(index):\n",
    "    \"\"\"Generate DatetimeIndex using first and last dates from index column\"\"\"\n",
    "    if type(index[0]) == str:\n",
    "        start = parse_date(index[0].split(\" \")[-1])\n",
    "        end = parse_date(index[-1].split(\" \")[-1])\n",
    "    else:\n",
    "        start = index[0]\n",
    "        end = index[-1]\n",
    "    \n",
    "    return pd.date_range(start=start, end=end)\n",
    "    \n",
    "tsa_county_map = {\n",
    "    'A': ['Armstrong', 'Briscoe', 'Carson', 'Childress', 'Collingsworth', 'Dallam', 'Deaf Smith', \n",
    "          'Donley', 'Gray', 'Hall', 'Hansford', 'Hartley', 'Hemphill', 'Hutchinson', 'Lipscomb', \n",
    "          'Moore', 'Ochiltree', 'Oldham', 'Parmer', 'Potter', 'Randall', 'Roberts', 'Sherman', \n",
    "          'Swisher', 'Wheeler'],\n",
    "    'B': ['Bailey', 'Borden', 'Castro', 'Cochran', 'Cottle', 'Crosby', 'Dawson', 'Dickens', 'Floyd', \n",
    "          'Gaines', 'Garza', 'Hale', 'Hockley', 'Kent', 'King', 'Lamb', 'Lubbock', 'Lynn', 'Motley', \n",
    "          'Scurry', 'Terry', 'Yoakum'],\n",
    "    'C': ['Archer', 'Baylor', 'Clay', 'Foard', 'Hardeman', 'Jack', 'Montague', 'Wichita', 'Wilbarger', 'Young'],\n",
    "    'D': ['Brown', 'Callahan', 'Coleman', 'Comanche', 'Eastland', 'Fisher', 'Haskell', 'Jones', \n",
    "          'Knox', 'Mitchell', 'Nolan', 'Shackelford', 'Stephens', 'Stonewall', 'Taylor', 'Throckmorton'],\n",
    "    'E': ['Collin', 'Cooke', 'Dallas', 'Denton', 'Ellis', 'Erath', 'Fannin', 'Grayson', 'Hood', \n",
    "          'Hunt', 'Johnson', 'Kaufman', 'Navarro', 'Palo Pinto', 'Parker', 'Rockwall', \n",
    "          'Somervell', 'Tarrant', 'Wise'],\n",
    "    'F': ['Bowie', 'Cass', 'Delta', 'Hopkins', 'Lamar', 'Morris', 'Red River', 'Titus'],\n",
    "    'G': ['Anderson', 'Camp', 'Cherokee', 'Franklin', 'Freestone', 'Gregg', 'Harrison', 'Henderson', \n",
    "          'Houston', 'Marion', 'Panola', 'Rains', 'Rusk', 'Shelby', 'Smith', 'Trinity', 'Upshur', \n",
    "          'Van Zandt', 'Wood'],\n",
    "    'H': ['Angelina', 'Nacogdoches', 'Polk', 'Sabine', 'San Augustine', 'San Jacinto', 'Tyler'],\n",
    "    'I': ['Culberson', 'El Paso', 'Hudspeth'],\n",
    "    'J': ['Andrews', 'Brewster', 'Crane', 'Ector', 'Glasscock', 'Howard', 'Jeff Davis', 'Loving', \n",
    "          'Martin', 'Midland', 'Pecos', 'Presidio', 'Reeves', 'Terrell', 'Upton', 'Ward', 'Winkler'],\n",
    "    'K': ['Coke', 'Concho', 'Crockett', 'Irion', 'Kimble', 'Mason', 'Mcculloch', 'Menard', 'Reagan', \n",
    "          'Runnels', 'Schleicher', 'Sterling', 'Sutton', 'Tom Green'],\n",
    "    'L': ['Bell', 'Coryell', 'Hamilton', 'Lampasas', 'Milam', 'Mills'],\n",
    "    'M': ['Bosque', 'Falls', 'Hill', 'Limestone', 'Mclennan'],\n",
    "    'N': ['Brazos', 'Burleson', 'Grimes', 'Leon', 'Madison', 'Robertson', 'Washington'],\n",
    "    'O': ['Bastrop', 'Blanco' ,'Burnet' ,'Caldwell', 'Fayette', 'Hays', 'Lee', 'Llano', 'San Saba', \n",
    "          'Travis', 'Williamson'],\n",
    "    'P': ['Atascosa', 'Bandera', 'Bexar', 'Comal', 'Dimmit', 'Edwards', 'Frio', 'Gillespie', \n",
    "          'Gonzales', 'Guadalupe', 'Karnes', 'Kendall', 'Kerr', 'Kinney', 'La Salle', 'Maverick', \n",
    "          'Medina', 'Real', 'Uvalde', 'Val Verde', 'Wilson', 'Zavala'],\n",
    "    'Q': ['Austin', 'Colorado', 'Fort Bend', 'Harris', 'Matagorda', 'Montgomery', 'Walker', 'Waller', 'Wharton'],\n",
    "    'R': ['Brazoria', 'Chambers', 'Galveston', 'Hardin', 'Jasper', 'Jefferson', 'Liberty', 'Newton', 'Orange'],\n",
    "    'S': ['Calhoun', 'Dewitt', 'De Witt', 'Goliad', 'Jackson', 'Lavaca', 'Victoria'],\n",
    "    'T': ['Jim Hogg', 'Webb', 'Zapata'],\n",
    "    'U': ['Aransas', 'Bee', 'Brooks', 'Duval', 'Jim Wells', 'Kenedy', 'Kleberg', 'Live Oak', \n",
    "          'Mcmullen', 'Nueces', 'Refugio', 'San Patricio'],\n",
    "    'V': ['Cameron', 'Hidalgo', 'Starr', 'Willacy'],\n",
    "    'Total': ['Total', 'Grand Total']\n",
    "}\n",
    "\n",
    "def map_county_to_tsa(county):\n",
    "    \"\"\"Map county name to TSA\"\"\"\n",
    "    for k,v in tsa_county_map.items():\n",
    "        if county.title() in v:\n",
    "            return(k)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hospitalization Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.dshs.texas.gov/sites/default/files/chs/data/COVID/Combined%20Hospital%20Data%20over%20Time%20by%20TSA%20Region.xlsx'\n",
    "url = \"../covid-texas-data/HospitalDataOverTimeByTSA/2023-05-10.xlsx\"\n",
    "df = pd.read_excel(url, sheet_name=None, header=2, index_col=[0, 1], nrows=23, engine=\"openpyxl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total Available Beds'].replace('--', np.nan, inplace=True)\n",
    "df['Total Available Beds'] = df['Total Available Beds'].apply(pd.to_numeric)\n",
    "df['Total Available Beds'].loc['Total', 'Statewide Total'] = df['Total Available Beds'][:-1].sum()\n",
    "df_total_beds_available = pd.DataFrame(df['Total Available Beds'].stack())\n",
    "df_total_beds_available.rename(columns = {0: 'total_beds_available'}, inplace=True)\n",
    "fix_date_index(df_total_beds_available)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Total Occupied Beds'].replace('--', np.nan, inplace=True)\n",
    "df['Total Occupied Beds'] = df['Total Occupied Beds'].apply(pd.to_numeric)\n",
    "df['Total Occupied Beds'].loc['Total', 'Statewide Total'] = df['Total Occupied Beds'][:-1].sum()\n",
    "df_total_beds_occupied = pd.DataFrame(df['Total Occupied Beds'].stack())\n",
    "df_total_beds_occupied.rename(columns = {0: 'total_beds_occupied'}, inplace=True)\n",
    "fix_date_index(df_total_beds_occupied)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Adult ICU Beds Available'].replace('--', np.nan, inplace=True)\n",
    "df['Adult ICU Beds Available'] = df['Adult ICU Beds Available'].apply(pd.to_numeric)\n",
    "df['Adult ICU Beds Available'].loc['Total', 'Statewide Total'] = df['Adult ICU Beds Available'][:-1].sum()\n",
    "df_icu_beds_available = pd.DataFrame(df['Adult ICU Beds Available'].stack())\n",
    "df_icu_beds_available.rename(columns = {0: 'icu_beds_available'}, inplace=True)\n",
    "fix_date_index(df_icu_beds_available)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['ICU Beds Occupied'].replace('--', np.nan, inplace=True)\n",
    "df['ICU Beds Occupied'] = df['ICU Beds Occupied'].apply(pd.to_numeric)\n",
    "df['ICU Beds Occupied'].loc['Total', 'Statewide Total'] = df['ICU Beds Occupied'][:-1].sum()\n",
    "df_icu_beds_occupied = pd.DataFrame(df['ICU Beds Occupied'].stack())\n",
    "df_icu_beds_occupied.rename(columns = {0: 'icu_beds_occupied'}, inplace=True)\n",
    "fix_date_index(df_icu_beds_occupied)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['COVID-19 Hospitalizations'].loc['Total', 'Statewide Total'] = df['COVID-19 Hospitalizations'][:-1].sum()\n",
    "df_covid_inpatients = pd.DataFrame(df['COVID-19 Hospitalizations'].stack())\n",
    "df_covid_inpatients.rename(columns = {0: 'covid_inpatients'}, inplace=True)\n",
    "fix_date_index(df_covid_inpatients)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Adult COVID-19 ICU'].loc['Total', 'Statewide Total'] = df['Adult COVID-19 ICU'][:-1].sum()\n",
    "df_covid_icu_inpatients = pd.DataFrame(df['Adult COVID-19 ICU'].stack())\n",
    "df_covid_icu_inpatients.rename(columns = {0: 'covid_icu_inpatients'}, inplace=True)\n",
    "fix_date_index(df_covid_icu_inpatients)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total Case Count Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.dshs.texas.gov/sites/default/files/chs/data/COVID/Texas%20COVID-19%20Cumulative%20Confirmed%20Cases%20by%20County.xlsx'\n",
    "url = \"../covid-texas-data/ConfirmedCasesOverTimeByCounty/2023-05-10.xlsx\"\n",
    "df = pd.read_excel(url, sheet_name=None, index_col=[0], header=2, nrows=256)\n",
    "df = pd.concat(df, axis=1).droplevel(0, axis=1)\n",
    "df.drop(columns=[\"Unknown Date\", \"Total\", \"2020 Total\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = generate_date_range(df.columns)\n",
    "df = df.loc[:, df.columns > '2020-04-11']\n",
    "df.insert(0, 'TSA ID', [map_county_to_tsa(county) for county in df.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby(['TSA ID']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tsa_location_map = dict(zip(df_total_beds_available.index.get_level_values(0).drop_duplicates(), \n",
    "                            df_total_beds_available.index.get_level_values(1).drop_duplicates()))\n",
    "df['TSA AREA'] = [tsa_location_map[tsa] for tsa in df.index]\n",
    "df.set_index('TSA AREA', append=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cases = pd.DataFrame(df.stack())\n",
    "df_cases.rename(columns = {0: 'cases'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Case Count Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.dshs.texas.gov/sites/default/files/chs/data/COVID/Texas%20COVID-19%20New%20Confirmed%20Cases%20by%20County.xlsx'\n",
    "url = \"../covid-texas-data/NewConfirmedCasesOverTimeByCounty/2023-05-10.xlsx\"\n",
    "df = pd.read_excel(url, sheet_name=None, index_col=[0], header=2, nrows=256)\n",
    "df = pd.concat(df, axis=1).droplevel(0, axis=1)\n",
    "df.drop(columns=[\"Unknown Date\", \"Total\", \"Total 2021\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = generate_date_range(df.columns)\n",
    "df = df.loc[:, df.columns > '2020-04-11']\n",
    "df.insert(0, 'TSA ID', [map_county_to_tsa(county) for county in df.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby(['TSA ID']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['TSA AREA'] = [tsa_location_map[tsa] for tsa in df.index]\n",
    "df.set_index('TSA AREA', append=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cases_new = pd.DataFrame(df.stack())\n",
    "df_cases_new.rename(columns = {0: 'cases_new'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Total Death Count Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.dshs.texas.gov/sites/default/files/chs/data/COVID/Texas%20COVID-19%20Fatality%20Count%20Data%20by%20County.xlsx'\n",
    "url = \"../covid-texas-data/FatalitiesOverTimeByCounty/2023-05-10.xlsx\"\n",
    "df = pd.read_excel(url, sheet_name=None, index_col=[0], header=2, nrows=256)\n",
    "df = pd.concat(df, axis=1).droplevel(0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_date_range = generate_date_range(df.columns)\n",
    "df.columns = new_date_range\n",
    "df_new = df.diff(axis=1) # Extract daily death count\n",
    "df = df.loc[:, df.columns > '2020-04-11']\n",
    "df.insert(0, 'TSA ID', [map_county_to_tsa(county) for county in df.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.groupby(['TSA ID']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Death data lags all other data by one day. Duplicate the last day of death data into a new column using last date from case dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[df_cases.index.get_level_values(2)[-1]] = df.iloc[:, -1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['TSA AREA'] = [tsa_location_map[tsa] for tsa in df.index]\n",
    "df.set_index('TSA AREA', append=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_deaths = pd.DataFrame(df.stack())\n",
    "df_deaths.rename(columns = {0: 'deaths'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Daily Death Count Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = df_new.loc[:, df_new.columns > '2020-04-11']\n",
    "df_new.insert(0, 'TSA ID', [map_county_to_tsa(county) for county in df_new.index])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new = df_new.groupby(['TSA ID']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_new['TSA AREA'] = [tsa_location_map[tsa] for tsa in df_new.index]\n",
    "df_new.set_index('TSA AREA', append=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_deaths_new = pd.DataFrame(df_new.stack())\n",
    "df_deaths_new.rename(columns = {0: 'deaths_new'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge the dataframes together"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = [df_total_beds_available, df_total_beds_occupied, df_icu_beds_available, \n",
    "      df_icu_beds_occupied, df_covid_inpatients, df_covid_icu_inpatients, \n",
    "      df_cases, df_cases_new, df_deaths, df_deaths_new]\n",
    "df_merged = pd.concat(df, join='outer', axis=1).reindex(df_total_beds_available.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.index.names = ['tsa', 'location', 'date']\n",
    "df_merged.reset_index(inplace=True)\n",
    "df_merged['tsa'] = df_merged['tsa'].apply(lambda x: x.replace('.', ''))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export unmodified data to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.to_csv('data.csv', index=False, float_format='%d', date_format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean data to remove extraneous values that impact charting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.loc[(df_merged['tsa'] == 'N') & (df_merged['date'] == '2020-04-28'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-05-15'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'I') & (df_merged['date'] == '2020-05-31'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2020-06-07'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-06-26'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-06-17'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-17'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'B') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-07-23'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'E') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'I') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-23'), ['covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'P') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Q') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'S') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2020-07-23'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-23'), ['total_beds_occupied', 'total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-07-24'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'B') & (df_merged['date'] == '2020-07-24'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2020-07-24'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-07-24'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-07-24'), 'total_beds_available'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-24'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-07-24'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-24'), 'covid_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2020-07-24'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-24'), ['total_beds_occupied', 'total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-07-25'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2020-07-25'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-07-25'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-07-25'), 'total_beds_available'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-25'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-07-25'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-25'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-25'), ['total_beds_occupied', 'total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'icu_beds_occupied', 'covid_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-07-26'), ['icu_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'B') & (df_merged['date'] == '2020-07-26'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'E') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'F') & (df_merged['date'] == '2020-07-26'), ['covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'G') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'H') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'I') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'N') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Q') & (df_merged['date'] == '2020-07-26'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'U') & (df_merged['date'] == '2020-07-26'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-26'), ['total_beds_occupied', 'total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'B') & (df_merged['date'] == '2020-07-27'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'H') & (df_merged['date'] == '2020-07-27'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-07-27'), 'total_beds_available'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-07-27'), 'covid_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-07-27'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-27'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-07-27'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-27'), ['total_beds_occupied', 'total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-07-28'), ['total_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2020-07-28'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-07-28'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'H') & (df_merged['date'] == '2020-07-28'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-28'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-07-28'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-28'), ['total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-07-29'), ['total_beds_occupied', 'covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2020-07-29'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-07-29'), ['total_beds_available', 'icu_beds_available']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-07-31'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Q') & (df_merged['date'] == '2020-07-31'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2020-07-31'), 'covid_inpatients'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'G') & (df_merged['date'] == '2020-08-01'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-08-01'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'M') & (df_merged['date'] == '2020-08-01'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'U') & (df_merged['date'] == '2020-08-01'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'F') & (df_merged['date'] == '2020-08-02'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'S') & (df_merged['date'] == '2020-08-08'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-08-09'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'P') & (df_merged['date'] == '2020-08-19'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-08-28'), 'total_beds_available'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'B') & (df_merged['date'] == '2020-09-12'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'P') & (df_merged['date'] == '2020-09-16'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-09-28'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2020-10-09'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2020-10-18'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'K') & (df_merged['date'] == '2020-10-20'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-10-21'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2020-10-28'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'Q') & (df_merged['date'] == '2020-11-01'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2020-11-01'), 'covid_inpatients'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'S') & (df_merged['date'] == '2020-11-02'), 'icu_beds_available'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'D') & (df_merged['date'] == '2020-11-08'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2020-11-17'), 'icu_beds_available'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'A') & (df_merged['date'] == '2020-11-27'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-12-10'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2020-12-17'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'H') & (df_merged['date'] == '2020-12-18'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2021-01-02'), 'covid_icu_inpatients'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2021-01-07'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'H') & (df_merged['date'] == '2021-01-08'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2021-01-10'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'G') & (df_merged['date'] == '2021-01-10'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2021-01-10'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'C') & (df_merged['date'] == '2021-01-11'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'G') & (df_merged['date'] == '2021-01-11'), ['total_beds_occupied', 'icu_beds_occupied']] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2021-01-11'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2021-01-14'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'F') & (df_merged['date'] == '2021-01-27'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'N') & (df_merged['date'] == '2021-02-01'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2021-05-12'), ['covid_inpatients', 'covid_icu_inpatients']] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2021-06-29'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2021-06-29'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2021-07-12'), 'icu_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2021-07-12'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'T') & (df_merged['date'] == '2021-08-10'), 'cases_new'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'I') & (df_merged['date'] == '2021-11-28'), 'icu_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2022-03-03'), 'total_beds_occupied'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2022-03-04'), 'total_beds_occupied'] = np.nan\n",
    "\n",
    "df_merged.loc[(df_merged['tsa'] == 'Total') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'E') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'G') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'I') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'J') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'L') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'O') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'P') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'Q') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'R') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'S') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'U') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan\n",
    "df_merged.loc[(df_merged['tsa'] == 'V') & (df_merged['date'] == '2022-03-07'), 'covid_icu_inpatients'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.loc[df_merged['cases_new'] < 0, 'cases_new'] = np.nan # Eliminate negative values from cases_new"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Export cleaned data to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged.to_csv('docs/data.csv', index=False, float_format='%d', date_format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "covid-texas",
   "language": "python",
   "name": "covid-texas"
  },
  "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}