This notebook implements the full analytical pipeline for my project Crime hotspot detection and short-term forecasting for Swansea.

Section 1: Importing essencial libraries for the analysis

# Data manipulation
import pandas as pd
import numpy as np

import warnings
warnings.filterwarnings('ignore')

# Database connection
import pyodbc

# Visualisation
import matplotlib.pyplot as plt
import seaborn as sns

# Machine learning
from sklearn.cluster import KMeans
from sklearn.ensemble import RandomForestRegressor
from sklearn.preprocessing import StandardScaler
from sklearn.metrics import mean_absolute_error, mean_squared_error
import xgboost as xgb

print("All libraries imported successfully")

All libraries imported successfully

Observations

All required libraries have been imported successfully:

• pandas and numpy for data manipulation and calculations
• pyodbc to connect Python to SQL Server and load the data
• matplotlib and seaborn for visualising crime trends and patterns
• sklearn for K-Means clustering, Random Forest forecasting and model evaluation

Section 2: Connecting to SQL Server and Loading Data

# Connect to SQL Server using Windows Authentication
conn = pyodbc.connect(
    'DRIVER={ODBC Driver 18 for SQL Server};'
    'SERVER=localhost;'
    'DATABASE=SwanseaCrimeDB;'
    'Trusted_Connection=yes;'
    'TrustServerCertificate=yes;'
)

print("Connected to SwanseaCrimeDB successfully")

Connected to SwanseaCrimeDB successfully

Loading the 4 tables from SwanseaCrimeDB into pandas dataframes

# Load crime monthly data
df_crime = pd.read_sql("SELECT * FROM crime_monthly", conn)

# Load WIMD deprivation data
df_wimd = pd.read_sql("SELECT * FROM lsoa_wimd", conn)

# Load LSOA centroids 
df_centroids = pd.read_sql(" SELECT * FROM lsoa_centroids", conn)

# Load weather monthly averages 
df_weather = pd.read_sql("SELECT * FROM weather_monthly", conn)

print("All tables loaded successfully")
print(f"Crime data: {df_crime.shape}")
print(f"WIMD data: {df_wimd.shape}")
print(f"Centroids: {df_centroids.shape}")
print(f"Weather: {df_weather.shape}")

All tables loaded successfully
Crime data: (8627, 5)
WIMD data: (150, 5)
Centroids: (1917, 3)
Weather: (12, 3)

Observations

All 4 tables have been successfully loaded from SwanseaCrimeDB into Python:

• crime_monthly 8,627 rows representing monthly crime counts per LSOA and crime type across 13 months (January 2024 to January 2025)
• lsoa_wimd 150 Swansea LSOAs with WIMD 2025 deprivation ranks
• lsoa_centroids 1,917 Welsh LSOA centroids (will be filtered to Swansea 150 in the next step)
• weather_monthly 12 monthly average temperatures used as seasonal proxy

Section 3: Exploring the Data (EDA)

Before merging and analysing the data, each table will be explored individually to understand its structure, check for missing values and identify any issues.

3.1 Exploring the Crime Data

# Display first 5 rows of crime data
df_crime.head()

	Month	LSOA_code	LSOA_name	Crime_type	Crime_Count
0	2024-01	W01000736	Swansea 028A	Burglary	1
1	2024-01	W01000736	Swansea 028A	Public order	1
2	2024-01	W01000736	Swansea 028A	Shoplifting	1
3	2024-01	W01000736	Swansea 028A	Violence and sexual offences	1
4	2024-01	W01000737	Swansea 028B	Anti-social behaviour	1

# Check shape, data types and missing values
print("Shape:", df_crime.shape)
print("\nData types:")
print(df_crime.dtypes)
print("\nMissing values:")
print(df_crime.isnull().sum())

Shape: (8627, 5)

Data types:
Month          object
LSOA_code      object
LSOA_name      object
Crime_type     object
Crime_Count     int64
dtype: object

Missing values:
Month          0
LSOA_code      0
LSOA_name      0
Crime_type     0
Crime_Count    0
dtype: int64

Observations: Crime Data

• The crime table has 8,627 rows and 5 columns
• No missing values in any column, the data is complete
• Month is stored as text (object) and will be converted to datetime format during the merging step
• Crime_Count is correctly stored as an integer (int64) ready for analysis

# Check WIMD data
print("=== WIMD DATA ===")
print("Shape:", df_wimd.shape)
print(df_wimd.dtypes)
print("\nMissing values:")
print(df_wimd.isnull().sum())

=== WIMD DATA ===
Shape: (150, 5)
LSOA_code     object
WIMD_rank      int64
Income         int64
Employment     int64
Health         int64
dtype: object

Missing values:
LSOA_code     0
WIMD_rank     0
Income        0
Employment    0
Health        0
dtype: int64

# Check centroids data
print("=== CENTROIDS DATA ===")
print("Shape:", df_centroids.shape)
print(df_centroids.dtypes)
print("\nMissing values:")
print(df_centroids.isnull().sum())

print("\n=== WEATHER DATA ===")
print("Shape:", df_weather.shape)
print(df_weather.dtypes)
print("\nMissing values:")
print(df_weather.isnull().sum())

=== CENTROIDS DATA ===
Shape: (1917, 3)
LSOA_code     object
x            float64
y            float64
dtype: object

Missing values:
LSOA_code    0
x            0
y            0
dtype: int64

=== WEATHER DATA ===
Shape: (12, 3)
Month_num       int64
Month_name     object
Avg_temp_c    float64
dtype: object

Missing values:
Month_num     0
Month_name    0
Avg_temp_c    0
dtype: int64

Observations

• All 4 tables have no missing values so no data cleaning required
• WIMD ranks are correctly stored as integers
• Centroid coordinates are stored as float64
• Weather temperatures are stored as float64
• Month in crime table is stored as text (object), It will be converted to datetime in the merging step
• Centroids has 1917 rows (all Welsh LSOAs), It will be filtered to 150 Swansea LSOAs during merging

