Assignment: Tableau Basic Module
Data Prep with Text and Excel Files
Poorly Formatted Data
Data files are not always well-formatted. Now we will practice using a file that needs some work before it is ready for analysis.
For this assignment we'll be using the "Data Prep - Flights" Excel file, which you can download from the course website. (Global Superstore, our main dataset, is too well-structured! This gives us a messier example to work with.)
Open the file in Excel. Here we have a report in Excel, showing the number of resolved incidents per Employee per month. The "Ideal" tab shows how we wish the data would be formatted - like a database table.
However, sometimes we receive data that looks more like what we see in the "Resolved Incidents" tab. Luckily, there are several features in Tableau Desktop to help automatically reshape Text and Excel files to get them ready for analysis in Tableau.
Let's connect to this Excel file and see if we can work with that poorly formatted sheet.
• In Tableau Desktop,click on Excel, navigate to where you saved the file and click open.
• Drag out the "Resolved Incidents" sheet.
Data Interpreter
Although Tableau can connect to this sheet, we can see here in the preview that there are some issues. There are no column names, the headers from Excel have a lot of nulls, and so on. Tableau has also recognized this and suggests the Data Interpreter (Tableau's built-in tool for preparing your data for analysis).
• Click turn on
• Now we see that those headers and nulls have been stripped out, and our columns are properly identified!
• If we want more specifics on what the Data Interpreter did, we can click "Review Results" on the right. This will open an Excel file describing the changes.
• If we click to the tab we used, Resolved Incidents, we see which fields are being used as headers, in red, and which are considered data, in green
Before we go back to Tableau and our data connection, let's take one more look at that "Ideal" tab. Note that instead of having a column for each month with data underneath, in this format, there is a "Date" column and each row contains the number of resolved incidents for each unique combination of date and employee. This data is in the preferred format for analysis: taller, with more rows, rather than wider, with more columns. Let's see if we can do that in Tableau.
Pivot
Back in Tableau, we want to change the format from that column-per-month layout into a single date column and a single column for Resolved Incidents.
• To do this easily, we'll simply select all the date columns. Click on the first, scroll if necessary, then shift click on the last. We'll open the menu and select "Pivot"
• This pivot feature essentially merges the information from the original columns and rows into two new columns - Pivot field names, and Pivot field values.
• We can see that "Pivot field names" is actually our Date, so we can click to open the menu and select rename.
• Similarly, "Pivot field values" can be renamed "Resolved Incidents"
Split
There's one more thing we can do to prepare this data. Note that the "Employee" field is actually two pieces of information - a location code, A, B, C, D, or E, followed by an Employee ID number. We can split the column based on the hyphen delimiter:
• Click to open the menu and select Split
• There are now two new fields - Employee - Split 1 and Split 2
• We'll use the Metadata Grid view (click the icon to the left of Sort Fields) to rename our split fields
• Click on the name to edit in-line, Split 1 should be Location, and we'll hit tab, Split 2 should be Employee ID
• There's an Abc next to the Date field indicating this column is considered a String. We know it's actually a Date, though, so we can click on the Abc and select Date to update the data type.
Now if we click on Sheet 1, we'll see nice tidy data ready for analysis!
Custom Split
Let's create a viz now: bring Employee ID to the view, Resolved Incidents to Columns, and sort it.
It's clear from this view that there are really two groups of employees - some who resolve a much higher number of incidents than others. Looks like some employees are often able to get through more cases, and they have a Tier II designation.
If we look at our original data set in Excel, we see there's a tab called Tiers. This report adds a -II to the end of an employee ID if they're tier II. Because not all rows have this -II, a standard split won't work. Let's see if we can create a viz that incorporates this Tier designation.
[[side note:both Split and Custom Split require consistent delimiters. If our data has irregular delimiters, Tableau won't be able to split out the data using these options.]]
Open a new Tableaufileand recreate the viz, this time using the Tiers sheet from Excel.
• Remember to usethe Data Interpreter, and Pivot the dates again
• Click on the Employee column to open the menu and select Custom Split
o We can choose our delimiter, we'll use a hyphen
o And now we can say we want to have 3 columns
o This forces Tableau to break off that 3rd column with the tier II indicator
Now finish the viz by yourself without detailed instructions: rename all columns, and do your bar chart as above, and this time color the bars by Tier.