Slide 3: Table 2 Data Visualisation

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(ggplot2)
library(readxl)
library(viridis)

## Loading required package: viridisLite

file_path <- "Data_Tables_LGA_Recorded_Offences_Year_Ending_June_2024.xlsx"
data <- read_excel(file_path, sheet = "Table 02")

data <- data %>%
  rename(
    Local.Government.Area = `Local Government Area`,
    Offence.Subdivision = `Offence Subdivision`,
    Offence.Count = `Offence Count`
  )

top_lgas <- data %>%
  group_by(Local.Government.Area) %>%
  summarise(Total.Offence.Count = sum(Offence.Count, na.rm = TRUE)) %>%
  arrange(desc(Total.Offence.Count)) %>%
  slice(1:3) %>%
  pull(Local.Government.Area)

filtered_data <- data %>%
  filter(Local.Government.Area %in% top_lgas)

top_offences <- filtered_data %>%
  group_by(Local.Government.Area, Offence.Subdivision) %>%
  summarise(Total.Offence.Count = sum(Offence.Count, na.rm = TRUE), .groups = "drop") %>%
  arrange(Local.Government.Area, desc(Total.Offence.Count)) %>%
  group_by(Local.Government.Area) %>%
  slice(1:3) %>%
  ungroup()

filtered_data <- filtered_data %>%
  semi_join(top_offences, by = c("Local.Government.Area", "Offence.Subdivision"))

plot_data <- filtered_data %>%
  group_by(Local.Government.Area, Offence.Subdivision) %>%
  summarise(Total.Offence.Count = sum(Offence.Count), .groups = "drop")

ggplot(plot_data, aes(x = Offence.Subdivision, y = Total.Offence.Count, fill = Offence.Subdivision)) +
  geom_bar(stat = "identity", position = position_dodge(), width = 0.6, colour = "black") +
  geom_text(aes(label = Total.Offence.Count), vjust = -0.5, size = 4, color = "black") +
  facet_wrap(~Local.Government.Area, scales = "free_x", ncol = 3) +
  scale_fill_viridis(discrete = TRUE, option = "C") +
  labs(
    title = "Top 3 Offence Subdivisions by Offence Count for Selected LGAs",
    subtitle = "Highlighting key offence subdivisions—Theft and Deception—across the top 3 LGAs with the highest offence counts",
    x = "Top Offence Types",
    y = "Total Offence Count",
    fill = "Offence Subdivision"
  ) +
  theme_minimal(base_size = 14) +
  theme(
    plot.title = element_text(hjust = 0.5, size = 18, face = "bold"),
    plot.subtitle = element_text(hjust = 0.5, size = 14),
    axis.text.x = element_text(angle = 20, hjust = 1, vjust = 1, size = 12),
    axis.title.x = element_text(margin = margin(t = 15)),
    legend.position = "bottom",
    legend.title = element_text(size = 12),
    legend.text = element_text(size = 10),
    strip.text = element_text(size = 14, face = "bold", margin = margin(b = 10)),
    panel.grid.major = element_line(linewidth = 0.8, colour = "grey80"),
    panel.grid.minor = element_blank(),
    panel.background = element_rect(fill = "white", colour = NA),
    plot.background = element_rect(fill = "grey95", colour = NA)
  ) +
  guides(fill = guide_legend(override.aes = list(alpha = 1)))

Slide 3: Table 2 Data Visualisation

Data Visualisation Assignment