1 Introduction

This report explores recorded offences in Victoria for the years ending June 2017–2026. It compares broad offence divisions, selected property-offence subgroups, and two theft categories: stealing from a motor vehicle (B42) and stealing from a retail store (B43).

The workbook is the source for the counts and rates shown below. Counts are recorded offences, not necessarily the number of victims or unique incidents.

2 Load and check the workbook

Keep this R Markdown file in the same folder as the Excel workbook:

Data_Tables_Recorded_Offences_Visualisation_Year_Ending_June_2026.xlsx

file_path <- "Data_Tables_Recorded_Offences_Visualisation_Year_Ending_June_2026.xlsx"

if (!file.exists(file_path)) {
  stop(
    "Excel workbook not found. Put this .Rmd file in the same folder as the workbook.\n",
    "Current working directory: ", getwd(), "\n",
    "Expected filename: ", file_path
  )
}

sheet_names <- readxl::excel_sheets(file_path)
if (!all(c("Table 01", "Table 05") %in% sheet_names)) {
  stop("Expected sheets 'Table 01' and 'Table 05'. Found: ",
       paste(sheet_names, collapse = ", "))
}

table01_raw <- readxl::read_excel(file_path, sheet = "Table 01")
table05_raw <- readxl::read_excel(file_path, sheet = "Table 05")

required01 <- c("Year", "Offence Division", "Offence Subdivision",
                "Offence Subgroup", "Offence Count",
                "Rate per 100,000 population")
required05 <- c("Year", "Offence Subgroup", "Offence Code",
                "Offence Description", "Offence Count")

missing01 <- setdiff(required01, names(table01_raw))
missing05 <- setdiff(required05, names(table05_raw))
if (length(missing01)) stop("Missing columns in Table 01: ", paste(missing01, collapse = ", "))
if (length(missing05)) stop("Missing columns in Table 05: ", paste(missing05, collapse = ", "))

table01 <- table01_raw %>%
  mutate(
    Year = suppressWarnings(as.integer(Year)),
    `Offence Division` = as.character(`Offence Division`),
    `Offence Subdivision` = as.character(`Offence Subdivision`),
    `Offence Subgroup` = as.character(`Offence Subgroup`),
    `Offence Count` = suppressWarnings(as.numeric(`Offence Count`)),
    `Rate per 100,000 population` = suppressWarnings(as.numeric(`Rate per 100,000 population`))
  ) %>%
  filter(Year >= 2017, Year <= 2026)

table05 <- table05_raw %>%
  mutate(
    Year = suppressWarnings(as.integer(Year)),
    `Offence Subgroup` = as.character(`Offence Subgroup`),
    `Offence Code` = as.character(`Offence Code`),
    `Offence Count` = suppressWarnings(as.numeric(`Offence Count`))
  ) %>%
  filter(Year >= 2017, Year <= 2026)

if (nrow(table01) == 0 || nrow(table05) == 0) {
  stop("No records for years 2017–2026 were found. Check the Year column in the workbook.")
}

cat("Table 01 rows for selected years:", nrow(table01), "\n")
## Table 01 rows for selected years: 1130
cat("Table 05 rows for selected years:", nrow(table05), "\n")
## Table 05 rows for selected years: 17574

3 1. Recorded offences across Victoria

This chart calculates annual totals from the division-level rows in Table 01. It filters to division labels beginning with A–F and does not add subdivision or subgroup rows on top of those division totals.

division_rows <- table01 %>%
  filter(str_detect(str_trim(`Offence Division`), "^[A-F]\\s")) %>%
  group_by(Year, Division = str_extract(str_trim(`Offence Division`), "^[A-F]")) %>%
  summarise(Offence.Count = max(`Offence Count`, na.rm = TRUE), .groups = "drop")

if (nrow(division_rows) == 0) {
  stop("Could not identify division-level rows in Table 01. Check the Offence Division labels.")
}

annual_total <- division_rows %>%
  group_by(Year) %>%
  summarise(Offence.Count = sum(Offence.Count, na.rm = TRUE), .groups = "drop")

ggplot(annual_total, aes(Year, Offence.Count)) +
  geom_line(linewidth = 1) +
  geom_point(size = 2.5) +
  scale_x_continuous(breaks = 2017:2026) +
  scale_y_continuous(labels = comma) +
  labs(
    title = "Recorded offences in Victoria, 2017–2026",
    subtitle = "Year ending June; annual total calculated from division-level rows",
    x = "Year ending June", y = "Recorded offences"
  ) +
  theme_minimal(base_size = 12)

4 2. How recorded offences differ by category

The following chart compares the A–F offence divisions over time, using division-level counts only.

