Introduction to Tableau

This lecture is designed to introduce students to Tableau so they can see how it can be used to explore a data set.

Versions of Tableau

There are many flavors of Tableau, but they are essentially the same program.

• Tableau Desktop is the full version. It can connect to files and databases. You can save your work to your computer at any time and later publish it online on Tableau Public. It can be quite expensive if buying directly, but you can get a free educational license as a student or a free professional license through an IRE membership.
• Tableau Public is both a website and software.  The software is essentially the same as Tableau Desktop, but you can't save files to your local computer; you can only publish files online on the Tableau Public website. Anyone (including yourself) can download workbooks from Tableau Public and continue to work on them.
• Tableau Server is a business class version of the software. Not really applicable to us.

Getting started

Launch Tableau and you are first taken to the screen to connect to your data. Choose your file type and open the file. For this we'll choose Microsoft Excel and then go find the Travis Gun Deaths.
• We'll use a data set about gun deaths in Travis County. I'll talk about and show the original data in class, and explain how it was cleaned up for use in Tableau.
• Download this data file for this lesson: 04 Data - Travis Gun Deaths
• The next thing that comes up is a view of your data. This is the time to check that all your data types are correct … that numbers, strings, dates and locations are noted properly. Once all is checked (Travis Gun Deaths should be fine) click on the big red Go to Worksheet button.
• Note on the left panel the Dimensions and Measures shelves. All the categorical data like Death Type, ZIP and such are Dimensions, while all the quantitative values that you can add up have been put on the Measures. (It's actually a bit more complicated than that, but that will do for now.)
• In our case, Age has been counted as a Measure, but we'll never add ages together, so we'll drag and drop it up to the Dimensions area.

Exploring through Show me

Tableau can be both very easy to use and quite complicated. The Show Me palette can help guide you on ways to explore your data.

• If you don't see the whole Show Me palette at the top-right of the screen, click on Show Me so it drops down.
• Click on the Date dimensions, and the hold down the Command key and click on the Number of Records measure. (I'm assuming Mac use throughout … use Ctrl where applicable.)
• As you click on measures and dimensions together, the Show Me palette will light up the chart types that might work with your data. Since we are looking at a date, let's choose the bar graph. (Left side, 3rd down.)
• What you get (probably) is a simple bar chart that shows the number of records by year, probably showing horizontal.
• When I look at date graphics, I like the dates to show at the bottom, a more vertical alignment, so I can see the years go from left-to-right. You can swap the rows and columns on the chart by clicking on the swap button, which looks like this:
• We can change that to see it by month by using the dropdown in the Date field that is now on the columns shelf and pulling down to the Month that also shows the year. (It shows May 2011: see screenshot.)
• Now we could use some help reading the number of deaths for each month, so let's add some labels. Take the Number of Records measures and move it to Marks palette and drop it right on top of the square called Label. This should add the number of records -- i.e., number of deaths -- to each bar.
• If you have trouble discerning which exact month a bar represents, you can hover over that bar with your cursor to see a tooltip that gives details about that data. Now we can see April 2010 had that most deaths with 14.
• I wonder if all April's are that bad. We can check by changing the Date field on the Columns shelf to the other Month listed there, where it just shows "May". Now we are looking at deaths by month for all three years, and over that period April was fairly normal, but November is pretty high.
• Could this November trend be holiday stress? Why might explore a little further by dragging the Death Type dimension to the Marks shelf and drop it on Color. This now divides our bars so we can see all the suicides, homicides and accidents. November is showing the most suicides, so maybe that is a lead to talk to mental health professionals.
• If you find the bars difficult to discern all three types of death together like this, you can change the chart type on the Marks shelf by using the dropdown there to move from from Bar to Line.
• If you don't like that move, you can use the back button at top left to undo that move and any many more previously. You can then use the forward button to return. Stop where you wish, and then change the name of the sheet at the bottom to something useful by double-clicking on Sheet 1 and renaming. It works similar to Excel sheets.
• Down by the sheet names, click on icon that show a bar chart and + sign on it to start a new blank sheet. (The other one starts a dashboard, which we'll talk about later.)
• Let's do one last thing with dates. Click on Date and Number of records and choose the Line graph from the Show Me tab.
• Go to Date in the columns shelf to change from YEAR and pull down to combined years set under More and choose Weekday. This gives you the most deaths by day of the week. Drop your Number of Records measure on the Label tab on Marks and then rename your sheet Weekday.
• Last thing with this section: We've done a bit of work, let's save the workbook so you don't lose it. Under File > Save or click on the disc icon. Save it where you can find it again.

Another bar chart with Age

Bar charts are pretty telling. Let's make another one.

• Create a new sheet
• Click on Age. Hold down Command and click on Death Type and Number of Records to add them to the selection.
• Click on the stacked bar chart in Show Me. (3rd row in the middle).
• This shows you the deaths by type, by year. In this case, I think it is more readable as horizontal bars, so use to flip the axis.
• Drag Number of Records measure to the Label tab on Marks.

This gives us a good view by date, but when we use color to show the death type, we lose the total number of deaths for each age. Let's add that back.

• On the bottom axis is the label Number of Records. Right-click on that to get a pop-up menu and choose "Add Reference Line, Band or Box."
• Set the following:
• Scope: Per cell
• Value: Number of Records by SUM.
• Label: Value
• This adds the total number to the end of the bar. Sometimes it looks funky if there is only one color bar, but I don't know how to fix that ;-(
• Save your sheet as "Age, type of death"

Null values

Note the Null value for age. We could exclude this to get rid of it, but it's better to first understand why it is there. Let's find out what record it is.

• Click on or hover over the Null record until the tooltip comes up.
• Roll over the the table looking icon on the far right of the tooltip and click on it.
• This brings up a "View Data" that shows you the Summary of all the records used in that bar.
• Click on Underlying and that will show you the individual records that make up the bar. In this case, that records has a blank where age is. We would want to go back to our original data source to find out why. (We simply could not determine the age of that victim.)

Maps

Tableau has a lot of map functionality built into it. If your data set has geography like state, city, zip and such, it will try it's best to map that data. Sometimes you have to help it. (When you get outside of common shapes like that to say, school districts or oil fields, it is much harder to use Tableau.)

• We'll start with a new sheet, click on Zip and Number of Records and go to the Show Me pallet and choose the Map with the blue dots. This draws a dot by ZIP code where the dot is bigger based on the number of records.
• You'll notice there is a dot way out in California. This is a case where someone was shot in Austin but died years later out of state. We will exclude it from the map as an exercise to show how. Click on the dot (or draw a sqare around it with your cursor) to select it, then in the tooltip click the Exclude link. It can be challenging to get that tooltip to come up, but you'll get the hang of it.
• Once that record is excluded (it's not deleted, only hidden from view) the map will refocus to Travis County, though it's kinda hard to discern that with the way the map looks, so we'll work on that.
• In the menus across the very top of the screen, go to Map > Map Options.
• This will bring up a palette on the left where you can change a number of things.
• Change the Style to "Normal."
• Click on Streets and Highways
• Now we can understand what we are looking at a bit more. Let's see how the type of death plays out on the map. Drag the Death Type onto the Color mark and you'll see how they fall upon the map.
• We can isolate those Death Types by creating a filter. If you click on the dropdown for Death Type dimension and choose Show Quick Filter, then you will get a palette on the right-hand side of the screen. (You might have to close the Show Me palette to see it).
• Now you can choose the death types individually and see how they fall upon the map. Let's add some things to make it easier to see:
• Let's add the Number of Records measure to the Label mark so you can more easily see some values, too.
• Click on the Size tab in Marks and make the side larger. This makes all of the dots bigger so the smaller ones are easier to see.
• You might see some interesting trends worth pursuing … what part of the cities see more homicides? What is up with 19 suicides up by the Arboretum?
• You can add some demographic data that Tableau stores into the map with a few steps.
• Under the Map menu, go to Background Maps and choose Tableau Classic.
• Make sure Map Options are up (Map > Map Options).
• Click on Data Layer toward the bottom of the Map Options, and look under US Households to "Household Income (median)". This colors the background map by income, but default to By State. Change By to Zip Code. Change to Block Group to see the difference.
• Zip is probably more appropriate. You can clearly see how the homicides cluster in areas of lower median income. Not sure you can call it causation, but clearly worth further reporting.
• Set your filter to show all the Death Types, then name your sheet "Map."

Tables

Sometimes a table of numbers is still the best way to show data, but Tableau can add some visual cues to help you read it.

• Start a new sheet.
• Click on Date, then hold down the Command key to add Gun Type and Number of Records to your selection.
• In the Show Me palette, choose the colored table at top right.
• You should get a table that shows deaths by year, by guy type. Clearly handgun use is highest, as shown by the darker shade of green.
• Save that sheet as "Gun type"

Dashboards

You can put your various sheets together on a dashboard to show a common thread. You can use filters to build interactivity within the sheets. Dashboards can be tied together as a Story. Tableau can become a powerful presentation tool.

• Create a new Dashboard by clicking on the tab at the bottom that looks like squares and a + sign:
• Click on the sheet name "Map" and drag and drop it onto the dashboard.
• Click on the sheet name "Age, type of death" and drag it toward the bottom of the dashboard like you are dropping it on the bottom part of the map. You'll see areas turn grey where the sheet will land when you drop it. If you drop at the bottom, the Map part should split in half and the bar chart should take up the bottom part.
• Take "Gun type" sheet and drop it on the left half of the bar chart.
• Take the "Weekday" sheet and drop it into the space under "Gun type". You'll have something like the screenshot in this section.

Global filters

This is just a start on a dashboard. You can do a lot to make the dashboard prettier and display more information, but we'll save those nuances for another lesson. We'll show one last thing with the dashboard … using a filter across multiple sheets.

• Click on the Death Type filter at the top right to see the dropdown for that filter.
• Click on the dropdown and choose Apply to Worksheets > All Using This Data Source.
• Now, when you check and uncheck the boxes in the Death Type filter, all the data on the dashboard filters according to your selections.

Cleaning up your dashboard

• Make sure you have a headline and well-written chatter to explain what you are trying to communicate. Don't forget to source your data.
• You'll note that you can float different sheets, legends and filters on your dashboard, which helps make nice looking displays. Be wary of trying to float everything. I've found it is best to use tiling for all your sheets, getting them in their general location first, then float only the legends and filters as necessary.
• Tooltips are useful for your readers, and a good place to add information, but they are also a place where you can look sloppy. Click on the tooltip mark to edit.
• Add data: Drag categories onto the tooltip mark to add to it.
• Delete stuff: If you have a map, Lat and Long will end up in your tooltip. Readers rarely need to know those coordinates, so you can usually delete them.
• You can format fonts, spacing, alignment and such in your tooltips, including adding things like percent signs.
• If you've deleted something from the tooltip and need it back, you can use the Insert menu. You can also hit the reset button to get everything back and start over.
• Once you are done formatting your tooltip, uncheck "Include command buttons" unless your readers really need them. They could end up excluding data unintentionally.

• You can edit worksheet titles for formatting and content, or hide them if you don't need them.

Publishing online

There are a couple of things to clean up before you publish online.

• 
  Multiple sheets and dashboards will show up as tabs unless you hide them. So, once you are done, right-click on the sheet name and "hide sheet." You can return to editing it later by clicking on the "Go to sheet" icon in the Dashboard palette, or by choosing it from the dropdown on the upper-right of the sheet on your dashboard.
• The name of your dashboard will be the name of your tab, so write it cleanly.
• The name of your workbook (the file name you saved it as) will be the name of the project online. You can change it later, but might as well get it right now.
• When you are ready to publish, go under Server to Tableau Public and log in. If you don't have an account, then you will need to create one. You can later update your profile to use it as a showcase for your work (like to help get a JOB!)

Tableau resources

• Google it. Tons of Tableau knowledgebase articles are online with good SEO.
• Tableau Training and Tutorials
• Outside vendor sites, like The Information Lab (including the "Show Me" series), Alteryx and more.
• Blogs like DataRemixed, JewellLoree (both Tableau employees), Viz of the Day (download and study how they are made). Here is a list of others and a really great blog roll.
• Grace Labriola's list:
• Alan Smithee Presents
• Eagereyes
• Paint By Numbers
• Tableau Love
• Ryan Robitalle
• Tableau Mapping
• VizCandy
• VizWiz
• Freakalytics
• Drawing with Numbers
• Ben Sullins

Categories: Handouts, Spring 2015, Tableau

Excel basics every reporter should know

(This Spring 2015 handout is the main one of the lecture, so worth posting separately. Some formatting has been challenged in the post to the web. It's also found here.)

These are basic tips for Excel that many experienced users may already know. That said, I know sometimes the simplest things can be frustrating when you don’t know how to do it, so that’s the genesis of this document. Almost all of these tips are much easier to show on a screen than to explain in text, so don’t be shy in asking to see these in action.

Column widths

Sometimes you’ll see a weird exponent number or ##### or a instead of a number or date you expect:

Excel is telling you it can’t show you the whole number or date because the column isn’t wide enough.

• Move your cursor to the right dividing line between the two columns right between the column letters until you see your cursor change to a line with two arrows going right and left. Click and drag the column wider.
• If you do that same thing but double-click on the line, it will change the width of the column to fit the longest content.
• Or, click on the triangle that is to the left of the A column and above the number 1 row, which will select everything in the sheet. Then move your cursor to the right dividing line between two columns and double-click to widen all the columns to their maximum width.
• Right click on any of the column letters and select “column width” from the pop-up window. You can then enter a character width for all the columns. (If you have more than one or all columns selected, it will change them all.)

Freezing rows and columns

Sometimes you need to scroll further into a file, but don’t know what the columns and rows mean anymore because they are stuck at the top and left of the file. It’s easy to “Freeze Panes” in Excel so you can carry those descriptions with your scroll.

• In many cases, you just want to freeze the top row or first column, and there are special menu items there under the View ribbon to Freeze Panes for that.
• If you want to freeze more than the top row, put cursor in the cell furthest to the top and left that you still want to move, and then choose View > Freeze Panes > Freeze Panes. This will freeze anything to the top and left of that cell.
• To unfreeze panes, you can find it under View > Freeze Panes. (On Mac: Layout ribbon > Window group > Freeze Panes

Filtering

One of the most useful things Excel can do is to filter your data so you only see what you want.

• Filter multiple columns: If you have a single cell selected, or all cells selected, and go to Data > Filter it will put the drop-down filter arrows on all the columns. You can then filter one column, then go to another column to further filter the rows.
• Filter a single column: Click on the column letter to choose the column. Go to the Data ribbon to Filter, which puts a drop-down arrow for your column. Click on the arrow to get a list of all the unique fields in your column. You can choose which ones you want to see.

Sorting

Equally useful is to sort your data on one or more columns. You can sort all your data or just a selection. (But be very careful you aren’t sorting such a small portion that you mess up your data. Always choose entire rows.)

• Select all or some rows of your data, then go under the Data ribbon to Sort so you get the sort wizard. If your data has headers and the box is checked, the “Sort by” column should show your headers.
• You can add levels, so you can sort by City, then Last Name within each City.

Copying cells

Magical copy point

There are too many ways to copy date in Excel to list them all, but here are some useful tidbits. There is a magical handle in a selected cell that allows you to copy that content in different ways. Put your cursor on the lower-right-hand dot of the selected arrow (see the arrow at right) and your cursor will become a thin black plus sign (as opposed to a thick white one.)

• When on the magical copy point, you can click and drag along other cells and it will copy the cell into the highlighted sections
• If you double-click the magical copy point, the cells below will be filled with the content, for a long as there are filled adjacent cells.
• If you highlight two sequential numbers and copy to other cells (either method) it will try to keep up the same number sequence. So a cell with 1 and an adjacent cell with 2 will copy into further cells as 3, 4, 5 and so on.

Other copying tips

• If you have multiple cells in your clipboard, you must select the same number of cells into which to copy. Or, you can copy multiple cells into a single cell and the data will “fan out” into adjoining cells. Be careful you don’t overwrite other data!
• Watch that sequential numbers. If you use the magical copy point on ZIP codes, it may try to make new numbers that are sequential!
• You can copy filtered and sorted data into a new sheet to work on just that data without affecting the original data.

Multiple windows

It can be frustrating working on multiple files as once if you don’t know how to “show” them at the same time. All the tools you need are on the View ribbon. (Mac: Layout ribbon > Window group.)

• Click on Arrange All to resize all your windows so you can see them all. You’ll get to choose how to stack them.
• Split screen allows you to windows into the same file, so you can see the top and bottom at the same time without scrolling. Don’t get confused!
• View Side by Side allows you to look at two files at once, scrolling them together if you wish. Very useful for comparing two files.
• Switch windows simply allows you to choose another open file and bring it to the front so you can see it. (Mac: main Window menu.)

Data warning: What are the green triangles!

Green triangles mean Excel is telling you the *MAY* be a problem with your data. Getting the green triangle does not guarantee there is a problem. Excel is just noting that something isn’t following a pattern or isn’t formatted as expected. To see what the warning is, select the cell, and then hover over the exclamation point. You can choose the little arrow to make suggested corrections, if needed. Some common problems:

• Numbers are stored at text. Sometimes you want numbers as text, so this might be OK.
• Adjacent formulas don’t fit the same pattern. If you have a column of percentages, and then one of the cells doesn’t follow a logical pattern (you might have a total), then Excel let’s you know there may be something amiss.

In short, let the green triangles show you where to check for problems, but be confident when you are purposefully breaking a pattern.

Opening a .txt or .csv file

Sources will often send data that isn’t in an Excel file. If you open it in Word, it’s crazy, but Excel can do some good stuff with this, especially fixed-column data.

Importing delimited files

.csv stands for Comma Separated Value, which means the file is really like a spreadsheet, but they used commas or something similar to divide the lines of text into rows. Excel is great with these.

• Launch Excel, and then go under the File ribbon to Open and then find your file. (Sometimes you have to change the File Type down by the Open button to “All Files” to see your text file.)
• You’ll get the Text Import Wizard. If  you are opening a CSV file, you’ll want to choose “Delimited” on the first screen.
• At the next screen you can choose which character is creating the columns. If Tab doesn’t separate the columns like you see above, then uncheck it and try Comma. If it’s anything other than those two, usually your source can tell you what the delimiter is. (Or it might be a fixed-width file, which is handled below.)
• Once you find the right delimiter, go to the next screen. Click on each column and see what “column data format” that Excel suggests, and change it if it is obviously wrong. General is fine for both text and numbers.  Dates are the most common changes.
• Click Finish and you should be looking at a spreadsheet of nice, neat rows. If not, start over and try different settings.

Importing fixed-width files

Some systems spit out data using spaces to create columns of text. Excel can turn these into spreadsheets, too.

• Open the file from Excel. The first screen may guess that it is a fixed-width file, but you can guess by the preview as well.
• On the next screen is where you choose to create columns. You can click on an existing break line to move it to the right or left as you see fit in the preview. You can click anywhere in the preview window to add a new break line, or double-click on one to remove it. Make sure you check all across the width of the file.
• The Next screen is much like above, where you choose what the data format should be for each column.
• Once you finish, you may have some cleanup to do, but you’ll be a lot further ahead than using a regular text editor!

Insert anything

The secret to inserting anything (cell, column, comment) is usually to right-click.

• Insert a column by right-clicking to the column to the right of it and choosing insert.
• If you have text in your clipboard, it will paste that into the created column.
• If you select more than one column and then right-click to insert, then you’ll add the same number of columns.
• To insert a row, right-click on the row header and choose insert.
• If you want to insert more than one row, select the same number of rows below, then right-click to insert.
• To insert cells, right-click and choose insert. You’ll be asked how to move the other cells affected. BE CAREFUL not to screw up your data by shifting it incorrectly.
• To insert a worksheet, click on the little tab with the starburst next to the last named worksheet at the bottom of the screen.
• There are all kinds of special insert stuff under the Insert ribbon.

Simple Sums

Formulas are why Excel was invented. Who wants to do math? There can be a multi-day class on formulas alone, so this covers just two things: AutoSum and Percentages.

AutoSum

• On the Home ribbon is AutoSum. If you put your cursor in a cell at the bottom of a row of number and press AutoSum, Excel will guess which cells you want to add. You’ll know which ones as Excel highlights them for you.
• If it doesn’t guess right, you can click first on the top cell, then the bottom cell, and it will adjust the highlight.
• You can always adjust the formula in the Formula Bar, but again, that’s another class.
• =SUM(J5:J8) will add all cells in between J5 and J8 together. =SUM(J5,J8) will add just the two cells together.

Percentage (typing a formula!)

• Put your cursor in the cell you want the percentage to be in. Begin typing =SUM and you’ll see that Excel wants to help you type it. Get to =SUM( and then click in the cell of the top number in your division, then type in the slash / and then click on the denominator, or what you are dividing by. Then close the parenthesis. So it should look something like this:

=SUM(C2/B2)

You’ll end up with a decimal number that you have to turn into a percentage. Right-click on the cell and you’ll get a pop-up menu. Choose Format Cell, then under the Number tab, choose Percentage and pick what decimal places you want to use.

• There are some tricks to clicking around the cells to add the right numbers for the formula. If it throws you for a loop, hit the Esc key to start over. Or, just type in the column and row by hand.

Making charts in Excel

Some day I may add more detail to this on making charts in Excel, but there are also plenty of references out on the web:

• Making charts in Excel

Using Google Spreadsheets

Everything worth doing in Excel can be done in Google Spreadsheets, which is free and supports collaborative editing. Only downside is the need for Internet access.

Categories: Handouts, Spring 2015

Tools of the trade: Spreadsheets and text editors

We are starting at the beginning, to the point of discussing copy and paste in class. But everyone confirmed they learned something they didn't previously know!

We start with Excel in the course because it is the tool you will start with for almost every project. If you don't master Excel, it is more difficult to progress.

This list includes what we covered in class, plus some other resources for you to save and use at your leisure. This lecture will continue on Monday.

• Handout: Excel Basics for Journalists (Links to an external site.)
• Data: In-class data (Links to an external site.) for download
• Data: Turnover data (Links to an external site.) for Google Spreadsheets
• Handout: Text editor recommendations (Links to an external site.)
• Handout: Common Excel formulas (Links to an external site.)
• Handout: Kinda crazy Excel formulas (Links to an external site.)
• Handout: Mary Jo Webster's Excel tricks (Links to an external site.)
• Data: Mary Jo's practice data (Links to an external site.)
• Book: Paul Bradshaw's Finding Stories in Spreadsheets (Links to an external site.)

Categories: Lecture notes, Spring 2015

What is data visualization? More than you think.

I'm gonna differ from others in the "industry" here, I think ...  considered simplistic, or too focused on entry-level. Well, good. Data visualization should be entry-level. Don't get me wrong ... I love what D3 can do and bow down to Mike Bostock, but what I want to help you do is find and tell stories.

I use data visualization first to FIND stories, then to TELL stories. There you go, in a nutshell.

• Lecture slides (Links to an external site.)
• If we don't cover Paul Bradshaw's Inverted Pyramid of Data Journalism (Links to an external site.) and my extension (Links to an external site.) of it, consider it homework

Categories: Lecture notes, Spring 2015

Spring 2015 Data Visualization course is on!

The 2015 Spring course is full, but we are in for a great semester. (If you think you will drop this class, let me know as there are folks wanting in.) Class days are Mon/Wed at 6 p.m. in CMA 4.146.

If you want to get a sneak peak at what is coming up, check out the tentative outline.

The required text will be Nathan Yau's Data Points. There will be other readings from other online sources.

You can join the UT Data Viz Class Facebook page now if you like. It's a closed group or current and past class members.

Categories: Spring 2015