Preppin' Data

Challenge by Tom Prowse We are revisiting our Prep Air Airline this week by looking at some flight details and trying to provide some data driven answers. As part of running an airline we are always interested in how successful the in-flight service is and if we can make any improvements to try to boost sales.  For this week's challenge we have been provided with a selection of different data sources and we want to combine them to answer some questions that will help us to understand some purchasing patterns on the flights.  We have the following inputs :  Passenger List A list of all the passengers from a selection of flights. This includes their name, a passenger number, the flight number and the total they purchased whilst on the flight. Note, not all flights are full and can hold a maximum of 120 passengers.  Seat List A mapping of where each passenger sits within each flight. This is the same for all flights across our fleet and includes the row number and seat letter within e

Solution by Tom Prowse and you can download the full workflow here . This week's challenge was brought to us from Simon Evans. He used to work as an analyst at a professional sports team so we looked at a similar data set that a football analyst will have to make their way through. There were a lot of fields and lots of the information weren't important to the questions that we wanted to answer, therefore this week was good practice about how to deal with these larger data sets. Step 1 - Input All Files The first step is to input all of the files for the challenge. We are given data from 5 different seasons and all of these csv files have the same structure meaning that we can use the wildcard union input so that all of the tables are stacked on top of each other.  Notice how we have left the matching pattern section blank as we want to include all of the files. Step 2 - Filter & Calculate Open Play Goals Now we have all of the files, we can start to filter and clean our da

Challenge by: Simon Evans Before Simon joined The Data School in the UK, he was a professional sporting performance analyst. Simon has reached into his previous professional life to come up with a football (read soccer) based challenge for this week.  Simon is channelling his inner fanalyst to use data to understand more about the game that he enjoys.  This week we want to create a data set that allows us to analyse 'Open Play Goals' scored. We will rank the players overall and by their position.  Input 5 csv files , all with a similar structure. There are a lot of columns in these data sets. Small part of one of the five files Requirements Open play goal scoring prowess in the Premier League 2015-2020 Input all the files Remove all goalkeepers from the data set Remove all records where appearances = 0 In this challenge we are interested in the goals scored from open play Create a new “Open Play Goals” field (the goals scored from open play is the number of goals scored that

  Solution by Tom Prowse and you can download the full workflow here.  The challenge this week took a look at a data set showed to us by Lorna Brown , and was all about different economic indicators related to tourism in the Maldives. This had a bit of a quirky structure so the challenge was transform and clean this up so that it'll be more useful to create a visualisation. Step 1 - Pivot Months The first step is to bring all of our month fields into a single column using the pivot tool. Using the Columns to Rows pivot, we have dragged all of the month fields into the pivot section so we only have our non month fields left:  After the pivot we want to rename the fields, remove any nulls, and change the data types for the Number of Tourists and Month fields. We also only want to focus on the Tourists information from each country, therefore we can also Keep Only the 'Tourists' from Unit-Detail, and we can Exclude 'Total Tourist Arrivals' from the Series-Measure fie

Challenge By: Jenny Martin One of the best things about being a Dr Prepper is that people are always bringing interesting datasets to your attention. A little while ago, Tableau Zen Master Lorna Brown  showed me a dataset with all kinds of information on tourism in the Maldives . This database has a lot of data on different Key Economic Indicators, but as you can imagine, it also has a bit of a quirky structure! For inspiration as to why we might want to clean this data up, check out Lorna's viz below: Link to viz Input Our input is very wide this week, with 136 fields and only 28 rows. It covers tourism in the Maldives from 2010 to 2020. The source of this data is here but you can download it in the usual way from here . Requirements Input the data Pivot all of the month fields into a single column ( help ) Rename the fields and ensure that each field has the correct data type Filter out the nulls ( help ) Filter our dataset so our Values are referring to Number of Tourists Our g

Solution by Tom Prowse and you can download the full workflow here .  This week we were taking a look at cocktail recipes with a challenge put together by Viven Ho . This covered a lot of fundamental data prep skills so hopefully you found it useful to bring your Tableau Prep skills together.  Step 1 - Split Ingredients & Measurements The first step is to input the Cocktails table, and then we want to split apart the 'Recipe (ml)' field so that each of the different ingredients have their own column. We can split these using a custom split on a ';' separator:  As a result we have 4 columns that we can pivot into a single column using a Columns to Rows pivot. In the pivot setup we can use a Wildcard on 'Recipe' to bring through all of the fields containing our recipe ingredients:  After the pivot our recipe ingredients are all have their own row and the table looks like this:  Now the ingredients are in a single column, we can then extract the measurements fr

Challenge by Vivien Ho (@vivienho22)   This week's challenge has been put together by Vivien to challenge a lot of the fundamental prep skills you have built up so far this year. Vivien's challenge is looking at cocktail pricing (a common trend over the years at Preppin' HQ) and whether you can determine how much profit you can make from certain cocktails because who doesn't talk about data preparation when you are in a bar? Input Cocktails: names, prices and their recipe with measurements Sourcing: ingredient prices, quantity per bottle, currency of price  Conversion rates: currencies and their conversion rates (e.g. 1.14 euros = 1 pound)  Cocktails Sourcing Conversion rates Requirements:  Input the dataset Split out the recipes into the different ingredients and their measurements Calculate the price in pounds, for the required measurement of each ingredient Join the ingredient costs to their relative cocktails Find the total cost of each cocktail  Include a calcu