---
title: "COVID-19 | Tennessee"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
knit: (function(input_file, encoding) {
 out_dir <- 'docs';
 rmarkdown::render(input_file,
 encoding=encoding,
 output_file=file.path(dirname(input_file), out_dir, 'index.html'))})
---
  

```{r setup, include=FALSE}
library(flexdashboard)
library(readr)
library(ggplot2)
library(plotly)
library(tidyverse)
library(dplyr)

#Acquire Data####
#Load NY Times Github data containing all US Counties###
nyt_path = 'https://raw.githubusercontent.com/nytimes/covid-19-data/master/us-counties.csv'

counties = read_csv(url(nyt_path))

#Separate Tennessee
tn = counties[ which(counties$state =='Tennessee'),]
tn = tn[which(tn$date < '2020-03-31'),] #The Tennessee data from the new source has data starting March 31

#Tennessee data from TN State Data Center
tn_state = 'https://myutk.maps.arcgis.com/sharing/rest/content/items/32b104abc5d841ca895de7f7c17fc4dc/data'

download.file(tn_state,'TN_COVID19_CountyDaily.xlsx') 

#Data cleaning and processing####
tn_daily =  readxl::read_excel('TN_COVID19_CountyDaily.xlsx',sheet=1) %>%
  filter(DATE > '2020-03-30') %>%
  select(DATE, COUNTY, TEST_POS, TEST_NEG, DEATHS_TOT) %>%
  filter(COUNTY != 'Balance') 

names(tn_daily) = c('Date', 'County', 'Positive', 'Negative', 'Death')

tn_daily$County = ifelse(tn_daily$County == 'Non-Tennessee Resident',
                         "Out of TN",
                         tn_daily$County)

tn_daily$County = ifelse(tn_daily$County == 'Out of State',
                         "Out of TN",
                         tn_daily$County)

tn_daily$County =ifelse(tn_daily$County == 'Dekalb', 
                        'DeKalb', 
                        tn_daily$County)

tn_daily$County =ifelse(tn_daily$County == 'VanBuren', 
                        'Van Buren', 
                        tn_daily$County)

tn_daily$County = as.factor(tn_daily$County)

tn_daily = filter(tn_daily, County !='Probable')

#Merge NYT and Tn Daily dataframes####
tn_daily2 = tn_daily[,c('Date','County', 'Positive', 'Death')]
names(tn_daily2) = c('date','county', 'cases', 'deaths')
tn_daily2 = tn_daily2[!(tn_daily2$county =='Out of TN' | tn_daily2$county =='Pending' | tn_daily2$county == 'Probable'),]
tn_daily2 = tibble::add_column(tn_daily2, state = 'Tennessee', .after='county')

fips_daily =tn %>% group_by(county, fips) %>% tally()

tn_daily2 = left_join(tn_daily2, fips_daily[,1:2], by ='county')
##Row bind tn_daily (TN Health Dept) with tn
tn = rbind(tn, tn_daily2) #Rbind will automatically put the correct columns together. 


#Add population####
#Get Census Population for counties in Tennessee

uscensus = 'https://raw.githubusercontent.com/mfcarrasco/COVID-TN-Counties/master/county_pop_2019.csv'
tn_pop = read_csv(url(uscensus))
tn_pop = tn_pop[ which(tn_pop$State =='Tennessee'),]
tn_pop = tn_pop[-1,c(2:3)]
tn_pop$County = gsub(' County', '', tn_pop$County)
tn_pop$Population = as.numeric(tn_pop$Population)
tn_pop = tn_pop[, c('County', 'Population')]
names(tn_pop) = c('county', 'population')

##Combine tn (NYT) dataframe with Population
tn = left_join(tn, tn_pop, by='county')
tn$county = as.factor(tn$county)

#Calculate per 10,000 residents
tn['cases_per_tenk'] = (tn$cases/tn$population)*10^4
#Tn dataframe is ready for long term data visualiations and includes standardization by population.


#Keep most recent for tn_daily
tn_daily = tn_daily %>% group_by(County) %>% top_n(1, Date)

#Clean the global environment###
rm(list=ls()[!ls() %in% c('tn', 'tn_daily')])



#Value Box Calculations####
tn_ext =  readxl::read_excel('TN_COVID19_COUNTYDaily.xlsx',sheet=1) %>%
  top_n(1,DATE) %>%
  filter(COUNTY != 'Balance') 

tn_ext$COUNTY = ifelse(tn_ext$COUNTY == 'Non-Tennessee Resident',
                         "Out of TN",
                         tn_ext$COUNTY)


tn_ext$COUNTY = as.factor(tn_ext$COUNTY)

#Total Cases

total_cases = sum(tn_ext$TEST_POS)
total_negative = sum(tn_ext$TEST_NEG)
total_death = sum(tn_ext$DEATHS_TOT)

total_recov = sum(tn_ext$TOTAL_INACTIVE_RECOVERED)
active_cases = total_cases - total_death - total_recov #sum(tn_ext$ACTIVE_TOT)

total_tests = total_cases + total_negative

ks = function(x) {scales::number_format(accuracy = 1, scale = 1/1000, suffix = 'K')(x)}

```