3.2 Visualising Crime Trends.

# Total crimes per month. 
monthly_totals = df_crime.groupby('Month')['Crime_Count'].sum().reset_index()

plt.figure(figsize=(12, 5))
plt.plot(monthly_totals['Month'], monthly_totals['Crime_Count'], 
         marker='o', color='steelblue', linewidth=2)
plt.title('Total Crimes per Month in Swansea (Jan 2024 - Jan 2025)')
plt.xlabel('Month')
plt.ylabel('Total Crimes')
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

Observations on Crime Trend

• Total monthly crimes range from approximately 1,650 to 2,140
• Crime peaks in May 2024 the warmest spring month
• Crime drops sharply in December 2024 and reaches its lowest point
• There is a general seasonal pattern with higher crime in spring/early summer and lower crime in winter, consistent with the literature on temperature and crime.
• The overall trend shows a slight decline across the year

# Total crimes by crime type
crime_by_type = df_crime.groupby('Crime_type')['Crime_Count'].sum().sort_values(ascending=True)

plt.figure(figsize=(10,6))
crime_by_type.plot(kind='barh', color='steelblue')
plt.title('Total Crimes by Type in Swansea (Jan 2024 - Jan 2025)')
plt.xlabel('Total Crimes')
plt.ylabel('Crime Type')
plt.tight_layout()
plt.show()

Observations on Crime by Type

• Violence and sexual offences is the most common crime type by far, with nearly 9,000 recorded incidents across the study period
• Anti-social behaviour, shoplifting and public order are the next most common types
• Robbery and possession of weapons are the least common
• The dominance of violence and social interaction crimes supports the literature on temperature and crime these types are most likely to increase during warmer months when people spend more time outdoors

Section 4: Data Preparation and Merging

In this section I'll merge the 4 tables into one master dataset, convert the Month column to datetime format, filter centroids to Swansea only and add the temperature variable.

4.1 Converting Month to datetime format

The Month column is currently stored as text (e.g. '2024-01'). It will be converted to a proper datetime format so I can extract the month number for joining with the weather table.

# Convert Month from text to datetime format
df_crime['Month'] = pd.to_datetime(df_crime['Month'])

# Extract month number (1-12) for joining with weather table
df_crime['Month_num'] = df_crime['Month'].dt.month

print("Month column converted successfully")
print(df_crime[['Month', 'Month_num']].head())

Month column converted successfully
       Month  Month_num
0 2024-01-01          1
1 2024-01-01          1
2 2024-01-01          1
3 2024-01-01          1
4 2024-01-01          1

# Filter centroids to Swansea LSOAs only
swansea_codes = df_crime['LSOA_code'].unique()
df_centroids_swansea = df_centroids[df_centroids['LSOA_code'].isin(swansea_codes)]

print(f"All Welsh centroids: {len(df_centroids)}")
print(f"Swansea centroids only: {len(df_centroids_swansea)}")

All Welsh centroids: 1917
Swansea centroids only: 150

4.2 Merging all tables into one master dataset

# Step 1: Merge crime data with WIMD deprivation data on LSOA_code
df_master = df_crime.merge(df_wimd, on='LSOA_code', how='left')

# Step 2: Merge with Swansea centroids on LSOA_code
df_master = df_master.merge(df_centroids_swansea, on='LSOA_code', how='left')

# Step 3: Merge with weather data on Month_num
df_master = df_master.merge(df_weather[['Month_num', 'Avg_temp_c']], 
                             on='Month_num', how='left')

print("Master dataset created successfully")
print("Shape:", df_master.shape)
print("\nColumns:", df_master.columns.tolist())

Master dataset created successfully
Shape: (8627, 13)

Columns: ['Month', 'LSOA_code', 'LSOA_name', 'Crime_type', 'Crime_Count', 'Month_num', 'WIMD_rank', 'Income', 'Employment', 'Health', 'x', 'y', 'Avg_temp_c']

# Check master dataset
print("Missing values in master dataset:")
print(df_master.isnull().sum())

print("\nFirst 3 rows:")
df_master.head(3)

Missing values in master dataset:
Month          0
LSOA_code      0
LSOA_name      0
Crime_type     0
Crime_Count    0
Month_num      0
WIMD_rank      0
Income         0
Employment     0
Health         0
x              0
y              0
Avg_temp_c     0
dtype: int64

First 3 rows:

	Month	LSOA_code	LSOA_name	Crime_type	Crime_Count	Month_num	WIMD_rank	Income	Employment	Health	x	y	Avg_temp_c
0	2024-01-01	W01000736	Swansea 028A	Burglary	1	1	1915	1895	1823	1912	258205.953125	188490.28125	6.93
1	2024-01-01	W01000736	Swansea 028A	Public order	1	1	1915	1895	1823	1912	258205.953125	188490.28125	6.93
2	2024-01-01	W01000736	Swansea 028A	Shoplifting	1	1	1915	1895	1823	1912	258205.953125	188490.28125	6.93

Observations on Master Dataset

• All 4 tables merged successfully into one master dataset
• Shape is (8627, 13), same number of rows as the original crime table, confirming no rows were lost during merging
• No missing values in any column, all joins were successful
• Each row now contains crime information, deprivation ranks, spatial coordinates and temperature in one place
• This master dataset is now ready for K-Means clustering and Random Forest forecasting

Section 5: K-Means Clustering (Hotspot Detection)

5.1 Creating LSOA level summary for clustering

For K-Means clustering, the data needs to be organised so there is one row for each LSOA. The master dataset currently has 8,627 rows, with each row representing a crime type for a specific month and LSOA. To prepare it, the data will be grouped by LSOA and the crime counts summarised into total crime and average monthly crime figures.

