CovidDashboard.Rmd

---
title: "Covid Dashboard"
output:
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: fill
  html_document: default
---

```{r setup, include=FALSE}
library(flexdashboard)
library(tidyverse)
library(ggplot2)
library(dplyr)
library(zoo)
library(hrbrthemes)
library(plotly)
library(emojifont)
library(choroplethr)
library(rvest)
library(geofacet)
library(runner)
library(RSocrata)
library(lubridate)


# extrafont::loadfonts(quiet=TRUE)

si_number = function(x, digits) {

    compress = function(x, n) {
        signif(x * 10^(-n), digits)
    }

    case_when(
        x >= 1e6   ~ paste0(compress(x, 6), "M"),
        x >= 1000  ~ paste0(compress(x, 3), "k"),
        x >= 1     ~ as.character(compress(x, 0)),
        x >= 0.001 ~ paste0(compress(x, -3), "m"),
        x >= 1e-6  ~ paste0(compress(x, -6), "u")
    )
}

community_risk_level = function(new_cases,new_admissions,covid_bed_utilization) {
  if ( is.na(new_cases) || is.na(new_admissions) || is.na(covid_bed_utilization)) {
    return(NA)
  } 
  if (new_cases < 200 ) { # && !is.na(new_cases) && !is.na(new_admissions) && !is.na(covid_bed_utilization)) {
    if (new_admissions >= 20 || covid_bed_utilization >= 0.15) {
      "High"
    } else if (new_admissions >= 10 || covid_bed_utilization >= 0.1 ) {
      "Medium"
    } else {
      "Low"
    }
  } else if (new_cases >= 200 ) { #&& !is.na(new_cases) && !is.na(new_admissions) && !is.na(covid_bed_utilization)) { 
    if (new_admissions >= 10 || covid_bed_utilization >= 0.1) {
      return("High")
    } else if (new_admissions < 10 || covid_bed_utilization < 0.1 ) {
       return("Medium")
    }
  } 
}

```

```{r data preparation, include=FALSE}
fips = read_csv("https://www2.census.gov/geo/docs/reference/codes/files/national_county.txt",
                col_names = c("state","state_code","county_code","county","level")
                ) %>% 
  mutate(fips_code = str_c(state_code,county_code))

# 2022-05-13 NY Times breaks the county data sets into smaller pieces

us_counties_2020 = read.csv("https://github.com/nytimes/covid-19-data/blob/master/us-counties-2020.csv?raw=true")
us_counties_2021 = read.csv("https://github.com/nytimes/covid-19-data/blob/master/us-counties-2021.csv?raw=true")
us_counties_2022 = read.csv("https://github.com/nytimes/covid-19-data/blob/master/us-counties-2022.csv?raw=true")
us_counties_2023 = read.csv("https://github.com/nytimes/covid-19-data/blob/master/us-counties-2023.csv?raw=true")

nytimes= bind_rows(list(us_counties_2020,us_counties_2021,us_counties_2022,us_counties_2023)) %>% tibble()


nytimes = nytimes %>% mutate(
  fips = as.character(fips),
  date = as.Date(date)
) %>% filter (county != 'Unknown')

king_county = nytimes  %>% filter(state =='Washington' & county == "King")
county_population = read.csv("https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/counties/totals/co-est2019-alldata.csv")
vaccinations <- read_csv("https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/vaccinations/us_state_vaccinations.csv") %>%
  mutate(location = replace(location,location == 'New York State', 'New York'))
state_population <- county_population %>%
  group_by(STNAME) %>%
  summarise(population=sum(POPESTIMATE2019))

url <- "https://ballotpedia.org/List_of_governors_of_the_American_states"
df <- url %>%
  read_html() %>%
  html_elements("table") %>%
  html_table(fill = T)

governors <- df[2][[1]] %>%
  mutate(Office = str_remove(Office,"Governor of "),State = str_remove(Office,"the ")) %>%
  select(State, Name, Party) %>%
  tibble()

## Hospitalization Data by Hospital
hospitalization_by_hospital = read.socrata("https://healthdata.gov/resource/anag-cw7u.csv?state=WA") %>%
    tibble() %>%
    mutate(collection_week = as_date(collection_week),
         fips_code = as.character(fips_code)) %>%
    mutate(inpatient_beds_used_7_day_avg = na_if(inpatient_beds_used_7_day_avg,-999999.0),
           inpatient_beds_7_day_avg = na_if(inpatient_beds_7_day_avg,-999999.0),
           inpatient_beds_used_covid_7_day_avg = na_if(inpatient_beds_used_covid_7_day_avg,-999999.0),
           total_staffed_adult_icu_beds_7_day_avg = na_if(total_staffed_adult_icu_beds_7_day_avg,-999999.0),
           icu_beds_used_7_day_avg = na_if(icu_beds_used_7_day_avg,-999999.0),
           previous_day_admission_adult_covid_confirmed_7_day_sum = na_if(previous_day_admission_adult_covid_confirmed_7_day_sum,-999999.0)
           ) %>%  
    select( hospital_name,
        ccn,
        hospital_subtype,
        fips_code,
        is_metro_micro,
        hhs_ids,
        state,
        geocoded_hospital_address,
        collection_week,
        total_beds_7_day_avg,
        inpatient_beds_7_day_avg,
        inpatient_beds_used_7_day_avg,
        inpatient_beds_used_covid_7_day_avg,
        total_icu_beds_7_day_avg,
        total_staffed_adult_icu_beds_7_day_avg,
        icu_beds_used_7_day_avg,
        previous_day_admission_adult_covid_confirmed_7_day_sum) %>%
  left_join(fips)
```

