Less than 24 hours after CMS released its Medicare charge data, AHCJ member Rose Hoban and data journalist Tyler Dukes analyzed the data and developed a comprehensive story on cost differences for several of the most prevalent conditions across the state.
Dukes used the statistics to code a map with different colored informational pins so readers could see the numbers for themselves. He explains how he went about creating this interactive tool.
By M. Tyler Dukes
The massive dataset on medical costs released by the Centers for Medicare and Medicaid Services in early May finally gave reporters an opportunity to quantify the wide disparities in pricing between the nation’s hospitals.
I’m more of a data reporter than a health care journalist, but by working with Rose Hoban of North Carolina Health News, we were able to use a little analysis and some simple mapping tools for a next-day story that broke down the wide variance in medical costs across the state.
Step 1: Exploring the data
There’s a lot of room for analysis in the CMS data, but given our deadline, we wanted to start with the low-hanging fruit by identifying the outliers. We settled on a specific question: Which hospitals statewide charge extremely high or low for the most common procedures?
For that, I needed to do some cross tabulation, and Excel makes that easy with a feature called pivot tables. Using a simple “builder,” pivot tables allows you to drag and drop column headers, specify values you want and automatically calculate the relevant data.
In my case, I wanted the total number of discharged patients by procedure and each procedure’s average statewide cost. Pivot tables allows you to break calculations down into subcategories, so I subdivided each procedure by hospital to easily compare apples to apples.
Step 2: Breaking it down
With my cross tabs constructed, I sorted by the total number of discharges and focused on the top-10 most common procedures in North Carolina, which included things like joint replacement and heart failure.
This is where spotting the outliers comes in. Within each top-level procedure (blood poisoning, for example), I used my pivot table to sort the hospitals by cost and compared them to the state average. That allowed me to spot some interesting cases, like one hospital that charged more than twice the state average for treatment of gastrointestinal distress.
We eventually chose three of these procedures to highlight for simplicity’s sake.
But to visualize the data, we needed even more simplicity, enough to make our results easy to color code on a map. So I used Excel to compute the standard deviation for each procedure’s average cost. With that, I categorized each hospital as low cost, high cost or close to statewide average for each of the three procedures.
Step 3: Clear visualization
For a cut-and-dry mapping project like this, Google Fusion Tables was pretty ideal. My three spreadsheet files (filtered by state, procedure and saved to .CSV) already contained valid addresses, which Fusion Tables can automatically geocode and place on a map.
After uploading each file to a new Google Fusion Table, I specified the address column for geocoding and changed the map style to color each marker based on my categories. In this case, those were “high,” “low” or “within average.”
From this point, you can make the map public, get the code from Google and embed the map right into your site.
There are literally hundreds of additional stories to tell with this data, many of which require more than just staring at a spreadsheet.
But as Hoban did with this collection of maps and the facts we were able to glean from them, there are a few simple projects you can complete with the data to add context, deepen your reporting and point you toward more promising questions.
Tyler Dukes is a data journalist based in North Carolina. He is managing editor of the Reporters’ Lab at Duke University and an adjunct lecturer in the School of Journalism and Mass Communication at the University of North Carolina -Chapel Hill.
Download CMS data on hospital costs
AHCJ is now offering federal government data showing what hospitals across the country charge Medicare for the same treatment or procedure. The 2011 data includes bills submitted by 3,300 hospitals for the 100 most commonly performed treatments. This allows a basis for some local or regional comparisons and a starting point for stories on hospital costs.
Reporting on costs requires interview strategy, resources
The cost of medicines, devices, tests and treatment is such an important element of health reporting that it is included in AHCJ’s Statement of Principles: “Strive to include information about cost and insurance coverage in any reporting of new ideas in medicine.”
To that end, Brenda Goodman, AHCJ’s topic leader on covering medical studies, and Michael Schroeder, who covers health for Angie’s List Magazine, have contributed tip sheets to help reporters get that vital information. Goodman focuses on several resources where you might find pricing information, while Schroeder shares his strategy and the specific questions he asks sources about costs.
