Happy 8th birthday to the Maveryx Community! Take a walk down memory lane in our birthday blog, and don't miss out on the awesome birthday present that all Maveryx Community members get to take advantage of!
Bold Text Example

The localized versions of the Core Certification will be discontinued on September 22, 2023. To take the exam in your preferred language, please schedule it before this date. The Core exam will still be available in English at any time after September 22, 2023. If future versions of the Core Certification exam are localized, we will promptly announce their release dates.

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

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

## Challenge #198: Beverage Inventory

ACE Emeritus

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

16 - Nebula

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

22 - Nova

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!