class SceneToolBox:
def __init__(self, data_provider, common):
self.data_provider = data_provider
self.common = common
self.project_name = self.data_provider.project_name
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.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.kpi_results_queries = []
self.tools = GENERALToolBox(data_provider)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_facings_per_sku(
location_type_1=True,
kpi_name=FACINGS_PER_SKU_SCENE_LOCATION_1_KPI)
self.calculate_facings_per_sku(
location_type_1=False,
kpi_name=FACINGS_PER_SKU_SCENE_LOCATION_OTHER_KPI)
def calculate_facings_per_sku(self, location_type_1, kpi_name):
if location_type_1:
result_df = self.count_facings_by_scenes(
self.scif, {
'location_type_fk': (1, self.tools.INCLUDE_FILTER),
"product_type": ["SKU", "Other"]
})[['product_fk', 'facings']]
else:
result_df = self.count_facings_by_scenes(
self.scif, {
'location_type_fk': (1, self.tools.EXCLUDE_FILTER),
"product_type": ["SKU", "Other"]
})[['product_fk', 'facings']]
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name=kpi_name)
for index, row in result_df.iterrows():
result = row['facings']
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=row['product_fk'],
result=result,
by_scene=True,
denominator_id=self.store_id)
def count_facings_by_scenes(self, df, filters):
facing_data = df[self.tools.get_filter_condition(df, **filters)]
# filter by scene_id and by template_name (scene type)
# scene_types_groupby = facing_data.groupby(['scene_id'])['facings'].sum().reset_index()
return facing_data
class PEPSICORUSANDToolBox:
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.pepsico_fk = self.get_relevant_pk_by_name(Const.MANUFACTURER,
Const.PEPSICO)
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.categories_to_calculate = self.get_relevant_categories_for_session(
)
self.toolbox = GENERALToolBox(data_provider)
self.main_shelves = [
scene_type
for scene_type in self.scif[Const.TEMPLATE_NAME].unique().tolist()
if Const.MAIN_SHELF in scene_type
]
def get_main_shelf_by_category(self, current_category):
"""
This function gets a category and return the relevant scene type for the SOS
:param current_category: One of the product's categories. E.g: Snacks.
:return: The relevant scene type to the current category
"""
for main_shelf in self.main_shelves:
if current_category in main_shelf.upper():
return main_shelf
@staticmethod
def get_category_from_template_name(template_name):
"""
This function gets a template name (scene_type) and return it's relevant category.
:param template_name: The scene type.
:return: category name
"""
if Const.SNACKS in template_name:
return Const.SNACKS
elif Const.BEVERAGES in template_name:
return Const.BEVERAGES
elif Const.JUICES in template_name:
return Const.JUICES
else:
Log.warning(
"Couldn't find a matching category for template name = {}".
format(template_name))
return None
def get_scene_type_by_sub_cat(self, sub_cat):
"""
This function gets a sub_category and return the relevant scene type for the SOS
:param sub_cat: One of the product's sub_categories. E.g: TODO todo todo.
:return: The relevant scene type to the current sub_category
"""
current_category = self.scif.loc[self.scif[Const.SUB_CATEGORY] ==
sub_cat][Const.CATEGORY].values[0]
return self.get_main_shelf_by_category(current_category)
def get_relevant_categories_for_session(self):
"""
This function returns a list of the relevant categories according to the scene_types in the session
:return: List of the relevant categories
"""
relevant_categories = set()
scene_types = self.scif[Const.TEMPLATE_NAME].unique().tolist()
for scene_type in scene_types:
if Const.SNACKS in scene_type.upper():
relevant_categories.add(Const.SNACKS)
elif Const.BEVERAGES in scene_type.upper():
relevant_categories.add(Const.BEVERAGES)
else:
relevant_categories.add(Const.JUICES)
return list(relevant_categories)
def get_relevant_sub_categories_for_session(self):
"""
This function returns a list of the relevant categories according to the scene_types in the session
:return: List of the relevant categories
"""
sub_categories = self.scif[Const.SUB_CATEGORY].unique().tolist()
if None in sub_categories:
sub_categories.remove(None)
for sub_cat in sub_categories:
relevant_category = self.get_unique_attribute_from_filters(
Const.SUB_CATEGORY, sub_cat, Const.CATEGORY)
if not relevant_category:
sub_categories.remove(sub_cat)
return sub_categories
def get_relevant_attributes_for_sos(self, attribute):
""" # todo todo todo todo todo ?
This function returns a list of the relevant attributes according to the scene_types in the session.
Firstly we filter by main shelf and than check all of the possible attributes.
:param attribute: The attribute you would like to get. E.g: brand_name, category etc.
:return: List of the relevant categories
"""
filtered_scif = self.scif[self.scif[Const.TEMPLATE_NAME].isin(
self.main_shelves)]
list_of_attribute = filtered_scif[attribute].unique().tolist()
for attr in list_of_attribute:
if filtered_scif[filtered_scif[attribute] == attr].empty:
list_of_attribute.remove(attr)
return list_of_attribute
def get_relevant_pk_by_name(self, filter_by, filter_param):
"""
This function gets a filter name and returns the relevant pk.
If the filter_by is equal to category it will be the field name because in SCIF there isn't category_name
:param filter_by: filter by name E.g: 'category', 'brand'.
:param filter_param: The param to filter by. E.g: if filter_by = 'category', filter_param could be 'Snack'
:return: The relevant pk
"""
pk_field = filter_by + Const.FK
field_name = filter_by + Const.NAME if Const.CATEGORY not in filter_by else filter_by
return self.scif.loc[self.scif[field_name] ==
filter_param][pk_field].values[0]
def get_unique_attribute_from_filters(self, filter_by, param,
attribute_to_get):
"""
This function gets an attribute name and a parameter and return the attribute the user wants to get.
For example: The function can get filter_by=sub_category_name and param=snacks and
attribute_to_get=sub_category_fk and it will return 41 (SNACK sub_cat fk).
:param filter_by: The relevant attribute E.g: brand_name, sub_category_fk
:param param: The parameter that fits the attribute: E.g snacks, adrenaline.
:param attribute_to_get: Which attribute to return! E.g: brand_fk, category_fk etc.
:return: The category fk that match the filter params.
"""
unique_attr = self.products[self.products[filter_by] ==
param][attribute_to_get].unique()
if len(unique_attr) > 1:
Log.warning("Several {} match to the following {}: {}".format(
attribute_to_get, filter_by, param))
return unique_attr[0]
@log_runtime('Share of shelf pepsicoRU')
def calculate_share_of_shelf(self):
"""
The function filters only the relevant scene (type = Main Shelf in category) and calculates the linear SOS and
the facing SOS for each level (Manufacturer, Category, Sub-Category, Brand).
The identifier for every kpi will be the current kpi_fk and the relevant attribute according to the level
E.g sub_category_fk for level 3 or brand_fk for level 4.
:return:
"""
# Level 1
filter_manu_param = {Const.MANUFACTURER_NAME: Const.PEPSICO}
general_filters = {Const.TEMPLATE_NAME: self.main_shelves}
# Calculate Facings SOS
numerator_score, denominator_score, result = self.calculate_facings_sos(
sos_filters=filter_manu_param, **general_filters)
facings_stores_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_MANUFACTURER_SOS)
facings_level_1_identifier = self.common.get_dictionary(
kpi_fk=facings_stores_kpi_fk)
self.common.write_to_db_result(
fk=facings_stores_kpi_fk,
numerator_id=self.pepsico_fk,
identifier_result=facings_level_1_identifier,
numerator_result=numerator_score,
denominator_id=self.store_id,
denominator_result=denominator_score,
result=result,
score=result)
# Calculate Linear SOS
linear_store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_MANUFACTURER_SOS)
linear_level_1_identifier = self.common.get_dictionary(
kpi_fk=linear_store_kpi_fk)
numerator_score, denominator_score, result = self.calculate_linear_sos(
sos_filters=filter_manu_param, **general_filters)
self.common.write_to_db_result(
fk=linear_store_kpi_fk,
numerator_id=self.pepsico_fk,
identifier_result=linear_level_1_identifier,
numerator_result=numerator_score,
denominator_id=self.store_id,
denominator_result=denominator_score,
result=result,
score=result)
# Level 2
for category in self.categories_to_calculate:
filter_params = {
Const.CATEGORY: category,
Const.TEMPLATE_NAME: self.get_main_shelf_by_category(category)
}
current_category_fk = self.get_relevant_pk_by_name(
Const.CATEGORY, category)
# Calculate Facings SOS
facings_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_CATEGORY_SOS)
numerator_score, denominator_score, result = self.calculate_facings_sos(
sos_filters=filter_manu_param, **filter_params)
level_2_facings_cat_identifier = self.common.get_dictionary(
kpi_fk=facings_cat_kpi_fk, category_fk=current_category_fk)
self.common.write_to_db_result(
fk=facings_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=numerator_score,
denominator_id=current_category_fk,
denominator_result=denominator_score,
identifier_result=level_2_facings_cat_identifier,
identifier_parent=facings_level_1_identifier,
result=result,
score=result)
# Calculate Linear SOS
linear_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_CATEGORY_SOS)
numerator_score, denominator_score, result = self.calculate_linear_sos(
sos_filters=filter_manu_param, **filter_params)
level_2_linear_cat_identifier = self.common.get_dictionary(
kpi_fk=linear_cat_kpi_fk, category_fk=current_category_fk)
self.common.write_to_db_result(
fk=linear_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=numerator_score,
denominator_id=current_category_fk,
denominator_result=denominator_score,
identifier_result=level_2_linear_cat_identifier,
identifier_parent=linear_level_1_identifier,
result=result,
score=result)
# Level 3
for sub_cat in self.get_relevant_sub_categories_for_session():
curr_category_fk = self.get_unique_attribute_from_filters(
Const.SUB_CATEGORY, sub_cat, Const.CATEGORY_FK)
current_sub_category_fk = self.get_relevant_pk_by_name(
Const.SUB_CATEGORY, sub_cat)
filter_sub_cat_param = {
Const.SUB_CATEGORY: sub_cat,
Const.TEMPLATE_NAME: self.get_scene_type_by_sub_cat(sub_cat)
}
# Calculate Facings SOS
facings_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_SUB_CATEGORY_SOS)
numerator_score, denominator_score, result = self.calculate_facings_sos(
sos_filters=filter_manu_param, **filter_sub_cat_param)
level_3_facings_sub_cat_identifier = self.common.get_dictionary(
kpi_fk=facings_sub_cat_kpi_fk,
sub_category_fk=current_sub_category_fk)
parent_identifier = self.common.get_dictionary(
kpi_fk=self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_CATEGORY_SOS),
category_fk=curr_category_fk)
self.common.write_to_db_result(
fk=facings_sub_cat_kpi_fk,
numerator_id=current_sub_category_fk,
numerator_result=numerator_score,
denominator_id=current_sub_category_fk,
identifier_result=level_3_facings_sub_cat_identifier,
identifier_parent=parent_identifier,
denominator_result=denominator_score,
result=result,
score=result)
# Calculate Linear SOS
numerator_score, denominator_score, result = self.calculate_linear_sos(
sos_filters=filter_manu_param, **filter_sub_cat_param)
linear_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_SUB_CATEGORY_SOS)
level_3_linear_sub_cat_identifier = self.common.get_dictionary(
kpi_fk=linear_sub_cat_kpi_fk,
sub_category_fk=current_sub_category_fk)
parent_identifier = self.common.get_dictionary(
kpi_fk=self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_CATEGORY_SOS),
category_fk=curr_category_fk)
self.common.write_to_db_result(
fk=linear_sub_cat_kpi_fk,
numerator_id=current_sub_category_fk,
numerator_result=numerator_score,
denominator_id=current_sub_category_fk,
identifier_result=level_3_linear_sub_cat_identifier,
identifier_parent=parent_identifier,
denominator_result=denominator_score,
result=result,
score=result)
# Level 4
brands_list = self.scif[
(self.scif[Const.MANUFACTURER_NAME] == Const.PEPSICO)
& self.scif['template_name'].isin(self.main_shelves)][
Const.BRAND_NAME].unique().tolist()
if None in brands_list:
brands_list.remove(None)
for brand in brands_list:
relevant_category = self.get_unique_attribute_from_filters(
Const.BRAND_NAME, brand, Const.CATEGORY)
relevant_sub_cat_fk = self.get_unique_attribute_from_filters(
Const.BRAND_NAME, brand, Const.SUB_CATEGORY_FK)
filter_brand_param = {
Const.BRAND_NAME: brand,
Const.MANUFACTURER_NAME: Const.PEPSICO
}
general_filters = {
Const.CATEGORY:
relevant_category,
Const.TEMPLATE_NAME:
self.get_main_shelf_by_category(relevant_category)
}
current_brand_fk = self.get_relevant_pk_by_name(Const.BRAND, brand)
# Calculate Facings SOS
facings_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_BRAND_SOS)
numerator_score, denominator_score, result = self.calculate_facings_sos(
sos_filters=filter_brand_param, **general_filters)
level_4_facings_brand_identifier = self.common.get_dictionary(
kpi_fk=facings_brand_kpi_fk, brand_fk=current_brand_fk)
parent_identifier = self.common.get_dictionary(
kpi_fk=self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_SUB_CATEGORY_SOS),
sub_cat=relevant_sub_cat_fk)
self.common.write_to_db_result(
fk=facings_brand_kpi_fk,
numerator_id=current_brand_fk,
numerator_result=numerator_score,
denominator_id=relevant_sub_cat_fk,
identifier_result=level_4_facings_brand_identifier,
identifier_parent=parent_identifier,
denominator_result=denominator_score,
result=result,
score=result)
# Calculate Linear SOS
linear_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_BRAND_SOS)
numerator_score, denominator_score, result = self.calculate_facings_sos(
filter_brand_param, **general_filters)
level_4_linear_brand_identifier = self.common.get_dictionary(
kpi_fk=linear_brand_kpi_fk, brand_fk=current_brand_fk)
parent_identifier = self.common.get_dictionary(
kpi_fk=self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_SUB_CATEGORY_SOS),
sub_cat=relevant_sub_cat_fk)
self.common.write_to_db_result(
fk=linear_brand_kpi_fk,
numerator_id=current_brand_fk,
numerator_result=numerator_score,
denominator_id=relevant_sub_cat_fk,
identifier_result=level_4_linear_brand_identifier,
identifier_parent=parent_identifier,
denominator_result=denominator_score,
result=result,
score=result)
return
def calculate_count_of_display(self):
"""
This function will calculate the Count of # of Pepsi Displays KPI
:return:
"""
# TODO: TARGETS TARGETS TARGETS
# Filtering out the main shelves
filtered_scif = self.scif.loc[~self.scif[Const.TEMPLATE_NAME].
isin(self.main_shelves)]
if filtered_scif.empty:
return
# Calculate count of display - store_level
display_count_store_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_STORE_LEVEL)
scene_types_in_store = len(filtered_scif[Const.SCENE_FK].unique())
self.common.write_to_db_result(
fk=display_count_store_level_fk,
numerator_id=self.store_id,
numerator_result=scene_types_in_store,
identifier_result=display_count_store_level_fk,
result=scene_types_in_store,
score=0)
# Calculate count of display - category_level
display_count_category_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_CATEGORY_LEVEL)
for category in self.categories_to_calculate:
category_fk = self.get_relevant_pk_by_name(Const.CATEGORY,
category)
relevant_scenes = [
scene_type for scene_type in filtered_scif[
Const.TEMPLATE_NAME].unique().tolist()
if category in scene_type.upper()
]
filtered_scif_by_cat = filtered_scif.loc[filtered_scif[
Const.TEMPLATE_NAME].isin(relevant_scenes)]
if filtered_scif_by_cat.empty:
continue
scene_types_in_category = len(
filtered_scif_by_cat[Const.SCENE_FK].unique())
display_count_category_level_identifier = self.common.get_dictionary(
kpi_fk=display_count_category_level_fk, category=category)
self.common.write_to_db_result(
fk=display_count_category_level_fk,
numerator_id=category_fk,
numerator_result=scene_types_in_category,
identifier_result=display_count_category_level_identifier,
identifier_parent=display_count_category_level_fk,
result=scene_types_in_category,
score=scene_types_in_category)
# Calculate count of display - scene_level
display_count_scene_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_SCENE_LEVEL)
for scene_type in filtered_scif[Const.TEMPLATE_NAME].unique().tolist():
relevant_category = self.get_category_from_template_name(
scene_type)
scene_type_score = len(filtered_scif[filtered_scif[
Const.TEMPLATE_NAME] == scene_type][Const.SCENE_FK].unique())
scene_type_fk = self.get_relevant_pk_by_name(
Const.TEMPLATE, scene_type)
display_count_scene_level_identifier = self.common.get_dictionary(
kpi_fk=display_count_category_level_fk,
category=relevant_category)
parent_identifier = self.common.get_dictionary(
kpi_fk=display_count_category_level_fk,
category=relevant_category)
self.common.write_to_db_result(
fk=display_count_scene_level_fk,
numerator_id=scene_type_fk,
numerator_result=scene_type_score,
identifier_result=display_count_scene_level_identifier,
identifier_parent=parent_identifier,
result=scene_type_score,
score=0)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_share_of_shelf()
self.calculate_count_of_display()
Assortment(self.data_provider, self.output,
common=self.common).main_assortment_calculation()
###################################### Plaster ######################################
def calculate_share_space_length(self, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:return: The total shelf width (in mm) the relevant facings occupy.
"""
filtered_matches = \
self.scif[self.toolbox.get_filter_condition(self.scif, **filters)]
space_length = filtered_matches['net_len_add_stack'].sum()
return space_length
def calculate_linear_sos(self,
sos_filters,
include_empty=Const.EXCLUDE_EMPTY,
**general_filters):
"""
:param sos_filters: These are the parameters on which ths SOS is calculated (out of the general DF).
:param include_empty: This dictates whether Empty-typed SKUs are included in the calculation.
:param general_filters: These are the parameters which the general data frame is filtered by.
:return: The numerator, denominator and ratio score.
"""
if include_empty == Const.EXCLUDE_EMPTY:
general_filters['product_type'] = (Const.EMPTY,
Const.EXCLUDE_FILTER)
numerator_width = self.calculate_share_space_length(
**dict(sos_filters, **general_filters))
denominator_width = self.calculate_share_space_length(
**general_filters)
if denominator_width == 0:
return 0, 0, 0
else:
return numerator_width / 1000, denominator_width / 1000, (
numerator_width / float(denominator_width))
def calculate_share_facings(self, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:return: The total number of the relevant facings occupy.
"""
filtered_scif = self.scif[self.toolbox.get_filter_condition(
self.scif, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
return sum_of_facings
def calculate_facings_sos(self,
sos_filters,
include_empty=Const.EXCLUDE_EMPTY,
**general_filters):
"""
:param sos_filters: These are the parameters on which ths SOS is calculated (out of the general DF).
:param include_empty: This dictates whether Empty-typed SKUs are included in the calculation.
:param general_filters: These are the parameters which the general data frame is filtered by.
:return: The numerator, denominator and ratio score.
"""
if include_empty == Const.EXCLUDE_EMPTY:
general_filters['product_type'] = (Const.EMPTY,
Const.EXCLUDE_FILTER)
numerator_counter = self.calculate_share_facings(
**dict(sos_filters, **general_filters))
denominator_counter = self.calculate_share_facings(**general_filters)
if denominator_counter == 0:
return 0, 0, 0
else:
return numerator_counter, denominator_counter, (
numerator_counter / float(denominator_counter))
class PEPSICORUToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common_v1 = CommonV1(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.visit_type = self.store_info[
Const.ADDITIONAL_ATTRIBUTE_2].values[0]
self.all_templates = self.data_provider[Data.ALL_TEMPLATES]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif.loc[~(self.scif[Const.PRODUCT_TYPE]
== Const.IRRELEVANT)] # Vitaly's request
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.toolbox = GENERALToolBox(data_provider)
self.assortment = Assortment(self.data_provider,
self.output,
common=self.common_v1)
if not self.scif.empty:
self.pepsico_fk = self.get_relevant_pk_by_name(
Const.MANUFACTURER, Const.PEPSICO)
self.categories_to_calculate = self.get_relevant_categories_for_session(
)
self.main_shelves = self.get_main_shelves()
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_share_of_shelf()
self.calculate_count_of_displays()
self.calculate_assortment()
def get_main_shelves(self):
"""
This function returns a list with the main shelves of this session
"""
main_shelves_template_groups = [
group
for group in self.scif[Const.TEMPLATE_GROUP].unique().tolist()
if Const.MAIN_SHELF in group.upper()
]
main_shelves = self.scif[self.scif[Const.TEMPLATE_GROUP].isin(
main_shelves_template_groups)][
Const.TEMPLATE_NAME].unique().tolist()
return main_shelves
def get_main_shelf_by_category(self, current_category):
"""
This function gets a category and return the relevant scene type for the SOS
:param current_category: One of the product's categories. E.g: Snacks.
:return: The relevant scene type to the current category
"""
main_shelves_for_category = []
for main_shelf in self.main_shelves:
if current_category.upper() in main_shelf.upper():
main_shelves_for_category.append(main_shelf)
return main_shelves_for_category
@staticmethod
def get_category_from_template_name(template_name):
"""
This function gets a template name (scene_type) and return it's relevant category.
:param template_name: The scene type.
:return: category name
"""
if Const.SNACKS.upper() in template_name.upper():
return Const.SNACKS
elif Const.BEVERAGES.upper() in template_name.upper():
return Const.BEVERAGES
elif Const.JUICES.upper() in template_name.upper():
return Const.JUICES
else:
Log.warning(
"Couldn't find a matching category for template name = {}".
format(template_name))
return None
def get_relevant_categories_for_session(self):
"""
This function returns a list of the relevant categories according to the store type.
The parameter additional_attribute_2 defines the visit type for each store.
We have 3 types: Visit LRB (Beverages and Juices), Visit Snack and Visit (= All of them).
The function is doing intersection between the categories in SCIF and the categories by store type.
:return: List of the relevant categories
"""
categories_in_scif = self.scif[Const.CATEGORY].unique().tolist()
if None in categories_in_scif:
categories_in_scif.remove(None)
if not categories_in_scif:
Log.warning("No categories at scene item facts!")
return []
store_type = self.store_info[Const.ADDITIONAL_ATTRIBUTE_2].values[0]
if not store_type:
Log.warning(
"Invalid additional_attribute_2 for store id = {}".format(
self.store_id))
return []
if Const.SNACKS.upper() in store_type.upper():
relevant_categories = [Const.SNACKS]
elif Const.LRB.upper() in store_type.upper():
relevant_categories = [Const.JUICES, Const.BEVERAGES]
else:
relevant_categories = [Const.SNACKS, Const.JUICES, Const.BEVERAGES]
categories_for_session = list(
set(relevant_categories).intersection(set(categories_in_scif)))
if not categories_for_session:
Log.warning(
"There aren't matching categories in scif for this store.")
return categories_for_session
def get_relevant_sub_categories_for_category(self, category):
"""
This function returns a list of the relevant categories according to the scene_types in the session
:param category: The relevant category
:return: List of the relevant sub categories for this category
"""
filtered_scif = self.scif.loc[
(self.scif[Const.CATEGORY] == category)
& (self.scif[Const.MANUFACTURER_NAME] == Const.PEPSICO) &
(self.scif[Const.TEMPLATE_NAME].isin(self.main_shelves))]
sub_categories = filtered_scif[Const.SUB_CATEGORY].unique().tolist()
if None in sub_categories:
sub_categories.remove(None)
if not sub_categories:
Log.warning("No relevant sub categories for category = {}".format(
category))
return sub_categories
def get_relevant_brands_for_sub_category(self, sub_category):
"""
This function returns a list of the relevant categories according to the scene_types in the session
:param sub_category: The relevant sub category
:return: List of the relevant brands for this category
"""
filtered_scif = self.scif.loc[
(self.scif[Const.SUB_CATEGORY] == sub_category)
& (self.scif[Const.MANUFACTURER_NAME] == Const.PEPSICO) &
(self.scif[Const.TEMPLATE_NAME].isin(self.main_shelves))]
brands_list = filtered_scif[Const.BRAND_NAME].unique().tolist()
if None in brands_list:
brands_list.remove(None)
if not brands_list:
Log.warning("No relevant brands for sub category = {}".format(
sub_category))
return brands_list
def get_relevant_pk_by_name(self, filter_by, filter_param):
"""
This function gets a filter name and returns the relevant pk.
If the filter_by is equal to category it will be the field name because in SCIF there isn't category_name
:param filter_by: filter by name E.g: 'category', 'brand'.
:param filter_param: The param to filter by. E.g: if filter_by = 'category', filter_param could be 'Snack'
:return: The relevant pk
"""
pk_field = filter_by + Const.FK
field_name = filter_by + Const.NAME if Const.CATEGORY not in filter_by else filter_by
return self.scif.loc[self.scif[field_name] ==
filter_param][pk_field].values[0]
def get_target_for_count_of_displays(self):
"""
This function reads the project's template and returns the targets for all of the levels.
It iterates over the relevant row and aggregate the result per level.
:return: target_by_store (int), target_by_category (dict), target_by_scene (dict).
Plus it return the scene_type list from the template
"""
targets = pd.read_excel(TEMPLATE_PATH).fillna(0)
store_number_1 = self.store_info['store_number_1'].values[0]
if not store_number_1:
Log.warning("No valid store number 1 for store_fk = {}".format(
self.store_id))
return
targets = targets.loc[targets[Const.STORE_NUMBER_1] == store_number_1]
has_targets = False
store_target, category_targets, scene_targets, scene_types_from_template = None, None, None, None
if not targets.empty:
has_targets = True
targets = targets.drop([Const.STORE_NAME, Const.STORE_NUMBER_1],
axis=1)
target_row = (targets.iloc[0])[(
targets.iloc[0]) > 0] # Takes only the ones with target > 0
scene_types_from_template = targets.columns.tolist()
scene_types_with_targets = target_row.keys().tolist()
# Targets by store:
store_target = target_row.sum()
# Targets by category and scenes:
category_targets = {key: 0 for key in self.categories_to_calculate}
scene_targets = {key: 0 for key in scene_types_from_template}
for scene_type in scene_types_with_targets:
for category in category_targets:
if category.upper() in scene_type.upper():
# category_targets[category] += 1
category_targets[category] += target_row[scene_type]
scene_targets[scene_type] = target_row[scene_type]
return has_targets, store_target, category_targets, scene_targets, scene_types_from_template
def get_relevant_scene_types_from_list(self, scene_types_from_template):
"""
There's a gap between the actual name and the name is the template because of the visit type.
So this function returns the a dictionary the narrows it.
:param scene_types_from_template: Scene type list from the template
:return: A dictionary where the keys are the names from the templates and values are the actual names
"""
scene_types_dict = dict.fromkeys(scene_types_from_template)
relevant_templates_for_visit_type = self.all_templates.loc[(
self.all_templates[Const.ADDITIONAL_ATTRIBUTE_2] == self.visit_type
) & (~self.all_templates[Const.TEMPLATE_NAME].isin(self.main_shelves)
)][Const.TEMPLATE_NAME].unique().tolist()
for scene_type in scene_types_from_template:
for template in relevant_templates_for_visit_type:
if scene_type.upper() in template.upper():
scene_types_dict[scene_type] = template
# Remove irrelevant scene types from the dictionary
for key in scene_types_dict.keys():
if not scene_types_dict[key]:
del scene_types_dict[key]
return scene_types_dict
def calculate_count_of_displays(self):
"""
This function will calculate the Count of # of Pepsi Displays KPI
:return:
"""
# Notice! store_target is Integer, scene_type_list is a list and the rest are dictionaries
has_target, store_target, category_targets, scene_targets, scene_type_list = self.get_target_for_count_of_displays(
)
if not store_target:
Log.warning(
"No targets were defined for this store (pk = {})".format(
self.store_id))
return
# Filtering out the main shelves
if has_target:
relevant_scenes_dict = self.get_relevant_scene_types_from_list(
scene_type_list)
relevant_template_name_list = relevant_scenes_dict.values()
filtered_scif = self.scif.loc[self.scif[Const.TEMPLATE_NAME].isin(
relevant_template_name_list)]
display_count_store_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_STORE_LEVEL)
scene_types_in_store = len(filtered_scif[Const.SCENE_FK].unique())
identifier_parent_store_level = self.common.get_dictionary(
kpi_fk=display_count_store_level_fk)
count_store_level = 0
# Calculate count of display - category_level
display_count_category_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_CATEGORY_LEVEL)
for category in self.categories_to_calculate:
current_category_target = category_targets[category]
if not current_category_target:
continue
category_fk = self.get_relevant_pk_by_name(
Const.CATEGORY, category)
relevant_scenes = [
scene_type for scene_type in relevant_template_name_list
if category.upper() in scene_type.upper()
]
filtered_scif_by_cat = filtered_scif.loc[filtered_scif[
Const.TEMPLATE_NAME].isin(relevant_scenes)]
display_count_category_level_identifier = self.common.get_dictionary(
kpi_fk=display_count_category_level_fk, category=category)
# scene_types_in_cate = 0
# result_cat_level = 0
# if not filtered_scif_by_cat.empty:
# scene_types_in_cate = len(filtered_scif_by_cat[Const.SCENE_FK].unique())
# result_cat_level = 1.0 if scene_types_in_cate >= current_category_target else scene_types_in_cate / float(
# current_category_target)
# self.common.write_to_db_result(fk=display_count_category_level_fk, numerator_id=self.pepsico_fk,
# numerator_result=scene_types_in_cate,
# denominator_id=category_fk, denominator_result=current_category_target,
# identifier_result=display_count_category_level_identifier,
# identifier_parent=identifier_parent_store_level,
# result=result_cat_level, should_enter=True)
scene_count_in_cate = 0
result_cat_level = 0
if not filtered_scif_by_cat.empty:
actual_scene_names_in_cate = filtered_scif_by_cat[
Const.TEMPLATE_NAME].unique().tolist()
reverse_scene_dict = {}
for scene_type, actual_scene_name in relevant_scenes_dict.iteritems(
):
for sc in actual_scene_names_in_cate:
if actual_scene_name == sc:
reverse_scene_dict[
actual_scene_name] = scene_type
df = filtered_scif_by_cat[[
Const.TEMPLATE_NAME, 'scene_id'
]].drop_duplicates()
df['scene_type'] = df[Const.TEMPLATE_NAME].apply(
lambda x: reverse_scene_dict.get(x))
by_scene_count_in_cat = df.groupby(['scene_type']).count()
for i, row in by_scene_count_in_cat.iterrows():
scene_count_in_cate += scene_targets[i] if row[Const.TEMPLATE_NAME]>=scene_targets[i] \
else row[Const.TEMPLATE_NAME]
result_cat_level = 1.0 if scene_count_in_cate >= current_category_target else scene_count_in_cate / float(
current_category_target)
self.common.write_to_db_result(
fk=display_count_category_level_fk,
numerator_id=self.pepsico_fk,
numerator_result=scene_count_in_cate,
denominator_id=category_fk,
denominator_result=current_category_target,
identifier_result=display_count_category_level_identifier,
identifier_parent=identifier_parent_store_level,
result=result_cat_level,
should_enter=True)
# Calculate count of display - scene_level
display_count_scene_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.DISPLAY_COUNT_SCENE_LEVEL)
for scene_type in relevant_scenes_dict.keys():
scene_type_target = scene_targets[scene_type]
if not scene_type_target:
continue
actual_scene_name = relevant_scenes_dict[scene_type]
relevant_category = self.get_category_from_template_name(
actual_scene_name)
relevant_category_fk = self.get_relevant_pk_by_name(
Const.CATEGORY, relevant_category)
scene_type_score = len(
filtered_scif[filtered_scif[Const.TEMPLATE_NAME] ==
actual_scene_name][Const.SCENE_FK].unique())
result_scene_level = 1.0 if scene_type_score >= scene_type_target else scene_type_score / float(
scene_type_target)
scene_type_fk = self.all_templates.loc[self.all_templates[
Const.TEMPLATE_NAME] == actual_scene_name][
Const.TEMPLATE_FK].values[0]
parent_identifier = self.common.get_dictionary(
kpi_fk=display_count_category_level_fk,
category=relevant_category)
self.common.write_to_db_result(
fk=display_count_scene_level_fk,
numerator_id=self.pepsico_fk,
numerator_result=scene_type_score,
denominator_id=relevant_category_fk,
denominator_result=scene_type_target,
identifier_parent=parent_identifier,
context_id=scene_type_fk,
result=result_scene_level,
should_enter=True)
count_store_level += scene_type_target if scene_type_score >= scene_type_target else scene_type_score
# Calculate count of display - store_level
result_store_level = 1.0 if count_store_level >= store_target else count_store_level / float(
store_target)
self.common.write_to_db_result(
fk=display_count_store_level_fk,
numerator_id=self.pepsico_fk,
numerator_result=count_store_level,
denominator_id=self.store_id,
denominator_result=store_target,
identifier_result=identifier_parent_store_level,
result=result_store_level,
should_enter=True)
# def calculate_count_of_displays_old(self):
# """
# This function will calculate the Count of # of Pepsi Displays KPI
# :return:
# """
# # Notice! store_target is Integer, scene_type_list is a list and the rest are dictionaries
# store_target, category_targets, scene_targets, scene_type_list = self.get_target_for_count_of_displays()
# if not store_target:
# Log.warning("No targets were defined for this store (pk = {})".format(self.store_id))
# return
# # Filtering out the main shelves
# relevant_scenes_dict = self.get_relevant_scene_types_from_list(scene_type_list)
# relevant_template_name_list = relevant_scenes_dict.values()
# filtered_scif = self.scif.loc[self.scif[Const.TEMPLATE_NAME].isin(relevant_template_name_list)]
#
# # Calculate count of display - store_level
# display_count_store_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_STORE_LEVEL)
# scene_types_in_store = len(filtered_scif[Const.SCENE_FK].unique())
# result_store_level = 100 if scene_types_in_store >= store_target else scene_types_in_store / float(store_target)
# # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk,
# # numerator_result=scene_types_in_store,
# # denominator_id=self.store_id, denominator_result=store_target,
# # identifier_result=display_count_store_level_fk,
# # result=result_store_level, should_enter=True)
#
# identifier_parent_store_level = self.common.get_dictionary(kpi_fk=display_count_store_level_fk)
# self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk,
# numerator_result=scene_types_in_store,
# denominator_id=self.store_id, denominator_result=store_target,
# identifier_result=identifier_parent_store_level,
# result=result_store_level, should_enter=True)
#
# # Calculate count of display - category_level
# display_count_category_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_CATEGORY_LEVEL)
# for category in self.categories_to_calculate:
# current_category_target = category_targets[category]
# if not current_category_target:
# continue
# category_fk = self.get_relevant_pk_by_name(Const.CATEGORY, category)
# relevant_scenes = [scene_type for scene_type in relevant_template_name_list if
# category.upper() in scene_type.upper()]
# filtered_scif_by_cat = filtered_scif.loc[filtered_scif[Const.TEMPLATE_NAME].isin(relevant_scenes)]
# if filtered_scif_by_cat.empty:
# continue
# scene_types_in_cate = len(filtered_scif_by_cat[Const.SCENE_FK].unique())
# result_cat_level = 100 if scene_types_in_cate >= current_category_target else scene_types_in_cate / float(
# current_category_target)
# display_count_category_level_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk,
# category=category)
# # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk,
# # numerator_result=scene_types_in_cate,
# # denominator_id=category_fk, denominator_result=current_category_target,
# # identifier_result=display_count_category_level_identifier,
# # identifier_parent=display_count_category_level_fk,
# # result=result_cat_level, should_enter=True)
# self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk,
# numerator_result=scene_types_in_cate,
# denominator_id=category_fk, denominator_result=current_category_target,
# identifier_result=display_count_category_level_identifier,
# identifier_parent=identifier_parent_store_level,
# result=result_cat_level, should_enter=True)
#
#
# # Calculate count of display - scene_level
# display_count_scene_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_SCENE_LEVEL)
# for scene_type in relevant_scenes_dict.keys():
# scene_type_target = scene_targets[scene_type]
# if not scene_type_target:
# continue
# actual_scene_name = relevant_scenes_dict[scene_type]
# relevant_category = self.get_category_from_template_name(actual_scene_name)
# relevant_category_fk = self.get_relevant_pk_by_name(Const.CATEGORY, relevant_category)
# scene_type_score = len(
# filtered_scif[filtered_scif[Const.TEMPLATE_NAME] == actual_scene_name][Const.SCENE_FK].unique())
#
# result_scene_level = 100 if scene_type_score >= scene_type_target else scene_types_in_store / float(
# scene_type_target)
# scene_type_fk = self.all_templates.loc[self.all_templates[Const.TEMPLATE_NAME] == actual_scene_name][
# Const.TEMPLATE_FK].values[0]
# display_count_scene_level_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk,
# category=relevant_category)
# # parent_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk,
# # category=relevant_category)
# # self.common.write_to_db_result(fk=display_count_scene_level_fk, numerator_id=self.pepsico_fk,
# # numerator_result=scene_types_in_store,
# # denominator_id=relevant_category_fk, denominator_result=scene_type_target,
# # identifier_result=display_count_scene_level_identifier,
# # identifier_parent=parent_identifier, context_id=scene_type_fk,
# # result=result_scene_level, should_enter=True)
#
# self.common.write_to_db_result(fk=display_count_scene_level_fk, numerator_id=self.pepsico_fk,
# numerator_result=scene_types_in_store,
# denominator_id=relevant_category_fk, denominator_result=scene_type_target,
# identifier_result=display_count_scene_level_identifier,
# identifier_parent=identifier_parent_store_level, context_id=scene_type_fk,
# result=result_scene_level, should_enter=True)
def calculate_assortment(self):
lvl3_result = self.assortment.calculate_lvl3_assortment()
# lvl3_result = self.get_lvl3_assortment_result_main_shelf()
self.category_assortment_calculation(lvl3_result)
self.store_assortment_calculation(lvl3_result)
def get_lvl3_assortment_result_main_shelf(self):
assortment_result = self.assortment.get_lvl3_relevant_ass()
if not self.main_shelves and not assortment_result.empty:
assortment_result.drop(assortment_result.index[0:], inplace=True)
if assortment_result.empty:
return assortment_result
filters = {Const.TEMPLATE_NAME: self.main_shelves}
filtered_scif = self.scif[self.toolbox.get_filter_condition(
self.scif, **filters)]
products_in_session = filtered_scif.loc[
filtered_scif['facings'] > 0]['product_fk'].values
assortment_result.loc[
assortment_result['product_fk'].isin(products_in_session),
'in_store'] = 1
return assortment_result
@log_runtime('Share of shelf pepsicoRU')
def calculate_share_of_shelf(self):
"""
The function filters only the relevant scene (type = Main Shelf in category) and calculates the linear SOS and
the facing SOS for each level (Manufacturer, Category, Sub-Category, Brand).
The identifier for every kpi will be the current kpi_fk and the relevant attribute according to the level
E.g sub_category_fk for level 3 or brand_fk for level 4.
:return:
"""
# Get all of the KPI fk in advance
facings_stores_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_MANUFACTURER_SOS)
facings_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_CATEGORY_SOS)
facings_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_SUB_CATEGORY_SOS)
facings_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.FACINGS_BRAND_SOS)
linear_store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_MANUFACTURER_SOS)
linear_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_CATEGORY_SOS)
linear_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_SUB_CATEGORY_SOS)
linear_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.LINEAR_BRAND_SOS)
filter_man_param = {Const.MANUFACTURER_NAME: Const.PEPSICO}
general_filters = {Const.TEMPLATE_NAME: self.main_shelves}
facings_level_1_identifier = self.common.get_dictionary(
kpi_fk=facings_stores_kpi_fk)
linear_level_1_identifier = self.common.get_dictionary(
kpi_fk=linear_store_kpi_fk)
num_facings = denom_facings = num_linear = denom_linear = result_facings = result_linear = 0
if self.main_shelves:
num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos(
sos_filters=filter_man_param, **general_filters)
result_facings = num_facings / float(
denom_facings) if denom_facings else 0
result_linear = num_linear / float(
denom_linear) if denom_linear else 0
# Facings level 1
self.common.write_to_db_result(
fk=facings_stores_kpi_fk,
numerator_id=self.pepsico_fk,
identifier_result=facings_level_1_identifier,
numerator_result=num_facings,
denominator_id=self.store_id,
denominator_result=denom_facings,
result=result_facings,
should_enter=True)
# Linear level 1
self.common.write_to_db_result(
fk=linear_store_kpi_fk,
numerator_id=self.pepsico_fk,
identifier_result=linear_level_1_identifier,
numerator_result=num_linear * 100,
denominator_id=self.store_id,
denominator_result=denom_linear * 100,
result=result_linear,
should_enter=True)
for category in self.categories_to_calculate:
current_category_fk = self.get_relevant_pk_by_name(
Const.CATEGORY, category)
main_shelves_for_category = self.get_main_shelf_by_category(
category)
if main_shelves_for_category:
filter_params = {
Const.CATEGORY: category,
Const.TEMPLATE_NAME: main_shelves_for_category
}
facings_cat_identifier = self.common.get_dictionary(
kpi_fk=facings_cat_kpi_fk, category_fk=current_category_fk)
linear_cat_identifier = self.common.get_dictionary(
kpi_fk=linear_cat_kpi_fk, category_fk=current_category_fk)
num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos(
sos_filters=filter_man_param, **filter_params)
result_facings = num_facings / float(
denom_facings) if denom_facings else 0
result_linear = num_linear / float(
denom_linear) if denom_linear else 0
# Facings level 2
self.common.write_to_db_result(
fk=facings_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=num_facings,
denominator_id=current_category_fk,
denominator_result=denom_facings,
identifier_result=facings_cat_identifier,
identifier_parent=facings_level_1_identifier,
result=result_facings,
should_enter=True)
# Linear level 2
self.common.write_to_db_result(
fk=linear_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=num_linear * 100,
denominator_id=current_category_fk,
denominator_result=denom_linear * 100,
identifier_result=linear_cat_identifier,
identifier_parent=linear_level_1_identifier,
result=result_linear,
should_enter=True)
for sub_cat in self.get_relevant_sub_categories_for_category(
category):
current_sub_category_fk = self.get_relevant_pk_by_name(
Const.SUB_CATEGORY, sub_cat)
filter_sub_cat_param = {
Const.SUB_CATEGORY: sub_cat,
Const.CATEGORY: category,
Const.TEMPLATE_NAME: main_shelves_for_category
}
facings_sub_cat_identifier = self.common.get_dictionary(
kpi_fk=facings_sub_cat_kpi_fk,
sub_category_fk=current_sub_category_fk)
linear_sub_cat_identifier = self.common.get_dictionary(
kpi_fk=linear_sub_cat_kpi_fk,
sub_category_fk=current_sub_category_fk)
num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos(
sos_filters=filter_man_param, **filter_sub_cat_param)
if denom_facings and denom_linear:
# Facings level 3
self.common.write_to_db_result(
fk=facings_sub_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=num_facings,
denominator_id=current_sub_category_fk,
denominator_result=denom_facings,
identifier_result=facings_sub_cat_identifier,
identifier_parent=facings_cat_identifier,
result=num_facings / float(denom_facings),
should_enter=True)
# Linear level 3
self.common.write_to_db_result(
fk=linear_sub_cat_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=num_linear * 100,
denominator_id=current_sub_category_fk,
denominator_result=denom_linear * 100,
identifier_result=linear_sub_cat_identifier,
identifier_parent=linear_cat_identifier,
result=num_linear / float(denom_linear),
should_enter=True)
for brand_name in self.get_relevant_brands_for_sub_category(
sub_cat):
current_brand_fk = self.get_relevant_pk_by_name(
Const.BRAND, brand_name)
filter_sos_brand = {
Const.BRAND_NAME: brand_name,
Const.SUB_CATEGORY: sub_cat,
Const.MANUFACTURER_NAME: Const.PEPSICO
}
filter_general_brand_param = {
Const.SUB_CATEGORY: sub_cat,
Const.CATEGORY: category,
Const.TEMPLATE_NAME: main_shelves_for_category
}
facings_brand_identifier = self.common.get_dictionary(
kpi_fk=facings_brand_kpi_fk,
brand_fk=current_brand_fk)
linear_brand_identifier = self.common.get_dictionary(
kpi_fk=linear_brand_kpi_fk,
brand_fk=current_brand_fk)
num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos(
sos_filters=filter_sos_brand,
**filter_general_brand_param)
if denom_facings and denom_linear:
# Facings level 4
self.common.write_to_db_result(
fk=facings_brand_kpi_fk,
numerator_id=current_brand_fk,
numerator_result=num_facings,
denominator_id=current_sub_category_fk,
denominator_result=denom_facings,
identifier_result=facings_brand_identifier,
identifier_parent=
facings_sub_cat_identifier,
result=num_facings / float(denom_facings),
should_enter=True)
# Linear level 4
self.common.write_to_db_result(
fk=linear_brand_kpi_fk,
numerator_id=current_brand_fk,
numerator_result=num_linear * 100,
denominator_id=current_sub_category_fk,
denominator_result=denom_linear * 100,
identifier_result=linear_brand_identifier,
identifier_parent=linear_sub_cat_identifier,
result=num_linear / float(denom_linear),
should_enter=True)
# Utils functions with a slight change from the SDK factory:
def calculate_sos(self,
sos_filters,
include_empty=Const.EXCLUDE_EMPTY,
**general_filters):
"""
:param sos_filters: These are the parameters on which ths SOS is calculated (out of the general DF).
:param include_empty: This dictates whether Empty-typed SKUs are included in the calculation.
:param general_filters: These are the parameters which the general data frame is filtered by.
:return: The numerator facings, denominator facings, numerator linear and denominator linear.
"""
if include_empty == Const.EXCLUDE_EMPTY:
general_filters[Const.PRODUCT_TYPE] = (Const.EMPTY,
Const.EXCLUDE_FILTER)
numerator_facings, numerator_linear = self.calculate_share_space(
**dict(sos_filters, **general_filters))
denominator_facings, denominator_linear = self.calculate_share_space(
**general_filters)
return numerator_facings, denominator_facings, numerator_linear / 1000.0, denominator_linear / 1000.0
def calculate_share_space(self, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = self.scif[self.toolbox.get_filter_condition(
self.scif, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['net_len_ign_stack'].sum()
return sum_of_facings, space_length
def category_assortment_calculation(self, lvl3_result):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
"""
osa_product_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OSA_SKU_LEVEL)
oos_product_level_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OOS_SKU_LEVEL)
osa_category_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OSA_CATEGORY_LEVEL)
oos_category_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OOS_CATEGORY_LEVEL)
if not lvl3_result.empty:
cat_df = self.all_products[['product_fk', 'category_fk']]
lvl3_with_cat = lvl3_result.merge(cat_df,
on='product_fk',
how='left')
lvl3_with_cat = lvl3_with_cat[
lvl3_with_cat['category_fk'].notnull()]
for result in lvl3_with_cat.itertuples():
if result.in_store == 1:
score = Const.DISTRIBUTION
else:
score = Const.OOS
# Distribution
self.common_v1.write_to_db_result_new_tables(
fk=osa_product_level_fk,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
if score == Const.OOS:
# OOS
self.common_v1.write_to_db_result_new_tables(
oos_product_level_fk,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
category_fk_list = lvl3_with_cat['category_fk'].unique()
for cat in category_fk_list:
lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] ==
cat]
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result_cat)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
# Distribution
self.common_v1.write_to_db_result_new_tables(
fk=osa_category_level_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common_v1.write_to_db_result_new_tables(
fk=oos_category_level_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=denominator_res - result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=1 - res,
score=(1 - res),
score_after_actions=1 - res)
self.assortment.LVL2_HEADERS.extend(['passes', 'total'])
return
def store_assortment_calculation(self, lvl3_result):
"""
This function calculates the KPI results.
"""
dist_store_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OSA_STORE_LEVEL)
oos_store_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Const.OOS_STORE_LEVEL)
# for result in lvl3_result.itertuples():
# if result.in_store == 1:
# score = Const.DISTRIBUTION
# else:
# score = Const.OOS
# # Distribution
# self.common_v1.write_to_db_result_new_tables(fk=?????, numerator_id=result.product_fk,
# numerator_result=score,
# result=score, denominator_id=self.store_id,
# denominator_result=1, score=score)
# if score == Const.OOS:
# # OOS
# self.common_v1.write_to_db_result_new_tables(fk=?????, numerator_id=result.product_fk,
# numerator_result=score,
# result=score, denominator_id=self.store_id,
# denominator_result=1, score=score,
# score_after_actions=score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
if not pd.isnull(result.target) and not pd.isnull(
result.group_target_date
) and result.group_target_date <= self.visit_date:
denominator_res = result.target
res = np.divide(float(result.passes), float(denominator_res))
# Distribution
self.common_v1.write_to_db_result_new_tables(
fk=dist_store_level_kpi_fk,
numerator_id=self.pepsico_fk,
denominator_id=self.store_id,
numerator_result=result.passes,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common_v1.write_to_db_result_new_tables(
fk=oos_store_level_kpi_fk,
numerator_id=self.pepsico_fk,
numerator_result=denominator_res - result.passes,
denominator_id=self.store_id,
denominator_result=denominator_res,
result=1 - res,
score=1 - res,
score_after_actions=1 - res)
return
class MOLSONCOORSHR_SANDToolBox:
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.current_date = datetime.now()
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.own_manufacturer_id = int(self.data_provider[Data.OWN_MANUFACTURER][self.data_provider[Data.OWN_MANUFACTURER]['param_name'] == 'manufacturer_id']['param_value'].tolist()[0])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.toolbox = GENERALToolBox(data_provider)
self.assortment = Assortment(self.data_provider, self.output, common=self.common)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template_path = self.get_template_path()
self.template_data = self.get_template_data()
self.sos_store_policies = self.get_sos_store_policies(self.visit_date.strftime('%Y-%m-%d'))
self.result_values = self.get_result_values()
self.scores = pd.DataFrame()
def get_sos_store_policies(self, visit_date):
query = MOLSONCOORSHR_SANDQueries.get_sos_store_policies(visit_date)
store_policies = pd.read_sql_query(query, self.rds_conn.db)
return store_policies
def get_result_values(self):
query = MOLSONCOORSHR_SANDQueries.get_result_values()
result_values = pd.read_sql_query(query, self.rds_conn.db)
return result_values
def main_calculation(self):
"""
This function starts the KPI results calculation.
"""
if not self.template_data or KPIS_TEMPLATE_SHEET not in self.template_data:
Log.error('KPIs template sheet is empty or not found')
return
self.kpis_calculation()
self.common.commit_results_data()
def kpis_calculation(self, kpi_group=''):
"""
This is a recursive function.
The function calculates each level KPI cascading from the highest level to the lowest.
"""
total_score = total_potential_score = total_calculated = 0
kpis = self.template_data['KPIs'][self.template_data['KPIs']['KPI Group'] == kpi_group]
for index, kpi in kpis.iterrows():
child_kpi_group = kpi['Child KPI Group']
kpi_type = kpi['KPI Type'].lower()
score_function = kpi['Score Function'].lower()
if not child_kpi_group:
if kpi_type in [LINEAR_SOS_VS_TARGET, FACINGS_SOS_VS_TARGET]:
score, potential_score, calculated = self.calculate_sos_vs_target(kpi)
elif kpi_type in [FACINGS_VS_TARGET, DISTRIBUTION]:
score, potential_score, calculated = self.calculate_assortment_vs_target(kpi)
else:
Log.error("KPI of type '{}' is not supported".format(kpi_type))
score = potential_score = calculated = 0
else:
score, potential_score, calculated = self.kpis_calculation(child_kpi_group)
if score_function in [WEIGHTED_SCORE, SUM_OF_SCORES]:
total_score += score
total_potential_score += potential_score
total_calculated += calculated
else:
total_score += 0
total_potential_score += 0
total_calculated += 0
if child_kpi_group and calculated:
if kpi['KPI name Eng'] == 'Store Score':
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0
numerator_id = self.own_manufacturer_id
denominator_id = self.store_id
identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk)
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=0,
denominator_id=denominator_id,
denominator_result=0,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True
)
else:
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0
numerator_id = self.own_manufacturer_id
denominator_id = self.store_id
identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk)
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=0,
denominator_id=denominator_id,
denominator_result=0,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True
)
return total_score, total_potential_score, total_calculated
@kpi_runtime()
def calculate_assortment_vs_target(self, kpi):
"""
The function filters only the relevant scenes by Location Type and calculates the Assortment scores
according to rules set in the target.
:return:
"""
lvl3_result = self.calculate_assortment_vs_target_lvl3(kpi)
for row in lvl3_result.itertuples():
numerator_id = row.product_fk
numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings
denominator_id = self.store_id
denominator_result = row.target
# denominator_result_after_actions = 0 if row.target < row.facings else row.target - row.facings
if kpi['KPI Type'] == 'Distribution':
if row.result_distributed:
result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') &
(self.result_values['result_value'] == 'DISTRIBUTED')]['result_value_fk'].tolist()[0]
score = 100
else:
result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') &
(self.result_values['result_value'] == 'OOS')]['result_value_fk'].tolist()[0]
score = 0
else:
result = row.result_facings
score = round(result*100, 0)
identifier_details = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl3)
identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2)
self.common.write_to_db_result(fk=row.kpi_fk_lvl3,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
# denominator_result_after_actions=denominator_result_after_actions,
result=result,
score=score,
identifier_result=identifier_details,
identifier_parent=identifier_kpi,
should_enter=True
)
score = potential_score = 0
if not lvl3_result.empty:
lvl2_result = self.calculate_assortment_vs_target_lvl2(lvl3_result)
for row in lvl2_result.itertuples():
numerator_id = self.own_manufacturer_id
numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings
denominator_id = self.store_id
denominator_result = row.target
result = row.result_distributed if kpi['KPI Type'] == 'Distribution' else row.result_facings
score += self.score_function(result*100, kpi)
potential_score += round(float(kpi['Weight'])*100, 0)
identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2)
identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']))
self.common.write_to_db_result(fk=row.kpi_fk_lvl2,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_kpi,
identifier_parent=identifier_parent,
should_enter=True
)
if len(lvl3_result) > 0:
calculated = 1
else:
calculated = 0
return score, potential_score, calculated
def calculate_assortment_vs_target_lvl3(self, kpi):
location_types = kpi['Location Type'].split(', ')
kpi_fk_lvl3 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'] + ' - SKU')
kpi_fk_lvl2 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
assortment_result = self.assortment.get_lvl3_relevant_ass()
if assortment_result.empty:
return assortment_result
assortment_result = assortment_result[(assortment_result['kpi_fk_lvl3'] == kpi_fk_lvl3) &
(assortment_result['kpi_fk_lvl2'] == kpi_fk_lvl2)]
if assortment_result.empty:
return assortment_result
assortment_result['target'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Target'), axis=1)
assortment_result['target'] = assortment_result['target'].fillna(0)
assortment_result = assortment_result[assortment_result['target'] > 0]
assortment_result['weight'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Weight'), axis=1)
assortment_result['weight'] = assortment_result['weight'].fillna(0)
assortment_total_weights = assortment_result[['assortment_fk', 'weight']].groupby('assortment_fk').agg({'weight': 'sum'}).reset_index()
assortment_result = assortment_result.merge(assortment_total_weights, how='left', left_on='assortment_fk', right_on='assortment_fk', suffixes=['', '_total'])
facings = 'facings_ign_stack' if kpi['Ignore Stacking'] else 'facings'
products_in_session = self.scif[(self.scif[facings] > 0) & (self.scif['location_type'].isin(location_types))][['product_fk', 'facings']]\
.groupby('product_fk').agg({'facings': 'sum'}).reset_index()
lvl3_result = assortment_result.merge(products_in_session, how='left', left_on='product_fk', right_on='product_fk')
lvl3_result['facings'] = lvl3_result['facings'].fillna(0)
lvl3_result['distributed'] = lvl3_result.apply(lambda x: 1 if x['facings'] else 0, axis=1)
lvl3_result['result_facings'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'facings'), axis=1)
lvl3_result['result_distributed'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'distributed'), axis=1)
return lvl3_result
def calculate_assortment_vs_target_lvl2(self, lvl3_result):
lvl2_result = lvl3_result.groupby(['kpi_fk_lvl2', self.assortment.ASSORTMENT_FK, self.assortment.ASSORTMENT_GROUP_FK])\
.agg({'facings': 'sum', 'distributed': 'sum', 'target': 'sum', 'result_facings': 'sum', 'result_distributed': 'sum'}).reset_index()
return lvl2_result
@staticmethod
def assortment_vs_target_result(x, y):
if x[y] < x['target']:
return round(x[y] / float(x['target']) * float(x['weight']) / x['weight_total'], 5)
else:
return round(1 * float(x['weight']) / x['weight_total'], 5)
@kpi_runtime()
def calculate_sos_vs_target(self, kpi):
"""
The function filters only the relevant scenes by Location Type and calculates the linear SOS and
the facing SOS according to Manufacturer and Category set in the target.
:return:
"""
location_type = kpi['Location Type']
kpi_fk = self.common.get_kpi_fk_by_kpi_type(SOS_MANUFACTURER_CATEGORY + ('_' + location_type if location_type else ''))
sos_store_policies = self.sos_store_policies[self.sos_store_policies['kpi_fk'] == str(kpi_fk)]
sos_store_policy = None
store_policy_passed = 0
for index, policy in sos_store_policies.iterrows():
sos_store_policy = policy
store_policy = json.loads(policy['store_policy'])
store_policy_passed = 1
for key in store_policy.keys():
if key in self.store_info.columns.tolist():
if self.store_info[key][0] in store_policy[key]:
continue
else:
store_policy_passed = 0
break
else:
Log.error("Store Policy attribute is not found: '{}'").format(key)
store_policy_passed = 0
break
if store_policy_passed:
break
score = potential_score = 0
if store_policy_passed:
general_filters = {LOCATION_TYPE: location_type}
sos_policy = json.loads(sos_store_policy['sos_policy'])
numerator_sos_filters = {MANUFACTURER_NAME: sos_policy[NUMERATOR][MANUFACTURER], CATEGORY: sos_policy[DENOMINATOR][CATEGORY]}
denominator_sos_filters = {CATEGORY: sos_policy[DENOMINATOR][CATEGORY]}
numerator_id = self.all_products.loc[self.all_products[MANUFACTURER_NAME] == sos_policy[NUMERATOR][MANUFACTURER]][MANUFACTURER + '_fk'].values[0]
denominator_id = self.all_products.loc[self.all_products[CATEGORY] == sos_policy[DENOMINATOR][CATEGORY]][CATEGORY + '_fk'].values[0]
ignore_stacking = kpi['Ignore Stacking'] if kpi['Ignore Stacking'] else 0
numer_facings, numer_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(numerator_sos_filters, **general_filters))
denom_facings, denom_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(denominator_sos_filters, **general_filters))
if kpi['KPI Type'].lower() == LINEAR_SOS_VS_TARGET:
numerator_result = round(numer_linear, 0)
denominator_result = round(denom_linear, 0)
result = numer_linear / float(denom_linear) if denom_linear else 0
elif kpi['KPI Type'].lower() == FACINGS_SOS_VS_TARGET:
numerator_result = numer_facings
denominator_result = denom_facings
result = numer_facings / float(denom_facings) if denom_facings else 0
else:
Log.error("KPI Type is invalid: '{}'").format(kpi['KPI Type'])
numerator_result = denominator_result = result = 0
if sos_store_policy['target']:
sos_target = round(float(sos_store_policy['target'])*100, 0)
else:
Log.error("SOS target is not set for Store ID {}").format(self.store_id)
sos_target = 0
result_vs_target = result/(float(sos_target)/100)*100 if sos_target else 0
score = self.score_function(result_vs_target, kpi)
potential_score = round(float(kpi['Weight'])*100, 0)
identifier_kpi = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']))
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
result=result,
score=score,
weight=potential_score,
target=sos_target,
identifier_result=identifier_kpi,
identifier_parent=identifier_parent,
should_enter=True
)
else:
Log.warning("Store Policy is not found for Store ID {}".format(self.store_id))
return score, potential_score, store_policy_passed
def calculate_share_space(self, ignore_stacking=1, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param ignore_stacking: 1 is to ignore stacking.
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)]
if ignore_stacking:
sum_of_facings = filtered_scif['facings_ign_stack'].sum()
space_length = filtered_scif['net_len_ign_stack'].sum()
else:
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['net_len_ign_stack'].sum()
return sum_of_facings, space_length
@staticmethod
def get_template_path():
return os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data', KPIS_TEMPLATE_NAME)
def get_template_data(self):
template_data = {}
try:
sheet_names = pd.ExcelFile(self.template_path).sheet_names
for sheet in sheet_names:
template_data[sheet] = parse_template(self.template_path, sheet, lower_headers_row_index=0)
except IOError as e:
Log.error('Template {} does not exist. {}'.format(KPIS_TEMPLATE_NAME, repr(e)))
return template_data
@staticmethod
def score_function(score, kpi):
weight = float(kpi['Weight']) if kpi['Weight'] else 1
score_function = kpi['Score Function'].lower()
l_threshold = float(kpi['Lower Threshold'])*100 if kpi['Lower Threshold'] else 0
h_threshold = float(kpi['Higher Threshold'])*100 if kpi['Higher Threshold'] else 100
if score < l_threshold:
score = 0
elif score >= h_threshold:
score = 100
if score_function in [WEIGHTED_SCORE]:
score = round(score*weight, 0)
else:
score = round(score, 0)
return score
class CBCDAIRYILToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.common = Common(self.data_provider)
self.old_common = oldCommon(self.data_provider)
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.session_fk = self.data_provider.session_id
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.survey = Survey(self.data_provider)
self.block = Block(self.data_provider)
self.general_toolbox = GENERALToolBox(self.data_provider)
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.template_path = self.get_relevant_template()
self.gap_data = self.get_gap_data()
self.kpi_weights = parse_template(self.template_path,
Consts.KPI_WEIGHT,
lower_headers_row_index=0)
self.template_data = self.parse_template_data()
self.kpis_gaps = list()
self.passed_availability = list()
self.kpi_static_data = self.old_common.get_kpi_static_data()
self.own_manufacturer_fk = int(
self.data_provider.own_manufacturer.param_value.values[0])
self.parser = Parser
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
def get_relevant_template(self):
"""
This function returns the relevant template according to it's visit date.
Because of a change that was done in the logic there are 3 templates that match different dates.
:return: Full template path
"""
if self.visit_date <= datetime.date(datetime(2019, 12, 31)):
return "{}/{}/{}".format(
Consts.TEMPLATE_PATH, Consts.PREVIOUS_TEMPLATES,
Consts.PROJECT_TEMPLATE_NAME_UNTIL_2019_12_31)
else:
return "{}/{}".format(Consts.TEMPLATE_PATH,
Consts.CURRENT_TEMPLATE)
def get_gap_data(self):
"""
This function parse the gap data template and returns the gap priorities.
:return: A dict with the priorities according to kpi_names. E.g: {kpi_name1: 1, kpi_name2: 2 ...}
"""
gap_sheet = parse_template(self.template_path,
Consts.KPI_GAP,
lower_headers_row_index=0)
gap_data = zip(gap_sheet[Consts.KPI_NAME], gap_sheet[Consts.ORDER])
gap_data = {kpi_name: int(order) for kpi_name, order in gap_data}
return gap_data
def main_calculation(self):
"""
This function calculates the KPI results.
At first it fetches the relevant Sets (according to the stores attributes) and go over all of the relevant
Atomic KPIs based on the project's template.
Than, It aggregates the result per KPI using the weights and at last aggregates for the set level.
"""
self.calculate_hierarchy_sos()
self.calculate_oos()
if self.template_data.empty:
Log.warning(Consts.EMPTY_TEMPLATE_DATA_LOG.format(self.store_id))
return
kpi_set, kpis = self.get_relevant_kpis_for_calculation()
kpi_set_fk = self.common.get_kpi_fk_by_kpi_type(Consts.TOTAL_SCORE)
old_kpi_set_fk = self.get_kpi_fk_by_kpi_name(Consts.TOTAL_SCORE, 1)
total_set_scores = list()
for kpi_name in kpis:
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name)
old_kpi_fk = self.get_kpi_fk_by_kpi_name(kpi_name, 2)
kpi_weight = self.get_kpi_weight(kpi_name, kpi_set)
atomics_df = self.get_atomics_to_calculate(kpi_name)
atomic_results = self.calculate_atomic_results(
kpi_fk, atomics_df) # Atomic level
kpi_results = self.calculate_kpis_and_save_to_db(
atomic_results, kpi_fk, kpi_weight, kpi_set_fk) # KPI lvl
self.old_common.old_write_to_db_result(fk=old_kpi_fk,
level=2,
score=format(
kpi_results, '.2f'))
total_set_scores.append(kpi_results)
kpi_set_score = self.calculate_kpis_and_save_to_db(
total_set_scores, kpi_set_fk) # Set level
self.old_common.write_to_db_result(fk=old_kpi_set_fk,
level=1,
score=kpi_set_score)
self.handle_gaps()
def calculate_oos(self):
numerator = total_facings = 0
store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=Consts.OOS)
sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_type=Consts.OOS_SKU)
leading_skus_df = self.template_data[self.template_data[
Consts.KPI_NAME].str.encode(
"utf8") == Consts.LEADING_PRODUCTS.encode("utf8")]
skus_ean_list = leading_skus_df[Consts.PARAMS_VALUE_1].tolist()
skus_ean_set = set([
ean_code.strip() for values in skus_ean_list
for ean_code in values.split(",")
])
product_fks = self.all_products[self.all_products[
'product_ean_code'].isin(skus_ean_set)]['product_fk'].tolist()
# sku level oos
for sku in product_fks:
# 2 for distributed and 1 for oos
product_df = self.scif[self.scif['product_fk'] == sku]
if product_df.empty:
numerator += 1
self.common.write_to_db_result(fk=sku_kpi_fk,
numerator_id=sku,
denominator_id=self.store_id,
result=1,
numerator_result=1,
denominator_result=1,
score=0,
identifier_parent="OOS",
should_enter=True)
# store level oos
denominator = len(product_fks)
if denominator == 0:
numerator = result = 0
else:
result = round(numerator / float(denominator), 4)
self.common.write_to_db_result(fk=store_kpi_fk,
numerator_id=self.own_manufacturer_fk,
denominator_id=self.store_id,
result=result,
numerator_result=numerator,
denominator_result=denominator,
score=total_facings,
identifier_result="OOS")
def calculate_hierarchy_sos(self):
store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_type=Consts.SOS_BY_OWN_MAN)
category_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_type=Consts.SOS_BY_OWN_MAN_CAT)
brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_type=Consts.SOS_BY_OWN_MAN_CAT_BRAND)
sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
kpi_type=Consts.SOS_BY_OWN_MAN_CAT_BRAND_SKU)
sos_df = self.scif[self.scif['rlv_sos_sc'] == 1]
# store level sos
store_res, store_num, store_den = self.calculate_own_manufacturer_sos(
filters={}, df=sos_df)
self.common.write_to_db_result(fk=store_kpi_fk,
numerator_id=self.own_manufacturer_fk,
denominator_id=self.store_id,
result=store_res,
numerator_result=store_num,
denominator_result=store_den,
score=store_res,
identifier_result="OWN_SOS")
# category level sos
session_categories = set(
self.parser.filter_df(
conditions={'manufacturer_fk': self.own_manufacturer_fk},
data_frame_to_filter=self.scif)['category_fk'])
for category_fk in session_categories:
filters = {'category_fk': category_fk}
cat_res, cat_num, cat_den = self.calculate_own_manufacturer_sos(
filters=filters, df=sos_df)
self.common.write_to_db_result(
fk=category_kpi_fk,
numerator_id=category_fk,
denominator_id=self.store_id,
result=cat_res,
numerator_result=cat_num,
denominator_result=cat_den,
score=cat_res,
identifier_parent="OWN_SOS",
should_enter=True,
identifier_result="OWN_SOS_cat_{}".format(str(category_fk)))
# brand-category level sos
filters['manufacturer_fk'] = self.own_manufacturer_fk
cat_brands = set(
self.parser.filter_df(conditions=filters,
data_frame_to_filter=sos_df)['brand_fk'])
for brand_fk in cat_brands:
filters['brand_fk'] = brand_fk
brand_df = self.parser.filter_df(conditions=filters,
data_frame_to_filter=sos_df)
brand_num = brand_df['facings'].sum()
brand_res, brand_num, cat_num = self.calculate_sos_res(
brand_num, cat_num)
self.common.write_to_db_result(
fk=brand_kpi_fk,
numerator_id=brand_fk,
denominator_id=category_fk,
result=brand_res,
numerator_result=brand_num,
should_enter=True,
denominator_result=cat_num,
score=brand_res,
identifier_parent="OWN_SOS_cat_{}".format(
str(category_fk)),
identifier_result="OWN_SOS_cat_{}_brand_{}".format(
str(category_fk), str(brand_fk)))
product_fks = set(
self.parser.filter_df(
conditions=filters,
data_frame_to_filter=sos_df)['product_fk'])
for sku in product_fks:
filters['product_fk'] = sku
product_df = self.parser.filter_df(
conditions=filters, data_frame_to_filter=sos_df)
sku_facings = product_df['facings'].sum()
sku_result, sku_num, sku_den = self.calculate_sos_res(
sku_facings, brand_num)
self.common.write_to_db_result(
fk=sku_kpi_fk,
numerator_id=sku,
denominator_id=brand_fk,
result=sku_result,
numerator_result=sku_facings,
should_enter=True,
denominator_result=brand_num,
score=sku_facings,
identifier_parent="OWN_SOS_cat_{}_brand_{}".format(
str(category_fk), str(brand_fk)))
del filters['product_fk']
del filters['brand_fk']
def calculate_own_manufacturer_sos(self, filters, df):
filters['manufacturer_fk'] = self.own_manufacturer_fk
numerator_df = self.parser.filter_df(conditions=filters,
data_frame_to_filter=df)
del filters['manufacturer_fk']
denominator_df = self.parser.filter_df(conditions=filters,
data_frame_to_filter=df)
if denominator_df.empty:
return 0, 0, 0
denominator = denominator_df['facings'].sum()
if numerator_df.empty:
numerator = 0
else:
numerator = numerator_df['facings'].sum()
return self.calculate_sos_res(numerator, denominator)
@staticmethod
def calculate_sos_res(numerator, denominator):
if denominator == 0:
return 0, 0, 0
result = round(numerator / float(denominator), 3)
return result, numerator, denominator
def add_gap(self, atomic_kpi, score, atomic_weight):
"""
In case the score is not perfect the gap is added to the gap list.
:param atomic_weight: The Atomic KPI's weight.
:param score: Atomic KPI score.
:param atomic_kpi: A Series with data about the Atomic KPI.
"""
parent_kpi_name = atomic_kpi[Consts.KPI_NAME]
atomic_name = atomic_kpi[Consts.KPI_ATOMIC_NAME]
atomic_fk = self.common.get_kpi_fk_by_kpi_type(atomic_name)
current_gap_dict = {
Consts.ATOMIC_FK: atomic_fk,
Consts.PRIORITY: self.gap_data[parent_kpi_name],
Consts.SCORE: score,
Consts.WEIGHT: atomic_weight
}
self.kpis_gaps.append(current_gap_dict)
@staticmethod
def sort_by_priority(gap_dict):
""" This is a util function for the kpi's gaps sorting by priorities"""
return gap_dict[Consts.PRIORITY], gap_dict[Consts.SCORE]
def handle_gaps(self):
""" This function takes the top 5 gaps (by priority) and saves it to the DB (pservice.custom_gaps table) """
self.kpis_gaps.sort(key=self.sort_by_priority)
gaps_total_score = 0
gaps_per_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.GAP_PER_ATOMIC_KPI)
gaps_total_score_kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.GAPS_TOTAL_SCORE_KPI)
for gap in self.kpis_gaps[:5]:
current_gap_score = gap[Consts.WEIGHT] - (gap[Consts.SCORE] / 100 *
gap[Consts.WEIGHT])
gaps_total_score += current_gap_score
self.insert_gap_results(gaps_per_kpi_fk,
current_gap_score,
gap[Consts.WEIGHT],
numerator_id=gap[Consts.ATOMIC_FK],
parent_fk=gaps_total_score_kpi_fk)
total_weight = sum(
map(lambda res: res[Consts.WEIGHT], self.kpis_gaps[:5]))
self.insert_gap_results(gaps_total_score_kpi_fk, gaps_total_score,
total_weight)
def insert_gap_results(self,
gap_kpi_fk,
score,
weight,
numerator_id=Consts.CBC_MANU,
parent_fk=None):
""" This is a utility function that insert results to the DB for the GAP """
should_enter = True if parent_fk else False
score, weight = score * 100, round(weight * 100, 2)
self.common.write_to_db_result(fk=gap_kpi_fk,
numerator_id=numerator_id,
numerator_result=score,
denominator_id=self.store_id,
denominator_result=weight,
weight=weight,
identifier_result=gap_kpi_fk,
identifier_parent=parent_fk,
result=score,
score=score,
should_enter=should_enter)
def calculate_kpis_and_save_to_db(self,
kpi_results,
kpi_fk,
parent_kpi_weight=1.0,
parent_fk=None):
"""
This KPI aggregates the score by weights and saves the results to the DB.
:param kpi_results: A list of results and weights tuples: [(score1, weight1), (score2, weight2) ... ].
:param kpi_fk: The relevant KPI fk.
:param parent_kpi_weight: The parent's KPI total weight.
:param parent_fk: The KPI SET FK that the KPI "belongs" too if exist.
:return: The aggregated KPI score.
"""
should_enter = True if parent_fk else False
ignore_weight = not should_enter # Weights should be ignored only in the set level!
kpi_score = self.calculate_kpi_result_by_weight(
kpi_results, parent_kpi_weight, ignore_weights=ignore_weight)
total_weight = round(parent_kpi_weight * 100, 2)
target = None if parent_fk else round(80,
2) # Requested for visualization
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=Consts.CBC_MANU,
numerator_result=kpi_score,
denominator_id=self.store_id,
denominator_result=total_weight,
target=target,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=should_enter,
weight=total_weight,
result=kpi_score,
score=kpi_score)
if not parent_fk: # required only for writing set score in anoter kpi needed for dashboard
kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.TOTAL_SCORE_FOR_DASHBOARD)
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=Consts.CBC_MANU,
numerator_result=kpi_score,
denominator_id=self.store_id,
denominator_result=total_weight,
target=target,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=should_enter,
weight=total_weight,
result=kpi_score,
score=kpi_score)
return kpi_score
def calculate_kpi_result_by_weight(self,
kpi_results,
parent_kpi_weight,
ignore_weights=False):
"""
This function aggregates the KPI results by scores and weights.
:param ignore_weights: If True the function just sums the results.
:param parent_kpi_weight: The parent's KPI total weight.
:param kpi_results: A list of results and weights tuples: [(score1, weight1), (score2, weight2) ... ].
:return: The aggregated KPI score.
"""
if ignore_weights or len(kpi_results) == 0:
return sum(kpi_results)
weights_list = map(lambda res: res[1], kpi_results)
if None in weights_list: # Ignoring weights and dividing equally by length!
kpi_score = sum(map(lambda res: res[0], kpi_results)) / float(
len(kpi_results))
elif round(
sum(weights_list), 2
) < parent_kpi_weight: # Missing weights needs to be divided among the kpis
kpi_score = self.divide_missing_percentage(kpi_results,
parent_kpi_weight,
sum(weights_list))
else:
kpi_score = sum([score * weight for score, weight in kpi_results])
return kpi_score
@staticmethod
def divide_missing_percentage(kpi_results, parent_weight, total_weights):
"""
This function is been activated in case the total number of KPI weights doesn't equal to 100%.
It divides the missing percentage among the other KPI and calculates the score.
:param parent_weight: Parent KPI's weight.
:param total_weights: The total number of weights that were calculated earlier.
:param kpi_results: A list of results and weights tuples: [(score1, weight1), (score2, weight2) ... ].
:return: KPI aggregated score.
"""
missing_weight = parent_weight - total_weights
weight_addition = missing_weight / float(
len(kpi_results)) if kpi_results else 0
kpi_score = sum([
score * (weight + weight_addition) for score, weight in kpi_results
])
return kpi_score
def calculate_atomic_results(self, kpi_fk, atomics_df):
"""
This method calculates the result for every atomic KPI (the lowest level) that are relevant for the kpi_fk.
:param kpi_fk: The KPI FK that the atomic "belongs" too.
:param atomics_df: The relevant Atomic KPIs from the project's template.
:return: A list of results and weights tuples: [(score1, weight1), (score2, weight2) ... ].
"""
total_scores = list()
for i in atomics_df.index:
current_atomic = atomics_df.loc[i]
kpi_type, atomic_weight, general_filters = self.get_relevant_data_per_atomic(
current_atomic)
if general_filters is None:
continue
num_result, den_result, atomic_score = self.calculate_atomic_kpi_by_type(
kpi_type, **general_filters)
# Handling Atomic KPIs results
if atomic_score is None: # In cases that we need to ignore the KPI and divide it's weight
continue
elif atomic_score < 100:
self.add_gap(current_atomic, atomic_score, atomic_weight)
total_scores.append((atomic_score, atomic_weight))
atomic_fk_lvl_2 = self.common.get_kpi_fk_by_kpi_type(
current_atomic[Consts.KPI_ATOMIC_NAME].strip())
old_atomic_fk = self.get_kpi_fk_by_kpi_name(
current_atomic[Consts.KPI_ATOMIC_NAME].strip(), 3)
self.common.write_to_db_result(fk=atomic_fk_lvl_2,
numerator_id=Consts.CBC_MANU,
numerator_result=num_result,
denominator_id=self.store_id,
weight=round(
atomic_weight * 100, 2),
denominator_result=den_result,
should_enter=True,
identifier_parent=kpi_fk,
result=atomic_score,
score=atomic_score * atomic_weight)
self.old_common.old_write_to_db_result(
fk=old_atomic_fk,
level=3,
result=str(format(atomic_score * atomic_weight, '.2f')),
score=atomic_score)
return total_scores
def get_kpi_fk_by_kpi_name(self, kpi_name, kpi_level):
if kpi_level == 1:
column_key = 'kpi_set_fk'
column_value = 'kpi_set_name'
elif kpi_level == 2:
column_key = 'kpi_fk'
column_value = 'kpi_name'
elif kpi_level == 3:
column_key = 'atomic_kpi_fk'
column_value = 'atomic_kpi_name'
else:
raise ValueError('invalid level')
try:
if column_key and column_value:
return self.kpi_static_data[
self.kpi_static_data[column_value].str.encode('utf-8') ==
kpi_name.encode('utf-8')][column_key].values[0]
except IndexError:
Log.error(
'Kpi name: {}, isnt equal to any kpi name in static table'.
format(kpi_name))
return None
def get_relevant_data_per_atomic(self, atomic_series):
"""
This function return the relevant data per Atomic KPI.
:param atomic_series: The Atomic row from the Template.
:return: A tuple with data: (atomic_type, atomic_weight, general_filters)
"""
kpi_type = atomic_series.get(Consts.KPI_TYPE)
atomic_weight = float(atomic_series.get(
Consts.WEIGHT)) if atomic_series.get(Consts.WEIGHT) else None
general_filters = self.get_general_filters(atomic_series)
return kpi_type, atomic_weight, general_filters
def calculate_atomic_kpi_by_type(self, atomic_type, **general_filters):
"""
This function calculates the result according to the relevant Atomic Type.
:param atomic_type: KPI Family from the template.
:param general_filters: Relevant attributes and values to calculate by.
:return: A tuple with results: (numerator_result, denominator_result, total_score).
"""
num_result = denominator_result = 0
if atomic_type in [Consts.AVAILABILITY]:
atomic_score = self.calculate_availability(**general_filters)
elif atomic_type == Consts.AVAILABILITY_FROM_BOTTOM:
atomic_score = self.calculate_availability_from_bottom(
**general_filters)
elif atomic_type == Consts.MIN_2_AVAILABILITY:
num_result, denominator_result, atomic_score = self.calculate_min_2_availability(
**general_filters)
elif atomic_type == Consts.SURVEY:
atomic_score = self.calculate_survey(**general_filters)
elif atomic_type == Consts.BRAND_BLOCK:
atomic_score = self.calculate_brand_block(**general_filters)
elif atomic_type == Consts.EYE_LEVEL:
num_result, denominator_result, atomic_score = self.calculate_eye_level(
**general_filters)
else:
Log.warning(Consts.UNSUPPORTED_KPI_LOG.format(atomic_type))
atomic_score = None
return num_result, denominator_result, atomic_score
def get_relevant_kpis_for_calculation(self):
"""
This function retrieve the relevant KPIs to calculate from the template
:return: A tuple: (set_name, [kpi1, kpi2, kpi3...]) to calculate.
"""
kpi_set = self.template_data[Consts.KPI_SET].values[0]
kpis = self.template_data[self.template_data[
Consts.KPI_SET].str.encode('utf-8') == kpi_set.encode('utf-8')][
Consts.KPI_NAME].unique().tolist()
# Planogram KPI should be calculated last because of the MINIMUM 2 FACINGS KPI.
if Consts.PLANOGRAM_KPI in kpis and kpis.index(
Consts.PLANOGRAM_KPI) != len(kpis) - 1:
kpis.append(kpis.pop(kpis.index(Consts.PLANOGRAM_KPI)))
return kpi_set, kpis
def get_atomics_to_calculate(self, kpi_name):
"""
This method filters the KPIs data to be the relevant atomic KPIs.
:param kpi_name: The hebrew KPI name from the template.
:return: A DataFrame that contains data about the relevant Atomic KPIs.
"""
atomics = self.template_data[self.template_data[
Consts.KPI_NAME].str.encode('utf-8') == kpi_name.encode('utf-8')]
return atomics
def get_store_attributes(self, attributes_names):
"""
This function encodes and returns the relevant store attribute.
:param attributes_names: List of requested store attributes to return.
:return: A dictionary with the requested attributes, E.g: {attr_name: attr_val, ...}
"""
# Filter store attributes
store_info_dict = self.store_info.iloc[0].to_dict()
filtered_store_info = {
store_att: store_info_dict[store_att]
for store_att in attributes_names
}
return filtered_store_info
def parse_template_data(self):
"""
This function responsible to filter the relevant template data..
:return: A DataFrame with filtered Data by store attributes.
"""
kpis_template = parse_template(self.template_path,
Consts.KPI_SHEET,
lower_headers_row_index=1)
relevant_store_info = self.get_store_attributes(
Consts.STORE_ATTRIBUTES_TO_FILTER_BY)
filtered_data = self.filter_template_by_store_att(
kpis_template, relevant_store_info)
return filtered_data
@staticmethod
def filter_template_by_store_att(kpis_template, store_attributes):
"""
This function gets a dictionary with store type, additional attribute 1, 2 and 3 and filters the template by it.
:param kpis_template: KPI sheet of the project's template.
:param store_attributes: {store_type: X, additional_attribute_1: Y, ... }.
:return: A filtered DataFrame.
"""
for store_att, store_val in store_attributes.iteritems():
if store_val is None:
store_val = ""
kpis_template = kpis_template[(
kpis_template[store_att].str.encode('utf-8') ==
store_val.encode('utf-8')) | (kpis_template[store_att] == "")]
return kpis_template
def get_relevant_scenes_by_params(self, params):
"""
This function returns the relevant scene_fks to calculate.
:param params: The Atomic KPI row filters from the template.
:return: List of scene fks.
"""
template_names = params[Consts.TEMPLATE_NAME].split(Consts.SEPARATOR)
template_groups = params[Consts.TEMPLATE_GROUP].split(Consts.SEPARATOR)
filtered_scif = self.scif[[
Consts.SCENE_ID, 'template_name', 'template_group'
]]
if template_names and any(template_names):
filtered_scif = filtered_scif[filtered_scif['template_name'].isin(
template_names)]
if template_groups and any(template_groups):
filtered_scif = filtered_scif[filtered_scif['template_group'].isin(
template_groups)]
return filtered_scif[Consts.SCENE_ID].unique().tolist()
def get_general_filters(self, params):
"""
This function returns the relevant KPI filters according to the template.
Filter params 1 & 2 are included and param 3 is for exclusion.
:param params: The Atomic KPI row in the template
:return: A dictionary with the relevant filters.
"""
general_filters = {
Consts.TARGET: params[Consts.TARGET],
Consts.SPLIT_SCORE: params[Consts.SPLIT_SCORE],
Consts.KPI_FILTERS: dict()
}
relevant_scenes = self.get_relevant_scenes_by_params(params)
if not relevant_scenes:
return None
else:
general_filters[Consts.KPI_FILTERS][
Consts.SCENE_ID] = relevant_scenes
for type_col, value_col in Consts.KPI_FILTER_VALUE_LIST:
if params[value_col]:
should_included = Consts.INCLUDE_VAL if value_col != Consts.PARAMS_VALUE_3 else Consts.EXCLUDE_VAL
param_type, param_value = params[type_col], params[value_col]
filter_param = self.handle_param_values(
param_type, param_value)
general_filters[Consts.KPI_FILTERS][param_type] = (
filter_param, should_included)
return general_filters
@staticmethod
def handle_param_values(param_type, param_value):
"""
:param param_type: The param type to filter by. E.g: product_ean code or brand_name
:param param_value: The value to filter by.
:return: list of param values.
"""
values_list = param_value.split(Consts.SEPARATOR)
params = map(
lambda val: float(val) if unicode.isdigit(val) and param_type !=
Consts.EAN_CODE else val.strip(), values_list)
return params
def get_kpi_weight(self, kpi, kpi_set):
"""
This method returns the KPI weight according to the project's template.
:param kpi: The KPI name.
:param kpi_set: Set KPI name.
:return: The kpi weight (Float).
"""
row = self.kpi_weights[(self.kpi_weights[Consts.KPI_SET].str.encode(
'utf-8') == kpi_set.encode('utf-8')) & (self.kpi_weights[
Consts.KPI_NAME].str.encode('utf-8') == kpi.encode('utf-8'))]
weight = row.get(Consts.WEIGHT)
return float(weight.values[0]) if not weight.empty else None
def merge_and_filter_scif_and_matches_for_eye_level(self, **kpi_filters):
"""
This function merges between scene_item_facts and match_product_in_scene DataFrames and filters the merged DF
according to the @param kpi_filters.
:param kpi_filters: Dictionary with attributes and values to filter the DataFrame by.
:return: The merged and filtered DataFrame.
"""
scif_matches_diff = self.match_product_in_scene[
['scene_fk', 'product_fk'] +
list(self.match_product_in_scene.keys().difference(
self.scif.keys()))]
merged_df = pd.merge(self.scif[self.scif.facings != 0],
scif_matches_diff,
how='outer',
left_on=['scene_id', 'item_id'],
right_on=[Consts.SCENE_FK, Consts.PRODUCT_FK])
merged_df = merged_df[self.general_toolbox.get_filter_condition(
merged_df, **kpi_filters)]
return merged_df
@kpi_runtime()
def calculate_eye_level(self, **general_filters):
"""
This function calculates the Eye level KPI. It filters and products according to the template and
returns a Tuple: (eye_level_facings / total_facings, score).
:param general_filters: A dictionary with the relevant KPI filters.
:return: E.g: (10, 20, 50) or (8, 10, 100) --> score >= 75 turns to 100.
"""
merged_df = self.merge_and_filter_scif_and_matches_for_eye_level(
**general_filters[Consts.KPI_FILTERS])
relevant_scenes = merged_df['scene_id'].unique().tolist()
total_number_of_facings = eye_level_facings = 0
for scene in relevant_scenes:
scene_merged_df = merged_df[merged_df['scene_id'] == scene]
scene_matches = self.match_product_in_scene[
self.match_product_in_scene['scene_fk'] == scene]
total_number_of_facings += len(scene_merged_df)
scene_merged_df = self.filter_df_by_shelves(
scene_merged_df, scene_matches, Consts.EYE_LEVEL_PER_SHELF)
eye_level_facings += len(scene_merged_df)
total_score = eye_level_facings / float(
total_number_of_facings) if total_number_of_facings else 0
total_score = 100 if total_score >= 0.75 else total_score * 100
return eye_level_facings, total_number_of_facings, total_score
@staticmethod
def filter_df_by_shelves(df, scene_matches, eye_level_definition):
"""
This function filters the df according to the eye-level definition
:param df: data frame to filter
:param scene_matches: match_product_in_scene for particular scene
:param eye_level_definition: definition for eye level shelves
:return: filtered data frame
"""
# number_of_shelves = df.shelf_number_from_bottom.max()
number_of_shelves = max(scene_matches.shelf_number_from_bottom.max(),
scene_matches.shelf_number.max())
top, bottom = 0, 0
for json_def in eye_level_definition:
if json_def[Consts.MIN] <= number_of_shelves <= json_def[
Consts.MAX]:
top = json_def[Consts.TOP]
bottom = json_def[Consts.BOTTOM]
return df[(df.shelf_number > top)
& (df.shelf_number_from_bottom > bottom)]
@kpi_runtime()
def calculate_availability_from_bottom(self, **general_filters):
"""
This function checks if *all* of the relevant products are in the lowest shelf.
:param general_filters: A dictionary with the relevant KPI filters.
:return:
"""
allowed_products_dict = self.get_allowed_product_by_params(
**general_filters)
filtered_matches = self.match_product_in_scene[
self.match_product_in_scene[Consts.PRODUCT_FK].isin(
allowed_products_dict[Consts.PRODUCT_FK])]
relevant_shelves_to_check = set(
filtered_matches[Consts.SHELF_NUM_FROM_BOTTOM].unique().tolist())
# Check bottom shelf condition
return 0 if len(
relevant_shelves_to_check
) != 1 or Consts.LOWEST_SHELF not in relevant_shelves_to_check else 100
@kpi_runtime()
def calculate_brand_block(self, **general_filters):
"""
This function calculates the brand block KPI. It filters and excluded products according to the template and
than checks if at least one scene has a block.
:param general_filters: A dictionary with the relevant KPI filters.
:return: 100 if at least one scene has a block, 0 otherwise.
"""
products_dict = self.get_allowed_product_by_params(**general_filters)
block_result = self.block.network_x_block_together(
population=products_dict,
additional={
'minimum_block_ratio': Consts.MIN_BLOCK_RATIO,
'minimum_facing_for_block': Consts.MIN_FACINGS_IN_BLOCK,
'allowed_products_filters': {
'product_type': ['Empty']
},
'calculate_all_scenes': False,
'include_stacking': True,
'check_vertical_horizontal': False
})
result = 100 if not block_result.empty and not block_result[
block_result.is_block].empty else 0
return result
def get_allowed_product_by_params(self, **filters):
"""
This function filters the relevant products for the block together KPI and exclude the ones that needs to be
excluded by the template.
:param filters: Atomic KPI filters.
:return: A Dictionary with the relevant products. E.g: {'product_fk': [1,2,3,4,5]}.
"""
allowed_product = dict()
filtered_scif = self.calculate_availability(return_df=True, **filters)
allowed_product[Consts.PRODUCT_FK] = filtered_scif[
Consts.PRODUCT_FK].unique().tolist()
return allowed_product
@kpi_runtime()
def calculate_survey(self, **general_filters):
"""
This function calculates the result for Survey KPI.
:param general_filters: A dictionary with the relevant KPI filters.
:return: 100 if the answer is yes, else 0.
"""
if Consts.QUESTION_ID not in general_filters[
Consts.KPI_FILTERS].keys():
Log.warning(Consts.MISSING_QUESTION_LOG)
return 0
survey_question_id = general_filters[Consts.KPI_FILTERS].get(
Consts.QUESTION_ID)
# General filters returns output for filter_df basically so we need to adjust it here.
if isinstance(survey_question_id, tuple):
survey_question_id = survey_question_id[0] # Get rid of the tuple
if isinstance(survey_question_id, list):
survey_question_id = int(
survey_question_id[0]) # Get rid of the list
target_answer = general_filters[Consts.TARGET]
survey_answer = self.survey.get_survey_answer(
(Consts.QUESTION_FK, survey_question_id))
if survey_answer in Consts.SURVEY_ANSWERS_TO_IGNORE:
return None
elif survey_answer:
return 100 if survey_answer.strip() == target_answer else 0
return 0
@kpi_runtime()
def calculate_availability(self, return_df=False, **general_filters):
"""
This functions checks for availability by filters.
During the calculation, if the KPI was passed, the results is being saved for future usage of
"MIN 2 AVAILABILITY KPI".
:param return_df: If True, the function returns the filtered scene item facts, else, returns the score.
:param general_filters: A dictionary with the relevant KPI filters.
:return: See @param return_df.
"""
filtered_scif = self.scif[self.general_toolbox.get_filter_condition(
self.scif, **general_filters[Consts.KPI_FILTERS])]
if return_df:
return filtered_scif
if not filtered_scif.empty:
tested_products = general_filters[Consts.KPI_FILTERS][
Consts.EAN_CODE][0]
self.passed_availability.append(tested_products)
return 100
return 0
@staticmethod
def get_number_of_facings_per_product_dict(df, ignore_stack=False):
"""
This function gets a DataFrame and returns a dictionary with number of facings per products.
:param df: Pandas.DataFrame with 'product_ean_code' and 'facings' / 'facings_ign_stack' fields.
:param ignore_stack: If True will use 'facings_ign_stack' field, else 'facings' field.
:return: E.g: {ean_code1: 10, ean_code2: 5, ean_code3: 1...}
"""
stacking_field = Consts.FACINGS_IGN_STACK if ignore_stack else Consts.FACINGS
df = df[[Consts.EAN_CODE, stacking_field]].dropna()
df = df[df[stacking_field] > 0]
facings_dict = dict(zip(df[Consts.EAN_CODE], df[stacking_field]))
return facings_dict
@kpi_runtime()
def calculate_min_2_availability(self, **general_filters):
"""
This KPI checks for all of the Availability Atomics KPIs that passed, if the tested products have at least
2 facings in case of IGNORE STACKING!
:param general_filters: A dictionary with the relevant KPI filters.
:return: numerator result, denominator result and total_score
"""
score = 0
filtered_df = self.calculate_availability(return_df=True,
**general_filters)
facings_counter = self.get_number_of_facings_per_product_dict(
filtered_df, ignore_stack=True)
for products in self.passed_availability:
score += 1 if sum([
facings_counter[product]
for product in products if product in facings_counter
]) > 1 else 0
total_score = (score / float(len(self.passed_availability))
) * 100 if self.passed_availability else 0
return score, len(self.passed_availability), total_score
class CCAAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
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.templates = self.data_provider[Data.TEMPLATES]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.own_manufacturer_fk = int(self.data_provider.own_manufacturer.param_value.values[0])
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common_v2.get_kpi_static_data()
self.kpi_results_queries = []
self.template = self.data_provider.all_templates # templates
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(kpi_path[:- len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(kpi_path[:- len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(kpi_path[:- len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
self.assortment_data = pd.read_excel(os.path.join(kpi_path[:- len(base_file)], 'Data', 'template.xlsx'),
sheetname="Assortment")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
self.calculate_bay_kpi()
self.calculate_assortment_kpis()
self.calculate_prod_delta_prev_session()
self.common_v2.commit_results_data()
def calculate_sos(self):
"""
This function filtering Data frame - "scene item facts" by the parameters in the template.
Sending the filtered data frames to linear Sos calculation and facing Sos calculation
Writing the results to the new tables in DB
"""
facing_kpi_fk = self.kpi_static_data[self.kpi_static_data['client_name'] ==
'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
linear_kpi_fk = self.kpi_static_data[self.kpi_static_data['client_name'] ==
'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
den_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (
self.exclude_filters['apply on'] == 'Denominator')]
den_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (
self.exclude_filters['apply on'] == 'Denominator')]
num_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (
self.exclude_filters['apply on'] == 'Numerator')]
num_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (
self.exclude_filters['apply on'] == 'Numerator')]
scene_templates = self.scif['template_fk'].unique().tolist()
scene_manufactures = self.scif['manufacturer_fk'].unique().tolist()
# exclude filters denominator
den_general_facing_filters = self.create_dict_filters(den_facing_exclude_template, self.EXCLUDE_FILTER)
den_general_linear_filters = self.create_dict_filters(den_linear_exclude_template, self.EXCLUDE_FILTER)
# exclude filters numerator
num_general_facing_filters = self.create_dict_filters(num_facing_exclude_template, self.EXCLUDE_FILTER)
num_general_linear_filters = self.create_dict_filters(num_linear_exclude_template, self.EXCLUDE_FILTER)
df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template)
df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template)
df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template)
df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template)
for template in scene_templates:
for manufacture in scene_manufactures:
sos_filters = {"template_fk": (template, self.INCLUDE_FILTER),
"manufacturer_fk": (manufacture, self.INCLUDE_FILTER)}
tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)}
dict_num_facing = dict(
(k, v) for d in [sos_filters, num_general_facing_filters] for k, v in
d.items())
numerator_facings = self.calculate_share_space(df_num_fac, dict_num_facing)[0]
dict_num_linear = dict(
(k, v) for d in [sos_filters, num_general_linear_filters] for k, v in d.items())
numerator_linear = self.calculate_share_space(df_num_lin, dict_num_linear)[1]
dict_den_facing = dict((k, v) for d in [tem_filters, den_general_facing_filters] for k, v in d.items())
denominator_facings = self.calculate_share_space(df_den_fac, dict_den_facing)[0]
dict_den_linear = dict(
(k, v) for d in [tem_filters, den_general_linear_filters] for k, v in d.items())
denominator_linear = self.calculate_share_space(df_den_lin, dict_den_linear)[1]
score_facing = 0 if denominator_facings == 0 else (numerator_facings / denominator_facings) * 100
score_linear = 0 if denominator_linear == 0 else (numerator_linear / denominator_linear) * 100
self.common_v2.write_to_db_result(fk=facing_kpi_fk,
numerator_id=manufacture,
numerator_result=numerator_facings,
result=score_facing,
denominator_id=template,
denominator_result=denominator_facings,
score=score_facing
)
self.common_v2.write_to_db_result(fk=linear_kpi_fk,
numerator_id=manufacture,
numerator_result=numerator_linear,
result=score_linear,
denominator_id=template,
denominator_result=denominator_linear,
score=score_linear
)
def create_dict_filters(self, template, param):
"""
:param template : Template of the desired filtering to data frame
:param param : exclude /include
:return: Dictionary of filters and parameter : exclude / include by demeaned
"""
filters_dict = {}
template_without_second = template[template['Param 2'].isnull()]
for row in template_without_second.iterrows():
filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','), param)
return filters_dict
def filter_2_cond(self, data_frame, template):
"""
:param template: Template of the desired filtering
:param data_frame : Data frame
:return: data frame filtered by entries in the template with 2 conditions
"""
template_without_second = template[template['Param 2'].notnull()]
if template_without_second is not None:
for row in template_without_second.iterrows():
data_frame = data_frame.loc[(~data_frame[row[1]['Param 1']].isin(row[1]['Value 1'].split(','))) | (
~data_frame[row[1]['Param 2']].isin(row[1]['Value 2'].split(',')))]
return data_frame
def calculate_share_space(self, data_frame, filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param data_frame : relevant scene item facts data frame (filtered )
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = data_frame[self.toolbox.get_filter_condition(data_frame, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['gross_len_split_stack'].sum()
return sum_of_facings, space_length
def calculate_bay_kpi(self):
bay_kpi_sheet = self.bay_count_kpi
kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] == BAY_COUNT_KPI]
if kpi.empty:
Log.info("CCAAU Calculate KPI Name:{} not found in DB".format(BAY_COUNT_KPI))
else:
Log.info("CCAAU Calculate KPI Name:{} found in DB".format(BAY_COUNT_KPI))
bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] == BAY_COUNT_KPI]
if not bay_kpi_row.empty:
scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0]
if scene_types_to_consider == '*':
# Consider all scene types
scene_types_to_consider = 'all'
else:
scene_types_to_consider = [x.strip() for x in scene_types_to_consider.split(',')]
mpis_with_scene = self.match_product_in_scene.merge(self.scene_info, how='left', on='scene_fk')
mpis_with_scene_and_template = mpis_with_scene.merge(self.templates, how='left', on='template_fk')
if scene_types_to_consider != 'all':
mpis_with_scene_and_template = mpis_with_scene_and_template[
mpis_with_scene_and_template['template_name'].isin(scene_types_to_consider)]
mpis_template_group = mpis_with_scene_and_template.groupby('template_fk')
for template_fk, template_data in mpis_template_group:
Log.info("Running for template ID {templ_id}".format(
templ_id=template_fk,
))
total_bays_for_scene_type = 0
scene_group = template_data.groupby('scene_fk')
for scene_fk, scene_data in scene_group:
Log.info("KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}".format(
kpi=BAY_COUNT_KPI,
bay_c=int(scene_data['bay_number'].max()),
scene_id=scene_fk,
))
total_bays_for_scene_type += int(scene_data['bay_number'].max())
Log.info("KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}".format(
kpi=BAY_COUNT_KPI,
bay_c=total_bays_for_scene_type,
templ_id=template_fk,
))
self.common_v2.write_to_db_result(
fk=int(kpi['pk'].iloc[0]),
numerator_id=int(template_fk),
numerator_result=total_bays_for_scene_type,
denominator_id=int(self.store_id),
denominator_result=total_bays_for_scene_type,
result=total_bays_for_scene_type,
)
def calculate_assortment_kpis(self):
Log.info("Calculate assortment kpis for session: {}".format(self.session_uid))
# get filter data starts
valid_scif = self.scif[self.scif['facings'] != 0]
if not self.assortment_data.empty:
# exclusions start
scenes_to_exclude = self.assortment_data.iloc[0].scene_types_to_exclude
categories_to_exclude = self.assortment_data.iloc[0].categories_to_exclude
brands_to_exclude = self.assortment_data.iloc[0].brands_to_exclude
ean_codes_to_exclude = self.assortment_data.iloc[0].ean_codes_to_exclude
if scenes_to_exclude and not is_nan(scenes_to_exclude):
scenes_to_exclude = [x.strip() for x in scenes_to_exclude.split(',') if x]
Log.info("Exclude scenes: {} for session: {}".format(scenes_to_exclude, self.session_uid))
valid_scif = valid_scif[~(self.scif['template_name'].isin(scenes_to_exclude))]
if categories_to_exclude and not is_nan(categories_to_exclude):
categories_to_exclude = [x.strip() for x in categories_to_exclude.split(',') if x]
Log.info("Exclude categories: {} for session: {}".format(categories_to_exclude, self.session_uid))
valid_scif = valid_scif[~(valid_scif['category_local_name'].isin(categories_to_exclude))]
if brands_to_exclude and not is_nan(brands_to_exclude):
brands_to_exclude = [x.strip() for x in brands_to_exclude.split(',') if x]
Log.info("Exclude brands: {} for session: {}".format(brands_to_exclude, self.session_uid))
valid_scif = valid_scif[~(valid_scif['brand_local_name'].isin(brands_to_exclude))]
if ean_codes_to_exclude and not is_nan(ean_codes_to_exclude):
ean_codes_to_exclude = [x.strip() for x in ean_codes_to_exclude.split(',') if x]
Log.info("Exclude EAN Codes: {} for session: {}".format(ean_codes_to_exclude, self.session_uid))
valid_scif = valid_scif[~(valid_scif['product_ean_code'].isin(ean_codes_to_exclude))]
# get filter data ends
distribution_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == DST_MAN_BY_STORE_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
oos_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == OOS_MAN_BY_STORE_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
prod_presence_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == PRODUCT_PRESENCE_BY_STORE_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
prod_presence_re_kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] == PRODUCT_PRESENCE_KPI]
oos_prod_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == OOS_PRODUCT_BY_STORE_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
# Category based Assortments
distribution_by_cat_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == DST_MAN_BY_CATEGORY_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
oos_by_cat_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == OOS_MAN_BY_CATEGORY_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
prod_presence_by_cat_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] ==
PRODUCT_PRESENCE_BY_CATEGORY_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
oos_by_cat_prod_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == OOS_MAN_BY_CATEGORY_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
def __return_valid_store_policies(policy):
valid_store = True
policy_json = json.loads(policy)
# special case where its only one assortment for all
# that is there is only one key and it is is_active => Y
if len(policy_json) == 1 and policy_json.get('is_active') == ['Y']:
return valid_store
store_json = json.loads(self.store_info.reset_index().to_json(orient='records'))[0]
# map the necessary keys to those names knows
for policy_value, store_info_value in POLICY_STORE_MAP.iteritems():
if policy_value in policy_json:
policy_json[store_info_value] = policy_json.pop(policy_value)
for key, values in policy_json.iteritems():
if str(store_json.get(key, 'is_active')) in values:
continue
else:
valid_store = False
break
return valid_store
policy_data = self.get_policies(distribution_kpi.iloc[0].pk)
if policy_data.empty:
Log.info("No Assortments Loaded.")
return 0
resp = policy_data['policy'].apply(__return_valid_store_policies)
valid_policy_data = policy_data[resp]
if valid_policy_data.empty:
Log.info("No policy applicable for session {sess} and kpi {kpi}.".format(
sess=self.session_uid,
kpi=distribution_kpi.iloc[0].type))
return 0
# calculate and save the percentage values for distribution and oos
self.calculate_and_save_distribution_and_oos(
valid_scif=valid_scif,
assortment_product_fks=valid_policy_data['product_fk'],
distribution_kpi_fk=distribution_kpi.iloc[0].pk,
oos_kpi_fk=oos_kpi.iloc[0].pk
)
# calculate and save prod presence and oos products
self.calculate_and_save_prod_presence_and_oos_products(
valid_scif=valid_scif,
assortment_product_fks=valid_policy_data['product_fk'],
prod_presence_kpi_fk=prod_presence_kpi.iloc[0].pk,
oos_prod_kpi_fk=oos_prod_kpi.iloc[0].pk,
distribution_kpi_name=DST_MAN_BY_STORE_PERC,
oos_kpi_name=OOS_MAN_BY_STORE_PERC,
prod_presence_re_kpi=prod_presence_re_kpi.iloc[0].pk
)
# calculate and save the percentage values for distribution and oos
self.calculate_and_save_distribution_and_oos_category(
valid_scif=valid_scif,
assortment_product_fks=valid_policy_data['product_fk'],
distribution_kpi_fk=distribution_by_cat_kpi.iloc[0].pk,
oos_kpi_fk=oos_by_cat_kpi.iloc[0].pk
)
# calculate and save prod presence and oos products
self.calculate_and_save_prod_presence_and_oos_products_category(
valid_scif=valid_scif,
assortment_product_fks=valid_policy_data['product_fk'],
prod_presence_kpi_fk=prod_presence_by_cat_kpi.iloc[0].pk,
oos_prod_kpi_fk=oos_by_cat_prod_kpi.iloc[0].pk,
distribution_kpi_name=DST_MAN_BY_CATEGORY_PERC,
oos_kpi_name=OOS_MAN_BY_CATEGORY_PERC
)
def calculate_and_save_prod_presence_and_oos_products(self, valid_scif, assortment_product_fks,
prod_presence_kpi_fk, oos_prod_kpi_fk,
distribution_kpi_name, oos_kpi_name,
prod_presence_re_kpi):
# all assortment products are only in own manufacturers context;
# but we have the products and hence no need to filter out denominator
Log.info("Calculate product presence and OOS products for {}".format(self.project_name))
total_products_in_scene = valid_scif["item_id"].unique()
total_own_man_products_in_scene = valid_scif[valid_scif['manufacturer_fk']
==self.own_manufacturer_fk]["item_id"].unique()
present_products = np.intersect1d(total_products_in_scene, assortment_product_fks)
extra_products = np.setdiff1d(total_own_man_products_in_scene, present_products)
oos_products = np.setdiff1d(assortment_product_fks, present_products)
product_map = {
OOS_CODE: oos_products,
PRESENT_CODE: present_products,
EXTRA_CODE: extra_products
}
# save product presence; with distribution % kpi as parent
for assortment_code, product_fks in product_map.iteritems():
for each_fk in product_fks:
self.common_v2.write_to_db_result(fk=prod_presence_kpi_fk,
numerator_id=each_fk,
denominator_id=self.store_id,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(assortment_code),
identifier_parent="{}_{}".format(distribution_kpi_name,
self.store_id),
should_enter=True
)
self.common_v2.write_to_db_result(fk=prod_presence_re_kpi,
numerator_id=each_fk,
denominator_id=self.store_id,
context_id=self.all_products[self.all_products['product_fk']
==each_fk]['category_fk'].iloc[0],
numerator_result=assortment_code,
denominator_result=1,
result=assortment_code,
score=assortment_code
)
if assortment_code == OOS_CODE:
# save OOS products; with OOS % kpi as parent
for each_fk in product_fks:
self.common_v2.write_to_db_result(fk=oos_prod_kpi_fk,
numerator_id=each_fk,
denominator_id=self.store_id,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(assortment_code),
identifier_parent="{}_{}".format(oos_kpi_name,
self.store_id),
should_enter=True
)
def calculate_and_save_distribution_and_oos(self, valid_scif, assortment_product_fks,
distribution_kpi_fk, oos_kpi_fk):
"""Function to calculate distribution and OOS percentage.
Saves distribution and oos percentage as values.
"""
Log.info("Calculate distribution and OOS for {}".format(self.project_name))
scene_products = pd.Series(valid_scif["item_id"].unique())
total_products_in_assortment = len(assortment_product_fks)
count_of_assortment_prod_in_scene = assortment_product_fks.isin(scene_products).sum()
oos_count = total_products_in_assortment - count_of_assortment_prod_in_scene
# count of lion sku / all sku assortment count
if not total_products_in_assortment:
Log.info("No assortments applicable for session {sess}.".format(sess=self.session_uid))
return 0
distribution_perc = count_of_assortment_prod_in_scene / float(total_products_in_assortment)
oos_perc = 1 - distribution_perc
self.common_v2.write_to_db_result(fk=distribution_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=count_of_assortment_prod_in_scene,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=distribution_perc,
score=distribution_perc,
identifier_result="{}_{}".format(DST_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True
)
self.common_v2.write_to_db_result(fk=oos_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=oos_count,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=oos_perc,
score=oos_perc,
identifier_result="{}_{}".format(OOS_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True
)
def calculate_and_save_prod_presence_and_oos_products_category(self, valid_scif, assortment_product_fks,
prod_presence_kpi_fk, oos_prod_kpi_fk,
distribution_kpi_name, oos_kpi_name):
# all assortment products are only in own manufacturers context;
# but we have the products and hence no need to filter out denominator
Log.info("Calculate product presence and OOS products per Category for {}".format(self.project_name))
scene_category_group = valid_scif.groupby('category_fk')
for category_fk, each_scif_data in scene_category_group:
total_products_in_scene_for_cat = each_scif_data["item_id"].unique()
total_own_man_products_in_scene_for_cat = each_scif_data[each_scif_data['manufacturer_fk']
==self.own_manufacturer_fk]["item_id"].unique()
curr_category_products_in_assortment_df = self.all_products[
(self.all_products.product_fk.isin(assortment_product_fks))
& (self.all_products.category_fk == category_fk)]
curr_category_products_in_assortment = curr_category_products_in_assortment_df['product_fk'].unique()
present_products = np.intersect1d(total_products_in_scene_for_cat, curr_category_products_in_assortment)
extra_products = np.setdiff1d(total_own_man_products_in_scene_for_cat, present_products)
oos_products = np.setdiff1d(curr_category_products_in_assortment, present_products)
product_map = {
OOS_CODE: oos_products,
PRESENT_CODE: present_products,
EXTRA_CODE: extra_products
}
# save product presence; with distribution % kpi as parent
for assortment_code, product_fks in product_map.iteritems():
for each_fk in product_fks:
self.common_v2.write_to_db_result(fk=prod_presence_kpi_fk,
numerator_id=each_fk,
denominator_id=category_fk,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(assortment_code),
identifier_parent="{}_{}".format(distribution_kpi_name,
category_fk),
should_enter=True
)
if assortment_code == OOS_CODE:
# save OOS products; with OOS % kpi as parent
for each_fk in product_fks:
self.common_v2.write_to_db_result(fk=oos_prod_kpi_fk,
numerator_id=each_fk,
denominator_id=category_fk,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(
assortment_code),
identifier_parent="{}_{}".format(oos_kpi_name,
category_fk),
should_enter=True
)
def calculate_and_save_distribution_and_oos_category(self, valid_scif, assortment_product_fks,
distribution_kpi_fk, oos_kpi_fk):
"""Function to calculate distribution and OOS percentage by Category.
Saves distribution and oos percentage as values.
"""
Log.info("Calculate distribution and OOS per Category for {}".format(self.project_name))
scene_category_group = valid_scif.groupby('category_fk')
for category_fk, each_scif_data in scene_category_group:
scene_products = pd.Series(each_scif_data["item_id"].unique())
# find products in assortment belonging to categor_fk
curr_category_products_in_assortment = len(self.all_products[
(self.all_products.product_fk.isin(assortment_product_fks))
& (self.all_products.category_fk == category_fk)])
count_of_assortment_prod_in_scene = assortment_product_fks.isin(scene_products).sum()
oos_count = curr_category_products_in_assortment - count_of_assortment_prod_in_scene
# count of lion sku / all sku assortment count
if not curr_category_products_in_assortment:
Log.info("No products from assortment with category: {cat} found in session {sess}.".format(
cat=category_fk,
sess=self.session_uid))
distribution_perc = 0
continue
else:
Log.info("Found assortment products with category: {cat} in session {sess}.".format(
cat=category_fk,
sess=self.session_uid))
distribution_perc = count_of_assortment_prod_in_scene / float(
curr_category_products_in_assortment)
oos_perc = 1 - distribution_perc
self.common_v2.write_to_db_result(fk=distribution_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=count_of_assortment_prod_in_scene,
denominator_id=category_fk,
denominator_result=curr_category_products_in_assortment,
context_id=self.store_id,
result=distribution_perc,
score=distribution_perc,
identifier_result="{}_{}".format(DST_MAN_BY_CATEGORY_PERC,
category_fk),
should_enter=True
)
self.common_v2.write_to_db_result(fk=oos_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=oos_count,
denominator_id=category_fk,
denominator_result=curr_category_products_in_assortment,
context_id=self.store_id,
result=oos_perc,
score=oos_perc,
identifier_result="{}_{}".format(OOS_MAN_BY_CATEGORY_PERC,
category_fk),
should_enter=True
)
def calculate_prod_delta_prev_session(self):
prod_delt_kpi = self.kpi_static_data[(self.kpi_static_data[KPI_TYPE_COL] == DELTA_KPI)
& (self.kpi_static_data['delete_time'].isnull())]
if prod_delt_kpi.empty:
Log.warning("Cannot find KPI {}".format(DELTA_KPI))
return True
Log.info("Calculating KPI: {kpi} for session: {sess}".format(
kpi=DELTA_KPI,
sess=self.session_uid
))
prev_session_df = self.get_previous_session()
if prev_session_df.empty:
Log.warning("No previous session for {}".format(self.session_uid))
return True
prev_session_uid = prev_session_df.iloc[0].session_uid
Log.info("Get delta products for session: {cur} with previous: {prev}".format(cur=self.session_uid,
prev=prev_session_uid))
prev_session_own_man_prods = self.get_scif_own_man_products_of_session(prev_session_uid)
# prev_session_own_man_prods
current_sess_own_man_prods = self.scif[
(self.scif.facings != 0) &
(self.scif['manufacturer_fk'] == self.own_manufacturer_fk)
]['item_id']
# delta_prods are the own manufacturer ones present in prev session
# but not present in current session
delta_prods = np.setdiff1d(prev_session_own_man_prods['product_fk'], current_sess_own_man_prods)
for each_product_fk in delta_prods:
Log.info("For session: {ses}, product: {pr} is a delta [present in prev: {prev}].".format(
ses=self.session_uid,
pr=each_product_fk,
prev=prev_session_uid
))
self.common_v2.write_to_db_result(
fk=int(prod_delt_kpi['pk'].iloc[0]),
numerator_id=int(each_product_fk),
numerator_result=prev_session_df.iloc[0].pk,
denominator_id=int(self.store_id),
context_id=self.all_products[self.all_products['product_fk']
== each_product_fk]['category_fk'].iloc[0],
result=1,
score=1,
)
def get_policies(self, kpi_fk):
query = """ select a.kpi_fk, p.policy_name, p.policy, atag.assortment_group_fk,
atp.assortment_fk, atp.product_fk, atp.start_date, atp.end_date
from pservice.assortment_to_product atp
join pservice.assortment_to_assortment_group atag on atp.assortment_fk = atag.assortment_fk
join pservice.assortment a on a.pk = atag.assortment_group_fk
join pservice.policy p on p.pk = a.store_policy_group_fk
where a.kpi_fk={kpi_fk}
AND '{sess_date}' between atp.start_date AND atp.end_date;
"""
policies = pd.read_sql_query(query.format(kpi_fk=kpi_fk,
sess_date=self.session_info.iloc[0].visit_date),
self.rds_conn.db)
return policies
def get_previous_session(self):
query = """
SELECT
pk, session_uid
FROM
probedata.session
WHERE
store_fk = (SELECT
store_fk
FROM
probedata.session
WHERE
session_uid = '{}')
ORDER BY visit_date DESC
LIMIT 1 , 1;
""".format(self.session_uid)
previous_session = pd.read_sql_query(query, self.rds_conn.db)
Log.info("Getting previous session for {c}: => {p}".format(
c=self.session_uid,
p=previous_session
))
return previous_session
def get_scif_own_man_products_of_session(self, session_uid):
Log.info("Getting previous sessions own manufacturer products")
query = """
SELECT
item_id as product_fk
FROM
reporting.scene_item_facts scif
JOIN
probedata.session sess ON sess.pk = scif.session_id
JOIN
static_new.product prod ON prod.pk = scif.item_id
JOIN
static_new.brand brand ON brand.pk = prod.brand_fk
WHERE
sess.session_uid = '{session_uid}'
AND facings <> 0
AND prod.type='SKU'
AND prod.is_active = 1
AND brand.manufacturer_fk = {manuf};
""".format(
session_uid=session_uid,
manuf=self.own_manufacturer_fk
)
product_df = pd.read_sql_query(query, self.rds_conn.db)
return product_df
class PepsicoUtil(UnifiedKPISingleton):
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUSION_TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Inclusion_Exclusion_Template_Rollout.xlsx')
ADDITIONAL_DISPLAY = 'additional display'
INCLUDE_EMPTY = True
EXCLUDE_EMPTY = False
OPERATION_TYPES = []
SOS_VS_TARGET = 'SOS vs Target'
HERO_SKU_SPACE_TO_SALES_INDEX = 'Hero SKU Space to Sales Index'
HERO_SKU_SOS_VS_TARGET = 'Hero SKU SOS vs Target'
LINEAR_SOS_INDEX = 'Linear SOS Index'
PEPSICO = 'PEPSICO'
SHELF_PLACEMENT = 'Shelf Placement'
HERO_SKU_PLACEMENT_TOP = 'Hero SKU Placement by shelf numbers_Top'
HERO_PLACEMENT = 'Hero Placement'
HERO_SKU_STACKING = 'Hero SKU Stacking'
HERO_SKU_PRICE = 'Hero SKU Price'
HERO_SKU_PROMO_PRICE = 'Hero SKU Promo Price'
BRAND_FULL_BAY_KPIS = ['Brand Full Bay_90', 'Brand Full Bay']
BRAND_FULL_BAY = 'Brand Full Bay'
HERO_PREFIX = 'Hero SKU'
ALL = 'ALL'
HERO_SKU_OOS_SKU = 'Hero SKU OOS - SKU'
HERO_SKU_OOS = 'Hero SKU OOS'
HERO_SKU_AVAILABILITY = 'Hero SKU Availability'
BRAND_SPACE_TO_SALES_INDEX = 'Brand Space to Sales Index'
BRAND_SPACE_SOS_VS_TARGET = 'Brand Space SOS vs Target'
SUB_BRAND_SPACE_TO_SALES_INDEX = 'Sub Brand Space to Sales Index'
SUB_BRAND_SPACE_SOS_VS_TARGET = 'Sub Brand Space SOS vs Target'
PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Segment Space to Sales Index'
PEPSICO_SEGMENT_SOS_VS_TARGET = 'PepsiCo Segment SOS vs Target'
PEPSICO_SUB_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Sub Segment Space to Sales Index'
PEPSICO_SUB_SEGMENT_SOS_VS_TARGET = 'PepsiCo Sub Segment SOS vs Target'
PLACEMENT_BY_SHELF_NUMBERS_TOP = 'Placement by shelf numbers_Top'
TOTAL_LINEAR_SPACE = 'Total Linear Space'
NUMBER_OF_FACINGS = 'Number of Facings'
NUMBER_OF_BAYS = 'Number of bays'
NUMBER_OF_SHELVES = 'Number of shelves'
PRODUCT_BLOCKING = 'Product Blocking'
PRODUCT_BLOCKING_ADJACENCY = 'Product Blocking Adjacency'
SHELF_PLACEMENT_VERTICAL_LEFT = 'Shelf Placement Vertical_Left'
SHELF_PLACEMENT_VERTICAL_CENTER = 'Shelf Placement Vertical_Center'
SHELF_PLACEMENT_VERTICAL_RIGHT = 'Shelf Placement Vertical_Right'
NUMBER_OF_SHELVES_TEMPL_COLUMN = 'No of Shelves in Fixture (per bay) (key)'
RELEVANT_SHELVES_TEMPL_COLUMN = 'Shelves From Bottom To Include (data)'
SHELF_PLC_TARGETS_COLUMNS = [
'kpi_operation_type_fk', 'operation_type', 'kpi_level_2_fk', 'type',
NUMBER_OF_SHELVES_TEMPL_COLUMN, RELEVANT_SHELVES_TEMPL_COLUMN,
'KPI Parent'
]
SHELF_PLC_TARGET_COL_RENAME = {
'kpi_operation_type_fk_x': 'kpi_operation_type_fk',
'operation_type_x': 'operation_type',
'kpi_level_2_fk_x': 'kpi_level_2_fk',
'type_x': 'type',
NUMBER_OF_SHELVES_TEMPL_COLUMN + '_x': NUMBER_OF_SHELVES_TEMPL_COLUMN,
RELEVANT_SHELVES_TEMPL_COLUMN + '_x': RELEVANT_SHELVES_TEMPL_COLUMN,
'KPI Parent_x': 'KPI Parent'
}
HERO_SKU_AVAILABILITY_SKU = 'Hero SKU Availability - SKU'
HERO_SKU_PLACEMENT_BY_SHELF_NUMBERS = 'Hero SKU Placement by shelf numbers'
HERO_SKU_AVAILABILITY_BY_HERO_TYPE = 'Hero SKU Availability by Hero Type'
SHARE_OF_ASSORTMENT_BY_HERO_TYPE = 'Share of Assortment by Hero Type'
HERO_SKU_LABEL = 'Hero SKU'
HERO_TYPE = 'hero_type'
HERO_SKU_SOS_OF_CAT_BY_HERO_TYPE = 'Hero SKU SOS of Category by Hero Type'
CATEGORY_FULL_BAY = 'Category Full Bay'
CSN = 'CSN'
PRICE = 'Price'
PROMO_PRICE = 'Promo Price'
LINEAR_SPACE_PER_PRODUCT = 'Linear Space Per Product'
FACINGS_PER_PRODUCT = 'Facings per Product'
PRICE_SCENE = 'Price Scene'
PROMO_PRICE_SCENE = 'Promo Price Scene'
HERO_SKU_SOS = 'Hero SKU SOS'
BRAND_SOS = 'Brand SOS'
SUB_BRAND_SOS = 'Sub Brand SOS'
PEPSICO_SEGMENT_SOS = 'PepsiCo Segment SOS'
BRAND_SOS_OF_SEGMENT = 'Brand SOS of Segment'
BINS_NOT_RECOGNIZED = 'Bins_not_recognized'
def __init__(self, output, data_provider):
super(PepsicoUtil, self).__init__(data_provider)
self.output = output
self.common = Common(self.data_provider)
# self.common_v1 = CommonV1(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] if self.data_provider[Data.STORE_FK] is not None \
else self.session_info['store_fk'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.display_scene = self.get_match_display_in_scene()
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.probe_groups = self.get_probe_group()
self.match_product_in_scene = self.match_product_in_scene.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.toolbox = GENERALToolBox(self.data_provider)
self.commontools = PEPSICOUKCommonToolBox(self.data_provider,
self.rds_conn)
self.all_templates = self.commontools.all_templates
self.custom_entities = self.commontools.custom_entities
self.on_display_products = self.commontools.on_display_products
self.exclusion_template = self.commontools.exclusion_template
self.filtered_scif = self.commontools.filtered_scif.copy()
self.filtered_matches = self.commontools.filtered_matches.copy()
self.filtered_matches = self.filtered_matches.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy(
)
self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy(
)
self.scene_bay_shelf_product = self.commontools.scene_bay_shelf_product
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.full_store_info = self.commontools.full_store_info.copy()
self.external_targets = self.commontools.external_targets
self.assortment = Assortment(self.commontools.data_provider,
self.output)
self.lvl3_ass_result = self.get_lvl3_relevant_assortment_result()
self.own_manuf_fk = self.all_products[
self.all_products['manufacturer_name'] ==
self.PEPSICO]['manufacturer_fk'].values[0]
self.scene_kpi_results = self.get_results_of_scene_level_kpis()
self.kpi_results_check = pd.DataFrame(columns=[
'kpi_fk', 'numerator', 'denominator', 'result', 'score', 'context'
])
self.sos_vs_target_targets = self.construct_sos_vs_target_base_df()
self.all_targets_unpacked = self.commontools.all_targets_unpacked.copy(
)
self.block_results = pd.DataFrame(columns=['Group Name', 'Score'])
self.hero_type_custom_entity_df = self.get_hero_type_custom_entity_df()
def get_match_display_in_scene(self):
query = PEPSICOUK_Queries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def get_probe_group(self):
query = PEPSICOUK_Queries.get_probe_group(self.session_uid)
probe_group = pd.read_sql_query(query, self.rds_conn.db)
return probe_group
@staticmethod
def get_full_bay_and_positional_filters(parameters):
filters = {parameters['Parameter 1']: parameters['Value 1']}
if parameters['Parameter 2']:
filters.update({parameters['Parameter 2']: parameters['Value 2']})
if parameters['Parameter 3']:
filters.update({parameters['Parameter 3']: parameters['Value 3']})
return filters
# @staticmethod
# def get_stack_data(row):
# is_stack = False
# sequences_list = row['all_sequences'][0:-1].split(',')
# count_sequences = collections.Counter(sequences_list)
# repeating_items = [c > 1 for c in count_sequences.values()]
# if repeating_items:
# if any(repeating_items):
# is_stack = True
# return is_stack
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), str(value).split(','))
def construct_sos_vs_target_base_df(self):
sos_targets = self.get_relevant_sos_vs_target_kpi_targets()
sos_targets = sos_targets.drop_duplicates(subset=[
'kpi_operation_type_fk', 'kpi_level_2_fk', 'numerator_value',
'denominator_value', 'type'
],
keep='first')
sos_targets = sos_targets.drop(
['key_json', 'data_json', 'start_date', 'end_date'], axis=1)
if not sos_targets.empty:
sos_targets['numerator_id'] = sos_targets.apply(
self.retrieve_relevant_item_pks,
axis=1,
args=('numerator_type', 'numerator_value'))
sos_targets['denominator_id'] = sos_targets.apply(
self.retrieve_relevant_item_pks,
axis=1,
args=('denominator_type', 'denominator_value'))
sos_targets['identifier_parent'] = sos_targets['KPI Parent'].apply(
lambda x: self.common.get_dictionary(kpi_fk=int(float(x))))
return sos_targets
def get_relevant_sos_vs_target_kpi_targets(self, brand_vs_brand=False):
sos_vs_target_kpis = self.external_targets[
self.external_targets['operation_type'] == self.SOS_VS_TARGET]
sos_vs_target_kpis = sos_vs_target_kpis.drop_duplicates(subset=[
'operation_type', 'kpi_level_2_fk', 'key_json', 'data_json'
])
relevant_targets_df = pd.DataFrame(
columns=sos_vs_target_kpis.columns.values.tolist())
if not sos_vs_target_kpis.empty:
policies_df = self.commontools.unpack_external_targets_json_fields_to_df(
sos_vs_target_kpis, field_name='key_json')
policy_columns = policies_df.columns.values.tolist()
del policy_columns[policy_columns.index('pk')]
store_dict = self.full_store_info.to_dict('records')[0]
for column in policy_columns:
store_att_value = store_dict.get(column)
policies_df = policies_df[policies_df[column].isin(
[store_att_value, self.ALL])]
kpi_targets_pks = policies_df['pk'].values.tolist()
relevant_targets_df = sos_vs_target_kpis[
sos_vs_target_kpis['pk'].isin(kpi_targets_pks)]
# relevant_targets_df = relevant_targets_df.merge(policies_df, on='pk', how='left')
data_json_df = self.commontools.unpack_external_targets_json_fields_to_df(
relevant_targets_df, 'data_json')
relevant_targets_df = relevant_targets_df.merge(data_json_df,
on='pk',
how='left')
kpi_data = self.kpi_static_data[['pk', 'type']].drop_duplicates()
kpi_data.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True)
relevant_targets_df = relevant_targets_df.merge(
kpi_data,
left_on='kpi_level_2_fk',
right_on='kpi_level_2_fk',
how='left')
linear_sos_fk = self.common.get_kpi_fk_by_kpi_type(
self.LINEAR_SOS_INDEX)
if brand_vs_brand:
relevant_targets_df = relevant_targets_df[
relevant_targets_df['KPI Parent'] == linear_sos_fk]
else:
relevant_targets_df = relevant_targets_df[~(
relevant_targets_df['KPI Parent'] == linear_sos_fk)]
return relevant_targets_df
def retrieve_relevant_item_pks(self, row, type_field_name,
value_field_name):
try:
if row[type_field_name].endswith("_fk"):
item_id = row[value_field_name]
else:
# print row[type_field_name], ' :', row[value_field_name]
item_id = self.custom_entities[
self.custom_entities['name'] ==
row[value_field_name]]['pk'].values[0]
except KeyError as e:
Log.error('No id found for field {}. Error: {}'.format(
row[type_field_name], e))
item_id = None
return item_id
def calculate_sos(self, sos_filters, **general_filters):
numerator_linear = self.calculate_share_space(
**dict(sos_filters, **general_filters))
denominator_linear = self.calculate_share_space(**general_filters)
return float(numerator_linear), float(denominator_linear)
def calculate_share_space(self, **filters):
filtered_scif = self.filtered_scif[self.toolbox.get_filter_condition(
self.filtered_scif, **filters)]
space_length = filtered_scif['updated_gross_length'].sum()
return space_length
def add_kpi_result_to_kpi_results_df(self, result_list):
self.kpi_results_check.loc[len(self.kpi_results_check)] = result_list
def get_results_of_scene_level_kpis(self):
scene_kpi_results = pd.DataFrame()
if not self.scene_info.empty:
scene_kpi_results = self.ps_data.get_scene_results(
self.scene_info['scene_fk'].drop_duplicates().values)
return scene_kpi_results
def get_store_data_by_store_id(self):
store_id = self.store_id if self.store_id else self.session_info[
'store_fk'].values[0]
query = PEPSICOUK_Queries.get_store_data_by_store_id(store_id)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_facings_scene_bay_shelf_product(self):
self.filtered_matches['count'] = 1
aggregate_df = self.filtered_matches.groupby(
['scene_fk', 'bay_number', 'shelf_number', 'product_fk'],
as_index=False).agg({'count': np.sum})
return aggregate_df
def get_lvl3_relevant_assortment_result(self):
assortment_result = self.assortment.get_lvl3_relevant_ass()
# if assortment_result.empty:
# return assortment_result
# products_in_session = self.filtered_scif.loc[self.filtered_scif['facings'] > 0]['product_fk'].values
# assortment_result.loc[assortment_result['product_fk'].isin(products_in_session), 'in_store'] = 1
return assortment_result
@staticmethod
def get_block_and_adjacency_filters(target_series):
filters = {target_series['Parameter 1']: target_series['Value 1']}
if target_series['Parameter 2']:
filters.update(
{target_series['Parameter 2']: target_series['Value 2']})
if target_series['Parameter 3']:
filters.update(
{target_series['Parameter 3']: target_series['Value 3']})
return filters
@staticmethod
def get_block_filters(target_series):
if isinstance(target_series['Value 1'], list):
filters = {target_series['Parameter 1']: target_series['Value 1']}
else:
filters = {
target_series['Parameter 1']: [target_series['Value 1']]
}
if target_series['Parameter 2']:
if isinstance(target_series['Value 2'], list):
filters.update(
{target_series['Parameter 2']: target_series['Value 2']})
else:
filters.update(
{target_series['Parameter 2']: [target_series['Value 2']]})
if target_series['Parameter 3']:
if isinstance(target_series['Value 2'], list):
filters.update(
{target_series['Parameter 3']: target_series['Value 3']})
else:
filters.update(
{target_series['Parameter 3']: [target_series['Value 3']]})
return filters
def reset_filtered_scif_and_matches_to_exclusion_all_state(self):
self.filtered_scif = self.commontools.filtered_scif.copy()
self.filtered_matches = self.commontools.filtered_matches.copy()
def reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(self):
self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy(
)
self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy(
)
def get_available_hero_sku_list(self, dependencies_df):
hero_list = dependencies_df[
(dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU)
& (dependencies_df['numerator_result'] == 1
)]['numerator_id'].unique().tolist()
return hero_list
def get_unavailable_hero_sku_list(self, dependencies_df):
hero_list = dependencies_df[
(dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU)
& (dependencies_df['numerator_result'] == 0
)]['numerator_id'].unique().tolist()
return hero_list
def get_hero_type_custom_entity_df(self):
hero_type_df = self.custom_entities[self.custom_entities['entity_type']
== self.HERO_TYPE]
hero_type_df.rename(columns={'pk': 'entity_fk'}, inplace=True)
return hero_type_df
class SOLARBRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_EMPTY = False
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
CONTAIN_FILTER = 2
EMPTY = 'Empty'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.commonV2 = CommonV2(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.k_engine = BaseCalculationsGroup(data_provider, output)
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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.commonV2.get_kpi_static_data()
self.kpi_results_queries = []
self.templates = {}
self.all_products = self.commonV2.data_provider[Data.ALL_PRODUCTS]
self.session_id = self.data_provider.session_id
self.score_templates = {}
self.get_templates()
self.get_score_template()
self.manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0]
self.sos = SOS(self.data_provider, self.output)
self.total_score = 0
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.toolbox = GENERALToolBox(self.data_provider)
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.kpi_results_new_tables_queries = []
def get_templates(self):
for sheet in Const.SHEETS_MAIN:
self.templates[sheet] = pd.read_excel(MAIN_TEMPLATE_PATH, sheetname=sheet.decode('utf8'),
keep_default_na=False)
def get_score_template(self):
for sheet in Const.SHEETS_SCORE:
self.score_templates[sheet] = pd.read_excel(SCORE_TEMPLATE_PATH, sheetname=sheet.decode('utf8'),
keep_default_na=False, encoding='utf8')
def main_calculation(self, *args, **kwargs):
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
self.calculate_main_kpi(main_line)
self.commonV2.commit_results_data()
def calculate_main_kpi(self, main_line):
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.Type]
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
exclude_template_groups = main_line[Const.EXCLUDED_TEMPLATE_GROUPS]
if exclude_template_groups != None:
self.scif = self.scif[~self.scif['template_group'].isin([exclude_template_groups])]
general_filters = {}
scif_template_groups = self.scif['template_group'].unique().tolist()
# encoding_fixed_list = [template_group.replace("\u2013","-") for template_group in scif_template_groups]
# scif_template_groups = encoding_fixed_list
store_type = self.store_info["store_type"].iloc[0]
store_types = self.does_exist(main_line, Const.STORE_TYPES)
if store_type in store_types:
if template_groups:
if ('All' in template_groups) or bool(set(scif_template_groups) & set(template_groups)):
if not ('All' in template_groups):
general_filters['template_group'] = template_groups
if kpi_type == Const.SOVI:
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[
relevant_template[Const.KPI_NAME].str.encode('utf-8') == kpi_name.encode('utf-8')]
if relevant_template["numerator param 1"].all() and relevant_template[
"denominator param 1"].all():
function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score = function(kpi_line, general_filters)
else:
pass
else:
pass
@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
def calculate_sos(self, kpi_line, general_filters):
kpi_name = kpi_line[Const.KPI_NAME]
# get denominator filters
for den_column in [col for col in kpi_line.keys() if Const.DEN_TYPE in col]: # get relevant den columns
if kpi_line[den_column]: # check to make sure this kpi has this denominator param
general_filters[kpi_line[den_column]] = \
kpi_line[den_column.replace(Const.DEN_TYPE, Const.DEN_VALUE)].split(
',') # get associated values
general_filters = self.convert_operators_to_values(general_filters)
sos_filters = {}
# get numerator filters
for num_column in [col for col in kpi_line.keys() if Const.NUM_TYPE in col]: # get numerator columns
if kpi_line[num_column]: # check to make sure this kpi has this numerator param
sos_filters[kpi_line[num_column]] = \
kpi_line[num_column.replace(Const.NUM_TYPE, Const.NUM_VALUE)].split(
',') # get associated values
sos_filters = self.convert_operators_to_values(sos_filters)
sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
sos_value = round(sos_value, 2)
score = self.get_score_from_range(kpi_name, sos_value)
manufacturer_products = self.all_products[
self.all_products['manufacturer_name'] == sos_filters['manufacturer_name'][0]].iloc[0]
manufacturer_fk = manufacturer_products["manufacturer_fk"]
filtered_kpi_list = self.kpi_static_data[
self.kpi_static_data['type'].str.encode('utf8') == kpi_name.encode('utf8')]
kpi_fk = filtered_kpi_list['pk'].iloc[0]
numerator_res, denominator_res = self.get_numerator_and_denominator(
sos_filters, **general_filters)
if numerator_res is None:
numerator_res = 0
denominator_fk = None
if general_filters.keys()[0] == 'category':
category_fk = self.all_products["category_fk"][
self.all_products['category'] == general_filters['category'][0]].iloc[0]
denominator_fk = category_fk
elif general_filters.keys()[0] == 'sub_category':
try:
sub_category_fk = self.all_products["sub_category_fk"][
self.all_products['sub_category'] == general_filters['sub_category'][0]].iloc[0]
denominator_fk = sub_category_fk
except:
sub_brand_fk = 999
denominator_fk = sub_brand_fk
elif general_filters.keys()[0] == 'sub_brand':
# sub brand table is empty, update when table is updated
try:
sub_brand_fk = self.all_products["sub_category_fk"][
self.all_products['sub_brand'] == general_filters['sub_brand'][0]].iloc[0]
except:
sub_brand_fk = 999
denominator_fk = sub_brand_fk
self.commonV2.write_to_db_result(fk=kpi_fk,
numerator_id=manufacturer_fk,
numerator_result=numerator_res,
denominator_id=denominator_fk,
denominator_result=denominator_res,
result=sos_value,
score=score,
score_after_actions=score,
context_id=kpi_fk)
return sos_value, score
def get_score_from_range(self, kpi_name, sos_value):
store_type = str(self.store_info["store_type"].iloc[0].encode('utf8'))
self.score_templates[store_type] = self.score_templates[store_type].replace(kpi_name,
kpi_name.encode('utf8').rstrip())
score_range = self.score_templates[store_type].query('Kpi == "' + str(kpi_name.encode('utf8')) +
'" & Low <= ' + str(sos_value) +
' & High >= ' + str(sos_value) + '')
try:
score = score_range['Score'].iloc[0]
except IndexError:
try:
Log.error('No score data found for KPI name {} in store type {}'.format(
kpi_name.encode('utf8'), store_type))
return 0
except UnicodeDecodeError:
Log.error('Unable to generate error for KPI name or store type with weird characters')
return 0
return score
def convert_operators_to_values(self, filters):
if 'number_of_sub_packages' in filters.keys():
value = filters['number_of_sub_packages']
operator, number = [x.strip() for x in re.split('(\d+)', value[0]) if x != '']
if operator == '>=':
subpackages_num = self.scif[self.scif['number_of_sub_packages'] >= int(
number)]['number_of_sub_packages'].unique().tolist()
filters['number_of_sub_packages'] = subpackages_num
elif operator == '<=':
subpackages_num = self.scif[self.scif['number_of_sub_packages'] <= int(
number)]['number_of_sub_packages'].unique().tolist()
filters['number_of_sub_packages'] = subpackages_num
elif operator == '>':
subpackages_num = self.scif[self.scif['number_of_sub_packages'] > int(
number)]['number_of_sub_packages'].unique().tolist()
filters['number_of_sub_packages'] = subpackages_num
elif operator == '<':
subpackages_num = self.scif[self.scif['number_of_sub_packages'] < int(
number)]['number_of_sub_packages'].unique().tolist()
filters['number_of_sub_packages'] = subpackages_num
return filters
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function .encode('utf-8')
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOVI:
return self.calculate_sos
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def round_result(result):
return round(result, 3)
def get_numerator_and_denominator(self, sos_filters=None, include_empty=False, **general_filters):
if include_empty == self.EXCLUDE_EMPTY and 'product_type' not in sos_filters.keys() + general_filters.keys():
general_filters['product_type'] = (self.EMPTY, self.EXCLUDE_FILTER)
pop_filter = self.toolbox.get_filter_condition(self.scif, **general_filters)
subset_filter = self.toolbox.get_filter_condition(self.scif, **sos_filters)
try:
pop = self.scif
filtered_population = pop[pop_filter]
if filtered_population.empty:
return 0, 0
else:
subset_population = filtered_population[subset_filter]
df = filtered_population
subset_df = subset_population
sum_field = Fd.FACINGS
try:
Validation.is_empty_df(df)
Validation.is_empty_df(subset_df)
Validation.is_subset(df, subset_df)
Validation.df_columns_equality(df, subset_df)
Validation.validate_columns_exists(df, [sum_field])
Validation.validate_columns_exists(subset_df, [sum_field])
Validation.is_none(sum_field)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
default_value = 0
numerator = TBox.calculate_frame_column_sum(subset_df, sum_field, default_value)
denominator = TBox.calculate_frame_column_sum(df, sum_field, default_value)
return numerator, denominator
except Exception as e:
Log.error(e.message)
return True
class PEPSICOUKCommonToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUSION_TEMPLATE_PATH = os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Inclusion_Exclusion_Template_Rollout.xlsx')
DISPLAY_TEMPLATE_PATH = os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'display_template.xlsx')
ADDITIONAL_DISPLAY = 'additional display'
STOCK = 'stock'
INCLUDE_EMPTY = True
EXCLUDE_EMPTY = False
OPERATION_TYPES = []
SOS_VS_TARGET = 'SOS vs Target'
HERO_SKU_SPACE_TO_SALES_INDEX = 'Hero SKU Space to Sales Index'
HERO_SKU_SOS_VS_TARGET = 'Hero SKU SOS vs Target'
LINEAR_SOS_INDEX = 'Linear SOS Index'
PEPSICO = 'PEPSICO'
SHELF_PLACEMENT = 'Shelf Placement'
HERO_SKU_PLACEMENT_TOP = 'Hero SKU Placement by shelf numbers_Top'
HERO_PLACEMENT = 'Hero Placement'
HERO_SKU_STACKING = 'Hero SKU Stacking'
HERO_SKU_PRICE = 'Hero SKU Price'
HERO_SKU_PROMO_PRICE = 'Hero SKU Promo Price'
BRAND_FULL_BAY_KPIS = ['Brand Full Bay 90', 'Brand Full Bay 100']
ALL = 'ALL'
DISPLAY_NAME_TEMPL = 'Display Name'
KPI_LOGIC = 'KPI Logic'
SHELF_LEN_DISPL = 'Shelf Length, m'
BAY_TO_SEPARATE = 'use bay to separate display'
BIN_TO_SEPARATE = 'use bin to separate display'
def __init__(self, data_provider, rds_conn=None):
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] # initial 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.all_templates = self.data_provider[Data.ALL_TEMPLATES]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] # initial scif
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) if rds_conn is None else rds_conn
self.complete_scif_data()
self.store_areas = self.get_store_areas()
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.full_store_info = self.get_store_data_by_store_id()
self.store_info_dict = self.full_store_info.to_dict('records')[0]
self.store_policy_exclusion_template = self.get_store_policy_data_for_exclusion_template()
self.displays_template = self.get_display_parameters()
self.toolbox = GENERALToolBox(data_provider)
self.custom_entities = self.get_custom_entity_data()
self.on_display_products = self.get_on_display_products()
self.exclusion_template = self.get_exclusion_template_data()
self.filtered_scif = self.scif # filtered scif acording to exclusion template
self.filtered_matches = self.match_product_in_scene # filtered scif according to exclusion template
self.set_filtered_scif_and_matches_for_all_kpis(self.scif, self.match_product_in_scene)
self.scene_bay_shelf_product = self.get_facings_scene_bay_shelf_product()
self.external_targets = self.get_all_kpi_external_targets()
self.all_targets_unpacked = self.unpack_all_external_targets()
self.kpi_result_values = self.get_kpi_result_values_df()
self.kpi_score_values = self.get_kpi_score_values_df()
# self.displays_template = self.get_display_parameters()
self.full_pallet_len = self.displays_template[self.displays_template[self.DISPLAY_NAME_TEMPL] == \
'HO Agreed Full Pallet'][self.SHELF_LEN_DISPL].values[0]
self.half_pallet_len = self.displays_template[self.displays_template[self.DISPLAY_NAME_TEMPL] == \
'HO Agreed Half Pallet'][self.SHELF_LEN_DISPL].values[0]
self.shelf_len_mixed_shelves = self.calculate_shelf_len_for_mixed_shelves()
self.scene_display = self.get_match_display_in_scene()
self.are_all_bins_tagged = self.check_if_all_bins_are_recognized()
self.filtered_scif_secondary = self.get_initial_secondary_scif()
self.filtered_matches_secondary = self.get_initial_secondary_matches()
if self.are_all_bins_tagged:
self.assign_bays_to_bins()
self.set_filtered_scif_and_matches_for_all_kpis_secondary(self.filtered_scif_secondary,
self.filtered_matches_secondary)
def check_if_all_bins_are_recognized(self):
tasks_with_bin_logic = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Bin') &
(self.displays_template[self.BAY_TO_SEPARATE] == 'No') &
(self.displays_template[self.BIN_TO_SEPARATE] == 'Yes')] \
[self.DISPLAY_NAME_TEMPL].unique()
scenes_with_bin_logic = set(self.scif[self.scif[ScifConsts.TEMPLATE_NAME].isin(tasks_with_bin_logic)]\
[ScifConsts.SCENE_FK].unique())
scenes_with_tagged_bins = set(self.scene_display[ScifConsts.SCENE_FK].unique()) if \
len(self.scene_display[ScifConsts.SCENE_FK].unique())>0 else set([0])
missing_bin_tags = scenes_with_bin_logic.difference(scenes_with_tagged_bins)
flag = False if missing_bin_tags else True
return flag
def add_sub_category_to_empty_and_other(self, scif, matches):
# exclude bin scenes
matches['include'] = 1
bin_scenes = self.displays_template[self.displays_template[self.KPI_LOGIC] == 'Bin'] \
[self.DISPLAY_NAME_TEMPL].values
scenes_excluding_bins = scif[~scif['template_name'].isin(bin_scenes)][ScifConsts.SCENE_FK].unique()
matches.loc[~matches[MatchesConsts.SCENE_FK].isin(scenes_excluding_bins), 'include'] = 0
mix_scenes = self.displays_template[self.displays_template[self.KPI_LOGIC] == 'Mix'] \
[self.DISPLAY_NAME_TEMPL].values
mix_scenes = scif[scif['template_name'].isin(mix_scenes)][ScifConsts.SCENE_FK].unique()
products_df = self.all_products[[ScifConsts.PRODUCT_FK, ScifConsts.SUB_CATEGORY_FK, ScifConsts.PRODUCT_TYPE,
ScifConsts.BRAND_FK, ScifConsts.CATEGORY_FK]]
matches = matches.merge(products_df, on=MatchesConsts.PRODUCT_FK, how='left')
matches['count_sub_cat'] = 1
scene_bay_shelves = self.match_product_in_scene.groupby([MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER],
as_index=False).agg({MatchesConsts.SHELF_NUMBER: np.max})
scene_bay_shelves.rename(columns={MatchesConsts.SHELF_NUMBER: 'max_shelf'}, inplace=True)
matches = matches.merge(scene_bay_shelves, on=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER],
how='left')
matches.loc[(matches[MatchesConsts.SCENE_FK].isin(mix_scenes)) &
(matches[MatchesConsts.SHELF_NUMBER] == matches['max_shelf']), 'include'] = 0
matches_sum = matches[matches['include'] == 1]
scene_bay_sub_cat_sum = matches_sum.groupby([MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER,
ScifConsts.SUB_CATEGORY_FK], as_index=False).agg(
{'count_sub_cat': np.sum})
scene_bay_sub_cat = scene_bay_sub_cat_sum.sort_values(by=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER,
'count_sub_cat'])
scene_bay_sub_cat = scene_bay_sub_cat.drop_duplicates(subset=[MatchesConsts.SCENE_FK,
MatchesConsts.BAY_NUMBER], keep='last')
scene_bay_sub_cat.rename(columns={ScifConsts.SUB_CATEGORY_FK: 'max_sub_cat',
'count_sub_cat': 'max_sub_cat_facings'}, inplace=True)
matches = matches.merge(scene_bay_sub_cat, on=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER], how='left')
matches.loc[((matches[ScifConsts.PRODUCT_TYPE].isin(['Empty', 'Other'])) &
(matches['include'] == 1)), ScifConsts.SUB_CATEGORY_FK] = \
matches['max_sub_cat']
matches['shelves_bay_before'] = None
matches['shelves_bay_after'] = None
if not matches.empty:
matches['shelves_bay_before'] = matches.apply(self.get_shelves_for_bay, args=(scene_bay_shelves, -1),
axis=1)
matches['shelves_bay_after'] = matches.apply(self.get_shelves_for_bay, args=(scene_bay_shelves, 1), axis=1)
matches[ScifConsts.SUB_CATEGORY_FK] = matches.apply(self.get_subcategory_from_neighbour_bays,
args=(scene_bay_sub_cat_sum,), axis=1)
matches = matches.drop(columns=[ScifConsts.PRODUCT_TYPE, ScifConsts.BRAND_FK, ScifConsts.CATEGORY_FK])
return scif, matches
def get_shelves_for_bay(self, row, scene_bay_shelves, bay_diff):
max_shelves_df = scene_bay_shelves[
(scene_bay_shelves[MatchesConsts.BAY_NUMBER] == row[MatchesConsts.BAY_NUMBER] + bay_diff) &
(scene_bay_shelves[MatchesConsts.SCENE_FK] == row[MatchesConsts.SCENE_FK])]
max_shelves = max_shelves_df['max_shelf'].values[0] if not max_shelves_df.empty else None
return max_shelves
def get_subcategory_from_neighbour_bays(self, row, scene_bay_sub_cat_sum):
subcat = row[ScifConsts.SUB_CATEGORY_FK]
if row['include'] == 1:
if (str(subcat) == 'nan' or subcat is None) and (row[ScifConsts.PRODUCT_TYPE] in ['Empty', 'Other']):
if (row['shelves_bay_before'] == row['max_shelf'] and row['shelves_bay_after'] == row['max_shelf']) or \
(row['shelves_bay_before'] != row['max_shelf'] and row['shelves_bay_after'] != row[
'max_shelf']):
reduced_df = scene_bay_sub_cat_sum[(scene_bay_sub_cat_sum[MatchesConsts.BAY_NUMBER].
isin([row[MatchesConsts.BAY_NUMBER] + 1, row[MatchesConsts.BAY_NUMBER] - 1])) &
(scene_bay_sub_cat_sum[ScifConsts.SCENE_FK] == row[
ScifConsts.SCENE_FK])]
subcat_df = reduced_df.groupby([ScifConsts.SUB_CATEGORY_FK], as_index=False).agg(
{'count_sub_cat': np.sum})
subcat = subcat_df[subcat_df['count_sub_cat'] == subcat_df['count_sub_cat'].max()] \
[ScifConsts.SUB_CATEGORY_FK].values[0] if not subcat_df.empty else None
elif row['shelves_bay_before'] == row['max_shelf']:
reduced_df = scene_bay_sub_cat_sum[(scene_bay_sub_cat_sum[MatchesConsts.BAY_NUMBER].
isin([row[MatchesConsts.BAY_NUMBER] - 1])) &
(scene_bay_sub_cat_sum[ScifConsts.SCENE_FK] == row[
ScifConsts.SCENE_FK])]
subcat_df = reduced_df.groupby([ScifConsts.SUB_CATEGORY_FK], as_index=False).agg(
{'count_sub_cat': np.sum})
subcat = subcat_df[subcat_df['count_sub_cat'] == subcat_df['count_sub_cat'].max()] \
[ScifConsts.SUB_CATEGORY_FK].values[0] if not subcat_df.empty else None
elif row['shelves_bay_after'] == row['max_shelf']:
reduced_df = scene_bay_sub_cat_sum[(scene_bay_sub_cat_sum[MatchesConsts.BAY_NUMBER].
isin([row[MatchesConsts.BAY_NUMBER] + 1])) &
(scene_bay_sub_cat_sum[ScifConsts.SCENE_FK] == row[
ScifConsts.SCENE_FK])]
subcat_df = reduced_df.groupby([ScifConsts.SUB_CATEGORY_FK], as_index=False).agg(
{'count_sub_cat': np.sum})
subcat = subcat_df[subcat_df['count_sub_cat'] == subcat_df['count_sub_cat'].max()] \
[ScifConsts.SUB_CATEGORY_FK].values[0] if not subcat_df.empty else None
else:
subcat = None
return subcat
else:
return subcat
return subcat
def get_store_areas(self):
query = PEPSICOUK_Queries.get_all_store_areas()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def calculate_shelf_len_for_mixed_shelves(self):
mix_displays = self.displays_template[self.displays_template[self.KPI_LOGIC] == 'Mix']\
[self.DISPLAY_NAME_TEMPL].unique()
mix_scenes = self.scif[self.scif[ScifConsts.TEMPLATE_NAME].isin(mix_displays)][ScifConsts.SCENE_FK].unique()
mix_matches = self.match_product_in_scene[self.match_product_in_scene[MatchesConsts.SCENE_FK].isin(mix_scenes)]
shelf_len_df = pd.DataFrame(columns=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER, 'shelf_length'])
if not mix_matches.empty:
shelf_len_df[MatchesConsts.SCENE_FK] = shelf_len_df[MatchesConsts.SCENE_FK].astype('float')
shelf_len_df[MatchesConsts.BAY_NUMBER] = shelf_len_df[MatchesConsts.BAY_NUMBER].astype('float')
scenes_bays = mix_matches.drop_duplicates(subset=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER])
for i, row in scenes_bays.iterrows():
filtered_matches = mix_matches[(mix_matches[MatchesConsts.SCENE_FK]==row[MatchesConsts.SCENE_FK]) &
(mix_matches[MatchesConsts.BAY_NUMBER] == row[MatchesConsts.BAY_NUMBER])]
left_edge = self.get_left_edge_mm(filtered_matches)
right_edge = self.get_right_edge_mm(filtered_matches)
shelf_len = float(right_edge - left_edge)
shelf_len_df.loc[len(shelf_len_df)] = [row[MatchesConsts.SCENE_FK], row[MatchesConsts.BAY_NUMBER], shelf_len]
return shelf_len_df
@staticmethod
def get_left_edge_mm(matches):
matches['left_edge_mm'] = matches['x_mm'] - matches['width_mm_advance'] / 2
left_edge = matches['left_edge_mm'].min()
return left_edge
@staticmethod
def get_right_edge_mm(matches):
matches['right_edge_mm'] = matches['x_mm'] + matches['width_mm_advance'] / 2
right_edge = matches['right_edge_mm'].max()
return right_edge
def get_match_display_in_scene(self):
query = PEPSICOUK_Queries.get_match_display(self.session_uid)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_display_parameters(self):
display_templ = pd.read_excel(self.DISPLAY_TEMPLATE_PATH)
display_templ = display_templ.fillna('')
display_templ[self.SHELF_LEN_DISPL] = display_templ[self.SHELF_LEN_DISPL].apply(lambda x: x*1000 if x!='N/A'
else 0)
return display_templ
def recalculate_display_product_length(self, scif, matches):
scene_template = scif[[ScifConsts.SCENE_FK, ScifConsts.TEMPLATE_NAME]].drop_duplicates()
matches = matches.merge(scene_template, on=ScifConsts.SCENE_FK, how='left')
scif = scif.merge(self.displays_template, left_on=ScifConsts.TEMPLATE_NAME, right_on=self.DISPLAY_NAME_TEMPL,
how='left')
matches = matches.merge(self.displays_template, left_on=ScifConsts.TEMPLATE_NAME,
right_on=self.DISPLAY_NAME_TEMPL, how='left')
matches['facings_matches'] = 1
if not matches.empty:
matches = self.place_posms_at_bay_minus_one_to_bays(matches)
# matches = self.construct_display_id(matches)
max_display_id = matches['display_id'].max()
bin_bay_scif, bin_bay_matches = self.calculate_displays_by_bin_bay_logic(scif, matches)
bay_scif, bay_matches, max_display_id = self.calculate_displays_separated_by_bays(scif, matches,
max_display_id)
bin_bin_scif, bin_bin_matches = self.calculate_displays_by_bin_bin_logic(scif, matches, max_display_id)
bin_shelf_scif, bin_shelf_matches = self.calculate_displays_by_mix_logic(scif, matches)
shelf_scif, shelf_matches = self.calculate_displays_by_shelf_logic(scif, matches)
scif = bin_bay_scif.append(bin_bin_scif)
scif = scif.append(bin_shelf_scif)
scif = scif.append(shelf_scif)
scif = scif.append(bay_scif)
scif.reset_index(drop=True, inplace=True)
matches = bin_bay_matches.append(bin_bin_matches)
matches = matches.append(bin_shelf_matches)
matches = matches.append(shelf_matches)
matches = matches.append(bay_matches)
matches.reset_index(drop=True, inplace=True)
return scif, matches
def calculate_displays_separated_by_bays(self, scif, matches, max_display_id):
bay_displays = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Bin') &
(self.displays_template[self.BAY_TO_SEPARATE] == 'No') &
(self.displays_template[self.BIN_TO_SEPARATE] == 'No')] \
[self.DISPLAY_NAME_TEMPL].unique()
scif = scif[scif[ScifConsts.TEMPLATE_NAME].isin(bay_displays)]
matches = matches[matches[ScifConsts.TEMPLATE_NAME].isin(bay_displays)]
bay_scif = scif
bay_matches = matches
if not bay_matches.empty:
bay_matches = bay_matches.drop_duplicates(subset=[MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK],
keep='last')
bay_matches[MatchesConsts.BAY_NUMBER] = 1
bay_matches = self.construct_display_id(bay_matches)
bay_matches['display_id'] = bay_matches['display_id']+max_display_id
bay_scif, bay_matches = self.calculate_product_length_on_display(bay_scif, bay_matches)
max_display_id = bay_matches['display_id'].max()
return bay_scif, bay_matches, max_display_id
def place_posms_at_bay_minus_one_to_bays(self, matches):
bay_number_minus_one = matches[matches[MatchesConsts.BAY_NUMBER] == -1]
if not bay_number_minus_one.empty:
filtered_matches = matches[~(matches[MatchesConsts.BAY_NUMBER] == -1)]
bay_borders = filtered_matches.assign(start_x=filtered_matches['rect_x'],
end_x=filtered_matches['rect_x']).groupby([MatchesConsts.SCENE_FK,
MatchesConsts.BAY_NUMBER], as_index=False).agg({'start_x': np.min, 'end_x': np.max})
bay_number_minus_one['new_bay_number'] = bay_number_minus_one.apply(self.find_bay_for_posm,
args=(bay_borders,), axis=1)
matches = matches.merge(bay_number_minus_one[[MatchesConsts.PROBE_MATCH_FK, 'new_bay_number']],
on=MatchesConsts.PROBE_MATCH_FK, how='left')
matches.loc[(matches[MatchesConsts.BAY_NUMBER] == -1), MatchesConsts.BAY_NUMBER] = \
matches['new_bay_number']
matches.drop('new_bay_number', axis=1, inplace=True)
matches = matches[~(matches[MatchesConsts.BAY_NUMBER].isnull())]
return matches
def find_bay_for_posm(self, row, bay_borders):
bay_df = bay_borders[(bay_borders[MatchesConsts.SCENE_FK] == row[MatchesConsts.SCENE_FK]) &
(bay_borders['start_x']<=row['rect_x']) & (bay_borders['end_x']>=row['rect_x'])]
bay_number = bay_df[MatchesConsts.BAY_NUMBER].values[0] if not bay_df.empty else None
return bay_number
def construct_display_id(self, matches):
scene_bay = matches[[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER]].drop_duplicates()
scene_bay = scene_bay.sort_values(by=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER])
scene_bay = scene_bay.values.tolist()
scene_bay = set(map(lambda x: tuple(x), scene_bay))
display_dict = {}
i = 0
for value in scene_bay:
display_dict[value] = i+1
i += 1
matches['display_id'] = matches.apply(self.assign_diplay_id, args=(display_dict, ), axis=1)
return matches
def assign_diplay_id(self, row, display_dict):
x = (row[MatchesConsts.SCENE_FK], row[MatchesConsts.BAY_NUMBER])
display_id = display_dict[x]
return display_id
def calculate_displays_by_shelf_logic(self, scif, matches):
shelf_displays = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Shelf')] \
[self.DISPLAY_NAME_TEMPL].unique()
scif = scif[scif[ScifConsts.TEMPLATE_NAME].isin(shelf_displays)]
matches = matches[matches[ScifConsts.TEMPLATE_NAME].isin(shelf_displays)]
return scif, matches
def calculate_displays_by_mix_logic(self, scif, matches):
mix_displays = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Mix')] \
[self.DISPLAY_NAME_TEMPL].unique()
scif = scif[scif[ScifConsts.TEMPLATE_NAME].isin(mix_displays)]
matches = matches[matches[ScifConsts.TEMPLATE_NAME].isin(mix_displays)]
matches = matches.merge(self.shelf_len_mixed_shelves, on=[MatchesConsts.BAY_NUMBER, MatchesConsts.SCENE_FK],
how='left')
mix_scif = scif
mix_matches = matches
if not mix_matches.empty:
bottom_shelf = matches[MatchesConsts.SHELF_NUMBER].max()
display_matches = mix_matches[mix_matches[MatchesConsts.SHELF_NUMBER] == bottom_shelf]
shelf_matches = mix_matches[~(mix_matches[MatchesConsts.SHELF_NUMBER] == bottom_shelf)]
bin_bin_scenes = self.scene_display[ScifConsts.SCENE_FK].unique()
bin_bin_matches, bin_bay_matches = self.allocate_matches_to_logic(display_matches, bin_bin_scenes)
if not bin_bin_matches.empty:
bin_bin_matches = self.place_products_to_bays(bin_bin_matches, self.scene_display)
display_matches = bin_bin_matches.append(bin_bay_matches)
if not display_matches.empty:
display_matches = self.calculate_product_length_in_matches_on_display(display_matches)
mix_matches = shelf_matches.append(display_matches)
mix_matches_agg = mix_matches.groupby([MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK]). \
agg({'facings_matches': np.sum, MatchesConsts.WIDTH_MM_ADVANCE: np.sum})
mix_scif = mix_scif.merge(mix_matches_agg, on=[MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK])
mix_scif['facings'] = mix_scif['updated_facings'] = mix_scif['facings_matches']
mix_scif[ScifConsts.GROSS_LEN_ADD_STACK] = mix_scif['updated_gross_length'] = mix_scif[
MatchesConsts.WIDTH_MM_ADVANCE]
return mix_scif, mix_matches
def allocate_matches_to_logic(self, matches, bin_bin_scenes):
bin_bin_matches = matches[matches[ScifConsts.SCENE_FK].isin(bin_bin_scenes)]
bin_bay_matches = matches[(~matches[ScifConsts.SCENE_FK].isin(bin_bin_scenes))]
return bin_bin_matches, bin_bay_matches
def calculate_displays_by_bin_bin_logic(self, scif, matches, max_display_id = None):
bin_bin_displays = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Bin') &
(self.displays_template[self.BAY_TO_SEPARATE] == 'No') &
(self.displays_template[self.BIN_TO_SEPARATE] == 'Yes')] \
[self.DISPLAY_NAME_TEMPL].unique()
scif = scif[scif[ScifConsts.TEMPLATE_NAME].isin(bin_bin_displays)]
matches = matches[matches[ScifConsts.TEMPLATE_NAME].isin(bin_bin_displays)]
bin_bin_scif = scif
bin_bin_matches = matches
if not bin_bin_matches.empty:
bin_bin_matches = self.place_products_to_bays(bin_bin_matches, self.scene_display, max_display_id)
bin_bin_scif, bin_bin_matches = self.calculate_product_length_on_display(bin_bin_scif, bin_bin_matches)
return bin_bin_scif, bin_bin_matches
def calculate_product_length_in_matches_on_display(self, matches):
matches = matches.drop_duplicates(
subset=[MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER])
matches = matches.merge(self.all_products[[ScifConsts.PRODUCT_FK, ScifConsts.PRODUCT_TYPE]],
on=ScifConsts.PRODUCT_FK, how='left')
matches_no_pos = matches[~(matches[ScifConsts.PRODUCT_TYPE]=='POS')]
# matches_no_pos = matches[~(matches[MatchesConsts.STACKING_LAYER] == -2)]
bay_sku = matches_no_pos.groupby([MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER],
as_index=False).agg({'facings_matches': np.sum})
bay_sku.rename(columns={'facings_matches': 'unique_skus'}, inplace=True)
matches = matches.merge(bay_sku, on=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER], how='left')
matches[MatchesConsts.WIDTH_MM_ADVANCE] = matches.apply(self.get_product_len, args=(matches,), axis=1)
matches.drop(columns=[ScifConsts.PRODUCT_TYPE], inplace=True)
return matches
def get_product_len(self, row, matches):
if row[ScifConsts.PRODUCT_TYPE] == 'POS':
return 0
# if Mix logic - then the length will depend of whether there are bins or bays in the bottom level
if row[self.KPI_LOGIC] == 'Mix':
# if there bins on bottom level
if row[MatchesConsts.SCENE_FK] in self.scene_display[MatchesConsts.SCENE_FK].unique():
number_of_bays = len(matches[matches[MatchesConsts.SCENE_FK] == row[MatchesConsts.SCENE_FK]] \
[MatchesConsts.BAY_NUMBER].unique())
if number_of_bays == 1:
leng = self.full_pallet_len / row['unique_skus']
else:
leng = self.half_pallet_len / row['unique_skus']
return leng
#if there is just a regular shelf on bottom level
else:
leng = row['shelf_length'] / row['unique_skus']
return leng
# if logic is not Mixed, the produc len depends on the length of display and number of unique skus there
else:
leng = row[self.SHELF_LEN_DISPL] / row['unique_skus']
return leng
def calculate_product_length_on_display(self, scif, matches):
matches = self.calculate_product_length_in_matches_on_display(matches)
aggregated_matches = matches.groupby([MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK], as_index=False). \
agg({'facings_matches': np.sum, MatchesConsts.WIDTH_MM_ADVANCE: np.sum})
scif = scif.merge(aggregated_matches, on=[MatchesConsts.PRODUCT_FK, MatchesConsts.SCENE_FK], how='left')
scif['facings'] = scif['updated_facings'] = scif['facings_matches']
scif[ScifConsts.GROSS_LEN_ADD_STACK] = scif['updated_gross_length'] = scif[
MatchesConsts.WIDTH_MM_ADVANCE]
return scif, matches
def place_products_to_bays(self, matches, scene_display, max_id=None):
matches = matches.merge(scene_display, on=ScifConsts.SCENE_FK, how='left')
matches.loc[(matches['rect_x'] >= matches['rect_x_start']) &
(matches['rect_x'] < matches['rect_x_end']), 'new_bay_number'] = matches['assigned_bay_number']
matches = matches[~(matches['new_bay_number'].isnull())]
matches['bay_number'] = matches['new_bay_number']
matches = matches.drop('new_bay_number', axis=1)
matches = matches.reset_index(drop=True)
if max_id is not None:
matches.drop('display_id', axis=1, inplace=True)
matches = self.construct_display_id(matches)
matches['display_id'] = matches['display_id'] + max_id
return matches
def assign_bays_to_bins(self):
if not self.scene_display.empty:
scene_display = self.scene_display[self.scene_display['display_name'] == 'Top Left Corner']
scene_display = scene_display.sort_values(['scene_fk', 'rect_x'])
scene_display = scene_display.assign(rect_x_end=scene_display.groupby('scene_fk').rect_x.shift(-1)). \
fillna({'rect_x_end': np.inf})
scene_display['assigned_bay_number'] = scene_display.groupby(['scene_fk'])['rect_x'].rank()
scene_display.rename(columns={'rect_x': 'rect_x_start'}, inplace=True)
scene_display.drop('bay_number', axis=1, inplace=True)
self.scene_display = scene_display
def calculate_displays_by_bin_bay_logic(self, scif, matches):
bin_bay_displays = self.displays_template[(self.displays_template[self.KPI_LOGIC] == 'Bin') &
(self.displays_template[self.BAY_TO_SEPARATE] == 'Yes')] \
[self.DISPLAY_NAME_TEMPL].unique()
scif = scif[scif[ScifConsts.TEMPLATE_NAME].isin(bin_bay_displays)]
matches = matches[matches[ScifConsts.TEMPLATE_NAME].isin(bin_bay_displays)]
bin_bay_scif = scif
bin_bay_matches = matches
if not bin_bay_matches.empty:
bin_bay_matches = matches.drop_duplicates(subset=[MatchesConsts.PRODUCT_FK, MatchesConsts.BAY_NUMBER,
MatchesConsts.SCENE_FK], keep='last')
bin_bay_scif, bin_bay_matches = self.calculate_product_length_on_display(bin_bay_scif, bin_bay_matches)
return bin_bay_scif, bin_bay_matches
def set_filtered_scif_and_matches_for_all_kpis_secondary(self, scif, matches):
if self.do_exclusion_rules_apply_to_store('ALL'):
excl_template_all_kpis = self.exclusion_template[self.exclusion_template['KPI'].str.upper() == 'ALL']
if not excl_template_all_kpis.empty:
template_filters = self.get_filters_dictionary(excl_template_all_kpis)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(template_filters, scif,
matches)
scif, matches = self.update_scif_and_matches_for_smart_attributes(scif, matches)
scif, matches = self.add_sub_category_to_empty_and_other(scif, matches)
scif, matches = self.recalculate_display_product_length(scif, matches)
self.filtered_scif_secondary = scif
self.filtered_matches_secondary = matches
def get_initial_secondary_scif(self):
scif = self.scif
if not self.scif.empty:
scif = self.scif[self.scif[ScifConsts.LOCATION_TYPE] == 'Secondary Shelf']
return scif
def get_initial_secondary_matches(self):
matches = self.match_product_in_scene
if not self.match_product_in_scene.empty:
secondary_scenes = self.filtered_scif_secondary[ScifConsts.SCENE_FK].unique()
matches = self.match_product_in_scene[self.match_product_in_scene[ScifConsts.SCENE_FK].isin(secondary_scenes)]
if not matches.empty:
matches = self.construct_display_id(matches)
return matches
def get_scene_to_store_area_map(self):
query = PEPSICOUK_Queries.get_scene_store_area(self.session_uid)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def complete_scif_data(self):
scene_store_area = self.get_scene_to_store_area_map()
self.scif = self.scif.merge(scene_store_area, on=ScifConsts.SCENE_FK, how='left')
self.match_product_in_scene = self.match_product_in_scene.merge(scene_store_area, on=ScifConsts.SCENE_FK,
how='left')
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), str(value).split(','))
def get_store_policy_data_for_exclusion_template(self):
store_policy = pd.read_excel(self.EXCLUSION_TEMPLATE_PATH, sheet_name='store_policy')
store_policy = store_policy.fillna('ALL')
return store_policy
def get_kpi_result_values_df(self):
query = PEPSICOUK_Queries.get_kpi_result_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_kpi_score_values_df(self):
query = PEPSICOUK_Queries.get_kpi_score_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_store_data_by_store_id(self):
store_id = self.store_id if self.store_id else self.session_info['store_fk'].values[0]
query = PEPSICOUK_Queries.get_store_data_by_store_id(store_id)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_facings_scene_bay_shelf_product(self):
self.filtered_matches['count'] = 1
aggregate_df = self.filtered_matches.groupby(['scene_fk', 'bay_number', 'shelf_number',
'shelf_number_from_bottom', 'product_fk'],
as_index=False).agg({'count': np.sum})
return aggregate_df
def get_all_kpi_external_targets(self):
query = PEPSICOUK_Queries.get_kpi_external_targets(self.visit_date)
external_targets = pd.read_sql_query(query, self.rds_conn.db)
return external_targets
def get_custom_entity_data(self):
query = PEPSICOUK_Queries.get_custom_entities_query()
custom_entity_data = pd.read_sql_query(query, self.rds_conn.db)
return custom_entity_data
def get_on_display_products(self):
probe_match_list = self.match_product_in_scene['probe_match_fk'].values.tolist()
on_display_products = pd.DataFrame(columns=["probe_match_fk", "smart_attribute"])
if probe_match_list:
query = PEPSICOUK_Queries.on_display_products_query(probe_match_list)
on_display_products = pd.read_sql_query(query, self.rds_conn.db)
return on_display_products
def get_exclusion_template_data(self):
excl_templ = pd.read_excel(self.EXCLUSION_TEMPLATE_PATH)
excl_templ = excl_templ.fillna('')
return excl_templ
def set_filtered_scif_and_matches_for_all_kpis(self, scif, matches):
if self.do_exclusion_rules_apply_to_store('ALL'):
excl_template_all_kpis = self.exclusion_template[self.exclusion_template['KPI'].str.upper() == 'ALL']
if not excl_template_all_kpis.empty:
template_filters = self.get_filters_dictionary(excl_template_all_kpis)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(template_filters, scif, matches)
scif, matches = self.update_scif_and_matches_for_smart_attributes(scif, matches)
scif, matches = self.add_sub_category_to_empty_and_other(scif, matches)
matches['facings_matches'] = 1
self.filtered_scif = scif
self.filtered_matches = matches
def do_exclusion_rules_apply_to_store(self, kpi):
exclusion_flag = True
policy_template = self.store_policy_exclusion_template.copy()
if kpi == 'ALL':
policy_template['KPI'] = policy_template['KPI'].apply(lambda x: str(x).upper())
relevant_policy = policy_template[policy_template['KPI'] == kpi]
if not relevant_policy.empty:
relevant_policy = relevant_policy.drop(columns='KPI')
policy_columns = relevant_policy.columns.values.tolist()
for column in policy_columns:
store_att_value = self.store_info_dict.get(column)
mask = relevant_policy.apply(self.get_masking_filter, args=(column, store_att_value), axis=1)
relevant_policy = relevant_policy[mask]
if relevant_policy.empty:
exclusion_flag = False
return exclusion_flag
def get_masking_filter(self, row, column, store_att_value):
if store_att_value in self.split_and_strip(row[column]) or row[column] == self.ALL:
return True
else:
return False
# def set_scif_and_matches_in_data_provider(self, scif, matches):
# self.data_provider._set_scene_item_facts(scif)
# self.data_provider._set_matches(matches)
def get_filters_dictionary(self, excl_template_all_kpis):
filters = {}
for i, row in excl_template_all_kpis.iterrows():
action = row['Action']
if action == action:
if action.upper() == 'INCLUDE':
filters.update({row['Type']: self.split_and_strip(row['Value'])})
if action.upper() == 'EXCLUDE':
filters.update({row['Type']: (self.split_and_strip(row['Value']), 0)})
else:
Log.warning('Exclusion template: filter in row {} has no action will be omitted'.format(i+1))
return filters
def filter_scif_and_matches_for_scene_and_product_filters(self, template_filters, scif, matches):
filters = self.get_filters_for_scif_and_matches(template_filters)
scif = scif[self.toolbox.get_filter_condition(scif, **filters)]
matches = matches[self.toolbox.get_filter_condition(matches, **filters)]
return scif, matches
def get_filters_for_scif_and_matches(self, template_filters):
product_keys = filter(lambda x: x in self.all_products.columns.values.tolist(),
template_filters.keys())
scene_keys = filter(lambda x: x in self.all_templates.columns.values.tolist(),
template_filters.keys())
scene_keys.extend(filter(lambda x: x in self.store_areas.columns.values.tolist(),
template_filters.keys()))
product_filters = {}
scene_filters = {}
for key in product_keys:
product_filters.update({key: template_filters[key]})
for key in scene_keys:
scene_filters.update({key: template_filters[key]})
filters_all = {}
if product_filters:
product_fks = self.get_product_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_product_fk_from_filters(self, filters):
all_products = self.all_products
product_fk_list = all_products[self.toolbox.get_filter_condition(all_products, **filters)]
product_fk_list = product_fk_list['product_fk'].unique().tolist()
product_fk_list = product_fk_list if product_fk_list else [None]
return product_fk_list
def get_scene_fk_from_filters(self, filters):
scif_data = self.scif
scene_fk_list = scif_data[self.toolbox.get_filter_condition(scif_data, **filters)]
scene_fk_list = scene_fk_list['scene_fk'].unique().tolist()
scene_fk_list = scene_fk_list if scene_fk_list else [None]
return scene_fk_list
def update_scif_and_matches_for_smart_attributes(self, scif, matches):
matches = self.filter_matches_for_products_with_smart_attributes(matches)
aggregated_matches = self.aggregate_matches(matches)
# remove relevant products from scif
scif = self.update_scif_for_products_with_smart_attributes(scif, aggregated_matches)
return scif, matches
@staticmethod
def update_scif_for_products_with_smart_attributes(scif, agg_matches):
scif = scif.merge(agg_matches, on=['scene_fk', 'product_fk'], how='left')
scif = scif[~scif['facings_matches'].isnull()]
scif.rename(columns={'width_mm_advance': 'updated_gross_length', 'facings_matches': 'updated_facings'},
inplace=True)
scif['facings'] = scif['updated_facings']
scif['gross_len_add_stack'] = scif['updated_gross_length']
return scif
def filter_matches_for_products_with_smart_attributes(self, matches):
matches = matches.merge(self.on_display_products, on='probe_match_fk', how='left')
matches = matches[~(matches['smart_attribute'].isin([self.ADDITIONAL_DISPLAY, self.STOCK]))]
return matches
@staticmethod
def aggregate_matches(matches):
matches = matches[~(matches['bay_number'] == -1)]
matches['facings_matches'] = 1
aggregated_df = matches.groupby(['scene_fk', 'product_fk'], as_index=False).agg({'width_mm_advance': np.sum,
'facings_matches': np.sum})
return aggregated_df
def get_shelf_placement_kpi_targets_data(self):
shelf_placement_targets = self.external_targets[self.external_targets['operation_type'] == self.SHELF_PLACEMENT]
shelf_placement_targets = shelf_placement_targets.drop_duplicates(subset=['operation_type', 'kpi_level_2_fk',
'key_json', 'data_json'])
output_targets_df = pd.DataFrame(columns=shelf_placement_targets.columns.values.tolist())
if not shelf_placement_targets.empty:
shelf_number_df = self.unpack_external_targets_json_fields_to_df(shelf_placement_targets, field_name='key_json')
shelves_to_include_df = self.unpack_external_targets_json_fields_to_df(shelf_placement_targets,
field_name='data_json')
shelf_placement_targets = shelf_placement_targets.merge(shelf_number_df, on='pk', how='left')
output_targets_df = shelf_placement_targets.merge(shelves_to_include_df, on='pk', how='left')
kpi_data = self.kpi_static_data[['pk', 'type']]
output_targets_df = output_targets_df.merge(kpi_data, left_on='kpi_level_2_fk', right_on='pk', how='left')
return output_targets_df
@staticmethod
def unpack_external_targets_json_fields_to_df(input_df, field_name):
data_list = []
for i, row in input_df.iterrows():
data_item = json.loads(row[field_name]) if row[field_name] else {}
data_item.update({'pk': row.pk})
data_list.append(data_item)
output_df = pd.DataFrame(data_list)
return output_df
def unpack_all_external_targets(self):
targets_df = self.external_targets.drop_duplicates(subset=['operation_type', 'kpi_level_2_fk', 'key_json',
'data_json'])
output_targets = pd.DataFrame(columns=targets_df.columns.values.tolist())
if not targets_df.empty:
keys_df = self.unpack_external_targets_json_fields_to_df(targets_df, field_name='key_json')
data_df = self.unpack_external_targets_json_fields_to_df(targets_df, field_name='data_json')
targets_df = targets_df.merge(keys_df, on='pk', how='left')
targets_df = targets_df.merge(data_df, on='pk', how='left')
kpi_data = self.kpi_static_data[['pk', 'type']]
kpi_data.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True)
output_targets = targets_df.merge(kpi_data, on='kpi_level_2_fk', how='left')
if output_targets.empty:
Log.warning('KPI External Targets Results are empty')
return output_targets
def get_kpi_result_value_pk_by_value(self, value):
pk = None
try:
pk = self.kpi_result_values[self.kpi_result_values['value'] == value]['pk'].values[0]
except:
Log.error('Value {} does not exist'.format(value))
return pk
def get_kpi_score_value_pk_by_value(self, value):
pk = None
try:
pk = self.kpi_score_values[self.kpi_score_values['value'] == value]['pk'].values[0]
except:
Log.error('Value {} does not exist'.format(value))
return pk
def get_yes_no_score(self, score):
value = 'NO' if not score else 'YES'
custom_score = self.get_kpi_score_value_pk_by_value(value)
return custom_score
def get_yes_no_result(self, result):
value = 'NO' if not result else 'YES'
custom_score = self.get_kpi_result_value_pk_by_value(value)
return custom_score
def set_filtered_scif_and_matches_for_specific_kpi(self, scif, matches, kpi):
try:
kpi = int(float(kpi))
except ValueError:
kpi = kpi
if isinstance(kpi, int):
kpi = self.get_kpi_type_by_pk(kpi)
if self.do_exclusion_rules_apply_to_store(kpi):
excl_template_for_kpi = self.exclusion_template[self.exclusion_template['KPI'] == kpi]
if not excl_template_for_kpi.empty:
template_filters = self.get_filters_dictionary(excl_template_for_kpi)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(template_filters, scif, matches)
else:
excl_template_for_kpi = self.exclusion_template[(self.exclusion_template['KPI'] == kpi) &
(self.exclusion_template['Ignore Store Policy'] == 1)]
if not excl_template_for_kpi.empty:
template_filters = self.get_filters_dictionary(excl_template_for_kpi)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(template_filters, scif, matches)
return scif, matches
def get_kpi_type_by_pk(self, kpi_fk):
try:
kpi_fk = int(float(kpi_fk))
return self.kpi_static_data[self.kpi_static_data['pk'] == kpi_fk]['type'].values[0]
except IndexError:
Log.info("Kpi name: {} is not equal to any kpi name in static table".format(kpi_fk))
return None
class JEFFToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output, commonv2):
self.k_engine = BaseCalculationsScript(data_provider, output)
self.output = output
self.data_provider = data_provider
self.common = commonv2
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
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.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.templates = self.data_provider[Data.TEMPLATES]
self.store_id = self.data_provider[Data.STORE_FK]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
# self.kpi_static_data = self.get_kpi_static_data()
self.kpi_results_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['store_type'].iloc[0]
# self.rules = pd.read_excel(TEMPLATE_PATH).set_index('store_type').to_dict('index')
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.manufacturer_fk = self.all_products['manufacturer_fk'][self.all_products['manufacturer_name'] == 'CCNA'].iloc[0]
# self.scene_data = self.load_scene_data()
# self.kpi_set_fk = kpi_set_fk
self.templates = {}
self.parse_template()
self.toolbox = GENERALToolBox(self.data_provider)
self.SOS = SOS_calc(self.data_provider)
self.survey = Survey_calc(self.data_provider)
self._merge_matches_and_all_product()
def _merge_matches_and_all_product(self):
"""
This method merges the all product data with the match product in scene DataFrame
"""
self.match_product_in_scene = self.match_product_in_scene.merge(self.all_products, on='product_fk', how='left')
def parse_template(self):
self.templates['SOS'] = pd.read_excel(TEMPLATE_PATH)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
def calculate_sos(self):
for i, row in self.templates[SOS].iterrows():
for scene in (self.scif['scene_fk'].unique()).tolist():
parent_kpi_fk = 0
kpi_name = row['KPI Name'].strip()
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name)
num_param1 = row['numerator_param1']
num_values1 = self.sanitize_values(row['numerator_value1'])
num_param2 = row['numerator_param2']
num_values2 = self.sanitize_values(row['numerator_value2'])
den_param1 = row['numerator_param1']
den_values1 = self.sanitize_values(row['numerator_value1'])
den_param2 = row['denominator_param2']
den_values2 = self.sanitize_values(row['denominator_value2'])
num_exclude_param1 = row['numerator_exclude_param1']
num_exclude_value1 = self.sanitize_values(row['numerator_exclude_value1'])
num_exclude_param2 = row['numerator_exclude_param2']
num_exclude_value2 = self.sanitize_values(row['numerator_exclude_value2'])
den_exclude_param = row['denominator_exclude_param']
den_exclude_value = self.sanitize_values(row['denominator_exclude_value'])
parent_kpi_name = row['Parent']
if not pd.isna(parent_kpi_name):
parent_kpi_fk = self.common.get_kpi_fk_by_kpi_name(parent_kpi_name.strip())
filters = {num_param1: num_values1, num_param2: num_values2, 'product_type': ['POS', 'SKU', 'OTHER'],
'scene_fk': scene}
filters = self.delete_filter_nan(filters)
general_filters = {den_param1: den_values1,
den_param2: den_values2,
'product_type': ['SKU', 'OTHER'],
'scene_fk': scene}
general_filters = self.delete_filter_nan(general_filters)
if not pd.isna(num_exclude_param1):
if 'ALL' in num_values1:
if not pd.isna(num_exclude_param1):
all_unique_values = (self.all_products[num_exclude_param1].unique()).tolist()
excluded_list = list(set(all_unique_values) - set(num_exclude_value1))
filters[num_exclude_param1] = excluded_list
if 'ALL' in num_values2:
if not pd.isna(num_exclude_param2):
all_unique_values = (self.all_products[num_exclude_param2].unique()).tolist()
excluded_list = list(set(all_unique_values) - set(num_exclude_value2))
filters[num_exclude_param2] = excluded_list
if not pd.isna(den_exclude_param):
if 'ALL' in den_values2:
all_unique_values = (self.all_products[den_exclude_param].unique()).tolist()
excluded_list = list(set(all_unique_values) - set(den_exclude_value))
general_filters[den_exclude_param] = excluded_list
ratio = self.SOS.calculate_share_of_shelf(filters, **general_filters)
shelf_count_list = (self.match_product_in_scene['shelf_number'][self.match_product_in_scene['scene_fk'] == scene].
unique()).tolist()
shelf_count = max(shelf_count_list) if shelf_count_list else 0
result = ratio
score = (ratio * shelf_count)
if parent_kpi_fk == 0:
self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=scene, denominator_result=0, result=result,
score=score)
else:
self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=scene,denominator_result=0, result=result,
score=score, identifier_parent=parent_kpi_fk)
@staticmethod
def sanitize_values(item):
if pd.isna(item):
return item
else:
items = [x.strip() for x in item.split(',')]
return items
@staticmethod
def delete_filter_nan(filters):
for key in filters.keys():
if type(filters[key]) is not list:
if pd.isna(filters[key]):
del filters[key]
return filters
def calculate_availability_df(self, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:return: Total number of SKUs facings appeared in the filtered Scene Item Facts data frame.
"""
if set(filters.keys()).difference(self.scif.keys()):
scif_mpis_diff = self.match_product_in_scene[['scene_fk', 'product_fk'] +
list(self.match_product_in_scene.keys().difference(
self.scif.keys()))]
# a patch for the item_id field which became item_id_x since it was added to product table as attribute.
item_id = 'item_id' if 'item_id' in self.scif.columns else 'item_id_x'
merged_df = pd.merge(self.scif[self.scif.facings != 0], scif_mpis_diff, how='outer',
left_on=['scene_fk', item_id], right_on=['scene_fk', 'product_fk'])
filtered_df = \
merged_df[self.toolbox.get_filter_condition(merged_df, **filters)]
# filtered_df = \
# self.match_product_in_scene[self.toolbox.get_filter_condition(self.match_product_in_scene, **filters)]
else:
filtered_df = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)]
if self.facings_field in filtered_df.columns:
availability_df = filtered_df
else:
availability_df = filtered_df
return availability_df
class TSINGTAOBEERCNToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
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.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.assortment = self.data_provider[Data.ASSORTMENTS]
self.tools = GENERALToolBox(data_provider)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sku_facing_session_level()
self.calculate_facings_by_assortment()
self.common.commit_results_data()
def calculate_sku_facing_session_level(self):
filters = {"product_type": ["SKU", "Other"]}
result_df = self.scif[self.tools.get_filter_condition(
self.scif, **filters)][['product_fk', 'facings']]
kpi_fk = self.common.get_kpi_fk_by_kpi_name(SESSION_SKU_FACINGS_KPI)
for index, row in result_df.iterrows():
result = row['facings']
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=row['product_fk'],
denominator_id=self.store_id,
score=result,
result=result)
def calculate_facings_by_assortment(self):
assortment_scif_merge = pd.merge(
self.assortment,
self.scif,
how="left",
on=['template_fk',
'product_fk'])[['product_fk', 'template_fk', 'facings']]
result_df = assortment_scif_merge.groupby(
['product_fk'])['facings'].sum().reset_index()
kpi_fk = self.common.get_kpi_fk_by_kpi_name(ASSORTMENT_KPI)
for index, row in result_df.iterrows():
result = 1 if row['facings'] else 0
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=row['product_fk'],
denominator_id=self.store_id,
score=result,
result=result,
numerator_result=row['facings'])
class FSOPToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output, commonv2):
self.k_engine = BaseCalculationsScript(data_provider, output)
self.output = output
self.data_provider = data_provider
self.common = commonv2
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
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.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.templates = self.data_provider[Data.TEMPLATES]
self.store_id = self.data_provider[Data.STORE_FK]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_results_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_type = self.store_info['store_type'].iloc[0]
# self.rules = pd.read_excel(TEMPLATE_PATH).set_index('store_type').to_dict('index')
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.manufacturer_fk = \
self.all_products['manufacturer_fk'][self.all_products['manufacturer_name'] == 'CCNA'].iloc[0]
# self.scene_data = self.load_scene_data()
# self.kpi_set_fk = kpi_set_fk
self.templates = {}
self.parse_template()
self.toolbox = GENERALToolBox(self.data_provider)
self.SOS = SOS_calc(self.data_provider)
self.survey = Survey_calc(self.data_provider)
self._merge_matches_and_all_product()
def _merge_matches_and_all_product(self):
"""
This method merges the all product data with the match product in scene DataFrame
"""
self.match_product_in_scene = self.match_product_in_scene.merge(self.all_products, on='product_fk', how='left')
def parse_template(self):
for sheet in Sheets:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH, sheet_name=sheet)
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_availability()
self.calculate_sos()
def calculate_availability(self):
for i, row in self.templates[Availability].iterrows():
kpi_name = row['KPI Name']
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name)
manufacturers = self.sanitize_values(row['manufacturer'])
brands = self.sanitize_values(row['Brand'])
container = self.sanitize_values(row['CONTAINER'])
attributte_4 = self.sanitize_values(row['att4'])
scene_types = self.sanitize_values(row['scene Type'])
required_brands = row['number_required_brands']
required_sparkling = row['number_required_Sparkling']
required_still = row['number_required_Still']
required_sku = row['number_required_SKU']
excluded_brands = self.sanitize_values(row['exclude brand'])
category = self.sanitize_values(row['category'])
# Bandaid Fix - Hunter Approved
if isinstance(brands, float):
brands_value = (excluded_brands, 0)
else:
brands_value = brands
filters = {'manufacturer_name': manufacturers, 'brand_name': brands_value, 'CONTAINER': container,
'att4': attributte_4,
'template_name': scene_types, 'category': category}
filters = self.delete_filter_nan(filters)
available_df = self.calculate_availability_df(**filters)
score = 0
if pd.notna(required_brands):
brands_available = len(available_df['brand_name'].unique())
if brands_available >= int(required_brands):
score = 1
else:
score = 0
# self.common.write_to_db_result(fk=kpi_fk, numerator_id=0, numerator_result=0, denominator_id=0,
# denominator_result=0, score=score )
if pd.notna(required_sparkling and required_still):
if required_sparkling <= len(available_df[available_df['att4'] == 'SSD']):
if (required_still <= len(available_df[available_df['att4'] == 'Still'])) or (
required_still <= len(available_df[available_df['att4'] == 'STILL'])):
score = 1
else:
score = 0
elif pd.notna(required_sparkling):
if required_sparkling <= len(available_df[available_df['att4'] == 'SSD']):
score = 1
else:
score = 0
elif pd.notna(required_still):
if (required_still <= len(available_df[available_df['att4'] == 'Still'])) or (
required_still <= len(available_df[available_df['att4'] == 'STILL'])):
score = 1
else:
score = 0
if pd.notna(required_sku):
if required_sku <= len(available_df['product_fk'].unique()):
score = 1
else:
score = 0
self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id,
denominator_result=0, score=score)
def calculate_sos(self):
for i, row in self.templates[SOS].iterrows():
general_filters = {}
kpi_name = row['KPI Name']
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name)
manufacturers = self.sanitize_values(row['manufacturer'])
scene_types = self.sanitize_values(row['scene Type'])
num_param1 = row['numerator param1'] # attributte_4
num_value1 = self.sanitize_values(row['numerator value1']) # attributte_4
den_param1 = row['denominator param1']
den_value1 = self.sanitize_values(row['denominator value1'])
den_param2 = row['denominator param2']
den_value2 = self.sanitize_values(row['denominator value2'])
target = row['Target']
product_type= self.sanitize_values(row['product_type'])
excluded_brands = self.sanitize_values(row['exclude brand'])
filters = {'manufacturer_name': manufacturers, num_param1: num_value1, 'template_name': scene_types,
'brand_name': (excluded_brands, 0), 'product_type': product_type}
filters = self.delete_filter_nan(filters)
general_filters = {den_param1: den_value1, den_param2: den_value2, 'product_type': product_type,
'template_name': scene_types}
general_filters = self.delete_filter_nan(general_filters)
if 'manufacturer' in general_filters.keys():
general_filters['manufacturer_name'] = general_filters.pop('manufacturer')
ratio = self.SOS.calculate_share_of_shelf(filters, **general_filters)
if pd.isna(target):
score = ratio
else:
target = int(target)
if (100 * ratio) >= target:
score = 1
else:
score = 0
self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0,
denominator_id=self.store_id,
denominator_result=0, result=ratio, score=score)
def delete_filter_nan(self, filters):
for key in filters.keys():
if type(filters[key]) is not list:
if pd.isna(filters[key]):
del filters[key]
return filters
def calculate_availability_df(self, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:return: Total number of SKUs facings appeared in the filtered Scene Item Facts data frame.
"""
if set(filters.keys()).difference(self.scif.keys()):
scif_mpis_diff = self.match_product_in_scene[['scene_fk', 'product_fk'] +
list(self.match_product_in_scene.keys().difference(
self.scif.keys()))]
# a patch for the item_id field which became item_id_x since it was added to product table as attribute.
item_id = 'item_id' if 'item_id' in self.scif.columns else 'item_id_x'
merged_df = pd.merge(self.scif[self.scif.facings != 0], scif_mpis_diff, how='outer',
left_on=['scene_fk', item_id], right_on=['scene_fk', 'product_fk'])
filtered_df = \
merged_df[self.toolbox.get_filter_condition(merged_df, **filters)]
# filtered_df = \
# self.match_product_in_scene[self.toolbox.get_filter_condition(self.match_product_in_scene, **filters)]
else:
filtered_df = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)]
if self.facings_field in filtered_df.columns:
availability_df = filtered_df
else:
availability_df = filtered_df
return availability_df
@staticmethod
def sanitize_values(item):
if pd.isna(item):
return item
else:
items = [x.strip() for x in item.split(',')]
return items
class PEPSICOUKCommonToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUSION_TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Inclusion_Exclusion_Template.xlsx')
ADDITIONAL_DISPLAY = 'additional display'
STOCK = 'stock'
INCLUDE_EMPTY = True
EXCLUDE_EMPTY = False
OPERATION_TYPES = []
SOS_VS_TARGET = 'SOS vs Target'
HERO_SKU_SPACE_TO_SALES_INDEX = 'Hero SKU Space to Sales Index'
HERO_SKU_SOS_VS_TARGET = 'Hero SKU SOS vs Target'
LINEAR_SOS_INDEX = 'Linear SOS Index'
PEPSICO = 'PEPSICO'
SHELF_PLACEMENT = 'Shelf Placement'
HERO_SKU_PLACEMENT_TOP = 'Hero SKU Placement by shelf numbers_Top'
HERO_PLACEMENT = 'Hero Placement'
HERO_SKU_STACKING = 'Hero SKU Stacking'
HERO_SKU_PRICE = 'Hero SKU Price'
HERO_SKU_PROMO_PRICE = 'Hero SKU Promo Price'
BRAND_FULL_BAY_KPIS = ['Brand Full Bay 90', 'Brand Full Bay 100']
ALL = 'ALL'
def __init__(self, data_provider, rds_conn=None):
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] # initial 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.all_templates = self.data_provider[Data.ALL_TEMPLATES]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] # initial scif
self.rds_conn = PSProjectConnector(
self.project_name,
DbUsers.CalculationEng) if rds_conn is None else rds_conn
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.full_store_info = self.get_store_data_by_store_id()
self.store_info_dict = self.full_store_info.to_dict('records')[0]
self.store_policy_exclusion_template = self.get_store_policy_data_for_exclusion_template(
)
self.toolbox = GENERALToolBox(data_provider)
self.custom_entities = self.get_custom_entity_data()
self.on_display_products = self.get_on_display_products()
self.exclusion_template = self.get_exclusion_template_data()
self.filtered_scif = self.scif # filtered scif acording to exclusion template
self.filtered_matches = self.match_product_in_scene # filtered scif according to exclusion template
self.set_filtered_scif_and_matches_for_all_kpis(
self.scif, self.match_product_in_scene)
self.scene_bay_shelf_product = self.get_facings_scene_bay_shelf_product(
)
self.external_targets = self.get_all_kpi_external_targets()
self.all_targets_unpacked = self.unpack_all_external_targets()
self.kpi_result_values = self.get_kpi_result_values_df()
self.kpi_score_values = self.get_kpi_score_values_df()
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), str(value).split(','))
def get_store_policy_data_for_exclusion_template(self):
store_policy = pd.read_excel(self.EXCLUSION_TEMPLATE_PATH,
sheet_name='store_policy')
store_policy = store_policy.fillna('ALL')
return store_policy
def get_kpi_result_values_df(self):
query = PEPSICOUK_Queries.get_kpi_result_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_kpi_score_values_df(self):
query = PEPSICOUK_Queries.get_kpi_score_values()
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_store_data_by_store_id(self):
store_id = self.store_id if self.store_id else self.session_info[
'store_fk'].values[0]
query = PEPSICOUK_Queries.get_store_data_by_store_id(store_id)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_facings_scene_bay_shelf_product(self):
self.filtered_matches['count'] = 1
aggregate_df = self.filtered_matches.groupby([
'scene_fk', 'bay_number', 'shelf_number',
'shelf_number_from_bottom', 'product_fk'
],
as_index=False).agg(
{'count': np.sum})
return aggregate_df
def get_all_kpi_external_targets(self):
query = PEPSICOUK_Queries.get_kpi_external_targets(self.visit_date)
external_targets = pd.read_sql_query(query, self.rds_conn.db)
return external_targets
def get_custom_entity_data(self):
query = PEPSICOUK_Queries.get_custom_entities_query()
custom_entity_data = pd.read_sql_query(query, self.rds_conn.db)
return custom_entity_data
def get_on_display_products(self):
probe_match_list = self.match_product_in_scene[
'probe_match_fk'].values.tolist()
on_display_products = pd.DataFrame(
columns=["probe_match_fk", "smart_attribute"])
if probe_match_list:
query = PEPSICOUK_Queries.on_display_products_query(
probe_match_list)
on_display_products = pd.read_sql_query(query, self.rds_conn.db)
return on_display_products
def get_exclusion_template_data(self):
excl_templ = pd.read_excel(self.EXCLUSION_TEMPLATE_PATH)
excl_templ = excl_templ.fillna('')
return excl_templ
def set_filtered_scif_and_matches_for_all_kpis(self, scif, matches):
if self.do_exclusion_rules_apply_to_store('ALL'):
excl_template_all_kpis = self.exclusion_template[
self.exclusion_template['KPI'].str.upper() == 'ALL']
if not excl_template_all_kpis.empty:
template_filters = self.get_filters_dictionary(
excl_template_all_kpis)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(
template_filters, scif, matches)
scif, matches = self.update_scif_and_matches_for_smart_attributes(
scif, matches)
self.filtered_scif = scif
self.filtered_matches = matches
def do_exclusion_rules_apply_to_store(self, kpi):
exclusion_flag = True
policy_template = self.store_policy_exclusion_template.copy()
if kpi == 'ALL':
policy_template['KPI'] = policy_template['KPI'].apply(
lambda x: str(x).upper())
relevant_policy = policy_template[policy_template['KPI'] == kpi]
if not relevant_policy.empty:
relevant_policy = relevant_policy.drop(columns='KPI')
policy_columns = relevant_policy.columns.values.tolist()
for column in policy_columns:
store_att_value = self.store_info_dict.get(column)
mask = relevant_policy.apply(self.get_masking_filter,
args=(column, store_att_value),
axis=1)
relevant_policy = relevant_policy[mask]
if relevant_policy.empty:
exclusion_flag = False
return exclusion_flag
def get_masking_filter(self, row, column, store_att_value):
if store_att_value in self.split_and_strip(
row[column]) or row[column] == self.ALL:
return True
else:
return False
# def set_scif_and_matches_in_data_provider(self, scif, matches):
# self.data_provider._set_scene_item_facts(scif)
# self.data_provider._set_matches(matches)
def get_filters_dictionary(self, excl_template_all_kpis):
filters = {}
for i, row in excl_template_all_kpis.iterrows():
action = row['Action']
if action == action:
if action.upper() == 'INCLUDE':
filters.update(
{row['Type']: self.split_and_strip(row['Value'])})
if action.upper() == 'EXCLUDE':
filters.update(
{row['Type']: (self.split_and_strip(row['Value']), 0)})
else:
Log.warning(
'Exclusion template: filter in row {} has no action will be omitted'
.format(i + 1))
return filters
def filter_scif_and_matches_for_scene_and_product_filters(
self, template_filters, scif, matches):
filters = self.get_filters_for_scif_and_matches(template_filters)
scif = scif[self.toolbox.get_filter_condition(scif, **filters)]
matches = matches[self.toolbox.get_filter_condition(
matches, **filters)]
return scif, matches
def get_filters_for_scif_and_matches(self, template_filters):
product_keys = filter(
lambda x: x in self.all_products.columns.values.tolist(),
template_filters.keys())
scene_keys = filter(
lambda x: x in self.all_templates.columns.values.tolist(),
template_filters.keys())
product_filters = {}
scene_filters = {}
for key in product_keys:
product_filters.update({key: template_filters[key]})
for key in scene_keys:
scene_filters.update({key: template_filters[key]})
filters_all = {}
if product_filters:
product_fks = self.get_product_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_product_fk_from_filters(self, filters):
all_products = self.all_products
product_fk_list = all_products[self.toolbox.get_filter_condition(
all_products, **filters)]
product_fk_list = product_fk_list['product_fk'].unique().tolist()
product_fk_list = product_fk_list if product_fk_list else [None]
return product_fk_list
def get_scene_fk_from_filters(self, filters):
scif_data = self.scif
scene_fk_list = scif_data[self.toolbox.get_filter_condition(
scif_data, **filters)]
scene_fk_list = scene_fk_list['scene_fk'].unique().tolist()
scene_fk_list = scene_fk_list if scene_fk_list else [None]
return scene_fk_list
def update_scif_and_matches_for_smart_attributes(self, scif, matches):
matches = self.filter_matches_for_products_with_smart_attributes(
matches)
aggregated_matches = self.aggregate_matches(matches)
# remove relevant products from scif
scif = self.update_scif_for_products_with_smart_attributes(
scif, aggregated_matches)
return scif, matches
@staticmethod
def update_scif_for_products_with_smart_attributes(scif, agg_matches):
scif = scif.merge(agg_matches,
on=['scene_fk', 'product_fk'],
how='left')
scif = scif[~scif['facings_matches'].isnull()]
scif.rename(columns={
'width_mm_advance': 'updated_gross_length',
'facings_matches': 'updated_facings'
},
inplace=True)
scif['facings'] = scif['updated_facings']
scif['gross_len_add_stack'] = scif['updated_gross_length']
return scif
def filter_matches_for_products_with_smart_attributes(self, matches):
matches = matches.merge(self.on_display_products,
on='probe_match_fk',
how='left')
matches = matches[~(matches['smart_attribute'].
isin([self.ADDITIONAL_DISPLAY, self.STOCK]))]
return matches
@staticmethod
def aggregate_matches(matches):
matches = matches[~(matches['bay_number'] == -1)]
matches['facings_matches'] = 1
aggregated_df = matches.groupby(['scene_fk', 'product_fk'],
as_index=False).agg({
'width_mm_advance':
np.sum,
'facings_matches':
np.sum
})
return aggregated_df
def get_shelf_placement_kpi_targets_data(self):
shelf_placement_targets = self.external_targets[
self.external_targets['operation_type'] == self.SHELF_PLACEMENT]
shelf_placement_targets = shelf_placement_targets.drop_duplicates(
subset=[
'operation_type', 'kpi_level_2_fk', 'key_json', 'data_json'
])
output_targets_df = pd.DataFrame(
columns=shelf_placement_targets.columns.values.tolist())
if not shelf_placement_targets.empty:
shelf_number_df = self.unpack_external_targets_json_fields_to_df(
shelf_placement_targets, field_name='key_json')
shelves_to_include_df = self.unpack_external_targets_json_fields_to_df(
shelf_placement_targets, field_name='data_json')
shelf_placement_targets = shelf_placement_targets.merge(
shelf_number_df, on='pk', how='left')
output_targets_df = shelf_placement_targets.merge(
shelves_to_include_df, on='pk', how='left')
kpi_data = self.kpi_static_data[['pk', 'type']]
output_targets_df = output_targets_df.merge(
kpi_data, left_on='kpi_level_2_fk', right_on='pk', how='left')
return output_targets_df
@staticmethod
def unpack_external_targets_json_fields_to_df(input_df, field_name):
data_list = []
for i, row in input_df.iterrows():
data_item = json.loads(row[field_name]) if row[field_name] else {}
data_item.update({'pk': row.pk})
data_list.append(data_item)
output_df = pd.DataFrame(data_list)
return output_df
def unpack_all_external_targets(self):
targets_df = self.external_targets.drop_duplicates(subset=[
'operation_type', 'kpi_level_2_fk', 'key_json', 'data_json'
])
output_targets = pd.DataFrame(
columns=targets_df.columns.values.tolist())
if not targets_df.empty:
keys_df = self.unpack_external_targets_json_fields_to_df(
targets_df, field_name='key_json')
data_df = self.unpack_external_targets_json_fields_to_df(
targets_df, field_name='data_json')
targets_df = targets_df.merge(keys_df, on='pk', how='left')
targets_df = targets_df.merge(data_df, on='pk', how='left')
kpi_data = self.kpi_static_data[['pk', 'type']]
kpi_data.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True)
output_targets = targets_df.merge(kpi_data,
on='kpi_level_2_fk',
how='left')
if output_targets.empty:
Log.warning('KPI External Targets Results are empty')
return output_targets
def get_kpi_result_value_pk_by_value(self, value):
pk = None
try:
pk = self.kpi_result_values[self.kpi_result_values['value'] ==
value]['pk'].values[0]
except:
Log.error('Value {} does not exist'.format(value))
return pk
def get_kpi_score_value_pk_by_value(self, value):
pk = None
try:
pk = self.kpi_score_values[self.kpi_score_values['value'] ==
value]['pk'].values[0]
except:
Log.error('Value {} does not exist'.format(value))
return pk
def get_yes_no_score(self, score):
value = 'NO' if not score else 'YES'
custom_score = self.get_kpi_score_value_pk_by_value(value)
return custom_score
def get_yes_no_result(self, result):
value = 'NO' if not result else 'YES'
custom_score = self.get_kpi_result_value_pk_by_value(value)
return custom_score
def set_filtered_scif_and_matches_for_specific_kpi(self, scif, matches,
kpi):
try:
kpi = int(float(kpi))
except ValueError:
kpi = kpi
if isinstance(kpi, int):
kpi = self.get_kpi_type_by_pk(kpi)
if self.do_exclusion_rules_apply_to_store(kpi):
excl_template_for_kpi = self.exclusion_template[
self.exclusion_template['KPI'] == kpi]
if not excl_template_for_kpi.empty:
template_filters = self.get_filters_dictionary(
excl_template_for_kpi)
scif, matches = self.filter_scif_and_matches_for_scene_and_product_filters(
template_filters, scif, matches)
return scif, matches
def get_kpi_type_by_pk(self, kpi_fk):
try:
kpi_fk = int(float(kpi_fk))
return self.kpi_static_data[self.kpi_static_data['pk'] ==
kpi_fk]['type'].values[0]
except IndexError:
Log.info(
"Kpi name: {} is not equal to any kpi name in static table".
format(kpi_fk))
return None
class INBEVMXToolBox:
def __init__(self, data_provider, output):
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.common_v2 = Common_V2(self.data_provider)
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.tools = GENERALToolBox(self.data_provider)
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.survey = Survey(self.data_provider, self.output)
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common_v2.get_kpi_static_data()
self.kpi_results_queries = []
self.kpi_results_new_tables_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.oos_policies = self.get_policies()
self.result_dict = {}
self.hierarchy_dict = {}
try:
self.store_type_filter = self.store_info['store_type'].values[
0].strip()
except:
Log.error("there is no store type in the db")
return
try:
self.region_name_filter = self.store_info['region_name'].values[
0].strip()
self.region_fk = self.store_info['region_fk'].values[0]
except:
Log.error("there is no region in the db")
return
try:
self.att6_filter = self.store_info[
'additional_attribute_6'].values[0].strip()
except:
Log.error("there is no additional attribute 6 in the db")
return
self.sos_target_sheet = pd.read_excel(PATH_SURVEY_AND_SOS_TARGET,
Const.SOS_TARGET).fillna("")
self.survey_sheet = pd.read_excel(PATH_SURVEY_AND_SOS_TARGET,
Const.SURVEY).fillna("")
self.survey_combo_sheet = pd.read_excel(PATH_SURVEY_AND_SOS_TARGET,
Const.SURVEY_COMBO).fillna("")
self.oos_sheet = pd.read_excel(PATH_SURVEY_AND_SOS_TARGET,
Const.OOS_KPI).fillna("")
def get_policies(self):
query = INBEVMXQueries.get_policies()
policies = pd.read_sql_query(query, self.rds_conn.db)
return policies
def main_calculation(self):
"""
This function calculates the KPI results.
"""
kpis_sheet = pd.read_excel(PATH_SURVEY_AND_SOS_TARGET,
Const.KPIS).fillna("")
for index, row in kpis_sheet.iterrows():
self.handle_atomic(row)
self.save_parent_kpis()
self.common_v2.commit_results_data()
def calculate_oos_target(self):
temp = self.oos_sheet[Const.TEMPLATE_STORE_TYPE]
rows_stores_filter = self.oos_sheet[
temp.apply(lambda r: self.store_type_filter in
[item.strip() for item in r.split(",")])]
if rows_stores_filter.empty:
weight = 0
else:
weight = rows_stores_filter[Const.TEMPLATE_SCORE].values[0]
all_data = pd.merge(
self.scif[["store_id", "product_fk", "facings", "template_name"]],
self.store_info,
left_on="store_id",
right_on="store_fk")
if all_data.empty:
return 0
json_policies = self.oos_policies.copy()
json_policies[Const.POLICY] = self.oos_policies[Const.POLICY].apply(
lambda line: json.loads(line))
diff_policies = json_policies[
Const.POLICY].drop_duplicates().reset_index()
diff_table = json_normalize(diff_policies[Const.POLICY].tolist())
# remove all lists from df
diff_table = diff_table.applymap(lambda x: x[0]
if isinstance(x, list) else x)
for col in diff_table.columns:
att = all_data.iloc[0][col]
if att is None:
return 0
diff_table = diff_table[diff_table[col] == att]
all_data = all_data[all_data[col] == att]
if len(diff_table) > 1:
Log.warning("There is more than one possible match")
return 0
if diff_table.empty:
return 0
selected_row = diff_policies.iloc[diff_table.index[0]][Const.POLICY]
json_policies = json_policies[json_policies[Const.POLICY] ==
selected_row]
products_to_check = json_policies['product_fk'].tolist()
products_df = all_data[(
all_data['product_fk'].isin(products_to_check))][[
'product_fk', 'facings'
]].fillna(0)
products_df = products_df.groupby('product_fk').sum().reset_index()
try:
atomic_pk_sku = self.common_v2.get_kpi_fk_by_kpi_name(
Const.OOS_SKU_KPI)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
Const.OOS_SKU_KPI)
return 0
for product in products_to_check:
if product not in products_df['product_fk'].values:
products_df = products_df.append(
{
'product_fk': product,
'facings': 0.0
}, ignore_index=True)
for index, row in products_df.iterrows():
result = 0 if row['facings'] > 0 else 1
self.common_v2.write_to_db_result(fk=atomic_pk_sku,
numerator_id=row['product_fk'],
numerator_result=row['facings'],
denominator_id=self.store_id,
result=result,
score=result,
identifier_parent=Const.OOS_KPI,
should_enter=True,
parent_fk=3)
not_existing_products_len = len(
products_df[products_df['facings'] == 0])
result = not_existing_products_len / float(len(products_to_check))
try:
atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(Const.OOS_KPI)
result_oos_pk = self.common_v2.get_kpi_fk_by_kpi_name(
Const.OOS_RESULT_KPI)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
Const.OOS_KPI)
return 0
score = result * weight
self.common_v2.write_to_db_result(
fk=atomic_pk,
numerator_id=self.region_fk,
numerator_result=not_existing_products_len,
denominator_id=self.store_id,
denominator_result=len(products_to_check),
result=result,
score=score,
identifier_result=Const.OOS_KPI,
parent_fk=3)
self.common_v2.write_to_db_result(
fk=result_oos_pk,
numerator_id=self.region_fk,
numerator_result=not_existing_products_len,
denominator_id=self.store_id,
denominator_result=len(products_to_check),
result=result,
score=result,
parent_fk=3)
return score
def save_parent_kpis(self):
for kpi in self.result_dict.keys():
try:
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(kpi)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " + kpi)
continue
if kpi not in self.hierarchy_dict:
self.common_v2.write_to_db_result(fk=kpi_fk,
numerator_id=self.region_fk,
denominator_id=self.store_id,
result=self.result_dict[kpi],
score=self.result_dict[kpi],
identifier_result=kpi,
parent_fk=1)
else:
self.common_v2.write_to_db_result(
fk=kpi_fk,
numerator_id=self.region_fk,
denominator_id=self.store_id,
result=self.result_dict[kpi],
score=self.result_dict[kpi],
identifier_result=kpi,
identifier_parent=self.hierarchy_dict[kpi],
should_enter=True,
parent_fk=2)
def handle_atomic(self, row):
result = 0
atomic_id = row[Const.TEMPLATE_KPI_ID]
atomic_name = row[Const.KPI_LEVEL_3].strip()
kpi_name = row[Const.KPI_LEVEL_2].strip()
set_name = row[Const.KPI_LEVEL_1].strip()
kpi_type = row[Const.TEMPLATE_KPI_TYPE].strip()
if atomic_name != kpi_name:
parent_name = kpi_name
else:
parent_name = set_name
if kpi_type == Const.SOS_TARGET:
if self.scene_info['number_of_probes'].sum() > 1:
result = self.handle_sos_target_atomics(
atomic_id, atomic_name, parent_name)
elif kpi_type == Const.SURVEY:
result = self.handle_survey_atomics(atomic_id, atomic_name,
parent_name)
elif kpi_type == Const.SURVEY_COMBO:
result = self.handle_survey_combo(atomic_id, atomic_name,
parent_name)
elif kpi_type == Const.OOS_KPI:
result = self.calculate_oos_target()
# Update kpi results
if atomic_name != kpi_name:
if kpi_name not in self.result_dict.keys():
self.result_dict[kpi_name] = result
self.hierarchy_dict[kpi_name] = set_name
else:
self.result_dict[kpi_name] += result
# Update set results
if set_name not in self.result_dict.keys():
self.result_dict[set_name] = result
else:
self.result_dict[set_name] += result
def handle_sos_target_atomics(self, atomic_id, atomic_name, parent_name):
denominator_number_of_total_facings = 0
count_result = -1
# bring the kpi rows from the sos sheet
rows = self.sos_target_sheet.loc[self.sos_target_sheet[
Const.TEMPLATE_KPI_ID] == atomic_id]
# get a single row
row = self.find_row(rows)
if row.empty:
return 0
target = row[Const.TEMPLATE_TARGET_PRECENT].values[0]
score = row[Const.TEMPLATE_SCORE].values[0]
df = pd.merge(self.scif,
self.store_info,
how="left",
left_on="store_id",
right_on="store_fk")
# get the filters
filters = self.get_filters_from_row(row.squeeze())
numerator_number_of_facings = self.count_of_facings(df, filters)
if numerator_number_of_facings != 0 and count_result == -1:
if 'manufacturer_name' in filters.keys():
deno_manufacturer = row[
Const.TEMPLATE_MANUFACTURER_DENOMINATOR].values[0].strip()
deno_manufacturer = deno_manufacturer.split(",")
filters['manufacturer_name'] = [
item.strip() for item in deno_manufacturer
]
denominator_number_of_total_facings = self.count_of_facings(
df, filters)
percentage = 100 * (numerator_number_of_facings /
denominator_number_of_total_facings)
count_result = score if percentage >= target else -1
if count_result == -1:
return 0
try:
atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return 0
self.common_v2.write_to_db_result(
fk=atomic_pk,
numerator_id=self.region_fk,
numerator_result=numerator_number_of_facings,
denominator_id=self.store_id,
denominator_result=denominator_number_of_total_facings,
result=count_result,
score=count_result,
identifier_result=atomic_name,
identifier_parent=parent_name,
should_enter=True,
parent_fk=3)
return count_result
def find_row(self, rows):
temp = rows[Const.TEMPLATE_STORE_TYPE]
rows_stores_filter = rows[(
temp.apply(lambda r: self.store_type_filter in
[item.strip() for item in r.split(",")])) |
(temp == "")]
temp = rows_stores_filter[Const.TEMPLATE_REGION]
rows_regions_filter = rows_stores_filter[(
temp.apply(lambda r: self.region_name_filter in
[item.strip() for item in r.split(",")])) |
(temp == "")]
temp = rows_regions_filter[Const.TEMPLATE_ADDITIONAL_ATTRIBUTE_6]
rows_att6_filter = rows_regions_filter[(
temp.apply(lambda r: self.att6_filter in
[item.strip() for item in r.split(",")])) |
(temp == "")]
return rows_att6_filter
def get_filters_from_row(self, row):
filters = dict(row)
# no need to be accounted for
for field in Const.DELETE_FIELDS:
if field in filters:
del filters[field]
# filter all the empty cells
for key in filters.keys():
if (filters[key] == ""):
del filters[key]
elif isinstance(filters[key], tuple):
filters[key] = (filters[key][0].split(","), filters[key][1])
else:
filters[key] = filters[key].split(",")
filters[key] = [item.strip() for item in filters[key]]
return self.create_filters_according_to_scif(filters)
def create_filters_according_to_scif(self, filters):
convert_from_scif = {
Const.TEMPLATE_GROUP: 'template_group',
Const.TEMPLATE_MANUFACTURER_NOMINATOR: 'manufacturer_name',
Const.TEMPLATE_ADDITIONAL_ATTRIBUTE_6: 'additional_attribute_6'
}
for key in filters.keys():
if key in convert_from_scif:
filters[convert_from_scif[key]] = filters.pop(key)
return filters
def count_of_facings(self, df, filters):
facing_data = df[self.tools.get_filter_condition(df, **filters)]
number_of_facings = facing_data['facings'].sum()
return number_of_facings
def handle_survey_combo(self, atomic_id, atomic_name, parent_name):
# bring the kpi rows from the survey sheet
numerator = denominator = 0
rows = self.survey_combo_sheet.loc[self.survey_combo_sheet[
Const.TEMPLATE_KPI_ID] == atomic_id]
temp = rows[Const.TEMPLATE_STORE_TYPE]
row_store_filter = rows[(
temp.apply(lambda r: self.store_type_filter in
[item.strip() for item in r.split(",")])) |
(temp == "")]
if row_store_filter.empty:
return 0
condition = row_store_filter[Const.TEMPLATE_CONDITION].values[0]
condition_type = row_store_filter[
Const.TEMPLATE_CONDITION_TYPE].values[0]
score = row_store_filter[Const.TEMPLATE_SCORE].values[0]
# find the answer to the survey in session
for i, row in row_store_filter.iterrows():
question_text = row[Const.TEMPLATE_SURVEY_QUESTION_TEXT]
question_answer_template = row[Const.TEMPLATE_TARGET_ANSWER]
survey_result = self.survey.get_survey_answer(
('question_text', question_text))
if not survey_result:
continue
if '-' in question_answer_template:
numbers = question_answer_template.split('-')
try:
numeric_survey_result = int(survey_result)
except:
Log.warning("Survey question - " + str(question_text) +
" - doesn't have a numeric result")
continue
if numeric_survey_result < int(
numbers[0]) or numeric_survey_result > int(numbers[1]):
continue
numerator_or_denominator = row_store_filter[
Const.NUMERATOR_OR_DENOMINATOR].values[0]
if numerator_or_denominator == Const.DENOMINATOR:
denominator += numeric_survey_result
else:
numerator += numeric_survey_result
else:
continue
if condition_type == '%':
if denominator != 0:
fraction = 100 * (float(numerator) / float(denominator))
else:
if numerator > 0:
fraction = 100
else:
fraction = 0
result = score if fraction >= condition else 0
else:
return 0
try:
atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return 0
self.common_v2.write_to_db_result(fk=atomic_pk,
numerator_id=self.region_fk,
numerator_result=numerator,
denominator_result=denominator,
denominator_id=self.store_id,
result=result,
score=result,
identifier_result=atomic_name,
identifier_parent=parent_name,
should_enter=True,
parent_fk=3)
return result
def handle_survey_atomics(self, atomic_id, atomic_name, parent_name):
# bring the kpi rows from the survey sheet
rows = self.survey_sheet.loc[self.survey_sheet[Const.TEMPLATE_KPI_ID]
== atomic_id]
temp = rows[Const.TEMPLATE_STORE_TYPE]
row_store_filter = rows[(
temp.apply(lambda r: self.store_type_filter in
[item.strip() for item in r.split(",")])) |
(temp == "")]
if row_store_filter.empty:
return 0
else:
# find the answer to the survey in session
question_text = row_store_filter[
Const.TEMPLATE_SURVEY_QUESTION_TEXT].values[0]
question_answer_template = row_store_filter[
Const.TEMPLATE_TARGET_ANSWER].values[0]
score = row_store_filter[Const.TEMPLATE_SCORE].values[0]
survey_result = self.survey.get_survey_answer(
('question_text', question_text))
if not survey_result:
return 0
if '-' in question_answer_template:
numbers = question_answer_template.split('-')
try:
numeric_survey_result = int(survey_result)
except:
Log.warning("Survey question - " + str(question_text) +
" - doesn't have a numeric result")
return 0
if numeric_survey_result < int(
numbers[0]) or numeric_survey_result > int(numbers[1]):
return 0
condition = row_store_filter[
Const.TEMPLATE_CONDITION].values[0]
if condition != "":
second_question_text = row_store_filter[
Const.TEMPLATE_SECOND_SURVEY_QUESTION_TEXT].values[0]
second_survey_result = self.survey.get_survey_answer(
('question_text', second_question_text))
if not second_survey_result:
second_survey_result = 0
second_numeric_survey_result = int(second_survey_result)
survey_result = 1 if numeric_survey_result >= second_numeric_survey_result else -1
else:
survey_result = 1
else:
question_answer_template = question_answer_template.split(',')
question_answer_template = [
item.strip() for item in question_answer_template
]
if survey_result in question_answer_template:
survey_result = 1
else:
survey_result = -1
final_score = score if survey_result == 1 else 0
try:
atomic_pk = self.common_v2.get_kpi_fk_by_kpi_name(atomic_name)
except IndexError:
Log.warning("There is no matching Kpi fk for kpi name: " +
atomic_name)
return 0
self.common_v2.write_to_db_result(fk=atomic_pk,
numerator_id=self.region_fk,
numerator_result=0,
denominator_result=0,
denominator_id=self.store_id,
result=survey_result,
score=final_score,
identifier_result=atomic_name,
identifier_parent=parent_name,
should_enter=True,
parent_fk=3)
return final_score
def get_new_kpi_static_data(self):
"""
This function extracts the static new KPI data (new tables) and saves it into one global data frame.
The data is taken from static.kpi_level_2.
"""
query = INBEVMXQueries.get_new_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
class PNGHKToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
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_info = self.data_provider[Data.STORE_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.match_probe_in_scene = self.get_product_special_attribute_data(
self.session_uid)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.kpis_sheet = pd.read_excel(PATH, Const.KPIS).fillna("")
self.osd_rules_sheet = pd.read_excel(PATH, Const.OSD_RULES).fillna("")
self.kpi_excluding = pd.DataFrame()
self.df = pd.DataFrame()
self.tools = GENERALToolBox(self.data_provider)
self.templates = self.data_provider[Data.ALL_TEMPLATES]
self.psdataprovider = PsDataProvider(self.data_provider)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
if self.match_product_in_scene.empty or self.products.empty:
return
df = pd.merge(self.match_product_in_scene,
self.products,
on="product_fk",
how="left")
exclude_products = self.psdataprovider.get_exclude_products_from_reporting_configuration(
)
exclude_products = exclude_products[
exclude_products['exclude_include_set_fk'] == 1]
exclude_products_list = exclude_products['product_fk'].tolist()
df = df[~df['product_fk'].isin(exclude_products_list)]
distinct_session_fk = self.scif[[
'scene_fk', 'template_name', 'template_fk'
]].drop_duplicates()
self.df = pd.merge(df, distinct_session_fk, on="scene_fk", how="left")
kpi_ids = self.kpis_sheet[Const.KPI_ID].drop_duplicates().tolist()
for id in kpi_ids:
kpi_df = self.kpis_sheet[self.kpis_sheet[Const.KPI_ID] == id]
self.handle_atomic(kpi_df)
self.common.commit_results_data()
def handle_atomic(self, kpi_df):
kpi_type = kpi_df[Const.KPI_TYPE].values[0].strip()
if kpi_type == Const.FSOS:
self.calculate_facings_sos_kpi(kpi_df)
if kpi_type == Const.LSOS:
self.calculate_linear_sos_kpi(kpi_df)
if kpi_type == Const.DISPLAY_NUMBER:
self.calculate_display_kpi(kpi_df)
def calculate_display_kpi(self, kpi_df):
total_numerator = 0
total_denominator = 0
total_save = True
kpi_name = kpi_df[Const.KPI_NAME].values[0]
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name)
if kpi_fk is None:
Log.warning("There is no matching Kpi fk for kpi name: " +
kpi_name)
return
for i, row in kpi_df.iterrows():
self.kpi_excluding = row[[
Const.EXCLUDE_EMPTY, Const.EXCLUDE_HANGER,
Const.EXCLUDE_IRRELEVANT, Const.EXCLUDE_POSM,
Const.EXCLUDE_OTHER, Const.STACKING, Const.EXCLUDE_SKU,
Const.EXCLUDE_STOCK, Const.EXCLUDE_OSD
]]
per_scene_type = row[Const.PER_SCENE_TYPE]
df = self.filter_df(row)
if per_scene_type == Const.EACH:
total_save = False
scene_types = row[Const.SCENE_TYPE].split(',')
if scene_types != "":
scene_types = [item.strip() for item in scene_types]
for sc in scene_types:
df_scene = df[df['template_name'] == sc]
denominator = len(set(df_scene['scene_fk']))
if denominator == 0:
continue
numerator = len(
set(df_scene[df_scene['product_type'] == 'SKU']
['scene_fk']))
result = float(numerator) / float(denominator)
context_id = df_scene['template_fk'].values[0]
self.common.write_to_db_result(
fk=kpi_fk,
numerator_id=self.store_id,
denominator_id=self.store_id,
context_id=context_id,
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
else:
total_numerator += len(
set(df[df['product_type'] == 'SKU']['scene_fk']))
total_denominator += len(set(df['scene_fk']))
if total_save:
result = float(total_numerator) / float(total_denominator) if (
total_denominator != 0) else 0
self.common.write_to_db_result(
fk=kpi_fk,
numerator_id=self.store_id,
denominator_id=self.store_id,
numerator_result=total_numerator,
denominator_result=total_denominator,
result=result,
score=result)
def calculate_facings_sos_kpi(self, kpi_df):
kpi_name = kpi_df[Const.KPI_NAME].values[0]
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name)
if kpi_fk is None:
Log.warning("There is no matching Kpi fk for kpi name: " +
kpi_name)
return
entity_name = kpi_df[Const.NUMERATOR_ENTITY].values[0]
entity_name_for_fk = Const.NAME_TO_FK[entity_name]
# iterate all categories (if kpi_df length > 1)
for i, row in kpi_df.iterrows():
filters = {}
self.kpi_excluding = row[[
Const.EXCLUDE_EMPTY, Const.EXCLUDE_HANGER,
Const.EXCLUDE_IRRELEVANT, Const.EXCLUDE_POSM,
Const.EXCLUDE_OTHER, Const.STACKING, Const.EXCLUDE_SKU,
Const.EXCLUDE_STOCK, Const.EXCLUDE_OSD
]]
df = self.filter_df(row)
if df.empty:
continue
category = row[Const.CATEGORY]
if category != "":
denominator_id = self.all_products[
self.all_products['category'] ==
category]['category_fk'].iloc[0]
filters['category'] = category
else:
denominator_id = self.store_id
all_denominators = df[entity_name].drop_duplicates().tolist()
if row[Const.NUMERATOR] != "":
all_denominators = [row[Const.NUMERATOR]]
denominator = self.tools.get_filter_condition(df, **filters).sum()
# iterate all enteties
for entity in all_denominators:
filters[entity_name] = entity
numerator = self.tools.get_filter_condition(df,
**filters).sum()
del filters[entity_name]
if numerator == 0:
continue
result = float(numerator) / float(denominator)
numerator_id = df[df[entity_name] ==
entity][entity_name_for_fk].values[0]
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
denominator_id=denominator_id,
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
def calculate_linear_sos_kpi(self, kpi_df):
ratio = 1
kpi_name = kpi_df[Const.KPI_NAME].values[0]
kpi_fk = self.common.get_kpi_fk_by_kpi_name(kpi_name,
get_numerator=False)
if kpi_fk is None:
Log.warning("There is no matching Kpi fk for kpi name: " +
kpi_name)
return
entity_name = kpi_df[Const.NUMERATOR_ENTITY].values[0]
entity_name_for_fk = Const.NAME_TO_FK[entity_name]
results_dict = {}
for i, row in kpi_df.iterrows():
filters = {}
scene_size = row[Const.SCENE_SIZE]
self.kpi_excluding = row[[
Const.EXCLUDE_EMPTY, Const.EXCLUDE_HANGER,
Const.EXCLUDE_IRRELEVANT, Const.EXCLUDE_POSM,
Const.EXCLUDE_OTHER, Const.STACKING, Const.EXCLUDE_SKU,
Const.EXCLUDE_STOCK, Const.EXCLUDE_OSD
]]
# filter df to the specific template row
df = self.filter_df(row)
if df.empty:
continue
if row[Const.PER_SCENE_TYPE] == Const.EACH:
scene_types = row[Const.SCENE_TYPE].split(",")
scene_types = [item.strip() for item in scene_types]
else:
scene_types = [""]
# Iterate scene types
for sc in scene_types:
if sc != "":
try:
context_id = self.templates[
self.templates['template_name'] ==
sc]['template_fk'].iloc[0]
except:
Log.warning("No scene type with the following name: " +
str(sc))
continue
filters['template_name'] = sc
else:
context_id = 0
category = row[Const.CATEGORY]
if category != "":
if category == Const.EACH:
categories = set(self.df['category'])
else:
categories = [category]
else:
categories = [""]
# Iterate categories
for category in categories:
if category != "":
denominator_id = self.all_products[
self.all_products['category'] ==
category]['category_fk'].iloc[0]
filters['category'] = category
all_numerators = self.df[
self.df['category'] == category][
entity_name].drop_duplicates().values.tolist()
else:
denominator_id = self.store_id
all_numerators = df[entity_name].drop_duplicates(
).values.tolist()
if row[Const.NUMERATOR] != "":
all_numerators = [row[Const.NUMERATOR]]
denominator = df[self.tools.get_filter_condition(
df, **filters)]['width_mm_advance'].sum()
if denominator == 0:
continue
if scene_size != "":
ratio = scene_size / denominator
denominator = scene_size
for entity in all_numerators:
filters[entity_name] = entity
numerator = df[self.tools.get_filter_condition(
df, **filters)]['width_mm_advance'].sum()
del filters[entity_name]
if scene_size != "":
numerator = numerator * ratio
try:
numerator_id = self.all_products[
self.all_products[entity_name] ==
entity][entity_name_for_fk].values[0]
except:
Log.warning("No entity in this name " + entity)
numerator_id = -1
if (numerator_id, denominator_id,
context_id) not in results_dict.keys():
results_dict[numerator_id, denominator_id,
context_id] = [
numerator, denominator
]
else:
results_dict[numerator_id, denominator_id,
context_id] = map(
sum,
zip(
results_dict[numerator_id,
denominator_id,
context_id],
[numerator, denominator]))
if len(results_dict) == 0:
return
results_as_df = pd.DataFrame.from_dict(results_dict, orient="index")
# numerator became column 0, denominator column 1, result will enter column 2
filtered_results_as_df = results_as_df[results_as_df[0] != 0]
filtered_df = filtered_results_as_df.copy()
filtered_df[2] = filtered_results_as_df[0] / filtered_results_as_df[1]
filtered_results_as_dict = filtered_df.to_dict(orient="index")
for numerator_id, denominator_id, context_id in filtered_results_as_dict.keys(
):
result_row = filtered_results_as_dict[numerator_id, denominator_id,
context_id]
result, numerator, denominator = result_row[2], result_row[
0], result_row[1]
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
denominator_id=denominator_id,
context_id=context_id,
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
def filter_df(self, kpi_df):
df = self.df.copy()
# filter scene_types
scene_types = kpi_df[Const.SCENE_TYPE].split(',')
if scene_types != "":
scene_types = [item.strip() for item in scene_types]
df = df[df['template_name'].isin(scene_types)]
# filter excludings
df = self.filter_excluding(df)
# filter category
category = kpi_df[Const.CATEGORY].strip()
if (category != "" and category != Const.EACH):
df = df[df['category'] == category]
return df
def filter_out_osd(self, df):
if df.empty:
return df
df_list = []
scene_types = set(df['template_name'])
for s in scene_types:
scene_df = df[df['template_name'] == s]
row = self.find_row_osd(s)
if row.empty:
df_list.append(scene_df)
continue
# if no osd rule is applied
if (row[Const.HAS_OSD].values[0]
== Const.NO) and (row[Const.HAS_HOTSPOT].values[0]
== Const.NO):
df_list.append(scene_df)
continue
# filter df to only shelf_to_include or higher shelves
shelfs_to_include = row[Const.OSD_NUMBER_OF_SHELVES].values[0]
if shelfs_to_include != "":
shelfs_to_include = int(shelfs_to_include)
scene_df = scene_df[
scene_df['shelf_number_from_bottom'] < shelfs_to_include]
# filter df to remove shelves with given ean code
if row[Const.HAS_OSD].values[0] == Const.YES:
products_to_filter = row[Const.POSM_EAN_CODE].values[0].split(
",")
if products_to_filter != "":
products_to_filter = [
item.strip() for item in products_to_filter
]
products_df = scene_df[scene_df['product_ean_code'].isin(
products_to_filter)][['scene_fk', 'shelf_number']]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = scene_df[~(
(scene_df['scene_fk'] == p['scene_fk']) &
(scene_df['shelf_number'] == p['shelf_number']))]
df_list.append(scene_df)
# filter df to remove shelves with given ean code (only on the same bay)
if row[Const.HAS_HOTSPOT].values[0] == Const.YES:
products_to_filter = row[
Const.POSM_EAN_CODE_HOTSPOT].values[0].split(",")
if products_to_filter != "":
products_to_filter = [
item.strip() for item in products_to_filter
]
products_df = scene_df[scene_df['product_ean_code'].isin(
products_to_filter)][[
'scene_fk', 'bay_number', 'shelf_number'
]]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = scene_df[~(
(scene_df['scene_fk'] == p['scene_fk']) &
(scene_df['bay_number'] == p['bay_number']) &
(scene_df['shelf_number'] == p['shelf_number']))]
df_list.append(scene_df)
final_df = pd.concat(df_list)
return final_df
def filter_in_osd(self, df):
df_list = []
scene_types = set(df['template_name'])
for s in scene_types:
scene_df = df[df['template_name'] == s]
const_scene_df = scene_df.copy()
row = self.find_row_osd(s)
if row.empty:
continue
# filter df include OSD when needed
shelfs_to_include = row[Const.OSD_NUMBER_OF_SHELVES].values[0]
if shelfs_to_include != "":
shelfs_to_include = int(shelfs_to_include)
df_list.append(scene_df[
scene_df['shelf_number_from_bottom'] >= shelfs_to_include])
# if no osd rule is applied
if row[Const.HAS_OSD].values[0] == Const.NO:
continue
# filter df to have only shelves with given ean code
if row[Const.HAS_OSD].values[0] == Const.YES:
products_to_filter = row[Const.POSM_EAN_CODE].values[0].split(
",")
if products_to_filter != "":
products_to_filter = [
item.strip() for item in products_to_filter
]
products_df = scene_df[scene_df['product_ean_code'].isin(
products_to_filter)][['scene_fk', 'shelf_number']]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = const_scene_df[(
(const_scene_df['scene_fk'] == p['scene_fk']) &
(const_scene_df['shelf_number']
== p['shelf_number']))]
df_list.append(scene_df)
if row[Const.HAS_HOTSPOT].values[0] == Const.YES:
products_to_filter = row[
Const.POSM_EAN_CODE_HOTSPOT].values[0].split(",")
if products_to_filter != "":
products_to_filter = [
item.strip() for item in products_to_filter
]
products_df = scene_df[scene_df['product_ean_code'].isin(
products_to_filter)][[
'scene_fk', 'bay_number', 'shelf_number'
]]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = const_scene_df[~(
(const_scene_df['scene_fk'] == p['scene_fk']) &
(const_scene_df['bay_number'] == p['bay_number']) &
(const_scene_df['shelf_number'] ==
p['shelf_number']))]
df_list.append(scene_df)
if len(df_list) != 0:
final_df = pd.concat(df_list)
else:
final_df = pd.DataFrame(columns=df.columns)
final_df = final_df.drop_duplicates()
return final_df
def find_row_osd(self, s):
rows = self.osd_rules_sheet[self.osd_rules_sheet[
Const.SCENE_TYPE].str.encode("utf8") == s.encode("utf8")]
row = rows[rows[Const.RETAILER] ==
self.store_info['retailer_name'].values[0]]
return row
def filter_excluding(self, df):
if self.kpi_excluding[Const.EXCLUDE_OSD] == Const.EXCLUDE:
df = self.filter_out_osd(df)
elif self.kpi_excluding[Const.EXCLUDE_SKU] == Const.EXCLUDE:
df = self.filter_in_osd(df)
if self.kpi_excluding[Const.EXCLUDE_STOCK] == Const.EXCLUDE:
df = self.exclude_special_attribute_products(
df, Const.DB_STOCK_NAME)
if self.kpi_excluding[Const.EXCLUDE_IRRELEVANT] == Const.EXCLUDE:
df = df[df['product_type'] != 'Irrelevant']
if self.kpi_excluding[Const.EXCLUDE_OTHER] == Const.EXCLUDE:
df = df[df['product_type'] != 'Other']
if self.kpi_excluding[Const.EXCLUDE_EMPTY] == Const.EXCLUDE:
df = df[df['product_type'] != 'Empty']
if self.kpi_excluding[Const.EXCLUDE_POSM] == Const.EXCLUDE:
df = df[df['product_type'] != 'POS']
if self.kpi_excluding[Const.STACKING] == Const.EXCLUDE:
df = df[df['stacking_layer'] == 1]
return df
def exclude_special_attribute_products(self, df, smart_attribute):
"""
Helper to exclude smart_attribute products
:return: filtered df without smart_attribute products
"""
if self.match_probe_in_scene.empty:
return df
smart_attribute_df = self.match_probe_in_scene[
self.match_probe_in_scene['name'] == smart_attribute]
if smart_attribute_df.empty:
return df
match_product_in_probe_fks = smart_attribute_df[
'match_product_in_probe_fk'].tolist()
df = df[~df['probe_match_fk'].isin(match_product_in_probe_fks)]
return df
def get_product_special_attribute_data(self, session_uid):
query = """
SELECT * FROM probedata.match_product_in_probe_state_value A
left join probedata.match_product_in_probe B on B.pk = A.match_product_in_probe_fk
left join static.match_product_in_probe_state C on C.pk = A.match_product_in_probe_state_fk
left join probedata.probe on probe.pk = probe_fk
where session_uid = '{}';
""".format(session_uid)
df = pd.read_sql_query(query, self.rds_conn.db)
return df
class CCAAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
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.templates = self.data_provider[Data.TEMPLATES]
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_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template = self.data_provider.all_templates # templates
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
self.calculate_bay_kpi()
self.common.commit_results_data_to_new_tables()
def calculate_sos(self):
"""
This function filtering Data frame - "scene item facts" by the parameters in the template.
Sending the filtered data frames to linear Sos calculation and facing Sos calculation
Writing the results to the new tables in DB
"""
facing_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
linear_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
den_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Denominator')]
den_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Denominator')]
num_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Numerator')]
num_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Numerator')]
scene_templates = self.scif['template_fk'].unique().tolist()
scene_manufactures = self.scif['manufacturer_fk'].unique().tolist()
# exclude filters denominator
den_general_facing_filters = self.create_dict_filters(
den_facing_exclude_template, self.EXCLUDE_FILTER)
den_general_linear_filters = self.create_dict_filters(
den_linear_exclude_template, self.EXCLUDE_FILTER)
# exclude filters numerator
num_general_facing_filters = self.create_dict_filters(
num_facing_exclude_template, self.EXCLUDE_FILTER)
num_general_linear_filters = self.create_dict_filters(
num_linear_exclude_template, self.EXCLUDE_FILTER)
df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template)
df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template)
df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template)
df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template)
for template in scene_templates:
for manufacture in scene_manufactures:
sos_filters = {
"template_fk": (template, self.INCLUDE_FILTER),
"manufacturer_fk": (manufacture, self.INCLUDE_FILTER)
}
tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)}
dict_num_facing = dict(
(k, v) for d in [sos_filters, num_general_facing_filters]
for k, v in d.items())
numerator_facings = self.calculate_share_space(
df_num_fac, dict_num_facing)[0]
dict_num_linear = dict(
(k, v) for d in [sos_filters, num_general_linear_filters]
for k, v in d.items())
numerator_linear = self.calculate_share_space(
df_num_lin, dict_num_linear)[1]
dict_den_facing = dict(
(k, v) for d in [tem_filters, den_general_facing_filters]
for k, v in d.items())
denominator_facings = self.calculate_share_space(
df_den_fac, dict_den_facing)[0]
dict_den_linear = dict(
(k, v) for d in [tem_filters, den_general_linear_filters]
for k, v in d.items())
denominator_linear = self.calculate_share_space(
df_den_lin, dict_den_linear)[1]
score_facing = 0 if denominator_facings == 0 else (
numerator_facings / denominator_facings) * 100
score_linear = 0 if denominator_linear == 0 else (
numerator_linear / denominator_linear) * 100
self.common.write_to_db_result_new_tables(
facing_kpi_fk, manufacture, numerator_facings,
score_facing, template, denominator_facings, score_facing)
self.common.write_to_db_result_new_tables(
linear_kpi_fk, manufacture, numerator_linear, score_linear,
template, denominator_linear, score_linear)
def create_dict_filters(self, template, param):
"""
:param template : Template of the desired filtering to data frame
:param param : exclude /include
:return: Dictionary of filters and parameter : exclude / include by demeaned
"""
filters_dict = {}
template_without_second = template[template['Param 2'].isnull()]
for row in template_without_second.iterrows():
filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','),
param)
return filters_dict
def filter_2_cond(self, data_frame, template):
"""
:param template: Template of the desired filtering
:param data_frame : Data frame
:return: data frame filtered by entries in the template with 2 conditions
"""
template_without_second = template[template['Param 2'].notnull()]
if template_without_second is not None:
for row in template_without_second.iterrows():
data_frame = data_frame.loc[
(~data_frame[row[1]['Param 1']].isin(row[1]['Value 1'].
split(','))) |
(~data_frame[row[1]['Param 2']].isin(row[1]['Value 2'].
split(',')))]
return data_frame
def calculate_share_space(self, data_frame, filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param data_frame : relevant scene item facts data frame (filtered )
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = data_frame[self.toolbox.get_filter_condition(
data_frame, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['gross_len_split_stack'].sum()
return sum_of_facings, space_length
def calculate_bay_kpi(self):
bay_kpi_sheet = self.bay_count_kpi
kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] ==
BAY_COUNT_KPI]
if kpi.empty:
Log.info("CCAAU Calculate KPI Name:{} not found in DB".format(
BAY_COUNT_KPI))
else:
Log.info("CCAAU Calculate KPI Name:{} found in DB".format(
BAY_COUNT_KPI))
bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] ==
BAY_COUNT_KPI]
if not bay_kpi_row.empty:
scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0]
if scene_types_to_consider == '*':
# Consider all scene types
scene_types_to_consider = 'all'
else:
scene_types_to_consider = [
x.strip() for x in scene_types_to_consider.split(',')
]
mpis_with_scene = self.match_product_in_scene.merge(
self.scene_info, how='left', on='scene_fk')
mpis_with_scene_and_template = mpis_with_scene.merge(
self.templates, how='left', on='template_fk')
if scene_types_to_consider != 'all':
mpis_with_scene_and_template = mpis_with_scene_and_template[
mpis_with_scene_and_template['template_name'].isin(
scene_types_to_consider)]
mpis_template_group = mpis_with_scene_and_template.groupby(
'template_fk')
for template_fk, template_data in mpis_template_group:
Log.info("Running for template ID {templ_id}".format(
templ_id=template_fk, ))
total_bays_for_scene_type = 0
scene_group = template_data.groupby('scene_fk')
for scene_fk, scene_data in scene_group:
Log.info(
"KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=int(scene_data['bay_number'].max()),
scene_id=scene_fk,
))
total_bays_for_scene_type += int(
scene_data['bay_number'].max())
Log.info(
"KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=total_bays_for_scene_type,
templ_id=template_fk,
))
self.common.write_to_db_result_new_tables(
fk=int(kpi['pk'].iloc[0]),
numerator_id=int(template_fk),
numerator_result=total_bays_for_scene_type,
denominator_id=int(self.store_id),
denominator_result=total_bays_for_scene_type,
result=total_bays_for_scene_type,
)
class SOLARBRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_EMPTY = False
EXCLUDE_FILTER = 0
EMPTY = 'Empty'
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.k_engine = BaseCalculationsGroup(data_provider, output)
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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.templates = {}
self.session_id = self.data_provider.session_id
self.score_templates = {}
self.get_templates()
self.get_score_template()
self.manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0]
self.sos = SOS(self.data_provider, self.output)
self.total_score = 0
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.toolbox = GENERALToolBox(self.data_provider)
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.kpi_results_new_tables_queries = []
# self.store_type = self.data_provider.store_type
def get_templates(self):
for sheet in Const.SHEETS_MAIN:
self.templates[sheet] = pd.read_excel(MAIN_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False)
def get_score_template(self):
for sheet in Const.SHEETS_SCORE:
self.score_templates[sheet] = pd.read_excel(SCORE_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False, encoding = "utf-8")
def main_calculation(self, *args, **kwargs):
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
self.calculate_main_kpi(main_line)
self.commit_results()
def calculate_main_kpi(self, main_line):
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.Type]
scene_types = self.does_exist(main_line, Const.SCENE_TYPES)
result = score = 0
general_filters = {}
scif_scene_types = self.scif['template_name'].unique().tolist()
store_type = str(self.store_info["store_type"].iloc[0])
store_types = self.does_exist_store(main_line, Const.STORE_TYPES)
if store_type in store_types:
if scene_types:
if (('All' in scene_types) or bool(set(scif_scene_types) & set(scene_types))) :
if not ('All' in scene_types):
general_filters['template_name'] = scene_types
if kpi_type == Const.SOVI:
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name]
if relevant_template["numerator param 1"].all() and relevant_template["denominator param"].all():
function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score = function(kpi_line, general_filters)
else:
pass
else:
pass
@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 cell.split(", ")
return None
@staticmethod
def does_exist_store(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 cell.split(",")
return None
def calculate_sos(self, kpi_line, general_filters):
kpi_name = kpi_line[Const.KPI_NAME]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1].split(',')
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1].split(',')
general_filters[den_type] = den_value
sos_filters = {num_type : num_value}
if kpi_line[Const.NUM_TYPES_2]:
num_type_2 = kpi_line[Const.NUM_TYPES_2]
num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',')
sos_filters[num_type_2] = num_value_2
sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
sos_value = round(sos_value, 2)
score = self.get_score_from_range(kpi_name, sos_value)
manufacturer_products = self.all_products[
self.all_products['manufacturer_name'] == num_value[0]].iloc[0]
manufacturer_fk = manufacturer_products["manufacturer_fk"]
all_products = self.all_products[
self.all_products['category'] == den_value[0]].iloc[0]
category_fk = all_products["category_fk"]
numerator_res, denominator_res = self.get_numerator_and_denominator(sos_filters, **general_filters)
self.common.write_to_db_result_new_tables(fk = 1,
numerator_id=manufacturer_fk,
numerator_result= numerator_res,
denominator_id=category_fk,
denominator_result= denominator_res,
result=sos_value,
score= score,
score_after_actions= score)
return sos_value, score
def get_score_from_range(self, kpi_name, sos_value):
store_type = str(self.store_info["store_type"].iloc[0])
self.score_templates[store_type] = self.score_templates[store_type].replace(kpi_name, kpi_name.encode("utf-8"))
score_range = self.score_templates[store_type].query('Kpi == "' + str(kpi_name.encode("utf-8")) +
'" & Low <= ' + str(sos_value) +
' & High >= ' + str(sos_value)+'')
score = score_range['Score'].iloc[0]
return score
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function .encode('utf-8')
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOVI:
return self.calculate_sos
else:
Log.warning("The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def round_result(result):
return round(result, 3)
def get_numerator_and_denominator(self, sos_filters=None, include_empty=False, **general_filters):
if include_empty == self.EXCLUDE_EMPTY and 'product_type' not in sos_filters.keys() + general_filters.keys():
general_filters['product_type'] = (self.EMPTY, self.EXCLUDE_FILTER)
pop_filter = self.toolbox.get_filter_condition(self.scif, **general_filters)
subset_filter = self.toolbox.get_filter_condition(self.scif, **sos_filters)
try:
pop = self.scif
filtered_population = pop[pop_filter]
if filtered_population.empty:
return 0,0
else:
subset_population = filtered_population[subset_filter]
# ratio = TBox.calculate_ratio_sum_field_in_rows(filtered_population, subset_population, Fd.FACINGS)
df = filtered_population
subset_df = subset_population
sum_field = Fd.FACINGS
try:
Validation.is_empty_df(df)
Validation.is_empty_df(subset_df)
Validation.is_subset(df, subset_df)
Validation.df_columns_equality(df, subset_df)
Validation.validate_columns_exists(df, [sum_field])
Validation.validate_columns_exists(subset_df, [sum_field])
Validation.is_none(sum_field)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
# raise Exception(msg)
default_value = 0
numerator = TBox.calculate_frame_column_sum(subset_df, sum_field, default_value)
denominator = TBox.calculate_frame_column_sum(df, sum_field, default_value)
return numerator, denominator
except Exception as e:
Log.error(e.message)
return True
def commit_results(self):
insert_queries = self.merge_insert_queries(self.kpi_results_new_tables_queries)
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_query = SOLARBRQueries.get_delete_session_results_query(self.session_uid, self.session_id)
cur.execute(delete_query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(group, ',\n'.join(query_groups[group])))
return merged_queries
