class JNJToolBox:
NUMERATOR = 'numerator'
DENOMINATOR = 'denominator'
SP_LOCATION_KPI = 'secondary placement location quality'
SP_LOCATION_QUALITY_KPI = 'secondary placement location visibility quality'
LVL3_HEADERS = [
'assortment_group_fk', 'assortment_fk', 'target', 'product_fk',
'in_store', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'kpi_fk_lvl3',
'group_target_date', 'assortment_super_group_fk', 'super_group_target'
]
LVL2_HEADERS = [
'assortment_super_group_fk', 'assortment_group_fk', 'assortment_fk',
'target', 'passes', 'total', 'kpi_fk_lvl1', 'kpi_fk_lvl2',
'group_target_date', 'super_group_target'
]
LVL1_HEADERS = [
'assortment_super_group_fk', 'assortment_group_fk',
'super_group_target', 'passes', 'total', 'kpi_fk_lvl1'
]
ASSORTMENT_FK = 'assortment_fk'
ASSORTMENT_GROUP_FK = 'assortment_group_fk'
ASSORTMENT_SUPER_GROUP_FK = 'assortment_super_group_fk'
# local_msl availability
# LOCAL_MSL_AVAILABILITY = 'local_msl'
# LOCAL_MSL_AVAILABILITY_SKU = 'local_msl - SKU'
# TODO: change this
# local_msl availability
LOCAL_MSL_AVAILABILITY = 'Distribution'
LOCAL_MSL_AVAILABILITY_SKU = 'Distribution - SKU'
# jnjanz local msl/oos KPIs
OOS_BY_LOCAL_ASSORT_STORE_KPI = 'OOS_BY_LOCAL_ASSORT_STORE'
OOS_BY_LOCAL_ASSORT_PRODUCT = 'OOS_BY_LOCAL_ASSORT_PRODUCT'
OOS_BY_LOCAL_ASSORT_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY'
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY'
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT'
MSL_BY_LOCAL_ASSORT = 'MSL_BY_LOCAL_ASSORT'
MSL_BY_LOCAL_ASSORT_PRODUCT = 'MSL_BY_LOCAL_ASSORT_PRODUCT'
MSL_BY_LOCAL_ASSORT_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY'
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY'
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT'
# msl availability
MSL_AVAILABILITY = 'MSL'
MSL_AVAILABILITY_SKU = 'MSL - SKU'
JNJ = 'JOHNSON & JOHNSON'
TYPE_SKU = 'SKU'
TYPE_OTHER = 'Other'
SUCCESSFUL = [1, 4]
OTHER = 'Other'
YES = 'Yes'
NO = 'No'
OOS = 'OOS'
DISTRIBUTED = 'DISTRIBUTED'
EXTRA = 'EXTRA'
def __init__(self, data_provider, output, common, exclusive_template):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.session_id = self.data_provider.session_id
self.products = self.data_provider[Data.PRODUCTS]
self.all_products_i_d = self.data_provider[
Data.ALL_PRODUCTS_INCLUDING_DELETED]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.templates = self.data_provider[Data.ALL_TEMPLATES]
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.tools = JNJGENERALToolBox(self.data_provider,
self.output,
rds_conn=self.rds_conn)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.common = common
self.New_kpi_static_data = common.get_new_kpi_static_data()
self.kpi_results_new_tables_queries = []
self.all_products = self.ps_data_provider.get_sub_category(
self.all_products, 'sub_category_local_name')
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_info = self.ps_data_provider.get_ps_store_info(
self.store_info)
self.current_date = datetime.now()
self.labels = self.ps_data_provider.get_labels()
self.products_in_ass = []
self.products_to_ass = pd.DataFrame(
columns=assTemplate.COLUMNS_ASSORTMENT_DEFINITION_SHEET)
self.assortment_policy = pd.DataFrame(
columns=assTemplate.COLUMNS_STORE_ATTRIBUTES_TO_ASSORT)
self.ass_deleted_prod = pd.DataFrame(columns=[
assTemplate.COLUMN_GRANULAR_GROUP, assTemplate.COLUMN_EAN_CODE
])
self.session_category_info = pd.DataFrame()
self.session_products = pd.DataFrame()
self.assortment = Assortment(self.data_provider, self.output,
self.ps_data_provider)
self.products_to_remove = []
self.ignore_from_top = 1
self.start_shelf = 3
self.products_for_ass_new = pd.DataFrame(
columns=['session_id', 'product_fk'])
self.prev_session_products_new_ass = pd.DataFrame()
self.session_category_new_ass = pd.DataFrame()
self.own_manuf_fk = int(
self.data_provider.own_manufacturer.param_value.values[0])
self.kpi_result_values = self.get_kpi_result_values_df()
self.parser = Parser
self.exclusive_template = exclusive_template
self.result_values = self.ps_data_provider.get_result_values()
def get_kpi_result_values_df(self):
query = JNJQueries.get_kpi_result_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_session_products(self, session):
return self.session_products[self.session_products['session_id'] ==
session]
def result_value_pk(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] ==
result]["pk"].iloc[0]
return pk
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), value.split(';'))
def reset_scif_and_matches(self):
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS].copy()
self.match_product_in_scene = self.data_provider[Data.MATCHES].copy()
def filter_scif_matches_for_kpi(self, kpi_name):
if not self.exclusive_template.empty:
template_filters = {}
kpi_filters_df = self.exclusive_template[
self.exclusive_template['KPI'] == kpi_name]
if kpi_filters_df.empty:
return
if not kpi_filters_df.empty:
if kpi_filters_df['Exclude1'].values[0]:
template_filters.update({
kpi_filters_df['Exclude1'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value1'].values[0]), 0)
})
if kpi_filters_df['Exclude2'].values[0]:
template_filters.update({
kpi_filters_df['Exclude2'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value2'].values[0]), 0)
})
if 'Exclude3' in kpi_filters_df.columns.values:
if kpi_filters_df['Exclude3'].values[0]:
template_filters.update({
kpi_filters_df['Exclude3'].values[0]:
(self.split_and_strip(
kpi_filters_df['Value3'].values[0]), 0)
})
if 'Exclude4' in kpi_filters_df.columns.values:
if kpi_filters_df['Exclude4'].values[0]:
template_filters.update({
template_filters['Exclude4'].values[0]:
(self.split_and_strip(
template_filters['Value4'].values[0]), 0)
})
filters = self.get_filters_for_scif_and_matches(
template_filters)
self.scif = self.scif[self.tools.get_filter_condition(
self.scif, **filters)]
self.match_product_in_scene = self.match_product_in_scene[
self.tools.get_filter_condition(
self.match_product_in_scene, **filters)]
def get_filters_for_scif_and_matches(self, template_filters):
product_keys = filter(
lambda x: x in self.data_provider[Data.ALL_PRODUCTS].columns.values
.tolist(), template_filters.keys())
scene_keys = filter(
lambda x: x in self.data_provider[Data.ALL_TEMPLATES].columns.
values.tolist(), template_filters.keys())
product_filters = {}
scene_filters = {}
filters_all = {}
for key in product_keys:
product_filters.update({key: template_filters[key]})
for key in scene_keys:
scene_filters.update({key: template_filters[key]})
if product_filters:
product_fks = self.get_fk_from_filters(product_filters)
filters_all.update({'product_fk': product_fks})
if scene_filters:
scene_fks = self.get_scene_fk_from_filters(scene_filters)
filters_all.update({'scene_fk': scene_fks})
return filters_all
def get_fk_from_filters(self, filters):
all_products = self.data_provider.all_products
product_fk_list = all_products[self.tools.get_filter_condition(
all_products, **filters)]
product_fk_list = product_fk_list['product_fk'].unique().tolist()
return product_fk_list
def get_scene_fk_from_filters(self, filters):
scif_data = self.data_provider[Data.SCENE_ITEM_FACTS]
scene_fk_list = scif_data[self.tools.get_filter_condition(
scif_data, **filters)]
scene_fk_list = scene_fk_list['scene_fk'].unique().tolist()
return scene_fk_list
def get_own_manufacturer_skus_in_scif(self):
# Filter scif by own_manufacturer & product_type = 'SKU'
return self.scif[(self.scif.manufacturer_fk == self.own_manuf_fk)
& (self.scif.product_type == "SKU")
& (self.scif["facings"] >
0)]['item_id'].unique().tolist()
def fetch_local_assortment_products(self):
# TODO Fix with real assortment
lvl3_assortment = self.assortment.get_lvl3_relevant_ass()
local_msl_ass_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.LOCAL_MSL_AVAILABILITY]['pk'].drop_duplicates().values[0]
local_msl_ass_sku_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.LOCAL_MSL_AVAILABILITY_SKU]['pk'].drop_duplicates().values[0]
if lvl3_assortment.empty:
return [], pd.DataFrame()
lvl3_assortment = lvl3_assortment[lvl3_assortment['kpi_fk_lvl3'] ==
local_msl_ass_sku_fk]
if lvl3_assortment.empty:
return [], pd.DataFrame()
assortments = lvl3_assortment['assortment_group_fk'].unique()
products_in_ass = []
for assortment in assortments:
current_assortment = lvl3_assortment[
lvl3_assortment['assortment_group_fk'] == assortment]
current_assortment_product_fks = list(
current_assortment[~current_assortment['product_fk'].isna()]
['product_fk'].unique())
products_in_ass.extend(current_assortment_product_fks)
#ignore None if anty
products_in_ass = [
p for p in products_in_ass if not ((p == None) or p == 'None')
]
return products_in_ass, lvl3_assortment
@kpi_runtime()
def local_assortment_hierarchy_per_store_calc(self):
Log.debug("starting local_assortment calc")
self.products_in_ass, lvl3_assortment = self.fetch_local_assortment_products(
)
self.products_in_ass = np.unique(self.products_in_ass)
if lvl3_assortment.empty or len(self.products_in_ass) == 0:
Log.warning(
"Assortment list is empty for store_fk {} in the requested session : {} - visit_date: {}"
.format(self.store_id, self.session_id,
self.session_info.get('visit_date').iloc[0]))
return
self.local_assortment_hierarchy_per_category_and_subcategory()
oos_per_product_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0]
msl_per_product_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0]
products_in_session = self.scif['item_id'].drop_duplicates().values
for sku in self.products_in_ass:
if sku in products_in_session:
result = self.result_value_pk(self.DISTRIBUTED)
result_num = 1
else:
result = self.result_value_pk(self.OOS)
result_num = 0
# Saving OOS
self.common.write_to_db_result(
fk=oos_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="OOS_Local_store",
should_enter=True)
# Saving MSL
self.common.write_to_db_result(fk=msl_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result_num,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="MSL_Local_store",
should_enter=True)
# Saving MSL - Extra
# Add the Extra Products found in Session from same manufacturer into MSL
own_manufacturer_skus = self.get_own_manufacturer_skus_in_scif()
extra_products_in_scene = set(products_in_session) - set(
self.products_in_ass)
for sku in extra_products_in_scene:
if sku in own_manufacturer_skus:
result = self.result_value_pk(self.EXTRA) # Extra
result_num = 1
self.common.write_to_db_result(
fk=msl_per_product_kpi_fk,
numerator_id=sku,
numerator_result=result_num,
result=result,
denominator_id=self.own_manuf_fk,
denominator_result=1,
score=result,
identifier_parent="MSL_Local_store",
should_enter=True)
oos_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_STORE_KPI]['pk'].values[0]
msl_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT]['pk'].values[0]
denominator = len(self.products_in_ass)
# Saving OOS
oos_numerator = len(
list(set(self.products_in_ass) - set(products_in_session)))
oos_res = round(
(oos_numerator / float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=oos_kpi_fk,
numerator_id=self.own_manuf_fk,
denominator_id=self.store_id,
numerator_result=oos_numerator,
result=oos_res,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_store")
# Saving MSL
msl_numerator = len(
list(set(self.products_in_ass) & set(products_in_session)))
msl_res = round(
(msl_numerator / float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=msl_kpi_fk,
numerator_id=self.own_manuf_fk,
denominator_id=self.store_id,
numerator_result=msl_numerator,
result=msl_res,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_store")
Log.debug("finishing oos_per_store_calc")
return
def local_assortment_hierarchy_per_category_and_subcategory(self):
Log.debug("starting oos_per_category_per_sub_category_per_product")
products_in_session = self.scif['product_fk'].drop_duplicates().values
# OOS KPIs
oos_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] == self.
OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0]
oos_cat_subcat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0]
oos_cat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.OOS_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0]
# MSL KPIs
msl_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] == self.
MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0]
msl_cat_subcat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0]
msl_cat_kpi_fk = self.New_kpi_static_data[
self.New_kpi_static_data['client_name'] ==
self.MSL_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0]
categories = self.all_products[self.all_products['product_fk'].isin(self.products_in_ass)] \
['category_fk'].drop_duplicates().values
for category in categories:
products_in_cat = self.all_products[
self.all_products['category_fk'] ==
category]['product_fk'].drop_duplicates().values
relevant_for_ass = list(
set(self.products_in_ass) & set(products_in_cat))
denominator = len(relevant_for_ass)
# Saving OOS
oos_numerator = len(
list(set(relevant_for_ass) - set(products_in_session)))
oos_res = round((oos_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=oos_cat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=oos_numerator,
result=oos_res,
denominator_id=category,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_cat_" +
str(int(category)))
# Saving MSL
msl_numerator = len(
list(set(relevant_for_ass) & set(products_in_session)))
msl_res = round((msl_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(fk=msl_cat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=msl_numerator,
result=msl_res,
denominator_id=category,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_cat_" +
str(int(category)))
sub_categories = self.all_products[(
self.all_products['product_fk'].isin(self.products_in_ass) &
(self.all_products['category_fk']
== category))]['sub_category_fk'].drop_duplicates().values
for sub_category in sub_categories:
products_in_sub_cat = self.all_products[
(self.all_products['sub_category_fk'] == sub_category)
& (self.all_products['category_fk'] == category
)]['product_fk'].drop_duplicates().values
relevant_for_ass = list(
set(self.products_in_ass) & set(products_in_sub_cat))
denominator = len(relevant_for_ass)
# Saving OOS
oos_numerator = len(
list(set(relevant_for_ass) - set(products_in_session)))
oos_res = round(
(oos_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(
fk=oos_cat_subcat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=oos_numerator,
result=oos_res,
denominator_id=sub_category,
denominator_result=denominator,
score=oos_res,
identifier_result="OOS_Local_subcat_" +
str(int(sub_category)),
identifier_parent="OOS_Local_cat_" + str(int(category)),
should_enter=True)
# Saving MSL
msl_numerator = len(
list(set(relevant_for_ass) & set(products_in_session)))
msl_res = round(
(msl_numerator /
float(denominator)), 4) if denominator != 0 else 0
self.common.write_to_db_result(
fk=msl_cat_subcat_kpi_fk,
numerator_id=self.own_manuf_fk,
numerator_result=msl_numerator,
result=msl_res,
denominator_id=sub_category,
denominator_result=denominator,
score=msl_res,
identifier_result="MSL_Local_subcat_" +
str(int(sub_category)),
identifier_parent="MSL_Local_cat_" + str(int(category)),
should_enter=True)
for sku in relevant_for_ass:
if sku in products_in_session:
result = self.result_value_pk(self.DISTRIBUTED)
result_num = 1
else:
result = self.result_value_pk(self.OOS)
result_num = 0
# Saving OOS
self.common.write_to_db_result(
fk=oos_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="OOS_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
# Saving MSL
self.common.write_to_db_result(
fk=msl_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result_num,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="MSL_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
# Saving MSL
# Add the New Products found in Session for the subcat,cat from same manufacturer into MSL
# Filter products in session based on sub_cat and category
# extra_products_in_scene = set(products_in_session) - set(self.products_in_ass)
# for sku in extra_products_in_scene:
relevant_products_in_session = list(
set(products_in_session) & set(products_in_sub_cat))
extra_products_in_scene = set(
relevant_products_in_session) - set(relevant_for_ass)
for sku in extra_products_in_scene:
# Filter scif by own_manufacturer & product_type = 'SKU'
own_manufacturer_skus = self.scif[
(self.scif.manufacturer_fk == self.own_manuf_fk)
& (self.scif.product_type == "SKU")
& (self.scif["facings"] > 0)]['item_id'].tolist()
if sku in own_manufacturer_skus:
result = self.result_value_pk(self.EXTRA) # Extra
result_num = 1
self.common.write_to_db_result(
fk=msl_cat_subcat_sku_kpi_fk,
result=result,
score=result,
numerator_id=sku,
numerator_result=result_num,
denominator_id=sub_category,
denominator_result=1,
identifier_parent="MSL_Local_subcat_" +
str(int(sub_category)),
should_enter=True)
Log.debug("finishing assortment_per_category")
return
def main_calculation(self):
try:
if self.scif.empty:
Log.warning('Scene item facts is empty for this session')
Log.warning(
'Unable to calculate local_msl assortment KPIs: SCIF is empty'
)
return 0
self.reset_scif_and_matches()
self.filter_scif_matches_for_kpi(
"Distribution") #changed from local_msl to Distribution
self.local_assortment_hierarchy_per_store_calc()
except Exception as e:
Log.error("Error: {}".format(e))
return 0
class HEINZCRToolBox:
LVL3_HEADERS = ['assortment_group_fk', 'assortment_fk', 'target', 'product_fk',
'in_store', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'kpi_fk_lvl3', 'group_target_date',
'assortment_super_group_fk']
LVL2_HEADERS = ['assortment_group_fk', 'assortment_fk', 'target', 'passes', 'total',
'kpi_fk_lvl1', 'kpi_fk_lvl2', 'group_target_date']
LVL1_HEADERS = ['assortment_group_fk', 'target', 'passes', 'total', 'kpi_fk_lvl1']
ASSORTMENT_FK = 'assortment_fk'
ASSORTMENT_GROUP_FK = 'assortment_group_fk'
ASSORTMENT_SUPER_GROUP_FK = 'assortment_super_group_fk'
BRAND_VARIENT = 'brand_varient'
NUMERATOR = 'numerator'
DENOMINATOR = 'denominator'
DISTRIBUTION_KPI = 'Distribution - SKU'
OOS_SKU_KPI = 'OOS - SKU'
OOS_KPI = 'OOS'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = CommonV2 # remove later
self.common_v2 = CommonV2(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_results_queries = []
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider,
common=self.common_v2)
self.store_sos_policies = self.ps_data_provider.get_store_policies()
self.labels = self.ps_data_provider.get_labels()
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_info = self.ps_data_provider.get_ps_store_info(self.store_info)
self.country = self.store_info['country'].iloc[0]
self.current_date = datetime.now()
self.extra_spaces_template = pd.read_excel(Const.EXTRA_SPACES_RELEVANT_SUB_CATEGORIES_PATH)
self.store_targets = pd.read_excel(Const.STORE_TARGETS_PATH)
self.sub_category_weight = pd.read_excel(Const.SUB_CATEGORY_TARGET_PATH, sheetname='category_score')
self.kpi_weights = pd.read_excel(Const.SUB_CATEGORY_TARGET_PATH, sheetname='max_weight')
self.targets = self.ps_data_provider.get_kpi_external_targets()
self.store_assortment = PSAssortmentDataProvider(
self.data_provider).execute(policy_name=None)
self.supervisor_target = self.get_supervisor_target()
try:
self.sub_category_assortment = pd.merge(self.store_assortment,
self.all_products.loc[:, ['product_fk', 'sub_category',
'sub_category_fk']],
how='left', on='product_fk')
self.sub_category_assortment = \
self.sub_category_assortment[~self.sub_category_assortment['assortment_name'].str.contains(
'ASSORTMENT')]
self.sub_category_assortment = pd.merge(self.sub_category_assortment, self.sub_category_weight, how='left',
left_on='sub_category',
right_on='Category')
except KeyError:
self.sub_category_assortment = pd.DataFrame()
self.update_score_sub_category_weights()
try:
self.store_assortment_without_powerskus = \
self.store_assortment[self.store_assortment['assortment_name'].str.contains('ASSORTMENT')]
except KeyError:
self.store_assortment_without_powerskus = pd.DataFrame()
self.adherence_results = pd.DataFrame(columns=['product_fk', 'trax_average',
'suggested_price', 'into_interval', 'min_target', 'max_target',
'percent_range'])
self.extra_spaces_results = pd.DataFrame(
columns=['sub_category_fk', 'template_fk', 'count'])
self.powersku_scores = {}
self.powersku_empty = {}
self.powersku_bonus = {}
self.powersku_price = {}
self.powersku_sos = {}
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
if self.scif.empty:
return
# these function must run first
# self.adherence_results = self.heinz_global_price_adherence(pd.read_excel(Const.PRICE_ADHERENCE_TEMPLATE_PATH,
# sheetname="Price Adherence"))
self.adherence_results = self.heinz_global_price_adherence(self.targets)
self.extra_spaces_results = self.heinz_global_extra_spaces()
self.set_relevant_sub_categories()
# this isn't relevant to the 'Perfect Score' calculation
self.heinz_global_distribution_per_category()
self.calculate_assortment()
self.calculate_powersku_assortment()
self.main_sos_calculation()
self.calculate_powersku_price_adherence()
self.calculate_perfect_store_extra_spaces()
self.check_bonus_question()
self.calculate_perfect_sub_category()
def calculate_assortment(self):
if self.store_assortment_without_powerskus.empty:
return
products_in_store = self.scif[self.scif['facings'] > 0]['product_fk'].unique().tolist()
pass_count = 0
total_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Distribution')
identifier_dict = self.common_v2.get_dictionary(kpi_fk=total_kpi_fk)
oos_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('OOS')
oos_identifier_dict = self.common_v2.get_dictionary(kpi_fk=oos_kpi_fk)
for row in self.store_assortment_without_powerskus.itertuples():
result = 0
if row.product_fk in products_in_store:
result = 1
pass_count += 1
sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Distribution - SKU')
self.common_v2.write_to_db_result(sku_kpi_fk, numerator_id=row.product_fk, denominator_id=row.assortment_fk,
result=result, identifier_parent=identifier_dict, should_enter=True)
oos_result = 0 if result else 1
oos_sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('OOS - SKU')
self.common_v2.write_to_db_result(oos_sku_kpi_fk, numerator_id=row.product_fk,
denominator_id=row.assortment_fk,
result=oos_result, identifier_parent=oos_identifier_dict,
should_enter=True)
number_of_products_in_assortment = len(self.store_assortment_without_powerskus)
if number_of_products_in_assortment:
total_result = (pass_count / float(number_of_products_in_assortment)) * 100
oos_products = number_of_products_in_assortment - pass_count
oos_result = (oos_products / float(number_of_products_in_assortment)) * 100
else:
total_result = 0
oos_products = number_of_products_in_assortment
oos_result = number_of_products_in_assortment
self.common_v2.write_to_db_result(total_kpi_fk, numerator_id=Const.OWN_MANUFACTURER_FK,
denominator_id=self.store_id,
numerator_result=pass_count,
denominator_result=number_of_products_in_assortment,
result=total_result, identifier_result=identifier_dict)
self.common_v2.write_to_db_result(oos_kpi_fk, numerator_id=Const.OWN_MANUFACTURER_FK,
denominator_id=self.store_id,
numerator_result=oos_products,
denominator_result=number_of_products_in_assortment,
result=oos_result, identifier_result=oos_identifier_dict)
def calculate_powersku_assortment(self):
if self.sub_category_assortment.empty:
return 0
sub_category_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_SUB_CATEGORY)
sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU)
target_kpi_weight = float(
self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS['POWERSKU']].iloc[
0])
kpi_weight = self.get_kpi_weight('POWERSKU')
products_in_session = self.scif[self.scif['facings'] > 0]['product_fk'].unique().tolist()
self.sub_category_assortment['in_session'] = \
self.sub_category_assortment.loc[:, 'product_fk'].isin(products_in_session)
# save PowerSKU results at SKU level
for sku in self.sub_category_assortment[
['product_fk', 'sub_category_fk', 'in_session', 'sub_category']].itertuples():
parent_dict = self.common_v2.get_dictionary(
kpi_fk=sub_category_kpi_fk, sub_category_fk=sku.sub_category_fk)
relevant_sub_category_df = self.sub_category_assortment[
self.sub_category_assortment['sub_category'] == sku.sub_category]
if relevant_sub_category_df.empty:
sub_category_count = 0
else:
sub_category_count = len(relevant_sub_category_df)
result = 1 if sku.in_session else 0
score = result * (target_kpi_weight / float(sub_category_count))
self.common_v2.write_to_db_result(sku_kpi_fk, numerator_id=sku.product_fk,
denominator_id=sku.sub_category_fk, score=score,
result=result, identifier_parent=parent_dict, should_enter=True)
# save PowerSKU results at sub_category level
aggregated_results = self.sub_category_assortment.groupby('sub_category_fk').agg(
{'in_session': 'sum', 'product_fk': 'count'}).reset_index().rename(
columns={'product_fk': 'product_count'})
aggregated_results['percent_complete'] = \
aggregated_results.loc[:, 'in_session'] / aggregated_results.loc[:, 'product_count']
aggregated_results['result'] = aggregated_results['percent_complete']
for sub_category in aggregated_results.itertuples():
identifier_dict = self.common_v2.get_dictionary(kpi_fk=sub_category_kpi_fk,
sub_category_fk=sub_category.sub_category_fk)
result = sub_category.result
score = result * kpi_weight
self.powersku_scores[sub_category.sub_category_fk] = score
self.common_v2.write_to_db_result(sub_category_kpi_fk, numerator_id=sub_category.sub_category_fk,
denominator_id=self.store_id,
identifier_parent=sub_category.sub_category_fk,
identifier_result=identifier_dict, result=result * 100, score=score,
weight=target_kpi_weight, target=target_kpi_weight,
should_enter=True)
def heinz_global_distribution_per_category(self):
relevant_stores = pd.DataFrame(columns=self.store_sos_policies.columns)
for row in self.store_sos_policies.itertuples():
policies = json.loads(row.store_policy)
df = self.store_info
for key, value in policies.items():
try:
df_1 = df[df[key].isin(value)]
except KeyError:
continue
if not df_1.empty:
stores = self.store_sos_policies[(self.store_sos_policies['store_policy'] == row.store_policy.encode('utf-8'))
& (
self.store_sos_policies[
'target_validity_start_date'] <= datetime.date(
self.current_date))]
if stores.empty:
relevant_stores = stores
else:
relevant_stores = relevant_stores.append(stores, ignore_index=True)
relevant_stores = relevant_stores.drop_duplicates(subset=['kpi', 'sku_name', 'target', 'sos_policy'],
keep='last')
for row in relevant_stores.itertuples():
sos_policy = json.loads(row.sos_policy)
numerator_key = sos_policy[self.NUMERATOR].keys()[0]
denominator_key = sos_policy[self.DENOMINATOR].keys()[0]
numerator_val = sos_policy[self.NUMERATOR][numerator_key]
denominator_val = sos_policy[self.DENOMINATOR][denominator_key]
target = row.target * 100
if numerator_key == 'manufacturer':
numerator_key = numerator_key + '_name'
if denominator_key == 'sub_category' \
and denominator_val.lower() != 'all' \
and json.loads(row.store_policy).get('store_type') \
and len(json.loads(row.store_policy).get('store_type')) == 1:
try:
denominator_id = self.all_products[self.all_products[denominator_key] == denominator_val][
denominator_key + '_fk'].values[0]
numerator_id = self.all_products[self.all_products[numerator_key] == numerator_val][
numerator_key.split('_')[0] + '_fk'].values[0]
# self.common.write_to_db_result_new_tables(fk=12, numerator_id=numerator_id,
# numerator_result=None,
# denominator_id=denominator_id,
# denominator_result=None,
# result=target)
self.common_v2.write_to_db_result(fk=12, numerator_id=numerator_id, numerator_result=None,
denominator_id=denominator_id, denominator_result=None,
result=target)
except Exception as e:
Log.warning(denominator_key + ' - - ' + denominator_val)
def calculate_perfect_store(self):
pass
def calculate_perfect_sub_category(self):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.PERFECT_STORE_SUB_CATEGORY)
parent_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Const.PERFECT_STORE)
total_score = 0
sub_category_fk_list = []
kpi_type_dict_scores = [self.powersku_scores, self.powersku_empty, self.powersku_price,
self.powersku_sos]
for kpi_dict in kpi_type_dict_scores:
sub_category_fk_list.extend(kpi_dict.keys())
kpi_weight_perfect_store = 0
if self.country in self.sub_category_weight.columns.to_list():
kpi_weight_perfect_store = self.sub_category_weight[self.country][
self.sub_category_weight['Category'] == Const.PERFECT_STORE_KPI_WEIGHT]
if not kpi_weight_perfect_store.empty:
kpi_weight_perfect_store = kpi_weight_perfect_store.iloc[0]
unique_sub_cat_fks = list(dict.fromkeys(sub_category_fk_list))
sub_category_fks = self.sub_category_weight.sub_category_fk.unique().tolist()
relevant_sub_cat_list = [x for x in sub_category_fks if str(x) != 'nan']
# relevant_sub_cat_list = self.sub_category_assortment['sub_category_fk'][
# self.sub_category_assortment['Category'] != pd.np.nan].unique().tolist()
for sub_cat_fk in unique_sub_cat_fks:
if sub_cat_fk in relevant_sub_cat_list:
bonus_score = 0
try:
bonus_score = self.powersku_bonus[sub_cat_fk]
except:
pass
sub_cat_weight = self.get_weight(sub_cat_fk)
sub_cat_score = self.calculate_sub_category_sum(kpi_type_dict_scores, sub_cat_fk)
result = sub_cat_score
score = (result * sub_cat_weight) + bonus_score
total_score += score
self.common_v2.write_to_db_result(kpi_fk, numerator_id=sub_cat_fk,
denominator_id=self.store_id,
result=result, score=score,
identifier_parent=parent_kpi,
identifier_result=sub_cat_fk,
weight=sub_cat_weight * 100,
should_enter=True)
self.common_v2.write_to_db_result(parent_kpi, numerator_id=Const.OWN_MANUFACTURER_FK,
denominator_id=self.store_id,
result=total_score, score=total_score,
identifier_result=parent_kpi,
target=kpi_weight_perfect_store,
should_enter=True)
def main_sos_calculation(self):
relevant_stores = pd.DataFrame(columns=self.store_sos_policies.columns)
for row in self.store_sos_policies.itertuples():
policies = json.loads(row.store_policy)
df = self.store_info
for key, value in policies.items():
try:
if key != 'additional_attribute_3':
df1 = df[df[key].isin(value)]
except KeyError:
continue
if not df1.empty:
stores = \
self.store_sos_policies[(self.store_sos_policies['store_policy'].str.encode(
'utf-8') == row.store_policy.encode('utf-8')) &
(self.store_sos_policies['target_validity_start_date'] <= datetime.date(
self.current_date))]
if stores.empty:
relevant_stores = stores
else:
relevant_stores = relevant_stores.append(stores, ignore_index=True)
relevant_stores = relevant_stores.drop_duplicates(subset=['kpi', 'sku_name', 'target', 'sos_policy'],
keep='last')
results_df = pd.DataFrame(columns=['sub_category', 'sub_category_fk', 'score'])
sos_sub_category_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_SUB_CATEGORY)
for row in relevant_stores.itertuples():
sos_policy = json.loads(row.sos_policy)
numerator_key = sos_policy[self.NUMERATOR].keys()[0]
denominator_key = sos_policy[self.DENOMINATOR].keys()[0]
numerator_val = sos_policy[self.NUMERATOR][numerator_key]
denominator_val = sos_policy[self.DENOMINATOR][denominator_key]
json_policy = json.loads(row.store_policy)
kpi_fk = row.kpi
# This is to assign the KPI to SOS_manufacturer_category_GLOBAL
if json_policy.get('store_type') and len(json_policy.get('store_type')) > 1:
kpi_fk = 8
if numerator_key == 'manufacturer':
numerator_key = numerator_key + '_name'
# we need to include 'Philadelphia' as a manufacturer for all countries EXCEPT Chile
if self.country == 'Chile':
numerator_values = [numerator_val]
else:
numerator_values = [numerator_val, 'Philadelphia']
else:
# if the numerator isn't 'manufacturer', we just need to convert the value to a list
numerator_values = [numerator_val]
if denominator_key == 'sub_category':
include_stacking_list = ['Nuts', 'DRY CHEESE', 'IWSN', 'Shredded', 'SNACK']
if denominator_val in include_stacking_list:
facings_field = 'facings'
else:
facings_field = 'facings_ign_stack'
else:
facings_field = 'facings_ign_stack'
if denominator_key == 'sub_category' and denominator_val.lower() == 'all':
# Here we are talkin on a KPI when the target have no denominator,
# the calculation should be done on Numerator only
numerator = self.scif[(self.scif[numerator_key] == numerator_val) &
(self.scif['location_type'] == 'Primary Shelf')
][facings_field].sum()
kpi_fk = 9
denominator = None
denominator_id = None
else:
numerator = self.scif[(self.scif[numerator_key].isin(numerator_values)) &
(self.scif[denominator_key] == denominator_val) &
(self.scif['location_type'] == 'Primary Shelf')][facings_field].sum()
denominator = self.scif[(self.scif[denominator_key] == denominator_val) &
(self.scif['location_type'] == 'Primary Shelf')][facings_field].sum()
try:
if denominator is not None:
denominator_id = self.all_products[self.all_products[denominator_key] == denominator_val][
denominator_key + '_fk'].values[0]
if numerator is not None:
numerator_id = self.all_products[self.all_products[numerator_key] == numerator_val][
numerator_key.split('_')[0] + '_fk'].values[0]
sos = 0
if numerator and denominator:
sos = np.divide(float(numerator), float(denominator)) * 100
score = 0
target = row.target * 100
if sos >= target:
score = 100
identifier_parent = None
should_enter = False
if denominator_key == 'sub_category' and kpi_fk == row.kpi:
# if this a sub_category result, save it to the results_df for 'Perfect Store' store
results_df.loc[len(results_df)] = [denominator_val, denominator_id, score / 100]
identifier_parent = self.common_v2.get_dictionary(kpi_fk=sos_sub_category_kpi_fk,
sub_category_fk=denominator_id)
should_enter = True
manufacturer = None
self.common_v2.write_to_db_result(kpi_fk, numerator_id=numerator_id, numerator_result=numerator,
denominator_id=denominator_id, denominator_result=denominator,
result=target, score=sos, target=target,
score_after_actions=manufacturer, identifier_parent=identifier_parent,
should_enter=should_enter)
except Exception as e:
Log.warning(denominator_key + ' - - ' + denominator_val)
# if there are no sub_category sos results, there's no perfect store information to be saved
if len(results_df) == 0:
return 0
# save aggregated results for each sub category
kpi_weight = self.get_kpi_weight('SOS')
for row in results_df.itertuples():
identifier_result = \
self.common_v2.get_dictionary(kpi_fk=sos_sub_category_kpi_fk,
sub_category_fk=row.sub_category_fk)
# sub_cat_weight = self.get_weight(row.sub_category_fk)
result = row.score
score = result * kpi_weight
self.powersku_sos[row.sub_category_fk] = score
# limit results so that aggregated results can only add up to 3
self.common_v2.write_to_db_result(sos_sub_category_kpi_fk,
numerator_id=row.sub_category_fk,
denominator_id=self.store_id,
result=row.score, score=score,
identifier_parent=row.sub_category_fk,
identifier_result=identifier_result,
weight=kpi_weight,
target=kpi_weight,
should_enter=True)
def calculate_powersku_price_adherence(self):
adherence_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_PRICE_ADHERENCE)
adherence_sub_category_kpi_fk = \
self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_PRICE_ADHERENCE_SUB_CATEGORY)
if self.sub_category_assortment.empty:
return False
results = pd.merge(self.sub_category_assortment,
self.adherence_results, how='left', on='product_fk')
results['into_interval'].fillna(0, inplace=True)
for row in results.itertuples():
parent_dict = self.common_v2.get_dictionary(kpi_fk=adherence_sub_category_kpi_fk,
sub_category_fk=row.sub_category_fk)
score_value = 'Not Present'
in_session = row.in_session
if in_session:
if not pd.isna(row.trax_average) and row.suggested_price:
price_in_interval = 1 if row.into_interval == 1 else 0
if price_in_interval == 1:
score_value = 'Pass'
else:
score_value = 'Fail'
else:
score_value = 'No Price'
score = Const.PRESENCE_PRICE_VALUES[score_value]
self.common_v2.write_to_db_result(adherence_kpi_fk, numerator_id=row.product_fk,
denominator_id=row.sub_category_fk, result=row.trax_average,
score=score, target=row.suggested_price, numerator_result=row.min_target,
denominator_result=row.max_target,
weight=row.percent_range,
identifier_parent=parent_dict, should_enter=True)
aggregated_results = results.groupby('sub_category_fk').agg(
{'into_interval': 'sum', 'product_fk': 'count'}).reset_index().rename(
columns={'product_fk': 'product_count'})
aggregated_results['percent_complete'] = \
aggregated_results.loc[:, 'into_interval'] / aggregated_results.loc[:, 'product_count']
for row in aggregated_results.itertuples():
identifier_result = self.common_v2.get_dictionary(kpi_fk=adherence_sub_category_kpi_fk,
sub_category_fk=row.sub_category_fk)
kpi_weight = self.get_kpi_weight('PRICE')
result = row.percent_complete
score = result * kpi_weight
self.powersku_price[row.sub_category_fk] = score
self.common_v2.write_to_db_result(adherence_sub_category_kpi_fk, numerator_id=row.sub_category_fk,
denominator_id=self.store_id, result=result, score=score,
numerator_result=row.into_interval, denominator_result=row.product_count,
identifier_parent=row.sub_category_fk,
identifier_result=identifier_result,
weight=kpi_weight, target=kpi_weight,
should_enter=True)
def heinz_global_price_adherence(self, config_df):
config_df = config_df.sort_values(by=["received_time"], ascending=False).drop_duplicates(
subset=['start_date', 'end_date', 'ean_code', 'store_type'], keep="first")
if config_df.empty:
Log.warning("No external_targets data found - Price Adherence will not be calculated")
return self.adherence_results
self.match_product_in_scene.loc[self.match_product_in_scene['price'].isna(), 'price'] = \
self.match_product_in_scene.loc[self.match_product_in_scene['price'].isna(), 'promotion_price']
# =============== remove after updating logic to support promotional pricing ===============
results_df = self.adherence_results
my_config_df = \
config_df[config_df['store_type'].str.encode('utf-8') == self.store_info.store_type[0].encode('utf-8')]
products_in_session = self.scif['product_ean_code'].unique().tolist()
products_in_session = [ean for ean in products_in_session if ean is not pd.np.nan and ean is not None]
my_config_df = my_config_df[my_config_df['ean_code'].isin(products_in_session)]
for row in my_config_df.itertuples():
product_pk = \
self.all_products[self.all_products['product_ean_code']
== row.ean_code]['product_fk'].iloc[0]
mpisc_df_price = \
self.match_product_in_scene[(self.match_product_in_scene['product_fk'] == product_pk) |
(self.match_product_in_scene[
'substitution_product_fk'] == product_pk)]['price']
try:
suggested_price = float(row.suggested_price)
except Exception as e:
Log.error("Product with ean_code {} is not in the configuration file for customer type {}"
.format(row.ean_code, self.store_info.store_type[0].encode('utf-8')))
break
percentage_weight = int(row.percentage_weight)
upper_percentage = (100 + percentage_weight) / float(100)
lower_percentage = (100 - percentage_weight) / float(100)
min_price = suggested_price * lower_percentage
max_price = suggested_price * upper_percentage
percentage_sku = percentage_weight
into_interval = 0
prices_sum = 0
count = 0
trax_average = None
for price in mpisc_df_price:
if price and pd.notna(price):
prices_sum += price
count += 1
if prices_sum > 0:
trax_average = prices_sum / count
into_interval = 0
if not np.isnan(suggested_price):
if min_price <= trax_average <= max_price:
into_interval = 100
results_df.loc[len(results_df)] = [product_pk, trax_average,
suggested_price, into_interval / 100, min_price, max_price,
percentage_sku]
self.common_v2.write_to_db_result(10, numerator_id=product_pk,
numerator_result=suggested_price,
denominator_id=product_pk,
denominator_result=trax_average,
result=row.percentage_weight,
score=into_interval)
if trax_average:
mark_up = (np.divide(np.divide(float(trax_average), float(1.13)),
float(suggested_price)) - 1) * 100
self.common_v2.write_to_db_result(11, numerator_id=product_pk,
numerator_result=suggested_price,
denominator_id=product_pk,
denominator_result=trax_average,
score=mark_up,
result=mark_up)
return results_df
def calculate_perfect_store_extra_spaces(self):
extra_spaces_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(
Const.PERFECT_STORE_EXTRA_SPACES_SUB_CATEGORY)
sub_cats_for_store = self.relevant_sub_categories
if self.extra_spaces_results.empty:
pass
try:
relevant_sub_categories = [x.strip() for x in self.extra_spaces_template[
self.extra_spaces_template['country'].str.encode('utf-8') == self.country.encode('utf-8')][
'sub_category'].iloc[0].split(',')]
except IndexError:
Log.warning(
'No relevant sub_categories for the Extra Spaces KPI found for the following country: {}'.format(
self.country))
self.extra_spaces_results = pd.merge(self.extra_spaces_results,
self.all_products.loc[:, [
'sub_category_fk',
'sub_category']].dropna().drop_duplicates(),
how='left', on='sub_category_fk')
relevant_extra_spaces = \
self.extra_spaces_results[self.extra_spaces_results['sub_category'].isin(
relevant_sub_categories)]
kpi_weight = self.get_kpi_weight('EXTRA')
for row in relevant_extra_spaces.itertuples():
self.powersku_empty[row.sub_category_fk] = 1 * kpi_weight
score = result = 1
if row.sub_category_fk in sub_cats_for_store:
sub_cats_for_store.remove(row.sub_category_fk)
self.common_v2.write_to_db_result(extra_spaces_kpi_fk, numerator_id=row.sub_category_fk,
denominator_id=row.template_fk, result=result, score=score,
identifier_parent=row.sub_category_fk,
target=1, should_enter=True)
for sub_cat_fk in sub_cats_for_store:
result = score = 0
self.powersku_empty[sub_cat_fk] = 0
self.common_v2.write_to_db_result(extra_spaces_kpi_fk, numerator_id=sub_cat_fk,
denominator_id=0, result=result, score=score,
identifier_parent=sub_cat_fk,
target=1, should_enter=True)
def heinz_global_extra_spaces(self):
try:
supervisor = self.store_info['additional_attribute_3'][0]
store_target = -1
# for row in self.store_sos_policies.itertuples():
# policies = json.loads(row.store_policy)
# for key, value in policies.items():
# try:
# if key == 'additional_attribute_3' and value[0] == supervisor:
# store_target = row.target
# break
# except KeyError:
# continue
for row in self.supervisor_target.itertuples():
try:
if row.supervisor == supervisor:
store_target = row.target
break
except:
continue
except Exception as e:
Log.error("Supervisor target is not configured for the extra spaces report ")
raise e
results_df = self.extra_spaces_results
# limit to only secondary scenes
relevant_scif = self.scif[(self.scif['location_type_fk'] == float(2)) &
(self.scif['facings'] > 0)]
if relevant_scif.empty:
return results_df
# aggregate facings for every scene/sub_category combination in the visit
relevant_scif = \
relevant_scif.groupby(['scene_fk', 'template_fk', 'sub_category_fk'], as_index=False)['facings'].sum()
# sort sub_categories by number of facings, largest first
relevant_scif = relevant_scif.sort_values(['facings'], ascending=False)
# drop all but the sub_category with the largest number of facings for each scene
relevant_scif = relevant_scif.drop_duplicates(subset=['scene_fk'], keep='first')
for row in relevant_scif.itertuples():
results_df.loc[len(results_df)] = [row.sub_category_fk, row.template_fk, row.facings]
self.common_v2.write_to_db_result(13, numerator_id=row.template_fk,
numerator_result=row.facings,
denominator_id=row.sub_category_fk,
denominator_result=row.facings,
context_id=row.scene_fk,
result=store_target)
return results_df
def check_bonus_question(self):
bonus_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.BONUS_QUESTION_SUB_CATEGORY)
bonus_weight = self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS['Bonus']].iloc[0]
sub_category_fks = self.sub_category_weight.sub_category_fk.unique().tolist()
sub_category_fks = [x for x in sub_category_fks if str(x) != 'nan']
if self.survey.check_survey_answer(('question_fk', Const.BONUS_QUESTION_FK), 'Yes,yes,si,Si'):
result = 1
else:
result = 0
for sub_cat_fk in sub_category_fks:
sub_cat_weight = self.get_weight(sub_cat_fk)
score = result * sub_cat_weight
target_weight = bonus_weight * sub_cat_weight
self.powersku_bonus[sub_cat_fk] = score
self.common_v2.write_to_db_result(bonus_kpi_fk, numerator_id=sub_cat_fk,
denominator_id=self.store_id,
result=result, score=score, identifier_parent=sub_cat_fk,
weight=target_weight, target=target_weight,
should_enter=True)
def commit_results_data(self):
self.common_v2.commit_results_data()
def update_score_sub_category_weights(self):
all_sub_category_fks = self.all_products[['sub_category', 'sub_category_fk']].drop_duplicates()
self.sub_category_weight = pd.merge(self.sub_category_weight, all_sub_category_fks, left_on='Category',
right_on='sub_category',
how='left')
def get_weight(self, sub_category_fk):
weight_value = 0
if self.country in self.sub_category_weight.columns.to_list():
weight_df = self.sub_category_weight[self.country][
(self.sub_category_weight.sub_category_fk == sub_category_fk)]
if weight_df.empty:
return 0
weight_value = weight_df.iloc[0]
if pd.isna(weight_value):
weight_value = 0
weight = weight_value * 0.01
return weight
def get_kpi_weight(self, kpi_name):
weight = self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS[kpi_name]].iloc[0]
return weight
def get_supervisor_target(self):
supervisor_target = self.targets[self.targets['kpi_type'] == 'Extra Spaces']
return supervisor_target
def calculate_sub_category_sum(self, dict_list, sub_cat_fk):
total_score = 0
for item in dict_list:
try:
total_score += item[sub_cat_fk]
except:
pass
return total_score
def set_relevant_sub_categories(self):
if self.country in self.sub_category_weight.columns.to_list():
df = self.sub_category_weight[['Category', 'sub_category_fk', self.country]].dropna()
self.relevant_sub_categories = df.sub_category_fk.to_list()
else:
self.relevant_sub_categories = []
class ToolBox:
def __init__(self, data_provider, output, common):
self.common = common
self.output = output
self.data_provider = data_provider
# ----------- fix for nan types in dataprovider -----------
# all_products = self.data_provider._static_data_provider.all_products.where(
# (pd.notnull(self.data_provider._static_data_provider.all_products)), None)
# self.data_provider._set_all_products(all_products)
# self.data_provider._init_session_data(None, True)
# self.data_provider._init_report_data(self.data_provider.session_uid)
# self.data_provider._init_reporting_data(self.data_provider.session_id)
# ----------- fix for nan types in dataprovider -----------
self.block = Block(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.templates = self.data_provider.all_templates
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.result_values_dict = self.make_result_values_dict()
self.store_assortment = self.ps_data_provider.get_store_assortment()
self.store_sos_policies = self.ps_data_provider.get_store_policies()
self.labels = self.ps_data_provider.get_labels()
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.products = self.data_provider[Data.PRODUCTS]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.full_mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p'])\
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s'])\
.merge(self.templates, on='template_fk', suffixes=['', '_t'])
self.mpis = self.full_mpis[self.full_mpis['product_type'] != 'Irrelevant']
self.mpis = self.filter_df(self.mpis, Const.SOS_EXCLUDE_FILTERS, exclude=1)
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scenes = self.scene_info['scene_fk'].tolist()
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.tmb_map = pd.read_excel(Const.TMB_MAP_PATH).set_index('Num Shelves').to_dict('index')
self .blockchain = {}
self.template = {}
self.dependencies = {}
self.dependency_lookup = {}
self.base_measure = None
self.global_fail = 0
# main functions:
def main_calculation(self, template_path):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
self.template = pd.read_excel(template_path, sheetname=None)
self.dependencies = {key: None for key in self.template[Const.KPIS][Const.KPI_NAME]}
self.dependency_reorder()
main_template = self.template[Const.KPIS]
self.dependency_lookup = main_template.set_index(Const.KPI_NAME)[Const.DEPENDENT].to_dict()
self.shun()
for i, main_line in main_template.iterrows():
self.global_fail = 0
self.calculate_main_kpi(main_line)
# self.flag_failures()
def calculate_main_kpi(self, main_line):
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.TYPE]
scene_types = self.read_cell_from_line(main_line, Const.SCENE_TYPE)
general_filters = {}
relevant_scif = self.filter_df(self.scif.copy(), Const.SOS_EXCLUDE_FILTERS, exclude=1)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
general_filters['template_name'] = scene_types
if relevant_scif.empty:
return
# if kpi_name not in (
# 'What is Apple Sauce Multi Serve linear footage?',
# 'What is Apple Sauce Single Serve linear footage?',
# 'What is Canned Fruit linear footage?',
# 'What is Canned Veg category linear footage?',
# 'What is Del Monte Canned Veg linear footage?',
# 'What is PFC linear footage?',
# 'What is Squeezers linear footage',
# 'What is the COS Fruit category linear footage?',
#
# ):
# # if kpi_name not in ('Are the majority of Green Giant Spec Veg blocked above Green Giant Core Veg'):
if kpi_name not in ('Are PFC shelved between Canned and Squeezers?'):
# if kpi_name not in ('is multi serve pineapple shelved above Canned Fruit?'):
# if kpi_type not in (Const.BLOCKING, Const.BLOCKING_PERCENT, Const.SOS, Const.ANCHOR, Const.MULTI_BLOCK,
# Const.SAME_AISLE, Const.SHELF_REGION, Const.SHELF_PLACEMENT):
return
# print('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~')
# print(kpi_name)
dependent_kpis = self.read_cell_from_line(main_line, Const.DEPENDENT)
dependent_results = self.read_cell_from_line(main_line, Const.DEPENDENT_RESULT)
if dependent_kpis:
for dependent_kpi in dependent_kpis:
if self.dependencies[dependent_kpi] not in dependent_results:
if dependent_results or self.dependencies[dependent_kpi] is None:
return
kpi_line = self.template[kpi_type].set_index(Const.KPI_NAME).loc[kpi_name]
function = self.get_kpi_function(kpi_type)
try:
all_kwargs = function(kpi_name, kpi_line, relevant_scif, general_filters)
except Exception as e:
# print(e)
if self.global_fail:
all_kwargs = [{'score': 0, 'result': None, 'failed': 0}]
Log.warning('kpi "{}" failed to calculate'.format(kpi_name))
else:
all_kwargs = [{'score': 0, 'result': None, 'failed': 1}]
Log.error('kpi "{}" failed error: "{}"'.format(kpi_name, e))
finally:
if not isinstance(all_kwargs, list) or not all_kwargs:
all_kwargs = [all_kwargs]
# print(all_kwargs)
for kwargs in all_kwargs:
if not kwargs or kwargs['score'] is None:
kwargs = {'score': 0, 'result': 0, 'failed': 0}
self.write_to_db(kpi_name, **kwargs)
self.dependencies[kpi_name] = kwargs['result']
def flag_failures(self):
for kpi, val in self.dependencies.items():
if val is None:
Log.warning('Warning: KPI "{}" not run for session "{}"'.format(
kpi, self.session_uid))
def calculate_sos(self, kpi_name, kpi_line, relevant_scif, general_filters):
num = self.filter_df(relevant_scif, self.get_kpi_line_filters(kpi_line))[
'net_len_ign_stack'].sum() / 304.8
return {'score': 1, 'result': num}
def calculate_same_aisle(self, kpi_name, kpi_line, relevant_scif, general_filters):
filters = self.get_kpi_line_filters(kpi_line)
relevant_scif = self.filter_df(self.scif, filters)
if relevant_scif.empty:
return
result = 0
if len(relevant_scif.scene_fk.unique()) == 1:
result = 1
return {'score': 1, 'result': result}
def calculate_shelf_placement(self, kpi_name, kpi_line, relevant_scif, general_filters):
location = kpi_line['Shelf Placement'].lower()
tmb_map = pd.read_excel(Const.TMB_MAP_PATH).melt(id_vars=['Num Shelves'], var_name=['Shelf'])\
.set_index(['Num Shelves', 'Shelf']).reset_index()
tmb_map.columns = ['max_shelves', 'shelf_number_from_bottom', 'TMB']
tmb_map['TMB'] = tmb_map['TMB'].str.lower()
filters = self.get_kpi_line_filters(kpi_line)
mpis = self.filter_df(self.mpis, filters)
mpis = self.filter_df(mpis, {'stacking_layer': 1})
if mpis.empty:
return
filters.update(general_filters)
mpis = self.filter_df(mpis, {'scene_fk': list(relevant_scif.scene_id.unique())})
bay_shelf = self.filter_df(self.full_mpis, general_filters).set_index(['scene_fk', 'bay_number'])\
.groupby(level=[0, 1])[['shelf_number', 'shelf_number_from_bottom']].max()
bay_max_shelf = bay_shelf['shelf_number'].to_dict()
bay_shelf['shelf_offset'] = bay_shelf['shelf_number_from_bottom'] - \
bay_shelf['shelf_number']
bay_shelf = bay_shelf.drop('shelf_number_from_bottom', axis=1).rename(
columns={'shelf_number': 'max_shelves'})
mpis = mpis.merge(bay_shelf, on=['bay_number', 'scene_fk'])
mpis['true_shelf'] = mpis['shelf_number_from_bottom'] + mpis['shelf_offset']
mpis = mpis.merge(tmb_map, on=['max_shelves', 'shelf_number_from_bottom'])
result = self.safe_divide(self.filter_df(mpis, {'TMB': location}).shape[0], mpis.shape[0])
return {'score': 1, 'result': result}
def calulate_shelf_region(self, kpi_name, kpi_line, relevant_scif, general_filters):
base = self.get_base_name(kpi_name, Const.REGIONS)
location = kpi_line['Shelf Placement'].lower()
if base not in self.blockchain:
num_filters = self.get_kpi_line_filters(kpi_line, 'numerator')
den_filters = self.get_kpi_line_filters(kpi_line, 'denominator')
mpis = self.filter_df(self.mpis, den_filters)
reg_list = ['left', 'center', 'right']
self.blockchain[base] = {reg: 0 for reg in reg_list}
self.blockchain[base]['den'] = 0
for scene in mpis.scene_fk.unique():
smpis = self.filter_df(mpis, {'scene_fk': scene})
num_df = self.filter_df(smpis, num_filters)
bays = sorted(list(smpis.bay_number.unique()))
size = len(bays) / Const.NUM_REG
mod = len(bays) % Const.NUM_REG
# find start ponts for center and right groups (left is always 0), this is bays var index
center = size
right = size * 2
if mod == 1:
right += 1 # if there is one odd bay we expand center
elif mod == 2:
center += 1 # If 2, we expand left and right by one
right += 1
self.blockchain[base]['den'] += num_df.shape[0]
regions = [0, center, right, len(bays)]
for i, reg in enumerate(reg_list):
self.blockchain[base][reg] += self.filter_df(
num_df, {'bay_number': bays[regions[i]:regions[i+1]]}).shape[0]
result = self.safe_divide(self.blockchain[base][location], self.blockchain[base]['den'])
return {'score': 1, 'result': result}
def calculate_sequence(self, kpi_name, kpi_line, relevant_scif, general_filters):
# this attribute should be pulled from the template once the template is updated
vector = kpi_line['Vector']
orth = (set(['x', 'y']) - set(vector)).pop()
Segment = namedtuple('Segment', 'seg position facings orth_min orth_max matches')
segments = [i.strip() for i in self.splitter(kpi_line['Sequence'])]
result = 0
for scene in relevant_scif.scene_fk.unique():
scene_scif = relevant_scif[relevant_scif['scene_fk'] == scene]
seg_list = []
for seg in segments:
seg_filters = self.get_kpi_line_filters(kpi_line, seg)
_, _, mpis_dict, _, results = self.base_block(kpi_name, kpi_line, scene_scif,
general_filters,
filters=seg_filters,
check_orient=0)
cluster = results.sort_values('facing_percentage', ascending=False).iloc[0, 0]
df = pd.DataFrame([(n['polygon'].centroid.x, n['polygon'].centroid.y, n['facings'],
list(n['match_fk'].values)) + n['polygon'].bounds
for i, n in cluster.nodes(data=True) if n['block_key'].value
not in Const.ALLOWED_FLAGS],
columns=['x', 'y', 'facings', 'matches', 'x_min', 'y_min', 'x_max', 'y_max'])
facings = df.facings.sum()
seg_list.append(Segment(seg=seg, position=(df[vector]*df['facings']).sum()/facings, facings=facings,
orth_min=mpis_dict[scene]['rect_{}'.format(orth)].min(),
orth_max=mpis_dict[scene]['rect_{}'.format(orth)].max(),
matches=df['matches'].sum()))
order = [x.seg for x in sorted(seg_list, key=lambda x: x.position)]
if '_'.join(order) == '_'.join(segments) or \
(kpi_line['Reversible'] in ['Y', 'y'] and '_'.join(order) == '_'.join(segments[::-1])):
flow_count = 1 # 1 is intentional, since loop is smaller than list by 1
for i in range(1, len(order)):
if self.safe_divide(self.seq_axis_engulfs_df(i, seg_list, orth), seg_list[i].facings) >= .1 and\
self.safe_divide(self.seq_axis_engulfs_df(i, seg_list, orth, r=1), seg_list[i-1].facings) >= .1:
flow_count += 1
if flow_count == len(order):
result = 1
return {'result': result, 'score': 1}
def seq_axis_engulfs_df(self, i, seg_list, orth, r=0):
j = i - 1
if r:
i, j = j, i
return self.mpis[(self.mpis['scene_match_fk'].isin(seg_list[i].matches)) &
(seg_list[j].orth_min <= self.mpis['rect_{}'.format(orth)]) &
(self.mpis['rect_{}'.format(orth)] <= seg_list[j].orth_max)].shape[0]
def calculate_max_block_adj_base(self, kpi_name, kpi_line, relevant_scif, general_filters):
allowed_edges = [x.upper() for x in self.read_cell_from_line(kpi_line, Const.EDGES)]
d = {'A': {}, 'B': {}}
for k, v in d.items():
filters = self.get_kpi_line_filters(kpi_line, k)
_, _, mpis_dict, _, results = self.base_block(kpi_name, kpi_line, relevant_scif,
general_filters,
filters=filters,
check_orient=0)
v['row'] = results.sort_values('facing_percentage', ascending=False).iloc[0, :]
v['items'] = sum([list(n['match_fk']) for i, n in v['row']['cluster'].nodes(data=True)
if n['block_key'].value not in Const.ALLOWED_FLAGS], [])
scene_graph = self.block.adj_graphs_by_scene[d[k]['row']['scene_fk']]
matches = [(edge, scene_graph[item][edge]['direction']) for item in v['items']
for edge in scene_graph[item].keys() if scene_graph[item][edge]['direction'] in allowed_edges]
v['edge_matches'], v['directions'] = zip(*matches) if matches else ([], [])
result = 0
if set(d['A']['edge_matches']) & set(d['B']['items']):
result = 1
return {'score': 1, 'result': result}, set(d['A']['directions'])
def calculate_max_block_adj(self, kpi_name, kpi_line, relevant_scif, general_filters):
result, _ = self.calculate_max_block_adj_base(kpi_name, kpi_line, relevant_scif, general_filters)
return result
def calculate_integrated_core(self, kpi_name, kpi_line, relevant_scif, general_filters):
result, dirs = self.calculate_max_block_adj_base(kpi_name, kpi_line, relevant_scif, general_filters)
if len(dirs) < 2:
result['result'] = 0
return result
def calculate_block_together(self, kpi_name, kpi_line, relevant_scif, general_filters):
result, _ = self.calculate_max_block_adj_base(kpi_name, kpi_line, relevant_scif, general_filters)
result['result'] = result['result'] ^ 1 # this kpi is reversed (is not blocked together?) so we xor
return result
def calculate_serial_adj(self, kpi_name, kpi_line, relevant_scif, general_filters):
result = {'score': 0, 'result': 0}
scif = self.filter_df(relevant_scif, self.get_kpi_line_filters(kpi_line, 'A'))
sizes = self.get_kpi_line_filters(kpi_line, 'A')['DLM_ VEGSZ(C)']
num_count_sizes = 0 if self.get_kpi_line_filters(kpi_line, 'A')['DLM_ VEGSZ(C)'] == [u'FAMILY LARGE'] else 1
if scif.empty:
return
subsets = scif[kpi_line['Unit']].unique()
tally = 0
skip = 0
for subset in subsets:
size_pass = 0
size_skip = 0
for size in sizes:
sub_kpi_line = kpi_line.copy()
for i in sub_kpi_line.index:
if sub_kpi_line[i] == ','.join(sizes):
sub_kpi_line[i] == size
general_filters[kpi_line['Unit']] = [subset]
try:
result, _ = self.calculate_max_block_adj_base(kpi_name, sub_kpi_line, relevant_scif, general_filters)
tally += result['result']
size_pass += 1
except TypeError: # yeah, i really should define a custom error, but, another day
size_skip += 1 # we will ignore subsets that are missing either A group or B group
if size_pass and not num_count_sizes: # Family large only needs to be next to one size, so we need to be careful how we increment skip
skip += 0 # family passed, even if one size failed, so we don't increment skip
if not size_pass and not num_count_sizes:
skip += 1 # Family size failed so we increment by one
else:
skip += size_skip # this is the mutipk rt.
target = len(subsets)*len(sizes) - skip if num_count_sizes else len(subsets) - skip #family only needs to pass one size, multipk both
result['result'] = 0 if target else None
if self.safe_divide(tally, target) > 75:
result['result'] = 1
return result
def calculate_adjacency_list(self, kpi_name, kpi_line, relevant_scif, general_filters):
max_block = self.read_cell_from_line(kpi_line, Const.MAX_BLOCK)
item_filters = {}
kwargs_list = []
if max_block:
_, _, _, _, blocks = self.base_block(
kpi_name, kpi_line, relevant_scif, general_filters, check_orient=False)
block = blocks.sort_values('facing_percentage').reset_index().iloc[-1, :]['cluster']
ids = sum([node['group_attributes']['match_fk_list']
for i, node in block.node(data=True)], [])
item_filters = {'scene_match_fk': ids}
if Const.END_OF_CAT in self.get_results_value(kpi_line):
anchor_filters = item_filters if item_filters else self.get_kpi_line_filters(kpi_line)
anchor = self.anchor_base(general_filters, anchor_filters,
relevant_scif['scene_fk'].unique(), 1)
if sum(anchor.values()) > 0:
kwargs_list.append({'score': 1, 'result': Const.END_OF_CAT, 'target': 1})
all_results = self.base_adjacency(
kpi_name, kpi_line, relevant_scif, general_filters, item_filters=item_filters)
for result in sum([x for x, y in all_results.values()], []):
# result_fk = self.result_values_dict[result]
kwargs_list.append({'score': 1, 'result': result})
return kwargs_list
def anchor_base(self, general_filters, potential_end, scenes, min_shelves, ratio=False):
results = {}
cat_filters = dict(general_filters)
func_dict = {'left': [min, op.lt, float('inf')], 'right': [max, op.gt, 0]}
results['left'], results['right'] = 0, 0
for scene in scenes:
cat_filters['scene_fk'] = scene
cat_mpis = self.filter_df(self.mpis, cat_filters)
# cat_mpis = self.filter_df(cat_mpis, Const.ALLOWED_FILTERS, exclude=1)
cat_mpis = self.filter_df(cat_mpis, {'product_type': 'Empty'}, exclude=1)
cat_mpis = self.filter_df(cat_mpis, {'stacking_layer': 1})
bays = {'left': cat_mpis['bay_number'].min(), 'right': cat_mpis['bay_number'].max()}
for dir, bay in bays.items():
agg_func, operator, fill_val = func_dict[dir]
bay_mpis = self.filter_df(cat_mpis, {'bay_number': bay})
smpis = self.filter_df(bay_mpis, potential_end).groupby(
['scene_fk', 'bay_number', 'shelf_number'])['facing_sequence_number'].agg(agg_func)
if smpis.empty:
continue
rmpis = self.filter_df(bay_mpis, potential_end, exclude=1) \
.groupby(['scene_fk', 'bay_number', 'shelf_number'])['facing_sequence_number'].agg(agg_func)
locs = pd.concat([smpis, rmpis], axis=1)
locs.columns = ['A', 'B']
locs.dropna(subset=['A'], inplace=True)
if ratio:
min_shelves = max(self.filter_df(
self.mpis, {'scene_fk': scene, 'bay_number': bay})['shelf_number'])
min_shelves = round(min_shelves / 2.0)
locs.fillna(fill_val, inplace=True)
if sum(operator(locs['A'], locs['B'])) >= min_shelves:
results[dir] = 1
return results
def calculate_anchor(self, kpi_name, kpi_line, relevant_scif, general_filters):
scenes = relevant_scif['scene_fk'].unique()
potential_end = self.get_kpi_line_filters(kpi_line, 'numerator')
general_filters.update(self.get_kpi_line_filters(kpi_line, 'denominator'))
results = self.anchor_base(general_filters, potential_end, scenes, 0, ratio=True)
edges = self.splitter(kpi_line[Const.EDGES].strip())
result = 0
for edge in edges:
if results[edge]:
result = 1
return {'score': 1, 'result': result}
def base_block(self, kpi_name, kpi_line, relevant_scif, general_filters_base, check_orient=1, other=1, filters={},
multi=0):
result = pd.DataFrame()
general_filters = dict(general_filters_base)
blocks = pd.DataFrame()
result = pd.DataFrame()
orientation = 'Not Blocked'
scenes = self.filter_df(relevant_scif, general_filters).scene_fk.unique()
if 'template_name' in general_filters:
del general_filters['template_name']
if 'scene_fk' in general_filters:
del general_filters['scene_fk']
mpis_dict = {}
valid_scene_found = 0
for scene in scenes:
score = 0
empty_check = 0
scene_filter = {'scene_fk': scene}
if not filters:
filters = self.get_kpi_line_filters(kpi_line)
filters.update(general_filters)
# mpis is only here for debugging purposes
mpis = self.filter_df(self.mpis, scene_filter)
mpis = self.filter_df(mpis, filters)
mpis = self.filter_df(mpis, {'stacking_layer': 1})
mpis_dict[scene] = mpis
if mpis.empty:
empty_check = -1
continue
allowed_filter = Const.ALLOWED_FILTERS
if not other:
allowed_filter = {'product_type': 'Empty'}
result = pd.concat([result, self.block.network_x_block_together(filters, location=scene_filter,
additional={
'allowed_products_filters': allowed_filter,
'include_stacking': False,
'check_vertical_horizontal': check_orient,
'minimum_facing_for_block': 1})])
blocks = result[result['is_block'] == True]
valid_scene_found = 1
if not blocks.empty and not multi:
score = 1
orientation = blocks.loc[0, 'orientation']
break
if empty_check == -1 and not valid_scene_found:
self.global_fail = 1
raise TypeError('No Data Found fo kpi "'.format(kpi_name))
return score, orientation, mpis_dict, blocks, result
def calculate_block(self, kpi_name, kpi_line, relevant_scif, general_filters):
base = self.get_base_name(kpi_name, Const.ORIENTS)
if base in self.blockchain:
# Data exists. Get it.
result, orientation, mpis_dict, blocks = self.blockchain[base]
else:
# Data doesn't exist, so create and add it
result, orientation, mpis_dict, blocks, _ = self.base_block(
kpi_name, kpi_line, relevant_scif, general_filters)
self.blockchain[base] = result, orientation, mpis_dict, blocks # result_fk = self.result_values_dict[orientation]
if kpi_line['AntiBlock'] in ['Y', 'y']:
result = result ^ 1
kwargs = {'score': 1, 'result': result}
return kwargs
def calculate_block_orientation(self, kpi_name, kpi_line, relevant_scif, general_filters):
allowed_orientation = kpi_line['Orientation'].strip()
# Check if data for this kpi already exists
base = self.get_base_name(kpi_name, Const.ORIENTS)
if base in self.blockchain:
# Data exists. Get it.
result, orientation, mpis_dict, blocks = self.blockchain[base]
else:
# Data doesn't exist, so create and add it
result, orientation, mpis_dict, blocks, _ = self.base_block(
kpi_name, kpi_line, relevant_scif, general_filters)
self.blockchain[base] = result, orientation, mpis_dict, blocks
if allowed_orientation.upper() != orientation:
result = 0
return {'score': 1, 'result': result}
# def calculate_block_percent(self, kpi_name, kpi_line, relevant_scif, general_filters):
#
# def concater(a, b):
# return pd.concat([a, b])
#
# allowed_orientation = kpi_line['Orientation'].strip()
# facings, score, den, result = 0, 0, 0, 0
# # Check if data for this kpi already exists
# base = self.get_base_name(kpi_name, Const.ORIENTS)
# if base in self.blockchain:
# # Data exists. Get it.
# score, orientation, mpis_dict, blocks = self.blockchain[base]
# else:
# # Data doesn't exist, so create and add it
# score, orientation, mpis_dict, blocks, _ = self.base_block(
# kpi_name, kpi_line, relevant_scif, general_filters)
# self.blockchain[base] = score, orientation, mpis_dict, blocks
#
# den = reduce(concater, mpis_dict.values()).shape[0]
# if orientation.lower() == allowed_orientation:
# for row in blocks.itertuples():
# skus = sum([list(node['match_fk']) for i, node in row.cluster.nodes(data=True)], [])
# mpis = mpis_dict[row.scene_fk]
# facings = mpis[mpis['scene_match_fk'].isin(skus)].shape[0]
# score = 1
# result = self.safe_divide(facings, den)
# return {'numerator_result': facings, 'denominator_result': den, 'result': result, 'score': score}
def calculate_multi_block(self, kpi_name, kpi_line, relevant_scif, general_filters):
den_filter = self.get_kpi_line_filters(kpi_line, 'denominator')
num_filter = self.get_kpi_line_filters(kpi_line, 'numerator')
if kpi_line[Const.ALL_SCENES_REQUIRED] in ('Y', 'y'): # get value for all scenes required
all_scenes_required = True
else:
all_scenes_required = False
groups = list(*num_filter.values())
result = 0
scenes = self.filter_df(relevant_scif, general_filters).scene_fk.unique()
if 'template_name' in general_filters:
del general_filters['template_name']
for scene in scenes: # check every scene
groups_exempt = 0
score = 0
scene_general_filters = general_filters.copy()
scene_general_filters.update({'scene_fk': scene})
for group in groups: # check all the groups in the current scene
sub_filters = {num_filter.keys()[0]: [group]}
sub_filters.update(den_filter)
sub_score = 0
try:
sub_score, _, _, _, _ = self.base_block(kpi_name, kpi_line, relevant_scif, scene_general_filters,
check_orient=0, filters=sub_filters)
except TypeError as e:
if e[0] == 'No Data Found fo kpi "': # no relevant products found, so this group is exempt
groups_exempt += 1
else:
raise e
score += sub_score
if score and score == len(groups) - groups_exempt: # check to make sure all non-exempt groups were blocked
result += 1
if not all_scenes_required: # we already found one passing scene so we don't need to continue
break
if all_scenes_required:
final_result = 1 if result == len(scenes) else 0 # make sure all scenes have a passing result
else:
final_result = 1 if result > 0 else 0
return {'score': 1, 'result': final_result}
def make_mpis(self, kpi_line, general_filters, ign_stacking=1, use_full_mpis=0):
mpis = self.full_mpis if use_full_mpis else self.mpis
filters = self.get_kpi_line_filters(kpi_line)
filters.update(general_filters)
if ign_stacking:
filters.update(Const.IGN_STACKING)
return self.filter_df(self.mpis, filters)
def shun(self):
exclude = self.template['Exclude']
filters = {}
for i, row in exclude.iterrows():
filters.update(self.get_kpi_line_filters(row))
self.mpis = self.filter_df(self.mpis, filters, exclude=1)
self.full_mpis = self.filter_df(self.full_mpis, filters, exclude=1)
self.scif = self.filter_df(self.scif, filters, exclude=1)
@staticmethod
def filter_df(df, filters, exclude=0):
cols = set(df.columns)
for key, val in filters.items():
if key not in cols:
return pd.DataFrame()
if not isinstance(val, list):
val = [val]
if exclude:
df = df[~df[key].isin(val)]
else:
df = df[df[key].isin(val)]
return df
@staticmethod
def filter_mask(df, filters, exclude=0):
mask = []
for key, val in filters.items():
if not isinstance(val, list):
val = [val]
if exclude:
mask.append(~df[key].isin(val))
else:
mask.append(df[key].isin(val))
return reduce((lambda x, y: x & y), mask)
@staticmethod
def filter_join(filters):
final_filter = defaultdict(list)
filters = reduce((lambda x, y: x + y.items() if isinstance(x, list)
else x.items() + y.items()), filters)
for (key, val) in filters:
final_filter[key].append(val)
return final_filter
@staticmethod
def ratio_score(num, den, target=None):
ratio = 0
if den:
ratio = round(num*100.0/den, 2)
score = 1 if ratio >= target and target else 0
return ratio, score
@staticmethod
def read_cell_from_line(line, col):
try:
val = line[col] if not pd.isnull(line[col]) else []
except:
val = []
if val:
if hasattr(val, 'split'):
if ', ' in val:
val = val.split(', ')
elif ',' in val:
val = val.split(',')
if not isinstance(val, list):
val = [val]
return val
def get_kpi_line_filters(self, kpi_orig, name=''):
kpi_line = kpi_orig.copy()
if name:
name = name.lower() + ' '
filters = defaultdict(list)
attribs = [x.lower() for x in kpi_line.index]
kpi_line.index = attribs
c = 1
while 1:
if '{}param {}'.format(name, c) in attribs and kpi_line['{}param {}'.format(name, c)]\
and not pd.isnull(kpi_line['{}param {}'.format(name, c)]):
filters[kpi_line['{}param {}'.format(
name, c)]] += self.splitter(kpi_line['{}value {}'.format(name, c)])
else:
if c > 3: # just in case someone inexplicably chose a nonlinear numbering format.
break
c += 1
return filters
@staticmethod
def splitter(text_str, delimiter=','):
ret = [text_str]
if hasattr(text_str, 'split'):
ret = [x.strip() for x in text_str.split(delimiter)]
return ret
@staticmethod
def get_base_name(kpi, group):
base = kpi.lower()
for obj in group:
base = base.replace(obj, '').strip()
return base
@staticmethod
def safe_divide(num, den):
res = 0
if den:
res = num*100.0 / den
return res
def sos_with_num_and_dem(self, kpi_line, relevant_scif, general_filters, facings_field):
num_filters = self.get_kpi_line_filters(kpi_line, name='numerator')
den_filters = self.get_kpi_line_filters(kpi_line, name='denominator')
num_filters.update(general_filters)
den_filters.update(general_filters)
num_scif = self.filter_df(relevant_scif, num_filters)
den_scif = self.filter_df(relevant_scif, den_filters)
den = den_scif[facings_field].sum()
num = num_scif[facings_field].sum()
if den:
ratio = round((num / float(den))*100, 2)
else:
ratio = 0
return ratio, num, den
def dependency_reorder(self):
kpis = self.template[Const.KPIS].copy()
name_to_index = kpis.reset_index().set_index(Const.KPI_NAME)['index'].to_dict()
dependent_index = list(kpis[kpis[Const.DEPENDENT].notnull()].index)
kpis_index = list(set(kpis.index) - set(dependent_index))
set_index = set(kpis_index)
c = 0
while dependent_index:
i = dependent_index.pop(0)
kpi = kpis.loc[i, Const.KPI_NAME]
dependencies = self.read_cell_from_line(kpis.loc[i, :], Const.DEPENDENT)
met = True
for dependency in dependencies:
if name_to_index[dependency] not in set_index:
met = False
if met:
kpis_index.append(i)
set_index.add(i)
c = 0
else:
dependent_index.append(i)
c += 1
if c > kpis.shape[0] * 1.1:
Log.error('Circular Dependency Found: KPIs Affected {}'.format(
[kpis.loc[i, Const.KPI_NAME] for i in dependent_index]))
break
self.template[Const.KPIS] = kpis.reindex(index=pd.Index(kpis_index)).reset_index(drop=True)
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SHELF_PLACEMENT:
return self.calculate_shelf_placement
elif kpi_type == Const.SHELF_REGION:
return self.calulate_shelf_region
elif kpi_type == Const.ADJACENCY:
return self.calculate_adjacency_list
elif kpi_type == Const.ANCHOR:
return self.calculate_anchor
elif kpi_type == Const.BLOCKING:
return self.calculate_block
elif kpi_type == Const.BLOCKING_PERCENT:
return self.calculate_block_percent
elif kpi_type == Const.BLOCK_ORIENTATION:
return self.calculate_block_orientation
elif kpi_type == Const.MULTI_BLOCK:
return self.calculate_multi_block
elif kpi_type == Const.MAX_BLOCK_ADJ:
return self.calculate_max_block_adj
elif kpi_type == Const.INTEGRATED:
return self.calculate_integrated_core
elif kpi_type == Const.BLOCKED_TOGETHER:
return self.calculate_block_together
elif kpi_type == Const.SERIAL:
return self.calculate_serial_adj
elif kpi_type == Const.SEQUENCE:
return self.calculate_sequence
elif kpi_type == Const.RELATIVE_POSTION:
return self.calculate_sequence
elif kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.SAME_AISLE:
return self.calculate_same_aisle
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
def make_result_values_dict(self):
query = "SELECT * FROM static.kpi_result_value;"
return pd.read_sql_query(query, self.ps_data_provider.rds_conn.db).set_index('value')['pk'].to_dict()
def write_to_db(self, kpi_name, score=0, result=None, target=None, numerator_result=0,
denominator_result=None, numerator_id=999, denominator_id=999, failed=0):
"""
writes result in the DB
:param kpi_name: str
:param score: float
:param display_text: str
:param result: str
:param threshold: int
"""
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name)
self.common.write_to_db_result(fk=kpi_fk, score=score, result=result, target=target,
numerator_result=numerator_result, denominator_result=denominator_result,
numerator_id=numerator_id, denominator_id=denominator_id)
