Care home deaths

temp="care_homes.xlsx"
url="https://www.ons.gov.uk/file?uri=/peoplepopulationandcommunity/birthsdeathsandmarriages/deaths/datasets/numberofdeathsincarehomesnotifiedtothecarequalitycommissionengland/2021/weeklydeathscqcweek32021.xlsx"
temp <- curl_download(url=url, destfile=temp, quiet=FALSE, mode="wb")

library(readxl)
all <- read_excel("care_homes.xlsx", 
    sheet = "Table 4", skip = 2, n_max = 6)[-c(1,2),]
names(all)[2:43]<-as.character(seq(as.Date("2020-4-17"),as.Date("2021-1-29"),by=7))

covid <- read_excel("care_homes.xlsx", 
    sheet = "Table 4", skip = 2, n_max = 13)[-c(1:8),]
names(covid)[2:43]<-as.character(seq(as.Date("2020-4-17"),as.Date("2021-1-29"),by=7))

names(all)<-gsub(" ", "_",names(all))
names(covid)<-gsub(" ", "_",names(covid))
pivot_longer(all,cols=2:43) ->all
pivot_longer(covid,cols=2:43) ->covid

names(all)<-c("Place", "Date", "All")
names(covid)<-c("Place", "Date", "Covid")
d<-merge(all,covid)
d %>% filter(Place %in% c("Care Home", "Hospital")) -> d
d$Date<-as.Date(d$Date)

d %>% arrange(Date) %>% ggplot(aes(x=Date,y=Covid/All, col=Place))  +geom_line()

library(dygraphs)
library(xts)

## Loading required package: zoo

## 
## Attaching package: 'zoo'

## The following objects are masked from 'package:base':
## 
##     as.Date, as.Date.numeric

## 
## Attaching package: 'xts'

## The following objects are masked from 'package:dplyr':
## 
##     first, last

str(d)

## 'data.frame':    84 obs. of  4 variables:
##  $ Place: chr  "Care Home" "Care Home" "Care Home" "Care Home" ...
##  $ Date : Date, format: "2020-04-17" "2020-04-24" ...
##  $ All  : num  7028 6634 5584 4027 3767 ...
##  $ Covid: num  2438 2769 2440 1752 1536 ...

d %>% pivot_wider(-3,names_from=Place,values_from = Covid) -> dd
dd<-xts(dd[,2:3], order.by = dd$Date)

dd %>% dygraph() %>% dyRangeSelector() %>%  dyRoller(rollPeriod = 1)

las <- read_excel("care_homes.xlsx", 
    sheet = "Table 2", skip = 2, n_max = 151)

## New names:
## * `` -> ...1

names(las)[1]<-"Laname"
las<-pivot_longer(las,-1)
names(las)[2:3]<-c("Date","Care_deaths")
las$Date<-janitor::excel_numeric_to_date(as.numeric(las$Date))

mutate(las, wave=ifelse(Date<"2020-8-15","wave1","wave2")) ->las
las %>% group_by(Laname,wave) %>% summarise(Care_deaths=sum(Care_deaths)) ->las_tot

## `summarise()` has grouped output by 'Laname'. You can override using the `.groups` argument.

las_tot %>% pivot_wider(names_from = wave,values_from = Care_deaths) ->las_tot

las_tot %>% filter(Laname != "England") %>%
ggplot(aes(x=wave1,y=wave2,label=Laname)) +geom_text(size=2) + geom_smooth(method="lm")

## `geom_smooth()` using formula 'y ~ x'

# library(giscourse)
# con<-sconnect11()
# lashex<-st_read(con,"lashex")
# backhex<-st_read(con,"backhex")
# disconnect(con)
# lashex %>% inner_join(las_tot) ->las_geo
# library(RColorBrewer)
# colors <- brewer.pal(7, "Reds")
# qtm(backhex) +
# tm_shape(las_geo) +
#   tm_fill("Care_deaths", palette = colors, midpoint = NA) + tm_borders(lwd = 0)