library(tidyverse)
library(readxl)
library(janitor)
library(extrafont)
library(ggrepel)

pm25 <- read_xlsx("~/Downloads/Population_exceeding_LAEI2016.xlsx",
          sheet = "Population_Weighted_Avg_PM2.5",
          range = "A6:B39") |>
  rename(borough = Borough,
         pm25 = `LAEI 2016`)

admit <- read_xls("~/Downloads/hospital-admissions-rates-borough.xls",
                  sheet = "2014-15",
                  range = "A1:J51") |>
  clean_names() |>
  select(code,
         area,
         rate = indirectly_age_and_sex_standardised_rate_per_100_000)

# Clean the files ---------------------------------------------------------

to_drop <- c(
  "Inner London",
  "Outer London",
  "North East",
  "North West",
  "Yorkshire and the Humber",
  "East Midlands",
  "West Midlands",
  "East of England",
  "London",
  "South East",
  "South West",
  "England"
)

admit <- admit |>
  drop_na(code) |>
  filter(!(area %in% to_drop)) |>
  rename(borough = area)

pm25 <- pm25 |>
  mutate(borough = str_replace(borough, "Of", "of"),
         borough = str_replace(borough, "Upon", "upon"))

d <- full_join(admit, pm25)

summary(d)

d |>
  ggplot() +
  aes(x = pm25,
      label = borough,
      y = rate) +
  geom_point() +
  geom_text_repel() +
  geom_smooth(method = "lm", se = FALSE)