\n<\/p>\n
Part 1: Organizing the Data<\/span><\/p>\n 1. You\u2019re going to be collecting several files for this assignment, so it\u2019s a good idea to create an assignment folder where you can keep all the raw data.<\/p>\n 2. First, we need the list of all establishments licensed to serve alcohol in Washington state. The Washington State Liquor and Cannabis Board<\/strong> maintains this information and regularly publishes an Excel file with all licenses. (This is the same data we used in the previous assignment, but this time we\u2019ll be using the full spreadsheet provided by the state agency, and only for on-premise licenses.)<\/p>\n Download the spreadsheet called \u201cOn-Premises Licensees\u201d<\/strong> from the WSLCB Frequently Requested Lists page: http:\/\/lcb.wa.gov\/records\/frequently-requested-lists<\/a><\/p>\n The Washington State Liquor and Cannabis Board publishes a number of frequently updated lists and datasets.<\/p><\/div>\n 3. Open the spreadsheet in Google spreadsheets or Excel. (These instructions will show Google spreadsheets, but either one should work similarly.) Scroll down and note that there are a lot<\/em> of rows.<\/p>\n 4. Some businesses have multiple licenses, but we only want to show each location once. Going through this spreadsheet to delete duplicates one at a time would be extraordinarily time-consuming. Please do not do this.<\/em> Instead, we\u2019ll use a formula to identify duplicates and then filter them out.<\/p>\n Use an “if” formula to check for duplicate license numbers. Note that the screenshots here are from earlier versions of the data, so what you see should be similar, but may not match exactly.<\/p><\/div>\n Double-click the little blue square to copy the formula all the way down.<\/p><\/div>\n 5. Instead of mapping all 10,000 locations, we\u2019re going to show the total number of licensed locations by county. In this step, you\u2019ll create a pivot table to tally this.<\/p>\n Your pivot table should show all the counties and the total number of licensed establishments in each one, including a filter to keep out the ones labeled as duplicates.<\/p><\/div>\n Which county has the most establishments licensed to serve alcohol? The answer shouldn\u2019t be too surprising, because it’s also the county with the most people.<\/p>\n 6. Now copy the pivot table data to a new sheet, because we\u2019re going to add a few more columns. To paste it into the new sheet, make sure only the data is transferred (rather than the formatting and pivot table calculations). In Google spreadsheets, paste the data by going to Edit > Paste Special > Paste Values Only<\/strong>.<\/p>\n Paste special is helpful if you want to keep the data without the formulas or formatting, or if you need to do other tasks like switch rows and columns.<\/p><\/div>\n 7. Next, you\u2019ll add county population data to the spreadsheet. The most recent data available is from 2022.<\/p>\n Make sure to copy the most recent population estimate for each county, not one of the smaller jurisdictions within the county. This screenshot shows a previous year, so make sure you get the most recent year.<\/p><\/div>\n Take a moment to look through the numbers and appreciate why population is important here. We already saw that King County, where Seattle is located, has the highest number of businesses with liquor licenses. That’s not surprising \u2014 it also has tons of people! At the other extreme, Garfield County in southeastern Washington has only 3 businesses that serve alcohol, but it also has only about 2,000 people. So how can you even compare? You can divide one factor by the other to get a rate.<\/p>\n If you divide the number of residents by the number of businesses that serve alcohol, there are 625 people per business in King County vs. 741 people per business in Garfield County. So they’re not so different after all.<\/p>\n 8. With the population numbers, we can calculate a ratio to see how many licensees each county has when adjusted for the number of people who live there. Because there are many more people than places that serve alcohol, it’s common to create a ratio per 1,000 people<\/strong> so the numbers are not tiny fractions.<\/p>\n (You should end up with numbers between 0 and 5.)<\/p>\n 9. Download or save a copy of this file that includes only the final sheet, to avoid any confusion when we use it in Tableau. In Google spreadsheets, one good format is to save it using File > Download As > CSV<\/strong>.<\/p>\n Your data is ready for visualizing! You can pause here or continue to Part 2.<\/em><\/p>\n <\/p>\n Part 2: Make a Choropleth Map<\/span><\/p>\n 1. Open Tableau Public<\/strong> and import the spreadsheet with your data. If you downloaded a CSV file, use the \u201cText File\u201d option, otherwise you can use the Excel option or connect directly to your Google spreadsheet.<\/p>\n 2. As long as your data loads correctly, click the tab to go to Sheet 1.<\/p>\n 3. If Tableau has recognized your county names as geographic data, a little globe icon will display next to that data under Dimensions. If it hasn\u2019t automatically recognized that the county names are representing geographic counties, you can click on the menu associated with that dimension and assign a geographic role.<\/p>\n The globe icon means Tableau recognizes this as geographic data. If it doesn’t happen automatically, you can tell Tableau that these are counties.<\/p><\/div>\n 4. Drag the dimension for your county names to the Rows tray, then choose the \u201cFilled Map\u201d option from the selection of chart types on the right. (If the options don\u2019t automatically display, click the \u201cShow Me\u201d button in the top right.)<\/p>\n Tableau may need to work for a while to create your map…<\/p>\n 5. Once your map appears, you\u2019ll see the county boundaries. You\u2019ll also see a number of gaps. These are counties that have the same name as some other county in the U.S., so Tableau isn\u2019t sure where to display them.<\/p>\n Counties with the same names as counties in other states won’t map immediately, but you can tell Tableau to place them all in Washington.<\/p><\/div>\n 6. Now we\u2019re going to change the map colors and some other labels.<\/p>\n Edit the tooltip information that appears when you hover, among other colors and labels.<\/p><\/div>\n 7. Next we\u2019ll make a bar chart to show similar data.<\/p>\n Sort the data so the bars appear in order with the largest at the top.<\/p><\/div>\n 8. Finally, create a dashboard to display your two charts.<\/p>\n Written questions<\/span><\/p>\n In a separate document, answer these questions about the data and charts:<\/p>\n 1. Why would it be useless to map the data about the number of licensed establishments without<\/em> adjusting for population?<\/p>\n 2. Given what you know about different parts of the state, what might explain why certain counties have above-average or below-average ratios? (If you’re not sure, take a guess.)<\/p>\n 3. Do you think this data is more effectively communicated as a map or as a different chart type, and why?<\/p>\n 4. Suppose you work for media strategy firm and one of your clients is a small family-owned winery in central Washington. The winery wants to expand their brand presence around the state and develop connections with local restaurants and chefs. What are two insights you could point out from this map during a planning meeting with this client?<\/p>\n Submitting Your Work<\/span><\/p>\n Complete your assignment by submitting the following materials to Canvas:<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" In this assignment, you\u2019ll map liquor licenses for the entire state. Instead of using point data to represent the location of every establishment licensed to sell alcohol, we\u2019ll aggregate the data by county and create a choropleth map.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-2038","post","type-post","status-publish","format-standard","hentry","category-assignments"],"_links":{"self":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/2038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/comments?post=2038"}],"version-history":[{"count":5,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/2038\/revisions"}],"predecessor-version":[{"id":3320,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/posts\/2038\/revisions\/3320"}],"wp:attachment":[{"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/media?parent=2038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/categories?post=2038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/murrowcourses.com\/graphics\/wp-json\/wp\/v2\/tags?post=2038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-wslcb-data.jpg\")
\n
=if(B2=B3,\"Duplicate\", \"\")<\/code> (This formula assumes the license number is in Column B. If it’s not, change the B’s in the formula to the correct column.)<\/li>\n![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-duplicate-formula.jpg\")
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-copy-formula.jpg\")
\n
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/filter-pivot-table.png\")
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/paste-special.png\")
\n
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-copy-population.jpg\")
\n
=B2\/C2*1000<\/code><\/li>\n<\/ul>\n
\n![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-tableau-data-format.jpg\")
\n
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-change-ambiguous-locations.jpg\")
\n
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-change-map-labels.jpg\")
\n
![\"\"](\"https:\/\/murrowcourses.com\/graphics\/wp-content\/uploads\/2019\/05\/A7-sort-bar-chart.jpg\")
\n
\n\n