Weekly Challenge
Solve the challenge, share your solution and summit the ranks of our Community!IDEAS WANTED
We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!
Submit Feedback
Like others on this post, I feel like this challenge kicked my butt. I was able to get the final output to match, and I'm guessing it could have been done with fewer tools. Still, I'm happy I completed this challenge. It gave me exposure to some new data shaping techniques I hadn't encountered before and forced me to use some tools I don't use every day.
Good luck!
Spoiler
I broke this challenge into 3 parts:
1.) Reshape Draft Pick Values
2.) Clean up NFL Draft Picks
3.) Combine & Calculate
Part I: Reshape Draft Pick Values
This is the part that really got me. I tried thinking of ways to use dynamic tools to do all reshaping in one step; like most things, I ended up having to balance time with practicality, so I ended up using a few tools to accomplish this.
- Transpose - Get all of the data into 1 column
- Formula - Now that all 14 column names are in 1 column, use a formula tool to standardize them (i.e, "Pick #" and "Value")
- Multi-Row Formula - with the data being 100% vertical, use this tool to grab the pick number from the previous row
- Filter - drop the rows that are no longer needed
- Select - drop the unnecessary column
Part II: NFL Draft Picks
Union all 5 years of data, then change any Null grades to 0.
In the real world, I don't know that changing all Null grades to 0 makes sense. I noticed a few players with Null grades that were picked in one of the first few rounds of the draft, meaning they were probably highly-sought prospects, so imputing the grade would have made more sense. However, there wasn't a lot of information to impute from and the instructions weren't clear on how to approach this. Without more information to go off of, assume "0" which ultimately matches the solution.
Part III: Combine & Calculate
From there, the solution is pretty straightforward. Be sure to use a Union tool after the Join tool to keep all matches + any leftover data from Part II (i.e, drafts without a Draft Pick Value). Use a formula tool (or Data Cleansing tool) to change any Null Draft Pick Values to 2 per the instructions.
Sum up the grades and Draft Pick Values, divide, sort, sample, and browse!
1.) Reshape Draft Pick Values
2.) Clean up NFL Draft Picks
3.) Combine & Calculate
Part I: Reshape Draft Pick Values
This is the part that really got me. I tried thinking of ways to use dynamic tools to do all reshaping in one step; like most things, I ended up having to balance time with practicality, so I ended up using a few tools to accomplish this.
- Transpose - Get all of the data into 1 column
- Formula - Now that all 14 column names are in 1 column, use a formula tool to standardize them (i.e, "Pick #" and "Value")
- Multi-Row Formula - with the data being 100% vertical, use this tool to grab the pick number from the previous row
- Filter - drop the rows that are no longer needed
- Select - drop the unnecessary column
Part II: NFL Draft Picks
Union all 5 years of data, then change any Null grades to 0.
In the real world, I don't know that changing all Null grades to 0 makes sense. I noticed a few players with Null grades that were picked in one of the first few rounds of the draft, meaning they were probably highly-sought prospects, so imputing the grade would have made more sense. However, there wasn't a lot of information to impute from and the instructions weren't clear on how to approach this. Without more information to go off of, assume "0" which ultimately matches the solution.
Part III: Combine & Calculate
From there, the solution is pretty straightforward. Be sure to use a Union tool after the Join tool to keep all matches + any leftover data from Part II (i.e, drafts without a Draft Pick Value). Use a formula tool (or Data Cleansing tool) to change any Null Draft Pick Values to 2 per the instructions.
Sum up the grades and Draft Pick Values, divide, sort, sample, and browse!
