Posted in Uncategorized

Library Space Assessment in Tableau: A Step by Step Guide to Custom Polygon Maps and Dashboard Actions

This slideshow requires JavaScript.

Introduction

Libraries need a tool for understanding observational space count data that’s as flexible as the spaces they’re trying to assess. Endless rows of numbers are difficult to interrogate or understand with any degree of nuance – whether that’s observing how one area is used compared to the whole, or how popular a particular kind of seating is by time of day.

Interactive visualization provides one solution to this problem. By building a map of the space and connecting it to the data, libraries are able to quickly see patterns and query them on the fly. The ability to subdivide and cross-section your data allows you to answer the questions you’re particularly interested in and supports finding new answers to the questions you develop.

This tutorial presents two methods for building interactive Space Assessment dashboards in Tableau. If you already have observation data, you should be able to select the method that best matches your needs and the format of your data. If you’re considering a space assessment project, thinking about the desired visualization can help identify collection methods and sampling schedules that will better answer the questions you have about your space.

Sample Projects

Overview

If you’re fairly comfortable with Tableau, don’t like detailed instructions, or just want to jump right in, here’s a quick reference version of the tutorial below:

  1. Gather the key information for your project: floorplan map, seating capacity for each area, and the observation data
  2. Use the GitHub tool or Drawing Tool to trace polygons over your floorplan. If you are making the map with icons, also place those coordinates at this time
  3. Export the polygon data. Give each shape the same name as the space has in the observation data. If you have icons, create a new column to tag those coordinates with the supplementary information, such as the type of seating the icon represents
  4. Combine all of your data sources into a single Excel workbook with separate tabs for each data source. Ensure that the Location field shows up in each tab, and that the naming is consistent across all sources.
  5. In Tableau, drag the X Coordinate to Columns and the Y Coordinate to rows. Change the mark type to Polygon, then add Path ID to Path and Location Name to details.
  6. For icons, also drag the Seating Type to details, and duplicate the X and Y pills to create a dual axis map. Change this mark type to Shape, and assign each seating type the appropriate icon from the custom shapes window. NULLs along the edge of the polygon should be assigned a transparent pixel.
  7. Color underlying map by location (categorical color scheme) or number of people (sequential color scheme) and add the underlying floorplan under the Background Images menu
  8. Synchronize axis and uncheck “show header” to remove axis

1. Data Prep

The instructions for Tableau assume you are working with a normalized dataset. If you don’t have normalized data, follow these instructions to manipulate your data into the appropriate format. If you’re not sure what normalized data looks like, check out the example below:

Cross-tab Data 

crosstab
This is the format that you often see when you export observation data from Google Forms or other survey programs. In order to build the maps and visualizations shown above, we need to normalize the data so it looks like the example below.

If you’ve used Tableau’s “Data Interpreter” to normalize data in the past,  you will still need to pre-process the data before uploading, as the built in tool only supports one pivot and we’ll end up needing four.

Normalized Data

normalized

If you don’t have normalized data but are comfortable with manipulating data, check out this quick normalization trick in Excel and check back in at the next step. Otherwise, you can follow along with the sample cross-tab data to work though the data processing steps below:

  1. Unmerge all cells
  2. Fill in empty dates with the appropriate value
  3. Insert a column between Time and “Printing Scanning Info Desk”
  4. Concatenate Date and Time using the following formula: =CONCATENATE(A3,”?”,B3) and copy formula to all cells
  5. Using another concatenate formula or with copy/paste, combine the location with the study information. Instead of:  merged we want: unmerged
  6. Use the method here to normalize your data. Select only the concatenated field on the left and the newly combined field names, as in the example below:highlighting
  7. In your new, normalized workbook, insert new columns between “Row and Column” and “Column and Value”
  8. Select the “Row” column and navigate to Data>Text to Columns>Delimited and type ? into Other.  If Excel reformatted your dates into a series of numbers, you can manually format the cell to ensure your dates have copied correctly. Repeat for the “Column” column.
  9. Rename your fields as below:
    • Row → Date
    • New Column 1 → Time
    • Column → Location
    • New Column 2 → Group/Individual Study
    • Value → Count

2. Custom Polygon Coordinates

Tableau uses longitude and latitude for its out of the box GIS mapping. When you need a custom map such as a floorplan, it uses an XY coordinate plane to recognize and draw polygon shapes.

Using an external tool to find the edges of your polygon is much easier than trying to find this data within Tableau. When you use these tools, you will generally upload an image of your map to the program and draw (by clicking on the corners of the location) the shapes you want. The program will then give you the coordinates of every point you clicked and the order in which you clicked them, which you can give to Tableau as your XY coordinate plane.

The following instructions walk you through the two most popular tools of this type. You will need:

Bryant B. Howell’s Open Source GitHub Tool

