library(readxl)
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(plotly)

## 
## Attaching package: 'plotly'

## The following object is masked from 'package:ggplot2':
## 
##     last_plot

## The following object is masked from 'package:stats':
## 
##     filter

## The following object is masked from 'package:graphics':
## 
##     layout

crime <- read_excel("/Users/nishanthyn/Downloads/Data_Tables_LGA_Recorded_Offences_Year_Ending_June_2025.xlsx", sheet = "Table 01")

crime <- crime %>%
  rename(Year = `Year`,
         Year_Ending = `Year ending`,
         Region = `Police Region`,
         LGA = `Local Government Area`,
         Offence_Count = `Offence Count`,
         Rate_per_100k = `Rate per 100,000 population`) %>%
  mutate(Year = as.numeric(Year))

#Slide 1 – Title

#Changing Crime Patterns in Victoria (2015 – 2025)
#An interactive data story using Crime Statistics Agency (CSA) open data.

FALSE && {
  #Slide 2 – About the Data
## About the Data

#Dataset:** Recorded Offences by Local Government Area (LGA), Victoria  
#Table used:** *Table 01 – Recorded Offences by Offence Division, LGA, Year ending June 2025*  
#Time span:** 2015 – 2025 (financial year basis; “Year ending June”)  
#Geography:** Victoria, Australia — aggregated at **Police Region** and **LGA**  
#Key fields used:**  
#`Year` (numeric), `Year ending` (text), `Police Region`, `Local Government Area`  
#`Offence Count` (number of recorded offences)  
#`Rate per 100,000 population` (offences per 100k residents)
#Source:** Crime Statistics Agency (CSA) Victoria — Latest crime data by area  
#Link: <https://www.crimestatistics.vic.gov.au>
#How rates are defined:** CSA calculates **offences per 100,000 population** using ABS population estimates for the reference year.
#*Cleaning steps (this analysis):**  
#Loaded Excel → selected **Table 01**  
#Standardised column names; coerced `Year` to integer  
#Removed aggregate rows like **“Total”** when comparing LGAs  
#For duplicates within an LGA–Year, used mean (equivalent if identical)
#Limitations / caveats:**  
# Recorded offences ≠ actual incidence (reporting and policing practices can affect counts)  
#Boundary changes or LGA reclassifications across years can affect comparability  
#Rates depend on population estimates; small LGAs can show volatile rates
#Use & licence:** Public data for educational use; cite **CSA Victoria** as the source.##
}

## [1] FALSE

#Slide 3 – Total Recorded Offences (2015–2025)

total_trend <- crime %>%
group_by(Year) %>%
summarise(Total = sum(Offence_Count, na.rm = TRUE))

p1 <- ggplot(total_trend, aes(x = Year, y = Total)) +
geom_line(color = "steelblue", linewidth = 1.2) +
geom_point() +
labs(title = "Total Recorded Offences (2015–2025)",
x = "Year", y = "Total Offences") +
theme_minimal()
ggplotly(p1)

#Slide 4 – Crime by Police Region (2025)

latest <- filter(crime, Year == 2025)
p2 <- ggplot(latest, aes(x = Region, y = Offence_Count, fill = Region)) +
geom_boxplot() +
labs(title = "Offence Counts by Police Region – 2025",
x = "Police Region", y = "Offence Count") +
theme_minimal() + theme(legend.position = "none")
ggplotly(p2)

#Slide 5 – Top 5 LGAs with Highest Crime Rates (2025)

top5 <- latest %>%
arrange(desc(Rate_per_100k)) %>%
slice_head(n = 5)
p3 <- ggplot(top5, aes(x = reorder(LGA, Rate_per_100k), y = Rate_per_100k)) +
geom_col(fill = "tomato") +
coord_flip() +
labs(title = "Top 5 LGAs by Crime Rate (2025)",
x = "Local Government Area", y = "Rate per 100k") +
theme_minimal()
ggplotly(p3)

Slide 6 – LGAs Showing Greatest Improvement (2015 → 2025)

Slide 6 – LGAs Showing Greatest Improvement (2015 → 2025)

Slide – Regional Summary

## Warning: Removed 20 rows containing missing values or values outside the scale range
## (`geom_line()`).

#Slide 8 – Regional Trends (2015–2025)

p6 <- ggplot(avg_rate, aes(x = Year, y = AvgRate, color = Region)) +
geom_line() +
facet_wrap(~Region) +
labs(title = "Regional Crime Trends (2015–2025)") +
theme_minimal()
p6

## Warning: Removed 20 rows containing missing values or values outside the scale range
## (`geom_line()`).

knitr::opts_chunk$set(eval = FALSE, include = FALSE)
#Slide 9 – Key Insights
#Total offences peaked around 2020 then declined post-COVID.
#Regional areas show higher average rates but slower change.
#Metro LGAs like Melbourne and Casey remain consistent hotspots.
#Policy efforts post-2021 align with visible reductions in some regions.