# King County

```{r King County Data Computation, message=FALSE, warning=FALSE}



data <- tibble(date = tail(king_county$date,-1),
                   fips_code = tail(king_county$fips,-1),
                   new_cases = diff(king_county$cases), 
                   new_deaths = diff(king_county$deaths))
## missing very very first day - this can be resolved with runner


risk_data <- tibble(date = data$date,
              new_cases = data$new_cases,
              fips_code = data$fips_code,
              last_14_day_cases = NULL,
              last_14_day_cases_per_100k_pop = NULL
         )

cases_per_100K_14d = sum(tail(data$new_cases,14))/subset(county_population, STNAME == 'Washington' & CTYNAME == "King County" ,select=POPESTIMATE2019)*100000

cases_per_100K_7d = sum(tail(data$new_cases,7))/subset(county_population, STNAME == 'Washington' & CTYNAME == "King County" ,select=POPESTIMATE2019)*100000


for (i in 1:nrow(risk_data)) {
  startdate = risk_data[i,'date']$date
  enddate = risk_data[i,'date']$date -14
  risk_data$last_14_day_cases[i] <- risk_data %>%
                                subset(date <= startdate & date >= enddate) %>%
                                select(new_cases) %>%
                                sum(na.rm=TRUE)
  risk_data$last_14_day_cases_per_100k_pop[i] <- risk_data$last_14_day_cases[i] / 2252782 * 100000
  if(risk_data$last_14_day_cases_per_100k_pop[i] >= 75) {
    risk_data$risk[i] = "High"
  } else if (cases_per_100K_14d >= 25 ){
    risk_data$risk[i] = "Moderate"
  } else {
    risk_data$risk[i] = "Low"
  }
}

for (i in 1:nrow(risk_data)) {
  startdate = risk_data[i,'date']$date
  enddate = risk_data[i,'date']$date -7 
  risk_data$last_7_day_cases[i] <- risk_data %>%
                                subset(date <= startdate & date >= enddate) %>%
                                select(new_cases) %>%
                                sum(na.rm=TRUE)
  risk_data$last_7_day_cases_per_100k_pop[i] <- risk_data$last_7_day_cases[i] / 2252782 * 100000
  if(risk_data$last_7_day_cases_per_100k_pop[i] >= 75) {
    risk_data$risk[i] = "High"
  } else if (cases_per_100K_7d >= 25 ){
    risk_data$risk[i] = "Moderate"
  } else {
    risk_data$risk[i] = "Low"
  }
}

king_county_tibble <- king_county %>%
  tibble() %>%
  mutate(
    date = lubridate::ymd(date),
    fips_code = as.character(fips),
    new_cases = cases - lag(cases,default=0),
    new_deaths = deaths -lag(deaths,default=0)
  )


if(cases_per_100K_14d >= 75) {
  risk = "High"
} else if (cases_per_100K_14d >= 25 ){
  risk = "Moderate"
} else {
  risk = "Low"
}

if(cases_per_100K_7d >= 100) {
  risk = "High"
} else if (cases_per_100K_7d >= 50 ){
  risk = "Substantial"
}else if (cases_per_100K_7d >= 10 ){
  risk = "Moderate"
} else {
  risk = "Low"
}

hospitalizations_king_county = hospitalization_by_hospital %>%
  filter(fips_code == '53033') %>%
  group_by(county,fips_code,collection_week) %>%
  summarize(
      inpatient_beds = sum(inpatient_beds_7_day_avg, na.rm = TRUE), # Removing NA reports certain counties as zero were NA would be more useful
      inpatient_beds_used = sum(inpatient_beds_used_7_day_avg, na.rm = TRUE), # but what aboutthe counties where they are mixed?
      inpatient_beds_used_covid = sum(inpatient_beds_used_covid_7_day_avg, na.rm = TRUE),
      utilization_inpatient = inpatient_beds_used / inpatient_beds,
      utilization_inpatient_covid = inpatient_beds_used_covid / inpatient_beds,
      new_admissions = sum(previous_day_admission_adult_covid_confirmed_7_day_sum, na.rm = TRUE)
  ) %>% 
  ungroup()

# This is interesting, but maybe not useful
king_county_tibble = king_county_tibble %>%
  left_join(hospitalizations_king_county %>% select(fips_code,collection_week,utilization_inpatient,utilization_inpatient_covid),
            c("fips_code"="fips_code","date"= "collection_week"))


risk_data = risk_data %>% 
    left_join(hospitalizations_king_county %>% select(fips_code,collection_week,utilization_inpatient,utilization_inpatient_covid,new_admissions),
            c("fips_code"="fips_code","date"= "collection_week")) %>%
  mutate(population = county_population %>% filter(STNAME == 'Washington' & CTYNAME == "King County") %>% pull(POPESTIMATE2019),
         new_admissions_per_100k_7d = (new_admissions / population) * 100000,
         community_risk = pmap(list(last_7_day_cases_per_100k_pop,new_admissions_per_100k_7d,utilization_inpatient_covid),community_risk_level)
  )



```

## KPI {data-width="350"}

### Community Risk Level {data-height="80px"}

<p style="text-align:center;font-size:x-large">
`r risk_data %>% filter(!is.na(community_risk)) %>% arrange(desc(date)) %>% head(1) %>% pull(community_risk)`
</p>

<p style="font-size:x-small">
Covid Community Risk Levels based on new CDC methodology, including new hospitalizations and COVID bed utilization at community hospitals.
</p>


