How do you interact with data?
Is it through a report or brief? A place where you’ll find the data has been selected, polished, and tailored for a very specific purpose. A purpose that may or may not match your needs.
It is through a series of tables or charts? Generic visuals with row and column after row and column of numbers and words. More information than you’ll see in a report, but somehow simultaneously feeling like too much and too little.
Is it through a spreadsheet? Like a table, but harder to interpret with the naked eye. Requiring additional approaches or tools to make any sense out of it whatsoever.
Is it through a website? Maybe a little more visual, but likely just a combination of reports, briefs, tables, charts, and spreadsheets.
Dashboards are decision making tools
They exist to provide specific information to the dashboard user. Good dashboards are strategic in nature, more than a report they provide feedback that directly supports decision making.
Think of your car’s dashboard. It gives you “to the second” feedback on your speed and fuel level. Because we need that information to successfully navigate the roads without running out of gas or getting pulled over for speeding.
We don’t need constantly updating information on tire tread, oil levels, headlight brightness, or air filter conditions. All we really need is a little light to pop on when something is wrong, so we can go get it checked out by an expert.
A data user interface is different
A user interface is how a person interacts with a digital application. Generally it’s the connection between humans and machines.
A human mind works differently than a computer processor. So we can’t just look at data the same way a computer does and expect results.
If we want to interact with data, we need an interface. And for most people that interface is in the form of reports, tables, and spreadsheets. But if we want to go deeper into the data, we need something more.
We need a data interface.
Starting with some data
So my first foray into contract research started back in the early 2000s as a data editor for a national study called the Medical Expenditure Panel Survey.
The study was an in-home interview of a representatively sampled population that required 5 home visits over the course of 2 years. The interviews were long, and involved collecting a lot of information on medical expenditures.
Not only that, the study went further, reaching out to medical providers and pharmacies for additional information to integrate with the interview responses.
And the results of that data collection, like so many other data collections in the world, ultimately end up on a website offering a collection of reports, data tables, and spreadsheets. You can find it at https://meps.ahrq.gov/mepsweb/.
Let’s narrow our scope a little and take a look at the Prescription Drugs data. Specifically the associated summary data tables, which you can find at https://meps.ahrq.gov/mepstrends/hc_pmed/. I ended up downloading the csv files for number of people who purchased each prescribed drug, and the total expenditures for each drug for the period of 1996 through 2006.
Cleaning and prepping your data
If you want to become a valuable data visualization designer you need to understand data and know how to properly clean, restructure, and prepare that data for analysis.
This can be a pain sometimes, but most data doesn’t come naturally in a really clean ready to pop into Tableau format.
I did a simple cleaning in Excel to get the data Tableau ready-ish. Then I brought the data into Tableau prep for a pivot restructure, join, and basic clean. This got me ready to bring the data into Tableau.
Exploring in Tableau
Now that we have the data in Tableau we can do a little bit of basic visual exploration. Simple one-filter dashboards and crowded line charts can provide some pretty quick insight. But more than that, I know have an interface I can use to fully explore the dataset.
Adding value to the interface
We have data on people and expenditures, one no brainer value add would be to create a calculated field that shows expenditures per capita. We just have to make sure to standardize the data first, especially since it comes to us with “people in thousands” and “expenditures in millions.”
Wow, now we’re seeing something.
You know everybody talks about the rising costs of healthcare, but it’s still amazing to see how it manifests in the data.
I wasn’t looking to find any particular story, I just wanted to create an exploratory interface for a bit data I knew a little about. But what I always find amazing, is that I don’t have to look for stories. If you restructure the data in just the right way, stories will find you.
Here are three stories I found in the prescription expenditure data.
Story 1: Insulin costs per capita are out of control
I can’t look at this data and not conclude that drug companies are likely killing our diabetics. The rise in insulin expenditures per capita is RIDICULOUS.
This isn’t just a, “well all drug prices are on the rise,” kind of thing. The insulin data when looked at through an interface is an outlier. The collective expenditure rise seems like it would be really hard to defend from a drug company point of view.
The most rapid rise came after the ACA went into effect in 2010. Here’s my guess at the drug company logic. Woohoo, more people have health insurance. These particular people need insulin to live so let us raise the prices, health insurance will have to pay. Hey look, they did, and they keep paying. Let’s keep these prices going up and up and up!!!
Next step as an analyst would be to find data on deaths among the diabetic population. I would also look for qualitative data on the rising drug prices. Here’s an article > The absurdly high cost of insulin, explained.
The person sitting next to me at my coworking space just snapped a picture of the chart because she wanted to share it with her diabetic friend who has been expressing concerns over cost.
Story 2: Allowing generics does in fact alleviate pricing spikes
Abilify (or it’s official formula name Aripiprazole) showed up as a clear outlier. The expenditures per capita consistently jumped until it was the highest priced drug in the dataset.
And what finally lead to the precipitous drop in per capita expenditures in 2016? Well, I’m going to go out on a limb and take a guess that it had something to do with this event that happened in June of 2015 > Generic Abilify Gets FDA Approval.
Story 3: Wait, how many people are getting prescribed hydrocodone?
Starting in 2004 the number of people getting prescribed Acetaminophen-Hydrocodone has gone through the roof.
Contrary to the other two stories, this one isn’t about high drug prices. The price of hydrocodone is relatively low, at least compared to comparable medications. Acetaminophen-Oxycodone (the second most popular drug in this grouping) grew to costing more than three times as much.
No, the story here is about the opioid crisis. The rise in prescriptions of these medications over the 2000s is having devastating impacts on communities across the nation.
But what event in particular happened in 2003 to spark the rise in use? Honestly, I’m still looking to find the spark.
In 2004 this combination was moved from a Schedule III to a Schedule II controlled substance. In theory, that should have had the reverse effect of what we see in the data. Any ideas?
Play with my basic data UI
Remember, this UI wasn’t designed to be pretty. It was designed to be useful. But if you want to play with the data I’ve compiled, please do. You can full-size the Tableau Public file I shared here. Or click on this link to access directly from Tableau Public.
Bonus: Making it Pretty
I could spend a bit of time trying to make my charts pretty in Tableau. But ultimately it’s far easier to do that in a graphics program like Adobe XD. Here is a version of the Insulin graphic shared above but designed for sharing on the web.
I’ll be relaunching diydatadesign soon (apparently me being out of a job is good news for the field as I keep doing things I’ve been wanting to do for awhile.) I’ll let you know when I have more information.