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)
