HW

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?

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?