Challenge #378: Inspire 2023 Grand Prix (Round 1)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
A solution to last week's challenge can be found here.
This week's challenge marks the beginning of a trilogy of challenges inspired by the 2023 Inspire Grand Prix. These challenges delve into real-life scenarios that numerous companies encounter on a regular basis. The initial challenge focuses on the preparation and integration of data, and the second challenge revolves around spatial problem-solving. The third and final challenge entails tackling a predictive case.
If you are eager to experience the same exhilaration our racers feel in Las Vegas, take a quick, 2-minute glance at the instructions, start your timer, and record how long it takes you to determine the correct answers! Remember to share your time when you submit your workflow.
Let’s start now: 3, 2, 1, Go!
A company called ACE collects donated food products and delivers them to customers in different locations. They calculate the weight of each product by product type. Using the provided datasets:
Considering only trips where products were collected with a successful Closed Reason, determine the highest total weight collected by a customer on a single day (all product types combined).
- What is the highest weight of product collected from a single customer on a single day? Note that some customers have multiple trips in a day.
- What is that customer's ID and the collection date?
Next, calculate the total successfully collected weight for all customers on that date.
- What is the total weight of products collected from all customers that day?
- What percentage (for example 23%, not 0.23) of the products collected that day did the customer from Question 1 contribute? Round your answer to the nearest integer.
- Labels:
- Basic
- Core
- Data Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Always easier when not on stage :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Unmatched solution attached.
And in the Python Tool,
#################################
from ayx import Alteryx
import pandas as pd
df = Alteryx.read('#1')
#################################
df = df[(df.ClosedReason.str.contains('01. Successful')) \
& (df['JobTypeName - simple']=='Collected')]
df['Weight'] = df.sum(axis=1)
df['Date'] = df['Date.Time.Customer'].str.extract('([\d-]{10})')
df['Customer'] = df['Date.Time.Customer'].str.extract('Customer:(\d+)')
df = df[['Customer', 'Weight', 'Date']]
# Get Date and Customer of highest weight collected
df2 = df.groupby(['Date', 'Customer'])['Weight'].sum().nlargest(1).reset_index()
# Combine with other data on same Date
df3 = pd.merge(df, df2, on='Date', suffixes=('Total', ''))
# Get Total Weight for that date
df4 = df3.groupby(['Date', 'Weight','Customer'])['WeightTotal'].sum().reset_index()
# Calculate Percentage
df4['Percent'] = round(100 * df4['Weight'] / df4['WeightTotal'], 0).astype(int).astype(str)+'%'
df4
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Good challenge!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Same results as already posted