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 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 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
def test_get_available_hero_sku_list_retrieves_only_skus_in_store(self): self.mock_scene_item_facts(pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif')) self.mock_match_product_in_scene(pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='matches')) self.mock_scene_info(DataTestUnitPEPSICOUK.scene_info) self.mock_scene_kpi_results(DataTestUnitPEPSICOUK.scene_kpi_results_test_case_1) util = PepsicoUtil(self.output, self.data_provider_mock) availability_sku = HeroAvailabilitySkuKpi(self.data_provider_mock, config_params={}) availability_sku.calculate() hero_dependency_df = pd.DataFrame(availability_sku.kpi_results) hero_dependency_df['kpi_type'] = util.HERO_SKU_AVAILABILITY_SKU hero_list = util.get_available_hero_sku_list(hero_dependency_df) self.assertItemsEqual(hero_list, [1, 2])
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
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 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 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 ])