This file shows the wrangling to raw files taken from:
These files are used to summarise the number of care homes in each nation and to calculate the total number of beds available.
Raw code can be downloaded by selecting the Download Rmd option from the Code drop down menu at the top of this page.
R packages required and helper functions
library(tidyverse)
library(curl)
library(readxl)
library(forcats)
library(ggthemes)
library(here)
#Helper function
`%nin%` <- negate(`%in%`)
#Short cut for csv output with html tables
my_datatable <- function(x){
DT::datatable(x, extensions = "Buttons", options = list(dom = "Bfrtip",
buttons = c("csv")))
}These files are stored locally (the English data is quite big) and are available on the project GitHub pages in the raw_data folder.
eng <- read_xlsx(here("raw_data/ENGLAND_care_homes.xlsx"), sheet = "HSCA Active Locations")
scot <- read_xlsx(here("raw_data/SCOTLAND_care_homes.xlsx"), sheet = "MDSF_data_31 May 2020")
ni <- read_xlsx(here("raw_data/NI_care_homes.xlsx"))
w <- read_xlsx(here("raw_data/22 - CIW - 20200730 - list of adult care homes in Wales.xlsx"),
range = "A4:K1060")The Care Quality Commission data contains a lot of information, not just about care homes. For our purposes we will filter using the convenient Care home? variable and also to include services that come under the bracket of “Adult social care”. We then keep only the required variables and drop the rest before adding a country variable to indicate this is English data.
eng %<>%
filter(`Care home?` == "Y" &
`Location Inspection Directorate` == "Adult social care") %>%
select(`Location Name`, `Care homes beds`, `Service user band - Older People`) %>%
mutate(country = "England")
my_datatable(eng)A similar story with the Scottish Care Inspectorate data. This time we use the “Care Home Service” and keep all service subtypes except “Children & Young People” and drop all inactive services. Again we add the country variable.
scot %<>%
filter(CareService == "Care Home Service" &
Subtype %nin% "Children & Young People" &
ServiceStatus == "Active") %>%
select(`Location Name` = ServiceName,
`Care homes beds` = TotalBeds, Subtype) %>%
mutate(country = "Scotland")
my_datatable(scot)This time keep all Nursing home and Residential home categories and add the country variable.
ni %<>%
filter(Category %in% c("Nursing (NH)", "Residential (RC)")) %>%
select(`Location Name` = ServiceName,
`Care homes beds` = `Max Approved Places`,
`Categories of Care`) %>%
mutate(country = "Northern Ireland")
my_datatable(ni)Data here is a little more sparse, we just drop the uneeded variables from the Care Inspectorate Wales file. CIW no longer distinguish between care homes for older and younger adults due to a change in the registrations process.
w %<>%
select(`Location Name` = `Service Name`,
`Care homes beds` = `Maximum Capacity`,
`Service Sub Type`) %>%
mutate(country = "Wales")
my_datatable(w)Now we create a uk dataframe by binding the previous 4 files together and then coercing the country varaible to a factor to make it play better with plots.
uk <- bind_rows(eng, scot, w, ni) %>%
mutate(country = factor(country,
levels = c("England", "Scotland", "Wales", "Northern Ireland")))
my_datatable(uk)This object is now written to a derived_data folder so we can load it in to another file and start analysing.
feather::write_feather(uk, here("derived_data/uk.feather"))devtools::session_info()## ─ Session info ────────────────────────────────────────
## setting value
## version R version 4.1.0 (2021-05-18)
## os macOS Big Sur 11.4
## system aarch64, darwin20
## ui RStudio
## language (EN)
## collate en_GB.UTF-8
## ctype en_GB.UTF-8
## tz Europe/London
## date 2021-06-14
##
## ─ Packages ────────────────────────────────────────────
## package * version date lib source
## assertthat 0.2.1 2019-03-21 [1] CRAN (R 4.1.0)
## backports 1.2.1 2020-12-09 [1] CRAN (R 4.1.0)
## broom 0.7.7 2021-06-13 [1] CRAN (R 4.1.0)
## bslib 0.2.5.1 2021-05-18 [1] CRAN (R 4.1.0)
## cachem 1.0.5 2021-05-15 [1] CRAN (R 4.1.0)
## callr 3.7.0 2021-04-20 [1] CRAN (R 4.1.0)
## cellranger 1.1.0 2016-07-27 [1] CRAN (R 4.1.0)
## cli 2.5.0 2021-04-26 [1] CRAN (R 4.1.0)
## colorspace 2.0-1 2021-05-04 [1] CRAN (R 4.1.0)
## crayon 1.4.1 2021-02-08 [1] CRAN (R 4.1.0)
## crosstalk 1.1.1 2021-01-12 [1] CRAN (R 4.1.0)
## curl * 4.3.1 2021-04-30 [1] CRAN (R 4.1.0)
## DBI 1.1.1 2021-01-15 [1] CRAN (R 4.1.0)
## dbplyr 2.1.1 2021-04-06 [1] CRAN (R 4.1.0)
## desc 1.3.0 2021-03-05 [1] CRAN (R 4.1.0)
## devtools 2.4.2 2021-06-07 [1] CRAN (R 4.1.0)
## digest 0.6.27 2020-10-24 [1] CRAN (R 4.1.0)
## dplyr * 1.0.6 2021-05-05 [1] CRAN (R 4.1.0)
## DT 0.18 2021-04-14 [1] CRAN (R 4.1.0)
## ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.1.0)
## evaluate 0.14 2019-05-28 [1] CRAN (R 4.1.0)
## fansi 0.5.0 2021-05-25 [1] CRAN (R 4.1.0)
## fastmap 1.1.0 2021-01-25 [1] CRAN (R 4.1.0)
## feather 0.3.5 2019-09-15 [1] CRAN (R 4.1.0)
## forcats * 0.5.1 2021-01-27 [1] CRAN (R 4.1.0)
## fs 1.5.0 2020-07-31 [1] CRAN (R 4.1.0)
## generics 0.1.0 2020-10-31 [1] CRAN (R 4.1.0)
## ggplot2 * 3.3.3 2020-12-30 [1] CRAN (R 4.1.0)
## ggthemes * 4.2.4 2021-01-20 [1] CRAN (R 4.1.0)
## glue 1.4.2 2020-08-27 [1] CRAN (R 4.1.0)
## gtable 0.3.0 2019-03-25 [1] CRAN (R 4.1.0)
## haven 2.4.1 2021-04-23 [1] CRAN (R 4.1.0)
## here * 1.0.1 2020-12-13 [1] CRAN (R 4.1.0)
## hms 1.1.0 2021-05-17 [1] CRAN (R 4.1.0)
## htmltools 0.5.1.1 2021-01-22 [1] CRAN (R 4.1.0)
## htmlwidgets 1.5.3 2020-12-10 [1] CRAN (R 4.1.0)
## httr 1.4.2 2020-07-20 [1] CRAN (R 4.1.0)
## jquerylib 0.1.4 2021-04-26 [1] CRAN (R 4.1.0)
## jsonlite 1.7.2 2020-12-09 [1] CRAN (R 4.1.0)
## knitr 1.33 2021-04-24 [1] CRAN (R 4.1.0)
## lifecycle 1.0.0 2021-02-15 [1] CRAN (R 4.1.0)
## lubridate 1.7.10 2021-02-26 [1] CRAN (R 4.1.0)
## magrittr 2.0.1 2020-11-17 [1] CRAN (R 4.1.0)
## memoise 2.0.0 2021-01-26 [1] CRAN (R 4.1.0)
## modelr 0.1.8 2020-05-19 [1] CRAN (R 4.1.0)
## munsell 0.5.0 2018-06-12 [1] CRAN (R 4.1.0)
## pillar 1.6.1 2021-05-16 [1] CRAN (R 4.1.0)
## pkgbuild 1.2.0 2020-12-15 [1] CRAN (R 4.1.0)
## pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.1.0)
## pkgload 1.2.1 2021-04-06 [1] CRAN (R 4.1.0)
## prettyunits 1.1.1 2020-01-24 [1] CRAN (R 4.1.0)
## processx 3.5.2 2021-04-30 [1] CRAN (R 4.1.0)
## ps 1.6.0 2021-02-28 [1] CRAN (R 4.1.0)
## purrr * 0.3.4 2020-04-17 [1] CRAN (R 4.1.0)
## R6 2.5.0 2020-10-28 [1] CRAN (R 4.1.0)
## Rcpp 1.0.6 2021-01-15 [1] CRAN (R 4.1.0)
## readr * 1.4.0 2020-10-05 [1] CRAN (R 4.1.0)
## readxl * 1.3.1 2019-03-13 [1] CRAN (R 4.1.0)
## rematch 1.0.1 2016-04-21 [1] CRAN (R 4.1.0)
## remotes 2.4.0 2021-06-02 [1] CRAN (R 4.1.0)
## reprex 2.0.0 2021-04-02 [1] CRAN (R 4.1.0)
## rlang 0.4.11 2021-04-30 [1] CRAN (R 4.1.0)
## rmarkdown * 2.8 2021-05-07 [1] CRAN (R 4.1.0)
## rprojroot 2.0.2 2020-11-15 [1] CRAN (R 4.1.0)
## rstudioapi 0.13 2020-11-12 [1] CRAN (R 4.1.0)
## rvest 1.0.0 2021-03-09 [1] CRAN (R 4.1.0)
## sass 0.4.0 2021-05-12 [1] CRAN (R 4.1.0)
## scales 1.1.1 2020-05-11 [1] CRAN (R 4.1.0)
## sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 4.1.0)
## stringi 1.6.2 2021-05-17 [1] CRAN (R 4.1.0)
## stringr * 1.4.0 2019-02-10 [1] CRAN (R 4.1.0)
## testthat 3.0.2 2021-02-14 [1] CRAN (R 4.1.0)
## tibble * 3.1.2 2021-05-16 [1] CRAN (R 4.1.0)
## tidyr * 1.1.3 2021-03-03 [1] CRAN (R 4.1.0)
## tidyselect 1.1.1 2021-04-30 [1] CRAN (R 4.1.0)
## tidyverse * 1.3.1 2021-04-15 [1] CRAN (R 4.1.0)
## usethis 2.0.1 2021-02-10 [1] CRAN (R 4.1.0)
## utf8 1.2.1 2021-03-12 [1] CRAN (R 4.1.0)
## vctrs 0.3.8 2021-04-29 [1] CRAN (R 4.1.0)
## withr 2.4.2 2021-04-18 [1] CRAN (R 4.1.0)
## xfun 0.23 2021-05-15 [1] CRAN (R 4.1.0)
## xml2 1.3.2 2020-04-23 [1] CRAN (R 4.1.0)
## yaml 2.2.1 2020-02-01 [1] CRAN (R 4.1.0)
##
## [1] /Library/Frameworks/R.framework/Versions/4.1-arm64/Resources/library