# Create LSOA level summary for clustering
# One row per LSOA with total and average monthly crime
df_lsoa_summary = df_master.groupby(
    ['LSOA_code', 'LSOA_name', 'x', 'y', 'WIMD_rank']
).agg(
    total_crime=('Crime_Count', 'sum'),
    avg_monthly_crime=('Crime_Count', 'mean')
).reset_index()

print("LSOA summary created")
print("Shape:", df_lsoa_summary.shape)
df_lsoa_summary.head()

LSOA summary created
Shape: (150, 7)

	LSOA_code	LSOA_name	x	y	WIMD_rank	total_crime	avg_monthly_crime
0	W01000736	Swansea 028A	258205.953125	188490.281250	1915	40	1.290323
1	W01000737	Swansea 028B	258725.109375	188960.953125	1789	50	1.785714
2	W01000738	Swansea 014A	267911.250000	195416.671875	95	171	2.671875
3	W01000739	Swansea 014B	267054.843750	195157.421875	534	192	2.313253
4	W01000740	Swansea 014C	268831.843750	196162.140625	1233	59	1.685714

Observations on LSOA Summary

The aggregation step worked as intended, reducing the dataset to 150 rows so that each row now represents one LSOA. Each area includes summary measures for total crime and average monthly crime, which makes the data ready for clustering. The sample output already shows clear differences in crime levels between areas, with more deprived LSOAs seeming to have higher crime counts, which fits with Social Disorganisation Theory discussed in the literature review. This variation makes K-means a useful method for identifying distinct crime zones.

5.2 Choosing the optimal number of clusters (K) using the Elbow Method

# Prepare features for clustering, spatial coordinates only
X_cluster = df_lsoa_summary[['x', 'y']].values

# Scale the coordinates so both axes contribute equally
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X_cluster)

# Run K-Means for k=2 to k=10 and record wcss
wcss = []
k_range = range(2, 11)

for k in k_range:
    kmeans = KMeans(n_clusters=k, init='k-means++', 
                    max_iter=300, n_init=10, random_state=42)
    kmeans.fit(X_scaled)
    wcss.append(kmeans.inertia_)

# Plot the elbow curve
plt.figure(figsize=(8, 5))
plt.plot(k_range, wcss, marker='o', color='steelblue', linewidth=2)
plt.title('Elbow Method Optimal Number of Clusters')
plt.xlabel('Number of Clusters (K)')
plt.ylabel('WCSS')
plt.xticks(list(k_range))
plt.tight_layout()
plt.show() 

Observations on Elbow Method

The graph shows that WCSS falls sharply from K=2 to K=4, then starts to level off. This suggests that using more than four clusters does not improve the model very much. For that reason, K=4 is considered the optimal number of clusters. This will be further validated using the Silhouette Score in the next step.

5.3 Validating K using the Silhouette Score

from sklearn.metrics import silhouette_score

silhouette_scores = []

for k in k_range:
    kmeans = KMeans(n_clusters=k, init='k-means++',
                    max_iter=300, n_init=10, random_state=42)
    kmeans.fit(X_scaled)
    silhouette_scores.append(silhouette_score(X_scaled, kmeans.labels_))

plt.figure(figsize=(8, 5))
plt.plot(k_range, silhouette_scores, marker='o', color='coral', linewidth=2)
plt.title('Silhouette Score Optimal Number of Clusters')
plt.xlabel('Number of Clusters (K)')
plt.ylabel('Silhouette Score')
plt.xticks(list(k_range))
plt.tight_layout()
plt.show()

for k, score in zip(k_range, silhouette_scores):
    print(f"K={k}: Silhouette Score = {score:.4f}")

K=2: Silhouette Score = 0.3289
K=3: Silhouette Score = 0.3983
K=4: Silhouette Score = 0.4491
K=5: Silhouette Score = 0.4273
K=6: Silhouette Score = 0.4391
K=7: Silhouette Score = 0.3858
K=8: Silhouette Score = 0.4056
K=9: Silhouette Score = 0.4072
K=10: Silhouette Score = 0.4184

Observations on Silhouette Score

The silhouette score increases from K=2 to K=4 and reaches its highest value at K=4. After that, the scores fall and vary, which suggests that using more clusters does not lead to better grouping. This means K=4 gives the clearest cluster structure, supporting the elbow method and confirming that four clusters is the best choice.

5.4 Running K-Means with K=4

# Run final K-Means with K=4
kmeans_final = KMeans(n_clusters=4, init='k-means++', 
                      max_iter=300, n_init=10, random_state=42)

df_lsoa_summary['Cluster'] = kmeans_final.fit_predict(X_scaled)

# Count how many LSOAs are in each cluster
print("LSOAs per cluster:")
print(df_lsoa_summary['Cluster'].value_counts().sort_index())

LSOAs per cluster:
Cluster
0    68
1    26
2    21
3    35
Name: count, dtype: int64

# Find average crime per cluster to identify hotspot zones
cluster_summary = df_lsoa_summary.groupby('Cluster').agg(
    num_lsoas=('LSOA_code', 'count'),
    avg_monthly_crime=('avg_monthly_crime', 'mean'),
    total_crime=('total_crime', 'sum')
).reset_index()

print(cluster_summary.sort_values('avg_monthly_crime', ascending=False))

   Cluster  num_lsoas  avg_monthly_crime  total_crime
0        0         68           2.711283        15865
3        3         35           2.335195         5116
1        1         26           1.941606         2578
2        2         21           1.650872         1362

Observations on Cluster Summary

The cluster summary shows clear differences in crime levels across the four groups. Cluster 0 has the highest average monthly crime and the highest total crime, suggesting it represents the main hotspot areas. Cluster 3 also shows relatively high crime levels, while Clusters 1 and 2 have lower averages and totals. Overall, this suggests that crime is not evenly spread across LSOAs, with some clusters showing much higher crime concentrations than others. This pattern is consistent with Weisburd's law of crime concentration, which states that a small number of places account for a disproportionately large share of crimes.

