Preppin' Data

Challenge by: Jenny Martin Prep Air want to do some analysis of flight delays to and from its key destinations. After many discussions with the airport, they finally agreed to share this data. However, it's not in the best structure, so we'll definitely need to do some prep before our analysis can begin. (It's almost like they're afraid of what we'll find!) A special thank you to Michael  this week for sharing a similarly structured dataset with us that sparked the idea for this challenge! Inputs We have 2 inputs this week: Information on the delayed flights, separated across multiple lines Aggregated view of flights which were not delayed Requirements Input the data Aggregate the data so that you have 1 row per flight delay, instead of the current 3 rows Make sure all Airport codes are valid. Group those which are not. Calculate the total delay and number of delayed flights for each Airport, for each journey type Combine with information on flights which were not d...

Challenge by: Jenny Martin This week we're looking at a slightly odd way that Chin & Beard Suds Co have been structuring their store sales and target data:   As you can see, for each Store, there are 3 rows, the first being the Sales values, the second row containing the Target values for each month and the third row containing the difference between these values. It's your job to transform this monstrosity  unique table into a more conventional output that we could use in Tableau Desktop.  Inputs You have 2 options this week: Start the challenge with a Row ID already present (as pictured above) Use this as an opportunity to play with the Script step and create your own Row ID! Our solution will cover the RServe option, but you're welcome to use TabPy instead. For help getting set up with RServe, check out this blog For TabPy, check out this blog by our colleague Brian Scally   Requirements Input the data Make sure Store names have filled down correctly and remo...

Challenge by: Jenny Martin Since we had so many interesting approaches to Carl's fill down challenge last week, I thought we'd continue the theme of multi-row data prep problems!  This week we're looking at Chin & Beard Suds Co's slightly odd inventory ordering process. Every Wednesday, they restock each scent with 700 new bars. This is working on the assumption that the average sales for each scent are 100 bars a day. Any unsold bars on the Tuesday evening are deemed "not fresh enough" and thrown away.  We've been tasked with finding out how many bars are being wasted due to this process and how much that's costing the company! Inputs We have 3 inputs this week: Daily Sales Input Orders Input Scent Input Requirements Input the data * *Edit 20/08 - Final 3 days in July Removed from Daily Sales Calculate the Units Sold (=Daily Sales/Price) For each week (Wednesday-Tuesday), calculate the Weekly Units Sold and Weekly Sales.  Hint: It may be useful t...