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 __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.templates = self.data_provider[Data.TEMPLATES] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template = self.data_provider.all_templates # templates self.kpi_static_data = self.common.get_new_kpi_static_data() self.toolbox = GENERALToolBox(data_provider) kpi_path = os.path.dirname(os.path.realpath(__file__)) base_file = os.path.basename(kpi_path) self.exclude_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Exclude") self.Include_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Include") self.bay_count_kpi = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="BayCountKPI")
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.k_engine = BaseCalculationsGroup(data_provider, output) self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates = {} self.session_id = self.data_provider.session_id self.score_templates = {} self.get_templates() self.get_score_template() self.manufacturer_fk = self.all_products[ self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0] self.sos = SOS(self.data_provider, self.output) self.total_score = 0 self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0] self.toolbox = GENERALToolBox(self.data_provider) self.scenes_info = self.data_provider[Data.SCENES_INFO] self.kpi_results_new_tables_queries = []
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.united_scenes = self.get_united_scenes() # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.sos = SOS(self.data_provider, self.output) self.templates = {} self.common_db = Common(self.data_provider, CMA_COMPLIANCE) self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.program = self.store_info['additional_attribute_14'].iloc[0] self.sales_center = self.store_info['additional_attribute_5'].iloc[0] if self.store_type in STORE_TYPES: ##### self.store_type = STORE_TYPES[self.store_type] #### self.store_attr = self.store_info['additional_attribute_15'].iloc[0] self.kpi_static_data = self.common_db.get_kpi_static_data() self.total_score = 0 for sheet in Const.SHEETS_CMA: self.templates[sheet] = pd.read_excel(TEMPLATE_PATH, sheetname=sheet).fillna('')
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.tool_box = HEINEKENTWToolBox(self.data_provider, self.output) self.common = Common(data_provider)
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 = DIAGEODISPUSToolBox(self.data_provider, self.output, self.common)
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.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'inbevtradmx_template.xlsx') self.availability = Availability(self.data_provider) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.geo = GeoLocation.Geo(self.rds_conn, self.session_uid, self.data_provider, self.kpi_static_data, self.common)
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v2 = CommonV2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.thresholds_and_results = {} self.result_df = [] self.writing_to_db_time = datetime.timedelta(0) self.kpi_results_queries = [] self.potential_products = {} self.shelf_square_boundaries = {} self.average_shelf_values = {} self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.all_template_data = parse_template(TEMPLATE_PATH, "KPI") self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing") self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel( FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities( 1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.assortment = Assortment(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider) self.store_assortment = self.assortment.get_lvl3_relevant_ass() self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider)
def __init__(self, data_provider, output, calculation_type, common_db2): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif[self.scif['product_type'] != "Irrelevant"] self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.templates = {} self.result_values = self.ps_data_provider.get_result_values() self.calculation_type = calculation_type if self.calculation_type == Const.SOVI: self.TEMPLATE_PATH = Const.TEMPLATE_PATH self.RED_SCORE = Const.RED_SCORE self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG for sheet in Const.SHEETS: self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.converters = self.templates[Const.CONVERTERS] self.scenes_results = self.ps_data_provider.get_scene_results( self.scene_info['scene_fk'].drop_duplicates().values) self.scenes_results = self.scenes_results[[Const.DB_RESULT, Const.DB_SCENE_FK, Const.DB_SCENE_KPI_FK]] else: self.TEMPLATE_PATH = Const.SURVEY_TEMPLATE_PATH self.RED_SCORE = Const.MANUAL_RED_SCORE self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG for sheet in Const.SHEETS_MANUAL: self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.store_attr = self.store_info['additional_attribute_15'].iloc[0] self.toolbox = FunctionsToolBox(self.data_provider, self.output, self.templates, self.store_attr) self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG) self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data() self.common_db = Common(self.data_provider, self.RED_SCORE) self.common_db2 = common_db2 self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] if self.store_type in Const.STORE_TYPES: self.store_type = Const.STORE_TYPES[self.store_type] self.kpi_static_data = self.common_db.get_kpi_static_data() main_template = self.templates[Const.KPIS] self.templates[Const.KPIS] = main_template[(main_template[Const.REGION] == self.region) & (main_template[Const.STORE_TYPE] == self.store_type)] self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS) self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS) self.used_scenes = [] self.red_score = 0 self.set_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE) self.set_integ_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG) self.weight_factor = self.get_weight_factor()
def __init__(self, data_provider, output, calculation_type): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif[self.scif['product_type'] != "Irrelevant"] self.united_scenes = self.get_united_scenes( ) # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.templates = {} self.calculation_type = calculation_type if self.calculation_type == Const.SOVI: self.TEMPLATE_PATH = TEMPLATE_PATH self.RED_SCORE = Const.RED_SCORE self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG for sheet in Const.SHEETS: self.templates[sheet] = pd.read_excel( self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.converters = self.templates[Const.CONVERTERS] else: self.TEMPLATE_PATH = SURVEY_TEMPLATE_PATH self.RED_SCORE = Const.MANUAL_RED_SCORE self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG for sheet in Const.SHEETS_MANUAL: self.templates[sheet] = pd.read_excel( self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG) self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data() self.common_db = Common(self.data_provider, self.RED_SCORE) self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] if self.store_type in STORE_TYPES: ##### self.store_type = STORE_TYPES[self.store_type] #### self.store_attr = self.store_info['additional_attribute_15'].iloc[0] self.kpi_static_data = self.common_db.get_kpi_static_data() main_template = self.templates[Const.KPIS] self.templates[Const.KPIS] = main_template[ (main_template[Const.REGION] == self.region) & (main_template[Const.STORE_TYPE] == self.store_type)] self.scene_calculator = CCBOTTLERSUSSceneRedToolBox( data_provider, output, self.templates, self) self.scenes_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE) self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_SESSION) self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE) self.used_scenes = [] self.red_score = 0 self.weight_factor = self.get_weight_factor()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common2 = Common2(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.templates = self.data_provider.all_templates self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'inbevtradmx_template_11_v3.xlsx') self.availability = Availability(self.data_provider) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid, self.data_provider, self.kpi_static_data, self.common, self.common2) self.new_static_data = self.common2.kpi_static_data self.manufacturer_fk = 1 self.match_displays_in_scene = self.data_provider.match_display_in_scene self.mpis = self.mpis_merger() self.all_data = pd.merge(self.scif, self.match_product_in_scene[[ 'product_fk', 'shelf_number', 'scene_fk', 'facing_sequence_number' ]], how="inner", left_on=['item_id', 'scene_id'], right_on=['product_fk', 'scene_fk']).drop_duplicates() self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.EXCLUDE_FILTER = 0 self.CONTAIN_FILTER = 2 self.EXCLUDE_EMPTY = False self.INCLUDE_EMPTY = True
class NESTLEUSToolBox: 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 = [] def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ score = 0 return score
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 = []
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.tool_box = MOLSONCOORSHR_SANDToolBox(self.data_provider, self.output) self.common = Common(data_provider)
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v2 = CommonV2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting() self.kpi_results_queries = [] self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider) self.active_kpis = self._get_active_kpis() self.external_targets = self.ps_data_provider.get_kpi_external_targets() self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value() self._add_smart_attributes_to_mpis() self.scene_graphs = {} self.excessive_flipsigns = False self.incorrect_tags_in_pos_areas = []
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.tool_box = HEINEKENTWToolBox(self.data_provider, self.output) self.common = Common(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.common.commit_results_data()
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 = DIAGEODISPUSToolBox(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.manual_collection_number.empty: Log.warning('Manual collection numbers is empty for this session') return self.tool_box.main_calculation() self.common.commit_results_data_to_new_tables()
def __init__(self, data_provider, output, common_db2): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.manufacturer_fk = 1 self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.united_scenes = self.get_united_scenes( ) # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.sos = SOS(self.data_provider, self.output) self.templates = {} self.common_db = Common(self.data_provider, SUB_PROJECT) self.common_db2 = common_db2 self.result_values = self.ps_data_provider.get_result_values() self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.program = self.store_info['additional_attribute_14'].iloc[0] self.sales_center = self.store_info['additional_attribute_5'].iloc[0] if self.store_type in STORE_TYPES: self.store_type = STORE_TYPES[self.store_type] self.store_attr = self.store_info['additional_attribute_15'].iloc[0] self.kpi_static_data = self.common_db.get_kpi_static_data() self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.total_score = 0 self.total_count = 0 for sheet in Const.SHEETS_CMA: self.templates[sheet] = pd.read_excel(TEMPLATE_PATH, sheetname=sheet).fillna('') self.tools = Shared(self.data_provider, self.output)
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.tool_box = CCAAUToolBox(self.data_provider, self.output) self.common = Common(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') # for kpi_set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique().tolist(): # score = self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk) # self.common.write_to_db_result(kpi_set_fk, self.tool_box.LEVEL1, score) self.tool_box.main_calculation() self.common.commit_results_data()
def __init__(self, data_provider, output, common_v2): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif[~(self.scif['product_type'] == 'Irrelevant')] self.sw_scenes = self.get_sw_scenes( ) # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.sos = SOS(self.data_provider, self.output) self.templates = {} self.common_db = Common(self.data_provider, CMA_COMPLIANCE) self.common_db2 = common_v2 self.common_scene = CommonV2(self.data_provider) self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.program = self.store_info['additional_attribute_3'].iloc[0] self.sales_center = self.store_info['additional_attribute_5'].iloc[0] if self.store_type in STORE_TYPES: ##### self.store_type = STORE_TYPES[self.store_type] #### self.store_attr = self.store_info['additional_attribute_3'].iloc[0] self.kpi_static_data = self.common_db.get_kpi_static_data() self.total_score = 0 self.sub_scores = defaultdict(int) self.sub_totals = defaultdict(int) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' for sheet in Const.SHEETS_CMA: self.templates[sheet] = pd.read_excel(TEMPLATE_PATH, sheetname=sheet).fillna('')
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('') self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS] self.kpi_results_queries = [] self.mapping_param = {"manufacturer": "manufacturer_name"} self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id", "scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"}
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ Common(self.data_provider).commit_results_data() self.calculate_red_score( ) # should be first, because it can include a deletion from the common # # self.calculate_bci() self.calculate_manufacturer_displays() self.calculate_cma_compliance() self.calculate_sovi() self.calculate_ara() self.calculate_msc() self.calculate_liberty() self.common_db.commit_results_data()
def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ Common(self.data_provider).commit_results_data() self.calculate_red_score() # self.calculate_bci() # self.calculate_manufacturer_displays() self.calculate_cma_compliance() # self.calculate_cma_compliance_sw() self.calculate_warehouse_juice() self.calculate_sovi() self.calculate_msc() self.calculate_liberty() self.calculate_contact_center() self.calculate_facings_by_shelf() self.common_v2.commit_results_data() # saves results to new tables
def __init__(self, data_provider, output, common_db2): self.output = output self.data_provider = data_provider self.common_db = Common(self.data_provider, SUB_PROJECT) self.common_db2 = common_db2 self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif[~(self.scif['product_type'] == 'Irrelevant')] self.sw_scenes = self.get_relevant_scenes( ) # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.sos = SOS(self.data_provider, self.output) self.results = self.data_provider[Data.SCENE_KPI_RESULTS] self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.program = self.store_info['additional_attribute_3'].iloc[0] self.sales_center = self.store_info['additional_attribute_5'].iloc[0] if self.store_type in STORE_TYPES: ##### self.store_type = STORE_TYPES[self.store_type] #### self.store_attr = self.store_info['additional_attribute_3'].iloc[0] # self.kpi_static_data = self.common_db.get_kpi_static_data() self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.sub_scores = defaultdict(int) self.sub_totals = defaultdict(int) self.templates = self.get_template() self.hierarchy = self.templates[Const.KPIS].set_index( Const.KPI_NAME)[Const.PARENT].to_dict() self.templates = self.get_relevant_template(self.templates) self.children = self.templates[Const.KPIS][Const.KPI_NAME] self.tools = Shared(self.data_provider, self.output)
class CCAAUToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 EXCLUDE_FILTER = 0 INCLUDE_FILTER = 1 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.templates = self.data_provider[Data.TEMPLATES] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template = self.data_provider.all_templates # templates self.kpi_static_data = self.common.get_new_kpi_static_data() self.toolbox = GENERALToolBox(data_provider) kpi_path = os.path.dirname(os.path.realpath(__file__)) base_file = os.path.basename(kpi_path) self.exclude_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Exclude") self.Include_filters = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="Include") self.bay_count_kpi = pd.read_excel(os.path.join( kpi_path[:-len(base_file)], 'Data', 'template.xlsx'), sheetname="BayCountKPI") def main_calculation(self): """ This function calculates the KPI results. """ self.calculate_sos() self.calculate_bay_kpi() self.common.commit_results_data_to_new_tables() def calculate_sos(self): """ This function filtering Data frame - "scene item facts" by the parameters in the template. Sending the filtered data frames to linear Sos calculation and facing Sos calculation Writing the results to the new tables in DB """ facing_kpi_fk = self.kpi_static_data[ self.kpi_static_data['client_name'] == 'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0] linear_kpi_fk = self.kpi_static_data[ self.kpi_static_data['client_name'] == 'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0] den_facing_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (self.exclude_filters['apply on'] == 'Denominator')] den_linear_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (self.exclude_filters['apply on'] == 'Denominator')] num_facing_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Facing') & (self.exclude_filters['apply on'] == 'Numerator')] num_linear_exclude_template = self.exclude_filters[ (self.exclude_filters['KPI'] == 'Share of Shelf by Linear') & (self.exclude_filters['apply on'] == 'Numerator')] scene_templates = self.scif['template_fk'].unique().tolist() scene_manufactures = self.scif['manufacturer_fk'].unique().tolist() # exclude filters denominator den_general_facing_filters = self.create_dict_filters( den_facing_exclude_template, self.EXCLUDE_FILTER) den_general_linear_filters = self.create_dict_filters( den_linear_exclude_template, self.EXCLUDE_FILTER) # exclude filters numerator num_general_facing_filters = self.create_dict_filters( num_facing_exclude_template, self.EXCLUDE_FILTER) num_general_linear_filters = self.create_dict_filters( num_linear_exclude_template, self.EXCLUDE_FILTER) df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template) df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template) df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template) df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template) for template in scene_templates: for manufacture in scene_manufactures: sos_filters = { "template_fk": (template, self.INCLUDE_FILTER), "manufacturer_fk": (manufacture, self.INCLUDE_FILTER) } tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)} dict_num_facing = dict( (k, v) for d in [sos_filters, num_general_facing_filters] for k, v in d.items()) numerator_facings = self.calculate_share_space( df_num_fac, dict_num_facing)[0] dict_num_linear = dict( (k, v) for d in [sos_filters, num_general_linear_filters] for k, v in d.items()) numerator_linear = self.calculate_share_space( df_num_lin, dict_num_linear)[1] dict_den_facing = dict( (k, v) for d in [tem_filters, den_general_facing_filters] for k, v in d.items()) denominator_facings = self.calculate_share_space( df_den_fac, dict_den_facing)[0] dict_den_linear = dict( (k, v) for d in [tem_filters, den_general_linear_filters] for k, v in d.items()) denominator_linear = self.calculate_share_space( df_den_lin, dict_den_linear)[1] score_facing = 0 if denominator_facings == 0 else ( numerator_facings / denominator_facings) * 100 score_linear = 0 if denominator_linear == 0 else ( numerator_linear / denominator_linear) * 100 self.common.write_to_db_result_new_tables( facing_kpi_fk, manufacture, numerator_facings, score_facing, template, denominator_facings, score_facing) self.common.write_to_db_result_new_tables( linear_kpi_fk, manufacture, numerator_linear, score_linear, template, denominator_linear, score_linear) def create_dict_filters(self, template, param): """ :param template : Template of the desired filtering to data frame :param param : exclude /include :return: Dictionary of filters and parameter : exclude / include by demeaned """ filters_dict = {} template_without_second = template[template['Param 2'].isnull()] for row in template_without_second.iterrows(): filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','), param) return filters_dict def filter_2_cond(self, data_frame, template): """ :param template: Template of the desired filtering :param data_frame : Data frame :return: data frame filtered by entries in the template with 2 conditions """ template_without_second = template[template['Param 2'].notnull()] if template_without_second is not None: for row in template_without_second.iterrows(): data_frame = data_frame.loc[ (~data_frame[row[1]['Param 1']].isin(row[1]['Value 1']. split(','))) | (~data_frame[row[1]['Param 2']].isin(row[1]['Value 2']. split(',')))] return data_frame def calculate_share_space(self, data_frame, filters): """ :param filters: These are the parameters which the data frame is filtered by. :param data_frame : relevant scene item facts data frame (filtered ) :return: The total number of facings and the shelf width (in mm) according to the filters. """ filtered_scif = data_frame[self.toolbox.get_filter_condition( data_frame, **filters)] sum_of_facings = filtered_scif['facings'].sum() space_length = filtered_scif['gross_len_split_stack'].sum() return sum_of_facings, space_length def calculate_bay_kpi(self): bay_kpi_sheet = self.bay_count_kpi kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] == BAY_COUNT_KPI] if kpi.empty: Log.info("CCAAU Calculate KPI Name:{} not found in DB".format( BAY_COUNT_KPI)) else: Log.info("CCAAU Calculate KPI Name:{} found in DB".format( BAY_COUNT_KPI)) bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] == BAY_COUNT_KPI] if not bay_kpi_row.empty: scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0] if scene_types_to_consider == '*': # Consider all scene types scene_types_to_consider = 'all' else: scene_types_to_consider = [ x.strip() for x in scene_types_to_consider.split(',') ] mpis_with_scene = self.match_product_in_scene.merge( self.scene_info, how='left', on='scene_fk') mpis_with_scene_and_template = mpis_with_scene.merge( self.templates, how='left', on='template_fk') if scene_types_to_consider != 'all': mpis_with_scene_and_template = mpis_with_scene_and_template[ mpis_with_scene_and_template['template_name'].isin( scene_types_to_consider)] mpis_template_group = mpis_with_scene_and_template.groupby( 'template_fk') for template_fk, template_data in mpis_template_group: Log.info("Running for template ID {templ_id}".format( templ_id=template_fk, )) total_bays_for_scene_type = 0 scene_group = template_data.groupby('scene_fk') for scene_fk, scene_data in scene_group: Log.info( "KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}" .format( kpi=BAY_COUNT_KPI, bay_c=int(scene_data['bay_number'].max()), scene_id=scene_fk, )) total_bays_for_scene_type += int( scene_data['bay_number'].max()) Log.info( "KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}" .format( kpi=BAY_COUNT_KPI, bay_c=total_bays_for_scene_type, templ_id=template_fk, )) self.common.write_to_db_result_new_tables( fk=int(kpi['pk'].iloc[0]), numerator_id=int(template_fk), numerator_result=total_bays_for_scene_type, denominator_id=int(self.store_id), denominator_result=total_bays_for_scene_type, result=total_bays_for_scene_type, )
class ALTRIAUSToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v2 = CommonV2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting() self.kpi_results_queries = [] self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider) self.active_kpis = self._get_active_kpis() self.external_targets = self.ps_data_provider.get_kpi_external_targets() self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value() self._add_smart_attributes_to_mpis() self.scene_graphs = {} self.excessive_flipsigns = False self.incorrect_tags_in_pos_areas = [] def _add_smart_attributes_to_mpis(self): smart_attribute_data = \ self.adp.get_match_product_in_probe_state_values(self.mpis['probe_match_fk'].unique().tolist()) self.mpis = pd.merge(self.mpis, smart_attribute_data, on='probe_match_fk', how='left') self.mpis['match_product_in_probe_state_fk'].fillna(0, inplace=True) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ self.generate_graph_per_scene() for graph in self.scene_graphs.values(): self.calculate_fixture_and_block_level_kpis(graph) self.calculate_register_type() self.calculate_age_verification() self.calculate_facings_by_scene_type() self.calculate_session_flags() return def calculate_fixture_and_block_level_kpis(self, graph): for node, node_data in graph.nodes(data=True): if node_data['category'].value != 'POS': self.calculate_fixture_width(node_data) self.calculate_flip_sign(node, node_data, graph) self.calculate_flip_sign_empty_space(node, node_data, graph) self.calculate_flip_sign_locations(node_data) self.calculate_total_shelves(node_data) self.calculate_tags_by_fixture_block(node_data) if node_data['block_number'].value == 1: self.calculate_header(node, node_data, graph) self.calculate_product_above_headers(node_data) self.calculate_no_headers(node_data) return def generate_graph_per_scene(self): relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space'] for scene in relevant_scif['scene_id'].unique().tolist(): agb = AltriaGraphBuilder(self.data_provider, scene) self.scene_graphs[scene] = agb.get_graph() if agb.incorrect_tags_in_pos_area: self.incorrect_tags_in_pos_areas.extend(agb.incorrect_tags_in_pos_area) if len(self.scene_graphs.keys()) > 1: Log.warning("More than one Tobacco Merchandising Space scene detected. Results could be mixed!") return def calculate_fixture_width(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Fixture Width') width = node_data['calculated_width_ft'].value width = round(width) fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value category_fk = self.get_category_fk_by_name(node_data['category'].value) self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=width) def calculate_flip_sign(self, node, node_data, graph): if node_data['category'].value == 'Cigarettes': self.calculate_flip_signs_cigarettes(node, node_data, graph) else: self.calculate_flip_signs_non_cigarettes(node, node_data, graph) return def calculate_flip_signs_non_cigarettes(self, node, node_data, graph): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value flip_signs_by_x_coord = {} for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Flip-Sign': continue center_x = neighbor_data['polygon'].centroid.coords[0][0] flip_signs_by_x_coord[center_x] = neighbor_data for i, pair in enumerate(sorted(flip_signs_by_x_coord.items())): position = i+1 position_fk = self.get_custom_entity_pk(str(position)) if position_fk == 0 or position > 8: self.excessive_flipsigns = True Log.warning('More than 8 flip-sign positions found for a non-cigarettes block') product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value implied_facings = pair[1]['width_of_signage_in_facings'].value width = self.round_threshold(width) self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=implied_facings) def calculate_flip_signs_cigarettes(self, node, node_data, graph): width = node_data['calculated_width_ft'].value fixture_bounds = node_data['polygon'].bounds fixture_width_coord_units = abs(fixture_bounds[0] - fixture_bounds[2]) proportions_dict = self.get_flip_sign_position_proportions(round(width)) if not proportions_dict: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value f_slot_boxes = {} left_bound = fixture_bounds[0] # start proportional divisions on left side of fixture for position, proportion in proportions_dict.items(): right_bound = left_bound + (fixture_width_coord_units * proportion) # TODO update this to use min and max height of graph f_slot_boxes[position] = box(left_bound, -9999, right_bound, 9999) left_bound = right_bound for position, slot_box in f_slot_boxes.items(): for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] try: if neighbor_data['pos_type'].value != 'Flip-Sign': continue except KeyError: continue flip_sign_bounds = neighbor_data['polygon'].bounds flip_sign_box = box(flip_sign_bounds[0], flip_sign_bounds[1], flip_sign_bounds[2], flip_sign_bounds[3]) overlap_ratio = flip_sign_box.intersection(slot_box).area / flip_sign_box.area if overlap_ratio >= REQUIRED_FLIP_SIGN_FSLOT_OVERLAP_RATIO: product_fk = neighbor_data['product_fk'].value position_fk = self.get_custom_entity_pk(position) flip_sign_width = neighbor_data['calculated_width_ft'].value flip_sign_width = self.round_threshold(flip_sign_width) implied_facings = neighbor_data['width_of_signage_in_facings'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=flip_sign_width, score=implied_facings) return def calculate_flip_sign_empty_space(self, node, node_data, graph): if node_data['category'].value != 'Cigarettes': return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Empty Flip Sign Space') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value fixture_width = node_data['calculated_width_ft'].value fixture_width = self.round_threshold(fixture_width) flip_sign_widths = [] for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Flip-Sign': continue # exclude 'General POS Other' if neighbor_data['product_fk'].value == 9304: continue neighbor_width = neighbor_data['calculated_width_ft'].value neighbor_width = self.round_threshold(neighbor_width) flip_sign_widths.append(neighbor_width) empty_space = abs(fixture_width - sum(flip_sign_widths)) self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=empty_space) def calculate_flip_sign_locations(self, node_data): if node_data['category'].value != 'Cigarettes': return fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign Locations') width = node_data['calculated_width_ft'].value width = round(width) proportions_dict = self.get_flip_sign_position_proportions(width) if not proportions_dict: return for position, proportion in proportions_dict.items(): position_fk = self.get_custom_entity_pk(position) self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=round(proportion * width)) return def get_flip_sign_position_proportions(self, width): relevant_template = self.fixture_width_template[self.fixture_width_template['Fixture Width (ft)'] == width] if relevant_template.empty: Log.error("Unable to save flip sign location data. {}ft does not exist as a defined width".format(width)) return None # this could definitely be simpler, but I'm tired and don't want to use my brain relevant_template[['F1', 'F2', 'F3', 'F4']] = relevant_template[['F1', 'F2', 'F3', 'F4']].fillna(0) f_slots = [relevant_template['F1'].iloc[0], relevant_template['F2'].iloc[0], relevant_template['F3'].iloc[0], relevant_template['F4'].iloc[0]] f_slots = [i for i in f_slots if i != 0] proportions_dict = {} for i, slot in enumerate(f_slots): proportions_dict["F{}".format(i+1)] = slot / float(sum(f_slots)) return proportions_dict def calculate_total_shelves(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Total Shelves') shelves = len(node_data['shelf_number'].values) fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value category_fk = self.get_category_fk_by_name(node_data['category'].value) self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=shelves) def calculate_tags_by_fixture_block(self, node_data): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('FACINGS_BY_FIXTURE_BLOCK') scene_match_fks = node_data['match_fk'].values fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value relevant_matches = self.mpis[self.mpis['scene_match_fk'].isin(scene_match_fks)] relevant_matches = relevant_matches.groupby(['product_fk', 'shelf_number', 'match_product_in_probe_state_fk'], as_index=False).agg({'scene_match_fk': 'min', 'probe_match_fk': 'count'}) relevant_matches.rename(columns={'probe_match_fk': 'facings'}, inplace=True) for row in relevant_matches.itertuples(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.match_product_in_probe_state_fk, numerator_result=block_number, denominator_result=fixture_number, result=row.facings, score=row.shelf_number, context_id=row.scene_match_fk) def calculate_header(self, node, node_data, graph): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Header') parent_category = node_data['category'].value fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value if parent_category not in self.header_positions_template.columns.tolist(): return headers_by_x_coord = {} menu_board_items_by_x_coord = {} for neighbor in graph.neighbors(node): neighbor_data = graph.nodes[neighbor] if neighbor_data['category'].value != 'POS': continue if neighbor_data['pos_type'].value != 'Header': continue center_x = neighbor_data['polygon'].centroid.coords[0][0] if neighbor_data['in_menu_board_area'].value is True: menu_board_items_by_x_coord[center_x] = neighbor_data else: headers_by_x_coord[center_x] = neighbor_data number_of_headers = len(headers_by_x_coord.keys()) if menu_board_items_by_x_coord: number_of_headers += 1 relevant_positions = \ self.header_positions_template[(self.header_positions_template['Number of Headers'] == number_of_headers)] if relevant_positions.empty: if number_of_headers == 0: return else: Log.error("Too many headers ({}) found for one block ({}). Unable to calculate positions!".format( number_of_headers, parent_category)) return positions = relevant_positions[parent_category].iloc[0] positions = [position.strip() for position in positions.split(',')] for i, pair in enumerate(sorted(headers_by_x_coord.items())): position_fk = self.get_custom_entity_pk(positions[i]) product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value width = self.round_threshold(width) self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=width) if menu_board_items_by_x_coord: for pair in menu_board_items_by_x_coord.items(): position_fk = self.get_custom_entity_pk(positions[-1]) product_fk = pair[1]['product_fk'].value width = pair[1]['calculated_width_ft'].value width = self.round_threshold(width) # width = 1 # this is because there is no real masking for menu board items self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk, numerator_result=block_number, denominator_result=fixture_number, result=width, score=width) return def calculate_product_above_headers(self, node_data): try: product_above_header = node_data['product_above_header'].value except KeyError: return if not product_above_header: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Product Above Header') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=1, score=1, context_id=fixture_number) return def calculate_no_headers(self, node_data): try: no_header = node_data['no_header'].value except KeyError: return if not no_header: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('No Header') fixture_number = node_data['fixture_number'].value block_number = node_data['block_number'].value self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, numerator_result=block_number, denominator_result=fixture_number, result=1, score=1, context_id=fixture_number) return def _get_active_kpis(self): active_kpis = self.kpi_new_static_data[(self.kpi_new_static_data['kpi_calculation_stage_fk'] == 3) & (self.kpi_new_static_data['valid_from'] <= self.visit_date) & ((self.kpi_new_static_data['valid_until']).isnull() | (self.kpi_new_static_data['valid_until'] >= self.visit_date))] return active_kpis def calculate_facings_by_scene_type(self): kpi_name = 'FACINGS_BY_SCENE_TYPE' kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name) if kpi_name not in self.active_kpis['type'].unique().tolist(): return config = self.get_external_target_data_by_kpi_fk(kpi_fk) product_types = config.product_type template_names = config.template_name relevant_mpis = self.mpis[(self.mpis['product_type'].isin(product_types)) & (self.mpis['template_name'].isin(template_names))] relevant_mpis = relevant_mpis.groupby(['product_fk', 'template_fk', 'match_product_in_probe_state_fk'], as_index=False)['scene_match_fk'].count() relevant_mpis.rename(columns={'scene_match_fk': 'facings'}, inplace=True) for row in relevant_mpis.itertuples(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.template_fk, context_id=row.match_product_in_probe_state_fk, result=row.facings) return def calculate_register_type(self): if self.survey_dot_com_collected_this_session: return relevant_scif = self.scif[(self.scif['product_type'].isin(['POS', 'Other'])) & (self.scif['category'] == 'POS Machinery')] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_age_verification(self): if self.survey_dot_com_collected_this_session: return kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification') relevant_scif = self.scif[self.scif['brand_name'].isin(['Age Verification'])] if relevant_scif.empty: result = 0 product_fk = 0 self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) else: result = 1 for product_fk in relevant_scif['product_fk'].unique().tolist(): self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) return def calculate_session_flags(self): if self.excessive_flipsigns: kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Excessive Flip Signs Detected') self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, result=1) if self.incorrect_tags_in_pos_areas: kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Invalid Product in Header Areas') relevant_product_fks = self.mpis[self.mpis['scene_match_fk'].isin(self.incorrect_tags_in_pos_areas)][ 'product_fk'].unique().tolist() for product_fk in relevant_product_fks: self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=1) def mark_tags_in_explorer(self, probe_match_fk_list, mpipsr_name): if not probe_match_fk_list: return try: match_type_fk = \ self.match_product_in_probe_state_reporting[ self.match_product_in_probe_state_reporting['name'] == mpipsr_name][ 'match_product_in_probe_state_reporting_fk'].values[0] except IndexError: Log.warning('Name not found in match_product_in_probe_state_reporting table: {}'.format(mpipsr_name)) return match_product_in_probe_state_values_old = self.common_v2.match_product_in_probe_state_values match_product_in_probe_state_values_new = pd.DataFrame(columns=[MATCH_PRODUCT_IN_PROBE_FK, MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK]) match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_FK] = probe_match_fk_list match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = match_type_fk self.common_v2.match_product_in_probe_state_values = pd.concat([match_product_in_probe_state_values_old, match_product_in_probe_state_values_new]) return @staticmethod def round_threshold(value, threshold=0.2): return round(value - threshold + 0.5) def _get_survey_dot_com_collected_value(self): try: sales_rep_fk = self.session_info['s_sales_rep_fk'].iloc[0] except IndexError: sales_rep_fk = 0 return int(sales_rep_fk) == 209050 def get_category_fk_by_name(self, category_name): return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0] def get_custom_entity_pk(self, name): try: return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0] except IndexError: Log.error('No custom entity found for {}'.format(name)) return 0 def get_external_target_data_by_kpi_fk(self, kpi_fk): return self.external_targets[self.external_targets['kpi_fk'] == kpi_fk].iloc[0] def commit(self): self.common_v2.commit_results_data()
class HEINEKENTWToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 DIST_STORE_LVL1 = 1014 OOS_STORE_LVL1 = 1015 DIST_STORE_LVL2 = 1016 OOS_STORE_LVL2 = 1017 DIST_CATEGORY_LVL1 = 1018 OOS_CATEGORY_LVL1 = 1019 DIST_CATEGORY_LVL2 = 1020 OOS_CATEGORY_LVL2 = 1021 DISTRIBUTION = 4 OOS = 5 MANUFACTURER_FK = 175 # heinken manfucturer def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_results_queries = [] self.assortment = Assortment(self.data_provider, self.output) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ lvl3_result = self.assortment.calculate_lvl3_assortment() self.category_assortment_calculation(lvl3_result) self.store_assortment_calculation(lvl3_result) self.common.commit_results_data_to_new_tables() # self.common.commit_results_data_to_new_tables() def category_assortment_calculation(self, lvl3_result): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups """ if not lvl3_result.empty: # cat_df = self.scif[['product_fk', 'category_fk']] cat_df = self.all_products[['product_fk', 'category_fk']] lvl3_with_cat = lvl3_result.merge(cat_df, on='product_fk', how='left') lvl3_with_cat = lvl3_with_cat[ lvl3_with_cat['category_fk'].notnull()] for result in lvl3_with_cat.itertuples(): if result.in_store == 1: score = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=result.category_fk, denominator_result=1, score=score, score_after_actions=score) if score == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=result.category_fk, denominator_result=1, score=score, score_after_actions=score) category_list = lvl3_with_cat['category_fk'].unique() for cat in category_list: lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] == cat] lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result_cat) for result in lvl2_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=result.passes, denominator_id=cat, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_CATEGORY_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=denominator_res - result.passes, denominator_id=cat, denominator_result=denominator_res, result=1 - res, score=(1 - res), score_after_actions=1 - res) return def store_assortment_calculation(self, lvl3_result): """ This function calculates the KPI results. """ for result in lvl3_result.itertuples(): if result.in_store == 1: score = self.DISTRIBUTION else: score = self.OOS # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=self.store_id, denominator_result=1, score=score) if score == self.OOS: # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL1, numerator_id=result.product_fk, numerator_result=score, result=score, denominator_id=self.store_id, denominator_result=1, score=score, score_after_actions=score) if not lvl3_result.empty: lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result) for result in lvl2_result.itertuples(): denominator_res = result.total if not pd.isnull(result.target) and not pd.isnull( result.group_target_date ) and result.group_target_date <= self.assortment.current_date: denominator_res = result.target res = np.divide(float(result.passes), float(denominator_res)) # Distrubtion self.common.write_to_db_result_new_tables( fk=self.DIST_STORE_LVL2, numerator_id=self.MANUFACTURER_FK, denominator_id=self.store_id, numerator_result=result.passes, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common.write_to_db_result_new_tables( fk=self.OOS_STORE_LVL2, numerator_id=self.MANUFACTURER_FK, numerator_result=denominator_res - result.passes, denominator_id=self.store_id, denominator_result=denominator_res, result=1 - res, score=1 - res, score_after_actions=1 - res) return
class INBEVTRADMXToolBox: 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.common2 = Common2(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.templates = self.data_provider.all_templates self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'inbevtradmx_template_11_v3.xlsx') self.availability = Availability(self.data_provider) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid, self.data_provider, self.kpi_static_data, self.common, self.common2) self.new_static_data = self.common2.kpi_static_data self.manufacturer_fk = 1 self.match_displays_in_scene = self.data_provider.match_display_in_scene self.mpis = self.mpis_merger() self.all_data = pd.merge(self.scif, self.match_product_in_scene[[ 'product_fk', 'shelf_number', 'scene_fk', 'facing_sequence_number' ]], how="inner", left_on=['item_id', 'scene_id'], right_on=['product_fk', 'scene_fk']).drop_duplicates() self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.EXCLUDE_FILTER = 0 self.CONTAIN_FILTER = 2 self.EXCLUDE_EMPTY = False self.INCLUDE_EMPTY = True # init functions: def parse_template(self): """ convert excel file to data frame :return: data frame """ template_df = pd.read_excel(self.excel_file_path, sheetname='template', encoding='utf-8') template_df['Store Additional Attribute 4'] = template_df[ 'Store Additional Attribute 4'].fillna('') template_df['Store Additional Attribute 13'] = template_df[ 'Store Additional Attribute 13'].fillna('') return template_df def check_store_type(self, row, relevant_columns): """ this method checks if the session store type is valid :type relevant_columns: list :param row: current KPI row :return: boolean """ # make sure that we need to check this field if relevant_columns.__contains__('store_type'): # get the valid stores from the template stores_df = pd.read_excel(self.excel_file_path, sheetname='store types') # create a list of the valid stores stores = stores_df.values return row['store_type'] in stores else: return True # calculation: def main_calculation(self): # calculate geo geo_result = self.geo.calculate_geo_location() self.geo.write_geo_to_db(float(geo_result)) # calculate from template self.calculate_kpi_set_from_template() self.calculate_posm_displays() self.common.commit_results_data() self.common2.commit_results_data() def calculate_kpi_set_from_template(self): """ this method chooses the correct set to calculate there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from template :return: None """ # get the template parsed_template = self.parse_template() # get all the unique sets # sets = parsed_template['KPI Level 1 Name'].unique() # get the session additional_attribute_4 & 13 additional_attribute_4 = self.store_info.additional_attribute_4.values[ 0] additional_attribute_13 = self.store_info.additional_attribute_13.values[ 0] set_name = self.choose_correct_sets_to_calculate( additional_attribute_4, additional_attribute_13, parsed_template) # for set_name in set_names: # wrong value in additional attribute 4 - shouldn't calculate if set_name == '': Log.warning( 'Wrong value in additional attribute 4 - shouldnt calculate') return -1 # get only the part of the template that is related to this set set_template_df = parsed_template[parsed_template['KPI Level 1 Name'] == set_name] # start calculating ! self.calculate_set_score(set_template_df, set_name) @staticmethod def choose_correct_sets_to_calculate(additional_attribute_4, additional_attribute_13, template): """ choose what is the appropriate set to calculate :param additional_attribute_4: session additional_attribute_4. if None, will ignore the kpi. :param additional_attribute_13: session additional_attribute_13. if None, will ignore this attribute :param template: parsed template :return: set name to calculate - assuming each additional attribute 4 matches only 1 set name. """ template = template.dropna(subset=['Store Additional Attribute 4'], axis=0) # Makes sure attribute 4 exist if not additional_attribute_4: return '' if additional_attribute_13: sets = template[ (template['Store Additional Attribute 4'].str.contains( additional_attribute_4)) & ((template['Store Additional Attribute 13'].str.contains( additional_attribute_13)) | (template['Store Additional Attribute 13'] == ''))] else: sets = template[ (template['Store Additional Attribute 4'].str.contains( additional_attribute_4)) & (template['Store Additional Attribute 13'] == '')] if sets.empty: return '' else: sets = sets['KPI Level 1 Name'].unique().tolist() if len(sets) == 1: set_name = sets[0] elif additional_attribute_4 == 'BC': set_name = sets[0] elif additional_attribute_4 == 'BA': set_name = sets[1] elif additional_attribute_4 == 'MODELORAMA': set_name = sets[2] else: Log.error('KPI should only run on one KPI set.') return set_name def calculate_set_score(self, set_df, set_name): """ this method iterates kpi set and calculates it's score :param set_df: the set df to calculate score for :param set_name: the kpi set name :return: None """ set_score = 0 # get array of all kpi level 2 names kpi_names = set_df['KPI Level 2 Name'].unique() # iterate all kpi level 2 for kpi_name in kpi_names: # calculate kpi level 2 score kpi_score = self.calculate_kpi_level_2_score( kpi_name, set_df, set_name) # accumulate set score set_score += kpi_score # round set_score if the set is 'Set Urban' if set_name == 'Set Urban': set_score = round(set_score) # finally, write level 1 kpi set score to DB self.write_kpi_set_score_to_db(set_name, set_score) def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name): """ this method gets kpi level 2 name, and iterates it's related atomic kpis :param set_name: kpi set name :param kpi_name: kpi level 2 name :param set_df: kpi set df :return: kpi level 2 score """ kpi_df = set_df[set_df['KPI Level 2 Name'].str.encode('utf8') == kpi_name.encode('utf-8')] kpi_score = 0 # iterate the all related atomic kpis for i, row in kpi_df.iterrows(): # get atomic kpi name kpi_level_3_name = row['KPI Level 3 Name'] # calculate atomic kpi score atomic_kpi_score = self.calculate_atomic_kpi_score( row, kpi_level_3_name, kpi_name, set_name) # accumulate kpi level 2 score kpi_score += atomic_kpi_score write_to_all_levels = False if len(kpi_df ) > 1: # if there is just one atomic we don't need two levels write_to_all_levels = True elif kpi_df['KPI Level 3 Name'].iloc[0] != kpi_df[ 'KPI Level 2 Name'].iloc[0]: write_to_all_levels = True self.write_kpi_score_to_db(kpi_name, set_name, kpi_score, write_to_all_levels) return kpi_score def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name, set_name): """ this method calculates score for specific atomic kpi :param set_name: kpi set name :param kpi_name: kpi name :param kpi_level_3_name: the atomic kpi name :param row: atomic kpi details :return: atomic kpi score """ atomic_kpi_score = 0 # get column name to consider in calculation relevant_columns = map(str.strip, str(row['column names']).split(',')) optional_columns = map(str.strip, str(row['optional column']).split(',')) is_kpi_passed = 0 if self.check_store_type(row, relevant_columns): # get weight of current atomic kpi curr_weight = row['weights'] # figure out what type of calculation need to be done if row['KPI type'] == 'Product Availability': if kpi_level_3_name == 'URBAN': score = self.calculate_weigthed_availability_score( row, relevant_columns) if score: atomic_kpi_score = score elif kpi_level_3_name == 'Hay o no hay # frentes': if self.calculate_lead_availability_score( row, relevant_columns): is_kpi_passed = 1 elif kpi_level_3_name == 'Adherencia de materiales': if self.calculate_or_availability(row, relevant_columns, optional_columns): is_kpi_passed = 1 else: if self.calculate_availability_score( row, relevant_columns): is_kpi_passed = 1 if kpi_level_3_name == '100% Marcas HE': curr_weight = self.unique_brand_count elif row['KPI type'] == 'SOS': ratio = self.calculate_sos_score(row, relevant_columns) if (row['product_type'] == 'Empty') & (ratio <= 0.2): if self.scif[self.scif['template_name'] == row['template_name']].empty: is_kpi_passed = 0 else: is_kpi_passed = 1 elif ratio == 1: is_kpi_passed = 1 elif row['KPI type'] == 'Survey': if self.calculate_survey_score(row): is_kpi_passed = 1 if is_kpi_passed == 1: atomic_kpi_score += curr_weight # write result to DB # the customer asked for this specific KPI will write 100 in DB if it passed even if the weight is 0 if kpi_level_3_name == 'Sin Espacios Vacios' and curr_weight == 0 and is_kpi_passed == 1: # atomic_kpi_score = 100 self.write_atomic_to_db(kpi_level_3_name, 100, kpi_name, set_name, is_kpi_passed, curr_weight) else: self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score, kpi_name, set_name, is_kpi_passed, curr_weight) return atomic_kpi_score # sos def calculate_sos_score(self, row, relevant_columns): """ this method calculates share of shelf score according to columns from the data frame :param relevant_columns: columns to check in the excel file :param row: data frame to calculate from :return: share of shelf score """ if 'shelf_number' in relevant_columns: scif_index_list = [] df = self.all_data[ (self.all_data.template_name == row['template_name']) & (self.all_data.shelf_number == row['shelf_number']) & (self.all_data.facing_sequence_number > 0)] df.shelf_number = df.shelf_number.astype(float) df.shelf_number = df.shelf_number.astype(str) df = df[df['product_type'] != 'Empty'] else: # get df only with the correct template name df = self.scif[self.scif.template_name == row['template_name']] if not pd.isna(row['exclude product_type']): for excl in [ e.strip() for e in row['exclude product_type'].split(',') ]: df = df[df['product_type'] != excl] # sum of all the facings in df facings = df.facings.values.sum() if facings == 0: return 0 # create dictionary for calculating filters_dict = self.create_sos_filtered_dictionary( relevant_columns, row) # reduce the df only to relevant columns df = df[filters_dict.keys()] # check if it's invasion KPI for the special case inv = row['KPI Level 3 Name'][:3] == 'Inv' ratio = self.calculate_sos_ratio(df, filters_dict, inv) return ratio / facings def calculate_sos_ratio(self, df, filters_dict, inv): ratio = 0 if 'manufacturer_name' in df.columns.tolist(): filter_out = df[(df['product_type'] == ('Empty')) & (df['manufacturer_name'] == 'General')].index df.drop(filter_out, inplace=True) for key in filters_dict: delete = [key for value in filters_dict[key] if value in ['nan']] for key in delete: del filters_dict[key] # iterate the data frame for i, df_row in df.iterrows(): # initialize the boolean variable bol = True # special case that the customer asked # if inv and df_row.product_type == 'Empty': if df_row.product_type == 'Empty': ratio = ratio + self.scif.facings.loc[i] continue # iterate the filtered dictionary keys else: for key in filters_dict.keys(): # check if the value in df row in in "key" column is in the filtered dictionary we created before bol &= df_row[key] in filters_dict[key] # that means success of the inner loop, that all the values matching for this data frame row if bol: if 'shelf_number' in filters_dict.keys(): ratio = ratio + self.all_data.facings.loc[i] else: # accumulate ratio ratio = ratio + self.scif.facings.loc[i] return ratio def create_sos_filtered_dictionary(self, relevant_columns, row): """ this method filters out relevant columns from the template fso we can calculate SOS easily :param relevant_columns: relevant columns from temolates :param row: specific row from template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value in [ 'Store Additional Attribute 4', 'Store Additional Attribute 13' ] or column_value == 'store_type': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict # availability: def calculate_weigthed_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ passed = 0 # Gets the scene types scene_types = row['template_name'].split(', ') scenes_num = len(scene_types) # man = row['manufacturer_name'] if 'scene_type' in relevant_columns: relevant_columns.remove('scene_type') # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for scene in scene_types: filters_dict.update({'template_name': scene}) # call the generic method from KPIUtils_v2 if self.availability.calculate_availability(**filters_dict): passed += 1 return (passed / float(scenes_num)) * 100 if scenes_num else 0 def calculate_lead_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ # Gets the brand names brand_names = row['brand_name'].split(', ') if 'scene_type' in relevant_columns: relevant_columns.remove('scene_type') # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for brand in brand_names: filters_dict.update({'brand_name': brand}) # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) if self.decide_availability_score(row, availability_score): return True return False def calculate_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for key in filters_dict: delete = [key for value in filters_dict[key] if value in ['nan']] for key in delete: del filters_dict[key] # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) filtered_df = self.scif[self.get_filter_condition( self.scif, **filters_dict)] self.unique_brand_count = len(filtered_df['brand_name'].unique()) # check if this score should pass or fail return self.decide_availability_score(row, availability_score) def create_availability_filtered_dictionary(self, relevant_columns, row): """ this method creates a dictionary with keys according to the specific row in the template :param relevant_columns: columns to create keys by :param row: the specific row in the template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value == 'Store Additional Attribute 4' or column_value == 'store_type' or \ column_value == 'Store Additional Attribute 13': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict def calculate_or_availability(self, row, relevant_columns, optional_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ for optional_column in optional_columns: # create filtered dictionary temp_relevant_columns = relevant_columns[:] temp_relevant_columns.append(optional_column) filters_dict = self.create_availability_filtered_dictionary( temp_relevant_columns, row) for key in filters_dict: delete = [ key for value in filters_dict[key] if value in ['nan'] ] for key in delete: del filters_dict[key] # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) # check if this score should pass or fail if self.decide_availability_score(row, availability_score): return True return False def filter_product_names(self, exclude_skus): """ this method filters list of SKUs from self.scif :param exclude_skus: list of SKUs to exclude from template :return: filtered list """ return filter(lambda sku: sku not in exclude_skus, self.scif.product_name.values) @staticmethod def decide_availability_score(row, availability_score): """ this method decides if the score should pass or fail according to the template :param row: scpecific row from template :param availability_score: score :return: Boolean """ if availability_score == 0: return False else: if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[ 'KPI Level 3 Name'] == 'Hay o no hay Pop?': if row['KPI Level 2 Name'] == 'Pop Exterior': return availability_score > 1 elif row['KPI Level 2 Name'] == 'Pop Interior': return availability_score > 1 elif row['KPI Level 1 Name'] == 'Set Self Execution' and row[ 'KPI Level 3 Name'] == 'Hay o no hay # frentes': return availability_score > 24 else: return True # surveys: def calculate_survey_score(self, row): """ this method calculates survey score according to columns from the data frame :param row: data frame to calculate from :return: boolean """ question_code = str(int(row['Survey Question Code'])) if not self.survey_response.empty and \ not self.survey_response[self.survey_response.code == question_code].empty: answer = self.survey_response.selected_option_text[ self.survey_response.code == question_code].values[0] if answer == 'Si': return True else: if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador': return True return False else: return False # help functions: def calculate_posm_displays(self): new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(DISPLAY_KPI) for display_fk in self.match_displays_in_scene['display_fk'].to_list(): self.common2.write_to_db_result(fk=new_kpi_set_fk, result=0, numerator_id=self.manufacturer_fk, denominator_id=display_fk) new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(POSM_KPI) for product_fk in self.scif['product_fk'][self.scif['location_type'] == 'POSM'].to_list(): self.common2.write_to_db_result(fk=new_kpi_set_fk, result=0, numerator_id=self.manufacturer_fk, denominator_id=product_fk) def handle_exclude_skus(self, filters_dict, relevant_columns, row): """ this method checks if there is value in 'exclude skus' column in the template. if exists, it filters out the relevant skus from the calculation :param filters_dict: filtered dictionary :param relevant_columns: columns to create keys by :param row: specific row to calculate :return: None """ try: exclude_skus = row['exclude skus'].split(',') except AttributeError: exclude_skus = [] if exclude_skus: # filter out some product names according to template product_names = self.filter_product_names(exclude_skus) filters_dict['product_name'] = product_names if 'exclude skus' in row.to_dict().keys( ) and 'exclude skus' in relevant_columns: relevant_columns.remove('exclude skus') # db functions: def write_kpi_set_score_to_db(self, set_name, set_score): """ this method writes set kpi score to static.kps_results DB :param set_name: set name :param set_score: set score :return: None """ # kpi_set_fk = self.kpi_static_data.kpi_set_fk[self.kpi_static_data.kpi_set_name == set_name].unique()[0] # self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score) new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(set_name) self.common2.write_to_db_result( fk=new_kpi_set_fk, result=set_score, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, identifier_result=self.common2.get_dictionary(name=set_name)) def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score, write_to_all_levels): """ this method writes kpi score to static.kpk_results DB :param kpi_name: name of level 2 kpi :param set_name: name of related set :param kpi_score: the score :return: None """ # kpi_fk = \ # self.kpi_static_data.kpi_fk[ # (self.kpi_static_data.kpi_name.str.encode('utf-8') == kpi_name.encode('utf-8')) & # (self.kpi_static_data.kpi_set_name == set_name)].values[0] # self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score) if write_to_all_levels: new_kpi_fk = self.common2.get_kpi_fk_by_kpi_name(kpi_name) self.common2.write_to_db_result( fk=new_kpi_fk, result=kpi_score, should_enter=True, identifier_parent=self.common2.get_dictionary(name=set_name), identifier_result=self.common2.get_dictionary(name=kpi_name)) def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name, set_name, is_kpi_passed, curr_weight): """ this method writes atomic kpi score to static.kpi_results DB :param curr_weight: current weight of atomic kpi :param is_kpi_passed: is this kpi passed :param atomic_name: atomic kpi name :param atomic_score: the score :param kpi_name: name of related kpi :param set_name: name of related set :return: """ # atomic_kpi_fk = \ # self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) & # (self.kpi_static_data.kpi_name == kpi_name) & # (self.kpi_static_data.kpi_set_name == set_name)].values[0] # attrs = self.common.create_attributes_dict(fk=atomic_kpi_fk, score=is_kpi_passed, level=self.LEVEL3) # attrs['result'] = {0: atomic_score} # attrs['kpi_weight'] = {0: curr_weight} # query = insert(attrs, self.common.KPI_RESULT) # self.common.kpi_results_queries.append(query) identifier_parent = self.common2.get_dictionary(name=kpi_name) if atomic_name == kpi_name: identifier_parent = self.common2.get_dictionary(name=set_name) new_atomic_fk = self.common2.get_kpi_fk_by_kpi_name(atomic_name) # kpi_fk = \ # self.new_static_data[self.new_static_data['client_name'].str.encode('utf-8') == kpi_name.encode('utf-8')][ # 'pk'].values[0] self.common2.write_to_db_result(fk=new_atomic_fk, result=atomic_score, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, weight=curr_weight, should_enter=True, score=is_kpi_passed, identifier_parent=identifier_parent) def mpis_merger(self): try: mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.templates, on='template_fk', suffixes=['', '_t']) return mpis except: pass def get_filter_condition(self, df, **filters): """ :param df: The data frame to be filters. :param filters: These are the parameters which the data frame is filtered by. Every parameter would be a tuple of the value and an include/exclude flag. INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUGENERALToolBox.INCLUDE_FILTER) INPUT EXAMPLE (2): manufacturer_name = 'Diageo' :return: a filtered Scene Item Facts data frame. """ if not filters: return df['pk'].apply(bool) if self.facings_field in df.keys(): filter_condition = (df[self.facings_field] > 0) else: filter_condition = None for field in filters.keys(): if field in df.keys(): if isinstance(filters[field], tuple): value, exclude_or_include = filters[field] else: value, exclude_or_include = filters[ field], self.INCLUDE_FILTER if not value: continue if not isinstance(value, list): value = [value] if exclude_or_include == self.INCLUDE_FILTER: condition = (df[field].isin(value)) elif exclude_or_include == self.EXCLUDE_FILTER: condition = (~df[field].isin(value)) elif exclude_or_include == self.CONTAIN_FILTER: condition = (df[field].str.contains(value[0], regex=False)) for v in value[1:]: condition |= df[field].str.contains(v, regex=False) else: continue if filter_condition is None: filter_condition = condition else: filter_condition &= condition else: pass # Log.warning('field {} is not in the Data Frame'.format(field)) return filter_condition
class CCPHLToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('') self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS] self.kpi_results_queries = [] self.mapping_param = {"manufacturer": "manufacturer_name"} self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id", "scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"} def main_calculation(self): """ This function calculates the KPI results. """ for group in self.template_data[CCPHLConsts.KPI_GROUP].unique(): kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group] if kpis.empty: print("KPI Group:{} is not valid".format(group)) continue for row_num, kpi in kpis.iterrows(): if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY: self.calculate_self_purity_util(kpi) elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL: self.calculate_self_purity_util(kpi) else: continue self.common.commit_results_data_to_new_tables() def calculate_self_purity_util(self, kpi): df_scene_data = self.scif if df_scene_data.empty: return scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')] filter_param_name_1 = kpi['filter_param_name_1'].strip() if len(filter_param_name_1) != 0: filter_param_value_1 = kpi['filter_param_value_1'].strip() else: filter_param_value_1 = "" if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY] df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY] if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y': df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT] df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']] if df_kpi_level_2_fk.empty: kpi_level_2_fk = 0 else: kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk'] df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)] group_list = [] for idx in range(1, 5): entity = kpi['entity' + str(idx)].strip() if entity == 'N/A' or len(entity) == 0: continue else: entity = self.mapping_entity[kpi['entity' + str(idx)].strip()] group_list.append(entity) denominator = 0 if group_list[0] == 'store_id': total_facings = df_scene_data['facings'].sum() denominator = float(total_facings) if len(filter_param_value_1) != 0: df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1] else: df_scene_data2 = df_scene_data filter_columns = list(group_list) filter_columns.append('facings') df_scene_data2 = df_scene_data2[filter_columns] df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index()) store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper() for row_num, row_data in df_purity.iterrows(): numerator = row_data['facings'] if group_list[0] == 'template_fk': df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']] if df_scene_count.empty: total_facings = 0 else: total_facings = df_scene_count['facings'].sum() denominator = float(total_facings) try: result = round(float(numerator) / float(denominator), 4) except ZeroDivisionError: print("Error: {}".format(ZeroDivisionError.message)) continue if kpi_level_2_fk != 0: if result == 0: if store_zero_results == 'Y': self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list)-1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result) else: self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk, numerator_id=row_data[group_list[len(group_list) - 1]], denominator_id=row_data[group_list[0]], numerator_result=numerator, denominator_result=denominator, result=result, score=result)
class CCBOTTLERSUSREDToolBox: def __init__(self, data_provider, output, calculation_type): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif[self.scif['product_type'] != "Irrelevant"] self.united_scenes = self.get_united_scenes( ) # we don't need to check scenes without United products self.survey = Survey(self.data_provider, self.output) self.templates = {} self.calculation_type = calculation_type if self.calculation_type == Const.SOVI: self.TEMPLATE_PATH = TEMPLATE_PATH self.RED_SCORE = Const.RED_SCORE self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG for sheet in Const.SHEETS: self.templates[sheet] = pd.read_excel( self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.converters = self.templates[Const.CONVERTERS] else: self.TEMPLATE_PATH = SURVEY_TEMPLATE_PATH self.RED_SCORE = Const.MANUAL_RED_SCORE self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG for sheet in Const.SHEETS_MANUAL: self.templates[sheet] = pd.read_excel( self.TEMPLATE_PATH, sheetname=sheet).fillna('') self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG) self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data() self.common_db = Common(self.data_provider, self.RED_SCORE) self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] if self.store_type in STORE_TYPES: ##### self.store_type = STORE_TYPES[self.store_type] #### self.store_attr = self.store_info['additional_attribute_15'].iloc[0] self.kpi_static_data = self.common_db.get_kpi_static_data() main_template = self.templates[Const.KPIS] self.templates[Const.KPIS] = main_template[ (main_template[Const.REGION] == self.region) & (main_template[Const.STORE_TYPE] == self.store_type)] self.scene_calculator = CCBOTTLERSUSSceneRedToolBox( data_provider, output, self.templates, self) self.scenes_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE) self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_SESSION) self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE) self.used_scenes = [] self.red_score = 0 self.weight_factor = self.get_weight_factor() # main functions: def main_calculation(self, *args, **kwargs): """ This function gets all the scene results from the SceneKPI, after that calculates every session's KPI, and in the end it calls "filter results" to choose every KPI and scene and write the results in DB. """ main_template = self.templates[Const.KPIS] if self.calculation_type == Const.SOVI: self.scenes_results = self.scene_calculator.main_calculation() session_template = main_template[main_template[Const.SESSION_LEVEL] == Const.V] for i, main_line in session_template.iterrows(): self.calculate_main_kpi(main_line) else: for i, main_line in main_template.iterrows(): self.calculate_manual_kpi(main_line) self.choose_and_write_results() def calculate_main_kpi(self, main_line): """ This function gets a line from the main_sheet, transfers it to the match function, and checks all of the KPIs in the same name in the match sheet. :param main_line: series from the template of the main_sheet. """ kpi_name = main_line[Const.KPI_NAME] kpi_type = main_line[Const.SHEET] relevant_scif = self.scif[ (self.scif['scene_id'].isin(self.united_scenes)) & (self.scif['product_type'] != 'Empty')] scene_types = self.does_exist(main_line, Const.SCENE_TYPE) if scene_types: relevant_scif = relevant_scif[relevant_scif['template_name'].isin( scene_types)] scene_groups = self.does_exist(main_line, Const.SCENE_TYPE_GROUP) if scene_groups: relevant_scif = relevant_scif[relevant_scif['template_group'].isin( scene_groups)] if kpi_type == Const.SCENE_AVAILABILITY: result = False if relevant_scif.empty else True else: isnt_dp = True if self.store_attr != Const.DP and main_line[ Const.STORE_ATTRIBUTE] == Const.DP else False relevant_template = self.templates[kpi_type] relevant_template = relevant_template[relevant_template[ Const.KPI_NAME] == kpi_name] target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \ else main_line[Const.GROUP_TARGET] if main_line[Const.SAME_PACK] == Const.V: result = self.calculate_availability_with_same_pack( relevant_template, relevant_scif, isnt_dp, target) else: function = self.get_kpi_function(kpi_type) passed_counter = 0 for i, kpi_line in relevant_template.iterrows(): answer = function(kpi_line, relevant_scif, isnt_dp) if answer: passed_counter += 1 result = passed_counter >= target self.write_to_session_level(kpi_name=kpi_name, result=result) def calculate_manual_kpi(self, main_line): """ This function gets a line from the main_sheet, transfers it to the match function, and checks all of the KPIs in the same name in the match sheet. :param main_line: series from the template of the main_sheet. """ kpi_name = main_line[Const.KPI_NAME] relevant_template = self.templates[Const.SURVEY] relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name] target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \ else main_line[Const.GROUP_TARGET] passed_counter = 0 for i, kpi_line in relevant_template.iterrows(): answer = self.calculate_survey_specific(kpi_line) if answer: passed_counter += 1 result = passed_counter >= target self.write_to_session_level(kpi_name=kpi_name, result=result) # write in DF: def write_to_session_level(self, kpi_name, result=0): """ Writes a result in the DF :param kpi_name: string :param result: boolean """ result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result * 1} self.session_results = self.session_results.append(result_dict, ignore_index=True) def write_to_all_levels(self, kpi_name, result, display_text, weight, scene_fk=None, reuse_scene=False): """ Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB :param kpi_name: str :param result: int :param display_text: str :param weight: int/float :param scene_fk: for the scene's kpi :param reuse_scene: this kpi can use scenes that were used """ score = self.get_score(weight) * (result > 0) self.red_score += score result_dict = { Const.KPI_NAME: kpi_name, Const.RESULT: result, Const.SCORE: score } if scene_fk: result_dict[Const.SCENE_FK] = scene_fk if not reuse_scene: self.used_scenes.append(scene_fk) self.all_results = self.all_results.append(result_dict, ignore_index=True) self.write_to_db(kpi_name, score, display_text=display_text) # survey: def calculate_survey_specific(self, kpi_line, relevant_scif=None, isnt_dp=None): """ returns a survey line if True or False :param kpi_line: line from the survey sheet :param relevant_scif: :param isnt_dp: :return: True or False - if the question gets the needed answer """ question = kpi_line[Const.Q_TEXT] if not question: question_id = kpi_line[Const.Q_ID] if question_id == "": Log.warning( "The template has a survey question without ID or text") return False question = ('question_fk', int(question_id)) answers = kpi_line[Const.ACCEPTED_ANSWER].split(',') min_answer = None if kpi_line[Const.REQUIRED_ANSWER] == '' else True for answer in answers: if self.survey.check_survey_answer(survey_text=question, target_answer=answer, min_required_answer=min_answer): return True return False # availability: def calculate_availability_with_same_pack(self, relevant_template, relevant_scif, isnt_dp, target): """ checks if all the lines in the availability sheet passes the KPI, AND if all of these filtered scif has at least one common product that has the same size and number of sub_packages. :param relevant_template: all the match lines from the availability sheet. :param relevant_scif: filtered scif :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate DP lines :param target: how many lines should pass :return: boolean """ relevant_scif = relevant_scif.fillna("NAN") # only items categorized as SSD should be evaluated in this calculation; see PROS-6342 relevant_scif = relevant_scif[relevant_scif['att4'] == 'SSD'] if relevant_scif.empty: return False sizes = relevant_scif['size'].tolist() sub_packages_nums = relevant_scif['number_of_sub_packages'].tolist() packages = set(zip(sizes, sub_packages_nums)) for package in packages: passed_counter = 0 filtered_scif = relevant_scif[ (relevant_scif['size'] == package[0]) & (relevant_scif['number_of_sub_packages'] == package[1])] for i, kpi_line in relevant_template.iterrows(): answer = self.calculate_availability(kpi_line, filtered_scif, isnt_dp) if answer: passed_counter += 1 if passed_counter < target: return False return True def calculate_availability(self, kpi_line, relevant_scif, isnt_dp): """ checks if all the lines in the availability sheet passes the KPI (there is at least one product in this relevant scif that has the attributes). :param relevant_scif: filtered scif :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate DP lines :param kpi_line: line from the availability sheet :return: boolean """ if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU: return True filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif) target = kpi_line[Const.TARGET] return filtered_scif[ filtered_scif['facings'] > 0]['facings'].count() >= target def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template, name_in_scif): """ takes scif and filters it from the template :param relevant_scif: the current filtered scif :param kpi_line: line from one sheet (availability for example) :param name_in_template: the column name in the template :param name_in_scif: the column name in SCIF :return: """ values = self.does_exist(kpi_line, name_in_template) if values: if name_in_scif in Const.NUMERIC_VALUES_TYPES: values = [float(x) for x in values] return relevant_scif[relevant_scif[name_in_scif].isin(values)] return relevant_scif def filter_scif_availability(self, kpi_line, relevant_scif): """ calls filter_scif_specific for every column in the template of availability :param kpi_line: :param relevant_scif: :return: """ names_of_columns = { Const.MANUFACTURER: "manufacturer_name", Const.BRAND: "brand_name", Const.TRADEMARK: "att2", Const.SIZE: "size", Const.NUM_SUB_PACKAGES: "number_of_sub_packages", Const.PREMIUM_SSD: "Premium SSD", Const.INNOVATION_BRAND: "Innovation Brand", } for name in names_of_columns: relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line, name, names_of_columns[name]) return relevant_scif # SOS: def calculate_sos(self, kpi_line, relevant_scif, isnt_dp): """ calculates SOS line in the relevant scif. :param kpi_line: line from SOS sheet. :param relevant_scif: filtered scif. :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter all the DP products out of the numerator. :return: boolean """ kpi_name = kpi_line[Const.KPI_NAME] if kpi_line[Const.EXCLUSION_SHEET] == Const.V: exclusion_sheet = self.templates[Const.SKU_EXCLUSION] relevant_exclusions = exclusion_sheet[exclusion_sheet[ Const.KPI_NAME] == kpi_name] for i, exc_line in relevant_exclusions.iterrows(): relevant_scif = self.exclude_scif(exc_line, relevant_scif) relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"] den_type = kpi_line[Const.DEN_TYPES_1] den_value = kpi_line[Const.DEN_VALUES_1] relevant_scif = self.filter_by_type_value(relevant_scif, den_type, den_value) if kpi_line[Const.SSD_STILL] != "": relevant_scif = self.filter_by_type_value( relevant_scif, Const.SSD_STILL, kpi_line[Const.SSD_STILL]) num_type = kpi_line[Const.NUM_TYPES_1] num_value = kpi_line[Const.NUM_VALUES_1] num_scif = self.filter_by_type_value(relevant_scif, num_type, num_value) if isnt_dp: num_scif = num_scif[~( num_scif['manufacturer_name'].isin(Const.DP_MANU))] target = float(kpi_line[Const.TARGET]) / 100 percentage = num_scif['facings'].sum() / relevant_scif['facings'].sum() if relevant_scif['facings'].sum() > 0 \ else 0 return percentage >= target # SOS majority: def calculate_sos_maj(self, kpi_line, relevant_scif, isnt_dp): """ calculates SOS majority line in the relevant scif. Filters the denominator and sends the line to the match function (majority or dominant) :param kpi_line: line from SOS majority sheet. :param relevant_scif: filtered scif. :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter all the DP products out of the numerator (and the denominator of the dominant part). :return: boolean """ kpi_name = kpi_line[Const.KPI_NAME] if kpi_line[Const.EXCLUSION_SHEET] == Const.V: exclusion_sheet = self.templates[Const.SKU_EXCLUSION] relevant_exclusions = exclusion_sheet[exclusion_sheet[ Const.KPI_NAME] == kpi_name] for i, exc_line in relevant_exclusions.iterrows(): relevant_scif = self.exclude_scif(exc_line, relevant_scif) relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"] den_type = kpi_line[Const.DEN_TYPES_1] den_value = kpi_line[Const.DEN_VALUES_1] relevant_scif = self.filter_by_type_value(relevant_scif, den_type, den_value) den_type = kpi_line[Const.DEN_TYPES_2] den_value = kpi_line[Const.DEN_VALUES_2] relevant_scif = self.filter_by_type_value(relevant_scif, den_type, den_value) if kpi_line[Const.MAJ_DOM] == Const.MAJOR: answer = self.calculate_majority_part(kpi_line, relevant_scif, isnt_dp) elif kpi_line[Const.MAJ_DOM] == Const.DOMINANT: answer = self.calculate_dominant_part(kpi_line, relevant_scif, isnt_dp) else: Log.warning("SOS majority does not know '{}' part".format( kpi_line[Const.MAJ_DOM])) answer = False return answer def calculate_majority_part(self, kpi_line, relevant_scif, isnt_dp): """ filters the numerator and checks if the SOS is bigger than 50%. :param kpi_line: line from SOS majority sheet. :param relevant_scif: filtered scif. :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter all the DP products out of the numerator. :return: boolean """ num_type = kpi_line[Const.NUM_TYPES_1] num_value = kpi_line[Const.NUM_VALUES_1] num_scif = self.filter_by_type_value(relevant_scif, num_type, num_value) num_type = kpi_line[Const.NUM_TYPES_2] num_value = kpi_line[Const.NUM_VALUES_2] num_scif = self.filter_by_type_value(num_scif, num_type, num_value) if num_scif.empty: return None if isnt_dp: num_scif = num_scif[~( num_scif['manufacturer_name'].isin(Const.DP_MANU))] target = Const.MAJORITY_TARGET return num_scif['facings'].sum() / relevant_scif['facings'].sum( ) >= target def calculate_dominant_part(self, kpi_line, relevant_scif, isnt_dp): """ filters the numerator and checks if the given value in the given type is the one with the most facings. :param kpi_line: line from SOS majority sheet. :param relevant_scif: filtered scif. :param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter all the DP products out. :return: boolean """ type_name = self.get_column_name(kpi_line[Const.NUM_TYPES_1], relevant_scif) values = str(kpi_line[Const.NUM_VALUES_1]).split(', ') if isnt_dp: relevant_scif = relevant_scif[~( relevant_scif['manufacturer_name'].isin(Const.DP_MANU))] if kpi_line[Const.ADD_IF_NOT_DP] != "": values_to_add = str(kpi_line[Const.ADD_IF_NOT_DP]).split(', ') values = values + values_to_add if type_name in Const.NUMERIC_VALUES_TYPES: values = [float(x) for x in values] max_facings, needed_one = 0, 0 values_type = relevant_scif[type_name].unique().tolist() if None in values_type: values_type.remove(None) current_sum = relevant_scif[ relevant_scif[type_name].isnull()]['facings'].sum() if current_sum > max_facings: max_facings = current_sum for value in values_type: current_sum = relevant_scif[relevant_scif[type_name] == value]['facings'].sum() if current_sum > max_facings: max_facings = current_sum if value in values: needed_one += current_sum return needed_one >= max_facings # helpers: def get_column_name(self, field_name, df): """ checks what the real field name in DttFrame is (if it exists in the DF or exists in the "converter" sheet). :param field_name: str :param df: scif/products :return: real column name (if exists) """ if field_name in df.columns: return field_name if field_name.upper() in self.converters[ Const.NAME_IN_TEMP].str.upper().tolist(): field_name = self.converters[self.converters[ Const.NAME_IN_TEMP].str.upper() == field_name.upper()][ Const.NAME_IN_DB].iloc[0] return field_name return None def filter_by_type_value(self, relevant_scif, type_name, value): """ filters scif with the type and value :param relevant_scif: current filtered scif :param type_name: str (from the template) :param value: str :return: new scif """ if type_name == "": return relevant_scif values = value.split(', ') new_type_name = self.get_column_name(type_name, relevant_scif) if not new_type_name: print "There is no field '{}'".format(type_name) return relevant_scif if new_type_name in Const.NUMERIC_VALUES_TYPES: values = [float(x) for x in values] return relevant_scif[relevant_scif[new_type_name].isin(values)] @staticmethod def exclude_scif(exclude_line, relevant_scif): """ filters products out of the scif :param exclude_line: line from the exclusion sheet :param relevant_scif: current filtered scif :return: new scif """ if exclude_line[Const.PRODUCT_EAN] != "": exclude_products = exclude_line[Const.PRODUCT_EAN].split(', ') relevant_scif = relevant_scif[~( relevant_scif['product_ean_code'].isin(exclude_products))] if exclude_line[Const.BRAND] != "": exclude_brands = exclude_line[Const.BRAND].split(', ') relevant_scif = relevant_scif[~( relevant_scif['brand_name'].isin(exclude_brands))] return relevant_scif @staticmethod def does_exist(kpi_line, column_name): """ checks if kpi_line has values in this column, and if it does - returns a list of these values :param kpi_line: line from template :param column_name: str :return: list of values if there are, otherwise None """ if column_name in kpi_line.keys() and kpi_line[column_name] != "": cell = kpi_line[column_name] if type(cell) in [int, float]: return [cell] elif type(cell) in [unicode, str]: return cell.split(", ") return None def get_kpi_function(self, kpi_type): """ transfers every kpi to its own function :param kpi_type: value from "sheet" column in the main sheet :return: function """ if kpi_type == Const.SURVEY: return self.calculate_survey_specific elif kpi_type == Const.AVAILABILITY: return self.calculate_availability elif kpi_type == Const.SOS: return self.calculate_sos elif kpi_type == Const.SOS_MAJOR: return self.calculate_sos_maj else: Log.warning( "The value '{}' in column sheet in the template is not recognized" .format(kpi_type)) return None def choose_and_write_results(self): """ writes all the KPI in the DB: first the session's ones, second the scene's ones and in the end the ones that depends on the previous ones. After all it writes the red score """ # self.scenes_results.to_csv('results/{}/scene {}.csv'.format(self.calculation_type, self.session_uid))#### # self.session_results.to_csv('results/{}/session {}.csv'.format(self.calculation_type, self.session_uid))#### main_template = self.templates[Const.KPIS] self.write_session_kpis(main_template) if self.calculation_type == Const.SOVI: self.write_scene_kpis(main_template) self.write_condition_kpis(main_template) self.write_missings(main_template) self.write_to_db(self.RED_SCORE, self.red_score) # result_dict = {Const.KPI_NAME: 'RED SCORE', Const.SCORE: self.red_score}#### # self.all_results = self.all_results.append(result_dict, ignore_index=True)#### # self.all_results.to_csv('results/{}/{}.csv'.format(self.calculation_type, self.session_uid))#### def write_missings(self, main_template): """ write 0 in all the KPIs that didn't get score :param main_template: """ for i, main_line in main_template.iterrows(): kpi_name = main_line[Const.KPI_NAME] if not self.all_results[self.all_results[Const.KPI_NAME] == kpi_name].empty: continue result = 0 display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] self.write_to_all_levels(kpi_name, result, display_text, weight) def write_session_kpis(self, main_template): """ iterates all the session's KPIs and saves them :param main_template: main_sheet. """ session_template = main_template[main_template[Const.CONDITION] == ""] if self.calculation_type == Const.SOVI: session_template = session_template[session_template[ Const.SESSION_LEVEL] == Const.V] for i, main_line in session_template.iterrows(): kpi_name = main_line[Const.KPI_NAME] result = self.session_results[self.session_results[Const.KPI_NAME] == kpi_name] if result.empty: continue result = result.iloc[0][Const.RESULT] display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] self.write_to_all_levels(kpi_name, result, display_text, weight) def write_incremental_kpis(self, scene_template): """ lets the incremental KPIs choose their scenes (if they passed). if KPI passed some scenes, we will choose the scene that the children passed :param scene_template: filtered main_sheet :return: the new template (without the KPI written already) """ incremental_template = scene_template[ scene_template[Const.INCREMENTAL] != ""] while not incremental_template.empty: for i, main_line in incremental_template.iterrows(): kpi_name = main_line[Const.KPI_NAME] reuse_scene = main_line[Const.REUSE_SCENE] == Const.V kpi_results = self.scenes_results[self.scenes_results[ Const.KPI_NAME] == kpi_name] if not reuse_scene: kpi_results = kpi_results[~( kpi_results[Const.SCENE_FK].isin(self.used_scenes))] true_results = kpi_results[kpi_results[Const.RESULT] > 0] increments = main_line[Const.INCREMENTAL] if ', ' in increments: first_kpi = increments.split(', ')[0] others = increments.replace(', '.format(first_kpi), '') scene_template.loc[scene_template[Const.KPI_NAME] == first_kpi, Const.INCREMENTAL] = others if true_results.empty: scene_template.loc[scene_template[Const.KPI_NAME] == kpi_name, Const.INCREMENTAL] = "" else: true_results = true_results.sort_values(by=Const.RESULT, ascending=False) display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] scene_fk = true_results.iloc[0][Const.SCENE_FK] self.write_to_all_levels( kpi_name, true_results.iloc[0][Const.RESULT], display_text, weight, scene_fk=scene_fk, reuse_scene=reuse_scene) scene_template = scene_template[~( scene_template[Const.KPI_NAME] == kpi_name)] incremental_template = scene_template[ scene_template[Const.INCREMENTAL] != ""] return scene_template def write_regular_scene_kpis(self, scene_template): """ lets the regular KPIs choose their scenes (if they passed). Like in the incremental part - if KPI passed some scenes, we will choose the scene that the children passed :param scene_template: filtered main_sheet (only scene KPIs, and without the passed incremental) :return: the new template (without the KPI written already) """ for i, main_line in scene_template.iterrows(): kpi_name = main_line[Const.KPI_NAME] reuse_scene = main_line[Const.REUSE_SCENE] == Const.V kpi_results = self.scenes_results[self.scenes_results[ Const.KPI_NAME] == kpi_name] if not reuse_scene: kpi_results = kpi_results[~( kpi_results[Const.SCENE_FK].isin(self.used_scenes))] true_results = kpi_results[kpi_results[Const.RESULT] > 0] display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] if true_results.empty: continue true_results = true_results.sort_values(by=Const.RESULT, ascending=False) scene_fk = true_results.iloc[0][Const.SCENE_FK] self.write_to_all_levels(kpi_name, true_results.iloc[0][Const.RESULT], display_text, weight, scene_fk=scene_fk, reuse_scene=reuse_scene) scene_template = scene_template[~( scene_template[Const.KPI_NAME] == kpi_name)] return scene_template def write_not_passed_scene_kpis(self, scene_template): """ lets the KPIs not passed choose their scenes. :param scene_template: filtered main_sheet (only scene KPIs, and without the passed KPIs) """ for i, main_line in scene_template.iterrows(): kpi_name = main_line[Const.KPI_NAME] reuse_scene = main_line[Const.REUSE_SCENE] == Const.V kpi_results = self.scenes_results[self.scenes_results[ Const.KPI_NAME] == kpi_name] if not reuse_scene: kpi_results = kpi_results[~( kpi_results[Const.SCENE_FK].isin(self.used_scenes))] display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] if kpi_results.empty: continue scene_fk = kpi_results.iloc[0][Const.SCENE_FK] self.write_to_all_levels(kpi_name, 0, display_text, weight, scene_fk=scene_fk, reuse_scene=reuse_scene) def write_scene_kpis(self, main_template): """ iterates every scene_kpi that does not depend on others, and choose the scene they will take: 1. the incrementals take their scene (if they passed). 2. the regular KPIs that passed choose their scenes. 3. the ones that didn't pass choose their random scenes. :param main_template: main_sheet. """ scene_template = main_template[ (main_template[Const.SESSION_LEVEL] != Const.V) & (main_template[Const.CONDITION] == "")] scene_template = self.write_incremental_kpis(scene_template) scene_template = self.write_regular_scene_kpis(scene_template) self.write_not_passed_scene_kpis(scene_template) def write_condition_kpis(self, main_template): """ writes all the KPI that depend on other KPIs by checking if the parent KPI has passed and in which scene. :param main_template: main_sheet """ condition_template = main_template[ main_template[Const.CONDITION] != ''] for i, main_line in condition_template.iterrows(): condition = main_line[Const.CONDITION] kpi_name = main_line[Const.KPI_NAME] if self.calculation_type == Const.MANUAL or main_line[ Const.SESSION_LEVEL] == Const.V: kpi_results = self.session_results[self.session_results[ Const.KPI_NAME] == kpi_name] else: kpi_results = self.scenes_results[self.scenes_results[ Const.KPI_NAME] == kpi_name] condition_result = self.all_results[ (self.all_results[Const.KPI_NAME] == condition) & (self.all_results[Const.RESULT] > 0)] if condition_result.empty: continue condition_result = condition_result.iloc[0] condition_scene = condition_result[Const.SCENE_FK] if condition_scene and Const.SCENE_FK in kpi_results: results = kpi_results[kpi_results[Const.SCENE_FK] == condition_scene] else: results = kpi_results if results.empty: continue result = results.iloc[0][Const.RESULT] display_text = main_line[Const.DISPLAY_TEXT] weight = main_line[Const.WEIGHT] scene_fk = results.iloc[0][ Const.SCENE_FK] if Const.SCENE_FK in kpi_results else None self.write_to_all_levels(kpi_name, result, display_text, weight, scene_fk=scene_fk) def get_united_scenes(self): return self.scif[self.scif['United Deliver'] == 'Y']['scene_id'].unique().tolist() def get_weight_factor(self): sum_weights = self.templates[Const.KPIS][Const.WEIGHT].sum() return sum_weights / 100.0 def get_score(self, weight): return weight / self.weight_factor def write_to_db(self, kpi_name, score, display_text=''): """ writes result in the DB :param kpi_name: str :param score: float :param display_text: str """ if kpi_name == self.RED_SCORE: self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name( self.RED_SCORE, 1), score=score, level=1) if self.common_db_integ: self.write_to_db_result( self.common_db_integ.get_kpi_fk_by_kpi_name( self.RED_SCORE_INTEG, 1), score=score, level=1, set_type=Const.MANUAL) else: self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name( kpi_name, 2), score=score, level=2) self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name( kpi_name, 3), score=score, level=3, display_text=display_text) if self.common_db_integ: self.write_to_db_result( self.common_db_integ.get_kpi_fk_by_kpi_name(kpi_name, 3), score=score, level=3, display_text=kpi_name, set_type=Const.MANUAL) def write_to_db_result(self, fk, level, score, set_type=Const.SOVI, **kwargs): """ This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set), and appends the insert SQL query into the queries' list, later to be written to the DB. """ if kwargs: kwargs['score'] = score attributes = self.create_attributes_dict(fk=fk, level=level, set_type=set_type, **kwargs) else: attributes = self.create_attributes_dict(fk=fk, score=score, set_type=set_type, level=level) if level == self.common_db.LEVEL1: table = self.common_db.KPS_RESULT elif level == self.common_db.LEVEL2: table = self.common_db.KPK_RESULT elif level == self.common_db.LEVEL3: table = self.common_db.KPI_RESULT else: return query = insert(attributes, table) if set_type == Const.SOVI: self.common_db.kpi_results_queries.append(query) else: self.common_db_integ.kpi_results_queries.append(query) def create_attributes_dict(self, score, fk=None, level=None, display_text=None, set_type=Const.SOVI, **kwargs): """ This function creates a data frame with all attributes needed for saving in KPI results tables. or you can send dict with all values in kwargs """ kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ if level == self.common_db.LEVEL1: if kwargs: kwargs['score'] = score values = [val for val in kwargs.values()] col = [col for col in kwargs.keys()] attributes = pd.DataFrame(values, columns=col) else: kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0] attributes = pd.DataFrame( [(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(), format(score, '.2f'), fk)], columns=[ 'kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1', 'kpi_set_fk' ]) elif level == self.common_db.LEVEL2: if kwargs: kwargs['score'] = score values = [val for val in kwargs.values()] col = [col for col in kwargs.keys()] attributes = pd.DataFrame(values, columns=col) else: kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace( "'", "\\'") attributes = pd.DataFrame( [(self.session_uid, self.store_id, self.visit_date.isoformat(), fk, kpi_name, score)], columns=[ 'session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score' ]) elif level == self.common_db.LEVEL3: if kwargs: kwargs['score'] = score values = tuple([val for val in kwargs.values()]) col = [col for col in kwargs.keys()] attributes = pd.DataFrame([values], columns=col) else: data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk] kpi_fk = data['kpi_fk'].values[0] kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0] attributes = pd.DataFrame( [(display_text, self.session_uid, kpi_set_name, self.store_id, self.visit_date.isoformat(), datetime.utcnow().isoformat(), score, kpi_fk, fk)], columns=[ 'display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date', 'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk' ]) else: attributes = pd.DataFrame() return attributes.to_dict() def commit_results(self): """ committing the results in both sets """ self.common_db.delete_results_data_by_kpi_set() self.common_db.commit_results_data_without_delete() if self.common_db_integ: self.common_db_integ.delete_results_data_by_kpi_set() self.common_db_integ.commit_results_data_without_delete()