Weekly Challenge

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

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

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

Challenge #198: Beverage Inventory

14 - Magnetar
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

# 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

 

Highlighted
Alteryx Partner

 

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

 

Highlighted
8 - Asteroid

Cheers!

Spoiler
tonyadam_0-1585437188870.png

 

Highlighted
Alteryx Partner

Done

Highlighted
Alteryx
Alteryx

My solution:

Highlighted
8 - Asteroid

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

Highlighted
12 - Quasar

On a spree to binge complete weekly challenges
Adrenaline rush

 

Spoiler
atcodedog05_0-1585991055057.png

 

Highlighted
5 - Atom

Here is my version of itchallenge198.PNG

Highlighted
8 - Asteroid

Here's my solution.

 

Spoiler
198_Solution.JPG
Highlighted
8 - Asteroid

Cheers!