Data Visualizations by County
=======================================

Rows {data-width = 150}
-----------
### Confirmed Cases to Date

```{r}
#Total Positive Cases
cases_per = ((total_cases/total_tests)*100) %>% 
  round(1) %>% 
  paste0('%')

total_cases_vb = total_cases %>% 
  formattable::comma(digits=0) %>% 
  paste0(' (',cases_per,')') 

valueBox(value = total_cases_vb, icon='fa-user-plus', color='#002D65')
```

### Negative Tests 

```{r} 
#Total Negative Cases
negative_per = ((total_negative/total_tests)*100) %>% 
  round(1) %>% paste0('%')

total_negative_vb = total_negative %>% 
  formattable::comma(digits=0) %>% paste0(' (', negative_per,')') 

valueBox(value = total_negative_vb, icon='fa-user-minus', color='#CC0000')
```


Rows {data-width = 150}
-----------

### Recovered Cases: `r total_recov %>% formattable::comma(digits=0)`
```{r}
recov_per = ((total_recov/(total_cases))*100) %>% round(1)

gauge(recov_per, min=0, max = 100, symbol = '%')
```

### Active Cases: `r active_cases %>% formattable::comma(digits=0)`
```{r}
active_per = ((active_cases/(total_cases))*100) %>% round(1) 

gauge(active_per, min=0, max = 100, symbol = '%', 
      gaugeSectors(
        success = c(0,25), warning = c(26,100)))
```

### Total Deaths: `r total_death %>% formattable::comma(digits=0)` 

```{r} 
 #Total Deaths Cases
death_per = ((total_death/total_cases)*100) %>% round(1) %>% paste0('%')

gauge(death_per, min=0, max = 100, symbol = '%', 
      gaugeSectors(
        success = c(0,5), warning = c(6,100)))
```


Column {}
-----------------------------------------------------------------------

### Cases across time in most populous counties

```{r}

tn_top =c('Shelby', 'Davidson', 'Knox', 'Hamilton', 'Rutherford', 'Williamson')
tn_top = tn[ tn$county %in% tn_top,]


t_line = tn_pop_line =ggplot(data=tn_top, aes(x=date, y=cases, color=county))+
  geom_line(size=1)+
  scale_x_date(expand = c(0,0), date_breaks = '3 week', date_labels = '%b %d')+
  scale_y_continuous(labels = ks)+
  labs(x='', y='Cases')+
  theme(legend.position = 'none', 
        panel.background = element_blank(), 
        axis.line = element_blank(),
        axis.ticks = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1))+
  scale_color_brewer(palette = 'Spectral',direction=-1)
ggplotly(t_line)
```

### Cases across time in most populous counties {.mobile}

```{r}
tn_top =c('Shelby', 'Davidson', 'Knox', 'Hamilton', 'Rutherford', 'Williamson')
tn_top = tn[ tn$county %in% tn_top,]

ggplot(data=tn_top, aes(x=date, y=cases, color=county))+
  geom_line(size=1)+
  scale_x_date(expand = c(0,0), date_breaks = '3 week', date_labels = '%m-%d')+
  scale_y_continuous(labels = ks)+
  labs(x='', y='Cases')+
  theme(legend.title = element_blank(), 
        panel.background = element_blank(), 
        axis.line = element_blank(),
        axis.ticks = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1),
        legend.position ='top',
        legend.box = 'horizontal')+
  scale_color_brewer(palette = 'Spectral',direction=-1)
```

