Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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.

SUBMIT YOUR IDEA

Challenge #198: Beverage Inventory

NicoleJohnson
ACE Emeritus
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

# Read in Inventory Data

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 = Alteryx.read("#2")

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

dfPurchaseOrders = Alteryx.read("#3").rename(columns={"Location ID": "LocationID",
"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

 

AngelosPachis
16 - Nebula

 

Spoiler
Annotation 2020-03-28 152856.png

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

 

TonyAdam
8 - Asteroid

Cheers!

Spoiler
tonyadam_0-1585437188870.png

 

KevinTang
8 - Asteroid

Done

DavidThorpe
Alteryx
Alteryx

My solution:

cc000072
8 - Asteroid

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

atcodedog05
22 - Nova
22 - Nova

On a spree to binge complete weekly challenges
Adrenaline rush

 

Spoiler
atcodedog05_0-1585991055057.png

 

manish_sumitra
5 - Atom

Here is my version of itchallenge198.PNG

patel_bm
8 - Asteroid

Here's my solution.

 

Spoiler
198_Solution.JPG
Niklas
8 - Asteroid

Cheers!