2024: Week 2 - Average Price Analysis

Created by: Carl Allchin

It's the second week of our introductory challenges. This week the challenge will involve unions, aggregation and reshaping data.

Input

The input data set for this week is the output from week one. You can download them here.





Requirements

  • Input the two csv files
  • Union the files together
  • Convert the Date field to a Quarter Number instead
    • Name this field Quarter
  • Aggregate the data in the following ways:
    • Median price per Quarter, Flow Card? and Class
    • Minimum price per Quarter, Flow Card? and Class
    • Maximum price per Quarter, Flow Card? and Class
  • Create three separate flows where you have only one of the aggregated measures in each. 
    • One for the minimum price
    • One for the median price
    • One for the maximum price
  • Now pivot the data to have a column per class for each quarter and whether the passenger had a flow card or not
  • Union these flows back together
What's this you see??? Economy is the most expensive seats and first class is the cheapest? When you go and check with your manager you realise the original data has been incorrectly classified so you need to the names of these columns.
  • Change the name of the following columns:
    • Economy to First
    • First Class to Economy
    • Business Class to Premium
    • Premium Economy to Business
  • Output the data

Output



6 data fields:
  • Flow Card?
  • Quarter
  • Economy
  • Premium
  • Business
  • First
Optional = you might want to add a column to show which aggregation each value is minimum, medium or maximum.

24 rows (25 including headers)

You can download the outputs from here.

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja@JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help! 

Popular posts from this blog

2024: Week 1 - Prep Air's Flow Card

2023: Week 1 The Data Source Bank

How to...Handle Free Text