Now clusters will be labelled so they make sense in Power BI.

# Label clusters by crime level
cluster_labels = {0: 'High Crime Zone', 
                  3: 'Medium-High Crime Zone',
                  1: 'Medium-Low Crime Zone', 
                  2: 'Low Crime Zone'}

df_lsoa_summary['Zone'] = df_lsoa_summary['Cluster'].map(cluster_labels)

print(df_lsoa_summary[['LSOA_name', 'Cluster', 'Zone', 'avg_monthly_crime']].head(10))

      LSOA_name  Cluster                    Zone  avg_monthly_crime
0  Swansea 028A        2          Low Crime Zone           1.290323
1  Swansea 028B        2          Low Crime Zone           1.785714
2  Swansea 014A        3  Medium-High Crime Zone           2.671875
3  Swansea 014B        0         High Crime Zone           2.313253
4  Swansea 014C        3  Medium-High Crime Zone           1.685714
5  Swansea 014D        3  Medium-High Crime Zone           3.900990
6  Swansea 016A        0         High Crime Zone           3.711712
7  Swansea 016B        0         High Crime Zone           2.917647
8  Swansea 025B        0         High Crime Zone           7.264901
9  Swansea 025C        0         High Crime Zone           2.576923

Observations on Cluster Labels by LSOA

The results show that different LSOAs fall into clearly different crime zones. Areas such as Swansea 025B and 016A are assigned to the High Crime Zone with average monthly crime values of 7.26 and 3.71 respectively, while Swansea 028A and 028B fall into the Low Crime Zone with values below 2. This variation supports the law of crime concentration, which suggests that crime tends to cluster in a small number of places rather than being evenly distributed across an area.

5.5 Visualising the K-Means Hotspot Clusters

The four clusters are visualised on a scatter plot using the LSOA centroid coordinates. Each cluster is colour coded to show the spatial distribution of crime zones across Swansea.

# Define colours for each zone
colours = {
    'High Crime Zone': 'red',
    'Medium-High Crime Zone': 'orange',
    'Medium-Low Crime Zone': 'yellow',
    'Low Crime Zone': 'green'
}

plt.figure(figsize=(8, 5))

for zone, colour in colours.items():
    subset = df_lsoa_summary[df_lsoa_summary['Zone'] == zone]
    plt.scatter(subset['x'], subset['y'], 
                c=colour, label=zone, s=60, alpha=0.7)

plt.title('K-Means Crime Hotspot Clusters in Swansea')
plt.xlabel('Easting (x)')
plt.ylabel('Northing (y)')
plt.legend()
plt.tight_layout()
plt.show()

Observations on Hotspot Map

The map suggests that the main hotspot is clustered around the central part of Swansea, particularly in the city centre and nearby eastern areas, while lower-crime zones are more scattered on the outskirts. This pattern is consistent with environmental criminology and Weisburd's law of crime concentration, which suggests that crime tends to cluster in specific places rather than being spread evenly across a whole city, likely because certain locations offer more opportunities for offending.

Section 6: Crime Forecasting

This section implements and evaluates three forecasting approaches: a simple baseline model, a Random Forest regressor, and an XGBoost regressor. The models will be trained on historical monthly crime data and evaluated using MAE and RMSE on a held-out test set.

6.1 Preparing data for forecasting

The forecasting models require a dataset with one row per LSOA per month showing total crime count. Lag features will be created to give the model information about previous months crime levels.

# Aggregate crime by LSOA and month (total across all crime types)
df_forecast = df_master.groupby(
    ['LSOA_code', 'LSOA_name', 'Month', 'Month_num', 
     'WIMD_rank', 'Avg_temp_c']
)['Crime_Count'].sum().reset_index()

# Sort by LSOA and Month
df_forecast = df_forecast.sort_values(['LSOA_code', 'Month']).reset_index(drop=True)

print("Forecast dataset shape:", df_forecast.shape)
df_forecast.head()

Forecast dataset shape: (1899, 7)

	LSOA_code	LSOA_name	Month	Month_num	WIMD_rank	Avg_temp_c	Crime_Count
0	W01000736	Swansea 028A	2024-01-01	1	1915	6.93	4
1	W01000736	Swansea 028A	2024-02-01	2	1915	7.23	2
2	W01000736	Swansea 028A	2024-03-01	3	1915	7.58	5
3	W01000736	Swansea 028A	2024-04-01	4	1915	9.46	3
4	W01000736	Swansea 028A	2024-05-01	5	1915	13.53	1

6.2 Creating lag features

# Create lag features per LSOA
df_forecast['lag1'] = df_forecast.groupby('LSOA_code')['Crime_Count'].shift(1)
df_forecast['lag2'] = df_forecast.groupby('LSOA_code')['Crime_Count'].shift(2)
df_forecast['lag3'] = df_forecast.groupby('LSOA_code')['Crime_Count'].shift(3)

# Create 3-month rolling average (baseline model)
df_forecast['rolling_avg3'] = df_forecast.groupby('LSOA_code')['Crime_Count'].transform(
    lambda x: x.shift(1).rolling(3, min_periods=1).mean()
)

# Drop rows with missing lag values
df_forecast = df_forecast.dropna(subset=['lag1', 'lag2', 'lag3'])

print("Shape after creating lag features:", df_forecast.shape)
df_forecast.head()

Shape after creating lag features: (1449, 11)

	LSOA_code	LSOA_name	Month	Month_num	WIMD_rank	Avg_temp_c	Crime_Count	lag1	lag2	lag3	rolling_avg3
