class PepsiUSV2ToolBox(GlobalSessionToolBox):
def __init__(self, data_provider, output):
GlobalSessionToolBox.__init__(self, data_provider, output)
self.assortment = Assortment(data_provider)
self.ps_data = PsDataProvider(data_provider)
self.display_in_scene = data_provider.match_display_in_scene
self.static_display = data_provider.static_display
self.manufacturer_fk = int(self.manufacturer_fk)
self._add_display_data_to_scif()
self._add_client_name_and_sub_brand_data()
def main_calculation(self):
self._calculate_display_compliance()
self._calculate_sos_sets()
self.commit_results()
def _add_display_data_to_scif(self):
""" This method adds the relevant display pk and name to Scene Item Facts.
Every scene should have exactly one Display tagged in."""
display_data = self.display_in_scene.drop_duplicates(
Sc.SCENE_FK)[['display_fk', 'display_name', Sc.SCENE_FK]]
self.scif = self.scif.merge(display_data, on=Sc.SCENE_FK, how='left')
def _add_client_name_and_sub_brand_data(self):
""" This method adds the client brand and sub brand fk to scene item facts.
Those two attribute are being taken from custom entity so it couldn't be found in the DataProvider"""
client_brand_custom_entity = self.ps_data.get_custom_entities_df(
Lc.CLIENT_BRAND)
sub_brand_custom_entity = self.ps_data.get_custom_entities_df(
Lc.SUB_BRAND)
self.scif[Lc.CLIENT_BRAND_FK] = self.scif[
Lc.CLIENT_BRAND].apply(lambda value: self._get_entity_fk(
client_brand_custom_entity, value))
self.scif[Lc.SUB_BRAND_FK] = self.scif[Lc.SUB_BRAND].apply(
lambda value: self._get_entity_fk(sub_brand_custom_entity, value))
@staticmethod
def _get_entity_fk(filtered_custom_entity, value_name):
"""This method gets the relevant custom_entity_fk based on the value name
:param: filtered_custom_entity - the relevant custom entity table (filtered by entity type)
:param: value_name - entity name to filter by
"""
relevant_value_entity_df = filtered_custom_entity.loc[
filtered_custom_entity.entity_name == value_name]
if relevant_value_entity_df.empty:
return None
return relevant_value_entity_df.iloc[0]['entity_fk']
def _calculate_display_compliance(self):
"""
This method calculates the Display Compliance KPI that is based on the assortment calculation.
First, it filtered scif by the relevant display and then calculates the assortment KPIs.
Please note that the logic is different than the assortment itself, 5 facings are enough to pass.
"""
group_results = pd.DataFrame()
active_displays = self.static_display.display_name.unique().tolist()
for display in active_displays:
group_results = group_results.append(
self._calculate_assortment_by_display(display))
if not group_results.empty:
self._calculate_display_compliance_store_result(group_results)
def _filter_lvl3_results_by_pallet(self, lvl3_results, display_name):
""" Every assortment to product has an additional attribute with the pallet the client expect it to be.
So this method filters the lvl3_results by the relevant display name."""
lvl3_results[Lc.DISPLAY_NAME] = lvl3_results[Lc.ASSORTMENT_ATTR].map(
lambda x: eval(x)[Lc.DISPLAY_TYPE])
lvl3_results = lvl3_results.loc[lvl3_results[Lc.DISPLAY_NAME] ==
display_name]
display_static_data = self.static_display[['pk',
Lc.DISPLAY_NAME]].rename(
{'pk': Lc.DISPLAY_FK},
axis=1)
lvl3_results = lvl3_results.merge(display_static_data,
on=Lc.DISPLAY_NAME,
how='left')
return lvl3_results
def _calculate_display_compliance_store_result(self, group_results):
""" This method aggregates the group results and calculates the store result for display compliance"""
passed_groups, total_groups = group_results.result.sum() / float(
100), len(group_results)
score = self.get_percentage(passed_groups, total_groups)
kpi_fk = self.common.get_kpi_fk_by_kpi_type(
Lc.DISPLAY_COMP_STORE_LEVEL_FK)
kpi_identifier = '_'.join([
str(self.manufacturer_fk), Lc.ASSORTMENT_ID_SUFFIX,
str(Lc.STORE_LVL)
])
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=passed_groups,
denominator_result=total_groups,
score=score,
result=score,
identifier_result=kpi_identifier)
def _get_filtered_assortment_lvl3_results(self, filtered_scif,
display_name):
""" This method calculates the lvl3 results and filters only the relevant results for the
current display_name"""
lvl3_results = self.assortment.calculate_lvl3_assortment_by_filtered_scif(
filtered_scif)
if not lvl3_results.empty:
lvl3_results = self._filter_lvl3_results_by_pallet(
lvl3_results, display_name)
lvl3_results['in_store'] = lvl3_results['in_store'].apply(
lambda res: res * 100)
return lvl3_results
def _save_assortment_results(self, assortment_res, assort_lvl):
"""
This method converts the assortment DataFrame to the expected DB results.
"""
cols_to_save, cols_rename_dict, denominator_res, kpi_id, parent_id = self._get_ass_consts_by_level(
assort_lvl)
results_df = assortment_res[cols_to_save]
results_df = self._set_kpi_df_identifiers(results_df, kpi_id,
parent_id, assort_lvl,
Lc.ASSORTMENT_ID_SUFFIX)
results_df.rename(cols_rename_dict, inplace=True, axis=1)
results_df[Src.SCORE] = results_df[Src.RESULT]
results_df = results_df.assign(denominator_result=denominator_res)
self.common.save_json_to_new_tables(results_df.to_dict('records'))
def _save_sos_results(self, sos_res_df, sos_lvl, category_fk,
category_name, suffix_identifier):
"""
This method converts the SOS DataFrame to the expected DB results.
:param sos_res_df: A group SOS DataFrame by manufacturer or brand
:param sos_lvl: A const with the relevant SOS lvl the being saved. It affects the consts and identifiers.
"""
cols_rename_dict = self._get_sos_consts_by_level(sos_lvl)
sos_res_df['identifier_parent'] = self._get_sos_identifier_parent(
category_fk, suffix_identifier)
results_df = sos_res_df.rename(cols_rename_dict, inplace=False, axis=1)
results_df[Src.SCORE] = results_df.apply(
lambda row: self.get_percentage(row.numerator_result, row.
denominator_result),
axis=1)
results_df[Src.RESULT] = results_df[Src.SCORE]
results_df['fk'] = self._get_sos_kpi_fk_by_category_and_lvl(
category_name, sos_lvl, suffix_identifier)
self.common.save_json_to_new_tables(results_df.to_dict('records'))
def _get_sos_identifier_parent(self, category_fk, suffix_identifier):
"""This method returns the relevant KPI parent identifier. There a different hierarchies between the
Linear and Facings SOS so this method returns the relevant id"""
if suffix_identifier == Lc.LINEAR_ID_SUFFIX:
return '_'.join([str(category_fk), suffix_identifier])
else:
return self.manufacturer_fk
def _set_kpi_df_identifiers(self,
results_df,
kpi_id_cols,
parent_id_cols,
kpi_level,
suffix_to_add=''):
""" This method gets assortment or SIS results and consts with the columns that should be the
KPI and parent identifiers. It creates the identifier_result and parent_result and adds the
`Should Enter` columns if necessary"""
valid_cols = results_df.columns.unique().to_list()
if kpi_id_cols and set(kpi_id_cols).issubset(valid_cols):
results_df.loc[:, 'identifier_result'] = results_df.apply(
lambda row: self._get_assortment_identifier(
row, kpi_id_cols, kpi_level, suffix_to_add),
axis=1)
if parent_id_cols and set(parent_id_cols).issubset(valid_cols):
results_df.loc[:, 'identifier_parent'] = results_df.apply(
lambda row: self._get_assortment_identifier(
row, parent_id_cols, kpi_level - 1, suffix_to_add),
axis=1)
results_df = results_df.assign(should_enter=True)
return results_df
@staticmethod
def _get_assortment_identifier(row,
cols_to_concat,
kpi_level,
additional_suffix=''):
"""
This method defines an identifier based on the relevant columns.
Please note! The columns must by instance of Integer or Float.
:param cols_to_concat: keys of the series (the row)
:param kpi_level: The relevant kpi level to save
:param additional_suffix (Str): Additional Suffix to add.
@:return A concatenation of the columns' values and the additional_suffix.
E.g: if additional_suffix='SOS' and cols_to_concat = ['brand_fk'] we can get: '7_sos'.
"""
res_id = '_'.join([
str(int(row[col])) if isinstance(row[col], (int, float)) else ''
for col in cols_to_concat
])
res_id = '_'.join([res_id,
str(additional_suffix),
str(kpi_level)]) if additional_suffix else res_id
return res_id
@staticmethod
def _get_sos_consts_by_level(sos_lvl):
"""This method gets the SOS lvl (Sub Brand / Brand / Manufacturer) and returns the relevant consts.
Currently the only relevant const in the rename dict the renaming all of the columns to match the
expected DB cols.
"""
if sos_lvl == Lc.SOS_BRAND_LVL:
return Lc.BRAND_SOS_RENAME_DICT
elif sos_lvl == Lc.SOS_MANU_LVL:
return Lc.ALL_MANU_SOS_RENAME_DICT
elif sos_lvl == Lc.SOS_SUB_BRAND_LVL:
return Lc.SUB_BRAND_SOS_RENAME_DICT
@staticmethod
def _get_ass_consts_by_level(assortment_lvl):
"""This method gets the assortment lvl (SOS_ALL_MANU_LVL / SOS_ALL_BRAND_LVL) and returns a tuple of 3 consts:
rename dict, kpi identifier cols, parent identifier columns
"""
if assortment_lvl == Lc.SKU_LVL:
return Lc.SKU_COLS_TO_SAVE, Lc.SKU_COLS_RENAME, 1, None, Lc.GROUP_IDE
elif assortment_lvl == Lc.GROUP_LVL:
return Lc.GROUP_COLS_TO_KEEP, Lc.GROUP_COLS_RENAME, Lc.GROUP_FACING_TARGET, Lc.GROUP_IDE, Lc.STORE_IDE
def _calculate_display_compliance_group_results(self, lvl3_results):
""" This method gets the filtered lvl3 results and calculates the final results for every group."""
group_res = lvl3_results.groupby(
['kpi_fk_lvl2', 'assortment_group_fk', 'display_fk'],
as_index=False).sum()
group_res[Src.RESULT] = group_res.loc[:, 'facings'].apply(
lambda res: 100 if res >= Lc.GROUP_FACING_TARGET else 0)
group_res = group_res.assign(manufacturer_fk=self.manufacturer_fk)
return group_res
def _calculate_assortment_by_display(self, display_name):
"""This method gets the display name and calculates the assortment for all of the SKUs in this display"""
filtered_scif = self.scif.loc[self.scif.display_name == display_name]
sku_lvl_results = self._get_filtered_assortment_lvl3_results(
filtered_scif, display_name)
if not sku_lvl_results.empty:
group_lvl_results = self._calculate_display_compliance_group_results(
sku_lvl_results)
self._save_assortment_results(sku_lvl_results, Lc.SKU_LVL)
self._save_assortment_results(group_lvl_results, Lc.GROUP_LVL)
return group_lvl_results
return pd.DataFrame()
def _calculate_sos_sets(self):
""" This method calculates the linear and the facings SOS kpi sets.
Each of them has three KPI sets, each per category (Tea, CSD, Energy).
Both has: 1. All manufacturer in category and brand in category KPIs, while the Linear has an additional
own manufacturer sos vs target and the facings regular own manufacturer in store.
"""
self._calculate_linear_and_facings_sos_by_category(Lc.CSD_CAT)
self._calculate_linear_and_facings_sos_by_category(Lc.TEA_CAT)
self._calculate_linear_and_facings_sos_by_category(Lc.ENERGY_CAT)
def _calculate_linear_and_facings_sos_by_category(self, category_name):
"""This method gets the category name and calculate the entire set.
The calculation considers ONLY SKUs and tasks that are relevant to this category!"""
relevant_scenes = self._get_relevant_scene_per_category(category_name)
filtered_scif = self.scif.loc[
(self.scif.category == category_name)
& self.scif.scene_fk.isin(relevant_scenes)]
category_fk = self._get_category_fk_by_name(category_name)
if filtered_scif.empty or not category_fk:
return []
self._calculate_linear_sos_results(filtered_scif, category_fk,
category_name)
self._calculate_facings_sos_results(filtered_scif, category_fk,
category_name)
def _get_category_fk_by_name(self, category_name):
""" This method gets category name and returns the relevant fk. If doesn't exist, it returns 0"""
category_fk = self.all_products.loc[self.all_products.category ==
category_name][
Sc.CATEGORY_FK].unique()
if not category_fk:
Log.error(Lc.WRONG_CATEGORY_LOG.format(category_name))
return 0
return category_fk[0]
def _calculate_linear_sos_results(self, filtered_scif, category_fk,
category_name):
"""
This method gets the filtered scif by category and return the SOS results.
There are three KPIs: 1. Own Manufacturer vs target, 2. All manufacturers in store, all brands in store
"""
self._calculate_sub_brands_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_LINEAR_LEN_ATTR)
self._calculate_brands_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_LINEAR_LEN_ATTR)
self._calculate_manufacturers_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_LINEAR_LEN_ATTR)
self._calculate_own_manufacturer_vs_target(filtered_scif, category_fk,
category_name)
def _calculate_facings_sos_results(self, filtered_scif, category_fk,
category_name):
"""
This method gets the filtered scif by category and return the Facings SOS results.
There are three KPIs: 1. Own Manufacturer in store, 2. All manufacturers in store, all brands in store
"""
self._calculate_sub_brands_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_FACINGS_ATTR)
self._calculate_brands_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_FACINGS_ATTR)
self._calculate_manufacturers_sos(filtered_scif,
category_fk,
category_name,
sos_attr=Lc.SOS_FACINGS_ATTR)
self._calculate_own_manufacturer_facings_sos(filtered_scif,
category_fk)
def _calculate_own_manufacturer_facings_sos(self, filtered_scif,
category_fk):
""" This method calculates the own manufacturer facings sos result """
total_cat_facings = filtered_scif[Lc.SOS_FACINGS_ATTR].sum()
own_manufacturer_scif = filtered_scif.loc[filtered_scif.manufacturer_fk
== self.manufacturer_fk]
own_manu_facings = own_manufacturer_scif[Lc.SOS_FACINGS_ATTR].sum()
score = self.get_percentage(own_manu_facings, total_cat_facings)
kpi_fk = self.get_kpi_fk_by_kpi_type(Lc.FACINGS_SOS_STORE_LEVEL_KPI)
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=own_manu_facings,
denominator_result=total_cat_facings,
context_id=category_fk,
score=score,
result=score,
identifier_result=self.manufacturer_fk)
def _calculate_own_manufacturer_vs_target(self, filtered_scif, category_fk,
category_name):
"""
This method calculates Pepsi linear SOS and compares it to the target of the store
:return: A DataFrame with one row of SOS results
"""
total_store_sos = filtered_scif[Lc.SOS_LINEAR_LEN_ATTR].sum()
own_manufacturer_scif = filtered_scif.loc[filtered_scif.manufacturer_fk
== self.manufacturer_fk]
store_target = self._get_store_target()
own_manu_sos = own_manufacturer_scif[Lc.SOS_LINEAR_LEN_ATTR].sum()
result, score = self._calculate_sos_vs_target_score_and_result(
own_manu_sos, total_store_sos, store_target)
kpi_fk = self._get_sos_kpi_fk_by_category_and_lvl(
category_name, Lc.SOS_OWN_MANU_LVL, Lc.LINEAR_ID_SUFFIX)
kpi_identifier = '_'.join([str(category_fk), Lc.LINEAR_ID_SUFFIX])
self.write_to_db(fk=kpi_fk,
numerator_id=self.manufacturer_fk,
denominator_id=self.store_id,
numerator_result=own_manu_sos,
denominator_result=total_store_sos,
context_id=category_fk,
score=score,
result=result,
identifier_result=kpi_identifier,
target=store_target)
def _get_store_target(self):
""" The store linear SOS target percentage is support to be saved in additional_attribute_4.
In case it doesn't exist will get 100"""
store_target = self.store_info.additional_attribute_4.values[0]
return float(store_target) if store_target else 100
def _calculate_manufacturers_sos(self, filtered_scif, cat_fk, cat_name,
sos_attr):
"""
This method calculates all of the manufacturers linear sos results
"""
manu_res_df = self._calculate_sos_by_attr(filtered_scif, cat_fk,
Sc.MANUFACTURER_FK, sos_attr)
if manu_res_df.empty:
return
kpi_suffix_id = Lc.FACINGS_ID_SUFFIX if sos_attr == Lc.SOS_FACINGS_ATTR else Lc.LINEAR_ID_SUFFIX
self._save_sos_results(manu_res_df,
Lc.SOS_MANU_LVL,
cat_fk,
cat_name,
suffix_identifier=kpi_suffix_id)
def _calculate_brands_sos(self, filtered_scif, cat_fk, cat_name, sos_attr):
"""
This method calculates all of the manufacturers linear sos results
"""
brands_res_df = self._calculate_sos_by_attr(filtered_scif, cat_fk,
[Lc.CLIENT_BRAND_FK],
sos_attr)
if brands_res_df.empty:
return
kpi_suffix_id = Lc.FACINGS_ID_SUFFIX if sos_attr == Lc.SOS_FACINGS_ATTR else Lc.LINEAR_ID_SUFFIX
self._save_sos_results(brands_res_df,
Lc.SOS_BRAND_LVL,
cat_fk,
cat_name,
suffix_identifier=kpi_suffix_id)
def _calculate_sub_brands_sos(self, filtered_scif, cat_fk, cat_name,
sos_attr):
"""
This method calculates all of the manufacturers linear sos results
"""
sub_brands_res_df = self._calculate_sos_by_attr(
filtered_scif, cat_fk, [Lc.SUB_BRAND_FK], sos_attr)
if sub_brands_res_df.empty:
return
kpi_suffix_id = Lc.FACINGS_ID_SUFFIX if sos_attr == Lc.SOS_FACINGS_ATTR else Lc.LINEAR_ID_SUFFIX
self._save_sos_results(sub_brands_res_df, Lc.SOS_SUB_BRAND_LVL, cat_fk,
cat_name, kpi_suffix_id)
@staticmethod
def _calculate_sos_by_attr(filtered_scif, category_fk, attr_to_group_by,
sos_attr):
"""
This method gets the filtered scif and the entity to group by and it calculates the linear sos.
And the end it adds to the results the category_fk and the total sos.
:param filtered_scif: Filtered Scene item facts with SKU with the current category being calculated
:param category_fk: current category_fk that is being calculated now (CSD / TEA / ENERGY)
:param attr_to_group_by: attribute to group the scif by E.g: 'manufacturer_fk' / 'brand_fk'
:param sos_attr: The SOS attribute to sum ('facings' or 'gross_len_ign_stack')
:return: A DataFrame with 4 columns: `entity_to_group_by`, net_len_ign_stack, total_linear_sos, category_fk
"""
results_df = filtered_scif.groupby(attr_to_group_by,
as_index=False)[sos_attr].sum()
total_sos = results_df[sos_attr].sum()
results_df = results_df.assign(category_fk=category_fk,
total_sos=total_sos,
should_enter=True)
return results_df
def _calculate_sos_vs_target_score_and_result(self, sum_of_linear_sos,
total_store_sos,
store_target):
""" This method gets the own manufacturer sos sum and the store target and calculates the score and result.
The score is basically numerator / denominator and the results is 100 if the target was passed"""
score = min(self.get_percentage(sum_of_linear_sos, total_store_sos),
100)
result = 100 if score >= store_target else 0
return score, result
def _get_sos_kpi_fk_by_category_and_lvl(self, category_name, lvl,
sos_attr):
"""This method gets a category name and sos kpi lvl and returns the relevant kpi fk"""
general_kpi_name = Lc.MAPPER_KPI_LVL_AND_NAME[lvl]
category_name_in_the_kpi = self._get_category_name_in_kpi(
category_name)
sos_attr_in_kpi = self._get_sos_attr_in_kpi(sos_attr)
kpi_fk = self.get_kpi_fk_by_kpi_type(
general_kpi_name.format(category_name_in_the_kpi, sos_attr_in_kpi))
return kpi_fk
@staticmethod
def _get_category_name_in_kpi(category_name):
""" The KPI names are having the category in it, this method returns the exact name that appears in the
KPI. This method created so in case the category name will change, the kpi won't be affected."""
upper_category_name = category_name.upper()
if 'TEA' in upper_category_name:
return 'Tea'
elif 'ENERGY' in upper_category_name:
return 'Energy'
elif 'CSD' in upper_category_name:
return 'CSD'
else:
Log.error(Lc.MISSING_KPI_FOR_CATEGORY.format(category_name))
@staticmethod
def _get_sos_attr_in_kpi(sos_attr):
""" The KPI names are having the sos_attr in it, this method returns the exact name that appears in the
KPI. This method created so in case the sos attribute will change, the kpi won't be affected."""
upper_category_name = sos_attr.upper()
if 'FACINGS' in upper_category_name:
return 'Facings'
else:
return 'Linear'
def _get_relevant_scene_per_category(self, category_name):
""" This method returns the relevant scenes to consider in the sos calculation.
The project team defines specific scene that we should consider them, and ONLY them.
"""
relevant_task_names_lst = Lc.SCENE_CATEGORY_MAPPER[category_name]
relevant_scenes_df = self.scif.loc[self.scif.template_name.isin(
relevant_task_names_lst)]
return relevant_scenes_df.scene_fk.unique().tolist()
class StraussfritolayilUtil(UnifiedKPISingleton):
def __init__(self, output, data_provider):
super(StraussfritolayilUtil, self).__init__(data_provider)
self.output = output
self.common = Common(self.data_provider)
self.project_name = self.data_provider.project_name
self.session_uid = self.data_provider.session_uid
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.products = self.data_provider[Data.PRODUCTS]
self.all_products = self.data_provider[Data.ALL_PRODUCTS]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.brand_mix_df = self.get_brand_mix_df()
self.add_sub_brand_to_scif()
self.add_brand_mix_to_scif()
self.match_probe_in_scene = self.ps_data.get_product_special_attribute_data(self.session_uid)
self.match_product_in_scene = self.data_provider[Data.MATCHES]
if not self.match_product_in_scene.empty:
self.match_product_in_scene = self.match_product_in_scene.merge(self.scif[Consts.RELEVENT_FIELDS],
on=["scene_fk", "product_fk"], how="left")
self.filter_scif_and_mpis_to_contain_only_primary_shelf()
else:
unique_fields = [ele for ele in Consts.RELEVENT_FIELDS if ele not in ["product_fk", "scene_fk"]]
self.match_product_in_scene = pd.concat([self.match_product_in_scene,
pd.DataFrame(columns=unique_fields)], axis=1)
self.match_product_in_scene_wo_hangers = self.exclude_special_attribute_products(df=self.match_product_in_scene)
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_info = self.data_provider[Data.STORE_INFO]
self.additional_attribute_2 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_2].values[0]
self.additional_attribute_3 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_3].values[0]
self.additional_attribute_4 = self.store_info[Consts.ADDITIONAL_ATTRIBUTE_4].values[0]
self.store_id = self.store_info['store_fk'].values[0] if self.store_info['store_fk'] is not None else 0
self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng)
self.toolbox = GENERALToolBox(self.data_provider)
self.kpi_external_targets = self.ps_data.get_kpi_external_targets(key_fields=Consts.KEY_FIELDS,
data_fields=Consts.DATA_FIELDS)
self.filter_external_targets()
self.assortment = Assortment(self.data_provider, self.output)
self.lvl3_assortment = self.set_updated_assortment()
self.own_manuf_fk = int(self.data_provider.own_manufacturer.param_value.values[0])
self.own_manufacturer_matches_wo_hangers = self.match_product_in_scene_wo_hangers[
self.match_product_in_scene_wo_hangers['manufacturer_fk'] == self.own_manuf_fk]
def set_updated_assortment(self):
assortment_result = self.assortment.get_lvl3_relevant_ass()
if assortment_result.empty:
return pd.DataFrame(columns=["kpi_fk_lvl2", "kpi_fk_lvl3"])
assortment_result = self.calculate_lvl3_assortment(assortment_result)
replacement_eans_df = pd.DataFrame([json_normalize(json.loads(js)).values[0] for js
in assortment_result['additional_attributes']])
replacement_eans_df.columns = [Consts.REPLACMENT_EAN_CODES]
replacement_eans_df = replacement_eans_df[Consts.REPLACMENT_EAN_CODES].apply(lambda row: [
x.strip() for x in str(row).split(",")] if row else None)
assortment_result = assortment_result.join(replacement_eans_df)
assortment_result['facings_all_products'] = assortment_result['facings'].copy()
assortment_result['facings_all_products_wo_hangers'] = assortment_result['facings_wo_hangers'].copy()
assortment_result = self.handle_replacment_products_row(assortment_result)
return assortment_result
def filter_external_targets(self):
self.kpi_external_targets = self.kpi_external_targets[
(self.kpi_external_targets[Consts.ADDITIONAL_ATTRIBUTE_2].str.encode("utf8").isin(
[None, self.additional_attribute_2.encode("utf8")])) &
(self.kpi_external_targets[Consts.ADDITIONAL_ATTRIBUTE_3].str.encode("utf8").isin(
[None, self.additional_attribute_3.encode("utf8")])) &
(self.kpi_external_targets[Consts.ADDITIONAL_ATTRIBUTE_4].str.encode("utf8").isin(
[None, self.additional_attribute_4.encode("utf8")]))]
def calculate_lvl3_assortment(self, assortment_result):
"""
:return: data frame on the sku level with the following fields:
['assortment_group_fk', 'assortment_fk', 'target', 'product_fk', 'in_store', 'kpi_fk_lvl1',
'kpi_fk_lvl2', 'kpi_fk_lvl3', 'group_target_date', 'assortment_super_group_fk',
'super_group_target', 'additional_attributes']. Indicates whether the product was in the store (1) or not (0).
"""
if assortment_result.empty:
return assortment_result
assortment_result['in_store_wo_hangers'] = assortment_result['in_store'].copy()
products_in_session = list(self.match_product_in_scene['product_fk'].values)
products_in_session_wo_hangers = list(self.match_product_in_scene_wo_hangers['product_fk'].values)
assortment_result.loc[assortment_result['product_fk'].isin(products_in_session), 'in_store'] = 1
assortment_result.loc[assortment_result['product_fk'].isin(products_in_session_wo_hangers),
'in_store_wo_hangers'] = 1
assortment_result['facings'] = 0
assortment_result['facings_wo_hangers'] = 0
product_assort = assortment_result['product_fk'].unique()
for sku in product_assort:
assortment_result.loc[assortment_result['product_fk'] == sku, 'facings'] = \
len(self.match_product_in_scene[self.match_product_in_scene['product_fk'] == sku])
assortment_result.loc[assortment_result['product_fk'] == sku, 'facings_wo_hangers'] = \
len(self.match_product_in_scene_wo_hangers[self.match_product_in_scene_wo_hangers['product_fk'] == sku])
return assortment_result
def handle_replacment_products_row(self, assortment_result):
additional_products_df = assortment_result[~assortment_result[Consts.REPLACMENT_EAN_CODES].isnull()]
products_in_session = set(self.match_product_in_scene['product_ean_code'].values)
products_in_session_wo_hangers = set(self.match_product_in_scene_wo_hangers['product_ean_code'].values)
for i, row in additional_products_df.iterrows():
replacement_products = row[Consts.REPLACMENT_EAN_CODES]
facings = len(self.match_product_in_scene[self.match_product_in_scene[
'product_ean_code'].isin(replacement_products)])
facings_wo_hangers = len(self.match_product_in_scene_wo_hangers[self.match_product_in_scene_wo_hangers[
'product_ean_code'].isin(replacement_products)])
assortment_result.loc[i, 'facings_all_products'] = facings + row['facings']
assortment_result.loc[i, 'facings_all_products_wo_hangers'] = facings_wo_hangers + row['facings_wo_hangers']
if row['in_store'] != 1:
for sku in replacement_products:
if sku in products_in_session:
product_df = self.all_products[self.all_products['product_ean_code'] == sku]['product_fk']
assortment_result.loc[i, 'product_fk'] = product_df.values[0]
assortment_result.loc[i, 'in_store'] = 1
break
if row['in_store_wo_hangers'] != 1:
for sku in replacement_products:
if sku in products_in_session_wo_hangers:
product_df = self.all_products[self.all_products['product_ean_code'] == sku]['product_fk']
assortment_result.loc[i, 'product_fk'] = product_df.values[0]
assortment_result.loc[i, 'in_store_wo_hangers'] = 1
break
return assortment_result
def filter_scif_and_mpis_to_contain_only_primary_shelf(self):
self.scif = self.scif[self.scif.location_type == Consts.PRIMARY_SHELF]
self.match_product_in_scene = self.match_product_in_scene[self.match_product_in_scene.location_type ==
Consts.PRIMARY_SHELF]
def add_sub_brand_to_scif(self):
sub_brand_df = self.ps_data.get_custom_entities_df(entity_type_name='Sub_Brand_Local')
sub_brand_df = sub_brand_df[['entity_name', 'entity_fk']].copy()
# sub_brand_df['entity_name'] = sub_brand_df['entity_name'].str.lower()
sub_brand_df.rename({'entity_fk': 'sub_brand_fk'}, axis='columns', inplace=True)
# delete duplicates by name and entity_type_fk to avoid recognition duplicates.
sub_brand_df.drop_duplicates(subset=['entity_name'], keep='first', inplace=True)
self.scif['Sub_Brand_Local'] = self.scif['Sub_Brand_Local'].fillna('no value')
self.scif = self.scif.merge(sub_brand_df, left_on='Sub_Brand_Local', right_on="entity_name", how="left")
self.scif['sub_brand_fk'].fillna(Consts.SUB_BRAND_NO_VALUE, inplace=True)
def get_brand_mix_df(self):
brand_mix_df = self.ps_data.get_custom_entities_df(entity_type_name='Brand_Mix')
brand_mix_df = brand_mix_df[['entity_name', 'entity_fk']].copy()
brand_mix_df.rename({'entity_fk': 'brand_mix_fk'}, axis='columns', inplace=True)
# delete duplicates by name and entity_type_fk to avoid recognition duplicates.
brand_mix_df.drop_duplicates(subset=['entity_name'], keep='first', inplace=True)
return brand_mix_df
def add_brand_mix_to_scif(self):
self.scif['Brand_Mix'] = self.scif['Brand_Mix'].fillna('no value')
self.scif = self.scif.merge(self.brand_mix_df, left_on='Brand_Mix', right_on="entity_name", how="left")
self.scif['brand_mix_fk'].fillna(Consts.BRAND_MIX_NO_VALUE, inplace=True)
@staticmethod
def calculate_sos_result(numerator, denominator):
if denominator == 0:
return 0
result = 100 * round((numerator / float(denominator)), 3)
return result
def exclude_special_attribute_products(self, df):
"""
Helper to exclude smart_attribute products
:return: filtered df without smart_attribute products
"""
if self.match_probe_in_scene.empty:
return df
smart_attribute_df = self.match_probe_in_scene[self.match_probe_in_scene['name'] == Consts.ADDITIONAL_DISPLAY]
if smart_attribute_df.empty:
return df
match_product_in_probe_fks = smart_attribute_df['match_product_in_probe_fk'].tolist()
df = df[~df['probe_match_fk'].isin(match_product_in_probe_fks)]
return df