### Transmission Levels 

<p style="text-align:center;font-size:x-large">

`r risk`

</p>

```{r guage, echo=FALSE, message = FALSE, warning=FALSE, fig.height=2, fig.align='left'}
value <- cases_per_100K_7d[,1]
max_range <-  case_when(
  value < 100 ~ 100,
  value <= 250 ~ 250,
  value <= 500 ~ 500,
  value <=1000 ~ 1000,
  TRUE ~ 250*(ceiling(value/250)+1)
)

fig <- plot_ly(
  domain = list(x = c(0, 1), y = c(0, 1)),
  value = value,
  title = list(text = "Seven Day Community Risk<br>Cases per<br>100k Population"),
  type = "indicator",
  mode = "gauge+number+delta",
  delta = list(
                reference= head(tail(risk_data,2),1)$last_7_day_cases_per_100k_pop,
                increasing =list(color='#FF4136'),
                decreasing =list(color='#3D9970')
            ),
  gauge = list(
    bar = list(
      color = "gray"
    ),
    axis = list(
      range = c(0,max_range),
      dtick = max_range/10
    ),
    steps = list(
      list(range = c(0,25),color = "lightgreen"),
      list(range = c(25,75), color = "lightyellow"),
      list(range = c(75,max_range), color = "red")
    )
  ))
fig <- fig %>%
  layout(margin = list(l=20,r=30))

fig

```

### Yesterday New Cases

```{r valuebox, echo=FALSE, fig.align='left', fig.height=2, message=FALSE, warning=FALSE, include=FALSE}
yesterday_new_cases <- tail(data$new_cases,1)
yesterday_deaths <-  tail(data$new_deaths,1)

df <- data.frame(
  x = c(1,8.5),
  y = 2,
  h = 4.25,
  w = 6.25,
  value = c(yesterday_new_cases,yesterday_deaths),
  info = c("new cases","deaths"),
  icon = c(emoji("nauseated_face"),emoji("coffin")),
  font_family = c("EmojiOne",
                  "EmojiOne"),
  color = factor(1:2)
)

ggplot(df, aes(x, y, height = h, width = w, label = info)) +
  ## Create the tiles using the `color` column
  geom_tile(aes(fill = color)) +
  ## Add the numeric values as text in `value` column
  geom_text(color = "white", fontface = "bold", size = 10,
            aes(label = value, x = x - 2.9, y = y + 1), hjust = 0) +
  ## Add the labels for each box stored in the `info` column
  geom_text(color = "white", fontface = "bold",
            aes(label = info, x = x - 2.9, y = y - 1), hjust = 0) +
  coord_fixed() +
  scale_fill_brewer(type = "qual",palette = "Dark2") +
  ## Use `geom_text()` to add the icons by specifying the unicode symbol.
  geom_text(size = 20, aes(label = icon, family = font_family,
                           x = x + 1.5, y = y + 0.5), alpha = 0.25) +
  theme_void() +
  guides(fill = FALSE)

```

```{r}
yesterday_new_cases %>%
  valueBox(caption='Yesterday New Cases',icon="fa-frown")
```

### 7 Day Average New Cases

```{r 7 day average new cases}
king_county_tibble %>% 
  mutate(rolling_7_day_deaths = mean_run(new_deaths,k=7), 
         rolling_7_day_cases = mean_run(new_cases,k=7),
         rolling_14_day_cases = mean_run(new_cases,k=14)) %>% 
  filter(date == max(date)) %>%
  pull(rolling_7_day_cases) %>%
  scales::comma() %>%
  valueBox(caption='New Cases (7 day average)',icon='fa-frown')
```

### Yesterday Deaths

```{r}
yesterday_deaths %>%
  valueBox(caption='Yesterday New Deaths',icon='fa-skull')
```

### 7 Day Average Deaths

```{r 7 day average deaths}
king_county_tibble %>% 
  mutate(rolling_7_day_deaths = mean_run(new_deaths,k=7), 
         rolling_7_day_cases = mean_run(new_cases,k=7),
         rolling_14_day_cases = mean_run(new_cases,k=14)) %>% 
  filter(date == max(date)) %>%
  pull(rolling_7_day_deaths) %>%
  scales::comma() %>%
  valueBox(caption='Deaths (7 day average)',icon='fa-skull')
```



### Last Updated {data-height="24px"}

Last Updated `r Sys.time()`

