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 ALTRIAUSToolBox: 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.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting( ) 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.empty_data_template = parse_template(TEMPLATE_PATH, "Empty") 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) 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( ) 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() self.calculate_empty_brand() self.calculate_facings_by_scene_type() 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 _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_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_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_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))] smart_attribute_data = \ self.adp.get_match_product_in_probe_state_values(relevant_mpis['probe_match_fk'].unique().tolist()) relevant_mpis = pd.merge(relevant_mpis, smart_attribute_data, on='probe_match_fk', how='left') relevant_mpis['match_product_in_probe_state_fk'].fillna(0, inplace=True) 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_empty_brand(self): product_type = ['Empty'] for i, row in self.empty_data_template.iterrows(): kpi_name = row['KPI Level 2 Name'] param1 = row['Param1'] value1 = row['Value1'] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(kpi_name) if value1.find(',') != -1: value1 = value1.split(',') for value in value1: relevant_scif = self.scif[(self.scif[param1] == value) & ( self.scif['product_type'].isin(product_type))] if relevant_scif.empty: pass else: result = empty_facings = relevant_scif['facings'].sum() brand_fk = relevant_scif['brand_fk'].iloc[0] self.common_v2.write_to_db_result( kpi_fk, numerator_id=brand_fk, numerator_result=empty_facings, denominator_id=self.store_id, result=result, score=0) 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): longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1] category_fk = self.get_category_fk_by_name(category) # ======== old method for synchronizing fixture width to header data ======== # 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: # correction_factor = 1 if category == 'Smokeless' else 2 # width = round(len(longest_shelf) + correction_factor / float( # self.facings_to_feet_template[category + ' Facings'].iloc[0])) # ======== old method for synchronizing fixture width to header data ======== width = len(longest_shelf) / float( self.facings_to_feet_template[category + ' Facings'].iloc[0]) self.mark_tags_in_explorer(longest_shelf['probe_match_fk'].tolist(), 'Longest Shelf - ' + category) 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 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 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 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 CigarrosToolBox(GlobalSessionToolBox): def __init__(self, data_provider, output, common): GlobalSessionToolBox.__init__(self, data_provider, output, common) self.scene_types = self.scif['template_name'].unique().tolist() self.gz = self.store_info['additional_attribute_4'].iloc[0] self.city = self.store_info['address_city'].iloc[0] self.relevant_targets = self._get_relevant_external_targets( kpi_operation_type='acomodo_cigarros') self.invasion_targets = self._get_relevant_external_targets( kpi_operation_type='invasion') self._determine_target_product_fks() self.leading_products = self._get_leading_products_from_scif() self.scene_realograms = self._calculate_scene_realograms() 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( ) def main_calculation(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CIGARROS) parent_fk = self.get_parent_fk(Consts.CIGARROS) kpi_max_points = self.get_kpi_points(Consts.CIGARROS) score = 0 score += self.calculate_mercadeo() score += self.calculate_surtido() result = score / float(kpi_max_points) self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, result=result * 100, score=score, weight=kpi_max_points, target=kpi_max_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def calculate_surtido(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.SURTIDO) parent_fk = self.get_parent_fk(Consts.SURTIDO) max_kpi_points = self.get_kpi_points(Consts.SURTIDO) weight = self.get_kpi_weight(Consts.SURTIDO) score = 0 # score += self.calculate_calificador() score += self.calculate_prioritario() # score += self.calculate_opcional() result = score / float(max_kpi_points) self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def calculate_calificador(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CALIFICADOR) parent_fk = self.get_parent_fk(Consts.CALIFICADOR) max_kpi_points = self.get_kpi_points(Consts.CALIFICADOR) weight = self.get_kpi_weight(Consts.CALIFICADOR) relevant_template = self.relevant_targets[ (self.relevant_targets['Nombre de Tarea'].isin(self.scene_types)) & (self.relevant_targets['TIPO DE SKU'] == 'C')] relevant_template = pd.merge(relevant_template, self.scif[['product_fk', 'facings']], how='left', left_on='target_product_fk', right_on='product_fk') relevant_template['facings'].fillna(0, inplace=True) relevant_template['in_session'] = relevant_template['facings'] > 0 self._calculate_calificador_sku(relevant_template) if relevant_template.empty: result = 0 else: result = relevant_template['in_session'].sum() / float( len(relevant_template)) score = result * max_kpi_points self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=relevant_template['in_session'].sum(), denominator_result=len(relevant_template), result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def _calculate_calificador_sku(self, relevant_template): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CALIFICADOR_SKU) parent_fk = self.get_parent_fk(Consts.CALIFICADOR_SKU) for sku in relevant_template.itertuples(): result = 1 if sku.in_session else 0 self.write_to_db(fk=kpi_fk, numerator_id=sku.target_product_fk, denominator_id=self.store_id, result=result, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) def calculate_prioritario(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.PRIORITARIO) parent_fk = self.get_parent_fk(Consts.PRIORITARIO) max_kpi_points = self.get_kpi_points(Consts.PRIORITARIO) weight = self.get_kpi_weight(Consts.PRIORITARIO) relevant_template = self.relevant_targets[ (self.relevant_targets['Nombre de Tarea'].isin(self.scene_types)) & (self.relevant_targets['TIPO DE SKU'] == 'P')] relevant_template = pd.merge(relevant_template, self.scif[['product_fk', 'facings']], how='left', left_on='target_product_fk', right_on='product_fk') relevant_template['facings'].fillna(0, inplace=True) relevant_template['in_session'] = relevant_template['facings'] > 0 self._calculate_prioritario_sku(relevant_template) if relevant_template.empty: result = 0 else: result = relevant_template['in_session'].sum() / float( len(relevant_template)) score = result * max_kpi_points self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=relevant_template['in_session'].sum(), denominator_result=len(relevant_template), result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def _calculate_prioritario_sku(self, relevant_template): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.PRIORITARIO_SKU) parent_fk = self.get_parent_fk(Consts.PRIORITARIO_SKU) for sku in relevant_template.itertuples(): result = 1 if sku.in_session else 0 self.write_to_db(fk=kpi_fk, numerator_id=sku.target_product_fk, denominator_id=self.store_id, result=result, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) def calculate_opcional(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.OPCIONAL) parent_fk = self.get_parent_fk(Consts.OPCIONAL) max_kpi_points = self.get_kpi_points(Consts.OPCIONAL) weight = self.get_kpi_weight(Consts.OPCIONAL) relevant_template = self.relevant_targets[ (self.relevant_targets['Nombre de Tarea'].isin(self.scene_types)) & (self.relevant_targets['TIPO DE SKU'] == 'O')] relevant_template = pd.merge(relevant_template, self.scif[['product_fk', 'facings']], how='left', left_on='target_product_fk', right_on='product_fk') relevant_template['facings'].fillna(0, inplace=True) relevant_template['in_session'] = relevant_template['facings'] > 0 self._calculate_opcional_sku(relevant_template) if relevant_template.empty: result = 0 else: result = relevant_template['in_session'].sum() / float( len(relevant_template)) score = result * max_kpi_points self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=relevant_template['in_session'].sum(), denominator_result=len(relevant_template), result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def _calculate_opcional_sku(self, relevant_template): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.OPCIONAL_SKU) parent_fk = self.get_parent_fk(Consts.OPCIONAL_SKU) for sku in relevant_template.itertuples(): result = 1 if sku.in_session else 0 self.write_to_db(fk=kpi_fk, numerator_id=sku.target_product_fk, denominator_id=self.store_id, result=result, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) def calculate_mercadeo(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.MERCADEO) parent_fk = self.get_parent_fk(Consts.MERCADEO) max_kpi_points = self.get_kpi_points(Consts.MERCADEO) weight = self.get_kpi_weight(Consts.MERCADEO) score = 0 score += self.calculate_acomodo() score += self.calculate_frentes() score += self.calculate_huecos() result = score / float(max_kpi_points) self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def calculate_huecos(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.HUECOS) parent_fk = self.get_parent_fk(Consts.HUECOS) max_kpi_points = self.get_kpi_points(Consts.HUECOS) weight = self.get_kpi_weight(Consts.HUECOS) relevant_scene_types = self.relevant_targets[ Consts.TEMPLATE_SCENE_TYPE].unique().tolist() relevant_scif = self.scif[self.scif['template_name'].isin( relevant_scene_types)] if relevant_scif.empty: result = 0 else: empty_scif = relevant_scif[relevant_scif['product_type'] == 'Empty'] result = 1 if empty_scif.empty else 0 score = result * max_kpi_points self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_result=kpi_fk, identifier_parent=parent_fk, should_enter=True) return score def calculate_frentes(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.FRENTES) parent_fk = self.get_parent_fk(Consts.FRENTES) max_kpi_points = self.get_kpi_points(Consts.FRENTES) weight = self.get_kpi_weight(Consts.FRENTES) valid_scene_types = self.relevant_targets[ Consts.TEMPLATE_SCENE_TYPE].unique().tolist() relevant_scif = self.scif[self.scif['template_name'].isin( valid_scene_types)] relevant_scif.groupby('product_fk', as_index=False)['facings'].sum() relevant_target_skus = \ self.relevant_targets[self.relevant_targets['Nombre de Tarea'].isin( relevant_scif['template_name'].unique().tolist())] relevant_target_skus = relevant_target_skus.groupby( 'target_product_fk', as_index=False)['Frentes'].sum() relevant_target_skus.rename(columns={'Frentes': 'target'}, inplace=True) relevant_target_skus = pd.merge(relevant_target_skus, relevant_scif, how='left', left_on='target_product_fk', right_on='product_fk').fillna(0) relevant_target_skus['meets_target'] = relevant_target_skus[ 'facings'] >= relevant_target_skus['target'] relevant_target_skus['meets_target'] = relevant_target_skus[ 'meets_target'].apply(lambda x: 1 if x else 0) count_of_passing_skus = relevant_target_skus['meets_target'].sum() self._calculate_frentes_sku(relevant_target_skus) if len(relevant_target_skus) == 0: result = 0 else: result = count_of_passing_skus / float(len(relevant_target_skus)) score = result * max_kpi_points self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=count_of_passing_skus, denominator_result=len(relevant_target_skus), result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) return score def _calculate_frentes_sku(self, relevant_target_skus): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.FRENTES_SKU) parent_fk = self.get_parent_fk(Consts.FRENTES_SKU) for sku_row in relevant_target_skus.itertuples(): self.write_to_db(fk=kpi_fk, numerator_id=sku_row.target_product_fk, denominator_id=self.store_id, numerator_result=sku_row.facings, result=sku_row.meets_target, target=sku_row.target, identifier_parent=parent_fk, should_enter=True) def calculate_acomodo(self): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.ACOMODO) parent_fk = self.get_parent_fk(Consts.ACOMODO) max_kpi_points = self.get_kpi_points(Consts.ACOMODO) weight = self.get_kpi_weight(Consts.ACOMODO) scene_result = 0 count = 0 for scene_id, scene_realogram in self.scene_realograms.items(): count += 1 scene_result += self.calculate_acomodo_scene(scene_realogram) if count == 0: result = 0 else: result = scene_result / float(count) score = result * max_kpi_points self.write_to_db(fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=result, denominator_result=count, result=result * 100, score=score, weight=weight, target=max_kpi_points, identifier_parent=parent_fk, identifier_result=kpi_fk, should_enter=True) return score def calculate_acomodo_scene(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.ACOMODO_SCENE) parent_fk = self.get_parent_fk(Consts.ACOMODO_SCENE) identifier_result = self.get_dictionary( kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk) result = self.calculate_colcado_correct(scene_realogram) self.calculate_colcado_incorrect(scene_realogram) self.calculate_extra(scene_realogram) self.write_to_db(fk=kpi_fk, numerator_id=scene_realogram.template_fk, denominator_id=self.store_id, context_id=scene_realogram.scene_fk, result=result, score=result * 100, identifier_parent=parent_fk, identifier_result=identifier_result, should_enter=True) return result def calculate_colcado_correct(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_CORRECT) parent_fk = self.get_parent_fk(Consts.COLCADO_CORRECT) identifier_result = self.get_dictionary( kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) self._calculate_colcado_correct_sku(scene_realogram) number_of_positions_in_planogram = float( scene_realogram.number_of_positions_in_planogram) self.mark_tags_in_explorer( scene_realogram.correctly_placed_tags['probe_match_fk'].dropna(). unique().tolist(), Consts.COLCADO_CORRECT) self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=scene_realogram.template_fk, numerator_result=len(scene_realogram.correctly_placed_tags), denominator_result=scene_realogram.number_of_skus_in_planogram, result=len(scene_realogram.correctly_placed_tags) / number_of_positions_in_planogram * 100, score=len(scene_realogram.correctly_placed_tags) / number_of_positions_in_planogram, context_id=scene_realogram.scene_fk, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True) return len(scene_realogram.correctly_placed_tags ) / number_of_positions_in_planogram def _calculate_colcado_correct_sku(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_CORRECT_SKU) parent_fk = self.get_parent_fk(Consts.COLCADO_CORRECT_SKU) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) correctly_placed_skus = scene_realogram.calculate_correctly_placed_skus( ) for sku_row in correctly_placed_skus.itertuples(): self.write_to_db( fk=kpi_fk, numerator_id=sku_row.target_product_fk, denominator_id=scene_realogram.template_fk, numerator_result=sku_row.facings, denominator_result=scene_realogram.number_of_skus_in_planogram, result=sku_row.facings, context_id=scene_realogram.scene_fk, identifier_parent=identifier_parent, should_enter=True) def calculate_colcado_incorrect(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_INCORRECT) parent_fk = self.get_parent_fk(Consts.COLCADO_INCORRECT) identifier_result = self.get_dictionary( kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) self._calculate_colcado_incorrect_sku(scene_realogram) number_of_positions_in_planogram = float( scene_realogram.number_of_positions_in_planogram) self.mark_tags_in_explorer( scene_realogram.incorrectly_placed_tags['probe_match_fk'].dropna(). unique().tolist(), Consts.COLCADO_INCORRECT) self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=scene_realogram.template_fk, numerator_result=len(scene_realogram.incorrectly_placed_tags), denominator_result=scene_realogram.number_of_skus_in_planogram, result=len(scene_realogram.incorrectly_placed_tags) / number_of_positions_in_planogram * 100, score=len(scene_realogram.incorrectly_placed_tags) / number_of_positions_in_planogram, context_id=scene_realogram.scene_fk, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True) return def _calculate_colcado_incorrect_sku(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_INCORRECT_SKU) parent_fk = self.get_parent_fk(Consts.COLCADO_INCORRECT_SKU) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) incorrectly_placed_skus = scene_realogram.calculate_incorrectly_placed_skus( ) for sku_row in incorrectly_placed_skus.itertuples(): self.write_to_db( fk=kpi_fk, numerator_id=sku_row.target_product_fk, denominator_id=scene_realogram.template_fk, numerator_result=sku_row.facings, denominator_result=scene_realogram.number_of_skus_in_planogram, result=sku_row.facings, context_id=scene_realogram.scene_fk, identifier_parent=identifier_parent, should_enter=True) def calculate_extra(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA) parent_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA) identifier_result = self.get_dictionary( kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) self._calculate_extra_sku(scene_realogram) number_of_positions_in_planogram = float( scene_realogram.number_of_positions_in_planogram) self.mark_tags_in_explorer( scene_realogram.extra_tags['probe_match_fk'].dropna().unique(). tolist(), Consts.EXTRA) self.write_to_db( fk=kpi_fk, numerator_id=self.manufacturer_fk, denominator_id=scene_realogram.template_fk, numerator_result=len(scene_realogram.extra_tags), denominator_result=scene_realogram.number_of_skus_in_planogram, result=len(scene_realogram.extra_tags) / number_of_positions_in_planogram * 100, score=len(scene_realogram.extra_tags) / number_of_positions_in_planogram, context_id=scene_realogram.scene_fk, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True) return def _calculate_extra_sku(self, scene_realogram): kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA_SKU) parent_fk = self.get_parent_fk(Consts.EXTRA_SKU) identifier_parent = self.get_dictionary( kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk) extra_skus = scene_realogram.calculate_extra_skus() for sku_row in extra_skus.itertuples(): self.write_to_db( fk=kpi_fk, numerator_id=sku_row.target_product_fk, denominator_id=scene_realogram.template_fk, numerator_result=sku_row.facings, denominator_result=scene_realogram.number_of_skus_in_planogram, result=sku_row.facings, context_id=scene_realogram.scene_fk, identifier_parent=identifier_parent, should_enter=True) def _calculate_scene_realograms(self): scene_realograms = {} for scene_type in self.relevant_targets[ Consts.TEMPLATE_SCENE_TYPE].unique().tolist(): if scene_type in self.scif['template_name'].unique().tolist(): template_fk = self._get_template_fk(scene_type) for scene_fk in self.scif[ self.scif['template_name'] == scene_type]['scene_id'].unique().tolist(): scene_mpis = self.matches[self.matches['scene_fk'] == scene_fk] scene_mpis = self._convert_mpis_product_fks_to_leads( scene_mpis) scene_realogram = HeinekenRealogram( scene_mpis, scene_type, template_fk, self.relevant_targets) scene_realograms[scene_fk] = scene_realogram return scene_realograms def _get_relevant_external_targets(self, kpi_operation_type=None): if kpi_operation_type == 'acomodo_cigarros': template_df = pd.read_excel(Consts.TEMPLATE_PATH, sheetname='Acomodo_cigarros', header=1) template_df = template_df[ (template_df['GZ'].str.encode('utf-8') == self.gz.encode( 'utf-8')) & (template_df['Ciudad'].str.encode('utf-8') == self.city.encode('utf-8'))] template_df['Puertas'] = template_df['Puertas'].fillna(1) template_df = template_df[[ 'GZ', 'Ciudad', 'Nombre de Tarea', 'Puertas', 'EAN Code', 'Product Name', 'TIPO DE SKU', 'x', 'y', 'Frentes' ]] return template_df elif kpi_operation_type == 'invasion': template_df = pd.read_excel(Consts.TEMPLATE_PATH, sheetname='Invasion', header=1) template_df = template_df[( template_df['NOMBRE DE TAREA'].str.contains('Cerveza'))] return template_df.dropna(subset=['Manufacturer', 'Category']) else: return pd.DataFrame() def _determine_target_product_fks(self): target_products = self.all_products[['product_fk', 'product_ean_code']] target_products.rename(columns={'product_fk': 'target_product_fk'}, inplace=True) target_products.dropna(inplace=True) target_products['product_ean_code'] = target_products[ 'product_ean_code'].astype('int') self.relevant_targets['EAN Code'] = self.relevant_targets[ 'EAN Code'].astype('int') self.relevant_targets = pd.merge(self.relevant_targets, target_products, how='left', left_on='EAN Code', right_on='product_ean_code') self.relevant_targets.dropna(subset=['target_product_fk'], inplace=True) def _get_leading_products_from_scif(self): leading_mappings = self.scif[['product_fk', 'substitution_product_fk' ]].drop_duplicates() leading_mappings.loc[leading_mappings['substitution_product_fk'].isna(), 'substitution_product_fk'] = \ leading_mappings.loc[leading_mappings['substitution_product_fk'].isna(), 'product_fk'] return leading_mappings @staticmethod def _convert_mpis_product_fks_to_leads(mpis): mpis['leading_product_fk'] = mpis['substitution_product_fk'] return mpis def get_parent_fk(self, kpi_name): parent_kpi_name = Consts.KPIS_HIERARCHY[kpi_name] parent_fk = self.get_kpi_fk_by_kpi_type(parent_kpi_name) return parent_fk @staticmethod def get_kpi_weight(kpi_name): weight = Consts.KPI_WEIGHTS[kpi_name] return weight @staticmethod def get_kpi_points(kpi_name): weight = Consts.KPI_POINTS[kpi_name] return weight def _get_template_fk(self, template_name): return self.templates[self.templates['template_name'] == template_name]['template_fk'].iloc[0] 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.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.match_product_in_probe_state_values = pd.concat([ match_product_in_probe_state_values_old, match_product_in_probe_state_values_new ]) return
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_INFO]['store_fk'].values[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.kpi_results_queries = [] self.df = pd.DataFrame() self.tools = GENERALToolBox(data_provider) self.osd_rules_sheet = pd.read_excel(PATH, Const.OSD_RULES).fillna("") self.store_info = self.data_provider[Data.STORE_INFO] self.psdataprovider = PsDataProvider(self.data_provider) self.match_product_in_probe_state_reporting = self.psdataprovider.get_match_product_in_probe_state_reporting() def main_calculation(self): """ 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") distinct_session_fk = self.scif[['scene_fk', 'template_name', 'template_fk']].drop_duplicates() df = pd.merge(df, distinct_session_fk, on="scene_fk", how="left") self.calculate_osd(df) def calculate_osd(self, df): if df.empty: return scene_type = df['template_name'].values[0] const_scene_df = df.copy() row = self.find_row_osd(scene_type) if row.empty: return results_list = [] # 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) result_df = df[df['shelf_number_from_bottom'] >= shelfs_to_include] if not result_df.empty: results_list.append(result_df) df = df[df['shelf_number_from_bottom'] < shelfs_to_include] # if no osd rule is applied. if (row[Const.HAS_OSD].values[0] == Const.NO) and (row[Const.HAS_HOTSPOT].values[0] == Const.NO): return # 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 = df[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']))] results_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 = const_scene_df[const_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']))] results_list.append(scene_df) if len(results_list) == 0: return kpi_fk = self.common.get_kpi_fk_by_kpi_name(Const.OSD_KPI) if kpi_fk is None: Log.warning("There is no matching Kpi fk for kpi name: " + Const.OSD_KPI) return try: template_fk = self.scene_info['template_fk'].values[0] except: template_fk = -1 self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.store_id, result=1, by_scene=True, denominator_id=template_fk) if len(results_list) > 1: df = pd.concat(results_list).drop_duplicates() else: df = results_list[0] osd_pk = self.match_product_in_probe_state_reporting[self.match_product_in_probe_state_reporting['name'] == 'OSD']['match_product_in_probe_state_reporting_fk'].values[0] self.common.match_product_in_probe_state_values[Const.MATCH_PRODUCT_IN_PROBE_FK] = \ df['probe_match_fk'].drop_duplicates() self.common.match_product_in_probe_state_values[Const.MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = osd_pk 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