Thursday, April 11, 2013

Exploring Hospital Patient Survey Data With Excel GeoFlow

As we add more OData features to FlatMerge, we are having fun discovering new ways to work with data. This post highlights 3 interesting things:
  1. The newly added FlatMerge OData $metadata support enables opening FlatMerge data directly into Excel Data Explorer. (note that FlatMerge offers minimal, but essential bits of support for $metadata currently)
  2. Some interesting data that is geocoded using Bing in Excel! Survey of Patients’ Hospital Experiences (HCAHPS) from data.gov.
  3. GeoFlow for Excel. From Microsoft: "GeoFlow lets you plot geographic and temporal data visually, analyze that data in 3D, and create interactive "tours" to share with others."
Here's how we loaded data from FlatMerge into Excel and used GeoFlow to see which hospitals are recommended by (sampled) patients, exploring using 3D maps!

First, we grabbed the text data from data.gov and changed the column names (they were way too long, like sentences). We also replaced "Not Available" to empty so calculations will be happy. Numbers were text percentages appended with %, so we removed that too.

Get the data and OData link here



Then we copied the OData link (http://flatmerge.com/Data/odata/9548f35f-7c02-4bca-a2e9-61d2b407fe92) and pasted it into the Excel Data Explorer -> From Other Sources -> From OData Feed


After clicking Apply, Excel queries the metadata about this OData feed, which just includes this one data feed (the survey data we've uploaded to FlatMerge). In FlatMerge, we provide one OData URL for each data file. So, choose the only table in the feed, which is identified by the GUID assigned by FlatMerge.  Click that and Excel queries the data to pull the top 100 rows ($top=100).


Click Done to let DataExplorer get the rest of your data. Once DataExplorer finishes downloading the rest of your data, click Insert tab -> Map (Map is the GeoFlow launcher, download it and install it here http://office.microsoft.com/en-us/download-geoflow-for-excel-FX104036784.aspx).
 
 
 
Start invstigating the data, visually, in 3D! One of the coolest things about GeoFlow is that you can make time series interactive tours of your data. This hospital survey that we grabbed is just one survey (there may be more), so no time series tour here, but the 3D navigation is still pretty cool.
 
Select "State" column for the Geography, then click Map It. GeoFlow calls Bing and geocodes the States' geographic centroids.

Then, to keep things simple , we add just one interesting data point, so select/click the PctRpt_YES_would_definitely_recommend_the_hospital column. That column appears in the Height dropdown. Select that Height again and choose "Average" to get the average respondent percentage who chose that that would definitely recommend the hospital.

We also clicked the "Chart" button to get a chart of the values. Drag around the map and have fun!

 
 
We hope this shows some interesting features of FlatMerge's OData support and how easy this functionality makes it to use with other tools like Excel and it's new toys like GeoFlow.