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
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!