class LinearBrandVsBrandIndexKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(LinearBrandVsBrandIndexKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
index_targets = self.util.get_relevant_sos_vs_target_kpi_targets(brand_vs_brand=True)
index_targets['numerator_id'] = index_targets.apply(self.util.retrieve_relevant_item_pks, axis=1,
args=('numerator_type', 'numerator_value'))
index_targets['denominator_id'] = index_targets.apply(self.util.retrieve_relevant_item_pks, axis=1,
args=('denominator_type', 'denominator_value'))
index_targets['identifier_parent'] = index_targets['KPI Parent'].apply(lambda x:
self.util.common.get_dictionary(
kpi_fk=int(float(x))))
index_targets = index_targets[index_targets['type'] == self._config_params['kpi_type']]
for i, row in index_targets.iterrows():
general_filters = {row['additional_filter_type_1']: row['additional_filter_value_1']}
numerator_sos_filters = {row['numerator_type']: row['numerator_value']}
num_num_linear, num_denom_linear = self.util.calculate_sos(numerator_sos_filters, **general_filters)
numerator_sos = num_num_linear/num_denom_linear if num_denom_linear else 0
denominator_sos_filters = {row['denominator_type']: row['denominator_value']}
denom_num_linear, denom_denom_linear = self.util.calculate_sos(denominator_sos_filters, **general_filters)
denominator_sos = denom_num_linear/denom_denom_linear if denom_denom_linear else 0
index = numerator_sos / denominator_sos if denominator_sos else 0
self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id,
numerator_result=num_num_linear, denominator_id=row.denominator_id,
denominator_result=denom_num_linear, result=index, score=index)
self.util.add_kpi_result_to_kpi_results_df([row.kpi_level_2_fk, row.numerator_id, row.denominator_id, index,
index])
class SosVsTargetSubBrandKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SosVsTargetSubBrandKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
sos_targets = self.util.sos_vs_target_targets.copy()
# sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']]
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self.util.SUB_BRAND_SPACE_TO_SALES_INDEX)
self.calculate_sub_brand_sos_vs_target(sos_targets)
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_sub_brand_sos_vs_target(self, sos_targets):
sos_targets = sos_targets[sos_targets['type'] ==
self.util.SUB_BRAND_SPACE_TO_SALES_INDEX]
session_sub_brands = self.util.filtered_scif['sub_brand'].unique(
).tolist()
session_sub_brands = filter(lambda v: v == v, session_sub_brands)
session_sub_brands = filter(lambda v: v is not None,
session_sub_brands)
targets_sub_brands = sos_targets['numerator_value'].values.tolist()
additional_sub_brands = list(
set(session_sub_brands) - set(targets_sub_brands))
category_fk = self.util.all_products[self.util.all_products['category']
== 'CSN']['category_fk'].values[0]
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.SUB_BRAND_SPACE_TO_SALES_INDEX)
kpi_parent = self.util.common.get_kpi_fk_by_kpi_type(
self.util.SUB_BRAND_SPACE_SOS_VS_TARGET)
additional_rows = []
for sub_brand in additional_sub_brands:
sub_brand_df = self.util.custom_entities[
self.util.custom_entities['name'] == sub_brand]
sub_brand_fk = sub_brand_df['pk'].values[0] if len(
sub_brand_df) > 0 else None
if sub_brand_fk is None:
Log.warning(
'Sub_brand: {} is not in custom_entity table. Please add'.
format(sub_brand))
else:
values_to_append = {
'numerator_id':
sub_brand_fk,
'numerator_type':
'sub_brand',
'numerator_value':
sub_brand,
'denominator_type':
'category_fk',
'denominator_value':
category_fk,
'Target':
None,
'denominator_id':
category_fk,
'kpi_level_2_fk':
kpi_fk,
'KPI Parent':
kpi_parent,
'identifier_parent':
self.util.common.get_dictionary(
kpi_fk=int(float(kpi_parent)))
}
additional_rows.append(values_to_append)
df_to_append = pd.DataFrame.from_records(additional_rows)
sos_targets = sos_targets.append(df_to_append)
sos_targets = sos_targets[sos_targets['numerator_value'].isin(
session_sub_brands)]
self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets)
def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets):
for i, row in sos_targets.iterrows():
general_filters = {
row['denominator_type']: row['denominator_value']
}
sos_filters = {row['numerator_type']: row['numerator_value']}
numerator_linear, denominator_linear = self.util.calculate_sos(
sos_filters, **general_filters)
result = numerator_linear / denominator_linear if denominator_linear != 0 else 0
score = result / row['Target'] if row['Target'] else 0
if row['Target']:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100,
score=score,
target=row['Target'] * 100)
else:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100)
self.util.add_kpi_result_to_kpi_results_df([
row.kpi_level_2_fk, row.numerator_id, row.denominator_id,
result * 100, score
])
class SosVsTargetSegmentKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SosVsTargetSegmentKpi, self).__init__(data_provider, config_params=config_params, **kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
sos_targets = self.util.sos_vs_target_targets.copy()
# sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']]
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX)
self.calculate_pepsico_segment_space_sos_vs_target(sos_targets)
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_pepsico_segment_space_sos_vs_target(self, sos_targets):
sos_targets = sos_targets[sos_targets['type'] == self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX]
session_sub_category_list = self.util.filtered_scif['sub_category_fk'].unique().tolist()
session_sub_category_list = filter(lambda v: v == v, session_sub_category_list)
session_sub_category_list = filter(lambda v: v is not None, session_sub_category_list)
targets_sub_category_list = sos_targets['denominator_value'].values.tolist()
additional_sub_categories = list(set(session_sub_category_list) - set(targets_sub_category_list))
kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(self.util.PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX)
kpi_parent = self.util.common.get_kpi_fk_by_kpi_type(self.util.PEPSICO_SEGMENT_SOS_VS_TARGET)
additional_rows = []
for sub_category in additional_sub_categories:
values_to_append = {'numerator_id': self.util.own_manuf_fk, 'numerator_type': 'manufacturer_fk',
'numerator_value': self.util.own_manuf_fk, 'denominator_type': 'sub_category_fk',
'denominator_value': sub_category, 'Target': None, 'denominator_id': sub_category,
'kpi_level_2_fk': kpi_fk, 'KPI Parent': kpi_parent,
'identifier_parent': self.util.common.get_dictionary(kpi_fk=int(float(kpi_parent)))}
additional_rows.append(values_to_append)
df_to_append = pd.DataFrame.from_records(additional_rows)
sos_targets = sos_targets.append(df_to_append)
sos_targets = sos_targets[sos_targets['denominator_value'].isin(session_sub_category_list)]
self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets)
def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets):
for i, row in sos_targets.iterrows():
general_filters = {row['denominator_type']: row['denominator_value']}
sos_filters = {row['numerator_type']: row['numerator_value']}
numerator_linear, denominator_linear = self.util.calculate_sos(sos_filters, **general_filters)
result = numerator_linear / denominator_linear if denominator_linear != 0 else 0
score = result / row['Target'] if row['Target'] else 0
if row['Target']:
self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id,
numerator_result=numerator_linear, denominator_id=row.denominator_id,
denominator_result=denominator_linear, result=result * 100, score=score,
target=row['Target'] * 100)
else:
self.write_to_db_result(fk=row.kpi_level_2_fk, numerator_id=row.numerator_id,
numerator_result=numerator_linear, denominator_id=row.denominator_id,
denominator_result=denominator_linear, result=result * 100)
self.util.add_kpi_result_to_kpi_results_df(
[row.kpi_level_2_fk, row.numerator_id, row.denominator_id, result * 100,
score])
class SosVsTargetHeroSkuKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(SosVsTargetHeroSkuKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
sos_targets = self.util.sos_vs_target_targets.copy()
# sos_targets = sos_targets[sos_targets['type'] == self._config_params['kpi_type']]
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self.util.HERO_SKU_SPACE_TO_SALES_INDEX)
self.calculate_hero_sku_sos_vs_target(sos_targets)
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()
def calculate_hero_sku_sos_vs_target(self, sos_targets):
kpi_filtered_products = self.util.filtered_scif['product_fk'].unique(
).tolist()
# hero_list = self.util.lvl3_ass_result[self.util.lvl3_ass_result['in_store'] == 1]['product_fk'].unique().tolist()
hero_list = self.util.get_available_hero_sku_list(
self.dependencies_data)
hero_list = filter(lambda x: x in kpi_filtered_products, hero_list)
sos_targets = sos_targets[sos_targets['type'] ==
self.util.HERO_SKU_SPACE_TO_SALES_INDEX]
sos_targets_hero_list = sos_targets['numerator_value'].values.tolist()
additional_skus = list(set(hero_list) - set(sos_targets_hero_list))
category_fk = self.util.all_products[self.util.all_products['category']
== 'CSN']['category_fk'].values[0]
hero_kpi_fk = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_SPACE_TO_SALES_INDEX)
kpi_hero_parent = self.util.common.get_kpi_fk_by_kpi_type(
self.util.HERO_SKU_SOS_VS_TARGET)
additional_rows = []
for sku in additional_skus:
values_to_append = {
'numerator_id':
sku,
'numerator_type':
'product_fk',
'numerator_value':
sku,
'denominator_type':
'category_fk',
'denominator_value':
category_fk,
'Target':
None,
'denominator_id':
category_fk,
'kpi_level_2_fk':
hero_kpi_fk,
'KPI Parent':
kpi_hero_parent,
'identifier_parent':
self.util.common.get_dictionary(
kpi_fk=int(float(kpi_hero_parent)))
}
additional_rows.append(values_to_append)
df_to_append = pd.DataFrame.from_records(additional_rows)
sos_targets = sos_targets.append(df_to_append)
sos_targets = sos_targets[sos_targets['numerator_value'].isin(
hero_list)]
self.calculate_and_write_to_db_sos_vs_target_kpi_results(sos_targets)
def calculate_and_write_to_db_sos_vs_target_kpi_results(self, sos_targets):
for i, row in sos_targets.iterrows():
general_filters = {
row['denominator_type']: row['denominator_value']
}
sos_filters = {row['numerator_type']: row['numerator_value']}
numerator_linear, denominator_linear = self.util.calculate_sos(
sos_filters, **general_filters)
result = numerator_linear / denominator_linear if denominator_linear != 0 else 0
score = result / row['Target'] if row['Target'] else 0
if row['Target']:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100,
score=score,
target=row['Target'] * 100)
else:
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=numerator_linear,
denominator_id=row.denominator_id,
denominator_result=denominator_linear,
result=result * 100)
self.util.add_kpi_result_to_kpi_results_df([
row.kpi_level_2_fk, row.numerator_id, row.denominator_id,
result * 100, score
])
class LinearBrandVsBrandIndexKpi(UnifiedCalculationsScript):
def __init__(self, data_provider, config_params=None, **kwargs):
super(LinearBrandVsBrandIndexKpi,
self).__init__(data_provider,
config_params=config_params,
**kwargs)
self.util = PepsicoUtil(None, data_provider)
def kpi_type(self):
pass
def calculate(self):
self.util.filtered_scif, self.util.filtered_matches = \
self.util.commontools.set_filtered_scif_and_matches_for_specific_kpi(self.util.filtered_scif,
self.util.filtered_matches,
self._config_params['kpi_type'])
index_targets = self.util.get_relevant_sos_vs_target_kpi_targets(
brand_vs_brand=True)
index_targets['numerator_id'] = index_targets.apply(
self.util.retrieve_relevant_item_pks,
axis=1,
args=('numerator_type', 'numerator_value'))
index_targets['denominator_id'] = index_targets.apply(
self.util.retrieve_relevant_item_pks,
axis=1,
args=('denominator_type', 'denominator_value'))
index_targets['identifier_parent'] = index_targets['KPI Parent'].apply(
lambda x: self.util.common.get_dictionary(kpi_fk=int(float(x))))
index_targets = index_targets[index_targets['type'] ==
self._config_params['kpi_type']]
location_type_fk = self.util.all_templates[self.util.all_templates[ScifConsts.LOCATION_TYPE] == 'Primary Shelf'] \
[ScifConsts.LOCATION_TYPE_FK].values[0]
for i, row in index_targets.iterrows():
general_filters = {
row['additional_filter_type_1']:
row['additional_filter_value_1']
}
numerator_sos_filters = {
row['numerator_type']: row['numerator_value']
}
num_num_linear, num_denom_linear = self.util.calculate_sos(
numerator_sos_filters, **general_filters)
numerator_sos = num_num_linear / num_denom_linear if num_denom_linear else 0
denominator_sos_filters = {
row['denominator_type']: row['denominator_value']
}
denom_num_linear, denom_denom_linear = self.util.calculate_sos(
denominator_sos_filters, **general_filters)
denominator_sos = denom_num_linear / denom_denom_linear if denom_denom_linear else 0
if denominator_sos == 0:
index = 0 if numerator_sos == 0 else 1
else:
index = numerator_sos / denominator_sos
self.write_to_db_result(fk=row.kpi_level_2_fk,
numerator_id=row.numerator_id,
numerator_result=num_num_linear,
denominator_id=row.denominator_id,
denominator_result=denom_num_linear,
result=index,
score=index,
context_id=location_type_fk)
self.util.add_kpi_result_to_kpi_results_df([
row.kpi_level_2_fk, row.numerator_id, row.denominator_id,
index, index, None
])
self.util.reset_filtered_scif_and_matches_to_exclusion_all_state()