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