Adventures with Open Data: Milwaukee BAR Review
In 2009, the Obama administration issued a directive requiring federal agencies to proactively publish government information online in formats that were open and machine readable. One of the results of this initiative was Data.gov, the home of the U.S. government’s open data.
The federal government open data initiative has trickled down to local governments too, including the city of Milwaukee which has their own open data portal. From this site you can download a plethora of data-sets about the city from traffic accident data to locations where people can dispose of unused medications.
Milwaukee BAR Review is a project intended to showcase utilization of open data, data janitoring and Tableau. This project in NO way intends to promote or encourage irresponsible or underage drinking.
This is not meant as the definitive list of ALL bars in Milwaukee, as in the data janitoring process, explained below, some bars may have been removed.
Below is an abbreviated break down of the project process. A more detailed explanation can be found in the methodology section on the project website.
As with any data driven project, it was important to #RespectTheProcess and be prepared for unexpected twists and turns.
- Locate the data — Thanks to the Milwaukee OpenData website, one can download the list of liquor licenses. Among the data in the set are trade name, street address (minus zip code), aldermanic district, police district and licenses type. The initial dataset included 1,332 entries.
- Define establishment parameters — Bar can have different definitions for different people, even Merriam Webster does not offer a lot of clarity as to what differentiates a barroom, tavern and restaurant. For this project, bar was defined as an establishment that (1) has a Class B Tavern License;
(2) closes after midnight at least 2 nights a week; (3) Food may be served but it is bar food. - Begin data janitoring — Before loading the data into Tableau, the data set needed to be cleaned. Milwaukee has 6 liquor license types, but only the Class “B” Tavern License fit the criteria of “bar” for this project, so only these establishments were moved forward to the next round of data janitoring.
- Adding Yelp Reviews — After removing the other licenses, 883 establishments remained. However, not all of these met the criteria for “bar”. These included establishments such as hotels, event halls, museums, retirement homes, gentlemen’s clubs and restaurants. To weed out the non criteria fitting establishments, each establishment name and address was entered into a search engine to double check that it fit the project criteria for “bar” and to get the URL for the establishment Yelp page and Yelp star review if applicable. After manually reviewing each of the entries, 383 establishments meeting the project criteria of “bar” remained.
- Geocoding the Addresses — With the final list set, the next step was to geocode the addresses to get the latitude and longitude Tableau would use to map each establishment. The Alteryx Public Geocoding App was used to geocode the addresses.
- Add data to Tableau — The geocoded list of establishments was loaded into Tableau Desktop and a dual-axis map was created with the shape file of Milwaukee Alderman Districts available for download from the City of Milwaukee. In the tool tip for each establishment, the star review rating was added (although the inability to do half stars meant 4.5 became 4 stars) along with a link to the Yelp review page if there was one. Two filters were added, one to search by establishment name and one to search by star reviews.
Coda
The liquor license data set is updated daily by the City of Milwaukee. The original plan was to access the data set via the web API so the map could be automatically updated when new establishments were added or removed. Given the amount of data janitoring required this no longer seems like a feasible option.
Future updates can use the TAXKEY field as a way to see which establishments have been added and removed.
If you have questions, comments, or suggestions please send them along and feel free to download the data set from Tableau to use for your own project.
