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 create_position_graphs(self, scene_id=None):
"""
This function creates a facings Graph for each scene of the given session.
"""
calc_start_time = datetime.datetime.utcnow()
if scene_id:
scenes = [scene_id]
else:
scenes = self.match_product_in_scene[
MatchesConsts.SCENE_FK].unique()
for scene in scenes:
matches = self.match_product_in_scene[self.match_product_in_scene[
MatchesConsts.SCENE_FK] == scene]
matches['distance_from_end_of_shelf'] = matches[
MatchesConsts.N_SHELF_ITEMS] - matches[
MatchesConsts.FACING_SEQUENCE_NUMBER]
scene_graph = igraph.Graph(directed=True)
edges = []
for f in xrange(len(matches)):
facing = matches.iloc[f]
facing_name = str(facing[MatchesConsts.SCENE_MATCH_FK])
scene_graph.add_vertex(facing_name)
# adding attributes to vertex
vertex = scene_graph.vs.find(facing_name)
for attribute in Consts.ATTRIBUTES_TO_SAVE:
vertex[attribute] = facing[attribute]
surrounding_products = self.get_surrounding_products(
facing, matches)
for direction in surrounding_products.keys():
for pk in surrounding_products[direction]:
edge = dict(source=facing_name,
target=str(pk),
direction=direction)
edges.append(edge)
for edge in edges:
scene_graph.add_edge(**edge)
self.position_graphs[scene] = scene_graph
calc_finish_time = datetime.datetime.utcnow()
Log.info('Creation of position graphs for scenes {} took {}'.format(
scenes, calc_finish_time - calc_start_time))
def create_position_graphs(self):
"""
This function creates a facings Graph for each scene of the given session.
"""
calc_start_time = datetime.datetime.utcnow()
graphs = {}
attributes_to_save = [
'product_name', 'product_type', 'product_ean_code', 'brand_name',
'category', 'manufacturer_name'
]
for scene in self.match_product_in_scene['scene_fk'].unique():
matches = self.match_product_in_scene[
self.match_product_in_scene['scene_fk'] == scene]
scene_graph = igraph.Graph(directed=True)
edges = []
for f in xrange(len(matches)):
facing = matches.iloc[f]
facing_name = str(facing[VERTEX_FK_FIELD])
scene_graph.add_vertex(facing_name)
# adding attributes to vertex
vertex = scene_graph.vs.find(facing_name)
for attribute in attributes_to_save:
vertex[attribute] = facing[attribute]
surrounding_products = self.get_surrounding_products(
facing, matches)
for direction in surrounding_products.keys():
for pk in surrounding_products[direction]:
edge = (facing_name, str(pk), direction)
edges.append(edge)
for edge in edges:
source, target, direction = edge
scene_graph.add_edge(source=source, target=target)
edge_id = scene_graph.get_eid(source, target)
scene_graph.es[edge_id]['direction'] = direction
graphs[scene] = scene_graph
calc_finish_time = datetime.datetime.utcnow()
Log.info(
'Creation of position graphs took {}'.format(calc_finish_time -
calc_start_time))
return graphs
def get_previous_session(self):
query = """
SELECT
pk, session_uid
FROM
probedata.session
WHERE
store_fk = (SELECT
store_fk
FROM
probedata.session
WHERE
session_uid = '{}')
ORDER BY visit_date DESC
LIMIT 1 , 1;
""".format(self.session_uid)
previous_session = pd.read_sql_query(query, self.rds_conn.db)
Log.info("Getting previous session for {c}: => {p}".format(
c=self.session_uid, p=previous_session))
return previous_session
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
return
self.tool_box.tools.update_templates()
set_names = ['Product Blocking', 'Linear Share of Shelf',
'OSA', 'Pallet Presence', 'Share of Assortment']
for kpi_set_name in set_names:
self.tool_box.main_calculation(set_name=kpi_set_name)
self.tool_box.main_calculation(set_name='Shelf Level')
self.tool_box.main_calculation(set_name='Linear Share of Shelf vs. Target')
self.tool_box.main_calculation(set_name='Shelf Impact Score')
self.tool_box.save_custom_scene_item_facts_results()
self.tool_box.save_linear_length_results()
Log.info('Downloading templates took {}'.format(self.tool_box.download_time))
self.tool_box.commit_results_data()
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
if self.tool_box.store_type:
successful = CCTH_SANDSuccessfulSessions(self.tool_box.rds_conn, self.session_uid)
success_status = successful.update_session()
if success_status == 1:
Log.info('Session is surveyed - hence calculating KPIs')
self.tool_box.calculate_red_score()
self.tool_box.calculate_report()
self.tool_box.write_gaps_to_db()
else:
Log.info('Session is not surveyed - hence skipping KPI calculations')
self.tool_box.commit_results_data()
else:
Log.warning('Store type is empty for this session')
def calculate_product_unique_position_on_shelf(self, scene_id, shelf_number, **filters):
"""
:param scene_id: The scene ID.
:param shelf_number: The number of shelf in question (from top).
:param filters: These are the parameters which the unique position is checked for.
:return: The position of the first SKU (from the given filters) to appear in the specific shelf.
"""
shelf_matches = self.match_product_in_scene[(self.match_product_in_scene['scene_fk'] == scene_id) &
(self.match_product_in_scene['shelf_number'] == shelf_number)]
if shelf_matches[self.get_filter_condition(shelf_matches, **filters)].empty:
Log.info("Products of '{}' are not tagged in shelf number {}".format(filters, shelf_number))
return None
shelf_matches = shelf_matches.sort_values(by=['bay_number', 'facing_sequence_number'])
shelf_matches = shelf_matches.drop_duplicates(subset=['product_ean_code'])
products = shelf_matches[self.get_filter_condition(shelf_matches, **filters)]['product_ean_code'].tolist()
for i in xrange(len(shelf_matches)):
match = shelf_matches.iloc[i]
if match['product_ean_code'] in products:
return i + 1
return None
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
calc_start_time = datetime.datetime.utcnow()
Log.info('Calculation Started at {}'.format(calc_start_time))
if not self.tool_box.scif.empty:
self.tool_box.main_calculation()
# Saving a dummy result into Level1 (KPI set)
for set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique():
category = self.tool_box.kpi_static_data[self.tool_box.kpi_static_data['kpi_set_fk'] ==
set_fk]['kpi_set_name'].values[0]
if self.tool_box.validate_category(category):
self.tool_box.write_to_db_result(fk=set_fk, score=100, level=self.tool_box.LEVEL1)
else:
Log.warning('Scene item facts is empty for this session')
self.tool_box.commit_results_data()
calc_finish_time = datetime.datetime.utcnow()
Log.info('Calculation time took {}'.format(calc_finish_time - calc_start_time))
def oos_sku_level(self, lvl_3_result):
"""
This function create df sql results, results of oos on sku level based assortment
:param lvl_3_result: df of assortment results in sku level
:return: df of sql results for oos assortment sku level
"""
# filter distrubution kpis
# oos_results = lvl_3_result[lvl_3_result['result'] == 0]
oos_results = lvl_3_result.copy()
if oos_results.empty:
return oos_results
oos_result = self.kpi_result_value(0)
oos_results = oos_results.loc[oos_results['result'] == oos_result]
if oos_results.empty:
return oos_results
oos_sku_kpi = self.get_kpi_fk('Live OOS - SKU')
oos_results.loc[:, 'kpi_level_2_fk'] = oos_sku_kpi
oos_results = self.filter_df_by_col(oos_results, self.SKU_LEVEL)
Log.info('oos_results_sku level Done')
return oos_results
def run_project_calculations(self):
self.timer.start()
Log.info('INTEG15 is running')
INTEG15EmptySpaceKpiGenerator(self.data_provider, self.output, self.data_provider.project_name).main_function()
self.timer.stop('PngCNEmptyCalculations.run_project_calculations')
# if __name__ == '__main__':
# LoggerInitializer.init('Png-cn calculations')
# Config.init()
# # project_name = 'pngcn-prod'
# project_name = 'integ15'
# data_provider = ACEDataProvider(project_name)
# session = 'aa0ea0fc-4ed8-4852-a1ee-af513723dd89'
# # for session in sessions:
# data_provider.load_session_data(session)
# output = Output()
# SessionVanillaCalculations(data_provider, output, project_name).run_project_calculations()
# INTEG15PngCNEmptyCalculations(data_provider, output).run_project_calculations()
# data_provider.export_session_data(output)
def create_position_graphs(self, scene_id=None):
"""
This function creates a facings Graph for each scene of the given session.
"""
calc_start_time = datetime.datetime.utcnow()
if scene_id:
scenes = [scene_id]
else:
scenes = self.match_product_in_scene['scene_fk'].unique()
for scene in scenes:
matches = self.match_product_in_scene[
(self.match_product_in_scene['scene_fk'] == scene)
& (self.match_product_in_scene['stacking_layer'] == 1)]
matches['distance_from_end_of_shelf'] = matches[
'n_shelf_items'] - matches['facing_sequence_number']
scene_graph = igraph.Graph(directed=True)
edges = []
for f in xrange(len(matches)):
facing = matches.iloc[f]
facing_name = str(facing[VERTEX_FK_FIELD])
scene_graph.add_vertex(facing_name)
# adding attributes to vertex
vertex = scene_graph.vs.find(facing_name)
for attribute in self.ATTRIBUTES_TO_SAVE:
vertex[attribute] = facing[attribute]
surrounding_products = self.get_surrounding_products(
facing, matches)
for direction in surrounding_products.keys():
for pk in surrounding_products[direction]:
edge = dict(source=facing_name,
target=str(pk),
direction=direction)
edges.append(edge)
for edge in edges:
scene_graph.add_edge(**edge)
self.position_graphs[scene] = scene_graph
calc_finish_time = datetime.datetime.utcnow()
Log.info('Creation of position graphs for scenes {} took {}'.format(
scenes, calc_finish_time - calc_start_time))
def main_function(self):
"""
This is the main KPI calculation function.
It calculates the score for every KPI set and saves it to the DB.
"""
if self.tool_box.scif.empty:
Log.warning('Scene item facts is empty for this session')
set_names = self.tool_box.kpi_static_data['kpi_set_name'].unique(
).tolist()
# self.tool_box.tools.update_templates()
# set_names = ['Footcare - Refill', 'Footcare - Tights', 'Footcare - Insoles', 'Footcare - Gadgets',
# 'Aircare - Refill', 'Aircare - Candles & Waxmelts', 'Aircare - Gadgets', 'Aircare - Spray',
# 'ADW - Brand Group', 'ADW - Products', 'SWB - Products', 'SWB - Brand Group', 'MPC - Sagrotan',
# 'MPC - Bath, Kitchen & Liquid', 'MPC - Wipes', 'MPC - Cillit', 'Displays', 'Gondola Ends',
# 'Second Placement', 'Location']
# set_names = ['Footcare', 'Aircare']
for kpi_set_name in set_names:
self.tool_box.main_calculation(set_name=kpi_set_name)
Log.info('Downloading templates took {}'.format(
self.tool_box.download_time))
self.tool_box.commit_results_data()
def calculate_energy_drinks(self, shelf_occupation_dict,
product_list_field):
"""
this function calculates score for energy drinks category
"""
score = 0
for shelf_number in range(
1,
shelf_occupation_dict.get(NUM_OF_SHELVES) + 1):
for bay_number in range(1,
shelf_occupation_dict.get(NUM_OF_BAYS) +
1):
# get the current probe to calculate - specific shelf, bay, and only in main_placement scene type
curr_probe = get_curr_probe(
shelf_occupation_dict.get(DF), shelf_number, bay_number,
shelf_occupation_dict.get(MAIN_PLACEMENT_SCENES))
if not curr_probe.empty:
score += self.calculate_category(curr_probe,
product_list_field)
Log.info("category score " + str(score))
return score
def distribution_group_level(self, lvl_2_result):
"""
This function create df sql results, results of distribution on group level based assortment
:param lvl_2_result: df of assortment results in group level
:return: df of sql results for oos assortment group level
"""
lvl_2_result = lvl_2_result.copy()
live_kpi_dist = self.get_kpi_fk(self.LIVE_DIST)
lvl_2_result.loc[:, 'kpi_level_2_fk'] = live_kpi_dist
lvl_2_result.loc[lvl_2_result['target'] == -1, 'target'] = None
lvl_2_result.loc[:, 'denominator_result'] = \
lvl_2_result.apply(lambda row: row['target'] if (row['target'] >= 0 and row['group_target_date'] >
self.current_date) else row['denominator_result'], axis=1)
lvl_2_result.loc[:, 'result'] = lvl_2_result.numerator_result / lvl_2_result.denominator_result
self.manipulate_result_row(lvl_2_result)
self._add_visit_summary_kpi_entities(lvl_2_result)
lvl_2_result = lvl_2_result[self.LVL2_SESSION_RESULTS_COL]
Log.info('Distribution group level is done ')
return lvl_2_result
def calculate_count_posm_per_scene(self, kpi_fk):
if self.match_display_in_scene.empty:
Log.info("No POSM detected at scene level for session: {}".format(self.session_uid))
return False
grouped_data = self.match_display_in_scene.groupby(['scene_fk', 'display_fk'])
for data_tup, scene_data_df in grouped_data:
scene_fk, display_fk = data_tup
posm_count = len(scene_data_df)
template_fk = self.scene_info[self.scene_info['scene_fk'] == scene_fk].get('template_fk')
if not template_fk.empty:
cur_template_fk = int(template_fk)
else:
Log.info("JRIJP: Scene ID {scene} is not complete and not found in scene Info.".format(
scene=scene_fk))
continue
self.common.write_to_db_result(fk=kpi_fk,
numerator_id=display_fk,
denominator_id=self.store_id,
context_id=cur_template_fk,
result=posm_count,
score=scene_fk)
def save_latest_templates(self):
"""
This function reads the latest templates from the Cloud, and saves them in a local path.
"""
if not os.path.exists(self.local_templates_path):
os.makedirs(self.local_templates_path)
dir_name = self.get_latest_directory_date_from_cloud(
self.cloud_templates_path.format(''), self.amz_conn)
files = [
f.key for f in self.amz_conn.bucket.list(
self.cloud_templates_path.format(dir_name))
]
for file_path in files:
file_name = file_path.split('/')[-1]
with open(os.path.join(self.local_templates_path, file_name),
'wb') as f:
self.amz_conn.download_file(file_path, f)
with open(os.path.join(self.local_templates_path, UPDATED_DATE_FILE),
'wb') as f:
f.write(datetime.utcnow().strftime(UPDATED_DATE_FORMAT))
Log.info('Latest version of templates has been saved to cache')
def distribution_sku_level(self, lvl_3_result):
""" This function receive df = lvl_3_result assortment with data regarding the assortment products
This function turn the sku_assortment_results to be in a shape of db result.
return distribution_db_results df
"""
lvl_3_result.rename(columns={
'product_fk': 'numerator_id',
'assortment_group_fk': 'denominator_id',
'in_store': 'result',
'kpi_fk_lvl3': 'kpi_level_2_fk'
},
inplace=True)
lvl_3_result.loc[:, 'result'] = lvl_3_result.apply(
lambda row: self.kpi_result_value(row.result), axis=1)
lvl_3_result = lvl_3_result.assign(
numerator_result=lvl_3_result['result'],
denominator_result=lvl_3_result['result'],
score=lvl_3_result['result'])
lvl_3_result = self.filter_df_by_col(lvl_3_result, self.SKU_LEVEL)
Log.info('Distribution sku level is done ')
return lvl_3_result
def calculate_and_save_distribution_and_oos(self, valid_scif, assortment_product_fks,
distribution_kpi_fk, oos_kpi_fk):
"""Function to calculate distribution and OOS percentage.
Saves distribution and oos percentage as values.
"""
Log.info("Calculate distribution and OOS for {}".format(self.project_name))
scene_products = pd.Series(valid_scif["item_id"].unique())
total_products_in_assortment = len(assortment_product_fks)
count_of_assortment_prod_in_scene = assortment_product_fks.isin(scene_products).sum()
oos_count = total_products_in_assortment - count_of_assortment_prod_in_scene
# count of lion sku / all sku assortment count
if not total_products_in_assortment:
Log.info("No assortments applicable for session {sess}.".format(sess=self.session_uid))
return 0
distribution_perc = count_of_assortment_prod_in_scene / float(total_products_in_assortment)
oos_perc = 1 - distribution_perc
self.common_v2.write_to_db_result(fk=distribution_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=count_of_assortment_prod_in_scene,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=distribution_perc,
score=distribution_perc,
identifier_result="{}_{}".format(DST_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True
)
self.common_v2.write_to_db_result(fk=oos_kpi_fk,
numerator_id=self.own_manufacturer_fk,
numerator_result=oos_count,
denominator_id=self.store_id,
denominator_result=total_products_in_assortment,
context_id=self.store_id,
result=oos_perc,
score=oos_perc,
identifier_result="{}_{}".format(OOS_MAN_BY_STORE_PERC,
self.store_id),
should_enter=True
)
def upload_new_templates(self, immediate_change=False):
"""
This function uploads the new template, along with the latest version of the rest of the templates,
to a new directory (with name as the current date's) in the Cloud.
"""
if not self.templates_to_upload:
Log.info(self.log_suffix + 'No new templates are ready for upload')
else:
if not immediate_change:
next_day = (datetime.utcnow() + timedelta(1)).strftime("%y%m%d")
else:
next_day = datetime.utcnow().strftime("%y%m%d")
templates_path_in_cloud = self.templates_path.format(next_day)
latest_templates = self.get_latest_templates()
for set_name in self.templates_to_upload:
self.amz_conn.save_file(templates_path_in_cloud, set_name, self.templates_to_upload[set_name])
os.remove(self.templates_to_upload[set_name])
if set_name in latest_templates:
latest_templates.pop(set_name)
Log.info(self.log_suffix + 'New templates for sets {} were uploaded'.format(self.templates_to_upload.keys()))
for template_name in latest_templates:
temp_file_path = '{}/{}_temp'.format(os.getcwd(), template_name)
f = open(temp_file_path, 'wb')
self.amz_conn.download_file(latest_templates[template_name], f)
f.close()
self.amz_conn.save_file(templates_path_in_cloud, template_name, temp_file_path)
os.remove(temp_file_path)
Log.info(self.log_suffix + 'Existing templates for sets {} were aligned with the new ones'.format(latest_templates.keys()))
return True
def main_function(self):
comment = None
if self.session_feedback in SESSION_FEEDBACK:
status = self.UNSUCCESSFUL_TM
elif self.scene_info.empty:
status = self.UNSUCCESSFUL_OTHER
comment = 'No Scenes'
elif all(self.scene_info['template_name'].str.contains(
EXTERIOR, flags=re.IGNORECASE)):
status = self.UNSUCCESSFUL_OTHER
comment = 'All scenes are exterior'
elif self.session_feedback.upper() != OK_FEEDBACK:
status = self.UNSUCCESSFUL_OTHER
comment = "Session feedback is not '{}'".format(OK_FEEDBACK)
else:
status = self.SUCCESSFUL
if status == self.SUCCESSFUL:
Log.info('Session is successful')
elif status == self.UNSUCCESSFUL_TM:
Log.info('Session is unsuccessful (TM)')
self.save_review(exclude_status=1,
resolution_code=2,
action_code=6)
elif status == self.UNSUCCESSFUL_OTHER:
Log.info('Session is unsuccessful (Other) - {}'.format(comment))
self.save_review(exclude_status=1,
resolution_code=2,
action_code=5)
def check_allowed_values(self):
Log.info("Allowed Values Check - Started")
result = True
allowed_values = [
column for column in self.data_mapping
if column.get("allowed_values", False)
]
lst_exceptions = []
for allowed_value in allowed_values:
name = allowed_value['xl_column_name']
value = allowed_value['allowed_values']
check = self.hierarchy_template[~self.hierarchy_template[name].str.
lower().isin(value)]
if not check.empty:
for idx, row_data in check.iterrows():
dict_exception = dict()
dict_exception[
'exception'] = "Invalid input, allowed_values: {}".format(
value)
dict_exception[
'message'] = "row_number: {}, column_name: {}, value: {}".format(
idx + 3, name, row_data[name])
if is_debug:
Log.info(dict_exception)
lst_exceptions.append(dict_exception)
result = False
if result:
Log.info("Allowed Values Check - Completed")
return result
def record_all_products(self):
kpi = self.kpi_static_data[
(self.kpi_static_data[KPI_FAMILY] == PS_KPI_FAMILY)
& (self.kpi_static_data[TYPE] == ALL_PROD_KPI)
& (self.kpi_static_data['delete_time'].isnull())]
if kpi.empty:
Log.info("KPI Name:{} not found in DB".format(ALL_PROD_KPI))
else:
Log.info("KPI Name:{} found in DB".format(ALL_PROD_KPI))
num_of_prods = 0
# it will have a substitution product; where the facings are aggregated
self.scif.dropna(subset=['facings'], inplace=True)
for index, each_row in self.scif.iterrows():
self.common.write_to_db_result(
fk=int(kpi.iloc[0].pk),
numerator_id=int(each_row.product_fk),
numerator_result=int(each_row.facings),
denominator_id=int(each_row.scene_id),
denominator_result=0,
score=1,
context_id=int(each_row.template_fk),
)
num_of_prods += 1
Log.info(
"{proj} - For session: {sess}, {prod_count} products were written for kpi: {kpi_name}"
.format(
proj=self.project_name,
sess=self.session_uid,
prod_count=num_of_prods,
kpi_name=kpi.iloc[0].type,
))
def add_atomics_to_static(self):
atomics = self.data.drop_duplicates(
subset=[self.SET_NAME, self.KPI_NAME, self.ATOMIC_NAME], keep='first')
cur = self.aws_conn.db.cursor()
for i in xrange(len(atomics)):
atomic = atomics.iloc[i]
set_name = atomic[self.SET_NAME].replace("'", "\\'").encode('utf-8')
kpi_name = str(atomic[self.KPI_NAME]).replace("'", "\\'").encode('utf-8')
atomic_name = str(atomic[self.ATOMIC_NAME]).replace("'", "\\'").encode('utf-8')
if self.ATOMIC_WEIGHT in atomics.iloc[i].keys():
atomic_weight = float(atomics.iloc[i][self.ATOMIC_WEIGHT])
else:
atomic_weight = 'NULL'
if self.ATOMIC_DISPLAY_TEXT in atomics.iloc[i].keys():
atomic_display_text = atomics.iloc[i][self.ATOMIC_DISPLAY_TEXT].replace(
"'", "\\'").encode('utf-8')
else:
atomic_display_text = atomic_name
if self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name) &
(self.kpi_static_data['atomic_kpi_name'] == atomic_name)].empty:
if set_name in self.kpis_added.keys() and kpi_name in self.kpis_added[set_name].keys():
kpi_fk = self.kpis_added[set_name][kpi_name]
else:
kpi_fk = self.kpi_static_data[(self.kpi_static_data['kpi_set_name'] == set_name) &
(self.kpi_static_data['kpi_name'] == kpi_name)]['kpi_fk'].values[0]
level3_query = \
"""
INSERT INTO static.atomic_kpi (kpi_fk, name, description, display_text, presentation_order, display, atomic_weight)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}');
""".format(kpi_fk, atomic_name, atomic_name, atomic_display_text, 1, 'Y', atomic_weight)
cur.execute(level3_query)
self.kpi_counter['atomic'] += 1
else:
Log.info("Atomic '{}' already exists for KPI '{}' Set '{}'. Ignored".format(
atomic_name, kpi_name, set_name))
self.aws_conn.db.commit()
def save_display_presence_per_sku(self, kpi, posm_to_check=None, numerator_result=None,
mandatory_eans=None, optional_posm_eans=None):
# This should be done once only per scene
current_scene_fk = self.scene_info.iloc[0].scene_fk
context_fk = self.scene_info.iloc[0].template_fk
if posm_to_check and (mandatory_eans or optional_posm_eans):
# the scene is valid as per external targets template
# PER SCENE which are secondary display
# save POSM as numerator; each product as denominator and template as context
Log.info('Calculate display presence per sku. The session: {sess} - scene: {scene} is valid.'
.format(sess=self.session_uid, scene=current_scene_fk))
numerator_id = posm_to_check
else:
# the scene doesn't have mandatory or optional eans
# PER SCENE which are secondary display
# save numerator as empty; each empty as denominator and template as context
Log.info('Calculate display presence per sku. The session: {sess} - scene: {scene} is invalid.'
.format(sess=self.session_uid, scene=current_scene_fk))
numerator_id = 0 # General Empty
# result => [ 0=Optional, 1=mandatory, 2=NA]
# numerator_result => [0-- posm not recognized; 1--one one posm; 2--multi posm]
for idx, each_row in self.scif.iterrows():
result = 2 # NA
if mandatory_eans and each_row.product_ean_code in mandatory_eans:
result = 1 # mandatory
elif optional_posm_eans and each_row.product_ean_code in optional_posm_eans:
result = 0 # optional
score = min(each_row.median_price, each_row.median_promo_price)
if not score or np.isnan(score):
score = -1
self.common.write_to_db_result(
fk=kpi.iloc[0].pk,
numerator_id=numerator_id, # its the POSM to check or {General Empty if not recognized or multi}
numerator_result=numerator_result, # whether POSM is 0, 1 or 2
denominator_id=each_row.item_id, # each product in scene
score=score, # -1 means not saved in DB
result=result, # 0-optional, 1-mandatory, 2- NA
context_id=context_fk, # template of scene
by_scene=True,
)
# Save All Displays in the scene
Log.info('Save all displays in the scene. The session: {sess} - scene: {scene}.'
.format(sess=self.session_uid, scene=current_scene_fk))
kpi_all_displays_in_scene = self.kpi_static_data[
(self.kpi_static_data[KPI_TYPE_COL] == GSK_DISPLAYS_ALL_IN_SCENE)
& (self.kpi_static_data['delete_time'].isnull())]
for idx, each_row in self.match_display_in_scene.iterrows():
self.common.write_to_db_result(
fk=kpi_all_displays_in_scene.iloc[0].pk,
numerator_id=each_row.display_fk, # the Display/POSM present in the scene
numerator_result=1, # no meaning
denominator_id=each_row.display_brand_fk, # display brand
score=1, # no meaning
result=1, # no meaning
context_id=self.store_id, # template of scene
by_scene=True,
)
return True
def calculate_overall_result_and_score(self, result_kpi_pk, score_kpi_fk, group_fk,
product_presence_data, product_position_data,
product_facings_data, best_shelf_position,
min_group_product_facing):
numerator_result = 1 in product_presence_data.values()
# product_position_data -> (min_shelf, is_in_config)
min_level_of_product = 0
if product_position_data:
_score_products_presence = filter(lambda x: x[1] == 1, product_position_data.values())
if _score_products_presence:
# find min result among the in config ones
_score_products_presence.sort(key=lambda x: x[0])
min_level_of_product = int(_score_products_presence[0][0])
else:
# score is all 0
_score_products_presence_present = filter(lambda x: x[0] != 0, product_position_data.values())
_score_products_presence_present.sort(key=lambda x: x[0])
if _score_products_presence_present:
min_level_of_product = _score_products_presence_present[0][0]
else:
Log.info("Product presence information is empty for any product in group = {group_fk}".format(
group_fk=group_fk
))
Log.info("Saving Overall Result for group: {group_fk} in session {sess}"
.format(group_fk=group_fk,
sess=self.session_uid,
))
self.common.write_to_db_result(fk=result_kpi_pk,
numerator_id=group_fk,
denominator_id=self.store_id,
context_id=self.store_id,
numerator_result=int(numerator_result), # bool to int
denominator_result=min_level_of_product,
result=sum(product_facings_data.values()), # bool to int
)
Log.info("Saving Overall Score for group: {group_fk} in session {sess}"
.format(group_fk=group_fk,
sess=self.session_uid,
))
is_in_best_shelf = False
if best_shelf_position and min_level_of_product:
if type(best_shelf_position) != list:
best_shelf_position = [best_shelf_position]
is_in_best_shelf = min_level_of_product in map(lambda x: int(x), best_shelf_position)
has_minumum_facings_per_config = False
if not min_group_product_facing or not min_group_product_facing.strip():
min_group_product_facing = 0
if sum(product_facings_data.values()) >= int(min_group_product_facing):
has_minumum_facings_per_config = True
self.common.write_to_db_result(fk=score_kpi_fk,
numerator_id=group_fk,
denominator_id=self.store_id,
context_id=self.store_id,
numerator_result=int(numerator_result), # bool to int
denominator_result=int(is_in_best_shelf), # bool to int
result=int(has_minumum_facings_per_config), # bool to int
score=int(all([numerator_result, is_in_best_shelf,
has_minumum_facings_per_config])) # bool to int
)
def check_allowed_values(self):
result = True
allowed_values = [
column for column in self.data_mapping
if column.get("allowed_values", False)
]
for allowed_value in allowed_values:
name = allowed_value['xl_column_name']
value = allowed_value['allowed_values']
check = self.template[~self.template[name].str.lower().isin(value)]
if not check.empty:
Log.error("Invalid input, allowed_values: {}".format(value))
for idx, row_data in check.iterrows():
Log.error(
"row_number: {}, column_name: {}, value: {}".format(
idx + 3, name, row_data[name]))
result = False
if result:
Log.info("Allowed Values check completed")
return result
def check_template(self):
set_names = self._get_set_names()
suspicious = {
'kpis with empty filters': [],
'filters with no data': {},
'kpi with no filters': []
}
for set_name in set_names:
template_data = self._template.parse_template(set_name)
hierarchy = Definition(
template_data,
self._get_store_channel).get_atomic_hierarchy_and_filters(
set_name)
product = self._data_provider.all_products
for kpi in hierarchy:
if kpi.has_key('filters'):
fil = kpi['filters']
if len(fil) == 0:
suspicious['kpis with empty filters'].append(
'set {} atomic {} filters are empty'.format(
set_name, kpi['atomic']))
for key, value in fil.iteritems():
if key == 'order by':
continue
key = self.split_filter(key)
key = self.rename_filter(key)
if isinstance(value, tuple):
value = value[0]
Log.info('set {} atomic {}, checking {} in {}'.format(
set_name, kpi['atomic'], value, key))
pp = product[product[key].isin(value)]
if len(pp) == 0:
suspicious['filters with no data'].setdefault(
key, set()).update(value)
else:
suspicious['kpi with no filters'].append(
'set {} kpi {} has no filters'.format(
set_name, kpi['atomic']))
return suspicious
def run_project_calculations(self):
self.timer.start()
tool_box = KCUSToolBox(self.data_provider,
self.output,
set_name='KC Score')
self.timer.stop('KPIGenerator.run_project_calculations')
jg = KCUSJsonGenerator('kc_us-prod')
jg.create_json('KEngine_KC_US.xlsx')
for p in jg.project_kpi_dict['Block Together']:
if p.get('KPI_Type') == 'Relative position of Blocked Together':
tool_box.calculate_block_together_relative(p)
if p.get('KPI_Type') == 'Blocked Together':
tool_box.calculate_block_together(p)
for p in jg.project_kpi_dict['Simple KPIs']:
if p.get('KPI_Type') == 'Anchor':
tool_box.calculate_anchor(p)
if p.get('KPI_Type') == 'Eye Level':
tool_box.calculate_eye_level(p)
if p.get('KPI_Type') == 'Flow':
tool_box.calculate_flow(p)
# if p.get('KPI_Type') == 'Survey':
# tool_box.check_survey_answer(p)
# if p.get('KPI_Type') == 'Flow between':
# tool_box.calculate_flow_between(p)
for p in jg.project_kpi_dict['Relative Position']:
if p.get('KPI_Type') == 'Relative Position':
tool_box.calculate_relative_position(p)
if p.get('KPI_Type') == 'Assortment':
tool_box.calculate_assort(p)
tool_box.commit_results_data()
kpitool_box = KCUS_KPIToolBox(self.data_provider,
self.output,
set_name='KC Score')
kpitool_box.main_calculation()
kpitool_box.commit_results_data()
Log.info('calculate kpi took {}'.format(tool_box.download_time))
def get_scif_own_man_products_of_session(self, session_uid):
Log.info("Getting previous sessions own manufacturer products")
query = """
SELECT
item_id as product_fk
FROM
reporting.scene_item_facts scif
JOIN
probedata.session sess ON sess.pk = scif.session_id
JOIN
static_new.product prod ON prod.pk = scif.item_id
JOIN
static_new.brand brand ON brand.pk = prod.brand_fk
WHERE
sess.session_uid = '{session_uid}'
AND facings <> 0
AND prod.type='SKU'
AND prod.is_active = 1
AND brand.manufacturer_fk = {manuf};
""".format(session_uid=session_uid, manuf=self.own_manufacturer_fk)
product_df = pd.read_sql_query(query, self.rds_conn.db)
return product_df
def calculate_brand_presence_overall_score(self, df):
if df.empty:
return
kpi_fk = self.get_kpi_fk_by_kpi_type("BRAND_GROUP_PRESENCE_OWN_MANF_WHOLE_STORE")
numerator_id = self.own_manufacturer_fk
numerator_result = df['result'].sum()
denominator_id = self.store_id
denominator_result = df.shape[0]
score = 0.0
try:
score = numerator_result / float(denominator_result)
except Exception as ex:
Log.info("Warning: {}".format(ex.message))
self.commonV2.write_to_db_result(fk=kpi_fk,
numerator_id=numerator_id,
numerator_result=numerator_result,
denominator_id=denominator_id,
denominator_result=denominator_result,
score=score,
result=score)
