Ask Computer Engineering Expert

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.

Computer Engineering, Engineering

  • Category:- Computer Engineering
  • Reference No.:- M92763484

Have any Question?


Related Questions in Computer Engineering

Does bmw have a guided missile corporate culture and

Does BMW have a guided missile corporate culture, and incubator corporate culture, a family corporate culture, or an Eiffel tower corporate culture?

Rebecca borrows 10000 at 18 compounded annually she pays

Rebecca borrows $10,000 at 18% compounded annually. She pays off the loan over a 5-year period with annual payments, starting at year 1. Each successive payment is $700 greater than the previous payment. (a) How much was ...

Jeff decides to start saving some money from this upcoming

Jeff decides to start saving some money from this upcoming month onwards. He decides to save only $500 at first, but each month he will increase the amount invested by $100. He will do it for 60 months (including the fir ...

Suppose you make 30 annual investments in a fund that pays

Suppose you make 30 annual investments in a fund that pays 6% compounded annually. If your first deposit is $7,500 and each successive deposit is 6% greater than the preceding deposit, how much will be in the fund immedi ...

Question -under what circumstances is it ethical if ever to

Question :- Under what circumstances is it ethical, if ever, to use consumer information in marketing research? Explain why you consider it ethical or unethical.

What are the differences between four types of economics

What are the differences between four types of economics evaluations and their differences with other two (budget impact analysis (BIA) and cost of illness (COI) studies)?

What type of economic system does norway have explain some

What type of economic system does Norway have? Explain some of the benefits of this system to the country and some of the drawbacks,

Among the who imf and wto which of these governmental

Among the WHO, IMF, and WTO, which of these governmental institutions do you feel has most profoundly shaped healthcare outcomes in low-income countries and why? Please support your reasons with examples and research/doc ...

A real estate developer will build two different types of

A real estate developer will build two different types of apartments in a residential area: one- bedroom apartments and two-bedroom apartments. In addition, the developer will build either a swimming pool or a tennis cou ...

Question what some of the reasons that evolutionary models

Question : What some of the reasons that evolutionary models are considered by many to be the best approach to software development. The response must be typed, single spaced, must be in times new roman font (size 12) an ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As