class CCAAUToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.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.templates = self.data_provider[Data.TEMPLATES]
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.template = self.data_provider.all_templates # templates
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.toolbox = GENERALToolBox(data_provider)
kpi_path = os.path.dirname(os.path.realpath(__file__))
base_file = os.path.basename(kpi_path)
self.exclude_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Exclude")
self.Include_filters = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="Include")
self.bay_count_kpi = pd.read_excel(os.path.join(
kpi_path[:-len(base_file)], 'Data', 'template.xlsx'),
sheetname="BayCountKPI")
def main_calculation(self):
"""
This function calculates the KPI results.
"""
self.calculate_sos()
self.calculate_bay_kpi()
self.common.commit_results_data_to_new_tables()
def calculate_sos(self):
"""
This function filtering Data frame - "scene item facts" by the parameters in the template.
Sending the filtered data frames to linear Sos calculation and facing Sos calculation
Writing the results to the new tables in DB
"""
facing_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'FACINGS_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
linear_kpi_fk = self.kpi_static_data[
self.kpi_static_data['client_name'] ==
'LINEAR_SOS_SCENE_TYPE_BY_MANUFACTURER']['pk'].iloc[0]
den_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Denominator')]
den_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Denominator')]
num_facing_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Facing')
& (self.exclude_filters['apply on'] == 'Numerator')]
num_linear_exclude_template = self.exclude_filters[
(self.exclude_filters['KPI'] == 'Share of Shelf by Linear')
& (self.exclude_filters['apply on'] == 'Numerator')]
scene_templates = self.scif['template_fk'].unique().tolist()
scene_manufactures = self.scif['manufacturer_fk'].unique().tolist()
# exclude filters denominator
den_general_facing_filters = self.create_dict_filters(
den_facing_exclude_template, self.EXCLUDE_FILTER)
den_general_linear_filters = self.create_dict_filters(
den_linear_exclude_template, self.EXCLUDE_FILTER)
# exclude filters numerator
num_general_facing_filters = self.create_dict_filters(
num_facing_exclude_template, self.EXCLUDE_FILTER)
num_general_linear_filters = self.create_dict_filters(
num_linear_exclude_template, self.EXCLUDE_FILTER)
df_num_fac = self.filter_2_cond(self.scif, num_facing_exclude_template)
df_num_lin = self.filter_2_cond(self.scif, num_linear_exclude_template)
df_den_lin = self.filter_2_cond(self.scif, den_facing_exclude_template)
df_den_fac = self.filter_2_cond(self.scif, den_linear_exclude_template)
for template in scene_templates:
for manufacture in scene_manufactures:
sos_filters = {
"template_fk": (template, self.INCLUDE_FILTER),
"manufacturer_fk": (manufacture, self.INCLUDE_FILTER)
}
tem_filters = {"template_fk": (template, self.INCLUDE_FILTER)}
dict_num_facing = dict(
(k, v) for d in [sos_filters, num_general_facing_filters]
for k, v in d.items())
numerator_facings = self.calculate_share_space(
df_num_fac, dict_num_facing)[0]
dict_num_linear = dict(
(k, v) for d in [sos_filters, num_general_linear_filters]
for k, v in d.items())
numerator_linear = self.calculate_share_space(
df_num_lin, dict_num_linear)[1]
dict_den_facing = dict(
(k, v) for d in [tem_filters, den_general_facing_filters]
for k, v in d.items())
denominator_facings = self.calculate_share_space(
df_den_fac, dict_den_facing)[0]
dict_den_linear = dict(
(k, v) for d in [tem_filters, den_general_linear_filters]
for k, v in d.items())
denominator_linear = self.calculate_share_space(
df_den_lin, dict_den_linear)[1]
score_facing = 0 if denominator_facings == 0 else (
numerator_facings / denominator_facings) * 100
score_linear = 0 if denominator_linear == 0 else (
numerator_linear / denominator_linear) * 100
self.common.write_to_db_result_new_tables(
facing_kpi_fk, manufacture, numerator_facings,
score_facing, template, denominator_facings, score_facing)
self.common.write_to_db_result_new_tables(
linear_kpi_fk, manufacture, numerator_linear, score_linear,
template, denominator_linear, score_linear)
def create_dict_filters(self, template, param):
"""
:param template : Template of the desired filtering to data frame
:param param : exclude /include
:return: Dictionary of filters and parameter : exclude / include by demeaned
"""
filters_dict = {}
template_without_second = template[template['Param 2'].isnull()]
for row in template_without_second.iterrows():
filters_dict[row[1]['Param 1']] = (row[1]['Value 1'].split(','),
param)
return filters_dict
def filter_2_cond(self, data_frame, template):
"""
:param template: Template of the desired filtering
:param data_frame : Data frame
:return: data frame filtered by entries in the template with 2 conditions
"""
template_without_second = template[template['Param 2'].notnull()]
if template_without_second is not None:
for row in template_without_second.iterrows():
data_frame = data_frame.loc[
(~data_frame[row[1]['Param 1']].isin(row[1]['Value 1'].
split(','))) |
(~data_frame[row[1]['Param 2']].isin(row[1]['Value 2'].
split(',')))]
return data_frame
def calculate_share_space(self, data_frame, filters):
"""
:param filters: These are the parameters which the data frame is filtered by.
:param data_frame : relevant scene item facts data frame (filtered )
:return: The total number of facings and the shelf width (in mm) according to the filters.
"""
filtered_scif = data_frame[self.toolbox.get_filter_condition(
data_frame, **filters)]
sum_of_facings = filtered_scif['facings'].sum()
space_length = filtered_scif['gross_len_split_stack'].sum()
return sum_of_facings, space_length
def calculate_bay_kpi(self):
bay_kpi_sheet = self.bay_count_kpi
kpi = self.kpi_static_data.loc[self.kpi_static_data['type'] ==
BAY_COUNT_KPI]
if kpi.empty:
Log.info("CCAAU Calculate KPI Name:{} not found in DB".format(
BAY_COUNT_KPI))
else:
Log.info("CCAAU Calculate KPI Name:{} found in DB".format(
BAY_COUNT_KPI))
bay_kpi_row = bay_kpi_sheet[bay_kpi_sheet['KPI Name'] ==
BAY_COUNT_KPI]
if not bay_kpi_row.empty:
scene_types_to_consider = bay_kpi_row['Scene Type'].iloc[0]
if scene_types_to_consider == '*':
# Consider all scene types
scene_types_to_consider = 'all'
else:
scene_types_to_consider = [
x.strip() for x in scene_types_to_consider.split(',')
]
mpis_with_scene = self.match_product_in_scene.merge(
self.scene_info, how='left', on='scene_fk')
mpis_with_scene_and_template = mpis_with_scene.merge(
self.templates, how='left', on='template_fk')
if scene_types_to_consider != 'all':
mpis_with_scene_and_template = mpis_with_scene_and_template[
mpis_with_scene_and_template['template_name'].isin(
scene_types_to_consider)]
mpis_template_group = mpis_with_scene_and_template.groupby(
'template_fk')
for template_fk, template_data in mpis_template_group:
Log.info("Running for template ID {templ_id}".format(
templ_id=template_fk, ))
total_bays_for_scene_type = 0
scene_group = template_data.groupby('scene_fk')
for scene_fk, scene_data in scene_group:
Log.info(
"KPI Name:{kpi} bay count is {bay_c} for scene ID {scene_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=int(scene_data['bay_number'].max()),
scene_id=scene_fk,
))
total_bays_for_scene_type += int(
scene_data['bay_number'].max())
Log.info(
"KPI Name:{kpi} total bay count is {bay_c} for template ID {templ_id}"
.format(
kpi=BAY_COUNT_KPI,
bay_c=total_bays_for_scene_type,
templ_id=template_fk,
))
self.common.write_to_db_result_new_tables(
fk=int(kpi['pk'].iloc[0]),
numerator_id=int(template_fk),
numerator_result=total_bays_for_scene_type,
denominator_id=int(self.store_id),
denominator_result=total_bays_for_scene_type,
result=total_bays_for_scene_type,
)
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 CCPHLToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_data = pd.read_excel(TEMPLATE_PATH, 'KPIs').fillna('')
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_static_data = self.kpi_static_data[self.kpi_static_data['kpi_family_fk'] == CCPHLConsts.CUSTOM_KPIS]
self.kpi_results_queries = []
self.mapping_param = {"manufacturer": "manufacturer_name"}
self.mapping_entity = {"manufacturer": "manufacturer_fk", "store": "store_id",
"scene_type": "template_fk", "brand": "brand_fk", "product": "product_fk"}
def main_calculation(self):
"""
This function calculates the KPI results.
"""
for group in self.template_data[CCPHLConsts.KPI_GROUP].unique():
kpis = self.template_data[self.template_data[CCPHLConsts.KPI_GROUP] == group]
if kpis.empty:
print("KPI Group:{} is not valid".format(group))
continue
for row_num, kpi in kpis.iterrows():
if kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_PURITY:
self.calculate_self_purity_util(kpi)
elif kpi[CCPHLConsts.KPI_GROUP] == CCPHLConsts.SHELF_CUTIL:
self.calculate_self_purity_util(kpi)
else:
continue
self.common.commit_results_data_to_new_tables()
def calculate_self_purity_util(self, kpi):
df_scene_data = self.scif
if df_scene_data.empty:
return
scene_types = [x.strip() for x in kpi[CCPHLConsts.SCENE_TYPE].split(',')]
filter_param_name_1 = kpi['filter_param_name_1'].strip()
if len(filter_param_name_1) != 0:
filter_param_value_1 = kpi['filter_param_value_1'].strip()
else:
filter_param_value_1 = ""
if str(kpi[CCPHLConsts.EXCLUDE_EMPTY]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.EMPTY]
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.PRODUCT_FK] != CCPHLConsts.GENERAL_EMPTY]
if str(kpi[CCPHLConsts.EXCLUDE_IRRELEVANT]).upper() == 'Y':
df_scene_data = df_scene_data[df_scene_data[CCPHLConsts.IRRELEVANT] != CCPHLConsts.IRRELEVANT]
df_kpi_level_2_fk = self.kpi_static_data[self.kpi_static_data['type'] == kpi['kpi_name']]
if df_kpi_level_2_fk.empty:
kpi_level_2_fk = 0
else:
kpi_level_2_fk = df_kpi_level_2_fk.iloc[0]['pk']
df_scene_data = df_scene_data[df_scene_data['template_name'].isin(scene_types)]
group_list = []
for idx in range(1, 5):
entity = kpi['entity' + str(idx)].strip()
if entity == 'N/A' or len(entity) == 0:
continue
else:
entity = self.mapping_entity[kpi['entity' + str(idx)].strip()]
group_list.append(entity)
denominator = 0
if group_list[0] == 'store_id':
total_facings = df_scene_data['facings'].sum()
denominator = float(total_facings)
if len(filter_param_value_1) != 0:
df_scene_data2 = df_scene_data[df_scene_data[self.mapping_param[filter_param_name_1]]==filter_param_value_1]
else:
df_scene_data2 = df_scene_data
filter_columns = list(group_list)
filter_columns.append('facings')
df_scene_data2 = df_scene_data2[filter_columns]
df_purity = pd.DataFrame(df_scene_data2.groupby(group_list).sum().reset_index())
store_zero_results = str(kpi[CCPHLConsts.STORE_ZERO_RESULTS]).strip().upper()
for row_num, row_data in df_purity.iterrows():
numerator = row_data['facings']
if group_list[0] == 'template_fk':
df_scene_count = df_scene_data[df_scene_data['template_fk'] == row_data['template_fk']]
if df_scene_count.empty:
total_facings = 0
else:
total_facings = df_scene_count['facings'].sum()
denominator = float(total_facings)
try:
result = round(float(numerator) / float(denominator), 4)
except ZeroDivisionError:
print("Error: {}".format(ZeroDivisionError.message))
continue
if kpi_level_2_fk != 0:
if result == 0:
if store_zero_results == 'Y':
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list)-1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
else:
self.common.write_to_db_result_new_tables(fk=kpi_level_2_fk,
numerator_id=row_data[group_list[len(group_list) - 1]],
denominator_id=row_data[group_list[0]],
numerator_result=numerator,
denominator_result=denominator,
result=result,
score=result)
class HEINEKENTWToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
DIST_STORE_LVL1 = 1014
OOS_STORE_LVL1 = 1015
DIST_STORE_LVL2 = 1016
OOS_STORE_LVL2 = 1017
DIST_CATEGORY_LVL1 = 1018
OOS_CATEGORY_LVL1 = 1019
DIST_CATEGORY_LVL2 = 1020
OOS_CATEGORY_LVL2 = 1021
DISTRIBUTION = 4
OOS = 5
MANUFACTURER_FK = 175 # heinken manfucturer
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.assortment = Assortment(self.data_provider, self.output)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
self.category_assortment_calculation(lvl3_result)
self.store_assortment_calculation(lvl3_result)
self.common.commit_results_data_to_new_tables()
# self.common.commit_results_data_to_new_tables()
def category_assortment_calculation(self, lvl3_result):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
"""
if not lvl3_result.empty:
# cat_df = self.scif[['product_fk', 'category_fk']]
cat_df = self.all_products[['product_fk', 'category_fk']]
lvl3_with_cat = lvl3_result.merge(cat_df,
on='product_fk',
how='left')
lvl3_with_cat = lvl3_with_cat[
lvl3_with_cat['category_fk'].notnull()]
for result in lvl3_with_cat.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=result.category_fk,
denominator_result=1,
score=score,
score_after_actions=score)
category_list = lvl3_with_cat['category_fk'].unique()
for cat in category_list:
lvl3_result_cat = lvl3_with_cat[lvl3_with_cat["category_fk"] ==
cat]
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result_cat)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_CATEGORY_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=cat,
denominator_result=denominator_res,
result=1 - res,
score=(1 - res),
score_after_actions=1 - res)
return
def store_assortment_calculation(self, lvl3_result):
"""
This function calculates the KPI results.
"""
for result in lvl3_result.itertuples():
if result.in_store == 1:
score = self.DISTRIBUTION
else:
score = self.OOS
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score)
if score == self.OOS:
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL1,
numerator_id=result.product_fk,
numerator_result=score,
result=score,
denominator_id=self.store_id,
denominator_result=1,
score=score,
score_after_actions=score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
if not pd.isnull(result.target) and not pd.isnull(
result.group_target_date
) and result.group_target_date <= self.assortment.current_date:
denominator_res = result.target
res = np.divide(float(result.passes), float(denominator_res))
# Distrubtion
self.common.write_to_db_result_new_tables(
fk=self.DIST_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
denominator_id=self.store_id,
numerator_result=result.passes,
denominator_result=denominator_res,
result=res,
score=res,
score_after_actions=res)
# OOS
self.common.write_to_db_result_new_tables(
fk=self.OOS_STORE_LVL2,
numerator_id=self.MANUFACTURER_FK,
numerator_result=denominator_res - result.passes,
denominator_id=self.store_id,
denominator_result=denominator_res,
result=1 - res,
score=1 - res,
score_after_actions=1 - res)
return
class PERFETTICNToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_new_kpi_static_data()
self.kpi_results_queries = []
self.store_info = self.data_provider[Data.STORE_INFO]
self.assortment = Assortment(self.data_provider, self.output)
self.template = self.data_provider.all_templates
def main_calculation(self, *args, **kwargs):
self.display_count()
self.assortment_calculation()
self.common.commit_results_data_to_new_tables()
def display_count(self):
"""This function calculates how many displays find from types secondary shelf
"""
num_brands = {}
display_info = self.scif['template_fk']
display_fks = display_info.unique()
template_seco = self.template[
self.template['included_in_secondary_shelf_report'] ==
'Y']['template_fk']
display_fks = list(
filter(lambda x: x in template_seco.values, display_fks))
count_fk = self.kpi_static_data[self.kpi_static_data['client_name'] ==
'COUNT OF DISPLAY']['pk'].iloc[0]
for value in display_fks:
num_brands[value] = display_info[display_info == value].count()
score = num_brands[value]
self.common.write_to_db_result_new_tables(count_fk, value, None,
score, score, score,
score)
return
def assortment_calculation(self):
"""
This function calculates 3 levels of assortment :
level3 is assortment SKU
level2 is assortment groups
level1 how many groups passed out of all
"""
lvl3_result = self.assortment.calculate_lvl3_assortment()
for result in lvl3_result.itertuples():
score = result.in_store
if score >= 1:
score = 100
self.common.write_to_db_result_new_tables(
result.kpi_fk_lvl3, result.product_fk, result.in_store, score,
result.assortment_group_fk, 1, score)
if not lvl3_result.empty:
lvl2_result = self.assortment.calculate_lvl2_assortment(
lvl3_result)
for result in lvl2_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes), float(denominator_res))
if result.passes >= 1:
score = 100
else:
score = 0
self.common.write_to_db_result_new_tables(
result.kpi_fk_lvl2, result.assortment_group_fk,
result.passes, (res * 100),
result.assortment_super_group_fk, denominator_res, score)
if not lvl2_result.empty:
lvl1_result = self.assortment.calculate_lvl1_assortment(
lvl2_result)
for result in lvl1_result.itertuples():
denominator_res = result.total
res = np.divide(float(result.passes),
float(denominator_res))
if res >= 0:
score = 100
else:
score = 0
self.common.write_to_db_result_new_tables(
fk=result.kpi_fk_lvl1,
numerator_id=result.assortment_super_group_fk,
numerator_result=result.passes,
denominator_result=denominator_res,
result=(res * 100),
score=score)
return
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()