division_trends <- division_rows %>%
  mutate(
    Division = factor(
      Division,
      levels = c("A", "B", "C", "D", "E", "F"),
      labels = c(
        "A – Crimes against the person",
        "B – Property and deception",
        "C – Drug offences",
        "D – Public order and security",
        "E – Justice procedures",
        "F – Other offences"
      )
    )
  )

ggplot(division_trends, aes(Year, Offence.Count, colour = Division)) +
  geom_line(linewidth = 0.9) +
  geom_point(size = 1.8) +
  scale_x_continuous(breaks = 2017:2026) +
  scale_y_continuous(labels = comma) +
  labs(
    title = "Recorded offences by offence division",
    subtitle = "Year ending June",
    x = "Year ending June", y = "Recorded offences", colour = "Division"
  ) +
  theme_minimal(base_size = 11) +
  theme(legend.position = "bottom")

5 3. Which property offences dominate?

This chart uses the subgroup labels in Table 05. The subgroup identifier (for example, B42) is extracted from Offence Subgroup, not from Offence Code. Counts are summed across the individual offence-code rows within each subgroup and year.

selected_codes <- c("B21", "B41", "B42", "B43", "B49")

property_data <- table05 %>%
  mutate(
    Property.Code = str_extract(str_trim(`Offence Subgroup`), "^B[0-9]+"),
    Property.Type = str_remove(str_trim(`Offence Subgroup`), "^B[0-9]+\\s*")
  ) %>%
  filter(Property.Code %in% selected_codes) %>%
  group_by(Year, Property.Code, Property.Type) %>%
  summarise(Offence.Count = sum(`Offence Count`, na.rm = TRUE), .groups = "drop")

if (nrow(property_data) == 0) {
  stop("No B21/B41/B42/B43/B49 rows found in Table 05's Offence Subgroup column.")
}

ggplot(property_data, aes(x = factor(Year), y = Offence.Count, fill = Property.Type)) +
  geom_col(position = "dodge") +
  scale_y_continuous(labels = comma) +
  labs(
    title = "Selected property offences in Victoria, 2017–2026",
    subtitle = "Year ending June; selected subgroups",
    x = "Year ending June", y = "Recorded offences", fill = "Offence subgroup"
  ) +
  theme_minimal(base_size = 11) +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "bottom")

7 5. Comparing population-adjusted rates

Rates are taken from Table 01’s Rate per 100,000 population field. This avoids calculating a rate from counts using an assumed population denominator. If a subgroup appears more than once in the source table for a year, the report retains one rate per year and subgroup.

rate_data <- table01 %>%
  mutate(
    Theft.Code = str_extract(str_trim(`Offence Subgroup`), "^B[0-9]+"),
    Theft.Type = str_remove(str_trim(`Offence Subgroup`), "^B[0-9]+\\s*")
  ) %>%
  filter(
    Year >= 2017, Year <= 2026,
    Theft.Code %in% c("B42", "B43"),
    !is.na(`Rate per 100,000 population`)
  ) %>%
  group_by(Year, Theft.Code, Theft.Type) %>%
  summarise(
    Rate = max(`Rate per 100,000 population`, na.rm = TRUE),
    .groups = "drop"
  )

if (nrow(rate_data) == 0) {
  stop(
    "No B42/B43 rates found in Table 01's Offence Subgroup column. ",
    "Check that Table 01 contains subgroup rows and rates for these years."
  )
}

ggplot(rate_data, aes(Year, Rate, colour = Theft.Type, group = Theft.Type)) +
  geom_line(linewidth = 1) +
  geom_point(size = 2.2) +
  scale_x_continuous(breaks = 2017:2026) +
  scale_y_continuous(labels = comma) +
  labs(
    title = "Population-adjusted rates for two theft categories",
    subtitle = "Recorded offences per 100,000 population; source-reported rates",
    x = "Year ending June", y = "Rate per 100,000 population", colour = "Theft type"
  ) +
  theme_minimal(base_size = 12) +
  theme(legend.position = "bottom")

8 Discussion

The charts allow three comparisons: how the overall recorded-offence total changes over time, how the broad offence divisions differ in scale, and how two specific theft subgroups change in both raw counts and source-reported population-adjusted rates.

When interpreting the results, distinguish a change in the number of recorded offences from a change in the rate per 100,000 population. Counts can change with population size, reporting behaviour, policing and recording practices, as well as underlying offending. These descriptive charts show patterns in the recorded data; they do not by themselves establish the causes of those patterns.

9 Limitations

10 Conclusion

Comparing offence counts and source-reported rates gives a more complete picture than either measure alone. The B42 and B43 comparison is useful because it contrasts two distinct theft contexts while keeping the reporting period consistent.

11 References

Crime Statistics Agency Victoria. (n.d.). Crime statistics. https://www.crimestatistics.vic.gov.au/