HW

Unique Carrier	Avg. Minutes Delayed
WN	27.99366011
VX	18.99497487
UA	29.17756138
OO	18.85696776
NK	25.47542327
HA	6.838201086
F9	30.99517636
EV	31.97972651
DL	28.089229
B6	31.24788357
AS	4.085214447
AA	29.58003139

Tableau Prep assignment – 20 points

This is an assignment to clean data and create a workflow in Tableau Prep. Data files needed for this assignment are in same folder

Connect to “Jul 2016 week1 flights.csv”. Examine the file and add a CLEAN step

1. Split the “Airline Description column in two columns, “Airline” and “Airline Code”.

2. Clean up “Airline” column so that same airline with different spellings are grouped together.

3. Remove “Unknown” and “Unknown Commercial” from “Airline” column ( highlight and “exclude”) since we do not know these airlines and their codes are “NA”, i.e. unavailable

4. Clean up the Flight Number column by getting rid of negative values

5. Clean up the Tail Num column – replace “Null” by “Unknown”

6. Create a UNION of cleaned file with “Jul 2016 week 2 flights.csv”. Merge fields which have similar data but different column name, e.g. merge “Carrier” in table 2 with “Airline” in table 1., and merge “Unique Carrier” with “Airline Code”.

7. Remove extraneous (unmatched) fields, “Dest” and “origin” since they contain letter codes whereas we already have full names of destination and origin city.

8. ADD “Jul 2016 week 3 flights.csv” to this Union. Remove fields “Wheels on”, “Airline Description”, “Distance Flown” and “Table Names”.

9. Merge other similar fields as necessary.

10. Rename the Union as “Combined All Weeks”.

11. Connect to file “Flight delays average.csv” and create a JOIN with “Combined All Weeks”, using INNER JOIN on fields “Airline Code” = “Unique Carrier”.

12. Add a clean step after the join to examine the file.

13. You will see that “Airline Code” column has 12 unique entries but “Airlines” has more than 12. Clean the “Airline” column by grouping together same airlines with different spellings so that number of unique airlines (12) aligns with unique airline code (12).

( hint: NK should be clubbed with Spirit Airlines and Atlantic Southeast airlines should be clubbed with Express Jet. Other names to group are obvious).

14. Clean other columns if needed.

15. Remove “Unique Carrier” column, leaving 11 fields.

16. Create an OUTPUT step. Don’t run the flow

17. Save the file as Packaged Tableau Flow file as “.tflx” file with your lastname_Tprep

Based on the output of the flow, answer following questions:

Questions to be answered:

1. Of all the flights that landed in the state of Texas, how many (%) originated in Texas itself?

2. How many flights (number) landed in Chicago, which started from Denver?

3. Which Airlines flew the maximum number of flights that ranged from 1,000 to 2,000 miles?

4. Which are the Airlines that had the least average delay (less than 10 minutes)?

5. Of all the flights that originated in the city of New York, what % were United Airlines flights destined to a destination in the state of Illinois?

Grading: 15 points for data cleaning and correct workflow. 5 points for the questions.

SUBMIT the following on CANVAS:

1. Doc file containing answers to the questions

2. Packaged Flow file (in .tflx format)