ONS infection survey

library(readxl)
d <- read_excel("covid19infectionsurveydatasets20201016.xlsx", 
    sheet = "1f", skip = 6)

rnames<-covid19infectionsurveydatasets20201016 <- read_excel("covid19infectionsurveydatasets20201016.xlsx", 
    sheet = "1f", col_names = TRUE, skip = 4, 
    n_max = 1)
rnames<-names(rnames)
rnames[grep("X",rnames)]<-NA
rnames<-na.omit(rnames)
rnames<-rep(rnames,each=6)
rnames<-c("date",rnames)
headers<-paste(c("rate","lwr","upr","remove1","remove2","remove3"),rnames[-1],sep="_")
headers<-c("date",headers)
names(d)<-paste(headers)
d<-d[,-grep("remove",names(d))]

d<-d[1:42,]
sdate<-dmy("27-8-20")
d$date<-sdate+1:42
d %>% pivot_longer(-1) -> d
d %>% separate(name, into=c("var","region"),sep="_") -> d

theme_set(theme_bw())
d %>% arrange(date) -> d
estimates<-d
d1<-filter(d,var=="rate")
d2<-filter(d,var=="lwr")
d3<-filter(d,var=="upr")

ggplot(d1,aes(x=date,y=value,col=region)) +geom_line() + geom_line(aes(y=d2$value),lty=2) + geom_line(aes(y=d3$value),lty=2)+ facet_wrap(~region)

load("data.rda")

d <- read_csv("https://coronavirus.data.gov.uk/downloads/msoa_data/MSOAs_latest.csv")
#save(d,bds,pop,shp,student_pop,universities,file="data.rda")

pop<-pop[,c(1,7)]
student_pop<-student_pop[,3:4]
names(pop)[1]<-"msoa11_cd"
names(student_pop)[1]<-"msoa11_cd"
d%>% pivot_longer(9:46)->d
d$value<-as.numeric(gsub("-99",0,d$value))
d$name<-gsub("latest_7_days", "wk_42", d$name)
d$week<-aqm::clean(d$name)

d<-merge(d,pop)
d<-merge(d,student_pop) 

d %>% group_by(rgn19_nm,week) %>% summarise(cases=sum(value),population=sum(Total.Pop)) %>% mutate(rate=cases/population) -> dregions
names(dregions)[1]<-"region"

d1$week<-week(d1$date)
d1 %>% filter(week>35) %>% filter(week<42) %>% group_by(week,region) %>% summarise(mean=mean(value)) -> infections
infections %>% filter(region %in% unique(dregions$region)) -> infections
dregions %>% filter(week>35) %>% filter(week<42) %>% filter(region %in% unique(infections$region)) -> dregions

 ggplot(infections,aes(x=week,y=mean,col=region)) + geom_line()  + facet_wrap(~region)

 ggplot(dregions,aes(x=week,y=rate,col=region)) + geom_line()  + facet_wrap(~region)

merge(dregions,infections) -> d

d %>% ggplot(aes(x=,week,col=region)) + geom_line(aes(y=mean)) + geom_line(aes(y=rate), colour="black")  + facet_wrap(~region,  scales = "free_y")

Data

d$predicted_cases<-round(d$mean*d$population,0)
d$prop_cases<-d$cases/d$predicted_cases
aqm::dt(d)