### Logarithm - Cases in populous counties
```{r}
tn_top =c('Shelby', 'Davidson', 'Knox', 'Hamilton', 'Rutherford', 'Williamson')
tn_top = tn[ tn$county %in% tn_top,]


logplot = ggplot(data=tn_top, aes(x=date, y=cases, color=county))+
  geom_line(size=1)+
  scale_x_date(expand = c(0,0), date_breaks = '3 week', date_labels = '%b %d')+
  labs(x='', y='Cases')+
  theme(legend.title = element_blank(), 
        panel.background = element_blank(), 
        axis.line = element_blank(),
        axis.ticks = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1))+
  scale_color_brewer(palette = 'Spectral',direction=-1)+
  scale_y_log10(breaks=scales::trans_breaks('log10', function(x) 10^x))
ggplotly(logplot)
```

### Logarithm - Cases in populous counties {.mobile}
```{r}
tn_top =c('Shelby', 'Davidson', 'Knox', 'Hamilton', 'Rutherford', 'Williamson')
tn_top = tn[ tn$county %in% tn_top,]


ggplot(data=tn_top, aes(x=date, y=cases, color=county))+
  geom_line(size=1)+
  scale_x_date(expand = c(0,0), date_breaks = '3 week', date_labels = '%b %d')+
  labs(x='', y='Cases')+
  theme(legend.title = element_blank(), 
        panel.background = element_blank(), 
        axis.line = element_blank(),
        axis.ticks = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1),
        legend.position ='top',
        legend.box = 'horizontal')+
  scale_color_brewer(palette = 'Spectral',direction=-1)+
  scale_y_log10(breaks=scales::trans_breaks('log10', function(x) 10^x))

```

Row {data-width=400}
-------------------------
### Active Cases Rate by 10K Population
```{r}
#Cases rate by ACTIVE cases
library(tidyverse)
library(usmap)
library(viridis)
tn_geo = tn_ext %>% 
  filter(COUNTY != 'Out of State' & COUNTY !='Pending') %>%
  left_join(tn[,c('county','date', 'population')], by=c(COUNTY = 'county')) %>%
  mutate(fips = fips(state = 'TN', county = COUNTY)) %>%
  top_n(1,date) %>%
  mutate(ACTIVE_PER_TENK = (ACTIVE_TOT/population)*10^4) %>%
  select(COUNTY, ACTIVE_TOT, fips, ACTIVE_PER_TENK)


library(rjson)
url = 'https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json'
json_file <- rjson::fromJSON(file=url)

#Create map

fig <- plot_ly() %>% 
  add_trace(
    text = paste(tn_geo$COUNTY,' County'),
    hoverinfo = 'text',
    type='choroplethmapbox',
    geojson= json_file,
    locations=tn_geo$fips,
    z = tn_geo$ACTIVE_PER_TENK,
    zmin=0,
    zmax = round(max(tn_geo$ACTIVE_PER_TENK),-3),
    colorscale='Viridis',
    marker=list(line=list(
      width=0),
      opacity=0.9)) %>% 
  layout(mapbox=list(
    style="carto-positron",
    zoom =5.05,
    center=list(lon= -86.7816, lat=36.1627))) %>%
  colorbar(title = "Cases per 10,000") 
fig
```

### Active Cases Rate by 10K Population {.mobile}
```{r}
#Create mobile-friendly map
plot_usmap(include = 'TN',
           regions = 'counties',
           data = tn_geo,
           values = 'ACTIVE_PER_TENK') +
  scale_fill_viridis(name = 'Cases per 10K')+
  theme(legend.position = 'top')
```

### Case numbers by county {.no-mobile}
```{r}
tn_ext %>%
  mutate(CASE_RATE = paste0(round(((TEST_POS/TEST_TOT)*100),1),'%')) %>%
  select('COUNTY','CASES_CONFIRMED','CASE_RATE','ACTIVE_TOT', 'TOTAL_INACTIVE_RECOVERED', 'HOSPITALIZED_TOT','DEATHS_TOT') %>%
  DT::datatable(rownames = FALSE,
                colnames = c('County','Confirmed','Positive Case Rate', 'Active','Recovered', 'Hospitalized', 'Death' ),
                options = list(pageLength = 10))
```