This is an open source program that runs off your hard drive to allow you to draw polygons over an image. You must store the code and the desired images in the same folder.

  1. Create a new folder on your computer for storing mapping files and data
  2. Follow this link and save the code provided to the folder you just created. (I right-click the Raw button and “save link as” from there, but you can also copy the code into a program such as Notepad ++ and save as an html file type)
  3. Copy the map(s) of the desired location into your mapping folder.
  4. Open the html file from your folder and follow the instructions from there.
  5. If you are making a normal location map, draw a polygon around each location where you collected data
  6. If you are making a map with icons, draw the polygon around each location, and also select the desired locations for your icons before closing your shape. Don’t worry if your polygon shape starts to looks funny – we’ll fix it in Tableau!
  7. After you “Output Polygons,” copy/paste the data back into Excel

Power Tools for Tableau: Drawing Tool

This tool is a free, browser-based polygon tool that works with online images with a direct URL. You have to provide your name, email, phone number, and company in order to access the tool, but it also has features such as grid overlay, snap to existing point, and align with previous point.

  1. Follow this link and provide the requested credentials.
  2. Share the URL to the desired map and click “load image”
  3. For perfect corners, make sure the”Align with previous point” and “show grid line” options have been selected
  4. Follow the provided instructions
  5. If you are making a normal location map, draw a polygon around each location where you collected data
  6. If you are making a map with icons, draw the polygon around each location, and also select the desired locations for your icons before closing your shape. Don’t worry if your polygon shape starts to looks funny – we’ll fix it in Tableau!
  7. Click “Output Polygons” and copy/paste the data into Excel

Shapefile

If you have a shapefile map of your library or location, you could also use one of the Shapefile To Tableau Conversion tools to generate the coordinates your map:

Polygon Processing 

Once you have the XY coordinates for your polygons, create a new tab in your space count data file and paste the data into the worksheet.

  1. Rename the Shape or Identifier field to “Location” (or whatever you call “Location” in your data file)
  2. Check that the names of each shape mirror the name of the location in your data file. Capitalization, spelling, and spaces matter!
  3. If you are making a map with icons, create a new column and tag each icon location (the XY coordinates where the icon will appear) with the type of activity you want the icon to represent. Leave the points marking the edge of the polygon shape blank.

Custom Shape Library

If you are creating a polygon map with icons, you will need to add at least one custom shape to your library. If you would like your icons to be more informative than “triangle” or “square,” you can search for custom icons at www.flaticon.com or www.iconfinder.com or use the package below:

carrel       Carrel

opendoor      Study Room

roundtable       Table

workstation       Computer Workstation

lounge       Lounge Chairs

floor       Sitting on the Floor

transparantpixel       Transparent Pixel (for edges)

Once your desired shapes are selected, you need to create a custom shapes menu. Navigate to Documents>My Tableau Repository>Shapes. Create a folder called Library Icons and add image files of the desired icons. Be sure to include a transparent pixel (file provided above), as you will need this to hide points along the edges of your shapes.

3. Custom Mapping in Tableau

Join all worksheets on Location field. If you have used the same field names across all data sources, Tableau should detect the connection automatically. However, you can also manually edit the connection using the Join menu below:

blend

Polygon Location Map

  1. Drag the X field to Columns and the Y field to Rows
  2. Change the mark type to Polygon
  3. Drag Path ID to Path and drag Location to Detail or Color.

Dual Axis Polygon Map with Icons

  1. Follow the instructions for the Location map. Then:
  2. Drag seating to the Detail mark
  3. Copy the X on Columns and Y on Rows by selecting the pill and Ctrl + Drag Right, or by dragging the field onto each shelf a second time
  4. Right click each pill and select “Dual Axis” to combine the shapes into a dual axis chart
  5. Change the secondary mark type to Shape
    axis-polygon
  6. Edit the shapes so that all NULL values are the transparent icon, and the remaining seating types are assigned an appropriate icon

Background Images

To add the underlying map, go to Map>Background Images and select your data source. Click “Add Image” and select the file from your computer. You will need to know the size of your final image – you can check the exact size in pixels in a program like Microsoft Paint – and add the total value of the width (X) with 0 for Left and the width of the image for Right, and the height (Y) with the bottom at 0 and the height of the image for Top. Hit OK.

You can modify the transparency of your image under the Color menu to see the underlying map.

You can also choose whether you want the map to be colored by the number of students in each area (a Choropleth map) or to use categorical color by location. If you choose to color by location, it will be easier to identify each location in the corresponding charts, but harder to see high and low traffic patterns in the building. The reverse is true for the Choropleth map, as in the example below:

categoryvschoropeth

You may want to modify the look of your polygons by adding or removing the border or halo around each polygon. You can find these options under “Effects” in the Color menu.

Depending on how you shape your data, you may need to take the AVG instead of SUM for each vertex, or disaggregate measures.

avgdisaggregate

4. Visualizing Capacity  

Because the questions asked in space assessment projects can be so contextual, I am not going over how to create specific views in Tableau. I assume most of the people interested in custom mapping are already pretty familiar with the basics and can create the supporting visualizations they need. However, because we used a blended data source, I do want to go over how data blending affects working with capacity.

When we blend data sources, duplication of fixed values becomes a slight problem. Tableau automatically uses SUM to aggregate, but a capacity of 25 will be duplicated for every count. After 4 counts, it would show a SUM of 100 (4*25). You can use “Average” to quickly compute the correct capacity for each location, as shown below:

