DataCheckpoint_Group116_WI24.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**If you lost points on the last checkpoint you can get them back by responding to TA/IA feedback**\n",
    "\n",
    "Update/change the relevant sections where you lost those points, make sure you respond on GitHub Issues to your TA/IA to call their attention to the changes you made here.\n",
    "\n",
    "Please update your Timeline... no battle plan survives contact with the enemy, so make sure we understand how your plans have changed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COGS 108 - Data Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Names\n",
    "\n",
    "- Benjamin Hinnant\n",
    "- Desmond Vu\n",
    "- Kayla Maldonado\n",
    "- Alexander Gao\n",
    "- Keshav Tiwari\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Research Question\n",
    "\n",
    "What is the relationship between the racial backgrounds and urbanization levels of western and eastern states, and what is their effect on COVID-19 mortality rates in the USA?  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Background & Prior Work\n",
    "\n",
    "During the COVID-19 pandemic, several analyses determined that people of color (POC) were experiencing a disproportionately larger COVID-19 mortality rate than Caucasians. One report from the APM Research Lab was written by Elisabeth Gawthrop, who used state-reported data from the U.S. Centers for Disease Control and Prevention, or CDC. She determined that as of September 27 of 2023, the COVID-19 mortality rate for Black individuals was around 55% higher than the rate for Caucasian individuals. The mortality rate for Latinos was nearly 65% higher than Caucasians (Gawthrop).  \n",
    "\n",
    "Another study done by Latoya Hill and Samantha Artiga used data from the CDC and the National Center for Health Statistics (NCHS). They determined that the largest differences in COVID-19 mortality rates between racial groups occurred during surges in the pandemic when the total mortality rate was particularly high. The first surge in COVID-19 deaths peaked in July 2020, and during this month, Hispanic individuals were five times more likely to die than Caucasians, American Indian or Alaska Natives (AIAN) were four times as likely to die, and Blacks were three times as likely to die. During the second surge in December 2020 and January 2021, all POC analyzed (Blacks, Hispanics, and AIAN) had higher rates of death than Caucasians. (Hill and Artiga).\n",
    "\n",
    "These previous studies have already assessed the relationship between racial background and COVID-19 mortality rates. As a result, we now want to determine the relationship between three variables: racial background, the level of urbanization, and whether the region of interest is an eastern or western state. We then want to determine the effect of these variables on COVID-19 mortality rates. By determining if any of these variables has a significant effect on mortality rates, we hope to potentially discover trends that can guide a future pandemic response.\n",
    "\n",
    "\n",
    "References \n",
    "\n",
    "Gawthrop, Elisabeth. “Color of Coronavirus: COVID-19 deaths analyzed by race and ethnicity — APM Research Lab.” APM Research Lab, 19 October 2023, https://www.apmresearchlab.org/covid/deaths-by-race. Accessed 25 February 2024. \n",
    "\n",
    "Hill, Latoya, and Samantha Artiga. “COVID-19 Cases and Deaths by Race/Ethnicity: Current Data and Changes Over Time.” KFF, 22 August 2022, https://www.kff.org/racial-equity-and-health-policy/issue-brief/covid-19-cases-and-deaths-by-race-ethnicity-current-data-and-changes-over-time/. Accessed 24 February 2024. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hypothesis\n",
    "\n",
    "We predict that areas with a higher urbanization level will have higher COVID-19 mortality rates. Within the confined spaces of an urban region, there are more opportunities for the uninfected population to make contact with COVID-19 infected individuals. We also predict that areas with a larger population of racial minorities will have higher COVID-19 mortality rates, since these communities often have less resources available and may find it more difficult to treat every infected patient optimally. Combining our previous predictions, we predict that urban areas with a larger population of racial minorities will have the highest COVID-19 mortality rates, while rural areas with a smaller population of racial minorities will have the lowest COVID-19 mortality rates."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data\n",
    "## Data Overview\n",
    "**Dataset** #1\n",
    "    - Dataset name: Provisional COVID-19 Deaths by County, and Race and Hispanic Origin\n",
    "    - Link to dataset: link \n",
    "    - Number of Observations: 3,687\n",
    "    - Number of Variables: 21\n",
    "**Description:** The data we will be using to answer our question is: Provisional COVID 19 Deaths by County and Race and Hispanic Origin which came directly from the Centers of Disease Control and Prevention (CDC) website.  This data was collected on a weekly basis from January 4, 2020 to September 27, 2023 from counties in all 50 states. Furthermore, this data set contains a total of ~3700 observations with string and int data types. and is relatively complete, with only NaN values on the different racial populations. The dataset’s columns include county name, state name, total deaths from all causes, deaths involving COVID-19, a description of the level of urbanization and a percentage of each racial category (like Asian, White, Hispanic, American Indian etc). Deaths involving COVID-19 are determined by the presence of an U07.1 diagnosis code assigned to a deceased patient, which can only be assigned if a patient tests positive for COVID-19. The level of urbanization is determined by the National Center for Health Statistics (NCHS) Urban-Rural Classification Scheme for Counties, which divides counties into 6 urban-rural categories that are well-suited for health analyses.\n",
    "**Important Variables:**\n",
    "    - Percentage of Race: this variable indicated the proportion of each racial category affected by COVID-19 in each county\n",
    "    - Urbanization level: describes the level of urbanization a county is from a scale of 1 (large metropolitan city) to 6 (small non-core area). This  provides insights into how smaller or bigger counties address health access, resources, and disease prevention.\n",
    "    - State Name: the state from which the data is collected. Helps in analyzing the regional difference in COVID-19\n",
    "    - Deaths by COVID-19: number of deaths with a leading cause of COVID-19.\n",
    "**Data Wrangling** Since we want to focus on COVID-19 deaths, populations affected, geographic locations and urbanization level, this singular dataset contains everything we need to answer our research question. On the other hand, some concerns we have with this dataset is the missing data on some races due to privacy and unnecessary columns that we won’t need for this project. In order to account for this, we decided to clean the dataset, removing columns that we don’t need like total deaths, and made sure to only observe counties that reported what racial groups were affected. This is to ensure validity by including as many people as we can in our observations. Even though we had to remove some columns, we thought it was a necessary step in order to account for everyone. While also considering there was enough data available from various counties that did include racial populations for our analysis. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "310a0b26",
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning\n",
    "\n",
    "To get the data into a usable format, we first had to keep only the parts of the data that were deaths specific to COVID, as the data also included non-COVID deaths. Based on our research question, we wanted to analyze states on the east and west coast. So, we only kept the remaining data that were for counties in east and west coast states. Then, we had to analyze which parts of the data were missing. Even though there were columns for specific ethnicities such as American Indian or Native Hawaiian, a lot of counties had missing information for these columns and only had data for few columns Non-Hispanic Whites and Non-Hispanic Blacks and NaN for others. In order to standardize the data, while still having a decent number of obvservations, we decided to see how many counties had data for Non-Hispanic Whites, Non-Hispanic Blacks, Non-Hispanic Asians, and Hispanics, which was 170, and removed those which didn’t. Then, we added essentially a column that was for the proportion of the population not in those ethnicities (the remainder/other column basically). Finally, we dropped a lot of columns which were unnecessary to our data, such as codes for counties and states (which do not provide as easily understandable info as county names and state names) and date-range, which was standard across the whole data set (1/2020 to 9/2023). We also removed total deaths, as we only cared about COVID-19 deaths."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "d0a5974d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('Provisional_COVID-19_Deaths_by_County__and_Race_and_Hispanic_Origin_20240224.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "89f9dbe1",
   "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>Data as of</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>State</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Urban Rural Code</th>\n",
       "      <th>FIPS State</th>\n",
       "      <th>FIPS County</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Indicator</th>\n",
       "      <th>...</th>\n",
       "      <th>COVID-19 Deaths</th>\n",
       "      <th>Non-Hispanic White</th>\n",
       "      <th>Non-Hispanic Black</th>\n",
       "      <th>Non-Hispanic American Indian or Alaska Native</th>\n",
       "      <th>Non-Hispanic Asian</th>\n",
       "      <th>Non-Hispanic Native Hawaiian or Other Pacific Islander</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other</th>\n",
       "      <th>Urban Rural Description</th>\n",
       "      <th>Footnote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Anchorage Municipality</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>2020</td>\n",
       "      <td>Distribution of all-cause deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>787</td>\n",
       "      <td>0.568</td>\n",
       "      <td>0.044</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.030</td>\n",
       "      <td>0.033</td>\n",
       "      <td>0.050</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Anchorage Municipality</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>2020</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>787</td>\n",
       "      <td>0.452</td>\n",
       "      <td>0.037</td>\n",
       "      <td>0.255</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.033</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Anchorage Municipality</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>2020</td>\n",
       "      <td>Distribution of population (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>787</td>\n",
       "      <td>0.564</td>\n",
       "      <td>0.052</td>\n",
       "      <td>0.083</td>\n",
       "      <td>0.098</td>\n",
       "      <td>0.031</td>\n",
       "      <td>0.095</td>\n",
       "      <td>0.077</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Fairbanks North Star Borough</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>2090</td>\n",
       "      <td>Distribution of all-cause deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>214</td>\n",
       "      <td>0.710</td>\n",
       "      <td>0.024</td>\n",
       "      <td>0.173</td>\n",
       "      <td>0.020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.027</td>\n",
       "      <td>0.044</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Fairbanks North Star Borough</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>2090</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>214</td>\n",
       "      <td>0.626</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.257</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.056</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Data as of  Start Date    End Date State                   County Name  \\\n",
       "0  09/27/2023  01/01/2020  09/23/2023    AK        Anchorage Municipality   \n",
       "1  09/27/2023  01/01/2020  09/23/2023    AK        Anchorage Municipality   \n",
       "2  09/27/2023  01/01/2020  09/23/2023    AK        Anchorage Municipality   \n",
       "3  09/27/2023  01/01/2020  09/23/2023    AK  Fairbanks North Star Borough   \n",
       "4  09/27/2023  01/01/2020  09/23/2023    AK  Fairbanks North Star Borough   \n",
       "\n",
       "   Urban Rural Code  FIPS State  FIPS County  FIPS Code  \\\n",
       "0                 3           2           20       2020   \n",
       "1                 3           2           20       2020   \n",
       "2                 3           2           20       2020   \n",
       "3                 4           2           90       2090   \n",
       "4                 4           2           90       2090   \n",
       "\n",
       "                              Indicator  ...  COVID-19 Deaths  \\\n",
       "0  Distribution of all-cause deaths (%)  ...              787   \n",
       "1   Distribution of COVID-19 deaths (%)  ...              787   \n",
       "2        Distribution of population (%)  ...              787   \n",
       "3  Distribution of all-cause deaths (%)  ...              214   \n",
       "4   Distribution of COVID-19 deaths (%)  ...              214   \n",
       "\n",
       "   Non-Hispanic White  Non-Hispanic Black  \\\n",
       "0               0.568               0.044   \n",
       "1               0.452               0.037   \n",
       "2               0.564               0.052   \n",
       "3               0.710               0.024   \n",
       "4               0.626                 NaN   \n",
       "\n",
       "   Non-Hispanic American Indian or Alaska Native  Non-Hispanic Asian  \\\n",
       "0                                          0.216               0.058   \n",
       "1                                          0.255               0.111   \n",
       "2                                          0.083               0.098   \n",
       "3                                          0.173               0.020   \n",
       "4                                          0.257                 NaN   \n",
       "\n",
       "   Non-Hispanic Native Hawaiian or Other Pacific Islander  Hispanic  Other  \\\n",
       "0                                              0.030          0.033  0.050   \n",
       "1                                              0.074          0.038  0.033   \n",
       "2                                              0.031          0.095  0.077   \n",
       "3                                                NaN          0.027  0.044   \n",
       "4                                                NaN            NaN  0.056   \n",
       "\n",
       "   Urban Rural Description                                           Footnote  \n",
       "0             Medium metro                                                NaN  \n",
       "1             Medium metro                                                NaN  \n",
       "2             Medium metro                                                NaN  \n",
       "3              Small metro  One or more data cells have counts between 1-9...  \n",
       "4              Small metro  One or more data cells have counts between 1-9...  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55a62eca",
   "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>Data as of</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>State</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Urban Rural Code</th>\n",
       "      <th>FIPS State</th>\n",
       "      <th>FIPS County</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Indicator</th>\n",
       "      <th>...</th>\n",
       "      <th>COVID-19 Deaths</th>\n",
       "      <th>Non-Hispanic White</th>\n",
       "      <th>Non-Hispanic Black</th>\n",
       "      <th>Non-Hispanic American Indian or Alaska Native</th>\n",
       "      <th>Non-Hispanic Asian</th>\n",
       "      <th>Non-Hispanic Native Hawaiian or Other Pacific Islander</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other</th>\n",
       "      <th>Urban Rural Description</th>\n",
       "      <th>Footnote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Anchorage Municipality</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>2020</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>787</td>\n",
       "      <td>0.452</td>\n",
       "      <td>0.037</td>\n",
       "      <td>0.255</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.074</td>\n",
       "      <td>0.038</td>\n",
       "      <td>0.033</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Fairbanks North Star Borough</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>90</td>\n",
       "      <td>2090</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>214</td>\n",
       "      <td>0.626</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.257</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.056</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AK</td>\n",
       "      <td>Matanuska-Susitna Borough</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>170</td>\n",
       "      <td>2170</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>241</td>\n",
       "      <td>0.826</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.083</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.046</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Autauga County</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1001</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>197</td>\n",
       "      <td>0.761</td>\n",
       "      <td>0.218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Baldwin County</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1003</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>653</td>\n",
       "      <td>0.884</td>\n",
       "      <td>0.086</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.023</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Blount County</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>1009</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>103</td>\n",
       "      <td>0.932</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Large fringe metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Calhoun County</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>1015</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>670</td>\n",
       "      <td>0.809</td>\n",
       "      <td>0.170</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Coffee County</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1031</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>201</td>\n",
       "      <td>0.796</td>\n",
       "      <td>0.154</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Micropolitan</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Colbert County</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1033</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>348</td>\n",
       "      <td>0.836</td>\n",
       "      <td>0.126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.034</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>AL</td>\n",
       "      <td>Covington County</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>1039</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>263</td>\n",
       "      <td>0.867</td>\n",
       "      <td>0.129</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Noncore</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Data as of  Start Date    End Date State                   County Name  \\\n",
       "0  09/27/2023  01/01/2020  09/23/2023    AK        Anchorage Municipality   \n",
       "1  09/27/2023  01/01/2020  09/23/2023    AK  Fairbanks North Star Borough   \n",
       "2  09/27/2023  01/01/2020  09/23/2023    AK     Matanuska-Susitna Borough   \n",
       "3  09/27/2023  01/01/2020  09/23/2023    AL                Autauga County   \n",
       "4  09/27/2023  01/01/2020  09/23/2023    AL                Baldwin County   \n",
       "5  09/27/2023  01/01/2020  09/23/2023    AL                 Blount County   \n",
       "6  09/27/2023  01/01/2020  09/23/2023    AL                Calhoun County   \n",
       "7  09/27/2023  01/01/2020  09/23/2023    AL                 Coffee County   \n",
       "8  09/27/2023  01/01/2020  09/23/2023    AL                Colbert County   \n",
       "9  09/27/2023  01/01/2020  09/23/2023    AL              Covington County   \n",
       "\n",
       "   Urban Rural Code  FIPS State  FIPS County  FIPS Code  \\\n",
       "0                 3           2           20       2020   \n",
       "1                 4           2           90       2090   \n",
       "2                 3           2          170       2170   \n",
       "3                 3           1            1       1001   \n",
       "4                 4           1            3       1003   \n",
       "5                 2           1            9       1009   \n",
       "6                 4           1           15       1015   \n",
       "7                 5           1           31       1031   \n",
       "8                 4           1           33       1033   \n",
       "9                 6           1           39       1039   \n",
       "\n",
       "                             Indicator  ...  COVID-19 Deaths  \\\n",
       "0  Distribution of COVID-19 deaths (%)  ...              787   \n",
       "1  Distribution of COVID-19 deaths (%)  ...              214   \n",
       "2  Distribution of COVID-19 deaths (%)  ...              241   \n",
       "3  Distribution of COVID-19 deaths (%)  ...              197   \n",
       "4  Distribution of COVID-19 deaths (%)  ...              653   \n",
       "5  Distribution of COVID-19 deaths (%)  ...              103   \n",
       "6  Distribution of COVID-19 deaths (%)  ...              670   \n",
       "7  Distribution of COVID-19 deaths (%)  ...              201   \n",
       "8  Distribution of COVID-19 deaths (%)  ...              348   \n",
       "9  Distribution of COVID-19 deaths (%)  ...              263   \n",
       "\n",
       "   Non-Hispanic White  Non-Hispanic Black  \\\n",
       "0               0.452               0.037   \n",
       "1               0.626                 NaN   \n",
       "2               0.826                 NaN   \n",
       "3               0.761               0.218   \n",
       "4               0.884               0.086   \n",
       "5               0.932                 NaN   \n",
       "6               0.809               0.170   \n",
       "7               0.796               0.154   \n",
       "8               0.836               0.126   \n",
       "9               0.867               0.129   \n",
       "\n",
       "   Non-Hispanic American Indian or Alaska Native  Non-Hispanic Asian  \\\n",
       "0                                          0.255               0.111   \n",
       "1                                          0.257                 NaN   \n",
       "2                                          0.083                 NaN   \n",
       "3                                            NaN                 NaN   \n",
       "4                                            NaN                 NaN   \n",
       "5                                            NaN                 NaN   \n",
       "6                                            NaN                 NaN   \n",
       "7                                            NaN                 NaN   \n",
       "8                                            NaN                 NaN   \n",
       "9                                            NaN                 NaN   \n",
       "\n",
       "   Non-Hispanic Native Hawaiian or Other Pacific Islander  Hispanic  Other  \\\n",
       "0                                              0.074          0.038  0.033   \n",
       "1                                                NaN            NaN  0.056   \n",
       "2                                                NaN          0.046    NaN   \n",
       "3                                                NaN            NaN    NaN   \n",
       "4                                                NaN          0.023    NaN   \n",
       "5                                                NaN            NaN    NaN   \n",
       "6                                                NaN          0.016    NaN   \n",
       "7                                                NaN            NaN    NaN   \n",
       "8                                                NaN          0.034    NaN   \n",
       "9                                                NaN            NaN    NaN   \n",
       "\n",
       "   Urban Rural Description                                           Footnote  \n",
       "0             Medium metro                                                NaN  \n",
       "1              Small metro  One or more data cells have counts between 1-9...  \n",
       "2             Medium metro  One or more data cells have counts between 1-9...  \n",
       "3             Medium metro  One or more data cells have counts between 1-9...  \n",
       "4              Small metro  One or more data cells have counts between 1-9...  \n",
       "5       Large fringe metro  One or more data cells have counts between 1-9...  \n",
       "6              Small metro  One or more data cells have counts between 1-9...  \n",
       "7             Micropolitan  One or more data cells have counts between 1-9...  \n",
       "8              Small metro  One or more data cells have counts between 1-9...  \n",
       "9                  Noncore  One or more data cells have counts between 1-9...  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Clean Data to only include rows for deaths from COVID-19\n",
    "df = df[df['Indicator'] == 'Distribution of COVID-19 deaths (%)']\n",
    "df.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "7f922250",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(500, 22)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Clean Data to only include coastal states(Column with East, West)\n",
    "coast_states = ['WA', 'OR', 'CA', 'ME', 'NH', 'MA', 'RI', 'CT', 'NY', 'NJ', 'PA', 'DE', 'MD', 'VA', 'NC', 'SC', 'GA', 'FL']\n",
    "df['Coast'] = df['State'].apply(lambda x: 'West' if x in ['CA', 'OR', 'WA'] else 'East')\n",
    "df = df[df['State'].isin(coast_states)]\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "8bf84e7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "170\n"
     ]
    }
   ],
   "source": [
    "#170 coastal counties where theres info for Non-Hispanic Whites, Non-Hispanic Blacks, Hispanics, Non-Hispanic Asians\n",
    "mask_white = df['Non-Hispanic White'].notna()\n",
    "mask_black = df['Non-Hispanic Black'].notna()\n",
    "mask_asian = df['Non-Hispanic Asian'].notna()\n",
    "mask_hispanic = df['Hispanic'].notna()\n",
    "combined_mask = mask_white & mask_black & mask_hispanic &mask_asian\n",
    "num_rows_non_nan = combined_mask.sum()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c43e64b",
   "metadata": {},
   "source": [
    "One of the biggest challenges we were thinking about was that a lot of counties had different or missing information for ethnicities. Some counties have information for every ethnicity, while others have missing data for one ethnicity only or for many ethnicities. Taking a look at the data, at least a significant number of counties had information for Non-Hispanic White, Black and Asians and Hispanics, which we felt was strong enough to analyze racial data trends for these counties. Thus we kept counties which had information for these categories and added one which were all ethnicities outside of these."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "a3598212",
   "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>Data as of</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>End Date</th>\n",
       "      <th>State</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Urban Rural Code</th>\n",
       "      <th>FIPS State</th>\n",
       "      <th>FIPS County</th>\n",
       "      <th>FIPS Code</th>\n",
       "      <th>Indicator</th>\n",
       "      <th>...</th>\n",
       "      <th>Non-Hispanic Black</th>\n",
       "      <th>Non-Hispanic American Indian or Alaska Native</th>\n",
       "      <th>Non-Hispanic Asian</th>\n",
       "      <th>Non-Hispanic Native Hawaiian or Other Pacific Islander</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Other</th>\n",
       "      <th>Urban Rural Description</th>\n",
       "      <th>Footnote</th>\n",
       "      <th>Coast</th>\n",
       "      <th>Non-White, Non-Black, Non-Hispanic, Non-Asian</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>CA</td>\n",
       "      <td>Alameda County</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6001</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.219</td>\n",
       "      <td>0.017</td>\n",
       "      <td>0.239</td>\n",
       "      <td>0.018</td>\n",
       "      <td>Large central metro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>West</td>\n",
       "      <td>0.039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>CA</td>\n",
       "      <td>Butte County</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>6007</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>0.015</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0.041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.119</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "      <td>West</td>\n",
       "      <td>0.035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>CA</td>\n",
       "      <td>Contra Costa County</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>6013</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>0.127</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.198</td>\n",
       "      <td>0.014</td>\n",
       "      <td>Large fringe metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "      <td>West</td>\n",
       "      <td>0.028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>CA</td>\n",
       "      <td>Fresno County</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>19</td>\n",
       "      <td>6019</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.092</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.450</td>\n",
       "      <td>0.005</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "      <td>West</td>\n",
       "      <td>0.019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>09/27/2023</td>\n",
       "      <td>01/01/2020</td>\n",
       "      <td>09/23/2023</td>\n",
       "      <td>CA</td>\n",
       "      <td>Kern County</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>29</td>\n",
       "      <td>6029</td>\n",
       "      <td>Distribution of COVID-19 deaths (%)</td>\n",
       "      <td>...</td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.012</td>\n",
       "      <td>0.040</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.505</td>\n",
       "      <td>0.009</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "      <td>West</td>\n",
       "      <td>0.024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Data as of  Start Date    End Date State          County Name  \\\n",
       "0  09/27/2023  01/01/2020  09/23/2023    CA       Alameda County   \n",
       "1  09/27/2023  01/01/2020  09/23/2023    CA         Butte County   \n",
       "2  09/27/2023  01/01/2020  09/23/2023    CA  Contra Costa County   \n",
       "3  09/27/2023  01/01/2020  09/23/2023    CA        Fresno County   \n",
       "4  09/27/2023  01/01/2020  09/23/2023    CA          Kern County   \n",
       "\n",
       "   Urban Rural Code  FIPS State  FIPS County  FIPS Code  \\\n",
       "0                 1           6            1       6001   \n",
       "1                 4           6            7       6007   \n",
       "2                 2           6           13       6013   \n",
       "3                 3           6           19       6019   \n",
       "4                 3           6           29       6029   \n",
       "\n",
       "                             Indicator  ...  Non-Hispanic Black  \\\n",
       "0  Distribution of COVID-19 deaths (%)  ...               0.191   \n",
       "1  Distribution of COVID-19 deaths (%)  ...               0.015   \n",
       "2  Distribution of COVID-19 deaths (%)  ...               0.127   \n",
       "3  Distribution of COVID-19 deaths (%)  ...               0.046   \n",
       "4  Distribution of COVID-19 deaths (%)  ...               0.058   \n",
       "\n",
       "   Non-Hispanic American Indian or Alaska Native  Non-Hispanic Asian  \\\n",
       "0                                          0.004               0.219   \n",
       "1                                          0.025               0.041   \n",
       "2                                            NaN               0.111   \n",
       "3                                          0.012               0.092   \n",
       "4                                          0.012               0.040   \n",
       "\n",
       "   Non-Hispanic Native Hawaiian or Other Pacific Islander  Hispanic  Other  \\\n",
       "0                                              0.017          0.239  0.018   \n",
       "1                                                NaN          0.119    NaN   \n",
       "2                                              0.013          0.198  0.014   \n",
       "3                                                NaN          0.450  0.005   \n",
       "4                                                NaN          0.505  0.009   \n",
       "\n",
       "   Urban Rural Description                                           Footnote  \\\n",
       "0      Large central metro                                                NaN   \n",
       "1              Small metro  One or more data cells have counts between 1-9...   \n",
       "2       Large fringe metro  One or more data cells have counts between 1-9...   \n",
       "3             Medium metro  One or more data cells have counts between 1-9...   \n",
       "4             Medium metro  One or more data cells have counts between 1-9...   \n",
       "\n",
       "   Coast Non-White, Non-Black, Non-Hispanic, Non-Asian  \n",
       "0   West                                         0.039  \n",
       "1   West                                         0.035  \n",
       "2   West                                         0.028  \n",
       "3   West                                         0.019  \n",
       "4   West                                         0.024  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#drop counties where theres missing info in any of aforementioned columns\n",
    "df = df[combined_mask]\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df['Non-White, Non-Black, Non-Hispanic, Non-Asian'] = 1 - df['Non-Hispanic White'] - df['Non-Hispanic Black'] - df['Hispanic'] - df['Non-Hispanic Asian']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "c9a8ddd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Drop unnecessary columns, Remember Data is from 01/01/2020 to 09/23/2023\n",
    "df = df.drop(['Data as of', 'Total deaths', 'Start Date', 'End Date', 'Urban Rural Code', 'FIPS State', 'FIPS Code','FIPS County', 'Indicator'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "42d7f21b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Coast</th>\n",
       "      <th>COVID-19 Deaths</th>\n",
       "      <th>Non-Hispanic White</th>\n",
       "      <th>Non-Hispanic Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Non-Hispanic Asian</th>\n",
       "      <th>Non-White, Non-Black, Non-Hispanic, Non-Asian</th>\n",
       "      <th>Non-Hispanic American Indian or Alaska Native</th>\n",
       "      <th>Non-Hispanic Native Hawaiian or Other Pacific Islander</th>\n",
       "      <th>Other</th>\n",
       "      <th>Urban Rural Description</th>\n",
       "      <th>Footnote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CA</td>\n",
       "      <td>Alameda County</td>\n",
       "      <td>West</td>\n",
       "      <td>2628</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.239</td>\n",
       "      <td>0.219</td>\n",
       "      <td>0.039</td>\n",
       "      <td>0.004</td>\n",
       "      <td>0.017</td>\n",
       "      <td>0.018</td>\n",
       "      <td>Large central metro</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA</td>\n",
       "      <td>Butte County</td>\n",
       "      <td>West</td>\n",
       "      <td>789</td>\n",
       "      <td>0.790</td>\n",
       "      <td>0.015</td>\n",
       "      <td>0.119</td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.035</td>\n",
       "      <td>0.025</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Small metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CA</td>\n",
       "      <td>Contra Costa County</td>\n",
       "      <td>West</td>\n",
       "      <td>1754</td>\n",
       "      <td>0.536</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.198</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.028</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.014</td>\n",
       "      <td>Large fringe metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>Fresno County</td>\n",
       "      <td>West</td>\n",
       "      <td>3278</td>\n",
       "      <td>0.393</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.450</td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.019</td>\n",
       "      <td>0.012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.005</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>Kern County</td>\n",
       "      <td>West</td>\n",
       "      <td>2711</td>\n",
       "      <td>0.373</td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.505</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.024</td>\n",
       "      <td>0.012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.009</td>\n",
       "      <td>Medium metro</td>\n",
       "      <td>One or more data cells have counts between 1-9...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State          County Name Coast  COVID-19 Deaths  Non-Hispanic White  \\\n",
       "0    CA       Alameda County  West             2628               0.312   \n",
       "1    CA         Butte County  West              789               0.790   \n",
       "2    CA  Contra Costa County  West             1754               0.536   \n",
       "3    CA        Fresno County  West             3278               0.393   \n",
       "4    CA          Kern County  West             2711               0.373   \n",
       "\n",
       "   Non-Hispanic Black  Hispanic  Non-Hispanic Asian  \\\n",
       "0               0.191     0.239               0.219   \n",
       "1               0.015     0.119               0.041   \n",
       "2               0.127     0.198               0.111   \n",
       "3               0.046     0.450               0.092   \n",
       "4               0.058     0.505               0.040   \n",
       "\n",
       "   Non-White, Non-Black, Non-Hispanic, Non-Asian  \\\n",
       "0                                          0.039   \n",
       "1                                          0.035   \n",
       "2                                          0.028   \n",
       "3                                          0.019   \n",
       "4                                          0.024   \n",
       "\n",
       "   Non-Hispanic American Indian or Alaska Native  \\\n",
       "0                                          0.004   \n",
       "1                                          0.025   \n",
       "2                                            NaN   \n",
       "3                                          0.012   \n",
       "4                                          0.012   \n",
       "\n",
       "   Non-Hispanic Native Hawaiian or Other Pacific Islander  Other  \\\n",
       "0                                              0.017       0.018   \n",
       "1                                                NaN         NaN   \n",
       "2                                              0.013       0.014   \n",
       "3                                                NaN       0.005   \n",
       "4                                                NaN       0.009   \n",
       "\n",
       "  Urban Rural Description                                           Footnote  \n",
       "0     Large central metro                                                NaN  \n",
       "1             Small metro  One or more data cells have counts between 1-9...  \n",
       "2      Large fringe metro  One or more data cells have counts between 1-9...  \n",
       "3            Medium metro  One or more data cells have counts between 1-9...  \n",
       "4            Medium metro  One or more data cells have counts between 1-9...  "
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Rearrange the columns to have data arranged in desirable order\n",
    "df = df[[\"State\",\"County Name\", \"Coast\",\"COVID-19 Deaths\",\"Non-Hispanic White\",\"Non-Hispanic Black\", \"Hispanic\", \"Non-Hispanic Asian\", \"Non-White, Non-Black, Non-Hispanic, Non-Asian\", \"Non-Hispanic American Indian or Alaska Native\",\"Non-Hispanic Native Hawaiian or Other Pacific Islander\",\"Other\",\"Urban Rural Description\",\"Footnote\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6877f39c",
   "metadata": {},
   "source": [
    "Because we standardized the ethnicity data above, we decide to remove all data for specific ethnicities which we are not observing and only keep the ones that we described above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "d0be3f18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>County Name</th>\n",
       "      <th>Coast</th>\n",
       "      <th>COVID-19 Deaths</th>\n",
       "      <th>Non-Hispanic White</th>\n",
       "      <th>Non-Hispanic Black</th>\n",
       "      <th>Hispanic</th>\n",
       "      <th>Non-Hispanic Asian</th>\n",
       "      <th>Non-White, Non-Black, Non-Hispanic, Non-Asian</th>\n",
       "      <th>Urban Rural Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CA</td>\n",
       "      <td>Alameda County</td>\n",
       "      <td>West</td>\n",
       "      <td>2628</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.191</td>\n",
       "      <td>0.239</td>\n",
       "      <td>0.219</td>\n",
       "      <td>0.039</td>\n",
       "      <td>Large central metro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA</td>\n",
       "      <td>Butte County</td>\n",
       "      <td>West</td>\n",
       "      <td>789</td>\n",
       "      <td>0.790</td>\n",
       "      <td>0.015</td>\n",
       "      <td>0.119</td>\n",
       "      <td>0.041</td>\n",
       "      <td>0.035</td>\n",
       "      <td>Small metro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CA</td>\n",
       "      <td>Contra Costa County</td>\n",
       "      <td>West</td>\n",
       "      <td>1754</td>\n",
       "      <td>0.536</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.198</td>\n",
       "      <td>0.111</td>\n",
       "      <td>0.028</td>\n",
       "      <td>Large fringe metro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA</td>\n",
       "      <td>Fresno County</td>\n",
       "      <td>West</td>\n",
       "      <td>3278</td>\n",
       "      <td>0.393</td>\n",
       "      <td>0.046</td>\n",
       "      <td>0.450</td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.019</td>\n",
       "      <td>Medium metro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA</td>\n",
       "      <td>Kern County</td>\n",
       "      <td>West</td>\n",
       "      <td>2711</td>\n",
       "      <td>0.373</td>\n",
       "      <td>0.058</td>\n",
       "      <td>0.505</td>\n",
       "      <td>0.040</td>\n",
       "      <td>0.024</td>\n",
       "      <td>Medium metro</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State          County Name Coast  COVID-19 Deaths  Non-Hispanic White  \\\n",
       "0    CA       Alameda County  West             2628               0.312   \n",
       "1    CA         Butte County  West              789               0.790   \n",
       "2    CA  Contra Costa County  West             1754               0.536   \n",
       "3    CA        Fresno County  West             3278               0.393   \n",
       "4    CA          Kern County  West             2711               0.373   \n",
       "\n",
       "   Non-Hispanic Black  Hispanic  Non-Hispanic Asian  \\\n",
       "0               0.191     0.239               0.219   \n",
       "1               0.015     0.119               0.041   \n",
       "2               0.127     0.198               0.111   \n",
       "3               0.046     0.450               0.092   \n",
       "4               0.058     0.505               0.040   \n",
       "\n",
       "   Non-White, Non-Black, Non-Hispanic, Non-Asian Urban Rural Description  \n",
       "0                                          0.039     Large central metro  \n",
       "1                                          0.035             Small metro  \n",
       "2                                          0.028      Large fringe metro  \n",
       "3                                          0.019            Medium metro  \n",
       "4                                          0.024            Medium metro  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.drop(['Non-Hispanic American Indian or Alaska Native','Non-Hispanic Native Hawaiian or Other Pacific Islander', 'Other', 'Footnote'], axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc6d199",
   "metadata": {},
   "source": [
    "After our data cleaning, we have only kept information from coastal counties meeting our criteria of minimum racial makeup infomartion, which was 170. Also, we have removed the unncessary columns and rearranged the order to have the dataframe in the most ideal format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "e82d2505",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(170, 10)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ethics & Privacy\n",
    "We do not believe there would be issues regarding the confidentiality of patient/personal information as no names or specific data from one particular person would be used. All results were aggregated ensuring privacy at an individual level.\n",
    "\n",
    "Moreover, our data is sourced from reputable organizations such as the Health Data organization, which adheres to strict ethical and data collection guidelines. Overall we believe our findings could prove to be beneficial to prepare for a more equitable approach towards a future pandemic. \n",
    "\n",
    "Furthermore, it is crucial to approach any correlations with caution, avoiding misrepresentation of certain groups/populations of people when dealing with such grave diseases. For example, we need to take into consideration why certain groups may be infected more, if healthcare is consistently accessible for all and if a high level of urbanization has any effect on the rate of infection. These concerns make the urbanization level of cities an imperative variable to consider to avoid biases or confounding variables. Lastly, to minimize inaccuracies within our data analysis, our group looks at the relationship between eastern and western states over a long period of time (2020-2023) rather than focusing at one point of time. Overall, our goal is to provide insights that would positively impact public health strategies. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team Expectations\n",
    "- Have weekly meetings where everyone can attend. If someone cannot attend then they will get a summary, documenting our meeting’s goals and discussions. In each meeting we will talk about how we can make improvements on our previous checkpoint if needed. Then the rest of the meeting will go over the upcoming checkpoint, assigning what task everyone should do and our goals for that week. \n",
    "- Split up work from data wrangling, code, video, and write-up about data: we expect everyone to take a part in the project that plays to their strengths. Those who are good or confident at coding will split the coding portions among themselves (data, wranging, creating graphs etc), and those who are not as confident, would take up the majority of the writing or analysis portion. If all the parts are taken, we plan on having one or two people look over the entire project or step in when help is needed in the coding or writing parts of the project. \n",
    "- Overall expectations: We expect every member in our group to contribute equally and be able to respond to our group chat in a reasonable amount of time. We also expect everyone to try to go to our group meetings and if not possible to check in with the group after to see what they missed.   \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project Timeline Proposal\n",
    "\n",
    "Week 6: For week 6 we found datasets that fit our research question. Once the best possible dataset was found for our specific topic we divided the work to conduct some wrangling and cleaning on it, such as detecting redundant columns and tidying the data. E.g. if there is age, change values to only years, weight to only pounds etc. \n",
    "Week 7: In week 7 we will start thinking about how we should present the data effectively. Deciding what types of graphs we should use and why, seeing what relationships we can find in our dataset and making sure the graphs are comprehensible to the viewer. \n",
    "Week 8: In week 8 we will start making an outline for our project of where each data, graph, explanation or code should be placed on our GitHub final project and start filling up relevant sections accordingly. \n",
    "Week 9: In week 9 we will review the whole project, fixing any errors or adding more information based on the feedback given to us by Professor Elis or a TA. \n",
    "Week 10: Finally, in week 10 we plan on making a final run down on our project, making sure we fulfill all the requirements as per the rubric. \n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}