About this Project

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, some bars may have been removed. Please review the project Methodology below to learn how establishments were selected and how the map below was created.

The map below contains bars in the city of Milwaukee overlaid on the 15 city Alderman Districts. Clicking on each point activates a window with the bar name, street address, Yelp review (if available) and a link to the Yelp review (if available).

Utilize the bar name search and star review filters to locate specific bars or to sort bars by star reviews. To move the map, hold the SHIFT key down as you click the mouse.

---

---

Project Methodology

1. 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.

2. 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. Merriam Webster defines barrom as a room or establishment whose main feature is a bar for the sale of liquor and tavern as an establishment where alcoholic beverages are sold to be drunk on the premises. Restaurant is defined as a business establishment where meals or refreshments may be purchased. So if an establishment sells alcoholic beverages drunk on premise and serves food is that a tavurant? For this project, bar was defined as an establishment with the following characteristics:

   • Has a Class B Tavern License (more on this later)
   • Closes after midnight at least 2 nights a week.
   • Food may be served but it is bar food, small snacks and handheld bites engineered to be tasty yet forgettable, a pure sidekick to the night out compared to a menu with appetizers, entrees and desserts.

3. Begin data janitoring - Before loading the data into Tableau, the data set needed to be cleaned.

   Milwaukee has 6 liquor license types: Class "A" Fermented Malt Beverage Retailer's License; Class "A" Liquor and Malt Retailer's License (Liquor Store); Class "B" Fermented Malt Beverage License; Class "B" Special Fermented Malt Beverage License; Class "B" Tavern License; Class "C" Wine License. One can read the complete details about each.

   The CliffsNotes version is a Class "A" licenses allow retail sale of fermented malt beverages EXCEPT between the hours of 9:00 p.m. and 8:00 a.m. Given the definition of "bar" this project is using, establishments with this license were removed from the list.

   The Class "B" Fermented Malt Beverage License and Class "B" Special Fermented Malt Beverage License mostly pertained to restaurants that also did not fit the project criteria for "bar" and were removed from the list.

   The Class "C" Wine License pertains to sale, barter or giving away wine by the glass or in an opened or original container for consumption on the premises. Establishments with this license also had the license below, so to reduce duplication, establishments with this license type were removed from the list.

   Class "B" Tavern License is defined as "For the consumption of intoxicating liquors and fermented malt beverages on the licensed tavern premises." This license fit the criteria of "bar" for this project and these establishments were moved forward to the next round of data janitoring.

4. Adding Yelp Reviews - After removing the other licenses, 883 establishments remained with a Class B Tavern License. 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.

   Why not use the Yelp Fusion API?

   The original plan was to utilize the Yelp Fusion API to pull all of the bars in Milwaukee from Yelp using a Python script. However, the team soon discovered two stumbling blocks: First, currently the API does not return businesses without any reviews and secondly, not every establishment is on Yelp. In the course of searching through the establishment list, it was soon discovered that some establishments were not on Yelp, but were on Facebook, or Google Reviews and in some cases establishments were neither on Yelp, Facebook or Google Reviews. The only way to be certain each establishment meet the criteria for "bar" was to manually review the establishment.

   After manually reviewing each of the entries, 383 establishments meeting the project criteria of "bar" remained.

5. 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 data set split the address into four columns: HOUSE_NR; SDIR; STREET and STTYPE. These columns were concatenated and the contents lowercased using Excel and another column named zip was added, since the Geocode tool being used required a zip. A generic Milwaukee zip was used for all addresses and appeared to work without issue. The Alteryx Public Geocoding App was used to geocode the addresses (NOTE: The Alteryx Public Geocoding App has been discontinued from their Public Gallery, effective 2019-06-20).

6. 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.

   Before publishing, a review of the map showed that certain establishments had been given the wrong latitude and longitude in the geocoding process. Upon further review, it turned out to be all the establishments on Martin Luther King Drive. The team is unsure why this was the case, but speculates that the address as represented in the original data set "n martin l king jr dr" may have caused the issue as re-running the addresses with more of the address spelled out - "n martin luther king jr dr", seemed to resolve the issue.

---

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 is a feasible option.

Instead, at the end of each month, the latest liquor license data set will be pulled and the TAXKEY field will be used to determine which bars have closed. The most recent dates in the EFF_DATE field will be reviewed to locate new bars that have opened in the past month.

An effort to confirm a bars closing will be made before removing it from the data set and a running list of bars removed will be maintained. If an establishment has been removed in error, please let the team know.