Column {data-width=350, data-height=470}
-----------------------------------------------------------------------

### Positive cases by counties with more than 5000 cases {.no-mobile}

```{r}
tn_cases = tn_daily[which(tn_daily$Positive >5000 & 
                            tn_daily$County != 'Pending'  &
                            tn_daily$County != 'Out of TN'), 
                    c('County', 'Positive','Negative','Death')] 
plot_ly(data=tn_cases,
        x=tn_cases$Positive,
        y=reorder(tn_cases$County, tn_cases$Positive),
        type='bar',
        orientation='h', 
        marker= list(color='#002D65')) %>%
  layout(xaxis = list(title= 'Count', 
                      zeroline = FALSE, 
                      showline = F, 
                      showticklabels = T, 
                      showgrid = F),
         yaxis = list(showgrid = FALSE, 
                      showline = FALSE, 
                      showticklabels = TRUE,
                      dtick=1,
                      tickfont = list(size=10)))
```



### All outcomes by counties with more than 5000 cases

```{r}
tn_cases = tn_daily[which(tn_daily$Positive > 5000 & 
                            tn_daily$County != 'Pending'  &
                            tn_daily$County != 'Out of TN'), c('County', 'Positive','Negative','Death')] #Remove where there are no cases

plot_ly(data=tn_cases,
        x= reorder(tn_cases$County, tn_cases$Negative),
        y=tn_cases$Negative,
        type='bar',
        name='Negative Cases',
        marker= list(color='grey')) %>%
          add_trace(y = tn_cases$Positive,
                    name='Positive Cases',
                    marker = list(color='#002D65')) %>%
          add_trace(y = tn_cases$Death,
                    name='Deaths',
                    marker = list(color='#CC0000')) %>%
          layout(barmode = 'stack',
                 xaxis = list(showgrid = FALSE, 
                              showlilnee = FALSE, 
                              showticklabels = TRUE,
                              dtick=1,
                              tickfont =list(size=10)),
                 yaxis = list(title= 'Count', 
                              zeroline = FALSE, 
                              showline = F, 
                              showticklabels = T, 
                              showgrid = F),
                 hovermode = 'compare')
```

### All outcomes by counties with more than 5000 cases {.mobile}

```{r}
tn_cases = tn_daily[which(tn_daily$Positive > 5000 & 
                            tn_daily$County != 'Pending'  &
                            tn_daily$County != 'Out of TN'), 
                    c('County', 'Death','Negative','Positive')] %>%
  gather(Cases, Count, Death:Positive) %>% 
  mutate(Cases = factor(Cases, levels = c("Death", "Positive", "Negative")))

ggplot(tn_cases,aes(y=reorder(County, Count, sum), x= Count, fill = Cases))+
  geom_bar(position='stack', stat =  'identity')+
  labs(x='Count', y='')+
  theme(legend.title = element_blank(), 
        panel.background = element_blank(), 
        axis.line = element_blank(),
        axis.ticks = element_blank(),
              axis.text = element_text(face = 'bold'),
              legend.direction='horizontal',
              legend.position = 'top')+
  scale_fill_manual(values = c(Death = '#CC0000', Positive = '#002D65', 'Negative' = 'grey')) +
  scale_x_continuous(labels = ks,breaks = seq(min(tn_cases$Count), max(tn_cases$Count)*1.5, by=20000))
```

Column {data-height=500}
----------------------------------------------------------------------

### Change of Total Cases in Tennessee - 4 Weeks

