A reproducible pipeline over fifty years of official statistics, and the finding that two of the most striking patterns in the data are not real
AlerTT, the crime awareness app in the previous case study, draws a line between verified reports and community reports. That distinction is the whole trust model of the app. But it raised a question the app itself could not answer: what does the verified layer actually say? Trinidad and Tobago publishes official crime statistics going back to 1975, and almost nobody reads them, because they arrive as government spreadsheets built for human eyes rather than analysis.
The question: what do fifty years of official Trinidad and Tobago crime statistics actually show, and which of the patterns in them are real?
The second half of that question turned out to matter more than the first. Official crime data is full of discontinuities that look like events. A category gets redefined, a recording practice changes, and the resulting step in the series is indistinguishable from a real change in the world unless you go looking for the cause. Most of the analytical work here went into telling those apart.
There was a second, more personal reason for the project. My thesis analysis was done in SPSS, and the dataset behind it is private and cannot be published. I needed an analysis built on genuinely public data, in R and SQL, that anyone could download and re-run. Not a certificate. A repository.
The plan was to use the national open data portal, data.gov.tt, which publishes crime by month and by division. It was under maintenance throughout the project and never came back. That single fact reshaped the entire scope.
What remained was the Central Statistical Office, which publishes four crime spreadsheets. All four are annual. None of them contain a single monthly figure. Two of the four analyses I had planned, a month-by-month national trend and a seasonality view testing whether crime spikes around Carnival and Christmas, became impossible.
Cutting them was the right call and it is worth being explicit about why. The alternative was to interpolate monthly values from annual totals, which would have produced a smooth, attractive seasonality chart made entirely of assumption. A chart that answers a question the data cannot answer is worse than no chart, because it looks like evidence.
Before writing any code I logged every file: source URL, publisher, download date, and a SHA-256 checksum, so any figure on the dashboard can be traced back to a specific version of a specific government file. That log also corrected an assumption I had started with. I had described the data as openly licensed. Reading the CSO terms of use showed it is not: republication is permitted with attribution, but commercial use is prohibited. That is closer to CC BY-NC than to an open data licence, and calling it "open" would have been wrong on a portfolio page.
Government spreadsheets are laid out for readers, not machines. These four had title rows, two-row headers, merged year cells, footnote blocks, spaces used as thousands separators, two separate tables stacked inside a single sheet, and one column containing a formula error that reported exactly double the correct total in every year.
The pipeline runs in four stages: clean the sheets into tidy data frames, load them into a normalised SQLite database, validate the result against the sources, then analyse. Aggregation lives in SQL views rather than in the plotting code, so a chart cannot quietly redefine what a measure means.
The single most consequential decision was how to read a dash. In these tables, - does not mean zero. The CSO footnotes state that robbery was only separately defined from 1992 and narcotics from 1990, so before those years the dash means the offence was folded into another category. Parsed as zero, the chart would show robbery flat at nothing until 1992 and then apparently exploding. It is parsed as NULL, and the line simply does not draw.
A zero is a measurement. A blank is an absence of measurement. Software will happily treat them as the same number, and the resulting chart will look completely normal.
The schema is a fact constellation: four fact tables at four different grains, sharing conformed dimensions for year, offence, crime category and division. 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. Merging them would have meant asserting rows that no source supports.
I designed that schema before seeing the real columns, and the data corrected it twice.
is_provisional flag on the year dimension. Then one file marked 2021 to 2024 provisional while another marked 2015 to 2022 provisional. It is a property of a published row, not of a calendar year. On the dimension, one publication's revision status would have silently overwritten the other's. The flag moved onto the fact tables.A third problem surfaced only because the database enforced a primary key. Reshaping the reported and prosecuted measures into columns failed on a key violation, and the cause was that the "revised" marker is applied to different years in different sheets of the same workbook. Carrying those flags through the reshape split single offence-years into two half-filled rows. Without the constraint the load would have succeeded and quietly halved several time series.
Seven automated checks run against the loaded database, and the script exits with an error if any fail. Row counts, referential integrity, and internal consistency are the routine ones. The useful one is a cross-check between publications.
Murder counts appear in three of the four CSO files, produced for different purposes: a divisional homicide table, a national crime rate summary, and a long offence-by-year series. Summing the divisional file across its nine police divisions and comparing to the other two gives an independent test of whether the load is faithful.
All three agree for every overlapping year, and the individual serious-crime offence columns sum to the published totals across all 48 years. One check deliberately holds back a column: the reported counts from a fourth file are never loaded, so that comparison tests two independent publications against each other rather than a column against itself.
Validation also resolved a labelling contradiction. Two sheets are titled "Prosecutions Instituted" on their tabs and "DETECTED" inside the table. Cross-checking the numbers settled it: both labels describe the same underlying figure, because operationally a crime is recorded as detected when a prosecution is instituted. Worth knowing before reporting them as two separate results.
The clearest result in the data is not a crime count. It is the gap between crimes reported and crimes solved.
The murder detection rate has sat between 13% and 18% for a decade with no trend. Roughly one murder in seven results in a prosecution. The mechanism is visible in the raw counts: detected murders have stayed near 70 to 90 per year while reported murders rose by half. The denominator grew and the numerator did not.
Murders themselves rose roughly tenfold across the long series, from 60 in 1975 to 625 in 2024. Because the rate per 100,000 rose over the same recent period, from 31.1 to 45.7, this is not explained by population growth. Murder is also the least under-reported offence in any crime dataset, which makes it the most trustworthy series available here.
At divisional level the picture is noisier than it first appears. Northern and Port of Spain recorded the most murders in 2024, but Port of Spain rose by 50 and Northern fell by 58 in a single year. Two divisions effectively swapped rank. Single-year divisional rankings in this data are not stable facts about places, and the dashboard says so directly rather than presenting a league table.
Two patterns in this data look like major findings. Neither is. Identifying them was the most useful analytical work in the project, and both are documented on the dashboard rather than plotted as trends.
Serious crime drops from 22,161 in 2009 to 11,135 in 2015. Read straight, that is a halving of crime in six years, and it is the most dramatic feature of the long series.
It is almost entirely property offences. Breaking and burglary falls 60%, larceny 51%, robbery 52%. Over the same period murder falls 11% and felonious wounding 3%. Murder and wounding are the hardest offences to under-report, because there is a body or a hospital record. A genuine halving of crime that leaves homicide essentially untouched is not credible.
The year-to-year path makes it harder still to believe: down 21.1%, then up 12.4%, then down 26.3%. Real crime trends do not move like that. Recording practices do. The CSO documentation offers no explanation, so the honest conclusion is an unexplained discontinuity in reported property crime that needs confirmation from the agency, not a story about falling crime.
Serious crime rises by 1,620 offences between 2016 and 2017, a jump of 14%.
Firearms possession offences moved from the Minor Crime category into Serious Crime that year. The category holds 942 offences under Minor Crime in 2016 and appears with 1,162 under Serious Crime in 2017, accounting for roughly 72% of the rise. The same offences, counted in a different column.
Neither of these is hidden. The reclassification is stated in a CSO footnote, and the property-crime pattern is visible to anyone who disaggregates before trending. But both are invisible if you plot the headline series and write a caption, which is what makes them a fair test of whether an analysis was actually done.
This is descriptive, not inferential. Counts, rates, year-over-year change and a detection ratio. There is no modelling here and no causal claim. The natural extension is to model the detection rate against divisional caseload and staffing, which would need data the CSO does not publish.
Reported crime is not actual crime. Every figure counts reports to police. Under-reporting varies by offence and is worst for exactly the offences people are least willing to report, so nothing here measures victimisation. The 2015 national victimisation survey would be the right instrument to pair with this, and comparing the two would be a genuinely interesting piece of work.
The property crime discontinuity deserves a direct answer. I can show that the post-2010 fall behaves like a recording change rather than a real one, but I cannot say what changed. That question goes to the CSO and the TTPS Crime and Problem Analysis Branch, and the answer would materially change how the last fifteen years of this series should be read.
The monthly layer is still missing. If data.gov.tt returns with crime by month and by division, the seasonality question becomes answerable and the divisional analysis extends beyond homicide. The schema was built to take a monthly fact table without restructuring, so that work would slot in rather than start over.