Good training.
That phrase from military jargon, which means learning something valuable from a difficult task, best describes what happened when an investigative reporter asked if I could help translate some prison data he was expecting.
I said yes without hesitation. As a researcher, I had worked with the reporter on other successful projects involving electronic public records.
Then I discovered the condition of the data. Some of it was zipped across several disks. Some of it was on printouts and had to be scanned and then edited. All of it would need translation to a database manager because of the expected large number of records.
The next few weeks promised to be interesting.
Project description
The reporter's task was to identify prison inmates from the Wisconsin prison system that had been exported to privatized prisons in our state. He suspected that two for-profit Tennessee prisons were receiving more murderers and sex offenders than other states that accepted out-of-state inmates.
He sought to verify this by using a master list of prisoners provided by the Wisconsin Department of Corrections along with the inmate population lists from prisons in Oklahoma, Minnesota, Tennessee and Texas. Matching the lists would pinpoint the identities and locations of out-of-state inmates as well as their offenses.
Data preparation
My task was to get the data in shape for the analysis, which turned out to be the easy part. After unzipping the disks, I used Microsoft Word and discovered that I had a 69-megabyte, field-length delimited blob of text that represented the entire inmate population in the Wisconsin prison system.
I used Microsoft Access and the field-length explanation sheet sent by the Wisconsin prison official with the data to create a tab-delimited text file.
I decided to use FileMaker Pro for Windows to analyze the data, because I am more familiar with its field calculation, database linking, layout and scripting (programming) capabilities.
Importing the data created a 72,610 record database with 53 fields of information for each inmate. This number reflected current and past prisoners. We identified inmates who had been released to exclude them from the data set, reducing the data to 20,673 records.
I created field calculations for text strings and dates and indexed necessary fields to aid in querying and sorting the records later.
Meanwhile, the reporter scanned the inmate lists he received from the five prisons being examined. This data was later proofread, formatted and uploaded to another FileMaker database.
The databases were linked by the inmate's Wisconsin Department of Corrections identification number, which identified the inmates housed in the five out-of-state prisons.
After we checked for the correct inmate name and date of birth for records that matched, we had 4,268 records that were needed for the analysis.
An immediate concern arose when we found conflicting locations for some inmates. The master list did not always reflect recent relocations. The reporter ran these down and the correct locations were added to our databases.
We were faced also with several numeric codes for prison names, sentences, offenses, race, etc. Two code types, prison name and offense identification, were used in creating lookup files that were linked to the main database. When codes matched, text was programmed to appear along with the code making it easier to identify locations of inmates and their offenses.
The offense identification codes were contained in as many as 16 fields within a record. This was confusing because some codes were duplicated within the record. We couldn't determine if any code was primary. Searching the database would be cumbersome if each field had to be included in a query.
Further examination of the data provided a solution, however. Another set of coded fields with numeric indicators for assaults, sex offenses, drug offenses, escape attempts and life sentences (indicating murderers) could be searched instead of the offense codes.
Testing the data
The reporter and I spent a few hours over a couple of days testing the databases to ensure that the results of queries made sense. We checked that the number of inmates in the out-of-state prisons matched the hard-copy printouts received from prison officials. We double-checked that the prison names and codes matched because the name data was keyed and copied into data fields. We searched the names of inmates from the various prisons to verify their locations, offenses and sentences.
Data analysis
I decided at this point to use FileMaker's scripting and layout capabilities to create the queries and reports based on the reporter's data requests. Querying the data was reduced to using the pull-down menu and selecting the appropriate search.
We used eight pieces of information from the records to create a profile of the type of prisoner being sent to our state:
Life sentences for murder convictions Sex offense indicators Assault indicators Drug offense indicators Maximum security rating of inmates Large sentences Prior felony convictions Escape indicators
Querying the data and then sorting by prison identification code and inmate counts verified the reporter's suspicions. Private prisons were indeed making Tennessee a repository for violent criminals exported from crowded public prisons in Wisconsin.
Of the five facilities under examination, one Tennessee prison was receiving the majority of Wisconsin's most offensive exiled offenders.
Once the reporter had the results of the data analysis, he was able to interview prison officials and present our findings for confirmation and comment.
The data gave us the names of some killers who were in prison for multiple charges in addition to murder. I searched the archives of newspapers in Wisconsin and the Associated Press to get background. We also used the names to find (in public records databases) relatives of inmates who might want to talk about an exported loved one.
Good training indeed.