class LIBERTYToolBox:
def __init__(self, data_provider, output, common_db):
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.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.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.store_info = self.ps_data_provider.get_ps_store_info(self.data_provider[Data.STORE_INFO])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(Const.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db = common_db
self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider,
common=self.common_db)
self.manufacturer_fk = Const.MANUFACTURER_FK
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.retailer = self.store_info['retailer_name'].iloc[0]
self.branch = self.store_info['branch_name'].iloc[0]
self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0]
self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0]
self.body_armor_delivered = self.get_body_armor_delivery_status()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
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.
"""
red_score = 0
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7)
if relevant_store_types and self.additional_attribute_7 not in relevant_store_types:
continue
result = self.calculate_main_kpi(main_line)
if result:
red_score += main_line[Const.WEIGHT]
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT)
self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score,
identifier_result=Const.RED_SCORE_PARENT, should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE)
if excluded_scene_types:
relevant_scif = relevant_scif[~relevant_scif['template_name'].isin(excluded_scene_types)]
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if template_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
return result
def calculate_kpi_by_type(self, main_line, relevant_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param relevant_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
kpi_line = relevant_template[relevant_template[Const.KPI_NAME] == main_line[Const.KPI_NAME]].iloc[0]
kpi_function = self.get_kpi_function(kpi_type)
weight = main_line[Const.WEIGHT]
if relevant_scif.empty:
result = 0
else:
result = kpi_function(kpi_line, relevant_scif, weight)
result_type_fk = self.ps_data_provider.get_pks_of_result(
Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL)
kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT,
identifier_result=kpi_name, should_enter=True)
return result
# SOS functions
def calculate_sos(self, kpi_line, relevant_scif, weight):
market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET)
if market_share_required:
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
if not market_share_target:
market_share_target = 0
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
number_of_facings = relevant_scif['facings'].sum()
result = 1 if number_of_facings > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_facings, target=market_share_target,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Availability functions
def calculate_availability(self, kpi_line, relevant_scif, weight):
survey_question_skus_required = self.does_exist(kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED)
if survey_question_skus_required:
survey_question_skus = self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME])
unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(survey_question_skus)]['product_fk'].unique().tolist()
else:
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
relevant_scif = relevant_scif[relevant_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)]
unique_skus = relevant_scif['product_fk'].unique().tolist()
length_of_unique_skus = len(unique_skus)
minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS]
result = 1 if length_of_unique_skus >= minimum_number_of_skus else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=length_of_unique_skus, target=minimum_number_of_skus,
identifier_parent=parent_kpi_name, should_enter=True)
return result
def get_relevant_product_assortment_by_kpi_name(self, kpi_name):
template = self.templates[Const.SURVEY_QUESTION_SKUS]
relevant_template = template[template[Const.KPI_NAME] == kpi_name]
relevant_ean_codes = relevant_template[Const.EAN_CODE].unique().tolist()
relevant_ean_codes = [str(int(x)) for x in relevant_ean_codes if x != ''] # we need this to fix dumb template
relevant_products = self.all_products[self.all_products['product_ean_code'].isin(relevant_ean_codes)]
return relevant_products['product_fk'].unique().tolist()
# Count of Display functions
def calculate_count_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM)
if size_subpackages:
# convert all pairings of size and number of subpackages to tuples
size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['size'],
filtered_scif['number_of_sub_packages'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM)
if sub_packages:
if sub_packages == [Const.NOT_NULL]:
filtered_scif = filtered_scif[~filtered_scif['number_of_sub_packages'].isnull()]
else:
filtered_scif = filtered_scif[filtered_scif['number_of_sub_packages'].isin([int(i) for i in sub_packages])]
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif)
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
identifier_parent=parent_kpi_name, should_enter=True)
return 1 if number_of_passing_displays > 0 else 0
else:
return 0
# Share of Display functions
def calculate_share_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target(ssd_still=ssd_still)
else:
market_share_target = 0
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif)
result = 1 if number_of_passing_displays > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays, target=market_share_target,
identifier_parent=parent_kpi_name, should_enter=True)
return result
else:
return 0
def get_number_of_passing_displays(self, filtered_scif):
if filtered_scif.empty:
return 0
filtered_scif['passed_displays'] = \
filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1)
return filtered_scif['passed_displays'].sum()
def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets
template = self.templates[Const.MINIMUM_FACINGS]
package_category = (row['size'], row['number_of_sub_packages'], row['size_unit'])
relevant_template = template[pd.Series(zip(template['size'],
template['subpackages_num'],
template['unit_of_measure'])) == package_category]
minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min()
return 1 if row['facings'] > minimum_facings else 0
# Survey functions
def calculate_survey(self, kpi_line, relevant_scif, weight):
return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0
# helper functions
def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets
template = self.templates[Const.MARKET_SHARE]
relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) &
(template[Const.RETAILER] == self.retailer) &
(template[Const.BRANCH] == self.branch)]
if relevant_template.empty:
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return 49
elif ssd_still[0].lower() == Const.STILL.lower():
return 16
else:
return 0
else:
return 26
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return relevant_template[Const.SSD].iloc[0]
elif ssd_still[0].lower() == Const.STILL.lower():
return relevant_template[Const.STILL].iloc[0]
# total 26, ssd only 49, still only 16
return relevant_template[Const.SSD_AND_STILL].iloc[0]
def get_body_armor_delivery_status(self):
if self.store_info['additional_attribute_8'].iloc[0] == 'Y':
return True
else:
return False
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.SOS:
return self.calculate_sos
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.COUNT_OF_DISPLAY:
return self.calculate_count_of_display
elif kpi_type == Const.SHARE_OF_DISPLAY:
return self.calculate_share_of_display
elif kpi_type == Const.SURVEY:
return self.calculate_survey
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return [x.strip() for x in cell.split(",")]
return None
class LIBERTYToolBox:
def __init__(self, data_provider, output, common_db):
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.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.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.store_info = self.ps_data_provider.get_ps_store_info(
self.data_provider[Data.STORE_INFO])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.result_values = self.ps_data_provider.get_result_values()
self.templates = self.read_templates()
self.common_db = common_db
self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider,
common=self.common_db)
self.manufacturer_fk = Const.MANUFACTURER_FK
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.retailer = self.store_info['retailer_name'].iloc[0]
self.branch = self.store_info['branch_name'].iloc[0]
self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0]
self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0]
self.body_armor_delivered = self.get_body_armor_delivery_status()
self.convert_base_size_and_multi_pack()
def read_templates(self):
templates = {}
for sheet in Const.SHEETS:
converters = None
if sheet == Const.MINIMUM_FACINGS:
converters = {Const.BASE_SIZE_MIN: self.convert_base_size_values,
Const.BASE_SIZE_MAX: self.convert_base_size_values}
templates[sheet] = \
pd.read_excel(Const.TEMPLATE_PATH, sheet_name=sheet,
converters=converters).fillna('')
return templates
# main functions:
def main_calculation(self, *args, **kwargs):
"""
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.
"""
if self.region != 'Liberty':
return
red_score = 0
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7)
if relevant_store_types and self.additional_attribute_7 not in relevant_store_types:
continue
result = self.calculate_main_kpi(main_line)
if result:
red_score += main_line[Const.WEIGHT] * result
if len(self.common_db.kpi_results) > 0:
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT)
self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score,
identifier_result=Const.RED_SCORE_PARENT, should_enter=True)
return
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
relevant_scif = self.scif
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE)
if excluded_scene_types:
relevant_scif = relevant_scif[~relevant_scif['template_name'].isin(
excluded_scene_types)]
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if template_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)]
result = self.calculate_kpi_by_type(main_line, relevant_scif)
return result
def calculate_kpi_by_type(self, main_line, relevant_scif):
"""
the function calculates all the kpis
:param main_line: one kpi line from the main template
:param relevant_scif:
:return: boolean, but it can be None if we want not to write it in DB
"""
kpi_type = main_line[Const.KPI_TYPE]
relevant_template = self.templates[kpi_type]
kpi_line = relevant_template[relevant_template[Const.KPI_NAME]
== main_line[Const.KPI_NAME]].iloc[0]
kpi_function = self.get_kpi_function(kpi_type)
weight = main_line[Const.WEIGHT]
if relevant_scif.empty:
result = 0
else:
result = kpi_function(kpi_line, relevant_scif, weight)
result_type_fk = self.ps_data_provider.get_pks_of_result(
Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL)
if self.does_exist(main_line, Const.PARENT_KPI_NAME):
# if this is a child KPI, we do not need to return a value to the Total Score KPI
return 0
else: # normal behavior for when this isn't a child KPI
kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT,
identifier_result=kpi_name, should_enter=True)
return result
# SOS functions
def calculate_sos(self, kpi_line, relevant_scif, weight):
market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET)
if market_share_required:
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
if not market_share_target:
market_share_target = 0
denominator_facings = relevant_scif['facings'].sum()
filtered_scif = relevant_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
numerator_facings = filtered_scif['facings'].sum()
sos_value = numerator_facings / float(denominator_facings)
result = 1 if sos_value > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_facings,
denominator_id=self.store_id, denominator_result=denominator_facings,
weight=weight, score=result * weight,
result=sos_value * 100, target=market_share_target * 100,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Availability functions
def calculate_availability(self, kpi_line, relevant_scif, weight):
survey_question_skus_required = self.does_exist(
kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED)
if survey_question_skus_required:
survey_question_skus, secondary_survey_question_skus = \
self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME])
unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(
survey_question_skus)]['product_fk'].unique().tolist()
if secondary_survey_question_skus:
secondary_unique_skus = \
relevant_scif[relevant_scif['product_fk'].isin(secondary_survey_question_skus)][
'product_fk'].unique().tolist()
else:
secondary_unique_skus = None
else:
secondary_unique_skus = None
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
relevant_scif = relevant_scif[relevant_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)]
excluded_sku = self.does_exist(kpi_line, Const.EXCLUDED_SKU)
if excluded_sku:
relevant_scif = relevant_scif[~relevant_scif['product_name'].isin(excluded_sku)]
unique_skus = relevant_scif['product_fk'].unique().tolist()
length_of_unique_skus = len(unique_skus)
minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS]
if length_of_unique_skus >= minimum_number_of_skus:
if secondary_unique_skus:
length_of_unique_skus = len(secondary_unique_skus)
minimum_number_of_skus = kpi_line[Const.SECONDARY_MINIMUM_NUMBER_OF_SKUS]
result = 1 if length_of_unique_skus > minimum_number_of_skus else 0
else:
result = 1
else:
result = 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=length_of_unique_skus, target=minimum_number_of_skus,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
def get_relevant_product_assortment_by_kpi_name(self, kpi_name):
template = self.templates[Const.SURVEY_QUESTION_SKUS]
relevant_template = template[template[Const.KPI_NAME] == kpi_name]
# we need this to fix dumb template
relevant_template[Const.EAN_CODE] = \
relevant_template[Const.EAN_CODE].apply(lambda x: str(int(x)) if x != '' else None)
primary_ean_codes = \
relevant_template[relevant_template[Const.SECONDARY_GROUP]
!= 'Y'][Const.EAN_CODE].unique().tolist()
primary_ean_codes = [code for code in primary_ean_codes if code is not None]
primary_products = self.all_products[self.all_products['product_ean_code'].isin(
primary_ean_codes)]
primary_product_pks = primary_products['product_fk'].unique().tolist()
secondary_ean_codes = \
relevant_template[relevant_template[Const.SECONDARY_GROUP]
== 'Y'][Const.EAN_CODE].unique().tolist()
if secondary_ean_codes:
secondary_products = self.all_products[self.all_products['product_ean_code'].isin(
secondary_ean_codes)]
secondary_product_pks = secondary_products['product_fk'].unique().tolist()
else:
secondary_product_pks = None
return primary_product_pks, secondary_product_pks
# Count of Display functions
def calculate_count_of_display(self, kpi_line, relevant_scif, weight):
filtered_scif = relevant_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
brand = self.does_exist(kpi_line, Const.BRAND)
if brand:
filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)]
category = self.does_exist(kpi_line, Const.CATEGORY)
if category:
filtered_scif = filtered_scif[filtered_scif['category'].isin(category)]
excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND)
if excluded_brand:
filtered_scif = filtered_scif[~filtered_scif['brand_name'].isin(excluded_brand)]
excluded_category = self.does_exist(kpi_line, Const.EXCLUDED_CATEGORY)
if excluded_category:
filtered_scif = filtered_scif[~filtered_scif['category'].isin(excluded_category)]
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)]
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM)
if size_subpackages:
# convert all pairings of size and number of subpackages to tuples
# size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in
size_subpackages]
filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['Base Size'],
filtered_scif['Multi-Pack Size'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
excluded_size_subpackages = self.does_exist(kpi_line, Const.EXCLUDED_SIZE_SUBPACKAGES_NUM)
if excluded_size_subpackages:
# convert all pairings of size and number of subpackages to tuples
# size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages]
size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in
excluded_size_subpackages]
filtered_scif = filtered_scif[~pd.Series(list(zip(filtered_scif['Base Size'],
filtered_scif['Multi-Pack Size'])),
index=filtered_scif.index).isin(size_subpackages_tuples)]
sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM)
if sub_packages:
if sub_packages == [Const.NOT_NULL]:
filtered_scif = filtered_scif[~filtered_scif['Multi-Pack Size'].isnull()]
elif sub_packages == [Const.GREATER_THAN_ONE]:
filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'] > 1]
else:
filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'].isin(
[int(i) for i in sub_packages])]
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
number_of_passing_displays, _ = self.get_number_of_passing_displays(filtered_scif)
if self.does_exist(kpi_line, Const.PARENT_KPI_NAME):
parent_kpi_name = kpi_line[Const.PARENT_KPI_NAME] + Const.LIBERTY + Const.DRILLDOWN
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(
kpi_line[Const.KPI_NAME] + Const.LIBERTY)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
score=number_of_passing_displays,
identifier_parent=parent_kpi_name, should_enter=True)
return 0
else:
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
identifier_result = parent_kpi_name + Const.DRILLDOWN
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id, denominator_result=0, weight=weight,
result=number_of_passing_displays,
score=number_of_passing_displays * weight,
identifier_parent=parent_kpi_name,
identifier_result=identifier_result, should_enter=True)
return number_of_passing_displays
else:
return 0
# Share of Display functions
def calculate_share_of_display(self, kpi_line, relevant_scif, weight):
base_scif = relevant_scif.copy()
ssd_still = self.does_exist(kpi_line, Const.ATT4)
if ssd_still:
ssd_still_scif = base_scif[base_scif['att4'].isin(ssd_still)]
else:
ssd_still_scif = base_scif
denominator_passing_displays, _ = \
self.get_number_of_passing_displays(ssd_still_scif)
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = ssd_still_scif[ssd_still_scif['manufacturer_name'].isin(manufacturer)]
else:
filtered_scif = ssd_still_scif
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = ssd_still_scif[ssd_still_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target(ssd_still=ssd_still)
else:
market_share_target = 0
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False)
if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED):
numerator_passing_displays, _ = \
self.get_number_of_passing_displays(filtered_scif)
if denominator_passing_displays != 0:
share_of_displays = \
numerator_passing_displays / float(denominator_passing_displays)
else:
share_of_displays = 0
result = 1 if share_of_displays > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk,
numerator_result=numerator_passing_displays,
denominator_id=self.store_id,
denominator_result=denominator_passing_displays, weight=weight,
result=share_of_displays * 100, target=market_share_target * 100,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
else:
return 0
def get_number_of_passing_displays(self, filtered_scif):
if filtered_scif.empty:
return 0, 0
filtered_scif = \
filtered_scif.groupby(['Base Size', 'Multi-Pack Size', 'scene_id'],
as_index=False)['facings'].sum()
filtered_scif['passed_displays'] = \
filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1)
number_of_displays = filtered_scif['passed_displays'].sum()
facings_of_displays = filtered_scif[filtered_scif['passed_displays'] == 1]['facings'].sum()
return number_of_displays, facings_of_displays
def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets
template = self.templates[Const.MINIMUM_FACINGS]
relevant_template = template[(template[Const.BASE_SIZE_MIN] <= row['Base Size']) &
(template[Const.BASE_SIZE_MAX] >= row['Base Size']) &
(template[Const.MULTI_PACK_SIZE] == row['Multi-Pack Size'])]
if relevant_template.empty:
return 0
minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min()
return 1 if row['facings'] >= minimum_facings else 0
# Share of Cooler functions
def calculate_share_of_coolers(self, kpi_line, relevant_scif, weight):
scene_ids = relevant_scif['scene_id'].unique().tolist()
total_coolers = len(scene_ids)
if total_coolers == 0:
return 0
passing_coolers = 0
if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET):
market_share_target = self.get_market_share_target()
else:
market_share_target = 0
for scene_id in scene_ids:
cooler_scif = relevant_scif[relevant_scif['scene_id'] == scene_id]
filtered_scif = cooler_scif.copy()
manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER)
if manufacturer:
filtered_scif = cooler_scif[cooler_scif['manufacturer_name'].isin(manufacturer)]
liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER)
if liberty_truck:
liberty_truck_scif = cooler_scif[cooler_scif[Const.LIBERTY_KEY_MANUFACTURER].isin(
liberty_truck)]
filtered_scif = filtered_scif.append(
liberty_truck_scif, sort=False).drop_duplicates()
if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered:
body_armor_scif = cooler_scif[cooler_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK]
filtered_scif = filtered_scif.append(body_armor_scif, sort=False).drop_duplicates()
coke_facings_threshold = self.does_exist(kpi_line, Const.COKE_FACINGS_THRESHOLD)
cooler_sos = filtered_scif['facings'].sum() / cooler_scif['facings'].sum()
cooler_result = 1 if cooler_sos >= coke_facings_threshold else 0
passing_coolers += cooler_result
coke_market_share = passing_coolers / float(total_coolers)
result = 1 if coke_market_share > market_share_target else 0
parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY
kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN)
self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk,
numerator_result=passing_coolers,
denominator_id=self.store_id,
denominator_result=total_coolers, weight=weight,
result=coke_market_share * 100, target=market_share_target * 100,
score=result * weight,
identifier_parent=parent_kpi_name, should_enter=True)
return result
# Survey functions
def calculate_survey(self, kpi_line, relevant_scif, weight):
return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0
# helper functions
def convert_base_size_and_multi_pack(self):
self.scif.loc[:, 'Base Size'] = self.scif['Base Size'].apply(self.convert_base_size_values)
self.scif.loc[:, 'Multi-Pack Size'] = \
self.scif['Multi-Pack Size'].apply(lambda x: int(x) if x is not None else None)
@staticmethod
def convert_base_size_values(value):
try:
new_value = float(value.split()[0]) if value not in [None, ''] else None
except IndexError:
Log.error('Could not convert base size value for {}'.format(value))
new_value = None
return new_value
def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets
template = self.templates[Const.MARKET_SHARE]
relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) &
(template[Const.RETAILER] == self.retailer) &
(template[Const.BRANCH] == self.branch)]
if relevant_template.empty:
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return 49
elif ssd_still[0].lower() == Const.STILL.lower():
return 16
else:
return 0
else:
return 26
if ssd_still:
if ssd_still[0].lower() == Const.SSD.lower():
return relevant_template[Const.SSD].iloc[0]
elif ssd_still[0].lower() == Const.STILL.lower():
return relevant_template[Const.STILL].iloc[0]
# total 26, ssd only 49, still only 16
return relevant_template[Const.SSD_AND_STILL].iloc[0]
def get_body_armor_delivery_status(self):
if self.store_info['additional_attribute_8'].iloc[0] == 'Y':
return True
else:
return False
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.SOS:
return self.calculate_sos
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.COUNT_OF_DISPLAY:
return self.calculate_count_of_display
elif kpi_type == Const.SHARE_OF_DISPLAY:
return self.calculate_share_of_display
elif kpi_type == Const.SHARE_OF_COOLERS:
return self.calculate_share_of_coolers
elif kpi_type == Const.SURVEY:
return self.calculate_survey
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float, np.float64]:
return [cell]
elif type(cell) in [unicode, str]:
return [x.strip() for x in cell.split(",")]
return None
class GSKSGToolBox:
KPI_DICT = {
"planogram": "planogram",
"secondary_display": "secondary_display",
"promo": "promo"
}
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.manufacturer_fk = None if self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0] is None else \
int(self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0])
self.set_up_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'gsk_set_up.xlsx'),
sheet_name='Functional KPIs',
keep_default_na=False)
self.gsk_generator = GSKGenerator(self.data_provider, self.output,
self.common, self.set_up_template)
self.targets = self.ps_data_provider.get_kpi_external_targets()
self.sequence = Sequence(self.data_provider)
self.set_up_data = {
('planogram', Const.KPI_TYPE_COLUMN): Const.NO_INFO,
('secondary_display', Const.KPI_TYPE_COLUMN): Const.NO_INFO,
('promo', Const.KPI_TYPE_COLUMN): Const.NO_INFO
}
def main_calculation(self):
"""
This function calculates the KPI results.
"""
# # global kpis in store_level
assortment_store_dict = self.gsk_generator.availability_store_function(
)
self.common.save_json_to_new_tables(assortment_store_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_whole_store_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
# global kpis in category level & kpi results are used for orange score kpi
assortment_category_dict = self.gsk_generator.availability_category_function(
)
self.common.save_json_to_new_tables(assortment_category_dict)
fsos_category_dict = self.gsk_generator.gsk_global_facings_sos_by_category_function(
)
self.common.save_json_to_new_tables(fsos_category_dict)
# updating the set up dictionary for all local kpis
for kpi in self.KPI_DICT.keys():
self.gsk_generator.tool_box.extract_data_set_up_file(
kpi, self.set_up_data, self.KPI_DICT)
orange_score_dict = self.orange_score_category(
assortment_category_dict, fsos_category_dict)
self.common.save_json_to_new_tables(orange_score_dict)
self.common.commit_results_data()
score = 0
return score
def msl_compliance_score(self, category, categories_results_json,
cat_targets, parent_result_identifier):
results_list = []
msl_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.MSL_ORANGE_SCORE)
msl_categories = self._filter_targets_by_kpi(cat_targets, msl_kpi_fk)
if category not in categories_results_json:
dst_result = 0
else:
dst_result = categories_results_json[category]
weight = msl_categories['msl_weight'].iloc[0]
score = dst_result * weight
result = score / weight
results_list.append({
'fk': msl_kpi_fk,
'numerator_id': category,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': result,
'result': result,
'target': weight,
'score': score,
'identifier_parent': parent_result_identifier,
'should_enter': True
})
return score, results_list
def fsos_compliance_score(self, category, categories_results_json,
cat_targets, parent_result_identifier):
"""
This function return json of keys- categories and values - kpi result for category
:param cat_targets-targets df for the specific category
:param category: pk of category
:param categories_results_json: type of the desired kpi
:return category json : number-category_fk,number-result
"""
results_list = []
fsos_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.FSOS_ORANGE_SCORE)
category_targets = self._filter_targets_by_kpi(cat_targets,
fsos_kpi_fk)
dst_result = categories_results_json[
category] if category in categories_results_json.keys() else 0
benchmark = category_targets['fsos_benchmark'].iloc[0]
weight = category_targets['fsos_weight'].iloc[0]
score = weight if dst_result >= benchmark else 0
result = score / weight
results_list.append({
'fk': fsos_kpi_fk,
'numerator_id': category,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': result,
'result': result,
'target': weight,
'score': score,
'identifier_parent': parent_result_identifier,
'should_enter': True
})
return score, results_list
def extract_json_results_by_kpi(self, general_kpi_results, kpi_type):
"""
This function return json of keys and values. keys= categories & values = kpi result for category
:param general_kpi_results: list of json's , each json is a db result
:param kpi_type: type of the desired kpi
:return category json : number-category_fk,number-result
"""
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type)
if general_kpi_results is None:
return {}
categories_results_json = self.extract_json_results(
kpi_fk, general_kpi_results)
return categories_results_json
@staticmethod
def extract_json_results(kpi_fk, general_kpi_results):
"""
This function created json of keys- categories and values - kpi result for category
:param kpi_fk: pk of the kpi you want to extract results from.
:param general_kpi_results: list of json's , each json is the db results
:return category json : number-category_fk,number-result
"""
category_json = {}
for row in general_kpi_results:
if row['fk'] == kpi_fk:
category_json[row[
DB.SessionResultsConsts.DENOMINATOR_ID]] = row[
DB.SessionResultsConsts.RESULT]
return category_json
def store_target(self):
"""
This function filters the external targets df , to the only df with policy that answer current session's store
attributes.
It search which store attributes defined the targets policy.
In addition it gives the targets flexibility to send "changed variables" , external targets need to save
store param+_key and store_val + _value , than this function search the store param to look for and which value
it need to have for this policy.
"""
target_columns = self.targets.columns
store_att = ['store_name', 'store_number', 'att']
store_columns = [
col for col in target_columns
if len([att for att in store_att if att in col]) > 0
]
for col in store_columns:
if self.targets.empty:
return
if 'key' in col:
value = col.replace('_key', '') + '_value'
if value not in store_columns:
continue
self.target_test(col, value)
store_columns.remove(value)
else:
if 'value' in col:
continue
self.target_test(col)
def target_test(self, store_param, store_param_val=None):
"""
:param store_param: string , store attribute . by this attribute will compare between targets policy and
current session
:param store_param_val: string , if not None the store attribute value the policy have
This function filters the targets to the only targets with a attributes that answer the current session's
store attributes
"""
store_param_val = store_param_val if store_param_val is not None else store_param
store_param = [
store_param
] if store_param_val is None else self.targets[store_param].unique()
for param in store_param:
if param is None:
continue
if self.store_info[param][0] is None:
if self.targets.empty:
return
else:
self.targets.drop(self.targets.index, inplace=True)
self.targets['target_match'] = self.targets[store_param_val].apply(
self.checking_param, store_info_col=param)
self.targets = self.targets[self.targets['target_match']]
def checking_param(self, df_param, store_info_col):
# x is self.targets[store_param_val]
if isinstance(df_param, list):
if self.store_info[store_info_col][0].encode(
GlobalConsts.HelperConsts.UTF8) in df_param:
return True
if isinstance(df_param, unicode):
if self.store_info[store_info_col][0].encode(
GlobalConsts.HelperConsts.UTF8
) == df_param or df_param == '':
return True
if isinstance(df_param, type(None)):
return True
return False
def display_distribution(self, display_name, category_fk, category_targets,
parent_identifier, kpi_name, parent_kpi_name,
scif_df):
"""
This Function sum facings of posm that it name contains substring (decided by external_targets )
if sum facings is equal/bigger than benchmark that gets weight.
:param display_name display name (in external targets this key contains relevant substrings)
:param category_fk
:param category_targets-targets df for the specific category
:param parent_identifier - result identifier for this kpi parent
:param kpi_name - kpi name
:param parent_kpi_name - this parent kpi name
:param scif_df - scif filtered by promo activation settings
"""
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name +
Consts.COMPLIANCE_KPI)
results_list = []
identifier_result = self.common.get_dictionary(category_fk=category_fk,
kpi_fk=kpi_fk)
weight = category_targets['{}_weight'.format(parent_kpi_name)].iloc[0]
if scif_df is None:
results_list.append({
'fk': kpi_fk,
'numerator_id': category_fk,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': 0,
'result': 0,
'score': 0,
'identifier_parent': parent_identifier,
'identifier_result': identifier_result,
'target': weight,
'should_enter': True
})
return 0, results_list
display_products = scif_df[(scif_df['product_type'] == 'POS')
& (scif_df['category_fk'] == category_fk)]
display_name = "{}_name".format(display_name.lower())
display_names = category_targets[display_name].iloc[0]
kpi_result = 0
# check's if display names (received from external targets) are string or array of strings
if isinstance(display_names, str) or isinstance(
display_names, unicode):
display_array = []
if len(display_names) > 0:
display_array.append(display_names)
display_names = display_array
# for each display name , search POSM that contains display name (= sub string)
for display in display_names:
current_display_prod = display_products[
display_products['product_name'].str.contains(display)]
display_sku_level = self.display_sku_results(
current_display_prod, category_fk, kpi_name)
kpi_result += current_display_prod['facings'].sum()
results_list.extend(display_sku_level)
benchmark = category_targets['{}_benchmark'.format(
parent_kpi_name)].iloc[0]
kpi_score = weight if kpi_result >= benchmark else 0
results_list.append({
'fk': kpi_fk,
'numerator_id': category_fk,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': kpi_score,
'result': kpi_score,
'score': kpi_score,
'identifier_parent': parent_identifier,
'identifier_result': identifier_result,
'target': weight,
'should_enter': True
})
return kpi_score, results_list
def display_sku_results(self, display_data, category_fk, kpi_name):
"""
This Function create for each posm in display data db result with score of posm facings.
:param category_fk
:param display_data-targets df for the specific category
:param kpi_name - kpi name
"""
results_list = []
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name +
Consts.SKU_LEVEL_LIST)
parent_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_name + Consts.COMPLIANCE_KPI)
identifier_parent = self.common.get_dictionary(category_fk=category_fk,
kpi_fk=parent_kpi_fk)
display_names = display_data['item_id'].unique()
for display in display_names:
count = float(display_data[display_data['item_id'] == display]
['facings'].sum()) / float(100)
results_list.append({
'fk': kpi_fk,
'numerator_id': display,
'denominator_id': category_fk,
'denominator_result': 1,
'numerator_result': count,
'result': count,
'score': count,
'identifier_parent': identifier_parent,
'should_enter': True
})
return results_list
def assortment(self):
"""
This Function get relevant assortment based on filtered scif
"""
lvl3_assort, filter_scif = self.gsk_generator.tool_box.get_assortment_filtered(
self.set_up_data, "planogram")
return lvl3_assort, filter_scif
def msl_assortment(self, kpi_name):
"""
:param kpi_name : name of level 3 assortment kpi
:return kpi_results : data frame of assortment products of the kpi, product's availability,
product details.(reduce assortment products that are not available)
filtered by set up
"""
lvl3_assort, filtered_scif = self.assortment()
if lvl3_assort is None or lvl3_assort.empty:
return None
kpi_assortment_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name)
kpi_results = lvl3_assort[lvl3_assort['kpi_fk_lvl3'] ==
kpi_assortment_fk] # general assortment
kpi_results = pd.merge(kpi_results,
self.all_products[Const.PRODUCTS_COLUMNS],
how='left',
on='product_fk')
# only distributed products
kpi_results = kpi_results[kpi_results['in_store'] == 1]
# filtering substitied products
kpi_results = kpi_results[
kpi_results['substitution_product_fk'].isnull()]
shelf_data = pd.merge(self.match_product_in_scene[[
'scene_fk', 'product_fk', 'shelf_number'
]],
filtered_scif[['scene_id', 'product_fk']],
how='right',
left_on=['scene_fk', 'product_fk'],
right_on=['scene_id', 'product_fk'
]) # why is this happening?
# merge assortment results with match_product_in_scene for shelf_number parameter
kpi_results = pd.merge(shelf_data,
kpi_results,
how='right',
on=['product_fk']) # also problematic
return kpi_results
def shelf_compliance(self, category, assortment_df, cat_targets,
identifier_parent):
"""
This function calculate how many assortment products available on specific shelves
:param category
:param cat_targets : targets df for the specific category
:param assortment_df :relevant assortment based on filtered scif
:param identifier_parent - result identifier for shelf compliance kpi parent .
"""
results_list = []
kpi_fk = self.common.get_kpi_fk_by_kpi_type(Consts.SHELF_COMPLIANCE)
category_targets = self._filter_targets_by_kpi(cat_targets, kpi_fk)
if assortment_df is not None:
assortment_cat = assortment_df[assortment_df['category_fk'] ==
category]
shelf_weight = category_targets['shelf_weight'].iloc[0]
benchmark = category_targets['shelf_benchmark'].iloc[0]
shelves = [
int(shelf) for shelf in
category_targets['shelf_number'].iloc[0].split(",")
]
shelf_df = assortment_cat[assortment_cat['shelf_number'].isin(
shelves)]
numerator = len(shelf_df['product_fk'].unique())
denominator = len(assortment_cat['product_fk'].unique())
result = float(numerator) / float(
denominator) if numerator and denominator != 0 else 0
score = shelf_weight if result >= benchmark else 0
else:
denominator, numerator, score, shelf_weight = 0, 0, 0, 0
result = float(numerator) / float(
denominator) if numerator and denominator != 0 else 0
shelf_weight = category_targets['shelf_weight'].iloc[0]
results_list.append({
'fk': kpi_fk,
'numerator_id': category,
'denominator_id': self.store_id,
'denominator_result': denominator,
'numerator_result': numerator,
'result': result,
'target': shelf_weight,
'score': score,
'identifier_parent': identifier_parent,
'should_enter': True
})
return score, results_list, shelf_weight
def planogram(self, category_fk, assortment, category_targets,
identifier_parent):
"""
This function sum sequence kpi and shelf compliance
:param category_fk
:param category_targets : targets df for the specific category
:param assortment :relevant assortment based on filtered scif
:param identifier_parent : result identifier for planogram kpi parent .
"""
results_list = []
kpi_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_CATEGORY)
identifier_result = self.common.get_dictionary(category_fk=category_fk,
kpi_fk=kpi_fk)
shelf_compliance_score, shelf_compliance_result, shelf_weight = self.shelf_compliance(
category_fk, assortment, category_targets, identifier_result)
results_list.extend(shelf_compliance_result)
sequence_kpi, sequence_weight = self._calculate_sequence(
category_fk, identifier_result)
planogram_score = shelf_compliance_score + sequence_kpi
planogram_weight = shelf_weight + sequence_weight
planogram_result = planogram_score / float(
planogram_weight) if planogram_weight else 0
results_list.append({
'fk': kpi_fk,
'numerator_id': category_fk,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': planogram_score,
'result': planogram_result,
'target': planogram_weight,
'score': planogram_score,
'identifier_parent': identifier_parent,
'identifier_result': identifier_result,
'should_enter': True
})
return planogram_score, results_list
def _calculate_sequence(self, cat_fk, planogram_identifier):
"""
This method calculated the sequence KPIs using the external targets' data and sequence calculation algorithm.
"""
sequence_kpi_fk, sequence_sku_kpi_fk = self._get_sequence_kpi_fks()
sequence_targets = self._filter_targets_by_kpi(self.targets,
sequence_kpi_fk)
sequence_targets = sequence_targets.loc[sequence_targets.category_fk ==
cat_fk]
passed_sequences_score, total_sequence_weight = 0, 0
for i, sequence in sequence_targets.iterrows():
population, location, sequence_attributes = self._prepare_data_for_sequence_calculation(
sequence)
sequence_result = self.sequence.calculate_sequence(
population, location, sequence_attributes)
score = self._save_sequence_results_to_db(sequence_sku_kpi_fk,
sequence_kpi_fk,
sequence,
sequence_result)
passed_sequences_score += score
total_sequence_weight += sequence[SessionResultsConsts.WEIGHT]
self._save_sequence_main_level_to_db(sequence_kpi_fk,
planogram_identifier, cat_fk,
passed_sequences_score,
total_sequence_weight)
return passed_sequences_score, total_sequence_weight
@staticmethod
def _prepare_data_for_sequence_calculation(sequence_params):
"""
This method gets the relevant targets per sequence and returns the sequence params for calculation.
"""
population = {
ProductsConsts.PRODUCT_FK:
sequence_params[ProductsConsts.PRODUCT_FK]
}
location = {
TemplatesConsts.TEMPLATE_GROUP:
sequence_params[TemplatesConsts.TEMPLATE_GROUP]
}
additional_attributes = {
AdditionalAttr.STRICT_MODE: sequence_params['strict_mode'],
AdditionalAttr.INCLUDE_STACKING:
sequence_params['include_stacking'],
AdditionalAttr.CHECK_ALL_SEQUENCES: True
}
return population, location, additional_attributes
def _extract_target_params(self, sequence_params):
"""
This method extract the relevant category_fk and result value from the sequence parameters.
"""
numerator_id = sequence_params[ProductsConsts.CATEGORY_FK]
result_value = self.ps_data_provider.get_pks_of_result(
sequence_params['sequence_name'])
return numerator_id, result_value
def _save_sequence_main_level_to_db(self, kpi_fk, planogram_identifier,
cat_fk, sequence_score, total_weight):
"""
This method saves the top sequence level to DB.
"""
result = round(
(sequence_score / float(total_weight)), 2) if total_weight else 0
score = result * total_weight
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=cat_fk,
numerator_result=sequence_score,
result=result,
denominator_id=self.store_id,
denominator_result=total_weight,
score=score,
weight=total_weight,
target=total_weight,
should_enter=True,
identifier_result=kpi_fk,
identifier_parent=planogram_identifier)
def _save_sequence_results_to_db(self, kpi_fk, parent_kpi_fk,
sequence_params, sequence_results):
"""
This method handles the saving of the SKU level sequence KPI.
:param kpi_fk: Sequence SKU kpi fk.
:param parent_kpi_fk: Total sequence score kpi fk.
:param sequence_params: A dictionary with sequence params for the external targets.
:param sequence_results: A DataFrame with the results that were received by the sequence algorithm.
:return: The score that was saved (0 or 100 * weight).
"""
category_fk, result_value = self._extract_target_params(
sequence_params)
num_of_sequences = len(sequence_results)
target, weight = sequence_params[
SessionResultsConsts.TARGET], sequence_params[
SessionResultsConsts.WEIGHT]
score = weight if len(sequence_results) >= target else 0
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=category_fk,
numerator_result=num_of_sequences,
result=result_value,
denominator_id=self.store_id,
denominator_result=None,
score=score,
weight=weight,
parent_fk=parent_kpi_fk,
target=target,
should_enter=True,
identifier_parent=parent_kpi_fk,
identifier_result=(kpi_fk, category_fk))
return score
def _get_sequence_kpi_fks(self):
"""This method fetches the relevant sequence kpi fks"""
sequence_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.SEQUENCE_KPI)
sequence_sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.SEQUENCE_SKU_KPI)
return sequence_kpi_fk, sequence_sku_kpi_fk
def secondary_display(self, category_fk, cat_targets, identifier_parent,
scif_df):
"""
This function calculate secondary score - 0 or full weight if at least
one of it's child kpis equal to weight.
:param category_fk
:param cat_targets : targets df for the specific category
:param identifier_parent : result identifier for promo activation kpi parent .
:param scif_df : scif filtered by promo activation settings
"""
results_list = []
parent_kpi_name = 'display'
total_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.DISPLAY_SUMMARY)
category_targets = self._filter_targets_by_kpi(cat_targets,
total_kpi_fk)
weight = category_targets['display_weight'].iloc[0]
result_identifier = self.common.get_dictionary(category_fk=category_fk,
kpi_fk=total_kpi_fk)
dispenser_score, dispenser_res = self.display_distribution(
Consts.DISPENSER_TARGET, category_fk, category_targets,
result_identifier, Consts.DISPENSERS, parent_kpi_name, scif_df)
counter_top_score, counter_top_res = self.display_distribution(
Consts.COUNTER_TOP_TARGET, category_fk, category_targets,
result_identifier, Consts.COUNTERTOP, parent_kpi_name, scif_df)
standee_score, standee_res = self.display_distribution(
Consts.STANDEE_TARGET, category_fk, category_targets,
result_identifier, Consts.STANDEE, parent_kpi_name, scif_df)
results_list.extend(dispenser_res)
results_list.extend(counter_top_res)
results_list.extend(standee_res)
display_score = weight if (dispenser_score == weight) or (
counter_top_score == weight) or (standee_score == weight) else 0
results_list.append({
'fk': total_kpi_fk,
'numerator_id': category_fk,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': display_score,
'result': display_score,
'target': weight,
'score': display_score,
'identifier_parent': identifier_parent,
'identifier_result': result_identifier,
'should_enter': True
})
return results_list, display_score
def promo_activation(self, category_fk, cat_targets, identifier_parent,
scif_df):
"""
This function calculate promo activation score - 0 or full weight if at least
one of it's child kpis equal to weight.
:param category_fk
:param cat_targets : targets df for the specific category
:param identifier_parent : result identifier for promo activation kpi parent .
:param scif_df : scif filtered by promo activation settings
"""
total_kpi_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PROMO_SUMMARY)
category_targets = self._filter_targets_by_kpi(cat_targets,
total_kpi_fk)
result_identifier = self.common.get_dictionary(category_fk=category_fk,
kpi_fk=total_kpi_fk)
results_list = []
parent_kpi_name = 'promo'
weight = category_targets['promo_weight'].iloc[0]
hang_shell_score, hang_shell_res = self.display_distribution(
Consts.HANGSELL, category_fk, category_targets, result_identifier,
Consts.HANGSELL_KPI, parent_kpi_name, scif_df)
top_shelf_score, top_shelf_res = self.display_distribution(
Consts.TOP_SHELF, category_fk, category_targets, result_identifier,
Consts.TOP_SHELF_KPI, parent_kpi_name, scif_df)
results_list.extend(hang_shell_res)
results_list.extend(top_shelf_res)
promo_score = weight if (hang_shell_score == weight) or (
top_shelf_score == weight) else 0
results_list.append({
'fk': total_kpi_fk,
'numerator_id': category_fk,
'denominator_id': self.store_id,
'denominator_result': 1,
'numerator_result': promo_score,
'result': promo_score,
'target': weight,
'score': promo_score,
'identifier_parent': identifier_parent,
'identifier_result': result_identifier,
'should_enter': True
})
return results_list, promo_score
@staticmethod
def _filter_targets_by_kpi(targets, kpi_fk):
""" This function filter all targets but targets which related to relevant kpi"""
filtered_targets = targets.loc[targets.kpi_fk == kpi_fk]
return filtered_targets
def orange_score_category(self, assortment_category_res,
fsos_category_res):
"""
This function calculate orange score kpi by category. Settings are based on external targets and set up file.
:param assortment_category_res : array of assortment results
:param fsos_category_res : array of facing sos by store results
"""
results_list = []
self.store_target()
if self.targets.empty:
return
total_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.ORANGE_SCORE_COMPLIANCE)
fsos_json_global_results = self.extract_json_results_by_kpi(
fsos_category_res, Consts.GLOBAL_FSOS_BY_CATEGORY)
msl_json_global_results = self.extract_json_results_by_kpi(
assortment_category_res, Consts.GLOBAL_DST_BY_CATEGORY)
# scif after filtering it by set up file for each kpi
scif_secondary = self.gsk_generator.tool_box.tests_by_template(
'secondary_display', self.scif, self.set_up_data)
scif_promo = self.gsk_generator.tool_box.tests_by_template(
'promo', self.scif, self.set_up_data)
categories = self.targets[
DataProviderConsts.ProductsConsts.CATEGORY_FK].unique()
assortment = self.msl_assortment('Distribution - SKU')
for cat in categories:
orange_score_result_identifier = self.common.get_dictionary(
category_fk=cat, kpi_fk=total_kpi_fk)
cat_targets = self.targets[self.targets[
DataProviderConsts.ProductsConsts.CATEGORY_FK] == cat]
msl_score, msl_results = self.msl_compliance_score(
cat, msl_json_global_results, cat_targets,
orange_score_result_identifier)
fsos_score, fsos_results = self.fsos_compliance_score(
cat, fsos_json_global_results, cat_targets,
orange_score_result_identifier)
planogram_score, planogram_results = self.planogram(
cat, assortment, cat_targets, orange_score_result_identifier)
secondary_display_res, secondary_score = self.secondary_display(
cat, cat_targets, orange_score_result_identifier,
scif_secondary)
promo_activation_res, promo_score = self.promo_activation(
cat, cat_targets, orange_score_result_identifier, scif_promo)
compliance_category_score = promo_score + secondary_score + fsos_score + msl_score + planogram_score
results_list.extend(msl_results + fsos_results +
planogram_results + secondary_display_res +
promo_activation_res)
results_list.append({
'fk':
total_kpi_fk,
'numerator_id':
self.manufacturer_fk,
'denominator_id':
cat,
'denominator_result':
1,
'numerator_result':
compliance_category_score,
'result':
compliance_category_score,
'score':
compliance_category_score,
'identifier_result':
orange_score_result_identifier
})
return results_list
