library(readr)
d <- read_csv("Provisional_COVID-19_Death_Counts_by_Sex__Age__and_Week.csv")
d<- read_csv("https://data.cdc.gov/api/views/vsak-wrfu/rows.csv?accessType=DOWNLOAD")


library(lubridate)

d$date<-mdy(d$`End Week`)
separate(d, `Age Group`, into =c("Start", "End")) -> d
gsub("years", "100", d$End) -> d$End
gsub("Under", "0", d$Start) -> d$Start

d$End<-as.numeric(as.character(d$End))
d$Start<-as.numeric(as.character(d$Start))
gsub(" ", "_", names(d)) -> names(d)
gsub("-", "_", names(d)) -> names(d)
tolower(names(d))->names(d)

d %>% filter(date < max(date)-20) -> d ## Take out data at the end due to long reporting delay

d$non_covid<-d$total_deaths-d$covid_19_deaths
d %>% filter(d$sex == "All Sex") -> d

Covid deaths and non covid deaths per age group

Free axes

d %>% pivot_longer(c(9,11)) %>% filter(!is.na(end)) %>%
ggplot(aes(x=date,y=value,col=name)) + geom_col(show.legend = FALSE)  +facet_wrap(~start, scales="free") +  theme(axis.text.x=element_text(angle=45, hjust=1)) + ggtitle("Age class lower limit: Covid related deaths in red") + ylab("Weekly total number of deaths")

Same scale

d %>% pivot_longer(c(9,11)) %>% filter(!is.na(end)) %>%
ggplot(aes(x=date,y=value,col=name)) + geom_col(show.legend = FALSE)  +facet_wrap(~start) +  theme(axis.text.x=element_text(angle=45, hjust=1)) + ggtitle("Age class lower limit: Covid related deaths in red") + ylab("Weekly total number of deaths")

All ages

d %>% pivot_longer(c(9,11)) %>% filter(is.na(end)) %>%
ggplot(aes(x=date,y=value,fill=name)) + geom_col(show.legend = TRUE) +  theme(axis.text.x=element_text(angle=45, hjust=1)) + ggtitle("Age class lower limit: Covid related deaths in red") + ylab("Weekly total number of deaths")

Median age at death

d %>% filter(!is.na(end)) %>% mutate(mid_age=(start+end)/2) -> d
d %>% filter(covid_19_deaths>0) %>% group_by(date) %>% summarise(covid_deaths_median_age= weighted.median(x=mid_age,covid_19_deaths), covid_deaths_mean_age= weighted.mean(x=mid_age,covid_19_deaths), non_covid_deaths_median_age= weighted.median(x=mid_age,non_covid), non_covid_deaths_mean_age= weighted.mean(x=mid_age,non_covid), total_deaths_median_age= weighted.median(x=mid_age,total_deaths),  total_deaths_mean_age= weighted.mean(x=mid_age,total_deaths)) -> ages

ages %>% filter(date > min(date)+20) -> ages
library(xts)
dd<-xts(x = ages[,c(2,4,6)], order.by = ages$date)
dygraph(dd)
dd<-xts(x = ages[,c(2,4,6)], order.by = ages$date)
dygraph(dd)

Mean age at death

ages %>% filter(date > min(date)+20) -> ages
library(xts)


dd<-xts(x = ages[,c(3,5,7)], order.by = ages$date)
dygraph(dd)

Mean minus median

ages$covid_deaths_difference<-ages$covid_deaths_mean_age-ages$covid_deaths_median_age

ages$non_covid_deaths_difference<- ages$non_covid_deaths_mean_age -ages$non_covid_deaths_median_age

ages %>% filter(date > min(date)+20) -> ages
library(xts)


dd<-xts(x = ages[,c(8,9)], order.by = ages$date)
dygraph(dd)

Aggregated data summary

d %>% group_by(start,end) %>% summarise(total=sum(total_deaths),covid=sum(covid_19_deaths),percent_deaths_with_covid=round(100*covid/total,1)) ->tbl

library(readxl)
usa_demo <- read_excel("usa_demo.xls", sheet = "Sheet2")[,-1]
tbl<-merge(usa_demo,tbl)
tbl$percent_pop_dying_with_covid<- round(100*tbl$covid/tbl$Pop,3)
tbl$estimated_incidence<-tbl$covid/(tbl$ifr/100)
tbl$percent_incidence<-round(100*tbl$estimated_incidence/tbl$Pop,1)
aqm::dt(tbl)
aqm::dt(tbl)