Crime Statistics Agency. (2025). Recorded Offences Visualisation Year Ending 2024. Crime Statistics Agency. https://files.crimestatistics.vic.gov.au/2025-03/Data_Tables_Recorded_Offences_Visualisation_Year_Ending_December_2024.xlsx
---
title: 'Recorded Offences in Victoria: Trends, Locations, and Resolution (Year Ending
December 2024)'
output:
flexdashboard::flex_dashboard:
orientation: columns
vertical_layout: fill
social: menu
source_code: embed
html_document:
df_print: paged
---
```{r setup, include=FALSE}
library(flexdashboard)
library(tidyverse)
library(plotly)
library(DT)
library(RColorBrewer)
library(dplyr)
theme_set(theme_minimal())
```
```{r, include=FALSE}
#setwd("C:/Users/ajayh/OneDrive/Documents/Analytics/Semester 2/Data Visualization")
library(tidyverse)
library(readxl)
df_table_01 <- read_excel("Data_Tables_Recorded_Offences_Visualisation_Year_Ending_December_2024.xlsx", sheet = "Table 01")
df_table_02 <- read_excel("Data_Tables_Recorded_Offences_Visualisation_Year_Ending_December_2024.xlsx", sheet = "Table 02")
df_table_04 <- read_excel("Data_Tables_Recorded_Offences_Visualisation_Year_Ending_December_2024.xlsx", sheet = "Table 04")
clean_colnames <- function(df) {
colnames(df) <- tolower(colnames(df))
colnames(df) <- gsub(" ", "_", colnames(df))
colnames(df) <- gsub("[^a-zA-Z0-9_]", "", colnames(df))
return(df)
}
df_table_01 <- clean_colnames(df_table_01)
df_table_02 <- clean_colnames(df_table_02)
df_table_04 <- clean_colnames(df_table_04)
df_table_01 <- df_table_01 %>% select(-year_ending)
df_table_02 <- df_table_02 %>% select(-year_ending)
df_table_04 <- df_table_04 %>% select(-year_ending)
df_table_01 <- df_table_01 %>%
mutate(
offence_count = parse_number(as.character(offence_count)),
rate_per_100000_population = parse_number(as.character(rate_per_100000_population))
)
df_table_02 <- df_table_02 %>%
mutate(offence_count = parse_number(as.character(offence_count)))
df_table_04 <- df_table_04 %>%
mutate(offence_count = parse_number(as.character(offence_count)))
latest_year <- max(df_table_01$year, na.rm = TRUE)
total_offences_by_year_df <- df_table_01 %>%
group_by(year) %>%
summarise(total_offence_count = sum(offence_count, na.rm = TRUE)) %>%
ungroup()
total_offences_latest_year <- total_offences_by_year_df %>%
filter(year == latest_year) %>%
pull(total_offence_count)
offence_division_counts <- df_table_01 %>%
filter(year == latest_year) %>%
group_by(offence_division) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
arrange(desc(offence_count)) %>%
ungroup()
offence_subdivision_total_counts <- df_table_01 %>%
group_by(offence_subdivision) %>%
summarise(offence_count_overall = sum(offence_count, na.rm = TRUE)) %>%
arrange(desc(offence_count_overall))
top_5_subdivisions <- head(offence_subdivision_total_counts$offence_subdivision, 5)
all_top_subdivisions_trends <- df_table_01 %>%
filter(offence_subdivision %in% top_5_subdivisions) %>%
group_by(year, offence_subdivision) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
ungroup()
offences_by_location_division <- df_table_02 %>%
filter(year == latest_year) %>%
group_by(location_division) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
arrange(desc(offence_count)) %>%
ungroup()
offences_by_location_subdivision <- df_table_02 %>%
filter(year == latest_year) %>%
group_by(location_subdivision) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
arrange(desc(offence_count)) %>%
ungroup()
resolution_status_trend <- df_table_04 %>%
group_by(year, investigation_status) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
ungroup()
resolution_status_latest_year <- df_table_04 %>%
filter(year == latest_year) %>%
group_by(investigation_status) %>%
summarise(offence_count = sum(offence_count, na.rm = TRUE)) %>%
ungroup()
```
Overall Trends and Offense Types
=======================================================================
Row
-----------------------------------------------------------------------
### Total Recorded Offences (Year Ending December 2024)
```{r total_box}
total_offences_latest_year <- total_offences_latest_year %>% as.numeric()
latest_year_dynamic <- total_offences_by_year_df$year[total_offences_by_year_df$year == max(total_offences_by_year_df$year)]
valueBox(total_offences_latest_year,
caption = paste0("Total Recorded Offences (", latest_year_dynamic, ")"),
icon = "fa-balance-scale",
color = "primary")
```
### Total Recorded Offences Over Time
```{r trend_plot}
plot_ly(total_offences_by_year_df,
x = ~year,
y = ~total_offence_count,
type = 'scatter',
mode = 'lines+markers',
hoverinfo = 'text',
text = ~paste('Year:', year, '<br>Offence Count:', total_offence_count)) %>%
layout(title = list(text = "Total Recorded Offences in Victoria (2015–2024)", x = 0.05),
xaxis = list(title = "Year"),
yaxis = list(title = "Total Offence Count")) %>%
config(displayModeBar = FALSE)
```
Row
-----------------------------------------------------------------------
### Top Offence Divisions (Year Ending December 2024)
```{r division_bar}
offence_division_counts <- offence_division_counts %>% dplyr::mutate(offence_division_display = stringr::str_sub(offence_division, 3))
plot_ly(offence_division_counts,
x = ~offence_count,
y = ~reorder(offence_division_display, offence_count),
type = 'bar',
orientation = 'h',
marker = list(color = ~offence_count,
colorscale = 'Plasma',
showscale = TRUE,
colorbar = list(title = "Offence Count"))) %>%
layout(title = list(text = "Offence Counts by Principal Division", x = 0.05),
xaxis = list(title = "Offence Count"),
yaxis = list(title = "Offence Division")) %>%
config(displayModeBar = FALSE)
```
Distribution of The Types of Crimes
=======================================================================
Row {data-height=500}
-----------------------------------------------------------------------
### Trends of Top Offence Subdivisions
```{r top_subdivision_trends}
all_top_subdivisions_trends <- all_top_subdivisions_trends %>% dplyr::mutate(offence_subdivision = stringr::str_sub(offence_subdivision,4))
suppressWarnings({
ggplotly(
ggplot(all_top_subdivisions_trends, aes(x = year, y = offence_count, color = offence_subdivision)) +
geom_line(aes(group = offence_subdivision), size = 1) +
geom_point(size = 2) +
facet_wrap(~ offence_subdivision, scales = "free_y", ncol = 3) +
labs(title = "Trends of Top Offence Subdivisions",
x = "Year",
y = "Offence Count") +
theme_minimal() +
theme(legend.position = "none",
strip.text = element_text(size = 11, face = "bold"),
plot.title = element_text(size = 16, face = "bold"))
) %>% config(displayModeBar = FALSE)
})
```
### Offence Counts by Location Subdivision (Year Ending December 2024)
```{r location_subdivision}
plot_ly(offences_by_location_subdivision %>% head(15),
x = ~offence_count,
y = ~reorder(location_subdivision, offence_count),
type = 'bar',
orientation = 'h',
marker = list(color = ~offence_count,
colorscale = 'Cividis',
showscale = TRUE,
colorbar = list(title = "Offence Count"))) %>%
layout(title = list(text = "Offence Counts by Location Subdivision (Top 15)", x = 0.05),
xaxis = list(title = "Offence Count"),
yaxis = list(title = "Location Subdivision")) %>%
config(displayModeBar = FALSE)
```
Resolution Status
=======================================================================
Row
-----------------------------------------------------------------------
### Offence Resolution Status Over Time
```{r resolution_trend}
plot_ly(resolution_status_trend,
x = ~year,
y = ~offence_count,
color = ~investigation_status,
type = 'bar') %>%
layout(title = list(text = "Offence Resolution Status Over Time", x = 0.05),
xaxis = list(title = "Year", type = "category"),
yaxis = list(title = "Offence Count", rangemode = "tozero"),
barmode = 'stack') %>%
config(displayModeBar = FALSE)
```
### Offence Resolution Status (Year Ending December 2024)
```{r resolution_donut}
plot_ly(resolution_status_latest_year,
labels = ~investigation_status,
values = ~offence_count,
type = 'pie',
hole = 0.6,
marker = list(colors = RColorBrewer::brewer.pal(n = nrow(resolution_status_latest_year), name = "Set3"))) %>%
layout(title = list(text = paste0("Offence Resolution Status (", latest_year_dynamic, ")"), x = 0.05),
showlegend = TRUE,
margin = list(l = 50, r = 50, t = 50, b = 50)) %>%
config(displayModeBar = FALSE)
```
Row{data-width=200}
-----------------------------------------------------------------------
### Reference
Crime Statistics Agency. (2025). Recorded Offences Visualisation Year Ending 2024. Crime Statistics Agency. https://files.crimestatistics.vic.gov.au/2025-03/Data_Tables_Recorded_Offences_Visualisation_Year_Ending_December_2024.xlsx