```{r}
#Moving Average function
ma = function(x, n=7){
  tsobject = stats::filter(x,
                           rep(1/n, n),
                           sides = 2)
  as.numeric(tsobject)}
#Data frame for Tennessee by county that goes back four weeks
tn_delta =  readxl::read_excel('TN_COVID19_COUNTYDaily.xlsx',sheet=1) %>%
  select(DATE:CASES_NEW,RECOV_TOT:ACTIVE_NEW) %>%
  mutate(DATE = as.Date(DATE)) %>%
  #filter(DATE >= (Sys.Date()-28))%>%
  mutate(COUNTY = ifelse(COUNTY== 'Dekalb',
                         'DeKalb',
                         COUNTY)) %>%
  mutate(COUNTY = as.factor(ifelse(COUNTY == 'Non-Tennessee Resident',
                                   "Out of TN",
                                   ifelse(COUNTY == 'Out of State',
                                          'Out of TN',
                                          COUNTY)))) %>%
  filter(is.na(COUNTY) != TRUE) #Remove the blank total row they had.

#Data frame for Tennessee total that goes back four weeks

total_delta = tn_delta %>%
  group_by(DATE) %>%
  summarise(CASES = sum(CASES_TOT, na.rm= T),
            ACTIVE = sum(ACTIVE_TOT,na.rm= T),
            DEATHS = sum(DEATHS_TOT,na.rm= T),
            RECOV = sum(RECOV_TOT, na.rm=T),
            CASES_NEW = sum(CASES_NEW, na.rm=T)) %>%
  mutate(PREV_CASES = lag(CASES, order_by= DATE)) %>%
  mutate(RATE_CHG_1DAY = ((CASES-PREV_CASES)/PREV_CASES)*100) %>%
  mutate(RATE_CHG_1DAY= ifelse(is.finite(RATE_CHG_1DAY),RATE_CHG_1DAY,0)) %>%
  mutate(MOV_AVG = tibble(RATE_CHG_1DAY) %>% ma)%>%
  mutate(MOV_AVG_CASES = tibble(CASES_NEW) %>% ma)%>%
  filter(DATE >= (Sys.Date()-28))

##Plot for Tennessee
colors = c('Tennessee Data' = '#002D65', 'Moving Average' = '#CC0000')
ggplot(total_delta, aes(x=DATE)) +
  geom_line(aes(y=RATE_CHG_1DAY, color= 'Tennessee Data'),size=1)+
  geom_line(aes(y=MOV_AVG, color = 'Moving Average' ),size = 1,linetype='dashed')+
  labs(x='', y='Change in Cases (%)')+
  theme(legend.title = element_blank(),
        panel.background = element_blank(),
        axis.line = element_blank(),
        axis.ticks = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1),
        legend.position = 'top',
        legend.direction = 'horizontal')+
  scale_x_date(expand = c(0,0), date_breaks = '2 day', date_labels = '%b %d')+
  scale_color_manual(values = colors)
```

### Change of Total Cases in Selected County {.no-mobile}
```{r}
library(crosstalk)
library(dplyr)
library(plotly)

#Add a moving average column to the dataframe
mov_avg = tn_delta %>%
  group_by(COUNTY) %>%
  mutate(PREV_CASES = lag(CASES_TOT, order_by= DATE)) %>%
  mutate(RATE_CHG_1DAY = ((CASES_TOT-PREV_CASES)/PREV_CASES)*100) %>%
  mutate(RATE_CHG_1DAY= ifelse(is.finite(RATE_CHG_1DAY),RATE_CHG_1DAY,0)) %>%
  select(DATE,COUNTY, RATE_CHG_1DAY) %>%
  mutate(MOV_AVG = tibble(RATE_CHG_1DAY) %>% ma) %>%
  filter(DATE >= (Sys.Date()-28))

#Interested in filtering so that people can select the appropriate county from mov avg

shared_movavg = SharedData$new(mov_avg)
bscols(widths=c(3,NA),
       list(
         filter_select('county', 'Type a County', shared_movavg, ~mov_avg$COUNTY)),
       plot_ly(shared_movavg,
               x = ~DATE,
               y = ~RATE_CHG_1DAY,
               name = 'County',
               type = 'scatter',
               mode = 'lines',
               line = list(
                 color = '#002D65')) %>%
         add_trace(y = ~MOV_AVG,
                   name='Moving Average',
                   type = 'scatter',
                   mode = 'lines',
                   line = list(
                     color = '#CC0000')) %>%
         layout(showlegend = F,
                yaxis = list(
                  title= 'Change in Cases (%)',
                  zeroline = FALSE,
                  showline = F,
                  showticklabels = T,
                  showgrid = F),
                xaxis = list(showgrid = FALSE,
                             showlilnee = FALSE,
                             showticklabels = TRUE,
                             tickfont =list(size=10),
                             title = '',
                             tickformat = '%b %d')) %>%
         highlight(opacityDim = 1)
)
```

