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 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 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 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()