class NumberOfBaysKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(NumberOfBaysKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: bays_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.NUMBER_OF_BAYS) matches = self.util.match_product_in_scene[~( self.util.match_product_in_scene['bay_number'] == -1)] bays_in_scene = matches['bay_number'].unique().tolist() bays_num = len(bays_in_scene) self.write_to_db_result(fk=bays_kpi_fk, numerator_id=self.util.own_manuf_fk, result=bays_num, denominator_id=self.util.store_id, by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ bays_kpi_fk, self.util.own_manuf_fk, self.util.store_id, bays_num, None ])
class NumberOfShelvesKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(NumberOfShelvesKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.NUMBER_OF_SHELVES) matches = self.util.match_product_in_scene[~( self.util.match_product_in_scene['bay_number'] == -1)] # bay_shelf = matches.drop_duplicates(subset=['bay_number', 'shelf_number']) # shelf_num = len(bay_shelf) shelf_num = matches['shelf_number'].max() self.write_to_db_result(fk=kpi_fk, numerator_id=self.util.own_manuf_fk, result=shelf_num, denominator_id=self.util.store_id, by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, self.util.own_manuf_fk, self.util.store_id, shelf_num, None ])
class LinearBrandVsBrandIndexKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(LinearBrandVsBrandIndexKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): index_targets = self.util.get_relevant_sos_vs_target_kpi_targets(brand_vs_brand=True) index_targets['numerator_id'] = index_targets.apply(self.util.retrieve_relevant_item_pks, axis=1, args=('numerator_type', 'numerator_value')) index_targets['denominator_id'] = index_targets.apply(self.util.retrieve_relevant_item_pks, axis=1, args=('denominator_type', 'denominator_value')) index_targets['identifier_parent'] = index_targets['KPI Parent'].apply(lambda x: self.util.common.get_dictionary( kpi_fk=int(float(x)))) index_targets = index_targets[index_targets['type'] == self._config_params['kpi_type']] for i, row in index_targets.iterrows(): general_filters = {row['additional_filter_type_1']: row['additional_filter_value_1']} numerator_sos_filters = {row['numerator_type']: row['numerator_value']} num_num_linear, num_denom_linear = self.util.calculate_sos(numerator_sos_filters, **general_filters) numerator_sos = num_num_linear/num_denom_linear if num_denom_linear else 0 denominator_sos_filters = {row['denominator_type']: row['denominator_value']} denom_num_linear, denom_denom_linear = self.util.calculate_sos(denominator_sos_filters, **general_filters) denominator_sos = denom_num_linear/denom_denom_linear if denom_denom_linear else 0 index = numerator_sos / denominator_sos if denominator_sos else 0 self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=num_num_linear, denominator_id=row.denominator_id, denominator_result=denom_num_linear, result=index, score=index) self.util.add_kpi_result_to_kpi_results_df([row.kpi_level_2_fk, row.numerator_id, row.denominator_id, index, index])
class HeroSkuPromoPriceKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroSkuPromoPriceKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.dependencies_data[self.dependencies_data['kpi_type'] == self.util.HERO_SKU_AVAILABILITY_SKU].empty: hero_sku_list = self.util.get_available_hero_sku_list(self.dependencies_data) promo_price_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.HERO_SKU_PROMO_PRICE) for sku in hero_sku_list: self.calculate_hero_sku_promo_price(sku, promo_price_kpi_fk) def calculate_hero_sku_promo_price(self, sku, kpi_fk): price = 0 prices_df = self.util.filtered_matches[(~(self.util.filtered_matches['promotion_price'].isnull())) & (self.util.filtered_matches['product_fk'] == sku)] if not prices_df.empty: price = 1 result = self.util.commontools.get_yes_no_result(price) self.write_to_db_result(fk=kpi_fk, numerator_id=sku, result=result) self.util.add_kpi_result_to_kpi_results_df([kpi_fk, sku, None, price, None])
class HeroAvailabilityKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroAvailabilityKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def calculate(self): lvl3_ass_res_df = self.dependencies_data distribution_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_AVAILABILITY) if not lvl3_ass_res_df.empty: location_type_fk = self.util.scif[self.util.scif[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] total_skus_in_ass = len(lvl3_ass_res_df) in_store_skus = len( self.util.get_available_hero_sku_list(self.dependencies_data)) res = np.divide(float(in_store_skus), float(total_skus_in_ass)) * 100 score = 100 if res >= 100 else 0 self.write_to_db_result(fk=distribution_kpi_fk, numerator_id=self.util.own_manuf_fk, numerator_result=in_store_skus, result=res, denominator_id=self.util.store_id, denominator_result=total_skus_in_ass, score=score, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ distribution_kpi_fk, self.util.own_manuf_fk, self.util.store_id, res, score, None ]) def kpi_type(self): pass
class HeroSKUAvailabilityByHeroTypeKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroSKUAvailabilityByHeroTypeKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def calculate(self): lvl3_ass_res_df = self.dependencies_data kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.HERO_SKU_AVAILABILITY_BY_HERO_TYPE) if not lvl3_ass_res_df.empty: location_type_fk = self.util.scif[self.util.scif[ScifConsts.LOCATION_TYPE] == 'Primary Shelf']\ [ScifConsts.LOCATION_TYPE_FK].values[0] product_hero_df = self.util.all_products[[ScifConsts.PRODUCT_FK, self.util.HERO_SKU_LABEL]] lvl3_ass_res_df = lvl3_ass_res_df.merge(product_hero_df, left_on='numerator_id', right_on=ScifConsts.PRODUCT_FK, how='left') lvl3_ass_res_df = lvl3_ass_res_df.merge(self.util.hero_type_custom_entity_df, left_on=self.util.HERO_SKU_LABEL, right_on='name', how='left') lvl3_ass_res_df['count'] = 1 kpi_res_df = lvl3_ass_res_df.groupby([self.util.HERO_SKU_LABEL, 'entity_fk'], as_index=False).agg({'numerator_result': np.sum, 'count': np.sum}) kpi_res_df['result'] = kpi_res_df['numerator_result'] / kpi_res_df['count'] * 100 kpi_res_df['score'] = kpi_res_df['result'].apply(lambda x: 100 if x >= 100 else 0) for i, res in kpi_res_df.iterrows(): self.write_to_db_result(fk=kpi_fk, numerator_id=res['entity_fk'], numerator_result=res['numerator_result'], result=res['result'], denominator_id=res['entity_fk'], denominator_result=res['count'], score=res['score'], context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df( [kpi_fk, res['entity_fk'], res['entity_fk'], res['result'], res['score'], location_type_fk]) def kpi_type(self): pass
class HeroSkuStackingBySequenceNumberKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroSkuStackingBySequenceNumberKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.dependencies_data[ self.dependencies_data['kpi_type'] == self.util.HERO_SKU_AVAILABILITY_SKU].empty: hero_list = self.util.get_available_hero_sku_list( self.dependencies_data) if hero_list: relevant_matches = self.util.filtered_matches[ self.util.filtered_matches['product_fk'].isin(hero_list)] relevant_matches = relevant_matches.reset_index(drop=True) relevant_matches['facing_sequence_number'] = relevant_matches[ 'facing_sequence_number'].astype(str) relevant_matches['all_sequences'] = relevant_matches.groupby(['scene_fk', 'bay_number', 'shelf_number', 'product_fk']) \ ['facing_sequence_number'].apply(lambda x: (x + ',').cumsum().str.strip()) grouped_matches = relevant_matches.drop_duplicates(subset=[ 'scene_fk', 'bay_number', 'shelf_number', 'product_fk' ], keep='last') grouped_matches['is_stack'] = grouped_matches.apply( self.get_stack_data, axis=1) stacking_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_STACKING) for sku in hero_list: stack_info = grouped_matches[ grouped_matches['product_fk'] == sku]['is_stack'].values.tolist() score = 0 if any(stack_info): score = 1 result = self.util.commontools.get_yes_no_result(score) self.write_to_db_result(fk=stacking_kpi_fk, numerator_id=sku, score=score, result=result) self.util.add_kpi_result_to_kpi_results_df( [stacking_kpi_fk, sku, None, result, score]) @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
class SosBrandOfSegmentKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosBrandOfSegmentKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.BRAND_SOS_OF_SEGMENT) self.calculate_brand_out_of_sub_category_sos() self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_brand_out_of_sub_category_sos(self): location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.BRAND_SOS_OF_SEGMENT) filtered_matches = self.util.filtered_matches[~( self.util.filtered_matches[ScifConsts.SUB_CATEGORY_FK].isnull())] products_df = self.util.all_products[[ MatchesConsts.PRODUCT_FK, ScifConsts.BRAND_FK, ScifConsts.CATEGORY_FK ]] filtered_matches = filtered_matches.merge(products_df, on=MatchesConsts.PRODUCT_FK, how='left') sub_cat_df = filtered_matches.groupby( [ScifConsts.SUB_CATEGORY_FK], as_index=False).agg({MatchesConsts.WIDTH_MM_ADVANCE: np.sum}) sub_cat_df.rename( columns={MatchesConsts.WIDTH_MM_ADVANCE: 'sub_cat_len'}, inplace=True) brand_sub_cat_df = filtered_matches.groupby( [ScifConsts.BRAND_FK, ScifConsts.SUB_CATEGORY_FK], as_index=False).agg({MatchesConsts.WIDTH_MM_ADVANCE: np.sum}) brand_sub_cat_df = brand_sub_cat_df.merge( sub_cat_df, on=ScifConsts.SUB_CATEGORY_FK, how='left') brand_sub_cat_df['sos'] = brand_sub_cat_df[ MatchesConsts.WIDTH_MM_ADVANCE] / brand_sub_cat_df['sub_cat_len'] for i, row in brand_sub_cat_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_id=row[ScifConsts.BRAND_FK], numerator_result=row[MatchesConsts.WIDTH_MM_ADVANCE], denominator_id=row[ScifConsts.SUB_CATEGORY_FK], denominator_result=row['sub_cat_len'], result=row['sos'] * 100, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row[ScifConsts.BRAND_FK], row[ScifConsts.SUB_CATEGORY_FK], row['sos'] * 100, None, None ])
class HeroAvailabilitySkuKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroAvailabilitySkuKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.HERO_SKU_AVAILABILITY_SKU) self.calculate_kpi_for_main_shelf() self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def kpi_type(self): pass def calculate_kpi_for_main_shelf(self): location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] lvl3_ass_res = self.util.lvl3_ass_result if lvl3_ass_res.empty: return if not self.util.filtered_scif.empty: products_in_session = self.util.filtered_scif.loc[ self.util.filtered_scif['facings'] > 0]['product_fk'].values products_df = self.util.all_products[[ ScifConsts.PRODUCT_FK, ScifConsts.MANUFACTURER_FK ]] lvl3_ass_res.loc[ lvl3_ass_res['product_fk'].isin(products_in_session), 'in_store'] = 1 lvl3_ass_res = lvl3_ass_res.merge(products_df, on=ScifConsts.PRODUCT_FK, how='left') for i, result in lvl3_ass_res.iterrows(): score = result.in_store * 100 custom_res = self.util.commontools.get_yes_no_result(score) self.write_to_db_result(fk=result.kpi_fk_lvl3, numerator_id=result.product_fk, numerator_result=result.in_store, result=custom_res, denominator_id=result.manufacturer_fk, denominator_result=1, score=score, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ result['kpi_fk_lvl3'], result['product_fk'], self.util.store_id, custom_res, score, None ])
class SosVsTargetBrandKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetBrandKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.BRAND_SOS) self.calculate_brand_out_of_category_sos() self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_brand_out_of_category_sos(self): location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.BRAND_SOS) filtered_scif = self.util.filtered_scif category_df = filtered_scif.groupby([ScifConsts.CATEGORY_FK], as_index=False).agg({ 'updated_gross_length': np.sum }) category_df.rename(columns={'updated_gross_length': 'cat_len'}, inplace=True) brand_cat_df = filtered_scif.groupby( [ScifConsts.BRAND_FK, ScifConsts.CATEGORY_FK], as_index=False).agg({'updated_gross_length': np.sum}) brand_cat_df = brand_cat_df.merge(category_df, on=ScifConsts.CATEGORY_FK, how='left') brand_cat_df['sos'] = brand_cat_df[ 'updated_gross_length'] / brand_cat_df['cat_len'] for i, row in brand_cat_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_id=row[ScifConsts.BRAND_FK], numerator_result=row['updated_gross_length'], denominator_id=row[ScifConsts.CATEGORY_FK], denominator_result=row['cat_len'], result=row['sos'] * 100, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row[ScifConsts.BRAND_FK], row[ScifConsts.CATEGORY_FK], row['sos'] * 100, None, None ])
class BrandFullBayKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(BrandFullBayKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.BRAND_FULL_BAY) kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.BRAND_FULL_BAY) external_kpi_targets = self.util.commontools.all_targets_unpacked[ self.util.commontools.all_targets_unpacked['kpi_level_2_fk'] == kpi_fk] external_kpi_targets = external_kpi_targets.reset_index(drop=True) if not external_kpi_targets.empty: external_kpi_targets['group_fk'] = external_kpi_targets['Group Name'].apply(lambda x: self.util.custom_entities[ self.util.custom_entities[ 'name'] == x][ 'pk'].values[0]) filtered_matches = self.util.filtered_matches[~(self.util.filtered_matches['bay_number'] == -1)] if not filtered_matches.empty: scene_bay_product = filtered_matches.groupby(['scene_fk', 'bay_number', 'product_fk'], as_index=False).agg({'count': np.sum}) scene_bay_product = scene_bay_product.merge(self.util.all_products, on='product_fk', how='left') scene_bay = scene_bay_product.groupby(['scene_fk', 'bay_number'], as_index=False).agg({'count': np.sum}) scene_bay.rename(columns={'count': 'total_facings'}, inplace=True) for i, row in external_kpi_targets.iterrows(): filters = self.util.get_full_bay_and_positional_filters(row) brand_relevant_df = scene_bay_product[ self.util.toolbox.get_filter_condition(scene_bay_product, **filters)] result_df = brand_relevant_df.groupby(['scene_fk', 'bay_number'], as_index=False).agg( {'count': np.sum}) result_df = result_df.merge(scene_bay, on=['scene_fk', 'bay_number'], how='left') result_df['ratio'] = result_df['count'] / result_df['total_facings'] target_ratio = float(self._config_params['ratio']) result = len(result_df[result_df['ratio'] >= target_ratio]) self.write_to_db_result(fk=row['kpi_level_2_fk'], numerator_id=row['group_fk'], result=result, score=result, target=target_ratio*100) self.util.add_kpi_result_to_kpi_results_df( [row['kpi_level_2_fk'], row['group_fk'], None, None, result, None]) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
class HeroPlacementKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroPlacementKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): child_kpi_results = self.dependencies_data if not child_kpi_results.empty: top_sku_parent = self.util.common.get_kpi_fk_by_kpi_type(self.util.HERO_PLACEMENT) res = len(child_kpi_results) self.write_to_db_result(fk=top_sku_parent, numerator_id=self.util.own_manuf_fk, denominator_id=self.util.store_id, result=res, score=res) self.util.add_kpi_result_to_kpi_results_df( [top_sku_parent, self.util.own_manuf_fk, self.util.store_id, res, res])
class HeroAvailabilitySkuKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroAvailabilitySkuKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def calculate(self): for i, result in self.util.lvl3_ass_result.iterrows(): score = result.in_store * 100 custom_res = self.util.commontools.get_yes_no_result(score) self.write_to_db_result(fk=result.kpi_fk_lvl3, numerator_id=result.product_fk, numerator_result=result.in_store, result=custom_res, denominator_id=self.util.store_id, denominator_result=1, score=score) self.util.add_kpi_result_to_kpi_results_df( [result['kpi_fk_lvl3'], result['product_fk'], self.util.store_id, custom_res, score, None]) def kpi_type(self): pass
class ShelfPlacementHeroSkusParentKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(ShelfPlacementHeroSkusParentKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): child_kpi_results = self.dependencies_data if not child_kpi_results.empty: child_kpi_results[ 'KPI Parent'] = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_PLACEMENT_BY_SHELF_NUMBERS) kpi_results = child_kpi_results.groupby( ['numerator_id', 'KPI Parent'], as_index=False).agg({'result': np.sum}) for i, row in kpi_results.iterrows(): self.write_to_db_result(fk=row['KPI Parent'], numerator_id=row['numerator_id'], result=row['result'], score=row['result'], denominator_id=self.util.store_id) self.util.add_kpi_result_to_kpi_results_df([ row['KPI Parent'], row.numerator_id, self.util.store_id, row['result'], row['result'] ]) @staticmethod def construct_hero_identifier_dict(row): id_dict = { 'kpi_fk': int(float(row['KPI Parent'])), 'sku': row['numerator_id'] } return id_dict
class NumberOfFacingsKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(NumberOfFacingsKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.NUMBER_OF_FACINGS) filtered_scif = self.util.filtered_scif.copy() filtered_scif['facings'] = filtered_scif.apply(self.update_facings_for_cardboard_boxes, axis=1) for i, row in filtered_scif.iterrows(): self.write_to_db_result(fk=kpi_fk, numerator_id=row['product_fk'], result=row['facings'], denominator_id=self.util.store_id, by_scene=True) self.util.add_kpi_result_to_kpi_results_df( [kpi_fk, row['product_fk'], self.util.store_id, row['facings'], None]) @staticmethod def update_facings_for_cardboard_boxes(row): facings = row['facings'] * 3 if row['att1'] == 'display cardboard box' else row['facings'] return facings
class PromoPriceKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(PromoPriceKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.PROMO_PRICE_SCENE) sku_list = self.util.filtered_scif[ScifConsts.PRODUCT_FK] promo_price_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.PROMO_PRICE_SCENE) for sku in sku_list: self.calculate_hero_sku_promo_price(sku, promo_price_kpi_fk) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_hero_sku_promo_price(self, sku, kpi_fk): price = 0 prices_df = self.util.filtered_matches[(~(self.util.filtered_matches[ MatchesConsts.PROMOTION_PRICE].isnull())) & ( self.util.filtered_matches[ScifConsts.PRODUCT_FK] == sku)] if not prices_df.empty: price = 1 result = self.util.commontools.get_yes_no_result(price) self.write_to_db_result(fk=kpi_fk, numerator_id=sku, denominator_id=sku, result=result) self.util.add_kpi_result_to_kpi_results_df( [kpi_fk, sku, None, price, None, None])
class LinearSpaceKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(LinearSpaceKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.TOTAL_LINEAR_SPACE) for i, row in self.util.filtered_scif.iterrows(): self.write_to_db_result(fk=kpi_fk, numerator_id=row['product_fk'], denominator_id=self.util.store_id, result=row['gross_len_add_stack'], by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row['product_fk'], self.util.store_id, row['gross_len_add_stack'], None ])
class SosVsTargetSegmentKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetSegmentKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): # sos_targets = self.util.sos_vs_target_targets.copy() # sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']] self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.PEPSICO_SEGMENT_SOS) # self.calculate_pepsico_segment_space_sos_vs_target(sos_targets) self.calculate_pepsico_segment_space_sos() self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_pepsico_segment_space_sos(self): kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.PEPSICO_SEGMENT_SOS) filtered_matches = self.util.filtered_matches products_df = self.util.all_products[[ MatchesConsts.PRODUCT_FK, ScifConsts.BRAND_FK, ScifConsts.CATEGORY_FK ]] filtered_matches = filtered_matches.merge(products_df, on=MatchesConsts.PRODUCT_FK, how='left') cat_df = filtered_matches.groupby([ScifConsts.CATEGORY_FK], as_index=False).agg({ MatchesConsts.WIDTH_MM_ADVANCE: np.sum }) cat_df.rename(columns={MatchesConsts.WIDTH_MM_ADVANCE: 'cat_len'}, inplace=True) # filtered_scif = filtered_scif[filtered_scif[ScifConsts.MANUFACTURER_FK] == self.util.own_manuf_fk] location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] if not filtered_matches.empty: sub_cat_df = filtered_matches.groupby( [ScifConsts.SUB_CATEGORY_FK, ScifConsts.CATEGORY_FK], as_index=False).agg({MatchesConsts.WIDTH_MM_ADVANCE: np.sum}) if not sub_cat_df.empty: sub_cat_df = sub_cat_df.merge(cat_df, on=ScifConsts.CATEGORY_FK, how='left') sub_cat_df['sos'] = sub_cat_df[ MatchesConsts.WIDTH_MM_ADVANCE] / sub_cat_df['cat_len'] for i, row in sub_cat_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_id=row[ScifConsts.SUB_CATEGORY_FK], numerator_result=row[MatchesConsts.WIDTH_MM_ADVANCE], denominator_id=row[ScifConsts.CATEGORY_FK], denominator_result=row['cat_len'], result=row['sos'] * 100, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row[ScifConsts.SUB_CATEGORY_FK], row[ScifConsts.CATEGORY_FK], row['sos'] * 100, None, None ])
class SosVsTargetSegmentKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetSegmentKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): sos_targets = self.util.sos_vs_target_targets.copy() # sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']] self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX) self.calculate_pepsico_segment_space_sos_vs_target(sos_targets) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_pepsico_segment_space_sos_vs_target(self, sos_targets): sos_targets = sos_targets[sos_targets['type'] == self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX] session_sub_category_list = self.util.filtered_scif['sub_category_fk'].unique().tolist() session_sub_category_list = filter(lambda v: v == v, session_sub_category_list) session_sub_category_list = filter(lambda v: v is not None, session_sub_category_list) targets_sub_category_list = sos_targets['denominator_value'].values.tolist() additional_sub_categories = list(set(session_sub_category_list) - set(targets_sub_category_list)) kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX) kpi_parent = self.util.common.get_kpi_fk_by_kpi_type(self.util.PEPSICO_SEGMENT_SOS_VS_TARGET) additional_rows = [] for sub_category in additional_sub_categories: values_to_append = {'numerator_id': self.util.own_manuf_fk, 'numerator_type': 'manufacturer_fk', 'numerator_value': self.util.own_manuf_fk, 'denominator_type': 'sub_category_fk', 'denominator_value': sub_category, 'Target': None, 'denominator_id': sub_category, 'kpi_level_2_fk': kpi_fk, 'KPI Parent': kpi_parent, 'identifier_parent': self.util.common.get_dictionary(kpi_fk=int(float(kpi_parent)))} additional_rows.append(values_to_append) df_to_append = pd.DataFrame.from_records(additional_rows) sos_targets = sos_targets.append(df_to_append) sos_targets = sos_targets[sos_targets['denominator_value'].isin(session_sub_category_list)] self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets) def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets): for i, row in sos_targets.iterrows(): general_filters = {row['denominator_type']: row['denominator_value']} sos_filters = {row['numerator_type']: row['numerator_value']} numerator_linear, denominator_linear = self.util.calculate_sos(sos_filters, **general_filters) result = numerator_linear / denominator_linear if denominator_linear != 0 else 0 score = result / row['Target'] if row['Target'] else 0 if row['Target']: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100, score=score, target=row['Target'] * 100) else: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100) self.util.add_kpi_result_to_kpi_results_df( [row.kpi_level_2_fk, row.numerator_id, row.denominator_id, result * 100, score])
class SosVsTargetHeroSkuKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetHeroSkuKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): sos_targets = self.util.sos_vs_target_targets.copy() # sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']] self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.HERO_SKU_SPACE_TO_SALES_INDEX) self.calculate_hero_sku_sos_vs_target(sos_targets) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_hero_sku_sos_vs_target(self, sos_targets): kpi_filtered_products = self.util.filtered_scif['product_fk'].unique( ).tolist() # hero_list = self.util.lvl3_ass_result[self.util.lvl3_ass_result['in_store'] == 1]['product_fk'].unique().tolist() hero_list = self.util.get_available_hero_sku_list( self.dependencies_data) hero_list = filter(lambda x: x in kpi_filtered_products, hero_list) sos_targets = sos_targets[sos_targets['type'] == self.util.HERO_SKU_SPACE_TO_SALES_INDEX] sos_targets_hero_list = sos_targets['numerator_value'].values.tolist() additional_skus = list(set(hero_list) - set(sos_targets_hero_list)) category_fk = self.util.all_products[self.util.all_products['category'] == 'CSN']['category_fk'].values[0] hero_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_SPACE_TO_SALES_INDEX) kpi_hero_parent = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_SOS_VS_TARGET) additional_rows = [] for sku in additional_skus: values_to_append = { 'numerator_id': sku, 'numerator_type': 'product_fk', 'numerator_value': sku, 'denominator_type': 'category_fk', 'denominator_value': category_fk, 'Target': None, 'denominator_id': category_fk, 'kpi_level_2_fk': hero_kpi_fk, 'KPI Parent': kpi_hero_parent, 'identifier_parent': self.util.common.get_dictionary( kpi_fk=int(float(kpi_hero_parent))) } additional_rows.append(values_to_append) df_to_append = pd.DataFrame.from_records(additional_rows) sos_targets = sos_targets.append(df_to_append) sos_targets = sos_targets[sos_targets['numerator_value'].isin( hero_list)] self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets) def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets): for i, row in sos_targets.iterrows(): general_filters = { row['denominator_type']: row['denominator_value'] } sos_filters = {row['numerator_type']: row['numerator_value']} numerator_linear, denominator_linear = self.util.calculate_sos( sos_filters, **general_filters) result = numerator_linear / denominator_linear if denominator_linear != 0 else 0 score = result / row['Target'] if row['Target'] else 0 if row['Target']: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100, score=score, target=row['Target'] * 100) else: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100) self.util.add_kpi_result_to_kpi_results_df([ row.kpi_level_2_fk, row.numerator_id, row.denominator_id, result * 100, score ])
class ShelfPlacementHorizontalKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(ShelfPlacementHorizontalKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: self.calculate_shelf_placement_horizontal() def calculate_shelf_placement_horizontal(self): external_targets = self.util.all_targets_unpacked shelf_placmnt_targets = external_targets[ external_targets['operation_type'] == self.util.SHELF_PLACEMENT] if not shelf_placmnt_targets.empty: bay_max_shelves = self.get_scene_bay_max_shelves( shelf_placmnt_targets) bay_all_shelves = bay_max_shelves.drop_duplicates( subset=['bay_number', 'shelves_all_placements'], keep='first') relevant_matches = self.filter_out_irrelevant_matches( bay_all_shelves) if not relevant_matches.empty: for i, row in bay_max_shelves.iterrows(): shelf_list = map( lambda x: float(x), row['Shelves From Bottom To Include (data)'].split( ',')) relevant_matches.loc[ (relevant_matches['bay_number'] == row['bay_number']) & (relevant_matches['shelf_number_from_bottom']. isin(shelf_list)), 'position'] = row['type'] kpi_results = self.get_kpi_results_df(relevant_matches, bay_max_shelves) kpi_results = kpi_results[kpi_results['type'] == self._config_params['kpi_type']] for i, result in kpi_results.iterrows(): self.write_to_db_result( fk=result['kpi_level_2_fk'], numerator_id=result['product_fk'], denominator_id=result['product_fk'], denominator_result=result['total_facings'], numerator_result=result['count'], result=result['ratio'], score=result['ratio'], by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ result['kpi_level_2_fk'], result['product_fk'], result['product_fk'], result['ratio'], result['ratio'] ]) def get_scene_bay_max_shelves(self, shelf_placement_targets): scene_bay_max_shelves = self.util.match_product_in_scene.groupby( ['bay_number'], as_index=False).agg({'shelf_number_from_bottom': np.max}) scene_bay_max_shelves.rename( columns={'shelf_number_from_bottom': 'shelves_in_bay'}, inplace=True) min_shelf_in_template = shelf_placement_targets[ self.util.NUMBER_OF_SHELVES_TEMPL_COLUMN].min() scene_bay_max_shelves = scene_bay_max_shelves[ scene_bay_max_shelves['shelves_in_bay'] >= min_shelf_in_template] max_shelf_in_template = shelf_placement_targets[ self.util.NUMBER_OF_SHELVES_TEMPL_COLUMN].max() shelf_placement_targets = self.complete_missing_target_shelves( scene_bay_max_shelves, max_shelf_in_template, shelf_placement_targets) scene_bay_max_shelves = scene_bay_max_shelves.merge( shelf_placement_targets, left_on='shelves_in_bay', right_on=self.util.NUMBER_OF_SHELVES_TEMPL_COLUMN) scene_bay_max_shelves.rename( columns=self.util.SHELF_PLC_TARGET_COL_RENAME, inplace=True) scene_bay_max_shelves = scene_bay_max_shelves[ self.util.SHELF_PLC_TARGETS_COLUMNS + ['bay_number', 'shelves_in_bay']] scene_bay_max_shelves = scene_bay_max_shelves.drop_duplicates() bay_all_relevant_shelves = self.get_bay_relevant_shelves_df( scene_bay_max_shelves) scene_bay_max_shelves = scene_bay_max_shelves.merge( bay_all_relevant_shelves, on='bay_number', how='left') scene_bay_max_shelves = scene_bay_max_shelves[~( scene_bay_max_shelves['bay_number'] == -1)] relevant_bays = self.util.filtered_matches['bay_number'].unique( ).tolist() final_df = scene_bay_max_shelves[ scene_bay_max_shelves['bay_number'].isin(relevant_bays)] return final_df def filter_out_irrelevant_matches(self, target_kpis_df): relevant_matches = self.util.scene_bay_shelf_product[~( self.util.scene_bay_shelf_product['bay_number'] == -1)] relevant_matches = relevant_matches[ relevant_matches['bay_number'].isin( target_kpis_df['bay_number'].unique().tolist())] for i, row in target_kpis_df.iterrows(): all_shelves = map(lambda x: float(x), row['shelves_all_placements'].split(',')) rows_to_remove = relevant_matches[ (relevant_matches['bay_number'] == row['bay_number']) & (~(relevant_matches['shelf_number_from_bottom'].isin( all_shelves)))].index relevant_matches.drop(rows_to_remove, inplace=True) relevant_matches['position'] = '' return relevant_matches def complete_missing_target_shelves(self, scene_bay_df, max_shelf_template, shelf_placement_targets): shelf_placement_targets = shelf_placement_targets[ self.util.SHELF_PLC_TARGETS_COLUMNS] shelf_placement_targets = shelf_placement_targets.reset_index( drop=True) for i, row in scene_bay_df.iterrows(): if row['shelves_in_bay'] > max_shelf_template: if row['shelves_in_bay'] not in shelf_placement_targets[ self.util. NUMBER_OF_SHELVES_TEMPL_COLUMN].values.tolist(): rows_to_add = shelf_placement_targets[shelf_placement_targets[self.util.NUMBER_OF_SHELVES_TEMPL_COLUMN] \ == max_shelf_template] rows_to_add[ self.util. NUMBER_OF_SHELVES_TEMPL_COLUMN] = row['shelves_in_bay'] top_shelf_range = ','.join( map( lambda x: str(x), range(int(float(max_shelf_template)), int(float(row['shelves_in_bay'] + 1))))) rows_to_add.loc[ rows_to_add['type'] == self.util.PLACEMENT_BY_SHELF_NUMBERS_TOP, self.util. RELEVANT_SHELVES_TEMPL_COLUMN] = top_shelf_range shelf_placement_targets = shelf_placement_targets.append( rows_to_add, ignore_index=True) return shelf_placement_targets def get_bay_relevant_shelves_df(self, scene_bay_max_shelves): scene_bay_max_shelves[ self.util.RELEVANT_SHELVES_TEMPL_COLUMN] = scene_bay_max_shelves[ self.util.RELEVANT_SHELVES_TEMPL_COLUMN].astype(str) bay_all_relevant_shelves = scene_bay_max_shelves[[ 'bay_number', self.util.RELEVANT_SHELVES_TEMPL_COLUMN ]].drop_duplicates() bay_all_relevant_shelves['shelves_all_placements'] = bay_all_relevant_shelves.groupby('bay_number') \ [self.util.RELEVANT_SHELVES_TEMPL_COLUMN].apply(lambda x: (x + ',').cumsum().str.strip()) if 'bay_number' in bay_all_relevant_shelves.index.names: bay_all_relevant_shelves.index.names = ['custom_ind'] bay_all_relevant_shelves = bay_all_relevant_shelves.drop_duplicates(subset=['bay_number'], keep='last') \ [['bay_number', 'shelves_all_placements']] bay_all_relevant_shelves['shelves_all_placements'] = bay_all_relevant_shelves['shelves_all_placements']. \ apply(lambda x: x[0:-1]) return bay_all_relevant_shelves def get_kpi_results_df(self, relevant_matches, kpi_targets_df): total_products_facings = relevant_matches.groupby( ['product_fk'], as_index=False).agg({'count': np.sum}) total_products_facings.rename(columns={'count': 'total_facings'}, inplace=True) result_df = relevant_matches.groupby(['product_fk', 'position'], as_index=False).agg( {'count': np.sum}) result_df = result_df.merge(total_products_facings, on='product_fk', how='left') kpis_df = kpi_targets_df.drop_duplicates( subset=['kpi_level_2_fk', 'type', 'KPI Parent']) result_df = result_df.merge(kpis_df, left_on='position', right_on='type', how='left') result_df['ratio'] = result_df.apply(self.get_sku_ratio, axis=1) return result_df @staticmethod def get_sku_ratio(row): ratio = float(row['count']) / row['total_facings'] * 100 return ratio
class CategoryFullBayKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(CategoryFullBayKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.CATEGORY_FULL_BAY) category_fk = self.util.all_products[self.util.all_products[ProductsConsts.CATEGORY] == self.util.CSN]\ [ProductsConsts.CATEGORY_FK].values[0] kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.CATEGORY_FULL_BAY) filtered_matches = self.util.filtered_matches[~( self.util.filtered_matches[MatchesConsts.BAY_NUMBER] == -1)] if not filtered_matches.empty: scene_bay_product = filtered_matches.groupby([ MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER, ScifConsts.PRODUCT_FK ], as_index=False).agg( {'count': np.sum}) scene_bay_product = scene_bay_product.merge( self.util.all_products, on=ProductsConsts.PRODUCT_FK, how='left') scene_bay = scene_bay_product.groupby( [MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER], as_index=False).agg({'count': np.sum}) scene_bay.rename(columns={'count': 'total_facings'}, inplace=True) cat_relevant_df = scene_bay_product[scene_bay_product[ ProductsConsts.CATEGORY_FK] == category_fk] result_df = cat_relevant_df.groupby( [MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER], as_index=False).agg({'count': np.sum}) result_df = result_df.merge( scene_bay, on=[MatchesConsts.SCENE_FK, MatchesConsts.BAY_NUMBER], how='left') result_df[ 'ratio'] = result_df['count'] / result_df['total_facings'] target_ratio = float(self._config_params['ratio']) result = len(result_df[result_df['ratio'] >= target_ratio]) self.write_to_db_result( fk=kpi_fk, numerator_id=category_fk, denominator_id=self.util.store_id, score=result, result=result, target=float(self._config_params['ratio']) * 100) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, category_fk, self.util.store_id, result, result, None ]) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
class ShelfPlacementHeroSkusKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(ShelfPlacementHeroSkusKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.dependencies_data[ self.dependencies_data['kpi_type'] == self.util.HERO_SKU_AVAILABILITY_SKU].empty: external_targets = self.util.commontools.all_targets_unpacked shelf_placmnt_targets = external_targets[ external_targets['operation_type'] == self.util.SHELF_PLACEMENT] kpi_fks = shelf_placmnt_targets['kpi_level_2_fk'].unique().tolist() scene_placement_results = self.util.scene_kpi_results[ self.util.scene_kpi_results['kpi_level_2_fk'].isin(kpi_fks)] if not scene_placement_results.empty: hero_results = self.get_hero_results_df( scene_placement_results) if not hero_results.empty: kpis_df = self.util.kpi_static_data[['pk', 'type']] kpis_df.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True) hero_results = hero_results.merge(kpis_df, on='kpi_level_2_fk', how='left') hero_results['parent_type'] = hero_results['KPI Parent'] hero_results = hero_results[hero_results['type'] == self._config_params['level']] if not hero_results.empty: hero_results['type'] = hero_results['type'].apply( lambda x: '{} {}'.format(self.util.HERO_PREFIX, x)) hero_results['parent_type'] = hero_results[ 'parent_type'].apply(lambda x: '{} {}'.format( self.util.HERO_PREFIX, x)) hero_results['kpi_level_2_fk'] = hero_results[ 'type'].apply( self.util.common.get_kpi_fk_by_kpi_type) hero_results['KPI Parent'] = hero_results[ 'parent_type'].apply( self.util.common.get_kpi_fk_by_kpi_type) hero_results['identifier_parent'] = hero_results.apply( self.construct_hero_identifier_dict, axis=1) for i, row in hero_results.iterrows(): self.write_to_db_result( fk=row['kpi_level_2_fk'], numerator_id=row['numerator_id'], denominator_id=row['numerator_id'], denominator_result=row['denominator_result'], numerator_result=row['numerator_result'], result=row['ratio'], score=row['ratio']) self.util.add_kpi_result_to_kpi_results_df([ row.kpi_level_2_fk, row.numerator_id, row['numerator_id'], row['ratio'], row['ratio'] ]) def get_hero_results_df(self, scene_placement_results): kpi_results = scene_placement_results.groupby( ['kpi_level_2_fk', 'numerator_id'], as_index=False).agg({'numerator_result': np.sum}) products_df = scene_placement_results.groupby( ['numerator_id'], as_index=False).agg({'numerator_result': np.sum}) products_df.rename(columns={'numerator_result': 'denominator_result'}, inplace=True) kpi_results = kpi_results.merge(products_df, on='numerator_id', how='left') hero_skus = self.util.get_available_hero_sku_list( self.dependencies_data) hero_results = kpi_results[kpi_results['numerator_id'].isin(hero_skus)] kpi_parent = self.util.commontools.all_targets_unpacked.drop_duplicates( subset=['kpi_level_2_fk', 'KPI Parent'])[[ 'kpi_level_2_fk', 'KPI Parent' ]] hero_results = hero_results.merge(kpi_parent, on='kpi_level_2_fk') hero_results['ratio'] = hero_results[ 'numerator_result'] / hero_results['denominator_result'] * 100 return hero_results def get_kpi_type_by_pk(self, kpi_fk): try: kpi_fk = int(float(kpi_fk)) return self.util.kpi_static_data[self.util.kpi_static_data['pk'] == kpi_fk]['type'].values[0] except IndexError: Log.info( "Kpi name: {} is not equal to any kpi name in static table". format(kpi_fk)) return None # @staticmethod # def get_sku_ratio(row): # ratio = row['count'] / row['total_facings'] # return ratio @staticmethod def construct_hero_identifier_dict(row): id_dict = { 'kpi_fk': int(float(row['KPI Parent'])), 'sku': row['numerator_id'] } return id_dict
class FacingsPerProductKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(FacingsPerProductKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.FACINGS_PER_PRODUCT) if not self.util.filtered_matches.empty: kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.FACINGS_PER_PRODUCT) filtered_matches = self.util.filtered_matches.copy() shelves_arr = np.sort(filtered_matches[ MatchesConsts.SHELF_NUMBER].unique()).tolist() filtered_matches[MatchesConsts.SHELF_NUMBER] = filtered_matches[MatchesConsts.SHELF_NUMBER]. \ apply(lambda x: shelves_arr.index(x)+1) filtered_matches = filtered_matches.merge( self.util.all_products, on=MatchesConsts.PRODUCT_FK, how='left') filtered_matches.loc[filtered_matches['att1'] == 'display cardboard box', 'count'] = \ filtered_matches['count'] * 3 result_df = filtered_matches.groupby([ MatchesConsts.PRODUCT_FK, MatchesConsts.SHELF_NUMBER, MatchesConsts.BAY_NUMBER ], as_index=False).agg( {'count': np.sum}) shelves_cust_entity = self.util.custom_entities[ self.util.custom_entities['entity_type'] == 'shelf_number'] shelves_cust_entity['name'] = shelves_cust_entity['name'].astype( int) bays_cust_entity = self.util.custom_entities[ self.util.custom_entities['entity_type'] == 'bay_number'] bays_cust_entity['name'] = bays_cust_entity['name'].astype(int) result_df = result_df.merge(shelves_cust_entity, left_on=MatchesConsts.SHELF_NUMBER, right_on='name', how='left') result_df.rename(columns={'pk': 'shelf_fk'}, inplace=True) result_df = result_df.merge(bays_cust_entity, left_on=MatchesConsts.BAY_NUMBER, right_on='name', how='left') result_df.rename(columns={'pk': 'bay_fk'}, inplace=True) for i, row in result_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_result=row[MatchesConsts.SHELF_NUMBER], result=row['count'], numerator_id=row[MatchesConsts.PRODUCT_FK], denominator_id=row[MatchesConsts.PRODUCT_FK], denominator_result=row[MatchesConsts.BAY_NUMBER], context_id=row['shelf_fk'], by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row[MatchesConsts.PRODUCT_FK], row['bay_fk'], row['count'], None, row['shelf_fk'] ]) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
class SosVsTargetSubBrandKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetSubBrandKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): sos_targets = self.util.sos_vs_target_targets.copy() # sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']] self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.SUB_BRAND_SPACE_TO_SALES_INDEX) self.calculate_sub_brand_sos_vs_target(sos_targets) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_sub_brand_sos_vs_target(self, sos_targets): sos_targets = sos_targets[sos_targets['type'] == self.util.SUB_BRAND_SPACE_TO_SALES_INDEX] session_sub_brands = self.util.filtered_scif['sub_brand'].unique( ).tolist() session_sub_brands = filter(lambda v: v == v, session_sub_brands) session_sub_brands = filter(lambda v: v is not None, session_sub_brands) targets_sub_brands = sos_targets['numerator_value'].values.tolist() additional_sub_brands = list( set(session_sub_brands) - set(targets_sub_brands)) category_fk = self.util.all_products[self.util.all_products['category'] == 'CSN']['category_fk'].values[0] kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.SUB_BRAND_SPACE_TO_SALES_INDEX) kpi_parent = self.util.common.get_kpi_fk_by_kpi_type( self.util.SUB_BRAND_SPACE_SOS_VS_TARGET) additional_rows = [] for sub_brand in additional_sub_brands: sub_brand_df = self.util.custom_entities[ self.util.custom_entities['name'] == sub_brand] sub_brand_fk = sub_brand_df['pk'].values[0] if len( sub_brand_df) > 0 else None if sub_brand_fk is None: Log.warning( 'Sub_brand: {} is not in custom_entity table. Please add'. format(sub_brand)) else: values_to_append = { 'numerator_id': sub_brand_fk, 'numerator_type': 'sub_brand', 'numerator_value': sub_brand, 'denominator_type': 'category_fk', 'denominator_value': category_fk, 'Target': None, 'denominator_id': category_fk, 'kpi_level_2_fk': kpi_fk, 'KPI Parent': kpi_parent, 'identifier_parent': self.util.common.get_dictionary( kpi_fk=int(float(kpi_parent))) } additional_rows.append(values_to_append) df_to_append = pd.DataFrame.from_records(additional_rows) sos_targets = sos_targets.append(df_to_append) sos_targets = sos_targets[sos_targets['numerator_value'].isin( session_sub_brands)] self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets) def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets): for i, row in sos_targets.iterrows(): general_filters = { row['denominator_type']: row['denominator_value'] } sos_filters = {row['numerator_type']: row['numerator_value']} numerator_linear, denominator_linear = self.util.calculate_sos( sos_filters, **general_filters) result = numerator_linear / denominator_linear if denominator_linear != 0 else 0 score = result / row['Target'] if row['Target'] else 0 if row['Target']: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100, score=score, target=row['Target'] * 100) else: self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=numerator_linear, denominator_id=row.denominator_id, denominator_result=denominator_linear, result=result * 100) self.util.add_kpi_result_to_kpi_results_df([ row.kpi_level_2_fk, row.numerator_id, row.denominator_id, result * 100, score ])
class SosVsTargetHeroSkuKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(SosVsTargetHeroSkuKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self.util.HERO_SKU_SOS) self.calculate_hero_sku_sos() self.util.reset_filtered_scif_and_matches_to_exclusion_all_state() def calculate_hero_sku_sos(self): kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_SOS) filtered_scif = self.util.filtered_scif location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] if (not filtered_scif.empty) and (not self.dependencies_data.empty): category_df = filtered_scif.groupby([ScifConsts.CATEGORY_FK], as_index=False).agg({ 'updated_gross_length': np.sum }) category_df.rename(columns={'updated_gross_length': 'cat_len'}, inplace=True) av_hero_list = self.util.get_available_hero_sku_list( self.dependencies_data) filtered_scif = filtered_scif[filtered_scif[ ScifConsts.PRODUCT_FK].isin(av_hero_list)] unav_hero_list = self.util.get_unavailable_hero_sku_list( self.dependencies_data) unav_hero_df = self.util.all_products[self.util.all_products[ScifConsts.PRODUCT_FK].isin(unav_hero_list)] \ [[ScifConsts.PRODUCT_FK, ScifConsts.CATEGORY_FK]] unav_hero_df['updated_gross_length'] = 0 filtered_scif = filtered_scif.append(unav_hero_df) hero_cat_df = filtered_scif.groupby( [ScifConsts.PRODUCT_FK, ScifConsts.CATEGORY_FK], as_index=False).agg({'updated_gross_length': np.sum}) hero_cat_df = hero_cat_df.merge(category_df, on=ScifConsts.CATEGORY_FK, how='left') hero_cat_df['cat_len'] = hero_cat_df['cat_len'].fillna(0) hero_cat_df['sos'] = hero_cat_df.apply(self.calculate_sos, axis=1) for i, row in hero_cat_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_id=row[ScifConsts.PRODUCT_FK], numerator_result=row['updated_gross_length'], denominator_id=row[ScifConsts.CATEGORY_FK], denominator_result=row['cat_len'], result=row['sos'], context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, row[ScifConsts.PRODUCT_FK], row[ScifConsts.CATEGORY_FK], row['sos'], None, None ]) @staticmethod def calculate_sos(row): sos = 0 if row['cat_len'] != 0: sos = float(row['updated_gross_length']) / row['cat_len'] * 100 return sos
class LinearBrandVsBrandIndexKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(LinearBrandVsBrandIndexKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): self.util.filtered_scif, self.util.filtered_matches = \ self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif, self.util.filtered_matches, self._config_params['kpi_type']) index_targets = self.util.get_relevant_sos_vs_target_kpi_targets( brand_vs_brand=True) index_targets['numerator_id'] = index_targets.apply( self.util.retrieve_relevant_item_pks, axis=1, args=('numerator_type', 'numerator_value')) index_targets['denominator_id'] = index_targets.apply( self.util.retrieve_relevant_item_pks, axis=1, args=('denominator_type', 'denominator_value')) index_targets['identifier_parent'] = index_targets['KPI Parent'].apply( lambda x: self.util.common.get_dictionary(kpi_fk=int(float(x)))) index_targets = index_targets[index_targets['type'] == self._config_params['kpi_type']] location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] for i, row in index_targets.iterrows(): general_filters = { row['additional_filter_type_1']: row['additional_filter_value_1'] } numerator_sos_filters = { row['numerator_type']: row['numerator_value'] } num_num_linear, num_denom_linear = self.util.calculate_sos( numerator_sos_filters, **general_filters) numerator_sos = num_num_linear / num_denom_linear if num_denom_linear else 0 denominator_sos_filters = { row['denominator_type']: row['denominator_value'] } denom_num_linear, denom_denom_linear = self.util.calculate_sos( denominator_sos_filters, **general_filters) denominator_sos = denom_num_linear / denom_denom_linear if denom_denom_linear else 0 if denominator_sos == 0: index = 0 if numerator_sos == 0 else 1 else: index = numerator_sos / denominator_sos self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id, numerator_result=num_num_linear, denominator_id=row.denominator_id, denominator_result=denom_num_linear, result=index, score=index, context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ row.kpi_level_2_fk, row.numerator_id, row.denominator_id, index, index, None ]) self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
class ShelfPlacementVerticalKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(ShelfPlacementVerticalKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): if not self.util.filtered_matches.empty: self.calculate_shelf_placement_vertical_mm() def calculate_shelf_placement_vertical_mm(self): probe_groups_list = self.util.probe_groups['probe_group_id'].unique( ).tolist() resulting_matches = pd.DataFrame() for probe_group in probe_groups_list: matches = self.util.match_product_in_scene[ self.util.match_product_in_scene['probe_group_id'] == probe_group] filtered_matches = self.util.filtered_matches[ self.util.filtered_matches['probe_group_id'] == probe_group] left_edge = self.get_left_edge_mm(matches) right_edge = self.get_right_edge_mm(matches) shelf_length = float(right_edge - left_edge) matches = self.define_product_position_mm(matches, shelf_length, left_edge, right_edge) matches_position = matches[['probe_match_fk', 'position']] filtered_matches = filtered_matches.merge(matches_position, on='probe_match_fk', how='left') if resulting_matches.empty: resulting_matches = filtered_matches else: resulting_matches = resulting_matches.append(filtered_matches) result_df = self.get_vertical_placement_kpi_result_df( resulting_matches) result_df = result_df[result_df['position'] == self._config_params['kpi_type']] for i, row in result_df.iterrows(): self.write_to_db_result(fk=row['kpi_fk'], numerator_id=row['product_fk'], denominator_id=row['product_fk'], numerator_result=row['count'], denominator_result=row['total_facings'], result=row['ratio'], score=row['ratio'], by_scene=True) self.util.add_kpi_result_to_kpi_results_df([ row['kpi_fk'], row['product_fk'], row['product_fk'], row['ratio'], row['ratio'] ]) @staticmethod def get_left_edge_mm(matches): matches[ 'left_edge_mm'] = matches['x_mm'] - matches['width_mm_advance'] / 2 left_edge = matches['left_edge_mm'].min() return left_edge @staticmethod def get_right_edge_mm(matches): matches['right_edge_mm'] = matches[ 'x_mm'] + matches['width_mm_advance'] / 2 right_edge = matches['right_edge_mm'].max() return right_edge def define_product_position_mm(self, matches, shelf_length, left_edge, right_edge): matches['position'] = '' matches.loc[(matches['x_mm'] >= left_edge) & (matches['x_mm'] <= (left_edge + shelf_length / 3)), 'position'] = self.util.SHELF_PLACEMENT_VERTICAL_LEFT matches.loc[(matches['x_mm'] > (left_edge + shelf_length / 3)) & (matches['x_mm'] <= (left_edge + shelf_length * 2 / 3)), 'position'] = self.util.SHELF_PLACEMENT_VERTICAL_CENTER matches.loc[(matches['x_mm'] > (left_edge + shelf_length * 2 / 3)) & (matches['x_mm'] <= right_edge), 'position'] = self.util.SHELF_PLACEMENT_VERTICAL_RIGHT return matches def get_vertical_placement_kpi_result_df(self, filtered_matches): all_products_df = filtered_matches.groupby(['product_fk'], as_index=False).agg( {'count': np.sum}) all_products_df.rename(columns={'count': 'total_facings'}, inplace=True) result_df = filtered_matches.groupby(['product_fk', 'position'], as_index=False).agg( {'count': np.sum}) result_df = result_df.merge(all_products_df, on='product_fk', how='left') result_df[ 'ratio'] = result_df['count'] / result_df['total_facings'] * 100 result_df['kpi_fk'] = result_df['position'].apply( lambda x: self.util.common.get_kpi_fk_by_kpi_type(x)) return result_df
class HeroSOSofCategoryByHeroTypeKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(HeroSOSofCategoryByHeroTypeKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def kpi_type(self): pass def calculate(self): hero_sos_kpi_results = self.dependencies_data kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.HERO_SKU_SOS_OF_CAT_BY_HERO_TYPE) if not hero_sos_kpi_results.empty: category_len_df = hero_sos_kpi_results.drop_duplicates( subset=['denominator_id']) category_len_df = category_len_df[[ 'denominator_id', 'denominator_result' ]] location_type_fk = self.util.scif[self.util.scif[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \ [ScifConsts.LOCATION_TYPE_FK].values[0] product_hero_df = self.util.all_products[[ ScifConsts.PRODUCT_FK, self.util.HERO_SKU_LABEL ]] hero_sos_kpi_results = hero_sos_kpi_results.merge( product_hero_df, left_on='numerator_id', right_on=ScifConsts.PRODUCT_FK, how='left') hero_sos_kpi_results = hero_sos_kpi_results.merge( self.util.hero_type_custom_entity_df, left_on=self.util.HERO_SKU_LABEL, right_on='name', how='left') hero_type_by_cat = hero_sos_kpi_results.groupby([self.util.HERO_SKU_LABEL, 'entity_fk', 'denominator_id'], as_index=False).\ agg({'numerator_result': np.sum}) hero_type_by_cat = hero_type_by_cat.merge(category_len_df, on='denominator_id', how='left') hero_type_by_cat['sos'] = hero_type_by_cat[ 'numerator_result'] / hero_type_by_cat[ 'denominator_result'] * 100 hero_type_by_cat['score'] = hero_type_by_cat['sos'].apply( lambda x: 100 if x >= 100 else 0) for i, res in hero_type_by_cat.iterrows(): self.write_to_db_result( fk=kpi_fk, score=res['score'], result=res['sos'], numerator_id=res['entity_fk'], denominator_id=res['denominator_id'], numerator_result=res['numerator_result'], denominator_result=res['denominator_result'], context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, res['entity_fk'], res['denominator_id'], res['sos'], res['score'], location_type_fk ])
class ShareOfAssortmentByHeroTypeKpi(UnifiedCalculationsScript): def __init__(self, data_provider, config_params=None, **kwargs): super(ShareOfAssortmentByHeroTypeKpi, self).__init__(data_provider, config_params=config_params, **kwargs) self.util = PepsicoUtil(None, data_provider) def calculate(self): lvl3_ass_res_df = self.dependencies_data if not lvl3_ass_res_df.empty: kpi_fk = self.util.common.get_kpi_fk_by_kpi_type( self.util.SHARE_OF_ASSORTMENT_BY_HERO_TYPE) in_store_skus = len( self.util.get_available_hero_sku_list( self.dependencies_data)) # total recognized location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf']\ [ScifConsts.LOCATION_TYPE_FK].values[0] product_hero_df = self.util.all_products[[ ScifConsts.PRODUCT_FK, self.util.HERO_SKU_LABEL ]] lvl3_ass_res_df = lvl3_ass_res_df.merge( product_hero_df, left_on='numerator_id', right_on=ScifConsts.PRODUCT_FK, how='left') lvl3_ass_res_df = lvl3_ass_res_df.merge( self.util.hero_type_custom_entity_df, left_on=self.util.HERO_SKU_LABEL, right_on='name', how='left') kpi_res_df = lvl3_ass_res_df.groupby( [self.util.HERO_SKU_LABEL, 'entity_fk'], as_index=False).agg({'numerator_result': np.sum}) kpi_res_df['in_store_total'] = in_store_skus kpi_res_df['result'] = kpi_res_df.apply(self.get_result, axis=1) kpi_res_df['score'] = kpi_res_df['result'].apply( lambda x: 100 if x >= 100 else 0) for i, res in kpi_res_df.iterrows(): self.write_to_db_result( fk=kpi_fk, numerator_id=res['entity_fk'], numerator_result=res['numerator_result'], result=res['result'], denominator_id=self.util.store_id, denominator_result=res['in_store_total'], score=res['score'], context_id=location_type_fk) self.util.add_kpi_result_to_kpi_results_df([ kpi_fk, res['entity_fk'], res['entity_fk'], res['result'], res['score'], location_type_fk ]) def kpi_type(self): pass @staticmethod def get_result(row): rv = float( row['numerator_result'] ) / row['in_store_total'] * 100 if row['in_store_total'] else 0 return rv