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 create_position_graphs(self, scene_id=None): """ This function creates a facings Graph for each scene of the given session. """ calc_start_time = datetime.datetime.utcnow() if scene_id: scenes = [scene_id] else: scenes = self.match_product_in_scene[ MatchesConsts.SCENE_FK].unique() for scene in scenes: matches = self.match_product_in_scene[self.match_product_in_scene[ MatchesConsts.SCENE_FK] == scene] matches['distance_from_end_of_shelf'] = matches[ MatchesConsts.N_SHELF_ITEMS] - matches[ MatchesConsts.FACING_SEQUENCE_NUMBER] scene_graph = igraph.Graph(directed=True) edges = [] for f in xrange(len(matches)): facing = matches.iloc[f] facing_name = str(facing[MatchesConsts.SCENE_MATCH_FK]) scene_graph.add_vertex(facing_name) # adding attributes to vertex vertex = scene_graph.vs.find(facing_name) for attribute in Consts.ATTRIBUTES_TO_SAVE: vertex[attribute] = facing[attribute] surrounding_products = self.get_surrounding_products( facing, matches) for direction in surrounding_products.keys(): for pk in surrounding_products[direction]: edge = dict(source=facing_name, target=str(pk), direction=direction) edges.append(edge) for edge in edges: scene_graph.add_edge(**edge) self.position_graphs[scene] = scene_graph calc_finish_time = datetime.datetime.utcnow() Log.info('Creation of position graphs for scenes {} took {}'.format( scenes, calc_finish_time - calc_start_time))
def create_position_graphs(self): """ This function creates a facings Graph for each scene of the given session. """ calc_start_time = datetime.datetime.utcnow() graphs = {} attributes_to_save = [ 'product_name', 'product_type', 'product_ean_code', 'brand_name', 'category', 'manufacturer_name' ] for scene in self.match_product_in_scene['scene_fk'].unique(): matches = self.match_product_in_scene[ self.match_product_in_scene['scene_fk'] == scene] scene_graph = igraph.Graph(directed=True) edges = [] for f in xrange(len(matches)): facing = matches.iloc[f] facing_name = str(facing[VERTEX_FK_FIELD]) scene_graph.add_vertex(facing_name) # adding attributes to vertex vertex = scene_graph.vs.find(facing_name) for attribute in attributes_to_save: vertex[attribute] = facing[attribute] surrounding_products = self.get_surrounding_products( facing, matches) for direction in surrounding_products.keys(): for pk in surrounding_products[direction]: edge = (facing_name, str(pk), direction) edges.append(edge) for edge in edges: source, target, direction = edge scene_graph.add_edge(source=source, target=target) edge_id = scene_graph.get_eid(source, target) scene_graph.es[edge_id]['direction'] = direction graphs[scene] = scene_graph calc_finish_time = datetime.datetime.utcnow() Log.info( 'Creation of position graphs took {}'.format(calc_finish_time - calc_start_time)) return graphs
def get_previous_session(self): query = """ SELECT pk, session_uid FROM probedata.session WHERE store_fk = (SELECT store_fk FROM probedata.session WHERE session_uid = '{}') ORDER BY visit_date DESC LIMIT 1 , 1; """.format(self.session_uid) previous_session = pd.read_sql_query(query, self.rds_conn.db) Log.info("Getting previous session for {c}: => {p}".format( c=self.session_uid, p=previous_session)) return previous_session
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for this session') return self.tool_box.tools.update_templates() set_names = ['Product Blocking', 'Linear Share of Shelf', 'OSA', 'Pallet Presence', 'Share of Assortment'] for kpi_set_name in set_names: self.tool_box.main_calculation(set_name=kpi_set_name) self.tool_box.main_calculation(set_name='Shelf Level') self.tool_box.main_calculation(set_name='Linear Share of Shelf vs. Target') self.tool_box.main_calculation(set_name='Shelf Impact Score') self.tool_box.save_custom_scene_item_facts_results() self.tool_box.save_linear_length_results() Log.info('Downloading templates took {}'.format(self.tool_box.download_time)) self.tool_box.commit_results_data()
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for this session') if self.tool_box.store_type: successful = CCTH_SANDSuccessfulSessions(self.tool_box.rds_conn, self.session_uid) success_status = successful.update_session() if success_status == 1: Log.info('Session is surveyed - hence calculating KPIs') self.tool_box.calculate_red_score() self.tool_box.calculate_report() self.tool_box.write_gaps_to_db() else: Log.info('Session is not surveyed - hence skipping KPI calculations') self.tool_box.commit_results_data() else: Log.warning('Store type is empty for this session')
def calculate_product_unique_position_on_shelf(self, scene_id, shelf_number, **filters): """ :param scene_id: The scene ID. :param shelf_number: The number of shelf in question (from top). :param filters: These are the parameters which the unique position is checked for. :return: The position of the first SKU (from the given filters) to appear in the specific shelf. """ shelf_matches = self.match_product_in_scene[(self.match_product_in_scene['scene_fk'] == scene_id) & (self.match_product_in_scene['shelf_number'] == shelf_number)] if shelf_matches[self.get_filter_condition(shelf_matches, **filters)].empty: Log.info("Products of '{}' are not tagged in shelf number {}".format(filters, shelf_number)) return None shelf_matches = shelf_matches.sort_values(by=['bay_number', 'facing_sequence_number']) shelf_matches = shelf_matches.drop_duplicates(subset=['product_ean_code']) products = shelf_matches[self.get_filter_condition(shelf_matches, **filters)]['product_ean_code'].tolist() for i in xrange(len(shelf_matches)): match = shelf_matches.iloc[i] if match['product_ean_code'] in products: return i + 1 return None
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ calc_start_time = datetime.datetime.utcnow() Log.info('Calculation Started at {}'.format(calc_start_time)) if not self.tool_box.scif.empty: self.tool_box.main_calculation() # Saving a dummy result into Level1 (KPI set) for set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique(): category = self.tool_box.kpi_static_data[self.tool_box.kpi_static_data['kpi_set_fk'] == set_fk]['kpi_set_name'].values[0] if self.tool_box.validate_category(category): self.tool_box.write_to_db_result(fk=set_fk, score=100, level=self.tool_box.LEVEL1) else: Log.warning('Scene item facts is empty for this session') self.tool_box.commit_results_data() calc_finish_time = datetime.datetime.utcnow() Log.info('Calculation time took {}'.format(calc_finish_time - calc_start_time))
def oos_sku_level(self, lvl_3_result): """ This function create df sql results, results of oos on sku level based assortment :param lvl_3_result: df of assortment results in sku level :return: df of sql results for oos assortment sku level """ # filter distrubution kpis # oos_results = lvl_3_result[lvl_3_result['result'] == 0] oos_results = lvl_3_result.copy() if oos_results.empty: return oos_results oos_result = self.kpi_result_value(0) oos_results = oos_results.loc[oos_results['result'] == oos_result] if oos_results.empty: return oos_results oos_sku_kpi = self.get_kpi_fk('Live OOS - SKU') oos_results.loc[:, 'kpi_level_2_fk'] = oos_sku_kpi oos_results = self.filter_df_by_col(oos_results, self.SKU_LEVEL) Log.info('oos_results_sku level Done') return oos_results
def run_project_calculations(self): self.timer.start() Log.info('INTEG15 is running') INTEG15EmptySpaceKpiGenerator(self.data_provider, self.output, self.data_provider.project_name).main_function() self.timer.stop('PngCNEmptyCalculations.run_project_calculations') # if __name__ == '__main__': # LoggerInitializer.init('Png-cn calculations') # Config.init() # # project_name = 'pngcn-prod' # project_name = 'integ15' # data_provider = ACEDataProvider(project_name) # session = 'aa0ea0fc-4ed8-4852-a1ee-af513723dd89' # # for session in sessions: # data_provider.load_session_data(session) # output = Output() # SessionVanillaCalculations(data_provider, output, project_name).run_project_calculations() # INTEG15PngCNEmptyCalculations(data_provider, output).run_project_calculations() # data_provider.export_session_data(output)
def create_position_graphs(self, scene_id=None): """ This function creates a facings Graph for each scene of the given session. """ calc_start_time = datetime.datetime.utcnow() if scene_id: scenes = [scene_id] else: scenes = self.match_product_in_scene['scene_fk'].unique() for scene in scenes: matches = self.match_product_in_scene[ (self.match_product_in_scene['scene_fk'] == scene) & (self.match_product_in_scene['stacking_layer'] == 1)] matches['distance_from_end_of_shelf'] = matches[ 'n_shelf_items'] - matches['facing_sequence_number'] scene_graph = igraph.Graph(directed=True) edges = [] for f in xrange(len(matches)): facing = matches.iloc[f] facing_name = str(facing[VERTEX_FK_FIELD]) scene_graph.add_vertex(facing_name) # adding attributes to vertex vertex = scene_graph.vs.find(facing_name) for attribute in self.ATTRIBUTES_TO_SAVE: vertex[attribute] = facing[attribute] surrounding_products = self.get_surrounding_products( facing, matches) for direction in surrounding_products.keys(): for pk in surrounding_products[direction]: edge = dict(source=facing_name, target=str(pk), direction=direction) edges.append(edge) for edge in edges: scene_graph.add_edge(**edge) self.position_graphs[scene] = scene_graph calc_finish_time = datetime.datetime.utcnow() Log.info('Creation of position graphs for scenes {} took {}'.format( scenes, calc_finish_time - calc_start_time))
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for this session') set_names = self.tool_box.kpi_static_data['kpi_set_name'].unique( ).tolist() # self.tool_box.tools.update_templates() # set_names = ['Footcare - Refill', 'Footcare - Tights', 'Footcare - Insoles', 'Footcare - Gadgets', # 'Aircare - Refill', 'Aircare - Candles & Waxmelts', 'Aircare - Gadgets', 'Aircare - Spray', # 'ADW - Brand Group', 'ADW - Products', 'SWB - Products', 'SWB - Brand Group', 'MPC - Sagrotan', # 'MPC - Bath, Kitchen & Liquid', 'MPC - Wipes', 'MPC - Cillit', 'Displays', 'Gondola Ends', # 'Second Placement', 'Location'] # set_names = ['Footcare', 'Aircare'] for kpi_set_name in set_names: self.tool_box.main_calculation(set_name=kpi_set_name) Log.info('Downloading templates took {}'.format( self.tool_box.download_time)) self.tool_box.commit_results_data()
def calculate_energy_drinks(self, shelf_occupation_dict, product_list_field): """ this function calculates score for energy drinks category """ score = 0 for shelf_number in range( 1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1): for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1): # get the current probe to calculate - specific shelf, bay, and only in main_placement scene type curr_probe = get_curr_probe( shelf_occupation_dict.get(DF), shelf_number, bay_number, shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES)) if not curr_probe.empty: score += self.calculate_category(curr_probe, product_list_field) Log.info("category score " + str(score)) return score
def distribution_group_level(self, lvl_2_result): """ This function create df sql results, results of distribution on group level based assortment :param lvl_2_result: df of assortment results in group level :return: df of sql results for oos assortment group level """ lvl_2_result = lvl_2_result.copy() live_kpi_dist = self.get_kpi_fk(self.LIVE_DIST) lvl_2_result.loc[:, 'kpi_level_2_fk'] = live_kpi_dist lvl_2_result.loc[lvl_2_result['target'] == -1, 'target'] = None lvl_2_result.loc[:, 'denominator_result'] = \ lvl_2_result.apply(lambda row: row['target'] if (row['target'] >= 0 and row['group_target_date'] > self.current_date) else row['denominator_result'], axis=1) lvl_2_result.loc[:, 'result'] = lvl_2_result.numerator_result / lvl_2_result.denominator_result self.manipulate_result_row(lvl_2_result) self._add_visit_summary_kpi_entities(lvl_2_result) lvl_2_result = lvl_2_result[self.LVL2_SESSION_RESULTS_COL] Log.info('Distribution group level is done ') return lvl_2_result
def calculate_count_posm_per_scene(self, kpi_fk): if self.match_display_in_scene.empty: Log.info("No POSM detected at scene level for session: {}".format(self.session_uid)) return False grouped_data = self.match_display_in_scene.groupby(['scene_fk', 'display_fk']) for data_tup, scene_data_df in grouped_data: scene_fk, display_fk = data_tup posm_count = len(scene_data_df) template_fk = self.scene_info[self.scene_info['scene_fk'] == scene_fk].get('template_fk') if not template_fk.empty: cur_template_fk = int(template_fk) else: Log.info("JRIJP: Scene ID {scene} is not complete and not found in scene Info.".format( scene=scene_fk)) continue self.common.write_to_db_result(fk=kpi_fk, numerator_id=display_fk, denominator_id=self.store_id, context_id=cur_template_fk, result=posm_count, score=scene_fk)
def save_latest_templates(self): """ This function reads the latest templates from the Cloud, and saves them in a local path. """ if not os.path.exists(self.local_templates_path): os.makedirs(self.local_templates_path) dir_name = self.get_latest_directory_date_from_cloud( self.cloud_templates_path.format(''), self.amz_conn) files = [ f.key for f in self.amz_conn.bucket.list( self.cloud_templates_path.format(dir_name)) ] for file_path in files: file_name = file_path.split('/')[-1] with open(os.path.join(self.local_templates_path, file_name), 'wb') as f: self.amz_conn.download_file(file_path, f) with open(os.path.join(self.local_templates_path, UPDATED_DATE_FILE), 'wb') as f: f.write(datetime.utcnow().strftime(UPDATED_DATE_FORMAT)) Log.info('Latest version of templates has been saved to cache')
def distribution_sku_level(self, lvl_3_result): """ This function receive df = lvl_3_result assortment with data regarding the assortment products This function turn the sku_assortment_results to be in a shape of db result. return distribution_db_results df """ lvl_3_result.rename(columns={ 'product_fk': 'numerator_id', 'assortment_group_fk': 'denominator_id', 'in_store': 'result', 'kpi_fk_lvl3': 'kpi_level_2_fk' }, inplace=True) lvl_3_result.loc[:, 'result'] = lvl_3_result.apply( lambda row: self.kpi_result_value(row.result), axis=1) lvl_3_result = lvl_3_result.assign( numerator_result=lvl_3_result['result'], denominator_result=lvl_3_result['result'], score=lvl_3_result['result']) lvl_3_result = self.filter_df_by_col(lvl_3_result, self.SKU_LEVEL) Log.info('Distribution sku level is done ') return lvl_3_result
def calculate_and_save_distribution_and_oos(self, valid_scif, assortment_product_fks, distribution_kpi_fk, oos_kpi_fk): """Function to calculate distribution and OOS percentage. Saves distribution and oos percentage as values. """ Log.info("Calculate distribution and OOS for {}".format(self.project_name)) scene_products = pd.Series(valid_scif["item_id"].unique()) total_products_in_assortment = len(assortment_product_fks) count_of_assortment_prod_in_scene = assortment_product_fks.isin(scene_products).sum() oos_count = total_products_in_assortment - count_of_assortment_prod_in_scene # count of lion sku / all sku assortment count if not total_products_in_assortment: Log.info("No assortments applicable for session {sess}.".format(sess=self.session_uid)) return 0 distribution_perc = count_of_assortment_prod_in_scene / float(total_products_in_assortment) oos_perc = 1 - distribution_perc self.common_v2.write_to_db_result(fk=distribution_kpi_fk, numerator_id=self.own_manufacturer_fk, numerator_result=count_of_assortment_prod_in_scene, denominator_id=self.store_id, denominator_result=total_products_in_assortment, context_id=self.store_id, result=distribution_perc, score=distribution_perc, identifier_result="{}_{}".format(DST_MAN_BY_STORE_PERC, self.store_id), should_enter=True ) self.common_v2.write_to_db_result(fk=oos_kpi_fk, numerator_id=self.own_manufacturer_fk, numerator_result=oos_count, denominator_id=self.store_id, denominator_result=total_products_in_assortment, context_id=self.store_id, result=oos_perc, score=oos_perc, identifier_result="{}_{}".format(OOS_MAN_BY_STORE_PERC, self.store_id), should_enter=True )
def upload_new_templates(self, immediate_change=False): """ This function uploads the new template, along with the latest version of the rest of the templates, to a new directory (with name as the current date's) in the Cloud. """ if not self.templates_to_upload: Log.info(self.log_suffix + 'No new templates are ready for upload') else: if not immediate_change: next_day = (datetime.utcnow() + timedelta(1)).strftime("%y%m%d") else: next_day = datetime.utcnow().strftime("%y%m%d") templates_path_in_cloud = self.templates_path.format(next_day) latest_templates = self.get_latest_templates() for set_name in self.templates_to_upload: self.amz_conn.save_file(templates_path_in_cloud, set_name, self.templates_to_upload[set_name]) os.remove(self.templates_to_upload[set_name]) if set_name in latest_templates: latest_templates.pop(set_name) Log.info(self.log_suffix + 'New templates for sets {} were uploaded'.format(self.templates_to_upload.keys())) for template_name in latest_templates: temp_file_path = '{}/{}_temp'.format(os.getcwd(), template_name) f = open(temp_file_path, 'wb') self.amz_conn.download_file(latest_templates[template_name], f) f.close() self.amz_conn.save_file(templates_path_in_cloud, template_name, temp_file_path) os.remove(temp_file_path) Log.info(self.log_suffix + 'Existing templates for sets {} were aligned with the new ones'.format(latest_templates.keys())) return True
def main_function(self): comment = None if self.session_feedback in SESSION_FEEDBACK: status = self.UNSUCCESSFUL_TM elif self.scene_info.empty: status = self.UNSUCCESSFUL_OTHER comment = 'No Scenes' elif all(self.scene_info['template_name'].str.contains( EXTERIOR, flags=re.IGNORECASE)): status = self.UNSUCCESSFUL_OTHER comment = 'All scenes are exterior' elif self.session_feedback.upper() != OK_FEEDBACK: status = self.UNSUCCESSFUL_OTHER comment = "Session feedback is not '{}'".format(OK_FEEDBACK) else: status = self.SUCCESSFUL if status == self.SUCCESSFUL: Log.info('Session is successful') elif status == self.UNSUCCESSFUL_TM: Log.info('Session is unsuccessful (TM)') self.save_review(exclude_status=1, resolution_code=2, action_code=6) elif status == self.UNSUCCESSFUL_OTHER: Log.info('Session is unsuccessful (Other) - {}'.format(comment)) self.save_review(exclude_status=1, resolution_code=2, action_code=5)
def check_allowed_values(self): Log.info("Allowed Values Check - Started") result = True allowed_values = [ column for column in self.data_mapping if column.get("allowed_values", False) ] lst_exceptions = [] for allowed_value in allowed_values: name = allowed_value['xl_column_name'] value = allowed_value['allowed_values'] check = self.hierarchy_template[~self.hierarchy_template[name].str. lower().isin(value)] if not check.empty: for idx, row_data in check.iterrows(): dict_exception = dict() dict_exception[ 'exception'] = "Invalid input, allowed_values: {}".format( value) dict_exception[ 'message'] = "row_number: {}, column_name: {}, value: {}".format( idx + 3, name, row_data[name]) if is_debug: Log.info(dict_exception) lst_exceptions.append(dict_exception) result = False if result: Log.info("Allowed Values Check - Completed") return result
def record_all_products(self): kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY) & (self.kpi_static_data[TYPE] == ALL_PROD_KPI) & (self.kpi_static_data['delete_time'].isnull())] if kpi.empty: Log.info("KPI Name:{} not found in DB".format(ALL_PROD_KPI)) else: Log.info("KPI Name:{} found in DB".format(ALL_PROD_KPI)) num_of_prods = 0 # it will have a substitution product; where the facings are aggregated self.scif.dropna(subset=['facings'], inplace=True) for index, each_row in self.scif.iterrows(): self.common.write_to_db_result( fk=int(kpi.iloc[0].pk), numerator_id=int(each_row.product_fk), numerator_result=int(each_row.facings), denominator_id=int(each_row.scene_id), denominator_result=0, score=1, context_id=int(each_row.template_fk), ) num_of_prods += 1 Log.info( "{proj} - For session: {sess}, {prod_count} products were written for kpi: {kpi_name}" .format( proj=self.project_name, sess=self.session_uid, prod_count=num_of_prods, kpi_name=kpi.iloc[0].type, ))
def add_atomics_to_static(self): atomics = self.data.drop_duplicates( subset=[self.SET_NAME, self.KPI_NAME, self.ATOMIC_NAME], keep='first') cur = self.aws_conn.db.cursor() for i in xrange(len(atomics)): atomic = atomics.iloc[i] set_name = atomic[self.SET_NAME].replace("'", "\\'").encode('utf-8') kpi_name = str(atomic[self.KPI_NAME]).replace("'", "\\'").encode('utf-8') atomic_name = str(atomic[self.ATOMIC_NAME]).replace("'", "\\'").encode('utf-8') if self.ATOMIC_WEIGHT in atomics.iloc[i].keys(): atomic_weight = float(atomics.iloc[i][self.ATOMIC_WEIGHT]) else: atomic_weight = 'NULL' if self.ATOMIC_DISPLAY_TEXT in atomics.iloc[i].keys(): atomic_display_text = atomics.iloc[i][self.ATOMIC_DISPLAY_TEXT].replace( "'", "\\'").encode('utf-8') else: atomic_display_text = atomic_name if self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) & (self.kpi_static_data['kpi_name'] == kpi_name) & (self.kpi_static_data['atomic_kpi_name'] == atomic_name)].empty: if set_name in self.kpis_added.keys() and kpi_name in self.kpis_added[set_name].keys(): kpi_fk = self.kpis_added[set_name][kpi_name] else: kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) & (self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0] level3_query = \ """ INSERT INTO static.atomic_kpi (kpi_fk, name, description, display_text, presentation_order, display, atomic_weight) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}'); """.format(kpi_fk, atomic_name, atomic_name, atomic_display_text, 1, 'Y', atomic_weight) cur.execute(level3_query) self.kpi_counter['atomic'] += 1 else: Log.info("Atomic '{}' already exists for KPI '{}' Set '{}'. Ignored".format( atomic_name, kpi_name, set_name)) self.aws_conn.db.commit()
def save_display_presence_per_sku(self, kpi, posm_to_check=None, numerator_result=None, mandatory_eans=None, optional_posm_eans=None): # This should be done once only per scene current_scene_fk = self.scene_info.iloc[0].scene_fk context_fk = self.scene_info.iloc[0].template_fk if posm_to_check and (mandatory_eans or optional_posm_eans): # the scene is valid as per external targets template # PER SCENE which are secondary display # save POSM as numerator; each product as denominator and template as context Log.info('Calculate display presence per sku. The session: {sess} - scene: {scene} is valid.' .format(sess=self.session_uid, scene=current_scene_fk)) numerator_id = posm_to_check else: # the scene doesn't have mandatory or optional eans # PER SCENE which are secondary display # save numerator as empty; each empty as denominator and template as context Log.info('Calculate display presence per sku. The session: {sess} - scene: {scene} is invalid.' .format(sess=self.session_uid, scene=current_scene_fk)) numerator_id = 0 # General Empty # result => [ 0=Optional, 1=mandatory, 2=NA] # numerator_result => [0-- posm not recognized; 1--one one posm; 2--multi posm] for idx, each_row in self.scif.iterrows(): result = 2 # NA if mandatory_eans and each_row.product_ean_code in mandatory_eans: result = 1 # mandatory elif optional_posm_eans and each_row.product_ean_code in optional_posm_eans: result = 0 # optional score = min(each_row.median_price, each_row.median_promo_price) if not score or np.isnan(score): score = -1 self.common.write_to_db_result( fk=kpi.iloc[0].pk, numerator_id=numerator_id, # its the POSM to check or {General Empty if not recognized or multi} numerator_result=numerator_result, # whether POSM is 0, 1 or 2 denominator_id=each_row.item_id, # each product in scene score=score, # -1 means not saved in DB result=result, # 0-optional, 1-mandatory, 2- NA context_id=context_fk, # template of scene by_scene=True, ) # Save All Displays in the scene Log.info('Save all displays in the scene. The session: {sess} - scene: {scene}.' .format(sess=self.session_uid, scene=current_scene_fk)) kpi_all_displays_in_scene = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAYS_ALL_IN_SCENE) & (self.kpi_static_data['delete_time'].isnull())] for idx, each_row in self.match_display_in_scene.iterrows(): self.common.write_to_db_result( fk=kpi_all_displays_in_scene.iloc[0].pk, numerator_id=each_row.display_fk, # the Display/POSM present in the scene numerator_result=1, # no meaning denominator_id=each_row.display_brand_fk, # display brand score=1, # no meaning result=1, # no meaning context_id=self.store_id, # template of scene by_scene=True, ) return True
def calculate_overall_result_and_score(self, result_kpi_pk, score_kpi_fk, group_fk, product_presence_data, product_position_data, product_facings_data, best_shelf_position, min_group_product_facing): numerator_result = 1 in product_presence_data.values() # product_position_data -> (min_shelf, is_in_config) min_level_of_product = 0 if product_position_data: _score_products_presence = filter(lambda x: x[1] == 1, product_position_data.values()) if _score_products_presence: # find min result among the in config ones _score_products_presence.sort(key=lambda x: x[0]) min_level_of_product = int(_score_products_presence[0][0]) else: # score is all 0 _score_products_presence_present = filter(lambda x: x[0] != 0, product_position_data.values()) _score_products_presence_present.sort(key=lambda x: x[0]) if _score_products_presence_present: min_level_of_product = _score_products_presence_present[0][0] else: Log.info("Product presence information is empty for any product in group = {group_fk}".format( group_fk=group_fk )) Log.info("Saving Overall Result for group: {group_fk} in session {sess}" .format(group_fk=group_fk, sess=self.session_uid, )) self.common.write_to_db_result(fk=result_kpi_pk, numerator_id=group_fk, denominator_id=self.store_id, context_id=self.store_id, numerator_result=int(numerator_result), # bool to int denominator_result=min_level_of_product, result=sum(product_facings_data.values()), # bool to int ) Log.info("Saving Overall Score for group: {group_fk} in session {sess}" .format(group_fk=group_fk, sess=self.session_uid, )) is_in_best_shelf = False if best_shelf_position and min_level_of_product: if type(best_shelf_position) != list: best_shelf_position = [best_shelf_position] is_in_best_shelf = min_level_of_product in map(lambda x: int(x), best_shelf_position) has_minumum_facings_per_config = False if not min_group_product_facing or not min_group_product_facing.strip(): min_group_product_facing = 0 if sum(product_facings_data.values()) >= int(min_group_product_facing): has_minumum_facings_per_config = True self.common.write_to_db_result(fk=score_kpi_fk, numerator_id=group_fk, denominator_id=self.store_id, context_id=self.store_id, numerator_result=int(numerator_result), # bool to int denominator_result=int(is_in_best_shelf), # bool to int result=int(has_minumum_facings_per_config), # bool to int score=int(all([numerator_result, is_in_best_shelf, has_minumum_facings_per_config])) # bool to int )
def check_allowed_values(self): result = True allowed_values = [ column for column in self.data_mapping if column.get("allowed_values", False) ] for allowed_value in allowed_values: name = allowed_value['xl_column_name'] value = allowed_value['allowed_values'] check = self.template[~self.template[name].str.lower().isin(value)] if not check.empty: Log.error("Invalid input, allowed_values: {}".format(value)) for idx, row_data in check.iterrows(): Log.error( "row_number: {}, column_name: {}, value: {}".format( idx + 3, name, row_data[name])) result = False if result: Log.info("Allowed Values check completed") return result
def check_template(self): set_names = self._get_set_names() suspicious = { 'kpis with empty filters': [], 'filters with no data': {}, 'kpi with no filters': [] } for set_name in set_names: template_data = self._template.parse_template(set_name) hierarchy = Definition( template_data, self._get_store_channel).get_atomic_hierarchy_and_filters( set_name) product = self._data_provider.all_products for kpi in hierarchy: if kpi.has_key('filters'): fil = kpi['filters'] if len(fil) == 0: suspicious['kpis with empty filters'].append( 'set {} atomic {} filters are empty'.format( set_name, kpi['atomic'])) for key, value in fil.iteritems(): if key == 'order by': continue key = self.split_filter(key) key = self.rename_filter(key) if isinstance(value, tuple): value = value[0] Log.info('set {} atomic {}, checking {} in {}'.format( set_name, kpi['atomic'], value, key)) pp = product[product[key].isin(value)] if len(pp) == 0: suspicious['filters with no data'].setdefault( key, set()).update(value) else: suspicious['kpi with no filters'].append( 'set {} kpi {} has no filters'.format( set_name, kpi['atomic'])) return suspicious
def run_project_calculations(self): self.timer.start() tool_box = KCUSToolBox(self.data_provider, self.output, set_name='KC Score') self.timer.stop('KPIGenerator.run_project_calculations') jg = KCUSJsonGenerator('kc_us-prod') jg.create_json('KEngine_KC_US.xlsx') for p in jg.project_kpi_dict['Block Together']: if p.get('KPI_Type') == 'Relative position of Blocked Together': tool_box.calculate_block_together_relative(p) if p.get('KPI_Type') == 'Blocked Together': tool_box.calculate_block_together(p) for p in jg.project_kpi_dict['Simple KPIs']: if p.get('KPI_Type') == 'Anchor': tool_box.calculate_anchor(p) if p.get('KPI_Type') == 'Eye Level': tool_box.calculate_eye_level(p) if p.get('KPI_Type') == 'Flow': tool_box.calculate_flow(p) # if p.get('KPI_Type') == 'Survey': # tool_box.check_survey_answer(p) # if p.get('KPI_Type') == 'Flow between': # tool_box.calculate_flow_between(p) for p in jg.project_kpi_dict['Relative Position']: if p.get('KPI_Type') == 'Relative Position': tool_box.calculate_relative_position(p) if p.get('KPI_Type') == 'Assortment': tool_box.calculate_assort(p) tool_box.commit_results_data() kpitool_box = KCUS_KPIToolBox(self.data_provider, self.output, set_name='KC Score') kpitool_box.main_calculation() kpitool_box.commit_results_data() Log.info('calculate kpi took {}'.format(tool_box.download_time))
def get_scif_own_man_products_of_session(self, session_uid): Log.info("Getting previous sessions own manufacturer products") query = """ SELECT item_id as product_fk FROM reporting.scene_item_facts scif JOIN probedata.session sess ON sess.pk = scif.session_id JOIN static_new.product prod ON prod.pk = scif.item_id JOIN static_new.brand brand ON brand.pk = prod.brand_fk WHERE sess.session_uid = '{session_uid}' AND facings <> 0 AND prod.type='SKU' AND prod.is_active = 1 AND brand.manufacturer_fk = {manuf}; """.format(session_uid=session_uid, manuf=self.own_manufacturer_fk) product_df = pd.read_sql_query(query, self.rds_conn.db) return product_df
def calculate_brand_presence_overall_score(self, df): if df.empty: return kpi_fk = self.get_kpi_fk_by_kpi_type("BRAND_GROUP_PRESENCE_OWN_MANF_WHOLE_STORE") numerator_id = self.own_manufacturer_fk numerator_result = df['result'].sum() denominator_id = self.store_id denominator_result = df.shape[0] score = 0.0 try: score = numerator_result / float(denominator_result) except Exception as ex: Log.info("Warning: {}".format(ex.message)) self.commonV2.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, score=score, result=score)