How to Make My Favorite Beers Visualization

How to Make My Favorite Beers Visualization

8/18/2015


Since I seemed to get quite a bit of interest about how to make My Favorite Beers Viz on Reddit, I thought I'd go ahead and create a post explaining the process. I'm going to first outline how to create it from scratch, then explain how to just update mine with your data (less work involved this way).

Creating the Full Viz

Step 1: Get the Data in Your Desired Format

I mentioned in my original post how I got the data, but I'll go ahead and explain it again. On Untappd's Website, you are able to become a "Supporter". Once you do that, you get access to all of your data in an .xls or .csv format. Please note that it costs $5 to become a supporter. Also, if you don't cancel your subscription, they will charge you that $5 every month. So, I just paid the $5, downloaded my data, then canceled my subscription so I don't get charged again. Once you download the data, go ahead and open it up in Excel.

Once you have the data opened up, you will see there are fields for the Brewery State and Brewery City. What we need to do is copy both of those and paste them into this Batch Geocode Website so we can create our Brewery Map later. You could try to just use the Brewery State and City to map in Tableau, but a lot of times, Tableau either won't recognize the city, or it will come back as an ambiguous location. So, just paste the city and state into the box on that website, choose Batch Geocode Output Options to be only Latitude and Longitude, then click Geocode. This may take a little while (its best to do about 100 at a time), but it will eventually spit out all of the Latitudes and Longitudes. Then, create two new columns in your spreadsheet and paste the values into there. You will have to use Text-to-Columns with a comma delimiter to split these out. Then you will be good to go ahead and load the data up into Tableau.

The only manipulation I did of the data once inside Tableau was a little bit of grouping for the Beer Type field. Untappd uses a lot of different beer types and I just wanted to group some of the similar ones up, so I wasn't stuck with 100 different groups. An example of this is combining all of the different IPA sytles into one over-arching category: IPA. This step is completely optional, but I think it looks much nicer only having 15-20 different groups when looking at the Style filter on the visualization.

Doing this is pretty easy. Just drag out the Beer Type field, then Ctrl-Click multiple styles you think would be grouped well together. Once you have multiple types selected, right click and choose "Group". You can see an example photo to the right. Then, you can right click your new group and choose "Edit Alias" to rename your group. Do this to all the styles you feel necessary to group. I used this website as reference.

Step 2: Creating the Top 5 Chart

This is probably the toughest part of this whole visualization. This is primarily because of using the stars to signify ranking. Thinking about it, you need not only a full star image, but you also need fractions of that star (1/2 of a star, 3/4 of a star, etc.) I learned how to do this by downloading this workbook. They did a really great job of getting this star rating system to work correctly in Tableau. So, to do this, you are going to need 9 calculated fields:

  • 1 Star Rating:

  • 2 Star Rating:

  • 3 Star Rating:

  • 4 Star Rating:

  • 5 Star Rating:

  • Position of 1/2 Star:

  • Position of 1/4 Star:

  • Position of 3/4 Star:

  • Index:

Next, we are going to right-click our index measure and choose "Convert to Discrete". After doing that, you can drag it out to Rows. Now, we are going to create a combined field of Beer Name and Brewery Name. To do this click on Beer Name, then hold ctrl and click on Brewery Name. Then, right click -> Create -> Combined Field. The reason we have to do this is because some beers have identical names across breweries. For example, Bell's, Mt. Carmel, and West Sixth all make a beer named Amber Ale, so creating this field allows us to differentiate between the three Amber Ales. Next, you can drag our new combined field out next to index, then right click it and uncheck "Show Header". Then, go ahead and drag out Beer Name and Brewery Name next to our Combined Field.

Now, we are going to drag out Measure Values onto Columns. You will see that it adds out all of our Measure values. You can see all of them listed in a box on the left hand side under the Marks card. We are going to take all of them out except for our Star Fields that we created before.

Next, under the marks card, change the mark from Automatic to Shapes. Then, drag out Measure names onto the Shapes card. At this moment, your Marks card should look like the image to the right.

Now we are going to need the actual stars themselves. The way I got the stars is a bit complicated. Earlier in the post, I mentioned this workbook which is how I learned to use this star method. Well, you are going to need to download that to get the star images. After downloading that, you have to download an excel workbook from the bottom of this article. What the workbook does is extracts all custom shapes used and places them into a folder.

Using the workbook is pretty straightforward. Be sure you have macros enabled, then click on Select Tableau Workbook and find the workbook that we just downloaded. Note: this must be a .twb, NOT a .twbx. If the workbook is saved as a .twbx, just resave it as a .twb file. Then, in the excel workbook, it will show you where it saved the folder containing the images. Go to that folder and cut it (ctrl+ x). Then, find your Tableau Repository (usually in My Documents). In your Tableau Repository, there will be a folder called Shapes. Open up that folder and paste the folder containing the images into it. Next, go back to your Tableau Workbook and click on Shapes under the Marks card. Under Select Shape Palette, choose the Stars folder that should now be present. Then, go ahead and assign Star Rating 1-5 to the full star, Position of 1/2 Star to the half-star, and so on. It should look like the image to the left.

Next, right click the Combined Field on the Rows Shelf and click Sort. In the dialogue box that pops up, choose to sort Descending by the Rating Score Field and change the Aggregation to be Average, not Sum, and click OK. Then, right click the index pill and change Compute Using to Table(Down). Now, ctrl-drag that index pill to filters and select only 1-5. Now you should be showing your Top 5 beers by Avg. Rating. Next, drag your Type/Style group to filters and choose all of them. Then, right click that pill and choose Add to Context. We need this for the filters later, or things will get messed up when we remove certain styles. Now, you can change the size of the stars by going to Size on the Marks card and dragging the slider to the right. I like to make them about as big as I can while still showing the whole star. Then, I like to get rid of the X-Axis label by right clicking on the axis and unchecking Show Header. Right now, your chart should look like the image below (but with your own beers).



