CPS: Prosecution Rates

This is my insider’s guide to how I did the story ‘Justice by Postcode’ that appeared in the The Times 23 November 2005.

First I filed an FOI request (27 September 2005).

I received a reply from the CPS as a CD-ROM containing twelve Excel spreadsheets with the data broken down as I requested by crime type, outcome and area. The data from September 2004 were minimal as electronic management only went online toward the end of that month. Here is an example of the raw data I received from CPS. This is for the month of August 2005 (Excel 158 kb) My overall impression of CPS was good: they were professional and met their obligations under the Act in a timely and helpful manner.

Once I had the data, I coordinated with Tommy Kass at the Danish International Center for Analytical Reporting (DICAR) to analyse just under a year’s caseload of statistics collected between September 2004 and August 2005. This was done by importing all the data into Microsoft Access and creating a new database that could handle the more sophisticated queries I wanted to make.

You can download a zipped verison of the Access database (winzip 208kb).

We made various queries and then exported the data back into an Excel spreadsheet with autofilters (Excel 168kb) that could be sorted in a variety of ways. I filtered the data into separate worksheets for each offence type and then sorted by the percentage of unsuccessful cases/total. Here are some helpful notes from Tommy on using Autofilter:

Use the black arrows in the first worksheet ‘From Access’.
Click on the black arrow to the right of “Offence_type”
Choose one of the types
Now you’ll only see the rows with numbers for this type
If you wish to have the rows sorted – go to “percentage” –
click on the arrow – over “All” and the line you’ll find “Sort descending” and “Sort Ascending”
Now you can copy the rows to another spreadsheet (Menu: Insert – Spreadsheet)
Go to the arrow in offence_type (now blue because the filter is active)
Choose “All” (in the top))
Now the filter is not active
Choose another offence type etc.

If you’re still confused, there is a bit more explanation here: http://www.uic.edu/depts/accc/seminars/excel2000-intermed/databases-autofilter.html

The end result was a series of league tables:

After the story came out in The Times, the CPS published its own league table.

Leave a Reply