3	W01000736	Swansea 028A	2024-04-01	4	1915	9.46	3	5.0	2.0	4.0	3.666667
4	W01000736	Swansea 028A	2024-05-01	5	1915	13.53	1	3.0	5.0	2.0	3.333333
5	W01000736	Swansea 028A	2024-07-01	7	1915	16.20	2	1.0	3.0	5.0	3.000000
6	W01000736	Swansea 028A	2024-08-01	8	1915	16.63	3	2.0	1.0	3.0	2.000000
7	W01000736	Swansea 028A	2024-09-01	9	1915	17.57	5	3.0	2.0	1.0	2.000000

Observations

The lag features were created successfully, reducing the dataset from 1,899 to 1,449 rows. This drop happened because the first three months for each LSOA did not have enough previous data to calculate lag values, so those rows were removed. Each row now includes crime counts from the previous one, two and three months, as well as a 3-month rolling average. For example, for Swansea 028A in April 2024, the lag values match the crime counts from January to March, confirming the features were calculated correctly.

6.3 Train/Test Split

# Time-based train/test split
# Train: Jan 2024 - Oct 2024
# Test: Nov 2024 - Jan 2025
train = df_forecast[df_forecast['Month'] < '2024-11-01']
test = df_forecast[df_forecast['Month'] >= '2024-11-01']

print(f"Training set: {train.shape[0]} rows")
print(f"Test set: {test.shape[0]} rows")
print(f"Train period: {train['Month'].min().strftime('%Y-%m')} to {train['Month'].max().strftime('%Y-%m')}")
print(f"Test period: {test['Month'].min().strftime('%Y-%m')} to {test['Month'].max().strftime('%Y-%m')}")

Training set: 1011 rows
Test set: 438 rows
Train period: 2024-04 to 2024-10
Test period: 2024-11 to 2025-01

Observations

The train/test split was done correctly using time order, with earlier months used for training and later months kept for testing. The model is trained on 1,011 rows from April to October 2024 and then tested on 438 rows from November 2024 to January 2025. This is a sensible setup for forecasting because it tests the model on future data rather than data it has already seen.

6.4 Defining features and target variable

The features used to train the models are the three lag variables, month number, average temperature and WIMD deprivation rank. The target variable is the Crime_Count for the current month.

# Define features and target
features = ['lag1', 'lag2', 'lag3', 'Month_num', 'Avg_temp_c', 'WIMD_rank']
target = 'Crime_Count'

# Split into X and y
X_train = train[features]
y_train = train[target]
X_test = test[features]
y_test = test[target]

print("Features:", features)
print("X_train shape:", X_train.shape)
print("X_test shape:", X_test.shape)

Features: ['lag1', 'lag2', 'lag3', 'Month_num', 'Avg_temp_c', 'WIMD_rank']
X_train shape: (1011, 6)
X_test shape: (438, 6)

Observation

The model inputs were set up correctly, using past crime levels, month, temperature, and deprivation rank to predict the current month’s crime count. The training and test data also have the expected number of features, so the dataset is now ready for modelling.

6.5 Training and Evaluating the Models

# Baseline: 3-month rolling average 
baseline_pred = test['rolling_avg3']
baseline_mae = mean_absolute_error(y_test, baseline_pred)
baseline_rmse = np.sqrt(mean_squared_error(y_test, baseline_pred))

# Random Forest 
rf_model = RandomForestRegressor(n_estimators=100, random_state=42)
rf_model.fit(X_train, y_train)
rf_pred = rf_model.predict(X_test)
rf_mae = mean_absolute_error(y_test, rf_pred)
rf_rmse = np.sqrt(mean_squared_error(y_test, rf_pred))

# XGBoost 
xgb_model = xgb.XGBRegressor(n_estimators=100, random_state=42)
xgb_model.fit(X_train, y_train)
xgb_pred = xgb_model.predict(X_test)
xgb_mae = mean_absolute_error(y_test, xgb_pred)
xgb_rmse = np.sqrt(mean_squared_error(y_test, xgb_pred))

# Print results 
print("=" * 45)
print(f"{'Model':<25} {'MAE':>8} {'RMSE':>8}")
print("=" * 45)
print(f"{'Baseline (Rolling Avg)':<25} {baseline_mae:>8.3f} {baseline_rmse:>8.3f}")
print(f"{'Random Forest':<25} {rf_mae:>8.3f} {rf_rmse:>8.3f}")
print(f"{'XGBoost':<25} {xgb_mae:>8.3f} {xgb_rmse:>8.3f}")
print("=" * 45)

=============================================
Model                          MAE     RMSE
=============================================
Baseline (Rolling Avg)       3.411    4.875
Random Forest                3.439    4.789
XGBoost                      3.774    5.821
=============================================

Observation

The results show that the baseline model performed slightly better overall, with the lowest MAE, meaning its predictions were closest to the real crime counts on average. Random Forest had a very similar MAE and the lowest RMSE, so it handled larger errors a bit better. XGBoost performed worst out of the three, with the highest MAE and RMSE. Overall, this suggests that the more complex models did not clearly outperform the simple rolling average approach for this dataset, possibly because crime patterns were fairly stable and recent past values already captured most of the useful information.

6.6 Random Forest Feature Importance

Feature importance shows which input variables contributed most to the Random Forest predictions.

# Plot feature importance
feature_importance = pd.Series(
    rf_model.feature_importances_, 
    index=features
).sort_values(ascending=True)

plt.figure(figsize=(8, 5))
feature_importance.plot(kind='barh', color='steelblue')
plt.title('Random Forest Feature Importance')
plt.xlabel('Importance Score')
plt.tight_layout()
plt.show()

Observation

The chart shows that the Random Forest model relied mainly on past crime levels, with lag2, lag1, and lag3 having by far the biggest influence on predictions. In contrast, WIMD rank, temperature, and month number contributed very little. This is interesting because it does not fully match the literature review, which suggested that deprivation and temperature can shape crime patterns, so in this dataset their effects may have been weaker or already reflected in past crime levels.

