# Load required libraries
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.0 ✔ stringr 1.5.1
## ✔ ggplot2 3.5.1 ✔ tibble 3.2.1
## ✔ lubridate 1.9.3 ✔ tidyr 1.3.1
## ✔ purrr 1.0.2
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(readxl)
# Load the data
file_path <- "crime.xlsx" # Update this path if the file is not in your working directory
table1 <- read_excel(file_path, sheet = "Table 01")
table2 <- read_excel(file_path, sheet = "Table 02")
table3 <- read_excel(file_path, sheet = "Table 03")
table4 <- read_excel(file_path, sheet = "Table 04")
# 1. Bar Chart: Number of Incidents by Offence Type
ggplot(table1, aes(x = `Offence Division`, y = `Incidents Recorded`)) +
geom_bar(stat = "identity", fill = "steelblue") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Number of Criminal Incidents by Offence Type (2019)", x = "Offence Type", y = "Number of Incidents")
# 2. Pie Chart: Proportion of Offence Types
offence_type_counts <- table1 %>%
group_by(`Offence Division`) %>%
summarise(total_incidents = sum(`Incidents Recorded`), .groups = 'drop')
pie_data <- offence_type_counts$total_incidents
pie_labels <- offence_type_counts$`Offence Division`
pie(pie_data, labels = paste(pie_labels, round(100 * pie_data / sum(pie_data), 1), "%"),
main = "Proportion of Offence Types (2019)", col = rainbow(length(pie_labels)))
# 3. Line Chart: Trend of Criminal Incidents Over Time
ggplot(table1, aes(x = Year, y = `Incidents Recorded`, group = `Offence Division`, color = `Offence Division`)) +
geom_line(linewidth = 1.2) +
labs(title = "Trend of Criminal Incidents Over Time", x = "Year", y = "Number of Incidents") +
theme_minimal()
# 1. Heatmap: Incidents by Location and Offence Type
location_offence_data <- table2 %>%
group_by(`Location Division`, `Offence Division`) %>%
summarise(total_incidents = sum(`Incidents Recorded`, na.rm = TRUE), .groups = 'drop') %>%
pivot_wider(names_from = `Offence Division`, values_from = total_incidents)
location_offence_long <- location_offence_data %>%
pivot_longer(-`Location Division`, names_to = "Offence Division", values_to = "total_incidents")
ggplot(location_offence_long, aes(x = `Location Division`, y = `Offence Division`, fill = total_incidents)) +
geom_tile(color = "white") +
scale_fill_gradient(low = "white", high = "red") +
labs(title = "Heatmap of Incidents by Location and Offence Type", x = "Location", y = "Offence Type")
# 2. Bar Chart: Total Incidents by Location
ggplot(table2, aes(x = `Location Division`, y = `Incidents Recorded`, fill = `Location Division`)) +
geom_bar(stat = "identity") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Total Incidents by Location", x = "Location", y = "Number of Incidents")
# 3. Stacked Bar Chart: Incidents by Offence Type per Location
ggplot(table2, aes(x = `Location Division`, y = `Incidents Recorded`, fill = `Offence Division`)) +
geom_bar(stat = "identity") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Incidents by Offence Type per Location", x = "Location", y = "Number of Incidents", fill = "Offence Type")
# Summarize total incidents by Offence Subdivision and Family Incident Flag for Table 3
table3_summary <- table3 %>%
group_by(`Offence Subdivision`, `Family Incident Flag`) %>%
summarise(total_incidents = sum(`Incidents Recorded`, na.rm = TRUE), .groups = 'drop')
# Bar chart
ggplot(table3_summary, aes(x = `Offence Subdivision`, y = total_incidents, fill = `Family Incident Flag`)) +
geom_bar(stat = "identity", position = "dodge") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Total Incidents by Offence Subdivision (2019)",
x = "Offence Subdivision",
y = "Total Incidents") +
scale_fill_manual(values = c("Family incident related" = "blue", "Not family incident related" = "red"))
# Summarize total incidents by Family Incident Flag for Pie Chart
family_incident_summary <- table3 %>%
group_by(`Family Incident Flag`) %>%
summarise(total_incidents = sum(`Incidents Recorded`, na.rm = TRUE))
# Pie chart
ggplot(family_incident_summary, aes(x = "", y = total_incidents, fill = `Family Incident Flag`)) +
geom_bar(stat = "identity", width = 1) +
coord_polar(theta = "y") +
labs(title = "Proportion of Family Incident Related Offences (2019)",
fill = "Family Incident Flag") +
theme(axis.text.x = element_blank(), axis.ticks = element_blank())
# Boxplot of rates by Offence Subdivision
ggplot(table3, aes(x = `Offence Subdivision`, y = `Rate per 100,000 population`, fill = `Family Incident Flag`)) +
geom_boxplot() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Distribution of Incident Rates by Offence Subdivision (2019)",
x = "Offence Subdivision",
y = "Rate per 100,000 Population") +
scale_fill_manual(values = c("Family incident related" = "blue", "Not family incident related" = "red"))
# Summarize top 3 Offence Subdivisions for Table 4
offence_subdivision_counts <- table4 %>%
group_by(`Offence Subdivision`) %>%
summarise(Total_Incidents = sum(`Incidents Recorded`, na.rm = TRUE), .groups = 'drop') %>%
arrange(desc(Total_Incidents)) %>%
slice_head(n = 3)
# Bar Chart for Top 3 Incidents by Offence Subdivision
ggplot(offence_subdivision_counts, aes(x = reorder(`Offence Subdivision`, -Total_Incidents), y = Total_Incidents)) +
geom_bar(stat = "identity", fill = "skyblue") +
labs(title = "Top 3 Incidents by Offence Subdivision",
x = "Offence Subdivision",
y = "Total Incidents") +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
theme_minimal()
# Line Chart for Trend Over Years for Table 4
trend_data <- table4 %>%
group_by(Year) %>%
summarise(Total_Incidents = sum(`Incidents Recorded`, na.rm = TRUE), .groups = 'drop')
ggplot(trend_data, aes(x = Year, y = Total_Incidents)) +
geom_line(color = "blue", linewidth = 1) +
labs(title = "Trend of Criminal Incidents Over Years", x = "Year", y = "Total Incidents") +
theme_minimal()
