Reference no: EM133585714

Homework: Tableau Prep

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

Connect to "Jul2016 week1 flights.csv". Examine the file and add a CLEAN step

I. Split the "Airline Description column in two columns, "Airline" and "Airline Code".

II. Clean up "Airline" column so that same airline with different spellings are grouped together.

III. 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

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

V. Clean up the Tail Num column - replace "Null" by "Unknown"

VI. 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 table2 with "Airline" in table1., and merge "Unique Carrier" with "Airline Code".

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

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

IX. Merge other similar fields as necessary.

X. Rename the Union as "Combined All Weeks".

XI. Connect to file "Flight delaysaverage.csv" and create a JOINwith "Combined All Weeks", using INNER JOIN on fields "Airline Code" = "Unique Carrier".

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

XIII. 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).

XIV. Clean other columns if needed.

XV. Remove "Unique Carrier" column, leaving 11 fields.

XVI. Create an OUTPUT step. Don't run the flow

XVII. 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:

Task

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

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

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

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

Question E. 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?