Step 3: Creating the Breweries Map

Creating the map is pretty straight forward, especially if you've created maps in Tableau before. The first thing you're going to do is drag out the longitude field we created in the very beginning onto Columns and the Latitude field onto Rows. Then, click show me and choose Symbol Maps. Next, drag Brewery Name onto Detail, and you will see your map populate with all of the different breweries you've had a beer from! I personally like to use the Lasso tool to select all of the breweries in the United States, then right click and choose Keep Only. This way, you can see much more detail as opposed to leaving the other breweries around the world on there. This is completely up to you to decide which areas to leave into the map.

Next, drag Rating Score onto color. Then, right click Rating Score on the Marks card and change the measure to average. Now drag Number of Records onto Size. The bubble on the map are now sized based on how many beers you've had from that brewery and colored based on their average ranking. For this map, I like to click on Map up on the toolbar, then click on Map Options. Once Map Options are opened, change the Style to Dark. Then, click on color on the Marks card and change the color to a shade of Orange to match the Untappd theme. Finally, drag Brewery State to detail. We will need this for a filter later on the dashboard. That's all for the map for now!

Step 4: Creating the Breweries Table

Creating the breweries table is easy work compared to creating the Top 5 Beers table, primarily because we already did all of the hard work. To make this, start by dragging out Brewery Name, Beer Name, and the Type/Style Group we created earlier onto the Rows shelf. Next, drag Measure Values to Columns, and like we did before, take all of the fields out except for the Star Fields. Again, like we did earlier, change the marks to shapes and drag out Measure Names onto the Shapes mark. The stars should already be assigned to the correct Measure Names, since we did that in the Top 5 chart. Now right click on the Brewery Name pill on the Rows Shelf and choose Sort. We are going to sort descending by average Rating Score again. Then, sort the exact same way for Beer Name. Change the size of the stars to be a little bit bigger and get rid of the X-Axis header again. Finally, drag out Rating Score to detail and change the measure to Average. Again, we will need this for a filter on the dashboard, which we are now able to construct!

Step 5: Putting It All Together on a Dashboard

We start this by creating a new Dashboard and change the size in the bottom left hand corner to Large Blog Portrait. I like to first put on my title. To do this, simply drag out a new text box, type in your title, and change the font size to something bit (~38). Next, I inserted this image and placed it to the left of my title. Next, we are going to start dragging out our charts. As you drag them out, go ahead and rename them by double clicking the title and entering a new one. First, we are going to drag out the Top 5 chart underneath your title and picture. You can get rid of the legend that will come up on the right hand side. Next, drag out the Map sheet underneath of our Top 5 chart. You will see a size and color legend come up on the right hand side when you do this. What I like to do, is to click one of them, then click on the drop down arrow at the top of the box and choose Floating. Now, you can drag this box wherever you want. I like to put it on an empty part of the map to save space. Then, do the same with the other legend. Next, drag out the Brewery Table underneath of the map. You may need to do some resizing of the three charts to make it look okay.

We are going to now add on the three filters: Style, Brewery State, and Average Rating. For Style, click on the dropdown of the Top 5 chart and go to Quick Filters -> Style/Type Group. Now, on the filter that pops up, click on the dropdown and change the filter type to Multiple Value (Dropdown). Then drag the filter underneath the Title and Untappd logo. Do the same thing with the map chart, but this time choose Brewery State. Again, change the filter to Multiple Value (Dropdown) and drag it next to the Style filter. Finally, do the same with the Brewery Table, but choose Avg. Rating Score. You can leave the filter as a slider, then drag it next to the other two. Next, I'd recommend changing the Titles of each of these.

If you want to change the colors like I did for mine, first click Dashboard up on the Toolbar and go to Format. Then, on the left hand side, the first thing you will see is Dashboard Shading. Go ahead and change that to the gold-ish color. Now, to change the colors of our two tables, right click on one of them and choose Format. Then on the format bar on the left hand side, click on the paint bucket. Now, you will see the same shading option, where you can choose a lighter gold color. Do the same for the other chart, and you're finished!

Updating My Viz with Your Own Data

If you don't want to follow the monstrosity of instructions that is above, there is a simplier way. First though, you need to read the first two paragraphs under Step 1 above. This will outline how to get the data and also how to get the Latitude and Longitude of all of the breweries (you need this for the map to populate). After you've done that, go to my post with the visualization and on the bottom of the viz, you will see a download button. Go ahead and download that and open it up. You need Tableau Public for this, so if you don't have it downloaded yet, go ahead and do so. Once you have the workbook downloaded and opened up, go to the left hand side of the screen. Here, you will see all of the individual sheets used to make this dashboard. When you hover over one of these sheets, you will see a little blue box with an arrow coming out of it on the right hand side (pictured on the right). Click on this, and you will be taken to that individual sheet.

Once on this sheet, you will see a Data tab on the top left hand side (next to Analytics). Underneath of that, you will see the data source (it should say checkin-report..). Right click on that and choose "Edit Data Source". Now, you will be taken to a new screen where you will see all of the current data. First, you need to get rid of all of this. To do so, hover over the check-in report pill on the big white box towards the top. You will see a red X, which you should go ahead and click. Now, click on Data on the top toolbar and choose Edit Connection. This will bring up a dialogue box which you should point towards your data file. The data on this screen should be updated. Now you can click on Dashboard 1 on the bottom, and you should see the viz with your data in it. Hopefully this works okay for everyone. I tested it out and it worked fine for me, but there is always bound to be problems.

Thanks for reading! If you have any comments or questions please feel free to post them below.


Check out more posts in the Tableau category!