def run_project_calculations(self):
self.timer.start()
data_path = os.path.join(os.path.dirname(os.path.realpath(__file__)),
'Data')
survey_template_path = os.path.join(data_path, 'SurveyTemplate.xlsx')
eye_hand_lvl_template_path = os.path.join(data_path,
'eye_level_jnjuk.xlsx')
exclusive_template_path = os.path.join(data_path,
'KPI Exclusions Template.xlsx')
survey_template = pd.read_excel(survey_template_path,
sheetname='Sheet1')
eye_hand_lvl_template = pd.read_excel(eye_hand_lvl_template_path)
exclusive_template = pd.read_excel(exclusive_template_path)
common = Common(self.data_provider)
jnj_generator = JNJGenerator(self.data_provider, self.output, common,
exclusive_template)
jnj_generator.linear_sos_out_of_store_discovery_report()
jnj_generator.secondary_placement_location_quality(survey_template)
jnj_generator.secondary_placement_location_visibility_quality(
survey_template)
jnj_generator.share_of_shelf_manufacturer_out_of_sub_category()
jnj_generator.calculate_auto_assortment(in_balde=False)
jnj_generator.promo_calc_recovery()
jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template)
jnj_generator.general_assortment_calculation()
jnj_generator.osa_calculation()
common.commit_results_data()
jnj_generator.tool_box.commit_osa_queries()
self.timer.stop('KPIGenerator.run_project_calculations')
def run_project_calculations(self):
self.timer.start()
eye_level_data, exclusion_data = self._parse_templates_for_calculations()
common = Common(self.data_provider)
try:
jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusion_data)
self.calculate_local_assortment(jnj_generator, self.data_provider, self.output, common, exclusion_data)
# Mobile KPIs with hierarchy
jnj_generator.calculate_auto_assortment(in_balde=False, hierarchy=True,
just_primary=False, filter_sub_categories=False)
jnj_generator.eye_hand_level_sos_calculation(eye_level_data, hierarchy=True)
jnj_generator.promo_calc(hierarchy=True)
jnj_generator.lsos_with_hierarchy()
# API global KPIs
jnj_generator.linear_sos_out_of_store_discovery_report()
jnj_generator.calculate_auto_assortment(in_balde=False)
jnj_generator.promo_calc()
jnj_generator.eye_hand_level_sos_calculation(eye_level_data)
except Exception as e:
print ("Error :{}".format(e))
common.commit_results_data()
self.timer.stop('KPIGenerator.run_project_calculations')
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.tool_box = MONDELEZUSToolBox(self.data_provider, self.output,
self.common)
self.cstore_tool_box = CSTOREToolBox(self.data_provider, self.output,
self.common)
self.sos_tool_box = MONDELEZUSSOSToolBox(self.data_provider,
self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
self.cstore_tool_box.main_calculation()
self.sos_tool_box.main_calculation()
for kpi_set_fk in self.tool_box.kpi_new_static_data['pk'].unique(
).tolist():
self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk)
self.common.commit_results_data()
class INBEVNLINBEVBEGenerator:
def __init__(self, data_provider, output):
self.k_engine = BaseCalculationsGroup(data_provider, output)
self.data_provider = data_provider
self.project_name = data_provider.project_name
self.output = output
self.session_uid = self.data_provider.session_uid
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.session_info = SessionInfo(data_provider)
self.store_id = self.data_provider[Data.STORE_FK]
self.common = Common(self.data_provider)
self.tool_box = INBEVToolBox(self.data_provider, self.output, common=self.common)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
return
# self.tool_box.tools.update_templates()
# set_names = ['Product Blocking', 'Linear Share of Shelf', 'OSA', 'Pallet Presence', 'Share of Assortment',
# 'Product Stacking', 'Shelf Level', 'Linear Share of Shelf vs. Target', 'Shelf Impact Score',
# 'Product Group Blocking']
# for kpi_set_name in set_names:
# self.tool_box.main_calculation(set_name=kpi_set_name)
# self.tool_box.save_custom_scene_item_facts_results()
# self.tool_box.save_linear_length_results()
# Log.info('Downloading templates took {}'.format(self.tool_box.download_time))
self.tool_box.main_calculation_poce()
self.tool_box.commit_results_data()
self.common.commit_results_data()
class SceneGenerator:
def __init__(self, data_provider, output=None):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.scene_tool_box = CCUSSceneToolBox(self.data_provider, self.output, self.common)
self.pillar_scene_tool_box = PillarsSceneToolBox(self.data_provider, self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def scene_score(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.scene_tool_box.match_product_in_scene.empty:
Log.warning('Match product in scene is empty for this scene')
else:
self.scene_tool_box.scene_score()
self.pillar_scene_tool_box.is_scene_belong_to_program()
self.common.commit_results_data(result_entity='scene')
del self.scene_tool_box
del self.pillar_scene_tool_box
def run_project_calculations(self):
self.timer.start()
eye_hand_lvl_template_path = os.path.join(os.path.dirname(os.path.dirname(os.path.realpath(__file__))),
'JNJDE', 'Data', 'eye_level_jnjde.xlsx')
eye_hand_lvl_template = pd.read_excel(eye_hand_lvl_template_path)
common = Common(self.data_provider)
jnj_generator = JNJGenerator(self.data_provider, self.output, common)
jnj_generator.linear_sos_out_of_store_discovery_report()
# KPI 2 - Share of Shelf
jnj_generator.sos_vs_target_calculation()
# KPI 3 - OOS
jnj_generator.calculate_auto_assortment()
# KPI 4 - Share of shelf - Hand & Eye
# KPI 12 - New H&E KPI
# KPI 13 - Share of shelf - Hand & Eye (Sub-Category)
jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template)
jnj_generator.eye_hand_level_sos_calculation_de(eye_hand_lvl_template)
# KPI 5 IR - Activation compliance vs plans
jnj_generator.promo_calc(sales_reps_date='2018-09-30', calc_brand_level=True)
# KPI 9 - MSL
jnj_generator.assortment_calculation()
# KPI 10 - New Display compliance
jnj_generator.display_compliance_calculation()
# Competitor KPI's
jnj_generator.sos_brand_out_of_sub_category()
jnj_generator.competitor_eye_hand_level_sos(eye_hand_lvl_template)
common.commit_results_data()
self.timer.stop('KPIGenerator.run_project_calculations')
class Generator:
# SUPER_CATS = ['Yogurt', 'RBG', 'Mexican', 'Soup']
SUPER_CATS = ['Yogurt', 'RBG', 'Soup', 'Mexican']
# SUPER_CATS = ['Mexican'] # Overwriting for testing purposes
SUPER_CATS = ['Snacks', 'Yogurt']
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.common = Common(self.data_provider)
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.toolboxes = {}
self.load_toolboxes()
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
for cat in self.SUPER_CATS:
if self.toolboxes[cat].scif.empty:
Log.warning('Distribution is empty for this session')
continue
template_path = self.find_template(cat)
# ResultUploader(self.project_name, template_path)
# EntityUploader(self.project_name, template_path)
# AtomicFarse(self.project_name, template_path)
self.toolboxes[cat].main_calculation(template_path)
self.common.commit_results_data()
def load_toolboxes(self):
ToolBox = 'imma lazy and no like red lines'
for cat in self.SUPER_CATS:
exec('from Projects.GMIUS.{0}.Utils.KPIToolBox import ToolBox'.
format(cat))
self.toolboxes[cat] = ToolBox(self.data_provider, self.output,
self.common)
def find_template(self, cat):
''' screw maintaining 4 hardcoded template paths... '''
path = os.path.join(os.path.dirname(os.path.realpath(__file__)),
'Data')
files = os.listdir(path)
candidates = [
f for f in files
if f.split(' ')[0] == cat and f.split('.')[-1] == 'xlsx'
]
versioned_candidates = []
all_vers = []
for x in candidates:
all_vers += x.split(' v')[-1].replace('.xlsx', '').split('.')
max_digits = len(max(all_vers, key=len))
for x in candidates:
version_comps = x.split(' v')[-1].replace('.xlsx', '').split('.')
normed_components = []
for i, comp in enumerate(version_comps):
norm_comp = int(comp) * 10**max_digits
normed_components.append(str(norm_comp))
versioned_candidates.append((float(''.join(normed_components)), x))
template = sorted(versioned_candidates, key=lambda x: x[0])[-1][1]
return os.path.join(path, template)
def run_project_calculations(self):
self.timer.start()
eye_level_data, exclusion_data = self._parse_templates_for_calculations(
)
common = Common(self.data_provider)
jnj_generator = JNJGenerator(self.data_provider, self.output, common,
exclusion_data)
jnj_generator.linear_sos_out_of_store_discovery_report()
jnj_generator.share_of_shelf_manufacturer_out_of_sub_category()
jnj_generator.calculate_auto_assortment()
jnj_generator.eye_hand_level_sos_calculation(eye_level_data)
jnj_generator.promo_calc(sales_reps_date='2019-06-30')
common.commit_results_data()
self.timer.stop('KPIGenerator.run_project_calculations')
def run_project_calculations(self):
self.timer.start()
eye_hand_lvl_template, survey_template = self._parse_templates_for_calculations(
)
common = Common(self.data_provider)
jnj_generator = JNJGenerator(self.data_provider, self.output, common)
jnj_generator.secondary_placement_location_quality(survey_template)
jnj_generator.secondary_placement_location_visibility_quality(
survey_template)
jnj_generator.calculate_auto_assortment()
jnj_generator.promo_calc(sales_reps_date='2018-09-30')
jnj_generator.eye_hand_level_sos_calculation(eye_hand_lvl_template)
jnj_generator.linear_sos_out_of_store_discovery_report()
common.commit_results_data()
self.timer.stop('KPIGenerator.run_project_calculations')
class NESTLETHSceneToolBox:
ERROR_LOC = -1
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.templates = self.data_provider[Data.TEMPLATES]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_type = self.data_provider.store_type
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.nestle_generator = SceneGenerator(self.data_provider, self.output,
self.common)
def main_function(self):
shelf_placement_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Placement.xlsx'),
sheetname='Minimum Shelf',
skiprows=1,
keep_default_na=False)
shelf_placement_dict = self.nestle_generator.nestle_global_shelf_placement_function(
shelf_placement_template)
if shelf_placement_dict == self.ERROR_LOC:
Log.warning(
"Shelf placement failed for scene - {0} "
"Match product in scene doesnt contain Nestle product in layer 1 "
.format(self.scene_info['scene_fk'].iloc[0]))
return 0
self.common.save_json_to_new_tables(shelf_placement_dict)
scene_availability_dict = self.nestle_generator.nestle_scene_availability_function(
)
self.common.save_json_to_new_tables(scene_availability_dict)
self.common.commit_results_data(result_entity='scene')
return 0
class SceneGenerator:
def __init__(self, data_provider, output=None):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
@log_runtime('Total CCBOTTLERSUS_SANDSceneCalculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
self.calculate_scene_redscore()
self.calculate_scene_coke_cooler()
self.common.commit_results_data(result_entity='scene')
@log_runtime('Scene RedScore Calculations', log_start=True)
def calculate_scene_redscore(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
tool_box = CCBOTTLERSUS_SANDSceneRedToolBox(self.data_provider,
self.output, self.common)
if tool_box.match_product_in_scene.empty:
Log.warning('Match product in scene is empty for this scene')
else:
if tool_box.main_calculation():
pass
# self.common.commit_results_data(result_entity='scene')
@log_runtime('Scene Coke Cooler Calculations', log_start=True)
def calculate_scene_coke_cooler(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
tool_box = CCBOTTLERSUS_SANDSceneCokeCoolerToolbox(
self.data_provider, self.output, self.common)
if tool_box.match_product_in_scene.empty:
Log.warning('Match product in scene is empty for this scene')
else:
if tool_box.main_calculation():
pass
def run_project_calculations(self):
self.timer.start()
eye_level_data, exclusion_data, survey_data = self._parse_templates_for_calculations()
common = Common(self.data_provider)
jnj_generator = JNJGenerator(self.data_provider, self.output, common, exclusion_data)
jnj_generator.linear_sos_out_of_store_discovery_report()
jnj_generator.secondary_placement_location_quality(survey_data)
jnj_generator.secondary_placement_location_visibility_quality(survey_data)
jnj_generator.share_of_shelf_manufacturer_out_of_sub_category()
jnj_generator.calculate_auto_assortment(in_balde=False)
jnj_generator.promo_calc_recovery()
jnj_generator.eye_hand_level_sos_calculation(eye_level_data)
jnj_generator.general_assortment_calculation()
jnj_generator.osa_calculation()
common.commit_results_data()
jnj_generator.tool_box.commit_osa_queries()
self.timer.stop('KPIGenerator.run_project_calculations')
class PlanogramGenerator:
def __init__(self, data_provider, output=None):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.scene_tool_box = BATAUPlanogramToolBox(self.data_provider,
self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def planogram_score(self):
if self.scene_tool_box.match_product_in_scene.empty:
Log.warning('Match product in scene is empty for this scene')
else:
self.scene_tool_box.main_function()
self.common.commit_results_data()
class SceneGenerator:
def __init__(self, data_provider):
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = SceneToolBox(self.data_provider, self.common)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
Scene level.
"""
self.tool_box.main_calculation()
self.common.commit_results_data(result_entity='scene')
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.common = Common(self.data_provider)
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = ToolBox(self.data_provider, self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
if self.tool_box.scif.empty:
Log.warning('Distribution is empty for this session')
else:
template_path = self.find_template('Template')
comp_path = self.find_template('Competitive')
adj_path = self.find_template('Adjacencies')
# EntityUploader(self.project_name, template_path)
# AtomicFarse(self.project_name, template_path)
self.tool_box.main_calculation(template_path, comp_path, adj_path)
self.common.commit_results_data()
def find_template(self, name):
''' screw maintaining hardcoded template paths... '''
path = os.path.join(os.path.dirname(os.path.realpath(__file__)),
'Data')
files = os.listdir(path)
candidates = [
f for f in files if f.split('.')[-1] == 'xlsx' and name in f
]
versioned_candidates = []
all_vers = []
for x in candidates:
all_vers += x.split(' v')[-1].replace('.xlsx', '').split('.')
max_digits = len(max(all_vers, key=len))
for x in candidates:
version_comps = x.split(' v')[-1].replace('.xlsx', '').split('.')
normed_components = []
for i, comp in enumerate(version_comps):
norm_comp = int(comp) * 10**max_digits
normed_components.append(str(norm_comp))
versioned_candidates.append((float(''.join(normed_components)), x))
template = sorted(versioned_candidates, key=lambda x: x[0])[-1][1]
return os.path.join(path, template)
class DIAGEOUKSceneToolBox(SceneBaseClass):
def __init__(self, data_provider):
super(DIAGEOUKSceneToolBox, self).__init__(data_provider)
self.common = Common(self._data_provider)
self.diageo_generator = DIAGEOGenerator(self._data_provider, None,
self.common)
def calculate_kpis(self):
if self.is_scene_relevant():
self.diageo_generator.diageo_global_equipment_score(
save_scene_level=True)
if not self.common.kpi_results.empty:
self.common.commit_results_data(result_entity='scene')
def is_scene_relevant(self):
if self.diageo_generator.scif.empty:
return False
else:
return True if self.diageo_generator.scif['template_name'].values[
0] == 'ON - DRAUGHT TAPS' else False
class SceneGenerator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.common = Common(self.data_provider)
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.tool_box = SceneGPUSToolBox(self.data_provider, self.common,
self.output)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
self.tool_box.main_calculation()
self.common.commit_results_data(result_entity='scene')
class SceneGenerator:
def __init__(self, data_provider, output=None):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(data_provider)
self.scene_tool_box = SINOTHSceneToolBox(self.data_provider,
self.output, self.common)
@log_runtime('Total Calculations', log_start=True)
def scene_main_calculation(self):
"""
This is the main KPI calculation function.
"""
if self.scene_tool_box.match_product_in_scene.empty:
Log.warning('Match product in scene is empty for this scene')
else:
self.scene_tool_box.filter_and_send_kpi_to_calc()
self.common.commit_results_data(result_entity='scene')
class NESTLETHToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.nestle_generator = NESTLEGenerator(self.data_provider,
self.output, self.common)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
linear_sos_dict = self.nestle_generator.nestle_global_linear_sos_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
assortment_dict = self.nestle_generator.nestle_global_assortment_function(
)
self.common.save_json_to_new_tables(assortment_dict)
self.common.commit_results_data()
return
class Generator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.project_name = data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.common = Common(self.data_provider)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
# if self.tool_box.scif.empty:
# Log.warning('Scene item facts is empty for this session')
# self.tool_box.main_calculation()
# self.tool_box.commit_results()
# assortment = Assortment(self.data_provider, common=common)
# if assortment.store_assortment.empty:
# Log.warning('Scene item facts is empty for this session')
if self.data_provider['scene_item_facts'].empty:
Log.warning('Scene item facts is empty for this session')
else:
PuestosFijosToolBox(self.data_provider, self.output,
self.common).main_calculation()
ComidasToolBox(self.data_provider, self.output,
self.common).main_calculation()
EspecializadoToolBox(self.data_provider, self.output,
self.common).main_calculation()
FONDASToolBox(self.data_provider, self.output,
self.common).main_calculation()
NationalToolBox(self.data_provider, self.output,
self.common).main_calculation()
ToolBox(self.data_provider, self.output,
self.common).main_calculation()
self.common.commit_results_data()
class SceneCalculations(SceneBaseClass):
def __init__(self, data_provider):
super(SceneCalculations, self).__init__(data_provider)
# self.scene_generator = SceneGenerator(self._data_provider)
self.common = Common(self._data_provider)
# self._monitor = None
# self.timer = self._monitor.Timer('Perform', 'Init Session')
def calculate_kpis(self):
# self.timer.start()
# self.scene_generator.scene_score()
template_path = os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))),
'Data', 'Brand Score.xlsx')
diageo_generator = DIAGEOGenerator(self._data_provider, None,
self.common)
diageo_generator.diageo_global_tap_brand_score_function(
template_path=template_path,
rank_kpi=False,
sub_category_rank_kpi=False,
save_scene_level=True,
calculate_components=True)
self.common.commit_results_data(result_entity='scene')
def create_graph_image(scene_id, graph):
filtered_figure = GraphPlot.plot_networkx_graph(
graph,
overlay_image=True,
scene_id=scene_id,
project_name='diageous-sand2')
filtered_figure.update_layout(autosize=False,
width=1000,
height=800,
title=str(scene_id))
iplot(filtered_figure)
if __name__ == '__main__':
from KPIUtils_v2.DB.CommonV2 import Common
from Trax.Utils.Conf.Configuration import Config
from Trax.Algo.Calculations.Core.DataProvider import KEngineDataProvider
from Trax.Cloud.Services.Connector.Logger import LoggerInitializer
LoggerInitializer.init('KEngine')
Config.init('KEngine')
test_data_provider = KEngineDataProvider('diageous')
sessions = ['2087F0BA-E12A-458A-83D0-0713E9DF1EBA']
for session in sessions:
print(session)
test_data_provider.load_session_data(session)
test_common = Common(test_data_provider)
case_counter_calculator = CaseCountCalculator(test_data_provider,
test_common)
case_counter_calculator.main_case_count_calculations()
test_common.commit_results_data()
class PsApacGSKAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.set_up_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'gsk_set_up.xlsx'),
sheet_name='Functional KPIs',
keep_default_na=False)
self.gsk_generator = GSKGenerator(self.data_provider, self.output,
self.common, self.set_up_template)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
assortment_store_dict = self.gsk_generator.availability_store_function(
)
self.common.save_json_to_new_tables(assortment_store_dict)
assortment_category_dict = self.gsk_generator.availability_category_function(
)
self.common.save_json_to_new_tables(assortment_category_dict)
assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function(
)
self.common.save_json_to_new_tables(assortment_subcategory_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_whole_store_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_by_sub_category_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_by_category_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
self.common.commit_results_data()
return
class CCUSGenerator:
def __init__(self, data_provider, output):
self.data_provider = data_provider
self.output = output
self.common = Common(self.data_provider)
@log_runtime('Total Calculations', log_start=True)
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
# self.calculate_fsop()
# self.calculate_manufacturer_displays()
# # self.calculate_obbo()
# # self.calculate_dunkin_donuts()
# self.calculate_monster()
# self.calculate_programs()
# self.calculate_holiday_programs()
# # self.calculate_msc_new()
# # self.calculate_gold_peak_block()
# self.calculate_special_programs()
# self.calculate_validation()
self.calculate_pillars_programs()
# self.calculate_jeff()
self.calculate_military()
self.common.commit_results_data()
@log_runtime('Manufacturer Displays Calculations')
def calculate_manufacturer_displays(self):
tool_box = DISPLAYSToolBox(self.data_provider, self.output)
tool_box.main_calculation()
tool_box.commit_results_data()
del tool_box
@log_runtime('MONSTER Calculations')
def calculate_monster(self):
tool_box = MONSTERToolBox(self.data_provider, self.output)
tool_box.main_calculation()
tool_box.commit_results_data(kpi_set_fk=27)
del tool_box
@log_runtime('FSOP Calculations')
def calculate_fsop(self):
tool_box = FSOPToolBox(self.data_provider, self.output, self.common)
tool_box.main_calculation()
del tool_box
@log_runtime('JEFF Calculations')
def calculate_jeff(self):
tool_box = JEFFToolBox(self.data_provider, self.output, self.common)
tool_box.main_calculation()
del tool_box
# @log_runtime('OBBO Calculations')
# def calculate_obbo(self):
# tool_box = OBBOToolBox(self.data_provider, self.output)
# tool_box.main_calculation()
# tool_box.commit_results_data()
#
# @log_runtime('Dunkin Donuts Calculations')
# def calculate_dunkin_donuts(self):
# set_name = 'Dunkin Donuts'
# tool_box = CCUSToolBox(self.data_provider, self.output)
# if tool_box.scif.empty:
# Log.warning('Scene item facts is empty for this session')
# tool_box.tools.update_templates()
# tool_box.main_calculation(set_name=set_name)
# tool_box.save_level1(set_name=set_name, score=100)
# set_fk = tool_box.kpi_static_data[tool_box.kpi_static_data['kpi_set_name'] == set_name]['kpi_set_fk'].values[0]
# tool_box.commit_results_data(kpi_set_fk=set_fk)
# Log.info('Dunkin Donuts: Downloading templates took {}'.format(tool_box.download_time))
@log_runtime('Programs Calculations')
def calculate_programs(self):
tool_box = PROGRAMSToolBox(self.data_provider, self.output)
tool_box.main_calculation()
tool_box.commit_results_data(kpi_set_fk=28)
del tool_box
@log_runtime('Pillars Programs Calculations')
def calculate_pillars_programs(self):
tool_box = PillarsPROGRAMSToolBox(self.data_provider, self.output, self.common)
tool_box.main_calculation()
del tool_box
# @log_runtime('MSC New Calculations')
# def calculate_msc_new(self):
# tool_box = MSC_NEWToolBox(self.data_provider, self.output)
# tool_box.main_calculation()
# tool_box.commit_results_data(kpi_set_fk=29)
@log_runtime('Holiday Programs Calculations')
def calculate_holiday_programs(self):
tool_box = HOLIDAYToolBox(self.data_provider, self.output)
tool_box.main_calculation()
tool_box.commit_results_data(kpi_set_fk=31)
del tool_box
# @log_runtime('Programs Calculations')
# def calculate_gold_peak_block(self):
# tool_box = GOLD_PEAK_BLOCKToolBox(self.data_provider, self.output)
# tool_box.main_calculation()
# tool_box.commit_results_data(kpi_set_fk = 30)
@log_runtime('Special Programs Calculations')
def calculate_special_programs(self):
tool_box = SpecialProgramsToolBox(self.data_provider, self.output)
tool_box.main_calculation()
tool_box.commit_results_data(kpi_set_fk=32)
del tool_box
@log_runtime('Special Programs Calculations')
def calculate_validation(self):
tool_box = VALIDATIONToolBox(self.data_provider, self.output, kpi_set_fk=34)
tool_box.main_calculation()
tool_box.commit_results_data()
del tool_box
@log_runtime('Military Calculations')
def calculate_military(self):
tool_box = MilitaryToolBox(self.data_provider, self.output, self.common)
tool_box.main_calculation()
del tool_box
class TWEAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'Template.xlsx')
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.templates = self.data_provider[Data.TEMPLATES]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_name = self.store_info.store_name[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.empty_product_ids = self.all_products.query(
'product_name.str.contains("empty", case=False) or'
' product_name.str.contains("irrelevant", case=False)',
engine='python')['product_fk'].values
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
# 1. calculate the macro linear sheet
self.calculate_macro_linear()
# 2. calculate the zone based sheet
self.calculate_zone_based()
self.common.commit_results_data()
score = 0
return score
def calculate_zone_based(self):
zone_kpi_sheet = self.get_template_details(ZONE_KPI_SHEET)
zone_category_sheet = self.get_template_details(ZONE_CATEGORY_SHEET)
name_grouped_zone_kpi_sheet = zone_kpi_sheet.groupby(KPI_TYPE)
for each_kpi in name_grouped_zone_kpi_sheet:
each_kpi_type = each_kpi[0]
kpi_sheet_rows = each_kpi[1]
denominator_row = pd.Series()
write_sku = False
kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY)
& (self.kpi_static_data[TYPE] == each_kpi_type)
& (self.kpi_static_data['delete_time'].isnull())]
if kpi.empty:
print("KPI Name:{} not found in DB".format(each_kpi_type))
else:
print("KPI Name:{} found in DB".format(each_kpi_type))
if 'sku_all' in each_kpi_type.lower():
write_sku = True
if 'sku_all' not in each_kpi_type.lower():
# there is no denominator for sku all
denominator_row = zone_category_sheet.loc[(
zone_category_sheet[KPI_NAME] == each_kpi_type
)].iloc[0]
list_of_zone_data = []
for idx, each_kpi_sheet_row in kpi_sheet_rows.iterrows():
zone_data = self._get_zone_based_data(
kpi,
each_kpi_sheet_row.T,
denominator_row=denominator_row)
list_of_zone_data.append(zone_data)
print "Final >>> ", list_of_zone_data
if write_sku:
# write for products
_output_to_write = []
for each in list_of_zone_data:
if len(each) > 1:
_output_to_write.extend([
x for x in each if 'unique_products' not in x
])
else:
# No relevant product as per excel row config parameters
continue
if not _output_to_write:
continue
_df_output_to_write = pd.DataFrame(_output_to_write)
_df_output_to_write.dropna(subset=['bay_number'],
inplace=True)
_grouped_output_to_write = _df_output_to_write.groupby(
['scene_id', 'bay_number'], as_index=False)
for scene_bay_tuple, each_data_to_write in _grouped_output_to_write:
# remove empty products when getting all SKU's
products_to_write = set([
prod_id for prod_list_per_bay in
each_data_to_write["products"]
for prod_id in prod_list_per_bay
])
data_to_write = each_data_to_write.iloc[0]
for each_product_id in products_to_write:
if int(each_product_id
) not in self.empty_product_ids:
in_assort_sc = int(
self.scif.query(
"item_id=={prod_id}".format(
prod_id=each_product_id)).
in_assort_sc.values[0])
self.common.write_to_db_result(
fk=int(data_to_write.fk),
numerator_id=int(each_product_id),
numerator_result=int(
data_to_write.bay_number),
denominator_id=int(
data_to_write.denominator_id),
denominator_result=int(
data_to_write.scene_id),
result=int(data_to_write.zone_number),
score=in_assort_sc,
identifier_result=str(
data_to_write.kpi_name),
context_id=int(data_to_write.zone_number),
)
else:
# its the calculation
_output_to_write = []
for each in list_of_zone_data:
if len(each) > 1:
_output_to_write.extend([
x for x in each if 'unique_products' not in x
])
else:
continue
if not _output_to_write:
continue
_df_output_to_write = pd.DataFrame(_output_to_write)
# remove rows with empty `products`
_df_output_to_write = _df_output_to_write[
_df_output_to_write.astype(str)['products'] != "[]"]
_grouped_output_to_write = _df_output_to_write.groupby(
'denominator_id', as_index=False)
unique_manufacturer_products_count_data = _df_output_to_write.get(
"unique_manufacturer_products_count").values
if not len(unique_manufacturer_products_count_data):
continue
unique_manufacturer_products_count = unique_manufacturer_products_count_data[
0]
for denominator_id, each_data_to_write in _grouped_output_to_write:
# remove empty products when getting all SKU's
# get all unique product ids from different dataframe rows in a set
products_to_write = set([
prod_id for prod_list_per_bay in
each_data_to_write["products"]
for prod_id in prod_list_per_bay
])
# sanitize the products
products_to_write = [
x for x in products_to_write
if x not in self.empty_product_ids
]
result = float(len(products_to_write)
) / unique_manufacturer_products_count
data_to_write = each_data_to_write.iloc[
0] # for dot selection from first row
self.common.write_to_db_result(
fk=int(data_to_write.fk),
numerator_id=int(data_to_write.numerator_id),
denominator_id=int(data_to_write.denominator_id),
result=int(result),
score=1,
identifier_result=str(data_to_write.kpi_name),
context_id=int(data_to_write.zone_number),
)
def calculate_macro_linear(self):
kpi_sheet = self.get_template_details(LINEAR_KPI_SHEET)
category_sheet = self.get_template_details(LINEAR_CATEGORY_SHEET)
for index, kpi_sheet_row in kpi_sheet.iterrows():
kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY)
& (self.kpi_static_data[TYPE] == kpi_sheet_row[KPI_TYPE])
& (self.kpi_static_data['delete_time'].isnull())]
if kpi.empty:
print("KPI Name:{} not found in DB".format(
kpi_sheet_row[KPI_NAME]))
else:
print("KPI Name:{} found in DB".format(
kpi_sheet_row[KPI_NAME]))
permitted_store_types = [
x.strip() for x in kpi_sheet_row[STORE_TYPE].split(',')
if x.strip()
]
if self.store_info.store_type.values[
0] not in permitted_store_types:
continue
# get the length field
length_field = STACKING_MAP[kpi_sheet_row[STACKING_COL]]
# NUMERATOR
numerator_filters, numerator_filter_string = get_filter_string_per_row(
kpi_sheet_row,
NUMERATOR_FILTER_ENTITIES,
additional_filters=
LINEAR_NUMERATOR_ADDITIONAL_FILTERS_PER_COL)
# Adding compulsory filtering values
if numerator_filter_string:
numerator_filter_string += " and "
# 1. remove empty/irrelevant products
numerator_filter_string += "{prod_id_col_scif} not in {empty_prod_ids}".format(
prod_id_col_scif=PROD_ID_COL_SCIF,
empty_prod_ids=self.empty_product_ids.tolist())
# 2. remove zero facings
numerator_filter_string += " and facings > 0"
numerator_data = self.scif.query(numerator_filter_string).fillna(0). \
groupby(numerator_filters, as_index=False).agg({length_field: 'sum'})
# DENOMINATOR
denominator_row = category_sheet.loc[(
category_sheet[KPI_NAME] == kpi_sheet_row[KPI_NAME]
)].iloc[0]
denominator_filters, denominator_filter_string = get_filter_string_per_row(
denominator_row,
DENOMINATOR_FILTER_ENTITIES,
)
if denominator_filter_string:
denominator_data = self.scif.query(denominator_filter_string).fillna(0). \
groupby(denominator_filters, as_index=False).agg({length_field: 'sum'})
else:
# nothing to query; no grouping; Transform the DataFrame; get all data
denominator_data = pd.DataFrame(
self.scif.agg({length_field: 'sum'})).T
for d_idx, denominator_row in denominator_data.iterrows():
denominator = denominator_row.get(length_field)
for idx, numerator_row in numerator_data.iterrows():
numerator = numerator_row.get(length_field)
try:
result = float(numerator) / float(denominator)
except ZeroDivisionError:
result = 0
numerator_id = int(numerator_row[EXCEL_DB_MAP[
kpi_sheet_row.numerator_fk]])
denominator_key_str = EXCEL_DB_MAP[
kpi_sheet_row.denominator_fk]
denominator_id = self.get_denominator_id(
denominator_key_str, numerator_row,
denominator_row)
kpi_parent_name = None
should_enter = False
if not is_nan(kpi_sheet_row.kpi_parent_name):
kpi_parent_name = kpi_sheet_row.kpi_parent_name
should_enter = True
if not denominator_id:
raise Exception(
"Denominator ID cannot be found. [TWEAU/Utils/KPIToolBox.py]"
)
self.common.write_to_db_result(
fk=int(kpi['pk']),
numerator_id=numerator_id,
numerator_result=numerator,
denominator_id=denominator_id,
denominator_result=denominator,
result=result,
score=result,
identifier_result=kpi_sheet_row[KPI_NAME],
identifier_parent=kpi_parent_name,
should_enter=should_enter,
)
def get_shelf_limit_for_scene(self, scene_id):
shelf_limit_per_scene_map = defaultdict(list)
scene_data = self.match_product_in_scene.loc[
self.match_product_in_scene['scene_fk'] == scene_id]
_bay_grouped_scene_data = scene_data.groupby('bay_number',
as_index=False)
for each_bay in _bay_grouped_scene_data:
bay_number = each_bay[0]
scene_data = each_bay[1]
if not scene_data.empty:
shelf_limit_per_scene_map[scene_id].append((bay_number, {
'max_shelf':
scene_data[SHELF_NUMBER].max(),
'min_shelf':
scene_data[SHELF_NUMBER].min()
}))
return shelf_limit_per_scene_map
def get_valid_bay_numbers(self, scene_id, permitted_shelves):
scene_max_min_map = self.get_shelf_limit_for_scene(scene_id)
bay_numbers = []
for scene_id, bay_shelf_map in scene_max_min_map.iteritems():
for each_map in bay_shelf_map:
_bay_number = each_map[0]
scene_max_min_map = each_map[1]
for each_permitted_shelf in permitted_shelves:
if scene_max_min_map['max_shelf'] == each_permitted_shelf:
bay_numbers.append(_bay_number)
return bay_numbers
def _get_zone_based_data(self, kpi, kpi_sheet_row, denominator_row):
# generate scene max shelf max bay map
zone_number = kpi_sheet_row[ZONE_NAME]
shelves_policy_from_top = [
int(x.strip())
for x in str(kpi_sheet_row[SHELF_POLICY_FROM_TOP]).split(',')
if x.strip()
]
permitted_shelves = [
int(x.strip())
for x in str(kpi_sheet_row[NUMBER_OF_SHELVES]).split(',')
if x.strip()
]
permitted_store_types = [
x.strip() for x in kpi_sheet_row[STORE_TYPE].split(',')
if x.strip()
]
unique_manufacturer_products_count = 0
# DENOMINATOR
if not denominator_row.empty:
denominator_filters, denominator_filter_string = get_filter_string_per_row(
denominator_row,
DENOMINATOR_FILTER_ENTITIES,
)
unique_manufacturer_products_count = len(
self.scif.query(denominator_filter_string).product_fk.unique())
# NUMERATOR
# filter based on store type [d]
# filter based on template_type [d]
# find the shelf, and filter based on zone
# filter based on exclude policy
if self.store_info.store_type.values[0] in permitted_store_types:
filters, filter_string = get_filter_string_per_row(
kpi_sheet_row,
ZONE_NUMERATOR_FILTER_ENTITIES,
additional_filters=ZONE_ADDITIONAL_FILTERS_PER_COL,
)
# combined tables
match_product_df = pd.merge(self.match_product_in_scene,
self.products,
how='left',
left_on=['product_fk'],
right_on=['product_fk'])
scene_template_df = pd.merge(self.scene_info,
self.templates,
how='left',
left_on=['template_fk'],
right_on=['template_fk'])
product_scene_join_data = pd.merge(match_product_df,
scene_template_df,
how='left',
left_on=['scene_fk'],
right_on=['scene_fk'])
if filters:
filtered_grouped_scene_items = product_scene_join_data.query(filter_string) \
.groupby(filters, as_index=False)
else:
# dummy structure without filters
filtered_grouped_scene_items = [
('', product_scene_join_data.query(filter_string))
]
# get the scene_id's worth getting data from
scene_data_map = []
unique_products = set() # if the scene type not even valid
for each_group_by_manf_templ in filtered_grouped_scene_items:
# append scene to group by
current_scene_types = each_group_by_manf_templ[
1].template_name.unique()
scene_type = current_scene_types
if len(current_scene_types) == 1:
scene_type = each_group_by_manf_templ[
1].template_name.unique()[0]
# unique_products = set()
scene_type_grouped_by_scene = each_group_by_manf_templ[
1].groupby(SCENE_FK)
for each_scene_id_group in scene_type_grouped_by_scene:
exclude_items = False
include_only = False
scene_id = each_scene_id_group[0]
scene_data_per_scene = each_scene_id_group[1]
valid_bay_numbers = self.get_valid_bay_numbers(
scene_id, permitted_shelves)
if not valid_bay_numbers:
continue
matched_products_for_scene = scene_data_per_scene.query(
'shelf_number in {shelves} and bay_number in {bays}'.
format(shelves=shelves_policy_from_top,
bays=valid_bay_numbers)).groupby(
['bay_number', 'shelf_number'],
as_index=False)
# scene_uid = self.scene_info.loc[
# self.scene_info['scene_fk'] == each_scene].scene_uid.values[0]
items_to_check_str = None
if not is_nan(kpi_sheet_row.exclude_include_policy):
match_exclude = exclude_re.search(
kpi_sheet_row.exclude_include_policy)
if not match_exclude:
match_only = only_re.search(
kpi_sheet_row.exclude_include_policy)
items_to_check_str = match_only.groups()[-1]
include_only = True
else:
items_to_check_str = match_exclude.groups()[-1]
exclude_items = True
for tuple_data in matched_products_for_scene:
# get excluding shelves
bay_number = tuple_data[0][0]
each_shelf_per_bay = tuple_data[1]
numerator_entity_id = EXCEL_ENTITY_MAP[
kpi_sheet_row.numerator_fk]
numerator_id = getattr(
each_shelf_per_bay,
EXCEL_DB_MAP[kpi_sheet_row.numerator_fk],
None).unique()[0]
denominator_entity_id = EXCEL_ENTITY_MAP[
kpi_sheet_row.denominator_fk]
denominator_data = getattr(
each_shelf_per_bay,
EXCEL_DB_MAP[kpi_sheet_row.denominator_fk],
pd.Series())
if denominator_data.empty:
# find in self
denominator_id = getattr(
self,
EXCEL_DB_MAP[kpi_sheet_row.denominator_fk],
None)
else:
denominator_id = denominator_data.unique()[0]
if items_to_check_str:
# exclude/include logic
last_shelf_number = str(
each_shelf_per_bay.n_shelf_items.unique()[0])
shelf_filter = items_to_check_str.replace(
'N', last_shelf_number)
shelf_filter_string = '[{}]'.format(shelf_filter)
if exclude_items:
# exclude rows in `items_to_check_tuple`
required_shelf_items = each_shelf_per_bay.drop(
each_shelf_per_bay.query(
'facing_sequence_number in {filter_string}'
.format(
filter_string=shelf_filter_string
)).index.tolist())
else:
# it is include_only:
required_shelf_items = each_shelf_per_bay.query(
'facing_sequence_number in {filter_string}'
.format(filter_string=shelf_filter_string))
product_ids = required_shelf_items.product_fk.unique(
).tolist()
else:
product_ids = each_shelf_per_bay.product_fk.unique(
).tolist()
unique_products.update(product_ids)
scene_data_map.append({
'fk':
int(kpi['pk']),
'numerator_id':
numerator_id,
'denominator_id':
denominator_id,
'bay_number':
bay_number,
'scene_id':
scene_id,
'products':
product_ids,
'kpi_name':
kpi_sheet_row[KPI_NAME],
'zone_number':
zone_number,
'unique_manufacturer_products_count':
unique_manufacturer_products_count,
})
scene_data_map.append({"unique_products": unique_products})
return scene_data_map
return []
def get_denominator_id(self, denominator_key_str, numerator_row,
denominator_row):
"""
:param denominator_key_str: str
:param numerator_row: pd.Dataframe
:param denominator_row: pd.Dataframe
:return: int
# zero check in self object
# first check in denominator
# second check in numerator
# third check in self.scif
>> always return one integer denominator_id
"""
denominator_id = getattr(self, denominator_key_str, None)
if not denominator_id:
denominator_data = denominator_row.get(
denominator_key_str,
numerator_row.get(denominator_key_str,
self.scif.get(denominator_key_str)))
if type(denominator_data) == pd.Series:
denominator_id = denominator_data.drop_duplicates()[0]
else:
denominator_id = denominator_data
return denominator_id
def get_template_details(self, sheet_name):
template = pd.read_excel(self.excel_file_path, sheetname=sheet_name)
return template
class LIONNZToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.store_info['store_fk'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scene_template_info = self.scif[[
'scene_fk', 'template_fk', 'template_name'
]].drop_duplicates()
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.kpi_template_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..',
TEMPLATE_PARENT_FOLDER, TEMPLATE_NAME)
self.own_man_fk = self.all_products[
self.all_products['manufacturer_name'].str.lower() ==
OWN_MAN_NAME.lower()]['manufacturer_fk'].values[0]
self.kpi_template = pd.ExcelFile(self.kpi_template_path)
self.empty_prod_ids = self.all_products[
self.all_products.product_name.str.contains(
'empty', case=False)]['product_fk'].values
self.irrelevant_prod_ids = self.all_products[
self.all_products.product_name.str.contains(
'irrelevant', case=False)]['product_fk'].values
self.other_prod_ids = self.all_products[
self.all_products.product_name.str.contains(
'other', case=False)]['product_fk'].values
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
pd.reset_option('mode.chained_assignment')
with pd.option_context('mode.chained_assignment', None):
self.filter_and_send_kpi_to_calc()
self.calculate_assortment_kpis()
self.common.commit_results_data()
return 0 # to mark successful run of script
def calculate_assortment_kpis(self):
distribution_kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] == DST_MAN_BY_STORE_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
oos_kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] == OOS_MAN_BY_STORE_PERC)
& (self.kpi_static_data['delete_time'].isnull())]
prod_presence_kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] ==
PRODUCT_PRESENCE_BY_STORE_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
oos_prod_kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] == OOS_PRODUCT_BY_STORE_LIST)
& (self.kpi_static_data['delete_time'].isnull())]
def __return_valid_store_policies(policy):
policy_json = json.loads(policy)
store_json = json.loads(
self.store_info.reset_index().to_json(orient='records'))[0]
valid_store = True
# map the necessary keys to those names knows
for policy_value, store_info_value in POLICY_STORE_MAP.iteritems():
if policy_value in policy_json:
policy_json[store_info_value] = policy_json.pop(
policy_value)
for key, values in policy_json.iteritems():
if str(store_json[key]) in values:
continue
else:
valid_store = False
break
return valid_store
policy_data = self.get_policies(distribution_kpi.iloc[0].pk)
if policy_data.empty:
Log.info("No Assortments Loaded.")
return 0
resp = policy_data['policy'].apply(__return_valid_store_policies)
valid_policy_data = policy_data[resp]
if valid_policy_data.empty:
Log.info("No policy applicable for session {sess} and kpi {kpi}.".
format(sess=self.session_uid,
kpi=distribution_kpi.iloc[0].type))
return 0
# calculate and save the percentage values for distribution and oos
self.calculate_and_save_distribution_and_oos(
assortment_product_fks=valid_policy_data['product_fk'],
distribution_kpi_fk=distribution_kpi.iloc[0].pk,
oos_kpi_fk=oos_kpi.iloc[0].pk)
# calculate and save prod presence and oos products
self.calculate_and_save_prod_presence_and_oos_products(
assortment_product_fks=valid_policy_data['product_fk'],
prod_presence_kpi_fk=prod_presence_kpi.iloc[0].pk,
oos_prod_kpi_fk=oos_prod_kpi.iloc[0].pk,
distribution_kpi_name=DST_MAN_BY_STORE_PERC,
oos_kpi_name=OOS_MAN_BY_STORE_PERC)
def calculate_and_save_prod_presence_and_oos_products(
self, assortment_product_fks, prod_presence_kpi_fk,
oos_prod_kpi_fk, distribution_kpi_name, oos_kpi_name):
# all assortment products are only in own manufacturers context
total_own_products_in_scene = self.scif[
self.scif['manufacturer_fk'].astype(
int) == self.own_man_fk]["item_id"].unique()
present_products = np.intersect1d(total_own_products_in_scene,
assortment_product_fks)
extra_products = np.setdiff1d(total_own_products_in_scene,
present_products)
oos_products = np.setdiff1d(assortment_product_fks, present_products)
product_map = {
OOS_CODE: oos_products,
PRESENT_CODE: present_products,
EXTRA_CODE: extra_products
}
# save product presence; with distribution % kpi as parent
for assortment_code, product_fks in product_map.iteritems():
for each_fk in product_fks:
self.common.write_to_db_result(
fk=prod_presence_kpi_fk,
numerator_id=each_fk,
denominator_id=self.store_id,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(assortment_code),
identifier_parent="{}_{}".format(distribution_kpi_name,
self.store_id),
should_enter=True)
if assortment_code == OOS_CODE:
# save OOS products; with OOS % kpi as parent
for each_fk in product_fks:
self.common.write_to_db_result(
fk=oos_prod_kpi_fk,
numerator_id=each_fk,
denominator_id=self.store_id,
context_id=self.store_id,
result=assortment_code,
score=assortment_code,
identifier_result=CODE_KPI_MAP.get(assortment_code),
identifier_parent="{}_{}".format(
oos_kpi_name, self.store_id),
should_enter=True)
def calculate_and_save_distribution_and_oos(self, assortment_product_fks,
distribution_kpi_fk,
oos_kpi_fk):
"""Function to calculate distribution and OOS percentage.
Saves distribution and oos percentage as values.
"""
Log.info("Calculate distribution and OOS for {}".format(
self.project_name))
scene_products = pd.Series(self.scif["item_id"].unique())
total_products_in_assortment = len(assortment_product_fks)
count_of_assortment_prod_in_scene = assortment_product_fks.isin(
scene_products).sum()
oos_count = total_products_in_assortment - count_of_assortment_prod_in_scene
# count of lion sku / all sku assortment count
if not total_products_in_assortment:
Log.info("No assortments applicable for session {sess}.".format(
sess=self.session_uid))
return 0
distribution_perc = count_of_assortment_prod_in_scene / float(
total_products_in_assortment) * 100
oos_perc = 100 - distribution_perc
self.common.write_to_db_result(
fk=distribution_kpi_fk,
numerator_id=self.own_man_fk,
numerator_result=count_of_assortment_prod_in_scene,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=distribution_perc,
score=distribution_perc,
identifier_result="{}_{}".format(DST_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True)
self.common.write_to_db_result(
fk=oos_kpi_fk,
numerator_id=self.own_man_fk,
numerator_result=oos_count,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=oos_perc,
score=oos_perc,
identifier_result="{}_{}".format(OOS_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True)
def get_policies(self, kpi_fk):
query = """ select a.kpi_fk, p.policy_name, p.policy, atag.assortment_group_fk,
atp.assortment_fk, atp.product_fk, atp.start_date, atp.end_date
from pservice.assortment_to_product atp
join pservice.assortment_to_assortment_group atag on atp.assortment_fk = atag.assortment_fk
join pservice.assortment a on a.pk = atag.assortment_group_fk
join pservice.policy p on p.pk = a.store_policy_group_fk
where a.kpi_fk={kpi_fk}
AND '{sess_date}' between atp.start_date AND atp.end_date;
"""
policies = pd.read_sql_query(
query.format(kpi_fk=kpi_fk,
sess_date=self.session_info.iloc[0].visit_date),
self.rds_conn.db)
return policies
def filter_and_send_kpi_to_calc(self):
kpi_sheet = self.kpi_template.parse(KPI_NAMES_SHEET)
kpi_sheet[KPI_FAMILY_COL] = kpi_sheet[KPI_FAMILY_COL].fillna(
method='ffill')
kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET)
kpi_include_exclude = self.kpi_template.parse(KPI_INC_EXC_SHEET)
for index, kpi_sheet_row in kpi_sheet.iterrows():
if not is_nan(kpi_sheet_row[KPI_ACTIVE]):
if str(kpi_sheet_row[KPI_ACTIVE]).strip().lower() in [
'0.0', 'n', 'no'
]:
Log.warning("KPI :{} deactivated in sheet.".format(
kpi_sheet_row[KPI_NAME_COL]))
continue
kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] ==
kpi_sheet_row[KPI_NAME_COL])
& (self.kpi_static_data['delete_time'].isnull())]
if kpi.empty:
Log.warning(
"*** KPI Name:{name} not found in DB for session {sess} ***"
.format(name=kpi_sheet_row[KPI_NAME_COL],
sess=self.session_uid))
return False
else:
Log.info(
"KPI Name:{name} found in DB for session {sess}".format(
name=kpi_sheet_row[KPI_NAME_COL],
sess=self.session_uid))
detail = kpi_details[kpi_details[KPI_NAME_COL] ==
kpi[KPI_TYPE_COL].values[0]]
# check for store types allowed
permitted_store_types = [
x.strip().lower()
for x in detail[STORE_POLICY].values[0].split(',')
if x.strip()
]
if self.store_info.store_type.iloc[0].lower(
) not in permitted_store_types:
Log.warning(
"Not permitted store type - {type} for session {sess}".
format(type=kpi_sheet_row[KPI_NAME_COL],
sess=self.session_uid))
continue
detail['pk'] = kpi['pk'].iloc[0]
# gather details
groupers, query_string = get_groupers_and_query_string(detail)
_include_exclude = kpi_include_exclude[
kpi_details[KPI_NAME_COL] == kpi[KPI_TYPE_COL].values[0]]
# gather include exclude
include_exclude_data_dict = get_include_exclude(
_include_exclude)
dataframe_to_process = self.get_sanitized_match_prod_scene(
include_exclude_data_dict)
if kpi_sheet_row[KPI_FAMILY_COL] == FSOS:
self.calculate_fsos(detail, groupers, query_string,
dataframe_to_process)
elif kpi_sheet_row[KPI_FAMILY_COL] == Count:
self.calculate_count(detail, groupers, query_string,
dataframe_to_process)
else:
pass
return True
def calculate_fsos(self, kpi, groupers, query_string,
dataframe_to_process):
Log.info("Calculate {name} for session {sess}".format(
name=kpi.kpi_name.iloc[0], sess=self.session_uid))
prod_empty_sub_cat = dataframe_to_process[dataframe_to_process[
'sub_category_fk'].isnull()]['product_fk'].tolist()
Log.info('Remove products with empty sub category fk: {}'.format(
prod_empty_sub_cat))
dataframe_to_process.dropna(subset=['sub_category_fk'], inplace=True)
dataframe_to_process = dataframe_to_process.astype(
{'sub_category_fk': 'int64'}, errors='ignore')
if query_string:
grouped_data_frame = dataframe_to_process.query(
query_string).groupby(groupers)
else:
grouped_data_frame = dataframe_to_process.groupby(groupers)
# for the two kpis, we need to show zero presence of own manufacturer.
# else the flow will be stuck in case own manufacturers are absent altogether.
if '_own_' in kpi['kpi_name'].iloc[0].lower() and \
'_whole_store' not in kpi['kpi_name'].iloc[0].lower():
self.scif['store_fk'] = self.store_id
dataframe_to_process['store_fk'] = self.store_id
scif_with_den_context = self.scif[[
PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'],
PARAM_DB_MAP[kpi['context'].iloc[0]]['key']
]].drop_duplicates()
df_with_den_context = dataframe_to_process.query(query_string)[[
PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'],
PARAM_DB_MAP[kpi['context'].iloc[0]]['key']
]].drop_duplicates()
denominators_df_to_save_zero = scif_with_den_context[(
~scif_with_den_context[PARAM_DB_MAP[
kpi['denominator'].iloc[0]]['key']].isin(
df_with_den_context[PARAM_DB_MAP[
kpi['denominator'].iloc[0]]['key']]))]
identifier_parent = None
numerator_fk = self.own_man_fk
result = numerator_result = 0 # SAVE ALL RESULTS AS ZERO
denominators_df_to_save_zero.dropna(inplace=True)
denominators_df_to_save_zero = denominators_df_to_save_zero.astype(
'int64')
for idx, each_row in denominators_df_to_save_zero.iterrows():
# get parent details
if not is_nan(kpi[KPI_PARENT_COL].iloc[0]):
param_id_map = dict(each_row.fillna('0'))
kpi_parent = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] ==
kpi[KPI_PARENT_COL].iloc[0])
& (self.kpi_static_data['delete_time'].isnull())]
kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET)
kpi_parent_detail = kpi_details[
kpi_details[KPI_NAME_COL] ==
kpi_parent[KPI_TYPE_COL].values[0]]
parent_denominator_id = get_parameter_id(
key_value=PARAM_DB_MAP[
kpi_parent_detail['denominator'].iloc[0]]['key'],
param_id_map=param_id_map)
if parent_denominator_id is None:
parent_denominator_id = self.store_id
parent_context_id = get_parameter_id(
key_value=PARAM_DB_MAP[
kpi_parent_detail['context'].iloc[0]]['key'],
param_id_map=param_id_map)
if parent_context_id is None:
parent_context_id = self.store_id
identifier_parent = "{}_{}_{}_{}".format(
kpi_parent_detail['kpi_name'].iloc[0],
kpi_parent['pk'].iloc[0],
# parent_numerator_id,
int(parent_denominator_id),
int(parent_context_id))
context_id = each_row[PARAM_DB_MAP[kpi['context'].iloc[0]]
['key']]
# query out empty product IDs since FSOS is not interested in them.
each_den_fk = each_row[PARAM_DB_MAP[kpi['denominator'].iloc[0]]
['key']]
_query = "{key}=='{value_id}' and product_fk not in {exc_prod_ids}".format(
key=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'],
value_id=each_den_fk,
exc_prod_ids=self.empty_prod_ids.tolist() +
self.irrelevant_prod_ids.tolist())
# find number of products in that context
denominator_result = len(dataframe_to_process.query(_query))
if not denominator_result:
continue
self.common.write_to_db_result(
fk=kpi['pk'].iloc[0],
numerator_id=numerator_fk,
denominator_id=each_den_fk,
context_id=context_id,
result=result,
numerator_result=numerator_result,
denominator_result=denominator_result,
identifier_result="{}_{}_{}_{}".format(
kpi['kpi_name'].iloc[0],
kpi['pk'].iloc[0],
# numerator_id,
each_den_fk,
context_id),
identifier_parent=identifier_parent,
should_enter=True,
)
for group_id_tup, group_data in grouped_data_frame:
if type(group_id_tup) not in [tuple, list]:
# convert to a tuple
group_id_tup = group_id_tup,
param_id_map = dict(zip(groupers, group_id_tup))
numerator_id = param_id_map.get(
PARAM_DB_MAP[kpi['numerator'].iloc[0]]['key'])
denominator_id = get_parameter_id(
key_value=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'],
param_id_map=param_id_map)
if denominator_id is None:
denominator_id = self.store_id
context_id = get_parameter_id(
key_value=PARAM_DB_MAP[kpi['context'].iloc[0]]['key'],
param_id_map=param_id_map)
if context_id is None:
context_id = self.store_id
if PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'] == 'store_fk':
denominator_df = dataframe_to_process
else:
denominator_df = dataframe_to_process.query(
'{key} == {value}'.format(
key=PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'],
value=denominator_id))
if not len(denominator_df):
Log.info(
"No denominator data for session {sess} to calculate {name}"
.format(sess=self.session_uid, name=kpi.kpi_name.iloc[0]))
continue
result = len(group_data) / float(len(denominator_df))
if not is_nan(kpi[KPI_PARENT_COL].iloc[0]):
kpi_parent = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] ==
kpi[KPI_PARENT_COL].iloc[0])
& (self.kpi_static_data['delete_time'].isnull())]
kpi_details = self.kpi_template.parse(KPI_DETAILS_SHEET)
kpi_parent_detail = kpi_details[
kpi_details[KPI_NAME_COL] ==
kpi_parent[KPI_TYPE_COL].values[0]]
parent_denominator_id = get_parameter_id(
key_value=PARAM_DB_MAP[
kpi_parent_detail['denominator'].iloc[0]]['key'],
param_id_map=param_id_map)
if parent_denominator_id is None:
parent_denominator_id = self.store_id
parent_context_id = get_parameter_id(key_value=PARAM_DB_MAP[
kpi_parent_detail['context'].iloc[0]]['key'],
param_id_map=param_id_map)
if parent_context_id is None:
parent_context_id = self.store_id
self.common.write_to_db_result(
fk=kpi['pk'].iloc[0],
numerator_id=numerator_id,
denominator_id=denominator_id,
context_id=context_id,
result=result,
numerator_result=len(group_data),
denominator_result=len(denominator_df),
identifier_result="{}_{}_{}_{}".format(
kpi['kpi_name'].iloc[0],
kpi['pk'].iloc[0],
# numerator_id,
denominator_id,
context_id),
identifier_parent="{}_{}_{}_{}".format(
kpi_parent_detail['kpi_name'].iloc[0],
kpi_parent['pk'].iloc[0],
# parent_numerator_id,
parent_denominator_id,
parent_context_id),
should_enter=True,
)
else:
# its the parent. Save the identifier result.
self.common.write_to_db_result(
fk=kpi['pk'].iloc[0],
numerator_id=numerator_id,
denominator_id=denominator_id,
context_id=context_id,
result=result,
numerator_result=len(group_data),
denominator_result=len(denominator_df),
identifier_result="{}_{}_{}_{}".format(
kpi['kpi_name'].iloc[0],
kpi['pk'].iloc[0],
# numerator_id,
denominator_id,
context_id),
should_enter=True,
)
return True
def calculate_count(self, kpi, groupers, query_string,
dataframe_to_process):
if query_string:
grouped_data_frame = dataframe_to_process.query(
query_string).groupby(groupers)
else:
grouped_data_frame = dataframe_to_process.groupby(groupers)
for group_id_tup, group_data in grouped_data_frame:
param_id_map = dict(zip(groupers, group_id_tup))
numerator_id = param_id_map.get(
PARAM_DB_MAP[kpi['numerator'].iloc[0]]['key'])
denominator_id = param_id_map.get(
PARAM_DB_MAP[kpi['denominator'].iloc[0]]['key'])
context_id = get_parameter_id(
key_value=PARAM_DB_MAP[kpi['context'].iloc[0]]['key'],
param_id_map=param_id_map)
if context_id is None:
context_id = self.store_id
result = len(group_data)
self.common.write_to_db_result(
fk=kpi['pk'].iloc[0],
numerator_id=numerator_id,
denominator_id=denominator_id,
context_id=context_id,
result=result,
)
return True
def get_sanitized_match_prod_scene(self, include_exclude_data_dict):
scene_product_data = self.match_product_in_scene.merge(
self.products,
how='left',
on=['product_fk'],
suffixes=('', '_prod'))
sanitized_products_in_scene = scene_product_data.merge(
self.scene_template_info,
how='left',
on='scene_fk',
suffixes=('', '_scene'))
# flags
include_empty = include_exclude_data_dict.get('empty')
include_irrelevant = include_exclude_data_dict.get('irrelevant')
include_others = include_exclude_data_dict.get('others')
include_stacking = include_exclude_data_dict.get('stacking')
# list
scene_types_to_exclude = include_exclude_data_dict.get(
'scene_types_to_exclude', False)
categories_to_exclude = include_exclude_data_dict.get(
'categories_to_exclude', False)
brands_to_exclude = include_exclude_data_dict.get(
'brands_to_exclude', False)
ean_codes_to_exclude = include_exclude_data_dict.get(
'ean_codes_to_exclude', False)
# Start removing items
if scene_types_to_exclude:
# list of scene types to include is present, otherwise all included
Log.info("Exclude template/scene type {}".format(
scene_types_to_exclude))
sanitized_products_in_scene.drop(sanitized_products_in_scene[
sanitized_products_in_scene['template_name'].str.upper().isin([
x.upper() if type(x) in [unicode, str] else x
for x in scene_types_to_exclude
])].index,
inplace=True)
if not include_stacking:
# exclude stacking if the flag is set
Log.info(
"Exclude stacking other than in layer 1 or negative stacking [menu]"
)
sanitized_products_in_scene = sanitized_products_in_scene.loc[
sanitized_products_in_scene['stacking_layer'] <= 1]
if categories_to_exclude:
# list of categories to exclude is present, otherwise all included
Log.info("Exclude categories {}".format(categories_to_exclude))
sanitized_products_in_scene.drop(sanitized_products_in_scene[
sanitized_products_in_scene['category'].str.upper().isin([
x.upper() if type(x) in [unicode, str] else x
for x in categories_to_exclude
])].index,
inplace=True)
if brands_to_exclude:
# list of brands to exclude is present, otherwise all included
Log.info("Exclude brands {}".format(brands_to_exclude))
sanitized_products_in_scene.drop(sanitized_products_in_scene[
sanitized_products_in_scene['brand_name'].str.upper().isin([
x.upper() if type(x) in [unicode, str] else x
for x in brands_to_exclude
])].index,
inplace=True)
if ean_codes_to_exclude:
# list of ean_codes to exclude is present, otherwise all included
Log.info("Exclude ean codes {}".format(ean_codes_to_exclude))
sanitized_products_in_scene.drop(
sanitized_products_in_scene[sanitized_products_in_scene[
'product_ean_code'].str.upper().isin([
x.upper() if type(x) in [unicode, str] else x
for x in ean_codes_to_exclude
])].index,
inplace=True)
product_ids_to_exclude = []
if not include_irrelevant:
# add product ids to exclude with irrelevant
product_ids_to_exclude.extend(self.irrelevant_prod_ids)
if not include_others:
# add product ids to exclude with others
product_ids_to_exclude.extend(self.other_prod_ids)
if not include_empty:
# add product ids to exclude with empty
product_ids_to_exclude.extend(self.empty_prod_ids)
if product_ids_to_exclude:
Log.info("Exclude product ids {}".format(product_ids_to_exclude))
sanitized_products_in_scene.drop(sanitized_products_in_scene[
sanitized_products_in_scene['product_fk'].isin(
product_ids_to_exclude)].index,
inplace=True)
return sanitized_products_in_scene
class MOLSONCOORSHR_SANDToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.current_date = datetime.now()
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_info = self.data_provider[Data.STORE_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.own_manufacturer_id = int(self.data_provider[Data.OWN_MANUFACTURER][self.data_provider[Data.OWN_MANUFACTURER]['param_name'] == 'manufacturer_id']['param_value'].tolist()[0])
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.toolbox = GENERALToolBox(data_provider)
self.assortment = Assortment(self.data_provider, self.output, common=self.common)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.template_path = self.get_template_path()
self.template_data = self.get_template_data()
self.sos_store_policies = self.get_sos_store_policies(self.visit_date.strftime('%Y-%m-%d'))
self.result_values = self.get_result_values()
self.scores = pd.DataFrame()
def get_sos_store_policies(self, visit_date):
query = MOLSONCOORSHR_SANDQueries.get_sos_store_policies(visit_date)
store_policies = pd.read_sql_query(query, self.rds_conn.db)
return store_policies
def get_result_values(self):
query = MOLSONCOORSHR_SANDQueries.get_result_values()
result_values = pd.read_sql_query(query, self.rds_conn.db)
return result_values
def main_calculation(self):
"""
This function starts the KPI results calculation.
"""
if not self.template_data or KPIS_TEMPLATE_SHEET not in self.template_data:
Log.error('KPIs template sheet is empty or not found')
return
self.kpis_calculation()
self.common.commit_results_data()
def kpis_calculation(self, kpi_group=''):
"""
This is a recursive function.
The function calculates each level KPI cascading from the highest level to the lowest.
"""
total_score = total_potential_score = total_calculated = 0
kpis = self.template_data['KPIs'][self.template_data['KPIs']['KPI Group'] == kpi_group]
for index, kpi in kpis.iterrows():
child_kpi_group = kpi['Child KPI Group']
kpi_type = kpi['KPI Type'].lower()
score_function = kpi['Score Function'].lower()
if not child_kpi_group:
if kpi_type in [LINEAR_SOS_VS_TARGET, FACINGS_SOS_VS_TARGET]:
score, potential_score, calculated = self.calculate_sos_vs_target(kpi)
elif kpi_type in [FACINGS_VS_TARGET, DISTRIBUTION]:
score, potential_score, calculated = self.calculate_assortment_vs_target(kpi)
else:
Log.error("KPI of type '{}' is not supported".format(kpi_type))
score = potential_score = calculated = 0
else:
score, potential_score, calculated = self.kpis_calculation(child_kpi_group)
if score_function in [WEIGHTED_SCORE, SUM_OF_SCORES]:
total_score += score
total_potential_score += potential_score
total_calculated += calculated
else:
total_score += 0
total_potential_score += 0
total_calculated += 0
if child_kpi_group and calculated:
if kpi['KPI name Eng'] == 'Store Score':
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0
numerator_id = self.own_manufacturer_id
denominator_id = self.store_id
identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk)
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=0,
denominator_id=denominator_id,
denominator_result=0,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True
)
else:
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
parent_fk = self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']) if kpi['KPI Group'] else 0
numerator_id = self.own_manufacturer_id
denominator_id = self.store_id
identifier_result = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=parent_fk)
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=0,
denominator_id=denominator_id,
denominator_result=0,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True
)
return total_score, total_potential_score, total_calculated
@kpi_runtime()
def calculate_assortment_vs_target(self, kpi):
"""
The function filters only the relevant scenes by Location Type and calculates the Assortment scores
according to rules set in the target.
:return:
"""
lvl3_result = self.calculate_assortment_vs_target_lvl3(kpi)
for row in lvl3_result.itertuples():
numerator_id = row.product_fk
numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings
denominator_id = self.store_id
denominator_result = row.target
# denominator_result_after_actions = 0 if row.target < row.facings else row.target - row.facings
if kpi['KPI Type'] == 'Distribution':
if row.result_distributed:
result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') &
(self.result_values['result_value'] == 'DISTRIBUTED')]['result_value_fk'].tolist()[0]
score = 100
else:
result = self.result_values[(self.result_values['result_type'] == 'PRESENCE') &
(self.result_values['result_value'] == 'OOS')]['result_value_fk'].tolist()[0]
score = 0
else:
result = row.result_facings
score = round(result*100, 0)
identifier_details = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl3)
identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2)
self.common.write_to_db_result(fk=row.kpi_fk_lvl3,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
# denominator_result_after_actions=denominator_result_after_actions,
result=result,
score=score,
identifier_result=identifier_details,
identifier_parent=identifier_kpi,
should_enter=True
)
score = potential_score = 0
if not lvl3_result.empty:
lvl2_result = self.calculate_assortment_vs_target_lvl2(lvl3_result)
for row in lvl2_result.itertuples():
numerator_id = self.own_manufacturer_id
numerator_result = row.distributed if kpi['KPI Type'] == 'Distribution' else row.facings
denominator_id = self.store_id
denominator_result = row.target
result = row.result_distributed if kpi['KPI Type'] == 'Distribution' else row.result_facings
score += self.score_function(result*100, kpi)
potential_score += round(float(kpi['Weight'])*100, 0)
identifier_kpi = self.common.get_dictionary(kpi_fk=row.kpi_fk_lvl2)
identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']))
self.common.write_to_db_result(fk=row.kpi_fk_lvl2,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
result=score,
score=score,
weight=potential_score,
target=potential_score,
identifier_result=identifier_kpi,
identifier_parent=identifier_parent,
should_enter=True
)
if len(lvl3_result) > 0:
calculated = 1
else:
calculated = 0
return score, potential_score, calculated
def calculate_assortment_vs_target_lvl3(self, kpi):
location_types = kpi['Location Type'].split(', ')
kpi_fk_lvl3 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'] + ' - SKU')
kpi_fk_lvl2 = self.common.get_kpi_fk_by_kpi_type(kpi['KPI name Eng'])
assortment_result = self.assortment.get_lvl3_relevant_ass()
if assortment_result.empty:
return assortment_result
assortment_result = assortment_result[(assortment_result['kpi_fk_lvl3'] == kpi_fk_lvl3) &
(assortment_result['kpi_fk_lvl2'] == kpi_fk_lvl2)]
if assortment_result.empty:
return assortment_result
assortment_result['target'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Target'), axis=1)
assortment_result['target'] = assortment_result['target'].fillna(0)
assortment_result = assortment_result[assortment_result['target'] > 0]
assortment_result['weight'] = assortment_result.apply(lambda x: json.loads(x['additional_attributes']).get('Weight'), axis=1)
assortment_result['weight'] = assortment_result['weight'].fillna(0)
assortment_total_weights = assortment_result[['assortment_fk', 'weight']].groupby('assortment_fk').agg({'weight': 'sum'}).reset_index()
assortment_result = assortment_result.merge(assortment_total_weights, how='left', left_on='assortment_fk', right_on='assortment_fk', suffixes=['', '_total'])
facings = 'facings_ign_stack' if kpi['Ignore Stacking'] else 'facings'
products_in_session = self.scif[(self.scif[facings] > 0) & (self.scif['location_type'].isin(location_types))][['product_fk', 'facings']]\
.groupby('product_fk').agg({'facings': 'sum'}).reset_index()
lvl3_result = assortment_result.merge(products_in_session, how='left', left_on='product_fk', right_on='product_fk')
lvl3_result['facings'] = lvl3_result['facings'].fillna(0)
lvl3_result['distributed'] = lvl3_result.apply(lambda x: 1 if x['facings'] else 0, axis=1)
lvl3_result['result_facings'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'facings'), axis=1)
lvl3_result['result_distributed'] = lvl3_result.apply(lambda x: self.assortment_vs_target_result(x, 'distributed'), axis=1)
return lvl3_result
def calculate_assortment_vs_target_lvl2(self, lvl3_result):
lvl2_result = lvl3_result.groupby(['kpi_fk_lvl2', self.assortment.ASSORTMENT_FK, self.assortment.ASSORTMENT_GROUP_FK])\
.agg({'facings': 'sum', 'distributed': 'sum', 'target': 'sum', 'result_facings': 'sum', 'result_distributed': 'sum'}).reset_index()
return lvl2_result
@staticmethod
def assortment_vs_target_result(x, y):
if x[y] < x['target']:
return round(x[y] / float(x['target']) * float(x['weight']) / x['weight_total'], 5)
else:
return round(1 * float(x['weight']) / x['weight_total'], 5)
@kpi_runtime()
def calculate_sos_vs_target(self, kpi):
"""
The function filters only the relevant scenes by Location Type and calculates the linear SOS and
the facing SOS according to Manufacturer and Category set in the target.
:return:
"""
location_type = kpi['Location Type']
kpi_fk = self.common.get_kpi_fk_by_kpi_type(SOS_MANUFACTURER_CATEGORY + ('_' + location_type if location_type else ''))
sos_store_policies = self.sos_store_policies[self.sos_store_policies['kpi_fk'] == str(kpi_fk)]
sos_store_policy = None
store_policy_passed = 0
for index, policy in sos_store_policies.iterrows():
sos_store_policy = policy
store_policy = json.loads(policy['store_policy'])
store_policy_passed = 1
for key in store_policy.keys():
if key in self.store_info.columns.tolist():
if self.store_info[key][0] in store_policy[key]:
continue
else:
store_policy_passed = 0
break
else:
Log.error("Store Policy attribute is not found: '{}'").format(key)
store_policy_passed = 0
break
if store_policy_passed:
break
score = potential_score = 0
if store_policy_passed:
general_filters = {LOCATION_TYPE: location_type}
sos_policy = json.loads(sos_store_policy['sos_policy'])
numerator_sos_filters = {MANUFACTURER_NAME: sos_policy[NUMERATOR][MANUFACTURER], CATEGORY: sos_policy[DENOMINATOR][CATEGORY]}
denominator_sos_filters = {CATEGORY: sos_policy[DENOMINATOR][CATEGORY]}
numerator_id = self.all_products.loc[self.all_products[MANUFACTURER_NAME] == sos_policy[NUMERATOR][MANUFACTURER]][MANUFACTURER + '_fk'].values[0]
denominator_id = self.all_products.loc[self.all_products[CATEGORY] == sos_policy[DENOMINATOR][CATEGORY]][CATEGORY + '_fk'].values[0]
ignore_stacking = kpi['Ignore Stacking'] if kpi['Ignore Stacking'] else 0
numer_facings, numer_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(numerator_sos_filters, **general_filters))
denom_facings, denom_linear = self.calculate_share_space(ignore_stacking=ignore_stacking, **dict(denominator_sos_filters, **general_filters))
if kpi['KPI Type'].lower() == LINEAR_SOS_VS_TARGET:
numerator_result = round(numer_linear, 0)
denominator_result = round(denom_linear, 0)
result = numer_linear / float(denom_linear) if denom_linear else 0
elif kpi['KPI Type'].lower() == FACINGS_SOS_VS_TARGET:
numerator_result = numer_facings
denominator_result = denom_facings
result = numer_facings / float(denom_facings) if denom_facings else 0
else:
Log.error("KPI Type is invalid: '{}'").format(kpi['KPI Type'])
numerator_result = denominator_result = result = 0
if sos_store_policy['target']:
sos_target = round(float(sos_store_policy['target'])*100, 0)
else:
Log.error("SOS target is not set for Store ID {}").format(self.store_id)
sos_target = 0
result_vs_target = result/(float(sos_target)/100)*100 if sos_target else 0
score = self.score_function(result_vs_target, kpi)
potential_score = round(float(kpi['Weight'])*100, 0)
identifier_kpi = self.common.get_dictionary(kpi_fk=kpi_fk)
identifier_parent = self.common.get_dictionary(kpi_fk=self.common.get_kpi_fk_by_kpi_type(kpi['KPI Group']))
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
result=result,
score=score,
weight=potential_score,
target=sos_target,
identifier_result=identifier_kpi,
identifier_parent=identifier_parent,
should_enter=True
)
else:
Log.warning("Store Policy is not found for Store ID {}".format(self.store_id))
return score, potential_score, store_policy_passed
def calculate_share_space(self, ignore_stacking=1, **filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param ignore_stacking: 1 is to ignore stacking.
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = self.scif[self.toolbox.get_filter_condition(self.scif, **filters)]
if ignore_stacking:
sum_of_facings = filtered_scif['facings_ign_stack'].sum()
space_length = filtered_scif['net_len_ign_stack'].sum()
else:
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['net_len_ign_stack'].sum()
return sum_of_facings, space_length
@staticmethod
def get_template_path():
return os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data', KPIS_TEMPLATE_NAME)
def get_template_data(self):
template_data = {}
try:
sheet_names = pd.ExcelFile(self.template_path).sheet_names
for sheet in sheet_names:
template_data[sheet] = parse_template(self.template_path, sheet, lower_headers_row_index=0)
except IOError as e:
Log.error('Template {} does not exist. {}'.format(KPIS_TEMPLATE_NAME, repr(e)))
return template_data
@staticmethod
def score_function(score, kpi):
weight = float(kpi['Weight']) if kpi['Weight'] else 1
score_function = kpi['Score Function'].lower()
l_threshold = float(kpi['Lower Threshold'])*100 if kpi['Lower Threshold'] else 0
h_threshold = float(kpi['Higher Threshold'])*100 if kpi['Higher Threshold'] else 100
if score < l_threshold:
score = 0
elif score >= h_threshold:
score = 100
if score_function in [WEIGHTED_SCORE]:
score = round(score*weight, 0)
else:
score = round(score, 0)
return score
class PSAPAC_SAND3ToolBox:
# Gsk Japan kpis
# DEFAULT_TARGET = {ProductsConsts.BRAND_FK: [-1], 'shelves': ["1,2,3"], 'block_target': [80], 'brand_target': [100], 'position_target': [80]}
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.set_up_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'gsk_set_up.xlsx'),
sheet_name='Functional KPIs',
keep_default_na=False)
self.gsk_generator = GSKGenerator(self.data_provider, self.output,
self.common, self.set_up_template)
self.blocking_generator = Block(self.data_provider)
self.assortment = self.gsk_generator.get_assortment_data_provider()
self.store_info = self.data_provider['store_info']
self.store_fk = self.data_provider[StoreInfoConsts.STORE_FK]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.targets = self.ps_data_provider.get_kpi_external_targets(
key_fields=Consts.KEY_FIELDS, data_fields=Consts.DATA_FIELDS)
self.own_manufacturer = self.get_manufacturer
self.set_up_data = {
(Consts.PLN_BLOCK, Const.KPI_TYPE_COLUMN): Const.NO_INFO,
(Consts.POSITION_SCORE, Const.KPI_TYPE_COLUMN): Const.NO_INFO,
(Consts.ECAPS_FILTER_IDENT, Const.KPI_TYPE_COLUMN): Const.NO_INFO,
(Consts.PLN_MSL, Const.KPI_TYPE_COLUMN): Const.NO_INFO,
("GSK_PLN_LSOS_SCORE", Const.KPI_TYPE_COLUMN): Const.NO_INFO,
(Consts.POSM, Const.KPI_TYPE_COLUMN): Const.NO_INFO
}
@property
def get_manufacturer(self):
return int(self.data_provider.own_manufacturer[
self.data_provider.own_manufacturer['param_name'] ==
'manufacturer_id']['param_value'].iloc[0])
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.Global functions and local functions
"""
# global kpis
assortment_store_dict = self.gsk_generator.availability_store_function(
)
self.common.save_json_to_new_tables(assortment_store_dict)
assortment_category_dict = self.gsk_generator.availability_category_function(
)
self.common.save_json_to_new_tables(assortment_category_dict)
assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function(
)
self.common.save_json_to_new_tables(assortment_subcategory_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
# # local kpis
for kpi in Consts.KPI_DICT.keys():
self.gsk_generator.tool_box.extract_data_set_up_file(
kpi, self.set_up_data, Consts.KPI_DICT)
results_ecaps = self.gsk_ecaps_kpis()
self.common.save_json_to_new_tables(results_ecaps)
self.get_store_target() # choosing the policy
if self.targets.empty:
Log.warning('There is no target policy matching this store')
else:
results_compliance = self.gsk_compliance()
self.common.save_json_to_new_tables(results_compliance)
results_pos = self.gsk_pos_kpis()
self.common.save_json_to_new_tables(results_pos)
self.common.commit_results_data()
return
def position_shelf(self, brand_fk, policy, df):
"""
:param brand_fk :
:param policy : dictionary that contains {
'shelves':"1 ,2 ,4 ,5" (or any other string of numbers separate by ','),
'position_target': 80 (or any other percentage you want the score to
reach)
}
:param df: data frame that contains columns MatchesConsts.SHELF_NUMBER , "brand kf"
:returns tuple of (result,score,numerator,denominator)
result = number of products from brand_fk in shelves / number of products from brand_fk ,
score = if result reach position target 100 else 0 ,
numerator = number of products from brand_fk in shelves
denominator = number of products from brand_fk
"""
if (Consts.SHELVES
not in policy.keys()) or policy[Consts.SHELVES].empty:
Log.warning(
'This sessions have external targets but doesnt have value for shelves position'
)
return 0, 0, 0, 0, 0
if isinstance(policy[Consts.SHELVES].iloc[0], list):
shelf_from_bottom = [
int(shelf) for shelf in policy[Consts.SHELVES].iloc[0]
]
else:
shelf_from_bottom = [
int(shelf)
for shelf in policy[Consts.SHELVES].iloc[0].split(",")
]
threshold = policy[Consts.POSITION_TARGET].iloc[0]
brand_df = df[df[ProductsConsts.BRAND_FK] == brand_fk]
shelf_df = brand_df[brand_df[MatchesConsts.SHELF_NUMBER].isin(
shelf_from_bottom)]
numerator = shelf_df.shape[0]
denominator = brand_df.shape[0]
result = float(numerator) / float(denominator)
score = 1 if (result * 100) >= threshold else 0
return result, score, numerator, denominator, threshold
def lsos_score(self, brand, policy):
"""
:param brand : pk of brand
:param policy : dictionary of { 'brand_target' : lsos number you want to reach}
This function uses the lsos_in whole_store global calculation.
it takes the result of the parameter 'brand' according to the policy set target and results.
:return result,score,target
result : result of this brand lsos
score : result / brand_target ,
target : branf_target
"""
df = pd.merge(self.match_product_in_scene,
self.all_products[Const.PRODUCTS_COLUMNS],
how='left',
on=[MatchesConsts.PRODUCT_FK])
df = pd.merge(self.scif[Const.SCIF_COLUMNS],
df,
how='right',
right_on=[ScifConsts.SCENE_FK, ScifConsts.PRODUCT_FK],
left_on=[ScifConsts.SCENE_ID, ScifConsts.PRODUCT_FK])
if df.empty:
Log.warning('match_product_in_scene is empty ')
return 0, 0, 0
df = self.gsk_generator.tool_box.tests_by_template(
'GSK_PLN_LSOS_SCORE', df, self.set_up_data)
if df is None:
Log.warning('match_product_in_scene is empty ')
return 0, 0, 0
result = self.gsk_generator.tool_box.calculate_sos(
df, {ProductsConsts.BRAND_FK: brand}, {}, Const.LINEAR)[0]
target = policy['brand_target'].iloc[0]
score = float(result) / float(target)
return result, score, target
def brand_blocking(self, brand, policy):
"""
:param brand : pk of brand
:param policy : dictionary of { 'block_target' : number you want to reach}
:return result : 1 if there is a block answer set_up_data conditions else 0
"""
templates = self.set_up_data[(Const.SCENE_TYPE, Consts.PLN_BLOCK)]
template_name = {
ScifConsts.TEMPLATE_NAME: templates
} if templates else None # figure out which template name should I use
ignore_empty = False
# taking from params from set up info
stacking_param = False if not self.set_up_data[(
Const.INCLUDE_STACKING, Consts.PLN_BLOCK)] else True # false
population_parameters = {
ProductsConsts.BRAND_FK: [brand],
ProductsConsts.PRODUCT_TYPE: [ProductTypeConsts.SKU]
}
if self.set_up_data[(Const.INCLUDE_OTHERS, Consts.PLN_BLOCK)]:
population_parameters[ProductsConsts.PRODUCT_TYPE].append(
Const.OTHER)
if self.set_up_data[(Const.INCLUDE_IRRELEVANT, Consts.PLN_BLOCK)]:
population_parameters[ProductsConsts.PRODUCT_TYPE].append(
Const.IRRELEVANT)
if self.set_up_data[(Const.INCLUDE_EMPTY, Consts.PLN_BLOCK)]:
population_parameters[ProductsConsts.PRODUCT_TYPE].append(
Const.EMPTY)
else:
ignore_empty = True
if self.set_up_data[(Const.CATEGORY_INCLUDE,
Consts.PLN_BLOCK)]: # category_name
population_parameters[ProductsConsts.CATEGORY] = self.set_up_data[(
Const.CATEGORY_INCLUDE, Consts.PLN_BLOCK)]
if self.set_up_data[(Const.SUB_CATEGORY_INCLUDE,
Consts.PLN_BLOCK)]: # sub_category_name
population_parameters[
ProductsConsts.SUB_CATEGORY] = self.set_up_data[(
Const.SUB_CATEGORY_INCLUDE, Consts.PLN_BLOCK)]
# from Data file
target = float(policy['block_target'].iloc[0]) / float(100)
result = self.blocking_generator.network_x_block_together(
location=template_name,
population=population_parameters,
additional={
'minimum_block_ratio': target,
'calculate_all_scenes': True,
'ignore_empty': ignore_empty,
'include_stacking': stacking_param,
'check_vertical_horizontal': True,
'minimum_facing_for_block': 1
})
result.sort_values('facing_percentage', ascending=False, inplace=True)
score = 0 if result[result['is_block']].empty else 1
numerator = 0 if result.empty else result['block_facings'].iloc[0]
denominator = 0 if result.empty else result['total_facings'].iloc[0]
return score, target, numerator, denominator
def msl_assortment(self, kpi_fk, kpi_name):
"""
:param kpi_fk : name of level 3 assortment kpi
:param kpi_name: GSK_PLN_MSL_SCORE assortment , or GSK_ECAPS assortment
:return kpi_results : data frame of assortment products of the kpi, product's availability,
product details.
filtered by set up
"""
lvl3_assort, filter_scif = self.gsk_generator.tool_box.get_assortment_filtered(
self.set_up_data, kpi_name)
if lvl3_assort is None or lvl3_assort.empty:
return None
kpi_assortment_fk = self.common.get_kpi_fk_by_kpi_type(kpi_fk)
kpi_results = lvl3_assort[lvl3_assort['kpi_fk_lvl3'] ==
kpi_assortment_fk] # general assortment
kpi_results = pd.merge(kpi_results,
self.all_products[Const.PRODUCTS_COLUMNS],
how='left',
on=ProductsConsts.PRODUCT_FK)
kpi_results = kpi_results[kpi_results[
ProductsConsts.SUBSTITUTION_PRODUCT_FK].isnull()]
return kpi_results
def pln_ecaps_score(self, brand, assortment):
"""
:param brand : pk of desired brand
:param assortment : data frame of assortment products of the kpi, product's availability,
product details. filtered by set up
besides result of lvl2_assortment function writing level 3 assortment product presence
results
:return numerator : how many products available out of the granular groups
denominator : how many products in assortment groups
result : (numerator/denominator)*100
results : array of dictionary, each dict contains the result details
"""
identifier_parent = self.common.get_dictionary(
brand_fk=brand,
kpi_fk=self.common.get_kpi_fk_by_kpi_type(Consts.ECAP_ALL_BRAND))
results = []
kpi_ecaps_product = self.common.get_kpi_fk_by_kpi_type(
Consts.PRODUCT_PRESENCE)
ecaps_assortment_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.PLN_ASSORTMENT_KPI)
if assortment.empty:
return 0, 0, 0, results
brand_results = assortment[assortment[ProductsConsts.BRAND_FK] ==
brand] # only assortment of desired brand
for result in brand_results.itertuples():
if (math.isnan(result.in_store)) | (result.kpi_fk_lvl3 !=
ecaps_assortment_fk):
score = self.gsk_generator.tool_box.result_value_pk(
Const.EXTRA)
result_num = 1
else:
score = self.gsk_generator.tool_box.result_value_pk(Const.OOS) if result.in_store == 0 else \
self.gsk_generator.tool_box.result_value_pk(Const.DISTRIBUTED)
result_num = result.in_store
last_status = self.gsk_generator.tool_box.get_last_status(
kpi_ecaps_product, result.product_fk)
# score = result.in_store * 100
results.append({
'fk': kpi_ecaps_product,
SessionResultsConsts.NUMERATOR_ID: result.product_fk,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT: 1,
SessionResultsConsts.NUMERATOR_RESULT: result_num,
SessionResultsConsts.RESULT: score,
SessionResultsConsts.SCORE: last_status,
'identifier_parent': identifier_parent,
'identifier_result': 1,
'should_enter': True
})
if 'total' not in self.assortment.LVL2_HEADERS or 'passes' not in self.assortment.LVL2_HEADERS:
self.assortment.LVL2_HEADERS.extend(['total', 'passes'])
lvl2 = self.assortment.calculate_lvl2_assortment(brand_results)
if lvl2.empty:
return 0, 0, 0, results # in case of no assortment return 0
result = round(
np.divide(float(lvl2.iloc[0].passes), float(lvl2.iloc[0].total)),
4)
return lvl2.iloc[0].passes, lvl2.iloc[0].total, result, results
def pln_msl_summary(self, brand, assortment):
"""
:param brand : pk of desired brand
:param assortment : data frame of assortment products of the kpi, product's availability,
product details. filtered by set up
:return numerator : how many products available out of the granular groups
denominator : how many products in assortment groups
result : (numerator/denominator)*100
results : array of dictionary, each dict contains the result details
"""
if assortment is None or assortment.empty:
return 0, 0, 0, 0
brand_results = assortment[assortment[ProductsConsts.BRAND_FK] ==
brand] # only assortment of desired brand
if 'total' not in self.assortment.LVL2_HEADERS or 'passes' not in self.assortment.LVL2_HEADERS:
self.assortment.LVL2_HEADERS.extend(['total', 'passes'])
lvl2 = self.assortment.calculate_lvl2_assortment(brand_results)
if lvl2.empty:
return 0, 0, 0, 0 # in case of no assortment return 0
result = round(
np.divide(float(lvl2.iloc[0].passes), float(lvl2.iloc[0].total)),
4)
return lvl2.iloc[0].passes, lvl2.iloc[0].total, result, lvl2.iloc[
0].assortment_group_fk
def get_store_target(self):
"""
Function checks which policies out of self.target are relevant to this store visit according to store
attributes.
"""
parameters_dict = {StoreInfoConsts.STORE_NUMBER_1: 'store_number'}
for store_param, target_param in parameters_dict.items():
if target_param in self.targets.columns:
if self.store_info[store_param][0] is None:
if self.targets.empty or self.targets[
self.targets[target_param] != ''].empty:
continue
else:
self.targets.drop(self.targets.index, inplace=True)
self.targets = self.targets[
(self.targets[target_param] ==
self.store_info[store_param][0].encode(HelperConsts.UTF8))
| (self.targets[target_param] == '')]
def gsk_compliance(self):
"""
Function calculate compliance score for each brand based on :
position score, brand-assortment score,
block score ,lsos score.
Also calculate compliance summary score - average of brands compliance scores
"""
results_df = []
df = self.scif
# kpis
kpi_block_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_BLOCK)
kpi_position_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.POSITION_SCORE)
kpi_lsos_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_LSOS)
kpi_msl_fk = self.common.get_kpi_fk_by_kpi_type(Consts.PLN_MSL)
kpi_compliance_brands_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.COMPLIANCE_ALL_BRANDS)
kpi_compliance_summary_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.COMPLIANCE_SUMMARY)
identifier_compliance_summary = self.common.get_dictionary(
kpi_fk=kpi_compliance_summary_fk)
# targets
block_target = 0.25
posit_target = 0.25
lsos_target = 0.25
msl_target = 0.25
total_brand_score = 0
counter_brands = 0
# assortment_lvl3 msl df initialize
self.gsk_generator.tool_box.extract_data_set_up_file(
Consts.PLN_MSL, self.set_up_data, Consts.KPI_DICT)
assortment_msl = self.msl_assortment(Const.DISTRIBUTION,
Consts.PLN_MSL)
# set data frame to find position shelf
df_position_score = pd.merge(self.match_product_in_scene,
self.all_products,
on=ProductsConsts.PRODUCT_FK)
df_position_score = pd.merge(
self.scif[Const.SCIF_COLUMNS],
df_position_score,
how='right',
right_on=[ScifConsts.SCENE_FK, ProductsConsts.PRODUCT_FK],
left_on=[ScifConsts.SCENE_ID, ScifConsts.PRODUCT_FK])
df_position_score = self.gsk_generator.tool_box.tests_by_template(
Consts.POSITION_SCORE, df_position_score, self.set_up_data)
if not self.set_up_data[(Const.INCLUDE_STACKING,
Consts.POSITION_SCORE)]:
df_position_score = df_position_score if df_position_score is None else df_position_score[
df_position_score[MatchesConsts.STACKING_LAYER] == 1]
# calculate all brands if template doesnt require specific brand else only for specific brands
template_brands = self.set_up_data[(Const.BRANDS_INCLUDE,
Consts.PLN_BLOCK)]
brands = df[df[ProductsConsts.BRAND_NAME].isin(template_brands)][ProductsConsts.BRAND_FK].unique() if \
template_brands else df[ProductsConsts.BRAND_FK].dropna().unique()
for brand in brands:
policy = self.targets[self.targets[ProductsConsts.BRAND_FK] ==
brand]
if policy.empty:
Log.warning('There is no target policy matching brand'
) # adding brand name
return results_df
identifier_parent = self.common.get_dictionary(
brand_fk=brand, kpi_fk=kpi_compliance_brands_fk)
# msl_kpi
msl_numerator, msl_denominator, msl_result, msl_assortment_group = self.pln_msl_summary(
brand, assortment_msl)
msl_score = msl_result * msl_target
results_df.append({
'fk': kpi_msl_fk,
SessionResultsConsts.NUMERATOR_ID: brand,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT: msl_denominator,
SessionResultsConsts.NUMERATOR_RESULT: msl_numerator,
SessionResultsConsts.RESULT: msl_result,
SessionResultsConsts.SCORE: msl_score,
SessionResultsConsts.TARGET: msl_target,
SessionResultsConsts.CONTEXT_ID: msl_assortment_group,
'identifier_parent': identifier_parent,
'should_enter': True
})
# lsos kpi
lsos_numerator, lsos_result, lsos_denominator = self.lsos_score(
brand, policy)
lsos_result = 1 if lsos_result > 1 else lsos_result
lsos_score = lsos_result * lsos_target
results_df.append({
'fk': kpi_lsos_fk,
SessionResultsConsts.NUMERATOR_ID: brand,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT: lsos_denominator,
SessionResultsConsts.NUMERATOR_RESULT: lsos_numerator,
SessionResultsConsts.RESULT: lsos_result,
SessionResultsConsts.SCORE: lsos_score,
SessionResultsConsts.TARGET: lsos_target,
'identifier_parent': identifier_parent,
SessionResultsConsts.WEIGHT: lsos_denominator,
'should_enter': True
})
# block_score
block_result, block_benchmark, numerator_block, block_denominator = self.brand_blocking(
brand, policy)
block_score = round(block_result * block_target, 4)
results_df.append({
'fk':
kpi_block_fk,
SessionResultsConsts.NUMERATOR_ID:
brand,
SessionResultsConsts.DENOMINATOR_ID:
self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT:
block_denominator,
SessionResultsConsts.NUMERATOR_RESULT:
numerator_block,
SessionResultsConsts.RESULT:
block_result,
SessionResultsConsts.SCORE:
block_score,
SessionResultsConsts.TARGET:
block_target,
'identifier_parent':
identifier_parent,
'should_enter':
True,
SessionResultsConsts.WEIGHT: (block_benchmark * 100)
})
# position score
if df_position_score is not None:
position_result, position_score, position_num, position_den, position_benchmark = self.position_shelf(
brand, policy, df_position_score)
else:
position_result, position_score, position_num, position_den, position_benchmark = 0, 0, 0, 0, 0
position_score = round(position_score * posit_target, 4)
results_df.append({
'fk': kpi_position_fk,
SessionResultsConsts.NUMERATOR_ID: brand,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT: position_den,
SessionResultsConsts.NUMERATOR_RESULT: position_num,
SessionResultsConsts.RESULT: position_result,
SessionResultsConsts.SCORE: position_score,
SessionResultsConsts.TARGET: posit_target,
'identifier_parent': identifier_parent,
'should_enter': True,
SessionResultsConsts.WEIGHT: position_benchmark
})
# compliance score per brand
compliance_score = round(
position_score + block_score + lsos_score + msl_score, 4)
results_df.append({
'fk': kpi_compliance_brands_fk,
SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer,
SessionResultsConsts.DENOMINATOR_ID: brand,
SessionResultsConsts.DENOMINATOR_RESULT: 1,
SessionResultsConsts.NUMERATOR_RESULT: compliance_score,
SessionResultsConsts.RESULT: compliance_score,
SessionResultsConsts.SCORE: compliance_score,
'identifier_parent': identifier_compliance_summary,
'identifier_result': identifier_parent,
'should_enter': True
})
# counter and sum updates
total_brand_score = round(total_brand_score + compliance_score, 4)
counter_brands = counter_brands + 1
if counter_brands == 0:
return results_df
# compliance summary
average_brand_score = round(total_brand_score / counter_brands, 4)
results_df.append({
'fk': kpi_compliance_summary_fk,
SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT: counter_brands,
SessionResultsConsts.NUMERATOR_RESULT: total_brand_score,
SessionResultsConsts.RESULT: average_brand_score,
SessionResultsConsts.SCORE: average_brand_score,
'identifier_result': identifier_compliance_summary
})
return results_df
def gsk_ecaps_kpis(self):
"""
Function calculate for each brand ecaps score, and for all brands together set ecaps summary score
:return
results_df : array of dictionary, each dict contains kpi's result details
"""
results_df = []
kpi_ecaps_brands_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.ECAP_ALL_BRAND)
kpi_ecaps_summary_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.ECAP_SUMMARY)
identifier_ecaps_summary = self.common.get_dictionary(
kpi_fk=kpi_ecaps_summary_fk)
total_brand_score = 0
assortment_display = self.msl_assortment(Consts.PLN_ASSORTMENT_KPI,
Consts.ECAPS_FILTER_IDENT)
if assortment_display is None or assortment_display.empty:
return results_df
template_brands = self.set_up_data[(Const.BRANDS_INCLUDE,
Consts.ECAPS_FILTER_IDENT)]
brands = assortment_display[assortment_display[ProductsConsts.BRAND_NAME].isin(template_brands)][
ProductsConsts.BRAND_FK].unique() if \
template_brands else assortment_display[ProductsConsts.BRAND_FK].dropna().unique()
for brand in brands:
numerator_res, denominator_res, result, product_presence_df = self.pln_ecaps_score(
brand, assortment_display)
results_df.extend(product_presence_df)
identifier_all_brand = self.common.get_dictionary(
brand_fk=brand,
kpi_fk=self.common.get_kpi_fk_by_kpi_type(
Consts.ECAP_ALL_BRAND))
results_df.append({
'fk': kpi_ecaps_brands_fk,
SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer,
SessionResultsConsts.DENOMINATOR_ID: brand,
SessionResultsConsts.DENOMINATOR_RESULT: denominator_res,
SessionResultsConsts.NUMERATOR_RESULT: numerator_res,
SessionResultsConsts.RESULT: result,
SessionResultsConsts.SCORE: result,
'identifier_parent': identifier_ecaps_summary,
'identifier_result': identifier_all_brand,
'should_enter': True
})
total_brand_score = total_brand_score + result
if len(
brands
) > 0: # don't want to show result in case of there are no brands relevan to the template
result_summary = round(total_brand_score / len(brands), 4)
results_df.append({
'fk':
kpi_ecaps_summary_fk,
SessionResultsConsts.NUMERATOR_ID:
self.own_manufacturer,
SessionResultsConsts.DENOMINATOR_ID:
self.store_fk,
SessionResultsConsts.DENOMINATOR_RESULT:
len(brands),
SessionResultsConsts.NUMERATOR_RESULT:
total_brand_score,
SessionResultsConsts.RESULT:
result_summary,
SessionResultsConsts.SCORE:
result_summary,
'identifier_result':
identifier_ecaps_summary
})
return results_df
def gsk_pos_kpis(self):
"""
Function calculate POSM Distribution
:return
- results : array of dictionary, each dict contains kpi's result details
"""
results = []
OOS = 1
DISTRIBUTED = 2
self.gsk_generator.tool_box.extract_data_set_up_file(
Consts.POSM, self.set_up_data, Consts.KPI_DICT)
assortment_pos = self.msl_assortment(Consts.POSM_SKU, Consts.POSM)
kpi_gsk_pos_distribution_store_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.GSK_POS_DISTRIBUTION_STORE)
kpi_gsk_pos_distribution_brand_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.GSK_POS_DISTRIBUTION_BRAND)
kpi_gsk_pos_distribution_sku_fk = self.common.get_kpi_fk_by_kpi_type(
Consts.GSK_POS_DISTRIBUTION_SKU)
if assortment_pos is None or assortment_pos.empty:
Log.info(
"Assortment df is empty. GSK_POS_DISTRIBUTION Kpis are not calculated"
)
return results
# Calculate KPI : GSK_POS_DISTRIBUTION_STORE
assortment_pos['in_store'] = assortment_pos['in_store'].astype('int')
Log.info(
"Dropping duplicate product_fks accros multiple-granular groups")
Log.info("Before : {}".format(len(assortment_pos)))
assortment_pos = assortment_pos.drop_duplicates(
subset=[ProductsConsts.PRODUCT_FK])
Log.info("After : {}".format(len(assortment_pos)))
numerator_res = len(assortment_pos[assortment_pos['in_store'] == 1])
denominator_res = len(assortment_pos)
result = round(
(numerator_res /
float(denominator_res)), 4) if denominator_res != 0 else 0
results.append({
'fk': kpi_gsk_pos_distribution_store_fk,
SessionResultsConsts.NUMERATOR_ID: self.own_manufacturer,
SessionResultsConsts.DENOMINATOR_ID: self.store_fk,
SessionResultsConsts.NUMERATOR_RESULT: numerator_res,
SessionResultsConsts.DENOMINATOR_RESULT: denominator_res,
SessionResultsConsts.RESULT: result,
SessionResultsConsts.SCORE: result,
# 'identifier_parent': identifier_ecaps_summary,
'identifier_result': "Gsk_Pos_Distribution_Store",
'should_enter': True
})
# Calculate KPI: GSK_POS_DISTRIBUTION_BRAND
brands_group = assortment_pos.groupby([ProductsConsts.BRAND_FK])
for brand, assortment_pos_by_brand in brands_group:
numerator_res = len(assortment_pos_by_brand[
assortment_pos_by_brand['in_store'] == 1])
denominator_res = len(assortment_pos_by_brand)
result = round(
(numerator_res /
float(denominator_res)), 4) if denominator_res != 0 else 0
results.append({
'fk':
kpi_gsk_pos_distribution_brand_fk,
SessionResultsConsts.NUMERATOR_ID:
int(brand),
SessionResultsConsts.DENOMINATOR_ID:
self.store_fk,
SessionResultsConsts.NUMERATOR_RESULT:
numerator_res,
SessionResultsConsts.DENOMINATOR_RESULT:
denominator_res,
SessionResultsConsts.RESULT:
result,
SessionResultsConsts.SCORE:
result,
'identifier_parent':
"Gsk_Pos_Distribution_Store",
'identifier_result':
"Gsk_Pos_Distribution_Brand_" + str(int(brand)),
'should_enter':
True
})
for idx, each_product in assortment_pos_by_brand.iterrows():
product_fk = each_product[ProductsConsts.PRODUCT_FK]
result = 1 if int(each_product['in_store']) == 1 else 0
result_status = DISTRIBUTED if result == 1 else OOS
last_status = self.gsk_generator.tool_box.get_last_status(
kpi_gsk_pos_distribution_sku_fk, product_fk)
results.append({
'fk':
kpi_gsk_pos_distribution_sku_fk,
SessionResultsConsts.NUMERATOR_ID:
product_fk,
SessionResultsConsts.DENOMINATOR_ID:
self.store_fk,
SessionResultsConsts.NUMERATOR_RESULT:
result,
SessionResultsConsts.DENOMINATOR_RESULT:
1,
SessionResultsConsts.RESULT:
result_status,
SessionResultsConsts.SCORE:
last_status,
'identifier_parent':
"Gsk_Pos_Distribution_Brand_" + str(int(brand)),
'identifier_result':
"Gsk_Pos_Distribution_SKU_" + str(int(product_fk)),
'should_enter':
True
})
return results
class CCKRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
PRODUCT_LVL = 'Main_Display_Sku'
MAIN_DISPLAY = 'Main_Display_Items'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
kpi_info = pd.read_excel(os.path.join(kpi_path[:-len(base_file)],
'Data', 'Template.xlsx'),
sheet_name="KPI")
file_template = pd.read_excel(os.path.join(kpi_path[:-len(base_file)],
'Data', 'Template.xlsx'),
sheet_name="Template")
self.kpi_template_info = pd.DataFrame(
kpi_info) # contains the kpis + ean codes
self.kpi_metadata = self.data_provider.kpi # information about kpis such as (presentation order)
self.template_info = self.data_provider.templates # static data on templates
self.kpi_template = pd.DataFrame(
file_template) # relevant template for kpis
# common for new tables
self.commonV2 = CommonV2(self.data_provider)
self.kpi_new_static_data = self.commonV2.get_new_kpi_static_data()
self.manufacturer_fk = None if self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0] is None else \
int(self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0])
def main_calculation(self):
self.kpi_calc()
self.common.commit_results_data()
def kpi_calc(self):
sum_kpi = 0
relevant_template_fk = self.template_info.loc[
self.template_info.additional_attribute_1.isin(
self.kpi_template.additional_attribute_1.unique())].template_fk
self.scif = self.scif.loc[self.scif.template_fk.isin(
relevant_template_fk.unique())]
if self.kpi_template_info.empty:
return
for row in self.kpi_template_info.iterrows():
result = self.scif.loc[self.scif.product_ean_code == str(
row[1]['SKU EAN Code'])]
result = 0 if result.empty else int(result.facings.sum())
result_final = result if result > 0 else None
score = 100 if result > 0 else None
self.kpi_res(self.LEVEL3, score, row[1], result, result_final)
score = 0 if score is None else score
self.kpi_res(self.LEVEL2, score, row[1])
sum_kpi += score # score_1(level1)
denominator = len(self.kpi_template_info) # score_2(level 2)
self.kpi_res(self.LEVEL1, sum_kpi / 100, self.kpi_template_info.loc[0],
denominator)
# saving results to new tables
self.commonV2.commit_results_data()
def kpi_res(self, level, score, row, result=None, result_final=None):
kpi_fks = self.kpi_static_data.loc[
self.kpi_static_data['atomic_kpi_name'].str.encode(
'utf8') == row['Atomic Kpi Name'].encode('utf8')]
if kpi_fks.empty:
print(row['Atomic Kpi Name'])
Log.error(
"differences between kpi template and kpi static table in DB")
return
kps_name = kpi_fks['kpi_set_name'].iloc[0]
kpi_set_level_2_fk = self.commonV2.get_kpi_fk_by_kpi_type(
self.MAIN_DISPLAY)
kpi_identifier = self.commonV2.get_dictionary(
kpi_fk=kpi_set_level_2_fk)
if level == self.LEVEL1:
self.common.write_to_db_result(
fk=kpi_fks['kpi_set_fk'].iloc[0],
session_uid=self.session_uid,
store_fk=self.store_id,
visit_date=self.visit_date.isoformat(),
level=self.LEVEL1,
kps_name=kps_name,
kps_result=0,
kpi_set_fk=kpi_fks['kpi_set_fk'].iloc[0],
score=score,
score_2=result,
score_3=0)
result = result if result is not None else 0
self.commonV2.write_to_db_result(fk=kpi_set_level_2_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=score,
denominator_result=result,
identifier_result=kpi_identifier,
result=score,
score=score,
should_enter=True)
else:
kpi_fk = kpi_fks['kpi_fk'].iloc[0]
presentation_order = self.kpi_metadata[
self.kpi_metadata["pk"] ==
kpi_fk]['presentation_order'].iloc[0]
if level == self.LEVEL2:
self.common.write_to_db_result(
fk=kpi_fk,
session_uid=self.session_uid,
store_fk=self.store_id,
visit_date=self.visit_date.isoformat(),
level=self.LEVEL2,
kpk_name=kpi_fks['kpi_name'].iloc[0],
kpi_fk=kpi_fk,
score=score,
presentation_order=presentation_order,
score_2=100,
score_3=0)
else: # level 3 DB insertion
self.common.write_to_db_result(
level=self.LEVEL3,
fk=kpi_fk,
kpi_fk=kpi_fk,
score=score,
session_uid=self.session_uid,
store_fk=self.store_id,
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=kps_name,
missing_kpi_score="Bad",
style=("good" if score == 100 else None),
kpi_presentation_order=kpi_fk,
atomic_kpi_fk=kpi_fks['atomic_kpi_fk'].iloc[0],
display_text=row['Kpi Display Text (SKU Name)'],
atomic_kpi_presentation_order=1,
score_2=100,
vs_1_facings=result,
threshold=1,
result=result_final,
result_2=1,
result_3=1)
product_info = self.products.loc[self.products.product_ean_code
== str(row['SKU EAN Code'])]
if not product_info.empty:
product_fk = product_info.product_fk.iloc[0]
kpi_level_2_fk = self.commonV2.get_kpi_fk_by_kpi_type(
self.PRODUCT_LVL)
self.commonV2.write_to_db_result(
fk=kpi_level_2_fk,
numerator_id=product_fk,
denominator_id=self.manufacturer_fk,
numerator_result=result,
denominator_result=1,
identifier_parent=kpi_identifier,
result=score,
score=score,
should_enter=True)
LoggerInitializer.init('ccbottlersus calculations')
Config.init()
project_name = 'cclibertyus'
# MSC
sessions = [
'FC51FAC6-4EBB-4C9B-AC1B-F72052442DDE',
'E79B5B80-BAA2-4FA0-8C1F-594269B39457',
'E86F80DE-62C2-44AB-9949-80E520BCB3B2',
'F05079E5-11C4-4289-B5AE-5B8205594E15',
'dc322cc1-bfb7-4f2b-a6c3-c4c33a12b077'
]
# Liberty
sessions = [
'fe6e86a5-e96c-4ed1-b285-689ee8da393c',
'FAB57A4E-4814-4B74-A521-53A003864D06',
'BE9F0199-17B6-4A11-BA97-97751FE6EE0E',
'f6c0247d-64b4-4d11-8e0b-f7616316c08f'
]
for session in sessions:
print('***********************************************************************************')
print('_______________________ {} ____________________'.format(session))
data_provider = KEngineDataProvider(project_name)
data_provider.load_session_data(session)
output = Output()
common = Common(data_provider)
MSCToolBox(data_provider, output, common).main_calculation()
common.commit_results_data()
