Using Google Fusion Tables to Analyze Medicare Datasets
Thanks to the wonders of the Open Data initiative, you can download healthcare datasets from data.gov and analyze them using Google Fusion Tables. This method offers powerful visualization tools. And did I mention that it’s free?
At the bottom of this post you can see an embed of a scatterplot showing the relationship between two variables from the 2008 Chronic Conditions PUF. The 2008 Chronic Conditions PUF is an aggregated file in which each record is a profile or cell defined by the characteristics of Medicare beneficiaries. A profile is defined by all combinations of age category, gender, various chronic conditions, and dual-eligibility status of the beneficiaries. Hence, the number of rows (or records) in the CMS 2008 Chronic Conditions PUF represents the number of unique profiles in the Medicare population. For each profile many claim-related variables are provided in the form of averages. The CMS 2008 Chronic Conditions PUF represents 100% of the Medicare beneficiaries provided in the 100% Beneficiary Summary File for reference year 2008. The 100% Beneficiary Summary File is created annually and contains demographic, entitlement and enrollment data for beneficiaries who were: documented as being alive for some part of the reference year of the Beneficiary Summary File, are entitled to Medicare benefits during the reference year, and enrolled in Medicare Part A and/or Part B for at least one month in the reference year. You will need the Data Dictionary for this dataset if you want to work with it, by the way.
To do this sample graphic, all I needed to do was download the data file, which is a simple .csv format. I then created a new Google Fusion Table by logging on to my Google Account, going to Google Drive, and then clicking “New > More > Fusion Table (beta)”. From there I browsed to the .csv file that I already had downloaded. Like magic, the file was uploaded to my Google Drive. I then changed the viewing permissions to “Public” since I want this demo to be visible to anyone on the net. While this demo is public, you can restrict access to files if you want to. A workgroup can use this method to upload datasets to a team folder on Google Drive and let team members play with the powerful visualization tools.
To create the graphic I went to the visualization options and selected the variables I wanted to view. All standard types of visualization are available.
The scatterplot I created is embedded into this blog post via an iframe, which makes it seem to be part of this page. The graphics are actually being served via Google in real time. A benefit of this is that if you change the data on your Google Drive, all graphics built on that data will automatically pick up the new values, regardless of where they are being displayed on the web. You can view individual data values by scrolling over the data points.
The X-axis shows the average Medicare payment for inpatient services per beneficiary for beneficiaries enrolled in Medicare Part A for 12 months in the calendar year (AVE_IP_PAY_EQ_12).
The Y-axis shows the average Medicare payment for outpatient services per beneficiary for beneficiaries enrolled in Medicare Part A for 12 months in the calendar year (AVE_OP_PAY_PB_EQ_12).
Not a very exciting graph, but it does show that outpatient visits are WAY cheaper than inpatient stays. If we want to reduce the total cost of Medicare spending we would be making more use of proactive outpatient care to help people stay out of the hospital. Dollars spent to help someone keep going on their own save money in the long run.
In case the iframe does not work (sometimes they are blocked by browsers) you can also see it using the following link: