class HeroAvailabilityKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(HeroAvailabilityKpi, self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def calculate(self):
lvl3_ass_res_df = self.dependencies_data
distribution_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_AVAILABILITY)
if not lvl3_ass_res_df.empty:
location_type_fk = self.util.scif[self.util.scif[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \
[ScifConsts.LOCATION_TYPE_FK].values[0]
total_skus_in_ass = len(lvl3_ass_res_df)
in_store_skus = len(
self.util.get_available_hero_sku_list(self.dependencies_data))
res = np.divide(float(in_store_skus),
float(total_skus_in_ass)) * 100
score = 100 if res >= 100 else 0
self.write_to_db_result(fk=distribution_kpi_fk,
numerator_id=self.util.own_manuf_fk,
numerator_result=in_store_skus,
result=res,
denominator_id=self.util.store_id,
denominator_result=total_skus_in_ass,
score=score,
context_id=location_type_fk)
self.util.add_kpi_result_to_kpi_results_df([
distribution_kpi_fk, self.util.own_manuf_fk,
self.util.store_id, res, score, None
])
def kpi_type(self):
pass
class HeroSkuPromoPriceKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(HeroSkuPromoPriceKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
if not self.dependencies_data[self.dependencies_data['kpi_type'] == self.util.HERO_SKU_AVAILABILITY_SKU].empty:
hero_sku_list = self.util.get_available_hero_sku_list(self.dependencies_data)
promo_price_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.HERO_SKU_PROMO_PRICE)
for sku in hero_sku_list:
self.calculate_hero_sku_promo_price(sku, promo_price_kpi_fk)
def calculate_hero_sku_promo_price(self, sku, kpi_fk):
price = 0
prices_df = self.util.filtered_matches[(~(self.util.filtered_matches['promotion_price'].isnull())) &
(self.util.filtered_matches['product_fk'] == sku)]
if not prices_df.empty:
price = 1
result = self.util.commontools.get_yes_no_result(price)
self.write_to_db_result(fk=kpi_fk, numerator_id=sku, result=result)
self.util.add_kpi_result_to_kpi_results_df([kpi_fk, sku, None, price, None])
class HeroSkuStackingBySequenceNumberKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(HeroSkuStackingBySequenceNumberKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
if not self.dependencies_data[
self.dependencies_data['kpi_type'] ==
self.util.HERO_SKU_AVAILABILITY_SKU].empty:
hero_list = self.util.get_available_hero_sku_list(
self.dependencies_data)
if hero_list:
relevant_matches = self.util.filtered_matches[
self.util.filtered_matches['product_fk'].isin(hero_list)]
relevant_matches = relevant_matches.reset_index(drop=True)
relevant_matches['facing_sequence_number'] = relevant_matches[
'facing_sequence_number'].astype(str)
relevant_matches['all_sequences'] = relevant_matches.groupby(['scene_fk', 'bay_number', 'shelf_number', 'product_fk']) \
['facing_sequence_number'].apply(lambda x: (x + ',').cumsum().str.strip())
grouped_matches = relevant_matches.drop_duplicates(subset=[
'scene_fk', 'bay_number', 'shelf_number', 'product_fk'
],
keep='last')
grouped_matches['is_stack'] = grouped_matches.apply(
self.get_stack_data, axis=1)
stacking_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_STACKING)
for sku in hero_list:
stack_info = grouped_matches[
grouped_matches['product_fk'] ==
sku]['is_stack'].values.tolist()
score = 0
if any(stack_info):
score = 1
result = self.util.commontools.get_yes_no_result(score)
self.write_to_db_result(fk=stacking_kpi_fk,
numerator_id=sku,
score=score,
result=result)
self.util.add_kpi_result_to_kpi_results_df(
[stacking_kpi_fk, sku, None, result, score])
@staticmethod
def get_stack_data(row):
is_stack = False
sequences_list = row['all_sequences'][0:-1].split(',')
count_sequences = collections.Counter(sequences_list)
repeating_items = [c > 1 for c in count_sequences.values()]
if repeating_items:
if any(repeating_items):
is_stack = True
return is_stack
def test_get_available_hero_sku_list_retrieves_only_skus_in_store(self):
self.mock_scene_item_facts(pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='matches'))
self.mock_scene_info(DataTestUnitPEPSICOUK.scene_info)
self.mock_scene_kpi_results(DataTestUnitPEPSICOUK.scene_kpi_results_test_case_1)
util = PepsicoUtil(self.output, self.data_provider_mock)
availability_sku = HeroAvailabilitySkuKpi(self.data_provider_mock, config_params={})
availability_sku.calculate()
hero_dependency_df = pd.DataFrame(availability_sku.kpi_results)
hero_dependency_df['kpi_type'] = util.HERO_SKU_AVAILABILITY_SKU
hero_list = util.get_available_hero_sku_list(hero_dependency_df)
self.assertItemsEqual(hero_list, [1, 2])
class ShareOfAssortmentByHeroTypeKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(ShareOfAssortmentByHeroTypeKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def calculate(self):
lvl3_ass_res_df = self.dependencies_data
if not lvl3_ass_res_df.empty:
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.SHARE_OF_ASSORTMENT_BY_HERO_TYPE)
in_store_skus = len(
self.util.get_available_hero_sku_list(
self.dependencies_data)) # total recognized
location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf']\
[ScifConsts.LOCATION_TYPE_FK].values[0]
product_hero_df = self.util.all_products[[
ScifConsts.PRODUCT_FK, self.util.HERO_SKU_LABEL
]]
lvl3_ass_res_df = lvl3_ass_res_df.merge(
product_hero_df,
left_on='numerator_id',
right_on=ScifConsts.PRODUCT_FK,
how='left')
lvl3_ass_res_df = lvl3_ass_res_df.merge(
self.util.hero_type_custom_entity_df,
left_on=self.util.HERO_SKU_LABEL,
right_on='name',
how='left')
kpi_res_df = lvl3_ass_res_df.groupby(
[self.util.HERO_SKU_LABEL, 'entity_fk'],
as_index=False).agg({'numerator_result': np.sum})
kpi_res_df['in_store_total'] = in_store_skus
kpi_res_df['result'] = kpi_res_df.apply(self.get_result, axis=1)
kpi_res_df['score'] = kpi_res_df['result'].apply(
lambda x: 100 if x >= 100 else 0)
for i, res in kpi_res_df.iterrows():
self.write_to_db_result(
fk=kpi_fk,
numerator_id=res['entity_fk'],
numerator_result=res['numerator_result'],
result=res['result'],
denominator_id=self.util.store_id,
denominator_result=res['in_store_total'],
score=res['score'],
context_id=location_type_fk)
self.util.add_kpi_result_to_kpi_results_df([
kpi_fk, res['entity_fk'], res['entity_fk'], res['result'],
res['score'], location_type_fk
])
def kpi_type(self):
pass
@staticmethod
def get_result(row):
rv = float(
row['numerator_result']
) / row['in_store_total'] * 100 if row['in_store_total'] else 0
return rv
class SosVsTargetHeroSkuKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SosVsTargetHeroSkuKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self.util.HERO_SKU_SOS)
self.calculate_hero_sku_sos()
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_hero_sku_sos(self):
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_SOS)
filtered_scif = self.util.filtered_scif
location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \
[ScifConsts.LOCATION_TYPE_FK].values[0]
if (not filtered_scif.empty) and (not self.dependencies_data.empty):
category_df = filtered_scif.groupby([ScifConsts.CATEGORY_FK],
as_index=False).agg({
'updated_gross_length':
np.sum
})
category_df.rename(columns={'updated_gross_length': 'cat_len'},
inplace=True)
av_hero_list = self.util.get_available_hero_sku_list(
self.dependencies_data)
filtered_scif = filtered_scif[filtered_scif[
ScifConsts.PRODUCT_FK].isin(av_hero_list)]
unav_hero_list = self.util.get_unavailable_hero_sku_list(
self.dependencies_data)
unav_hero_df = self.util.all_products[self.util.all_products[ScifConsts.PRODUCT_FK].isin(unav_hero_list)] \
[[ScifConsts.PRODUCT_FK, ScifConsts.CATEGORY_FK]]
unav_hero_df['updated_gross_length'] = 0
filtered_scif = filtered_scif.append(unav_hero_df)
hero_cat_df = filtered_scif.groupby(
[ScifConsts.PRODUCT_FK, ScifConsts.CATEGORY_FK],
as_index=False).agg({'updated_gross_length': np.sum})
hero_cat_df = hero_cat_df.merge(category_df,
on=ScifConsts.CATEGORY_FK,
how='left')
hero_cat_df['cat_len'] = hero_cat_df['cat_len'].fillna(0)
hero_cat_df['sos'] = hero_cat_df.apply(self.calculate_sos, axis=1)
for i, row in hero_cat_df.iterrows():
self.write_to_db_result(
fk=kpi_fk,
numerator_id=row[ScifConsts.PRODUCT_FK],
numerator_result=row['updated_gross_length'],
denominator_id=row[ScifConsts.CATEGORY_FK],
denominator_result=row['cat_len'],
result=row['sos'],
context_id=location_type_fk)
self.util.add_kpi_result_to_kpi_results_df([
kpi_fk, row[ScifConsts.PRODUCT_FK],
row[ScifConsts.CATEGORY_FK], row['sos'], None, None
])
@staticmethod
def calculate_sos(row):
sos = 0
if row['cat_len'] != 0:
sos = float(row['updated_gross_length']) / row['cat_len'] * 100
return sos
class ShelfPlacementHeroSkusKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(ShelfPlacementHeroSkusKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
if not self.dependencies_data[
self.dependencies_data['kpi_type'] ==
self.util.HERO_SKU_AVAILABILITY_SKU].empty:
external_targets = self.util.commontools.all_targets_unpacked
shelf_placmnt_targets = external_targets[
external_targets['operation_type'] ==
self.util.SHELF_PLACEMENT]
kpi_fks = shelf_placmnt_targets['kpi_level_2_fk'].unique().tolist()
scene_placement_results = self.util.scene_kpi_results[
self.util.scene_kpi_results['kpi_level_2_fk'].isin(kpi_fks)]
if not scene_placement_results.empty:
hero_results = self.get_hero_results_df(
scene_placement_results)
if not hero_results.empty:
kpis_df = self.util.kpi_static_data[['pk', 'type']]
kpis_df.rename(columns={'pk': 'kpi_level_2_fk'},
inplace=True)
hero_results = hero_results.merge(kpis_df,
on='kpi_level_2_fk',
how='left')
hero_results['parent_type'] = hero_results['KPI Parent']
hero_results = hero_results[hero_results['type'] ==
self._config_params['level']]
if not hero_results.empty:
hero_results['type'] = hero_results['type'].apply(
lambda x: '{} {}'.format(self.util.HERO_PREFIX, x))
hero_results['parent_type'] = hero_results[
'parent_type'].apply(lambda x: '{} {}'.format(
self.util.HERO_PREFIX, x))
hero_results['kpi_level_2_fk'] = hero_results[
'type'].apply(
self.util.common.get_kpi_fk_by_kpi_type)
hero_results['KPI Parent'] = hero_results[
'parent_type'].apply(
self.util.common.get_kpi_fk_by_kpi_type)
hero_results['identifier_parent'] = hero_results.apply(
self.construct_hero_identifier_dict, axis=1)
for i, row in hero_results.iterrows():
self.write_to_db_result(
fk=row['kpi_level_2_fk'],
numerator_id=row['numerator_id'],
denominator_id=row['numerator_id'],
denominator_result=row['denominator_result'],
numerator_result=row['numerator_result'],
result=row['ratio'],
score=row['ratio'])
self.util.add_kpi_result_to_kpi_results_df([
row.kpi_level_2_fk, row.numerator_id,
row['numerator_id'], row['ratio'], row['ratio']
])
def get_hero_results_df(self, scene_placement_results):
kpi_results = scene_placement_results.groupby(
['kpi_level_2_fk', 'numerator_id'],
as_index=False).agg({'numerator_result': np.sum})
products_df = scene_placement_results.groupby(
['numerator_id'], as_index=False).agg({'numerator_result': np.sum})
products_df.rename(columns={'numerator_result': 'denominator_result'},
inplace=True)
kpi_results = kpi_results.merge(products_df,
on='numerator_id',
how='left')
hero_skus = self.util.get_available_hero_sku_list(
self.dependencies_data)
hero_results = kpi_results[kpi_results['numerator_id'].isin(hero_skus)]
kpi_parent = self.util.commontools.all_targets_unpacked.drop_duplicates(
subset=['kpi_level_2_fk', 'KPI Parent'])[[
'kpi_level_2_fk', 'KPI Parent'
]]
hero_results = hero_results.merge(kpi_parent, on='kpi_level_2_fk')
hero_results['ratio'] = hero_results[
'numerator_result'] / hero_results['denominator_result'] * 100
return hero_results
def get_kpi_type_by_pk(self, kpi_fk):
try:
kpi_fk = int(float(kpi_fk))
return self.util.kpi_static_data[self.util.kpi_static_data['pk'] ==
kpi_fk]['type'].values[0]
except IndexError:
Log.info(
"Kpi name: {} is not equal to any kpi name in static table".
format(kpi_fk))
return None
# @staticmethod
# def get_sku_ratio(row):
# ratio = row['count'] / row['total_facings']
# return ratio
@staticmethod
def construct_hero_identifier_dict(row):
id_dict = {
'kpi_fk': int(float(row['KPI Parent'])),
'sku': row['numerator_id']
}
return id_dict
class SosVsTargetHeroSkuKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SosVsTargetHeroSkuKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
sos_targets = self.util.sos_vs_target_targets.copy()
# sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']]
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self.util.HERO_SKU_SPACE_TO_SALES_INDEX)
self.calculate_hero_sku_sos_vs_target(sos_targets)
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_hero_sku_sos_vs_target(self, sos_targets):
kpi_filtered_products = self.util.filtered_scif['product_fk'].unique(
).tolist()
# hero_list = self.util.lvl3_ass_result[self.util.lvl3_ass_result['in_store'] == 1]['product_fk'].unique().tolist()
hero_list = self.util.get_available_hero_sku_list(
self.dependencies_data)
hero_list = filter(lambda x: x in kpi_filtered_products, hero_list)
sos_targets = sos_targets[sos_targets['type'] ==
self.util.HERO_SKU_SPACE_TO_SALES_INDEX]
sos_targets_hero_list = sos_targets['numerator_value'].values.tolist()
additional_skus = list(set(hero_list) - set(sos_targets_hero_list))
category_fk = self.util.all_products[self.util.all_products['category']
== 'CSN']['category_fk'].values[0]
hero_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_SPACE_TO_SALES_INDEX)
kpi_hero_parent = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_SOS_VS_TARGET)
additional_rows = []
for sku in additional_skus:
values_to_append = {
'numerator_id':
sku,
'numerator_type':
'product_fk',
'numerator_value':
sku,
'denominator_type':
'category_fk',
'denominator_value':
category_fk,
'Target':
None,
'denominator_id':
category_fk,
'kpi_level_2_fk':
hero_kpi_fk,
'KPI Parent':
kpi_hero_parent,
'identifier_parent':
self.util.common.get_dictionary(
kpi_fk=int(float(kpi_hero_parent)))
}
additional_rows.append(values_to_append)
df_to_append = pd.DataFrame.from_records(additional_rows)
sos_targets = sos_targets.append(df_to_append)
sos_targets = sos_targets[sos_targets['numerator_value'].isin(
hero_list)]
self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets)
def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets):
for i, row in sos_targets.iterrows():
general_filters = {
row['denominator_type']: row['denominator_value']
}
sos_filters = {row['numerator_type']: row['numerator_value']}
numerator_linear, denominator_linear = self.util.calculate_sos(
sos_filters, **general_filters)
result = numerator_linear / denominator_linear if denominator_linear != 0 else 0
score = result / row['Target'] if row['Target'] else 0
if row['Target']:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100,
score=score,
target=row['Target'] * 100)
else:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100)
self.util.add_kpi_result_to_kpi_results_df([
row.kpi_level_2_fk, row.numerator_id, row.denominator_id,
result * 100, score
])