6.7 Actual vs Predicted Crime Count

The actual crime counts will be plotted against the predictions from each model to visually assess forecasting performance on the test set.

plt.figure(figsize=(10, 5))

plt.plot(y_test.values[:50], label='Actual', color='black', linewidth=2)
plt.plot(baseline_pred.values[:50], label='Baseline', 
         color='blue', linestyle='--')
plt.plot(rf_pred[:50], label='Random Forest', 
         color='green', linestyle='--')
plt.plot(xgb_pred[:50], label='XGBoost', 
         color='red', linestyle='--')

plt.title('Actual vs Predicted Crime Count (First 50 Test Records)')
plt.xlabel('Test Record')
plt.ylabel('Crime Count')
plt.legend()
plt.tight_layout()
plt.show()

Observations on Actual vs Predicted

All three models generally follow the overall pattern of the actual crime counts across the 50 test records. The biggest feature is the sharp spike around records 23–24, where crime rises to about 88. All models picked up this increase, but none matched it exactly, which suggests they tend to smooth out sudden extreme changes. Across the rest of the chart, the prediction lines are very similar and often overlap, which fits with the similar MAE and RMSE results. Overall, the models do a reasonable job of capturing the general trend, but they are less accurate when sudden spikes happen.

6.8 Forecasting Next Month Crime (February 2025)

The models are retrained on the full dataset to generate a true next-month forecast for February 2025. This directly fulfils the project objective of supporting short-term crime forecasting for Swansea.

# Retrain on full dataset (all 1449 rows)
X_full = df_forecast[features]
y_full = df_forecast[target]

# Retrain Random Forest on full data
rf_full = RandomForestRegressor(n_estimators=100, random_state=42)
rf_full.fit(X_full, y_full)

# Retrain XGBoost on full data
xgb_full = xgb.XGBRegressor(n_estimators=100, random_state=42)
xgb_full.fit(X_full, y_full)

print("Models retrained on full dataset successfully")

Models retrained on full dataset successfully

# Get the last 3 months of data per LSOA for lag features
# Most recent month in data is January 2025
latest_data = df_forecast.sort_values(['LSOA_code', 'Month'])

# Get last 3 months per LSOA
lag_data = latest_data.groupby('LSOA_code').tail(3)

# Create February 2025 prediction input
feb_2025 = []

for lsoa in df_forecast['LSOA_code'].unique():
    lsoa_data = latest_data[latest_data['LSOA_code'] == lsoa].tail(3)
    
    if len(lsoa_data) >= 3:
        lag1 = lsoa_data.iloc[-1]['Crime_Count']  # January 2025
        lag2 = lsoa_data.iloc[-2]['Crime_Count']  # December 2024
        lag3 = lsoa_data.iloc[-3]['Crime_Count']  # November 2024
        wimd = lsoa_data.iloc[-1]['WIMD_rank']
        lsoa_name = lsoa_data.iloc[-1]['LSOA_name']
        
        feb_2025.append({
            'LSOA_code': lsoa,
            'LSOA_name': lsoa_name,
            'Month_num': 2,        # February
            'Avg_temp_c': 7.23,    # February average temperature
            'WIMD_rank': wimd,
            'lag1': lag1,
            'lag2': lag2,
            'lag3': lag3
        })

df_feb = pd.DataFrame(feb_2025)
print(f"February 2025 prediction input created: {len(df_feb)} LSOAs")
df_feb.head()

February 2025 prediction input created: 150 LSOAs

	LSOA_code	LSOA_name	Month_num	Avg_temp_c	WIMD_rank	lag1	lag2	lag3
0	W01000736	Swansea 028A	2	7.23	1915	4	7	4
1	W01000737	Swansea 028B	2	7.23	1789	4	4	2
2	W01000738	Swansea 014A	2	7.23	95	13	19	16
3	W01000739	Swansea 014B	2	7.23	534	16	13	13
4	W01000740	Swansea 014C	2	7.23	1233	6	4	3

Observation:

The February 2025 prediction dataset was created successfully for all 150 LSOAs. Each row now includes the key inputs needed for forecasting, including temperature, deprivation rank, and the previous three months of crime counts, so the data is ready to generate February predictions.

# Generate February 2025 predictions
X_feb = df_feb[features]

df_feb['RF_Forecast'] = rf_full.predict(X_feb).round(1)
df_feb['XGB_Forecast'] = xgb_full.predict(X_feb).round(1)
df_feb['Baseline_Forecast'] = df_feb[['lag1', 'lag2', 'lag3']].mean(axis=1).round(1)

# Show top 10 highest predicted crime LSOAs
print("Top 10 LSOAs by RF Forecast — February 2025:")
print(df_feb[['LSOA_name', 'RF_Forecast', 'XGB_Forecast', 'Baseline_Forecast']]
      .sort_values('RF_Forecast', ascending=False).head(10).to_string(index=False))

Top 10 LSOAs by RF Forecast — February 2025:
   LSOA_name  RF_Forecast  XGB_Forecast  Baseline_Forecast
Swansea 025F        229.4    100.000000              228.0
Swansea 025B         69.7     60.799999               72.7
Swansea 025I         44.7     37.099998               51.0
Swansea 008C         32.9     30.900000               38.7
Swansea 009A         32.7     31.900000               26.3
Swansea 010A         30.7     25.299999               31.7
Swansea 014D         29.4     34.799999               29.3
Swansea 016A         27.8     27.900000               28.0
Swansea 026A         27.7     34.599998               24.3
Swansea 024F         26.9     29.700001               24.0

Observation:

The February forecasts suggest that Swansea 025F is still the main hotspot, with very high predicted crime compared with the other LSOAs. Areas like Swansea 025B and 025I also appear near the top, which lines up with the earlier K-means results and helps confirm that these are consistently high-crime areas. One interesting difference is that XGBoost predicts a much lower value for Swansea 025F than Random Forest and the baseline, which suggests it may be underestimating the most extreme hotspot.

