This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!
Submit FeedbackAlternate Python based solution! I need several of these frosty beverages after that one... #SnakingMyWayThruChallenges
#################################
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
My solution for this challenge. It took me some time to solve this but I ended up using 9 tools 🙂
Cheers!