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!