# ---- DATA ----------------------------------------------------------------
# Downloaded from https://www.crimestatistics.vic.gov.au/ (Download crime data ->
# "Data Tables LGA Criminal Incidents Year Ending June 2026") and saved in /data.
file <- list.files(pattern = "Criminal_Incidents")[1]
# Table 01: incidents and rate per 100,000 by LGA and year
lga_raw <- read_excel(file, sheet = "Table 01")
# Table 05: incidents by charge status (Charges laid / No charges laid / Unsolved)
status_raw <- read_excel(file, sheet = "Table 05")
# ---- PREPROCESSING -------------------------------------------------------
# 1. Rename columns to short names
# 2. Trim stray spaces from LGA names
# 3. Remove non-LGA rows (state Total, Justice Institutions, Unincorporated Vic)
# 4. Remove the number prefix from police region names ("1 North West Metro")
not_lga <- c("Total", "Justice Institutions and Immigration Facilities",
"Unincorporated Vic")
lga <- lga_raw %>%
rename(year = Year, region = `Police Region`, lga = `Local Government Area`,
incidents = `Incidents Recorded`, rate = `Rate per 100,000 population`) %>%
mutate(lga = str_trim(lga), region = str_remove(region, "^\\d+\\s*")) %>%
filter(!lga %in% not_lga) %>%
select(year, region, lga, incidents, rate)
status <- status_raw %>%
rename(year = Year, lga = `Local Government Area`,
status = `Charge Status`, incidents = `Incidents Recorded`) %>%
mutate(lga = str_trim(lga)) %>%
filter(!lga %in% not_lga) %>%
select(year, lga, status, incidents)
# 5. Victoria-wide share of each outcome per year
vic_share <- status %>%
group_by(year, status) %>%
summarise(incidents = sum(incidents), .groups = "drop") %>%
group_by(year) %>%
mutate(share = incidents / sum(incidents) * 100) %>%
ungroup()
# 6. Unsolved % per LGA per year, joined with region, crime rate and volume
lga_unsolved <- status %>%
group_by(year, lga) %>%
summarise(total = sum(incidents),
unsolved = sum(incidents[status == "Unsolved"]),
charged = sum(incidents[status == "Charges laid"]),
.groups = "drop") %>%
mutate(unsolved_pct = unsolved / total * 100,
charged_pct = charged / total * 100) %>%
inner_join(lga, by = c("year", "lga"))
first_yr <- min(vic_share$year); last_yr <- max(vic_share$year)Here’s something most people don’t realise: over half of the crime recorded in Victoria in 2025–26 went unsolved. Crime has risen about 15% since 2017, yet fewer cases end in charges than before.
Look at the orange line: in 2025 it crosses the 50% mark, while the share of cases where someone was charged drops from 39% to 30%.
p1 <- vic_share %>%
mutate(status = factor(status, levels = names(col_status))) %>%
ggplot(aes(year, share, colour = status, group = status,
text = paste0("<b>", status, "</b><br>Year ending June ", year,
"<br>", round(share, 1), "% of incidents",
"<br>", comma(incidents), " incidents"))) +
geom_hline(yintercept = 50, linetype = "dashed", colour = "grey60") +
geom_line(linewidth = 1.2) +
geom_point(size = 2.5) +
scale_colour_manual(values = col_status) +
scale_x_continuous(breaks = first_yr:last_yr) +
scale_y_continuous(limits = c(0, 60), labels = function(x) paste0(x, "%")) +
labs(title = "Outcome of recorded criminal incidents, Victoria",
x = NULL, y = "Share of incidents", colour = NULL, caption = src) +
theme_story
ggplotly(p1, tooltip = "text") %>%
layout(legend = list(orientation = "h", y = -0.15),
hovermode = "x unified")Since 2017, unsolved cases have gone up by about a third, but charges laid have actually gone down. Most of that gap opened up after 2024.
index_df <- status %>%
group_by(year, status) %>%
summarise(n = sum(incidents), .groups = "drop") %>%
bind_rows(status %>% group_by(year) %>%
summarise(n = sum(incidents)) %>%
mutate(status = "All incidents")) %>%
group_by(status) %>%
arrange(year) %>%
mutate(index = n / first(n) * 100) %>%
ungroup() %>%
filter(status != "No charges laid") %>%
mutate(status = factor(status, levels = c("All incidents", "Unsolved", "Charges laid")))
p2 <- ggplot(index_df, aes(year, index, colour = status, group = status,
text = paste0("<b>", status, "</b><br>", year,
"<br>Index: ", round(index),
"<br>Count: ", comma(n)))) +
geom_hline(yintercept = 100, colour = "grey60") +
geom_line(linewidth = 1.2) +
geom_point(size = 2.5) +
scale_colour_manual(values = c("All incidents" = "grey40",
"Unsolved" = "#D55E00",
"Charges laid" = "#0072B2")) +
scale_x_continuous(breaks = first_yr:last_yr) +
labs(title = paste0("Change since ", first_yr, " (", first_yr, " = 100)"),
x = NULL, y = "Index", colour = NULL, caption = src) +
theme_story
ggplotly(p2, tooltip = "text") %>%
layout(legend = list(orientation = "h", y = -0.15))Every region is worse than it was in 2017, but Melbourne is hit hardest. In North West Metro, unsolved cases went from 53% to 61%, while Western Victoria barely changed (42% to 44%). Each thin grey line is one LGA; the bold line is the region overall.
region_line <- lga_unsolved %>%
group_by(year, region) %>%
summarise(unsolved_pct = sum(unsolved) / sum(total) * 100, .groups = "drop")
p3 <- ggplot() +
geom_line(data = lga_unsolved,
aes(year, unsolved_pct, group = lga,
text = paste0(lga, "<br>", year, ": ", round(unsolved_pct, 1), "% unsolved")),
colour = "grey80", linewidth = 0.4) +
geom_line(data = region_line,
aes(year, unsolved_pct, colour = region, group = region,
text = paste0("<b>", region, " (all LGAs)</b><br>", year, ": ",
round(unsolved_pct, 1), "% unsolved")),
linewidth = 1.4) +
facet_wrap(~ region, ncol = 2) +
scale_colour_manual(values = col_region, guide = "none") +
scale_x_continuous(breaks = c(2017, 2020, 2023, 2026)) +
scale_y_continuous(labels = function(x) paste0(x, "%")) +
labs(title = "Share of incidents unsolved, by police region and LGA",
x = NULL, y = "Unsolved", caption = src) +
theme_story
ggplotly(p3, tooltip = "text")Not really. Press play and you’ll see it’s more about city vs country than crime rate. Regional towns like Horsham and Warrnambool solve far more cases than inner-Melbourne areas like Maribyrnong and Stonnington, where about two in three crimes stay unsolved. Bubble size shows the number of incidents.
plot_ly(lga_unsolved,
x = ~rate, y = ~unsolved_pct, size = ~incidents,
color = ~region, colors = col_region,
frame = ~year, ids = ~lga,
type = "scatter", mode = "markers",
sizes = c(8, 60),
marker = list(opacity = 0.7, line = list(width = 0.5, color = "white")),
text = ~paste0("<b>", lga, "</b> (", region, ")",
"<br>Crime rate: ", comma(round(rate)), " per 100,000",
"<br>Unsolved: ", round(unsolved_pct, 1), "%",
"<br>Incidents: ", comma(incidents)),
hoverinfo = "text") %>%
layout(title = list(text = "<b>Crime rate vs share unsolved, by LGA</b>", x = 0),
xaxis = list(title = "Criminal incidents per 100,000 population",
type = "log"),
yaxis = list(title = "Unsolved (%)", ticksuffix = "%"),
legend = list(orientation = "h", y = -0.25)) %>%
animation_opts(frame = 900, transition = 400, redraw = FALSE) %>%
animation_slider(currentvalue = list(prefix = "Year ending June ",
font = list(size = 14)))Pick an area from the dropdown to see how it compares with the Victorian average (the dashed line).
lgas <- sort(unique(lga_unsolved$lga))
default_lga <- "Melbourne" # change to any LGA
vic_line <- vic_share %>% filter(status == "Unsolved")
p5 <- plot_ly()
for (l in lgas) {
d <- filter(lga_unsolved, lga == l)
p5 <- add_trace(p5, data = d, x = ~year, y = ~unsolved_pct,
type = "scatter", mode = "lines+markers", name = l,
visible = (l == default_lga),
line = list(color = "#D55E00", width = 3),
marker = list(color = "#D55E00", size = 8),
text = ~paste0("<b>", lga, "</b><br>", year,
"<br>Unsolved: ", round(unsolved_pct, 1), "%",
"<br>Charges laid: ", round(charged_pct, 1), "%"),
hoverinfo = "text")
}
p5 <- add_trace(p5, data = vic_line, x = ~year, y = ~share,
type = "scatter", mode = "lines", name = "Victoria",
line = list(color = "grey50", width = 2, dash = "dash"),
text = ~paste0("<b>Victoria</b><br>", year, "<br>Unsolved: ",
round(share, 1), "%"),
hoverinfo = "text")
buttons <- lapply(seq_along(lgas), function(i) {
list(method = "restyle", label = lgas[i],
args = list("visible", as.list(c(seq_along(lgas) == i, TRUE))))
})
p5 %>%
layout(margin = list(t = 120),
title = list(text = "<b>Share of incidents unsolved: your LGA vs Victoria</b>",
x = 0, xref = "paper",
y = 1.02, yref = "paper", yanchor = "bottom"),
xaxis = list(title = "", dtick = 1),
yaxis = list(title = "Unsolved (%)", ticksuffix = "%", rangemode = "tozero"),
showlegend = FALSE,
updatemenus = list(list(type = "dropdown", x = 0, y = 1.2,
xanchor = "left", yanchor = "bottom",
buttons = buttons,
active = which(lgas == default_lga) - 1)),
annotations = list(list(text = "Dashed = Victoria", x = 1, y = 1.2,
xref = "paper", yref = "paper",
yanchor = "bottom",
showarrow = FALSE, xanchor = "right")))The problem isn’t just that there’s more crime. It’s that more of it ends with nobody being caught, and it’s worst in Melbourne and in the last two years. Some recent cases are still being investigated, so the 2026 number might drop a bit. But the overall trend is clear, and it’s worth asking whether police resources have kept up with demand.
Crime Statistics Agency. (2026). Data tables: LGA criminal incidents, year ending June 2026 [Data set]. https://www.crimestatistics.vic.gov.au/
Sievert, C. (2020). Interactive web-based data visualization with R, plotly, and shiny. Chapman and Hall/CRC. https://plotly-r.com
Wickham, H., et al. (2019). Welcome to the tidyverse. Journal of Open Source Software, 4(43), 1686. https://doi.org/10.21105/joss.01686
Anthropic. (2026). Claude [Large language model]. https://claude.ai
I used Claude (Anthropic, 2026) to help write and debug the R code, organise the report, and improve grammar and clarity. I chose the topic and dataset and decided on the story the report tells.