class NESTLEUSToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
score = 0
return score
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 INBEVTRADMXToolBox:
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.common2 = Common2(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.templates = self.data_provider.all_templates
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.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.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template_11_v3.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid,
self.data_provider,
self.kpi_static_data,
self.common, self.common2)
self.new_static_data = self.common2.kpi_static_data
self.manufacturer_fk = 1
self.match_displays_in_scene = self.data_provider.match_display_in_scene
self.mpis = self.mpis_merger()
self.all_data = pd.merge(self.scif,
self.match_product_in_scene[[
'product_fk', 'shelf_number', 'scene_fk',
'facing_sequence_number'
]],
how="inner",
left_on=['item_id', 'scene_id'],
right_on=['product_fk',
'scene_fk']).drop_duplicates()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.EXCLUDE_FILTER = 0
self.CONTAIN_FILTER = 2
self.EXCLUDE_EMPTY = False
self.INCLUDE_EMPTY = True
# init functions:
def parse_template(self):
"""
convert excel file to data frame
:return: data frame
"""
template_df = pd.read_excel(self.excel_file_path,
sheetname='template',
encoding='utf-8')
template_df['Store Additional Attribute 4'] = template_df[
'Store Additional Attribute 4'].fillna('')
template_df['Store Additional Attribute 13'] = template_df[
'Store Additional Attribute 13'].fillna('')
return template_df
def check_store_type(self, row, relevant_columns):
"""
this method checks if the session store type is valid
:type relevant_columns: list
:param row: current KPI row
:return: boolean
"""
# make sure that we need to check this field
if relevant_columns.__contains__('store_type'):
# get the valid stores from the template
stores_df = pd.read_excel(self.excel_file_path,
sheetname='store types')
# create a list of the valid stores
stores = stores_df.values
return row['store_type'] in stores
else:
return True
# calculation:
def main_calculation(self):
# calculate geo
geo_result = self.geo.calculate_geo_location()
self.geo.write_geo_to_db(float(geo_result))
# calculate from template
self.calculate_kpi_set_from_template()
self.calculate_posm_displays()
self.common.commit_results_data()
self.common2.commit_results_data()
def calculate_kpi_set_from_template(self):
"""
this method chooses the correct set to calculate
there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from
template
:return: None
"""
# get the template
parsed_template = self.parse_template()
# get all the unique sets
# sets = parsed_template['KPI Level 1 Name'].unique()
# get the session additional_attribute_4 & 13
additional_attribute_4 = self.store_info.additional_attribute_4.values[
0]
additional_attribute_13 = self.store_info.additional_attribute_13.values[
0]
set_name = self.choose_correct_sets_to_calculate(
additional_attribute_4, additional_attribute_13, parsed_template)
# for set_name in set_names:
# wrong value in additional attribute 4 - shouldn't calculate
if set_name == '':
Log.warning(
'Wrong value in additional attribute 4 - shouldnt calculate')
return -1
# get only the part of the template that is related to this set
set_template_df = parsed_template[parsed_template['KPI Level 1 Name']
== set_name]
# start calculating !
self.calculate_set_score(set_template_df, set_name)
@staticmethod
def choose_correct_sets_to_calculate(additional_attribute_4,
additional_attribute_13, template):
"""
choose what is the appropriate set to calculate
:param additional_attribute_4: session additional_attribute_4. if None, will ignore the kpi.
:param additional_attribute_13: session additional_attribute_13. if None, will ignore this attribute
:param template: parsed template
:return: set name to calculate - assuming each additional attribute 4 matches only 1 set name.
"""
template = template.dropna(subset=['Store Additional Attribute 4'],
axis=0)
# Makes sure attribute 4 exist
if not additional_attribute_4:
return ''
if additional_attribute_13:
sets = template[
(template['Store Additional Attribute 4'].str.contains(
additional_attribute_4))
& ((template['Store Additional Attribute 13'].str.contains(
additional_attribute_13))
| (template['Store Additional Attribute 13'] == ''))]
else:
sets = template[
(template['Store Additional Attribute 4'].str.contains(
additional_attribute_4))
& (template['Store Additional Attribute 13'] == '')]
if sets.empty:
return ''
else:
sets = sets['KPI Level 1 Name'].unique().tolist()
if len(sets) == 1:
set_name = sets[0]
elif additional_attribute_4 == 'BC':
set_name = sets[0]
elif additional_attribute_4 == 'BA':
set_name = sets[1]
elif additional_attribute_4 == 'MODELORAMA':
set_name = sets[2]
else:
Log.error('KPI should only run on one KPI set.')
return set_name
def calculate_set_score(self, set_df, set_name):
"""
this method iterates kpi set and calculates it's score
:param set_df: the set df to calculate score for
:param set_name: the kpi set name
:return: None
"""
set_score = 0
# get array of all kpi level 2 names
kpi_names = set_df['KPI Level 2 Name'].unique()
# iterate all kpi level 2
for kpi_name in kpi_names:
# calculate kpi level 2 score
kpi_score = self.calculate_kpi_level_2_score(
kpi_name, set_df, set_name)
# accumulate set score
set_score += kpi_score
# round set_score if the set is 'Set Urban'
if set_name == 'Set Urban':
set_score = round(set_score)
# finally, write level 1 kpi set score to DB
self.write_kpi_set_score_to_db(set_name, set_score)
def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name):
"""
this method gets kpi level 2 name, and iterates it's related atomic kpis
:param set_name: kpi set name
:param kpi_name: kpi level 2 name
:param set_df: kpi set df
:return: kpi level 2 score
"""
kpi_df = set_df[set_df['KPI Level 2 Name'].str.encode('utf8') ==
kpi_name.encode('utf-8')]
kpi_score = 0
# iterate the all related atomic kpis
for i, row in kpi_df.iterrows():
# get atomic kpi name
kpi_level_3_name = row['KPI Level 3 Name']
# calculate atomic kpi score
atomic_kpi_score = self.calculate_atomic_kpi_score(
row, kpi_level_3_name, kpi_name, set_name)
# accumulate kpi level 2 score
kpi_score += atomic_kpi_score
write_to_all_levels = False
if len(kpi_df
) > 1: # if there is just one atomic we don't need two levels
write_to_all_levels = True
elif kpi_df['KPI Level 3 Name'].iloc[0] != kpi_df[
'KPI Level 2 Name'].iloc[0]:
write_to_all_levels = True
self.write_kpi_score_to_db(kpi_name, set_name, kpi_score,
write_to_all_levels)
return kpi_score
def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name,
set_name):
"""
this method calculates score for specific atomic kpi
:param set_name: kpi set name
:param kpi_name: kpi name
:param kpi_level_3_name: the atomic kpi name
:param row: atomic kpi details
:return: atomic kpi score
"""
atomic_kpi_score = 0
# get column name to consider in calculation
relevant_columns = map(str.strip, str(row['column names']).split(','))
optional_columns = map(str.strip,
str(row['optional column']).split(','))
is_kpi_passed = 0
if self.check_store_type(row, relevant_columns):
# get weight of current atomic kpi
curr_weight = row['weights']
# figure out what type of calculation need to be done
if row['KPI type'] == 'Product Availability':
if kpi_level_3_name == 'URBAN':
score = self.calculate_weigthed_availability_score(
row, relevant_columns)
if score:
atomic_kpi_score = score
elif kpi_level_3_name == 'Hay o no hay # frentes':
if self.calculate_lead_availability_score(
row, relevant_columns):
is_kpi_passed = 1
elif kpi_level_3_name == 'Adherencia de materiales':
if self.calculate_or_availability(row, relevant_columns,
optional_columns):
is_kpi_passed = 1
else:
if self.calculate_availability_score(
row, relevant_columns):
is_kpi_passed = 1
if kpi_level_3_name == '100% Marcas HE':
curr_weight = self.unique_brand_count
elif row['KPI type'] == 'SOS':
ratio = self.calculate_sos_score(row, relevant_columns)
if (row['product_type'] == 'Empty') & (ratio <= 0.2):
if self.scif[self.scif['template_name'] ==
row['template_name']].empty:
is_kpi_passed = 0
else:
is_kpi_passed = 1
elif ratio == 1:
is_kpi_passed = 1
elif row['KPI type'] == 'Survey':
if self.calculate_survey_score(row):
is_kpi_passed = 1
if is_kpi_passed == 1:
atomic_kpi_score += curr_weight
# write result to DB
# the customer asked for this specific KPI will write 100 in DB if it passed even if the weight is 0
if kpi_level_3_name == 'Sin Espacios Vacios' and curr_weight == 0 and is_kpi_passed == 1:
# atomic_kpi_score = 100
self.write_atomic_to_db(kpi_level_3_name, 100, kpi_name,
set_name, is_kpi_passed, curr_weight)
else:
self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score,
kpi_name, set_name, is_kpi_passed,
curr_weight)
return atomic_kpi_score
# sos
def calculate_sos_score(self, row, relevant_columns):
"""
this method calculates share of shelf score according to columns from the data frame
:param relevant_columns: columns to check in the excel file
:param row: data frame to calculate from
:return: share of shelf score
"""
if 'shelf_number' in relevant_columns:
scif_index_list = []
df = self.all_data[
(self.all_data.template_name == row['template_name'])
& (self.all_data.shelf_number == row['shelf_number']) &
(self.all_data.facing_sequence_number > 0)]
df.shelf_number = df.shelf_number.astype(float)
df.shelf_number = df.shelf_number.astype(str)
df = df[df['product_type'] != 'Empty']
else:
# get df only with the correct template name
df = self.scif[self.scif.template_name == row['template_name']]
if not pd.isna(row['exclude product_type']):
for excl in [
e.strip() for e in row['exclude product_type'].split(',')
]:
df = df[df['product_type'] != excl]
# sum of all the facings in df
facings = df.facings.values.sum()
if facings == 0:
return 0
# create dictionary for calculating
filters_dict = self.create_sos_filtered_dictionary(
relevant_columns, row)
# reduce the df only to relevant columns
df = df[filters_dict.keys()]
# check if it's invasion KPI for the special case
inv = row['KPI Level 3 Name'][:3] == 'Inv'
ratio = self.calculate_sos_ratio(df, filters_dict, inv)
return ratio / facings
def calculate_sos_ratio(self, df, filters_dict, inv):
ratio = 0
if 'manufacturer_name' in df.columns.tolist():
filter_out = df[(df['product_type'] == ('Empty'))
& (df['manufacturer_name'] == 'General')].index
df.drop(filter_out, inplace=True)
for key in filters_dict:
delete = [key for value in filters_dict[key] if value in ['nan']]
for key in delete:
del filters_dict[key]
# iterate the data frame
for i, df_row in df.iterrows():
# initialize the boolean variable
bol = True
# special case that the customer asked
# if inv and df_row.product_type == 'Empty':
if df_row.product_type == 'Empty':
ratio = ratio + self.scif.facings.loc[i]
continue
# iterate the filtered dictionary keys
else:
for key in filters_dict.keys():
# check if the value in df row in in "key" column is in the filtered dictionary we created before
bol &= df_row[key] in filters_dict[key]
# that means success of the inner loop, that all the values matching for this data frame row
if bol:
if 'shelf_number' in filters_dict.keys():
ratio = ratio + self.all_data.facings.loc[i]
else:
# accumulate ratio
ratio = ratio + self.scif.facings.loc[i]
return ratio
def create_sos_filtered_dictionary(self, relevant_columns, row):
"""
this method filters out relevant columns from the template fso we can calculate SOS easily
:param relevant_columns: relevant columns from temolates
:param row: specific row from template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value in [
'Store Additional Attribute 4',
'Store Additional Attribute 13'
] or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
# availability:
def calculate_weigthed_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
passed = 0
# Gets the scene types
scene_types = row['template_name'].split(', ')
scenes_num = len(scene_types)
# man = row['manufacturer_name']
if 'scene_type' in relevant_columns:
relevant_columns.remove('scene_type')
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for scene in scene_types:
filters_dict.update({'template_name': scene})
# call the generic method from KPIUtils_v2
if self.availability.calculate_availability(**filters_dict):
passed += 1
return (passed / float(scenes_num)) * 100 if scenes_num else 0
def calculate_lead_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# Gets the brand names
brand_names = row['brand_name'].split(', ')
if 'scene_type' in relevant_columns:
relevant_columns.remove('scene_type')
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for brand in brand_names:
filters_dict.update({'brand_name': brand})
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
if self.decide_availability_score(row, availability_score):
return True
return False
def calculate_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
for key in filters_dict:
delete = [key for value in filters_dict[key] if value in ['nan']]
for key in delete:
del filters_dict[key]
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
filtered_df = self.scif[self.get_filter_condition(
self.scif, **filters_dict)]
self.unique_brand_count = len(filtered_df['brand_name'].unique())
# check if this score should pass or fail
return self.decide_availability_score(row, availability_score)
def create_availability_filtered_dictionary(self, relevant_columns, row):
"""
this method creates a dictionary with keys according to the specific row in the template
:param relevant_columns: columns to create keys by
:param row: the specific row in the template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'Store Additional Attribute 4' or column_value == 'store_type' or \
column_value == 'Store Additional Attribute 13':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def calculate_or_availability(self, row, relevant_columns,
optional_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
for optional_column in optional_columns:
# create filtered dictionary
temp_relevant_columns = relevant_columns[:]
temp_relevant_columns.append(optional_column)
filters_dict = self.create_availability_filtered_dictionary(
temp_relevant_columns, row)
for key in filters_dict:
delete = [
key for value in filters_dict[key] if value in ['nan']
]
for key in delete:
del filters_dict[key]
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
# check if this score should pass or fail
if self.decide_availability_score(row, availability_score):
return True
return False
def filter_product_names(self, exclude_skus):
"""
this method filters list of SKUs from self.scif
:param exclude_skus: list of SKUs to exclude from template
:return: filtered list
"""
return filter(lambda sku: sku not in exclude_skus,
self.scif.product_name.values)
@staticmethod
def decide_availability_score(row, availability_score):
"""
this method decides if the score should pass or fail according to the template
:param row: scpecific row from template
:param availability_score: score
:return: Boolean
"""
if availability_score == 0:
return False
else:
if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[
'KPI Level 3 Name'] == 'Hay o no hay Pop?':
if row['KPI Level 2 Name'] == 'Pop Exterior':
return availability_score > 1
elif row['KPI Level 2 Name'] == 'Pop Interior':
return availability_score > 1
elif row['KPI Level 1 Name'] == 'Set Self Execution' and row[
'KPI Level 3 Name'] == 'Hay o no hay # frentes':
return availability_score > 24
else:
return True
# surveys:
def calculate_survey_score(self, row):
"""
this method calculates survey score according to columns from the data frame
:param row: data frame to calculate from
:return: boolean
"""
question_code = str(int(row['Survey Question Code']))
if not self.survey_response.empty and \
not self.survey_response[self.survey_response.code == question_code].empty:
answer = self.survey_response.selected_option_text[
self.survey_response.code == question_code].values[0]
if answer == 'Si':
return True
else:
if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador':
return True
return False
else:
return False
# help functions:
def calculate_posm_displays(self):
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(DISPLAY_KPI)
for display_fk in self.match_displays_in_scene['display_fk'].to_list():
self.common2.write_to_db_result(fk=new_kpi_set_fk,
result=0,
numerator_id=self.manufacturer_fk,
denominator_id=display_fk)
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(POSM_KPI)
for product_fk in self.scif['product_fk'][self.scif['location_type'] ==
'POSM'].to_list():
self.common2.write_to_db_result(fk=new_kpi_set_fk,
result=0,
numerator_id=self.manufacturer_fk,
denominator_id=product_fk)
def handle_exclude_skus(self, filters_dict, relevant_columns, row):
"""
this method checks if there is value in 'exclude skus' column in the template.
if exists, it filters out the relevant skus from the calculation
:param filters_dict: filtered dictionary
:param relevant_columns: columns to create keys by
:param row: specific row to calculate
:return: None
"""
try:
exclude_skus = row['exclude skus'].split(',')
except AttributeError:
exclude_skus = []
if exclude_skus:
# filter out some product names according to template
product_names = self.filter_product_names(exclude_skus)
filters_dict['product_name'] = product_names
if 'exclude skus' in row.to_dict().keys(
) and 'exclude skus' in relevant_columns:
relevant_columns.remove('exclude skus')
# db functions:
def write_kpi_set_score_to_db(self, set_name, set_score):
"""
this method writes set kpi score to static.kps_results DB
:param set_name: set name
:param set_score: set score
:return: None
"""
# kpi_set_fk = self.kpi_static_data.kpi_set_fk[self.kpi_static_data.kpi_set_name == set_name].unique()[0]
# self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score)
new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(set_name)
self.common2.write_to_db_result(
fk=new_kpi_set_fk,
result=set_score,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
identifier_result=self.common2.get_dictionary(name=set_name))
def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score,
write_to_all_levels):
"""
this method writes kpi score to static.kpk_results DB
:param kpi_name: name of level 2 kpi
:param set_name: name of related set
:param kpi_score: the score
:return: None
"""
# kpi_fk = \
# self.kpi_static_data.kpi_fk[
# (self.kpi_static_data.kpi_name.str.encode('utf-8') == kpi_name.encode('utf-8')) &
# (self.kpi_static_data.kpi_set_name == set_name)].values[0]
# self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score)
if write_to_all_levels:
new_kpi_fk = self.common2.get_kpi_fk_by_kpi_name(kpi_name)
self.common2.write_to_db_result(
fk=new_kpi_fk,
result=kpi_score,
should_enter=True,
identifier_parent=self.common2.get_dictionary(name=set_name),
identifier_result=self.common2.get_dictionary(name=kpi_name))
def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name, set_name,
is_kpi_passed, curr_weight):
"""
this method writes atomic kpi score to static.kpi_results DB
:param curr_weight: current weight of atomic kpi
:param is_kpi_passed: is this kpi passed
:param atomic_name: atomic kpi name
:param atomic_score: the score
:param kpi_name: name of related kpi
:param set_name: name of related set
:return:
"""
# atomic_kpi_fk = \
# self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) &
# (self.kpi_static_data.kpi_name == kpi_name) &
# (self.kpi_static_data.kpi_set_name == set_name)].values[0]
# attrs = self.common.create_attributes_dict(fk=atomic_kpi_fk, score=is_kpi_passed, level=self.LEVEL3)
# attrs['result'] = {0: atomic_score}
# attrs['kpi_weight'] = {0: curr_weight}
# query = insert(attrs, self.common.KPI_RESULT)
# self.common.kpi_results_queries.append(query)
identifier_parent = self.common2.get_dictionary(name=kpi_name)
if atomic_name == kpi_name:
identifier_parent = self.common2.get_dictionary(name=set_name)
new_atomic_fk = self.common2.get_kpi_fk_by_kpi_name(atomic_name)
# kpi_fk = \
# self.new_static_data[self.new_static_data['client_name'].str.encode('utf-8') == kpi_name.encode('utf-8')][
# 'pk'].values[0]
self.common2.write_to_db_result(fk=new_atomic_fk,
result=atomic_score,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
weight=curr_weight,
should_enter=True,
score=is_kpi_passed,
identifier_parent=identifier_parent)
def mpis_merger(self):
try:
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.templates, on='template_fk', suffixes=['', '_t'])
return mpis
except:
pass
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', DIAGEOAUGENERALToolBox.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:
pass
# Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
class CCBOTTLERSUSREDToolBox:
def __init__(self, data_provider, output, calculation_type):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.templates = {}
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
else:
self.TEMPLATE_PATH = SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(
self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[
(main_template[Const.REGION] == self.region)
& (main_template[Const.STORE_TYPE] == self.store_type)]
self.scene_calculator = CCBOTTLERSUSSceneRedToolBox(
data_provider, output, self.templates, self)
self.scenes_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_SESSION)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_SCENE)
self.used_scenes = []
self.red_score = 0
self.weight_factor = self.get_weight_factor()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.calculation_type == Const.SOVI:
self.scenes_results = self.scene_calculator.main_calculation()
session_template = main_template[main_template[Const.SESSION_LEVEL]
== Const.V]
for i, main_line in session_template.iterrows():
self.calculate_main_kpi(main_line)
else:
for i, main_line in main_template.iterrows():
self.calculate_manual_kpi(main_line)
self.choose_and_write_results()
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.SHEET]
relevant_scif = self.scif[
(self.scif['scene_id'].isin(self.united_scenes))
& (self.scif['product_type'] != 'Empty')]
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
scene_groups = self.does_exist(main_line, Const.SCENE_TYPE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(
scene_groups)]
if kpi_type == Const.SCENE_AVAILABILITY:
result = False if relevant_scif.empty else True
else:
isnt_dp = True if self.store_attr != Const.DP and main_line[
Const.STORE_ATTRIBUTE] == Const.DP else False
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[
Const.KPI_NAME] == kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
if main_line[Const.SAME_PACK] == Const.V:
result = self.calculate_availability_with_same_pack(
relevant_template, relevant_scif, isnt_dp, target)
else:
function = self.get_kpi_function(kpi_type)
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = function(kpi_line, relevant_scif, isnt_dp)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
def calculate_manual_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_template = self.templates[Const.SURVEY]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME]
== kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = self.calculate_survey_specific(kpi_line)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
# write in DF:
def write_to_session_level(self, kpi_name, result=0):
"""
Writes a result in the DF
:param kpi_name: string
:param result: boolean
"""
result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result * 1}
self.session_results = self.session_results.append(result_dict,
ignore_index=True)
def write_to_all_levels(self,
kpi_name,
result,
display_text,
weight,
scene_fk=None,
reuse_scene=False):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
score = self.get_score(weight) * (result > 0)
self.red_score += score
result_dict = {
Const.KPI_NAME: kpi_name,
Const.RESULT: result,
Const.SCORE: score
}
if scene_fk:
result_dict[Const.SCENE_FK] = scene_fk
if not reuse_scene:
self.used_scenes.append(scene_fk)
self.all_results = self.all_results.append(result_dict,
ignore_index=True)
self.write_to_db(kpi_name, score, display_text=display_text)
# survey:
def calculate_survey_specific(self,
kpi_line,
relevant_scif=None,
isnt_dp=None):
"""
returns a survey line if True or False
:param kpi_line: line from the survey sheet
:param relevant_scif:
:param isnt_dp:
:return: True or False - if the question gets the needed answer
"""
question = kpi_line[Const.Q_TEXT]
if not question:
question_id = kpi_line[Const.Q_ID]
if question_id == "":
Log.warning(
"The template has a survey question without ID or text")
return False
question = ('question_fk', int(question_id))
answers = kpi_line[Const.ACCEPTED_ANSWER].split(',')
min_answer = None if kpi_line[Const.REQUIRED_ANSWER] == '' else True
for answer in answers:
if self.survey.check_survey_answer(survey_text=question,
target_answer=answer,
min_required_answer=min_answer):
return True
return False
# availability:
def calculate_availability_with_same_pack(self, relevant_template,
relevant_scif, isnt_dp, target):
"""
checks if all the lines in the availability sheet passes the KPI, AND if all of these filtered scif has
at least one common product that has the same size and number of sub_packages.
:param relevant_template: all the match lines from the availability sheet.
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param target: how many lines should pass
:return: boolean
"""
relevant_scif = relevant_scif.fillna("NAN")
# only items categorized as SSD should be evaluated in this calculation; see PROS-6342
relevant_scif = relevant_scif[relevant_scif['att4'] == 'SSD']
if relevant_scif.empty:
return False
sizes = relevant_scif['size'].tolist()
sub_packages_nums = relevant_scif['number_of_sub_packages'].tolist()
packages = set(zip(sizes, sub_packages_nums))
for package in packages:
passed_counter = 0
filtered_scif = relevant_scif[
(relevant_scif['size'] == package[0])
& (relevant_scif['number_of_sub_packages'] == package[1])]
for i, kpi_line in relevant_template.iterrows():
answer = self.calculate_availability(kpi_line, filtered_scif,
isnt_dp)
if answer:
passed_counter += 1
if passed_counter < target:
return False
return True
def calculate_availability(self, kpi_line, relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param kpi_line: line from the availability sheet
:return: boolean
"""
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
return True
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
target = kpi_line[Const.TARGET]
return filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= target
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
def filter_scif_availability(self, kpi_line, relevant_scif):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:return:
"""
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.TRADEMARK: "att2",
Const.SIZE: "size",
Const.NUM_SUB_PACKAGES: "number_of_sub_packages",
Const.PREMIUM_SSD: "Premium SSD",
Const.INNOVATION_BRAND: "Innovation Brand",
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
# SOS:
def calculate_sos(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS line in the relevant scif.
:param kpi_line: line from SOS sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.SSD_STILL] != "":
relevant_scif = self.filter_by_type_value(
relevant_scif, Const.SSD_STILL, kpi_line[Const.SSD_STILL])
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = float(kpi_line[Const.TARGET]) / 100
percentage = num_scif['facings'].sum() / relevant_scif['facings'].sum() if relevant_scif['facings'].sum() > 0 \
else 0
return percentage >= target
# SOS majority:
def calculate_sos_maj(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS majority line in the relevant scif. Filters the denominator and sends the line to the
match function (majority or dominant)
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator (and the denominator of the dominant part).
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
den_type = kpi_line[Const.DEN_TYPES_2]
den_value = kpi_line[Const.DEN_VALUES_2]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.MAJ_DOM] == Const.MAJOR:
answer = self.calculate_majority_part(kpi_line, relevant_scif,
isnt_dp)
elif kpi_line[Const.MAJ_DOM] == Const.DOMINANT:
answer = self.calculate_dominant_part(kpi_line, relevant_scif,
isnt_dp)
else:
Log.warning("SOS majority does not know '{}' part".format(
kpi_line[Const.MAJ_DOM]))
answer = False
return answer
def calculate_majority_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the SOS is bigger than 50%.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
num_type = kpi_line[Const.NUM_TYPES_2]
num_value = kpi_line[Const.NUM_VALUES_2]
num_scif = self.filter_by_type_value(num_scif, num_type, num_value)
if num_scif.empty:
return None
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = Const.MAJORITY_TARGET
return num_scif['facings'].sum() / relevant_scif['facings'].sum(
) >= target
def calculate_dominant_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the given value in the given type is the one with the most facings.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out.
:return: boolean
"""
type_name = self.get_column_name(kpi_line[Const.NUM_TYPES_1],
relevant_scif)
values = str(kpi_line[Const.NUM_VALUES_1]).split(', ')
if isnt_dp:
relevant_scif = relevant_scif[~(
relevant_scif['manufacturer_name'].isin(Const.DP_MANU))]
if kpi_line[Const.ADD_IF_NOT_DP] != "":
values_to_add = str(kpi_line[Const.ADD_IF_NOT_DP]).split(', ')
values = values + values_to_add
if type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
max_facings, needed_one = 0, 0
values_type = relevant_scif[type_name].unique().tolist()
if None in values_type:
values_type.remove(None)
current_sum = relevant_scif[
relevant_scif[type_name].isnull()]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
for value in values_type:
current_sum = relevant_scif[relevant_scif[type_name] ==
value]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
if value in values:
needed_one += current_sum
return needed_one >= max_facings
# helpers:
def get_column_name(self, field_name, df):
"""
checks what the real field name in DttFrame is (if it exists in the DF or exists in the "converter" sheet).
:param field_name: str
:param df: scif/products
:return: real column name (if exists)
"""
if field_name in df.columns:
return field_name
if field_name.upper() in self.converters[
Const.NAME_IN_TEMP].str.upper().tolist():
field_name = self.converters[self.converters[
Const.NAME_IN_TEMP].str.upper() == field_name.upper()][
Const.NAME_IN_DB].iloc[0]
return field_name
return None
def filter_by_type_value(self, relevant_scif, type_name, value):
"""
filters scif with the type and value
:param relevant_scif: current filtered scif
:param type_name: str (from the template)
:param value: str
:return: new scif
"""
if type_name == "":
return relevant_scif
values = value.split(', ')
new_type_name = self.get_column_name(type_name, relevant_scif)
if not new_type_name:
print "There is no field '{}'".format(type_name)
return relevant_scif
if new_type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[new_type_name].isin(values)]
@staticmethod
def exclude_scif(exclude_line, relevant_scif):
"""
filters products out of the scif
:param exclude_line: line from the exclusion sheet
:param relevant_scif: current filtered scif
:return: new scif
"""
if exclude_line[Const.PRODUCT_EAN] != "":
exclude_products = exclude_line[Const.PRODUCT_EAN].split(', ')
relevant_scif = relevant_scif[~(
relevant_scif['product_ean_code'].isin(exclude_products))]
if exclude_line[Const.BRAND] != "":
exclude_brands = exclude_line[Const.BRAND].split(', ')
relevant_scif = relevant_scif[~(
relevant_scif['brand_name'].isin(exclude_brands))]
return relevant_scif
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(", ")
return None
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SURVEY:
return self.calculate_survey_specific
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.SOS_MAJOR:
return self.calculate_sos_maj
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized"
.format(kpi_type))
return None
def choose_and_write_results(self):
"""
writes all the KPI in the DB: first the session's ones, second the scene's ones and in the end the ones
that depends on the previous ones. After all it writes the red score
"""
# self.scenes_results.to_csv('results/{}/scene {}.csv'.format(self.calculation_type, self.session_uid))####
# self.session_results.to_csv('results/{}/session {}.csv'.format(self.calculation_type, self.session_uid))####
main_template = self.templates[Const.KPIS]
self.write_session_kpis(main_template)
if self.calculation_type == Const.SOVI:
self.write_scene_kpis(main_template)
self.write_condition_kpis(main_template)
self.write_missings(main_template)
self.write_to_db(self.RED_SCORE, self.red_score)
# result_dict = {Const.KPI_NAME: 'RED SCORE', Const.SCORE: self.red_score}####
# self.all_results = self.all_results.append(result_dict, ignore_index=True)####
# self.all_results.to_csv('results/{}/{}.csv'.format(self.calculation_type, self.session_uid))####
def write_missings(self, main_template):
"""
write 0 in all the KPIs that didn't get score
:param main_template:
"""
for i, main_line in main_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
if not self.all_results[self.all_results[Const.KPI_NAME] ==
kpi_name].empty:
continue
result = 0
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_session_kpis(self, main_template):
"""
iterates all the session's KPIs and saves them
:param main_template: main_sheet.
"""
session_template = main_template[main_template[Const.CONDITION] == ""]
if self.calculation_type == Const.SOVI:
session_template = session_template[session_template[
Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
result = self.session_results[self.session_results[Const.KPI_NAME]
== kpi_name]
if result.empty:
continue
result = result.iloc[0][Const.RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_incremental_kpis(self, scene_template):
"""
lets the incremental KPIs choose their scenes (if they passed).
if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet
:return: the new template (without the KPI written already)
"""
incremental_template = scene_template[
scene_template[Const.INCREMENTAL] != ""]
while not incremental_template.empty:
for i, main_line in incremental_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.RESULT] > 0]
increments = main_line[Const.INCREMENTAL]
if ', ' in increments:
first_kpi = increments.split(', ')[0]
others = increments.replace(', '.format(first_kpi), '')
scene_template.loc[scene_template[Const.KPI_NAME] ==
first_kpi, Const.INCREMENTAL] = others
if true_results.empty:
scene_template.loc[scene_template[Const.KPI_NAME] ==
kpi_name, Const.INCREMENTAL] = ""
else:
true_results = true_results.sort_values(by=Const.RESULT,
ascending=False)
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = true_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(
kpi_name,
true_results.iloc[0][Const.RESULT],
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
scene_template = scene_template[~(
scene_template[Const.KPI_NAME] == kpi_name)]
incremental_template = scene_template[
scene_template[Const.INCREMENTAL] != ""]
return scene_template
def write_regular_scene_kpis(self, scene_template):
"""
lets the regular KPIs choose their scenes (if they passed).
Like in the incremental part - if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed incremental)
:return: the new template (without the KPI written already)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.RESULT] > 0]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if true_results.empty:
continue
true_results = true_results.sort_values(by=Const.RESULT,
ascending=False)
scene_fk = true_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(kpi_name,
true_results.iloc[0][Const.RESULT],
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
scene_template = scene_template[~(
scene_template[Const.KPI_NAME] == kpi_name)]
return scene_template
def write_not_passed_scene_kpis(self, scene_template):
"""
lets the KPIs not passed choose their scenes.
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed KPIs)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
if not reuse_scene:
kpi_results = kpi_results[~(
kpi_results[Const.SCENE_FK].isin(self.used_scenes))]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if kpi_results.empty:
continue
scene_fk = kpi_results.iloc[0][Const.SCENE_FK]
self.write_to_all_levels(kpi_name,
0,
display_text,
weight,
scene_fk=scene_fk,
reuse_scene=reuse_scene)
def write_scene_kpis(self, main_template):
"""
iterates every scene_kpi that does not depend on others, and choose the scene they will take:
1. the incrementals take their scene (if they passed).
2. the regular KPIs that passed choose their scenes.
3. the ones that didn't pass choose their random scenes.
:param main_template: main_sheet.
"""
scene_template = main_template[
(main_template[Const.SESSION_LEVEL] != Const.V)
& (main_template[Const.CONDITION] == "")]
scene_template = self.write_incremental_kpis(scene_template)
scene_template = self.write_regular_scene_kpis(scene_template)
self.write_not_passed_scene_kpis(scene_template)
def write_condition_kpis(self, main_template):
"""
writes all the KPI that depend on other KPIs by checking if the parent KPI has passed and in which scene.
:param main_template: main_sheet
"""
condition_template = main_template[
main_template[Const.CONDITION] != '']
for i, main_line in condition_template.iterrows():
condition = main_line[Const.CONDITION]
kpi_name = main_line[Const.KPI_NAME]
if self.calculation_type == Const.MANUAL or main_line[
Const.SESSION_LEVEL] == Const.V:
kpi_results = self.session_results[self.session_results[
Const.KPI_NAME] == kpi_name]
else:
kpi_results = self.scenes_results[self.scenes_results[
Const.KPI_NAME] == kpi_name]
condition_result = self.all_results[
(self.all_results[Const.KPI_NAME] == condition)
& (self.all_results[Const.RESULT] > 0)]
if condition_result.empty:
continue
condition_result = condition_result.iloc[0]
condition_scene = condition_result[Const.SCENE_FK]
if condition_scene and Const.SCENE_FK in kpi_results:
results = kpi_results[kpi_results[Const.SCENE_FK] ==
condition_scene]
else:
results = kpi_results
if results.empty:
continue
result = results.iloc[0][Const.RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = results.iloc[0][
Const.SCENE_FK] if Const.SCENE_FK in kpi_results else None
self.write_to_all_levels(kpi_name,
result,
display_text,
weight,
scene_fk=scene_fk)
def get_united_scenes(self):
return self.scif[self.scif['United Deliver'] ==
'Y']['scene_id'].unique().tolist()
def get_weight_factor(self):
sum_weights = self.templates[Const.KPIS][Const.WEIGHT].sum()
return sum_weights / 100.0
def get_score(self, weight):
return weight / self.weight_factor
def write_to_db(self, kpi_name, score, display_text=''):
"""
writes result in the DB
:param kpi_name: str
:param score: float
:param display_text: str
"""
if kpi_name == self.RED_SCORE:
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
self.RED_SCORE, 1),
score=score,
level=1)
if self.common_db_integ:
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(
self.RED_SCORE_INTEG, 1),
score=score,
level=1,
set_type=Const.MANUAL)
else:
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 2),
score=score,
level=2)
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 3),
score=score,
level=3,
display_text=display_text)
if self.common_db_integ:
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(kpi_name, 3),
score=score,
level=3,
display_text=kpi_name,
set_type=Const.MANUAL)
def write_to_db_result(self,
fk,
level,
score,
set_type=Const.SOVI,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk,
level=level,
set_type=set_type,
**kwargs)
else:
attributes = self.create_attributes_dict(fk=fk,
score=score,
set_type=set_type,
level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
if set_type == Const.SOVI:
self.common_db.kpi_results_queries.append(query)
else:
self.common_db_integ.kpi_results_queries.append(query)
def create_attributes_dict(self,
score,
fk=None,
level=None,
display_text=None,
set_type=Const.SOVI,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0].replace(
"'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id,
self.visit_date.isoformat(), fk, kpi_name, score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.common_db.LEVEL3:
if kwargs:
kwargs['score'] = score
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk']
== fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name,
self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'atomic_kpi_fk'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
if self.common_db_integ:
self.common_db_integ.delete_results_data_by_kpi_set()
self.common_db_integ.commit_results_data_without_delete()
class GSKNZToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.set_up_template = pd.read_excel(os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'gsk_set_up.xlsx'),
sheet_name='Functional KPIs',
keep_default_na=False)
self.gsk_generator = GSKGenerator(self.data_provider, self.output,
self.common, self.set_up_template)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
assortment_store_dict = self.gsk_generator.availability_store_function(
)
self.common.save_json_to_new_tables(assortment_store_dict)
assortment_category_dict = self.gsk_generator.availability_category_function(
)
self.common.save_json_to_new_tables(assortment_category_dict)
assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function(
)
self.common.save_json_to_new_tables(assortment_subcategory_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_whole_store_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_by_sub_category_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_by_category_function(
)
self.common.save_json_to_new_tables(facings_sos_dict)
linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function(
)
self.common.save_json_to_new_tables(linear_sos_dict)
self.common.commit_results_data()
return
class CMAToolBox:
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
CONTAIN_FILTER = 2
def __init__(self, data_provider, output, common_db2):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.manufacturer_fk = 1
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.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.united_scenes = self.get_united_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, SUB_PROJECT)
self.common_db2 = common_db2
self.result_values = self.ps_data_provider.get_result_values()
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_14'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES:
self.store_type = STORE_TYPES[self.store_type]
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.total_score = 0
self.total_count = 0
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheetname=sheet).fillna('')
self.tools = Shared(self.data_provider, self.output)
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.region in Const.REGIONS:
for i, main_line in main_template.iterrows():
store_type = self.does_exist(main_line, Const.STORE_TYPE)
if store_type is None or self.store_type in self.does_exist(
main_line, Const.STORE_TYPE):
self.calculate_main_kpi(main_line)
kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(SUB_PROJECT)
result = 0
if self.total_count:
result = self.total_score * 100.0 / self.total_count
self.common_db2.write_to_db_result(
fk=kpi_fk,
result=result,
numerator_result=self.total_score,
numerator_id=self.manufacturer_fk,
denominator_result=self.total_count,
denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(
parent_name=SUB_PROJECT))
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
SUB_PROJECT, 1),
score=self.total_score,
level=1)
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.TYPE]
relevant_scif = self.scif[self.scif['scene_id'].isin(
self.united_scenes)]
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
result = score = target = None
general_filters = {}
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
general_filters['template_name'] = scene_types
scene_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(
scene_groups)]
general_filters['template_group'] = scene_groups
if kpi_type == Const.SOS:
isnt_dp = True if self.store_attr != Const.DP and main_line[
Const.STORE_ATTRIBUTE] == Const.DP else False
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[
Const.KPI_NAME] == kpi_name]
kpi_function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score, target = kpi_function(kpi_line, relevant_scif,
isnt_dp, general_filters)
else:
pass
self.total_count += 1
if score > 0:
self.total_score += 1
if isinstance(result, tuple):
self.write_to_all_levels(kpi_name=kpi_name,
result=result[0],
score=score,
target=target,
num=result[1],
den=result[2])
else:
self.write_to_all_levels(kpi_name=kpi_name,
result=result,
score=score,
target=target)
# write in DF:
def write_to_all_levels(self,
kpi_name,
result,
score,
target=None,
num=None,
den=None):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
# result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result, Const.SCORE: score, Const.THRESHOLD: target}
# self.all_results = self.all_results.append(result_dict, ignore_index=True)
self.write_to_db(kpi_name,
score,
result=result,
target=target,
num=num,
den=den)
# survey:
def calculate_survey_specific(self,
kpi_line,
relevant_scif=None,
isnt_dp=None):
"""
returns a survey line if True or False
:param kpi_line: line from the survey sheet
:param relevant_scif:
:param isnt_dp:
:return: True or False - if the question gets the needed answer
"""
question = kpi_line[Const.Q_TEXT]
if not question:
question_id = kpi_line[Const.Q_ID]
if question_id == "":
Log.warning(
"The template has a survey question without ID or text")
return False
question = ('question_fk', int(question_id))
answers = kpi_line[Const.ACCEPTED_ANSWER].split(',')
min_answer = None if kpi_line[Const.REQUIRED_ANSWER] == '' else True
for answer in answers:
if self.survey.check_survey_answer(survey_text=question,
target_answer=answer,
min_required_answer=min_answer):
return True
return False
# availability:
def calculate_availability_with_same_pack(self, relevant_template,
relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI, AND if all of these filtered scif has
at least one common product that has the same size and number of sub_packages.
:param relevant_template: all the match lines from the availability sheet.
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:return: boolean
"""
packages = None
for i, kpi_line in relevant_template.iterrows():
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
continue
filtered_scif = self.filter_scif_availability(
kpi_line, relevant_scif)
filtered_scif = filtered_scif.fillna("NAN")
target = kpi_line[Const.TARGET]
sizes = filtered_scif['size'].tolist()
sub_packages_nums = filtered_scif['number_of_sub_packages'].tolist(
)
cur_packages = set(zip(sizes, sub_packages_nums))
if packages is None:
packages = cur_packages
else:
packages = cur_packages & packages
if len(packages) == 0:
return False
if filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() < target:
return False
if len(packages) > 1:
return False
return True
def calculate_availability(self, kpi_line, relevant_scif, isnt_dp):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param kpi_line: line from the availability sheet
:return: boolean
"""
if isnt_dp and kpi_line[Const.MANUFACTURER] in Const.DP_MANU:
return True
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
target = kpi_line[Const.TARGET]
return filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= target
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
def filter_scif_availability(self, kpi_line, relevant_scif):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:return:
"""
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.TRADEMARK: "att2",
Const.SIZE: "size",
Const.NUM_SUB_PACKAGES: "number_of_sub_packages",
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
# SOS:
def calculate_sos(self, kpi_line, relevant_scif, isnt_dp, general_filters):
"""
calculates SOS line in the relevant scif.
:param kpi_line: line from SOS sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1].split(',')
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1].split(',')
target = self.get_sos_targets(kpi_name)
general_filters[den_type] = den_value
if kpi_line[Const.DEN_TYPES_2]:
den_type_2 = kpi_line[Const.DEN_TYPES_2]
den_value_2 = kpi_line[Const.DEN_VALUES_2].split(',')
general_filters[den_type_2] = den_value_2
sos_filters = {num_type: num_value}
if isnt_dp:
sos_filters['manufacturer_name'] = (Const.DP_MANU, 0)
if kpi_line[Const.NUM_TYPES_2]:
num_type_2 = kpi_line[Const.NUM_TYPES_2]
num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',')
sos_filters[num_type_2] = num_value_2
num_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **sos_filters)]
den_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **general_filters)]
sos_value, num, den = self.tools.sos_with_num_and_dem(
kpi_line, num_scif, den_scif, self.facings_field)
# sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
# sos_value = round(sos_value, 2)
if target:
target *= 100
score = 1 if sos_value >= target else 0
else:
score = 0
target = 0
return (sos_value, num, den), score, target
# SOS majority:
def get_sos_targets(self, kpi_name):
targets_template = self.templates[Const.TARGETS]
store_targets = targets_template.loc[
(targets_template['program'] == self.program)
& (targets_template['channel'] == self.store_type)]
filtered_targets_to_kpi = store_targets.loc[
targets_template['KPI name'] == kpi_name]
if not filtered_targets_to_kpi.empty:
target = filtered_targets_to_kpi[Const.TARGET].values[0]
else:
target = None
return target
# return False
def calculate_sos_maj(self, kpi_line, relevant_scif, isnt_dp):
"""
calculates SOS majority line in the relevant scif. Filters the denominator and sends the line to the
match function (majority or dominant)
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator (and the denominator of the dominant part).
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
if kpi_line[Const.EXCLUSION_SHEET] == Const.V:
exclusion_sheet = self.templates[Const.SKU_EXCLUSION]
relevant_exclusions = exclusion_sheet[exclusion_sheet[
Const.KPI_NAME] == kpi_name]
for i, exc_line in relevant_exclusions.iterrows():
relevant_scif = self.exclude_scif(exc_line, relevant_scif)
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
den_type = kpi_line[Const.DEN_TYPES_2]
den_value = kpi_line[Const.DEN_VALUES_2]
relevant_scif = self.filter_by_type_value(relevant_scif, den_type,
den_value)
if kpi_line[Const.MAJ_DOM] == Const.MAJOR:
answer = self.calculate_majority_part(kpi_line, relevant_scif,
isnt_dp)
elif kpi_line[Const.MAJ_DOM] == Const.DOMINANT:
answer = self.calculate_dominant_part(kpi_line, relevant_scif,
isnt_dp)
else:
Log.warning("SOS majority does not know '{}' part".format(
kpi_line[Const.MAJ_DOM]))
answer = False
return answer
def calculate_majority_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the SOS is bigger than 50%.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1]
num_scif = self.filter_by_type_value(relevant_scif, num_type,
num_value)
num_type = kpi_line[Const.NUM_TYPES_2]
num_value = kpi_line[Const.NUM_VALUES_2]
num_scif = self.filter_by_type_value(num_scif, num_type, num_value)
if num_scif.empty:
return None
if isnt_dp:
num_scif = num_scif[~(
num_scif['manufacturer_name'].isin(Const.DP_MANU))]
target = Const.MAJORITY_TARGET
return num_scif['facings'].sum() / relevant_scif['facings'].sum(
) >= target
def calculate_dominant_part(self, kpi_line, relevant_scif, isnt_dp):
"""
filters the numerator and checks if the given value in the given type is the one with the most facings.
:param kpi_line: line from SOS majority sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out.
:return: boolean
"""
if isnt_dp:
relevant_scif = relevant_scif[~(
relevant_scif['manufacturer_name'].isin(Const.DP_MANU))]
type_name = self.get_column_name(kpi_line[Const.NUM_TYPES_1],
relevant_scif)
values = str(kpi_line[Const.NUM_VALUES_1]).split(', ')
if type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
max_facings, needed_one = 0, 0
values_type = relevant_scif[type_name].unique().tolist()
if None in values_type:
values_type.remove(None)
current_sum = relevant_scif[
relevant_scif[type_name].isnull()]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
for value in values_type:
current_sum = relevant_scif[relevant_scif[type_name] ==
value]['facings'].sum()
if current_sum > max_facings:
max_facings = current_sum
if value in values:
needed_one += current_sum
return needed_one >= max_facings
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', DIAGEOAUPNGAMERICAGENERALToolBox.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
# helpers:
@staticmethod
def get_column_name(field_name, df):
"""
checks what the real field name in DttFrame is (if it exists in the DF or exists in the "converter" sheet).
:param field_name: str
:param df: scif/products
:return: real column name (if exists)
"""
if field_name in df.columns:
return field_name
return None
def filter_by_type_value(self, relevant_scif, type_name, value):
"""
filters scif with the type and value
:param relevant_scif: current filtered scif
:param type_name: str (from the template)
:param value: str
:return: new scif
"""
if type_name == "":
return relevant_scif
values = value.split(', ')
new_type_name = self.get_column_name(type_name, relevant_scif)
if not new_type_name:
print "There is no field '{}'".format(type_name)
return relevant_scif
if new_type_name in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[new_type_name].isin(values)]
@staticmethod
def exclude_scif(exclude_line, relevant_scif):
"""
filters products out of the scif
:param exclude_line: line from the exclusion sheet
:param relevant_scif: current filtered scif
:return: new scif
"""
exclude_products = exclude_line[Const.PRODUCT_EAN].split(', ')
return relevant_scif[~(
relevant_scif['product_ean_code'].isin(exclude_products))]
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
if ", " in cell:
return cell.split(", ")
else:
return cell.split(',')
return None
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SURVEY:
return self.calculate_survey_specific
elif kpi_type == Const.AVAILABILITY:
return self.calculate_availability
elif kpi_type == Const.SOS:
return self.calculate_sos
elif kpi_type == Const.SOS_MAJOR:
return self.calculate_sos_maj
else:
Log.warning(
"The value '{}' in column sheet in the template is not recognized"
.format(kpi_type))
return None
def get_united_scenes(self):
return self.scif[self.scif['United Deliver'] ==
'Y']['scene_id'].unique().tolist()
def get_pks_of_result(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] ==
result]['pk'].iloc[0]
return pk
def write_to_db(self,
kpi_name,
score,
result=None,
target=None,
num=None,
den=None):
"""
writes result in the DB
:param kpi_name: str
:param score: float
:param result: str
:param target: int
"""
if target and score == 0:
delta = den * (target / 100) - num
else:
delta = 0
score_value = Const.PASS if score == 1 else Const.FAIL
score = self.get_pks_of_result(score_value)
kpi_fk = self.common_db2.get_kpi_fk_by_kpi_type(SUB_PROJECT + " " +
kpi_name)
self.common_db2.write_to_db_result(
fk=kpi_fk,
result=result,
score=score,
should_enter=True,
target=target,
numerator_result=num,
denominator_result=den,
weight=delta,
numerator_id=Const.MANUFACTURER_FK,
denominator_id=self.store_id,
identifier_parent=self.common_db2.get_dictionary(
parent_name=SUB_PROJECT))
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 2),
score=score,
level=2)
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
kpi_name, 3),
score=score,
level=3,
threshold=target,
result=result)
def write_to_db_result(self,
fk,
level,
score,
set_type=Const.SOVI,
**kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk,
level=level,
**kwargs)
else:
attributes = self.create_attributes_dict(fk=fk,
score=score,
level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
self.common_db.kpi_results_queries.append(query)
def create_attributes_dict(self,
score,
fk=None,
level=None,
display_text=None,
set_type=Const.SOVI,
**kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] ==
fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id,
self.visit_date.isoformat(), format(score, '.2f'), fk)],
columns=[
'kps_name', 'session_uid', 'store_fk', 'visit_date',
'score_1', 'kpi_set_fk'
])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] ==
fk]['kpi_name'].values[0].replace(
"'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id,
self.visit_date.isoformat(), fk, kpi_name, score)],
columns=[
'session_uid', 'store_fk', 'visit_date', 'kpi_fk',
'kpk_name', 'score'
])
elif level == self.common_db.LEVEL3:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk'] ==
fk]['kpi_set_name'].values[0]
display_text = data['kpi_name'].values[0]
if kwargs:
kwargs = self.add_additional_data_to_attributes(
kwargs, score, kpi_set_name, kpi_fk, fk,
datetime.utcnow().isoformat(), display_text)
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name,
self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=[
'display_text', 'session_uid', 'kps_name', 'store_fk',
'visit_date', 'calculation_time', 'score', 'kpi_fk',
'atomic_kpi_fk'
])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def add_additional_data_to_attributes(self, kwargs_dict, score,
kpi_set_name, kpi_fk, fk, calc_time,
display_text):
kwargs_dict['score'] = score
kwargs_dict['kps_name'] = kpi_set_name
kwargs_dict['kpi_fk'] = kpi_fk
kwargs_dict['atomic_kpi_fk'] = fk
kwargs_dict['calculation_time'] = calc_time
kwargs_dict['session_uid'] = self.session_uid
kwargs_dict['store_fk'] = self.store_id
kwargs_dict['visit_date'] = self.visit_date.isoformat()
kwargs_dict['display_text'] = display_text
return kwargs_dict
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
class INBEVTRADMXToolBox:
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.store_info = self.data_provider[Data.STORE_INFO]
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.templates_path = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data')
self.excel_file_path = os.path.join(self.templates_path,
'inbevtradmx_template.xlsx')
self.availability = Availability(self.data_provider)
self.survey_response = self.data_provider[Data.SURVEY_RESPONSES]
self.geo = GeoLocation.Geo(self.rds_conn, self.session_uid,
self.data_provider, self.kpi_static_data,
self.common)
def parse_template(self):
"""
convert excel file to data frame
:return: data frame
"""
template_df = pd.read_excel(self.excel_file_path, sheetname='template')
return template_df
def filter_product_names(self, exclude_skus):
"""
this method filters list of SKUs from self.scif
:param exclude_skus: list of SKUs to exclude from template
:return: filtered list
"""
return filter(lambda sku: sku not in exclude_skus,
self.scif.product_name.values)
def calculate_availability_score(self, row, relevant_columns):
"""
this method calculates availability score according to columns from the data frame
:param row: data frame to calculate from
:param relevant_columns: columns to check in the excel file
:return: boolean
"""
# create filtered dictionary
filters_dict = self.create_availability_filtered_dictionary(
relevant_columns, row)
# call the generic method from KPIUtils_v2
availability_score = self.availability.calculate_availability(
**filters_dict)
# check if this score should pass or fail
return self.decide_availability_score(row, availability_score)
def create_availability_filtered_dictionary(self, relevant_columns, row):
"""
this method creates a dictionary with keys according to the specific row in the template
:param relevant_columns: columns to create keys by
:param row: the specific row in the template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'store Additional Attribute 4' or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def handle_exclude_skus(self, filters_dict, relevant_columns, row):
"""
this method checks if there is value in 'exclude skus' column in the template.
if exists, it filters out the relevant skus from the calculation
:param filters_dict: filtered dictionary
:param relevant_columns: columns to create keys by
:param row: specific row to calculate
:return: None
"""
try:
exclude_skus = row['exclude skus'].split(',')
except AttributeError:
exclude_skus = []
if exclude_skus:
# filter out some product names according to template
product_names = self.filter_product_names(exclude_skus)
filters_dict['product_name'] = product_names
relevant_columns.remove('exclude skus')
@staticmethod
def decide_availability_score(row, availability_score):
"""
this method decides if the score should pass or fail according to the template
:param row: scpecific row from template
:param availability_score: score
:return: Boolean
"""
if availability_score == 0:
return False
else:
if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[
'KPI Level 3 Name'] == 'Hay o no hay Pop?':
if row['KPI Level 2 Name'] == 'Pop Exterior':
return availability_score > 1
elif row['KPI Level 2 Name'] == 'Pop Interior':
return availability_score > 2
else:
return True
def calculate_sos_score(self, row, relevant_columns):
"""
this method calculates share of shelf score according to columns from the data frame
:param relevant_columns: columns to check in the excel file
:param row: data frame to calculate from
:return: share of shelf score
"""
# get df only with the correct template name
df = self.scif[self.scif.template_name == row['template_name']]
# sum of all the facings in df
facings = df.facings.values.sum()
if facings == 0:
return 0
# create dictionary for calculating
filters_dict = self.create_sos_filtered_dictionary(
relevant_columns, row)
# reduce the df only to relevant columns
df = df[filters_dict.keys()]
ratio = self.calculate_sos_ratio(df, filters_dict)
return ratio / facings
def calculate_sos_ratio(self, df, filters_dict):
ratio = 0
# iterate the data frame
for i, df_row in df.iterrows():
# initialize the boolean variable
bol = True
# iterate the filtered dictionary keys
for key in filters_dict.keys():
# check if the value in df row in in "key" column is in the filtered dictionary we created before
bol &= df_row[key] in filters_dict[key]
# that means success of the inner loop, that all the values matching for this data frame row
if bol:
# accumulate ratio
ratio = ratio + self.scif.facings.loc[i]
return ratio
def create_sos_filtered_dictionary(self, relevant_columns, row):
"""
this method filters out relevant columns from the template fso we can calculate SOS easily
:param relevant_columns: relevant columns from temolates
:param row: specific row from template
:return: dictionary
"""
# dictionary to send to the generic method
filters_dict = {}
self.handle_exclude_skus(filters_dict, relevant_columns, row)
# fill the dictionary
for column_value in relevant_columns:
if column_value == 'Store Additional Attribute 4' or column_value == 'store_type':
continue
filters_dict[column_value] = map(
str.strip,
str(row.loc[column_value]).split(','))
return filters_dict
def calculate_survey_score(self, row):
"""
this method calculates survey score according to columns from the data frame
:param row: data frame to calculate from
:return: boolean
"""
question_code = str(int(row['Survey Question Code']))
if not self.survey_response.empty and \
not self.survey_response[self.survey_response.code == question_code].empty:
answer = self.survey_response.selected_option_text[
self.survey_response.code == question_code].values[0]
if answer == 'Si':
return True
else:
if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador':
return True
return False
else:
return False
def check_store_type(self, row, relevant_columns):
"""
this method checks if the session store type is valid
:type relevant_columns: list
:param row: current KPI row
:return: boolean
"""
# make sure that we need to check this field
if relevant_columns.__contains__('store_type'):
# get the valid stores from the template
stores_df = pd.read_excel(self.excel_file_path,
sheetname='store types')
# create a list of the valid stores
stores = stores_df.values
return row['store_type'] in stores
else:
return True
def calculate_set_score(self, set_df, set_name):
"""
this method iterates kpi set and calculates it's score
:param set_df: the set df to calculate score for
:param set_name: the kpi set name
:return: None
"""
set_score = 0
# get array of all kpi level 2 names
kpi_names = set_df['KPI Level 2 Name'].unique()
# iterate all kpi level 2
for kpi_name in kpi_names:
# calculate kpi level 2 score
kpi_score = self.calculate_kpi_level_2_score(
kpi_name, set_df, set_name)
# write kpi level 2 score to DB
self.write_kpi_score_to_db(kpi_name, set_name, kpi_score)
# accumulate set score
set_score += kpi_score
# finally, write level 1 kpi set score to DB
self.write_kpi_set_score_to_db(set_name, set_score)
def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name):
"""
this method gets kpi level 2 name, and iterates it's related atomic kpis
:param set_name: kpi set name
:param kpi_name: kpi level 2 name
:param set_df: kpi set df
:return: kpi level 2 score
"""
kpi_df = set_df[set_df['KPI Level 2 Name'] == kpi_name]
kpi_score = 0
# iterate the all related atomic kpis
for i, row in kpi_df.iterrows():
# get atomic kpi name
kpi_level_3_name = row['KPI Level 3 Name']
# calculate atomic kpi score
atomic_kpi_score = self.calculate_atomic_kpi_score(
row, kpi_level_3_name, kpi_name, set_name)
# accumulate kpi level 2 score
kpi_score += atomic_kpi_score
return kpi_score
def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name,
set_name):
"""
this method calculates score for specific atomic kpi
:param set_name: kpi set name
:param kpi_name: kpi name
:param kpi_level_3_name: the atomic kpi name
:param row: atomic kpi details
:return: atomic kpi score
"""
atomic_kpi_score = 0
# get column name to consider in calculation
relevant_columns = map(str.strip, str(row['column names']).split(','))
if self.check_store_type(row, relevant_columns):
# get weight of current atomic kpi
curr_weight = row['weights']
# figure out what type of calculation need to be done
if row['KPI type'] == 'Product Availability':
if self.calculate_availability_score(row, relevant_columns):
atomic_kpi_score += curr_weight
elif row['KPI type'] == 'SOS':
ratio = self.calculate_sos_score(row, relevant_columns)
if (row['product_type'] == 'Empty') & (ratio <= 0.2):
atomic_kpi_score += curr_weight
elif ratio == 1:
atomic_kpi_score += curr_weight
elif row['KPI type'] == 'Survey':
if self.calculate_survey_score(row):
atomic_kpi_score += curr_weight
# write result to DB
self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score, kpi_name,
set_name)
return atomic_kpi_score
def write_kpi_set_score_to_db(self, set_name, set_score):
"""
this method writes set kpi score to static.kps_results DB
:param set_name: set name
:param set_score: set score
:return: None
"""
kpi_set_fk = self.kpi_static_data.kpi_set_fk[
self.kpi_static_data.kpi_set_name == set_name].unique()[0]
self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score)
def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score):
"""
this method writes kpi score to static.kpk_results DB
:param kpi_name: name of level 2 kpi
:param set_name: name of related set
:param kpi_score: the score
:return: None
"""
kpi_fk = \
self.kpi_static_data.kpi_fk[(self.kpi_static_data.kpi_name == kpi_name) &
(self.kpi_static_data.kpi_set_name == set_name)].values[0]
self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score)
def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name,
set_name):
"""
this method writes atomic kpi score to static.kpi_results DB
:param atomic_name: atomic kpi name
:param atomic_score: the score
:param kpi_name: name of related kpi
:param set_name: name of related set
:return:
"""
atomic_kpi_fk = \
self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) &
(self.kpi_static_data.kpi_name == kpi_name) &
(self.kpi_static_data.kpi_set_name == set_name)].values[0]
self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3,
atomic_score)
def calculate_kpi_set_from_template(self):
"""
this method chooses the correct set to calculate
there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from
template
:return: None
"""
# get the template
parsed_template = self.parse_template()
# get all the unique sets
sets = parsed_template['KPI Level 1 Name'].unique()
# get the session additional_attribute_4
additional_attribute_4 = self.store_info.additional_attribute_4.values[
0]
set_name = self.choose_correct_set_to_calculate(
additional_attribute_4, sets)
# wrong value in additional attribute 4 - shouldn't calculate
if set_name == '':
Log.warning(
'Wrong value in additional attribute 4 - shouldnt calculate')
return -1
# get only the part of the template that is related to this set
set_template_df = parsed_template[parsed_template['KPI Level 1 Name']
== set_name]
# start calculating !
self.calculate_set_score(set_template_df, set_name)
@staticmethod
def choose_correct_set_to_calculate(additional_attribute_4, sets):
"""
choose what is the appropriate set to calculate
:param additional_attribute_4: session additional_attribute_4
:param sets: list of optional sets
:return: set name to calculate
"""
if additional_attribute_4 == 'BC':
set_name = sets[0]
elif additional_attribute_4 == 'BA':
set_name = sets[1]
elif additional_attribute_4 == 'MODELORAMA':
set_name = sets[2]
else:
return ''
return set_name
def main_calculation(self):
# calculate geo
geo_result = self.geo.calculate_geo_location()
self.geo.write_geo_to_db(float(geo_result))
# calculate from template
self.calculate_kpi_set_from_template()
self.common.commit_results_data()
class ALTRIAUS_SANDToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.common_v2 = CommonV2(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.template_info = self.data_provider.all_templates
self.rds_conn = ProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.ps_data_provider = PsDataProvider(self.data_provider)
self.thresholds_and_results = {}
self.result_df = []
self.writing_to_db_time = datetime.timedelta(0)
self.kpi_results_queries = []
self.potential_products = {}
self.shelf_square_boundaries = {}
self.average_shelf_values = {}
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.all_template_data = parse_template(TEMPLATE_PATH, "KPI")
self.spacing_template_data = parse_template(TEMPLATE_PATH, "Spacing")
self.fixture_width_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Fixture Width",
dtype=pd.Int64Dtype())
self.facings_to_feet_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Conversion Table",
dtype=pd.Int64Dtype())
self.header_positions_template = pd.read_excel(FIXTURE_WIDTH_TEMPLATE,
"Header Positions")
self.flip_sign_positions_template = pd.read_excel(
FIXTURE_WIDTH_TEMPLATE, "Flip Sign Positions")
self.custom_entity_data = self.ps_data_provider.get_custom_entities(
1005)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
self.INCLUDE_FILTER = 1
self.assortment = Assortment(self.data_provider,
output=self.output,
ps_data_provider=self.ps_data_provider)
self.store_assortment = self.assortment.get_lvl3_relevant_ass()
self.kpi_new_static_data = self.common.get_new_kpi_static_data()
try:
self.mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \
.merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \
.merge(self.template_info, on='template_fk', suffixes=['', '_t'])
except KeyError:
Log.warning('MPIS cannot be generated!')
return
self.adp = AltriaDataProvider(self.data_provider)
def main_calculation(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
self.calculate_signage_locations_and_widths('Cigarettes')
self.calculate_signage_locations_and_widths('Smokeless')
self.calculate_register_type()
self.calculate_age_verification()
self.calculate_juul_availability()
self.calculate_assortment()
self.calculate_vapor_kpis()
kpi_set_fk = 2
set_name = \
self.kpi_static_data.loc[self.kpi_static_data['kpi_set_fk'] == kpi_set_fk]['kpi_set_name'].values[0]
template_data = self.all_template_data.loc[
self.all_template_data['KPI Level 1 Name'] == set_name]
try:
if set_name and not set(
template_data['Scene Types to Include'].values[0].encode(
).split(', ')) & set(
self.scif['template_name'].unique().tolist()):
Log.info('Category {} was not captured'.format(
template_data['category'].values[0]))
return
except Exception as e:
Log.info(
'KPI Set {} is not defined in the template'.format(set_name))
for i, row in template_data.iterrows():
try:
kpi_name = row['KPI Level 2 Name']
if kpi_name in KPI_LEVEL_2_cat_space:
# scene_type = [s for s in row['Scene_Type'].encode().split(', ')]
kpi_type = row['KPI Type']
scene_type = row['scene_type']
if row['Param1'] == 'Category' or 'sub_category':
category = row['Value1']
if kpi_type == 'category_space':
kpi_set_fk = \
self.kpi_new_static_data.loc[self.kpi_new_static_data['type'] == kpi_type]['pk'].values[0]
self.calculate_category_space(
kpi_set_fk,
kpi_name,
category,
scene_types=scene_type)
except Exception as e:
Log.info('KPI {} calculation failed due to {}'.format(
kpi_name.encode('utf-8'), e))
continue
return
def calculate_vapor_kpis(self):
category = 'Vapor'
relevant_scif = self.scif[self.scif['template_name'] ==
'JUUL Merchandising']
if relevant_scif.empty:
Log.info('No products found for {} category'.format(category))
return
relevant_scif = relevant_scif[
(relevant_scif['category'].isin([category, 'POS']))
& (relevant_scif['brand_name'] == 'Juul')]
if relevant_scif.empty:
return
relevant_product_pks = relevant_scif[
relevant_scif['product_type'] ==
'SKU']['product_fk'].unique().tolist()
relevant_scene_id = self.get_most_frequent_scene(relevant_scif)
product_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_product_pks))
& (self.mpis['scene_fk'] == relevant_scene_id)]
if product_mpis.empty:
Log.info('No products found for {} category'.format(category))
return
self.calculate_total_shelves(product_mpis, category, product_mpis)
longest_shelf = \
product_mpis[product_mpis['shelf_number'] ==
self.get_longest_shelf_number(product_mpis,
max_shelves_from_top=999)].sort_values(by='rect_x',
ascending=True)
if longest_shelf.empty or longest_shelf.isnull().all().all():
Log.warning(
'The {} category items are in a non-standard location. The {} category will not be calculated.'
.format(category, category))
return
relevant_pos = pd.DataFrame()
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
def calculate_assortment(self):
if self.scif.empty or self.store_assortment.empty:
Log.warning(
'Unable to calculate assortment: SCIF or store assortment is empty'
)
return
grouped_scif = self.scif.groupby('product_fk',
as_index=False)['facings'].sum()
assortment_with_facings = \
pd.merge(self.store_assortment, grouped_scif, how='left', on='product_fk')
assortment_with_facings.loc[:, 'facings'] = assortment_with_facings[
'facings'].fillna(0)
for product in assortment_with_facings.itertuples():
score = 1 if product.facings > 0 else 0
self.common_v2.write_to_db_result(
product.kpi_fk_lvl3,
numerator_id=product.product_fk,
denominator_id=product.assortment_fk,
numerator_result=product.facings,
result=product.facings,
score=score)
number_of_skus_present = len(
assortment_with_facings[assortment_with_facings['facings'] > 0])
score = 1 if number_of_skus_present > 0 else 0
kpi_fk = assortment_with_facings['kpi_fk_lvl2'].iloc[0]
assortment_group_fk = assortment_with_facings[
'assortment_group_fk'].iloc[0]
self.common_v2.write_to_db_result(
kpi_fk,
numerator_id=assortment_group_fk,
numerator_result=number_of_skus_present,
denominator_result=len(assortment_with_facings),
result=number_of_skus_present,
score=score)
def calculate_register_type(self):
relevant_scif = self.scif[
(self.scif['product_type'].isin(['POS', 'Other']))
& (self.scif['category'] == 'POS Machinery')]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Register Type')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_age_verification(self):
relevant_scif = self.scif[self.scif['brand_name'].isin(
['Age Verification'])]
if relevant_scif.empty:
result = 0
product_fk = 0
else:
result = 1
product_fk = relevant_scif['product_fk'].iloc[0]
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Age Verification')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_juul_availability(self):
relevant_scif = self.scif[(self.scif['brand_name'].isin(['Juul']))
& (self.scif['product_type'].isin(['POS']))]
juul_pos = relevant_scif['product_fk'].unique().tolist()
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Juul POS Availability')
if not juul_pos:
return
result = 1
for product_fk in juul_pos:
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=product_fk,
denominator_id=self.store_id,
result=result)
def calculate_category_space(self,
kpi_set_fk,
kpi_name,
category,
scene_types=None):
template = self.all_template_data.loc[
(self.all_template_data['KPI Level 2 Name'] == kpi_name)
& (self.all_template_data['Value1'] == category)]
kpi_template = template.loc[template['KPI Level 2 Name'] == kpi_name]
if kpi_template.empty:
return None
kpi_template = kpi_template.iloc[0]
values_to_check = []
filters = {
'template_name': scene_types,
'category': kpi_template['Value1']
}
if kpi_template['Value1'] in CATEGORIES:
category_att = 'category'
if kpi_template['Value1']:
values_to_check = self.all_products.loc[
self.all_products[category_att] ==
kpi_template['Value1']][category_att].unique().tolist()
for primary_filter in values_to_check:
filters[kpi_template['Param1']] = primary_filter
new_kpi_name = self.kpi_name_builder(kpi_name, **filters)
result = self.calculate_category_space_length(
new_kpi_name, **filters)
filters['Category'] = kpi_template['KPI Level 2 Name']
score = result
numerator_id = self.products['category_fk'][
self.products['category'] == kpi_template['Value1']].iloc[0]
self.common_v2.write_to_db_result(kpi_set_fk,
numerator_id=numerator_id,
numerator_result=999,
result=score,
score=score)
def calculate_category_space_length(self,
kpi_name,
threshold=0.5,
retailer=None,
exclude_pl=False,
**filters):
"""
:param threshold: The ratio for a bay to be counted as part of a category.
:param filters: These are the parameters which the data frame is filtered by.
:return: The total shelf width (in mm) the relevant facings occupy.
"""
try:
filtered_scif = self.scif[self.get_filter_condition(
self.scif, **filters)]
space_length = 0
bay_values = []
for scene in filtered_scif['scene_fk'].unique().tolist():
scene_matches = self.mpis[self.mpis['scene_fk'] == scene]
scene_filters = filters
scene_filters['scene_fk'] = scene
for bay in scene_matches['bay_number'].unique().tolist():
bay_total_linear = scene_matches.loc[
(scene_matches['bay_number'] == bay)
& (scene_matches['stacking_layer'] == 1) &
(scene_matches['status']
== 1)]['width_mm_advance'].sum()
scene_filters['bay_number'] = bay
tested_group_linear = scene_matches[
self.get_filter_condition(scene_matches,
**scene_filters)]
tested_group_linear_value = tested_group_linear[
'width_mm_advance'].sum()
if tested_group_linear_value:
bay_ratio = tested_group_linear_value / float(
bay_total_linear)
else:
bay_ratio = 0
if bay_ratio >= threshold:
category = filters['category']
max_facing = scene_matches.loc[
(scene_matches['bay_number'] == bay)
& (scene_matches['stacking_layer'] == 1
)]['facing_sequence_number'].max()
shelf_length = self.spacing_template_data.query(
'Category == "' + category + '" & Low <= "' +
str(max_facing) + '" & High >= "' +
str(max_facing) + '"')
shelf_length = int(shelf_length['Size'].iloc[-1])
bay_values.append(shelf_length)
space_length += shelf_length
except Exception as e:
Log.info('Linear Feet calculation failed due to {}'.format(e))
space_length = 0
return space_length
def get_filter_condition(self, df, **filters):
"""
:param df: The data frame to be filters.
:param filters: These are the parameters which the data frame is filtered by.
Every parameter would be a tuple of the value and an include/exclude flag.
INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUPNGROGENERALToolBox.INCLUDE_FILTER)
INPUT EXAMPLE (2): manufacturer_name = 'Diageo'
:return: a filtered Scene Item Facts data frame.
"""
if not filters:
return df['pk'].apply(bool)
if self.facings_field in df.keys():
filter_condition = (df[self.facings_field] > 0)
else:
filter_condition = None
for field in filters.keys():
if field in df.keys():
if isinstance(filters[field], tuple):
value, exclude_or_include = filters[field]
else:
value, exclude_or_include = filters[
field], self.INCLUDE_FILTER
if not value:
continue
if not isinstance(value, list):
value = [value]
if exclude_or_include == self.INCLUDE_FILTER:
condition = (df[field].isin(value))
elif exclude_or_include == self.EXCLUDE_FILTER:
condition = (~df[field].isin(value))
elif exclude_or_include == self.CONTAIN_FILTER:
condition = (df[field].str.contains(value[0], regex=False))
for v in value[1:]:
condition |= df[field].str.contains(v, regex=False)
else:
continue
if filter_condition is None:
filter_condition = condition
else:
filter_condition &= condition
else:
Log.warning('field {} is not in the Data Frame'.format(field))
return filter_condition
def kpi_name_builder(self, kpi_name, **filters):
"""
This function builds kpi name according to naming convention
"""
for filter in filters.keys():
if filter == 'template_name':
continue
kpi_name = kpi_name.replace('{' + filter + '}',
str(filters[filter]))
kpi_name = kpi_name.replace("'", "\'")
return kpi_name
def calculate_signage_locations_and_widths(self, category):
excluded_types = ['Other', 'Irrelevant', 'Empty']
relevant_scif = self.scif[self.scif['template_name'] ==
'Tobacco Merchandising Space']
if relevant_scif.empty:
Log.info('No products found for {} category'.format(category))
return
# need to include scene_id from previous relevant_scif
# also need to split this shit up into different categories, i.e. smokeless, cigarettes
# need to figure out how to deal with POS from smokeless being included with cigarette MPIS
# get relevant SKUs from the cigarettes category
relevant_scif = relevant_scif[relevant_scif['category'].isin(
[category, 'POS'])]
relevant_product_pks = relevant_scif[
relevant_scif['product_type'] ==
'SKU']['product_fk'].unique().tolist()
relevant_pos_pks = \
relevant_scif[(relevant_scif['product_type'] == 'POS') &
~(relevant_scif['brand_name'] == 'Age Verification') &
~(relevant_scif['product_name'] == 'General POS Other')]['product_fk'].unique().tolist()
other_product_and_pos_pks = \
relevant_scif[relevant_scif['product_type'].isin(excluded_types)]['product_fk'].tolist()
relevant_scene_id = self.get_most_frequent_scene(relevant_scif)
product_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_product_pks))
& (self.mpis['scene_fk'] == relevant_scene_id)]
if product_mpis.empty:
Log.info('No products found for {} category'.format(category))
return
self.calculate_total_shelves(product_mpis, category)
longest_shelf = \
product_mpis[product_mpis['shelf_number'] ==
self.get_longest_shelf_number(product_mpis)].sort_values(by='rect_x', ascending=True)
if longest_shelf.empty or longest_shelf.isnull().all().all():
Log.warning(
'The {} category items are in a non-standard location. The {} category will not be calculated.'
.format(category, category))
return
# demarcation_line = longest_shelf['rect_y'].median() old method, had bugs due to longest shelf being lower
demarcation_line = product_mpis['rect_y'].min()
exclusion_line = -9999
excluded_mpis = self.mpis[
~(self.mpis['product_fk'].isin(relevant_pos_pks +
relevant_product_pks +
other_product_and_pos_pks))
& (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) &
(self.mpis['rect_x'] > longest_shelf['rect_x'].min()) &
(self.mpis['scene_fk'] == relevant_scene_id) &
(self.mpis['rect_y'] < demarcation_line)]
# we need this line for when SCIF and MPIS don't match
excluded_mpis = excluded_mpis[~excluded_mpis['product_type'].
isin(excluded_types)]
if not excluded_mpis.empty:
exclusion_line = excluded_mpis['rect_y'].max()
# we need to get POS stuff that falls within the x-range of the longest shelf (which is limited by category)
# we also need to account for the fact that the images suck, so we're going to add/subtract 5% of the
# max/min values to allow for POS items that fall slightly out of the shelf length range
correction_factor = 0.05
correction_value = (longest_shelf['rect_x'].max() -
longest_shelf['rect_x'].min()) * correction_factor
pos_mpis = self.mpis[
(self.mpis['product_fk'].isin(relevant_pos_pks))
& (self.mpis['rect_x'] <
(longest_shelf['rect_x'].max() + correction_value)) &
(self.mpis['rect_x'] >
(longest_shelf['rect_x'].min() - correction_value)) &
(self.mpis['scene_fk'] == relevant_scene_id)]
# DO NOT SET TO TRUE WHEN DEPLOYING
# debug flag displays polygon_mask graph DO NOT SET TO TRUE WHEN DEPLOYING
# DO NOT SET TO TRUE WHEN DEPLOYING
relevant_pos = self.adp.get_products_contained_in_displays(
pos_mpis, y_axis_threshold=35, debug=False)
if relevant_pos.empty:
Log.warning(
'No polygon mask was generated for {} category - cannot compute KPIs'
.format(category))
# we need to attempt to calculate fixture width, even if there's no polygon mask
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
relevant_pos = relevant_pos[[
'product_fk', 'product_name', 'left_bound', 'right_bound',
'center_x', 'center_y'
]]
relevant_pos = relevant_pos.reindex(
columns=relevant_pos.columns.tolist() +
['type', 'width', 'position'])
relevant_pos['width'] = \
relevant_pos.apply(lambda row: self.get_length_of_pos(row, longest_shelf, category), axis=1)
relevant_pos['type'] = \
relevant_pos['center_y'].apply(lambda x: 'Header' if exclusion_line < x < demarcation_line else 'Flip Sign')
relevant_pos = relevant_pos.sort_values(['center_x'], ascending=True)
relevant_pos = self.remove_duplicate_pos_tags(relevant_pos)
# generate header positions
if category == 'Cigarettes':
number_of_headers = len(
relevant_pos[relevant_pos['type'] == 'Header'])
if number_of_headers > len(
self.header_positions_template['Cigarettes Positions'].
dropna()):
Log.warning(
'Number of Headers for Cigarettes is greater than max number defined in template!'
)
elif number_of_headers > 0:
header_position_list = [
position.strip()
for position in self.header_positions_template[
self.header_positions_template['Number of Headers'] ==
number_of_headers]
['Cigarettes Positions'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Header',
['position']] = header_position_list
elif category == 'Smokeless':
relevant_pos = self.check_menu_scene_recognition(relevant_pos)
number_of_headers = len(
relevant_pos[relevant_pos['type'] == 'Header'])
if number_of_headers > len(
self.header_positions_template['Smokeless Positions'].
dropna()):
Log.warning(
'Number of Headers for Smokeless is greater than max number defined in template!'
)
elif number_of_headers > 0:
header_position_list = [
position.strip()
for position in self.header_positions_template[
self.header_positions_template['Number of Headers'] ==
number_of_headers]
['Smokeless Positions'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Header',
['position']] = header_position_list
relevant_pos = self.get_menu_board_items(relevant_pos,
longest_shelf, pos_mpis)
# generate flip-sign positions
if category == 'Cigarettes':
relevant_template = \
self.fixture_width_template.loc[(self.fixture_width_template['Fixture Width (facings)']
- len(longest_shelf)).abs().argsort()[:1]].dropna(axis=1)
locations = relevant_template.columns[2:].tolist()
right_bound = 0
longest_shelf_copy = longest_shelf.copy()
for location in locations:
if right_bound > 0:
left_bound = right_bound + 1
else:
left_bound = longest_shelf_copy.iloc[:relevant_template[
location].iloc[0]]['rect_x'].min()
right_bound = longest_shelf_copy.iloc[:relevant_template[
location].iloc[0]]['rect_x'].max()
if locations[-1] == location:
right_bound = right_bound + abs(right_bound * 0.05)
flip_sign_pos = relevant_pos[
(relevant_pos['type'] == 'Flip Sign')
& (relevant_pos['center_x'] > left_bound) &
(relevant_pos['center_x'] < right_bound)]
if flip_sign_pos.empty:
# add 'NO Flip Sign' product_fk
relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \
[location, NO_FLIP_SIGN_PK, 'Flip Sign']
else:
relevant_pos.loc[flip_sign_pos.index,
['position']] = location
longest_shelf_copy.drop(
longest_shelf_copy.iloc[:relevant_template[location].
iloc[0]].index,
inplace=True)
elif category == 'Smokeless':
# if there are no flip signs found, there are no positions to assign
number_of_flip_signs = len(
relevant_pos[relevant_pos['type'] == 'Flip Sign'])
if number_of_flip_signs > self.flip_sign_positions_template[
'Number of Flip Signs'].max():
Log.warning(
'Number of Flip Signs for Smokeless is greater than max number defined in template!'
)
elif number_of_flip_signs > 0:
flip_sign_position_list = [
position.strip()
for position in self.flip_sign_positions_template[
self.
flip_sign_positions_template['Number of Flip Signs'] ==
number_of_flip_signs]['Position'].iloc[0].split(',')
]
relevant_pos.loc[relevant_pos['type'] == 'Flip Sign',
['position']] = flip_sign_position_list
# store empty flip sign values
for location in ['Secondary', 'Tertiary']:
if location not in relevant_pos[
relevant_pos['type'] ==
'Flip Sign']['position'].tolist():
relevant_pos.loc[len(relevant_pos), ['position', 'product_fk', 'type']] = \
[location, NO_FLIP_SIGN_PK, 'Flip Sign']
relevant_pos = relevant_pos.reindex(
columns=relevant_pos.columns.tolist() + ['denominator_id'])
# this is a bandaid fix that should be removed -> 'F7011A7C-1BB6-4007-826D-2B674BD99DAE'
# removes POS items that were 'extra', i.e. more than max value in template
# only affects smokeless
relevant_pos.dropna(subset=['position'], inplace=True)
relevant_pos.loc[:,
['denominator_id']] = relevant_pos['position'].apply(
self.get_custom_entity_pk)
for row in relevant_pos.itertuples():
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name(row.type)
self.common_v2.write_to_db_result(
kpi_fk,
numerator_id=row.product_fk,
denominator_id=row.denominator_id,
result=row.width,
score=row.width)
self.calculate_fixture_width(relevant_pos, longest_shelf, category)
return
def calculate_total_shelves(self,
longest_shelf,
category,
product_mpis=None):
category_fk = self.get_category_fk_by_name(category)
if product_mpis is None:
product_mpis = self.mpis[
(self.mpis['rect_x'] > longest_shelf['rect_x'].min())
& (self.mpis['rect_x'] < longest_shelf['rect_x'].max()) &
(self.mpis['scene_fk']
== longest_shelf['scene_fk'].fillna(0).mode().iloc[0])]
total_shelves = len(product_mpis['shelf_number'].unique())
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Total Shelves')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=category_fk,
denominator_id=self.store_id,
result=total_shelves)
def calculate_fixture_width(self, relevant_pos, longest_shelf, category):
correction_factor = 1 if category == 'Smokeless' else 2
longest_shelf = longest_shelf[longest_shelf['stacking_layer'] == 1]
category_fk = self.get_category_fk_by_name(category)
# this is needed to remove intentionally duplicated 'Menu Board' POS 'Headers'
relevant_pos = relevant_pos.drop_duplicates(subset=['position'])
# try:
# width = relevant_pos[relevant_pos['type'] == 'Header']['width'].sum()
# except KeyError:
# # needed for when 'width' doesn't exist
# width = 0
# if relevant_pos.empty or width == 0:
width = round(
len(longest_shelf) + correction_factor /
float(self.facings_to_feet_template[category + ' Facings'].iloc[0])
)
kpi_fk = self.common_v2.get_kpi_fk_by_kpi_name('Fixture Width')
self.common_v2.write_to_db_result(kpi_fk,
numerator_id=category_fk,
denominator_id=self.store_id,
result=width)
def get_category_fk_by_name(self, category_name):
return self.all_products[self.all_products['category'] ==
category_name]['category_fk'].iloc[0]
def check_menu_scene_recognition(self, relevant_pos):
mdis = self.adp.get_match_display_in_scene()
mdis = mdis[mdis['display_name'] == 'Menu POS']
if mdis.empty:
return relevant_pos
dummy_sku_for_menu_pk = 9282 # 'Other (Smokeless Tobacco)'
dummy_sky_for_menu_name = 'Menu POS (Scene Recognized Item)'
location_type = 'Header'
width = 1
center_x = mdis['x'].iloc[0]
center_y = mdis['y'].iloc[0]
relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x', 'center_y', 'type', 'width']] = \
[dummy_sku_for_menu_pk, dummy_sky_for_menu_name, center_x, center_y, location_type, width]
relevant_pos = relevant_pos.sort_values(
['center_x'], ascending=True).reset_index(drop=True)
return relevant_pos
def get_menu_board_items(self, relevant_pos, longest_shelf, pos_mpis):
# get the placeholder item
menu_board_dummy = relevant_pos[relevant_pos['product_name'] ==
'Menu POS (Scene Recognized Item)']
# if no placeholder, this function isn't relevant
if menu_board_dummy.empty:
return relevant_pos
center_x = menu_board_dummy['center_x'].iloc[0]
center_y = menu_board_dummy['center_y'].iloc[0]
position = menu_board_dummy['position'].iloc[0]
demarcation_line = longest_shelf['rect_y'].min()
upper_demarcation_line = center_y - (demarcation_line - center_y)
distance_in_facings = 2
try:
left_bound = longest_shelf[
longest_shelf['rect_x'] < center_x].sort_values(
by=['rect_x'],
ascending=False)['rect_x'].iloc[int(distance_in_facings) -
1]
except IndexError:
# if there are no POS items found to the left of the 'Menu POS' scene recognition tag, use the tag itself
# in theory this should never happen
left_bound = center_x
try:
right_bound = longest_shelf[
longest_shelf['rect_x'] > center_x].sort_values(
by=['rect_x'],
ascending=True)['rect_x'].iloc[int(distance_in_facings) -
1]
except IndexError:
# if there are no POS items found to the right of the 'Menu POS' scene recognition tag, use the tag itself
# this is more likely to happen for the right bound than the left bound
right_bound = center_x
pos_mpis = pos_mpis[(pos_mpis['rect_x'] > left_bound)
& (pos_mpis['rect_x'] < right_bound) &
(pos_mpis['rect_y'] > upper_demarcation_line) &
(pos_mpis['rect_y'] < demarcation_line)]
if pos_mpis.empty:
return relevant_pos
# remove the placeholder item
relevant_pos = relevant_pos[~(relevant_pos['product_name'] ==
'Menu POS (Scene Recognized Item)')]
location_type = 'Header'
width = 1
for row in pos_mpis.itertuples():
relevant_pos.loc[len(relevant_pos), ['product_fk', 'product_name', 'center_x',
'center_y', 'type', 'width', 'position']] = \
[row.product_fk, row.product_name, row.rect_x, row.rect_y, location_type, width, position]
return relevant_pos
@staticmethod
def remove_duplicate_pos_tags(relevant_pos_df):
duplicate_results = \
relevant_pos_df[relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)]
duplicate_results_without_other = duplicate_results[
~duplicate_results['product_name'].str.contains('Other')]
results_without_duplicates = \
relevant_pos_df[~relevant_pos_df.duplicated(subset=['left_bound', 'right_bound'], keep=False)]
if duplicate_results_without_other.empty:
return relevant_pos_df[~relevant_pos_df.duplicated(
subset=['left_bound', 'right_bound'], keep='first')]
else:
results = pd.concat(
[duplicate_results_without_other, results_without_duplicates])
# we need to sort_index to fix the sort order to reflect center_x values
return results.drop_duplicates(
subset=['left_bound', 'right_bound']).sort_index()
def get_custom_entity_pk(self, name):
return self.custom_entity_data[self.custom_entity_data['name'] ==
name]['pk'].iloc[0]
def get_length_of_pos(self, row, longest_shelf, category):
width_in_facings = len(
longest_shelf[(longest_shelf['rect_x'] > row['left_bound']) &
(longest_shelf['rect_x'] < row['right_bound'])]) + 2
category_facings = category + ' Facings'
return self.facings_to_feet_template.loc[(
self.facings_to_feet_template[category_facings] -
width_in_facings).abs().argsort()[:1]]['POS Width (ft)'].iloc[0]
@staticmethod
def get_longest_shelf_number(relevant_mpis, max_shelves_from_top=3):
# returns the shelf_number of the longest shelf
try:
longest_shelf = \
relevant_mpis[relevant_mpis['shelf_number'] <= max_shelves_from_top].groupby('shelf_number').agg(
{'scene_match_fk': 'count'})['scene_match_fk'].idxmax()
except ValueError:
longest_shelf = pd.DataFrame()
return longest_shelf
@staticmethod
def get_most_frequent_scene(relevant_scif):
try:
relevant_scene_id = relevant_scif['scene_id'].fillna(
0).mode().iloc[0]
except IndexError:
relevant_scene_id = 0
return relevant_scene_id
def commit(self):
self.common_v2.commit_results_data()
class AMERICASToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
FIRST_KPI = 'First KPI'
SECOND_KPI = 'Second KPI'
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
def get_product_fk_from_product_name(self, product_name):
return self.all_products.product_fk[self.all_products.product_name ==
product_name].values[0]
def calculate_first_kpi(self):
"""
this method if in shelf number 2 all products are the product name below
:return: True/False
"""
product_name = 'CORONA LIGHT 355ML BOTE TALLCAN'
mpis = self.match_product_in_scene
second_shelf = mpis[mpis.shelf_number == 2]
unique_products_in_second_self = second_shelf.product_fk.unique()
if len(unique_products_in_second_self) != 1:
return False
else:
product_fk = self.get_product_fk_from_product_name(product_name)
if product_fk in unique_products_in_second_self:
return True
else:
return False
def calculate_second_kpi(self):
product_name = 'MODELO ESPECIAL 355ML BOTE SINGLE'
facing_counter = 0
try:
product_fk = self.get_product_fk_from_product_name(product_name)
products_in_scene_df = self.match_product_in_scene[
self.match_product_in_scene.product_fk == product_fk]
for i, row in products_in_scene_df.iterrows():
if str(row.face_count) == 'nan':
facing_counter += 1
else:
facing_counter += row.face_count
except IndexError:
pass
return facing_counter >= 5
def write_result_to_db(self, kpi_name, kpi_result):
if kpi_result:
score = 100
else:
score = 0
atomic_kpi_fk = self.kpi_static_data.atomic_kpi_fk[
self.kpi_static_data.atomic_kpi_name == kpi_name].values[0]
self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3, score)
kpi_fk = self.kpi_static_data.kpi_fk[self.kpi_static_data.kpi_name ==
kpi_name].values[0]
self.common.write_to_db_result(kpi_fk, self.LEVEL2, score)
kpi_set_fk = self.kpi_static_data.kpi_set_fk[
self.kpi_static_data.kpi_name == kpi_name].values[0]
self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, score)
def main_calculation(self):
self.write_result_to_db(self.FIRST_KPI, self.calculate_first_kpi())
self.write_result_to_db(self.SECOND_KPI, self.calculate_second_kpi())
self.common.commit_results_data()
class CCBOTTLERSUSCMASOUTHWESTToolBox:
EXCLUDE_FILTER = 0
INCLUDE_FILTER = 1
CONTAIN_FILTER = 2
def __init__(self, data_provider, output, common_v2):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[~(self.scif['product_type'] == 'Irrelevant')]
self.sw_scenes = self.get_sw_scenes(
) # we don't need to check scenes without United products
self.survey = Survey(self.data_provider, self.output)
self.sos = SOS(self.data_provider, self.output)
self.templates = {}
self.common_db = Common(self.data_provider, CMA_COMPLIANCE)
self.common_db2 = common_v2
self.common_scene = CommonV2(self.data_provider)
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
self.program = self.store_info['additional_attribute_3'].iloc[0]
self.sales_center = self.store_info['additional_attribute_5'].iloc[0]
if self.store_type in STORE_TYPES: #####
self.store_type = STORE_TYPES[self.store_type] ####
self.store_attr = self.store_info['additional_attribute_3'].iloc[0]
self.kpi_static_data = self.common_db.get_kpi_static_data()
self.total_score = 0
self.sub_scores = defaultdict(int)
self.sub_totals = defaultdict(int)
self.ignore_stacking = False
self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack'
for sheet in Const.SHEETS_CMA:
self.templates[sheet] = pd.read_excel(TEMPLATE_PATH,
sheetname=sheet).fillna('')
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.region in Const.REGIONS:
for i, main_line in main_template.iterrows():
store_type = self.does_exist(main_line, Const.STORE_TYPE)
if store_type is None or self.store_type in store_type:
self.calculate_main_kpi(main_line)
self.write_sub_parents()
self.write_parent()
self.write_to_db_result(self.common_db.get_kpi_fk_by_kpi_name(
CMA_COMPLIANCE, 1),
score=self.total_score,
level=1)
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.TYPE]
relevant_scif = self.scif[self.scif['scene_id'].isin(self.sw_scenes)]
scene_types = self.does_exist(main_line, Const.SCENE_TYPE)
scene_level = self.does_exist(main_line, Const.SCENE_LEVEL)
store_attrs = main_line[Const.PROGRAM].split(',')
result = score = target = None
general_filters = {}
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(
scene_types)]
general_filters['template_name'] = scene_types
scene_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(
scene_groups)]
general_filters['template_group'] = scene_groups
if kpi_type == 'shelves bonus':
relevant_template = self.templates['shelves']
else:
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME]
== kpi_name]
function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
if not self.store_attr or (store_attrs[0] != '' and self.store_attr not in store_attrs)\
or relevant_scif.empty:
continue
if scene_level:
self.scene_level_kpis(kpi_line, relevant_scif, general_filters,
function)
else:
result, score, target = function(kpi_line, relevant_scif,
general_filters)
# write in DF:
if result is None and score is None and target is None:
continue
if 'Bonus' in self.get_kpi_parent(kpi_name):
self.update_sub_score(kpi_name, passed=result)
else:
self.update_sub_score(kpi_name, passed=score)
if target is None:
target = 0
self.write_to_all_levels(kpi_name=kpi_name,
result=result,
score=score,
target=target)
else:
pass
def write_to_session_level(self, kpi_name, result=0):
"""
Writes a result in the DF
:param kpi_name: string
:param result: boolean
"""
result_dict = {Const.KPI_NAME: kpi_name, Const.RESULT: result * 1}
self.session_results = self.session_results.append(result_dict,
ignore_index=True)
def write_to_all_levels(self,
kpi_name,
result,
score,
target=None,
scene_fk=None,
reuse_scene=False):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
result_dict = {
Const.KPI_NAME: kpi_name,
Const.RESULT: result,
Const.SCORE: score,
Const.THRESHOLD: target
}
# self.all_results = self.all_results.append(result_dict, ignore_index=True)
self.write_to_db(kpi_name, score, result=result, threshold=target)
# availability:
def calculate_availability(self, kpi_line, relevant_scif):
"""
checks if all the lines in the availability sheet passes the KPI (there is at least one product
in this relevant scif that has the attributes).
:param relevant_scif: filtered scif
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we shouldn't calculate
DP lines
:param kpi_line: line from the availability sheet
:return: boolean
"""
filtered_scif = self.filter_scif_availability(kpi_line, relevant_scif)
target = kpi_line[Const.TARGET]
return filtered_scif[
filtered_scif['facings'] > 0]['facings'].count() >= target
def filter_scif_specific(self, relevant_scif, kpi_line, name_in_template,
name_in_scif):
"""
takes scif and filters it from the template
:param relevant_scif: the current filtered scif
:param kpi_line: line from one sheet (availability for example)
:param name_in_template: the column name in the template
:param name_in_scif: the column name in SCIF
:return:
"""
values = self.does_exist(kpi_line, name_in_template)
if values:
if name_in_scif in Const.NUMERIC_VALUES_TYPES:
values = [float(x) for x in values]
return relevant_scif[relevant_scif[name_in_scif].isin(values)]
return relevant_scif
def filter_scif_availability(self, kpi_line, relevant_scif):
"""
calls filter_scif_specific for every column in the template of availability
:param kpi_line:
:param relevant_scif:
:return:
"""
names_of_columns = {
Const.MANUFACTURER: "manufacturer_name",
Const.BRAND: "brand_name",
Const.TRADEMARK: "att2",
Const.SIZE: "size",
Const.NUM_SUB_PACKAGES: "number_of_sub_packages",
# CCBOTTLERSUSConst.PREMIUM_SSD: "Premium SSD",
# CCBOTTLERSUSConst.INNOVATION_BRAND: "Innovation Brand",
}
for name in names_of_columns:
relevant_scif = self.filter_scif_specific(relevant_scif, kpi_line,
name,
names_of_columns[name])
return relevant_scif
# SOS:
def calculate_sos(self, kpi_line, relevant_scif, general_filters):
"""
calculates SOS line in the relevant scif.
:param kpi_line: line from SOS sheet.
:param relevant_scif: filtered scif.
:param isnt_dp: if "store attribute" in the main sheet has DP, and the store is not DP, we should filter
all the DP products out of the numerator.
:return: boolean
"""
kpi_name = kpi_line[Const.KPI_NAME]
relevant_scif = relevant_scif[relevant_scif['product_type'] != "Empty"]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1].split(',')
# relevant_scif = self.filter_by_type_value(relevant_scif, den_type, den_value)
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1].split(',')
# num_scif = self.filter_by_type_value(relevant_scif, num_type, num_value)
general_filters['product_type'] = (['Empty', 'Irrelevant'], 0)
if kpi_line['range'] == 'Y':
upper_limit, lower_limit = self.get_sos_targets(kpi_name,
sos_range=True)
target = None
else:
upper_limit, lower_limit = None, None
target = self.get_sos_targets(kpi_name)
general_filters[den_type] = den_value
if kpi_line[Const.DEN_TYPES_2]:
den_type_2 = kpi_line[Const.DEN_TYPES_2]
den_value_2 = kpi_line[Const.DEN_VALUES_2].split(',')
general_filters[den_type_2] = den_value_2
sos_filters = {num_type: num_value}
if kpi_line[Const.NUM_TYPES_2]:
num_type_2 = kpi_line[Const.NUM_TYPES_2]
num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',')
sos_filters[num_type_2] = num_value_2
sos_value = self.sos.calculate_share_of_shelf(sos_filters,
**general_filters)
sos_value *= 100
sos_value = round(sos_value, 2)
if target:
target = target * 100
score = 1 if sos_value >= target else 0
elif not target and upper_limit and lower_limit:
score = 1 if (
lower_limit * 100 <= sos_value <= upper_limit * 100) else 0
target = '{}% - {}%'.format(lower_limit, upper_limit)
else:
score = 1
target = 0
return sos_value, score, target
# Targets:
def get_sos_targets(self, kpi_name, sos_range=False):
targets_template = self.templates[Const.TARGETS]
store_targets = targets_template.loc[
(targets_template[Const.PROGRAM] == self.program)
& (targets_template['region'] == self.region)]
filtered_targets_to_kpi = store_targets.loc[
targets_template['KPI name'] == kpi_name]
if sos_range:
if not filtered_targets_to_kpi.empty:
range = filtered_targets_to_kpi['target'].values[0].split(
' - ')
upper_limit = int(range[1].replace('%', '').strip())
lower_limit = int(range[0].replace('%', '').strip())
else:
upper_limit, lower_limit = None, None
return upper_limit, lower_limit
else:
if not filtered_targets_to_kpi.empty:
target = float(filtered_targets_to_kpi[Const.TARGET].values[0])
else:
target = None
return target
def get_targets(self, kpi_name):
targets_template = self.templates[Const.TARGETS]
store_targets = targets_template.loc[
(targets_template[Const.PROGRAM] == self.program)
& (targets_template['region'] == self.region)]
filtered_targets_to_kpi = store_targets.loc[
targets_template['KPI name'] == kpi_name]
if not filtered_targets_to_kpi.empty:
target = filtered_targets_to_kpi[Const.TARGET].values[0]
else:
target = None
return target
@staticmethod
def get_kpi_line_filters(kpi_line):
filters = {}
attribs = list(kpi_line.index)
c = 1
while 1:
if 'Param {}'.format(c) in attribs and kpi_line['Param {}'.format(
c)]:
filters[kpi_line['Param {}'.format(c)]] = kpi_line[
'Value {}'.format(c)].split(',')
else:
if c > 3: # just in case someone inexplicably chose a nonlinear numbering format.
break
c += 1
return filters
@staticmethod
def get_kpi_line_targets(kpi_line):
mask = kpi_line.index.str.contains('Target')
if mask.any():
targets = kpi_line.loc[mask].replace('', np.nan).dropna()
targets.index = [
int(x.split(Const.SEPERATOR)[1].split(' ')[0])
for x in targets.index
]
targets = targets.to_dict()
else:
targets = {}
return targets
@staticmethod
def extrapolate_target(targets, c):
while 1:
if targets[c]:
target = targets[c]
break
else:
c -= 1
if c < 0:
target = 0
break
return target
def scene_level_kpis(self, kpi_line, scif, general_filters, func):
num_filters = self.get_kpi_line_filters(kpi_line)
general_filters['product_type'] = (['Empty', 'Irrelevant'], 0)
scenes = scif['scene_fk'].unique().tolist()
if not isinstance(scenes, list):
scenes = [scenes]
total_num = 0
total_den = 0
for scene in scenes:
# self.data_provider.load_scene_data(self.session_uid, scene)
self.common_scene.scene_id = scene
scene_scif = scif[scif['scene_fk'] == scene]
if scif.empty:
pass
Log.warning('Match product in scene is empty for this scene')
else:
num, ratio, den = func(kpi_line, scene_scif, num_filters,
general_filters)
total_num += num
total_den += den
self.common_scene.commit_results_data(result_entity='scene')
self.common_scene.kpi_results = pd.DataFrame(
columns=self.common_db2.COLUMNS)
# self.common_db2.write_to_db_result(fk=2161, numerator_result=total_num,
# denominator_result=total_den, result=ratio,
# identifier_result=self.common_db2.get_dictionary(
# parent_name='Total Coke Cooler Purity'),
# should_enter=True)
def sos_with_num_and_dem(self, kpi_line, relevant_scif, num_filters,
general_filters):
kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_line['KPI name'])
num_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **num_filters)]
den_scif = relevant_scif[self.get_filter_condition(
relevant_scif, **general_filters)]
try:
Validation.is_empty_df(den_scif)
Validation.is_empty_df(num_scif)
Validation.df_columns_equality(den_scif, num_scif)
Validation.is_subset(den_scif, num_scif)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
raise Exception(msg)
num = num_scif[self.facings_field].sum()
den = den_scif[self.facings_field].sum()
ratio = num / float(den)
# numerator_id=product_fk,
self.common_scene.write_to_db_result(fk=kpi_fk,
numerator_result=num,
denominator_result=den,
result=ratio,
by_scene=True)
# self.common_scene.write_to_db_result(fk=kpi_fk, numerator_result=num,
# denominator_result=den, result=ratio, by_scene=True
# identifier_parent=self.common_db2.get_dictionary(
# parent_name='Total Coke Cooler Purity'),
# should_enter=True)
return num, ratio, den
class REDToolBox:
def __init__(self, data_provider, output, calculation_type, common_db2):
self.output = output
self.data_provider = data_provider
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.store_info = self.data_provider[Data.STORE_INFO]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.scif = self.scif[self.scif['product_type'] != "Irrelevant"]
self.ps_data_provider = PsDataProvider(self.data_provider, self.output)
self.templates = {}
self.result_values = self.ps_data_provider.get_result_values()
self.calculation_type = calculation_type
if self.calculation_type == Const.SOVI:
self.TEMPLATE_PATH = Const.TEMPLATE_PATH
self.RED_SCORE = Const.RED_SCORE
self.RED_SCORE_INTEG = Const.RED_SCORE_INTEG
for sheet in Const.SHEETS:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.converters = self.templates[Const.CONVERTERS]
self.scenes_results = self.ps_data_provider.get_scene_results(
self.scene_info['scene_fk'].drop_duplicates().values)
self.scenes_results = self.scenes_results[[Const.DB_RESULT, Const.DB_SCENE_FK, Const.DB_SCENE_KPI_FK]]
else:
self.TEMPLATE_PATH = Const.SURVEY_TEMPLATE_PATH
self.RED_SCORE = Const.MANUAL_RED_SCORE
self.RED_SCORE_INTEG = Const.MANUAL_RED_SCORE_INTEG
for sheet in Const.SHEETS_MANUAL:
self.templates[sheet] = pd.read_excel(self.TEMPLATE_PATH, sheetname=sheet).fillna('')
self.store_attr = self.store_info['additional_attribute_15'].iloc[0]
self.toolbox = FunctionsToolBox(self.data_provider, self.output, self.templates, self.store_attr)
self.common_db_integ = Common(self.data_provider, self.RED_SCORE_INTEG)
self.kpi_static_data_integ = self.common_db_integ.get_kpi_static_data()
self.common_db = Common(self.data_provider, self.RED_SCORE)
self.common_db2 = common_db2
self.region = self.store_info['region_name'].iloc[0]
self.store_type = self.store_info['store_type'].iloc[0]
if self.store_type in Const.STORE_TYPES:
self.store_type = Const.STORE_TYPES[self.store_type]
self.kpi_static_data = self.common_db.get_kpi_static_data()
main_template = self.templates[Const.KPIS]
self.templates[Const.KPIS] = main_template[(main_template[Const.REGION] == self.region) &
(main_template[Const.STORE_TYPE] == self.store_type)]
self.session_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.all_results = pd.DataFrame(columns=Const.COLUMNS_OF_RESULTS)
self.used_scenes = []
self.red_score = 0
self.set_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE)
self.set_integ_fk = self.common_db2.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG)
self.weight_factor = self.get_weight_factor()
# main functions:
def main_calculation(self, *args, **kwargs):
"""
This function gets all the scene results from the SceneKPI, after that calculates every session's KPI,
and in the end it calls "filter results" to choose every KPI and scene and write the results in DB.
"""
main_template = self.templates[Const.KPIS]
if self.calculation_type == Const.SOVI:
session_template = main_template[main_template[Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
self.calculate_main_kpi(main_line)
else:
for i, main_line in main_template.iterrows():
self.calculate_manual_kpi(main_line)
if not main_template.empty:
self.choose_and_write_results()
return self.red_score
def calculate_main_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_scif = self.scif
scene_types = self.toolbox.does_exist(main_line, Const.SCENE_TYPE)
if scene_types:
relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)]
scene_groups = self.toolbox.does_exist(main_line, Const.SCENE_TYPE_GROUP)
if scene_groups:
relevant_scif = relevant_scif[relevant_scif['template_group'].isin(scene_groups)]
if main_line[Const.SHEET] == Const.SCENE_AVAILABILITY:
result = False if relevant_scif.empty else True
else:
result = self.toolbox.calculate_kpi_by_type(main_line, relevant_scif)
self.write_to_session_level(kpi_name=kpi_name, result=result)
def calculate_manual_kpi(self, main_line):
"""
This function gets a line from the main_sheet, transfers it to the match function, and checks all of the
KPIs in the same name in the match sheet.
:param main_line: series from the template of the main_sheet.
"""
kpi_name = main_line[Const.KPI_NAME]
relevant_template = self.templates[Const.SURVEY]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name]
target = len(relevant_template) if main_line[Const.GROUP_TARGET] == Const.ALL \
else main_line[Const.GROUP_TARGET]
passed_counter = 0
for i, kpi_line in relevant_template.iterrows():
answer = self.toolbox.calculate_survey_specific(kpi_line)
if answer:
passed_counter += 1
result = passed_counter >= target
self.write_to_session_level(kpi_name=kpi_name, result=result)
# write in DF:
def write_to_session_level(self, kpi_name, result=0):
"""
Writes a result in the DF
:param kpi_name: string
:param result: boolean
"""
result_dict = {Const.KPI_NAME: kpi_name, Const.DB_RESULT: result * 1}
self.session_results = self.session_results.append(result_dict, ignore_index=True)
def write_to_all_levels(self, kpi_name, result, display_text, weight, scene_fk=None, reuse_scene=False):
"""
Writes the final result in the "all" DF, add the score to the red score and writes the KPI in the DB
:param kpi_name: str
:param result: int
:param display_text: str
:param weight: int/float
:param scene_fk: for the scene's kpi
:param reuse_scene: this kpi can use scenes that were used
"""
score = self.get_score(weight)
result_value = Const.PASS if result > 0 else Const.FAIL
if result_value == Const.PASS:
self.red_score += score
result_dict = {Const.KPI_NAME: kpi_name, Const.DB_RESULT: result, Const.SCORE: score}
if scene_fk:
result_dict[Const.DB_SCENE_FK] = scene_fk
if not reuse_scene:
self.used_scenes.append(scene_fk)
self.all_results = self.all_results.append(result_dict, ignore_index=True)
self.write_to_db(kpi_name, score, display_text=display_text, result_value=result_value)
def choose_and_write_results(self):
"""
writes all the KPI in the DB: first the session's ones, second the scene's ones and in the end the ones
that depends on the previous ones. After all it writes the red score
"""
main_template = self.templates[Const.KPIS]
self.write_session_kpis(main_template)
if self.calculation_type == Const.SOVI:
self.write_scene_kpis(main_template)
self.write_condition_kpis(main_template)
self.write_missings(main_template)
self.write_to_db(self.RED_SCORE, self.red_score)
def write_missings(self, main_template):
"""
write 0 in all the KPIs that didn't get score
:param main_template:
"""
for i, main_line in main_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
if not self.all_results[self.all_results[Const.KPI_NAME] == kpi_name].empty:
continue
result = 0
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_session_kpis(self, main_template):
"""
iterates all the session's KPIs and saves them
:param main_template: main_sheet.
"""
session_template = main_template[main_template[Const.CONDITION] == ""]
if self.calculation_type == Const.SOVI:
session_template = session_template[session_template[Const.SESSION_LEVEL] == Const.V]
for i, main_line in session_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
result = self.session_results[self.session_results[Const.KPI_NAME] == kpi_name]
if result.empty:
continue
result = result.iloc[0][Const.DB_RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
self.write_to_all_levels(kpi_name, result, display_text, weight)
def write_incremental_kpis(self, scene_template):
"""
lets the incremental KPIs choose their scenes (if they passed).
if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet
:return: the new template (without the KPI written already)
"""
incremental_template = scene_template[scene_template[Const.INCREMENTAL] != ""]
while not incremental_template.empty:
for i, main_line in incremental_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.DB_RESULT] > 0]
increments = main_line[Const.INCREMENTAL]
if ', ' in increments:
first_kpi = increments.split(', ')[0]
others = increments.replace(', '.format(first_kpi), '')
scene_template.loc[scene_template[Const.KPI_NAME] == first_kpi, Const.INCREMENTAL] = others
if true_results.empty:
scene_template.loc[scene_template[Const.KPI_NAME] == kpi_name, Const.INCREMENTAL] = ""
else:
true_results = true_results.sort_values(by=Const.DB_RESULT, ascending=False)
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = true_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, true_results.iloc[0][Const.DB_RESULT], display_text,
weight, scene_fk=scene_fk, reuse_scene=reuse_scene)
scene_template = scene_template[~(scene_template[Const.KPI_NAME] == kpi_name)]
incremental_template = scene_template[scene_template[Const.INCREMENTAL] != ""]
return scene_template
def write_regular_scene_kpis(self, scene_template):
"""
lets the regular KPIs choose their scenes (if they passed).
Like in the incremental part - if KPI passed some scenes, we will choose the scene that the children passed
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed incremental)
:return: the new template (without the KPI written already)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
true_results = kpi_results[kpi_results[Const.DB_RESULT] > 0]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if true_results.empty:
continue
true_results = true_results.sort_values(by=Const.DB_RESULT, ascending=False)
scene_fk = true_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, true_results.iloc[0][Const.DB_RESULT], display_text, weight,
scene_fk=scene_fk, reuse_scene=reuse_scene)
scene_template = scene_template[~(scene_template[Const.KPI_NAME] == kpi_name)]
return scene_template
def write_not_passed_scene_kpis(self, scene_template):
"""
lets the KPIs not passed choose their scenes.
:param scene_template: filtered main_sheet (only scene KPIs, and without the passed KPIs)
"""
for i, main_line in scene_template.iterrows():
kpi_name = main_line[Const.KPI_NAME]
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
reuse_scene = main_line[Const.REUSE_SCENE] == Const.V
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
if not reuse_scene:
kpi_results = kpi_results[~(kpi_results[Const.DB_SCENE_FK].isin(self.used_scenes))]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
if kpi_results.empty:
continue
scene_fk = kpi_results.iloc[0][Const.DB_SCENE_FK]
self.write_to_all_levels(kpi_name, 0, display_text, weight, scene_fk=scene_fk, reuse_scene=reuse_scene)
def write_scene_kpis(self, main_template):
"""
iterates every scene_kpi that does not depend on others, and choose the scene they will take:
1. the incrementals take their scene (if they passed).
2. the regular KPIs that passed choose their scenes.
3. the ones that didn't pass choose their random scenes.
:param main_template: main_sheet.
"""
scene_template = main_template[(main_template[Const.SESSION_LEVEL] != Const.V) &
(main_template[Const.CONDITION] == "")]
scene_template = self.write_incremental_kpis(scene_template)
scene_template = self.write_regular_scene_kpis(scene_template)
self.write_not_passed_scene_kpis(scene_template)
def write_condition_kpis(self, main_template):
"""
writes all the KPI that depend on other KPIs by checking if the parent KPI has passed and in which scene.
:param main_template: main_sheet
"""
condition_template = main_template[main_template[Const.CONDITION] != '']
for i, main_line in condition_template.iterrows():
condition = main_line[Const.CONDITION]
kpi_name = main_line[Const.KPI_NAME]
if self.calculation_type == Const.MANUAL or main_line[Const.SESSION_LEVEL] == Const.V:
kpi_results = self.session_results[self.session_results[Const.KPI_NAME] == kpi_name]
else:
kpi_scene_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name + Const.SCENE_SUFFIX)
kpi_results = self.scenes_results[self.scenes_results[Const.DB_SCENE_KPI_FK] == kpi_scene_fk]
condition_result = self.all_results[(self.all_results[Const.KPI_NAME] == condition) &
(self.all_results[Const.DB_RESULT] > 0)]
if condition_result.empty:
continue
condition_result = condition_result.iloc[0]
if Const.DB_SCENE_FK in condition_result:
condition_scene = condition_result[Const.DB_SCENE_FK]
else:
condition_scene = None
if condition_scene and Const.DB_SCENE_FK in kpi_results:
results = kpi_results[kpi_results[Const.DB_SCENE_FK] == condition_scene]
else:
results = kpi_results
if results.empty:
continue
result = results.iloc[0][Const.DB_RESULT]
display_text = main_line[Const.DISPLAY_TEXT]
weight = main_line[Const.WEIGHT]
scene_fk = results.iloc[0][Const.DB_SCENE_FK] if Const.DB_SCENE_FK in kpi_results else None
self.write_to_all_levels(kpi_name, result, display_text, weight, scene_fk=scene_fk)
def get_weight_factor(self):
sum_weights = self.templates[Const.KPIS][Const.WEIGHT].sum()
return sum_weights / 100.0
def get_score(self, weight):
return weight / self.weight_factor
def get_pks_of_result(self, result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = self.result_values[self.result_values['value'] == result]['pk'].iloc[0]
return pk
@staticmethod
def get_0_1_of_result(result):
"""
converts string result to its pk (in static.kpi_result_value)
:param result: str
:return: int
"""
pk = 0 if result == Const.FAIL else 1
return pk
def write_to_db(self, kpi_name, score, display_text='', result_value=Const.FAIL):
"""
writes result in the DB
:param kpi_name: str
:param score: float, the weight of the question
:param display_text: str
:param result_value: str, Pass/Fail
"""
if kpi_name == self.RED_SCORE:
self.common_db2.write_to_db_result(
fk=self.set_fk, score=score, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(kpi_fk=self.set_fk))
self.common_db2.write_to_db_result(
fk=self.set_integ_fk, score=score, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id,
identifier_result=self.common_db2.get_dictionary(kpi_fk=self.set_integ_fk))
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(self.RED_SCORE, 1), score=score, level=1)
self.write_to_db_result(
self.common_db_integ.get_kpi_fk_by_kpi_name(self.RED_SCORE_INTEG, 1), score=score, level=1,
set_type=Const.MANUAL)
else:
integ_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(kpi_name)
display_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(display_text)
if display_kpi_fk is None:
display_kpi_fk = self.common_db2.get_kpi_fk_by_kpi_name(display_text[:100])
result = self.get_pks_of_result(result_value)
self.common_db2.write_to_db_result(
fk=display_kpi_fk, score=score, identifier_parent=self.common_db2.get_dictionary(kpi_fk=self.set_fk),
should_enter=True, result=result, numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id)
result = self.get_0_1_of_result(result_value)
self.common_db2.write_to_db_result(
fk=integ_kpi_fk, score=score, should_enter=True, result=result,
identifier_parent=self.common_db2.get_dictionary(kpi_fk=self.set_integ_fk),
numerator_id=Const.MANUFACTURER_FK, denominator_id=self.store_id)
if result_value == Const.FAIL:
score = 0
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(kpi_name, 2), score=score, level=2)
self.write_to_db_result(
self.common_db.get_kpi_fk_by_kpi_name(kpi_name, 3), score=score, level=3, display_text=display_text)
self.write_to_db_result(self.common_db_integ.get_kpi_fk_by_kpi_name(
kpi_name, 3), score=score, level=3, display_text=kpi_name, set_type=Const.MANUAL)
def write_to_db_result(self, fk, level, score, set_type=Const.SOVI, **kwargs):
"""
This function creates the result data frame of every KPI (atomic KPI/KPI/KPI set),
and appends the insert SQL query into the queries' list, later to be written to the DB.
"""
if kwargs:
kwargs['score'] = score
attributes = self.create_attributes_dict(fk=fk, level=level, set_type=set_type, **kwargs)
else:
attributes = self.create_attributes_dict(fk=fk, score=score, set_type=set_type, level=level)
if level == self.common_db.LEVEL1:
table = self.common_db.KPS_RESULT
elif level == self.common_db.LEVEL2:
table = self.common_db.KPK_RESULT
elif level == self.common_db.LEVEL3:
table = self.common_db.KPI_RESULT
else:
return
query = insert(attributes, table)
if set_type == Const.SOVI:
self.common_db.kpi_results_queries.append(query)
else:
self.common_db_integ.kpi_results_queries.append(query)
def create_attributes_dict(self, score, fk=None, level=None, display_text=None, set_type=Const.SOVI, **kwargs):
"""
This function creates a data frame with all attributes needed for saving in KPI results tables.
or
you can send dict with all values in kwargs
"""
kpi_static_data = self.kpi_static_data if set_type == Const.SOVI else self.kpi_static_data_integ
if level == self.common_db.LEVEL1:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_set_name = kpi_static_data[kpi_static_data['kpi_set_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(kpi_set_name, self.session_uid, self.store_id, self.visit_date.isoformat(),
format(score, '.2f'), fk)],
columns=['kps_name', 'session_uid', 'store_fk', 'visit_date', 'score_1', 'kpi_set_fk'])
elif level == self.common_db.LEVEL2:
if kwargs:
kwargs['score'] = score
values = [val for val in kwargs.values()]
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame(values, columns=col)
else:
kpi_name = kpi_static_data[kpi_static_data['kpi_fk'] == fk]['kpi_name'].values[0].replace("'", "\\'")
attributes = pd.DataFrame(
[(self.session_uid, self.store_id, self.visit_date.isoformat(), fk, kpi_name, score)],
columns=['session_uid', 'store_fk', 'visit_date', 'kpi_fk', 'kpk_name', 'score'])
elif level == self.common_db.LEVEL3:
if kwargs:
kwargs['score'] = score
values = tuple([val for val in kwargs.values()])
col = [col for col in kwargs.keys()]
attributes = pd.DataFrame([values], columns=col)
else:
data = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]
kpi_fk = data['kpi_fk'].values[0]
kpi_set_name = kpi_static_data[kpi_static_data['atomic_kpi_fk'] == fk]['kpi_set_name'].values[0]
attributes = pd.DataFrame(
[(display_text, self.session_uid, kpi_set_name, self.store_id, self.visit_date.isoformat(),
datetime.utcnow().isoformat(), score, kpi_fk, fk)],
columns=['display_text', 'session_uid', 'kps_name', 'store_fk', 'visit_date',
'calculation_time', 'score', 'kpi_fk', 'atomic_kpi_fk'])
else:
attributes = pd.DataFrame()
return attributes.to_dict()
def remove_queries_of_calculation_type(self):
"""
In case that the session has no results in the SOVI KPIs we are deleting all the queries
and calculating the MANUAL
:return:
"""
self.common_db2.kpi_results = pd.DataFrame(columns=self.common_db2.COLUMNS)
def commit_results(self):
"""
committing the results in both sets
"""
self.common_db.delete_results_data_by_kpi_set()
self.common_db.commit_results_data_without_delete()
if self.common_db_integ:
self.common_db_integ.delete_results_data_by_kpi_set()
self.common_db_integ.commit_results_data_without_delete()
class PURINAToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
def __init__(self, data_provider, output):
self.output = output
self.data_provider = data_provider
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.match_product_in_scene = self.data_provider[Data.MATCHES]
self.visit_date = self.data_provider[Data.VISIT_DATE]
self.session_info = self.data_provider[Data.SESSION_INFO]
self.scene_info = self.data_provider[Data.SCENES_INFO]
self.store_id = self.data_provider[Data.STORE_FK]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.kpi_static_data = self.common.get_kpi_static_data()
self.session_info = self.data_provider[Data.SESSION_INFO]
self.session_fk = self.session_info['pk'].values[0]
self.kpi_results_queries = []
self.kpi_static_queries = []
self.purina_scif = self.scif.loc[self.scif['category_fk'] == PET_FOOD_CATEGORY]
def calculate_purina(self, *args, **kwargs):
"""
This function calculates the KPI results.
"""
if not self.is_session_purina():
return
# Update all new static KPIs
self.create_new_static_kpi()
self.kpi_static_data = self.common.get_kpi_static_data(refresh=True)
self.update_kpi_score()
self.run_data_collecting()
self.common.commit_results_data()
def update_kpi_score(self):
# Only to see results in join :(
for kpi in PURINA_KPI:
kpi_fk = self.get_kpi_fk_by_kpi_name(kpi, self.LEVEL2, set_name=PURINA_SET)
self.common.write_to_db_result(kpi_fk, self.LEVEL2, 1)
def run_data_collecting(self):
"""
This function run the man calculation of linear sos with sub category out of subsegment
or price out of subsegment
:param price_kpi:
:return:
"""
data = self.purina_scif.dropna(subset=[LINEAR_SIZE])
if data.empty:
Log.info("No relevant purina's products were found in session.")
return
# subseg_name_list = data[SCIF_SUBSEGMENT].unique()
# for subseg in subseg_name_list:
# if not subseg:
# subseg = NO_SUBSEG
# by_subseg = data.loc[pd.isnull(data[SCIF_SUBSEGMENT])]
# subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE]))
# else:
# by_subseg = data.loc[data[SCIF_SUBSEGMENT] == subseg]
# subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE]))
# atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=SUBSEGMENT_SET)
# self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft)
# atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=PRICE_SET)
# self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft)
# gets all category linear size
category_ft = self.cm_to_ft(sum(data[LINEAR_SIZE]))
fk = self.get_kpi_fk_by_kpi_name(PURINA_SET, self.LEVEL1)
self.common.write_to_db_result(fk, self.LEVEL1, category_ft)
man = data['manufacturer_name'].unique()
for mf in man:
by_mf = data.loc[data['manufacturer_name'] == mf]
manufacturer_ft = self.cm_to_ft(sum(by_mf[LINEAR_SIZE]))
relevant_kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == MANUFACTUR) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(mf, self.LEVEL3, father=MANUFACTUR, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=manufacturer_ft, score_2=manufacturer_ft,
session_uid=self.session_uid, store_fk=self.store_id,
display_text=mf.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=relevant_kpi_fk)
else:
print 'atomic cannot be saved for manufacturer {}'.format(mf)
brands = by_mf['brand_name'].unique()
for brand in brands:
by_brand = by_mf.loc[data['brand_name'] == brand]
brand_ft = self.cm_to_ft(sum(by_brand[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == BRAND) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(brand, self.LEVEL3, father=BRAND, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=brand_ft, score_2=brand_ft, style=mf.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=brand.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for brand {}'.format(brand)
categories = by_brand[SCIF_CATEOGRY].unique()
for cat in categories:
if not cat:
cat = OTHER
by_cat = by_brand.loc[pd.isnull(by_brand[SCIF_PRICE])]
cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE]))
else:
by_cat = by_brand.loc[data[SCIF_SUB_CATEOGRY] == cat]
cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == CATEGORY) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(cat, self.LEVEL3, father=CATEGORY, set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=cat_ft,
score_2=cat_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=cat.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for category {}'.format(cat)
sub_cats = by_cat[SCIF_SUB_CATEOGRY].unique()
for sub_cat in sub_cats:
if not sub_cat:
sub_cat = OTHER
by_sub_cat = by_cat.loc[pd.isnull(by_cat[SCIF_PRICE])]
sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE]))
else:
by_sub_cat = by_cat.loc[data[SCIF_SUB_CATEOGRY] == sub_cat]
sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE]))
# write to db under sub category atomic kpi score with brand name in results
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == SUB_CATEGORY) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)][
'kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(sub_cat, self.LEVEL3, father=SUB_CATEGORY,
set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=sub_cat_ft,
score_2=sub_cat_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
result_2=cat.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=sub_cat.replace("'","''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk)
else:
print 'atomic cannot be saved for sub category {}'.format(sub_cat)
prices = by_sub_cat[SCIF_PRICE].unique()
for price_class in prices:
if not price_class:
price_class = OTHER
by_prices = by_sub_cat.loc[pd.isnull(by_sub_cat[SCIF_PRICE])]
price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE]))
else:
by_prices = by_sub_cat.loc[by_sub_cat[SCIF_PRICE] == price_class]
price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE]))
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == PRICE_KPI) &
(self.kpi_static_data['kpi_set_name'] == PURINA_SET)][
'kpi_fk'].values[0]
atomic_fk = self.get_kpi_fk_by_kpi_name(price_class, self.LEVEL3, father=PRICE_KPI,
set_name=PURINA_SET)
if atomic_fk:
self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3,
score=price_ft,
score_2=price_ft, style=mf.replace("'","''"),
result=brand.replace("'","''"),
result_2=cat.replace("'","''"),
result_3=sub_cat.replace("'","''"),
session_uid=self.session_uid, store_fk=self.store_id,
display_text=price_class.replace("'", "''"),
visit_date=self.visit_date.isoformat(),
calculation_time=datetime.utcnow().isoformat(),
kps_name=PURINA_SET,
kpi_fk=kpi_fk )
else:
print 'atomic cannot be saved for price class {}'.format(price_class)
@staticmethod
def cm_to_ft(cm):
return cm / 30.48
def get_labels(self):
query = """select pk, labels, ean_code
from static_new.product
"""
labels = pd.read_sql_query(query, self.rds_conn.db)
return labels
def get_kpi_fk_by_kpi_name(self, kpi_name, kpi_level, father=None, logic_father=None, set_name=None):
if kpi_level == self.LEVEL1:
column_key = 'kpi_set_fk'
column_value = 'kpi_set_name'
father_value = 'kpi_set_name'
elif kpi_level == self.LEVEL2:
column_key = 'kpi_fk'
column_value = 'kpi_name'
father_value = 'kpi_set_name'
elif kpi_level == self.LEVEL3:
column_key = 'atomic_kpi_fk'
column_value = 'atomic_kpi_name'
father_value = 'kpi_name'
else:
raise ValueError('invalid level')
try:
relevant = self.kpi_static_data[self.kpi_static_data[column_value] == kpi_name]
if father:
relevant = relevant[relevant[father_value] == father]
if set_name:
relevant = relevant[relevant['kpi_set_name'] == set_name]
return relevant[column_key].values[0]
except IndexError:
Log.info('Kpi name: {}, isn\'t equal to any kpi name in static table'.format(kpi_name))
return None
def create_new_static_kpi(self):
# This functions takes all brands, sub categories, categories and manufacturers in session.
# The function adds them to database in case they are new.
brands = self.get_all_brands()
sub_cats = self.get_all_sub_categories()
manufacturer = self.get_all_manufacturers()
cats = self.get_all_categories()
prices = self.get_all_price_classes()
new_brands = self.purina_scif.loc[~self.purina_scif['brand_name'].isin(brands)]['brand_name'].unique()
new_manufacturer = self.purina_scif.loc[~self.purina_scif['manufacturer_name'].isin(manufacturer)][
'manufacturer_name'].unique()
new_sub_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_SUB_CATEOGRY].isin(sub_cats)) &
(~pd.isnull(self.purina_scif[SCIF_SUB_CATEOGRY]))][SCIF_SUB_CATEOGRY].unique()
new_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_CATEOGRY].isin(cats)) &
(~pd.isnull(self.purina_scif[SCIF_CATEOGRY]))][SCIF_CATEOGRY].unique()
new_prices = self.purina_scif.loc[(~self.purina_scif[SCIF_PRICE].isin(prices)) &
(~pd.isnull(self.purina_scif[SCIF_PRICE]))][SCIF_PRICE].unique()
self.save_static_atomics(BRAND, new_brands, PURINA_SET)
self.save_static_atomics(MANUFACTUR, new_manufacturer, PURINA_SET)
self.save_static_atomics(CATEGORY, new_cat, PURINA_SET)
self.save_static_atomics(SUB_CATEGORY, new_sub_cat, PURINA_SET)
self.save_static_atomics(PRICE_KPI, new_prices, PURINA_SET)
self.commit_static_data()
def get_all_brands(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == BRAND]['atomic_kpi_name']
def get_all_sub_categories(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == SUB_CATEGORY]['atomic_kpi_name']
def get_all_manufacturers(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == MANUFACTUR]['atomic_kpi_name']
def get_all_categories(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == CATEGORY]['atomic_kpi_name']
def get_all_price_classes(self):
return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == PRICE_KPI]['atomic_kpi_name']
def save_static_atomics(self, kpi_name, atomics, set_name):
kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == kpi_name) &
(self.kpi_static_data['kpi_set_name'] == set_name)]['kpi_fk'].values[0]
for current in atomics:
current = current.replace("'", "''")
query = """
INSERT INTO {0} (`kpi_fk`, `name`, `description`, `display_text`,`presentation_order`, `display`)
VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}');""".format(STATIC_ATOMIC,
kpi_fk, current, current, current, 1, 'Y')
self.kpi_static_queries.append(query)
def commit_static_data(self):
"""
This function writes all KPI results to the DB, and commits the changes.
"""
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
# ProjectConnector(self.project_name, DbUsers.CalculationEng)
cur = self.rds_conn.db.cursor()
for query in self.kpi_static_queries:
try:
cur.execute(query)
except Exception as e:
Log.info('query {} could not be executed.'.format(query))
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
def is_session_purina(self):
# This function checks is the session is of Purina project by its category and that it is a successful visit.
session_data = self.get_session_category_data()
session_data = session_data.loc[(session_data['category_fk'] == 13) &
(session_data['resolution_fk'] == 1) &
(session_data['exclude_status_fk'] == 1)]
if not session_data.empty:
return True
return False
def get_session_category_data(self):
local_con = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
query = """select category_fk, resolution_fk, exclude_status_fk from probedata.session_category
where session_fk = {}""".format(self.session_fk)
data = pd.read_sql_query(query, local_con.db)
return data
class SOLARBRToolBox:
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUDE_EMPTY = False
EXCLUDE_FILTER = 0
EMPTY = 'Empty'
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.k_engine = BaseCalculationsGroup(data_provider, output)
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.store_info = self.data_provider[Data.STORE_INFO]
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.templates = {}
self.session_id = self.data_provider.session_id
self.score_templates = {}
self.get_templates()
self.get_score_template()
self.manufacturer_fk = self.all_products[
self.all_products['manufacturer_name'] == 'Coca Cola'].iloc[0]
self.sos = SOS(self.data_provider, self.output)
self.total_score = 0
self.session_fk = self.data_provider[Data.SESSION_INFO]['pk'].iloc[0]
self.toolbox = GENERALToolBox(self.data_provider)
self.scenes_info = self.data_provider[Data.SCENES_INFO]
self.kpi_results_new_tables_queries = []
# self.store_type = self.data_provider.store_type
def get_templates(self):
for sheet in Const.SHEETS_MAIN:
self.templates[sheet] = pd.read_excel(MAIN_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False)
def get_score_template(self):
for sheet in Const.SHEETS_SCORE:
self.score_templates[sheet] = pd.read_excel(SCORE_TEMPLATE_PATH, sheetname=sheet.decode("utf-8"), keep_default_na=False, encoding = "utf-8")
def main_calculation(self, *args, **kwargs):
main_template = self.templates[Const.KPIS]
for i, main_line in main_template.iterrows():
self.calculate_main_kpi(main_line)
self.commit_results()
def calculate_main_kpi(self, main_line):
kpi_name = main_line[Const.KPI_NAME]
kpi_type = main_line[Const.Type]
scene_types = self.does_exist(main_line, Const.SCENE_TYPES)
result = score = 0
general_filters = {}
scif_scene_types = self.scif['template_name'].unique().tolist()
store_type = str(self.store_info["store_type"].iloc[0])
store_types = self.does_exist_store(main_line, Const.STORE_TYPES)
if store_type in store_types:
if scene_types:
if (('All' in scene_types) or bool(set(scif_scene_types) & set(scene_types))) :
if not ('All' in scene_types):
general_filters['template_name'] = scene_types
if kpi_type == Const.SOVI:
relevant_template = self.templates[kpi_type]
relevant_template = relevant_template[relevant_template[Const.KPI_NAME] == kpi_name]
if relevant_template["numerator param 1"].all() and relevant_template["denominator param"].all():
function = self.get_kpi_function(kpi_type)
for i, kpi_line in relevant_template.iterrows():
result, score = function(kpi_line, general_filters)
else:
pass
else:
pass
@staticmethod
def does_exist(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(", ")
return None
@staticmethod
def does_exist_store(kpi_line, column_name):
"""
checks if kpi_line has values in this column, and if it does - returns a list of these values
:param kpi_line: line from template
:param column_name: str
:return: list of values if there are, otherwise None
"""
if column_name in kpi_line.keys() and kpi_line[column_name] != "":
cell = kpi_line[column_name]
if type(cell) in [int, float]:
return [cell]
elif type(cell) in [unicode, str]:
return cell.split(",")
return None
def calculate_sos(self, kpi_line, general_filters):
kpi_name = kpi_line[Const.KPI_NAME]
den_type = kpi_line[Const.DEN_TYPES_1]
den_value = kpi_line[Const.DEN_VALUES_1].split(',')
num_type = kpi_line[Const.NUM_TYPES_1]
num_value = kpi_line[Const.NUM_VALUES_1].split(',')
general_filters[den_type] = den_value
sos_filters = {num_type : num_value}
if kpi_line[Const.NUM_TYPES_2]:
num_type_2 = kpi_line[Const.NUM_TYPES_2]
num_value_2 = kpi_line[Const.NUM_VALUES_2].split(',')
sos_filters[num_type_2] = num_value_2
sos_value = self.sos.calculate_share_of_shelf(sos_filters, **general_filters)
# sos_value *= 100
sos_value = round(sos_value, 2)
score = self.get_score_from_range(kpi_name, sos_value)
manufacturer_products = self.all_products[
self.all_products['manufacturer_name'] == num_value[0]].iloc[0]
manufacturer_fk = manufacturer_products["manufacturer_fk"]
all_products = self.all_products[
self.all_products['category'] == den_value[0]].iloc[0]
category_fk = all_products["category_fk"]
numerator_res, denominator_res = self.get_numerator_and_denominator(sos_filters, **general_filters)
self.common.write_to_db_result_new_tables(fk = 1,
numerator_id=manufacturer_fk,
numerator_result= numerator_res,
denominator_id=category_fk,
denominator_result= denominator_res,
result=sos_value,
score= score,
score_after_actions= score)
return sos_value, score
def get_score_from_range(self, kpi_name, sos_value):
store_type = str(self.store_info["store_type"].iloc[0])
self.score_templates[store_type] = self.score_templates[store_type].replace(kpi_name, kpi_name.encode("utf-8"))
score_range = self.score_templates[store_type].query('Kpi == "' + str(kpi_name.encode("utf-8")) +
'" & Low <= ' + str(sos_value) +
' & High >= ' + str(sos_value)+'')
score = score_range['Score'].iloc[0]
return score
def get_kpi_function(self, kpi_type):
"""
transfers every kpi to its own function .encode('utf-8')
:param kpi_type: value from "sheet" column in the main sheet
:return: function
"""
if kpi_type == Const.SOVI:
return self.calculate_sos
else:
Log.warning("The value '{}' in column sheet in the template is not recognized".format(kpi_type))
return None
@staticmethod
def round_result(result):
return round(result, 3)
def get_numerator_and_denominator(self, sos_filters=None, include_empty=False, **general_filters):
if include_empty == self.EXCLUDE_EMPTY and 'product_type' not in sos_filters.keys() + general_filters.keys():
general_filters['product_type'] = (self.EMPTY, self.EXCLUDE_FILTER)
pop_filter = self.toolbox.get_filter_condition(self.scif, **general_filters)
subset_filter = self.toolbox.get_filter_condition(self.scif, **sos_filters)
try:
pop = self.scif
filtered_population = pop[pop_filter]
if filtered_population.empty:
return 0,0
else:
subset_population = filtered_population[subset_filter]
# ratio = TBox.calculate_ratio_sum_field_in_rows(filtered_population, subset_population, Fd.FACINGS)
df = filtered_population
subset_df = subset_population
sum_field = Fd.FACINGS
try:
Validation.is_empty_df(df)
Validation.is_empty_df(subset_df)
Validation.is_subset(df, subset_df)
Validation.df_columns_equality(df, subset_df)
Validation.validate_columns_exists(df, [sum_field])
Validation.validate_columns_exists(subset_df, [sum_field])
Validation.is_none(sum_field)
except Exception, e:
msg = "Data verification failed: {}.".format(e)
# raise Exception(msg)
default_value = 0
numerator = TBox.calculate_frame_column_sum(subset_df, sum_field, default_value)
denominator = TBox.calculate_frame_column_sum(df, sum_field, default_value)
return numerator, denominator
except Exception as e:
Log.error(e.message)
return True
def commit_results(self):
insert_queries = self.merge_insert_queries(self.kpi_results_new_tables_queries)
self.rds_conn.disconnect_rds()
self.rds_conn.connect_rds()
cur = self.rds_conn.db.cursor()
delete_query = SOLARBRQueries.get_delete_session_results_query(self.session_uid, self.session_id)
cur.execute(delete_query)
for query in insert_queries:
cur.execute(query)
self.rds_conn.db.commit()
self.rds_conn.disconnect_rds()
@staticmethod
def merge_insert_queries(insert_queries):
query_groups = {}
for query in insert_queries:
static_data, inserted_data = query.split('VALUES ')
if static_data not in query_groups:
query_groups[static_data] = []
query_groups[static_data].append(inserted_data)
merged_queries = []
for group in query_groups:
merged_queries.append('{0} VALUES {1}'.format(group, ',\n'.join(query_groups[group])))
return merged_queries