We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
alteryx Community

# Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
###### IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

## Challenge #198: Beverage Inventory

14 - Magnetar

Alternate Python based solution! I need several of these frosty beverages after that one... #SnakingMyWayThruChallenges

Spoiler
Approach:

I tried keeping everything in dataframes for the most part on this one (previously had been converting to lists, which I felt marginally more comfortable with). Found a few new "tools" that were handy for this one - melt (to pivot), merge (to join), group by (to aggregate), and fillna (to impute for null values).

Full Python Script:

#################################

from ayx import Package
import pandas as pd
import numpy as np
import datetime

#################################
from ayx import Alteryx

dfInventory = pd.melt(Alteryx.read("#1"),id_vars = ['Product ID','Product'],var_name = 'LocationID',value_name = 'Inventory').rename(columns={"Product ID": "ProductID"})

dfInventory.LocationID = dfInventory.LocationID.apply(lambda x:x.replace('Location ID: ',''))

#################################
# Read in Invoice data and remove trailing spaces

dfInvoice.Location = dfInvoice.Location.apply(lambda x:x.strip())

# Group by Location/product and aggregate total sales & avg sales

dfGroupedInv = dfInvoice.groupby(
['Location ID','Product ID'])['Units Sold'].agg(
[np.sum, np.mean]).rename(
columns={"sum": "Total Units Sold", "mean": "Avg Units Sold"})

# Create a lookup list for Location name

dfLocation = pd.DataFrame(dfInvoice.groupby(['Location ID','Location']))
dfLocation = pd.DataFrame(dfLocation[0].tolist())

#################################
# Read in Purchase Order data and convert year & day to integers

"Prod ID": "ProductID",
"ETA: Year": "Year",
"ETA: Month": "Month",
"ETA: Day": "Day"})

dfPurchaseOrders.Year = dfPurchaseOrders.Year.apply(lambda x:int(x))
dfPurchaseOrders.Day = dfPurchaseOrders.Day.apply(lambda x:int(x))

# Format date by converting dataframe to list and using for loop to generate desired datetime format

date_list = dfPurchaseOrders.values.tolist()
date_list_formatted = list()

for d in range(len(date_list)):
date_format = datetime.datetime.strptime('{}-{}-{}'.format(date_list[d][4],date_list[d][2],date_list[d][3]),'%Y-%B-%d').strftime('%B %d, %Y')
date_list_formatted.append(date_format)

# Append formatted date to dfPurchaseOrders dataframe

dfPurchaseOrders['ETA Date'] = date_list_formatted

dfGroupedPO = dfPurchaseOrders.groupby(['LocationID','ProductID']).agg({'On Order Qty': 'sum', 'ETA Date': 'min'})

#################################
# Merge datasets 1 & 2

df_merge1 = pd.merge(dfInventory, dfGroupedInv, left_on=["LocationID","ProductID"], right_on=["Location ID","Product ID"], how="left")
df_merge2 = pd.merge(df_merge1, dfGroupedPO, left_on=["LocationID","ProductID"], right_on=["LocationID","ProductID"], how="left")
df_merge3 = pd.merge(df_merge2, dfLocation, left_on=["LocationID"], right_on=[0], how="left")

# Rename columns

df_merge3 = df_merge3.drop([0],axis=1).rename(columns={"ProductID": "Product ID", "LocationID": "Location ID", "Inventory": "Inventory Qty", "Total Units Sold": "Total Sales", "Avg Units Sold": "Avg Sales", 1: "Location"})

# Imputation of null values

values = {'ETA Date':'No PO', 'Inventory Qty':0, 'Total Sales':0, 'Avg Sales':0, 'On Order Qty':0}
df_final = df_merge3[['Location ID','Location', 'Product ID','Product','Inventory Qty','Total Sales','Avg Sales','On Order Qty','ETA Date']].fillna(value = values)

#################################
# Output final data

Alteryx.write(df_final,1)

Cheers!

NJ

15 - Aurora

Spoiler

My solution for this challenge. It took me some time to solve this but I ended up using 9 tools 🙂

8 - Asteroid

Cheers!

Spoiler

8 - Asteroid

Done

Alteryx

My solution:

8 - Asteroid

Here is my solution but output comes out bit different between mine and expected result...

17 - Castor

On a spree to binge complete weekly challenges

Spoiler

5 - Atom

Here is my version of it

8 - Asteroid

Here's my solution.

Spoiler
8 - Asteroid

Cheers!