def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.current_date = datetime.now() self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.own_manufacturer_id = int(self.data_provider[Data.OWN_MANUFACTURER][self.data_provider[Data.OWN_MANUFACTURER]['param_name'] == 'manufacturer_id']['param_value'].tolist()[0]) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.toolbox = GENERALToolBox(data_provider) self.assortment = Assortment(self.data_provider, self.output, common=self.common) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template_path = self.get_template_path() self.template_data = self.get_template_data() self.sos_store_policies = self.get_sos_store_policies(self.visit_date.strftime('%Y-%m-%d')) self.result_values = self.get_result_values() self.scores = pd.DataFrame()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.ps_data_provider = PsDataProvider(data_provider) self.kpi_result_values = self.ps_data_provider.get_result_values() self.common_v2 = Common(self.data_provider) self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.assortment = Assortment(self.data_provider) self.own_manufacturer_fk = int( self.data_provider.own_manufacturer.param_value.values[0]) self.kpi_static_data = self.common_v2.kpi_static_data[[ 'pk', StaticKpis.TYPE ]] # self.custom_entity = self.ps_data_provider.get_custom_entities(Consts.PRODUCT_GROUP) self.visit_date = self.data_provider[Data.VISIT_DATE] self.project_name = self.data_provider.project_name self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.external_targets = self._retrieve_completeness_external_targets() self.products_trax_cat = self._get_products_with_trax_categories() self.ass_groups_present = { Consts.DISTR_SNACKS: 0, Consts.DISTR_SABRA: 0 }
def __init__(self, data_provider, output): self.k_engine = BaseCalculationsScript(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.store_type = self.store_info['store_type'].iloc[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.general_tools = CCKHGENERALToolBox(self.data_provider, self.output) self.template = CCKHTemplateConsts() self.kpi_static_data = self.get_kpi_static_data() self.kpi_results_queries = [] self.commonV2 = CommonV2(self.data_provider) self.kpi_new_static_data = self.commonV2.get_new_kpi_static_data() self.manufacturer = int(self.data_provider.own_manufacturer.param_value.values[0]) self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.external_targets = self.ps_data_provider.get_kpi_external_targets() self.assortment = Assortment(self.data_provider, self.output) self.templates_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.BASIC_SHEET] self.visibility_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.VISIBILITY_SHEET] self.cooler_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.COOLER_SHEET]
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.session_id = self.data_provider.session_id 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.tools = RISPARKWINEDEGENERALToolBox( # self.data_provider, self.output, rds_conn=self.rds_conn) # self.New_kpi_static_data = self.get_new_kpi_static_data() self.kpi_results_new_tables_queries = [] # self.store_assortment = PSAssortmentDataProvider(self.data_provider).execute() self.store_info = self.data_provider[Data.STORE_INFO] self.current_date = datetime.now() self.assortment = Assortment(self.data_provider, self.output) self.store_assortment = self.assortment.store_assortment self.common = Common(self.data_provider)
def __init__(self, data_provider, output, common): self.output = output self.data_provider = data_provider self.common = common self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.session_id = self.data_provider.session_id self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.scene_results = self.data_provider[Data.SCENE_KPI_RESULTS] self.templates = self.data_provider[Data.TEMPLATES] 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.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif['store_fk'] = self.store_id self.scif = self.scif[~(self.scif['product_type'].isin([Const.IRRELEVANT, Const.EMPTY]))] self.scif = self.scif[self.scif['facings'] > 0] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpis = self.load_kpis() self.results_values = self.load_results_values() self.kpi_static_data = self.common.get_kpi_static_data() self.manufacturer_fk = int(self.data_provider[Data.OWN_MANUFACTURER].iloc[0, 1]) self.kpi_results = [] self.assortment = Assortment(self.data_provider, self.output)
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): GlobalSessionToolBox.__init__(self, data_provider, output) self.assortment = Assortment(data_provider) self.ps_data = PsDataProvider(data_provider) self.display_in_scene = data_provider.match_display_in_scene self.static_display = data_provider.static_display self.manufacturer_fk = int(self.manufacturer_fk) self._add_display_data_to_scif() self._add_client_name_and_sub_brand_data()
def __init__(self, data_provider, output): GlobalSessionToolBox.__init__(self, data_provider, output) self.own_manufacturer_fk = int(self.data_provider.own_manufacturer.param_value.values[0]) self.parser = Parser self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.assortment = Assortment(self.data_provider, self.output) self.ps_data = PsDataProvider(self.data_provider, self.output) self.kpi_external_targets = self.ps_data.get_kpi_external_targets(key_fields=Consts.KEY_FIELDS, data_fields=Consts.DATA_FIELDS)
def __init__(self, output, data_provider): super(PepsicoUtil, self).__init__(data_provider) self.output = output self.common = Common(self.data_provider) # self.common_v1 = CommonV1(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] if self.data_provider[Data.STORE_FK] is not None \ else self.session_info['store_fk'].values[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.display_scene = self.get_match_display_in_scene() self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.probe_groups = self.get_probe_group() self.match_product_in_scene = self.match_product_in_scene.merge(self.probe_groups, on='probe_match_fk', how='left') self.toolbox = GENERALToolBox(self.data_provider) self.commontools = PEPSICOUKCommonToolBox(self.data_provider, self.rds_conn) self.all_templates = self.commontools.all_templates self.custom_entities = self.commontools.custom_entities self.on_display_products = self.commontools.on_display_products self.exclusion_template = self.commontools.exclusion_template self.filtered_scif = self.commontools.filtered_scif.copy() self.filtered_matches = self.commontools.filtered_matches.copy() self.filtered_matches = self.filtered_matches.merge(self.probe_groups, on='probe_match_fk', how='left') self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy() self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy() self.scene_bay_shelf_product = self.commontools.scene_bay_shelf_product self.ps_data = PsDataProvider(self.data_provider, self.output) self.full_store_info = self.commontools.full_store_info.copy() self.external_targets = self.commontools.external_targets self.assortment = Assortment(self.commontools.data_provider, self.output) self.lvl3_ass_result = self.get_lvl3_relevant_assortment_result() self.own_manuf_fk = self.all_products[self.all_products['manufacturer_name'] == self.PEPSICO]['manufacturer_fk'].values[0] self.scene_kpi_results = self.get_results_of_scene_level_kpis() self.kpi_results_check = pd.DataFrame(columns=['kpi_fk', 'numerator', 'denominator', 'result', 'score', 'context']) self.sos_vs_target_targets = self.construct_sos_vs_target_base_df() self.all_targets_unpacked = self.commontools.all_targets_unpacked.copy() self.block_results = pd.DataFrame(columns=['Group Name', 'Score']) self.hero_type_custom_entity_df = self.get_hero_type_custom_entity_df()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common_v2 = Common(self.data_provider) self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.assortment = Assortment(self.data_provider, self.output) self.own_manufacturer_fk = int(self.data_provider.own_manufacturer.param_value.values[0]) self.db_handler = DBHandler(self.data_provider.project_name, self.data_provider.session_uid) self.previous_oos_results = self.db_handler.get_last_session_oos_results() self.kpi_result_types = self.db_handler.get_kpi_result_value() self.oos_store_results = list() self.initial_scif = self.scif.copy()
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.project_name = self.data_provider.project_name self.commonV2 = CommonV2(self.data_provider) self.common = Common(self.data_provider) self.assortment = Assortment(self.data_provider, self.output) self.own_manuf_fk = int( self.data_provider.own_manufacturer.param_value.values[0]) self.store_info = self.data_provider[Data.STORE_INFO] self.new_kpi_static_data = self.commonV2.get_new_kpi_static_data() self.all_products_include_deleted = self.data_provider[ Data.ALL_PRODUCTS_INCLUDING_DELETED] self.visit_date = self.data_provider[Data.VISIT_DATE]
def __init__(self, data_provider, common, output, template_path): self.output = output self.data_provider = data_provider self.common = common self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.session_id = self.data_provider.session_id self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.scene_results = self.data_provider[Data.SCENE_KPI_RESULTS] self.templates = self.data_provider[Data.TEMPLATES] 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.mpis = self.make_mpis() self.visit_date = self.data_provider[Data.VISIT_DATE] self.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.jump_shelves = pd.read_excel(template_path).T.to_dict('index') self.scif = self.generate_scif() self.scif['store_fk'] = self.store_id self.scif = self.scif[~(self.scif['product_type'] == Const.IRRELEVANT)] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpis = self.load_kpis() self.kpi_static_data = self.common.get_kpi_static_data() self.dist_oos = self.dist_kpi_to_oos_kpi() self.manufacturer_fk = int( self.data_provider[Data.OWN_MANUFACTURER].iloc[0, 1]) self.gp_manufacturer = self.get_gp_manufacturer() self.gp_categories = self.get_gp_categories() self.gp_brands = self.get_gp_brands() self.all_man = self.scif[['manufacturer_name', 'manufacturer_fk']].set_index('manufacturer_name') \ ['manufacturer_fk'].to_dict() self.all_brands = self.scif[[ 'brand_name', 'brand_fk' ]].set_index('brand_name')['brand_fk'].to_dict() self.man_fk_filter = { 'manufacturer_name': list(self.gp_manufacturer.keys()) } self.cat_filter = {'category': list(self.gp_categories.keys())} self.brand_filter = {'brand_name': list(self.gp_brands.keys())} self.all_brands_filter = {'brand_name': list(self.all_brands.keys())} self.all_man_filter = {'manufacturer_name': list(self.all_man.keys())} self.kpi_results = [] self.assortment = Assortment(self.data_provider, self.output) self.assortment.scif = self.scif
def __init__(self, data_provider, output, common): self.output = output self.data_provider = data_provider self.common = common 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.manufacturer_fk = self.products['manufacturer_fk'][ self.products['manufacturer_name'] == 'MONDELEZ INTERNATIONAL INC'].iloc[0] self.store_assortment = pd.DataFrame() self.assortment = Assortment(self.data_provider, common=self.common) self.store_number = self.get_store_number() self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.custom_entities = self.ps_data_provider.get_custom_entities( Const.PPG_ENTITY_TYPE_FK)
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common_v2 = CommonV2(self.data_provider) self.common_v1 = CommonV1(self.data_provider) self.data_provider.common_v2 = self.common_v2 self.data_provider.common_v1 = self.common_v1 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.channel = self.get_store_channel(self.store_id) self.kpi_static_data = self.common_v2.get_kpi_static_data() self.data_provider.kpi_sheets = {} self.kpi_sheets = self.data_provider.kpi_sheets self.old_kpi_static_data = self.common_v1.get_kpi_static_data() for name in SHEETS_NAME: parsed_template = ParseTemplates.parse_template(TEMPLATE_PATH, sheet_name=name) self.kpi_sheets[name] = parsed_template[parsed_template['Channel'] == self.channel] self.data_provider.sos = SOS(self.data_provider, output=None) self.data_provider.assortment = Assortment(self.data_provider, output=None)
def main_calculation(self): """ This function calculates the KPI results. """ self.calculate_share_of_shelf() self.calculate_count_of_display() Assortment(self.data_provider, self.output, common=self.common).main_assortment_calculation()
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 __init__(self, output, data_provider): super(StraussfritolayilUtil, self).__init__(data_provider) self.output = output self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.ps_data = PsDataProvider(self.data_provider, self.output) self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.brand_mix_df = self.get_brand_mix_df() self.add_sub_brand_to_scif() self.add_brand_mix_to_scif() self.match_probe_in_scene = self.ps_data.get_product_special_attribute_data(self.session_uid) self.match_product_in_scene = self.data_provider[Data.MATCHES] if not self.match_product_in_scene.empty: self.match_product_in_scene = self.match_product_in_scene.merge(self.scif[Consts.RELEVENT_FIELDS], on=["scene_fk", "product_fk"], how="left") self.filter_scif_and_mpis_to_contain_only_primary_shelf() else: unique_fields = [ele for ele in Consts.RELEVENT_FIELDS if ele not in ["product_fk", "scene_fk"]] self.match_product_in_scene = pd.concat([self.match_product_in_scene, pd.DataFrame(columns=unique_fields)], axis=1) self.match_product_in_scene_wo_hangers = self.exclude_special_attribute_products(df=self.match_product_in_scene) self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_info = self.data_provider[Data.STORE_INFO] self.additional_attribute_2 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_2].values[0] self.additional_attribute_3 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_3].values[0] self.additional_attribute_4 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_4].values[0] self.store_id = self.store_info['store_fk'].values[0] if self.store_info['store_fk'] is not None else 0 self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.toolbox = GENERALToolBox(self.data_provider) self.kpi_external_targets = self.ps_data.get_kpi_external_targets(key_fields=Consts.KEY_FIELDS, data_fields=Consts.DATA_FIELDS) self.filter_external_targets() self.assortment = Assortment(self.data_provider, self.output) self.lvl3_assortment = self.set_updated_assortment() self.own_manuf_fk = int(self.data_provider.own_manufacturer.param_value.values[0]) self.own_manufacturer_matches_wo_hangers = self.match_product_in_scene_wo_hangers[ self.match_product_in_scene_wo_hangers['manufacturer_fk'] == self.own_manuf_fk]
def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v1 = CommonV1(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.visit_type = self.store_info[ Const.ADDITIONAL_ATTRIBUTE_2].values[0] self.all_templates = self.data_provider[Data.ALL_TEMPLATES] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif.loc[~(self.scif[Const.PRODUCT_TYPE] == Const.IRRELEVANT)] # Vitaly's request self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.k_engine = BaseCalculationsGroup(data_provider, output) self.toolbox = GENERALToolBox(data_provider) self.assortment = Assortment(self.data_provider, self.output, common=self.common_v1) if not self.scif.empty: self.pepsico_fk = self.get_relevant_pk_by_name( Const.MANUFACTURER, Const.PEPSICO) self.categories_to_calculate = self.get_relevant_categories_for_session( ) self.main_shelves = self.get_main_shelves()
def __init__(self, data_provider, common, output): self.output = output self.data_provider = data_provider self.common = common self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.scene = self.scene_info.loc[0, 'scene_fk'] self.templates = self.data_provider[Data.TEMPLATES] 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.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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.mdis = self.get_match_display_in_scene() self.mpis = self.get_mpis() self.manufacturer_fk = self.products['manufacturer_fk'][ self.products['manufacturer_name'] == 'MONDELEZ INTERNATIONAL INC'].iloc[0] self.static_task_area_location = self.get_store_task_area() # self.vtw_points_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data', # "VTW_POINTS_SCORE.xlsx") self.dmi_template = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', "MondelezDMI_KPITemplatev7.1.xlsx") self.points_template = pd.read_excel(self.dmi_template, sheetname='VTW_POINTS') self.goldzone_template = pd.read_excel(self.dmi_template, sheetname='GOLD_ZONE') self.assortment = Assortment(self.data_provider, common=self.common) # self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.store_areas = self.get_store_area_df()
class ToolBox(GlobalSessionToolBox): def __init__(self, data_provider, output): GlobalSessionToolBox.__init__(self, data_provider, output) self.assortment = Assortment(self.data_provider, self.output) def main_calculation(self): self.calculate_assortment() @kpi_runtime() def calculate_assortment(self): lvl3_result = self.assortment.calculate_lvl3_assortment() if lvl3_result.empty: return kpi_fks = lvl3_result[Consts.KPI_FK_LVL_2].unique() for kpi_fk in kpi_fks: df = lvl3_result[lvl3_result[Consts.KPI_FK_LVL_2] == kpi_fk] sku_kpi_fk = df[Consts.KPI_FK_LVL_3].values[0] assortment_group_fk = df[ AssortmentGroupConsts.ASSORTMENT_GROUP_FK].values[0] assortment_fk = df[AssortmentProductConsts.ASSORTMENT_FK].values[0] df.apply(lambda row: self.write_to_db( fk=sku_kpi_fk, numerator_id=row[ProductsConsts.PRODUCT_FK], denominator_id=assortment_fk, result=row[Consts.IN_STORE], score=row[Consts.IN_STORE], identifier_parent=kpi_fk, should_enter=True), axis=1) denominator = len(df) numerator = len(df[df[Consts.IN_STORE] == 1]) result = numerator / float(denominator) self.write_to_db(fk=kpi_fk, numerator_id=assortment_group_fk, denominator_id=self.store_id, result=result, score=result, numerator_result=numerator, denominator_result=denominator, identifier_result=kpi_fk)
class CSTOREToolBox: def __init__(self, data_provider, output, common): self.output = output self.data_provider = data_provider self.common = common self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.session_id = self.data_provider.session_id self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.scene_results = self.data_provider[Data.SCENE_KPI_RESULTS] self.templates = self.data_provider[Data.TEMPLATES] 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.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif['store_fk'] = self.store_id self.scif = self.scif[~(self.scif['product_type'].isin([Const.IRRELEVANT, Const.EMPTY]))] self.scif = self.scif[self.scif['facings'] > 0] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpis = self.load_kpis() self.results_values = self.load_results_values() self.kpi_static_data = self.common.get_kpi_static_data() self.manufacturer_fk = int(self.data_provider[Data.OWN_MANUFACTURER].iloc[0, 1]) self.kpi_results = [] self.assortment = Assortment(self.data_provider, self.output) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ # if not self.filter_df(self.scif, self.brand_filter).empty: self.calculate_displays() self.calculate_assortment() for result in self.kpi_results: self.write_to_db(**result) return def calculate_facings_sos(self): self.safety_func('Facings SOS', self.calculate_sos, [Const.FACING_SOS_KPI, {}]) def safety_func(self, group, func, args): try: func(*args) Log.info('{} KPIs Calculated'.format(group)) except Exception as e: Log.error('ERROR {} KPIs Failed to Calculate'.format(group)) Log.error(e) def calculate_displays(self): relevant_kpis = self.kpis[self.kpis[Const.KPI_FAMILY] == Const.DISPLAY] relevant_kpis['num_types'] = self.name_to_col_name(relevant_kpis[Const.NUMERATOR]) df_base = self.scif[self.scif['template_name'] == 'Displays - Mondelez Brands Only'] df_base = df_base[df_base['manufacturer_fk'] == self.manufacturer_fk] df_base['numerator_result'], df_base['result'] = 1, 1 if not df_base.empty: for i, kpi in relevant_kpis.iterrows(): parent = relevant_kpis[relevant_kpis['type'] == Const.SOS_HIERARCHY[kpi['type']]] # Note, parent is df, kpi is a series df = df_base.copy() df = self.update_and_rename_df(df, kpi, parent) df = self.transform_new_col(df, 'numerator_id', 'numerator_result') df.drop('scene_id', axis=1, inplace=True) df.drop_duplicates(inplace=True) df['result'] = df['numerator_result'] if parent.empty: df['denominator_id'] = self.store_id df.drop('ident_parent', axis=1, inplace=True) self.update_results(df) else: df = pd.DataFrame([self.store_id], columns=['denominator_id']) df['numerator_id'] = self.manufacturer_fk df['numerator_result'], df['result'] = 0, 0 df['kpi_name'] = [key for key, val in Const.SOS_HIERARCHY.items() if val == 'ihavenoparent'][0] self.update_results(df) def update_results(self, df): results = [val for key, val in df.to_dict('index').items()] self.kpi_results += results def update_and_rename_df(self, df, kpi, parent): df['ident_result'] = ['{}_{}'.format(row[kpi['num_types']], kpi['type']) for i, row in df.iterrows()] df['ident_parent'] = ['{}_{}_{}'.format(row[kpi['num_types']], 'Parent', kpi['type']) for i, row in df.iterrows()] parent_col = ['ident_parent'] if not parent.empty: df['ident_parent'] = ['{}_{}'.format(row[parent['num_types'].iloc[0]], parent['type'].iloc[0]) for i, row in df.iterrows()] #parent is a df, hence the iloc df = df[['scene_id', 'numerator_result', 'result', kpi['num_types'], 'ident_result'] + parent_col] df.drop_duplicates(inplace=True) df.rename(columns={kpi['num_types']: 'numerator_id'}, inplace=True) df['kpi_name'] = kpi['type'] return df def name_to_col_name(self, col): return ['{}_fk'.format(num) for num in col] def transform_new_col(self, df, group_col, sum_col): df[sum_col] = df.groupby(group_col)[sum_col].transform('sum') return df def grouper(self, filter, df): return self.filter_df(df, filter).groupby(filter.keys()[0]) def calculate_assortment(self): lvl3_results = self.assortment.calculate_lvl3_assortment() if not lvl3_results.empty: for kpi in lvl3_results['kpi_fk_lvl3'].unique(): lvl3_result = lvl3_results[lvl3_results['kpi_fk_lvl3']==kpi] lvl3_result['target'] = 1 lvlx_result = pd.DataFrame() lvl1_result = pd.DataFrame() # For Dist, they have assortments, but want the results by category # and since there is only one policy per store (barring new which is # handled elsewhere) we will kust pretend that category_fk is the # level 2 assortment group. God rest the soul of whomever needs # to implement additional policies. if kpi == 4000: lvl3_result = lvl3_result.set_index('product_fk').join(self.all_products.set_index('product_fk') ['category_fk']).reset_index()\ .drop_duplicates() lvl3_result = lvl3_result.rename(columns={'assortment_group_fk': 'ass_grp_fk', 'category_fk': 'assortment_group_fk'}) lvlx_result = self.assortment_additional(lvl3_result) lvl2_result = self.assortment.calculate_lvl2_assortment(lvl3_result) if not lvl2_result['kpi_fk_lvl1'].any(): # lvl3_result['assortment_group_fk'] = self.manufacturer_fk lvl2_result['assortment_group_fk'] = self.manufacturer_fk lvl2_result['assortment_super_group_fk'] = self.store_id else: lvl1_result = self.assortment.calculate_lvl1_assortment(lvl2_result) lvl1_result['total'] = lvl2_result['total'].sum() lvl1_result['passes'] = lvl2_result['passes'].sum() lvl1_result['num_id'] = self.manufacturer_fk lvl1_result['den_id'] = self.store_id lvl3_result['in_store'] = lvl3_result['in_store'].apply(lambda x: self.results_values[Const.RESULTS_TYPE_DICT[x]]) self.parse_assortment_results(lvl3_result, 'kpi_fk_lvl3', 'product_fk', 'in_store', 'assortment_group_fk', 'target', None, 'assortment_group_fk') self.parse_assortment_results(lvlx_result, 'kpi_fk_lvl3', 'product_fk', 'in_store', 'assortment_group_fk', 'target', None, 'assortment_group_fk') self.parse_assortment_results(lvl2_result, 'kpi_fk_lvl2', 'assortment_group_fk', 'passes', 'assortment_fk', 'total', 'assortment_group_fk', 'assortment_super_group_fk') self.parse_assortment_results(lvl1_result, 'kpi_fk_lvl1', 'num_id', 'passes', 'den_id', 'total', 'assortment_super_group_fk', None) Log.info('Assortment KPIs Calculated') def parse_assortment_results(self, df, kpi_col, num_id_col, num_col, den_id_col, den_col, self_id, parent): for i, row in df.iterrows(): kpi_res = {'kpi_fk': row[kpi_col], 'numerator_id': row[num_id_col], 'numerator_result': row[num_col], 'denominator_id': row[den_id_col], 'denominator_result': row[den_col], 'score': self.safe_divide(row[num_col], row[den_col]), 'result': self.safe_divide(row[num_col], row[den_col]), 'ident_result': row[self_id] if self_id else None, 'ident_parent': row[parent] if parent else None} self.kpi_results.append(kpi_res) def assortment_additional(self, lvl3_result): assort = set(lvl3_result['product_fk']) additional_sku_df = self.scif[~self.scif['product_fk'].isin(assort)] additional_sku_df = additional_sku_df[additional_sku_df['manufacturer_fk'] == self.manufacturer_fk] additional_sku_df = additional_sku_df[additional_sku_df['facings'] != 0][['product_fk', 'category_fk']] additional_sku_df['kpi_fk_lvl3'] = lvl3_result['kpi_fk_lvl3'].values[0] additional_sku_df['in_store'] = self.results_values['Additional'] additional_sku_df['target'] = 0 additional_sku_df = additional_sku_df.rename(columns={'category_fk': 'assortment_group_fk'}) return additional_sku_df def safe_divide(self, num, den): res = num if num <= den: res = round((float(num) / den) * 100, 2) if num and den else 0 res = '{:.2f}'.format(res) return res @staticmethod def filter_df(df, filters, exclude=0): cols = set(df.columns) for key, val in filters.items(): if key not in cols: return pd.DataFrame() if not isinstance(val, list): val = [val] if exclude: df = df[~df[key].isin(val)] else: df = df[df[key].isin(val)] return df def load_kpis(self): return pd.read_sql_query(Const.KPI_QUERY, self.rds_conn.db) def load_results_values(self): return pd.read_sql_query(Const.RESULT_TYPE_QUERY, self.rds_conn.db).set_index('value')['pk'].to_dict() def write_to_db(self, kpi_name=None, score=0, result=None, target=None, numerator_result=None, scene_result_fk=None, denominator_result=None, numerator_id=999, denominator_id=999, ident_result=None, ident_parent=None, kpi_fk = None): """ writes result in the DB :param kpi_name: str :param score: float :param display_text: str :param result: str :param threshold: int """ if not kpi_fk and kpi_name: kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name) self.common.write_to_db_result(fk=kpi_fk, score=score, result=result, should_enter=True, target=target, numerator_result=numerator_result, denominator_result=denominator_result, numerator_id=numerator_id, denominator_id=denominator_id, identifier_result=ident_result, identifier_parent=ident_parent, scene_result_fk=scene_result_fk)
class ToolBox(GlobalSessionToolBox): def __init__(self, data_provider, output): GlobalSessionToolBox.__init__(self, data_provider, output) self.own_manufacturer_fk = int(self.data_provider.own_manufacturer.param_value.values[0]) self.parser = Parser self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.assortment = Assortment(self.data_provider, self.output) self.ps_data = PsDataProvider(self.data_provider, self.output) self.kpi_external_targets = self.ps_data.get_kpi_external_targets(key_fields=Consts.KEY_FIELDS, data_fields=Consts.DATA_FIELDS) def main_calculation(self): self.calculate_score_sos() self.calculate_oos_and_distribution(assortment_type="Core") self.calculate_oos_and_distribution(assortment_type="Launch") self.calculate_oos_and_distribution(assortment_type="Focus") self.calculate_hierarchy_sos(calculation_type='FACINGS') self.calculate_hierarchy_sos(calculation_type='LINEAR') @kpi_runtime() def calculate_oos_and_distribution(self, assortment_type): dis_numerator = total_facings = 0 oos_store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=assortment_type + Consts.OOS) oos_sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=assortment_type + Consts.OOS_SKU) dis_store_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=assortment_type + Consts.DISTRIBUTION) dis_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=assortment_type + Consts.DISTRIBUTION_CAT) dis_sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=assortment_type + Consts.DISTRIBUTION_SKU) assortment_df = self.assortment.get_lvl3_relevant_ass() assortment_df = assortment_df[assortment_df['kpi_fk_lvl3'] == dis_sku_kpi_fk] product_fks = assortment_df['product_fk'].tolist() categories = list(set(self.all_products[self.all_products['product_fk'].isin(product_fks)]['category_fk'])) categories_dict = dict.fromkeys(categories, (0, 0)) # sku level distribution for sku in product_fks: # 2 for distributed and 1 for oos category_fk = self.all_products[self.all_products['product_fk'] == sku]['category_fk'].values[0] product_df = self.scif[self.scif['product_fk'] == sku] if product_df.empty: categories_dict[category_fk] = map(sum, zip(categories_dict[category_fk], [0, 1])) result = 1 facings = 0 # Saving OOS only if product wasn't in store self.common.write_to_db_result(fk=oos_sku_kpi_fk, numerator_id=sku, denominator_id=category_fk, result=result, numerator_result=result, denominator_result=result, score=facings, identifier_parent=assortment_type + "_OOS", should_enter=True) else: categories_dict[category_fk] = map(sum, zip(categories_dict[category_fk], [1, 1])) result = 2 facings = product_df['facings'].values[0] dis_numerator += 1 total_facings += facings self.common.write_to_db_result(fk=dis_sku_kpi_fk, numerator_id=sku, denominator_id=category_fk, result=result, numerator_result=result, denominator_result=result, score=facings, should_enter=True, identifier_parent=assortment_type + "_DIS_CAT_{}".format(str(category_fk))) # category level distribution for category_fk in categories_dict.keys(): cat_numerator, cat_denominator = categories_dict[category_fk] cat_result = self.get_result(cat_numerator, cat_denominator) self.common.write_to_db_result(fk=dis_cat_kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, result=cat_result, should_enter=True, numerator_result=cat_numerator, denominator_result=cat_denominator, score=cat_result, identifier_parent=assortment_type + "_DIS", identifier_result=assortment_type + "_DIS_CAT_{}".format(str(category_fk))) # store level oos and distribution denominator = len(product_fks) dis_result = self.get_result(dis_numerator, denominator) oos_result = 1 - dis_result oos_numerator = denominator - dis_numerator self.common.write_to_db_result(fk=oos_store_kpi_fk, numerator_id=self.own_manufacturer_fk, denominator_id=self.store_id, result=oos_result, numerator_result=oos_numerator, denominator_result=denominator, score=total_facings, identifier_result=assortment_type + "_OOS") self.common.write_to_db_result(fk=dis_store_kpi_fk, numerator_id=self.own_manufacturer_fk, denominator_id=self.store_id, result=dis_result, numerator_result=dis_numerator, denominator_result=denominator, score=total_facings, identifier_result=assortment_type + "_DIS") def get_kpi_fks(self, kpis_list): for kpi in kpis_list: self.common.get_kpi_fk_by_kpi_type(kpi_type=kpi) @staticmethod def calculate_sos_res(numerator, denominator): if denominator == 0: return 0, 0, 0 result = round(numerator / float(denominator), 3) return result, numerator, denominator @kpi_runtime() def calculate_score_sos(self): relevant_template = self.kpi_external_targets[self.kpi_external_targets[ExternalTargetsConsts.OPERATION_TYPE] == Consts.SOS_KPIS] relevant_rows = relevant_template.copy() lsos_score_kpi_fk = self.common.get_kpi_fk_by_kpi_type(Consts.LSOS_SCORE_KPI) store_denominator = len(relevant_rows) store_numerator = 0 for i, kpi_row in relevant_template.iterrows(): kpi_fk, num_type, num_value, deno_type, deno_value, target, target_range = kpi_row[Consts.RELEVANT_FIELDS] numerator_filters, denominator_filters = self.get_num_and_den_filters(num_type, num_value, deno_type, deno_value) # Only straussil SKUs numerator_filters['manufacturer_fk'] = self.own_manufacturer_fk denominator_filters['manufacturer_fk'] = self.own_manufacturer_fk numerator_df = self.parser.filter_df(conditions=numerator_filters, data_frame_to_filter=self.scif) denominator_df = self.parser.filter_df(conditions=denominator_filters, data_frame_to_filter=self.scif) numerator_result = numerator_df['gross_len_ign_stack'].sum() denominator_result = denominator_df['gross_len_ign_stack'].sum() lsos_result = self.get_result(numerator_result, denominator_result) score = 1 if ((target - target_range) <= lsos_result <= (target + target_range)) else 0 store_numerator += score self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.own_manufacturer_fk, denominator_id=self.store_id, should_enter=True, target=target, numerator_result=numerator_result, denominator_result=denominator_result, result=lsos_result, score=score, identifier_parent='LSOS_SCORE', weight=target_range) store_result = self.get_result(store_numerator, store_denominator) self.common.write_to_db_result(fk=lsos_score_kpi_fk, numerator_id=self.own_manufacturer_fk, denominator_id=self.store_id, should_enter=True, target=store_denominator, numerator_result=store_numerator, denominator_result=store_denominator, result=store_numerator, score=store_result, identifier_result='LSOS_SCORE') @staticmethod def get_num_and_den_filters(numerator_type, numerator_value, denominator_type, denominator_value): if type(numerator_value) != list: numerator_value = [numerator_value] if type(denominator_value) != list: denominator_value = [denominator_value] numerator_filters = {numerator_type: numerator_value} denominator_filters = {denominator_type: denominator_value} return numerator_filters, denominator_filters @staticmethod def get_result(numerator, denominator): if denominator == 0: return 0 else: return round(numerator / float(denominator), 4) def calculate_hierarchy_sos(self, calculation_type): brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=(calculation_type + Consts.SOS_BY_BRAND)) brand_category_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=(calculation_type + Consts.SOS_BY_CAT_BRAND)) sku_kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_type=(calculation_type + Consts.SOS_BY_CAT_BRAND_SKU)) calculation_param = "facings_ign_stack" if calculation_type == 'FACINGS' else "gross_len_ign_stack" sos_df = self.parser.filter_df(conditions={'rlv_sos_sc': 1, 'product_type': ['SKU', 'Empty']}, data_frame_to_filter=self.scif) # brand level sos session_brands = set(sos_df['brand_fk']) brand_den = sos_df[calculation_param].sum() for brand_fk in session_brands: filters = {'brand_fk': brand_fk} brand_df = self.parser.filter_df(conditions=filters, data_frame_to_filter=sos_df) if brand_df.empty: continue manufacturer_fk = brand_df['manufacturer_fk'].values[0] brand_num = brand_df[calculation_param].sum() if brand_num == 0: continue brand_res, brand_num, brand_den = self.calculate_sos_res(brand_num, brand_den) self.common.write_to_db_result(fk=brand_kpi_fk, numerator_id=brand_fk, denominator_id=manufacturer_fk, result=brand_res, numerator_result=brand_num, denominator_result=brand_den, score=brand_res, identifier_result="{}_SOS_brand_{}".format(calculation_type, str(brand_fk))) # brand-category level sos brand_categories = set(self.parser.filter_df(conditions=filters, data_frame_to_filter=sos_df)['category_fk']) for category_fk in brand_categories: cat_den = self.parser.filter_df(conditions={'category_fk': category_fk}, data_frame_to_filter=sos_df)[calculation_param].sum() filters['category_fk'] = category_fk category_df = self.parser.filter_df(conditions=filters, data_frame_to_filter=sos_df) cat_num = category_df[calculation_param].sum() if cat_num == 0: continue cat_res, cat_num, cat_den = self.calculate_sos_res(cat_num, cat_den) self.common.write_to_db_result(fk=brand_category_kpi_fk, numerator_id=brand_fk, context_id=manufacturer_fk, denominator_id=category_fk, result=cat_res, numerator_result=cat_num, should_enter=True, denominator_result=cat_den, score=cat_res, identifier_parent="{}_SOS_brand_{}".format(calculation_type, str(brand_fk)), identifier_result="{}_SOS_cat_{}_brand_{}".format(calculation_type, str(category_fk), str(brand_fk))) product_fks = set(self.parser.filter_df(conditions=filters, data_frame_to_filter=sos_df)['product_fk']) for sku in product_fks: filters['product_fk'] = sku product_df = self.parser.filter_df(conditions=filters, data_frame_to_filter=sos_df) sku_num = product_df[calculation_param].sum() if sku_num == 0: continue sku_result, sku_num, sku_den = self.calculate_sos_res(sku_num, cat_num) self.common.write_to_db_result(fk=sku_kpi_fk, numerator_id=sku, denominator_id=brand_fk, result=sku_result, numerator_result=sku_num, should_enter=True, denominator_result=cat_num, score=sku_num, context_id=category_fk, weight=manufacturer_fk, identifier_parent="{}_SOS_cat_{}_brand_{}".format(calculation_type, str(category_fk), str(brand_fk))) del filters['product_fk'] del filters['category_fk']
class CCKHToolBox(CCKHConsts): LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.k_engine = BaseCalculationsScript(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.store_type = self.store_info['store_type'].iloc[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.general_tools = CCKHGENERALToolBox(self.data_provider, self.output) self.template = CCKHTemplateConsts() self.kpi_static_data = self.get_kpi_static_data() self.kpi_results_queries = [] self.commonV2 = CommonV2(self.data_provider) self.kpi_new_static_data = self.commonV2.get_new_kpi_static_data() self.manufacturer = int(self.data_provider.own_manufacturer.param_value.values[0]) self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.external_targets = self.ps_data_provider.get_kpi_external_targets() self.assortment = Assortment(self.data_provider, self.output) self.templates_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.BASIC_SHEET] self.visibility_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.VISIBILITY_SHEET] self.cooler_info = self.external_targets[self.external_targets[CCKHTemplateConsts.TEMPLATE_OPERATION] == CCKHTemplateConsts.COOLER_SHEET] def get_kpi_static_data(self): """ This function extracts the static KPI data and saves it into one global data frame. The data is taken from static.kpi / static.atomic_kpi / static.kpi_set. """ query = CCKHQueries.get_all_kpi_data() kpi_static_data = pd.read_sql_query(query, self.rds_conn.db) return kpi_static_data def calculate_red_score(self): """ This function calculates the KPI results. """ scores_dict = {} results_list_new_db = [] # assortments based calculations for availability availability_kpi_dict, availability_score_dict = self.get_availability_kpi_data() results_list_new_db.extend(availability_kpi_dict) scores_dict.update(availability_score_dict) # external target based calculations final_main_child = self.templates_info[self.templates_info['Tested KPI Group'] == self.RED_SCORE].iloc[0] all_kpi_dict, all_score_dict = self.get_all_kpi_data() results_list_new_db.extend(all_kpi_dict) scores_dict.update(all_score_dict) # aggregation to calculate red score max_points = sum([score[0] for score in scores_dict.values()]) actual_points = sum([score[1] for score in scores_dict.values()]) red_score = 0 if max_points == 0 else round((actual_points / float(max_points)) * 100, 2) set_fk = self.kpi_static_data['kpi_set_fk'].values[0] self.write_to_db_result(set_fk, (actual_points, max_points, red_score), level=self.LEVEL1) results_list_new_db.append(self.get_new_kpi_dict(self.get_new_kpi_fk(final_main_child), red_score, red_score, actual_points, max_points, target=max_points, weight=actual_points, identifier_result=self.RED_SCORE, identifier_parent=CCKHConsts.WEB_HIERARCHY)) results_list_new_db.append(self.get_new_kpi_dict(self.get_new_kpi_by_name(self.RED_SCORE), red_score, red_score, actual_points, max_points, target=max_points, weight=actual_points, identifier_result=CCKHConsts.WEB_HIERARCHY)) self.commonV2.save_json_to_new_tables(results_list_new_db) self.commonV2.commit_results_data() def get_availability_kpi_data(self): availability_results_list = [] scores_dict = {} availability_assortment_df = self.assortment.calculate_lvl3_assortment() if availability_assortment_df.empty: Log.info("Availability KPI: session: {} does not have relevant assortments.".format(self.session_uid)) return [], {} availability_kpi = self.kpi_new_static_data[self.kpi_new_static_data['type'].str.encode( HelperConsts.UTF8) == self.template.AVAILABILITY_KPI_TYPE.encode(HelperConsts.UTF8)].iloc[0] availability_new_kpi_fk = availability_kpi.pk scores = [] # no need to validate_kpi_run because Availability is 1; seems it was added for the other KPIs kpi_availability_group = availability_assortment_df.groupby('kpi_fk_lvl2') for kpi_fk_lvl2, availability_kpi_df in kpi_availability_group: score, result, threshold = self.calculate_availability(availability_kpi_df) numerator, denominator, result_new_db = result, threshold, result if score is not False: if score is None: points = 0 else: points = 1 scores.append((points, score)) atomic_kpi_name = self.kpi_new_static_data[self.kpi_new_static_data['pk'] == kpi_fk_lvl2].iloc[0].type Log.info('Save availability atomic kpi: {}'.format(atomic_kpi_name)) atomic_kpi = self.kpi_static_data[(self.kpi_static_data['kpi_name'].str.encode(HelperConsts.UTF8) == self.template.AVAILABILITY_KPI_TYPE.encode(HelperConsts.UTF8)) & (self.kpi_static_data['atomic_kpi_name'] == atomic_kpi_name)] atomic_fk = atomic_kpi.iloc[0].atomic_kpi_fk self.write_to_db_result(atomic_fk, (score, result, threshold, points), level=self.LEVEL3) child_name = atomic_kpi.atomic_kpi_name.iloc[0] child_kpi_fk = self.get_new_kpi_by_name(child_name) # kpi fk from new tables Log.info('Save availability for {} ID: {}'.format(child_name, child_kpi_fk)) availability_results_list.append(self.get_new_kpi_dict(child_kpi_fk, result_new_db, score, numerator, denominator, weight=points, target=denominator, identifier_parent={ 'kpi_fk': availability_new_kpi_fk}, )) max_points = sum([score[0] for score in scores]) actual_points = sum([score[0] * score[1] for score in scores]) percentage = 0 if max_points == 0 else round( (actual_points / float(max_points)) * 100, 2) kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'].str.encode(HelperConsts.UTF8) == self.template.AVAILABILITY_KPI_TYPE.encode(HelperConsts.UTF8)][ 'kpi_fk'].values[0] self.write_to_db_result(kpi_fk, (actual_points, max_points, percentage), level=self.LEVEL2) scores_dict[self.template.AVAILABILITY_KPI_TYPE] = (max_points, actual_points) availability_results_list.append(self.get_new_kpi_dict(availability_new_kpi_fk, percentage, percentage, actual_points, max_points, target=max_points, weight=actual_points, identifier_result={ 'kpi_fk': availability_new_kpi_fk}, identifier_parent=self.RED_SCORE)) return availability_results_list, scores_dict def get_all_kpi_data(self): results_list_new_db = [] scores_dict = {} if self.templates_info.empty: Log.info("All KPI: session: {} doesnt have relevant external targets".format(self.session_uid)) return [], {} main_children = self.templates_info[self.templates_info[self.template.KPI_GROUP] == self.RED_SCORE] for c in xrange(0, len(main_children)): main_child = main_children.iloc[c] main_child_kpi_fk = self.get_new_kpi_fk(main_child) # kpi fk from new tables main_kpi_identifier = self.commonV2.get_dictionary(kpi_fk=main_child_kpi_fk) if self.validate_store_type(main_child): children = self.templates_info[self.templates_info [self.template.KPI_GROUP].str.encode(HelperConsts.UTF8) == main_child[self.template.KPI_NAME].encode(HelperConsts.UTF8)] scores = [] for i in xrange(len(children)): child = children.iloc[i] numerator, denominator, result_new_db, numerator_id = 0, 0, 0, None kpi_weight = self.validate_kpi_run(child) if kpi_weight is not False: kpi_type = child[self.template.KPI_TYPE] result = threshold = None if kpi_type == self.SURVEY: score, result, threshold, survey_answer_fk = self.check_survey(child) threshold = None numerator, denominator, result_new_db = 1, 1, score * 100 numerator_id = survey_answer_fk elif kpi_type == self.SHARE_OF_SHELF: score, result, threshold, result_new_db, numerator, denominator = \ self.calculate_share_of_shelf(child) elif kpi_type == self.NUMBER_OF_SCENES: scene_types = self.get_scene_types(child) result = self.general_tools.calculate_number_of_scenes( **{SCENE_TYPE_FIELD: scene_types}) numerator, denominator, result_new_db = result, 1, result score = 1 if result >= 1 else 0 else: Log.warning("KPI of type '{}' is not supported via assortments".format(kpi_type)) continue if score is not False: if score is None: points = 0 else: points = float(child[self.template.WEIGHT] ) if kpi_weight is True else kpi_weight scores.append((points, score)) atomic_fk = self.get_atomic_fk(main_child, child) self.write_to_db_result( atomic_fk, (score, result, threshold, points), level=self.LEVEL3) identifier_parent = main_kpi_identifier child_name = '{}-{}'.format(child[self.template.TRANSLATION], 'Atomic') \ if main_child[self.template.KPI_NAME] == child[self.template.KPI_NAME] else child[ self.template.TRANSLATION] child.set_value(self.template.TRANSLATION, child_name) child_kpi_fk = self.get_new_kpi_fk(child) # kpi fk from new tables results_list_new_db.append(self.get_new_kpi_dict(child_kpi_fk, result_new_db, score, numerator, denominator, weight=points, target=denominator, identifier_parent=identifier_parent, numerator_id=numerator_id)) max_points = sum([score[0] for score in scores]) actual_points = sum([score[0] * score[1] for score in scores]) percentage = 0 if max_points == 0 else round( (actual_points / float(max_points)) * 100, 2) kpi_name = main_child[self.template.TRANSLATION] kpi_fk = self.kpi_static_data[self.kpi_static_data['kpi_name'].str.encode(HelperConsts.UTF8) == kpi_name.encode(HelperConsts.UTF8)]['kpi_fk'].values[0] self.write_to_db_result(kpi_fk, (actual_points, max_points, percentage), level=self.LEVEL2) scores_dict[kpi_name] = (max_points, actual_points) results_list_new_db.append(self.get_new_kpi_dict(main_child_kpi_fk, percentage, percentage, actual_points, max_points, target=max_points, weight=actual_points, identifier_result=main_kpi_identifier, identifier_parent=self.RED_SCORE)) return results_list_new_db, scores_dict def validate_store_type(self, params): """ This function checks whether or not a KPI is relevant for calculation, by the session's store type. """ validation = False stores = params[self.template.STORE_TYPE] if not stores: validation = True elif isinstance(stores, (str, unicode)): if stores.upper() == self.template.ALL or self.store_type in stores.split(self.template.SEPARATOR): validation = True elif isinstance(stores, list): if self.store_type in stores: validation = True return validation def validate_kpi_run(self, params): """ This function checks whether or not a KPI Atomic needs to be calculated, based on a customized template. """ weight = params[self.template.WEIGHT] if str(weight).isdigit(): validation = True else: kpi_group = params[self.template.KPI_GROUP] if kpi_group == 'Visibility': custom_template = self.visibility_info elif kpi_group in ('Ambient Space', 'Cooler Space'): custom_template = self.cooler_info else: return False condition = (custom_template[self.template.KPI_NAME] == params[self.template.KPI_NAME]) if self.template.KPI_GROUP in custom_template.keys() and kpi_group != 'Visibility': condition &= (custom_template[self.template.KPI_GROUP] == params[self.template.KPI_GROUP]) kpi_data = custom_template[condition] if kpi_data.empty: return False try: weight = \ kpi_data[ kpi_data['store_type'].str.encode(HelperConsts.UTF8) == self.store_type.encode( HelperConsts.UTF8)][ 'Target'].values[0] validation = float(weight) except ValueError: validation = False except IndexError: Log.warning("{kpi}: No matching external targets for this session: {sess}".format( kpi=kpi_group, sess=self.session_uid)) validation = False return validation def get_atomic_fk(self, pillar, params): """ This function gets an Atomic KPI's FK out of the template data. """ atomic_name = params[self.template.TRANSLATION] kpi_name = pillar[self.template.TRANSLATION] atomic_fk = self.kpi_static_data[(self.kpi_static_data['kpi_name'].str.encode(HelperConsts.UTF8) == kpi_name.encode(HelperConsts.UTF8)) & ( self.kpi_static_data['atomic_kpi_name'].str.encode( HelperConsts.UTF8) == atomic_name.encode(HelperConsts.UTF8))][ 'atomic_kpi_fk'] if atomic_fk.empty: return None return atomic_fk.values[0] def get_new_kpi_fk(self, params): """ This function gets an KPI's FK from new kpi table 'static.kpi_level_2' out of the template data . """ kpi_name = params[self.template.TRANSLATION] return self.get_new_kpi_by_name(kpi_name) def get_new_kpi_by_name(self, kpi_name): kpi_fk = self.kpi_new_static_data[self.kpi_new_static_data['type'].str.encode(HelperConsts.UTF8) == kpi_name.encode(HelperConsts.UTF8)]['pk'] if kpi_fk.empty: return None return kpi_fk.values[0] def get_scene_types(self, params): """ This function extracts the relevant scene types (==additional_attribute_1) from the template. """ scene_types = params[self.template.SCENE_TYPE] if not scene_types or (isinstance(scene_types, (str, unicode)) and scene_types.upper() == self.template.ALL): return None return scene_types def calculate_availability(self, availability_kpi_df): """ This function calculates Availability typed Atomics from a customized template, and saves the results to the DB. """ all_targets = availability_kpi_df.target.unique() if not all_targets: return False, False, False target = float(all_targets[0]) total_facings_count = availability_kpi_df.facings.sum() score = 1 if total_facings_count >= target else 0 return score, total_facings_count, target def check_survey(self, params): """ This function calculates Survey typed Atomics, and saves the results to the DB. """ survey_id = int(float(params[self.template.SURVEY_ID])) target_answer = params[self.template.SURVEY_ANSWER] survey_answer, survey_answer_fk = self.general_tools.get_survey_answer(survey_data=('question_fk', survey_id)) score = 1 if survey_answer == target_answer else 0 return score, survey_answer, target_answer, survey_answer_fk def calculate_share_of_shelf(self, params): """ This function calculates Facings Share of Shelf typed Atomics, and saves the results to the DB. """ if params[self.template.SOS_NUMERATOR].startswith('~'): sos_filters = {params[self.template.SOS_ENTITY]: (params[self.template.SOS_NUMERATOR][1:], self.general_tools.EXCLUDE_FILTER)} else: sos_filters = {params[self.template.SOS_ENTITY]: params[self.template.SOS_NUMERATOR]} general_filters = {} scene_types = self.get_scene_types(params) if isinstance(scene_types, (str, unicode)): scene_types = scene_types.split(self.template.SEPARATOR) if scene_types: general_filters[SCENE_TYPE_FIELD] = scene_types products_to_exclude = params[self.template.PRODUCT_TYPES_TO_EXCLUDE] if products_to_exclude: general_filters['product_type'] = (products_to_exclude.split(self.template.SEPARATOR), self.general_tools.EXCLUDE_FILTER) numerator_result = self.general_tools.calculate_availability( **dict(sos_filters, **general_filters)) denominator_result = self.general_tools.calculate_availability(**general_filters) if denominator_result == 0: result = 0 else: result = round((numerator_result / float(denominator_result)) * 100, 2) if params[self.template.TARGET]: target = float(params[self.template.TARGET]) * 100 score = 1 if result >= target else 0 else: score = target = None result_string = '{0}% ({1}/{2})'.format(result, int(numerator_result), int(denominator_result)) return score, result_string, target, result, numerator_result, denominator_result def write_to_db_result(self, fk, score, level): """ 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. """ attributes = self.create_attributes_dict(fk, score, level) if level == self.LEVEL1: table = KPS_RESULT elif level == self.LEVEL2: table = KPK_RESULT elif level == self.LEVEL3: table = KPI_RESULT else: return query = insert(attributes, table) self.kpi_results_queries.append(query) def create_attributes_dict(self, fk, score, level): """ This function creates a data frame with all attributes needed for saving in KPI results tables. """ if level == self.LEVEL1: score_2, score_3, score_1 = score kpi_set_name = self.kpi_static_data[self.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_1, '.2f'), score_2, score_3, fk)], columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1', 'score_2', 'score_3', 'kpi_set_fk']) elif level == self.LEVEL2: score_2, score_3, score = score kpi_name = self.kpi_static_data[self.kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0] attributes = pd.DataFrame([(self.session_uid, self.store_id, self.visit_date.isoformat(), fk, kpi_name, score, score_2, score_3)], columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score', 'score_2', 'score_3']) elif level == self.LEVEL3: score, result, threshold, weight = score data = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk] atomic_kpi_name = data['atomic_kpi_name'].values[0] kpi_fk = data['kpi_fk'].values[0] kpi_set_name = self.kpi_static_data[self.kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0] attributes = pd.DataFrame([(atomic_kpi_name, self.session_uid, kpi_set_name, self.store_id, self.visit_date.isoformat(), datetime.utcnow().isoformat(), score, kpi_fk, fk, threshold, result, weight)], columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date', 'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk', 'threshold', 'result', 'kpi_weight']) else: attributes = pd.DataFrame() return attributes.to_dict() @log_runtime('Saving to DB') def commit_results_data(self): """ This function writes all KPI results to the DB, and commits the changes. """ cur = self.rds_conn.db.cursor() delete_queries = CCKHQueries.get_delete_session_results_query(self.session_uid) for query in delete_queries: cur.execute(query) for query in self.kpi_results_queries: cur.execute(query) self.rds_conn.db.commit() def get_new_kpi_dict(self, kpi_fk, result, score, numerator_result, denominator_result, score_after_action=0, weight=None, target=None, identifier_parent=None, identifier_result=None, numerator_id=None): """ This function gets all kpi info and add the relevant numerator_id and denominator_id and return a dictionary with the passed data. :param kpi_fk: pk of kpi :param result :param score :param numerator_result :param denominator_result :param weight :param target :param identifier_parent :param identifier_result :param numerator_id :param score_after_action :returns dict in format of db result """ numerator_id = self.manufacturer if numerator_id is None else numerator_id denominator_id = self.store_id return {'fk': kpi_fk, SessionResultsConsts.NUMERATOR_ID: numerator_id, SessionResultsConsts.DENOMINATOR_ID: denominator_id, SessionResultsConsts.DENOMINATOR_RESULT: denominator_result, SessionResultsConsts.NUMERATOR_RESULT: numerator_result, SessionResultsConsts.RESULT: result, SessionResultsConsts.SCORE: score, SessionResultsConsts.TARGET: target, SessionResultsConsts.WEIGHT: weight, 'identifier_parent': identifier_parent, 'identifier_result': identifier_result, 'score_after_actions': score_after_action, 'should_enter': True, }
def __init__(self, data_provider, output): GlobalSessionToolBox.__init__(self, data_provider, output) self.assortment = Assortment(self.data_provider, self.output)
class ALTRIAUS_SANDToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v2 = CommonV2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.template_info = self.data_provider.all_templates self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng) self.ps_data_provider = PsDataProvider(self.data_provider) self.thresholds_and_results = {} self.result_df = [] self.writing_to_db_time = datetime.timedelta(0) self.kpi_results_queries = [] self.potential_products = {} self.shelf_square_boundaries = {} self.average_shelf_values = {} self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.all_template_data = parse_template(TEMPLATE_PATH, "KPI") self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing") self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype()) self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype()) self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions") self.flip_sign_positions_template = pd.read_excel( FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions") self.custom_entity_data = self.ps_data_provider.get_custom_entities( 1005) self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.assortment = Assortment(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider) self.store_assortment = self.assortment.get_lvl3_relevant_ass() self.kpi_new_static_data = self.common.get_new_kpi_static_data() try: self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.template_info, on='template_fk', suffixes=['', '_t']) except KeyError: Log.warning('MPIS cannot be generated!') return self.adp = AltriaDataProvider(self.data_provider) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ self.calculate_signage_locations_and_widths('Cigarettes') self.calculate_signage_locations_and_widths('Smokeless') self.calculate_register_type() self.calculate_age_verification() self.calculate_juul_availability() self.calculate_assortment() self.calculate_vapor_kpis() kpi_set_fk = 2 set_name = \ self.kpi_static_data.loc[self.kpi_static_data['kpi_set_fk'] == kpi_set_fk]['kpi_set_name'].values[0] template_data = self.all_template_data.loc[ self.all_template_data['KPI Level 1 Name'] == set_name] try: if set_name and not set( template_data['Scene Types to Include'].values[0].encode( ).split(', ')) & set( self.scif['template_name'].unique().tolist()): Log.info('Category {} was not captured'.format( template_data['category'].values[0])) return except Exception as e: Log.info( 'KPI Set {} is not defined in the template'.format(set_name)) for i, row in template_data.iterrows(): try: kpi_name = row['KPI Level 2 Name'] if kpi_name in KPI_LEVEL_2_cat_space: # scene_type = [s for s in row['Scene_Type'].encode().split(', ')] kpi_type = row['KPI Type'] scene_type = row['scene_type'] if row['Param1'] == 'Category' or 'sub_category': category = row['Value1'] if kpi_type == 'category_space': kpi_set_fk = \ self.kpi_new_static_data.loc[self.kpi_new_static_data['type'] == kpi_type]['pk'].values[0] self.calculate_category_space( kpi_set_fk, kpi_name, category, scene_types=scene_type) except Exception as e: Log.info('KPI {} calculation failed due to {}'.format( kpi_name.encode('utf-8'), e)) continue return def calculate_vapor_kpis(self): category = 'Vapor' relevant_scif = self.scif[self.scif['template_name'] == 'JUUL Merchandising'] if relevant_scif.empty: Log.info('No products found for {} category'.format(category)) return relevant_scif = relevant_scif[ (relevant_scif['category'].isin([category, 'POS'])) & (relevant_scif['brand_name'] == 'Juul')] if relevant_scif.empty: return relevant_product_pks = relevant_scif[ relevant_scif['product_type'] == 'SKU']['product_fk'].unique().tolist() relevant_scene_id = self.get_most_frequent_scene(relevant_scif) product_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_product_pks)) & (self.mpis['scene_fk'] == relevant_scene_id)] if product_mpis.empty: Log.info('No products found for {} category'.format(category)) return self.calculate_total_shelves(product_mpis, category, product_mpis) longest_shelf = \ product_mpis[product_mpis['shelf_number'] == self.get_longest_shelf_number(product_mpis, max_shelves_from_top=999)].sort_values(by='rect_x', ascending=True) if longest_shelf.empty or longest_shelf.isnull().all().all(): Log.warning( 'The {} category items are in a non-standard location. The {} category will not be calculated.' .format(category, category)) return relevant_pos = pd.DataFrame() self.calculate_fixture_width(relevant_pos, longest_shelf, category) return def calculate_assortment(self): if self.scif.empty or self.store_assortment.empty: Log.warning( 'Unable to calculate assortment: SCIF or store assortment is empty' ) return grouped_scif = self.scif.groupby('product_fk', as_index=False)['facings'].sum() assortment_with_facings = \ pd.merge(self.store_assortment, grouped_scif, how='left', on='product_fk') assortment_with_facings.loc[:, 'facings'] = assortment_with_facings[ 'facings'].fillna(0) for product in assortment_with_facings.itertuples(): score = 1 if product.facings > 0 else 0 self.common_v2.write_to_db_result( product.kpi_fk_lvl3, numerator_id=product.product_fk, denominator_id=product.assortment_fk, numerator_result=product.facings, result=product.facings, score=score) number_of_skus_present = len( assortment_with_facings[assortment_with_facings['facings'] > 0]) score = 1 if number_of_skus_present > 0 else 0 kpi_fk = assortment_with_facings['kpi_fk_lvl2'].iloc[0] assortment_group_fk = assortment_with_facings[ 'assortment_group_fk'].iloc[0] self.common_v2.write_to_db_result( kpi_fk, numerator_id=assortment_group_fk, numerator_result=number_of_skus_present, denominator_result=len(assortment_with_facings), result=number_of_skus_present, score=score) def calculate_register_type(self): relevant_scif = self.scif[ (self.scif['product_type'].isin(['POS', 'Other'])) & (self.scif['category'] == 'POS Machinery')] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_age_verification(self): relevant_scif = self.scif[self.scif['brand_name'].isin( ['Age Verification'])] if relevant_scif.empty: result = 0 product_fk = 0 else: result = 1 product_fk = relevant_scif['product_fk'].iloc[0] kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification') self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_juul_availability(self): relevant_scif = self.scif[(self.scif['brand_name'].isin(['Juul'])) & (self.scif['product_type'].isin(['POS']))] juul_pos = relevant_scif['product_fk'].unique().tolist() kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Juul POS Availability') if not juul_pos: return result = 1 for product_fk in juul_pos: self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id, result=result) def calculate_category_space(self, kpi_set_fk, kpi_name, category, scene_types=None): template = self.all_template_data.loc[ (self.all_template_data['KPI Level 2 Name'] == kpi_name) & (self.all_template_data['Value1'] == category)] kpi_template = template.loc[template['KPI Level 2 Name'] == kpi_name] if kpi_template.empty: return None kpi_template = kpi_template.iloc[0] values_to_check = [] filters = { 'template_name': scene_types, 'category': kpi_template['Value1'] } if kpi_template['Value1'] in CATEGORIES: category_att = 'category' if kpi_template['Value1']: values_to_check = self.all_products.loc[ self.all_products[category_att] == kpi_template['Value1']][category_att].unique().tolist() for primary_filter in values_to_check: filters[kpi_template['Param1']] = primary_filter new_kpi_name = self.kpi_name_builder(kpi_name, **filters) result = self.calculate_category_space_length( new_kpi_name, **filters) filters['Category'] = kpi_template['KPI Level 2 Name'] score = result numerator_id = self.products['category_fk'][ self.products['category'] == kpi_template['Value1']].iloc[0] self.common_v2.write_to_db_result(kpi_set_fk, numerator_id=numerator_id, numerator_result=999, result=score, score=score) def calculate_category_space_length(self, kpi_name, threshold=0.5, retailer=None, exclude_pl=False, **filters): """ :param threshold: The ratio for a bay to be counted as part of a category. :param filters: These are the parameters which the data frame is filtered by. :return: The total shelf width (in mm) the relevant facings occupy. """ try: filtered_scif = self.scif[self.get_filter_condition( self.scif, **filters)] space_length = 0 bay_values = [] for scene in filtered_scif['scene_fk'].unique().tolist(): scene_matches = self.mpis[self.mpis['scene_fk'] == scene] scene_filters = filters scene_filters['scene_fk'] = scene for bay in scene_matches['bay_number'].unique().tolist(): bay_total_linear = scene_matches.loc[ (scene_matches['bay_number'] == bay) & (scene_matches['stacking_layer'] == 1) & (scene_matches['status'] == 1)]['width_mm_advance'].sum() scene_filters['bay_number'] = bay tested_group_linear = scene_matches[ self.get_filter_condition(scene_matches, **scene_filters)] tested_group_linear_value = tested_group_linear[ 'width_mm_advance'].sum() if tested_group_linear_value: bay_ratio = tested_group_linear_value / float( bay_total_linear) else: bay_ratio = 0 if bay_ratio >= threshold: category = filters['category'] max_facing = scene_matches.loc[ (scene_matches['bay_number'] == bay) & (scene_matches['stacking_layer'] == 1 )]['facing_sequence_number'].max() shelf_length = self.spacing_template_data.query( 'Category == "' + category + '" & Low <= "' + str(max_facing) + '" & High >= "' + str(max_facing) + '"') shelf_length = int(shelf_length['Size'].iloc[-1]) bay_values.append(shelf_length) space_length += shelf_length except Exception as e: Log.info('Linear Feet calculation failed due to {}'.format(e)) space_length = 0 return space_length def get_filter_condition(self, df, **filters): """ :param df: The data frame to be filters. :param filters: These are the parameters which the data frame is filtered by. Every parameter would be a tuple of the value and an include/exclude flag. INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGROGENERALToolBox.INCLUDE_FILTER) INPUT EXAMPLE (2): manufacturer_name = 'Diageo' :return: a filtered Scene Item Facts data frame. """ if not filters: return df['pk'].apply(bool) if self.facings_field in df.keys(): filter_condition = (df[self.facings_field] > 0) else: filter_condition = None for field in filters.keys(): if field in df.keys(): if isinstance(filters[field], tuple): value, exclude_or_include = filters[field] else: value, exclude_or_include = filters[ field], self.INCLUDE_FILTER if not value: continue if not isinstance(value, list): value = [value] if exclude_or_include == self.INCLUDE_FILTER: condition = (df[field].isin(value)) elif exclude_or_include == self.EXCLUDE_FILTER: condition = (~df[field].isin(value)) elif exclude_or_include == self.CONTAIN_FILTER: condition = (df[field].str.contains(value[0], regex=False)) for v in value[1:]: condition |= df[field].str.contains(v, regex=False) else: continue if filter_condition is None: filter_condition = condition else: filter_condition &= condition else: Log.warning('field {} is not in the Data Frame'.format(field)) return filter_condition def kpi_name_builder(self, kpi_name, **filters): """ This function builds kpi name according to naming convention """ for filter in filters.keys(): if filter == 'template_name': continue kpi_name = kpi_name.replace('{' + filter + '}', str(filters[filter])) kpi_name = kpi_name.replace("'", "\'") return kpi_name def calculate_signage_locations_and_widths(self, category): excluded_types = ['Other', 'Irrelevant', 'Empty'] relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space'] if relevant_scif.empty: Log.info('No products found for {} category'.format(category)) return # need to include scene_id from previous relevant_scif # also need to split this shit up into different categories, i.e. smokeless, cigarettes # need to figure out how to deal with POS from smokeless being included with cigarette MPIS # get relevant SKUs from the cigarettes category relevant_scif = relevant_scif[relevant_scif['category'].isin( [category, 'POS'])] relevant_product_pks = relevant_scif[ relevant_scif['product_type'] == 'SKU']['product_fk'].unique().tolist() relevant_pos_pks = \ relevant_scif[(relevant_scif['product_type'] == 'POS') & ~(relevant_scif['brand_name'] == 'Age Verification') & ~(relevant_scif['product_name'] == 'General POS Other')]['product_fk'].unique().tolist() other_product_and_pos_pks = \ relevant_scif[relevant_scif['product_type'].isin(excluded_types)]['product_fk'].tolist() relevant_scene_id = self.get_most_frequent_scene(relevant_scif) product_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_product_pks)) & (self.mpis['scene_fk'] == relevant_scene_id)] if product_mpis.empty: Log.info('No products found for {} category'.format(category)) return self.calculate_total_shelves(product_mpis, category) longest_shelf = \ product_mpis[product_mpis['shelf_number'] == self.get_longest_shelf_number(product_mpis)].sort_values(by='rect_x', ascending=True) if longest_shelf.empty or longest_shelf.isnull().all().all(): Log.warning( 'The {} category items are in a non-standard location. The {} category will not be calculated.' .format(category, category)) return # demarcation_line = longest_shelf['rect_y'].median() old method, had bugs due to longest shelf being lower demarcation_line = product_mpis['rect_y'].min() exclusion_line = -9999 excluded_mpis = self.mpis[ ~(self.mpis['product_fk'].isin(relevant_pos_pks + relevant_product_pks + other_product_and_pos_pks)) & (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) & (self.mpis['rect_x'] > longest_shelf['rect_x'].min()) & (self.mpis['scene_fk'] == relevant_scene_id) & (self.mpis['rect_y'] < demarcation_line)] # we need this line for when SCIF and MPIS don't match excluded_mpis = excluded_mpis[~excluded_mpis['product_type']. isin(excluded_types)] if not excluded_mpis.empty: exclusion_line = excluded_mpis['rect_y'].max() # we need to get POS stuff that falls within the x-range of the longest shelf (which is limited by category) # we also need to account for the fact that the images suck, so we're going to add/subtract 5% of the # max/min values to allow for POS items that fall slightly out of the shelf length range correction_factor = 0.05 correction_value = (longest_shelf['rect_x'].max() - longest_shelf['rect_x'].min()) * correction_factor pos_mpis = self.mpis[ (self.mpis['product_fk'].isin(relevant_pos_pks)) & (self.mpis['rect_x'] < (longest_shelf['rect_x'].max() + correction_value)) & (self.mpis['rect_x'] > (longest_shelf['rect_x'].min() - correction_value)) & (self.mpis['scene_fk'] == relevant_scene_id)] # DO NOT SET TO TRUE WHEN DEPLOYING # debug flag displays polygon_mask graph DO NOT SET TO TRUE WHEN DEPLOYING # DO NOT SET TO TRUE WHEN DEPLOYING relevant_pos = self.adp.get_products_contained_in_displays( pos_mpis, y_axis_threshold=35, debug=False) if relevant_pos.empty: Log.warning( 'No polygon mask was generated for {} category - cannot compute KPIs' .format(category)) # we need to attempt to calculate fixture width, even if there's no polygon mask self.calculate_fixture_width(relevant_pos, longest_shelf, category) return relevant_pos = relevant_pos[[ 'product_fk', 'product_name', 'left_bound', 'right_bound', 'center_x', 'center_y' ]] relevant_pos = relevant_pos.reindex( columns=relevant_pos.columns.tolist() + ['type', 'width', 'position']) relevant_pos['width'] = \ relevant_pos.apply(lambda row: self.get_length_of_pos(row, longest_shelf, category), axis=1) relevant_pos['type'] = \ relevant_pos['center_y'].apply(lambda x: 'Header' if exclusion_line < x < demarcation_line else 'Flip Sign') relevant_pos = relevant_pos.sort_values(['center_x'], ascending=True) relevant_pos = self.remove_duplicate_pos_tags(relevant_pos) # generate header positions if category == 'Cigarettes': number_of_headers = len( relevant_pos[relevant_pos['type'] == 'Header']) if number_of_headers > len( self.header_positions_template['Cigarettes Positions']. dropna()): Log.warning( 'Number of Headers for Cigarettes is greater than max number defined in template!' ) elif number_of_headers > 0: header_position_list = [ position.strip() for position in self.header_positions_template[ self.header_positions_template['Number of Headers'] == number_of_headers] ['Cigarettes Positions'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Header', ['position']] = header_position_list elif category == 'Smokeless': relevant_pos = self.check_menu_scene_recognition(relevant_pos) number_of_headers = len( relevant_pos[relevant_pos['type'] == 'Header']) if number_of_headers > len( self.header_positions_template['Smokeless Positions']. dropna()): Log.warning( 'Number of Headers for Smokeless is greater than max number defined in template!' ) elif number_of_headers > 0: header_position_list = [ position.strip() for position in self.header_positions_template[ self.header_positions_template['Number of Headers'] == number_of_headers] ['Smokeless Positions'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Header', ['position']] = header_position_list relevant_pos = self.get_menu_board_items(relevant_pos, longest_shelf, pos_mpis) # generate flip-sign positions if category == 'Cigarettes': relevant_template = \ self.fixture_width_template.loc[(self.fixture_width_template['Fixture Width (facings)'] - len(longest_shelf)).abs().argsort()[:1]].dropna(axis=1) locations = relevant_template.columns[2:].tolist() right_bound = 0 longest_shelf_copy = longest_shelf.copy() for location in locations: if right_bound > 0: left_bound = right_bound + 1 else: left_bound = longest_shelf_copy.iloc[:relevant_template[ location].iloc[0]]['rect_x'].min() right_bound = longest_shelf_copy.iloc[:relevant_template[ location].iloc[0]]['rect_x'].max() if locations[-1] == location: right_bound = right_bound + abs(right_bound * 0.05) flip_sign_pos = relevant_pos[ (relevant_pos['type'] == 'Flip Sign') & (relevant_pos['center_x'] > left_bound) & (relevant_pos['center_x'] < right_bound)] if flip_sign_pos.empty: # add 'NO Flip Sign' product_fk relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \ [location, NO_FLIP_SIGN_PK, 'Flip Sign'] else: relevant_pos.loc[flip_sign_pos.index, ['position']] = location longest_shelf_copy.drop( longest_shelf_copy.iloc[:relevant_template[location]. iloc[0]].index, inplace=True) elif category == 'Smokeless': # if there are no flip signs found, there are no positions to assign number_of_flip_signs = len( relevant_pos[relevant_pos['type'] == 'Flip Sign']) if number_of_flip_signs > self.flip_sign_positions_template[ 'Number of Flip Signs'].max(): Log.warning( 'Number of Flip Signs for Smokeless is greater than max number defined in template!' ) elif number_of_flip_signs > 0: flip_sign_position_list = [ position.strip() for position in self.flip_sign_positions_template[ self. flip_sign_positions_template['Number of Flip Signs'] == number_of_flip_signs]['Position'].iloc[0].split(',') ] relevant_pos.loc[relevant_pos['type'] == 'Flip Sign', ['position']] = flip_sign_position_list # store empty flip sign values for location in ['Secondary', 'Tertiary']: if location not in relevant_pos[ relevant_pos['type'] == 'Flip Sign']['position'].tolist(): relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \ [location, NO_FLIP_SIGN_PK, 'Flip Sign'] relevant_pos = relevant_pos.reindex( columns=relevant_pos.columns.tolist() + ['denominator_id']) # this is a bandaid fix that should be removed -> 'F7011A7C-1BB6-4007-826D-2B674BD99DAE' # removes POS items that were 'extra', i.e. more than max value in template # only affects smokeless relevant_pos.dropna(subset=['position'], inplace=True) relevant_pos.loc[:, ['denominator_id']] = relevant_pos['position'].apply( self.get_custom_entity_pk) for row in relevant_pos.itertuples(): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(row.type) self.common_v2.write_to_db_result( kpi_fk, numerator_id=row.product_fk, denominator_id=row.denominator_id, result=row.width, score=row.width) self.calculate_fixture_width(relevant_pos, longest_shelf, category) return def calculate_total_shelves(self, longest_shelf, category, product_mpis=None): category_fk = self.get_category_fk_by_name(category) if product_mpis is None: product_mpis = self.mpis[ (self.mpis['rect_x'] > longest_shelf['rect_x'].min()) & (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) & (self.mpis['scene_fk'] == longest_shelf['scene_fk'].fillna(0).mode().iloc[0])] total_shelves = len(product_mpis['shelf_number'].unique()) kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Total Shelves') self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, result=total_shelves) def calculate_fixture_width(self, relevant_pos, longest_shelf, category): correction_factor = 1 if category == 'Smokeless' else 2 longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1] category_fk = self.get_category_fk_by_name(category) # this is needed to remove intentionally duplicated 'Menu Board' POS 'Headers' relevant_pos = relevant_pos.drop_duplicates(subset=['position']) # try: # width = relevant_pos[relevant_pos['type'] == 'Header']['width'].sum() # except KeyError: # # needed for when 'width' doesn't exist # width = 0 # if relevant_pos.empty or width == 0: width = round( len(longest_shelf) + correction_factor / float(self.facings_to_feet_template[category + ' Facings'].iloc[0]) ) kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Fixture Width') self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id, result=width) def get_category_fk_by_name(self, category_name): return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0] def check_menu_scene_recognition(self, relevant_pos): mdis = self.adp.get_match_display_in_scene() mdis = mdis[mdis['display_name'] == 'Menu POS'] if mdis.empty: return relevant_pos dummy_sku_for_menu_pk = 9282 # 'Other (Smokeless Tobacco)' dummy_sky_for_menu_name = 'Menu POS (Scene Recognized Item)' location_type = 'Header' width = 1 center_x = mdis['x'].iloc[0] center_y = mdis['y'].iloc[0] relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width']] = \ [dummy_sku_for_menu_pk, dummy_sky_for_menu_name, center_x, center_y, location_type, width] relevant_pos = relevant_pos.sort_values( ['center_x'], ascending=True).reset_index(drop=True) return relevant_pos def get_menu_board_items(self, relevant_pos, longest_shelf, pos_mpis): # get the placeholder item menu_board_dummy = relevant_pos[relevant_pos['product_name'] == 'Menu POS (Scene Recognized Item)'] # if no placeholder, this function isn't relevant if menu_board_dummy.empty: return relevant_pos center_x = menu_board_dummy['center_x'].iloc[0] center_y = menu_board_dummy['center_y'].iloc[0] position = menu_board_dummy['position'].iloc[0] demarcation_line = longest_shelf['rect_y'].min() upper_demarcation_line = center_y - (demarcation_line - center_y) distance_in_facings = 2 try: left_bound = longest_shelf[ longest_shelf['rect_x'] < center_x].sort_values( by=['rect_x'], ascending=False)['rect_x'].iloc[int(distance_in_facings) - 1] except IndexError: # if there are no POS items found to the left of the 'Menu POS' scene recognition tag, use the tag itself # in theory this should never happen left_bound = center_x try: right_bound = longest_shelf[ longest_shelf['rect_x'] > center_x].sort_values( by=['rect_x'], ascending=True)['rect_x'].iloc[int(distance_in_facings) - 1] except IndexError: # if there are no POS items found to the right of the 'Menu POS' scene recognition tag, use the tag itself # this is more likely to happen for the right bound than the left bound right_bound = center_x pos_mpis = pos_mpis[(pos_mpis['rect_x'] > left_bound) & (pos_mpis['rect_x'] < right_bound) & (pos_mpis['rect_y'] > upper_demarcation_line) & (pos_mpis['rect_y'] < demarcation_line)] if pos_mpis.empty: return relevant_pos # remove the placeholder item relevant_pos = relevant_pos[~(relevant_pos['product_name'] == 'Menu POS (Scene Recognized Item)')] location_type = 'Header' width = 1 for row in pos_mpis.itertuples(): relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width', 'position']] = \ [row.product_fk, row.product_name, row.rect_x, row.rect_y, location_type, width, position] return relevant_pos @staticmethod def remove_duplicate_pos_tags(relevant_pos_df): duplicate_results = \ relevant_pos_df[relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)] duplicate_results_without_other = duplicate_results[ ~duplicate_results['product_name'].str.contains('Other')] results_without_duplicates = \ relevant_pos_df[~relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)] if duplicate_results_without_other.empty: return relevant_pos_df[~relevant_pos_df.duplicated( subset=['left_bound', 'right_bound'], keep='first')] else: results = pd.concat( [duplicate_results_without_other, results_without_duplicates]) # we need to sort_index to fix the sort order to reflect center_x values return results.drop_duplicates( subset=['left_bound', 'right_bound']).sort_index() def get_custom_entity_pk(self, name): return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0] def get_length_of_pos(self, row, longest_shelf, category): width_in_facings = len( longest_shelf[(longest_shelf['rect_x'] > row['left_bound']) & (longest_shelf['rect_x'] < row['right_bound'])]) + 2 category_facings = category + ' Facings' return self.facings_to_feet_template.loc[( self.facings_to_feet_template[category_facings] - width_in_facings).abs().argsort()[:1]]['POS Width (ft)'].iloc[0] @staticmethod def get_longest_shelf_number(relevant_mpis, max_shelves_from_top=3): # returns the shelf_number of the longest shelf try: longest_shelf = \ relevant_mpis[relevant_mpis['shelf_number'] <= max_shelves_from_top].groupby('shelf_number').agg( {'scene_match_fk': 'count'})['scene_match_fk'].idxmax() except ValueError: longest_shelf = pd.DataFrame() return longest_shelf @staticmethod def get_most_frequent_scene(relevant_scif): try: relevant_scene_id = relevant_scif['scene_id'].fillna( 0).mode().iloc[0] except IndexError: relevant_scene_id = 0 return relevant_scene_id def commit(self): self.common_v2.commit_results_data()
class PEPSICORUToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common_v1 = CommonV1(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.visit_type = self.store_info[ Const.ADDITIONAL_ATTRIBUTE_2].values[0] self.all_templates = self.data_provider[Data.ALL_TEMPLATES] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.scif = self.scif.loc[~(self.scif[Const.PRODUCT_TYPE] == Const.IRRELEVANT)] # Vitaly's request self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.k_engine = BaseCalculationsGroup(data_provider, output) self.toolbox = GENERALToolBox(data_provider) self.assortment = Assortment(self.data_provider, self.output, common=self.common_v1) if not self.scif.empty: self.pepsico_fk = self.get_relevant_pk_by_name( Const.MANUFACTURER, Const.PEPSICO) self.categories_to_calculate = self.get_relevant_categories_for_session( ) self.main_shelves = self.get_main_shelves() def main_calculation(self): """ This function calculates the KPI results. """ self.calculate_share_of_shelf() self.calculate_count_of_displays() self.calculate_assortment() def get_main_shelves(self): """ This function returns a list with the main shelves of this session """ main_shelves_template_groups = [ group for group in self.scif[Const.TEMPLATE_GROUP].unique().tolist() if Const.MAIN_SHELF in group.upper() ] main_shelves = self.scif[self.scif[Const.TEMPLATE_GROUP].isin( main_shelves_template_groups)][ Const.TEMPLATE_NAME].unique().tolist() return main_shelves def get_main_shelf_by_category(self, current_category): """ This function gets a category and return the relevant scene type for the SOS :param current_category: One of the product's categories. E.g: Snacks. :return: The relevant scene type to the current category """ main_shelves_for_category = [] for main_shelf in self.main_shelves: if current_category.upper() in main_shelf.upper(): main_shelves_for_category.append(main_shelf) return main_shelves_for_category @staticmethod def get_category_from_template_name(template_name): """ This function gets a template name (scene_type) and return it's relevant category. :param template_name: The scene type. :return: category name """ if Const.SNACKS.upper() in template_name.upper(): return Const.SNACKS elif Const.BEVERAGES.upper() in template_name.upper(): return Const.BEVERAGES elif Const.JUICES.upper() in template_name.upper(): return Const.JUICES else: Log.warning( "Couldn't find a matching category for template name = {}". format(template_name)) return None def get_relevant_categories_for_session(self): """ This function returns a list of the relevant categories according to the store type. The parameter additional_attribute_2 defines the visit type for each store. We have 3 types: Visit LRB (Beverages and Juices), Visit Snack and Visit (= All of them). The function is doing intersection between the categories in SCIF and the categories by store type. :return: List of the relevant categories """ categories_in_scif = self.scif[Const.CATEGORY].unique().tolist() if None in categories_in_scif: categories_in_scif.remove(None) if not categories_in_scif: Log.warning("No categories at scene item facts!") return [] store_type = self.store_info[Const.ADDITIONAL_ATTRIBUTE_2].values[0] if not store_type: Log.warning( "Invalid additional_attribute_2 for store id = {}".format( self.store_id)) return [] if Const.SNACKS.upper() in store_type.upper(): relevant_categories = [Const.SNACKS] elif Const.LRB.upper() in store_type.upper(): relevant_categories = [Const.JUICES, Const.BEVERAGES] else: relevant_categories = [Const.SNACKS, Const.JUICES, Const.BEVERAGES] categories_for_session = list( set(relevant_categories).intersection(set(categories_in_scif))) if not categories_for_session: Log.warning( "There aren't matching categories in scif for this store.") return categories_for_session def get_relevant_sub_categories_for_category(self, category): """ This function returns a list of the relevant categories according to the scene_types in the session :param category: The relevant category :return: List of the relevant sub categories for this category """ filtered_scif = self.scif.loc[ (self.scif[Const.CATEGORY] == category) & (self.scif[Const.MANUFACTURER_NAME] == Const.PEPSICO) & (self.scif[Const.TEMPLATE_NAME].isin(self.main_shelves))] sub_categories = filtered_scif[Const.SUB_CATEGORY].unique().tolist() if None in sub_categories: sub_categories.remove(None) if not sub_categories: Log.warning("No relevant sub categories for category = {}".format( category)) return sub_categories def get_relevant_brands_for_sub_category(self, sub_category): """ This function returns a list of the relevant categories according to the scene_types in the session :param sub_category: The relevant sub category :return: List of the relevant brands for this category """ filtered_scif = self.scif.loc[ (self.scif[Const.SUB_CATEGORY] == sub_category) & (self.scif[Const.MANUFACTURER_NAME] == Const.PEPSICO) & (self.scif[Const.TEMPLATE_NAME].isin(self.main_shelves))] brands_list = filtered_scif[Const.BRAND_NAME].unique().tolist() if None in brands_list: brands_list.remove(None) if not brands_list: Log.warning("No relevant brands for sub category = {}".format( sub_category)) return brands_list def get_relevant_pk_by_name(self, filter_by, filter_param): """ This function gets a filter name and returns the relevant pk. If the filter_by is equal to category it will be the field name because in SCIF there isn't category_name :param filter_by: filter by name E.g: 'category', 'brand'. :param filter_param: The param to filter by. E.g: if filter_by = 'category', filter_param could be 'Snack' :return: The relevant pk """ pk_field = filter_by + Const.FK field_name = filter_by + Const.NAME if Const.CATEGORY not in filter_by else filter_by return self.scif.loc[self.scif[field_name] == filter_param][pk_field].values[0] def get_target_for_count_of_displays(self): """ This function reads the project's template and returns the targets for all of the levels. It iterates over the relevant row and aggregate the result per level. :return: target_by_store (int), target_by_category (dict), target_by_scene (dict). Plus it return the scene_type list from the template """ targets = pd.read_excel(TEMPLATE_PATH).fillna(0) store_number_1 = self.store_info['store_number_1'].values[0] if not store_number_1: Log.warning("No valid store number 1 for store_fk = {}".format( self.store_id)) return targets = targets.loc[targets[Const.STORE_NUMBER_1] == store_number_1] has_targets = False store_target, category_targets, scene_targets, scene_types_from_template = None, None, None, None if not targets.empty: has_targets = True targets = targets.drop([Const.STORE_NAME, Const.STORE_NUMBER_1], axis=1) target_row = (targets.iloc[0])[( targets.iloc[0]) > 0] # Takes only the ones with target > 0 scene_types_from_template = targets.columns.tolist() scene_types_with_targets = target_row.keys().tolist() # Targets by store: store_target = target_row.sum() # Targets by category and scenes: category_targets = {key: 0 for key in self.categories_to_calculate} scene_targets = {key: 0 for key in scene_types_from_template} for scene_type in scene_types_with_targets: for category in category_targets: if category.upper() in scene_type.upper(): # category_targets[category] += 1 category_targets[category] += target_row[scene_type] scene_targets[scene_type] = target_row[scene_type] return has_targets, store_target, category_targets, scene_targets, scene_types_from_template def get_relevant_scene_types_from_list(self, scene_types_from_template): """ There's a gap between the actual name and the name is the template because of the visit type. So this function returns the a dictionary the narrows it. :param scene_types_from_template: Scene type list from the template :return: A dictionary where the keys are the names from the templates and values are the actual names """ scene_types_dict = dict.fromkeys(scene_types_from_template) relevant_templates_for_visit_type = self.all_templates.loc[( self.all_templates[Const.ADDITIONAL_ATTRIBUTE_2] == self.visit_type ) & (~self.all_templates[Const.TEMPLATE_NAME].isin(self.main_shelves) )][Const.TEMPLATE_NAME].unique().tolist() for scene_type in scene_types_from_template: for template in relevant_templates_for_visit_type: if scene_type.upper() in template.upper(): scene_types_dict[scene_type] = template # Remove irrelevant scene types from the dictionary for key in scene_types_dict.keys(): if not scene_types_dict[key]: del scene_types_dict[key] return scene_types_dict def calculate_count_of_displays(self): """ This function will calculate the Count of # of Pepsi Displays KPI :return: """ # Notice! store_target is Integer, scene_type_list is a list and the rest are dictionaries has_target, store_target, category_targets, scene_targets, scene_type_list = self.get_target_for_count_of_displays( ) if not store_target: Log.warning( "No targets were defined for this store (pk = {})".format( self.store_id)) return # Filtering out the main shelves if has_target: relevant_scenes_dict = self.get_relevant_scene_types_from_list( scene_type_list) relevant_template_name_list = relevant_scenes_dict.values() filtered_scif = self.scif.loc[self.scif[Const.TEMPLATE_NAME].isin( relevant_template_name_list)] display_count_store_level_fk = self.common.get_kpi_fk_by_kpi_type( Const.DISPLAY_COUNT_STORE_LEVEL) scene_types_in_store = len(filtered_scif[Const.SCENE_FK].unique()) identifier_parent_store_level = self.common.get_dictionary( kpi_fk=display_count_store_level_fk) count_store_level = 0 # Calculate count of display - category_level display_count_category_level_fk = self.common.get_kpi_fk_by_kpi_type( Const.DISPLAY_COUNT_CATEGORY_LEVEL) for category in self.categories_to_calculate: current_category_target = category_targets[category] if not current_category_target: continue category_fk = self.get_relevant_pk_by_name( Const.CATEGORY, category) relevant_scenes = [ scene_type for scene_type in relevant_template_name_list if category.upper() in scene_type.upper() ] filtered_scif_by_cat = filtered_scif.loc[filtered_scif[ Const.TEMPLATE_NAME].isin(relevant_scenes)] display_count_category_level_identifier = self.common.get_dictionary( kpi_fk=display_count_category_level_fk, category=category) # scene_types_in_cate = 0 # result_cat_level = 0 # if not filtered_scif_by_cat.empty: # scene_types_in_cate = len(filtered_scif_by_cat[Const.SCENE_FK].unique()) # result_cat_level = 1.0 if scene_types_in_cate >= current_category_target else scene_types_in_cate / float( # current_category_target) # self.common.write_to_db_result(fk=display_count_category_level_fk, numerator_id=self.pepsico_fk, # numerator_result=scene_types_in_cate, # denominator_id=category_fk, denominator_result=current_category_target, # identifier_result=display_count_category_level_identifier, # identifier_parent=identifier_parent_store_level, # result=result_cat_level, should_enter=True) scene_count_in_cate = 0 result_cat_level = 0 if not filtered_scif_by_cat.empty: actual_scene_names_in_cate = filtered_scif_by_cat[ Const.TEMPLATE_NAME].unique().tolist() reverse_scene_dict = {} for scene_type, actual_scene_name in relevant_scenes_dict.iteritems( ): for sc in actual_scene_names_in_cate: if actual_scene_name == sc: reverse_scene_dict[ actual_scene_name] = scene_type df = filtered_scif_by_cat[[ Const.TEMPLATE_NAME, 'scene_id' ]].drop_duplicates() df['scene_type'] = df[Const.TEMPLATE_NAME].apply( lambda x: reverse_scene_dict.get(x)) by_scene_count_in_cat = df.groupby(['scene_type']).count() for i, row in by_scene_count_in_cat.iterrows(): scene_count_in_cate += scene_targets[i] if row[Const.TEMPLATE_NAME]>=scene_targets[i] \ else row[Const.TEMPLATE_NAME] result_cat_level = 1.0 if scene_count_in_cate >= current_category_target else scene_count_in_cate / float( current_category_target) self.common.write_to_db_result( fk=display_count_category_level_fk, numerator_id=self.pepsico_fk, numerator_result=scene_count_in_cate, denominator_id=category_fk, denominator_result=current_category_target, identifier_result=display_count_category_level_identifier, identifier_parent=identifier_parent_store_level, result=result_cat_level, should_enter=True) # Calculate count of display - scene_level display_count_scene_level_fk = self.common.get_kpi_fk_by_kpi_type( Const.DISPLAY_COUNT_SCENE_LEVEL) for scene_type in relevant_scenes_dict.keys(): scene_type_target = scene_targets[scene_type] if not scene_type_target: continue actual_scene_name = relevant_scenes_dict[scene_type] relevant_category = self.get_category_from_template_name( actual_scene_name) relevant_category_fk = self.get_relevant_pk_by_name( Const.CATEGORY, relevant_category) scene_type_score = len( filtered_scif[filtered_scif[Const.TEMPLATE_NAME] == actual_scene_name][Const.SCENE_FK].unique()) result_scene_level = 1.0 if scene_type_score >= scene_type_target else scene_type_score / float( scene_type_target) scene_type_fk = self.all_templates.loc[self.all_templates[ Const.TEMPLATE_NAME] == actual_scene_name][ Const.TEMPLATE_FK].values[0] parent_identifier = self.common.get_dictionary( kpi_fk=display_count_category_level_fk, category=relevant_category) self.common.write_to_db_result( fk=display_count_scene_level_fk, numerator_id=self.pepsico_fk, numerator_result=scene_type_score, denominator_id=relevant_category_fk, denominator_result=scene_type_target, identifier_parent=parent_identifier, context_id=scene_type_fk, result=result_scene_level, should_enter=True) count_store_level += scene_type_target if scene_type_score >= scene_type_target else scene_type_score # Calculate count of display - store_level result_store_level = 1.0 if count_store_level >= store_target else count_store_level / float( store_target) self.common.write_to_db_result( fk=display_count_store_level_fk, numerator_id=self.pepsico_fk, numerator_result=count_store_level, denominator_id=self.store_id, denominator_result=store_target, identifier_result=identifier_parent_store_level, result=result_store_level, should_enter=True) # def calculate_count_of_displays_old(self): # """ # This function will calculate the Count of # of Pepsi Displays KPI # :return: # """ # # Notice! store_target is Integer, scene_type_list is a list and the rest are dictionaries # store_target, category_targets, scene_targets, scene_type_list = self.get_target_for_count_of_displays() # if not store_target: # Log.warning("No targets were defined for this store (pk = {})".format(self.store_id)) # return # # Filtering out the main shelves # relevant_scenes_dict = self.get_relevant_scene_types_from_list(scene_type_list) # relevant_template_name_list = relevant_scenes_dict.values() # filtered_scif = self.scif.loc[self.scif[Const.TEMPLATE_NAME].isin(relevant_template_name_list)] # # # Calculate count of display - store_level # display_count_store_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_STORE_LEVEL) # scene_types_in_store = len(filtered_scif[Const.SCENE_FK].unique()) # result_store_level = 100 if scene_types_in_store >= store_target else scene_types_in_store / float(store_target) # # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk, # # numerator_result=scene_types_in_store, # # denominator_id=self.store_id, denominator_result=store_target, # # identifier_result=display_count_store_level_fk, # # result=result_store_level, should_enter=True) # # identifier_parent_store_level = self.common.get_dictionary(kpi_fk=display_count_store_level_fk) # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk, # numerator_result=scene_types_in_store, # denominator_id=self.store_id, denominator_result=store_target, # identifier_result=identifier_parent_store_level, # result=result_store_level, should_enter=True) # # # Calculate count of display - category_level # display_count_category_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_CATEGORY_LEVEL) # for category in self.categories_to_calculate: # current_category_target = category_targets[category] # if not current_category_target: # continue # category_fk = self.get_relevant_pk_by_name(Const.CATEGORY, category) # relevant_scenes = [scene_type for scene_type in relevant_template_name_list if # category.upper() in scene_type.upper()] # filtered_scif_by_cat = filtered_scif.loc[filtered_scif[Const.TEMPLATE_NAME].isin(relevant_scenes)] # if filtered_scif_by_cat.empty: # continue # scene_types_in_cate = len(filtered_scif_by_cat[Const.SCENE_FK].unique()) # result_cat_level = 100 if scene_types_in_cate >= current_category_target else scene_types_in_cate / float( # current_category_target) # display_count_category_level_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk, # category=category) # # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk, # # numerator_result=scene_types_in_cate, # # denominator_id=category_fk, denominator_result=current_category_target, # # identifier_result=display_count_category_level_identifier, # # identifier_parent=display_count_category_level_fk, # # result=result_cat_level, should_enter=True) # self.common.write_to_db_result(fk=display_count_store_level_fk, numerator_id=self.pepsico_fk, # numerator_result=scene_types_in_cate, # denominator_id=category_fk, denominator_result=current_category_target, # identifier_result=display_count_category_level_identifier, # identifier_parent=identifier_parent_store_level, # result=result_cat_level, should_enter=True) # # # # Calculate count of display - scene_level # display_count_scene_level_fk = self.common.get_kpi_fk_by_kpi_type(Const.DISPLAY_COUNT_SCENE_LEVEL) # for scene_type in relevant_scenes_dict.keys(): # scene_type_target = scene_targets[scene_type] # if not scene_type_target: # continue # actual_scene_name = relevant_scenes_dict[scene_type] # relevant_category = self.get_category_from_template_name(actual_scene_name) # relevant_category_fk = self.get_relevant_pk_by_name(Const.CATEGORY, relevant_category) # scene_type_score = len( # filtered_scif[filtered_scif[Const.TEMPLATE_NAME] == actual_scene_name][Const.SCENE_FK].unique()) # # result_scene_level = 100 if scene_type_score >= scene_type_target else scene_types_in_store / float( # scene_type_target) # scene_type_fk = self.all_templates.loc[self.all_templates[Const.TEMPLATE_NAME] == actual_scene_name][ # Const.TEMPLATE_FK].values[0] # display_count_scene_level_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk, # category=relevant_category) # # parent_identifier = self.common.get_dictionary(kpi_fk=display_count_category_level_fk, # # category=relevant_category) # # self.common.write_to_db_result(fk=display_count_scene_level_fk, numerator_id=self.pepsico_fk, # # numerator_result=scene_types_in_store, # # denominator_id=relevant_category_fk, denominator_result=scene_type_target, # # identifier_result=display_count_scene_level_identifier, # # identifier_parent=parent_identifier, context_id=scene_type_fk, # # result=result_scene_level, should_enter=True) # # self.common.write_to_db_result(fk=display_count_scene_level_fk, numerator_id=self.pepsico_fk, # numerator_result=scene_types_in_store, # denominator_id=relevant_category_fk, denominator_result=scene_type_target, # identifier_result=display_count_scene_level_identifier, # identifier_parent=identifier_parent_store_level, context_id=scene_type_fk, # result=result_scene_level, should_enter=True) def calculate_assortment(self): lvl3_result = self.assortment.calculate_lvl3_assortment() # lvl3_result = self.get_lvl3_assortment_result_main_shelf() self.category_assortment_calculation(lvl3_result) self.store_assortment_calculation(lvl3_result) def get_lvl3_assortment_result_main_shelf(self): assortment_result = self.assortment.get_lvl3_relevant_ass() if not self.main_shelves and not assortment_result.empty: assortment_result.drop(assortment_result.index[0:], inplace=True) if assortment_result.empty: return assortment_result filters = {Const.TEMPLATE_NAME: self.main_shelves} filtered_scif = self.scif[self.toolbox.get_filter_condition( self.scif, **filters)] products_in_session = filtered_scif.loc[ filtered_scif['facings'] > 0]['product_fk'].values assortment_result.loc[ assortment_result['product_fk'].isin(products_in_session), 'in_store'] = 1 return assortment_result @log_runtime('Share of shelf pepsicoRU') def calculate_share_of_shelf(self): """ The function filters only the relevant scene (type = Main Shelf in category) and calculates the linear SOS and the facing SOS for each level (Manufacturer, Category, Sub-Category, Brand). The identifier for every kpi will be the current kpi_fk and the relevant attribute according to the level E.g sub_category_fk for level 3 or brand_fk for level 4. :return: """ # Get all of the KPI fk in advance facings_stores_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.FACINGS_MANUFACTURER_SOS) facings_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.FACINGS_CATEGORY_SOS) facings_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.FACINGS_SUB_CATEGORY_SOS) facings_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.FACINGS_BRAND_SOS) linear_store_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.LINEAR_MANUFACTURER_SOS) linear_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.LINEAR_CATEGORY_SOS) linear_sub_cat_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.LINEAR_SUB_CATEGORY_SOS) linear_brand_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.LINEAR_BRAND_SOS) filter_man_param = {Const.MANUFACTURER_NAME: Const.PEPSICO} general_filters = {Const.TEMPLATE_NAME: self.main_shelves} facings_level_1_identifier = self.common.get_dictionary( kpi_fk=facings_stores_kpi_fk) linear_level_1_identifier = self.common.get_dictionary( kpi_fk=linear_store_kpi_fk) num_facings = denom_facings = num_linear = denom_linear = result_facings = result_linear = 0 if self.main_shelves: num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos( sos_filters=filter_man_param, **general_filters) result_facings = num_facings / float( denom_facings) if denom_facings else 0 result_linear = num_linear / float( denom_linear) if denom_linear else 0 # Facings level 1 self.common.write_to_db_result( fk=facings_stores_kpi_fk, numerator_id=self.pepsico_fk, identifier_result=facings_level_1_identifier, numerator_result=num_facings, denominator_id=self.store_id, denominator_result=denom_facings, result=result_facings, should_enter=True) # Linear level 1 self.common.write_to_db_result( fk=linear_store_kpi_fk, numerator_id=self.pepsico_fk, identifier_result=linear_level_1_identifier, numerator_result=num_linear * 100, denominator_id=self.store_id, denominator_result=denom_linear * 100, result=result_linear, should_enter=True) for category in self.categories_to_calculate: current_category_fk = self.get_relevant_pk_by_name( Const.CATEGORY, category) main_shelves_for_category = self.get_main_shelf_by_category( category) if main_shelves_for_category: filter_params = { Const.CATEGORY: category, Const.TEMPLATE_NAME: main_shelves_for_category } facings_cat_identifier = self.common.get_dictionary( kpi_fk=facings_cat_kpi_fk, category_fk=current_category_fk) linear_cat_identifier = self.common.get_dictionary( kpi_fk=linear_cat_kpi_fk, category_fk=current_category_fk) num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos( sos_filters=filter_man_param, **filter_params) result_facings = num_facings / float( denom_facings) if denom_facings else 0 result_linear = num_linear / float( denom_linear) if denom_linear else 0 # Facings level 2 self.common.write_to_db_result( fk=facings_cat_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=num_facings, denominator_id=current_category_fk, denominator_result=denom_facings, identifier_result=facings_cat_identifier, identifier_parent=facings_level_1_identifier, result=result_facings, should_enter=True) # Linear level 2 self.common.write_to_db_result( fk=linear_cat_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=num_linear * 100, denominator_id=current_category_fk, denominator_result=denom_linear * 100, identifier_result=linear_cat_identifier, identifier_parent=linear_level_1_identifier, result=result_linear, should_enter=True) for sub_cat in self.get_relevant_sub_categories_for_category( category): current_sub_category_fk = self.get_relevant_pk_by_name( Const.SUB_CATEGORY, sub_cat) filter_sub_cat_param = { Const.SUB_CATEGORY: sub_cat, Const.CATEGORY: category, Const.TEMPLATE_NAME: main_shelves_for_category } facings_sub_cat_identifier = self.common.get_dictionary( kpi_fk=facings_sub_cat_kpi_fk, sub_category_fk=current_sub_category_fk) linear_sub_cat_identifier = self.common.get_dictionary( kpi_fk=linear_sub_cat_kpi_fk, sub_category_fk=current_sub_category_fk) num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos( sos_filters=filter_man_param, **filter_sub_cat_param) if denom_facings and denom_linear: # Facings level 3 self.common.write_to_db_result( fk=facings_sub_cat_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=num_facings, denominator_id=current_sub_category_fk, denominator_result=denom_facings, identifier_result=facings_sub_cat_identifier, identifier_parent=facings_cat_identifier, result=num_facings / float(denom_facings), should_enter=True) # Linear level 3 self.common.write_to_db_result( fk=linear_sub_cat_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=num_linear * 100, denominator_id=current_sub_category_fk, denominator_result=denom_linear * 100, identifier_result=linear_sub_cat_identifier, identifier_parent=linear_cat_identifier, result=num_linear / float(denom_linear), should_enter=True) for brand_name in self.get_relevant_brands_for_sub_category( sub_cat): current_brand_fk = self.get_relevant_pk_by_name( Const.BRAND, brand_name) filter_sos_brand = { Const.BRAND_NAME: brand_name, Const.SUB_CATEGORY: sub_cat, Const.MANUFACTURER_NAME: Const.PEPSICO } filter_general_brand_param = { Const.SUB_CATEGORY: sub_cat, Const.CATEGORY: category, Const.TEMPLATE_NAME: main_shelves_for_category } facings_brand_identifier = self.common.get_dictionary( kpi_fk=facings_brand_kpi_fk, brand_fk=current_brand_fk) linear_brand_identifier = self.common.get_dictionary( kpi_fk=linear_brand_kpi_fk, brand_fk=current_brand_fk) num_facings, denom_facings, num_linear, denom_linear = self.calculate_sos( sos_filters=filter_sos_brand, **filter_general_brand_param) if denom_facings and denom_linear: # Facings level 4 self.common.write_to_db_result( fk=facings_brand_kpi_fk, numerator_id=current_brand_fk, numerator_result=num_facings, denominator_id=current_sub_category_fk, denominator_result=denom_facings, identifier_result=facings_brand_identifier, identifier_parent= facings_sub_cat_identifier, result=num_facings / float(denom_facings), should_enter=True) # Linear level 4 self.common.write_to_db_result( fk=linear_brand_kpi_fk, numerator_id=current_brand_fk, numerator_result=num_linear * 100, denominator_id=current_sub_category_fk, denominator_result=denom_linear * 100, identifier_result=linear_brand_identifier, identifier_parent=linear_sub_cat_identifier, result=num_linear / float(denom_linear), should_enter=True) # Utils functions with a slight change from the SDK factory: def calculate_sos(self, sos_filters, include_empty=Const.EXCLUDE_EMPTY, **general_filters): """ :param sos_filters: These are the parameters on which ths SOS is calculated (out of the general DF). :param include_empty: This dictates whether Empty-typed SKUs are included in the calculation. :param general_filters: These are the parameters which the general data frame is filtered by. :return: The numerator facings, denominator facings, numerator linear and denominator linear. """ if include_empty == Const.EXCLUDE_EMPTY: general_filters[Const.PRODUCT_TYPE] = (Const.EMPTY, Const.EXCLUDE_FILTER) numerator_facings, numerator_linear = self.calculate_share_space( **dict(sos_filters, **general_filters)) denominator_facings, denominator_linear = self.calculate_share_space( **general_filters) return numerator_facings, denominator_facings, numerator_linear / 1000.0, denominator_linear / 1000.0 def calculate_share_space(self, **filters): """ :param filters: These are the parameters which the data frame is filtered by. :return: The total number of facings and the shelf width (in mm) according to the filters. """ filtered_scif = self.scif[self.toolbox.get_filter_condition( self.scif, **filters)] sum_of_facings = filtered_scif['facings'].sum() space_length = filtered_scif['net_len_ign_stack'].sum() return sum_of_facings, space_length def category_assortment_calculation(self, lvl3_result): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups """ osa_product_level_fk = self.common.get_kpi_fk_by_kpi_type( Const.OSA_SKU_LEVEL) oos_product_level_fk = self.common.get_kpi_fk_by_kpi_type( Const.OOS_SKU_LEVEL) osa_category_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.OSA_CATEGORY_LEVEL) oos_category_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.OOS_CATEGORY_LEVEL) if not lvl3_result.empty: 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 = Const.DISTRIBUTION else: score = Const.OOS # Distribution self.common_v1.write_to_db_result_new_tables( fk=osa_product_level_fk, 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 == Const.OOS: # OOS self.common_v1.write_to_db_result_new_tables( oos_product_level_fk, 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_fk_list = lvl3_with_cat['category_fk'].unique() for cat in category_fk_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)) # Distribution self.common_v1.write_to_db_result_new_tables( fk=osa_category_level_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=result.passes, denominator_id=cat, denominator_result=denominator_res, result=res, score=res, score_after_actions=res) # OOS self.common_v1.write_to_db_result_new_tables( fk=oos_category_level_kpi_fk, numerator_id=self.pepsico_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) self.assortment.LVL2_HEADERS.extend(['passes', 'total']) return def store_assortment_calculation(self, lvl3_result): """ This function calculates the KPI results. """ dist_store_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.OSA_STORE_LEVEL) oos_store_level_kpi_fk = self.common.get_kpi_fk_by_kpi_type( Const.OOS_STORE_LEVEL) # for result in lvl3_result.itertuples(): # if result.in_store == 1: # score = Const.DISTRIBUTION # else: # score = Const.OOS # # Distribution # self.common_v1.write_to_db_result_new_tables(fk=?????, numerator_id=result.product_fk, # numerator_result=score, # result=score, denominator_id=self.store_id, # denominator_result=1, score=score) # if score == Const.OOS: # # OOS # self.common_v1.write_to_db_result_new_tables(fk=?????, 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.visit_date: denominator_res = result.target res = np.divide(float(result.passes), float(denominator_res)) # Distribution self.common_v1.write_to_db_result_new_tables( fk=dist_store_level_kpi_fk, numerator_id=self.pepsico_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_v1.write_to_db_result_new_tables( fk=oos_store_level_kpi_fk, numerator_id=self.pepsico_fk, numerator_result=denominator_res - result.passes, denominator_id=self.store_id, denominator_result=denominator_res, result=1 - res, score=1 - res, score_after_actions=1 - res) return
class PERFETTICNToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_new_kpi_static_data() self.kpi_results_queries = [] self.store_info = self.data_provider[Data.STORE_INFO] self.assortment = Assortment(self.data_provider, self.output) self.template = self.data_provider.all_templates def main_calculation(self, *args, **kwargs): self.display_count() self.assortment_calculation() self.common.commit_results_data_to_new_tables() def display_count(self): """This function calculates how many displays find from types secondary shelf """ num_brands = {} display_info = self.scif['template_fk'] display_fks = display_info.unique() template_seco = self.template[ self.template['included_in_secondary_shelf_report'] == 'Y']['template_fk'] display_fks = list( filter(lambda x: x in template_seco.values, display_fks)) count_fk = self.kpi_static_data[self.kpi_static_data['client_name'] == 'COUNT OF DISPLAY']['pk'].iloc[0] for value in display_fks: num_brands[value] = display_info[display_info == value].count() score = num_brands[value] self.common.write_to_db_result_new_tables(count_fk, value, None, score, score, score, score) return def assortment_calculation(self): """ This function calculates 3 levels of assortment : level3 is assortment SKU level2 is assortment groups level1 how many groups passed out of all """ lvl3_result = self.assortment.calculate_lvl3_assortment() for result in lvl3_result.itertuples(): score = result.in_store if score >= 1: score = 100 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl3, result.product_fk, result.in_store, score, result.assortment_group_fk, 1, score) if not lvl3_result.empty: lvl2_result = self.assortment.calculate_lvl2_assortment( lvl3_result) for result in lvl2_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) if result.passes >= 1: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( result.kpi_fk_lvl2, result.assortment_group_fk, result.passes, (res * 100), result.assortment_super_group_fk, denominator_res, score) if not lvl2_result.empty: lvl1_result = self.assortment.calculate_lvl1_assortment( lvl2_result) for result in lvl1_result.itertuples(): denominator_res = result.total res = np.divide(float(result.passes), float(denominator_res)) if res >= 0: score = 100 else: score = 0 self.common.write_to_db_result_new_tables( fk=result.kpi_fk_lvl1, numerator_id=result.assortment_super_group_fk, numerator_result=result.passes, denominator_result=denominator_res, result=(res * 100), score=score) return
class MOLSONCOORSHR_SANDToolBox: def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.current_date = datetime.now() self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_info = self.data_provider[Data.STORE_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.own_manufacturer_id = int(self.data_provider[Data.OWN_MANUFACTURER][self.data_provider[Data.OWN_MANUFACTURER]['param_name'] == 'manufacturer_id']['param_value'].tolist()[0]) self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.toolbox = GENERALToolBox(data_provider) self.assortment = Assortment(self.data_provider, self.output, common=self.common) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.template_path = self.get_template_path() self.template_data = self.get_template_data() self.sos_store_policies = self.get_sos_store_policies(self.visit_date.strftime('%Y-%m-%d')) self.result_values = self.get_result_values() self.scores = pd.DataFrame() def get_sos_store_policies(self, visit_date): query = MOLSONCOORSHR_SANDQueries.get_sos_store_policies(visit_date) store_policies = pd.read_sql_query(query, self.rds_conn.db) return store_policies def get_result_values(self): query = MOLSONCOORSHR_SANDQueries.get_result_values() result_values = pd.read_sql_query(query, self.rds_conn.db) return result_values def main_calculation(self): """ This function starts the KPI results calculation. """ if not self.template_data or KPIS_TEMPLATE_SHEET not in self.template_data: Log.error('KPIs template sheet is empty or not found') return self.kpis_calculation() self.common.commit_results_data() def kpis_calculation(self, kpi_group=''): """ This is a recursive function. The function calculates each level KPI cascading from the highest level to the lowest. """ total_score = total_potential_score = total_calculated = 0 kpis = self.template_data['KPIs'][self.template_data['KPIs']['KPI Group'] == kpi_group] for index, kpi in kpis.iterrows(): child_kpi_group = kpi['Child KPI Group'] kpi_type = kpi['KPI Type'].lower() score_function = kpi['Score Function'].lower() if not child_kpi_group: if kpi_type in [LINEAR_SOS_VS_TARGET, FACINGS_SOS_VS_TARGET]: score, potential_score, calculated = self.calculate_sos_vs_target(kpi) elif kpi_type in [FACINGS_VS_TARGET, DISTRIBUTION]: score, potential_score, calculated = self.calculate_assortment_vs_target(kpi) else: Log.error("KPI of type '{}' is not supported".format(kpi_type)) score = potential_score = calculated = 0 else: score, potential_score, calculated = self.kpis_calculation(child_kpi_group) if score_function in [WEIGHTED_SCORE, SUM_OF_SCORES]: total_score += score total_potential_score += potential_score total_calculated += calculated else: total_score += 0 total_potential_score += 0 total_calculated += 0 if child_kpi_group and calculated: if kpi['KPI name Eng'] == 'Store Score': kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0 numerator_id = self.own_manufacturer_id denominator_id = self.store_id identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=0, denominator_id=denominator_id, denominator_result=0, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True ) else: kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0 numerator_id = self.own_manufacturer_id denominator_id = self.store_id identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=0, denominator_id=denominator_id, denominator_result=0, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_result, identifier_parent=identifier_parent, should_enter=True ) return total_score, total_potential_score, total_calculated @kpi_runtime() def calculate_assortment_vs_target(self, kpi): """ The function filters only the relevant scenes by Location Type and calculates the Assortment scores according to rules set in the target. :return: """ lvl3_result = self.calculate_assortment_vs_target_lvl3(kpi) for row in lvl3_result.itertuples(): numerator_id = row.product_fk numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings denominator_id = self.store_id denominator_result = row.target # denominator_result_after_actions = 0 if row.target < row.facings else row.target - row.facings if kpi['KPI Type'] == 'Distribution': if row.result_distributed: result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') & (self.result_values['result_value'] == 'DISTRIBUTED')]['result_value_fk'].tolist()[0] score = 100 else: result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') & (self.result_values['result_value'] == 'OOS')]['result_value_fk'].tolist()[0] score = 0 else: result = row.result_facings score = round(result*100, 0) identifier_details = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl3) identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2) self.common.write_to_db_result(fk=row.kpi_fk_lvl3, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, # denominator_result_after_actions=denominator_result_after_actions, result=result, score=score, identifier_result=identifier_details, identifier_parent=identifier_kpi, should_enter=True ) score = potential_score = 0 if not lvl3_result.empty: lvl2_result = self.calculate_assortment_vs_target_lvl2(lvl3_result) for row in lvl2_result.itertuples(): numerator_id = self.own_manufacturer_id numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings denominator_id = self.store_id denominator_result = row.target result = row.result_distributed if kpi['KPI Type'] == 'Distribution' else row.result_facings score += self.score_function(result*100, kpi) potential_score += round(float(kpi['Weight'])*100, 0) identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2) identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group'])) self.common.write_to_db_result(fk=row.kpi_fk_lvl2, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, result=score, score=score, weight=potential_score, target=potential_score, identifier_result=identifier_kpi, identifier_parent=identifier_parent, should_enter=True ) if len(lvl3_result) > 0: calculated = 1 else: calculated = 0 return score, potential_score, calculated def calculate_assortment_vs_target_lvl3(self, kpi): location_types = kpi['Location Type'].split(', ') kpi_fk_lvl3 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'] + ' - SKU') kpi_fk_lvl2 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng']) assortment_result = self.assortment.get_lvl3_relevant_ass() if assortment_result.empty: return assortment_result assortment_result = assortment_result[(assortment_result['kpi_fk_lvl3'] == kpi_fk_lvl3) & (assortment_result['kpi_fk_lvl2'] == kpi_fk_lvl2)] if assortment_result.empty: return assortment_result assortment_result['target'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Target'), axis=1) assortment_result['target'] = assortment_result['target'].fillna(0) assortment_result = assortment_result[assortment_result['target'] > 0] assortment_result['weight'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Weight'), axis=1) assortment_result['weight'] = assortment_result['weight'].fillna(0) assortment_total_weights = assortment_result[['assortment_fk', 'weight']].groupby('assortment_fk').agg({'weight': 'sum'}).reset_index() assortment_result = assortment_result.merge(assortment_total_weights, how='left', left_on='assortment_fk', right_on='assortment_fk', suffixes=['', '_total']) facings = 'facings_ign_stack' if kpi['Ignore Stacking'] else 'facings' products_in_session = self.scif[(self.scif[facings] > 0) & (self.scif['location_type'].isin(location_types))][['product_fk', 'facings']]\ .groupby('product_fk').agg({'facings': 'sum'}).reset_index() lvl3_result = assortment_result.merge(products_in_session, how='left', left_on='product_fk', right_on='product_fk') lvl3_result['facings'] = lvl3_result['facings'].fillna(0) lvl3_result['distributed'] = lvl3_result.apply(lambda x: 1 if x['facings'] else 0, axis=1) lvl3_result['result_facings'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'facings'), axis=1) lvl3_result['result_distributed'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'distributed'), axis=1) return lvl3_result def calculate_assortment_vs_target_lvl2(self, lvl3_result): lvl2_result = lvl3_result.groupby(['kpi_fk_lvl2', self.assortment.ASSORTMENT_FK, self.assortment.ASSORTMENT_GROUP_FK])\ .agg({'facings': 'sum', 'distributed': 'sum', 'target': 'sum', 'result_facings': 'sum', 'result_distributed': 'sum'}).reset_index() return lvl2_result @staticmethod def assortment_vs_target_result(x, y): if x[y] < x['target']: return round(x[y] / float(x['target']) * float(x['weight']) / x['weight_total'], 5) else: return round(1 * float(x['weight']) / x['weight_total'], 5) @kpi_runtime() def calculate_sos_vs_target(self, kpi): """ The function filters only the relevant scenes by Location Type and calculates the linear SOS and the facing SOS according to Manufacturer and Category set in the target. :return: """ location_type = kpi['Location Type'] kpi_fk = self.common.get_kpi_fk_by_kpi_type(SOS_MANUFACTURER_CATEGORY + ('_' + location_type if location_type else '')) sos_store_policies = self.sos_store_policies[self.sos_store_policies['kpi_fk'] == str(kpi_fk)] sos_store_policy = None store_policy_passed = 0 for index, policy in sos_store_policies.iterrows(): sos_store_policy = policy store_policy = json.loads(policy['store_policy']) store_policy_passed = 1 for key in store_policy.keys(): if key in self.store_info.columns.tolist(): if self.store_info[key][0] in store_policy[key]: continue else: store_policy_passed = 0 break else: Log.error("Store Policy attribute is not found: '{}'").format(key) store_policy_passed = 0 break if store_policy_passed: break score = potential_score = 0 if store_policy_passed: general_filters = {LOCATION_TYPE: location_type} sos_policy = json.loads(sos_store_policy['sos_policy']) numerator_sos_filters = {MANUFACTURER_NAME: sos_policy[NUMERATOR][MANUFACTURER], CATEGORY: sos_policy[DENOMINATOR][CATEGORY]} denominator_sos_filters = {CATEGORY: sos_policy[DENOMINATOR][CATEGORY]} numerator_id = self.all_products.loc[self.all_products[MANUFACTURER_NAME] == sos_policy[NUMERATOR][MANUFACTURER]][MANUFACTURER + '_fk'].values[0] denominator_id = self.all_products.loc[self.all_products[CATEGORY] == sos_policy[DENOMINATOR][CATEGORY]][CATEGORY + '_fk'].values[0] ignore_stacking = kpi['Ignore Stacking'] if kpi['Ignore Stacking'] else 0 numer_facings, numer_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(numerator_sos_filters, **general_filters)) denom_facings, denom_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(denominator_sos_filters, **general_filters)) if kpi['KPI Type'].lower() == LINEAR_SOS_VS_TARGET: numerator_result = round(numer_linear, 0) denominator_result = round(denom_linear, 0) result = numer_linear / float(denom_linear) if denom_linear else 0 elif kpi['KPI Type'].lower() == FACINGS_SOS_VS_TARGET: numerator_result = numer_facings denominator_result = denom_facings result = numer_facings / float(denom_facings) if denom_facings else 0 else: Log.error("KPI Type is invalid: '{}'").format(kpi['KPI Type']) numerator_result = denominator_result = result = 0 if sos_store_policy['target']: sos_target = round(float(sos_store_policy['target'])*100, 0) else: Log.error("SOS target is not set for Store ID {}").format(self.store_id) sos_target = 0 result_vs_target = result/(float(sos_target)/100)*100 if sos_target else 0 score = self.score_function(result_vs_target, kpi) potential_score = round(float(kpi['Weight'])*100, 0) identifier_kpi = self.common.get_dictionary(kpi_fk=kpi_fk) identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group'])) self.common.write_to_db_result(fk=kpi_fk, numerator_id=numerator_id, numerator_result=numerator_result, denominator_id=denominator_id, denominator_result=denominator_result, result=result, score=score, weight=potential_score, target=sos_target, identifier_result=identifier_kpi, identifier_parent=identifier_parent, should_enter=True ) else: Log.warning("Store Policy is not found for Store ID {}".format(self.store_id)) return score, potential_score, store_policy_passed def calculate_share_space(self, ignore_stacking=1, **filters): """ :param filters: These are the parameters which the data frame is filtered by. :param ignore_stacking: 1 is to ignore stacking. :return: The total number of facings and the shelf width (in mm) according to the filters. """ filtered_scif = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)] if ignore_stacking: sum_of_facings = filtered_scif['facings_ign_stack'].sum() space_length = filtered_scif['net_len_ign_stack'].sum() else: sum_of_facings = filtered_scif['facings'].sum() space_length = filtered_scif['net_len_ign_stack'].sum() return sum_of_facings, space_length @staticmethod def get_template_path(): return os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data', KPIS_TEMPLATE_NAME) def get_template_data(self): template_data = {} try: sheet_names = pd.ExcelFile(self.template_path).sheet_names for sheet in sheet_names: template_data[sheet] = parse_template(self.template_path, sheet, lower_headers_row_index=0) except IOError as e: Log.error('Template {} does not exist. {}'.format(KPIS_TEMPLATE_NAME, repr(e))) return template_data @staticmethod def score_function(score, kpi): weight = float(kpi['Weight']) if kpi['Weight'] else 1 score_function = kpi['Score Function'].lower() l_threshold = float(kpi['Lower Threshold'])*100 if kpi['Lower Threshold'] else 0 h_threshold = float(kpi['Higher Threshold'])*100 if kpi['Higher Threshold'] else 100 if score < l_threshold: score = 0 elif score >= h_threshold: score = 100 if score_function in [WEIGHTED_SCORE]: score = round(score*weight, 0) else: score = round(score, 0) return score
class PepsicoUtil(UnifiedKPISingleton): LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 EXCLUSION_TEMPLATE_PATH = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', 'Inclusion_Exclusion_Template_Rollout.xlsx') ADDITIONAL_DISPLAY = 'additional display' INCLUDE_EMPTY = True EXCLUDE_EMPTY = False OPERATION_TYPES = [] SOS_VS_TARGET = 'SOS vs Target' HERO_SKU_SPACE_TO_SALES_INDEX = 'Hero SKU Space to Sales Index' HERO_SKU_SOS_VS_TARGET = 'Hero SKU SOS vs Target' LINEAR_SOS_INDEX = 'Linear SOS Index' PEPSICO = 'PEPSICO' SHELF_PLACEMENT = 'Shelf Placement' HERO_SKU_PLACEMENT_TOP = 'Hero SKU Placement by shelf numbers_Top' HERO_PLACEMENT = 'Hero Placement' HERO_SKU_STACKING = 'Hero SKU Stacking' HERO_SKU_PRICE = 'Hero SKU Price' HERO_SKU_PROMO_PRICE = 'Hero SKU Promo Price' BRAND_FULL_BAY_KPIS = ['Brand Full Bay_90', 'Brand Full Bay'] BRAND_FULL_BAY = 'Brand Full Bay' HERO_PREFIX = 'Hero SKU' ALL = 'ALL' HERO_SKU_OOS_SKU = 'Hero SKU OOS - SKU' HERO_SKU_OOS = 'Hero SKU OOS' HERO_SKU_AVAILABILITY = 'Hero SKU Availability' BRAND_SPACE_TO_SALES_INDEX = 'Brand Space to Sales Index' BRAND_SPACE_SOS_VS_TARGET = 'Brand Space SOS vs Target' SUB_BRAND_SPACE_TO_SALES_INDEX = 'Sub Brand Space to Sales Index' SUB_BRAND_SPACE_SOS_VS_TARGET = 'Sub Brand Space SOS vs Target' PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Segment Space to Sales Index' PEPSICO_SEGMENT_SOS_VS_TARGET = 'PepsiCo Segment SOS vs Target' PEPSICO_SUB_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Sub Segment Space to Sales Index' PEPSICO_SUB_SEGMENT_SOS_VS_TARGET = 'PepsiCo Sub Segment SOS vs Target' PLACEMENT_BY_SHELF_NUMBERS_TOP = 'Placement by shelf numbers_Top' TOTAL_LINEAR_SPACE = 'Total Linear Space' NUMBER_OF_FACINGS = 'Number of Facings' NUMBER_OF_BAYS = 'Number of bays' NUMBER_OF_SHELVES = 'Number of shelves' PRODUCT_BLOCKING = 'Product Blocking' PRODUCT_BLOCKING_ADJACENCY = 'Product Blocking Adjacency' SHELF_PLACEMENT_VERTICAL_LEFT = 'Shelf Placement Vertical_Left' SHELF_PLACEMENT_VERTICAL_CENTER = 'Shelf Placement Vertical_Center' SHELF_PLACEMENT_VERTICAL_RIGHT = 'Shelf Placement Vertical_Right' NUMBER_OF_SHELVES_TEMPL_COLUMN = 'No of Shelves in Fixture (per bay) (key)' RELEVANT_SHELVES_TEMPL_COLUMN = 'Shelves From Bottom To Include (data)' SHELF_PLC_TARGETS_COLUMNS = [ 'kpi_operation_type_fk', 'operation_type', 'kpi_level_2_fk', 'type', NUMBER_OF_SHELVES_TEMPL_COLUMN, RELEVANT_SHELVES_TEMPL_COLUMN, 'KPI Parent' ] SHELF_PLC_TARGET_COL_RENAME = { 'kpi_operation_type_fk_x': 'kpi_operation_type_fk', 'operation_type_x': 'operation_type', 'kpi_level_2_fk_x': 'kpi_level_2_fk', 'type_x': 'type', NUMBER_OF_SHELVES_TEMPL_COLUMN + '_x': NUMBER_OF_SHELVES_TEMPL_COLUMN, RELEVANT_SHELVES_TEMPL_COLUMN + '_x': RELEVANT_SHELVES_TEMPL_COLUMN, 'KPI Parent_x': 'KPI Parent' } HERO_SKU_AVAILABILITY_SKU = 'Hero SKU Availability - SKU' HERO_SKU_PLACEMENT_BY_SHELF_NUMBERS = 'Hero SKU Placement by shelf numbers' HERO_SKU_AVAILABILITY_BY_HERO_TYPE = 'Hero SKU Availability by Hero Type' SHARE_OF_ASSORTMENT_BY_HERO_TYPE = 'Share of Assortment by Hero Type' HERO_SKU_LABEL = 'Hero SKU' HERO_TYPE = 'hero_type' HERO_SKU_SOS_OF_CAT_BY_HERO_TYPE = 'Hero SKU SOS of Category by Hero Type' CATEGORY_FULL_BAY = 'Category Full Bay' CSN = 'CSN' PRICE = 'Price' PROMO_PRICE = 'Promo Price' LINEAR_SPACE_PER_PRODUCT = 'Linear Space Per Product' FACINGS_PER_PRODUCT = 'Facings per Product' PRICE_SCENE = 'Price Scene' PROMO_PRICE_SCENE = 'Promo Price Scene' HERO_SKU_SOS = 'Hero SKU SOS' BRAND_SOS = 'Brand SOS' SUB_BRAND_SOS = 'Sub Brand SOS' PEPSICO_SEGMENT_SOS = 'PepsiCo Segment SOS' BRAND_SOS_OF_SEGMENT = 'Brand SOS of Segment' BINS_NOT_RECOGNIZED = 'Bins_not_recognized' def __init__(self, output, data_provider): super(PepsicoUtil, self).__init__(data_provider) self.output = output self.common = Common(self.data_provider) # self.common_v1 = CommonV1(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] if self.data_provider[Data.STORE_FK] is not None \ else self.session_info['store_fk'].values[0] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.display_scene = self.get_match_display_in_scene() self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.probe_groups = self.get_probe_group() self.match_product_in_scene = self.match_product_in_scene.merge( self.probe_groups, on='probe_match_fk', how='left') self.toolbox = GENERALToolBox(self.data_provider) self.commontools = PEPSICOUKCommonToolBox(self.data_provider, self.rds_conn) self.all_templates = self.commontools.all_templates self.custom_entities = self.commontools.custom_entities self.on_display_products = self.commontools.on_display_products self.exclusion_template = self.commontools.exclusion_template self.filtered_scif = self.commontools.filtered_scif.copy() self.filtered_matches = self.commontools.filtered_matches.copy() self.filtered_matches = self.filtered_matches.merge( self.probe_groups, on='probe_match_fk', how='left') self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy( ) self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy( ) self.scene_bay_shelf_product = self.commontools.scene_bay_shelf_product self.ps_data = PsDataProvider(self.data_provider, self.output) self.full_store_info = self.commontools.full_store_info.copy() self.external_targets = self.commontools.external_targets self.assortment = Assortment(self.commontools.data_provider, self.output) self.lvl3_ass_result = self.get_lvl3_relevant_assortment_result() self.own_manuf_fk = self.all_products[ self.all_products['manufacturer_name'] == self.PEPSICO]['manufacturer_fk'].values[0] self.scene_kpi_results = self.get_results_of_scene_level_kpis() self.kpi_results_check = pd.DataFrame(columns=[ 'kpi_fk', 'numerator', 'denominator', 'result', 'score', 'context' ]) self.sos_vs_target_targets = self.construct_sos_vs_target_base_df() self.all_targets_unpacked = self.commontools.all_targets_unpacked.copy( ) self.block_results = pd.DataFrame(columns=['Group Name', 'Score']) self.hero_type_custom_entity_df = self.get_hero_type_custom_entity_df() def get_match_display_in_scene(self): query = PEPSICOUK_Queries.get_match_display(self.session_uid) match_display = pd.read_sql_query(query, self.rds_conn.db) return match_display def get_probe_group(self): query = PEPSICOUK_Queries.get_probe_group(self.session_uid) probe_group = pd.read_sql_query(query, self.rds_conn.db) return probe_group @staticmethod def get_full_bay_and_positional_filters(parameters): filters = {parameters['Parameter 1']: parameters['Value 1']} if parameters['Parameter 2']: filters.update({parameters['Parameter 2']: parameters['Value 2']}) if parameters['Parameter 3']: filters.update({parameters['Parameter 3']: parameters['Value 3']}) return filters # @staticmethod # def get_stack_data(row): # is_stack = False # sequences_list = row['all_sequences'][0:-1].split(',') # count_sequences = collections.Counter(sequences_list) # repeating_items = [c > 1 for c in count_sequences.values()] # if repeating_items: # if any(repeating_items): # is_stack = True # return is_stack @staticmethod def split_and_strip(value): return map(lambda x: x.strip(), str(value).split(',')) def construct_sos_vs_target_base_df(self): sos_targets = self.get_relevant_sos_vs_target_kpi_targets() sos_targets = sos_targets.drop_duplicates(subset=[ 'kpi_operation_type_fk', 'kpi_level_2_fk', 'numerator_value', 'denominator_value', 'type' ], keep='first') sos_targets = sos_targets.drop( ['key_json', 'data_json', 'start_date', 'end_date'], axis=1) if not sos_targets.empty: sos_targets['numerator_id'] = sos_targets.apply( self.retrieve_relevant_item_pks, axis=1, args=('numerator_type', 'numerator_value')) sos_targets['denominator_id'] = sos_targets.apply( self.retrieve_relevant_item_pks, axis=1, args=('denominator_type', 'denominator_value')) sos_targets['identifier_parent'] = sos_targets['KPI Parent'].apply( lambda x: self.common.get_dictionary(kpi_fk=int(float(x)))) return sos_targets def get_relevant_sos_vs_target_kpi_targets(self, brand_vs_brand=False): sos_vs_target_kpis = self.external_targets[ self.external_targets['operation_type'] == self.SOS_VS_TARGET] sos_vs_target_kpis = sos_vs_target_kpis.drop_duplicates(subset=[ 'operation_type', 'kpi_level_2_fk', 'key_json', 'data_json' ]) relevant_targets_df = pd.DataFrame( columns=sos_vs_target_kpis.columns.values.tolist()) if not sos_vs_target_kpis.empty: policies_df = self.commontools.unpack_external_targets_json_fields_to_df( sos_vs_target_kpis, field_name='key_json') policy_columns = policies_df.columns.values.tolist() del policy_columns[policy_columns.index('pk')] store_dict = self.full_store_info.to_dict('records')[0] for column in policy_columns: store_att_value = store_dict.get(column) policies_df = policies_df[policies_df[column].isin( [store_att_value, self.ALL])] kpi_targets_pks = policies_df['pk'].values.tolist() relevant_targets_df = sos_vs_target_kpis[ sos_vs_target_kpis['pk'].isin(kpi_targets_pks)] # relevant_targets_df = relevant_targets_df.merge(policies_df, on='pk', how='left') data_json_df = self.commontools.unpack_external_targets_json_fields_to_df( relevant_targets_df, 'data_json') relevant_targets_df = relevant_targets_df.merge(data_json_df, on='pk', how='left') kpi_data = self.kpi_static_data[['pk', 'type']].drop_duplicates() kpi_data.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True) relevant_targets_df = relevant_targets_df.merge( kpi_data, left_on='kpi_level_2_fk', right_on='kpi_level_2_fk', how='left') linear_sos_fk = self.common.get_kpi_fk_by_kpi_type( self.LINEAR_SOS_INDEX) if brand_vs_brand: relevant_targets_df = relevant_targets_df[ relevant_targets_df['KPI Parent'] == linear_sos_fk] else: relevant_targets_df = relevant_targets_df[~( relevant_targets_df['KPI Parent'] == linear_sos_fk)] return relevant_targets_df def retrieve_relevant_item_pks(self, row, type_field_name, value_field_name): try: if row[type_field_name].endswith("_fk"): item_id = row[value_field_name] else: # print row[type_field_name], ' :', row[value_field_name] item_id = self.custom_entities[ self.custom_entities['name'] == row[value_field_name]]['pk'].values[0] except KeyError as e: Log.error('No id found for field {}. Error: {}'.format( row[type_field_name], e)) item_id = None return item_id def calculate_sos(self, sos_filters, **general_filters): numerator_linear = self.calculate_share_space( **dict(sos_filters, **general_filters)) denominator_linear = self.calculate_share_space(**general_filters) return float(numerator_linear), float(denominator_linear) def calculate_share_space(self, **filters): filtered_scif = self.filtered_scif[self.toolbox.get_filter_condition( self.filtered_scif, **filters)] space_length = filtered_scif['updated_gross_length'].sum() return space_length def add_kpi_result_to_kpi_results_df(self, result_list): self.kpi_results_check.loc[len(self.kpi_results_check)] = result_list def get_results_of_scene_level_kpis(self): scene_kpi_results = pd.DataFrame() if not self.scene_info.empty: scene_kpi_results = self.ps_data.get_scene_results( self.scene_info['scene_fk'].drop_duplicates().values) return scene_kpi_results def get_store_data_by_store_id(self): store_id = self.store_id if self.store_id else self.session_info[ 'store_fk'].values[0] query = PEPSICOUK_Queries.get_store_data_by_store_id(store_id) query_result = pd.read_sql_query(query, self.rds_conn.db) return query_result def get_facings_scene_bay_shelf_product(self): self.filtered_matches['count'] = 1 aggregate_df = self.filtered_matches.groupby( ['scene_fk', 'bay_number', 'shelf_number', 'product_fk'], as_index=False).agg({'count': np.sum}) return aggregate_df def get_lvl3_relevant_assortment_result(self): assortment_result = self.assortment.get_lvl3_relevant_ass() # if assortment_result.empty: # return assortment_result # products_in_session = self.filtered_scif.loc[self.filtered_scif['facings'] > 0]['product_fk'].values # assortment_result.loc[assortment_result['product_fk'].isin(products_in_session), 'in_store'] = 1 return assortment_result @staticmethod def get_block_and_adjacency_filters(target_series): filters = {target_series['Parameter 1']: target_series['Value 1']} if target_series['Parameter 2']: filters.update( {target_series['Parameter 2']: target_series['Value 2']}) if target_series['Parameter 3']: filters.update( {target_series['Parameter 3']: target_series['Value 3']}) return filters @staticmethod def get_block_filters(target_series): if isinstance(target_series['Value 1'], list): filters = {target_series['Parameter 1']: target_series['Value 1']} else: filters = { target_series['Parameter 1']: [target_series['Value 1']] } if target_series['Parameter 2']: if isinstance(target_series['Value 2'], list): filters.update( {target_series['Parameter 2']: target_series['Value 2']}) else: filters.update( {target_series['Parameter 2']: [target_series['Value 2']]}) if target_series['Parameter 3']: if isinstance(target_series['Value 2'], list): filters.update( {target_series['Parameter 3']: target_series['Value 3']}) else: filters.update( {target_series['Parameter 3']: [target_series['Value 3']]}) return filters def reset_filtered_scif_and_matches_to_exclusion_all_state(self): self.filtered_scif = self.commontools.filtered_scif.copy() self.filtered_matches = self.commontools.filtered_matches.copy() def reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(self): self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy( ) self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy( ) def get_available_hero_sku_list(self, dependencies_df): hero_list = dependencies_df[ (dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU) & (dependencies_df['numerator_result'] == 1 )]['numerator_id'].unique().tolist() return hero_list def get_unavailable_hero_sku_list(self, dependencies_df): hero_list = dependencies_df[ (dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU) & (dependencies_df['numerator_result'] == 0 )]['numerator_id'].unique().tolist() return hero_list def get_hero_type_custom_entity_df(self): hero_type_df = self.custom_entities[self.custom_entities['entity_type'] == self.HERO_TYPE] hero_type_df.rename(columns={'pk': 'entity_fk'}, inplace=True) return hero_type_df
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