Tom Cooper died face down in a pool of blood on his kitchen floor. Virginia Gregg was found dead in her closet. And a co-worker discovered Fred Lookabill dead on the steps of his front porch.
North Carolina medical examiners ruled all three died from natural causes.
They were wrong.
Forget what you’ve seen on television dramas. North Carolina’s Office of the Chief Medical Examiner investigates suspicious deaths much like it did 40 years ago.
Medical examiners don’t rush to the scene. (They don’t go at all 90 percent of the time.)
They don’t wield high-tech equipment. (Often medical examiners have to buy their own cameras, gloves and shoe covers.)
And they don’t always inspect bodies in close detail. (In one out of nine deaths, medical examiners don’t view the bodies at all, the Charlotte Observer found.)
Obtaining the data
Observer reporters first requested data from the state medical examiner’s office in 2012, when several families sued the state over allegations that examiners botched investigations into the deaths of their loved ones.
At first, the requests were limited. Reporters asked for specific fields over a specific time. But each request was answered with inaccurate or incomplete data.
After one request, for instance, the state wrongly said it did not store data it routinely collects. After a second, the state left off several year’s worth of records. After yet another, it failed to include the unique identifier that linked tables.
It took six requests, two certified letters to state officials, two conference calls with attorneys, the threat of legal action and more than a year for the Observer to get North Carolina’s entire Medical Examiner Information System database and structure file.
The state charged the Observer $720, but the newspaper refused to pay. The paper contended it shouldn’t be forced to pay for the data because the state created a filing system that not only made it difficult to extract public records but also made it difficult to separate public records from confidential information – such as bank account and Social Security numbers.
The MEIS database provided a complete history of every death North Carolina medical examiners investigated since 2001.
It was exported into more than 130 delimited text files, some of which were look-up tables while others contained up to 300,000 records.
Reviewing the data
Medical examiners are called in to investigate when the stakes are highest: suspicious, violent, accidental and unattended deaths. Those account for about 10,000 deaths a year in North Carolina.
The state’s MEIS database tracks those cases from start to finish.
The Investigation table, for instance, lists the basic details of a case, including:
- The name and characteristics of the decedent
- The name of the medical examiner
- Where – if at all – the medical examiner inspected the body
- The provisional manner of death (homicide, suicide, accidental, natural or undetermined)
The Decedent table gives a deeper look, including:
- Whether an autopsy was performed
- The pathologist who performed the autopsy
- The final manner of death
- The final means of death (gunshot, hanging, poisoning, asphyxia, etc.)
The toxicology tables listed what substances were found in the decedents and review tables showed which pathologist closed a case by signing a death certificate.
The database included numerous date fields: the decedents’ medical history, investigators’ qualifications, and how much medical examiners, pathologists and body transporters were paid for each case.
A number of unique identifiers link the tables. The most common identifier tracked each case. Other IDs tracked things such as the decedents or toxicology specimens.
Cleaning the data
The MEIS data was clean. Coded records (reporters used smaller lookup tables to populate these fields) and unique IDs reduced problems with the database’s occasional misspellings and blank records.
But there were some quirks in the data.
Ten tables, for instance, had a manner of death field, which sometimes changed as a case progressed. Four tables had an autopsy field, which also changed at times.
Interviews with the state’s data specialists determined which tables to use and when.
Also, for as comprehensive as the MEIS database is, it includes only a manner of death and a means of death. It does not include a specific cause of death. For that information, the Observer requested the mortality file from North Carolina Vital Records.
That database housed about a million records, including the causes of death in all medical examiner cases.
Analyzing the data
To analyze the MEIS data, Observer reporters use Microsoft Access, SQL Server database managers and Excel spreadsheets.
Because of the sheer number of tables, and because some matches were one-to-many joins (which created duplicate records), reporters created separate file directory folders for each topic they investigated.
One folder, for instance, only looked at how medical examiners investigated the deaths of elderly people. Another only looked at payments to medical examiners.
The individual folders prevented reporters from mistakenly counting duplicates or using the incorrect field that appeared in several tables.
Lawsuits and news tips guided some of the queries. Standard SQL GROUP BY and COUNT(*) queries gave reporters other leads.
Which county, for example, had the highest percentage of cases in which medical examiners never looked at the body?
SELECT queries allowed reporters to drill further. Who were the medical examiners in that county? Did those medical examiners get paid even when they failed to thoroughly investigate a death? SUM queries totaled how much they made.
Reporters also queried the data for red flags in an investigation. They looked to see if a manner of death was changed from natural to homicide. They looked for cases in which a body was exhumed and cases in which the state violated its own guidelines. North Carolina guidelines, for instance, call for autopsies on all charred bodies.
Reporters also used the DATEDIFF function to find cases that took years to close, leaving grieving families waiting for answers.
The occasionally populated “narrative” field proved invaluable. Reporters searched this field for phrases such as “police suspect murder” and then researched how thoroughly the medical examiner’s office investigated the death.
Publishing the series
About six months after obtaining the MEIS database, the Observer published a five-day series on the failings of the state’s death investigation system.
Investigative editor Jim Walser oversaw the project. Reporters Ames Alexander, Elizabeth Leland, Fred Clasen-Kelly and I wrote 17 stories, nearly all of which relied heavily on findings from the data.
Later, reporters produced a searchable database in Caspio, five interactive maps and graphics in Tableau Public and numerous other charts and breakout boxes for the paper’s print version.
A day after the series concluded, North Carolina Gov. Pat McCrory announced that he was asking lawmakers for an additional $2 million for the underfunded medical examiner’s system. He also asked that the state increase the $100 fee paid to medical examiners.
The following month, the state Senate requested a formal review of the state’s death investigation system.
Gavin Off is the database reporter at the Charlotte Observer. Prior to joining the Observer, Off worked as data reporter for the Tulsa World and Scripps Howard News Service in Washington, D.C. He can be reached at [email protected]