class ALTRIAUSToolBox:
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common_v2 = CommonV2(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.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name, DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting()
self.kpi_results_queries = []
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Fixture Width", dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Conversion Table", dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Header Positions")
self.flip_sign_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
self.active_kpis = self._get_active_kpis()
self.external_targets = self.ps_data_provider.get_kpi_external_targets()
self.survey_dot_com_collected_this_session = self._get_survey_dot_com_collected_value()
self._add_smart_attributes_to_mpis()
self.scene_graphs = {}
self.excessive_flipsigns = False
self.incorrect_tags_in_pos_areas = []
def _add_smart_attributes_to_mpis(self):
smart_attribute_data = \
self.adp.get_match_product_in_probe_state_values(self.mpis['probe_match_fk'].unique().tolist())
self.mpis = pd.merge(self.mpis, smart_attribute_data, on='probe_match_fk', how='left')
self.mpis['match_product_in_probe_state_fk'].fillna(0, inplace=True)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
self.generate_graph_per_scene()
for graph in self.scene_graphs.values():
self.calculate_fixture_and_block_level_kpis(graph)
self.calculate_register_type()
self.calculate_age_verification()
self.calculate_facings_by_scene_type()
self.calculate_session_flags()
return
def calculate_fixture_and_block_level_kpis(self, graph):
for node, node_data in graph.nodes(data=True):
if node_data['category'].value != 'POS':
self.calculate_fixture_width(node_data)
self.calculate_flip_sign(node, node_data, graph)
self.calculate_flip_sign_empty_space(node, node_data, graph)
self.calculate_flip_sign_locations(node_data)
self.calculate_total_shelves(node_data)
self.calculate_tags_by_fixture_block(node_data)
if node_data['block_number'].value == 1:
self.calculate_header(node, node_data, graph)
self.calculate_product_above_headers(node_data)
self.calculate_no_headers(node_data)
return
def generate_graph_per_scene(self):
relevant_scif = self.scif[self.scif['template_name'] == 'Tobacco Merchandising Space']
for scene in relevant_scif['scene_id'].unique().tolist():
agb = AltriaGraphBuilder(self.data_provider, scene)
self.scene_graphs[scene] = agb.get_graph()
if agb.incorrect_tags_in_pos_area:
self.incorrect_tags_in_pos_areas.extend(agb.incorrect_tags_in_pos_area)
if len(self.scene_graphs.keys()) > 1:
Log.warning("More than one Tobacco Merchandising Space scene detected. Results could be mixed!")
return
def calculate_fixture_width(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Fixture Width')
width = node_data['calculated_width_ft'].value
width = round(width)
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
category_fk = self.get_category_fk_by_name(node_data['category'].value)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=width)
def calculate_flip_sign(self, node, node_data, graph):
if node_data['category'].value == 'Cigarettes':
self.calculate_flip_signs_cigarettes(node, node_data, graph)
else:
self.calculate_flip_signs_non_cigarettes(node, node_data, graph)
return
def calculate_flip_signs_non_cigarettes(self, node, node_data, graph):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
flip_signs_by_x_coord = {}
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
center_x = neighbor_data['polygon'].centroid.coords[0][0]
flip_signs_by_x_coord[center_x] = neighbor_data
for i, pair in enumerate(sorted(flip_signs_by_x_coord.items())):
position = i+1
position_fk = self.get_custom_entity_pk(str(position))
if position_fk == 0 or position > 8:
self.excessive_flipsigns = True
Log.warning('More than 8 flip-sign positions found for a non-cigarettes block')
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
implied_facings = pair[1]['width_of_signage_in_facings'].value
width = self.round_threshold(width)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=implied_facings)
def calculate_flip_signs_cigarettes(self, node, node_data, graph):
width = node_data['calculated_width_ft'].value
fixture_bounds = node_data['polygon'].bounds
fixture_width_coord_units = abs(fixture_bounds[0] - fixture_bounds[2])
proportions_dict = self.get_flip_sign_position_proportions(round(width))
if not proportions_dict:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
f_slot_boxes = {}
left_bound = fixture_bounds[0] # start proportional divisions on left side of fixture
for position, proportion in proportions_dict.items():
right_bound = left_bound + (fixture_width_coord_units * proportion)
# TODO update this to use min and max height of graph
f_slot_boxes[position] = box(left_bound, -9999, right_bound, 9999)
left_bound = right_bound
for position, slot_box in f_slot_boxes.items():
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
try:
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
except KeyError:
continue
flip_sign_bounds = neighbor_data['polygon'].bounds
flip_sign_box = box(flip_sign_bounds[0], flip_sign_bounds[1], flip_sign_bounds[2], flip_sign_bounds[3])
overlap_ratio = flip_sign_box.intersection(slot_box).area / flip_sign_box.area
if overlap_ratio >= REQUIRED_FLIP_SIGN_FSLOT_OVERLAP_RATIO:
product_fk = neighbor_data['product_fk'].value
position_fk = self.get_custom_entity_pk(position)
flip_sign_width = neighbor_data['calculated_width_ft'].value
flip_sign_width = self.round_threshold(flip_sign_width)
implied_facings = neighbor_data['width_of_signage_in_facings'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=flip_sign_width, score=implied_facings)
return
def calculate_flip_sign_empty_space(self, node, node_data, graph):
if node_data['category'].value != 'Cigarettes':
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Empty Flip Sign Space')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
fixture_width = node_data['calculated_width_ft'].value
fixture_width = self.round_threshold(fixture_width)
flip_sign_widths = []
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Flip-Sign':
continue
# exclude 'General POS Other'
if neighbor_data['product_fk'].value == 9304:
continue
neighbor_width = neighbor_data['calculated_width_ft'].value
neighbor_width = self.round_threshold(neighbor_width)
flip_sign_widths.append(neighbor_width)
empty_space = abs(fixture_width - sum(flip_sign_widths))
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=empty_space)
def calculate_flip_sign_locations(self, node_data):
if node_data['category'].value != 'Cigarettes':
return
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Flip Sign Locations')
width = node_data['calculated_width_ft'].value
width = round(width)
proportions_dict = self.get_flip_sign_position_proportions(width)
if not proportions_dict:
return
for position, proportion in proportions_dict.items():
position_fk = self.get_custom_entity_pk(position)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=round(proportion * width))
return
def get_flip_sign_position_proportions(self, width):
relevant_template = self.fixture_width_template[self.fixture_width_template['Fixture Width (ft)'] == width]
if relevant_template.empty:
Log.error("Unable to save flip sign location data. {}ft does not exist as a defined width".format(width))
return None
# this could definitely be simpler, but I'm tired and don't want to use my brain
relevant_template[['F1', 'F2', 'F3', 'F4']] = relevant_template[['F1', 'F2', 'F3', 'F4']].fillna(0)
f_slots = [relevant_template['F1'].iloc[0], relevant_template['F2'].iloc[0],
relevant_template['F3'].iloc[0], relevant_template['F4'].iloc[0]]
f_slots = [i for i in f_slots if i != 0]
proportions_dict = {}
for i, slot in enumerate(f_slots):
proportions_dict["F{}".format(i+1)] = slot / float(sum(f_slots))
return proportions_dict
def calculate_total_shelves(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Total Shelves')
shelves = len(node_data['shelf_number'].values)
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
category_fk = self.get_category_fk_by_name(node_data['category'].value)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=category_fk, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=shelves)
def calculate_tags_by_fixture_block(self, node_data):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('FACINGS_BY_FIXTURE_BLOCK')
scene_match_fks = node_data['match_fk'].values
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
relevant_matches = self.mpis[self.mpis['scene_match_fk'].isin(scene_match_fks)]
relevant_matches = relevant_matches.groupby(['product_fk', 'shelf_number', 'match_product_in_probe_state_fk'],
as_index=False).agg({'scene_match_fk': 'min',
'probe_match_fk': 'count'})
relevant_matches.rename(columns={'probe_match_fk': 'facings'}, inplace=True)
for row in relevant_matches.itertuples():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk,
denominator_id=row.match_product_in_probe_state_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=row.facings, score=row.shelf_number,
context_id=row.scene_match_fk)
def calculate_header(self, node, node_data, graph):
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Header')
parent_category = node_data['category'].value
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
if parent_category not in self.header_positions_template.columns.tolist():
return
headers_by_x_coord = {}
menu_board_items_by_x_coord = {}
for neighbor in graph.neighbors(node):
neighbor_data = graph.nodes[neighbor]
if neighbor_data['category'].value != 'POS':
continue
if neighbor_data['pos_type'].value != 'Header':
continue
center_x = neighbor_data['polygon'].centroid.coords[0][0]
if neighbor_data['in_menu_board_area'].value is True:
menu_board_items_by_x_coord[center_x] = neighbor_data
else:
headers_by_x_coord[center_x] = neighbor_data
number_of_headers = len(headers_by_x_coord.keys())
if menu_board_items_by_x_coord:
number_of_headers += 1
relevant_positions = \
self.header_positions_template[(self.header_positions_template['Number of Headers'] == number_of_headers)]
if relevant_positions.empty:
if number_of_headers == 0:
return
else:
Log.error("Too many headers ({}) found for one block ({}). Unable to calculate positions!".format(
number_of_headers, parent_category))
return
positions = relevant_positions[parent_category].iloc[0]
positions = [position.strip() for position in positions.split(',')]
for i, pair in enumerate(sorted(headers_by_x_coord.items())):
position_fk = self.get_custom_entity_pk(positions[i])
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
width = self.round_threshold(width)
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=width)
if menu_board_items_by_x_coord:
for pair in menu_board_items_by_x_coord.items():
position_fk = self.get_custom_entity_pk(positions[-1])
product_fk = pair[1]['product_fk'].value
width = pair[1]['calculated_width_ft'].value
width = self.round_threshold(width)
# width = 1 # this is because there is no real masking for menu board items
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=position_fk,
numerator_result=block_number, denominator_result=fixture_number,
result=width, score=width)
return
def calculate_product_above_headers(self, node_data):
try:
product_above_header = node_data['product_above_header'].value
except KeyError:
return
if not product_above_header:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Product Above Header')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=1, score=1, context_id=fixture_number)
return
def calculate_no_headers(self, node_data):
try:
no_header = node_data['no_header'].value
except KeyError:
return
if not no_header:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('No Header')
fixture_number = node_data['fixture_number'].value
block_number = node_data['block_number'].value
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id,
numerator_result=block_number, denominator_result=fixture_number,
result=1, score=1, context_id=fixture_number)
return
def _get_active_kpis(self):
active_kpis = self.kpi_new_static_data[(self.kpi_new_static_data['kpi_calculation_stage_fk'] == 3) &
(self.kpi_new_static_data['valid_from'] <= self.visit_date) &
((self.kpi_new_static_data['valid_until']).isnull() |
(self.kpi_new_static_data['valid_until'] >= self.visit_date))]
return active_kpis
def calculate_facings_by_scene_type(self):
kpi_name = 'FACINGS_BY_SCENE_TYPE'
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(kpi_name)
if kpi_name not in self.active_kpis['type'].unique().tolist():
return
config = self.get_external_target_data_by_kpi_fk(kpi_fk)
product_types = config.product_type
template_names = config.template_name
relevant_mpis = self.mpis[(self.mpis['product_type'].isin(product_types)) &
(self.mpis['template_name'].isin(template_names))]
relevant_mpis = relevant_mpis.groupby(['product_fk',
'template_fk',
'match_product_in_probe_state_fk'],
as_index=False)['scene_match_fk'].count()
relevant_mpis.rename(columns={'scene_match_fk': 'facings'}, inplace=True)
for row in relevant_mpis.itertuples():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=row.product_fk, denominator_id=row.template_fk,
context_id=row.match_product_in_probe_state_fk, result=row.facings)
return
def calculate_register_type(self):
if self.survey_dot_com_collected_this_session:
return
relevant_scif = self.scif[(self.scif['product_type'].isin(['POS', 'Other'])) &
(self.scif['category'] == 'POS Machinery')]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type')
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
def calculate_age_verification(self):
if self.survey_dot_com_collected_this_session:
return
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification')
relevant_scif = self.scif[self.scif['brand_name'].isin(['Age Verification'])]
if relevant_scif.empty:
result = 0
product_fk = 0
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
else:
result = 1
for product_fk in relevant_scif['product_fk'].unique().tolist():
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=result)
return
def calculate_session_flags(self):
if self.excessive_flipsigns:
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Excessive Flip Signs Detected')
self.common_v2.write_to_db_result(kpi_fk, numerator_id=49, denominator_id=self.store_id, result=1)
if self.incorrect_tags_in_pos_areas:
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Invalid Product in Header Areas')
relevant_product_fks = self.mpis[self.mpis['scene_match_fk'].isin(self.incorrect_tags_in_pos_areas)][
'product_fk'].unique().tolist()
for product_fk in relevant_product_fks:
self.common_v2.write_to_db_result(kpi_fk, numerator_id=product_fk, denominator_id=self.store_id,
result=1)
def mark_tags_in_explorer(self, probe_match_fk_list, mpipsr_name):
if not probe_match_fk_list:
return
try:
match_type_fk = \
self.match_product_in_probe_state_reporting[
self.match_product_in_probe_state_reporting['name'] == mpipsr_name][
'match_product_in_probe_state_reporting_fk'].values[0]
except IndexError:
Log.warning('Name not found in match_product_in_probe_state_reporting table: {}'.format(mpipsr_name))
return
match_product_in_probe_state_values_old = self.common_v2.match_product_in_probe_state_values
match_product_in_probe_state_values_new = pd.DataFrame(columns=[MATCH_PRODUCT_IN_PROBE_FK,
MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK])
match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_FK] = probe_match_fk_list
match_product_in_probe_state_values_new[MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = match_type_fk
self.common_v2.match_product_in_probe_state_values = pd.concat([match_product_in_probe_state_values_old,
match_product_in_probe_state_values_new])
return
@staticmethod
def round_threshold(value, threshold=0.2):
return round(value - threshold + 0.5)
def _get_survey_dot_com_collected_value(self):
try:
sales_rep_fk = self.session_info['s_sales_rep_fk'].iloc[0]
except IndexError:
sales_rep_fk = 0
return int(sales_rep_fk) == 209050
def get_category_fk_by_name(self, category_name):
return self.all_products[self.all_products['category'] == category_name]['category_fk'].iloc[0]
def get_custom_entity_pk(self, name):
try:
return self.custom_entity_data[self.custom_entity_data['name'] == name]['pk'].iloc[0]
except IndexError:
Log.error('No custom entity found for {}'.format(name))
return 0
def get_external_target_data_by_kpi_fk(self, kpi_fk):
return self.external_targets[self.external_targets['kpi_fk'] == kpi_fk].iloc[0]
def commit(self):
self.common_v2.commit_results_data()
class ALTRIAUS_SANDToolBox:
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.common_v2 = CommonV2(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.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.thresholds_and_results = {}
self.result_df = []
self.writing_to_db_time = datetime.timedelta(0)
self.kpi_results_queries = []
self.potential_products = {}
self.shelf_square_boundaries = {}
self.average_shelf_values = {}
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.all_template_data = parse_template(TEMPLATE_PATH, "KPI")
self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing")
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Fixture Width",
dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Conversion Table",
dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Header Positions")
self.flip_sign_positions_template = pd.read_excel(
FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(
1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.assortment = Assortment(self.data_provider,
output=self.output,
ps_data_provider=self.ps_data_provider)
self.store_assortment = self.assortment.get_lvl3_relevant_ass()
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
self.calculate_signage_locations_and_widths('Cigarettes')
self.calculate_signage_locations_and_widths('Smokeless')
self.calculate_register_type()
self.calculate_age_verification()
self.calculate_juul_availability()
self.calculate_assortment()
self.calculate_vapor_kpis()
kpi_set_fk = 2
set_name = \
self.kpi_static_data.loc[self.kpi_static_data['kpi_set_fk'] == kpi_set_fk]['kpi_set_name'].values[0]
template_data = self.all_template_data.loc[
self.all_template_data['KPI Level 1 Name'] == set_name]
try:
if set_name and not set(
template_data['Scene Types to Include'].values[0].encode(
).split(', ')) & set(
self.scif['template_name'].unique().tolist()):
Log.info('Category {} was not captured'.format(
template_data['category'].values[0]))
return
except Exception as e:
Log.info(
'KPI Set {} is not defined in the template'.format(set_name))
for i, row in template_data.iterrows():
try:
kpi_name = row['KPI Level 2 Name']
if kpi_name in KPI_LEVEL_2_cat_space:
# scene_type = [s for s in row['Scene_Type'].encode().split(', ')]
kpi_type = row['KPI Type']
scene_type = row['scene_type']
if row['Param1'] == 'Category' or 'sub_category':
category = row['Value1']
if kpi_type == 'category_space':
kpi_set_fk = \
self.kpi_new_static_data.loc[self.kpi_new_static_data['type'] == kpi_type]['pk'].values[0]
self.calculate_category_space(
kpi_set_fk,
kpi_name,
category,
scene_types=scene_type)
except Exception as e:
Log.info('KPI {} calculation failed due to {}'.format(
kpi_name.encode('utf-8'), e))
continue
return
def calculate_vapor_kpis(self):
category = 'Vapor'
relevant_scif = self.scif[self.scif['template_name'] ==
'JUUL Merchandising']
if relevant_scif.empty:
Log.info('No products found for {} category'.format(category))
return
relevant_scif = relevant_scif[
(relevant_scif['category'].isin([category, 'POS']))
& (relevant_scif['brand_name'] == 'Juul')]
if relevant_scif.empty:
return
relevant_product_pks = relevant_scif[
relevant_scif['product_type'] ==
'SKU']['product_fk'].unique().tolist()
relevant_scene_id = self.get_most_frequent_scene(relevant_scif)
product_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_product_pks))
& (self.mpis['scene_fk'] == relevant_scene_id)]
if product_mpis.empty:
Log.info('No products found for {} category'.format(category))
return
self.calculate_total_shelves(product_mpis, category, product_mpis)
longest_shelf = \
product_mpis[product_mpis['shelf_number'] ==
self.get_longest_shelf_number(product_mpis,
max_shelves_from_top=999)].sort_values(by='rect_x',
ascending=True)
if longest_shelf.empty or longest_shelf.isnull().all().all():
Log.warning(
'The {} category items are in a non-standard location. The {} category will not be calculated.'
.format(category, category))
return
relevant_pos = pd.DataFrame()
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
def calculate_assortment(self):
if self.scif.empty or self.store_assortment.empty:
Log.warning(
'Unable to calculate assortment: SCIF or store assortment is empty'
)
return
grouped_scif = self.scif.groupby('product_fk',
as_index=False)['facings'].sum()
assortment_with_facings = \
pd.merge(self.store_assortment, grouped_scif, how='left', on='product_fk')
assortment_with_facings.loc[:, 'facings'] = assortment_with_facings[
'facings'].fillna(0)
for product in assortment_with_facings.itertuples():
score = 1 if product.facings > 0 else 0
self.common_v2.write_to_db_result(
product.kpi_fk_lvl3,
numerator_id=product.product_fk,
denominator_id=product.assortment_fk,
numerator_result=product.facings,
result=product.facings,
score=score)
number_of_skus_present = len(
assortment_with_facings[assortment_with_facings['facings'] > 0])
score = 1 if number_of_skus_present > 0 else 0
kpi_fk = assortment_with_facings['kpi_fk_lvl2'].iloc[0]
assortment_group_fk = assortment_with_facings[
'assortment_group_fk'].iloc[0]
self.common_v2.write_to_db_result(
kpi_fk,
numerator_id=assortment_group_fk,
numerator_result=number_of_skus_present,
denominator_result=len(assortment_with_facings),
result=number_of_skus_present,
score=score)
def calculate_register_type(self):
relevant_scif = self.scif[
(self.scif['product_type'].isin(['POS', 'Other']))
& (self.scif['category'] == 'POS Machinery')]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_age_verification(self):
relevant_scif = self.scif[self.scif['brand_name'].isin(
['Age Verification'])]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_juul_availability(self):
relevant_scif = self.scif[(self.scif['brand_name'].isin(['Juul']))
& (self.scif['product_type'].isin(['POS']))]
juul_pos = relevant_scif['product_fk'].unique().tolist()
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Juul POS Availability')
if not juul_pos:
return
result = 1
for product_fk in juul_pos:
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_category_space(self,
kpi_set_fk,
kpi_name,
category,
scene_types=None):
template = self.all_template_data.loc[
(self.all_template_data['KPI Level 2 Name'] == kpi_name)
& (self.all_template_data['Value1'] == category)]
kpi_template = template.loc[template['KPI Level 2 Name'] == kpi_name]
if kpi_template.empty:
return None
kpi_template = kpi_template.iloc[0]
values_to_check = []
filters = {
'template_name': scene_types,
'category': kpi_template['Value1']
}
if kpi_template['Value1'] in CATEGORIES:
category_att = 'category'
if kpi_template['Value1']:
values_to_check = self.all_products.loc[
self.all_products[category_att] ==
kpi_template['Value1']][category_att].unique().tolist()
for primary_filter in values_to_check:
filters[kpi_template['Param1']] = primary_filter
new_kpi_name = self.kpi_name_builder(kpi_name, **filters)
result = self.calculate_category_space_length(
new_kpi_name, **filters)
filters['Category'] = kpi_template['KPI Level 2 Name']
score = result
numerator_id = self.products['category_fk'][
self.products['category'] == kpi_template['Value1']].iloc[0]
self.common_v2.write_to_db_result(kpi_set_fk,
numerator_id=numerator_id,
numerator_result=999,
result=score,
score=score)
def calculate_category_space_length(self,
kpi_name,
threshold=0.5,
retailer=None,
exclude_pl=False,
**filters):
"""
:param threshold: The ratio for a bay to be counted as part of a category.
:param filters: These are the parameters which the data frame is filtered by.
:return: The total shelf width (in mm) the relevant facings occupy.
"""
try:
filtered_scif = self.scif[self.get_filter_condition(
self.scif, **filters)]
space_length = 0
bay_values = []
for scene in filtered_scif['scene_fk'].unique().tolist():
scene_matches = self.mpis[self.mpis['scene_fk'] == scene]
scene_filters = filters
scene_filters['scene_fk'] = scene
for bay in scene_matches['bay_number'].unique().tolist():
bay_total_linear = scene_matches.loc[
(scene_matches['bay_number'] == bay)
& (scene_matches['stacking_layer'] == 1) &
(scene_matches['status']
== 1)]['width_mm_advance'].sum()
scene_filters['bay_number'] = bay
tested_group_linear = scene_matches[
self.get_filter_condition(scene_matches,
**scene_filters)]
tested_group_linear_value = tested_group_linear[
'width_mm_advance'].sum()
if tested_group_linear_value:
bay_ratio = tested_group_linear_value / float(
bay_total_linear)
else:
bay_ratio = 0
if bay_ratio >= threshold:
category = filters['category']
max_facing = scene_matches.loc[
(scene_matches['bay_number'] == bay)
& (scene_matches['stacking_layer'] == 1
)]['facing_sequence_number'].max()
shelf_length = self.spacing_template_data.query(
'Category == "' + category + '" & Low <= "' +
str(max_facing) + '" & High >= "' +
str(max_facing) + '"')
shelf_length = int(shelf_length['Size'].iloc[-1])
bay_values.append(shelf_length)
space_length += shelf_length
except Exception as e:
Log.info('Linear Feet calculation failed due to {}'.format(e))
space_length = 0
return space_length
def get_filter_condition(self, df, **filters):
"""
:param df: The data frame to be filters.
:param filters: These are the parameters which the data frame is filtered by.
Every parameter would be a tuple of the value and an include/exclude flag.
INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGROGENERALToolBox.INCLUDE_FILTER)
INPUT EXAMPLE (2): manufacturer_name = 'Diageo'
:return: a filtered Scene Item Facts data frame.
"""
if not filters:
return df['pk'].apply(bool)
if self.facings_field in df.keys():
filter_condition = (df[self.facings_field] > 0)
else:
filter_condition = None
for field in filters.keys():
if field in df.keys():
if isinstance(filters[field], tuple):
value, exclude_or_include = filters[field]
else:
value, exclude_or_include = filters[
field], self.INCLUDE_FILTER
if not value:
continue
if not isinstance(value, list):
value = [value]
if exclude_or_include == self.INCLUDE_FILTER:
condition = (df[field].isin(value))
elif exclude_or_include == self.EXCLUDE_FILTER:
condition = (~df[field].isin(value))
elif exclude_or_include == self.CONTAIN_FILTER:
condition = (df[field].str.contains(value[0], regex=False))
for v in value[1:]:
condition |= df[field].str.contains(v, regex=False)
else:
continue
if filter_condition is None:
filter_condition = condition
else:
filter_condition &= condition
else:
Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
def kpi_name_builder(self, kpi_name, **filters):
"""
This function builds kpi name according to naming convention
"""
for filter in filters.keys():
if filter == 'template_name':
continue
kpi_name = kpi_name.replace('{' + filter + '}',
str(filters[filter]))
kpi_name = kpi_name.replace("'", "\'")
return kpi_name
def calculate_signage_locations_and_widths(self, category):
excluded_types = ['Other', 'Irrelevant', 'Empty']
relevant_scif = self.scif[self.scif['template_name'] ==
'Tobacco Merchandising Space']
if relevant_scif.empty:
Log.info('No products found for {} category'.format(category))
return
# need to include scene_id from previous relevant_scif
# also need to split this shit up into different categories, i.e. smokeless, cigarettes
# need to figure out how to deal with POS from smokeless being included with cigarette MPIS
# get relevant SKUs from the cigarettes category
relevant_scif = relevant_scif[relevant_scif['category'].isin(
[category, 'POS'])]
relevant_product_pks = relevant_scif[
relevant_scif['product_type'] ==
'SKU']['product_fk'].unique().tolist()
relevant_pos_pks = \
relevant_scif[(relevant_scif['product_type'] == 'POS') &
~(relevant_scif['brand_name'] == 'Age Verification') &
~(relevant_scif['product_name'] == 'General POS Other')]['product_fk'].unique().tolist()
other_product_and_pos_pks = \
relevant_scif[relevant_scif['product_type'].isin(excluded_types)]['product_fk'].tolist()
relevant_scene_id = self.get_most_frequent_scene(relevant_scif)
product_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_product_pks))
& (self.mpis['scene_fk'] == relevant_scene_id)]
if product_mpis.empty:
Log.info('No products found for {} category'.format(category))
return
self.calculate_total_shelves(product_mpis, category)
longest_shelf = \
product_mpis[product_mpis['shelf_number'] ==
self.get_longest_shelf_number(product_mpis)].sort_values(by='rect_x', ascending=True)
if longest_shelf.empty or longest_shelf.isnull().all().all():
Log.warning(
'The {} category items are in a non-standard location. The {} category will not be calculated.'
.format(category, category))
return
# demarcation_line = longest_shelf['rect_y'].median() old method, had bugs due to longest shelf being lower
demarcation_line = product_mpis['rect_y'].min()
exclusion_line = -9999
excluded_mpis = self.mpis[
~(self.mpis['product_fk'].isin(relevant_pos_pks +
relevant_product_pks +
other_product_and_pos_pks))
& (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) &
(self.mpis['rect_x'] > longest_shelf['rect_x'].min()) &
(self.mpis['scene_fk'] == relevant_scene_id) &
(self.mpis['rect_y'] < demarcation_line)]
# we need this line for when SCIF and MPIS don't match
excluded_mpis = excluded_mpis[~excluded_mpis['product_type'].
isin(excluded_types)]
if not excluded_mpis.empty:
exclusion_line = excluded_mpis['rect_y'].max()
# we need to get POS stuff that falls within the x-range of the longest shelf (which is limited by category)
# we also need to account for the fact that the images suck, so we're going to add/subtract 5% of the
# max/min values to allow for POS items that fall slightly out of the shelf length range
correction_factor = 0.05
correction_value = (longest_shelf['rect_x'].max() -
longest_shelf['rect_x'].min()) * correction_factor
pos_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_pos_pks))
& (self.mpis['rect_x'] <
(longest_shelf['rect_x'].max() + correction_value)) &
(self.mpis['rect_x'] >
(longest_shelf['rect_x'].min() - correction_value)) &
(self.mpis['scene_fk'] == relevant_scene_id)]
# DO NOT SET TO TRUE WHEN DEPLOYING
# debug flag displays polygon_mask graph DO NOT SET TO TRUE WHEN DEPLOYING
# DO NOT SET TO TRUE WHEN DEPLOYING
relevant_pos = self.adp.get_products_contained_in_displays(
pos_mpis, y_axis_threshold=35, debug=False)
if relevant_pos.empty:
Log.warning(
'No polygon mask was generated for {} category - cannot compute KPIs'
.format(category))
# we need to attempt to calculate fixture width, even if there's no polygon mask
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
relevant_pos = relevant_pos[[
'product_fk', 'product_name', 'left_bound', 'right_bound',
'center_x', 'center_y'
]]
relevant_pos = relevant_pos.reindex(
columns=relevant_pos.columns.tolist() +
['type', 'width', 'position'])
relevant_pos['width'] = \
relevant_pos.apply(lambda row: self.get_length_of_pos(row, longest_shelf, category), axis=1)
relevant_pos['type'] = \
relevant_pos['center_y'].apply(lambda x: 'Header' if exclusion_line < x < demarcation_line else 'Flip Sign')
relevant_pos = relevant_pos.sort_values(['center_x'], ascending=True)
relevant_pos = self.remove_duplicate_pos_tags(relevant_pos)
# generate header positions
if category == 'Cigarettes':
number_of_headers = len(
relevant_pos[relevant_pos['type'] == 'Header'])
if number_of_headers > len(
self.header_positions_template['Cigarettes Positions'].
dropna()):
Log.warning(
'Number of Headers for Cigarettes is greater than max number defined in template!'
)
elif number_of_headers > 0:
header_position_list = [
position.strip()
for position in self.header_positions_template[
self.header_positions_template['Number of Headers'] ==
number_of_headers]
['Cigarettes Positions'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Header',
['position']] = header_position_list
elif category == 'Smokeless':
relevant_pos = self.check_menu_scene_recognition(relevant_pos)
number_of_headers = len(
relevant_pos[relevant_pos['type'] == 'Header'])
if number_of_headers > len(
self.header_positions_template['Smokeless Positions'].
dropna()):
Log.warning(
'Number of Headers for Smokeless is greater than max number defined in template!'
)
elif number_of_headers > 0:
header_position_list = [
position.strip()
for position in self.header_positions_template[
self.header_positions_template['Number of Headers'] ==
number_of_headers]
['Smokeless Positions'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Header',
['position']] = header_position_list
relevant_pos = self.get_menu_board_items(relevant_pos,
longest_shelf, pos_mpis)
# generate flip-sign positions
if category == 'Cigarettes':
relevant_template = \
self.fixture_width_template.loc[(self.fixture_width_template['Fixture Width (facings)']
- len(longest_shelf)).abs().argsort()[:1]].dropna(axis=1)
locations = relevant_template.columns[2:].tolist()
right_bound = 0
longest_shelf_copy = longest_shelf.copy()
for location in locations:
if right_bound > 0:
left_bound = right_bound + 1
else:
left_bound = longest_shelf_copy.iloc[:relevant_template[
location].iloc[0]]['rect_x'].min()
right_bound = longest_shelf_copy.iloc[:relevant_template[
location].iloc[0]]['rect_x'].max()
if locations[-1] == location:
right_bound = right_bound + abs(right_bound * 0.05)
flip_sign_pos = relevant_pos[
(relevant_pos['type'] == 'Flip Sign')
& (relevant_pos['center_x'] > left_bound) &
(relevant_pos['center_x'] < right_bound)]
if flip_sign_pos.empty:
# add 'NO Flip Sign' product_fk
relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \
[location, NO_FLIP_SIGN_PK, 'Flip Sign']
else:
relevant_pos.loc[flip_sign_pos.index,
['position']] = location
longest_shelf_copy.drop(
longest_shelf_copy.iloc[:relevant_template[location].
iloc[0]].index,
inplace=True)
elif category == 'Smokeless':
# if there are no flip signs found, there are no positions to assign
number_of_flip_signs = len(
relevant_pos[relevant_pos['type'] == 'Flip Sign'])
if number_of_flip_signs > self.flip_sign_positions_template[
'Number of Flip Signs'].max():
Log.warning(
'Number of Flip Signs for Smokeless is greater than max number defined in template!'
)
elif number_of_flip_signs > 0:
flip_sign_position_list = [
position.strip()
for position in self.flip_sign_positions_template[
self.
flip_sign_positions_template['Number of Flip Signs'] ==
number_of_flip_signs]['Position'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Flip Sign',
['position']] = flip_sign_position_list
# store empty flip sign values
for location in ['Secondary', 'Tertiary']:
if location not in relevant_pos[
relevant_pos['type'] ==
'Flip Sign']['position'].tolist():
relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \
[location, NO_FLIP_SIGN_PK, 'Flip Sign']
relevant_pos = relevant_pos.reindex(
columns=relevant_pos.columns.tolist() + ['denominator_id'])
# this is a bandaid fix that should be removed -> 'F7011A7C-1BB6-4007-826D-2B674BD99DAE'
# removes POS items that were 'extra', i.e. more than max value in template
# only affects smokeless
relevant_pos.dropna(subset=['position'], inplace=True)
relevant_pos.loc[:,
['denominator_id']] = relevant_pos['position'].apply(
self.get_custom_entity_pk)
for row in relevant_pos.itertuples():
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(row.type)
self.common_v2.write_to_db_result(
kpi_fk,
numerator_id=row.product_fk,
denominator_id=row.denominator_id,
result=row.width,
score=row.width)
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
def calculate_total_shelves(self,
longest_shelf,
category,
product_mpis=None):
category_fk = self.get_category_fk_by_name(category)
if product_mpis is None:
product_mpis = self.mpis[
(self.mpis['rect_x'] > longest_shelf['rect_x'].min())
& (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) &
(self.mpis['scene_fk']
== longest_shelf['scene_fk'].fillna(0).mode().iloc[0])]
total_shelves = len(product_mpis['shelf_number'].unique())
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Total Shelves')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=category_fk,
denominator_id=self.store_id,
result=total_shelves)
def calculate_fixture_width(self, relevant_pos, longest_shelf, category):
correction_factor = 1 if category == 'Smokeless' else 2
longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1]
category_fk = self.get_category_fk_by_name(category)
# this is needed to remove intentionally duplicated 'Menu Board' POS 'Headers'
relevant_pos = relevant_pos.drop_duplicates(subset=['position'])
# try:
# width = relevant_pos[relevant_pos['type'] == 'Header']['width'].sum()
# except KeyError:
# # needed for when 'width' doesn't exist
# width = 0
# if relevant_pos.empty or width == 0:
width = round(
len(longest_shelf) + correction_factor /
float(self.facings_to_feet_template[category + ' Facings'].iloc[0])
)
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Fixture Width')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=category_fk,
denominator_id=self.store_id,
result=width)
def get_category_fk_by_name(self, category_name):
return self.all_products[self.all_products['category'] ==
category_name]['category_fk'].iloc[0]
def check_menu_scene_recognition(self, relevant_pos):
mdis = self.adp.get_match_display_in_scene()
mdis = mdis[mdis['display_name'] == 'Menu POS']
if mdis.empty:
return relevant_pos
dummy_sku_for_menu_pk = 9282 # 'Other (Smokeless Tobacco)'
dummy_sky_for_menu_name = 'Menu POS (Scene Recognized Item)'
location_type = 'Header'
width = 1
center_x = mdis['x'].iloc[0]
center_y = mdis['y'].iloc[0]
relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width']] = \
[dummy_sku_for_menu_pk, dummy_sky_for_menu_name, center_x, center_y, location_type, width]
relevant_pos = relevant_pos.sort_values(
['center_x'], ascending=True).reset_index(drop=True)
return relevant_pos
def get_menu_board_items(self, relevant_pos, longest_shelf, pos_mpis):
# get the placeholder item
menu_board_dummy = relevant_pos[relevant_pos['product_name'] ==
'Menu POS (Scene Recognized Item)']
# if no placeholder, this function isn't relevant
if menu_board_dummy.empty:
return relevant_pos
center_x = menu_board_dummy['center_x'].iloc[0]
center_y = menu_board_dummy['center_y'].iloc[0]
position = menu_board_dummy['position'].iloc[0]
demarcation_line = longest_shelf['rect_y'].min()
upper_demarcation_line = center_y - (demarcation_line - center_y)
distance_in_facings = 2
try:
left_bound = longest_shelf[
longest_shelf['rect_x'] < center_x].sort_values(
by=['rect_x'],
ascending=False)['rect_x'].iloc[int(distance_in_facings) -
1]
except IndexError:
# if there are no POS items found to the left of the 'Menu POS' scene recognition tag, use the tag itself
# in theory this should never happen
left_bound = center_x
try:
right_bound = longest_shelf[
longest_shelf['rect_x'] > center_x].sort_values(
by=['rect_x'],
ascending=True)['rect_x'].iloc[int(distance_in_facings) -
1]
except IndexError:
# if there are no POS items found to the right of the 'Menu POS' scene recognition tag, use the tag itself
# this is more likely to happen for the right bound than the left bound
right_bound = center_x
pos_mpis = pos_mpis[(pos_mpis['rect_x'] > left_bound)
& (pos_mpis['rect_x'] < right_bound) &
(pos_mpis['rect_y'] > upper_demarcation_line) &
(pos_mpis['rect_y'] < demarcation_line)]
if pos_mpis.empty:
return relevant_pos
# remove the placeholder item
relevant_pos = relevant_pos[~(relevant_pos['product_name'] ==
'Menu POS (Scene Recognized Item)')]
location_type = 'Header'
width = 1
for row in pos_mpis.itertuples():
relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x',
'center_y', 'type', 'width', 'position']] = \
[row.product_fk, row.product_name, row.rect_x, row.rect_y, location_type, width, position]
return relevant_pos
@staticmethod
def remove_duplicate_pos_tags(relevant_pos_df):
duplicate_results = \
relevant_pos_df[relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)]
duplicate_results_without_other = duplicate_results[
~duplicate_results['product_name'].str.contains('Other')]
results_without_duplicates = \
relevant_pos_df[~relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)]
if duplicate_results_without_other.empty:
return relevant_pos_df[~relevant_pos_df.duplicated(
subset=['left_bound', 'right_bound'], keep='first')]
else:
results = pd.concat(
[duplicate_results_without_other, results_without_duplicates])
# we need to sort_index to fix the sort order to reflect center_x values
return results.drop_duplicates(
subset=['left_bound', 'right_bound']).sort_index()
def get_custom_entity_pk(self, name):
return self.custom_entity_data[self.custom_entity_data['name'] ==
name]['pk'].iloc[0]
def get_length_of_pos(self, row, longest_shelf, category):
width_in_facings = len(
longest_shelf[(longest_shelf['rect_x'] > row['left_bound']) &
(longest_shelf['rect_x'] < row['right_bound'])]) + 2
category_facings = category + ' Facings'
return self.facings_to_feet_template.loc[(
self.facings_to_feet_template[category_facings] -
width_in_facings).abs().argsort()[:1]]['POS Width (ft)'].iloc[0]
@staticmethod
def get_longest_shelf_number(relevant_mpis, max_shelves_from_top=3):
# returns the shelf_number of the longest shelf
try:
longest_shelf = \
relevant_mpis[relevant_mpis['shelf_number'] <= max_shelves_from_top].groupby('shelf_number').agg(
{'scene_match_fk': 'count'})['scene_match_fk'].idxmax()
except ValueError:
longest_shelf = pd.DataFrame()
return longest_shelf
@staticmethod
def get_most_frequent_scene(relevant_scif):
try:
relevant_scene_id = relevant_scif['scene_id'].fillna(
0).mode().iloc[0]
except IndexError:
relevant_scene_id = 0
return relevant_scene_id
def commit(self):
self.common_v2.commit_results_data()
class RBUSToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = 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.tools = RBUSGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn)
self.old_common = common_old(data_provider)
self.kpi_static_data = self.old_common.get_kpi_static_data()
# self.kpi_results_queries = []
self.templates_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.template_dict = self.get_df_from_excel_path()
self.template_dict = self.template_dict[self.template_dict['Template for KPI'] != 'none']\
.set_index('Template for KPI')['KPI Level 3 Name'].to_dict()
self.excluded_sub_categories = self.get_excluded_sub_categories(self.get_df_from_excel_path())
self.common_new = Common(data_provider)
self.manufacturer_fk = self.data_provider[Data.OWN_MANUFACTURER]['param_value'].iloc[0]
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.templates = self.data_provider[Data.TEMPLATES]
self.sub_brands = self.ps_data.get_custom_entities(1001)
def get_kpi_static_data(self):
"""
This function extracts the static KPI data and saves it into one global data frame.
The data is taken from static.kpi / static.atomic_kpi / static.kpi_set.
"""
query = RBUSQueries.get_all_kpi_data()
kpi_static_data = pd.read_sql_query(query, self.rds_conn.db)
return kpi_static_data
@staticmethod
def get_scene_count(df, template_name):
return len(df['scene_fk'][df.template_name == template_name].unique())
def calculate_placement_count(self):
"""
this function calculates number of occurrences for each scene type and adds the correct score to insert query
which will be later committed to report.kpi_result db
:return: None
"""
df = self.scif[['template_name', 'scene_fk']]
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(PLACEMENT_COUNT)
# iterate scene types names
for template_name in df.template_name.unique():
# get number of occurrences of this scene type
value = self.get_scene_count(df, template_name)
# get the atomic kpi name
atomic_kpi_name = 'no'
if template_name in self.template_dict:
atomic_kpi_name = self.template_dict[template_name]
if atomic_kpi_name != 'no':
# get the atomic kpi fk of template name
old_atomic_kpi_fk = self.old_common.get_kpi_fk_by_kpi_name(atomic_kpi_name, self.LEVEL3)
Log.debug(atomic_kpi_name + " " + str(old_atomic_kpi_fk) + " " + str(value))
# add insert query for later use
self.old_common.write_to_db_result(old_atomic_kpi_fk, score=value, level=self.LEVEL3)
template_fk = self.templates[self.templates['template_name'] == template_name]
if len(template_fk) == 0:
Log.debug('template {} does not exist'.format(template_name))
else:
template_fk = template_fk['template_fk'].values[0]
self.common_new.write_to_db_result(fk=new_kpi_fk, numerator_id=template_fk,
result=value, denominator_id=self.store_id)
def get_product_list_field(self, kpi):
"""
get the type to check according to excel template
:param kpi: kpi name to check type for
:return: type name if kpi exists
"""
excel_df = self.get_df_from_excel_path()
if kpi in excel_df[KPI_LEVEL_3_NAME].values:
return excel_df['Product List Field'].loc[excel_df[KPI_LEVEL_3_NAME] == kpi].values[0]
@staticmethod
def get_type_dict(template_df, kpi_type):
"""
this function returns dictionary with type and value to calculate kpi to
:param template_df: excel file
:param kpi_type: brand/sku
:return: dictionary
"""
type_dict = {}
for i, row in template_df.iterrows():
if str(row['Type']).strip().lower() == kpi_type:
type_dict[row[KPI_LEVEL_3_NAME]] = row['Value']
return type_dict
def calculate_manufacturer(self, curr_probe, product_list_field):
"""
this function calculates score for Red Bull manufacturer category in a specific probe
"""
total_products = 0
redbull_manufacturer_products = 0
for i, row in curr_probe.iterrows():
if row['stacking_layer'] == 1:
facing_count = get_face_count(row.face_count)
total_products += facing_count
if (self.get_value_of_product_by_column_name(row, product_list_field) == u'Red Bull') & \
(~self.is_sub_category_excluded(row)):
redbull_manufacturer_products += facing_count
return float(redbull_manufacturer_products) / total_products
def calculate_redbull_manufacturer(self, shelf_occupation_dict, product_list_field):
"""
this function calculates score for Red Bull manufacturer category
it iterates shelves and bays as if they were matrix and aggregates score for each "cell" or "curr_probe"
"""
score = 0
for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1):
for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1):
# get the current probe to calculate - specific shelf, bay, and only in main_placement scene type
curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number, bay_number,
shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES))
if not curr_probe.empty:
score += self.calculate_manufacturer(curr_probe, product_list_field)
Log.debug("manufacturer score " + str(score))
return score
def calculate_category(self, curr_probe, product_list_field):
"""
this function calculates score for energy drinks category in a specific probe
"""
energy_products = 0
total_products = 0
# iterate curr_probe rows
for i, row in curr_probe.iterrows():
if row['stacking_layer'] == 1:
facing_count = get_face_count(row.face_count)
total_products += facing_count
if (self.get_value_of_product_by_column_name(row, product_list_field) == u'Energy') & \
(~self.is_sub_category_excluded(row)):
energy_products += facing_count
return float(energy_products) / total_products
def calculate_energy_drinks(self, shelf_occupation_dict, product_list_field):
"""
this function calculates score for energy drinks category
"""
score = 0
for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1):
for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1):
# get the current probe to calculate - specific shelf, bay, and only in main_placement scene type
curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number, bay_number,
shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES))
if not curr_probe.empty:
score += self.calculate_category(curr_probe, product_list_field)
Log.debug("category score " + str(score))
return score
def calculate_brand(self, curr_probe, brand_value, product_list_field):
"""
this function iterates current probe and calculates score for kpi of type brand
"""
total_products = 0
curr_brand_count = 0
for i, row in curr_probe.iterrows():
if row['stacking_layer'] == 1:
facing_count = get_face_count(row.face_count)
total_products += facing_count
sub_brand = self.get_value_of_product_by_column_name(row, product_list_field)
if (sub_brand == brand_value) & (~self.is_sub_category_excluded(row)):
curr_brand_count += facing_count
return (curr_brand_count) / total_products
def calculate_brand_by_name(self, kpi, shelf_occupation_dict, brand_name, product_list_field):
"""
this function calculates the score for atomic kpi of type brand
after it gets the score it adds query for later insert to DB
"""
score = 0
for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1):
for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1):
# get the current probe to calculate - specific shelf, bay, and only in main_placement scene type
curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number,
bay_number, shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES))
if not curr_probe.empty:
score += self.calculate_brand(curr_probe, brand_name, product_list_field)
return score
def is_sub_category_excluded(self, row):
"""
this method checks if this product sub-category is in the list of sub-categories to exclude from calculation
:param row: represent 1 item
:return: bool
"""
return self.all_products.sub_category.loc[self.all_products.product_fk ==
row.product_fk].isin(self.excluded_sub_categories).values[0]
def get_value_of_product_by_column_name(self, row, column_name):
"""
this method calculates value rom specific column in self.all_products
:param row: current row in curr_probe data frame(see other method to understand what is curr_probe) ,
represent 1 item
:param column_name: column name ot get the value from
:return: value
"""
product_fk = row.product_fk
all_products_row = self.all_products.loc[self.all_products.product_fk == product_fk]
if column_name == 'Sub Brand':
column_name = 'sub_brand_name'
value = all_products_row[column_name].values[0]
return value
def calculate_sku(self, curr_probe, sku_name, product_list_field):
"""
this function iterates current probe and calculates score for kpi of type sku
"""
total_products = 0
curr_sku_count = 0
for i, row in curr_probe.iterrows():
if row['stacking_layer'] == 1:
facing_count = get_face_count(row.face_count)
total_products += facing_count
if (self.get_value_of_product_by_column_name(row, product_list_field) == sku_name) & \
(~self.is_sub_category_excluded(row)):
curr_sku_count += facing_count
return float(curr_sku_count) / total_products
def calculate_sku_by_name(self, kpi, shelf_occupation_dict, sku_name, product_list_field):
"""
this function calculates the score for atomic kpi of type SKU
after it gets the score it adds query for later insert to DB
"""
score = 0
for shelf_number in range(1, shelf_occupation_dict.get(NUM_OF_SHELVES) + 1):
for bay_number in range(1, shelf_occupation_dict.get(NUM_OF_BAYS) + 1):
# get the current probe to calculate - specific shelf, bay, and only in main_placement scene type
curr_probe = get_curr_probe(shelf_occupation_dict.get(DF), shelf_number,
bay_number, shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES))
if not curr_probe.empty:
score += self.calculate_sku(curr_probe, sku_name, product_list_field)
return score
def get_df_from_excel_path(self):
"""
this method finds the template path and converts it to Data frame
:return: Data frame
"""
# path to template file
excel_path = os.path.join(self.templates_path, 'KPIs Template RB 180405.xlsx')
# convert template to Data frame
excel_df = pd.read_excel(excel_path, sheetname='Sheet1')
return excel_df
def calculate_shelf_occupation(self):
"""
this function iterates the atomic kpis in Shelf occupation and "sends" each kpi to it's correct method
for getting it's score.
after it gets the score it adds query for later insert to DB for the first 3 KPIs
all other LPI results will be added to the insert query later
"""
shelf_occupation_dict = self.get_shelf_occupation_dictionary()
manufacturer_score, category_score = 0, 0
parent_fk = self.common_new.get_kpi_fk_by_kpi_name(SHELF_OCCUPATION)
# iterate all atomic KPIs from this KPI set
for kpi in shelf_occupation_dict[SHELF_OCCUPATIONS_KPIS]:
product_list_field = self.get_product_list_field(kpi)
# calculate redbull manufacturer score
if kpi == 'K001':
manufacturer_score = self.calculate_redbull_manufacturer(shelf_occupation_dict, product_list_field)
self.old_common.write_to_db_result(self.old_common.get_kpi_fk_by_kpi_name(kpi, self.LEVEL3),
score=manufacturer_score, level=self.LEVEL3)
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(kpi)
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=self.manufacturer_fk, result=manufacturer_score,
denominator_id=self.store_id, identifier_parent=self.common_new.get_dictionary(kpi_fk=parent_fk),
should_enter=True)
# calculate energy category score
elif kpi == 'K002':
category_score = self.calculate_energy_drinks(shelf_occupation_dict, product_list_field)
self.old_common.write_to_db_result(self.old_common.get_kpi_fk_by_kpi_name(kpi, self.LEVEL3),
score=category_score, level=self.LEVEL3)
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(kpi)
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=CATEGORY_ENERGY_FK, result=category_score,
denominator_id=self.store_id, identifier_parent=self.common_new.get_dictionary(kpi_fk=parent_fk),
should_enter=True)
# calculate score for all energy drinks that are NOT from red bull manufacturer(K002 score - K001 score)
elif kpi == 'K003':
score = category_score - manufacturer_score
self.old_common.write_to_db_result(self.old_common.get_kpi_fk_by_kpi_name(kpi, self.LEVEL3),
score=score, level=self.LEVEL3)
Log.debug("K003 Score " + str(score))
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(kpi)
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=self.manufacturer_fk, result=score,
denominator_id=self.store_id, identifier_parent=self.common_new.get_dictionary(kpi_fk=parent_fk),
should_enter=True)
# calculate brands score
elif kpi in shelf_occupation_dict.get(BRANDS_DICT):
sub_brand_name = shelf_occupation_dict.get(BRANDS_DICT).get(kpi)
score = self.calculate_brand_by_name(kpi, shelf_occupation_dict, sub_brand_name, product_list_field)
Log.debug("brand score " + sub_brand_name + " " + str(score))
self.old_common.write_to_db_result(self.old_common.get_kpi_fk_by_kpi_name(kpi, self.LEVEL3),
score=score, level=self.LEVEL3)
# New table's kpi name is different
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(kpi[:4])
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=self.get_sub_brand_fk(sub_brand_name), result=score,
denominator_id=self.store_id, identifier_parent=self.common_new.get_dictionary(kpi_fk=parent_fk),
should_enter=True)
# calculate sku score
elif kpi in shelf_occupation_dict.get(SKUS_DICT):
sku_name = shelf_occupation_dict.get(SKUS_DICT).get(kpi)
score = self.calculate_sku_by_name(kpi, shelf_occupation_dict, sku_name, product_list_field)
Log.debug("sku score " + sku_name + " " + str(score))
self.old_common.write_to_db_result(self.old_common.get_kpi_fk_by_kpi_name(kpi, self.LEVEL3),
score=score, level=self.LEVEL3)
# New table's kpi name is different
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(kpi[:4])
if new_kpi_fk:
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=self.get_product_alt_code(sku_name), result=score,
denominator_id=self.store_id, identifier_parent=self.common_new.get_dictionary(kpi_fk=parent_fk),
should_enter=True)
def get_shelf_occupation_dictionary(self):
"""
this method prepares dictionary that contains all necessary data for calculating shelf occupation KPI
:return: shelf occupation dictionary
"""
shelf_occupation_dict = {}
# get a list of shelf occupation atomic KPIs
shelf_occupations_kpis = self.kpi_static_data[self.kpi_static_data[KPI_SET_NAME] ==
SHELF_OCCUPATION]['atomic_kpi_name'].values
shelf_occupation_dict[SHELF_OCCUPATIONS_KPIS] = shelf_occupations_kpis
# get only main_placement scenes because we need only them for this set
main_placement_scenes = self.scif.scene_id[(self.scif['template_name'] == MAIN_PLACEMENT)].unique()
shelf_occupation_dict[MAIN_PLACEMENT_SCENES] = main_placement_scenes
# shortcut for this table name
df = self.match_product_in_scene
shelf_occupation_dict[DF] = df
# get number of shelves
num_of_shelves = df.loc[df[SHELF_NUMBER].idxmax()][SHELF_NUMBER]
shelf_occupation_dict[NUM_OF_SHELVES] = num_of_shelves
# get number of bays
num_of_bays = df.loc[df[BAY_NUMBER].idxmax()][BAY_NUMBER]
shelf_occupation_dict[NUM_OF_BAYS] = num_of_bays
# get the template file as Data frame
excel_df = self.get_df_from_excel_path()
shelf_occupation_dict[EXCEL_DF] = excel_df
# get dictionary of brand name and values
brands_dict = self.get_type_dict(excel_df, BRAND)
shelf_occupation_dict[BRANDS_DICT] = brands_dict
# get dictionary of sku name and value
skus_dict = self.get_type_dict(excel_df, SKU)
shelf_occupation_dict[SKUS_DICT] = skus_dict
# kpi set for later use in "get_atomic_kpi_fk" method
shelf_occupation_dict[KPI_SET] = SHELF_OCCUPATION
return shelf_occupation_dict
@staticmethod
def get_excluded_sub_categories(excel_df):
excluding_sub_categories = excel_df['Excluding Sub Category'].unique()
return excluding_sub_categories
# def get_atomic_kpi_fk(self, atomic_kpi, kpi_set):
# """
# this function returns atomic_kpi_fk by kpi name
# :param atomic_kpi: atomic kpi name
# :param kpi_set: kpi set name
# :return: atomic_kpi_fk
# """
# atomic_kpi_fk = \
# self.kpi_static_data[(self.kpi_static_data[KPI_SET_NAME] == kpi_set) &
# (self.kpi_static_data[ATOMIC_KPI_NAME] == atomic_kpi)][ATOMIC_KPI_FK].values[0]
# return atomic_kpi_fk
def main_calculation(self, set_name):
"""
this function chooses the correct set name and calculates it's scores
:param set_name: set name to calculate score for
"""
if set_name == 'Placement count':
self.calculate_placement_count()
elif set_name == SHELF_OCCUPATION:
self.calculate_shelf_occupation()
# save high level kpi for hierarchy in new tables
new_kpi_fk = self.common_new.get_kpi_fk_by_kpi_name(SHELF_OCCUPATION)
self.common_new.write_to_db_result(
fk=new_kpi_fk, numerator_id=self.manufacturer_fk, result=0,
denominator_id=self.store_id, identifier_result=self.common_new.get_dictionary(kpi_fk=new_kpi_fk),
should_enter=False)
def get_sub_brand_fk(self, sub_brand):
"""
takes sub_brand and returns its pk
:param sub_brand: str
:param brand_fk: we need it for the parent_id (different brands can have common sub_brand)
:return: pk
"""
sub_brand_line = self.sub_brands[(self.sub_brands['name'] == sub_brand)]
if sub_brand_line.empty:
return None
else:
return sub_brand_line.iloc[0]['pk']
def get_product_alt_code(self, product_name):
"""
takes sub_brand and returns its pk
:param sub_brand: str
:param brand_fk: we need it for the parent_id (different brands can have common sub_brand)
:return: pk
"""
products = self.all_products[['product_name', 'product_fk', 'alt_code_1']]
products = products.loc[products['alt_code_1'] == product_name]
if products.empty:
Log.debug('Product {} has no valid alt_code_1 attribute'.format(product_name))
return None
else:
return products['product_fk'].iloc[0]
class ToolBox(GlobalSessionToolBox):
def __init__(self, data_provider, output):
GlobalSessionToolBox.__init__(self, data_provider, output)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.results_df = pd.DataFrame(columns=[
'kpi_name', 'kpi_fk', 'numerator_id', 'numerator_result',
'denominator_id', 'denominator_result', 'result', 'score',
'identifier_result', 'identifier_parent', 'should_enter', 'target'
])
self.templates = {}
self.parse_template()
self.own_manuf_fk = int(
self.data_provider.own_manufacturer.param_value.values[0])
self.scif = self.scif[self.scif['product_type'] != 'Irrelevant']
self.scene_survey_results = self.get_scene_survey_response()
self.session_survey_results = self.get_session_survey_response()
self.custom_entities = self.ps_data_provider.get_custom_entities(1002)
def parse_template(self):
for sheet in SHEETS:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheet_name=sheet)
def main_calculation(self):
relevant_kpi_template = self.templates[KPIS]
store_type = self.store_info['store_type'].iloc[0]
relevant_kpi_template = relevant_kpi_template[
(relevant_kpi_template[STORE_TYPE].isnull()) |
(relevant_kpi_template[STORE_TYPE].str.contains(store_type))]
foundation_kpi_types = [
SOS, PER_SCENE_SOS, ASSORTMENT, AVAILABILITY, BAY_COUNT,
SCENE_SURVEY, SCORING_SURVEY, COOLER_CC, PLANOGRAMA, COMMUNICACION,
ROLLBACKS, PLATFORMAS_SURVEY, PLATFORMAS_SOS, SCENE_IDENTITY,
PLATFORMAS_AVAILABILITY
]
foundation_kpi_template = relevant_kpi_template[
relevant_kpi_template[KPI_TYPE].isin(foundation_kpi_types)]
# platformas_kpi_template = relevant_kpi_template[relevant_kpi_template[KPI_TYPE] == PLATFORMAS_SCORING]
# combo_kpi_template = relevant_kpi_template[relevant_kpi_template[KPI_TYPE] == COMBO]
scoring_kpi_template = relevant_kpi_template[
relevant_kpi_template[KPI_TYPE] == SCORING]
self._calculate_kpis_from_template(foundation_kpi_template)
# self._calculate_kpis_from_template(platformas_kpi_template)
# self._calculate_kpis_from_template(combo_kpi_template)
self._calculate_kpis_from_template(scoring_kpi_template)
self.save_results_to_db()
return
def save_results_to_db(self):
self.results_df.drop(columns=['kpi_name'], inplace=True)
self.results_df.rename(columns={'kpi_fk': 'fk'}, inplace=True)
self.results_df.loc[~self.results_df['identifier_parent'].isnull(),
'should_enter'] = True
# set result to NaN for records that do not have a parent
identifier_results = self.results_df[self.results_df['result'].notna(
)]['identifier_result'].unique().tolist()
self.results_df['result'] = self.results_df.apply(
lambda row: pd.np.nan
if pd.notna(row['identifier_parent']) and row['identifier_parent']
not in identifier_results else row['result'],
axis=1)
# get rid of 'not applicable' results
self.results_df.dropna(subset=['result'], inplace=True)
self.results_df.fillna(0)
results = self.results_df.to_dict('records')
for result in results:
self.write_to_db(**result)
def _calculate_kpis_from_template(self, template_df):
for i, row in template_df.iterrows():
calculation_function = self._get_calculation_function_by_kpi_type(
row[KPI_TYPE])
try:
kpi_row = self.templates[row[KPI_TYPE]][self.templates[
row[KPI_TYPE]][KPI_NAME].str.encode(
'utf-8') == row[KPI_NAME].encode('utf-8')].iloc[0]
except IndexError:
pass
result_data = calculation_function(kpi_row)
if result_data:
if isinstance(result_data, dict):
weight = row['Score']
if weight and pd.notna(weight) and pd.notna(
result_data['result']):
if row[KPI_TYPE] == SCORING and 'score' not in result_data.keys(
):
result_data[
'score'] = weight * result_data['result']
elif row[KPI_TYPE] != SCORING:
result_data[
'score'] = weight * result_data['result']
if 'identifier_parent' not in result_data.keys():
parent_kpi_name = self._get_parent_name_from_kpi_name(
result_data['kpi_name'])
if parent_kpi_name:
result_data['identifier_parent'] = parent_kpi_name
if 'identifier_result' not in result_data.keys():
result_data['identifier_result'] = result_data[
'kpi_name']
if result_data['result'] <= 1:
result_data['result'] = result_data['result'] * 100
self.results_df.loc[len(self.results_df),
result_data.keys()] = result_data
else: # must be a list
for result in result_data:
weight = row['Score']
if weight and pd.notna(weight) and pd.notna(
result['result']):
if row[KPI_TYPE] == SCORING and 'score' not in result.keys(
):
result['score'] = weight * result['result']
elif row[KPI_TYPE] != SCORING:
result['score'] = weight * result['result']
if 'identifier_parent' not in result.keys():
parent_kpi_name = self._get_parent_name_from_kpi_name(
result['kpi_name'])
if parent_kpi_name:
result['identifier_parent'] = parent_kpi_name
if 'identifier_result' not in result.keys():
result['identifier_result'] = result['kpi_name']
if result['result'] <= 1:
if row[PARENT_KPI] != 'Portafolio' and result[
'kpi_name'] != 'Capacidad Fria (Detalle)':
result['result'] = result['result'] * 100
self.results_df.loc[len(self.results_df),
result.keys()] = result
def calculate_scoring(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.common.get_kpi_fk_by_kpi_type(kpi_name)
numerator_id = self.own_manuf_fk
denominator_id = self.store_id
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': numerator_id,
'denominator_id': denominator_id
}
component_kpis = self.does_exist(row, 'Component KPIs')
relevant_results = self.results_df[self.results_df['kpi_name'].isin(
component_kpis)]
passing_results = relevant_results[(relevant_results['result'] != 0) &
(relevant_results['result'].notna())
& (relevant_results['score'] != 0)]
nan_results = relevant_results[relevant_results['result'].isna()]
if len(relevant_results) > 0 and len(relevant_results) == len(
nan_results):
result_dict['result'] = pd.np.nan
elif row['Component aggregation'] == 'one-passed':
if len(relevant_results) > 0 and len(passing_results) > 0:
result_dict['result'] = 1
else:
result_dict['result'] = 0
elif row['Component aggregation'] == 'sum':
if len(relevant_results) > 0:
result_dict['score'] = relevant_results['score'].sum()
if 'result' not in result_dict.keys():
result_dict['result'] = result_dict['score']
else:
result_dict['score'] = 0
if 'result' not in result_dict.keys():
result_dict['result'] = result_dict['score']
elif row['Component aggregation'] == 'all-passed':
if len(relevant_results) == len(passing_results):
result_dict['result'] = 1
else:
result_dict['result'] = 0
return result_dict
def _get_calculation_function_by_kpi_type(self, kpi_type):
if kpi_type == SOS:
return self.calculate_sos
elif kpi_type == BAY_COUNT:
return self.calculate_bay_count
elif kpi_type == PER_SCENE_SOS:
return self.calculate_per_scene_sos
elif kpi_type == AVAILABILITY:
return self.calculate_availability
elif kpi_type == ASSORTMENT:
return self.calculate_assortment
elif kpi_type == SCENE_SURVEY:
return self.calculate_scene_survey
elif kpi_type == SCORING_SURVEY:
return self.calculate_scoring_survey
elif kpi_type == COOLER_CC:
return self.calculate_cooler_cc
elif kpi_type == PLANOGRAMA:
return self.calculate_planograma
elif kpi_type == COMMUNICACION:
return self.calculate_communicacion
elif kpi_type == SCORING:
return self.calculate_scoring
elif kpi_type == ROLLBACKS:
return self.calculate_rollbacks
elif kpi_type == PLATFORMAS_SURVEY:
return self.calculate_platformas_survey
elif kpi_type == PLATFORMAS_SOS:
return self.calculate_platformas_sos
elif kpi_type == SCENE_IDENTITY:
return self.calculate_scene_identity
elif kpi_type == PLATFORMAS_AVAILABILITY:
return self.calculate_platformas_availability
def _get_parent_name_from_kpi_name(self, kpi_name):
template = self.templates[KPIS]
parent_kpi_name = \
template[template[KPI_NAME].str.encode('utf-8') == kpi_name.encode('utf-8')][PARENT_KPI].iloc[0]
if parent_kpi_name and pd.notna(parent_kpi_name):
return parent_kpi_name
else:
return None
def calculate_assortment(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
sku_kpi_name = kpi_name + ' - SKU'
sku_kpi_fk = self.get_kpi_fk_by_kpi_type(sku_kpi_name)
results_list = []
relevant_scif = self.scif.copy()
template_name = self.does_exist(row, TEMPLATE_NAME)
if template_name:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
template_name)]
sub_category = row['Client Subcategory Local Name']
relevant_template = self.templates[PORTAFOLIO_PRODUCTS]
relevant_template = relevant_template[
relevant_template['Client Subcategory Local Name'] == sub_category]
passing_products = 0
for i, sku_row in relevant_template.iterrows():
facings = relevant_scif[
relevant_scif['product_name'] ==
sku_row['Product Local Name']]['facings'].sum()
product_fk = self._get_product_fk_from_name(
sku_row['Product Local Name'])
target = sku_row['Minimum Number of Facings']
score = 1 if facings >= target else 0
result_dict = {
'kpi_name': sku_kpi_name,
'kpi_fk': sku_kpi_fk,
'numerator_id': product_fk,
'denominator_id': self.store_id,
'result': facings,
'score': score,
'target': target,
'identifier_parent': kpi_name
}
results_list.append(result_dict)
if score == 1:
passing_products += 1
result = 1 if passing_products == len(relevant_template) else 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
results_list.append(result_dict)
return results_list
def calculate_availability(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
template_name = row[TEMPLATE_NAME]
totem_products = self.templates[TOTEM]['POSM'].unique().tolist()
relevant_scif = self.scif[self.scif['template_name'].str.encode(
'utf-8') == template_name.encode('utf-8')]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
relevant_scif = relevant_scif[relevant_scif['product_name'].isin(
totem_products)]
unique_products = len(relevant_scif['product_name'].unique().tolist())
target = row['target']
if unique_products >= target:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
return result_dict
def calculate_sos(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
template_name = row[TEMPLATE_NAME]
denominator_scif = self.scif[
(self.scif['template_name'] == template_name)
& (self.scif['product_type'] != 'POS')]
ignored_types = self.does_exist(row, 'ignored_types')
if ignored_types:
denominator_scif = denominator_scif[
~denominator_scif['product_type'].isin(ignored_types)]
if denominator_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
denominator_id = denominator_scif['template_fk'].iloc[0]
denominator_facings = denominator_scif['facings'].sum()
sub_category = self.does_exist(row, 'sub_category')
if sub_category:
numerator_scif = denominator_scif[
denominator_scif['sub_category'].isin(sub_category)]
numerator_id = \
self.all_products[self.all_products['sub_category'].isin(sub_category)]['sub_category_fk'].iloc[0]
manufacturer_name = row['manufacturer_name']
if pd.notna(manufacturer_name):
numerator_scif = denominator_scif[
denominator_scif['manufacturer_name'] == manufacturer_name]
numerator_id = \
self.all_products[self.all_products['manufacturer_name'] == manufacturer_name][
'manufacturer_fk'].iloc[0]
numerator_facings = numerator_scif['facings'].sum()
result = numerator_facings / float(denominator_facings)
min_target, max_target = self._get_target_range(row['target'])
if min_target <= result * 100 <= max_target:
score = 1
else:
score = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': numerator_id,
'denominator_id': denominator_id,
'numerator_result': numerator_facings,
'denominator_result': denominator_facings,
'result': result,
'score': score
}
return result_dict
@staticmethod
def _get_target_range(target_range):
min_target, max_target = [int(x) for x in target_range.split('-')]
return min_target, max_target
def calculate_per_scene_sos(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
template_name = row[TEMPLATE_NAME]
relevant_scif = self.scif[(self.scif['template_name'] == template_name)
& (self.scif['product_type'] != 'POS')]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
denominator_scif = relevant_scif.groupby(
'scene_id', as_index=False)['facings'].sum()
denominator_scif.rename(columns={'facings': 'denominator'},
inplace=True)
numerator_scif = relevant_scif[relevant_scif['manufacturer_name'] ==
'TCCC']
numerator_scif = numerator_scif.groupby(
'scene_id', as_index=False)['facings'].sum()
numerator_scif.rename(columns={'facings': 'numerator'}, inplace=True)
merged_df = pd.merge(denominator_scif,
numerator_scif,
how='left',
on='scene_id').fillna(0)
merged_df['sos'] = (merged_df['numerator'] / merged_df['denominator'])
min_target, max_target = self._get_target_range(row['target'])
merged_df['passing'] = merged_df['sos'].apply(
lambda x: min_target <= x * 100 <= max_target)
failing_scenes = merged_df[~merged_df['passing']]
if failing_scenes.empty:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
return result_dict
def calculate_bay_count(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
detalle_kpi_name = kpi_name + ' (Detalle)'
detalle_kpi_fk = self.get_kpi_fk_by_kpi_type(detalle_kpi_name)
results_list = []
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
scenes = relevant_scif['scene_id'].unique().tolist()
products = relevant_scif['product_fk'].unique().tolist()
bay_mpis = self.matches[(self.matches['scene_fk'].isin(scenes))
& (self.matches['product_fk'].isin(products))]
bay_mpis.drop_duplicates(subset=['bay_number', 'scene_fk'],
inplace=True)
number_of_bays = len(bay_mpis)
target_mapping = self._get_target_mapping(row['targets'])
target = target_mapping.get(
self.store_info['additional_attribute_1'].iloc[0])
if not target:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
if number_of_bays >= target:
result = 1
else:
result = 0
result_dict = {
'kpi_name': detalle_kpi_name,
'kpi_fk': detalle_kpi_fk,
'numerator_id': self.own_manuf_fk,
'numerator_result': number_of_bays,
'denominator_id': self.store_id,
'result': number_of_bays,
'target': target,
'identifier_parent': kpi_name
}
results_list.append(result_dict)
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'numerator_result': number_of_bays,
'denominator_id': self.store_id,
'result': result,
'target': target
}
results_list.append(result_dict)
return results_list
def calculate_planograma(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
brands = self.does_exist(row, 'Brands')
minimum_brands_required = row['minimum number of brands']
relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brands)]
unique_brands = relevant_scif['brand_name'].unique().tolist()
if len(unique_brands) >= minimum_brands_required:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'numerator_result': len(unique_brands),
'denominator_id': self.store_id,
'result': result,
'target': minimum_brands_required
}
return result_dict
def calculate_communicacion(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
product_names_in_scene_type = relevant_scif['product_name'].unique(
).tolist()
pos_option_found = 0
groups = self._get_groups(row, 'POS Option')
for group in groups:
if all(product in product_names_in_scene_type
for product in group):
pos_option_found = 1 # True
break
if pos_option_found:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
return result_dict
@staticmethod
def _get_groups(series, root_string):
groups = []
for column in [
col for col in series.index.tolist() if root_string in col
]:
if series[column] not in ['', pd.np.nan]:
groups.append([x.strip() for x in series[column].split(',')])
return groups
def calculate_cooler_cc(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
brand_kpi_name = kpi_name + ' - Brand'
brand_kpi_fk = self.get_kpi_fk_by_kpi_type(brand_kpi_name)
results_list = []
relevant_question_fks = self.does_exist(row, 'Survey Question PK')
relevant_results = \
self.scene_survey_results[self.scene_survey_results['question_fk'].isin(relevant_question_fks)]
count_cooler_brands = relevant_results[
'selected_option_text'].value_counts()
cooler_brands = relevant_results['selected_option_text'].unique(
).tolist()
if cooler_brands:
for cooler_brand_name in cooler_brands:
if cooler_brand_name and pd.notna(cooler_brand_name):
cooler_brand_fk = self._get_cooler_brand_fk_by_cooler_brand_name(
cooler_brand_name)
result_dict = {
'kpi_name': brand_kpi_name,
'kpi_fk': brand_kpi_fk,
'numerator_id': cooler_brand_fk,
'denominator_id': self.store_id,
'result': count_cooler_brands[cooler_brand_name],
'identifier_parent': kpi_name
}
results_list.append(result_dict)
if len(relevant_results) > 1:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'numerator_result': len(relevant_results),
'denominator_id': self.store_id,
'result': result
}
results_list.append(result_dict)
return results_list
def _get_cooler_brand_fk_by_cooler_brand_name(self, cooler_brand_name):
try:
entity = self.custom_entities[self.custom_entities['name'] ==
cooler_brand_name]
except:
entity = self.custom_entities[
self.custom_entities['name'].str.encode(
'utf-8') == cooler_brand_name.encode('utf-8')]
if entity.empty:
return None
else:
return entity['pk'].iloc[0]
@staticmethod
def _get_target_mapping(column_value):
target_dict = {}
for pair in column_value.split(','):
key, value = pair.split(';')
target_dict.update({key: int(value)})
return target_dict
def calculate_rollbacks(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
sku_kpi_name = kpi_name + ' - SKU'
sku_kpi_fk = self.get_kpi_fk_by_kpi_type(sku_kpi_name)
results_list = []
product_questions = self._get_target_mapping(
row['Survey Product Pairing'])
passing_results = 0
for question_fk, product_fk in product_questions.iteritems():
relevant_results = \
self.session_survey_results[self.session_survey_results['question_fk'] == int(question_fk)]
if len(relevant_results) == 2 and len(
relevant_results['number_value'].unique()) == 1:
result = 1
passing_results += 1
else:
result = 0
result_dict = {
'kpi_name': sku_kpi_name,
'kpi_fk': sku_kpi_fk,
'numerator_id': product_fk,
'denominator_id': self.store_id,
'result': result,
'identifier_parent': kpi_name
}
results_list.append(result_dict)
if passing_results == len(product_questions.keys()):
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
results_list.append(result_dict)
return results_list
def calculate_scene_identity(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
results_list = []
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
scenes = relevant_scif['scene_id'].unique().tolist()
for scene in scenes:
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': scene,
'denominator_result': scene,
'result': 1,
'score': 1,
'identifier_result': scene
}
results_list.append(result_dict)
return results_list
def calculate_platformas_availability(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
results_list = []
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
scenes = relevant_scif['scene_id'].unique().tolist()
for scene in scenes:
product_names_in_scene = \
relevant_scif[relevant_scif['scene_id'] == scene]['product_name'].unique().tolist()
pos_option_found = 0
groups = self._get_groups(row, 'POS Option')
for group in groups:
if all(product in product_names_in_scene for product in group):
pos_option_found = 1 # True
break
if pos_option_found:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': scene,
'denominator_result': scene,
'result': result,
'identifier_parent': scene
}
results_list.append(result_dict)
return results_list
def calculate_platformas_sos(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
results_list = []
template_name = self.does_exist(row, TEMPLATE_NAME)
relevant_scif = self.scif[self.scif['template_name'].isin(
template_name)]
if relevant_scif.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
denominator_scif = relevant_scif.groupby(
'scene_id', as_index=False)['facings'].sum()
denominator_scif.rename(columns={'facings': 'denominator'},
inplace=True)
numerator_scif = relevant_scif[relevant_scif['manufacturer_name'] ==
'TCCC']
numerator_scif = numerator_scif.groupby(
'scene_id', as_index=False)['facings'].sum()
numerator_scif.rename(columns={'facings': 'numerator'}, inplace=True)
merged_df = pd.merge(denominator_scif,
numerator_scif,
how='left',
on='scene_id').fillna(0)
merged_df['sos'] = (merged_df['numerator'] / merged_df['denominator'])
min_target, max_target = self._get_target_range(row['target'])
merged_df['passing'] = merged_df['sos'].apply(
lambda x: min_target <= x * 100 <= max_target)
for i, row in merged_df.iterrows():
scene_id = row['scene_id']
numerator_result = row['numerator']
denominator_result = row['denominator']
result = row['sos']
score = 1 if row['passing'] else 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'numerator_result': numerator_result,
'denominator_id': scene_id,
'denominator_result': denominator_result,
'result': result,
'identifier_parent': scene_id,
'score': score
}
results_list.append(result_dict)
return results_list
def calculate_platformas_survey(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
survey_kpi_name = kpi_name + ' - Survey'
survey_kpi_fk = self.get_kpi_fk_by_kpi_type(survey_kpi_name)
results_list = []
relevant_question_fks = self.does_exist(row, 'Survey Question PK')
relevant_results = \
self.scene_survey_results[self.scene_survey_results['question_fk'].isin(relevant_question_fks)]
relevant_results.drop_duplicates(subset=['scene_id'], inplace=True)
for i, row in relevant_results.iterrows():
numerator_fk = self._get_cooler_brand_fk_by_cooler_brand_name(
row['selected_option_text'])
scene_id = row['scene_id']
if numerator_fk:
result_dict = {
'kpi_name': survey_kpi_name,
'kpi_fk': survey_kpi_fk,
'numerator_id': numerator_fk,
'denominator_id': scene_id,
'denominator_result': scene_id,
'result': 1,
'identifier_parent': str(scene_id) + " " + kpi_name
}
results_list.append(result_dict)
for i, row in relevant_results.iterrows():
numerator_fk = self._get_cooler_brand_fk_by_cooler_brand_name(
row['selected_option_text'])
scene_id = row['scene_id']
if numerator_fk:
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': numerator_fk,
'denominator_id': scene_id,
'denominator_result': scene_id,
'result': 1,
'identifier_parent': scene_id,
'identifier_result': str(scene_id) + " " + kpi_name
}
results_list.append(result_dict)
return results_list
def calculate_scene_survey(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
relevant_question_fks = self.does_exist(row, 'Survey Question PK')
required_answer = row['Required Answer']
relevant_results = \
self.scene_survey_results[self.scene_survey_results['question_fk'].isin(relevant_question_fks)]
if relevant_results.empty:
return {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'result': pd.np.nan
}
failing_results = relevant_results[
relevant_results['selected_option_text'] != required_answer]
if failing_results.empty:
result = 1
else:
result = 0
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result
}
return result_dict
def calculate_scoring_survey(self, row):
kpi_name = row[KPI_NAME]
kpi_fk = self.get_kpi_fk_by_kpi_type(kpi_name)
relevant_question_fks = self.does_exist(row, 'Survey Question PK')
relevant_results = \
self.session_survey_results[self.session_survey_results['question_fk'].isin(relevant_question_fks)]
if relevant_results.empty:
result = 0
score = 0
else:
score = len(relevant_results['selected_option_text'].iloc[0])
result = 1
result_dict = {
'kpi_name': kpi_name,
'kpi_fk': kpi_fk,
'numerator_id': self.own_manuf_fk,
'denominator_id': self.store_id,
'result': result,
'score': score
}
return result_dict
def _get_product_fk_from_name(self, product_name):
return self.all_products[self.all_products['product_name'] ==
product_name]['product_fk'].iloc[0]
def get_scene_survey_response(self):
query = """SELECT session_uid,question_fk,selected_option_text, sce.pk as 'scene_id'
FROM probedata.scene_survey_response res
LEFT JOIN probedata.scene sce ON res.scene_fk = sce.pk
WHERE session_uid = '{}';""".format(self.session_uid)
scene_survey_response = pd.read_sql_query(
query, self.ps_data_provider.rds_conn.db)
return scene_survey_response
def get_session_survey_response(self):
query = """SELECT session_uid,question_fk,selected_option_text,number_value
FROM probedata.survey_response res
WHERE session_uid = '{}';""".format(self.session_uid)
session_survey_response = pd.read_sql_query(
query, self.ps_data_provider.rds_conn.db)
return session_survey_response
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys(
) and kpi_line[column_name] != "" and pd.notna(kpi_line[column_name]):
cell = kpi_line[column_name]
if type(cell) in [int, float, pd.np.int64]:
return [cell]
elif type(cell) in [unicode, str]:
return [x.strip() for x in cell.split(",")]
return None
class DMIToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output, common):
self.output = output
self.data_provider = data_provider
self.common = common
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.manufacturer_fk = self.products['manufacturer_fk'][
self.products['manufacturer_name'] ==
'MONDELEZ INTERNATIONAL INC'].iloc[0]
self.store_assortment = pd.DataFrame()
self.assortment = Assortment(self.data_provider, common=self.common)
self.store_number = self.get_store_number()
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.custom_entities = self.ps_data_provider.get_custom_entities(
Const.PPG_ENTITY_TYPE_FK)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
self.calculate_display_count()
self.calculate_assortment()
self.calculate_FD_compliance()
self.calculate_scripted_compliance()
self.calculate_assortment_time()
def calculate_assortment_time(self):
if not self.store_assortment.empty:
for selected_kpi in [Const.AD_BREAK_START, Const.AD_BREAK_STOP]:
kpi_fk = self.common.get_kpi_fk_by_kpi_name(selected_kpi)
start_date = self.store_assortment.start_date.min()
if selected_kpi == Const.AD_BREAK_START:
unix = int((start_date -
datetime.datetime(1970, 1, 1)).total_seconds())
else:
end_date_timestamp = start_date + pd.DateOffset(days=6)
unix = int((end_date_timestamp -
datetime.datetime(1970, 1, 1)).total_seconds())
self.common.write_to_db_result(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
numerator_result=unix,
denominator_id=self.store_id,
denominator_result=unix,
result=unix,
score=0)
def calculate_display_count(self):
kpi_fk = self.common.get_kpi_fk_by_kpi_name(Const.DISPLAY_COUNT_kpi)
count_of_scenes = len(self.scif['scene_fk'].unique().tolist())
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
numerator_result=count_of_scenes,
denominator_id=self.store_id,
denominator_result=1,
result=count_of_scenes,
score=count_of_scenes)
def calculate_FD_compliance(self):
score = 0
compliance_status = Const.NON_COMPLIANT_FK
kpi_fk = self.common.get_kpi_fk_by_kpi_name(Const.FD_COMPLIANCE_KPI)
if self.store_assortment.empty:
# LOG ERROR
pass
else:
filtered_assortment = self.store_assortment[
self.store_assortment['kpi_fk_lvl3'] == kpi_fk]
if not filtered_assortment.empty:
for i, row in filtered_assortment.iterrows():
if Const.PPG_COLUMN_NAME in filtered_assortment.columns.to_list(
):
ppg = row[Const.PPG_COLUMN_NAME]
if not pd.isnull(ppg):
product_fk = self.custom_entities['pk'][
self.custom_entities['name'] == ppg].iloc[0]
filtered_scif_count = len(
self.scif[self.scif['PPG'] == ppg])
if filtered_scif_count > 0:
score = 1
compliance_status = Const.COMPLIANT_FK
else:
score = 0
compliance_status = Const.NON_COMPLIANT_FK
if Const.SUB_PPG_COLUMN_NAME in filtered_assortment.columns.to_list(
):
sub_ppg = row[Const.SUB_PPG_COLUMN_NAME]
if not pd.isnull(sub_ppg):
product_fk = \
self.custom_entities['pk'][self.custom_entities['name'] == sub_ppg].iloc[0]
filtered_scif_count = len(
self.scif[self.scif['Sub PPG'] == sub_ppg])
if filtered_scif_count > 0:
score = 1
compliance_status = Const.COMPLIANT_FK
else:
score = 0
# product_fk = 0
compliance_status = Const.NON_COMPLIANT_FK
self.common.write_to_db_result(
fk=kpi_fk,
numerator_id=product_fk,
numerator_result=score,
denominator_id=self.store_id,
denominator_result=1,
result=compliance_status,
score=score)
def calculate_scripted_compliance(self):
score = 0
kpi_fk = self.common.get_kpi_fk_by_kpi_name(
Const.SCRIPTED_COMPLIANCE_KPI)
if self.store_assortment.empty:
# LOG ERROR
pass
else:
filtered_assortment = self.store_assortment[
self.store_assortment['kpi_fk_lvl3'] == kpi_fk]
if not filtered_assortment.empty:
for i, row in filtered_assortment.iterrows():
if Const.PPG_COLUMN_NAME in filtered_assortment.columns.to_list(
):
ppg = row[Const.PPG_COLUMN_NAME]
if not pd.isnull(ppg):
product_fk = self.custom_entities['pk'][
self.custom_entities['name'] == ppg].iloc[0]
filtered_scif_count = len(
self.scif[self.scif['PPG'] == ppg])
if filtered_scif_count > 0:
score = 1
compliance_status = Const.COMPLIANT_FK
else:
score = 0
compliance_status = Const.NON_COMPLIANT_FK
if Const.SUB_PPG_COLUMN_NAME in filtered_assortment.columns.to_list(
):
sub_ppg = row[Const.SUB_PPG_COLUMN_NAME]
if not pd.isnull(sub_ppg):
product_fk = \
self.custom_entities['pk'][self.custom_entities['name'] == sub_ppg].iloc[0]
filtered_scif_count = len(
self.scif[self.scif['Sub PPG'] == sub_ppg])
if filtered_scif_count > 0:
score = 1
compliance_status = Const.COMPLIANT_FK
else:
score = 0
# product_fk = 0
compliance_status = Const.NON_COMPLIANT_FK
self.common.write_to_db_result(
fk=kpi_fk,
numerator_id=product_fk,
numerator_result=score,
denominator_id=self.store_id,
denominator_result=1,
result=compliance_status,
score=score)
def calculate_assortment(self):
self.store_assortment = self.assortment.store_assortment
self.store_assortment.reset_index(inplace=True)
if self.store_assortment.empty:
return
else:
ppg_list = [
ast.literal_eval(x) for x in
self.store_assortment['additional_attributes'].to_list() if x
]
df_assortment = pd.DataFrame(ppg_list)
merged_assortment = self.store_assortment.join(df_assortment)
self.store_assortment = merged_assortment
# self.store_assortment['additional_attributes'] = self.store_assortment['additional_attributes'].apply(json.loads)
# self.store_assortment[['ppg', 'value']] = json_normalize(self.store_assortment['additional_attributes'])
def get_store_assortment(self, policy_filter=None):
"""
fetch all active assortment for store type in current date
:param policy_filter: str, for filtering policy (if we know its name)
:return:
"""
# TODO parse store policy and to where 1.date 2. store attreibute to match policy
# Log.info("get_store_assortment query_v2")
policy_str = ''
if policy_filter:
policy_str = " and pol.policy_name = '{}'".format(policy_filter)
query = """select p.pk as product_fk, p.name, p.ean_code,
atp.assortment_additional_attribute as additional_attributes, atp.start_date,
pol.policy, pol.policy_type, ass.*
from (select group3.*, ass3.kpi_fk as kpi_fk_lvl1
from
(SELECT
group2.assortment_fk, group2.assortment_name, group2.kpi_fk as kpi_fk_lvl3, coalesce(group2.store_policy_group_fk,
(select store_policy_group_fk from pservice.assortment where pk = group2.assortment_group_fk)) as store_policy_group_fk,
group2.assortment_group_fk, coalesce(group1.kpi_fk,
(select kpi_fk from pservice.assortment where pk = group2.assortment_group_fk)) as kpi_fk_lvl2,
group1.target, group1.start_date as group_target_date, group1.assortment_group_fk as assortment_super_group_fk, group1.super_group_target
FROM
(select atag1.*, ass1.assortment_name, ass1.kpi_fk, ass1.store_policy_group_fk, ass1.target as super_group_target
from pservice.assortment_to_assortment_group as atag1 ,
pservice.assortment as ass1
where atag1.assortment_fk = ass1.pk and atag1.end_date is null) as group1
right join
(select atag2.*, ass2.assortment_name, ass2.kpi_fk, ass2.store_policy_group_fk
from pservice.assortment_to_assortment_group as atag2,
pservice.assortment as ass2
where atag2.assortment_fk = ass2.pk and atag2.end_date is null) as group2
on group1.assortment_fk = group2.assortment_group_fk) as group3
left join
pservice.assortment as ass3
on ass3.pk = group3.assortment_super_group_fk) as ass,
pservice.assortment_to_product as atp,
static_new.product as p,
pservice.policy as pol
where atp.product_fk = p.pk and
atp.assortment_fk = ass.assortment_fk and
pol.pk = ass.store_policy_group_fk
and ((atp.end_date is null and '{0}' >= atp.start_date) or
('{0}' between atp.start_date and atp.end_date)){1};
""".format(self.visit_date, policy_str)
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()
headers = [
'product_fk', 'name', 'ean_code', 'additional_attributes',
'start_date', 'policy', 'policy_type', 'assortment_fk',
'assortment_name', 'kpi_fk_lvl3', 'store_policy_group_fk',
'assortment_group_fk', 'kpi_fk_lvl2', 'target',
'group_target_date', 'assortment_super_group_fk',
'super_group_target', 'kpi_fk_lvl1'
]
df = pd.DataFrame(list(res), columns=headers)
df = df.drop_duplicates(subset=[
'product_fk', 'assortment_fk', 'assortment_group_fk',
'assortment_super_group_fk'
],
keep='last')
# df['additional_atttributes'] =
return df
def get_store_number(self):
query = """select store_number_1 from probedata.session ps
left join static.stores store on store.pk = ps.store_fk
where session_uid = '{}'
""".format(self.session_uid, )
cur = self.rds_conn.db.cursor()
cur.execute(query)
res = cur.fetchall()
df = pd.DataFrame(list(res))
return int(df.iloc[0])
class DIAGEODISPUSToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.dirname(os.path.realpath(__file__))), 'Data',
'kpi_template.xlsx')
DISPLAY_CASES = 'Display Cases'
DISPLAY_BOTTLES = 'Display Bottles'
CASE_PACK = 'CASE PACK'
SKU_PERFORMANCE = 'sku_performance'
BRAND_PERFORMANCE = 'brand_performance'
FAMILY_BRAND = 1000
def __init__(self, data_provider, output, common):
self.output = output
self.data_provider = data_provider
self.common = common
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.new_kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.template = pd.read_excel(self.TEMPLATE_PATH)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.manual_collection_number = self.ps_data_provider.get_manual_collection_number(
)
self.custom_entities = self.ps_data_provider.get_custom_entities(
self.FAMILY_BRAND)
def get_kpi_fk_by_type(self, kpi_type):
assert isinstance(
kpi_type,
(unicode, basestring)), "name is not a string: %r" % kpi_type
try:
return self.new_kpi_static_data[self.new_kpi_static_data['type'] ==
kpi_type]['pk'].values[0]
except IndexError:
Log.info(
"Kpi name: {} is not equal to any kpi name in static table".
format(kpi_type))
return None
def competition_group_fk(self, brand):
try:
return self.custom_entities[self.custom_entities['name'] ==
brand]['pk'].values[0]
except IndexError:
Log.info(
"Family Brand name: {} is not equal to any family brand name in static table"
.format(brand))
return None
def main_calculation(self):
"""
This function calculates the KPI results.
"""
relevant_brands = self.template['Competition Group'].values
# relevant_products = []
# for row in self.template.iterrows():
# # x = row[1].values.tostr()
# filters = dict([(str(k), str(v)) for k, v in dict(row[1]).items()])
# tmp = self.all_products[self.genaral_toolbox(self.data_provider).get_filter_condition(
# self.all_products, **filters)]['product_fk'].drop_duplicates()
#
# if not tmp.empty:
# relevant_products.append(list(tmp.values))
# # relevant_products.append()
relevant_products = self.all_products[
self.all_products['Competition Group'].isin(
relevant_brands)]['product_fk'].drop_duplicates().values
# Log.info("Relevant products type: {}".format(type(relevant_products[0])))
# Log.info("manual_collection_number type: {}".format(type(self.manual_collection_number['product_fk'][0])))
relevant_products = self.manual_collection_number[(
self.manual_collection_number['product_fk'].isin(relevant_products)
)]['product_fk'].drop_duplicates().values
sku_results = pd.DataFrame(columns=['sku', 'brand', 'num_of_cases'])
sku_results['sku'] = relevant_products
for product in relevant_products:
try:
display_cases = self.manual_collection_number[
(self.manual_collection_number['product_fk'] == product)
& (self.manual_collection_number['name'] == self.
DISPLAY_CASES)]['value'].drop_duplicates().values[0]
display_bottles = self.manual_collection_number[
(self.manual_collection_number['product_fk'] == product)
& (self.manual_collection_number['name'] == self.
DISPLAY_BOTTLES)]['value'].drop_duplicates().values[0]
case_pack = self.all_products[
self.all_products['product_fk'] == product][
self.CASE_PACK].drop_duplicates().values[0]
sku_results.loc[
sku_results['sku'] == product,
'brand'] = self.all_products[
self.all_products['product_fk'] == product][
'Competition Group'].drop_duplicates().values[0]
sku_results.loc[sku_results['sku'] == product,
'num_of_cases'] = display_cases + np.divide(
float(display_bottles), float(case_pack))
except Exception as err:
Log.info('{} for product {}'.format(err, product))
continue
sku_kpi_fk = self.get_kpi_fk_by_type(self.SKU_PERFORMANCE)
for row in sku_results.itertuples():
self.common.write_to_db_result_new_tables(
fk=sku_kpi_fk,
numerator_id=row.sku,
numerator_result=row.num_of_cases,
result=row.num_of_cases,
score=row.num_of_cases)
brand_results = pd.DataFrame(columns=['brand', 'num_of_cases'])
brand_results['brand'] = sku_results['brand'].dropna().drop_duplicates(
).values
for brand in sku_results['brand'].drop_duplicates().values:
brand_results.loc[
brand_results['brand'] == brand, 'num_of_cases'] = sku_results[
sku_results['brand'] == brand]['num_of_cases'].sum()
brand_kpi_fk = self.get_kpi_fk_by_type(self.BRAND_PERFORMANCE)
for row in brand_results.itertuples():
competition_group_fk = self.competition_group_fk(row.brand)
self.common.write_to_db_result_new_tables(
fk=brand_kpi_fk,
numerator_id=competition_group_fk,
numerator_result=row.num_of_cases,
result=row.num_of_cases,
score=row.num_of_cases)
return