Question 1

library(tidyverse)
library(knitr)
library(readxl)
library(zoo)


# read in covid data from URL
covid19 = read_csv('https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv')

Filter data to California and add new column of daily new cases

covid_ca = covid19 %>%
  filter(state=="California") %>%
  group_by(county) %>%
  mutate(newcases = cases - lag(cases)) %>%
  ungroup()

Table of 5 counties with most cases

top5cumulative= covid_ca %>%
  filter(date==max(date)) %>%
  slice_max(cases, n=5) %>%
  select(county, cases)

knitr::kable(top5cumulative,
             caption = "Most Cumulative Cases California Counties",
             col.names = c("County", "Cumulative Cases"))
Most Cumulative Cases California Counties
County Cumulative Cases
Los Angeles 253176
Riverside 55073
Orange 51936
San Bernardino 50543
San Diego 42477

Table of 5 counties with most new cases

top5_newcases = covid_ca %>%
  filter(date==max(date)) %>%
  slice_max(newcases, n=5) %>%
  select(county, newcases)

knitr::kable(top5_newcases,
             caption = "Most New Cases California Counties",
             col.names = c("County", "New Cases"))
Most New Cases California Counties
County New Cases
Los Angeles 1110
San Diego 445
Santa Clara 274
Orange 178
San Joaquin 165

Read in population data and join with covid data.

pop_est = read_excel("C:/Users/hopew/Desktop/github176/geog-176A-labs/data/PopulationEstimates.xls",
                     skip=2)
pop_est = pop_est %>%
  select(fips="FIPStxt", state="State", "Area_Name", pop2019="POP_ESTIMATE_2019")

pop_joined_covid = inner_join(pop_est, covid_ca, by="fips")

cases_percapita = pop_joined_covid %>%
  filter(date==max(date)-13) %>%
  mutate(most_percapita = (sum(cases))/pop2019) %>%
  mutate(new_percapita = ((sum(cases-lag(cases)))/pop2019))

Table of most cases per capita

most_cumulative_percapita = cases_percapita %>%
  slice_max(most_percapita, n=5) %>%
  select(county, most_percapita)

knitr::kable(most_cumulative_percapita,
             caption = "Most Cumulative Cases Per Capita California Counties",
             col.names = c("County", "Cumulative Cases per Capita"))
Most Cumulative Cases Per Capita California Counties
County Cumulative Cases per Capita
Alpine 625.85385
Sierra 235.13777
Modoc 79.92184
Trinity 57.51640
Mono 48.91921

Table of most new cases per hundred thousand people

last14days = pop_joined_covid %>%
  filter(date>max(date)-14) %>%
  group_by(county, pop2019) %>%
  summarise(newcases=sum(newcases)) %>%
  ungroup() %>%
  mutate(case_per100k = newcases/(pop2019/100000)) %>%
  filter(case_per100k<=100)
knitr::kable(last14days,
             caption="Counties with Most New Cases")
Counties with Most New Cases
county pop2019 newcases case_per100k
Alpine 1129 0 0.00000
Del Norte 27812 7 25.16899
El Dorado 192843 81 42.00308
Humboldt 135558 79 58.27764
Inyo 18039 16 88.69671
Lake 64386 62 96.29423
Lassen 30573 12 39.25032
Mariposa 17203 2 11.62588
Mono 14444 1 6.92329
Nevada 99755 45 45.11052
Placer 398329 354 88.87126
Plumas 18807 3 15.95151
Shasta 180080 50 27.76544
Sierra 3005 0 0.00000
Siskiyou 43539 27 62.01337
Solano 447643 432 96.50547
Tehama 65084 56 86.04265
Trinity 12285 5 40.70004
Tuolumne 54478 32 58.73931

Question 2