Challenge #378: Inspire 2023 Grand Prix (Round 1)

[AYXAcademy]

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).

1. 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.
2. What is that customer's ID and the collection date?

Next, calculate the total successfully collected weight for all customers on that date.

3. What is the total weight of products collected from all customers that day? 
4. 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.

[Kenda]

Spoiler

Hmm not getting the same answer as the given output... Maybe the pressure of the Grand Prix is getting to me and I'm missing something 

[patrick_digan]

Spoiler

Same answers as @Kenda 

[mmontgomery]

Always easier when not on stage :)

Spoiler

[LynnPätzold]

It took me roughtly 15min. Afterwards I tried to find desperatly my "mistake", but I got the same answer as @Kenda :)

Spoiler

 

[ahsanaali]

See solution attached.

Spoiler

 Same answer as others. 

[PhilipMannering]

Unmatched solution attached.

Spoiler

Same as @Kenda 

And in the Python Tool,

Spoiler

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

[MatteoReddavide]

Not the same results of the soloution, but I see I'm not the only one 🤗

Spoiler

[alexnajm]

Good challenge!

Spoiler

Same answer as others...

[nSpire]

Same results as already posted

Spoiler