Section 7: Exporting Results to SQL

The clustering and forecasting results are exported back to SQL Server so they can be connected to Power BI for dashboard visualisation.

# Create a cursor to write data back to SQL
cursor = conn.cursor()

# Drop tables if they already exist
cursor.execute("IF OBJECT_ID('clustering_results', 'U') IS NOT NULL DROP TABLE clustering_results")
cursor.execute("IF OBJECT_ID('forecast_results', 'U') IS NOT NULL DROP TABLE forecast_results")
conn.commit()

print("Old tables cleared")

Old tables cleared

# Create clustering_results table
cursor.execute("""
    CREATE TABLE clustering_results (
        LSOA_code NVARCHAR(20),
        LSOA_name NVARCHAR(100),
        x FLOAT,
        y FLOAT,
        WIMD_rank INT,
        total_crime INT,
        avg_monthly_crime FLOAT,
        Cluster INT,
        Zone NVARCHAR(50)
    )
""")
conn.commit()

# Insert clustering results row by row
for _, row in df_lsoa_summary.iterrows():
    cursor.execute("""
        INSERT INTO clustering_results 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, row['LSOA_code'], row['LSOA_name'], row['x'], row['y'],
        row['WIMD_rank'], row['total_crime'], 
        row['avg_monthly_crime'], row['Cluster'], row['Zone'])

conn.commit()
print(f"Clustering results exported: {len(df_lsoa_summary)} rows")

Clustering results exported: 150 rows

# Create forecast_results table
cursor.execute("""
    CREATE TABLE forecast_results (
        LSOA_code NVARCHAR(20),
        LSOA_name NVARCHAR(100),
        Month NVARCHAR(20),
        Actual_Crime INT,
        Baseline_Pred FLOAT,
        RF_Pred FLOAT,
        XGB_Pred FLOAT
    )
""")
conn.commit()

# Prepare forecast results dataframe
df_results = test[['LSOA_code', 'LSOA_name', 'Month']].copy()
df_results['Actual_Crime'] = y_test.values
df_results['Baseline_Pred'] = baseline_pred.values
df_results['RF_Pred'] = rf_pred
df_results['XGB_Pred'] = xgb_pred

# Insert forecast results row by row
for _, row in df_results.iterrows():
    cursor.execute("""
        INSERT INTO forecast_results 
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, row['LSOA_code'], row['LSOA_name'], 
        str(row['Month']), int(row['Actual_Crime']),
        float(row['Baseline_Pred']), float(row['RF_Pred']), 
        float(row['XGB_Pred']))

conn.commit()
print(f"Forecast results exported: {len(df_results)} rows")

Forecast results exported: 438 rows

# Verify tables in SQL
df_check1 = pd.read_sql("SELECT COUNT(*) AS rows FROM clustering_results", conn)
df_check2 = pd.read_sql("SELECT COUNT(*) AS rows FROM forecast_results", conn)

print(f"clustering_results: {df_check1['rows'][0]} rows")
print(f"forecast_results: {df_check2['rows'][0]} rows")
print("Export complete — ready for Power BI")

clustering_results: 150 rows
forecast_results: 438 rows
Export complete — ready for Power BI

Observations on Export to SQL

Two results tables were successfully exported to SwanseaCrimeDB:

• clustering_results (150 rows) contains LSOA cluster assignments and zone labels for use in the Power BI map visual
• forecast_results (438 rows) contains actual and predicted crime counts from all three models for the test period November 2024 to January 2025 The database now contains all the data needed to build the Power BI dashboard.

# Export February 2025 forecasts to SQL
cursor.execute("IF OBJECT_ID('feb_2025_forecast', 'U') IS NOT NULL DROP TABLE feb_2025_forecast")
conn.commit()

cursor.execute("""
    CREATE TABLE feb_2025_forecast (
        LSOA_code NVARCHAR(20),
        LSOA_name NVARCHAR(100),
        RF_Forecast FLOAT,
        XGB_Forecast FLOAT,
        Baseline_Forecast FLOAT
    )
""")
conn.commit()

for _, row in df_feb.iterrows():
    cursor.execute("""
        INSERT INTO feb_2025_forecast VALUES (?, ?, ?, ?, ?)
    """, row['LSOA_code'], row['LSOA_name'],
        float(row['RF_Forecast']), float(row['XGB_Forecast']),
        float(row['Baseline_Forecast']))

conn.commit()
print(f"February 2025 forecasts exported: {len(df_feb)} rows")

February 2025 forecasts exported: 150 rows

df_check3 = pd.read_sql("SELECT COUNT(*) AS rows FROM feb_2025_forecast", conn)
print(f"feb_2025_forecast: {df_check3['rows'][0]} rows")

# Quick preview of top 5
df_preview = pd.read_sql("""
    SELECT TOP 5 LSOA_name, RF_Forecast, XGB_Forecast, Baseline_Forecast 
    FROM feb_2025_forecast 
    ORDER BY RF_Forecast DESC
""", conn)
print(df_preview)

feb_2025_forecast: 150 rows
      LSOA_name  RF_Forecast  XGB_Forecast  Baseline_Forecast
0  Swansea 025F        229.4    100.000000              228.0
1  Swansea 025B         69.7     60.799999               72.7
2  Swansea 025I         44.7     37.099998               51.0
3  Swansea 008C         32.9     30.900000               38.7
4  Swansea 009A         32.7     31.900000               26.3

Observations: February 2025 Forecast Export

The February 2025 forecasts were successfully exported to SQL Server as a new table (feb_2025_forecast) containing 150 rows, one per LSOA. The top predicted hotspot remains Swansea 025F with RF forecasting 229.4 crimes, consistent with both the K-Means clustering results and historical crime patterns. These forecasts are now ready to be connected to Power BI for dashboard visualisation.