Column {data-height=500}
----------------------------------------------------------------------

### New Cases in Tennessee - 4 Weeks

```{r}
#Data frame for Tennessee total that goes back four weeks
#Plot for Tennessee
colors = c('Tennessee Data' = '#002D65', 'Moving Average' = '#CC0000')
ggplot(total_delta, aes(x=DATE)) +
  geom_col(aes(y=(CASES_NEW), fill= 'Tennessee Data'),size=.5)+
  geom_line(aes(y=MOV_AVG_CASES , color = 'Moving Average' ),size = 1,linetype='dashed')+
  labs(x='', y='New Cases')+
  theme(legend.title = element_blank(),
        panel.background = element_blank(),
        axis.line.x=element_blank(),
        axis.line.y.left = element_blank(),
        axis.text=element_text(face='bold'),
        axis.text.x = element_text(angle=45, hjust=1),
       legend.position = 'top',
        legend.direction = 'horizontal')+
  scale_x_date(expand = c(0,0), date_breaks = '2 day', date_labels = '%b %d')+
  scale_color_manual(values = colors)+
  scale_fill_manual(values = colors)
```

### New Cases in Selected County {.no-mobile}
```{r}
#Moving average for new cases
mov_avg = tn_delta %>%
  select(DATE,COUNTY, CASES_NEW) %>%
  group_by(COUNTY) %>%
  mutate(MOV_AVG = tibble(CASES_NEW) %>% ma) %>%
  filter(DATE >= (Sys.Date()-28))



#Interested in filtering so that people can select the appropriate county from mov avg

shared_movavg = SharedData$new(mov_avg)
bscols(widths=c(3,NA),
       list(
         filter_select('county', 'Type a County', shared_movavg, ~mov_avg$COUNTY)),
       plot_ly(shared_movavg, x = ~DATE) %>%
         add_trace(y = ~CASES_NEW,
               name = 'County',
               type = 'bar',
               marker = list(
                 color = '#002D65')) %>%
         add_trace(y = ~MOV_AVG,
                   name='Moving Average',
                   type = 'scatter',
                   mode = 'lines',
                   line = list(
                     color = '#CC0000')) %>%
         layout(showlegend = F,
                yaxis = list(
                  title= 'New Cases',
                  zeroline = FALSE,
                  showline = F,
                  showticklabels = T,
                  showgrid = F),
                xaxis = list(showgrid = FALSE,
                             showlilnee = FALSE,
                             showticklabels = TRUE,
                             tickfont =list(size=10),
                             title = '',
                             tickformat = '%b %d'))%>%
         highlight(opacityDim = 1)
       )
```

Data Visualizatons by Demographics
=====================================

Column {data-width=350, data-height=450}
---------------------------
### Confirmed Cases by Age
```{r}
#Get US Census Demographic Data 
census_demo = 'https://raw.githubusercontent.com/mfcarrasco/COVID-TN-Counties/master/census_demographics.xlsx'

age_census = readxl::read_excel('census_demographics.xlsx',sheet='Age') %>% 
  select(Age, Percent)%>%
  rename('Census_Percent' = 'Percent') 

#Get TN Cases Data
tn_age = 'https://myutk.maps.arcgis.com/sharing/rest/content/items/1bdfe86c38514c9c878241d5230d9a85/data'

download.file(tn_age,'TN_Age.xlsx') 

tn_age =  readxl::read_excel('TN_Age.xlsx',sheet=1) %>% 
  top_n(1,DATE) %>%
  select(DATE, AGE, TOT_CASE_COUNT, DEATHS_TOT)

names(tn_age) = c('Date', 'Age', 'Count',  'Deaths')

tn_age$Age = as.factor(tn_age$Age)
tn_age$Case_Percent = (tn_age$Count/sum(tn_age$Count))*100
tn_age$Death_Percent =(tn_age$Deaths/sum(tn_age$Deaths))*100

tn_age = cbind(tn_age[,c('Age', 'Case_Percent','Death_Percent')], age_census[,2])
tn_age$Census_Percent[10] = NA

fills = c('Case_Percent' = '#002D65', 'Death_Percent' = '#CC0000', 'Census_Percent' = 'grey')

ggplot(tn_age,aes(x=Age))+
  geom_col(aes(y = Census_Percent, fill='Census_Percent'),width = .75)+
  geom_col(aes(y = Case_Percent, fill= 'Case_Percent'),width = .5)+
  geom_col(aes(y = Death_Percent, fill='Death_Percent'),width = .1)+
  theme(panel.background = element_blank(), 
        axis.title = element_blank(),
        axis.ticks = element_blank(),
        axis.line = element_blank(), 
        axis.text = element_text(face = 'bold'),
        axis.text.x = element_text(angle=30),
        legend.title = element_blank(),
        legend.position = c(.2,.90),
        legend.box.just = 'left')+
  scale_fill_manual(breaks=c('Census_Percent', 'Case_Percent', 'Death_Percent'),
                    values=fills,
                    labels=c('Population %', 'Cases %', 'Deaths %'))


```

