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 ALTRIAUSToolBox:
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.match_product_in_probe_state_reporting = self.ps_data_provider.get_match_product_in_probe_state_reporting(
)
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.empty_data_template = parse_template(TEMPLATE_PATH, "Empty")
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)
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(
)
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()
self.calculate_empty_brand()
self.calculate_facings_by_scene_type()
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 _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_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_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_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))]
smart_attribute_data = \
self.adp.get_match_product_in_probe_state_values(relevant_mpis['probe_match_fk'].unique().tolist())
relevant_mpis = pd.merge(relevant_mpis,
smart_attribute_data,
on='probe_match_fk',
how='left')
relevant_mpis['match_product_in_probe_state_fk'].fillna(0,
inplace=True)
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_empty_brand(self):
product_type = ['Empty']
for i, row in self.empty_data_template.iterrows():
kpi_name = row['KPI Level 2 Name']
param1 = row['Param1']
value1 = row['Value1']
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(kpi_name)
if value1.find(',') != -1:
value1 = value1.split(',')
for value in value1:
relevant_scif = self.scif[(self.scif[param1] == value) & (
self.scif['product_type'].isin(product_type))]
if relevant_scif.empty:
pass
else:
result = empty_facings = relevant_scif['facings'].sum()
brand_fk = relevant_scif['brand_fk'].iloc[0]
self.common_v2.write_to_db_result(
kpi_fk,
numerator_id=brand_fk,
numerator_result=empty_facings,
denominator_id=self.store_id,
result=result,
score=0)
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):
longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1]
category_fk = self.get_category_fk_by_name(category)
# ======== old method for synchronizing fixture width to header data ========
# 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:
# correction_factor = 1 if category == 'Smokeless' else 2
# width = round(len(longest_shelf) + correction_factor / float(
# self.facings_to_feet_template[category + ' Facings'].iloc[0]))
# ======== old method for synchronizing fixture width to header data ========
width = len(longest_shelf) / float(
self.facings_to_feet_template[category + ' Facings'].iloc[0])
self.mark_tags_in_explorer(longest_shelf['probe_match_fk'].tolist(),
'Longest Shelf - ' + category)
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 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
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 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 CigarrosToolBox(GlobalSessionToolBox):
def __init__(self, data_provider, output, common):
GlobalSessionToolBox.__init__(self, data_provider, output, common)
self.scene_types = self.scif['template_name'].unique().tolist()
self.gz = self.store_info['additional_attribute_4'].iloc[0]
self.city = self.store_info['address_city'].iloc[0]
self.relevant_targets = self._get_relevant_external_targets(
kpi_operation_type='acomodo_cigarros')
self.invasion_targets = self._get_relevant_external_targets(
kpi_operation_type='invasion')
self._determine_target_product_fks()
self.leading_products = self._get_leading_products_from_scif()
self.scene_realograms = self._calculate_scene_realograms()
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(
)
def main_calculation(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CIGARROS)
parent_fk = self.get_parent_fk(Consts.CIGARROS)
kpi_max_points = self.get_kpi_points(Consts.CIGARROS)
score = 0
score += self.calculate_mercadeo()
score += self.calculate_surtido()
result = score / float(kpi_max_points)
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result * 100,
score=score,
weight=kpi_max_points,
target=kpi_max_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def calculate_surtido(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.SURTIDO)
parent_fk = self.get_parent_fk(Consts.SURTIDO)
max_kpi_points = self.get_kpi_points(Consts.SURTIDO)
weight = self.get_kpi_weight(Consts.SURTIDO)
score = 0
# score += self.calculate_calificador()
score += self.calculate_prioritario()
# score += self.calculate_opcional()
result = score / float(max_kpi_points)
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def calculate_calificador(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CALIFICADOR)
parent_fk = self.get_parent_fk(Consts.CALIFICADOR)
max_kpi_points = self.get_kpi_points(Consts.CALIFICADOR)
weight = self.get_kpi_weight(Consts.CALIFICADOR)
relevant_template = self.relevant_targets[
(self.relevant_targets['Nombre de Tarea'].isin(self.scene_types))
& (self.relevant_targets['TIPO DE SKU'] == 'C')]
relevant_template = pd.merge(relevant_template,
self.scif[['product_fk', 'facings']],
how='left',
left_on='target_product_fk',
right_on='product_fk')
relevant_template['facings'].fillna(0, inplace=True)
relevant_template['in_session'] = relevant_template['facings'] > 0
self._calculate_calificador_sku(relevant_template)
if relevant_template.empty:
result = 0
else:
result = relevant_template['in_session'].sum() / float(
len(relevant_template))
score = result * max_kpi_points
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=relevant_template['in_session'].sum(),
denominator_result=len(relevant_template),
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def _calculate_calificador_sku(self, relevant_template):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.CALIFICADOR_SKU)
parent_fk = self.get_parent_fk(Consts.CALIFICADOR_SKU)
for sku in relevant_template.itertuples():
result = 1 if sku.in_session else 0
self.write_to_db(fk=kpi_fk,
numerator_id=sku.target_product_fk,
denominator_id=self.store_id,
result=result,
identifier_parent=parent_fk,
identifier_result=kpi_fk,
should_enter=True)
def calculate_prioritario(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.PRIORITARIO)
parent_fk = self.get_parent_fk(Consts.PRIORITARIO)
max_kpi_points = self.get_kpi_points(Consts.PRIORITARIO)
weight = self.get_kpi_weight(Consts.PRIORITARIO)
relevant_template = self.relevant_targets[
(self.relevant_targets['Nombre de Tarea'].isin(self.scene_types))
& (self.relevant_targets['TIPO DE SKU'] == 'P')]
relevant_template = pd.merge(relevant_template,
self.scif[['product_fk', 'facings']],
how='left',
left_on='target_product_fk',
right_on='product_fk')
relevant_template['facings'].fillna(0, inplace=True)
relevant_template['in_session'] = relevant_template['facings'] > 0
self._calculate_prioritario_sku(relevant_template)
if relevant_template.empty:
result = 0
else:
result = relevant_template['in_session'].sum() / float(
len(relevant_template))
score = result * max_kpi_points
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=relevant_template['in_session'].sum(),
denominator_result=len(relevant_template),
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def _calculate_prioritario_sku(self, relevant_template):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.PRIORITARIO_SKU)
parent_fk = self.get_parent_fk(Consts.PRIORITARIO_SKU)
for sku in relevant_template.itertuples():
result = 1 if sku.in_session else 0
self.write_to_db(fk=kpi_fk,
numerator_id=sku.target_product_fk,
denominator_id=self.store_id,
result=result,
identifier_parent=parent_fk,
identifier_result=kpi_fk,
should_enter=True)
def calculate_opcional(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.OPCIONAL)
parent_fk = self.get_parent_fk(Consts.OPCIONAL)
max_kpi_points = self.get_kpi_points(Consts.OPCIONAL)
weight = self.get_kpi_weight(Consts.OPCIONAL)
relevant_template = self.relevant_targets[
(self.relevant_targets['Nombre de Tarea'].isin(self.scene_types))
& (self.relevant_targets['TIPO DE SKU'] == 'O')]
relevant_template = pd.merge(relevant_template,
self.scif[['product_fk', 'facings']],
how='left',
left_on='target_product_fk',
right_on='product_fk')
relevant_template['facings'].fillna(0, inplace=True)
relevant_template['in_session'] = relevant_template['facings'] > 0
self._calculate_opcional_sku(relevant_template)
if relevant_template.empty:
result = 0
else:
result = relevant_template['in_session'].sum() / float(
len(relevant_template))
score = result * max_kpi_points
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=relevant_template['in_session'].sum(),
denominator_result=len(relevant_template),
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def _calculate_opcional_sku(self, relevant_template):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.OPCIONAL_SKU)
parent_fk = self.get_parent_fk(Consts.OPCIONAL_SKU)
for sku in relevant_template.itertuples():
result = 1 if sku.in_session else 0
self.write_to_db(fk=kpi_fk,
numerator_id=sku.target_product_fk,
denominator_id=self.store_id,
result=result,
identifier_parent=parent_fk,
identifier_result=kpi_fk,
should_enter=True)
def calculate_mercadeo(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.MERCADEO)
parent_fk = self.get_parent_fk(Consts.MERCADEO)
max_kpi_points = self.get_kpi_points(Consts.MERCADEO)
weight = self.get_kpi_weight(Consts.MERCADEO)
score = 0
score += self.calculate_acomodo()
score += self.calculate_frentes()
score += self.calculate_huecos()
result = score / float(max_kpi_points)
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def calculate_huecos(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.HUECOS)
parent_fk = self.get_parent_fk(Consts.HUECOS)
max_kpi_points = self.get_kpi_points(Consts.HUECOS)
weight = self.get_kpi_weight(Consts.HUECOS)
relevant_scene_types = self.relevant_targets[
Consts.TEMPLATE_SCENE_TYPE].unique().tolist()
relevant_scif = self.scif[self.scif['template_name'].isin(
relevant_scene_types)]
if relevant_scif.empty:
result = 0
else:
empty_scif = relevant_scif[relevant_scif['product_type'] ==
'Empty']
result = 1 if empty_scif.empty else 0
score = result * max_kpi_points
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_result=kpi_fk,
identifier_parent=parent_fk,
should_enter=True)
return score
def calculate_frentes(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.FRENTES)
parent_fk = self.get_parent_fk(Consts.FRENTES)
max_kpi_points = self.get_kpi_points(Consts.FRENTES)
weight = self.get_kpi_weight(Consts.FRENTES)
valid_scene_types = self.relevant_targets[
Consts.TEMPLATE_SCENE_TYPE].unique().tolist()
relevant_scif = self.scif[self.scif['template_name'].isin(
valid_scene_types)]
relevant_scif.groupby('product_fk', as_index=False)['facings'].sum()
relevant_target_skus = \
self.relevant_targets[self.relevant_targets['Nombre de Tarea'].isin(
relevant_scif['template_name'].unique().tolist())]
relevant_target_skus = relevant_target_skus.groupby(
'target_product_fk', as_index=False)['Frentes'].sum()
relevant_target_skus.rename(columns={'Frentes': 'target'},
inplace=True)
relevant_target_skus = pd.merge(relevant_target_skus,
relevant_scif,
how='left',
left_on='target_product_fk',
right_on='product_fk').fillna(0)
relevant_target_skus['meets_target'] = relevant_target_skus[
'facings'] >= relevant_target_skus['target']
relevant_target_skus['meets_target'] = relevant_target_skus[
'meets_target'].apply(lambda x: 1 if x else 0)
count_of_passing_skus = relevant_target_skus['meets_target'].sum()
self._calculate_frentes_sku(relevant_target_skus)
if len(relevant_target_skus) == 0:
result = 0
else:
result = count_of_passing_skus / float(len(relevant_target_skus))
score = result * max_kpi_points
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=count_of_passing_skus,
denominator_result=len(relevant_target_skus),
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_parent=parent_fk,
identifier_result=kpi_fk,
should_enter=True)
return score
def _calculate_frentes_sku(self, relevant_target_skus):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.FRENTES_SKU)
parent_fk = self.get_parent_fk(Consts.FRENTES_SKU)
for sku_row in relevant_target_skus.itertuples():
self.write_to_db(fk=kpi_fk,
numerator_id=sku_row.target_product_fk,
denominator_id=self.store_id,
numerator_result=sku_row.facings,
result=sku_row.meets_target,
target=sku_row.target,
identifier_parent=parent_fk,
should_enter=True)
def calculate_acomodo(self):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.ACOMODO)
parent_fk = self.get_parent_fk(Consts.ACOMODO)
max_kpi_points = self.get_kpi_points(Consts.ACOMODO)
weight = self.get_kpi_weight(Consts.ACOMODO)
scene_result = 0
count = 0
for scene_id, scene_realogram in self.scene_realograms.items():
count += 1
scene_result += self.calculate_acomodo_scene(scene_realogram)
if count == 0:
result = 0
else:
result = scene_result / float(count)
score = result * max_kpi_points
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=result,
denominator_result=count,
result=result * 100,
score=score,
weight=weight,
target=max_kpi_points,
identifier_parent=parent_fk,
identifier_result=kpi_fk,
should_enter=True)
return score
def calculate_acomodo_scene(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.ACOMODO_SCENE)
parent_fk = self.get_parent_fk(Consts.ACOMODO_SCENE)
identifier_result = self.get_dictionary(
kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk)
result = self.calculate_colcado_correct(scene_realogram)
self.calculate_colcado_incorrect(scene_realogram)
self.calculate_extra(scene_realogram)
self.write_to_db(fk=kpi_fk,
numerator_id=scene_realogram.template_fk,
denominator_id=self.store_id,
context_id=scene_realogram.scene_fk,
result=result,
score=result * 100,
identifier_parent=parent_fk,
identifier_result=identifier_result,
should_enter=True)
return result
def calculate_colcado_correct(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_CORRECT)
parent_fk = self.get_parent_fk(Consts.COLCADO_CORRECT)
identifier_result = self.get_dictionary(
kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
self._calculate_colcado_correct_sku(scene_realogram)
number_of_positions_in_planogram = float(
scene_realogram.number_of_positions_in_planogram)
self.mark_tags_in_explorer(
scene_realogram.correctly_placed_tags['probe_match_fk'].dropna().
unique().tolist(), Consts.COLCADO_CORRECT)
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=len(scene_realogram.correctly_placed_tags),
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=len(scene_realogram.correctly_placed_tags) /
number_of_positions_in_planogram * 100,
score=len(scene_realogram.correctly_placed_tags) /
number_of_positions_in_planogram,
context_id=scene_realogram.scene_fk,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True)
return len(scene_realogram.correctly_placed_tags
) / number_of_positions_in_planogram
def _calculate_colcado_correct_sku(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_CORRECT_SKU)
parent_fk = self.get_parent_fk(Consts.COLCADO_CORRECT_SKU)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
correctly_placed_skus = scene_realogram.calculate_correctly_placed_skus(
)
for sku_row in correctly_placed_skus.itertuples():
self.write_to_db(
fk=kpi_fk,
numerator_id=sku_row.target_product_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=sku_row.facings,
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=sku_row.facings,
context_id=scene_realogram.scene_fk,
identifier_parent=identifier_parent,
should_enter=True)
def calculate_colcado_incorrect(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_INCORRECT)
parent_fk = self.get_parent_fk(Consts.COLCADO_INCORRECT)
identifier_result = self.get_dictionary(
kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
self._calculate_colcado_incorrect_sku(scene_realogram)
number_of_positions_in_planogram = float(
scene_realogram.number_of_positions_in_planogram)
self.mark_tags_in_explorer(
scene_realogram.incorrectly_placed_tags['probe_match_fk'].dropna().
unique().tolist(), Consts.COLCADO_INCORRECT)
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=len(scene_realogram.incorrectly_placed_tags),
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=len(scene_realogram.incorrectly_placed_tags) /
number_of_positions_in_planogram * 100,
score=len(scene_realogram.incorrectly_placed_tags) /
number_of_positions_in_planogram,
context_id=scene_realogram.scene_fk,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True)
return
def _calculate_colcado_incorrect_sku(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.COLCADO_INCORRECT_SKU)
parent_fk = self.get_parent_fk(Consts.COLCADO_INCORRECT_SKU)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
incorrectly_placed_skus = scene_realogram.calculate_incorrectly_placed_skus(
)
for sku_row in incorrectly_placed_skus.itertuples():
self.write_to_db(
fk=kpi_fk,
numerator_id=sku_row.target_product_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=sku_row.facings,
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=sku_row.facings,
context_id=scene_realogram.scene_fk,
identifier_parent=identifier_parent,
should_enter=True)
def calculate_extra(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA)
parent_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA)
identifier_result = self.get_dictionary(
kpi_fk=kpi_fk, scene_fk=scene_realogram.scene_fk)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
self._calculate_extra_sku(scene_realogram)
number_of_positions_in_planogram = float(
scene_realogram.number_of_positions_in_planogram)
self.mark_tags_in_explorer(
scene_realogram.extra_tags['probe_match_fk'].dropna().unique().
tolist(), Consts.EXTRA)
self.write_to_db(
fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=len(scene_realogram.extra_tags),
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=len(scene_realogram.extra_tags) /
number_of_positions_in_planogram * 100,
score=len(scene_realogram.extra_tags) /
number_of_positions_in_planogram,
context_id=scene_realogram.scene_fk,
identifier_result=identifier_result,
identifier_parent=identifier_parent,
should_enter=True)
return
def _calculate_extra_sku(self, scene_realogram):
kpi_fk = self.get_kpi_fk_by_kpi_type(Consts.EXTRA_SKU)
parent_fk = self.get_parent_fk(Consts.EXTRA_SKU)
identifier_parent = self.get_dictionary(
kpi_fk=parent_fk, scene_fk=scene_realogram.scene_fk)
extra_skus = scene_realogram.calculate_extra_skus()
for sku_row in extra_skus.itertuples():
self.write_to_db(
fk=kpi_fk,
numerator_id=sku_row.target_product_fk,
denominator_id=scene_realogram.template_fk,
numerator_result=sku_row.facings,
denominator_result=scene_realogram.number_of_skus_in_planogram,
result=sku_row.facings,
context_id=scene_realogram.scene_fk,
identifier_parent=identifier_parent,
should_enter=True)
def _calculate_scene_realograms(self):
scene_realograms = {}
for scene_type in self.relevant_targets[
Consts.TEMPLATE_SCENE_TYPE].unique().tolist():
if scene_type in self.scif['template_name'].unique().tolist():
template_fk = self._get_template_fk(scene_type)
for scene_fk in self.scif[
self.scif['template_name'] ==
scene_type]['scene_id'].unique().tolist():
scene_mpis = self.matches[self.matches['scene_fk'] ==
scene_fk]
scene_mpis = self._convert_mpis_product_fks_to_leads(
scene_mpis)
scene_realogram = HeinekenRealogram(
scene_mpis, scene_type, template_fk,
self.relevant_targets)
scene_realograms[scene_fk] = scene_realogram
return scene_realograms
def _get_relevant_external_targets(self, kpi_operation_type=None):
if kpi_operation_type == 'acomodo_cigarros':
template_df = pd.read_excel(Consts.TEMPLATE_PATH,
sheetname='Acomodo_cigarros',
header=1)
template_df = template_df[
(template_df['GZ'].str.encode('utf-8') == self.gz.encode(
'utf-8')) & (template_df['Ciudad'].str.encode('utf-8') ==
self.city.encode('utf-8'))]
template_df['Puertas'] = template_df['Puertas'].fillna(1)
template_df = template_df[[
'GZ', 'Ciudad', 'Nombre de Tarea', 'Puertas', 'EAN Code',
'Product Name', 'TIPO DE SKU', 'x', 'y', 'Frentes'
]]
return template_df
elif kpi_operation_type == 'invasion':
template_df = pd.read_excel(Consts.TEMPLATE_PATH,
sheetname='Invasion',
header=1)
template_df = template_df[(
template_df['NOMBRE DE TAREA'].str.contains('Cerveza'))]
return template_df.dropna(subset=['Manufacturer', 'Category'])
else:
return pd.DataFrame()
def _determine_target_product_fks(self):
target_products = self.all_products[['product_fk', 'product_ean_code']]
target_products.rename(columns={'product_fk': 'target_product_fk'},
inplace=True)
target_products.dropna(inplace=True)
target_products['product_ean_code'] = target_products[
'product_ean_code'].astype('int')
self.relevant_targets['EAN Code'] = self.relevant_targets[
'EAN Code'].astype('int')
self.relevant_targets = pd.merge(self.relevant_targets,
target_products,
how='left',
left_on='EAN Code',
right_on='product_ean_code')
self.relevant_targets.dropna(subset=['target_product_fk'],
inplace=True)
def _get_leading_products_from_scif(self):
leading_mappings = self.scif[['product_fk', 'substitution_product_fk'
]].drop_duplicates()
leading_mappings.loc[leading_mappings['substitution_product_fk'].isna(), 'substitution_product_fk'] = \
leading_mappings.loc[leading_mappings['substitution_product_fk'].isna(), 'product_fk']
return leading_mappings
@staticmethod
def _convert_mpis_product_fks_to_leads(mpis):
mpis['leading_product_fk'] = mpis['substitution_product_fk']
return mpis
def get_parent_fk(self, kpi_name):
parent_kpi_name = Consts.KPIS_HIERARCHY[kpi_name]
parent_fk = self.get_kpi_fk_by_kpi_type(parent_kpi_name)
return parent_fk
@staticmethod
def get_kpi_weight(kpi_name):
weight = Consts.KPI_WEIGHTS[kpi_name]
return weight
@staticmethod
def get_kpi_points(kpi_name):
weight = Consts.KPI_POINTS[kpi_name]
return weight
def _get_template_fk(self, template_name):
return self.templates[self.templates['template_name'] ==
template_name]['template_fk'].iloc[0]
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.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.match_product_in_probe_state_values = pd.concat([
match_product_in_probe_state_values_old,
match_product_in_probe_state_values_new
])
return
class SceneToolBox:
def __init__(self, data_provider, common):
self.data_provider = data_provider
self.common = common
self.project_name = self.data_provider.project_name
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.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_INFO]['store_fk'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.kpi_results_queries = []
self.df = pd.DataFrame()
self.tools = GENERALToolBox(data_provider)
self.osd_rules_sheet = pd.read_excel(PATH, Const.OSD_RULES).fillna("")
self.store_info = self.data_provider[Data.STORE_INFO]
self.psdataprovider = PsDataProvider(self.data_provider)
self.match_product_in_probe_state_reporting = self.psdataprovider.get_match_product_in_probe_state_reporting()
def main_calculation(self):
"""
This function calculates the KPI results.
"""
if self.match_product_in_scene.empty or self.products.empty:
return
df = pd.merge(self.match_product_in_scene, self.products, on="product_fk", how="left")
distinct_session_fk = self.scif[['scene_fk',
'template_name', 'template_fk']].drop_duplicates()
df = pd.merge(df, distinct_session_fk, on="scene_fk", how="left")
self.calculate_osd(df)
def calculate_osd(self, df):
if df.empty:
return
scene_type = df['template_name'].values[0]
const_scene_df = df.copy()
row = self.find_row_osd(scene_type)
if row.empty:
return
results_list = []
# filter df include OSD when needed
shelfs_to_include = row[Const.OSD_NUMBER_OF_SHELVES].values[0]
if shelfs_to_include != "":
shelfs_to_include = int(shelfs_to_include)
result_df = df[df['shelf_number_from_bottom'] >= shelfs_to_include]
if not result_df.empty:
results_list.append(result_df)
df = df[df['shelf_number_from_bottom'] < shelfs_to_include]
# if no osd rule is applied.
if (row[Const.HAS_OSD].values[0] == Const.NO) and (row[Const.HAS_HOTSPOT].values[0] == Const.NO):
return
# filter df to have only shelves with given ean code
if row[Const.HAS_OSD].values[0] == Const.YES:
products_to_filter = row[Const.POSM_EAN_CODE].values[0].split(",")
if products_to_filter != "":
products_to_filter = [item.strip() for item in products_to_filter]
products_df = df[df['product_ean_code'].isin(products_to_filter)][[
'scene_fk', 'shelf_number']]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = const_scene_df[((const_scene_df['scene_fk'] == p['scene_fk']) &
(const_scene_df['shelf_number'] == p['shelf_number']))]
results_list.append(scene_df)
if row[Const.HAS_HOTSPOT].values[0] == Const.YES:
products_to_filter = row[Const.POSM_EAN_CODE_HOTSPOT].values[0].split(",")
if products_to_filter != "":
products_to_filter = [item.strip() for item in products_to_filter]
products_df = const_scene_df[const_scene_df['product_ean_code'].isin(products_to_filter)][['scene_fk',
'bay_number',
'shelf_number']]
products_df = products_df.drop_duplicates()
if not products_df.empty:
for index, p in products_df.iterrows():
scene_df = const_scene_df[((const_scene_df['scene_fk'] == p['scene_fk']) &
(const_scene_df['bay_number'] == p['bay_number']) &
(const_scene_df['shelf_number'] == p['shelf_number']))]
results_list.append(scene_df)
if len(results_list) == 0:
return
kpi_fk = self.common.get_kpi_fk_by_kpi_name(Const.OSD_KPI)
if kpi_fk is None:
Log.warning("There is no matching Kpi fk for kpi name: " + Const.OSD_KPI)
return
try:
template_fk = self.scene_info['template_fk'].values[0]
except:
template_fk = -1
self.common.write_to_db_result(fk=kpi_fk, numerator_id=self.store_id, result=1, by_scene=True,
denominator_id=template_fk)
if len(results_list) > 1:
df = pd.concat(results_list).drop_duplicates()
else:
df = results_list[0]
osd_pk = self.match_product_in_probe_state_reporting[self.match_product_in_probe_state_reporting['name']
== 'OSD']['match_product_in_probe_state_reporting_fk'].values[0]
self.common.match_product_in_probe_state_values[Const.MATCH_PRODUCT_IN_PROBE_FK] = \
df['probe_match_fk'].drop_duplicates()
self.common.match_product_in_probe_state_values[Const.MATCH_PRODUCT_IN_PROBE_STATE_REPORTING_FK] = osd_pk
def find_row_osd(self, s):
rows = self.osd_rules_sheet[self.osd_rules_sheet[Const.SCENE_TYPE].str.encode(
"utf8") == s.encode("utf8")]
row = rows[rows[Const.RETAILER] == self.store_info['retailer_name'].values[0]]
return row
