Slide 2: Table 1 Data Visualisation

# Data Visualisation # 1 Table 1
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(scales)

file_path <- "Data_Tables_LGA_Recorded_Offences_Year_Ending_June_2024.xlsx"
table1 <- read_excel(file_path, sheet = "Table 01")

table1 <- table1 %>%
  mutate(Rate_per_100000_population = as.numeric(`Rate per 100,000 population`)) %>%
  rename(Local_Government_Area = `Local Government Area`)

top_5_unique <- table1 %>%
  filter(!is.na(Rate_per_100000_population)) %>%
  group_by(Local_Government_Area) %>%
  filter(Rate_per_100000_population == max(Rate_per_100000_population, na.rm = TRUE)) %>%
  ungroup() %>%
  arrange(desc(Rate_per_100000_population)) %>%
  slice(1:5)

bottom_5_unique <- table1 %>%
  filter(!is.na(Rate_per_100000_population)) %>%
  group_by(Local_Government_Area) %>%
  filter(Rate_per_100000_population == max(Rate_per_100000_population, na.rm = TRUE)) %>%
  ungroup() %>%
  arrange(Rate_per_100000_population) %>%
  slice(1:5)

extreme_lgas_5 <- bind_rows(
  top_5_unique %>% mutate(Category = "Category: Urban (Top 5)"),
  bottom_5_unique %>% mutate(Category = "Regional (Bottom 5)")
)

x_axis_limit <- c(0, max(extreme_lgas_5$Rate_per_100000_population, na.rm = TRUE) * 1.1)

top_annotation <- data.frame(
  Rate_per_100000_population = 14000,
  Local_Government_Area = "Horsham",
  label = "Highest crime rate: Urban concentration of offences",
  Category = "Category: Urban (Top 5)"
)

bottom_annotation <- data.frame(
  Rate_per_100000_population = 4500,
  Local_Government_Area = "Towong",
  label = "Lowest recorded crime rate: Effective prevention or low density",
  Category = "Regional (Bottom 5)"
)

ggplot(extreme_lgas_5, aes(x = Rate_per_100000_population, y = reorder(Local_Government_Area, Rate_per_100000_population), fill = Category)) +
  geom_col(width = 0.7, colour = "black") +
  geom_text(aes(label = comma(Rate_per_100000_population, accuracy = 1)), hjust = -0.2, size = 4) +
  facet_wrap(~Category, scales = "free_y", ncol = 1, strip.position = "top") +
  scale_fill_manual(values = c("Regional (Bottom 5)" = "#56B4E9", "Category: Urban (Top 5)" = "#E69F00"), labels = c("Regional (Bottom 5)", "Top 5")) +
  labs(
    title = "Top and Bottom 5 Crime Rates: Highlights from Urban and Regional LGAs (2024)",
    subtitle = "Crime rates per 100,000 residents: Comparing disparities between urban and regional LGAs (Year Ending Jun 2024).",
    x = "Rate per 100,000 Population",
    y = "Local Government Area (Ordered by Crime Rate)",
    fill = "Category"
  ) +
  geom_text(data = top_annotation, aes(x = Rate_per_100000_population, y = Local_Government_Area, label = label), hjust = 1.2, size = 4, color = "black") +
  geom_text(data = bottom_annotation, aes(x = Rate_per_100000_population, y = Local_Government_Area, label = label), hjust = -0.2, size = 4, color = "black") +
  theme_minimal(base_size = 14) +
  theme(
    plot.title = element_text(hjust = 0.5, size = 16, face = "bold"),
    plot.subtitle = element_text(hjust = 0.5, size = 12),
    strip.text = element_text(size = 14, face = "bold"),
    legend.position = "bottom",
    legend.title = element_text(size = 12),
    legend.text = element_text(size = 10),
    axis.text.y = element_text(size = 12),
    axis.text.x = element_text(size = 10),
    plot.margin = margin(20, 20, 20, 20)
  ) +
  xlim(x_axis_limit)

Slide 2: Table 1 Data Visualisation

Data Visualisation Assignment