A reproducible R and SQL pipeline over official Trinidad and Tobago crime statistics, 1975 to 2024. Raw government spreadsheets in, normalised SQLite database out, published as a static dashboard.
Live dashboard: https://nickchass.github.io/alertt-data/
This is the analytics companion to AlerTT, a crime-awareness app for Trinidad and Tobago. AlerTT separates verified reports from community reports; this is the verified layer, built only from official published statistics. No scraping, no estimates, no modelling.
The murder detection rate is flat at roughly one in seven. It has sat between 13% and 18% since 2015 with no trend, and was 13.8% in 2024. Detected murders have stayed near 70 to 90 per year while reported murders rose from 420 to 625: the denominator grew and the numerator did not.
Murders rose roughly tenfold over the long series, from 60 in 1975 to 625 in 2024. The rate per 100,000 rose from 31.1 to 45.7 between 2015 and 2024, so this is not explained by population growth.
Both are documented rather than plotted as trends. This is the more useful half of the analysis.
The post-2010 "crime decline" is almost certainly a recording change. Serious crime fell from 22,161 (2009) to 11,135 (2015). The fall is almost entirely property offences (breaking and burglary -60%, larceny -51%, robbery -52%) while murder fell only 11% and felonious wounding only 3%. Murder and wounding are the hardest offences to under-report. The year-to-year path is also implausible for real crime: -21.1%, then +12.4%, then -26.3%.
Most of the 2017 rise is an accounting change. Serious crime rose 1,620 offences from 2016 to 2017. Firearms possession offences moved from Minor Crime into Serious Crime that year and account for 1,162 of that rise, about 72%.
Full detail, including everything the data cannot support, is in FINDINGS.md.
4 CSO spreadsheets (data/raw/)
| 01_clean.R parse, tidy, handle source quirks
v
tidy data frames
| 02_build_db.R DDL + load into a star schema
v
db/alertt.sqlite 4 fact tables, 4 conformed dimensions
| 03_validate.R 7 checks, exits non-zero on failure
| 04_views.R SQL views, year-over-year via window functions
v
| 05_plots.R ggplot2 figures
| dashboard/ Quarto
v
static HTML dashboard
| Script | Does |
|---|---|
R/01_clean.R |
Parses each sheet by verified row and column position. Asserts the years it extracted, so a changed layout fails loudly instead of loading silently wrong data. |
R/02_build_db.R |
Creates and loads db/alertt.sqlite. Rebuilt from scratch on every run. |
R/03_validate.R |
7 checks against the sources and against internal consistency. |
R/04_views.R |
The SQL views the analysis reads from. Aggregation lives here, not in the plotting code. |
R/05_plots.R |
Figure functions shared by the static PNG export and the dashboard. |
A fact constellation: four fact tables at four honest grains, sharing conformed dimensions. One fact table per source publication, because the four files are separate publications with different vintages and there is no single division-by-offence-by-time crosstab anywhere in the data.
| Fact table | Grain | Rows |
|---|---|---|
fact_crime_category |
category x year | 40 |
fact_headline_offence |
offence x year, 2015-2024 | 30 |
fact_offence_annual |
offence x year, 1975-2022 | 1,296 |
fact_homicide_division |
division x year | 90 |
Dimensions: dim_year (50), dim_crime_category (4), dim_offence (28),
dim_division (9). Full design notes and the reasoning behind two schema changes the
data forced are in db/SCHEMA.md.
Seven checks in R/03_validate.R, all passing. The strongest one: murder counts are
compared across three independent CSO publications (the divisional file summed over
its nine divisions, the crime rate file, and the offence table) and agree exactly for
every overlapping year. Serious-crime offence columns also reconcile to the published
totals across all 48 years.
The four spreadsheets are laid out for human readers, not machines:
- Title rows, two-row headers, merged year cells, footnote blocks
- Two separate tables stacked inside one sheet with different headers
- Spaces used as thousands separators ("4 454")
- Year suffixes for provisional ("2015p") and revised ("2004r") figures, applied to different years in different sheets
- The same offence spelled inconsistently between sheets ("Larceny < $2 000" and "Larceny <$2 000")
- A formula error in the source, reporting exactly double the correct total in one block
- is parsed as NULL, not zero. It means the offence was not separately defined
that year, per the CSO footnotes. Robbery was only separately defined from 1992,
narcotics from 1990. Parsing it as zero would claim robbery was zero in 1975, which
the source never says.
Requires R 4.x and Quarto.
renv::restore() # install locked package versions
source("R/02_build_db.R")
source("R/03_validate.R")
source("R/04_views.R")
source("R/05_plots.R")Then quarto render for the dashboard.
db/alertt.sqlite is not committed. It is a build artifact, rebuilt from the raw files
on every run, and committing it would invite drift between the database and the code
that produces it. The raw source spreadsheets are committed, because those are the
inputs.
Central Statistical Office (CSO), Trinidad and Tobago, retrieved 23/07/2026 from https://cso.gov.tt. Underlying collection by the Crime and Problem Analysis Branch of the Trinidad and Tobago Police Service.
Used under the CSO Terms of Use, which permit republication and adaptation with attribution and prohibit commercial use. This is a non-commercial portfolio project. These terms are not a standard open-data licence, and calling them "open" would be inaccurate.
Full provenance, including per-file checksums and download dates, is in data/SOURCES.md.
All figures are reported crime, not actual crime. There is no monthly data in these files, so no seasonality analysis is possible. Divisional data covers murders only. The long series is not population-adjusted. 2021 to 2024 are provisional. See FINDINGS.md for the full list.