Script to create graph "Top 20 Areas affected by Coronavirus per 1000 citizens"

top20subs.R

#### Making barplots of largest affected areas #####

## Libraries
library("ggplot2")
library("dplyr")
library("readxl")

## Read Data
link1 <- "https://data.nsw.gov.au/data/dataset/aefcde60-3b0c-4bc0-9af1-6fe652944ec2/resource/21304414-1ff1-4243-a5d2-f52778048b29/download/covid-19-cases-by-notification-date-and-postcode-local-health-district-and-local-government-area.csv"
link2 <- "https://www.ausstats.abs.gov.au/ausstats/subscriber.nsf/0/2658B9B8CC9269F7CA2584640014C2C4/$File/32350ds0003_lga_2018.xls"
download.file(link2, destfile = "pop.xls")
dat <- read.csv(link1)
dat2 <- readxl::read_excel("pop.xls", sheet = 4, skip = 7, col_names = TRUE)
file.remove("pop.xls")

## Wrangling
dat2 <- dat2[-c(1, 2), c(3, 23)]
colnames(dat2) <- c("lga_code19", "pop")
dat2 <- as.data.frame(sapply(dat2, as.numeric))

### Combine data
cases <- dat %>% 
  group_by(lga_code19) %>%
  summarise(n = length(lga_code19)) %>%
  arrange(-n)
cases <- left_join(cases, dat %>% dplyr::select(lga_code19, lga_name19) %>% unique())
cases <- left_join(cases, dat2) %>% na.omit()
cases <- cases %>%
  mutate(cpc = n / pop) %>%
  arrange(-cpc) %>%
  mutate(lga_name19 = with(., reorder(lga_name19, cpc))) %>%
  mutate(cp1000 = cpc * 1000)

### Cases per Capita
plot1 <- ggplot(cases %>% slice(1:20), aes(x = lga_name19, y = cp1000)) +
  geom_bar(stat = "identity") +
  theme_bw() +
  theme(axis.text.x = element_text(angle = 45, 
                                   hjust = 1)) +
  labs(y = "Coronavirus Cases per 1000 citizens",
       x = "Name of Local Governmental Area (LGA)",
       title = "NSW: Top 20 areas with highest COVID-19 cases per 1000 people",
       subtitle = "Source: [1] Australian Bureau of Statistics, [2] Data.NSW") +
  coord_flip()
ggsave(plot = plot1, filename = "top20subs.png", width = 10, height = 7)