Mash Up Minneapolis and New York City Restaurant Inspection Data
The City of Minneapolis has made restaurant inspection data available to the public as open data. A useful example of how this data can be used and exposed to the public can be found at this link, which provides a web based map that displays the results of the restaurant inspections on a map. The Open Twin Cities open data user group has set up a Socrata portal that exposes the raw data at this link.
New York City also makes restaurant inspection data available to the public. Comparing and contrasting NYC and Minneapolis restaurant inspection data requires a data mashup since both data sets are from different sources. Keep reading below to see what happens when you mash up these data sets.
Details about data modeling for the New York Restaurant Inspection results can be found at this page. The new Open Data Bits page which outlines the process by which to model the Minneapolis Restaurants Inspection Data for a mashup is at this page. While the two data sets have some different attributes, they can be mashed up using the Date and Zip Code fields. The resulting data model would looks as follows:
Once the data model has been created, measures can be created that compare the two data sets by Date and Zip Code values. The screenshot below shows an Excel report displaying the data mashup using Power Pivot. The slicers on the left filter NYC and Minneapolis data on the Pivot Table and Pivot Charts. Measures such as Critical % (% of inspections with a critical flag) and Inspection Counts can be compared by month, by year, by zip code, and by state. Take note that the criteria to flag something as “critical” may be very different for both cities and you would need additional requirements and data definitions from the agencies that curate both data sets before using this mashup as a direct comparison of results:
Another example of how this data can be visualized uses Zip Codes and addresses to plot the data in a mashed up Power View for Excel report:
Drilling into a zip code reveals data for individual restaurants:
Restaurant inspection data that is made open by other cities can be mashed up with both of these data sets for more robust analysis. The same methodology used in this post and the corresponding links can be applied to any data containing Date and Zip Code fields. The resulting data mashup can be used for trend analysis, or for simply browsing restaurants that you might visit.
Feedback, questions, and suggestions are always welcome. You can contact us through our feedback form or via our Twitter account @OpenDataBits.