def load_recipes(strtWeek, endWeek, productFamily):
df = pd.DataFrame(index = [], columns = ['price', 'product', 'skucode', 'skuname', 'region', 'quantitytoorder1p',
'quantitytoorder2p', 'quantitytoorder3p', 'quantitytoorder4p','recipecode', 'slot'])
for x in range(strtWeek, endWeek):
df2 = fc.load_odata_table('recipesku', 'COGSDetailSKU_Week', 'eq', \
fc.get_boxweek_id(x), 'Price,Product,SKUCode,SKUName,Region,QuantityToOrder1P,\
QuantityToOrder2P,QuantityToOrder3P,QuantityToOrder4P,RecipeCode,Slot')
df = df.append(df2)
df = df[df['product'] == productFamily]
df = df[df['region'] == 'NL']
df['hf_week'] = '2019-W' + str(strtWeek)
df['cost1p'] = df['price'].map(float) * df['quantitytoorder1p'].map(float)
df['cost2p'] = df['price'].map(float) * df['quantitytoorder2p'].map(float)
df['cost3p'] = df['price'].map(float) * df['quantitytoorder3p'].map(float)
df['cost4p'] = df['price'].map(float) * df['quantitytoorder4p'].map(float)
df3 = df.copy()
sku_filter = '%27' + load_odata_filter(df3.iloc[0:100,], 'Code', 'skucode') + '%27'
skus = fc.load_odata_table('sku', 'Code', 'eq', sku_filter, 'Code,PackingSize')
for x in range(1,math.ceil(len(df)/100)+1):
sku_filter = '%27' + load_odata_filter(df3.iloc[100*x:100*x+100,], 'Code', 'skucode') + '%27'
df4 = fc.load_odata_table('sku', 'Code', 'eq', sku_filter, 'Code,PackingSize')
skus = skus.append(df4)
skus.drop_duplicates(subset = 'code', inplace = True)
df5 = df.merge(skus, left_on = 'skucode', right_on = 'code')
return df5
def load_sku_info(table):
sku_filter = '%27' + load_odata_filter(table.iloc[0:100, ], 'Code',
'skucode') + '%27'
skus = fc.load_odata_table('sku', 'Code', 'eq', sku_filter,
'Code,PackingSize')
for x in range(1, math.ceil(len(table) / 100) + 1):
sku_filter = '%27' + load_odata_filter(
table.iloc[100 * x:100 * x + 100, ], 'Code', 'skucode') + '%27'
df = fc.load_odata_table('sku', 'Code', 'eq', sku_filter,
'Code,PackingSize')
skus = skus.append(df)
skus.drop_duplicates(subset='code', inplace=True)
return skus
def get_sku_for_weeks(strtWeek, endWeek, productFamily):
df = pd.DataFrame(index=[],
columns=['price', 'product', 'skucode', 'skuname'])
for x in range(strtWeek, endWeek):
df2 = fc.load_odata_table('recipesku', 'COGSDetailSKU_Week', 'eq', \
fc.get_boxweek_id(x), 'ID,Price,Product,SKUCode,SKUName,Region,QuantityToOrder2P') #,RecipeCode,Slot
df2 = df2[df2['product'] == productFamily]
df2 = df2[df2['quantitytoorder2p'] != 0]
df2 = df2[df2['region'] == 'NL']
df = df.append(df2)
df.drop_duplicates(subset='skucode', inplace=True)
df['ahsearch'] = df['skuname'].str.lower()
df['ahsearch'] = df['ahsearch'].str.replace('\(.*', '')
df['ahsearch'] = df['ahsearch'].str.strip()
df['ahsearch'] = df['ahsearch'].str.replace(' ', '%20')
df.reset_index(inplace=True)
# Drop duplicate urls
df2 = df[['region', 'ahsearch']]
df2 = df[['region', 'ahsearch']].drop_duplicates()
df2.reset_index(inplace=True)
df2.drop('index', axis=1, inplace=True)
return df, df2
def load_recipes(strtWeek, endWeek, productFamily):
df = pd.DataFrame(index=[],
columns=[
'price', 'product', 'skucode', 'skuname', 'region',
'quantitytoorder1p', 'quantitytoorder2p',
'quantitytoorder3p', 'quantitytoorder4p',
'recipecode', 'slot'
])
for x in range(strtWeek, endWeek):
df2 = fc.load_odata_table('recipesku', 'COGSDetailSKU_Week', 'eq', \
fc.get_boxweek_id(x), 'Price,Product,SKUCode,SKUName,Region,QuantityToOrder1P,\
QuantityToOrder2P,QuantityToOrder3P,QuantityToOrder4P,RecipeCode,Slot' )
df = df.append(df2)
df = df[df['product'] == productFamily]
df = df[df['region'] == 'NL']
df['hf_week'] = '2019-W' + str(strtWeek)
df['cost1p'] = df['price'].map(float) * df['quantitytoorder1p'].map(float)
df['cost2p'] = df['price'].map(float) * df['quantitytoorder2p'].map(float)
df['cost3p'] = df['price'].map(float) * df['quantitytoorder3p'].map(float)
df['cost4p'] = df['price'].map(float) * df['quantitytoorder4p'].map(float)
return df