def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryAllHeroSOSofCSNCategoryKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
class SecondaryPriceMechanicKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryPriceMechanicKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
filtered_matches = self.util.filtered_matches_secondary.merge(
self.util.all_products, on=[ScifConsts.PRODUCT_FK], how='left')
filtered_matches = filtered_matches[filtered_matches[ScifConsts.PRODUCT_TYPE] \
== 'POS'].drop_duplicates(subset=[ScifConsts.PRODUCT_FK])
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
for i, row in filtered_matches.iterrows():
self.write_to_db_result(
fk=kpi_fk,
numerator_id=row[ScifConsts.PRODUCT_FK],
denominator_id=row['display_id'],
denominator_result=row['display_id'],
context_id=row['store_area_fk'],
result=1)
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
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 SecondaryAvailabilityPerProductKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryAvailabilityPerProductKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
if not self.util.filtered_matches_secondary.empty:
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
filtered_matches = self.util.filtered_matches_secondary.copy()
store_area = filtered_matches['store_area_fk'].values[0]
product_display = filtered_matches.drop_duplicates(
subset=[MatchesConsts.PRODUCT_FK, 'display_id'])
for i, row in product_display.iterrows():
self.write_to_db_result(
fk=kpi_fk,
result=1,
numerator_id=row[MatchesConsts.PRODUCT_FK],
denominator_id=row[MatchesConsts.PRODUCT_FK],
denominator_result=row['display_id'],
context_id=store_area)
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryHeroLengthByHeroTypeKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondarySosBrandOfSegmentKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
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 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 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 ProductBlockingKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(ProductBlockingKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.util = PepsicoUtil(None, data_provider)
self.block = None
def kpi_type(self):
pass
def calculate(self):
if not self.util.filtered_matches.empty:
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.PRODUCT_BLOCKING)
filtered_matches = self.util.filtered_matches.copy()
if 'sub_category_fk' in filtered_matches.columns:
filtered_matches = filtered_matches.drop(columns=['sub_category_fk'])
self.block = Block(self.data_provider, custom_scif=self.util.filtered_scif,
custom_matches=filtered_matches)
if not self.util.filtered_matches.empty:
self.calculate_product_blocking()
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_product_blocking(self):
external_targets = self.util.all_targets_unpacked[self.util.all_targets_unpacked['type'] == self.util.PRODUCT_BLOCKING]
additional_block_params = {'check_vertical_horizontal': True, 'minimum_facing_for_block': 3,
'include_stacking': True,
'allowed_products_filters': {'product_type': ['Empty']}}
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.PRODUCT_BLOCKING)
for i, row in external_targets.iterrows():
# print row['Group Name']
group_fk = self.util.custom_entities[self.util.custom_entities['name'] == row['Group Name']]['pk'].values[0]
# filters = self.util.get_block_and_adjacency_filters(row)
filters = self.util.get_block_filters(row)
target = row['Target']
additional_block_params.update({'minimum_block_ratio': float(target)/100})
result_df = self.block.network_x_block_together(filters, additional=additional_block_params)
score = max_ratio = 0
result = self.util.commontools.get_yes_no_result(0)
if not result_df.empty:
max_ratio = result_df['facing_percentage'].max()
result_df = result_df[result_df['is_block']==True]
if not result_df.empty:
max_ratio = result_df['facing_percentage'].max()
result_df = result_df[result_df['facing_percentage'] == max_ratio]
result = self.util.commontools.get_yes_no_result(1)
orientation = result_df['orientation'].values[0]
score = self.util.commontools.get_kpi_result_value_pk_by_value(orientation.upper())
# print score
self.write_to_db_result(fk=kpi_fk, numerator_id=group_fk, denominator_id=self.util.store_id,
numerator_result=max_ratio * 100,
score=score, result=result, target=target, by_scene=True)
self.util.block_results = self.util.block_results.append(pd.DataFrame([{'Group Name': row['Group Name'],
'Score':
result_df['is_block'].values[
0] if not result_df.empty else False}]))
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
])
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryAvailabilityPerProductKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
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 __init__(self, data_provider, config_params=None, **kwargs):
super(BlocksAdjacencyKpi, self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.adjacency = Adjancency(self.data_provider,
custom_scif=self.util.filtered_scif,
custom_matches=self.util.filtered_matches)
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 SecondarySosBrandOfSegmentKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondarySosBrandOfSegmentKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
self.calculate_brand_out_of_sub_category_sos()
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
def calculate_brand_out_of_sub_category_sos(self):
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
filtered_matches = self.util.filtered_matches_secondary
filtered_matches = filtered_matches[~(
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)
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
])
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 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 SecondarySOSBrandofCategoryKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondarySOSBrandofCategoryKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
self.calculate_brand_out_of_category_sos()
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
def calculate_brand_out_of_category_sos(self):
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
filtered_scif = self.util.filtered_scif_secondary
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)
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)
self.kpi_name = self._config_params['kpi_type']
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryNumberofUniqueHeroSKUKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
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 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)
self.kpi_name = self._config_params['kpi_type']
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
self.calculate_kpi_for_secondary_shelf()
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def kpi_type(self):
pass
def calculate_kpi_for_secondary_shelf(self):
# scif for secondary should have display and store_area breakdown
lvl3_ass_res = self.util.lvl3_ass_result
if lvl3_ass_res.empty:
return
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
ass_list = lvl3_ass_res[ScifConsts.PRODUCT_FK].values.tolist()
filtered_scif = self.util.filtered_scif_secondary[self.util.filtered_scif_secondary[ScifConsts.PRODUCT_FK]. \
isin(ass_list)]
# products_in_session = filtered_scif.loc[filtered_scif['facings'] > 0]['product_fk'].values
# lvl3_ass_res.loc[lvl3_ass_res['product_fk'].isin(products_in_session), 'in_store'] = 1
assortment_scif = filtered_scif.drop_duplicates(subset=[
ScifConsts.TEMPLATE_FK, 'store_area_fk', ScifConsts.PRODUCT_FK
])
for i, result in assortment_scif.iterrows():
score = 100
custom_res = self.util.commontools.get_yes_no_result(score)
self.write_to_db_result(fk=kpi_fk,
numerator_id=result.product_fk,
numerator_result=1,
result=custom_res,
denominator_id=result.template_fk,
denominator_result=1,
score=score,
context_id=result.store_area_fk)
class SeondaryPromoPriceKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SeondaryPromoPriceKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
filtered_matches = self.util.filtered_matches_secondary
if not filtered_matches.empty:
product_display = filtered_matches.drop_duplicates(
subset=[MatchesConsts.PRODUCT_FK, 'display_id'])
store_area = self.util.filtered_scif_secondary[
'store_area_fk'].values[0]
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
for i, row in product_display.iterrows():
price = 0
prices_df = filtered_matches[
(~(filtered_matches[
MatchesConsts.PROMOTION_PRICE].isnull()))
& (filtered_matches[ScifConsts.PRODUCT_FK] == row[
MatchesConsts.PRODUCT_FK]) &
(filtered_matches['display_id'] == row['display_id'])]
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=row[MatchesConsts.PRODUCT_FK],
denominator_id=row[MatchesConsts.PRODUCT_FK],
denominator_result=row['display_id'],
context_id=store_area,
result=result)
self.util.reset_secondary_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 SecondaryLinearSpacePerProductKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryLinearSpacePerProductKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def kpi_type(self):
pass
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
if not self.util.filtered_matches_secondary.empty:
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
filtered_matches = self.util.filtered_matches_secondary.copy()
store_area = self.util.filtered_scif_secondary[
'store_area_fk'].values[0]
result_df = filtered_matches.groupby(
[MatchesConsts.PRODUCT_FK, 'display_id'],
as_index=False).agg(
{MatchesConsts.WIDTH_MM_ADVANCE: np.sum})
for i, row in result_df.iterrows():
self.write_to_db_result(
fk=kpi_fk,
numerator_result=row[MatchesConsts.WIDTH_MM_ADVANCE],
result=row[MatchesConsts.WIDTH_MM_ADVANCE],
numerator_id=row[MatchesConsts.PRODUCT_FK],
denominator_id=row[MatchesConsts.PRODUCT_FK],
denominator_result=row['display_id'],
context_id=store_area)
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
class SecondaryHeroLengthByHeroTypeKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SecondaryHeroLengthByHeroTypeKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
self.kpi_name = self._config_params['kpi_type']
def calculate(self):
if self.util.commontools.are_all_bins_tagged:
self.util.filtered_scif_secondary, self.util.filtered_matches_secondary = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif_secondary,
self.util.filtered_matches_secondary,
self.kpi_name)
total_skus_in_ass = len(self.util.lvl3_ass_result)
if not total_skus_in_ass:
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
return
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.kpi_name)
lvl3_ass_res_df = self.dependencies_data
if lvl3_ass_res_df.empty and total_skus_in_ass:
self.write_to_db_result(fk=kpi_fk,
numerator_id=self.util.own_manuf_fk,
result=0,
score=0,
denominator_id=self.util.store_id)
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
return
if not lvl3_ass_res_df.empty:
available_hero_list = lvl3_ass_res_df[(lvl3_ass_res_df['numerator_result'] == 1)] \
['numerator_id'].unique().tolist()
filtered_scif = self.util.filtered_scif_secondary[self.util.filtered_scif_secondary[ScifConsts.PRODUCT_FK].\
isin(available_hero_list)]
result_df = filtered_scif.groupby([self.util.HERO_SKU_LABEL],
as_index=False).agg({
'updated_gross_length':
np.sum
})
result_df = result_df.merge(
self.util.hero_type_custom_entity_df,
left_on=self.util.HERO_SKU_LABEL,
right_on='name',
how='left')
for i, row in result_df.iterrows():
self.write_to_db_result(fk=kpi_fk,
numerator_id=row['entity_fk'],
denominator_id=row['entity_fk'],
result=row['updated_gross_length'],
score=row['updated_gross_length'])
# add a function that resets to secondary scif and matches
self.util.reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(
)
def kpi_type(self):
pass