### Confirmed Cases by Sex
```{r}
#Get US Census Demographic Data - Sex
sex_census = readxl::read_excel('census_demographics.xlsx',sheet='Sex') %>% 
  rename('Census_Percent' = 'Percent')

#Get TN Cases Data
tndh_demo = 'https://www.tn.gov/content/dam/tn/health/documents/cedep/novel-coronavirus/datasets/Public-Dataset-RaceEthSex.XLSX'
download.file(tndh_demo,'Public-Dataset-RaceEthSex-2.xlsx') 

tndh_demo =  readxl::read_excel('Public-Dataset-RaceEthSex-2.xlsx',sheet=1) %>% 
  top_n(1,Date) %>%
  group_split(Category)

tn_sex = tndh_demo[[3]] %>% 
  select(-c('Date', 'Category'))
  
names(tn_sex) = c('Sex', 'Count', 'Case_Percent', 'Deaths', 'Death_Percent')

tn_sex = tn_sex %>% 
  mutate(Case_Percent = Case_Percent*100) %>%
  mutate(Death_Percent = Death_Percent*100) %>%
  select(Sex, Case_Percent, Death_Percent) %>%
  left_join(sex_census, 'Sex')

fills = c('Case_Percent' = '#002D65', 'Death_Percent' = '#CC0000', 'Census_Percent' = 'grey')

ggplot(tn_sex,aes(x=Sex))+
  geom_col(aes(y = Census_Percent, fill='Census_Percent'),width = .75)+
  geom_col(aes(y = Case_Percent, fill= 'Case_Percent'),width = .5)+
  geom_col(aes(y = Death_Percent, fill='Death_Percent'),width = .1)+
  theme(panel.background = element_blank(), 
        axis.title = element_blank(),
        axis.ticks = element_blank(),
        axis.line = element_blank(), 
        axis.text = element_text(face = 'bold'),
        axis.text.x = element_text(angle=30),
        legend.title = element_blank(),
        legend.position = c(.85,.90),
        legend.box.just = 'left')+
  scale_fill_manual(breaks=c('Census_Percent', 'Case_Percent', 'Death_Percent'),values=fills,labels=c('Population %', 'Cases %', 'Deaths %'))
```


Column {data-width=350, data-height=450}
---------------------------

### Confirmed Cases by Race
```{r}
#Get US Census Demographic Data - Race
race_census = readxl::read_excel('census_demographics.xlsx',sheet='Race') %>% 
  select(Race = Race, Census_Percent =Percent)

tn_race = tndh_demo[[2]] %>% 
  select(-c('Date', 'Category'))
  
names(tn_race) = c('Race', 'Count', 'Case_Percent', 'Deaths', 'Death_Percent')

tn_race = tn_race %>% 
  mutate(Case_Percent = Case_Percent*100) %>%
  mutate(Death_Percent = Death_Percent*100) %>%
  select(Race, Case_Percent, Death_Percent) %>%
  left_join(race_census, 'Race') %>%
  mutate(Race = factor(Race, levels = c('Asian', 'Black or African American', 'White', 'Other/Multiracial', 'Pending')))


fills = c('Case_Percent' = '#002D65', 'Death_Percent' = '#CC0000', 'Census_Percent' = 'grey')

ggplot(tn_race,aes(y=Race))+
  geom_col(aes(x = Census_Percent, fill='Census_Percent'),width = .75)+
  geom_col(aes(x = Case_Percent, fill= 'Case_Percent'),width = .5)+
  geom_col(aes(x = Death_Percent, fill='Death_Percent'),width = .1)+
  scale_y_discrete(limits = rev(levels(tn_race$Race)))+
  theme(panel.background = element_blank(), 
        axis.line = element_blank(), 
        axis.title=element_blank(),
        axis.ticks = element_blank(),
        axis.text = element_text(face = 'bold'),
        axis.text.x = element_text(angle=30, h=1),
        legend.title = element_blank(), 
        legend.position = c(.85,.90),
        legend.box.just = 'left')+
  scale_fill_manual(breaks=c('Census_Percent', 'Case_Percent', 'Death_Percent'),values=fills,labels=c('Population %', 'Cases %', 'Deaths %'))
```

