DataCheckpoint_Group022_WI24.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COGS 108 - Data Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Names\n",
    "\n",
    "- Marina Hu\n",
    "- Allison Huang\n",
    "- Samuel Lee\n",
    "- Audrey Liang\n",
    "- Lilian Xia "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Research Question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to figure out the following question for this project: \n",
    "\n",
    "- Are we able to predict the rise or fall of crime rates based on the rise or fall of COVID-19 rates and effects?\n",
    "\n",
    "To do so, here are some of the variables we will be looking at (These are all on a county by county basis in California):\n",
    "- Violent crime rates from around the years March 2020- March 2022\n",
    "- Hospitalization rates\n",
    "- Vaccination Rates\n",
    "- COVID cases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background and Prior Work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The COVID-19 pandemic has had numerous effects in many aspects of life, from social connections to physical and mental health. Among its less apparent interpersonal and political impacts are those on crime rates. Existing research and analyses reveal a comprehensive picture: while the pandemic has led to substantial shifts in crime rates compared to pre-pandemic times, the direction and scale of these changes vary depending on the type of crime. Our research aims to investigate these dynamics, particularly focusing on the trends in violent crime rates in correlation with various COVID-19 data in California. \n",
    "\n",
    "The Centers for Disease Control and Prevention (CDC) and FBI both reported a striking 30 percent increase in the number of homicides in the U.S. from 2019 to 2020, marking the largest single-year increase on record.<a name=\"cite_ref-2\"></a>[<sup>2</sup>](#cite_note-2) According to a study by the Council on Criminal Justice (CCJ), this surge likely stemmed from a combination of factors, including the pandemic and social unrest.<a name=\"cite_ref-3\"></a>[<sup>3</sup>](#cite_note-3) This study also highlights increases in aggravated and gun assaults and motor vehicle thefts, juxtaposed with declines in residential and non-residential burglaries, larcenies, and drug offenses. Recent data reveals that there was a significant 2% decline in violent crime in the U.S., approaching pre-pandemic rates.<a name=\"cite_ref-4\"></a>[<sup>4</sup>](#cite_note-4) However, these studies note that it is unclear if these fluctuations in crime rate can be attributed to the pandemic. In our project, we aim to analyze COVID-19 data to see if there are significant correlations that can potentially help explain and predict these trends.\n",
    "\n",
    "More specifically, we focus on analyzing data in California as findings reveal a notable widening of the gap between the state’s violent crime rates and the national average during the pandemic.<a name=\"cite_ref-1\"></a>[<sup>1</sup>](#cite_note-1) This disparity increased significantly from 15% in 2019 to 31% in 2022. Surprisingly, this surge is not correlated with the national increase in homicide rates but rather due to a rise in aggravated assaults within the state. Moreover, the Public Policy Institute of California indicates that the violent crime rates across counties varies, with 36 out of 58 witnessing a surge in 2022, 13 of which increasing by 20% or more. With this research, we’re interested in further analyzing violent crime rates in California to identify potential correlations with COVID-19. Are the shifts in crime rate truly attributed to the pandemic or merely an overlap in timing? Moreover, are these correlations consistent across the state or confined to specific counties? To answer these questions, we look at three major indicators of COVID-19: hospitalization rates, overall cases, and vaccination rates. Going a step further, we want to see if we can potentially predict the rise or fall of violent crime rates given the upward or downward trends of the pandemic.\n",
    "\n",
    "1. <a name=\"cite_note-1\"></a> Lofstrom, M., & Martin, B. (2023, October). Crime Trends in California. Public Policy Institute of California. https://www.ppic.org/publication/crime-trends-in-california/ \n",
    "2. <a name=\"cite_note-2\"></a> Northeastern University Public Affairs. (2023, January 27). How COVID-19 Changed Crime in the U.S.. https://publicaffairs.northeastern.edu/articles/us-crime-rate-during-pandemic/ \n",
    "3. <a name=\"cite_note-3\"></a> Rosenfeld, R., & Lopez Jr., E. (2021, May 21). Pandemic, Social Unrest, And Crime in U.S. Cities: March 2021 Update. Council on Criminal Justice. https://counciloncj.org/impact-report-covid-19-and-crime/ \n",
    "4. <a name=\"cite_note-4\"></a> Salter, J. (2023, October). Violent Crime Decreases to Pre-Pandemic Levels: FBI Report. TIME USA. https://time.com/6324381/violent-crime-decreases-pre-pandemic-levels-fbi/ \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hypothesis\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "We hypothesize that COVID-19's effect on the public's health will be reflected in the violent crime rates. We believe there is a relationship between counties more heavily impacted by COVID-19 and rising serious crime rates. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data overview\n",
    "\n",
    "For each dataset include the following information\n",
    "- Dataset #1\n",
    "  - Dataset Name: California Crime Rates by County\n",
    "  - Link to the dataset: https://openjustice.doj.ca.gov/data, under Crime and Clearances Monthly CSV\n",
    "  - Number of observations: 350\n",
    "  - Number of variables: 12\n",
    "- Dataset #2 \n",
    "  - Dataset Name: COVID-19 Time-Series Metrics by County and State\n",
    "  - Link to the dataset: https://catalog.data.gov/dataset/covid-19-time-series-metrics-by-county-and-state-e7757\n",
    "  - Number of observations: 86559\n",
    "  - Number of variables: 12\n",
    "- Dataset #3 \n",
    "  - Dataset Name: Statewide Covid-19 Hospital County Data\n",
    "  - Link to the dataset: https://data.ca.gov/dataset/covid-19-hospital-data/resource/e34f966b-39dc-4f86-8087-2f9b327f60f3\n",
    "  - Number of observations: 79554\n",
    "  - Number of variables: 10\n",
    "- Dataset #4\n",
    "  - Dataset Name: Covid-19 Vaccines Counts by County\n",
    "  - Link to the dataset: https://data.ca.gov/dataset/covid-19-vaccine-progress-dashboard-data/resource/317f8cd8-7225-4b7e-99d1-6ea441043a51\n",
    "  - Number of observations: 9195\n",
    "  - Number of variables: 10 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How we plan to use these Datasets: \n",
    "\n",
    "By combining this with the below COVID Datasets, we want to try and predict if crime rates will increase based on the COVID statistics. We plan to merge the COVID datasets together to form a cohesive understanding about the effect of COVID on a particular county at a given time. Then, we will be able to plot the COVID and Crime datasets together to see if there is a correlation between the effect of COVID and the given crime rate. To simplify our datasets just a little bit, we decided to use the 15 most populous counties in California, since there is likely to be more data for both crime and COVID rates. \n",
    "\n",
    "The counties we'll be working with are: \n",
    "    \n",
    "- `Los Angeles County`\n",
    "- `San Diego County`\n",
    "- `Orange County`\n",
    "- `Riverside County`\n",
    "- `San Bernardino County`\n",
    "- `Santa Clara County`\n",
    "- `Alameda County`\n",
    "- `Sacramento County`\n",
    "- `Contra Costa County`\n",
    "- `Fresno County`\n",
    "- `Kern County`\n",
    "- `Ventura County`\n",
    "- `San Joaquin County`\n",
    "- `San Francisco County`\n",
    "- `San Mateo County`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Description:\n",
    "- The dataset below describes the crime rates of various counties in California. They are grouped together based on their county, month, and year. The dataset spans from March 2020 to March 2022\n",
    "\n",
    "Important Variables:\n",
    "\n",
    " * **Month:** The month of which the data was reported for a county. Since we will be looking at this on a month to month basis, this is important.\n",
    " * **Year:** The year of which the data was reported for a county. This is important so we can appropriately group the dataset.\n",
    " * **County:** The county in California for which the crime was reported for. This is important to group each dataset properly.\n",
    "\n",
    " * **____Sum:** These are the types of crimes that were committed in this county for a specific month and year.(Types of crimes include: Violent, Homicide, ForRape, Robbery, Aggravated Assault, Property, Burglary, Vehicle, Total). These can be used to represent the crime rate per county.\n",
    "\n",
    "Data Types:\n",
    "\n",
    "- All variables except for the County Datatype, which is of type `object`, are integer types (`int32`).\n",
    "\n",
    "How we'll clean and wrangle the data:\n",
    "\n",
    "- The data has been somewhat cleaned already (as seen in the comments in the code). We edited the dataset to only include the years 2020-2022 (March 2020 - March 2022), and this dataset has been grouped and summed together by county name, month, and year to facilitate easier wrangling in the future.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Difference between the real datasets and the ideal datasets \n",
    "- The scope of the data becomes smaller \n",
    "    - Instead of looking at the impact of Covid-19 on violent crime rates across the states, given the enormous data, we narrowed our dataset down into California, and more specifically, the top 15 counties in California by population, as we may get more data & clearer relationship between Covid-19 and violent crime rates. However, our choice of selecting only those counties may prevent us from generalizing the trend/relationship to other states. \n",
    "- The frequency is not as expected \n",
    "    - We decided to only include the data from March 2020 to March 2022, but there were different degrees of missingness in different dataset. We expected daily report from all the datasets, but the real dataset didn't include all daily data from March 2020 to March 2022. The incontinuity of the data might also make the analysis more difficult. \n",
    "- Missingness of the data \n",
    "    - There are some missing data in the datasets, and we need to look at the reasons behind the missingness and further check it won't effect our analysis. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## California Crime Rate by County Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>County</th>\n",
       "      <th>Month</th>\n",
       "      <th>Year</th>\n",
       "      <th>Violent_sum</th>\n",
       "      <th>Homicide_sum</th>\n",
       "      <th>ForRape_sum</th>\n",
       "      <th>Robbery_sum</th>\n",
       "      <th>AggAssault_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alameda County</td>\n",
       "      <td>1</td>\n",
       "      <td>2021</td>\n",
       "      <td>1021</td>\n",
       "      <td>15</td>\n",
       "      <td>55</td>\n",
       "      <td>506</td>\n",
       "      <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alameda County</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>1012</td>\n",
       "      <td>9</td>\n",
       "      <td>62</td>\n",
       "      <td>503</td>\n",
       "      <td>438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alameda County</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>687</td>\n",
       "      <td>8</td>\n",
       "      <td>43</td>\n",
       "      <td>324</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alameda County</td>\n",
       "      <td>2</td>\n",
       "      <td>2022</td>\n",
       "      <td>712</td>\n",
       "      <td>10</td>\n",
       "      <td>55</td>\n",
       "      <td>335</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alameda County</td>\n",
       "      <td>3</td>\n",
       "      <td>2020</td>\n",
       "      <td>767</td>\n",
       "      <td>12</td>\n",
       "      <td>75</td>\n",
       "      <td>329</td>\n",
       "      <td>351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>367</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>10</td>\n",
       "      <td>2021</td>\n",
       "      <td>191</td>\n",
       "      <td>0</td>\n",
       "      <td>30</td>\n",
       "      <td>44</td>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>368</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>11</td>\n",
       "      <td>2020</td>\n",
       "      <td>133</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>39</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>369</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>11</td>\n",
       "      <td>2021</td>\n",
       "      <td>149</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>53</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>370</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "      <td>126</td>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>33</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>371</th>\n",
       "      <td>Ventura County</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>113</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>41</td>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>372 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             County  Month  Year  Violent_sum  Homicide_sum  ForRape_sum  \\\n",
       "0    Alameda County      1  2021         1021            15           55   \n",
       "1    Alameda County      1  2022         1012             9           62   \n",
       "2    Alameda County      2  2021          687             8           43   \n",
       "3    Alameda County      2  2022          712            10           55   \n",
       "4    Alameda County      3  2020          767            12           75   \n",
       "..              ...    ...   ...          ...           ...          ...   \n",
       "367  Ventura County     10  2021          191             0           30   \n",
       "368  Ventura County     11  2020          133             0           11   \n",
       "369  Ventura County     11  2021          149             1           15   \n",
       "370  Ventura County     12  2020          126             2           18   \n",
       "371  Ventura County     12  2021          113             1            7   \n",
       "\n",
       "     Robbery_sum  AggAssault_sum  \n",
       "0            506             445  \n",
       "1            503             438  \n",
       "2            324             312  \n",
       "3            335             312  \n",
       "4            329             351  \n",
       "..           ...             ...  \n",
       "367           44             117  \n",
       "368           39              83  \n",
       "369           53              80  \n",
       "370           33              73  \n",
       "371           41              64  \n",
       "\n",
       "[372 rows x 8 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## YOUR CODE TO LOAD/CLEAN/TIDY/WRANGLE THE DATA GOES HERE\n",
    "## FEEL FREE TO ADD MULTIPLE CELLS PER SECTION \n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.options.display.max_rows = 30\n",
    "pd.options.display.max_columns = 20\n",
    "\n",
    "counties = ['Los Angeles County' , 'San Diego County' , 'Orange County' , 'Riverside County' , 'San Bernardino County' , 'Santa Clara County', 'Alameda County', 'Sacramento County', 'Contra Costa County', 'Fresno County', 'Kern County', 'Ventura County',  'San Joaquin County', 'San Francisco County', 'San Mateo County']\n",
    "obj_cols = ['Violent_sum','Homicide_sum','ForRape_sum','Robbery_sum','AggAssault_sum','Property_sum','Burglary_sum','VehicleTheft_sum','LTtotal_sum']\n",
    "crime_df = pd.read_csv('CrimeData.csv', low_memory= False)\n",
    "\n",
    "#Replacing all empty strings with 0 to get rid of converting empty strings to ints\n",
    "crime_df[obj_cols] = crime_df[obj_cols].replace(\" \", '0').astype(int)\n",
    "\n",
    "#drop column ncic code\n",
    "crime_df = crime_df[crime_df['County'].isin(counties)].drop('NCICCode', axis= 1)\n",
    "\n",
    "#Locate just years 2020-2022 and just from March 2020 - March 2022\n",
    "crime_df = crime_df[crime_df['Year'] > 2019].iloc[2:14599,:8]\n",
    "\n",
    "#Drop Months from 2020 and 2022 that aren't used\n",
    "crime_df = crime_df.drop(crime_df[(crime_df['Month'] < 3 ) & (crime_df['Year'] == 2020)].index)\n",
    "crime_df = crime_df.drop(crime_df[(crime_df['Month'] > 3 ) & (crime_df['Year'] == 2022)].index)\n",
    "\n",
    "# Sum up from each individual city\n",
    "crime_df = crime_df.groupby(['County', 'Month', 'Year'], group_keys= True).sum().reset_index()\n",
    "\n",
    "crime_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COVID-19 Cases by County Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Description: \n",
    "    - The dataset below is from to California Department of Public Health and contains data about the number of COVID-19 cases, deaths, and positive tests across all the counties in California between 02/01/2020 to 12/19/2023. \n",
    "- Variables and their datatypes:\n",
    "    - The main varibales that we are interested in analyzing are 'cumulative_cases' (`float64` type), 'cumulative_deaths' (`float64` type), 'cumulative_total_tests' (`int64` type), 'cumulative_positive_tests' (`int64` type). 'date' and 'area' are included as well which are of `object` type, and for area, which in this dataset is synonymous with county, is limited to the top 15 most populated counties in California, and this has been consistent amongst all our dataset. 'population' (also `float64` type) is kept as a potential important and useful factor for analysis in the future. \n",
    "- Concepts:\n",
    "    - In general, these variables reveal the total most updated number of cases, deaths, total tests, and positive tests in these counties on each date of 02/01/2020 to 12/19/2023. This will help us further determine further if a county was more affected by COVID-19 than other counties, along with our other factors too for the COVID-19 part of our research question\n",
    "- Wrangling/Cleaning/Preprocessing the dataset:\n",
    "    - To begin our initial wrangling, we plan to first just pull out all the data for our variables, counties, and dates of interest (as shown below) and thus condensing the dataset down to 11415 rows × 7 columns. Because we have multiple datasets for COVID-19, we plan to do the same process for all the COVID-19 dataset, eventually combining all these into one dataframe to simplify and increase the efficiency of future analysis.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>area</th>\n",
       "      <th>month</th>\n",
       "      <th>year</th>\n",
       "      <th>population</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>total_tests</th>\n",
       "      <th>positive_tests</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>1</td>\n",
       "      <td>2021</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>18938.0</td>\n",
       "      <td>332.0</td>\n",
       "      <td>339271.0</td>\n",
       "      <td>22295.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>1</td>\n",
       "      <td>2022</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>93940.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>716974.0</td>\n",
       "      <td>119132.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>1</td>\n",
       "      <td>2023</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>4531.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>91835.0</td>\n",
       "      <td>5240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2</td>\n",
       "      <td>2020</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2</td>\n",
       "      <td>2021</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>5537.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>277654.0</td>\n",
       "      <td>7546.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>700</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>11</td>\n",
       "      <td>2023</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>720.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9008.0</td>\n",
       "      <td>741.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>701</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>12</td>\n",
       "      <td>2020</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>23120.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>199930.0</td>\n",
       "      <td>26249.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>702</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>12</td>\n",
       "      <td>2021</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>16697.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>180228.0</td>\n",
       "      <td>19435.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>703</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>12</td>\n",
       "      <td>2022</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>4171.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>42469.0</td>\n",
       "      <td>4636.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>12</td>\n",
       "      <td>2023</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>712.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6473.0</td>\n",
       "      <td>757.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>705 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        area  month  year  population    cases  deaths  total_tests  \\\n",
       "0    Alameda      1  2021   1685886.0  18938.0   332.0     339271.0   \n",
       "1    Alameda      1  2022   1685886.0  93940.0   139.0     716974.0   \n",
       "2    Alameda      1  2023   1685886.0   4531.0    40.0      91835.0   \n",
       "3    Alameda      2  2020   1685886.0     17.0     0.0         28.0   \n",
       "4    Alameda      2  2021   1685886.0   5537.0   157.0     277654.0   \n",
       "..       ...    ...   ...         ...      ...     ...          ...   \n",
       "700  Ventura     11  2023    852747.0    720.0     4.0       9008.0   \n",
       "701  Ventura     12  2020    852747.0  23120.0   171.0     199930.0   \n",
       "702  Ventura     12  2021    852747.0  16697.0    33.0     180228.0   \n",
       "703  Ventura     12  2022    852747.0   4171.0    36.0      42469.0   \n",
       "704  Ventura     12  2023    852747.0    712.0    11.0       6473.0   \n",
       "\n",
       "     positive_tests  \n",
       "0           22295.0  \n",
       "1          119132.0  \n",
       "2            5240.0  \n",
       "3               4.0  \n",
       "4            7546.0  \n",
       "..              ...  \n",
       "700           741.0  \n",
       "701         26249.0  \n",
       "702         19435.0  \n",
       "703          4636.0  \n",
       "704           757.0  \n",
       "\n",
       "[705 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## YOUR CODE TO LOAD/CLEAN/TIDY/WRANGLE THE DATA GOES HERE\n",
    "## FEEL FREE TO ADD MULTIPLE CELLS PER SECTION \n",
    "\n",
    "# counties and interested columns to analyze further into\n",
    "counties = ['Los Angeles' , 'San Diego' , 'Orange' , 'Riverside' , 'San Bernardino' , 'Santa Clara', 'Alameda', 'Sacramento', 'Contra Costa', 'Fresno', 'Kern', 'Ventura',  'San Joaquin', 'San Francisco', 'San Mateo']\n",
    "interested_cols = ['date', 'area', 'population', 'cumulative_cases', 'cumulative_deaths', 'cumulative_total_tests', 'cumulative_positive_tests']\n",
    "\n",
    "# loading/reading the initial dataframe\n",
    "covid_cases_df = pd.read_csv('COVID19_Cases_Test.csv')\n",
    "covid_cases_df = covid_cases_df[covid_cases_df['area'].isin(counties)].drop('area_type', axis = 1)\n",
    "covid_cases_df = covid_cases_df.dropna()\n",
    "covid_cases_df['date'] = pd.to_datetime(covid_cases_df['date']) \n",
    "covid_cases_df['month'] = covid_cases_df['date'].dt.month.astype(int)\n",
    "covid_cases_df['year'] = covid_cases_df['date'].dt.year.astype(int)\n",
    "covid_cases_df.insert(0, 'month', covid_cases_df.pop('month'))\n",
    "covid_cases_df.insert(0, 'year', covid_cases_df.pop('year'))\n",
    "covid_cases_df = covid_cases_df.drop(['cumulative_positive_tests', 'cumulative_cases', 'cumulative_deaths', 'cumulative_total_tests', 'date'], axis = 1)\n",
    "\n",
    "covid_cases_df = covid_cases_df.groupby(['area', 'month', 'year', 'population'], group_keys= True).sum().reset_index()\n",
    "\n",
    "covid_cases_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(705, 8)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# initial size of the dataframe\n",
    "covid_cases_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>date</th>\n",
       "      <th>area</th>\n",
       "      <th>population</th>\n",
       "      <th>cumulative_cases</th>\n",
       "      <th>cumulative_deaths</th>\n",
       "      <th>cumulative_total_tests</th>\n",
       "      <th>cumulative_positive_tests</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-03-01</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>38</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-03-02</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-03-03</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-03-04</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>84</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-03-05</td>\n",
       "      <td>Alameda</td>\n",
       "      <td>1685886.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>134</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11410</th>\n",
       "      <td>2022-03-27</td>\n",
       "      <td>Ventura</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>173005.0</td>\n",
       "      <td>1496.0</td>\n",
       "      <td>2904465</td>\n",
       "      <td>218270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11411</th>\n",
       "      <td>2022-03-28</td>\n",
       "      <td>Ventura</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>173051.0</td>\n",
       "      <td>1496.0</td>\n",
       "      <td>2908787</td>\n",
       "      <td>218326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11412</th>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>Ventura</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>173088.0</td>\n",
       "      <td>1496.0</td>\n",
       "      <td>2912638</td>\n",
       "      <td>218381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11413</th>\n",
       "      <td>2022-03-30</td>\n",
       "      <td>Ventura</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>173142.0</td>\n",
       "      <td>1498.0</td>\n",
       "      <td>2916917</td>\n",
       "      <td>218455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11414</th>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>Ventura</td>\n",
       "      <td>852747.0</td>\n",
       "      <td>173182.0</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>2919984</td>\n",
       "      <td>218503</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11415 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             date     area  population  cumulative_cases  cumulative_deaths  \\\n",
       "0      2020-03-01  Alameda   1685886.0              21.0                0.0   \n",
       "1      2020-03-02  Alameda   1685886.0              22.0                0.0   \n",
       "2      2020-03-03  Alameda   1685886.0              25.0                0.0   \n",
       "3      2020-03-04  Alameda   1685886.0              31.0                0.0   \n",
       "4      2020-03-05  Alameda   1685886.0              35.0                0.0   \n",
       "...           ...      ...         ...               ...                ...   \n",
       "11410  2022-03-27  Ventura    852747.0          173005.0             1496.0   \n",
       "11411  2022-03-28  Ventura    852747.0          173051.0             1496.0   \n",
       "11412  2022-03-29  Ventura    852747.0          173088.0             1496.0   \n",
       "11413  2022-03-30  Ventura    852747.0          173142.0             1498.0   \n",
       "11414  2022-03-31  Ventura    852747.0          173182.0             1500.0   \n",
       "\n",
       "       cumulative_total_tests  cumulative_positive_tests  \n",
       "0                          38                          4  \n",
       "1                          53                          4  \n",
       "2                          62                          4  \n",
       "3                          84                          5  \n",
       "4                         134                          5  \n",
       "...                       ...                        ...  \n",
       "11410                 2904465                     218270  \n",
       "11411                 2908787                     218326  \n",
       "11412                 2912638                     218381  \n",
       "11413                 2916917                     218455  \n",
       "11414                 2919984                     218503  \n",
       "\n",
       "[11415 rows x 7 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filters out the dataset for the interested counties and columns to analyze \n",
    "covid_cases_df = covid_cases_df[covid_cases_df['area'].isin(counties)]\n",
    "covid_cases_df = covid_cases_df[interested_cols]\n",
    "\n",
    "# filters/extract that data between 03/01/2020 to 03/31/2022, or our dates of interest\n",
    "covid_cases_df = covid_cases_df[covid_cases_df['date'].between('2020-03-01', '2022-03-31')].reset_index(drop=True)\n",
    "\n",
    "# display final filtered dataframe \n",
    "covid_cases_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                          object\n",
       "area                          object\n",
       "population                   float64\n",
       "cumulative_cases             float64\n",
       "cumulative_deaths            float64\n",
       "cumulative_total_tests         int64\n",
       "cumulative_positive_tests      int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what the datatypes are for each of the varaibles that we are interested in analyzing \n",
    "covid_cases_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11415, 7)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# size of the dataframe after filtereing for interested variables, counties, and dates\n",
    "covid_cases_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                         0\n",
       "area                         0\n",
       "population                   0\n",
       "cumulative_cases             0\n",
       "cumulative_deaths            0\n",
       "cumulative_total_tests       0\n",
       "cumulative_positive_tests    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting an overview of and checking missingness of the dataset\n",
    "covid_cases_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COVID-19 Hospitalization by County Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Description: \n",
    "    - The dataset below is from the California Department of Public Health and contains data about hostipitalization related to COVID across all the counties in California between 2020-03-27 to 2024-02-17. \n",
    "- Variables and their datatypes:\n",
    "    - **todays_date** (pd.timestamp): The date of the observation\n",
    "    - **hospitalized_covid_confirmed_patients** (float64): The number of patients hospitalized in an inpatient bed who have laboratory-confirmed COVID.\n",
    "    - **hospitalized_suspected_covid_patients** (float64): The number of patients hospitalized in an inpatient bed without a laboratory-confirmed COVID diagnosis who have signs and symptoms compatible with COVID.\n",
    "    - **hospitalized_covid_patients** (float64): The number of patients currently hospitalized in an inpatient bed who have suspected or confirmed COVID.\n",
    "    - **all_hospital_beds** (float64): The total number of beds in the facility, including all surge beds, inpatient and outpatient post-surgical beds, labor and delivery unit beds, and observation beds.\n",
    "    - **icu_covid_confirmed_patients** (float64): The number of laboratory-confirmed positive COVID patients that are in the ICU at the hospital.\n",
    "    - **icu_suspected_covid_patients** (float64): The number of symptomatic patients, with tests for COVID pending laboratory confirmation, that are in the ICU at the hospital.\n",
    "    - **icu_available_beds** (float64): The number of ICU beds available at the hospital.\n",
    "- Concepts:\n",
    "    - This dataset can give us the general condition of COVID hospitalization, which can help us to estimate the impact of COVID on medical capacity.\n",
    "- Wrangling/Cleaning/Preprocessing the dataset:\n",
    "    - We first removed \"_id\" column because it is the same as the index, and then we converted \"todays_date\" into pd.Timestamp datatype for us to select the time range we want. Then, we keeped the data in the top 15 populated counties."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>_id</th>\n",
       "      <th>county</th>\n",
       "      <th>todays_date</th>\n",
       "      <th>hospitalized_covid_confirmed_patients</th>\n",
       "      <th>hospitalized_suspected_covid_patients</th>\n",
       "      <th>hospitalized_covid_patients</th>\n",
       "      <th>all_hospital_beds</th>\n",
       "      <th>icu_covid_confirmed_patients</th>\n",
       "      <th>icu_suspected_covid_patients</th>\n",
       "      <th>icu_available_beds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Imperial</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>5.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Kern</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>16.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Contra Costa</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>23.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79549</th>\n",
       "      <td>79550</td>\n",
       "      <td>Riverside</td>\n",
       "      <td>2024-02-17</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>1271.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>137.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79550</th>\n",
       "      <td>79551</td>\n",
       "      <td>Modoc</td>\n",
       "      <td>2024-02-17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79551</th>\n",
       "      <td>79552</td>\n",
       "      <td>Sacramento</td>\n",
       "      <td>2024-02-17</td>\n",
       "      <td>67.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>1025.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79552</th>\n",
       "      <td>79553</td>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-17</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79553</th>\n",
       "      <td>79554</td>\n",
       "      <td>Tehama</td>\n",
       "      <td>2024-02-17</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79554 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         _id        county todays_date  hospitalized_covid_confirmed_patients  \\\n",
       "0          1          Kern  2020-03-27                                    0.0   \n",
       "1          2      Imperial  2020-03-29                                    5.0   \n",
       "2          3          Kern  2020-03-29                                   16.0   \n",
       "3          4  Contra Costa  2020-03-29                                   23.0   \n",
       "4          5        Nevada  2020-03-29                                   23.0   \n",
       "...      ...           ...         ...                                    ...   \n",
       "79549  79550     Riverside  2024-02-17                                   68.0   \n",
       "79550  79551         Modoc  2024-02-17                                    0.0   \n",
       "79551  79552    Sacramento  2024-02-17                                   67.0   \n",
       "79552  79553          Yuba  2024-02-17                                    5.0   \n",
       "79553  79554        Tehama  2024-02-17                                    1.0   \n",
       "\n",
       "       hospitalized_suspected_covid_patients  hospitalized_covid_patients  \\\n",
       "0                                        0.0                          NaN   \n",
       "1                                      100.0                          NaN   \n",
       "2                                       57.0                          NaN   \n",
       "3                                       62.0                          NaN   \n",
       "4                                        NaN                          NaN   \n",
       "...                                      ...                          ...   \n",
       "79549                                    0.0                         53.0   \n",
       "79550                                    0.0                          0.0   \n",
       "79551                                   58.0                         93.0   \n",
       "79552                                    0.0                          0.0   \n",
       "79553                                    0.0                          0.0   \n",
       "\n",
       "       all_hospital_beds  icu_covid_confirmed_patients  \\\n",
       "0                    NaN                           0.0   \n",
       "1                    NaN                           1.0   \n",
       "2                    NaN                           8.0   \n",
       "3                    NaN                           8.0   \n",
       "4                    NaN                           1.0   \n",
       "...                  ...                           ...   \n",
       "79549             1271.0                           6.0   \n",
       "79550                NaN                           0.0   \n",
       "79551             1025.0                           3.0   \n",
       "79552                NaN                           1.0   \n",
       "79553                NaN                           1.0   \n",
       "\n",
       "       icu_suspected_covid_patients  icu_available_beds  \n",
       "0                               0.0                 NaN  \n",
       "1                               2.0                 2.0  \n",
       "2                               8.0                39.0  \n",
       "3                              15.0                62.0  \n",
       "4                               0.0                 3.0  \n",
       "...                             ...                 ...  \n",
       "79549                           NaN               137.0  \n",
       "79550                           NaN                 0.0  \n",
       "79551                           NaN                52.0  \n",
       "79552                           NaN                 9.0  \n",
       "79553                           NaN                 3.0  \n",
       "\n",
       "[79554 rows x 10 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hospital_df = pd.read_csv('covid19_hospitalization.csv')\n",
    "hospital_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>county</th>\n",
       "      <th>todays_date</th>\n",
       "      <th>hospitalized_covid_confirmed_patients</th>\n",
       "      <th>hospitalized_suspected_covid_patients</th>\n",
       "      <th>hospitalized_covid_patients</th>\n",
       "      <th>all_hospital_beds</th>\n",
       "      <th>icu_covid_confirmed_patients</th>\n",
       "      <th>icu_suspected_covid_patients</th>\n",
       "      <th>icu_available_beds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kern</td>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kern</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>16.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Contra Costa</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>23.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>San Diego</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>121.0</td>\n",
       "      <td>211.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>205.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>141.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         county todays_date  hospitalized_covid_confirmed_patients  \\\n",
       "0          Kern  2020-03-27                                    0.0   \n",
       "1          Kern  2020-03-29                                   16.0   \n",
       "2  Contra Costa  2020-03-29                                   23.0   \n",
       "3     San Diego  2020-03-29                                  121.0   \n",
       "4   Santa Clara  2020-03-29                                  141.0   \n",
       "\n",
       "   hospitalized_suspected_covid_patients  hospitalized_covid_patients  \\\n",
       "0                                    0.0                          NaN   \n",
       "1                                   57.0                          NaN   \n",
       "2                                   62.0                          NaN   \n",
       "3                                  211.0                          NaN   \n",
       "4                                   72.0                          0.0   \n",
       "\n",
       "   all_hospital_beds  icu_covid_confirmed_patients  \\\n",
       "0                NaN                           0.0   \n",
       "1                NaN                           8.0   \n",
       "2                NaN                           8.0   \n",
       "3                NaN                          77.0   \n",
       "4                NaN                          65.0   \n",
       "\n",
       "   icu_suspected_covid_patients  icu_available_beds  \n",
       "0                           0.0                 NaN  \n",
       "1                           8.0                39.0  \n",
       "2                          15.0                62.0  \n",
       "3                          39.0               205.0  \n",
       "4                          15.0                70.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop id column\n",
    "hospital_df = hospital_df.drop('_id', axis=1)\n",
    "\n",
    "# change the date column to pd.datetime\n",
    "hospital_df[\"todays_date\"] = pd.to_datetime(hospital_df[\"todays_date\"])\n",
    "\n",
    "# select rows between march 2020 and march 2022\n",
    "hospital_df = hospital_df[hospital_df['todays_date'].between('2020-03-01', '2022-03-31')]\n",
    "\n",
    "# select rows with the following counties\n",
    "counties_2 = [county.replace(\" County\", \"\") for county in counties]\n",
    "hospital_df = hospital_df[hospital_df['county'].isin(counties_2)].reset_index(drop=True)\n",
    "\n",
    "hospital_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                                   0.000000\n",
       "todays_date                              0.000000\n",
       "hospitalized_covid_confirmed_patients    0.000000\n",
       "hospitalized_suspected_covid_patients    0.000000\n",
       "hospitalized_covid_patients              0.030375\n",
       "all_hospital_beds                        0.031284\n",
       "icu_covid_confirmed_patients             0.000000\n",
       "icu_suspected_covid_patients             0.000000\n",
       "icu_available_beds                       0.000091\n",
       "dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Proportion of missingness in each column\n",
    "hospital_df.isnull().sum() / len(hospital_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## COVID-19 Vaccines by County"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Description of the dataset\n",
    "    - This dataset includes the vaccination counts in each county in California from 2020 to 2024. The vaccination counts are further broken down into partially vaccinated and fully vaccinated, and at least one dose. Except for the \"county\" and \"administered_date\" variables, which are \"objects\", all other variables are integers.\n",
    "- Relevant to the research question\n",
    "    - The vaccination rates provide insight into the Covid-19 situation in each county, which may be correlated to the Covid-19 cases and further related to the violent crime rates. The vaccination rate could be a counfounder or a mediator, or has nothing to do with the relationship, and this need to be further analyzed. \n",
    "- Data cleaning & wrangling\n",
    "    - Visualize the change in vaccination rate (at least one dose vs. partially vaccinated vs. fully vaccinated) in different county & compare the change across the counties. Also, analyze the vaccination rates by calculating the proportion in relation to the county's total population. Visualize the change in proportions. Planning to merge this dataset with other Covid-19 related datasets including total cases and hospitalization. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>county</th>\n",
       "      <th>administered_date</th>\n",
       "      <th>total_doses</th>\n",
       "      <th>cumulative_total_doses</th>\n",
       "      <th>partially_vaccinated</th>\n",
       "      <th>total_partially_vaccinated</th>\n",
       "      <th>fully_vaccinated</th>\n",
       "      <th>cumulative_fully_vaccinated</th>\n",
       "      <th>at_least_one_dose</th>\n",
       "      <th>cumulative_at_least_one_dose</th>\n",
       "      <th>california_flag</th>\n",
       "      <th>up_to_date_count</th>\n",
       "      <th>cumulative_up_to_date_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-29</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-30</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81029</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-18</td>\n",
       "      <td>1</td>\n",
       "      <td>115831</td>\n",
       "      <td>0</td>\n",
       "      <td>4159</td>\n",
       "      <td>0</td>\n",
       "      <td>40560</td>\n",
       "      <td>0</td>\n",
       "      <td>44725</td>\n",
       "      <td>California</td>\n",
       "      <td>1</td>\n",
       "      <td>4707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81030</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-19</td>\n",
       "      <td>1</td>\n",
       "      <td>115832</td>\n",
       "      <td>0</td>\n",
       "      <td>4159</td>\n",
       "      <td>0</td>\n",
       "      <td>40560</td>\n",
       "      <td>0</td>\n",
       "      <td>44725</td>\n",
       "      <td>California</td>\n",
       "      <td>1</td>\n",
       "      <td>4708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81031</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-20</td>\n",
       "      <td>4</td>\n",
       "      <td>115836</td>\n",
       "      <td>0</td>\n",
       "      <td>4159</td>\n",
       "      <td>0</td>\n",
       "      <td>40560</td>\n",
       "      <td>0</td>\n",
       "      <td>44725</td>\n",
       "      <td>California</td>\n",
       "      <td>4</td>\n",
       "      <td>4712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81032</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-21</td>\n",
       "      <td>10</td>\n",
       "      <td>115846</td>\n",
       "      <td>0</td>\n",
       "      <td>4159</td>\n",
       "      <td>0</td>\n",
       "      <td>40560</td>\n",
       "      <td>0</td>\n",
       "      <td>44725</td>\n",
       "      <td>California</td>\n",
       "      <td>10</td>\n",
       "      <td>4722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81033</th>\n",
       "      <td>Yuba</td>\n",
       "      <td>2024-02-22</td>\n",
       "      <td>7</td>\n",
       "      <td>115853</td>\n",
       "      <td>0</td>\n",
       "      <td>4159</td>\n",
       "      <td>0</td>\n",
       "      <td>40560</td>\n",
       "      <td>0</td>\n",
       "      <td>44725</td>\n",
       "      <td>California</td>\n",
       "      <td>7</td>\n",
       "      <td>4729</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81034 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        county administered_date  total_doses  cumulative_total_doses  \\\n",
       "0      Alameda        2020-01-05            0                       0   \n",
       "1      Alameda        2020-07-27            0                       0   \n",
       "2      Alameda        2020-07-29            0                       1   \n",
       "3      Alameda        2020-07-30            0                       1   \n",
       "4      Alameda        2020-07-31            0                       1   \n",
       "...        ...               ...          ...                     ...   \n",
       "81029     Yuba        2024-02-18            1                  115831   \n",
       "81030     Yuba        2024-02-19            1                  115832   \n",
       "81031     Yuba        2024-02-20            4                  115836   \n",
       "81032     Yuba        2024-02-21           10                  115846   \n",
       "81033     Yuba        2024-02-22            7                  115853   \n",
       "\n",
       "       partially_vaccinated  total_partially_vaccinated  fully_vaccinated  \\\n",
       "0                         0                           0                 0   \n",
       "1                         0                           0                 0   \n",
       "2                         0                           1                 0   \n",
       "3                         0                           1                 0   \n",
       "4                         0                           1                 0   \n",
       "...                     ...                         ...               ...   \n",
       "81029                     0                        4159                 0   \n",
       "81030                     0                        4159                 0   \n",
       "81031                     0                        4159                 0   \n",
       "81032                     0                        4159                 0   \n",
       "81033                     0                        4159                 0   \n",
       "\n",
       "       cumulative_fully_vaccinated  at_least_one_dose  \\\n",
       "0                                0                  0   \n",
       "1                                0                  0   \n",
       "2                                0                  0   \n",
       "3                                0                  0   \n",
       "4                                0                  0   \n",
       "...                            ...                ...   \n",
       "81029                        40560                  0   \n",
       "81030                        40560                  0   \n",
       "81031                        40560                  0   \n",
       "81032                        40560                  0   \n",
       "81033                        40560                  0   \n",
       "\n",
       "       cumulative_at_least_one_dose california_flag  up_to_date_count  \\\n",
       "0                                 0             NaN                 0   \n",
       "1                                 0             NaN                 0   \n",
       "2                                 1             NaN                 0   \n",
       "3                                 1             NaN                 0   \n",
       "4                                 1             NaN                 0   \n",
       "...                             ...             ...               ...   \n",
       "81029                         44725      California                 1   \n",
       "81030                         44725      California                 1   \n",
       "81031                         44725      California                 4   \n",
       "81032                         44725      California                10   \n",
       "81033                         44725      California                 7   \n",
       "\n",
       "       cumulative_up_to_date_count  \n",
       "0                                0  \n",
       "1                                0  \n",
       "2                                0  \n",
       "3                                0  \n",
       "4                                0  \n",
       "...                            ...  \n",
       "81029                         4707  \n",
       "81030                         4708  \n",
       "81031                         4712  \n",
       "81032                         4722  \n",
       "81033                         4729  \n",
       "\n",
       "[81034 rows x 13 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vaccine_df = pd.read_csv('covid19vaccinesbycounty.csv')\n",
    "vaccine_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# top 15 counties in CA by population\n",
    "counties = ['Los Angeles' , 'San Diego' , 'Orange' , 'Riverside' , 'San Bernardino' , 'Santa Clara', 'Alameda', 'Sacramento', 'Contra Costa', 'Fresno', 'Kern', 'Ventura',  'San Joaquin', 'San Francisco', 'San Mateo']\n",
    "\n",
    "# inclule only the 15 counties in df\n",
    "vaccine_df = vaccine_df[vaccine_df['county'].isin(counties)]\n",
    "vaccine_df.reset_index(inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# include the administered_date from March 2020 - March 2022\n",
    "vaccine_df = vaccine_df[vaccine_df['administered_date'].between('2020-03-01', '2022-03-31')]\n",
    "vaccine_df.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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>county</th>\n",
       "      <th>administered_date</th>\n",
       "      <th>total_doses</th>\n",
       "      <th>cumulative_total_doses</th>\n",
       "      <th>partially_vaccinated</th>\n",
       "      <th>total_partially_vaccinated</th>\n",
       "      <th>fully_vaccinated</th>\n",
       "      <th>cumulative_fully_vaccinated</th>\n",
       "      <th>at_least_one_dose</th>\n",
       "      <th>cumulative_at_least_one_dose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-27</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-29</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-30</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-07-31</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alameda</td>\n",
       "      <td>2020-08-02</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9190</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>2022-03-27</td>\n",
       "      <td>143</td>\n",
       "      <td>1563903</td>\n",
       "      <td>14</td>\n",
       "      <td>43924</td>\n",
       "      <td>22</td>\n",
       "      <td>611556</td>\n",
       "      <td>14</td>\n",
       "      <td>655502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9191</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>2022-03-28</td>\n",
       "      <td>610</td>\n",
       "      <td>1564513</td>\n",
       "      <td>82</td>\n",
       "      <td>43865</td>\n",
       "      <td>144</td>\n",
       "      <td>611700</td>\n",
       "      <td>85</td>\n",
       "      <td>655587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9192</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>801</td>\n",
       "      <td>1565314</td>\n",
       "      <td>99</td>\n",
       "      <td>43863</td>\n",
       "      <td>104</td>\n",
       "      <td>611804</td>\n",
       "      <td>102</td>\n",
       "      <td>655689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9193</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>2022-03-30</td>\n",
       "      <td>1711</td>\n",
       "      <td>1567025</td>\n",
       "      <td>78</td>\n",
       "      <td>43790</td>\n",
       "      <td>161</td>\n",
       "      <td>611965</td>\n",
       "      <td>88</td>\n",
       "      <td>655777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9194</th>\n",
       "      <td>Ventura</td>\n",
       "      <td>2022-03-31</td>\n",
       "      <td>2292</td>\n",
       "      <td>1569317</td>\n",
       "      <td>93</td>\n",
       "      <td>43745</td>\n",
       "      <td>147</td>\n",
       "      <td>612112</td>\n",
       "      <td>102</td>\n",
       "      <td>655879</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9195 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       county administered_date  total_doses  cumulative_total_doses  \\\n",
       "0     Alameda        2020-07-27            0                       0   \n",
       "1     Alameda        2020-07-29            0                       1   \n",
       "2     Alameda        2020-07-30            0                       1   \n",
       "3     Alameda        2020-07-31            0                       1   \n",
       "4     Alameda        2020-08-02            0                       2   \n",
       "...       ...               ...          ...                     ...   \n",
       "9190  Ventura        2022-03-27          143                 1563903   \n",
       "9191  Ventura        2022-03-28          610                 1564513   \n",
       "9192  Ventura        2022-03-29          801                 1565314   \n",
       "9193  Ventura        2022-03-30         1711                 1567025   \n",
       "9194  Ventura        2022-03-31         2292                 1569317   \n",
       "\n",
       "      partially_vaccinated  total_partially_vaccinated  fully_vaccinated  \\\n",
       "0                        0                           0                 0   \n",
       "1                        0                           1                 0   \n",
       "2                        0                           1                 0   \n",
       "3                        0                           1                 0   \n",
       "4                        0                           2                 0   \n",
       "...                    ...                         ...               ...   \n",
       "9190                    14                       43924                22   \n",
       "9191                    82                       43865               144   \n",
       "9192                    99                       43863               104   \n",
       "9193                    78                       43790               161   \n",
       "9194                    93                       43745               147   \n",
       "\n",
       "      cumulative_fully_vaccinated  at_least_one_dose  \\\n",
       "0                               0                  0   \n",
       "1                               0                  0   \n",
       "2                               0                  0   \n",
       "3                               0                  0   \n",
       "4                               0                  0   \n",
       "...                           ...                ...   \n",
       "9190                       611556                 14   \n",
       "9191                       611700                 85   \n",
       "9192                       611804                102   \n",
       "9193                       611965                 88   \n",
       "9194                       612112                102   \n",
       "\n",
       "      cumulative_at_least_one_dose  \n",
       "0                                0  \n",
       "1                                1  \n",
       "2                                1  \n",
       "3                                1  \n",
       "4                                2  \n",
       "...                            ...  \n",
       "9190                        655502  \n",
       "9191                        655587  \n",
       "9192                        655689  \n",
       "9193                        655777  \n",
       "9194                        655879  \n",
       "\n",
       "[9195 rows x 10 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the unwanted columns \n",
    "vaccine_df.drop(columns=['level_0', 'index', 'california_flag', 'up_to_date_count', 'cumulative_up_to_date_count'], inplace=True)\n",
    "vaccine_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                          0\n",
       "administered_date               0\n",
       "total_doses                     0\n",
       "cumulative_total_doses          0\n",
       "partially_vaccinated            0\n",
       "total_partially_vaccinated      0\n",
       "fully_vaccinated                0\n",
       "cumulative_fully_vaccinated     0\n",
       "at_least_one_dose               0\n",
       "cumulative_at_least_one_dose    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking missingness of the dataset\n",
    "vaccine_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "county                          object\n",
       "administered_date               object\n",
       "total_doses                      int64\n",
       "cumulative_total_doses           int64\n",
       "partially_vaccinated             int64\n",
       "total_partially_vaccinated       int64\n",
       "fully_vaccinated                 int64\n",
       "cumulative_fully_vaccinated      int64\n",
       "at_least_one_dose                int64\n",
       "cumulative_at_least_one_dose     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vaccine_df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ethics & Privacy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In regards to potential ethical concerns of this project, there may be various biases depending on the specific dataset used. For crime data, it is likely that datasets are not reflective of all the crimes committed in a specific county but rather only those reported by the police. When conducting our analyses, we need to be aware of this potential collection bias as certain crimes may be underreported or overreported due to factors like law enforcement, community awareness campaigns, recent events, etc. Additionally, we need to consider many potential confounding variables in our analysis; for example, crime rates can vary based on geographical and demographic factors like population density, socioeconomic status, and cultural influences. Any correlations or associations found from our analyses may be due to a range of factors that may not be directly identified through our datasets. Similarly, for COVID-19 data, it is likely that datasets are subject to testing and reporting biases that are influenced by the availability and accessibility to testing resources and healthcare facilities in certain regions and populations. Differences in case definitions, data collection methods, and reporting protocols may create variations in the data cross regions. Confounding variables like asymptomatic cases, pre-existing health conditions, and public health interventions can also skew the measures of the “impact” of COVID-19. Keeping these considerations in mind, we can closely review how data was collected to see if certain groups were potentially overrepresented or underrepresented in the dataset. We can also look for any inconsistencies, missing values, or data entry errors that potentially affect the integrity of the dataset and consider whether this is due to any systematic biases in data collection practices. Statistical techniques can also be used to detect patterns, outliers, and anomalies in the data and help control for confounding variables. Lastly, we can compare and cross-reference multiple datasets from different sources to validate our findings and identify potential biases.\n",
    "\n",
    "In general, in regards to privacy, no Personal Identifiable Information is being used because the data collected are more generalized by region. We should also keep in consideration that many crime and COVID-19 datasets are collected and maintained by law enforcement agencies or the government and therefore may be subject to terms of use agreements, restricting how data can be accessed or shared. Another general issue with this topic is data transparency as we want to ensure that findings in this research study avoid selective reporting or manipulation of data to fit a particular narrative. To address this, we should work to address any potential blindspots and work to mitigate any dataset bias such as stereotype perpetuation and confirmation bias. Another issue to consider is fairness and equity as a potential concern is that analyzing crime data in correlation with COVID-19 impact may reveal disparities in the distribution of resources, access to healthcare, and exposure to risk factors across marginalized populations. In our analysis, we should consider potential unintended consequences and prevent data from being misinterpreted or used to justify any discriminatory practices or policies."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team Expectations "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "* *Team Expectation 1: Communication*\n",
    "    - Please make sure to tell the group if you can't finish your work by the deadline, or if any problems come up. If you can't make a meeting, be sure to let everybody know in the group discord. \n",
    "* *Team Expectation 2: Work Style*\n",
    "    - The team will have designated work during the meetings before the due dates and will try to follow the project timeline as best as possible to ensure that we are all caught up on work. \n",
    "* *Team Expecation 3: Conflict Resolution*\n",
    "    - If any problems arise, the team will either bring it up at the weekly Wednesday meeting or bring it up in the general discord channel. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project Timeline Proposal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Meeting Date  | Meeting Time| Completed Before Meeting  | Discuss at Meeting |\n",
    "|---|---|---|---|\n",
    "| 2/7  |  5 PM | Read & Think about COGS 108 expectations; brainstorm topics/questions  | Determine best form of communication; Discuss and decide on final project topic; discuss hypothesis; begin background research | \n",
    "| 2/14  |  5 PM |  Find Datasets  | Discuss datasets, start cleaning and wrangling | \n",
    "| 2/21  | 5 PM  | Continue cleaning and wrangling, revise background and hypothesis  | Discuss Wrangling and possible analytical approaches; Assign group members to lead each specific part   |\n",
    "| 2/28  | 5 PM  | Import & Wrangle Data; Start EDA | Combine relevant datasets; Review/Edit wrangling/EDA; Discuss Analysis Plan   |\n",
    "| 3/6  | 5 PM  | Finalize wrangling/EDA; Begin Analysis | Discuss/edit Analysis; Complete project check-in, Final Video  |\n",
    "| 3/13  | 5 PM  | Complete analysis; Draft results/conclusion/discussion| Discuss/edit full project, Final video |\n",
    "| 3/20  | Before 11:59 PM  | Complete all individual and collective parts of the project | Turn in Final Project & Group Project Surveys |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": []
  }
 ],
 "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}