[Data Notes](#data-sources)

## Charts {data-width="650"}

### New Cases

```{r 14_day_data, include = FALSE}


p <- ggplot(data, aes(x=date, y=new_cases)) +
  # geom_line() +
  geom_bar(stat = "identity") +
  xlab("")+ylab("New Cases")
p+scale_x_date(date_labels = "%b-%y",date_breaks = "2 month")

x<-zoo(data$new_cases)

rolling_average = data.frame(date = as.Date(tail(king_county$date,-14)), rolling_new_cases_14d = rollmean(x,14))
rolling_average_7d = data.frame(date = as.Date(tail(king_county$date,-7)), rolling_new_cases_7d = rollmean(x,7))

p2 = ggplot(data=rolling_average, aes(x=date,y=rolling_new_cases_14d,group=1))+geom_line()+xlab("")
p2 +ylab("14 Day Rolling Average New Cases")
p2 +scale_x_date(date_labels = "%b-%y",date_breaks = "2 month")

combined_plot = ggplot() +
  geom_bar(stat="identity",data=data, aes(x=date, y=new_cases),fill="#FF9999", colour="#FF9999") +
  # geom_line(data=rolling_average, colour= "darkblue", aes(x=date,y=rolling_new_cases_14d,group=1)) +
  geom_line(data=rolling_average_7d, colour= "darkblue", aes(x=date,y=rolling_new_cases_7d,group=1)) +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  xlab("") + ylab("Cases")

# Add annotation
#combined_plot +
#  annotate(geom="text",x =as.Date('2020-12-15'),subset(data,date=='2020-12-15')$new_cases,label="Christmas Day") +
#  annotate(geom="point",x =as.Date('2020-12-15'),subset(data,date=='2020-12-15')$new_cases, size=10, shape=21, fill="transparent")

## Add titles
combined_plot = combined_plot +
  labs(title="King County Covid-19 Cases",
       caption="Data sourced from NY Times GitHub",
       subtitle = "Daily cases and 7 day rolling average")



## Write on plot
# combined_plot = combined_plot + annotate(geom="text",x=as.Date(Sys.Date()-21),y=2000,label=paste("Cases per 100k pop:",round(cases_per_100K_14d,2),sep="\n"))

# ipsum Theme
combined_plot = combined_plot  +
  theme_ipsum()
```

```{r combined_plot}

# Title and subtitle logic from https://datascott.com/blog/subtitles-with-ggplotly/

ggplotly(combined_plot)  %>%
  layout(
    yaxis = list(fixedrange = FALSE),
    xaxis = list(
      rangeslider = list(type = "date")),
      title = list(text = paste0('King County Covid-19 Cases',
                                '<br>',
                                '<sup>',
                                 'Daily cases and 7 day rolling average. Data sourced from NY Times.','</sup>')
                   )
  )
```

### Risk Factor over Time

```{r King County Risk Factors, echo=FALSE, message = FALSE, warning=FALSE }


risk_plot <- ggplot(data = risk_data, mapping=aes(x=date,y=last_14_day_cases_per_100k_pop)) +
  geom_area(fill="lightgray",aes()) +
  geom_hline(yintercept=25,linetype="dashed",color="yellow")+
  geom_hline(yintercept=75,linetype="dashed",color="red") +
  labs(title="King County 14 Day Risk per 100k population",
       subtitle="Using Washington state risk factors: Less than 25, low, 25 - 75 medium, greater than 75, high. Effective June 30, 2021, Washington state lifted their mask mandate and switched to the CDC Risk factors.",
       caption="") +
  xlab("") +
  ylab("Cases per 100k population in last 14 days") +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  theme_ipsum()

ggplotly(risk_plot)  %>%
  layout(
    yaxis = list(fixedrange = FALSE),
    xaxis = list(
      rangeslider = list(type = "date")),
      title = list(text = paste0('King County 14 Day Risk per 100k population',
                                '<br>',
                                '<sup>',
                                 'Using Washington state risk factors: Less than 25, low, 25 - 75 medium, greater than 75, high. Effective June 30, 2021, Washington state lifted their mask mandate and switched to the CDC Risk factors.','</sup>')
                   )
  )

```

## Charts Column 2

### Daily Deaths

```{r Daily Deaths, message=FALSE, warning=FALSE}



(king_county_tibble %>%
  mutate(rolling_7_day_deaths = mean_run(new_deaths,k=7)) %>%
  ggplot()  +
  # geom_smooth() +
  geom_bar(aes(x=date,y=new_deaths),stat = "identity",fill="#FF9999", colour="#FF9999") +
  geom_line(aes(x=date,y=rolling_7_day_deaths)) +
  ylim(0,NA) +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  xlab("") + ylab("Deaths") +
  labs(title="King County Covid-19 Deaths",caption="Data sourced from NY Times GitHub, does not display massive data correction in December") +
  theme_ipsum()
  ) %>%
  ggplotly()  %>%
  layout(
    yaxis = list(fixedrange = FALSE),
    xaxis = list(
      rangeslider = list(type = "date")),
      title = list(text = paste0('King County Covid-19 Deaths',
                                '<br>',
                                '<sup>',
                                 'Data sourced from NY Times GitHub, does not display massive data correction in December.','</sup>')
                   )
  )

```

### Last 3 Weeks

```{r 30day chart, warning=FALSE}
#  Maybe use the King County Sourced Dta
threeWeeksData <- tibble(data) %>%
  filter( date >= Sys.Date() - 21) %>%
  mutate(new_cases_7d_average = mean_run(new_cases,k=7)) %>%
  mutate(new_cases = ifelse(new_cases == 0, NA, new_cases))

write.csv(threeWeeksData,file="kingCountyThreeWeeks.csv",row.names = FALSE,na="")  

# Add hospitalizations
# Add legend
# Maybe use log axes
reportingDays <- c("Monday","Wednesday","Friday")

ggplot(threeWeeksData) +
  geom_bar(stat = "identity",aes(x=date, y=new_cases),fill="#FF9999", colour="#FF9999") +
  geom_line(aes(x=date, y=new_cases_7d_average),colour= "darkblue") +
  geom_line(aes(x=date, y=new_deaths)) +
  geom_label(aes(x=date, y=new_cases,label=new_cases),nudge_y = 50)+
  geom_label(data = threeWeeksData %>% filter (weekdays(date) %in% reportingDays),aes(x=date, y=new_cases_7d_average,label=round(new_cases_7d_average,0)),nudge_y = 50)+
  geom_label(data = threeWeeksData %>% filter (new_deaths > 0), aes(x=date, y=new_deaths,label = new_deaths),nudge_y = 50)+

  xlab("")+ylab("Deaths and New Cases") + 
  scale_x_date(date_labels = "%b-%d",date_breaks = "3 day")+
  labs(title="Last 3 Weeks Deaths and Cases",caption="Data sourced from NY Times GitHub, King County data is updated Monday, Wednesday and Friday") +
  theme_ipsum()



```

# Washington State

## KPI {data-width="250"}

```{r state KPI data, include=FALSE}
source("CumulativeDeathsAndCases.R")
```

```{r Hospitaliztion Data, warning=FALSE}
hospitalizations = read.socrata("https://healthdata.gov/resource/g62h-syeh.csv")
hospitalizations$date = as.Date(hospitalizations$date)
hospitalizations <-tibble(hospitalizations)
hospitalizations_wa = subset(hospitalizations,state == 'WA')
```

### Cumulative Cases

```{r}
format(wa_cases,big.mark = ",") %>%
  valueBox(caption='Cumulative Cases',icon="fa-frown")
```

### Cumulative Deaths

```{r}
format(wa_deaths,big.mark = ",") %>%
  valueBox(caption='Cumulative Deaths',icon='fa-skull')
```

### Vaccination Rate

```{r}
v_rate <- vaccinations %>%
  filter(date == max(date)) %>%
  select(location,people_fully_vaccinated,people_fully_vaccinated_per_hundred)

v_rate %>%
  filter(location %in% c('Washington')) %>%
  select(people_fully_vaccinated_per_hundred) %>%
  str_c("%") %>%
  valueBox(caption='Vaccination Rate',icon='fa-syringe')
```

### Current WA Hospitalizations

```{r Current Hospitalizations Washington}
hospitalizations %>%
  filter(date == max(date),state =='WA') %>%
  select(state,
         date,
         total_adult_patients_hospitalized_confirmed_and_suspected_covid,
         staffed_icu_adult_patients_confirmed_and_suspected_covid,
         total_pediatric_patients_hospitalized_confirmed_and_suspected_covid) %>%
  mutate(hospitalized = +total_adult_patients_hospitalized_confirmed_and_suspected_covid + total_pediatric_patients_hospitalized_confirmed_and_suspected_covid + staffed_icu_adult_patients_confirmed_and_suspected_covid) %>%
  summarise(hospitalized = sum(hospitalized)) %>%
  pull() %>%
  scales::comma() %>%
  valueBox(caption='Currently Hospitalized',icon="fa-bed")

```

### Washington Hospital Bed Utilization

```{r Washington Hospital Bed Utilization}
hospitalizations_wa %>%
  filter(date == max(date)) %>%
  select(inpatient_beds_utilization) %>%
  pull() %>%
  scales::percent(accuracy=.1) %>%
  valueBox(caption='Washington Inpatient Beds Utilized',icon='fa-bed')
```

### ICU Beds

```{r Washington ICU Bed Utilization}
hospitalizations_wa %>%
  filter(date == max(date)) %>%
  select(adult_icu_bed_utilization)%>%
  pull() %>%
  scales::percent(accuracy=.1) %>%
  valueBox(caption='Washington ICU Beds Utilized',icon='fa-bed')
```

### Last 14 Day Cases by County

```{r Washington Last 14 Day Table, echo=FALSE, message = FALSE}
last_14_day_cases <- nytimes %>%
filter( date >= Sys.Date() - 15) %>%
group_by(state, county, fips) %>%
summarise(last_14_days_cases = sum(diff(cases)))
last_14_day_cases = left_join(last_14_day_cases,county_population,by=c("state"="STNAME","county"= "CTYNAME"))
last_14_day_cases = subset(last_14_day_cases, select = c("state","county","fips","last_14_days_cases","POPESTIMATE2019"))
last_14_day_cases$cases_per_100K_14d = last_14_day_cases$last_14_days_cases / last_14_day_cases$POPESTIMATE2019 * 100000

knitr::kable(subset(last_14_day_cases,state=='Washington',select=c('county','last_14_days_cases')),caption="Cases by County, Last 14 Days")
```

## Column 1 {data-width="750"}

### County Cases per 100k Population over 7 days

```{r Washington county map, echo=FALSE, message = FALSE }
outlist = list()

washington = subset(nytimes,state =='Washington')

#Remove unknown county


for (c in unique(washington$county)) {
  county_info <-subset(washington,county == c & county != 'Unknown')
  county_fips = unique(county_info$fips)
  fooSTATE = as.numeric(substr(county_fips, 1, 2))
  fooCOUNTY = as.numeric(substr(county_fips, nchar(county_fips)-3+1, nchar(county_fips)))
  population = subset(county_population,STATE == fooSTATE & COUNTY == fooCOUNTY,select=c('POPESTIMATE2019'),na=0)$POPESTIMATE2019
  first_case_reported <- min(county_info$date)
  new_cases =data.frame(date = tail(county_info$date,-1), new_cases = diff(county_info$cases))
  last_14d_cases <- sum(subset(new_cases,date >= Sys.Date()-14)$new_cases)
  last_7d_cases <- sum(subset(new_cases,date >= Sys.Date()-7)$new_cases)
  out <- data.frame(state = unique(county_info$state),
                    county = unique(county_info$county),
                    county_fips = county_fips,
                    region = unique(as.numeric(county_info$fips)), ## region needs to be numeric for county_choropleth
                    first_case_reported = first_case_reported,
                    last_14d_cases = last_14d_cases,
                    last_7d_cases = last_7d_cases,
                    population = coalesce(population,0),
                    last_14d_cases_per_100k = coalesce(last_14d_cases / population *100000,0) ,
                    last_7d_cases_per_100k = coalesce(last_7d_cases / population *100000,0))
  outlist[[unique(county_info$fips)]] <- out
}



metrics = do.call(rbind,outlist)


library(choroplethr)
metrics$value = metrics$last_7d_cases_per_100k

choroplethr::county_choropleth(metrics,title="Last 7 Days Cases per 100k population",num_colors = 4,state_zoom = 'washington')+ scale_fill_brewer(palette=7)

```

### Washington Vaccinations

```{r Washington Daily Vaccinations }
daily_vax_plot <- vaccinations %>%
  filter( location %in% c('Washington')) %>%
  select(date,location,daily_vaccinations) %>%
  ggplot(aes(x=date,y=daily_vaccinations)) + geom_line() +
  xlab("") + ylab("Daiily Vaccinations") +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month")

daily_percent_vax_plot <- vaccinations %>%
  filter( location %in% c('Washington')) %>%
  select(date,location,people_fully_vaccinated_per_hundred) %>%
  ggplot(aes(x=date,y=people_fully_vaccinated_per_hundred)) + geom_line() +
  xlab("Date") + ylab("Percent Fully Vaccinated") +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  ylim(0,100)

gridExtra::grid.arrange(daily_vax_plot,daily_percent_vax_plot)

```

## Column 2

### Hospital Bed Utilization

```{r Hospital Bed Utilization, message= FALSE, warning= FALSE}
hospitalizations_wa %>%
  filter(date>='2021-01-01' & inpatient_beds_coverage > 98) %>%
  select(state,
         date,
         `Inpatient_beds utilized` = inpatient_beds_utilization,
         `Inpatient_beds used for covid` = inpatient_bed_covid_utilization,
         `Adult ICU_beds utilized` = adult_icu_bed_utilization,
         `Adult ICU_beds used for covid` = adult_icu_bed_covid_utilization) %>%
  pivot_longer(cols=any_of(c("Inpatient_beds utilized", "Inpatient_beds used for covid", "Adult ICU_beds utilized", "Adult ICU_beds used for covid")),
               names_to="measure",
               values_to = "utilization") %>%
  separate(measure,into= c("bed_type","measure"),sep ="_") %>%
  ggplot(aes(x=date,y=utilization,color=measure)) +
  geom_line() +
  scale_y_continuous(labels = scales::percent) +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  ylab('') + xlab('') +
  facet_wrap(~bed_type) +
  theme_ipsum_rc() + theme(legend.title = element_blank(),legend.position="bottom")
```

### Placeholder

```{r Washington New Cases Over Time}
washington_by_day <- washington %>%
  group_by(date) %>%
  summarise(cases = sum(cases,na.rm = TRUE),deaths=sum(deaths,na.rm = TRUE)) %>%
  arrange(date) %>%
  mutate(
    date = as.Date(date),
    new_cases = cases - lag(cases,default=0,),
    new_deaths = deaths -lag(deaths,default=0),
    new_cases_7d_average = mean_run(new_cases,k=7)
  )


washington_case_plot <- ggplot(washington_by_day) +
  geom_bar(stat="identity", aes(x=date, y=new_cases),fill="#FF9999", colour="#FF9999") +
  geom_line(colour= "darkblue", aes(x=date,y=new_cases_7d_average,group=1)) +
  scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
  xlab("") + ylab("Cases") +
  labs(title="Washington Covid-19 Cases",caption="Data sourced from NY Times GitHub")+
  theme_ipsum()

ggplotly(washington_case_plot) %>%
  layout(
    yaxis = list(fixedrange = FALSE),
    xaxis = list(
      rangeslider = list(type = "date")),
      title = list(text = paste0('Washington Covid-19 Cases',
                                '<br>',
                                '<sup>',
                                 'Daily cases and 7 day rolling average. Data sourced from NY Times.','</sup>')
                   )
  )

```

# United States

```{r US Data, message=FALSE}
cases_week_over_week <- nytimes %>%
  filter( date >= Sys.Date() - 15 & date <= Sys.Date() - 8) %>%
  group_by(state, county, fips) %>%
  summarise(last_14_days_cases = sum(diff(cases)))

cases_week_over_week <- inner_join(cases_week_over_week,
                                   nytimes %>%
                                    filter( date >= Sys.Date() - 8, county != 'Unknown', !is.na(fips)) %>%
                                    group_by(state, county, fips) %>%
                                    summarise(last_7_days_cases = sum(diff(cases))),
                                   by = NULL
                        )


cases_week_over_week$new_case_growth = (cases_week_over_week$last_7_days_cases-cases_week_over_week$last_14_days_cases)/cases_week_over_week$last_14_days_cases

cases_week_over_week$state_fips = substr(cases_week_over_week$fips,1,nchar(cases_week_over_week$fips)-3)

```

```{r US by Day Data}
us_by_day <- nytimes %>%
  group_by(date) %>%
  summarise(cases = sum(cases,na.rm = TRUE),deaths=sum(deaths,na.rm = TRUE)) %>%
  arrange(date) %>%
  mutate(
    date = as.Date(date),
    new_cases = cases - lag(cases,default=0,),
    new_deaths = deaths -lag(deaths,default=0),
    new_cases_7d_average = mean_run(new_cases,k=7)
  )
```

## US KPI Cards {data-width="350"}

### new US cases by day

```{r}
us_by_day %>%
  filter(date == max(date)) %>%
  pull(new_cases) %>%
  scales::comma() %>%
  valueBox(caption='Yesterday New Cases',icon="fa-frown")
```

### new US Deaths by day

```{r}
us_by_day %>%
  filter(date == max(date)) %>%
  pull(new_deaths) %>%
  scales::comma() %>%
  valueBox(caption='Yesterday New Deaths',icon="fa-skull")
```

### Cumulative US Cases

```{r}
si_number(us_cases,3) %>%
  valueBox(caption='Cumulative Cases',icon="fa-frown")
```

### Cumulative US Deaths

```{r}
si_number(us_deaths,3) %>%
  valueBox(caption='Cumulative Deaths',icon="fa-skull")
```

### Total Vaccinated

```{r}
vaccinations %>%
  filter(date == max(date)) %>%
  select(date,location,people_fully_vaccinated) %>%
  group_by(date) %>%
  summarise(people_fully_vaccinated = sum(people_fully_vaccinated,na.rm=TRUE)) %>%
  pull(people_fully_vaccinated) %>%
  si_number(4) %>%
  valueBox(caption='Fully Vaccinated',icon='fa-syringe')
```

### Vaccination Rate

```{r}
vaccinations %>%
  filter(date == max(date)) %>%
  select(date,location,people_fully_vaccinated,people_fully_vaccinated_per_hundred) %>%
  mutate(people_not_vaccinated = people_fully_vaccinated * (100- people_fully_vaccinated_per_hundred)/100) %>%
  mutate(people = people_not_vaccinated + people_fully_vaccinated) %>%
  summarise(vax = sum(people_fully_vaccinated,na.rm = TRUE), total_pop = sum(people,na.rm = TRUE)) %>%
  summarise(people_fully_vaccinated_per_hundred = vax/total_pop) %>%
  select(people_fully_vaccinated_per_hundred) %>%
  pull() %>%
  scales::percent(accuracy=.1) %>%
  valueBox(caption='Vaccination Rate',icon='fa-syringe')
```

### Currently Hospitalized

```{r Current Hospitalizations}
hospitalizations %>%
  filter(date == max(date)) %>%
  select(state,
         date,
         total_adult_patients_hospitalized_confirmed_and_suspected_covid,
         staffed_icu_adult_patients_confirmed_and_suspected_covid,
         total_pediatric_patients_hospitalized_confirmed_and_suspected_covid) %>%
  mutate(hospitalized = +total_adult_patients_hospitalized_confirmed_and_suspected_covid + total_pediatric_patients_hospitalized_confirmed_and_suspected_covid + staffed_icu_adult_patients_confirmed_and_suspected_covid) %>%
  summarise(hospitalized = sum(hospitalized)) %>%
  pull() %>%
  scales::comma() %>%
  valueBox(caption='Currently Hospitalized',icon="fa-bed")

```

### Yesterday's Hospitalizations

```{r Yesterdays Hospitalizations KPI}
hospitalizations %>%
  filter(date == max(date)) %>%
  select(previous_day_admission_adult_covid_confirmed,previous_day_admission_pediatric_covid_confirmed)%>%
  summarise(previous_day_addmissions = sum(previous_day_admission_adult_covid_confirmed + previous_day_admission_pediatric_covid_confirmed)) %>%
  pull() %>%
  scales::comma() %>%
  valueBox(caption='Yesterday\'s Hospitalizations',icon="fa-bed")

```

## US Charts {data-width="650"}

### State Compared to prior 7 days

```{r Case Growth by State, echo=FALSE, message = FALSE, out.width="100%"}
state_cases_week_over_week <- cases_week_over_week %>%
                                group_by(state,state_fips) %>%
                                summarise(last_7_days_cases = sum(last_7_days_cases), last_14_days_cases = sum(last_14_days_cases))

state_cases_week_over_week$region = str_to_lower(state_cases_week_over_week$state)
state_cases_week_over_week$value = (state_cases_week_over_week$last_7_days_cases-state_cases_week_over_week$last_14_days_cases)/state_cases_week_over_week$last_14_days_cases

choroplethr::state_choropleth(state_cases_week_over_week,title="Week over Week Change in New Cases")+ scale_fill_brewer(palette='RdYlGn',direction = -1)

```

<!-- ### County Compared to prior 7 days  -->

<!-- ```{r Case Growth by County, echo=FALSE, message = FALSE, out.width="100%"} -->

<!-- ## Requires region and value for plot -->

<!-- cases_week_over_week$value = cases_week_over_week$new_case_growth -->

<!-- cases_week_over_week$region = cases_week_over_week$fips -->

<!-- choroplethr::county_choropleth(cases_week_over_week,title="Week over Week Change in New Cases")+ scale_fill_brewer(palette='RdYlGn',direction = -1) -->

<!-- ``` -->

<!-- ### Cumulative US Cases -->

```{r Cumulative US Cases, eval=FALSE, message=FALSE, include=FALSE, out.width="100%"}
nytimes %>%
  group_by(date) %>%
  summarise(cumulative_cases = sum(cases), cumulative_deaths = sum(deaths)) %>%
  ggplot(aes(x=as.Date(date),y=cumulative_cases)) +
    # geom_point(color="red") +
    geom_line(color="red") +
    theme_ipsum() +
    ylab("Cases") + xlab("") +
    scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
    scale_y_continuous(labels = scales::label_number_si(),minor_breaks = scales::breaks_width(1000000),limits = c(0,NA)) +
    labs(title="Cumulative US Cases",caption="Data sourced from NY Times GitHub")

```

### Daily New US Cases

```{r Daily New US Cases}

daily_new_cases_us_plot <- us_by_day %>% 
  ggplot(aes(x=date,y=new_cases_7d_average)) +
    geom_line(color="red")+
    geom_area(fill="#FFCCCB") +
    theme_ipsum() +
    ylab("Cases") + xlab("") +
    scale_x_date(date_labels = "%b-%y",date_breaks = "2 month") +
    scale_y_continuous(labels = scales::label_number_si(),minor_breaks = scales::breaks_width(500000),limits = c(0,NA)) +
    labs(title="US New Cases",subtitle = "7 day average",caption="Data sourced from NY Times GitHub")

ggplotly(daily_new_cases_us_plot)  %>%
  layout(
    yaxis = list(fixedrange = FALSE),
    xaxis = list(
      rangeslider = list(type = "date")),
      title = list(text = paste0('US New Cases',
                                '<br>',
                                '<sup>',
                                 'Seven day rolling average. Data sourced from NY Times.','</sup>')
                   )
  )
```

## 

### Vaccinations

```{r Vaccination Rates by State, echo=FALSE, message = FALSE}
vaccinations %>%
  filter(date == max(date)) %>%
  select(location,people_fully_vaccinated_per_hundred) %>%
  mutate(location=tolower(location)) %>%
  rename(region= location,value = people_fully_vaccinated_per_hundred) %>%
  choroplethr::state_choropleth(title="Vaccinations Rate", legend = "%", num_colors=1)
```

### Impact of Vaccination on Community Risk

```{r warning=FALSE}
df <-inner_join(v_rate,state_cases_week_over_week,c("location"="state")) %>%
      left_join(state_population,c("location" = "STNAME"))

ggplot(df,aes(x=people_fully_vaccinated_per_hundred,y=(last_7_days_cases/population*100000))) +
  geom_point() +
  geom_text(aes(label=location),hjust=1, vjust=1,size=2)+
  geom_smooth()+
  xlab("Percent of state fully vaccinated") +
  ylab("Last 7 days cases per 100k population")
```

# Hospitalizations

### Hospital Bed Utilization By State

```{r Hospital Bed Utilization By State Plot, fig.height=15, fig.width=25, warning=FALSE}
hospitalizations %>%
  filter(date>='2021-07-01') %>%
  select(state,
         date,
         `Inpatient_beds utilized` = inpatient_beds_utilization,
         `Inpatient_beds used for covid` = inpatient_bed_covid_utilization,
         `Adult ICU_beds utilized` = adult_icu_bed_utilization,
         `Adult ICU_beds used for covid` = adult_icu_bed_covid_utilization) %>%
  pivot_longer(cols=any_of(c("Inpatient_beds utilized", "Adult ICU_beds utilized")),
               names_to="measure",
               values_to = "utilization") %>%
  separate(measure,into= c("bed_type","measure"),sep ="_")  %>%
  ggplot(aes(x=date,y=utilization,color=bed_type)) +
    geom_line() + 
    scale_y_continuous(labels = scales::percent, limits=c(NA,1)) +
    scale_x_date(date_labels = "%b-%y",date_breaks = "1 month") +
    ylab('') + xlab('') + 
    geofacet::facet_geo(~ state) +
    theme_ft_rc() + theme(legend.title = element_blank()) +
    labs(title="Hospital Bed Utilization",subtitle = "Inpatient and Adult ICU",caption="")

```

# Data Sources {#data-sources}

**Community Risk**

https://www.cdc.gov/coronavirus/2019-ncov/science/community-levels.html

Counties with fewer than 200 new Covid-19 cases per 100,000 people in the past week are considered to have "low" Covid-19 community levels if they have fewer than 10 new Covid-19 hospital admissions per 100,000 or less than 10% of staffed hospital beds occupied by Covid-19 patients on average in the past week.
Levels are "medium" if counties have 10 to nearly 20 new Covid-19 hospital admissions per 100,000 or between 10% and 14.9% of staffed hospital beds occupied by Covid-19 patients on average in the past week.
Levels are considered "high" if counties have 20 or more new Covid-19 hospital admissions per 100,000 or at least 15% of staffed hospital beds occupied by Covid-19 patients on average in the past week.

**Case Data**

The New York Times. (2021). Coronavirus (Covid-19) Data in the United States. Retrieved `r Sys.time()`, from <https://github.com/nytimes/covid-19-data>

**Vaccination Data**

Mathieu, E., Ritchie, H., Ortiz-Ospina, E. *et al.* A global database of COVID-19 vaccinations. *Nat Hum Behav* (2021). <https://doi.org/10.1038/s41562-021-01122-8>

**Population and Geographic Datasets**

United States Census Bureau, State and Conty FIPS identifiers. <https://www2.census.gov/geo/docs/reference/codes/files/national_county.txt>

United States Census Bureau, County Population Projections. <https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/counties/totals/co-est2019-alldata.csv>

**Hospitalization Data**

[COVID-19 Reported Patient Impact and Hospital Capacity by State Timeseries](https://healthdata.gov/Hospital/COVID-19-Reported-Patient-Impact-and-Hospital-Capa/g62h-syeh), <https://healthdata.gov/Hospital/COVID-19-Reported-Patient-Impact-and-Hospital-Capa/g62h-syeh>

**County Level Hospitalization Data**
[COVID-19 Reported Patient Impact and Hospital Capacity by Facility](https://healthdata.gov/Hospital/COVID-19-Reported-Patient-Impact-and-Hospital-Capa/anag-cw7u), <https://healthdata.gov/Hospital/COVID-19-Reported-Patient-Impact-and-Hospital-Capa/anag-cw7u>