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