# Install pyproj for coordinate conversion
import subprocess
subprocess.run(['python', '-m', 'pip', 'install', 'pyproj'], 
               capture_output=True)
print("pyproj ready")

pyproj ready

from pyproj import Transformer

# Convert British National Grid (EPSG:27700) to Latitude/Longitude (EPSG:4326)
transformer = Transformer.from_crs("EPSG:27700", "EPSG:4326")

# Convert coordinates for all 150 LSOAs
df_lsoa_summary['latitude'], df_lsoa_summary['longitude'] = transformer.transform(
    df_lsoa_summary['x'].values, 
    df_lsoa_summary['y'].values
)

print("Coordinates converted successfully")
print(df_lsoa_summary[['LSOA_name', 'x', 'y', 'latitude', 'longitude']].head())

Coordinates converted successfully
      LSOA_name              x              y   latitude  longitude
0  Swansea 028A  258205.953125  188490.281250  51.577391  -4.047668
1  Swansea 028B  258725.109375  188960.953125  51.581750  -4.040371
2  Swansea 014A  267911.250000  195416.671875  51.641993  -3.910307
3  Swansea 014B  267054.843750  195157.421875  51.639461  -3.922577
4  Swansea 014C  268831.843750  196162.140625  51.648908  -3.897290

# Add latitude and longitude to clustering_results in SQL
cursor.execute("ALTER TABLE clustering_results ADD latitude FLOAT, longitude FLOAT")
conn.commit()

# Update each row with lat/lon values
for _, row in df_lsoa_summary.iterrows():
    cursor.execute("""
        UPDATE clustering_results 
        SET latitude = ?, longitude = ?
        WHERE LSOA_code = ?
    """, float(row['latitude']), float(row['longitude']), row['LSOA_code'])

conn.commit()
print("Latitude and longitude added to clustering_results successfully")

Latitude and longitude added to clustering_results successfully

# Update zone labels to shorter versions
cursor.execute("UPDATE clustering_results SET Zone = 'High' WHERE Zone = 'High Crime Zone'")
cursor.execute("UPDATE clustering_results SET Zone = 'Med-High' WHERE Zone = 'Medium-High Crime Zone'")
cursor.execute("UPDATE clustering_results SET Zone = 'Med-Low' WHERE Zone = 'Medium-Low Crime Zone'")
cursor.execute("UPDATE clustering_results SET Zone = 'Low' WHERE Zone = 'Low Crime Zone'")
conn.commit()
print("Zone labels updated")

Zone labels updated

# Create combined actual vs forecast table
df_combined = df_lsoa_summary[['LSOA_code', 'LSOA_name', 'avg_monthly_crime']].merge(
    df_feb[['LSOA_code', 'RF_Forecast']], on='LSOA_code', how='left'
)

# Export to SQL
cursor.execute("IF OBJECT_ID('actual_vs_forecast', 'U') IS NOT NULL DROP TABLE actual_vs_forecast")
conn.commit()

cursor.execute("""
    CREATE TABLE actual_vs_forecast (
        LSOA_code NVARCHAR(20),
        LSOA_name NVARCHAR(100),
        avg_monthly_crime FLOAT,
        RF_Forecast FLOAT
    )
""")
conn.commit()

for _, row in df_combined.iterrows():
    cursor.execute("INSERT INTO actual_vs_forecast VALUES (?,?,?,?)",
        row['LSOA_code'], row['LSOA_name'],
        float(row['avg_monthly_crime']), float(row['RF_Forecast']))

conn.commit()
print(f"Exported: {len(df_combined)} rows")

Exported: 150 rows

print(df_combined[df_combined['LSOA_name'] == 'Swansea 025F'][['LSOA_name', 'avg_monthly_crime', 'RF_Forecast']])

        LSOA_name  avg_monthly_crime  RF_Forecast
140  Swansea 025F          18.612717        229.4

# Recalculate correct monthly average per LSOA
df_monthly_total = df_forecast.groupby(['LSOA_code', 'LSOA_name', 'Month'])['Crime_Count'].sum().reset_index()
df_correct_avg = df_monthly_total.groupby(['LSOA_code', 'LSOA_name'])['Crime_Count'].mean().reset_index()
df_correct_avg.columns = ['LSOA_code', 'LSOA_name', 'avg_monthly_crime']

# Check Swansea 025F
print(df_correct_avg[df_correct_avg['LSOA_name'] == 'Swansea 025F'])

     LSOA_code     LSOA_name  avg_monthly_crime
140  W01001955  Swansea 025F              238.8

# Create corrected combined table
df_combined_correct = df_correct_avg.merge(
    df_feb[['LSOA_code', 'RF_Forecast']], 
    on='LSOA_code', how='left'
)

# Update actual_vs_forecast table in SQL
cursor.execute("IF OBJECT_ID('actual_vs_forecast', 'U') IS NOT NULL DROP TABLE actual_vs_forecast")
conn.commit()

cursor.execute("""
    CREATE TABLE actual_vs_forecast (
        LSOA_code NVARCHAR(20),
        LSOA_name NVARCHAR(100),
        avg_monthly_crime FLOAT,
        RF_Forecast FLOAT
    )
""")
conn.commit()

for _, row in df_combined_correct.iterrows():
    cursor.execute("INSERT INTO actual_vs_forecast VALUES (?,?,?,?)",
        row['LSOA_code'], row['LSOA_name'],
        float(row['avg_monthly_crime']), float(row['RF_Forecast']))

conn.commit()
print(f"Exported: {len(df_combined_correct)} rows")

# Verify 025F
print(df_combined_correct[df_combined_correct['LSOA_name'] == 'Swansea 025F'])

Exported: 150 rows
     LSOA_code     LSOA_name  avg_monthly_crime  RF_Forecast
140  W01001955  Swansea 025F              238.8        229.4