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 ALTRIAUSToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.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.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.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting() self.kpi_results_queries = [] self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider) self.active_kpis = self._get_active_kpis() self.external_targets = self.ps_data_provider.get_kpi_external_targets() self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value() self._add_smart_attributes_to_mpis() self.scene_graphs = {} self.excessive_flipsigns = False self.incorrect_tags_in_pos_areas = [] def _add_smart_attributes_to_mpis(self): smart_attribute_data = \ self.adp.get_match_product_in_probe_state_values(self.mpis['probe_match_fk'].unique().tolist()) self.mpis = pd.merge(self.mpis, smart_attribute_data, on='probe_match_fk', how='left') self.mpis['match_product_in_probe_state_fk'].fillna(0, inplace=True) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ self.generate_graph_per_scene() for graph in self.scene_graphs.values(): self.calculate_fixture_and_block_level_kpis(graph) self.calculate_register_type() self.calculate_age_verification() self.calculate_facings_by_scene_type() self.calculate_session_flags() return def calculate_fixture_and_block_level_kpis(self, graph): for node, node_data in graph.nodes(data=True): if node_data['category'].value != 'POS': self.calculate_fixture_width(node_data) self.calculate_flip_sign(node, node_data, graph) self.calculate_flip_sign_empty_space(node, node_data, graph) self.calculate_flip_sign_locations(node_data) self.calculate_total_shelves(node_data) self.calculate_tags_by_fixture_block(node_data) if node_data['block_number'].value == 1: self.calculate_header(node, node_data, graph) self.calculate_product_above_headers(node_data) self.calculate_no_headers(node_data) return def generate_graph_per_scene(self): relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space'] for scene in relevant_scif['scene_id'].unique().tolist(): agb = AltriaGraphBuilder(self.data_provider, scene) self.scene_graphs[scene] = agb.get_graph() if agb.incorrect_tags_in_pos_area: self.incorrect_tags_in_pos_areas.extend(agb.incorrect_tags_in_pos_area) if len(self.scene_graphs.keys()) > 1: Log.warning("More than one Tobacco Merchandising Space scene detected. Results could be mixed!") return def calculate_fixture_width(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Fixture Width') width = node_data['calculated_width_ft'].value width = round(width) fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value category_fk = self.get_category_fk_by_name(node_data['category'].value) self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=width) def calculate_flip_sign(self, node, node_data, graph): if node_data['category'].value == 'Cigarettes': self.calculate_flip_signs_cigarettes(node, node_data, graph) else: self.calculate_flip_signs_non_cigarettes(node, node_data, graph) return def calculate_flip_signs_non_cigarettes(self, node, node_data, graph): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value flip_signs_by_x_coord = {} for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Flip-Sign': continue center_x = neighbor_data['polygon'].centroid.coords[0][0] flip_signs_by_x_coord[center_x] = neighbor_data for i, pair in enumerate(sorted(flip_signs_by_x_coord.items())): position = i+1 position_fk = self.get_custom_entity_pk(str(position)) if position_fk == 0 or position > 8: self.excessive_flipsigns = True Log.warning('More than 8 flip-sign positions found for a non-cigarettes block') product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value implied_facings = pair[1]['width_of_signage_in_facings'].value width = self.round_threshold(width) self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=implied_facings) def calculate_flip_signs_cigarettes(self, node, node_data, graph): width = node_data['calculated_width_ft'].value fixture_bounds = node_data['polygon'].bounds fixture_width_coord_units = abs(fixture_bounds[0] - fixture_bounds[2]) proportions_dict = self.get_flip_sign_position_proportions(round(width)) if not proportions_dict: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value f_slot_boxes = {} left_bound = fixture_bounds[0] # start proportional divisions on left side of fixture for position, proportion in proportions_dict.items(): right_bound = left_bound + (fixture_width_coord_units * proportion) # TODO update this to use min and max height of graph f_slot_boxes[position] = box(left_bound, -9999, right_bound, 9999) left_bound = right_bound for position, slot_box in f_slot_boxes.items(): for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] try: if neighbor_data['pos_type'].value != 'Flip-Sign': continue except KeyError: continue flip_sign_bounds = neighbor_data['polygon'].bounds flip_sign_box = box(flip_sign_bounds[0], flip_sign_bounds[1], flip_sign_bounds[2], flip_sign_bounds[3]) overlap_ratio = flip_sign_box.intersection(slot_box).area / flip_sign_box.area if overlap_ratio >= REQUIRED_FLIP_SIGN_FSLOT_OVERLAP_RATIO: product_fk = neighbor_data['product_fk'].value position_fk = self.get_custom_entity_pk(position) flip_sign_width = neighbor_data['calculated_width_ft'].value flip_sign_width = self.round_threshold(flip_sign_width) implied_facings = neighbor_data['width_of_signage_in_facings'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=flip_sign_width, score=implied_facings) return def calculate_flip_sign_empty_space(self, node, node_data, graph): if node_data['category'].value != 'Cigarettes': return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Empty Flip Sign Space') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value fixture_width = node_data['calculated_width_ft'].value fixture_width = self.round_threshold(fixture_width) flip_sign_widths = [] for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Flip-Sign': continue # exclude 'General POS Other' if neighbor_data['product_fk'].value == 9304: continue neighbor_width = neighbor_data['calculated_width_ft'].value neighbor_width = self.round_threshold(neighbor_width) flip_sign_widths.append(neighbor_width) empty_space = abs(fixture_width - sum(flip_sign_widths)) self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=empty_space) def calculate_flip_sign_locations(self, node_data): if node_data['category'].value != 'Cigarettes': return fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign Locations') width = node_data['calculated_width_ft'].value width = round(width) proportions_dict = self.get_flip_sign_position_proportions(width) if not proportions_dict: return for position, proportion in proportions_dict.items(): position_fk = self.get_custom_entity_pk(position) self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=round(proportion * width)) return def get_flip_sign_position_proportions(self, width): relevant_template = self.fixture_width_template[self.fixture_width_template['Fixture Width (ft)'] == width] if relevant_template.empty: Log.error("Unable to save flip sign location data. {}ft does not exist as a defined width".format(width)) return None # this could definitely be simpler, but I'm tired and don't want to use my brain relevant_template[['F1', 'F2', 'F3', 'F4']] = relevant_template[['F1', 'F2', 'F3', 'F4']].fillna(0) f_slots = [relevant_template['F1'].iloc[0], relevant_template['F2'].iloc[0], relevant_template['F3'].iloc[0], relevant_template['F4'].iloc[0]] f_slots = [i for i in f_slots if i != 0] proportions_dict = {} for i, slot in enumerate(f_slots): proportions_dict["F{}".format(i+1)] = slot / float(sum(f_slots)) return proportions_dict def calculate_total_shelves(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Total Shelves') shelves = len(node_data['shelf_number'].values) fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value category_fk = self.get_category_fk_by_name(node_data['category'].value) self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=shelves) def calculate_tags_by_fixture_block(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('FACINGS_BY_FIXTURE_BLOCK') scene_match_fks = node_data['match_fk'].values fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value relevant_matches = self.mpis[self.mpis['scene_match_fk'].isin(scene_match_fks)] relevant_matches = relevant_matches.groupby(['product_fk', 'shelf_number', 'match_product_in_probe_state_fk'], as_index=False).agg({'scene_match_fk': 'min', 'probe_match_fk': 'count'}) relevant_matches.rename(columns={'probe_match_fk': 'facings'}, inplace=True) for row in relevant_matches.itertuples(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.match_product_in_probe_state_fk, numerator_result=block_number, denominator_result=fixture_number, result=row.facings, score=row.shelf_number, context_id=row.scene_match_fk) def calculate_header(self, node, node_data, graph): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Header') parent_category = node_data['category'].value fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value if parent_category not in self.header_positions_template.columns.tolist(): return headers_by_x_coord = {} menu_board_items_by_x_coord = {} for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Header': continue center_x = neighbor_data['polygon'].centroid.coords[0][0] if neighbor_data['in_menu_board_area'].value is True: menu_board_items_by_x_coord[center_x] = neighbor_data else: headers_by_x_coord[center_x] = neighbor_data number_of_headers = len(headers_by_x_coord.keys()) if menu_board_items_by_x_coord: number_of_headers += 1 relevant_positions = \ self.header_positions_template[(self.header_positions_template['Number of Headers'] == number_of_headers)] if relevant_positions.empty: if number_of_headers == 0: return else: Log.error("Too many headers ({}) found for one block ({}). Unable to calculate positions!".format( number_of_headers, parent_category)) return positions = relevant_positions[parent_category].iloc[0] positions = [position.strip() for position in positions.split(',')] for i, pair in enumerate(sorted(headers_by_x_coord.items())): position_fk = self.get_custom_entity_pk(positions[i]) product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value width = self.round_threshold(width) self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=width) if menu_board_items_by_x_coord: for pair in menu_board_items_by_x_coord.items(): position_fk = self.get_custom_entity_pk(positions[-1]) product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value width = self.round_threshold(width) # width = 1 # this is because there is no real masking for menu board items self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=width) return def calculate_product_above_headers(self, node_data): try: product_above_header = node_data['product_above_header'].value except KeyError: return if not product_above_header: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Product Above Header') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=1, score=1, context_id=fixture_number) return def calculate_no_headers(self, node_data): try: no_header = node_data['no_header'].value except KeyError: return if not no_header: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('No Header') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=1, score=1, context_id=fixture_number) return def _get_active_kpis(self): active_kpis = self.kpi_new_static_data[(self.kpi_new_static_data['kpi_calculation_stage_fk'] == 3) & (self.kpi_new_static_data['valid_from'] <= self.visit_date) & ((self.kpi_new_static_data['valid_until']).isnull() | (self.kpi_new_static_data['valid_until'] >= self.visit_date))] return active_kpis def calculate_facings_by_scene_type(self): kpi_name = 'FACINGS_BY_SCENE_TYPE' kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name) if kpi_name not in self.active_kpis['type'].unique().tolist(): return config = self.get_external_target_data_by_kpi_fk(kpi_fk) product_types = config.product_type template_names = config.template_name relevant_mpis = self.mpis[(self.mpis['product_type'].isin(product_types)) & (self.mpis['template_name'].isin(template_names))] relevant_mpis = relevant_mpis.groupby(['product_fk', 'template_fk', 'match_product_in_probe_state_fk'], as_index=False)['scene_match_fk'].count() relevant_mpis.rename(columns={'scene_match_fk': 'facings'}, inplace=True) for row in relevant_mpis.itertuples(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.template_fk, context_id=row.match_product_in_probe_state_fk, result=row.facings) return def calculate_register_type(self): if self.survey_dot_com_collected_this_session: return relevant_scif = self.scif[(self.scif['product_type'].isin(['POS', 'Other'])) & (self.scif['category'] == 'POS Machinery')] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_age_verification(self): if self.survey_dot_com_collected_this_session: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification') relevant_scif = self.scif[self.scif['brand_name'].isin(['Age Verification'])] if relevant_scif.empty: result = 0 product_fk = 0 self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) else: result = 1 for product_fk in relevant_scif['product_fk'].unique().tolist(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) return def calculate_session_flags(self): if self.excessive_flipsigns: kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Excessive Flip Signs Detected') self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, result=1) if self.incorrect_tags_in_pos_areas: kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Invalid Product in Header Areas') relevant_product_fks = self.mpis[self.mpis['scene_match_fk'].isin(self.incorrect_tags_in_pos_areas)][ 'product_fk'].unique().tolist() for product_fk in relevant_product_fks: self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=1) def mark_tags_in_explorer(self, probe_match_fk_list, mpipsr_name): if not probe_match_fk_list: return try: match_type_fk = \ self.match_product_in_probe_state_reporting[ self.match_product_in_probe_state_reporting['name'] == mpipsr_name][ 'match_product_in_probe_state_reporting_fk'].values[0] except IndexError: Log.warning('Name not found in match_product_in_probe_state_reporting table: {}'.format(mpipsr_name)) return match_product_in_probe_state_values_old = self.common_v2.match_product_in_probe_state_values match_product_in_probe_state_values_new = pd.DataFrame(columns=[MATCH_PRODUCT_IN_PROBE_FK, MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK]) match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_FK] = probe_match_fk_list match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = match_type_fk self.common_v2.match_product_in_probe_state_values = pd.concat([match_product_in_probe_state_values_old, match_product_in_probe_state_values_new]) return @staticmethod def round_threshold(value, threshold=0.2): return round(value - threshold + 0.5) def _get_survey_dot_com_collected_value(self): try: sales_rep_fk = self.session_info['s_sales_rep_fk'].iloc[0] except IndexError: sales_rep_fk = 0 return int(sales_rep_fk) == 209050 def get_category_fk_by_name(self, category_name): return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0] def get_custom_entity_pk(self, name): try: return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0] except IndexError: Log.error('No custom entity found for {}'.format(name)) return 0 def get_external_target_data_by_kpi_fk(self, kpi_fk): return self.external_targets[self.external_targets['kpi_fk'] == kpi_fk].iloc[0] def commit(self): self.common_v2.commit_results_data()
class CCPHLToolBox: 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.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('') self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS] self.kpi_results_queries = [] self.mapping_param = {"manufacturer": "manufacturer_name"} self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id", "scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"} def main_calculation(self): """ This function calculates the KPI results. """ for group in self.template_data[CCPHLConsts.KPI_GROUP].unique(): kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group] if kpis.empty: print("KPI Group:{} is not valid".format(group)) continue for row_num, kpi in kpis.iterrows(): if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY: self.calculate_self_purity_util(kpi) elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL: self.calculate_self_purity_util(kpi) else: continue self.common.commit_results_data_to_new_tables() def calculate_self_purity_util(self, kpi): df_scene_data = self.scif if df_scene_data.empty: return scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')] filter_param_name_1 = kpi['filter_param_name_1'].strip() if len(filter_param_name_1) != 0: filter_param_value_1 = kpi['filter_param_value_1'].strip() else: filter_param_value_1 = "" if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY] df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY] if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT] df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']] if df_kpi_level_2_fk.empty: kpi_level_2_fk = 0 else: kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk'] df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)] group_list = [] for idx in range(1, 5): entity = kpi['entity' + str(idx)].strip() if entity == 'N/A' or len(entity) == 0: continue else: entity = self.mapping_entity[kpi['entity' + str(idx)].strip()] group_list.append(entity) denominator = 0 if group_list[0] == 'store_id': total_facings = df_scene_data['facings'].sum() denominator = float(total_facings) if len(filter_param_value_1) != 0: df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1] else: df_scene_data2 = df_scene_data filter_columns = list(group_list) filter_columns.append('facings') df_scene_data2 = df_scene_data2[filter_columns] df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index()) store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper() for row_num, row_data in df_purity.iterrows(): numerator = row_data['facings'] if group_list[0] == 'template_fk': df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']] if df_scene_count.empty: total_facings = 0 else: total_facings = df_scene_count['facings'].sum() denominator = float(total_facings) try: result = round(float(numerator) / float(denominator), 4) except ZeroDivisionError: print("Error: {}".format(ZeroDivisionError.message)) continue if kpi_level_2_fk != 0: if result == 0: if store_zero_results == 'Y': self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list)-1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result) else: self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list) - 1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result)
class HEINEKENTWToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 DIST_STORE_LVL1 = 1014 OOS_STORE_LVL1 = 1015 DIST_STORE_LVL2 = 1016 OOS_STORE_LVL2 = 1017 DIST_CATEGORY_LVL1 = 1018 OOS_CATEGORY_LVL1 = 1019 DIST_CATEGORY_LVL2 = 1020 OOS_CATEGORY_LVL2 = 1021 DISTRIBUTION = 4 OOS = 5 MANUFACTURER_FK = 175 # heinken manfucturer 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_new_kpi_static_data() self.kpi_results_queries = [] self.assortment = Assortment(self.data_provider, self.output) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ lvl3_result = self.assortment.calculate_lvl3_assortment() self.category_assortment_calculation(lvl3_result) self.store_assortment_calculation(lvl3_result) self.common.commit_results_data_to_new_tables() # self.common.commit_results_data_to_new_tables() def category_assortment_calculation(self, lvl3_result): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups """ if not lvl3_result.empty: # cat_df = self.scif[['product_fk', 'category_fk']] 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 = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL1, 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 == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL1, 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_list = lvl3_with_cat['category_fk'].unique() for cat in category_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)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=result.passes, denominator_id=cat, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_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) return def store_assortment_calculation(self, lvl3_result): """ This function calculates the KPI results. """ for result in lvl3_result.itertuples(): if result.in_store == 1: score = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=self.store_id, denominator_result=1, score=score) if score == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL1, 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.assortment.current_date: denominator_res = result.target res = np.divide(float(result.passes), float(denominator_res)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL2, numerator_id=self.MANUFACTURER_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.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL2, numerator_id=self.MANUFACTURER_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 PERFETTICNToolBox: 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_new_kpi_static_data() self.kpi_results_queries = [] self.store_info = self.data_provider[Data.STORE_INFO] self.assortment = Assortment(self.data_provider, self.output) self.template = self.data_provider.all_templates def main_calculation(self, *args, **kwargs): self.display_count() self.assortment_calculation() self.common.commit_results_data_to_new_tables() def display_count(self): """This function calculates how many displays find from types secondary shelf """ num_brands = {} display_info = self.scif['template_fk'] display_fks = display_info.unique() template_seco = self.template[ self.template['included_in_secondary_shelf_report'] == 'Y']['template_fk'] display_fks = list( filter(lambda x: x in template_seco.values, display_fks)) count_fk = self.kpi_static_data[self.kpi_static_data['client_name'] == 'COUNT OF DISPLAY']['pk'].iloc[0] for value in display_fks: num_brands[value] = display_info[display_info == value].count() score = num_brands[value] self.common.write_to_db_result_new_tables(count_fk, value, None, score, score, score, score) return def assortment_calculation(self): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups level1 how many groups passed out of all """ lvl3_result = self.assortment.calculate_lvl3_assortment() for result in lvl3_result.itertuples(): score = result.in_store if score >= 1: score = 100 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl3, result.product_fk, result.in_store, score, result.assortment_group_fk, 1, 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 res = np.divide(float(result.passes), float(denominator_res)) if result.passes >= 1: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl2, result.assortment_group_fk, result.passes, (res * 100), result.assortment_super_group_fk, denominator_res, score) if not lvl2_result.empty: lvl1_result = self.assortment.calculate_lvl1_assortment( lvl2_result) for result in lvl1_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) if res >= 0: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( fk=result.kpi_fk_lvl1, numerator_id=result.assortment_super_group_fk, numerator_result=result.passes, denominator_result=denominator_res, result=(res * 100), score=score) return
class ALTRIAUS_SANDToolBox: 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.common_v2 = CommonV2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.thresholds_and_results = {} self.result_df = [] self.writing_to_db_time = datetime.timedelta(0) self.kpi_results_queries = [] self.potential_products = {} self.shelf_square_boundaries = {} self.average_shelf_values = {} self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.all_template_data = parse_template(TEMPLATE_PATH, "KPI") self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing") self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel( FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities( 1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.assortment = Assortment(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider) self.store_assortment = self.assortment.get_lvl3_relevant_ass() self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ self.calculate_signage_locations_and_widths('Cigarettes') self.calculate_signage_locations_and_widths('Smokeless') self.calculate_register_type() self.calculate_age_verification() self.calculate_juul_availability() self.calculate_assortment() self.calculate_vapor_kpis() kpi_set_fk = 2 set_name = \ self.kpi_static_data.loc[self.kpi_static_data['kpi_set_fk'] == kpi_set_fk]['kpi_set_name'].values[0] template_data = self.all_template_data.loc[ self.all_template_data['KPI Level 1 Name'] == set_name] try: if set_name and not set( template_data['Scene Types to Include'].values[0].encode( ).split(', ')) & set( self.scif['template_name'].unique().tolist()): Log.info('Category {} was not captured'.format( template_data['category'].values[0])) return except Exception as e: Log.info( 'KPI Set {} is not defined in the template'.format(set_name)) for i, row in template_data.iterrows(): try: kpi_name = row['KPI Level 2 Name'] if kpi_name in KPI_LEVEL_2_cat_space: # scene_type = [s for s in row['Scene_Type'].encode().split(', ')] kpi_type = row['KPI Type'] scene_type = row['scene_type'] if row['Param1'] == 'Category' or 'sub_category': category = row['Value1'] if kpi_type == 'category_space': kpi_set_fk = \ self.kpi_new_static_data.loc[self.kpi_new_static_data['type'] == kpi_type]['pk'].values[0] self.calculate_category_space( kpi_set_fk, kpi_name, category, scene_types=scene_type) except Exception as e: Log.info('KPI {} calculation failed due to {}'.format( kpi_name.encode('utf-8'), e)) continue return def calculate_vapor_kpis(self): category = 'Vapor' relevant_scif = self.scif[self.scif['template_name'] == 'JUUL Merchandising'] if relevant_scif.empty: Log.info('No products found for {} category'.format(category)) return relevant_scif = relevant_scif[ (relevant_scif['category'].isin([category, 'POS'])) & (relevant_scif['brand_name'] == 'Juul')] if relevant_scif.empty: return relevant_product_pks = relevant_scif[ relevant_scif['product_type'] == 'SKU']['product_fk'].unique().tolist() relevant_scene_id = self.get_most_frequent_scene(relevant_scif) product_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_product_pks)) & (self.mpis['scene_fk'] == relevant_scene_id)] if product_mpis.empty: Log.info('No products found for {} category'.format(category)) return self.calculate_total_shelves(product_mpis, category, product_mpis) longest_shelf = \ product_mpis[product_mpis['shelf_number'] == self.get_longest_shelf_number(product_mpis, max_shelves_from_top=999)].sort_values(by='rect_x', ascending=True) if longest_shelf.empty or longest_shelf.isnull().all().all(): Log.warning( 'The {} category items are in a non-standard location. The {} category will not be calculated.' .format(category, category)) return relevant_pos = pd.DataFrame() self.calculate_fixture_width(relevant_pos, longest_shelf, category) return def calculate_assortment(self): if self.scif.empty or self.store_assortment.empty: Log.warning( 'Unable to calculate assortment: SCIF or store assortment is empty' ) return grouped_scif = self.scif.groupby('product_fk', as_index=False)['facings'].sum() assortment_with_facings = \ pd.merge(self.store_assortment, grouped_scif, how='left', on='product_fk') assortment_with_facings.loc[:, 'facings'] = assortment_with_facings[ 'facings'].fillna(0) for product in assortment_with_facings.itertuples(): score = 1 if product.facings > 0 else 0 self.common_v2.write_to_db_result( product.kpi_fk_lvl3, numerator_id=product.product_fk, denominator_id=product.assortment_fk, numerator_result=product.facings, result=product.facings, score=score) number_of_skus_present = len( assortment_with_facings[assortment_with_facings['facings'] > 0]) score = 1 if number_of_skus_present > 0 else 0 kpi_fk = assortment_with_facings['kpi_fk_lvl2'].iloc[0] assortment_group_fk = assortment_with_facings[ 'assortment_group_fk'].iloc[0] self.common_v2.write_to_db_result( kpi_fk, numerator_id=assortment_group_fk, numerator_result=number_of_skus_present, denominator_result=len(assortment_with_facings), result=number_of_skus_present, score=score) def calculate_register_type(self): relevant_scif = self.scif[ (self.scif['product_type'].isin(['POS', 'Other'])) & (self.scif['category'] == 'POS Machinery')] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_age_verification(self): relevant_scif = self.scif[self.scif['brand_name'].isin( ['Age Verification'])] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_juul_availability(self): relevant_scif = self.scif[(self.scif['brand_name'].isin(['Juul'])) & (self.scif['product_type'].isin(['POS']))] juul_pos = relevant_scif['product_fk'].unique().tolist() kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Juul POS Availability') if not juul_pos: return result = 1 for product_fk in juul_pos: self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_category_space(self, kpi_set_fk, kpi_name, category, scene_types=None): template = self.all_template_data.loc[ (self.all_template_data['KPI Level 2 Name'] == kpi_name) & (self.all_template_data['Value1'] == category)] kpi_template = template.loc[template['KPI Level 2 Name'] == kpi_name] if kpi_template.empty: return None kpi_template = kpi_template.iloc[0] values_to_check = [] filters = { 'template_name': scene_types, 'category': kpi_template['Value1'] } if kpi_template['Value1'] in CATEGORIES: category_att = 'category' if kpi_template['Value1']: values_to_check = self.all_products.loc[ self.all_products[category_att] == kpi_template['Value1']][category_att].unique().tolist() for primary_filter in values_to_check: filters[kpi_template['Param1']] = primary_filter new_kpi_name = self.kpi_name_builder(kpi_name, **filters) result = self.calculate_category_space_length( new_kpi_name, **filters) filters['Category'] = kpi_template['KPI Level 2 Name'] score = result numerator_id = self.products['category_fk'][ self.products['category'] == kpi_template['Value1']].iloc[0] self.common_v2.write_to_db_result(kpi_set_fk, numerator_id=numerator_id, numerator_result=999, result=score, score=score) def calculate_category_space_length(self, kpi_name, threshold=0.5, retailer=None, exclude_pl=False, **filters): """ :param threshold: The ratio for a bay to be counted as part of a category. :param filters: These are the parameters which the data frame is filtered by. :return: The total shelf width (in mm) the relevant facings occupy. """ try: filtered_scif = self.scif[self.get_filter_condition( self.scif, **filters)] space_length = 0 bay_values = [] for scene in filtered_scif['scene_fk'].unique().tolist(): scene_matches = self.mpis[self.mpis['scene_fk'] == scene] scene_filters = filters scene_filters['scene_fk'] = scene for bay in scene_matches['bay_number'].unique().tolist(): bay_total_linear = scene_matches.loc[ (scene_matches['bay_number'] == bay) & (scene_matches['stacking_layer'] == 1) & (scene_matches['status'] == 1)]['width_mm_advance'].sum() scene_filters['bay_number'] = bay tested_group_linear = scene_matches[ self.get_filter_condition(scene_matches, **scene_filters)] tested_group_linear_value = tested_group_linear[ 'width_mm_advance'].sum() if tested_group_linear_value: bay_ratio = tested_group_linear_value / float( bay_total_linear) else: bay_ratio = 0 if bay_ratio >= threshold: category = filters['category'] max_facing = scene_matches.loc[ (scene_matches['bay_number'] == bay) & (scene_matches['stacking_layer'] == 1 )]['facing_sequence_number'].max() shelf_length = self.spacing_template_data.query( 'Category == "' + category + '" & Low <= "' + str(max_facing) + '" & High >= "' + str(max_facing) + '"') shelf_length = int(shelf_length['Size'].iloc[-1]) bay_values.append(shelf_length) space_length += shelf_length except Exception as e: Log.info('Linear Feet calculation failed due to {}'.format(e)) space_length = 0 return space_length def get_filter_condition(self, df, **filters): """ :param df: The data frame to be filters. :param filters: These are the parameters which the data frame is filtered by. Every parameter would be a tuple of the value and an include/exclude flag. INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGROGENERALToolBox.INCLUDE_FILTER) INPUT EXAMPLE (2): manufacturer_name = 'Diageo' :return: a filtered Scene Item Facts data frame. """ if not filters: return df['pk'].apply(bool) if self.facings_field in df.keys(): filter_condition = (df[self.facings_field] > 0) else: filter_condition = None for field in filters.keys(): if field in df.keys(): if isinstance(filters[field], tuple): value, exclude_or_include = filters[field] else: value, exclude_or_include = filters[ field], self.INCLUDE_FILTER if not value: continue if not isinstance(value, list): value = [value] if exclude_or_include == self.INCLUDE_FILTER: condition = (df[field].isin(value)) elif exclude_or_include == self.EXCLUDE_FILTER: condition = (~df[field].isin(value)) elif exclude_or_include == self.CONTAIN_FILTER: condition = (df[field].str.contains(value[0], regex=False)) for v in value[1:]: condition |= df[field].str.contains(v, regex=False) else: continue if filter_condition is None: filter_condition = condition else: filter_condition &= condition else: Log.warning('field {} is not in the Data Frame'.format(field)) return filter_condition def kpi_name_builder(self, kpi_name, **filters): """ This function builds kpi name according to naming convention """ for filter in filters.keys(): if filter == 'template_name': continue kpi_name = kpi_name.replace('{' + filter + '}', str(filters[filter])) kpi_name = kpi_name.replace("'", "\'") return kpi_name def calculate_signage_locations_and_widths(self, category): excluded_types = ['Other', 'Irrelevant', 'Empty'] relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space'] if relevant_scif.empty: Log.info('No products found for {} category'.format(category)) return # need to include scene_id from previous relevant_scif # also need to split this shit up into different categories, i.e. smokeless, cigarettes # need to figure out how to deal with POS from smokeless being included with cigarette MPIS # get relevant SKUs from the cigarettes category relevant_scif = relevant_scif[relevant_scif['category'].isin( [category, 'POS'])] relevant_product_pks = relevant_scif[ relevant_scif['product_type'] == 'SKU']['product_fk'].unique().tolist() relevant_pos_pks = \ relevant_scif[(relevant_scif['product_type'] == 'POS') & ~(relevant_scif['brand_name'] == 'Age Verification') & ~(relevant_scif['product_name'] == 'General POS Other')]['product_fk'].unique().tolist() other_product_and_pos_pks = \ relevant_scif[relevant_scif['product_type'].isin(excluded_types)]['product_fk'].tolist() relevant_scene_id = self.get_most_frequent_scene(relevant_scif) product_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_product_pks)) & (self.mpis['scene_fk'] == relevant_scene_id)] if product_mpis.empty: Log.info('No products found for {} category'.format(category)) return self.calculate_total_shelves(product_mpis, category) longest_shelf = \ product_mpis[product_mpis['shelf_number'] == self.get_longest_shelf_number(product_mpis)].sort_values(by='rect_x', ascending=True) if longest_shelf.empty or longest_shelf.isnull().all().all(): Log.warning( 'The {} category items are in a non-standard location. The {} category will not be calculated.' .format(category, category)) return # demarcation_line = longest_shelf['rect_y'].median() old method, had bugs due to longest shelf being lower demarcation_line = product_mpis['rect_y'].min() exclusion_line = -9999 excluded_mpis = self.mpis[ ~(self.mpis['product_fk'].isin(relevant_pos_pks + relevant_product_pks + other_product_and_pos_pks)) & (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) & (self.mpis['rect_x'] > longest_shelf['rect_x'].min()) & (self.mpis['scene_fk'] == relevant_scene_id) & (self.mpis['rect_y'] < demarcation_line)] # we need this line for when SCIF and MPIS don't match excluded_mpis = excluded_mpis[~excluded_mpis['product_type']. isin(excluded_types)] if not excluded_mpis.empty: exclusion_line = excluded_mpis['rect_y'].max() # we need to get POS stuff that falls within the x-range of the longest shelf (which is limited by category) # we also need to account for the fact that the images suck, so we're going to add/subtract 5% of the # max/min values to allow for POS items that fall slightly out of the shelf length range correction_factor = 0.05 correction_value = (longest_shelf['rect_x'].max() - longest_shelf['rect_x'].min()) * correction_factor pos_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_pos_pks)) & (self.mpis['rect_x'] < (longest_shelf['rect_x'].max() + correction_value)) & (self.mpis['rect_x'] > (longest_shelf['rect_x'].min() - correction_value)) & (self.mpis['scene_fk'] == relevant_scene_id)] # DO NOT SET TO TRUE WHEN DEPLOYING # debug flag displays polygon_mask graph DO NOT SET TO TRUE WHEN DEPLOYING # DO NOT SET TO TRUE WHEN DEPLOYING relevant_pos = self.adp.get_products_contained_in_displays( pos_mpis, y_axis_threshold=35, debug=False) if relevant_pos.empty: Log.warning( 'No polygon mask was generated for {} category - cannot compute KPIs' .format(category)) # we need to attempt to calculate fixture width, even if there's no polygon mask self.calculate_fixture_width(relevant_pos, longest_shelf, category) return relevant_pos = relevant_pos[[ 'product_fk', 'product_name', 'left_bound', 'right_bound', 'center_x', 'center_y' ]] relevant_pos = relevant_pos.reindex( columns=relevant_pos.columns.tolist() + ['type', 'width', 'position']) relevant_pos['width'] = \ relevant_pos.apply(lambda row: self.get_length_of_pos(row, longest_shelf, category), axis=1) relevant_pos['type'] = \ relevant_pos['center_y'].apply(lambda x: 'Header' if exclusion_line < x < demarcation_line else 'Flip Sign') relevant_pos = relevant_pos.sort_values(['center_x'], ascending=True) relevant_pos = self.remove_duplicate_pos_tags(relevant_pos) # generate header positions if category == 'Cigarettes': number_of_headers = len( relevant_pos[relevant_pos['type'] == 'Header']) if number_of_headers > len( self.header_positions_template['Cigarettes Positions']. dropna()): Log.warning( 'Number of Headers for Cigarettes is greater than max number defined in template!' ) elif number_of_headers > 0: header_position_list = [ position.strip() for position in self.header_positions_template[ self.header_positions_template['Number of Headers'] == number_of_headers] ['Cigarettes Positions'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Header', ['position']] = header_position_list elif category == 'Smokeless': relevant_pos = self.check_menu_scene_recognition(relevant_pos) number_of_headers = len( relevant_pos[relevant_pos['type'] == 'Header']) if number_of_headers > len( self.header_positions_template['Smokeless Positions']. dropna()): Log.warning( 'Number of Headers for Smokeless is greater than max number defined in template!' ) elif number_of_headers > 0: header_position_list = [ position.strip() for position in self.header_positions_template[ self.header_positions_template['Number of Headers'] == number_of_headers] ['Smokeless Positions'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Header', ['position']] = header_position_list relevant_pos = self.get_menu_board_items(relevant_pos, longest_shelf, pos_mpis) # generate flip-sign positions if category == 'Cigarettes': relevant_template = \ self.fixture_width_template.loc[(self.fixture_width_template['Fixture Width (facings)'] - len(longest_shelf)).abs().argsort()[:1]].dropna(axis=1) locations = relevant_template.columns[2:].tolist() right_bound = 0 longest_shelf_copy = longest_shelf.copy() for location in locations: if right_bound > 0: left_bound = right_bound + 1 else: left_bound = longest_shelf_copy.iloc[:relevant_template[ location].iloc[0]]['rect_x'].min() right_bound = longest_shelf_copy.iloc[:relevant_template[ location].iloc[0]]['rect_x'].max() if locations[-1] == location: right_bound = right_bound + abs(right_bound * 0.05) flip_sign_pos = relevant_pos[ (relevant_pos['type'] == 'Flip Sign') & (relevant_pos['center_x'] > left_bound) & (relevant_pos['center_x'] < right_bound)] if flip_sign_pos.empty: # add 'NO Flip Sign' product_fk relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \ [location, NO_FLIP_SIGN_PK, 'Flip Sign'] else: relevant_pos.loc[flip_sign_pos.index, ['position']] = location longest_shelf_copy.drop( longest_shelf_copy.iloc[:relevant_template[location]. iloc[0]].index, inplace=True) elif category == 'Smokeless': # if there are no flip signs found, there are no positions to assign number_of_flip_signs = len( relevant_pos[relevant_pos['type'] == 'Flip Sign']) if number_of_flip_signs > self.flip_sign_positions_template[ 'Number of Flip Signs'].max(): Log.warning( 'Number of Flip Signs for Smokeless is greater than max number defined in template!' ) elif number_of_flip_signs > 0: flip_sign_position_list = [ position.strip() for position in self.flip_sign_positions_template[ self. flip_sign_positions_template['Number of Flip Signs'] == number_of_flip_signs]['Position'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Flip Sign', ['position']] = flip_sign_position_list # store empty flip sign values for location in ['Secondary', 'Tertiary']: if location not in relevant_pos[ relevant_pos['type'] == 'Flip Sign']['position'].tolist(): relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \ [location, NO_FLIP_SIGN_PK, 'Flip Sign'] relevant_pos = relevant_pos.reindex( columns=relevant_pos.columns.tolist() + ['denominator_id']) # this is a bandaid fix that should be removed -> 'F7011A7C-1BB6-4007-826D-2B674BD99DAE' # removes POS items that were 'extra', i.e. more than max value in template # only affects smokeless relevant_pos.dropna(subset=['position'], inplace=True) relevant_pos.loc[:, ['denominator_id']] = relevant_pos['position'].apply( self.get_custom_entity_pk) for row in relevant_pos.itertuples(): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(row.type) self.common_v2.write_to_db_result( kpi_fk, numerator_id=row.product_fk, denominator_id=row.denominator_id, result=row.width, score=row.width) self.calculate_fixture_width(relevant_pos, longest_shelf, category) return def calculate_total_shelves(self, longest_shelf, category, product_mpis=None): category_fk = self.get_category_fk_by_name(category) if product_mpis is None: product_mpis = self.mpis[ (self.mpis['rect_x'] > longest_shelf['rect_x'].min()) & (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) & (self.mpis['scene_fk'] == longest_shelf['scene_fk'].fillna(0).mode().iloc[0])] total_shelves = len(product_mpis['shelf_number'].unique()) kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Total Shelves') self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, result=total_shelves) def calculate_fixture_width(self, relevant_pos, longest_shelf, category): correction_factor = 1 if category == 'Smokeless' else 2 longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1] category_fk = self.get_category_fk_by_name(category) # this is needed to remove intentionally duplicated 'Menu Board' POS 'Headers' relevant_pos = relevant_pos.drop_duplicates(subset=['position']) # try: # width = relevant_pos[relevant_pos['type'] == 'Header']['width'].sum() # except KeyError: # # needed for when 'width' doesn't exist # width = 0 # if relevant_pos.empty or width == 0: width = round( len(longest_shelf) + correction_factor / float(self.facings_to_feet_template[category + ' Facings'].iloc[0]) ) kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Fixture Width') self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, result=width) def get_category_fk_by_name(self, category_name): return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0] def check_menu_scene_recognition(self, relevant_pos): mdis = self.adp.get_match_display_in_scene() mdis = mdis[mdis['display_name'] == 'Menu POS'] if mdis.empty: return relevant_pos dummy_sku_for_menu_pk = 9282 # 'Other (Smokeless Tobacco)' dummy_sky_for_menu_name = 'Menu POS (Scene Recognized Item)' location_type = 'Header' width = 1 center_x = mdis['x'].iloc[0] center_y = mdis['y'].iloc[0] relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width']] = \ [dummy_sku_for_menu_pk, dummy_sky_for_menu_name, center_x, center_y, location_type, width] relevant_pos = relevant_pos.sort_values( ['center_x'], ascending=True).reset_index(drop=True) return relevant_pos def get_menu_board_items(self, relevant_pos, longest_shelf, pos_mpis): # get the placeholder item menu_board_dummy = relevant_pos[relevant_pos['product_name'] == 'Menu POS (Scene Recognized Item)'] # if no placeholder, this function isn't relevant if menu_board_dummy.empty: return relevant_pos center_x = menu_board_dummy['center_x'].iloc[0] center_y = menu_board_dummy['center_y'].iloc[0] position = menu_board_dummy['position'].iloc[0] demarcation_line = longest_shelf['rect_y'].min() upper_demarcation_line = center_y - (demarcation_line - center_y) distance_in_facings = 2 try: left_bound = longest_shelf[ longest_shelf['rect_x'] < center_x].sort_values( by=['rect_x'], ascending=False)['rect_x'].iloc[int(distance_in_facings) - 1] except IndexError: # if there are no POS items found to the left of the 'Menu POS' scene recognition tag, use the tag itself # in theory this should never happen left_bound = center_x try: right_bound = longest_shelf[ longest_shelf['rect_x'] > center_x].sort_values( by=['rect_x'], ascending=True)['rect_x'].iloc[int(distance_in_facings) - 1] except IndexError: # if there are no POS items found to the right of the 'Menu POS' scene recognition tag, use the tag itself # this is more likely to happen for the right bound than the left bound right_bound = center_x pos_mpis = pos_mpis[(pos_mpis['rect_x'] > left_bound) & (pos_mpis['rect_x'] < right_bound) & (pos_mpis['rect_y'] > upper_demarcation_line) & (pos_mpis['rect_y'] < demarcation_line)] if pos_mpis.empty: return relevant_pos # remove the placeholder item relevant_pos = relevant_pos[~(relevant_pos['product_name'] == 'Menu POS (Scene Recognized Item)')] location_type = 'Header' width = 1 for row in pos_mpis.itertuples(): relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width', 'position']] = \ [row.product_fk, row.product_name, row.rect_x, row.rect_y, location_type, width, position] return relevant_pos @staticmethod def remove_duplicate_pos_tags(relevant_pos_df): duplicate_results = \ relevant_pos_df[relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)] duplicate_results_without_other = duplicate_results[ ~duplicate_results['product_name'].str.contains('Other')] results_without_duplicates = \ relevant_pos_df[~relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)] if duplicate_results_without_other.empty: return relevant_pos_df[~relevant_pos_df.duplicated( subset=['left_bound', 'right_bound'], keep='first')] else: results = pd.concat( [duplicate_results_without_other, results_without_duplicates]) # we need to sort_index to fix the sort order to reflect center_x values return results.drop_duplicates( subset=['left_bound', 'right_bound']).sort_index() def get_custom_entity_pk(self, name): return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0] def get_length_of_pos(self, row, longest_shelf, category): width_in_facings = len( longest_shelf[(longest_shelf['rect_x'] > row['left_bound']) & (longest_shelf['rect_x'] < row['right_bound'])]) + 2 category_facings = category + ' Facings' return self.facings_to_feet_template.loc[( self.facings_to_feet_template[category_facings] - width_in_facings).abs().argsort()[:1]]['POS Width (ft)'].iloc[0] @staticmethod def get_longest_shelf_number(relevant_mpis, max_shelves_from_top=3): # returns the shelf_number of the longest shelf try: longest_shelf = \ relevant_mpis[relevant_mpis['shelf_number'] <= max_shelves_from_top].groupby('shelf_number').agg( {'scene_match_fk': 'count'})['scene_match_fk'].idxmax() except ValueError: longest_shelf = pd.DataFrame() return longest_shelf @staticmethod def get_most_frequent_scene(relevant_scif): try: relevant_scene_id = relevant_scif['scene_id'].fillna( 0).mode().iloc[0] except IndexError: relevant_scene_id = 0 return relevant_scene_id def commit(self): self.common_v2.commit_results_data()