def run_project_calculations(self): self.timer.start() data_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Data') survey_template_path = os.path.join(data_path, 'SurveyTemplate.xlsx') eye_hand_lvl_template_path = os.path.join(data_path, 'eye_level_jnjuk.xlsx') exclusive_template_path = os.path.join(data_path, 'KPI Exclusions Template.xlsx') survey_template = pd.read_excel(survey_template_path, sheetname='Sheet1') eye_hand_lvl_template = pd.read_excel(eye_hand_lvl_template_path) exclusive_template = pd.read_excel(exclusive_template_path) common = Common(self.data_provider) jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusive_template) jnj_generator.linear_sos_out_of_store_discovery_report() jnj_generator.secondary_placement_location_quality(survey_template) jnj_generator.secondary_placement_location_visibility_quality( survey_template) jnj_generator.share_of_shelf_manufacturer_out_of_sub_category() jnj_generator.calculate_auto_assortment(in_balde=False) jnj_generator.promo_calc_recovery() jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template) jnj_generator.general_assortment_calculation() jnj_generator.osa_calculation() common.commit_results_data() jnj_generator.tool_box.commit_osa_queries() self.timer.stop('KPIGenerator.run_project_calculations')
def run_project_calculations(self): self.timer.start() eye_level_data, exclusion_data = self._parse_templates_for_calculations() common = Common(self.data_provider) try: jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusion_data) self.calculate_local_assortment(jnj_generator, self.data_provider, self.output, common, exclusion_data) # Mobile KPIs with hierarchy jnj_generator.calculate_auto_assortment(in_balde=False, hierarchy=True, just_primary=False, filter_sub_categories=False) jnj_generator.eye_hand_level_sos_calculation(eye_level_data, hierarchy=True) jnj_generator.promo_calc(hierarchy=True) jnj_generator.lsos_with_hierarchy() # API global KPIs jnj_generator.linear_sos_out_of_store_discovery_report() jnj_generator.calculate_auto_assortment(in_balde=False) jnj_generator.promo_calc() jnj_generator.eye_hand_level_sos_calculation(eye_level_data) except Exception as e: print ("Error :{}".format(e)) common.commit_results_data() self.timer.stop('KPIGenerator.run_project_calculations')
class Generator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(data_provider) self.tool_box = MONDELEZUSToolBox(self.data_provider, self.output, self.common) self.cstore_tool_box = CSTOREToolBox(self.data_provider, self.output, self.common) self.sos_tool_box = MONDELEZUSSOSToolBox(self.data_provider, self.output, self.common) @log_runtime('Total Calculations', log_start=True) 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') self.cstore_tool_box.main_calculation() self.sos_tool_box.main_calculation() for kpi_set_fk in self.tool_box.kpi_new_static_data['pk'].unique( ).tolist(): self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk) self.common.commit_results_data()
class INBEVNLINBEVBEGenerator: def __init__(self, data_provider, output): self.k_engine = BaseCalculationsGroup(data_provider, output) self.data_provider = data_provider self.project_name = data_provider.project_name self.output = output self.session_uid = self.data_provider.session_uid self.visit_date = self.data_provider[Data.VISIT_DATE] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.session_info = SessionInfo(data_provider) self.store_id = self.data_provider[Data.STORE_FK] self.common = Common(self.data_provider) self.tool_box = INBEVToolBox(self.data_provider, self.output, common=self.common) @log_runtime('Total Calculations', log_start=True) 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', # 'Product Stacking', 'Shelf Level', 'Linear Share of Shelf vs. Target', 'Shelf Impact Score', # 'Product Group Blocking'] # for kpi_set_name in set_names: # self.tool_box.main_calculation(set_name=kpi_set_name) # 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.main_calculation_poce() self.tool_box.commit_results_data() self.common.commit_results_data()
class SceneGenerator: def __init__(self, data_provider, output=None): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(data_provider) self.scene_tool_box = CCUSSceneToolBox(self.data_provider, self.output, self.common) self.pillar_scene_tool_box = PillarsSceneToolBox(self.data_provider, self.output, self.common) @log_runtime('Total Calculations', log_start=True) def scene_score(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.scene_tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: self.scene_tool_box.scene_score() self.pillar_scene_tool_box.is_scene_belong_to_program() self.common.commit_results_data(result_entity='scene') del self.scene_tool_box del self.pillar_scene_tool_box
def run_project_calculations(self): self.timer.start() eye_hand_lvl_template_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'JNJDE', 'Data', 'eye_level_jnjde.xlsx') eye_hand_lvl_template = pd.read_excel(eye_hand_lvl_template_path) common = Common(self.data_provider) jnj_generator = JNJGenerator(self.data_provider, self.output, common) jnj_generator.linear_sos_out_of_store_discovery_report() # KPI 2 - Share of Shelf jnj_generator.sos_vs_target_calculation() # KPI 3 - OOS jnj_generator.calculate_auto_assortment() # KPI 4 - Share of shelf - Hand & Eye # KPI 12 - New H&E KPI # KPI 13 - Share of shelf - Hand & Eye (Sub-Category) jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template) jnj_generator.eye_hand_level_sos_calculation_de(eye_hand_lvl_template) # KPI 5 IR - Activation compliance vs plans jnj_generator.promo_calc(sales_reps_date='2018-09-30', calc_brand_level=True) # KPI 9 - MSL jnj_generator.assortment_calculation() # KPI 10 - New Display compliance jnj_generator.display_compliance_calculation() # Competitor KPI's jnj_generator.sos_brand_out_of_sub_category() jnj_generator.competitor_eye_hand_level_sos(eye_hand_lvl_template) common.commit_results_data() self.timer.stop('KPIGenerator.run_project_calculations')
class Generator: # SUPER_CATS = ['Yogurt', 'RBG', 'Mexican', 'Soup'] SUPER_CATS = ['Yogurt', 'RBG', 'Soup', 'Mexican'] # SUPER_CATS = ['Mexican'] # Overwriting for testing purposes SUPER_CATS = ['Snacks', 'Yogurt'] def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.common = Common(self.data_provider) self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.toolboxes = {} self.load_toolboxes() @log_runtime('Total Calculations', log_start=True) def main_function(self): for cat in self.SUPER_CATS: if self.toolboxes[cat].scif.empty: Log.warning('Distribution is empty for this session') continue template_path = self.find_template(cat) # ResultUploader(self.project_name, template_path) # EntityUploader(self.project_name, template_path) # AtomicFarse(self.project_name, template_path) self.toolboxes[cat].main_calculation(template_path) self.common.commit_results_data() def load_toolboxes(self): ToolBox = 'imma lazy and no like red lines' for cat in self.SUPER_CATS: exec('from Projects.GMIUS.{0}.Utils.KPIToolBox import ToolBox'. format(cat)) self.toolboxes[cat] = ToolBox(self.data_provider, self.output, self.common) def find_template(self, cat): ''' screw maintaining 4 hardcoded template paths... ''' path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Data') files = os.listdir(path) candidates = [ f for f in files if f.split(' ')[0] == cat and f.split('.')[-1] == 'xlsx' ] versioned_candidates = [] all_vers = [] for x in candidates: all_vers += x.split(' v')[-1].replace('.xlsx', '').split('.') max_digits = len(max(all_vers, key=len)) for x in candidates: version_comps = x.split(' v')[-1].replace('.xlsx', '').split('.') normed_components = [] for i, comp in enumerate(version_comps): norm_comp = int(comp) * 10**max_digits normed_components.append(str(norm_comp)) versioned_candidates.append((float(''.join(normed_components)), x)) template = sorted(versioned_candidates, key=lambda x: x[0])[-1][1] return os.path.join(path, template)
def run_project_calculations(self): self.timer.start() eye_level_data, exclusion_data = self._parse_templates_for_calculations( ) common = Common(self.data_provider) jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusion_data) jnj_generator.linear_sos_out_of_store_discovery_report() jnj_generator.share_of_shelf_manufacturer_out_of_sub_category() jnj_generator.calculate_auto_assortment() jnj_generator.eye_hand_level_sos_calculation(eye_level_data) jnj_generator.promo_calc(sales_reps_date='2019-06-30') common.commit_results_data() self.timer.stop('KPIGenerator.run_project_calculations')
def run_project_calculations(self): self.timer.start() eye_hand_lvl_template, survey_template = self._parse_templates_for_calculations( ) common = Common(self.data_provider) jnj_generator = JNJGenerator(self.data_provider, self.output, common) jnj_generator.secondary_placement_location_quality(survey_template) jnj_generator.secondary_placement_location_visibility_quality( survey_template) jnj_generator.calculate_auto_assortment() jnj_generator.promo_calc(sales_reps_date='2018-09-30') jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template) jnj_generator.linear_sos_out_of_store_discovery_report() common.commit_results_data() self.timer.stop('KPIGenerator.run_project_calculations')
class NESTLETHSceneToolBox: ERROR_LOC = -1 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.templates = self.data_provider[Data.TEMPLATES] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_type = self.data_provider.store_type self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.nestle_generator = SceneGenerator(self.data_provider, self.output, self.common) def main_function(self): shelf_placement_template = pd.read_excel(os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', 'Placement.xlsx'), sheetname='Minimum Shelf', skiprows=1, keep_default_na=False) shelf_placement_dict = self.nestle_generator.nestle_global_shelf_placement_function( shelf_placement_template) if shelf_placement_dict == self.ERROR_LOC: Log.warning( "Shelf placement failed for scene - {0} " "Match product in scene doesnt contain Nestle product in layer 1 " .format(self.scene_info['scene_fk'].iloc[0])) return 0 self.common.save_json_to_new_tables(shelf_placement_dict) scene_availability_dict = self.nestle_generator.nestle_scene_availability_function( ) self.common.save_json_to_new_tables(scene_availability_dict) self.common.commit_results_data(result_entity='scene') return 0
class SceneGenerator: def __init__(self, data_provider, output=None): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(data_provider) @log_runtime('Total CCBOTTLERSUS_SANDSceneCalculations', log_start=True) 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. """ self.calculate_scene_redscore() self.calculate_scene_coke_cooler() self.common.commit_results_data(result_entity='scene') @log_runtime('Scene RedScore Calculations', log_start=True) def calculate_scene_redscore(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ tool_box = CCBOTTLERSUS_SANDSceneRedToolBox(self.data_provider, self.output, self.common) if tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: if tool_box.main_calculation(): pass # self.common.commit_results_data(result_entity='scene') @log_runtime('Scene Coke Cooler Calculations', log_start=True) def calculate_scene_coke_cooler(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ tool_box = CCBOTTLERSUS_SANDSceneCokeCoolerToolbox( self.data_provider, self.output, self.common) if tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: if tool_box.main_calculation(): pass
def run_project_calculations(self): self.timer.start() eye_level_data, exclusion_data, survey_data = self._parse_templates_for_calculations() common = Common(self.data_provider) jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusion_data) jnj_generator.linear_sos_out_of_store_discovery_report() jnj_generator.secondary_placement_location_quality(survey_data) jnj_generator.secondary_placement_location_visibility_quality(survey_data) jnj_generator.share_of_shelf_manufacturer_out_of_sub_category() jnj_generator.calculate_auto_assortment(in_balde=False) jnj_generator.promo_calc_recovery() jnj_generator.eye_hand_level_sos_calculation(eye_level_data) jnj_generator.general_assortment_calculation() jnj_generator.osa_calculation() common.commit_results_data() jnj_generator.tool_box.commit_osa_queries() self.timer.stop('KPIGenerator.run_project_calculations')
class PlanogramGenerator: def __init__(self, data_provider, output=None): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(data_provider) self.scene_tool_box = BATAUPlanogramToolBox(self.data_provider, self.output, self.common) @log_runtime('Total Calculations', log_start=True) def planogram_score(self): if self.scene_tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: self.scene_tool_box.main_function() self.common.commit_results_data()
class SceneGenerator: def __init__(self, data_provider): self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.tool_box = SceneToolBox(self.data_provider, self.common) @log_runtime('Total Calculations', log_start=True) 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. Scene level. """ self.tool_box.main_calculation() self.common.commit_results_data(result_entity='scene')
class Generator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.common = Common(self.data_provider) self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.tool_box = ToolBox(self.data_provider, self.output, self.common) @log_runtime('Total Calculations', log_start=True) def main_function(self): if self.tool_box.scif.empty: Log.warning('Distribution is empty for this session') else: template_path = self.find_template('Template') comp_path = self.find_template('Competitive') adj_path = self.find_template('Adjacencies') # EntityUploader(self.project_name, template_path) # AtomicFarse(self.project_name, template_path) self.tool_box.main_calculation(template_path, comp_path, adj_path) self.common.commit_results_data() def find_template(self, name): ''' screw maintaining hardcoded template paths... ''' path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'Data') files = os.listdir(path) candidates = [ f for f in files if f.split('.')[-1] == 'xlsx' and name in f ] versioned_candidates = [] all_vers = [] for x in candidates: all_vers += x.split(' v')[-1].replace('.xlsx', '').split('.') max_digits = len(max(all_vers, key=len)) for x in candidates: version_comps = x.split(' v')[-1].replace('.xlsx', '').split('.') normed_components = [] for i, comp in enumerate(version_comps): norm_comp = int(comp) * 10**max_digits normed_components.append(str(norm_comp)) versioned_candidates.append((float(''.join(normed_components)), x)) template = sorted(versioned_candidates, key=lambda x: x[0])[-1][1] return os.path.join(path, template)
class DIAGEOUKSceneToolBox(SceneBaseClass): def __init__(self, data_provider): super(DIAGEOUKSceneToolBox, self).__init__(data_provider) self.common = Common(self._data_provider) self.diageo_generator = DIAGEOGenerator(self._data_provider, None, self.common) def calculate_kpis(self): if self.is_scene_relevant(): self.diageo_generator.diageo_global_equipment_score( save_scene_level=True) if not self.common.kpi_results.empty: self.common.commit_results_data(result_entity='scene') def is_scene_relevant(self): if self.diageo_generator.scif.empty: return False else: return True if self.diageo_generator.scif['template_name'].values[ 0] == 'ON - DRAUGHT TAPS' else False
class SceneGenerator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.common = Common(self.data_provider) self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.tool_box = SceneGPUSToolBox(self.data_provider, self.common, self.output) @log_runtime('Total Calculations', log_start=True) 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') self.tool_box.main_calculation() self.common.commit_results_data(result_entity='scene')
class SceneGenerator: def __init__(self, data_provider, output=None): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(data_provider) self.scene_tool_box = SINOTHSceneToolBox(self.data_provider, self.output, self.common) @log_runtime('Total Calculations', log_start=True) def scene_main_calculation(self): """ This is the main KPI calculation function. """ if self.scene_tool_box.match_product_in_scene.empty: Log.warning('Match product in scene is empty for this scene') else: self.scene_tool_box.filter_and_send_kpi_to_calc() self.common.commit_results_data(result_entity='scene')
class NESTLETHToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.nestle_generator = NESTLEGenerator(self.data_provider, self.output, self.common) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ linear_sos_dict = self.nestle_generator.nestle_global_linear_sos_function( ) self.common.save_json_to_new_tables(linear_sos_dict) assortment_dict = self.nestle_generator.nestle_global_assortment_function( ) self.common.save_json_to_new_tables(assortment_dict) self.common.commit_results_data() return
class Generator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.common = Common(self.data_provider) @log_runtime('Total Calculations', log_start=True) 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') # self.tool_box.main_calculation() # self.tool_box.commit_results() # assortment = Assortment(self.data_provider, common=common) # if assortment.store_assortment.empty: # Log.warning('Scene item facts is empty for this session') if self.data_provider['scene_item_facts'].empty: Log.warning('Scene item facts is empty for this session') else: PuestosFijosToolBox(self.data_provider, self.output, self.common).main_calculation() ComidasToolBox(self.data_provider, self.output, self.common).main_calculation() EspecializadoToolBox(self.data_provider, self.output, self.common).main_calculation() FONDASToolBox(self.data_provider, self.output, self.common).main_calculation() NationalToolBox(self.data_provider, self.output, self.common).main_calculation() ToolBox(self.data_provider, self.output, self.common).main_calculation() self.common.commit_results_data()
class SceneCalculations(SceneBaseClass): def __init__(self, data_provider): super(SceneCalculations, self).__init__(data_provider) # self.scene_generator = SceneGenerator(self._data_provider) self.common = Common(self._data_provider) # self._monitor = None # self.timer = self._monitor.Timer('Perform', 'Init Session') def calculate_kpis(self): # self.timer.start() # self.scene_generator.scene_score() template_path = os.path.join( os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data', 'Brand Score.xlsx') diageo_generator = DIAGEOGenerator(self._data_provider, None, self.common) diageo_generator.diageo_global_tap_brand_score_function( template_path=template_path, rank_kpi=False, sub_category_rank_kpi=False, save_scene_level=True, calculate_components=True) self.common.commit_results_data(result_entity='scene')
def create_graph_image(scene_id, graph): filtered_figure = GraphPlot.plot_networkx_graph( graph, overlay_image=True, scene_id=scene_id, project_name='diageous-sand2') filtered_figure.update_layout(autosize=False, width=1000, height=800, title=str(scene_id)) iplot(filtered_figure) if __name__ == '__main__': from KPIUtils_v2.DB.CommonV2 import Common from Trax.Utils.Conf.Configuration import Config from Trax.Algo.Calculations.Core.DataProvider import KEngineDataProvider from Trax.Cloud.Services.Connector.Logger import LoggerInitializer LoggerInitializer.init('KEngine') Config.init('KEngine') test_data_provider = KEngineDataProvider('diageous') sessions = ['2087F0BA-E12A-458A-83D0-0713E9DF1EBA'] for session in sessions: print(session) test_data_provider.load_session_data(session) test_common = Common(test_data_provider) case_counter_calculator = CaseCountCalculator(test_data_provider, test_common) case_counter_calculator.main_case_count_calculations() test_common.commit_results_data()
class PsApacGSKAUToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.set_up_template = pd.read_excel(os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', 'gsk_set_up.xlsx'), sheet_name='Functional KPIs', keep_default_na=False) self.gsk_generator = GSKGenerator(self.data_provider, self.output, self.common, self.set_up_template) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ assortment_store_dict = self.gsk_generator.availability_store_function( ) self.common.save_json_to_new_tables(assortment_store_dict) assortment_category_dict = self.gsk_generator.availability_category_function( ) self.common.save_json_to_new_tables(assortment_category_dict) assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function( ) self.common.save_json_to_new_tables(assortment_subcategory_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_whole_store_function( ) self.common.save_json_to_new_tables(facings_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function( ) self.common.save_json_to_new_tables(linear_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_by_sub_category_function( ) self.common.save_json_to_new_tables(facings_sos_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_by_category_function( ) self.common.save_json_to_new_tables(facings_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) self.common.commit_results_data() return
class CCUSGenerator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.common = Common(self.data_provider) @log_runtime('Total Calculations', log_start=True) 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. """ # self.calculate_fsop() # self.calculate_manufacturer_displays() # # self.calculate_obbo() # # self.calculate_dunkin_donuts() # self.calculate_monster() # self.calculate_programs() # self.calculate_holiday_programs() # # self.calculate_msc_new() # # self.calculate_gold_peak_block() # self.calculate_special_programs() # self.calculate_validation() self.calculate_pillars_programs() # self.calculate_jeff() self.calculate_military() self.common.commit_results_data() @log_runtime('Manufacturer Displays Calculations') def calculate_manufacturer_displays(self): tool_box = DISPLAYSToolBox(self.data_provider, self.output) tool_box.main_calculation() tool_box.commit_results_data() del tool_box @log_runtime('MONSTER Calculations') def calculate_monster(self): tool_box = MONSTERToolBox(self.data_provider, self.output) tool_box.main_calculation() tool_box.commit_results_data(kpi_set_fk=27) del tool_box @log_runtime('FSOP Calculations') def calculate_fsop(self): tool_box = FSOPToolBox(self.data_provider, self.output, self.common) tool_box.main_calculation() del tool_box @log_runtime('JEFF Calculations') def calculate_jeff(self): tool_box = JEFFToolBox(self.data_provider, self.output, self.common) tool_box.main_calculation() del tool_box # @log_runtime('OBBO Calculations') # def calculate_obbo(self): # tool_box = OBBOToolBox(self.data_provider, self.output) # tool_box.main_calculation() # tool_box.commit_results_data() # # @log_runtime('Dunkin Donuts Calculations') # def calculate_dunkin_donuts(self): # set_name = 'Dunkin Donuts' # tool_box = CCUSToolBox(self.data_provider, self.output) # if tool_box.scif.empty: # Log.warning('Scene item facts is empty for this session') # tool_box.tools.update_templates() # tool_box.main_calculation(set_name=set_name) # tool_box.save_level1(set_name=set_name, score=100) # set_fk = tool_box.kpi_static_data[tool_box.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0] # tool_box.commit_results_data(kpi_set_fk=set_fk) # Log.info('Dunkin Donuts: Downloading templates took {}'.format(tool_box.download_time)) @log_runtime('Programs Calculations') def calculate_programs(self): tool_box = PROGRAMSToolBox(self.data_provider, self.output) tool_box.main_calculation() tool_box.commit_results_data(kpi_set_fk=28) del tool_box @log_runtime('Pillars Programs Calculations') def calculate_pillars_programs(self): tool_box = PillarsPROGRAMSToolBox(self.data_provider, self.output, self.common) tool_box.main_calculation() del tool_box # @log_runtime('MSC New Calculations') # def calculate_msc_new(self): # tool_box = MSC_NEWToolBox(self.data_provider, self.output) # tool_box.main_calculation() # tool_box.commit_results_data(kpi_set_fk=29) @log_runtime('Holiday Programs Calculations') def calculate_holiday_programs(self): tool_box = HOLIDAYToolBox(self.data_provider, self.output) tool_box.main_calculation() tool_box.commit_results_data(kpi_set_fk=31) del tool_box # @log_runtime('Programs Calculations') # def calculate_gold_peak_block(self): # tool_box = GOLD_PEAK_BLOCKToolBox(self.data_provider, self.output) # tool_box.main_calculation() # tool_box.commit_results_data(kpi_set_fk = 30) @log_runtime('Special Programs Calculations') def calculate_special_programs(self): tool_box = SpecialProgramsToolBox(self.data_provider, self.output) tool_box.main_calculation() tool_box.commit_results_data(kpi_set_fk=32) del tool_box @log_runtime('Special Programs Calculations') def calculate_validation(self): tool_box = VALIDATIONToolBox(self.data_provider, self.output, kpi_set_fk=34) tool_box.main_calculation() tool_box.commit_results_data() del tool_box @log_runtime('Military Calculations') def calculate_military(self): tool_box = MilitaryToolBox(self.data_provider, self.output, self.common) tool_box.main_calculation() del tool_box
class TWEAUToolBox: 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.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'Template.xlsx') self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.templates = self.data_provider[Data.TEMPLATES] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.store_name = self.store_info.store_name[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.empty_product_ids = self.all_products.query( 'product_name.str.contains("empty", case=False) or' ' product_name.str.contains("irrelevant", case=False)', engine='python')['product_fk'].values def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ # 1. calculate the macro linear sheet self.calculate_macro_linear() # 2. calculate the zone based sheet self.calculate_zone_based() self.common.commit_results_data() score = 0 return score def calculate_zone_based(self): zone_kpi_sheet = self.get_template_details(ZONE_KPI_SHEET) zone_category_sheet = self.get_template_details(ZONE_CATEGORY_SHEET) name_grouped_zone_kpi_sheet = zone_kpi_sheet.groupby(KPI_TYPE) for each_kpi in name_grouped_zone_kpi_sheet: each_kpi_type = each_kpi[0] kpi_sheet_rows = each_kpi[1] denominator_row = pd.Series() write_sku = False kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY) & (self.kpi_static_data[TYPE] == each_kpi_type) & (self.kpi_static_data['delete_time'].isnull())] if kpi.empty: print("KPI Name:{} not found in DB".format(each_kpi_type)) else: print("KPI Name:{} found in DB".format(each_kpi_type)) if 'sku_all' in each_kpi_type.lower(): write_sku = True if 'sku_all' not in each_kpi_type.lower(): # there is no denominator for sku all denominator_row = zone_category_sheet.loc[( zone_category_sheet[KPI_NAME] == each_kpi_type )].iloc[0] list_of_zone_data = [] for idx, each_kpi_sheet_row in kpi_sheet_rows.iterrows(): zone_data = self._get_zone_based_data( kpi, each_kpi_sheet_row.T, denominator_row=denominator_row) list_of_zone_data.append(zone_data) print "Final >>> ", list_of_zone_data if write_sku: # write for products _output_to_write = [] for each in list_of_zone_data: if len(each) > 1: _output_to_write.extend([ x for x in each if 'unique_products' not in x ]) else: # No relevant product as per excel row config parameters continue if not _output_to_write: continue _df_output_to_write = pd.DataFrame(_output_to_write) _df_output_to_write.dropna(subset=['bay_number'], inplace=True) _grouped_output_to_write = _df_output_to_write.groupby( ['scene_id', 'bay_number'], as_index=False) for scene_bay_tuple, each_data_to_write in _grouped_output_to_write: # remove empty products when getting all SKU's products_to_write = set([ prod_id for prod_list_per_bay in each_data_to_write["products"] for prod_id in prod_list_per_bay ]) data_to_write = each_data_to_write.iloc[0] for each_product_id in products_to_write: if int(each_product_id ) not in self.empty_product_ids: in_assort_sc = int( self.scif.query( "item_id=={prod_id}".format( prod_id=each_product_id)). in_assort_sc.values[0]) self.common.write_to_db_result( fk=int(data_to_write.fk), numerator_id=int(each_product_id), numerator_result=int( data_to_write.bay_number), denominator_id=int( data_to_write.denominator_id), denominator_result=int( data_to_write.scene_id), result=int(data_to_write.zone_number), score=in_assort_sc, identifier_result=str( data_to_write.kpi_name), context_id=int(data_to_write.zone_number), ) else: # its the calculation _output_to_write = [] for each in list_of_zone_data: if len(each) > 1: _output_to_write.extend([ x for x in each if 'unique_products' not in x ]) else: continue if not _output_to_write: continue _df_output_to_write = pd.DataFrame(_output_to_write) # remove rows with empty `products` _df_output_to_write = _df_output_to_write[ _df_output_to_write.astype(str)['products'] != "[]"] _grouped_output_to_write = _df_output_to_write.groupby( 'denominator_id', as_index=False) unique_manufacturer_products_count_data = _df_output_to_write.get( "unique_manufacturer_products_count").values if not len(unique_manufacturer_products_count_data): continue unique_manufacturer_products_count = unique_manufacturer_products_count_data[ 0] for denominator_id, each_data_to_write in _grouped_output_to_write: # remove empty products when getting all SKU's # get all unique product ids from different dataframe rows in a set products_to_write = set([ prod_id for prod_list_per_bay in each_data_to_write["products"] for prod_id in prod_list_per_bay ]) # sanitize the products products_to_write = [ x for x in products_to_write if x not in self.empty_product_ids ] result = float(len(products_to_write) ) / unique_manufacturer_products_count data_to_write = each_data_to_write.iloc[ 0] # for dot selection from first row self.common.write_to_db_result( fk=int(data_to_write.fk), numerator_id=int(data_to_write.numerator_id), denominator_id=int(data_to_write.denominator_id), result=int(result), score=1, identifier_result=str(data_to_write.kpi_name), context_id=int(data_to_write.zone_number), ) def calculate_macro_linear(self): kpi_sheet = self.get_template_details(LINEAR_KPI_SHEET) category_sheet = self.get_template_details(LINEAR_CATEGORY_SHEET) for index, kpi_sheet_row in kpi_sheet.iterrows(): kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY) & (self.kpi_static_data[TYPE] == kpi_sheet_row[KPI_TYPE]) & (self.kpi_static_data['delete_time'].isnull())] if kpi.empty: print("KPI Name:{} not found in DB".format( kpi_sheet_row[KPI_NAME])) else: print("KPI Name:{} found in DB".format( kpi_sheet_row[KPI_NAME])) permitted_store_types = [ x.strip() for x in kpi_sheet_row[STORE_TYPE].split(',') if x.strip() ] if self.store_info.store_type.values[ 0] not in permitted_store_types: continue # get the length field length_field = STACKING_MAP[kpi_sheet_row[STACKING_COL]] # NUMERATOR numerator_filters, numerator_filter_string = get_filter_string_per_row( kpi_sheet_row, NUMERATOR_FILTER_ENTITIES, additional_filters= LINEAR_NUMERATOR_ADDITIONAL_FILTERS_PER_COL) # Adding compulsory filtering values if numerator_filter_string: numerator_filter_string += " and " # 1. remove empty/irrelevant products numerator_filter_string += "{prod_id_col_scif} not in {empty_prod_ids}".format( prod_id_col_scif=PROD_ID_COL_SCIF, empty_prod_ids=self.empty_product_ids.tolist()) # 2. remove zero facings numerator_filter_string += " and facings > 0" numerator_data = self.scif.query(numerator_filter_string).fillna(0). \ groupby(numerator_filters, as_index=False).agg({length_field: 'sum'}) # DENOMINATOR denominator_row = category_sheet.loc[( category_sheet[KPI_NAME] == kpi_sheet_row[KPI_NAME] )].iloc[0] denominator_filters, denominator_filter_string = get_filter_string_per_row( denominator_row, DENOMINATOR_FILTER_ENTITIES, ) if denominator_filter_string: denominator_data = self.scif.query(denominator_filter_string).fillna(0). \ groupby(denominator_filters, as_index=False).agg({length_field: 'sum'}) else: # nothing to query; no grouping; Transform the DataFrame; get all data denominator_data = pd.DataFrame( self.scif.agg({length_field: 'sum'})).T for d_idx, denominator_row in denominator_data.iterrows(): denominator = denominator_row.get(length_field) for idx, numerator_row in numerator_data.iterrows(): numerator = numerator_row.get(length_field) try: result = float(numerator) / float(denominator) except ZeroDivisionError: result = 0 numerator_id = int(numerator_row[EXCEL_DB_MAP[ kpi_sheet_row.numerator_fk]]) denominator_key_str = EXCEL_DB_MAP[ kpi_sheet_row.denominator_fk] denominator_id = self.get_denominator_id( denominator_key_str, numerator_row, denominator_row) kpi_parent_name = None should_enter = False if not is_nan(kpi_sheet_row.kpi_parent_name): kpi_parent_name = kpi_sheet_row.kpi_parent_name should_enter = True if not denominator_id: raise Exception( "Denominator ID cannot be found. [TWEAU/Utils/KPIToolBox.py]" ) self.common.write_to_db_result( fk=int(kpi['pk']), numerator_id=numerator_id, numerator_result=numerator, denominator_id=denominator_id, denominator_result=denominator, result=result, score=result, identifier_result=kpi_sheet_row[KPI_NAME], identifier_parent=kpi_parent_name, should_enter=should_enter, ) def get_shelf_limit_for_scene(self, scene_id): shelf_limit_per_scene_map = defaultdict(list) scene_data = self.match_product_in_scene.loc[ self.match_product_in_scene['scene_fk'] == scene_id] _bay_grouped_scene_data = scene_data.groupby('bay_number', as_index=False) for each_bay in _bay_grouped_scene_data: bay_number = each_bay[0] scene_data = each_bay[1] if not scene_data.empty: shelf_limit_per_scene_map[scene_id].append((bay_number, { 'max_shelf': scene_data[SHELF_NUMBER].max(), 'min_shelf': scene_data[SHELF_NUMBER].min() })) return shelf_limit_per_scene_map def get_valid_bay_numbers(self, scene_id, permitted_shelves): scene_max_min_map = self.get_shelf_limit_for_scene(scene_id) bay_numbers = [] for scene_id, bay_shelf_map in scene_max_min_map.iteritems(): for each_map in bay_shelf_map: _bay_number = each_map[0] scene_max_min_map = each_map[1] for each_permitted_shelf in permitted_shelves: if scene_max_min_map['max_shelf'] == each_permitted_shelf: bay_numbers.append(_bay_number) return bay_numbers def _get_zone_based_data(self, kpi, kpi_sheet_row, denominator_row): # generate scene max shelf max bay map zone_number = kpi_sheet_row[ZONE_NAME] shelves_policy_from_top = [ int(x.strip()) for x in str(kpi_sheet_row[SHELF_POLICY_FROM_TOP]).split(',') if x.strip() ] permitted_shelves = [ int(x.strip()) for x in str(kpi_sheet_row[NUMBER_OF_SHELVES]).split(',') if x.strip() ] permitted_store_types = [ x.strip() for x in kpi_sheet_row[STORE_TYPE].split(',') if x.strip() ] unique_manufacturer_products_count = 0 # DENOMINATOR if not denominator_row.empty: denominator_filters, denominator_filter_string = get_filter_string_per_row( denominator_row, DENOMINATOR_FILTER_ENTITIES, ) unique_manufacturer_products_count = len( self.scif.query(denominator_filter_string).product_fk.unique()) # NUMERATOR # filter based on store type [d] # filter based on template_type [d] # find the shelf, and filter based on zone # filter based on exclude policy if self.store_info.store_type.values[0] in permitted_store_types: filters, filter_string = get_filter_string_per_row( kpi_sheet_row, ZONE_NUMERATOR_FILTER_ENTITIES, additional_filters=ZONE_ADDITIONAL_FILTERS_PER_COL, ) # combined tables match_product_df = pd.merge(self.match_product_in_scene, self.products, how='left', left_on=['product_fk'], right_on=['product_fk']) scene_template_df = pd.merge(self.scene_info, self.templates, how='left', left_on=['template_fk'], right_on=['template_fk']) product_scene_join_data = pd.merge(match_product_df, scene_template_df, how='left', left_on=['scene_fk'], right_on=['scene_fk']) if filters: filtered_grouped_scene_items = product_scene_join_data.query(filter_string) \ .groupby(filters, as_index=False) else: # dummy structure without filters filtered_grouped_scene_items = [ ('', product_scene_join_data.query(filter_string)) ] # get the scene_id's worth getting data from scene_data_map = [] unique_products = set() # if the scene type not even valid for each_group_by_manf_templ in filtered_grouped_scene_items: # append scene to group by current_scene_types = each_group_by_manf_templ[ 1].template_name.unique() scene_type = current_scene_types if len(current_scene_types) == 1: scene_type = each_group_by_manf_templ[ 1].template_name.unique()[0] # unique_products = set() scene_type_grouped_by_scene = each_group_by_manf_templ[ 1].groupby(SCENE_FK) for each_scene_id_group in scene_type_grouped_by_scene: exclude_items = False include_only = False scene_id = each_scene_id_group[0] scene_data_per_scene = each_scene_id_group[1] valid_bay_numbers = self.get_valid_bay_numbers( scene_id, permitted_shelves) if not valid_bay_numbers: continue matched_products_for_scene = scene_data_per_scene.query( 'shelf_number in {shelves} and bay_number in {bays}'. format(shelves=shelves_policy_from_top, bays=valid_bay_numbers)).groupby( ['bay_number', 'shelf_number'], as_index=False) # scene_uid = self.scene_info.loc[ # self.scene_info['scene_fk'] == each_scene].scene_uid.values[0] items_to_check_str = None if not is_nan(kpi_sheet_row.exclude_include_policy): match_exclude = exclude_re.search( kpi_sheet_row.exclude_include_policy) if not match_exclude: match_only = only_re.search( kpi_sheet_row.exclude_include_policy) items_to_check_str = match_only.groups()[-1] include_only = True else: items_to_check_str = match_exclude.groups()[-1] exclude_items = True for tuple_data in matched_products_for_scene: # get excluding shelves bay_number = tuple_data[0][0] each_shelf_per_bay = tuple_data[1] numerator_entity_id = EXCEL_ENTITY_MAP[ kpi_sheet_row.numerator_fk] numerator_id = getattr( each_shelf_per_bay, EXCEL_DB_MAP[kpi_sheet_row.numerator_fk], None).unique()[0] denominator_entity_id = EXCEL_ENTITY_MAP[ kpi_sheet_row.denominator_fk] denominator_data = getattr( each_shelf_per_bay, EXCEL_DB_MAP[kpi_sheet_row.denominator_fk], pd.Series()) if denominator_data.empty: # find in self denominator_id = getattr( self, EXCEL_DB_MAP[kpi_sheet_row.denominator_fk], None) else: denominator_id = denominator_data.unique()[0] if items_to_check_str: # exclude/include logic last_shelf_number = str( each_shelf_per_bay.n_shelf_items.unique()[0]) shelf_filter = items_to_check_str.replace( 'N', last_shelf_number) shelf_filter_string = '[{}]'.format(shelf_filter) if exclude_items: # exclude rows in `items_to_check_tuple` required_shelf_items = each_shelf_per_bay.drop( each_shelf_per_bay.query( 'facing_sequence_number in {filter_string}' .format( filter_string=shelf_filter_string )).index.tolist()) else: # it is include_only: required_shelf_items = each_shelf_per_bay.query( 'facing_sequence_number in {filter_string}' .format(filter_string=shelf_filter_string)) product_ids = required_shelf_items.product_fk.unique( ).tolist() else: product_ids = each_shelf_per_bay.product_fk.unique( ).tolist() unique_products.update(product_ids) scene_data_map.append({ 'fk': int(kpi['pk']), 'numerator_id': numerator_id, 'denominator_id': denominator_id, 'bay_number': bay_number, 'scene_id': scene_id, 'products': product_ids, 'kpi_name': kpi_sheet_row[KPI_NAME], 'zone_number': zone_number, 'unique_manufacturer_products_count': unique_manufacturer_products_count, }) scene_data_map.append({"unique_products": unique_products}) return scene_data_map return [] def get_denominator_id(self, denominator_key_str, numerator_row, denominator_row): """ :param denominator_key_str: str :param numerator_row: pd.Dataframe :param denominator_row: pd.Dataframe :return: int # zero check in self object # first check in denominator # second check in numerator # third check in self.scif >> always return one integer denominator_id """ denominator_id = getattr(self, denominator_key_str, None) if not denominator_id: denominator_data = denominator_row.get( denominator_key_str, numerator_row.get(denominator_key_str, self.scif.get(denominator_key_str))) if type(denominator_data) == pd.Series: denominator_id = denominator_data.drop_duplicates()[0] else: denominator_id = denominator_data return denominator_id def get_template_details(self, sheet_name): template = pd.read_excel(self.excel_file_path, sheetname=sheet_name) return template
class LIONNZToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.store_info['store_fk'].values[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scene_template_info = self.scif[[ 'scene_fk', 'template_fk', 'template_name' ]].drop_duplicates() self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.kpi_template_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', TEMPLATE_PARENT_FOLDER, TEMPLATE_NAME) self.own_man_fk = self.all_products[ self.all_products['manufacturer_name'].str.lower() == OWN_MAN_NAME.lower()]['manufacturer_fk'].values[0] self.kpi_template = pd.ExcelFile(self.kpi_template_path) self.empty_prod_ids = self.all_products[ self.all_products.product_name.str.contains( 'empty', case=False)]['product_fk'].values self.irrelevant_prod_ids = self.all_products[ self.all_products.product_name.str.contains( 'irrelevant', case=False)]['product_fk'].values self.other_prod_ids = self.all_products[ self.all_products.product_name.str.contains( 'other', case=False)]['product_fk'].values def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ pd.reset_option('mode.chained_assignment') with pd.option_context('mode.chained_assignment', None): self.filter_and_send_kpi_to_calc() self.calculate_assortment_kpis() self.common.commit_results_data() return 0 # to mark successful run of script def calculate_assortment_kpis(self): distribution_kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == DST_MAN_BY_STORE_PERC) & (self.kpi_static_data['delete_time'].isnull())] oos_kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == OOS_MAN_BY_STORE_PERC) & (self.kpi_static_data['delete_time'].isnull())] prod_presence_kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == PRODUCT_PRESENCE_BY_STORE_LIST) & (self.kpi_static_data['delete_time'].isnull())] oos_prod_kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == OOS_PRODUCT_BY_STORE_LIST) & (self.kpi_static_data['delete_time'].isnull())] def __return_valid_store_policies(policy): policy_json = json.loads(policy) store_json = json.loads( self.store_info.reset_index().to_json(orient='records'))[0] valid_store = True # map the necessary keys to those names knows for policy_value, store_info_value in POLICY_STORE_MAP.iteritems(): if policy_value in policy_json: policy_json[store_info_value] = policy_json.pop( policy_value) for key, values in policy_json.iteritems(): if str(store_json[key]) in values: continue else: valid_store = False break return valid_store policy_data = self.get_policies(distribution_kpi.iloc[0].pk) if policy_data.empty: Log.info("No Assortments Loaded.") return 0 resp = policy_data['policy'].apply(__return_valid_store_policies) valid_policy_data = policy_data[resp] if valid_policy_data.empty: Log.info("No policy applicable for session {sess} and kpi {kpi}.". format(sess=self.session_uid, kpi=distribution_kpi.iloc[0].type)) return 0 # calculate and save the percentage values for distribution and oos self.calculate_and_save_distribution_and_oos( assortment_product_fks=valid_policy_data['product_fk'], distribution_kpi_fk=distribution_kpi.iloc[0].pk, oos_kpi_fk=oos_kpi.iloc[0].pk) # calculate and save prod presence and oos products self.calculate_and_save_prod_presence_and_oos_products( assortment_product_fks=valid_policy_data['product_fk'], prod_presence_kpi_fk=prod_presence_kpi.iloc[0].pk, oos_prod_kpi_fk=oos_prod_kpi.iloc[0].pk, distribution_kpi_name=DST_MAN_BY_STORE_PERC, oos_kpi_name=OOS_MAN_BY_STORE_PERC) def calculate_and_save_prod_presence_and_oos_products( self, assortment_product_fks, prod_presence_kpi_fk, oos_prod_kpi_fk, distribution_kpi_name, oos_kpi_name): # all assortment products are only in own manufacturers context total_own_products_in_scene = self.scif[ self.scif['manufacturer_fk'].astype( int) == self.own_man_fk]["item_id"].unique() present_products = np.intersect1d(total_own_products_in_scene, assortment_product_fks) extra_products = np.setdiff1d(total_own_products_in_scene, present_products) oos_products = np.setdiff1d(assortment_product_fks, present_products) product_map = { OOS_CODE: oos_products, PRESENT_CODE: present_products, EXTRA_CODE: extra_products } # save product presence; with distribution % kpi as parent for assortment_code, product_fks in product_map.iteritems(): for each_fk in product_fks: self.common.write_to_db_result( fk=prod_presence_kpi_fk, numerator_id=each_fk, denominator_id=self.store_id, context_id=self.store_id, result=assortment_code, score=assortment_code, identifier_result=CODE_KPI_MAP.get(assortment_code), identifier_parent="{}_{}".format(distribution_kpi_name, self.store_id), should_enter=True) if assortment_code == OOS_CODE: # save OOS products; with OOS % kpi as parent for each_fk in product_fks: self.common.write_to_db_result( fk=oos_prod_kpi_fk, numerator_id=each_fk, denominator_id=self.store_id, context_id=self.store_id, result=assortment_code, score=assortment_code, identifier_result=CODE_KPI_MAP.get(assortment_code), identifier_parent="{}_{}".format( oos_kpi_name, self.store_id), should_enter=True) def calculate_and_save_distribution_and_oos(self, 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(self.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) * 100 oos_perc = 100 - distribution_perc self.common.write_to_db_result( fk=distribution_kpi_fk, numerator_id=self.own_man_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.write_to_db_result( fk=oos_kpi_fk, numerator_id=self.own_man_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 get_policies(self, kpi_fk): query = """ select a.kpi_fk, p.policy_name, p.policy, atag.assortment_group_fk, atp.assortment_fk, atp.product_fk, atp.start_date, atp.end_date from pservice.assortment_to_product atp join pservice.assortment_to_assortment_group atag on atp.assortment_fk = atag.assortment_fk join pservice.assortment a on a.pk = atag.assortment_group_fk join pservice.policy p on p.pk = a.store_policy_group_fk where a.kpi_fk={kpi_fk} AND '{sess_date}' between atp.start_date AND atp.end_date; """ policies = pd.read_sql_query( query.format(kpi_fk=kpi_fk, sess_date=self.session_info.iloc[0].visit_date), self.rds_conn.db) return policies def filter_and_send_kpi_to_calc(self): kpi_sheet = self.kpi_template.parse(KPI_NAMES_SHEET) kpi_sheet[KPI_FAMILY_COL] = kpi_sheet[KPI_FAMILY_COL].fillna( method='ffill') kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET) kpi_include_exclude = self.kpi_template.parse(KPI_INC_EXC_SHEET) for index, kpi_sheet_row in kpi_sheet.iterrows(): if not is_nan(kpi_sheet_row[KPI_ACTIVE]): if str(kpi_sheet_row[KPI_ACTIVE]).strip().lower() in [ '0.0', 'n', 'no' ]: Log.warning("KPI :{} deactivated in sheet.".format( kpi_sheet_row[KPI_NAME_COL])) continue kpi = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == kpi_sheet_row[KPI_NAME_COL]) & (self.kpi_static_data['delete_time'].isnull())] if kpi.empty: Log.warning( "*** KPI Name:{name} not found in DB for session {sess} ***" .format(name=kpi_sheet_row[KPI_NAME_COL], sess=self.session_uid)) return False else: Log.info( "KPI Name:{name} found in DB for session {sess}".format( name=kpi_sheet_row[KPI_NAME_COL], sess=self.session_uid)) detail = kpi_details[kpi_details[KPI_NAME_COL] == kpi[KPI_TYPE_COL].values[0]] # check for store types allowed permitted_store_types = [ x.strip().lower() for x in detail[STORE_POLICY].values[0].split(',') if x.strip() ] if self.store_info.store_type.iloc[0].lower( ) not in permitted_store_types: Log.warning( "Not permitted store type - {type} for session {sess}". format(type=kpi_sheet_row[KPI_NAME_COL], sess=self.session_uid)) continue detail['pk'] = kpi['pk'].iloc[0] # gather details groupers, query_string = get_groupers_and_query_string(detail) _include_exclude = kpi_include_exclude[ kpi_details[KPI_NAME_COL] == kpi[KPI_TYPE_COL].values[0]] # gather include exclude include_exclude_data_dict = get_include_exclude( _include_exclude) dataframe_to_process = self.get_sanitized_match_prod_scene( include_exclude_data_dict) if kpi_sheet_row[KPI_FAMILY_COL] == FSOS: self.calculate_fsos(detail, groupers, query_string, dataframe_to_process) elif kpi_sheet_row[KPI_FAMILY_COL] == Count: self.calculate_count(detail, groupers, query_string, dataframe_to_process) else: pass return True def calculate_fsos(self, kpi, groupers, query_string, dataframe_to_process): Log.info("Calculate {name} for session {sess}".format( name=kpi.kpi_name.iloc[0], sess=self.session_uid)) prod_empty_sub_cat = dataframe_to_process[dataframe_to_process[ 'sub_category_fk'].isnull()]['product_fk'].tolist() Log.info('Remove products with empty sub category fk: {}'.format( prod_empty_sub_cat)) dataframe_to_process.dropna(subset=['sub_category_fk'], inplace=True) dataframe_to_process = dataframe_to_process.astype( {'sub_category_fk': 'int64'}, errors='ignore') if query_string: grouped_data_frame = dataframe_to_process.query( query_string).groupby(groupers) else: grouped_data_frame = dataframe_to_process.groupby(groupers) # for the two kpis, we need to show zero presence of own manufacturer. # else the flow will be stuck in case own manufacturers are absent altogether. if '_own_' in kpi['kpi_name'].iloc[0].lower() and \ '_whole_store' not in kpi['kpi_name'].iloc[0].lower(): self.scif['store_fk'] = self.store_id dataframe_to_process['store_fk'] = self.store_id scif_with_den_context = self.scif[[ PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'], PARAM_DB_MAP[kpi['context'].iloc[0]]['key'] ]].drop_duplicates() df_with_den_context = dataframe_to_process.query(query_string)[[ PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'], PARAM_DB_MAP[kpi['context'].iloc[0]]['key'] ]].drop_duplicates() denominators_df_to_save_zero = scif_with_den_context[( ~scif_with_den_context[PARAM_DB_MAP[ kpi['denominator'].iloc[0]]['key']].isin( df_with_den_context[PARAM_DB_MAP[ kpi['denominator'].iloc[0]]['key']]))] identifier_parent = None numerator_fk = self.own_man_fk result = numerator_result = 0 # SAVE ALL RESULTS AS ZERO denominators_df_to_save_zero.dropna(inplace=True) denominators_df_to_save_zero = denominators_df_to_save_zero.astype( 'int64') for idx, each_row in denominators_df_to_save_zero.iterrows(): # get parent details if not is_nan(kpi[KPI_PARENT_COL].iloc[0]): param_id_map = dict(each_row.fillna('0')) kpi_parent = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == kpi[KPI_PARENT_COL].iloc[0]) & (self.kpi_static_data['delete_time'].isnull())] kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET) kpi_parent_detail = kpi_details[ kpi_details[KPI_NAME_COL] == kpi_parent[KPI_TYPE_COL].values[0]] parent_denominator_id = get_parameter_id( key_value=PARAM_DB_MAP[ kpi_parent_detail['denominator'].iloc[0]]['key'], param_id_map=param_id_map) if parent_denominator_id is None: parent_denominator_id = self.store_id parent_context_id = get_parameter_id( key_value=PARAM_DB_MAP[ kpi_parent_detail['context'].iloc[0]]['key'], param_id_map=param_id_map) if parent_context_id is None: parent_context_id = self.store_id identifier_parent = "{}_{}_{}_{}".format( kpi_parent_detail['kpi_name'].iloc[0], kpi_parent['pk'].iloc[0], # parent_numerator_id, int(parent_denominator_id), int(parent_context_id)) context_id = each_row[PARAM_DB_MAP[kpi['context'].iloc[0]] ['key']] # query out empty product IDs since FSOS is not interested in them. each_den_fk = each_row[PARAM_DB_MAP[kpi['denominator'].iloc[0]] ['key']] _query = "{key}=='{value_id}' and product_fk not in {exc_prod_ids}".format( key=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'], value_id=each_den_fk, exc_prod_ids=self.empty_prod_ids.tolist() + self.irrelevant_prod_ids.tolist()) # find number of products in that context denominator_result = len(dataframe_to_process.query(_query)) if not denominator_result: continue self.common.write_to_db_result( fk=kpi['pk'].iloc[0], numerator_id=numerator_fk, denominator_id=each_den_fk, context_id=context_id, result=result, numerator_result=numerator_result, denominator_result=denominator_result, identifier_result="{}_{}_{}_{}".format( kpi['kpi_name'].iloc[0], kpi['pk'].iloc[0], # numerator_id, each_den_fk, context_id), identifier_parent=identifier_parent, should_enter=True, ) for group_id_tup, group_data in grouped_data_frame: if type(group_id_tup) not in [tuple, list]: # convert to a tuple group_id_tup = group_id_tup, param_id_map = dict(zip(groupers, group_id_tup)) numerator_id = param_id_map.get( PARAM_DB_MAP[kpi['numerator'].iloc[0]]['key']) denominator_id = get_parameter_id( key_value=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'], param_id_map=param_id_map) if denominator_id is None: denominator_id = self.store_id context_id = get_parameter_id( key_value=PARAM_DB_MAP[kpi['context'].iloc[0]]['key'], param_id_map=param_id_map) if context_id is None: context_id = self.store_id if PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'] == 'store_fk': denominator_df = dataframe_to_process else: denominator_df = dataframe_to_process.query( '{key} == {value}'.format( key=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'], value=denominator_id)) if not len(denominator_df): Log.info( "No denominator data for session {sess} to calculate {name}" .format(sess=self.session_uid, name=kpi.kpi_name.iloc[0])) continue result = len(group_data) / float(len(denominator_df)) if not is_nan(kpi[KPI_PARENT_COL].iloc[0]): kpi_parent = self.kpi_static_data[ (self.kpi_static_data[KPI_TYPE_COL] == kpi[KPI_PARENT_COL].iloc[0]) & (self.kpi_static_data['delete_time'].isnull())] kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET) kpi_parent_detail = kpi_details[ kpi_details[KPI_NAME_COL] == kpi_parent[KPI_TYPE_COL].values[0]] parent_denominator_id = get_parameter_id( key_value=PARAM_DB_MAP[ kpi_parent_detail['denominator'].iloc[0]]['key'], param_id_map=param_id_map) if parent_denominator_id is None: parent_denominator_id = self.store_id parent_context_id = get_parameter_id(key_value=PARAM_DB_MAP[ kpi_parent_detail['context'].iloc[0]]['key'], param_id_map=param_id_map) if parent_context_id is None: parent_context_id = self.store_id self.common.write_to_db_result( fk=kpi['pk'].iloc[0], numerator_id=numerator_id, denominator_id=denominator_id, context_id=context_id, result=result, numerator_result=len(group_data), denominator_result=len(denominator_df), identifier_result="{}_{}_{}_{}".format( kpi['kpi_name'].iloc[0], kpi['pk'].iloc[0], # numerator_id, denominator_id, context_id), identifier_parent="{}_{}_{}_{}".format( kpi_parent_detail['kpi_name'].iloc[0], kpi_parent['pk'].iloc[0], # parent_numerator_id, parent_denominator_id, parent_context_id), should_enter=True, ) else: # its the parent. Save the identifier result. self.common.write_to_db_result( fk=kpi['pk'].iloc[0], numerator_id=numerator_id, denominator_id=denominator_id, context_id=context_id, result=result, numerator_result=len(group_data), denominator_result=len(denominator_df), identifier_result="{}_{}_{}_{}".format( kpi['kpi_name'].iloc[0], kpi['pk'].iloc[0], # numerator_id, denominator_id, context_id), should_enter=True, ) return True def calculate_count(self, kpi, groupers, query_string, dataframe_to_process): if query_string: grouped_data_frame = dataframe_to_process.query( query_string).groupby(groupers) else: grouped_data_frame = dataframe_to_process.groupby(groupers) for group_id_tup, group_data in grouped_data_frame: param_id_map = dict(zip(groupers, group_id_tup)) numerator_id = param_id_map.get( PARAM_DB_MAP[kpi['numerator'].iloc[0]]['key']) denominator_id = param_id_map.get( PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key']) context_id = get_parameter_id( key_value=PARAM_DB_MAP[kpi['context'].iloc[0]]['key'], param_id_map=param_id_map) if context_id is None: context_id = self.store_id result = len(group_data) self.common.write_to_db_result( fk=kpi['pk'].iloc[0], numerator_id=numerator_id, denominator_id=denominator_id, context_id=context_id, result=result, ) return True def get_sanitized_match_prod_scene(self, include_exclude_data_dict): scene_product_data = self.match_product_in_scene.merge( self.products, how='left', on=['product_fk'], suffixes=('', '_prod')) sanitized_products_in_scene = scene_product_data.merge( self.scene_template_info, how='left', on='scene_fk', suffixes=('', '_scene')) # flags include_empty = include_exclude_data_dict.get('empty') include_irrelevant = include_exclude_data_dict.get('irrelevant') include_others = include_exclude_data_dict.get('others') include_stacking = include_exclude_data_dict.get('stacking') # list scene_types_to_exclude = include_exclude_data_dict.get( 'scene_types_to_exclude', False) categories_to_exclude = include_exclude_data_dict.get( 'categories_to_exclude', False) brands_to_exclude = include_exclude_data_dict.get( 'brands_to_exclude', False) ean_codes_to_exclude = include_exclude_data_dict.get( 'ean_codes_to_exclude', False) # Start removing items if scene_types_to_exclude: # list of scene types to include is present, otherwise all included Log.info("Exclude template/scene type {}".format( scene_types_to_exclude)) sanitized_products_in_scene.drop(sanitized_products_in_scene[ sanitized_products_in_scene['template_name'].str.upper().isin([ x.upper() if type(x) in [unicode, str] else x for x in scene_types_to_exclude ])].index, inplace=True) if not include_stacking: # exclude stacking if the flag is set Log.info( "Exclude stacking other than in layer 1 or negative stacking [menu]" ) sanitized_products_in_scene = sanitized_products_in_scene.loc[ sanitized_products_in_scene['stacking_layer'] <= 1] if categories_to_exclude: # list of categories to exclude is present, otherwise all included Log.info("Exclude categories {}".format(categories_to_exclude)) sanitized_products_in_scene.drop(sanitized_products_in_scene[ sanitized_products_in_scene['category'].str.upper().isin([ x.upper() if type(x) in [unicode, str] else x for x in categories_to_exclude ])].index, inplace=True) if brands_to_exclude: # list of brands to exclude is present, otherwise all included Log.info("Exclude brands {}".format(brands_to_exclude)) sanitized_products_in_scene.drop(sanitized_products_in_scene[ sanitized_products_in_scene['brand_name'].str.upper().isin([ x.upper() if type(x) in [unicode, str] else x for x in brands_to_exclude ])].index, inplace=True) if ean_codes_to_exclude: # list of ean_codes to exclude is present, otherwise all included Log.info("Exclude ean codes {}".format(ean_codes_to_exclude)) sanitized_products_in_scene.drop( sanitized_products_in_scene[sanitized_products_in_scene[ 'product_ean_code'].str.upper().isin([ x.upper() if type(x) in [unicode, str] else x for x in ean_codes_to_exclude ])].index, inplace=True) product_ids_to_exclude = [] if not include_irrelevant: # add product ids to exclude with irrelevant product_ids_to_exclude.extend(self.irrelevant_prod_ids) if not include_others: # add product ids to exclude with others product_ids_to_exclude.extend(self.other_prod_ids) if not include_empty: # add product ids to exclude with empty product_ids_to_exclude.extend(self.empty_prod_ids) if product_ids_to_exclude: Log.info("Exclude product ids {}".format(product_ids_to_exclude)) sanitized_products_in_scene.drop(sanitized_products_in_scene[ sanitized_products_in_scene['product_fk'].isin( product_ids_to_exclude)].index, inplace=True) return sanitized_products_in_scene
class MOLSONCOORSHR_SANDToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.current_date = datetime.now() self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.own_manufacturer_id = int(self.data_provider[Data.OWN_MANUFACTURER][self.data_provider[Data.OWN_MANUFACTURER]['param_name'] == 'manufacturer_id']['param_value'].tolist()[0]) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.toolbox = GENERALToolBox(data_provider) self.assortment = Assortment(self.data_provider, self.output, common=self.common) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template_path = self.get_template_path() self.template_data = self.get_template_data() self.sos_store_policies = self.get_sos_store_policies(self.visit_date.strftime('%Y-%m-%d')) self.result_values = self.get_result_values() self.scores = pd.DataFrame() def get_sos_store_policies(self, visit_date): query = MOLSONCOORSHR_SANDQueries.get_sos_store_policies(visit_date) store_policies = pd.read_sql_query(query, self.rds_conn.db) return store_policies def get_result_values(self): query = MOLSONCOORSHR_SANDQueries.get_result_values() result_values = pd.read_sql_query(query, self.rds_conn.db) return result_values def main_calculation(self): """ This function starts the KPI results calculation. """ if not self.template_data or KPIS_TEMPLATE_SHEET not in self.template_data: Log.error('KPIs template sheet is empty or not found') return self.kpis_calculation() self.common.commit_results_data() def kpis_calculation(self, kpi_group=''): """ This is a recursive function. The function calculates each level KPI cascading from the highest level to the lowest. """ total_score = total_potential_score = total_calculated = 0 kpis = self.template_data['KPIs'][self.template_data['KPIs']['KPI Group'] == kpi_group] for index, kpi in kpis.iterrows(): child_kpi_group = kpi['Child KPI Group'] kpi_type = kpi['KPI Type'].lower() score_function = kpi['Score Function'].lower() if not child_kpi_group: if kpi_type in [LINEAR_SOS_VS_TARGET, FACINGS_SOS_VS_TARGET]: score, potential_score, calculated = self.calculate_sos_vs_target(kpi) elif kpi_type in [FACINGS_VS_TARGET, DISTRIBUTION]: score, potential_score, calculated = self.calculate_assortment_vs_target(kpi) else: Log.error("KPI of type '{}' is not supported".format(kpi_type)) score = potential_score = calculated = 0 else: score, potential_score, calculated = self.kpis_calculation(child_kpi_group) if score_function in [WEIGHTED_SCORE, SUM_OF_SCORES]: total_score += score total_potential_score += potential_score total_calculated += calculated else: total_score += 0 total_potential_score += 0 total_calculated += 0 if child_kpi_group and calculated: if kpi['KPI name Eng'] == 'Store Score': kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0 numerator_id = self.own_manufacturer_id denominator_id = self.store_id identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=0, denominator_id=denominator_id, denominator_result=0, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True ) else: kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0 numerator_id = self.own_manufacturer_id denominator_id = self.store_id identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=0, denominator_id=denominator_id, denominator_result=0, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True ) return total_score, total_potential_score, total_calculated @kpi_runtime() def calculate_assortment_vs_target(self, kpi): """ The function filters only the relevant scenes by Location Type and calculates the Assortment scores according to rules set in the target. :return: """ lvl3_result = self.calculate_assortment_vs_target_lvl3(kpi) for row in lvl3_result.itertuples(): numerator_id = row.product_fk numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings denominator_id = self.store_id denominator_result = row.target # denominator_result_after_actions = 0 if row.target < row.facings else row.target - row.facings if kpi['KPI Type'] == 'Distribution': if row.result_distributed: result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') & (self.result_values['result_value'] == 'DISTRIBUTED')]['result_value_fk'].tolist()[0] score = 100 else: result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') & (self.result_values['result_value'] == 'OOS')]['result_value_fk'].tolist()[0] score = 0 else: result = row.result_facings score = round(result*100, 0) identifier_details = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl3) identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2) self.common.write_to_db_result(fk=row.kpi_fk_lvl3, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, # denominator_result_after_actions=denominator_result_after_actions, result=result, score=score, identifier_result=identifier_details, identifier_parent=identifier_kpi, should_enter=True ) score = potential_score = 0 if not lvl3_result.empty: lvl2_result = self.calculate_assortment_vs_target_lvl2(lvl3_result) for row in lvl2_result.itertuples(): numerator_id = self.own_manufacturer_id numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings denominator_id = self.store_id denominator_result = row.target result = row.result_distributed if kpi['KPI Type'] == 'Distribution' else row.result_facings score += self.score_function(result*100, kpi) potential_score += round(float(kpi['Weight'])*100, 0) identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2) identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group'])) self.common.write_to_db_result(fk=row.kpi_fk_lvl2, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_kpi, identifier_parent=identifier_parent, should_enter=True ) if len(lvl3_result) > 0: calculated = 1 else: calculated = 0 return score, potential_score, calculated def calculate_assortment_vs_target_lvl3(self, kpi): location_types = kpi['Location Type'].split(', ') kpi_fk_lvl3 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'] + ' - SKU') kpi_fk_lvl2 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) assortment_result = self.assortment.get_lvl3_relevant_ass() if assortment_result.empty: return assortment_result assortment_result = assortment_result[(assortment_result['kpi_fk_lvl3'] == kpi_fk_lvl3) & (assortment_result['kpi_fk_lvl2'] == kpi_fk_lvl2)] if assortment_result.empty: return assortment_result assortment_result['target'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Target'), axis=1) assortment_result['target'] = assortment_result['target'].fillna(0) assortment_result = assortment_result[assortment_result['target'] > 0] assortment_result['weight'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Weight'), axis=1) assortment_result['weight'] = assortment_result['weight'].fillna(0) assortment_total_weights = assortment_result[['assortment_fk', 'weight']].groupby('assortment_fk').agg({'weight': 'sum'}).reset_index() assortment_result = assortment_result.merge(assortment_total_weights, how='left', left_on='assortment_fk', right_on='assortment_fk', suffixes=['', '_total']) facings = 'facings_ign_stack' if kpi['Ignore Stacking'] else 'facings' products_in_session = self.scif[(self.scif[facings] > 0) & (self.scif['location_type'].isin(location_types))][['product_fk', 'facings']]\ .groupby('product_fk').agg({'facings': 'sum'}).reset_index() lvl3_result = assortment_result.merge(products_in_session, how='left', left_on='product_fk', right_on='product_fk') lvl3_result['facings'] = lvl3_result['facings'].fillna(0) lvl3_result['distributed'] = lvl3_result.apply(lambda x: 1 if x['facings'] else 0, axis=1) lvl3_result['result_facings'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'facings'), axis=1) lvl3_result['result_distributed'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'distributed'), axis=1) return lvl3_result def calculate_assortment_vs_target_lvl2(self, lvl3_result): lvl2_result = lvl3_result.groupby(['kpi_fk_lvl2', self.assortment.ASSORTMENT_FK, self.assortment.ASSORTMENT_GROUP_FK])\ .agg({'facings': 'sum', 'distributed': 'sum', 'target': 'sum', 'result_facings': 'sum', 'result_distributed': 'sum'}).reset_index() return lvl2_result @staticmethod def assortment_vs_target_result(x, y): if x[y] < x['target']: return round(x[y] / float(x['target']) * float(x['weight']) / x['weight_total'], 5) else: return round(1 * float(x['weight']) / x['weight_total'], 5) @kpi_runtime() def calculate_sos_vs_target(self, kpi): """ The function filters only the relevant scenes by Location Type and calculates the linear SOS and the facing SOS according to Manufacturer and Category set in the target. :return: """ location_type = kpi['Location Type'] kpi_fk = self.common.get_kpi_fk_by_kpi_type(SOS_MANUFACTURER_CATEGORY + ('_' + location_type if location_type else '')) sos_store_policies = self.sos_store_policies[self.sos_store_policies['kpi_fk'] == str(kpi_fk)] sos_store_policy = None store_policy_passed = 0 for index, policy in sos_store_policies.iterrows(): sos_store_policy = policy store_policy = json.loads(policy['store_policy']) store_policy_passed = 1 for key in store_policy.keys(): if key in self.store_info.columns.tolist(): if self.store_info[key][0] in store_policy[key]: continue else: store_policy_passed = 0 break else: Log.error("Store Policy attribute is not found: '{}'").format(key) store_policy_passed = 0 break if store_policy_passed: break score = potential_score = 0 if store_policy_passed: general_filters = {LOCATION_TYPE: location_type} sos_policy = json.loads(sos_store_policy['sos_policy']) numerator_sos_filters = {MANUFACTURER_NAME: sos_policy[NUMERATOR][MANUFACTURER], CATEGORY: sos_policy[DENOMINATOR][CATEGORY]} denominator_sos_filters = {CATEGORY: sos_policy[DENOMINATOR][CATEGORY]} numerator_id = self.all_products.loc[self.all_products[MANUFACTURER_NAME] == sos_policy[NUMERATOR][MANUFACTURER]][MANUFACTURER + '_fk'].values[0] denominator_id = self.all_products.loc[self.all_products[CATEGORY] == sos_policy[DENOMINATOR][CATEGORY]][CATEGORY + '_fk'].values[0] ignore_stacking = kpi['Ignore Stacking'] if kpi['Ignore Stacking'] else 0 numer_facings, numer_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(numerator_sos_filters, **general_filters)) denom_facings, denom_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(denominator_sos_filters, **general_filters)) if kpi['KPI Type'].lower() == LINEAR_SOS_VS_TARGET: numerator_result = round(numer_linear, 0) denominator_result = round(denom_linear, 0) result = numer_linear / float(denom_linear) if denom_linear else 0 elif kpi['KPI Type'].lower() == FACINGS_SOS_VS_TARGET: numerator_result = numer_facings denominator_result = denom_facings result = numer_facings / float(denom_facings) if denom_facings else 0 else: Log.error("KPI Type is invalid: '{}'").format(kpi['KPI Type']) numerator_result = denominator_result = result = 0 if sos_store_policy['target']: sos_target = round(float(sos_store_policy['target'])*100, 0) else: Log.error("SOS target is not set for Store ID {}").format(self.store_id) sos_target = 0 result_vs_target = result/(float(sos_target)/100)*100 if sos_target else 0 score = self.score_function(result_vs_target, kpi) potential_score = round(float(kpi['Weight'])*100, 0) identifier_kpi = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group'])) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, result=result, score=score, weight=potential_score, target=sos_target, identifier_result=identifier_kpi, identifier_parent=identifier_parent, should_enter=True ) else: Log.warning("Store Policy is not found for Store ID {}".format(self.store_id)) return score, potential_score, store_policy_passed def calculate_share_space(self, ignore_stacking=1, **filters): """ :param filters: These are the parameters which the data frame is filtered by. :param ignore_stacking: 1 is to ignore stacking. :return: The total number of facings and the shelf width (in mm) according to the filters. """ filtered_scif = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)] if ignore_stacking: sum_of_facings = filtered_scif['facings_ign_stack'].sum() space_length = filtered_scif['net_len_ign_stack'].sum() else: sum_of_facings = filtered_scif['facings'].sum() space_length = filtered_scif['net_len_ign_stack'].sum() return sum_of_facings, space_length @staticmethod def get_template_path(): return os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data', KPIS_TEMPLATE_NAME) def get_template_data(self): template_data = {} try: sheet_names = pd.ExcelFile(self.template_path).sheet_names for sheet in sheet_names: template_data[sheet] = parse_template(self.template_path, sheet, lower_headers_row_index=0) except IOError as e: Log.error('Template {} does not exist. {}'.format(KPIS_TEMPLATE_NAME, repr(e))) return template_data @staticmethod def score_function(score, kpi): weight = float(kpi['Weight']) if kpi['Weight'] else 1 score_function = kpi['Score Function'].lower() l_threshold = float(kpi['Lower Threshold'])*100 if kpi['Lower Threshold'] else 0 h_threshold = float(kpi['Higher Threshold'])*100 if kpi['Higher Threshold'] else 100 if score < l_threshold: score = 0 elif score >= h_threshold: score = 100 if score_function in [WEIGHTED_SCORE]: score = round(score*weight, 0) else: score = round(score, 0) return score
class PSAPAC_SAND3ToolBox: # Gsk Japan kpis # DEFAULT_TARGET = {ProductsConsts.BRAND_FK: [-1], 'shelves': ["1,2,3"], 'block_target': [80], 'brand_target': [100], 'position_target': [80]} def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.set_up_template = pd.read_excel(os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', 'gsk_set_up.xlsx'), sheet_name='Functional KPIs', keep_default_na=False) self.gsk_generator = GSKGenerator(self.data_provider, self.output, self.common, self.set_up_template) self.blocking_generator = Block(self.data_provider) self.assortment = self.gsk_generator.get_assortment_data_provider() self.store_info = self.data_provider['store_info'] self.store_fk = self.data_provider[StoreInfoConsts.STORE_FK] self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.targets = self.ps_data_provider.get_kpi_external_targets( key_fields=Consts.KEY_FIELDS, data_fields=Consts.DATA_FIELDS) self.own_manufacturer = self.get_manufacturer self.set_up_data = { (Consts.PLN_BLOCK, Const.KPI_TYPE_COLUMN): Const.NO_INFO, (Consts.POSITION_SCORE, Const.KPI_TYPE_COLUMN): Const.NO_INFO, (Consts.ECAPS_FILTER_IDENT, Const.KPI_TYPE_COLUMN): Const.NO_INFO, (Consts.PLN_MSL, Const.KPI_TYPE_COLUMN): Const.NO_INFO, ("GSK_PLN_LSOS_SCORE", Const.KPI_TYPE_COLUMN): Const.NO_INFO, (Consts.POSM, Const.KPI_TYPE_COLUMN): Const.NO_INFO } @property def get_manufacturer(self): return int(self.data_provider.own_manufacturer[ self.data_provider.own_manufacturer['param_name'] == 'manufacturer_id']['param_value'].iloc[0]) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results.Global functions and local functions """ # global kpis assortment_store_dict = self.gsk_generator.availability_store_function( ) self.common.save_json_to_new_tables(assortment_store_dict) assortment_category_dict = self.gsk_generator.availability_category_function( ) self.common.save_json_to_new_tables(assortment_category_dict) assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function( ) self.common.save_json_to_new_tables(assortment_subcategory_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function( ) self.common.save_json_to_new_tables(linear_sos_dict) # # local kpis for kpi in Consts.KPI_DICT.keys(): self.gsk_generator.tool_box.extract_data_set_up_file( kpi, self.set_up_data, Consts.KPI_DICT) results_ecaps = self.gsk_ecaps_kpis() self.common.save_json_to_new_tables(results_ecaps) self.get_store_target() # choosing the policy if self.targets.empty: Log.warning('There is no target policy matching this store') else: results_compliance = self.gsk_compliance() self.common.save_json_to_new_tables(results_compliance) results_pos = self.gsk_pos_kpis() self.common.save_json_to_new_tables(results_pos) self.common.commit_results_data() return def position_shelf(self, brand_fk, policy, df): """ :param brand_fk : :param policy : dictionary that contains { 'shelves':"1 ,2 ,4 ,5" (or any other string of numbers separate by ','), 'position_target': 80 (or any other percentage you want the score to reach) } :param df: data frame that contains columns MatchesConsts.SHELF_NUMBER , "brand kf" :returns tuple of (result,score,numerator,denominator) result = number of products from brand_fk in shelves / number of products from brand_fk , score = if result reach position target 100 else 0 , numerator = number of products from brand_fk in shelves denominator = number of products from brand_fk """ if (Consts.SHELVES not in policy.keys()) or policy[Consts.SHELVES].empty: Log.warning( 'This sessions have external targets but doesnt have value for shelves position' ) return 0, 0, 0, 0, 0 if isinstance(policy[Consts.SHELVES].iloc[0], list): shelf_from_bottom = [ int(shelf) for shelf in policy[Consts.SHELVES].iloc[0] ] else: shelf_from_bottom = [ int(shelf) for shelf in policy[Consts.SHELVES].iloc[0].split(",") ] threshold = policy[Consts.POSITION_TARGET].iloc[0] brand_df = df[df[ProductsConsts.BRAND_FK] == brand_fk] shelf_df = brand_df[brand_df[MatchesConsts.SHELF_NUMBER].isin( shelf_from_bottom)] numerator = shelf_df.shape[0] denominator = brand_df.shape[0] result = float(numerator) / float(denominator) score = 1 if (result * 100) >= threshold else 0 return result, score, numerator, denominator, threshold def lsos_score(self, brand, policy): """ :param brand : pk of brand :param policy : dictionary of { 'brand_target' : lsos number you want to reach} This function uses the lsos_in whole_store global calculation. it takes the result of the parameter 'brand' according to the policy set target and results. :return result,score,target result : result of this brand lsos score : result / brand_target , target : branf_target """ df = pd.merge(self.match_product_in_scene, self.all_products[Const.PRODUCTS_COLUMNS], how='left', on=[MatchesConsts.PRODUCT_FK]) df = pd.merge(self.scif[Const.SCIF_COLUMNS], df, how='right', right_on=[ScifConsts.SCENE_FK, ScifConsts.PRODUCT_FK], left_on=[ScifConsts.SCENE_ID, ScifConsts.PRODUCT_FK]) if df.empty: Log.warning('match_product_in_scene is empty ') return 0, 0, 0 df = self.gsk_generator.tool_box.tests_by_template( 'GSK_PLN_LSOS_SCORE', df, self.set_up_data) if df is None: Log.warning('match_product_in_scene is empty ') return 0, 0, 0 result = self.gsk_generator.tool_box.calculate_sos( df, {ProductsConsts.BRAND_FK: brand}, {}, Const.LINEAR)[0] target = policy['brand_target'].iloc[0] score = float(result) / float(target) return result, score, target def brand_blocking(self, brand, policy): """ :param brand : pk of brand :param policy : dictionary of { 'block_target' : number you want to reach} :return result : 1 if there is a block answer set_up_data conditions else 0 """ templates = self.set_up_data[(Const.SCENE_TYPE, Consts.PLN_BLOCK)] template_name = { ScifConsts.TEMPLATE_NAME: templates } if templates else None # figure out which template name should I use ignore_empty = False # taking from params from set up info stacking_param = False if not self.set_up_data[( Const.INCLUDE_STACKING, Consts.PLN_BLOCK)] else True # false population_parameters = { ProductsConsts.BRAND_FK: [brand], ProductsConsts.PRODUCT_TYPE: [ProductTypeConsts.SKU] } if self.set_up_data[(Const.INCLUDE_OTHERS, Consts.PLN_BLOCK)]: population_parameters[ProductsConsts.PRODUCT_TYPE].append( Const.OTHER) if self.set_up_data[(Const.INCLUDE_IRRELEVANT, Consts.PLN_BLOCK)]: population_parameters[ProductsConsts.PRODUCT_TYPE].append( Const.IRRELEVANT) if self.set_up_data[(Const.INCLUDE_EMPTY, Consts.PLN_BLOCK)]: population_parameters[ProductsConsts.PRODUCT_TYPE].append( Const.EMPTY) else: ignore_empty = True if self.set_up_data[(Const.CATEGORY_INCLUDE, Consts.PLN_BLOCK)]: # category_name population_parameters[ProductsConsts.CATEGORY] = self.set_up_data[( Const.CATEGORY_INCLUDE, Consts.PLN_BLOCK)] if self.set_up_data[(Const.SUB_CATEGORY_INCLUDE, Consts.PLN_BLOCK)]: # sub_category_name population_parameters[ ProductsConsts.SUB_CATEGORY] = self.set_up_data[( Const.SUB_CATEGORY_INCLUDE, Consts.PLN_BLOCK)] # from Data file target = float(policy['block_target'].iloc[0]) / float(100) result = self.blocking_generator.network_x_block_together( location=template_name, population=population_parameters, additional={ 'minimum_block_ratio': target, 'calculate_all_scenes': True, 'ignore_empty': ignore_empty, 'include_stacking': stacking_param, 'check_vertical_horizontal': True, 'minimum_facing_for_block': 1 }) result.sort_values('facing_percentage', ascending=False, inplace=True) score = 0 if result[result['is_block']].empty else 1 numerator = 0 if result.empty else result['block_facings'].iloc[0] denominator = 0 if result.empty else result['total_facings'].iloc[0] return score, target, numerator, denominator def msl_assortment(self, kpi_fk, kpi_name): """ :param kpi_fk : name of level 3 assortment kpi :param kpi_name: GSK_PLN_MSL_SCORE assortment , or GSK_ECAPS assortment :return kpi_results : data frame of assortment products of the kpi, product's availability, product details. filtered by set up """ lvl3_assort, filter_scif = self.gsk_generator.tool_box.get_assortment_filtered( self.set_up_data, kpi_name) if lvl3_assort is None or lvl3_assort.empty: return None kpi_assortment_fk = self.common.get_kpi_fk_by_kpi_type(kpi_fk) kpi_results = lvl3_assort[lvl3_assort['kpi_fk_lvl3'] == kpi_assortment_fk] # general assortment kpi_results = pd.merge(kpi_results, self.all_products[Const.PRODUCTS_COLUMNS], how='left', on=ProductsConsts.PRODUCT_FK) kpi_results = kpi_results[kpi_results[ ProductsConsts.SUBSTITUTION_PRODUCT_FK].isnull()] return kpi_results def pln_ecaps_score(self, brand, assortment): """ :param brand : pk of desired brand :param assortment : data frame of assortment products of the kpi, product's availability, product details. filtered by set up besides result of lvl2_assortment function writing level 3 assortment product presence results :return numerator : how many products available out of the granular groups denominator : how many products in assortment groups result : (numerator/denominator)*100 results : array of dictionary, each dict contains the result details """ identifier_parent = self.common.get_dictionary( brand_fk=brand, kpi_fk=self.common.get_kpi_fk_by_kpi_type(Consts.ECAP_ALL_BRAND)) results = [] kpi_ecaps_product = self.common.get_kpi_fk_by_kpi_type( Consts.PRODUCT_PRESENCE) ecaps_assortment_fk = self.common.get_kpi_fk_by_kpi_type( Consts.PLN_ASSORTMENT_KPI) if assortment.empty: return 0, 0, 0, results brand_results = assortment[assortment[ProductsConsts.BRAND_FK] == brand] # only assortment of desired brand for result in brand_results.itertuples(): if (math.isnan(result.in_store)) | (result.kpi_fk_lvl3 != ecaps_assortment_fk): score = self.gsk_generator.tool_box.result_value_pk( Const.EXTRA) result_num = 1 else: score = self.gsk_generator.tool_box.result_value_pk(Const.OOS) if result.in_store == 0 else \ self.gsk_generator.tool_box.result_value_pk(Const.DISTRIBUTED) result_num = result.in_store last_status = self.gsk_generator.tool_box.get_last_status( kpi_ecaps_product, result.product_fk) # score = result.in_store * 100 results.append({ 'fk': kpi_ecaps_product, SessionResultsConsts.NUMERATOR_ID: result.product_fk, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: 1, SessionResultsConsts.NUMERATOR_RESULT: result_num, SessionResultsConsts.RESULT: score, SessionResultsConsts.SCORE: last_status, 'identifier_parent': identifier_parent, 'identifier_result': 1, 'should_enter': True }) if 'total' not in self.assortment.LVL2_HEADERS or 'passes' not in self.assortment.LVL2_HEADERS: self.assortment.LVL2_HEADERS.extend(['total', 'passes']) lvl2 = self.assortment.calculate_lvl2_assortment(brand_results) if lvl2.empty: return 0, 0, 0, results # in case of no assortment return 0 result = round( np.divide(float(lvl2.iloc[0].passes), float(lvl2.iloc[0].total)), 4) return lvl2.iloc[0].passes, lvl2.iloc[0].total, result, results def pln_msl_summary(self, brand, assortment): """ :param brand : pk of desired brand :param assortment : data frame of assortment products of the kpi, product's availability, product details. filtered by set up :return numerator : how many products available out of the granular groups denominator : how many products in assortment groups result : (numerator/denominator)*100 results : array of dictionary, each dict contains the result details """ if assortment is None or assortment.empty: return 0, 0, 0, 0 brand_results = assortment[assortment[ProductsConsts.BRAND_FK] == brand] # only assortment of desired brand if 'total' not in self.assortment.LVL2_HEADERS or 'passes' not in self.assortment.LVL2_HEADERS: self.assortment.LVL2_HEADERS.extend(['total', 'passes']) lvl2 = self.assortment.calculate_lvl2_assortment(brand_results) if lvl2.empty: return 0, 0, 0, 0 # in case of no assortment return 0 result = round( np.divide(float(lvl2.iloc[0].passes), float(lvl2.iloc[0].total)), 4) return lvl2.iloc[0].passes, lvl2.iloc[0].total, result, lvl2.iloc[ 0].assortment_group_fk def get_store_target(self): """ Function checks which policies out of self.target are relevant to this store visit according to store attributes. """ parameters_dict = {StoreInfoConsts.STORE_NUMBER_1: 'store_number'} for store_param, target_param in parameters_dict.items(): if target_param in self.targets.columns: if self.store_info[store_param][0] is None: if self.targets.empty or self.targets[ self.targets[target_param] != ''].empty: continue else: self.targets.drop(self.targets.index, inplace=True) self.targets = self.targets[ (self.targets[target_param] == self.store_info[store_param][0].encode(HelperConsts.UTF8)) | (self.targets[target_param] == '')] def gsk_compliance(self): """ Function calculate compliance score for each brand based on : position score, brand-assortment score, block score ,lsos score. Also calculate compliance summary score - average of brands compliance scores """ results_df = [] df = self.scif # kpis kpi_block_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_BLOCK) kpi_position_fk = self.common.get_kpi_fk_by_kpi_type( Consts.POSITION_SCORE) kpi_lsos_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_LSOS) kpi_msl_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_MSL) kpi_compliance_brands_fk = self.common.get_kpi_fk_by_kpi_type( Consts.COMPLIANCE_ALL_BRANDS) kpi_compliance_summary_fk = self.common.get_kpi_fk_by_kpi_type( Consts.COMPLIANCE_SUMMARY) identifier_compliance_summary = self.common.get_dictionary( kpi_fk=kpi_compliance_summary_fk) # targets block_target = 0.25 posit_target = 0.25 lsos_target = 0.25 msl_target = 0.25 total_brand_score = 0 counter_brands = 0 # assortment_lvl3 msl df initialize self.gsk_generator.tool_box.extract_data_set_up_file( Consts.PLN_MSL, self.set_up_data, Consts.KPI_DICT) assortment_msl = self.msl_assortment(Const.DISTRIBUTION, Consts.PLN_MSL) # set data frame to find position shelf df_position_score = pd.merge(self.match_product_in_scene, self.all_products, on=ProductsConsts.PRODUCT_FK) df_position_score = pd.merge( self.scif[Const.SCIF_COLUMNS], df_position_score, how='right', right_on=[ScifConsts.SCENE_FK, ProductsConsts.PRODUCT_FK], left_on=[ScifConsts.SCENE_ID, ScifConsts.PRODUCT_FK]) df_position_score = self.gsk_generator.tool_box.tests_by_template( Consts.POSITION_SCORE, df_position_score, self.set_up_data) if not self.set_up_data[(Const.INCLUDE_STACKING, Consts.POSITION_SCORE)]: df_position_score = df_position_score if df_position_score is None else df_position_score[ df_position_score[MatchesConsts.STACKING_LAYER] == 1] # calculate all brands if template doesnt require specific brand else only for specific brands template_brands = self.set_up_data[(Const.BRANDS_INCLUDE, Consts.PLN_BLOCK)] brands = df[df[ProductsConsts.BRAND_NAME].isin(template_brands)][ProductsConsts.BRAND_FK].unique() if \ template_brands else df[ProductsConsts.BRAND_FK].dropna().unique() for brand in brands: policy = self.targets[self.targets[ProductsConsts.BRAND_FK] == brand] if policy.empty: Log.warning('There is no target policy matching brand' ) # adding brand name return results_df identifier_parent = self.common.get_dictionary( brand_fk=brand, kpi_fk=kpi_compliance_brands_fk) # msl_kpi msl_numerator, msl_denominator, msl_result, msl_assortment_group = self.pln_msl_summary( brand, assortment_msl) msl_score = msl_result * msl_target results_df.append({ 'fk': kpi_msl_fk, SessionResultsConsts.NUMERATOR_ID: brand, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: msl_denominator, SessionResultsConsts.NUMERATOR_RESULT: msl_numerator, SessionResultsConsts.RESULT: msl_result, SessionResultsConsts.SCORE: msl_score, SessionResultsConsts.TARGET: msl_target, SessionResultsConsts.CONTEXT_ID: msl_assortment_group, 'identifier_parent': identifier_parent, 'should_enter': True }) # lsos kpi lsos_numerator, lsos_result, lsos_denominator = self.lsos_score( brand, policy) lsos_result = 1 if lsos_result > 1 else lsos_result lsos_score = lsos_result * lsos_target results_df.append({ 'fk': kpi_lsos_fk, SessionResultsConsts.NUMERATOR_ID: brand, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: lsos_denominator, SessionResultsConsts.NUMERATOR_RESULT: lsos_numerator, SessionResultsConsts.RESULT: lsos_result, SessionResultsConsts.SCORE: lsos_score, SessionResultsConsts.TARGET: lsos_target, 'identifier_parent': identifier_parent, SessionResultsConsts.WEIGHT: lsos_denominator, 'should_enter': True }) # block_score block_result, block_benchmark, numerator_block, block_denominator = self.brand_blocking( brand, policy) block_score = round(block_result * block_target, 4) results_df.append({ 'fk': kpi_block_fk, SessionResultsConsts.NUMERATOR_ID: brand, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: block_denominator, SessionResultsConsts.NUMERATOR_RESULT: numerator_block, SessionResultsConsts.RESULT: block_result, SessionResultsConsts.SCORE: block_score, SessionResultsConsts.TARGET: block_target, 'identifier_parent': identifier_parent, 'should_enter': True, SessionResultsConsts.WEIGHT: (block_benchmark * 100) }) # position score if df_position_score is not None: position_result, position_score, position_num, position_den, position_benchmark = self.position_shelf( brand, policy, df_position_score) else: position_result, position_score, position_num, position_den, position_benchmark = 0, 0, 0, 0, 0 position_score = round(position_score * posit_target, 4) results_df.append({ 'fk': kpi_position_fk, SessionResultsConsts.NUMERATOR_ID: brand, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: position_den, SessionResultsConsts.NUMERATOR_RESULT: position_num, SessionResultsConsts.RESULT: position_result, SessionResultsConsts.SCORE: position_score, SessionResultsConsts.TARGET: posit_target, 'identifier_parent': identifier_parent, 'should_enter': True, SessionResultsConsts.WEIGHT: position_benchmark }) # compliance score per brand compliance_score = round( position_score + block_score + lsos_score + msl_score, 4) results_df.append({ 'fk': kpi_compliance_brands_fk, SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer, SessionResultsConsts.DENOMINATOR_ID: brand, SessionResultsConsts.DENOMINATOR_RESULT: 1, SessionResultsConsts.NUMERATOR_RESULT: compliance_score, SessionResultsConsts.RESULT: compliance_score, SessionResultsConsts.SCORE: compliance_score, 'identifier_parent': identifier_compliance_summary, 'identifier_result': identifier_parent, 'should_enter': True }) # counter and sum updates total_brand_score = round(total_brand_score + compliance_score, 4) counter_brands = counter_brands + 1 if counter_brands == 0: return results_df # compliance summary average_brand_score = round(total_brand_score / counter_brands, 4) results_df.append({ 'fk': kpi_compliance_summary_fk, SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: counter_brands, SessionResultsConsts.NUMERATOR_RESULT: total_brand_score, SessionResultsConsts.RESULT: average_brand_score, SessionResultsConsts.SCORE: average_brand_score, 'identifier_result': identifier_compliance_summary }) return results_df def gsk_ecaps_kpis(self): """ Function calculate for each brand ecaps score, and for all brands together set ecaps summary score :return results_df : array of dictionary, each dict contains kpi's result details """ results_df = [] kpi_ecaps_brands_fk = self.common.get_kpi_fk_by_kpi_type( Consts.ECAP_ALL_BRAND) kpi_ecaps_summary_fk = self.common.get_kpi_fk_by_kpi_type( Consts.ECAP_SUMMARY) identifier_ecaps_summary = self.common.get_dictionary( kpi_fk=kpi_ecaps_summary_fk) total_brand_score = 0 assortment_display = self.msl_assortment(Consts.PLN_ASSORTMENT_KPI, Consts.ECAPS_FILTER_IDENT) if assortment_display is None or assortment_display.empty: return results_df template_brands = self.set_up_data[(Const.BRANDS_INCLUDE, Consts.ECAPS_FILTER_IDENT)] brands = assortment_display[assortment_display[ProductsConsts.BRAND_NAME].isin(template_brands)][ ProductsConsts.BRAND_FK].unique() if \ template_brands else assortment_display[ProductsConsts.BRAND_FK].dropna().unique() for brand in brands: numerator_res, denominator_res, result, product_presence_df = self.pln_ecaps_score( brand, assortment_display) results_df.extend(product_presence_df) identifier_all_brand = self.common.get_dictionary( brand_fk=brand, kpi_fk=self.common.get_kpi_fk_by_kpi_type( Consts.ECAP_ALL_BRAND)) results_df.append({ 'fk': kpi_ecaps_brands_fk, SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer, SessionResultsConsts.DENOMINATOR_ID: brand, SessionResultsConsts.DENOMINATOR_RESULT: denominator_res, SessionResultsConsts.NUMERATOR_RESULT: numerator_res, SessionResultsConsts.RESULT: result, SessionResultsConsts.SCORE: result, 'identifier_parent': identifier_ecaps_summary, 'identifier_result': identifier_all_brand, 'should_enter': True }) total_brand_score = total_brand_score + result if len( brands ) > 0: # don't want to show result in case of there are no brands relevan to the template result_summary = round(total_brand_score / len(brands), 4) results_df.append({ 'fk': kpi_ecaps_summary_fk, SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.DENOMINATOR_RESULT: len(brands), SessionResultsConsts.NUMERATOR_RESULT: total_brand_score, SessionResultsConsts.RESULT: result_summary, SessionResultsConsts.SCORE: result_summary, 'identifier_result': identifier_ecaps_summary }) return results_df def gsk_pos_kpis(self): """ Function calculate POSM Distribution :return - results : array of dictionary, each dict contains kpi's result details """ results = [] OOS = 1 DISTRIBUTED = 2 self.gsk_generator.tool_box.extract_data_set_up_file( Consts.POSM, self.set_up_data, Consts.KPI_DICT) assortment_pos = self.msl_assortment(Consts.POSM_SKU, Consts.POSM) kpi_gsk_pos_distribution_store_fk = self.common.get_kpi_fk_by_kpi_type( Consts.GSK_POS_DISTRIBUTION_STORE) kpi_gsk_pos_distribution_brand_fk = self.common.get_kpi_fk_by_kpi_type( Consts.GSK_POS_DISTRIBUTION_BRAND) kpi_gsk_pos_distribution_sku_fk = self.common.get_kpi_fk_by_kpi_type( Consts.GSK_POS_DISTRIBUTION_SKU) if assortment_pos is None or assortment_pos.empty: Log.info( "Assortment df is empty. GSK_POS_DISTRIBUTION Kpis are not calculated" ) return results # Calculate KPI : GSK_POS_DISTRIBUTION_STORE assortment_pos['in_store'] = assortment_pos['in_store'].astype('int') Log.info( "Dropping duplicate product_fks accros multiple-granular groups") Log.info("Before : {}".format(len(assortment_pos))) assortment_pos = assortment_pos.drop_duplicates( subset=[ProductsConsts.PRODUCT_FK]) Log.info("After : {}".format(len(assortment_pos))) numerator_res = len(assortment_pos[assortment_pos['in_store'] == 1]) denominator_res = len(assortment_pos) result = round( (numerator_res / float(denominator_res)), 4) if denominator_res != 0 else 0 results.append({ 'fk': kpi_gsk_pos_distribution_store_fk, SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.NUMERATOR_RESULT: numerator_res, SessionResultsConsts.DENOMINATOR_RESULT: denominator_res, SessionResultsConsts.RESULT: result, SessionResultsConsts.SCORE: result, # 'identifier_parent': identifier_ecaps_summary, 'identifier_result': "Gsk_Pos_Distribution_Store", 'should_enter': True }) # Calculate KPI: GSK_POS_DISTRIBUTION_BRAND brands_group = assortment_pos.groupby([ProductsConsts.BRAND_FK]) for brand, assortment_pos_by_brand in brands_group: numerator_res = len(assortment_pos_by_brand[ assortment_pos_by_brand['in_store'] == 1]) denominator_res = len(assortment_pos_by_brand) result = round( (numerator_res / float(denominator_res)), 4) if denominator_res != 0 else 0 results.append({ 'fk': kpi_gsk_pos_distribution_brand_fk, SessionResultsConsts.NUMERATOR_ID: int(brand), SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.NUMERATOR_RESULT: numerator_res, SessionResultsConsts.DENOMINATOR_RESULT: denominator_res, SessionResultsConsts.RESULT: result, SessionResultsConsts.SCORE: result, 'identifier_parent': "Gsk_Pos_Distribution_Store", 'identifier_result': "Gsk_Pos_Distribution_Brand_" + str(int(brand)), 'should_enter': True }) for idx, each_product in assortment_pos_by_brand.iterrows(): product_fk = each_product[ProductsConsts.PRODUCT_FK] result = 1 if int(each_product['in_store']) == 1 else 0 result_status = DISTRIBUTED if result == 1 else OOS last_status = self.gsk_generator.tool_box.get_last_status( kpi_gsk_pos_distribution_sku_fk, product_fk) results.append({ 'fk': kpi_gsk_pos_distribution_sku_fk, SessionResultsConsts.NUMERATOR_ID: product_fk, SessionResultsConsts.DENOMINATOR_ID: self.store_fk, SessionResultsConsts.NUMERATOR_RESULT: result, SessionResultsConsts.DENOMINATOR_RESULT: 1, SessionResultsConsts.RESULT: result_status, SessionResultsConsts.SCORE: last_status, 'identifier_parent': "Gsk_Pos_Distribution_Brand_" + str(int(brand)), 'identifier_result': "Gsk_Pos_Distribution_SKU_" + str(int(product_fk)), 'should_enter': True }) return results
class CCKRToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 PRODUCT_LVL = 'Main_Display_Sku' MAIN_DISPLAY = 'Main_Display_Items' def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] kpi_path = os.path.dirname(os.path.realpath(__file__)) base_file = os.path.basename(kpi_path) kpi_info = pd.read_excel(os.path.join(kpi_path[:-len(base_file)], 'Data', 'Template.xlsx'), sheet_name="KPI") file_template = pd.read_excel(os.path.join(kpi_path[:-len(base_file)], 'Data', 'Template.xlsx'), sheet_name="Template") self.kpi_template_info = pd.DataFrame( kpi_info) # contains the kpis + ean codes self.kpi_metadata = self.data_provider.kpi # information about kpis such as (presentation order) self.template_info = self.data_provider.templates # static data on templates self.kpi_template = pd.DataFrame( file_template) # relevant template for kpis # common for new tables self.commonV2 = CommonV2(self.data_provider) self.kpi_new_static_data = self.commonV2.get_new_kpi_static_data() self.manufacturer_fk = None if self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0] is None else \ int(self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0]) def main_calculation(self): self.kpi_calc() self.common.commit_results_data() def kpi_calc(self): sum_kpi = 0 relevant_template_fk = self.template_info.loc[ self.template_info.additional_attribute_1.isin( self.kpi_template.additional_attribute_1.unique())].template_fk self.scif = self.scif.loc[self.scif.template_fk.isin( relevant_template_fk.unique())] if self.kpi_template_info.empty: return for row in self.kpi_template_info.iterrows(): result = self.scif.loc[self.scif.product_ean_code == str( row[1]['SKU EAN Code'])] result = 0 if result.empty else int(result.facings.sum()) result_final = result if result > 0 else None score = 100 if result > 0 else None self.kpi_res(self.LEVEL3, score, row[1], result, result_final) score = 0 if score is None else score self.kpi_res(self.LEVEL2, score, row[1]) sum_kpi += score # score_1(level1) denominator = len(self.kpi_template_info) # score_2(level 2) self.kpi_res(self.LEVEL1, sum_kpi / 100, self.kpi_template_info.loc[0], denominator) # saving results to new tables self.commonV2.commit_results_data() def kpi_res(self, level, score, row, result=None, result_final=None): kpi_fks = self.kpi_static_data.loc[ self.kpi_static_data['atomic_kpi_name'].str.encode( 'utf8') == row['Atomic Kpi Name'].encode('utf8')] if kpi_fks.empty: print(row['Atomic Kpi Name']) Log.error( "differences between kpi template and kpi static table in DB") return kps_name = kpi_fks['kpi_set_name'].iloc[0] kpi_set_level_2_fk = self.commonV2.get_kpi_fk_by_kpi_type( self.MAIN_DISPLAY) kpi_identifier = self.commonV2.get_dictionary( kpi_fk=kpi_set_level_2_fk) if level == self.LEVEL1: self.common.write_to_db_result( fk=kpi_fks['kpi_set_fk'].iloc[0], session_uid=self.session_uid, store_fk=self.store_id, visit_date=self.visit_date.isoformat(), level=self.LEVEL1, kps_name=kps_name, kps_result=0, kpi_set_fk=kpi_fks['kpi_set_fk'].iloc[0], score=score, score_2=result, score_3=0) result = result if result is not None else 0 self.commonV2.write_to_db_result(fk=kpi_set_level_2_fk, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, numerator_result=score, denominator_result=result, identifier_result=kpi_identifier, result=score, score=score, should_enter=True) else: kpi_fk = kpi_fks['kpi_fk'].iloc[0] presentation_order = self.kpi_metadata[ self.kpi_metadata["pk"] == kpi_fk]['presentation_order'].iloc[0] if level == self.LEVEL2: self.common.write_to_db_result( fk=kpi_fk, session_uid=self.session_uid, store_fk=self.store_id, visit_date=self.visit_date.isoformat(), level=self.LEVEL2, kpk_name=kpi_fks['kpi_name'].iloc[0], kpi_fk=kpi_fk, score=score, presentation_order=presentation_order, score_2=100, score_3=0) else: # level 3 DB insertion self.common.write_to_db_result( level=self.LEVEL3, fk=kpi_fk, kpi_fk=kpi_fk, score=score, session_uid=self.session_uid, store_fk=self.store_id, visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=kps_name, missing_kpi_score="Bad", style=("good" if score == 100 else None), kpi_presentation_order=kpi_fk, atomic_kpi_fk=kpi_fks['atomic_kpi_fk'].iloc[0], display_text=row['Kpi Display Text (SKU Name)'], atomic_kpi_presentation_order=1, score_2=100, vs_1_facings=result, threshold=1, result=result_final, result_2=1, result_3=1) product_info = self.products.loc[self.products.product_ean_code == str(row['SKU EAN Code'])] if not product_info.empty: product_fk = product_info.product_fk.iloc[0] kpi_level_2_fk = self.commonV2.get_kpi_fk_by_kpi_type( self.PRODUCT_LVL) self.commonV2.write_to_db_result( fk=kpi_level_2_fk, numerator_id=product_fk, denominator_id=self.manufacturer_fk, numerator_result=result, denominator_result=1, identifier_parent=kpi_identifier, result=score, score=score, should_enter=True)
LoggerInitializer.init('ccbottlersus calculations') Config.init() project_name = 'cclibertyus' # MSC sessions = [ 'FC51FAC6-4EBB-4C9B-AC1B-F72052442DDE', 'E79B5B80-BAA2-4FA0-8C1F-594269B39457', 'E86F80DE-62C2-44AB-9949-80E520BCB3B2', 'F05079E5-11C4-4289-B5AE-5B8205594E15', 'dc322cc1-bfb7-4f2b-a6c3-c4c33a12b077' ] # Liberty sessions = [ 'fe6e86a5-e96c-4ed1-b285-689ee8da393c', 'FAB57A4E-4814-4B74-A521-53A003864D06', 'BE9F0199-17B6-4A11-BA97-97751FE6EE0E', 'f6c0247d-64b4-4d11-8e0b-f7616316c08f' ] for session in sessions: print('***********************************************************************************') print('_______________________ {} ____________________'.format(session)) data_provider = KEngineDataProvider(project_name) data_provider.load_session_data(session) output = Output() common = Common(data_provider) MSCToolBox(data_provider, output, common).main_calculation() common.commit_results_data()