capcity-duplication

You could also use a FIXED LOD calculation: {FIXED [Location]: AVG(Capacity)}. Note that with LOD calculations, any filters must be Added to Context to work as intended. I have never run into a problem with the standard Average calc, however, so I tend to stick with that.

5. Dashboard Actions in Tableau

Once you’ve created the desired visualizations to correspond with  your map, it’s time to make it interactive! Drag your map and your visualizations onto a new dashboard and arrange the windows as you see fit.

In Tableau, adding interactivity is done through Dashboard Actions. This is what tells Tableau that an action taken in one workbook should effect other workbooks on the same dashboard. Tableau has three kinds of Dashboard Actions:

  • Highlight: A highlight action makes the selected field pop out across all views in your dashboard. Tableau draws your attention by changing the field name to yellow and dimming all other information in the worksheet.
  • Filter: A filter action excludes all values that do not match your selection. It can be used to look at a particular section of data in more detail by limiting the available information to only the values that match your selection.
  • URL: A URL action directs you to an external link when selected. You can use this to direct users to a more detailed explanation of your observation methods, for example, or to tweet about the results they find – but these actions are not particularly relevant to this tutorial.
  • Tableau also allows interactivity with dropdown filters, checkboxes, and radial menus that can apply across all worksheets or selected worksheets on your dashboard. Like URL actions, these can certainly be used in your space assessment dashboard, but are not covered in this tutorial.

In general, remember that highlighting will preserve context while filtering will reduce the amount of information available.

There are two ways to apply dashboard actions in Tableau.

Generated Dashboard Actions

Generated dashboard actions are applied from the dashboard itself, not from the Dashboard Actions menu. These can be generated by adding a color legend or selecting “Use as Filter” on a worksheet.

Highlight Actions are automatically applied from color legends and,  by default, highlight all values on your dashboard with the same color palette.

Filter actions can also be applied easily; when you select a workbook on your dashboard, there’s a small icon on the upper right that you can toggle on and off to use as a filter:
filterimg

Generated dashboard actions work well on many dashboards, however, they work less well in this context. Having a large custom map effectively replaces a small color legend as the obvious trigger for a highlight action, and the filter toggle automatically filters all charts by all fields. This combination means you could end up with situations like below:

filtereffect
You can see that a selection on the map excludes all bars that show the count of students in other areas and resizes the axis to the maximum space available. Because there is no matching field to highlight in the line chart, the values are greyed out and hard to see.

The solution to this is to use the Dashboard Actions menu.

Dashboard Actions Menu 

To access this menu, go to your dashboard and select Dashboard>Actions. This will pull up the list of current actions on your workbook, where you can see the menu-created and Tableau (generated) actions all in one place. Selecting Add Action will allow you to select the kind of action you prefer.

dashboard-actions-menu

The Source Sheets section allows you to select the worksheet(s) in which your user must make a selection. Highlight actions can have multiple sources; filter actions can only have one source – but you can add multiple filters that will allow every worksheet to affect every other sheet. The Target Sheets allow you to specify which workbooks the action affects.

The two actions above would lead to a result like this:

dashboard-actions
With a filter applied to the line chart, you can see how the number of users change over time in the selected area only; using a highlight action on the bar chart preserves the context of the rest of the building, but helps the selected value pop out.

 

In general, your steps to creating a dashboard action are below:

  1. Navigate to Dashboard>Actions and select “Add action”
  2. Select either the filter or highlight action to reduce (filter) or feature (highlight) information
  3. In the Source Sheets menu, select the sheet(s) where the action must be taken by the user
  4. In the Target Sheets menu, select the sheet(s) where the effect of the action will be shown.
  5. Decide whether the action affects all fields in your workbook or selected fields and modify your action accordingly.

Troubleshooting

If your actions aren’t working as expected, these questions may help you find the problem:

  • Is everything suddenly dim? Is your highlight having no effect at all? Check if the desired field (e.g. Location) is on all sheets. If not, add the field to the view and see if your action works now.
  • Did your data disappear? Check if your filter affects All Fields. If so, there may be fields (like time of day) that aren’t showing on some views (like the map). Use Selected Fields to set the fields that should be affected and ignore those that shouldn’t.
  • Did you filter when you meant to highlight? You’ll have to create a whole new action – there’s no easy way to switch one the action is created.

Resources

A list of all tools, links, and downloads mentioned in this tutorial can be found below:

A note on versioning:

In Tableau 10, you can use data blending across all locations. You will be able to drag separate files (data, polygons, capacity) and blend on location.

For Tableau 9 and earlier, adding the data as different tabs in an Excel workbook will still enable you to blend as above. Avoid using relationships to merge data across data sources in this case, as will give you some difficulty as you try to compute % occupancy across two data sources (capacity and data)

Additional Resources

There are quite a few other resources and tutorials to work with custom polygon data. When I was learning, I consulted the Creating Custom Polygons on a Background Image instructions at Tableau and Behold. The Tableau blog also came out with a recent post about custom polygons and US Congressional Districts and they points to several other projects and tutorials at the end of the post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s