### Confirmed Cases by Ethnicity
```{r}
#Get US Census Demographic Data - Ethnicity
eth_census = readxl::read_excel('census_demographics.xlsx',sheet='Ethnicity') %>% 
  select(Ethnicity=Ethnicity,Census_Percent =Percent)

tn_eth = tndh_demo[[1]] %>% 
  select(-c('Date', 'Category'))
  
names(tn_eth) = c('Ethnicity', 'Count', 'Case_Percent', 'Deaths', 'Death_Percent')

tn_eth = tn_eth %>% 
  mutate(Ethnicity = ifelse(Ethnicity == 'Hispanic', 'Hispanic or Latino', Ethnicity)) %>% 
  mutate(Case_Percent = Case_Percent*100) %>%
  mutate(Death_Percent = Death_Percent*100) %>%
  select(Ethnicity, Case_Percent, Death_Percent) %>%
  left_join(eth_census, 'Ethnicity') %>% 
  mutate(Ethnicity = factor(Ethnicity, levels = c('Hispanic or Latino','Not Hispanic or Latino', 'Pending')))


fills = c('Case_Percent' = '#002D65', 'Death_Percent' = '#CC0000', 'Census_Percent' = 'grey')

ggplot(tn_eth,aes(y=Ethnicity))+
  geom_col(aes(x = Census_Percent, fill='Census_Percent'),width = .75)+
  geom_col(aes(x = Case_Percent, fill= 'Case_Percent'),width = .5)+
  geom_col(aes(x = Death_Percent, fill='Death_Percent'),width = .1)+
  scale_y_discrete(limits = rev(levels(tn_eth$Ethnicity)), position='right')+
  scale_x_reverse()+
  theme(panel.background = element_blank(), 
        axis.line = element_blank(), 
        axis.title=element_blank(),
        axis.ticks = element_blank(),
        axis.text = element_text(face = 'bold'),
        axis.text.x = element_text(angle=30, h=1),
        legend.title = element_blank(), 
        legend.position = c(.15,.90),
        legend.box.just = 'left')+
  scale_fill_manual(breaks=c('Census_Percent', 'Case_Percent', 'Death_Percent'),values=fills,labels=c('Population %', 'Cases %', 'Deaths %'))
```

About 
================================

**The Tennessee Coronavirus Dashboard**    
  
The sole intention of this Coronavirus dashboard is to provide a visual overview of the 2019 Novel COVID-19 as it relates to counties in Tennessee. This dashboard has different graphs for small screens. For more interactive graphs, please view this website on a larger screen (computer/large tablet).   


**Data**

Data is acquired from the [New York Times Coronavirus Data](https://github.com/nytimes/covid-19-data), the [Tennessee State Data Center](https://myutk.maps.arcgis.com/home/group.html?id=c98fc99308dd43fb98146d3cf21fc31c&q=tags%3A%22COVID-19%22&view=list&focus=files#content), and the [Tennessee Department of Health](https://www.tn.gov/health/cedep/ncov.html). 

Last updated: `r max(tn$date) %>% format('%m-%d')`.

Population data acquired from the [US Census](https://data.census.gov/cedsci/table?q=Tennessee%20race%20demographics&g=0400000US47&tid=ACSDP1Y2018.DP05&hidePreview=true).



Created by [Malle Carrasco-Harris, PhD](https://www.linkedin.com/in/malle-carrasco-harris) using [RStudio Flexdashboard](https://rmarkdown.rstudio.com/flexdashboard/) and associated packages available in the Source Code.