class HEINZCRToolBox: LVL3_HEADERS = ['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'] LVL2_HEADERS = ['assortment_group_fk', 'assortment_fk', 'target', 'passes', 'total', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'group_target_date'] LVL1_HEADERS = ['assortment_group_fk', 'target', 'passes', 'total', 'kpi_fk_lvl1'] ASSORTMENT_FK = 'assortment_fk' ASSORTMENT_GROUP_FK = 'assortment_group_fk' ASSORTMENT_SUPER_GROUP_FK = 'assortment_super_group_fk' BRAND_VARIENT = 'brand_varient' NUMERATOR = 'numerator' DENOMINATOR = 'denominator' DISTRIBUTION_KPI = 'Distribution - SKU' OOS_SKU_KPI = 'OOS - SKU' OOS_KPI = 'OOS' def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = CommonV2 # remove later 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.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_results_queries = [] self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider, common=self.common_v2) self.store_sos_policies = self.ps_data_provider.get_store_policies() self.labels = self.ps_data_provider.get_labels() self.store_info = self.data_provider[Data.STORE_INFO] self.store_info = self.ps_data_provider.get_ps_store_info(self.store_info) self.country = self.store_info['country'].iloc[0] self.current_date = datetime.now() self.extra_spaces_template = pd.read_excel(Const.EXTRA_SPACES_RELEVANT_SUB_CATEGORIES_PATH) self.store_targets = pd.read_excel(Const.STORE_TARGETS_PATH) self.sub_category_weight = pd.read_excel(Const.SUB_CATEGORY_TARGET_PATH, sheetname='category_score') self.kpi_weights = pd.read_excel(Const.SUB_CATEGORY_TARGET_PATH, sheetname='max_weight') self.targets = self.ps_data_provider.get_kpi_external_targets() self.store_assortment = PSAssortmentDataProvider( self.data_provider).execute(policy_name=None) self.supervisor_target = self.get_supervisor_target() try: self.sub_category_assortment = pd.merge(self.store_assortment, self.all_products.loc[:, ['product_fk', 'sub_category', 'sub_category_fk']], how='left', on='product_fk') self.sub_category_assortment = \ self.sub_category_assortment[~self.sub_category_assortment['assortment_name'].str.contains( 'ASSORTMENT')] self.sub_category_assortment = pd.merge(self.sub_category_assortment, self.sub_category_weight, how='left', left_on='sub_category', right_on='Category') except KeyError: self.sub_category_assortment = pd.DataFrame() self.update_score_sub_category_weights() try: self.store_assortment_without_powerskus = \ self.store_assortment[self.store_assortment['assortment_name'].str.contains('ASSORTMENT')] except KeyError: self.store_assortment_without_powerskus = pd.DataFrame() self.adherence_results = pd.DataFrame(columns=['product_fk', 'trax_average', 'suggested_price', 'into_interval', 'min_target', 'max_target', 'percent_range']) self.extra_spaces_results = pd.DataFrame( columns=['sub_category_fk', 'template_fk', 'count']) self.powersku_scores = {} self.powersku_empty = {} self.powersku_bonus = {} self.powersku_price = {} self.powersku_sos = {} def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ if self.scif.empty: return # these function must run first # self.adherence_results = self.heinz_global_price_adherence(pd.read_excel(Const.PRICE_ADHERENCE_TEMPLATE_PATH, # sheetname="Price Adherence")) self.adherence_results = self.heinz_global_price_adherence(self.targets) self.extra_spaces_results = self.heinz_global_extra_spaces() self.set_relevant_sub_categories() # this isn't relevant to the 'Perfect Score' calculation self.heinz_global_distribution_per_category() self.calculate_assortment() self.calculate_powersku_assortment() self.main_sos_calculation() self.calculate_powersku_price_adherence() self.calculate_perfect_store_extra_spaces() self.check_bonus_question() self.calculate_perfect_sub_category() def calculate_assortment(self): if self.store_assortment_without_powerskus.empty: return products_in_store = self.scif[self.scif['facings'] > 0]['product_fk'].unique().tolist() pass_count = 0 total_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Distribution') identifier_dict = self.common_v2.get_dictionary(kpi_fk=total_kpi_fk) oos_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('OOS') oos_identifier_dict = self.common_v2.get_dictionary(kpi_fk=oos_kpi_fk) for row in self.store_assortment_without_powerskus.itertuples(): result = 0 if row.product_fk in products_in_store: result = 1 pass_count += 1 sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('Distribution - SKU') self.common_v2.write_to_db_result(sku_kpi_fk, numerator_id=row.product_fk, denominator_id=row.assortment_fk, result=result, identifier_parent=identifier_dict, should_enter=True) oos_result = 0 if result else 1 oos_sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type('OOS - SKU') self.common_v2.write_to_db_result(oos_sku_kpi_fk, numerator_id=row.product_fk, denominator_id=row.assortment_fk, result=oos_result, identifier_parent=oos_identifier_dict, should_enter=True) number_of_products_in_assortment = len(self.store_assortment_without_powerskus) if number_of_products_in_assortment: total_result = (pass_count / float(number_of_products_in_assortment)) * 100 oos_products = number_of_products_in_assortment - pass_count oos_result = (oos_products / float(number_of_products_in_assortment)) * 100 else: total_result = 0 oos_products = number_of_products_in_assortment oos_result = number_of_products_in_assortment self.common_v2.write_to_db_result(total_kpi_fk, numerator_id=Const.OWN_MANUFACTURER_FK, denominator_id=self.store_id, numerator_result=pass_count, denominator_result=number_of_products_in_assortment, result=total_result, identifier_result=identifier_dict) self.common_v2.write_to_db_result(oos_kpi_fk, numerator_id=Const.OWN_MANUFACTURER_FK, denominator_id=self.store_id, numerator_result=oos_products, denominator_result=number_of_products_in_assortment, result=oos_result, identifier_result=oos_identifier_dict) def calculate_powersku_assortment(self): if self.sub_category_assortment.empty: return 0 sub_category_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_SUB_CATEGORY) sku_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU) target_kpi_weight = float( self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS['POWERSKU']].iloc[ 0]) kpi_weight = self.get_kpi_weight('POWERSKU') products_in_session = self.scif[self.scif['facings'] > 0]['product_fk'].unique().tolist() self.sub_category_assortment['in_session'] = \ self.sub_category_assortment.loc[:, 'product_fk'].isin(products_in_session) # save PowerSKU results at SKU level for sku in self.sub_category_assortment[ ['product_fk', 'sub_category_fk', 'in_session', 'sub_category']].itertuples(): parent_dict = self.common_v2.get_dictionary( kpi_fk=sub_category_kpi_fk, sub_category_fk=sku.sub_category_fk) relevant_sub_category_df = self.sub_category_assortment[ self.sub_category_assortment['sub_category'] == sku.sub_category] if relevant_sub_category_df.empty: sub_category_count = 0 else: sub_category_count = len(relevant_sub_category_df) result = 1 if sku.in_session else 0 score = result * (target_kpi_weight / float(sub_category_count)) self.common_v2.write_to_db_result(sku_kpi_fk, numerator_id=sku.product_fk, denominator_id=sku.sub_category_fk, score=score, result=result, identifier_parent=parent_dict, should_enter=True) # save PowerSKU results at sub_category level aggregated_results = self.sub_category_assortment.groupby('sub_category_fk').agg( {'in_session': 'sum', 'product_fk': 'count'}).reset_index().rename( columns={'product_fk': 'product_count'}) aggregated_results['percent_complete'] = \ aggregated_results.loc[:, 'in_session'] / aggregated_results.loc[:, 'product_count'] aggregated_results['result'] = aggregated_results['percent_complete'] for sub_category in aggregated_results.itertuples(): identifier_dict = self.common_v2.get_dictionary(kpi_fk=sub_category_kpi_fk, sub_category_fk=sub_category.sub_category_fk) result = sub_category.result score = result * kpi_weight self.powersku_scores[sub_category.sub_category_fk] = score self.common_v2.write_to_db_result(sub_category_kpi_fk, numerator_id=sub_category.sub_category_fk, denominator_id=self.store_id, identifier_parent=sub_category.sub_category_fk, identifier_result=identifier_dict, result=result * 100, score=score, weight=target_kpi_weight, target=target_kpi_weight, should_enter=True) def heinz_global_distribution_per_category(self): relevant_stores = pd.DataFrame(columns=self.store_sos_policies.columns) for row in self.store_sos_policies.itertuples(): policies = json.loads(row.store_policy) df = self.store_info for key, value in policies.items(): try: df_1 = df[df[key].isin(value)] except KeyError: continue if not df_1.empty: stores = self.store_sos_policies[(self.store_sos_policies['store_policy'] == row.store_policy.encode('utf-8')) & ( self.store_sos_policies[ 'target_validity_start_date'] <= datetime.date( self.current_date))] if stores.empty: relevant_stores = stores else: relevant_stores = relevant_stores.append(stores, ignore_index=True) relevant_stores = relevant_stores.drop_duplicates(subset=['kpi', 'sku_name', 'target', 'sos_policy'], keep='last') for row in relevant_stores.itertuples(): sos_policy = json.loads(row.sos_policy) numerator_key = sos_policy[self.NUMERATOR].keys()[0] denominator_key = sos_policy[self.DENOMINATOR].keys()[0] numerator_val = sos_policy[self.NUMERATOR][numerator_key] denominator_val = sos_policy[self.DENOMINATOR][denominator_key] target = row.target * 100 if numerator_key == 'manufacturer': numerator_key = numerator_key + '_name' if denominator_key == 'sub_category' \ and denominator_val.lower() != 'all' \ and json.loads(row.store_policy).get('store_type') \ and len(json.loads(row.store_policy).get('store_type')) == 1: try: denominator_id = self.all_products[self.all_products[denominator_key] == denominator_val][ denominator_key + '_fk'].values[0] numerator_id = self.all_products[self.all_products[numerator_key] == numerator_val][ numerator_key.split('_')[0] + '_fk'].values[0] # self.common.write_to_db_result_new_tables(fk=12, numerator_id=numerator_id, # numerator_result=None, # denominator_id=denominator_id, # denominator_result=None, # result=target) self.common_v2.write_to_db_result(fk=12, numerator_id=numerator_id, numerator_result=None, denominator_id=denominator_id, denominator_result=None, result=target) except Exception as e: Log.warning(denominator_key + ' - - ' + denominator_val) def calculate_perfect_store(self): pass def calculate_perfect_sub_category(self): kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.PERFECT_STORE_SUB_CATEGORY) parent_kpi = self.common_v2.get_kpi_fk_by_kpi_type(Const.PERFECT_STORE) total_score = 0 sub_category_fk_list = [] kpi_type_dict_scores = [self.powersku_scores, self.powersku_empty, self.powersku_price, self.powersku_sos] for kpi_dict in kpi_type_dict_scores: sub_category_fk_list.extend(kpi_dict.keys()) kpi_weight_perfect_store = 0 if self.country in self.sub_category_weight.columns.to_list(): kpi_weight_perfect_store = self.sub_category_weight[self.country][ self.sub_category_weight['Category'] == Const.PERFECT_STORE_KPI_WEIGHT] if not kpi_weight_perfect_store.empty: kpi_weight_perfect_store = kpi_weight_perfect_store.iloc[0] unique_sub_cat_fks = list(dict.fromkeys(sub_category_fk_list)) sub_category_fks = self.sub_category_weight.sub_category_fk.unique().tolist() relevant_sub_cat_list = [x for x in sub_category_fks if str(x) != 'nan'] # relevant_sub_cat_list = self.sub_category_assortment['sub_category_fk'][ # self.sub_category_assortment['Category'] != pd.np.nan].unique().tolist() for sub_cat_fk in unique_sub_cat_fks: if sub_cat_fk in relevant_sub_cat_list: bonus_score = 0 try: bonus_score = self.powersku_bonus[sub_cat_fk] except: pass sub_cat_weight = self.get_weight(sub_cat_fk) sub_cat_score = self.calculate_sub_category_sum(kpi_type_dict_scores, sub_cat_fk) result = sub_cat_score score = (result * sub_cat_weight) + bonus_score total_score += score self.common_v2.write_to_db_result(kpi_fk, numerator_id=sub_cat_fk, denominator_id=self.store_id, result=result, score=score, identifier_parent=parent_kpi, identifier_result=sub_cat_fk, weight=sub_cat_weight * 100, should_enter=True) self.common_v2.write_to_db_result(parent_kpi, numerator_id=Const.OWN_MANUFACTURER_FK, denominator_id=self.store_id, result=total_score, score=total_score, identifier_result=parent_kpi, target=kpi_weight_perfect_store, should_enter=True) def main_sos_calculation(self): relevant_stores = pd.DataFrame(columns=self.store_sos_policies.columns) for row in self.store_sos_policies.itertuples(): policies = json.loads(row.store_policy) df = self.store_info for key, value in policies.items(): try: if key != 'additional_attribute_3': df1 = df[df[key].isin(value)] except KeyError: continue if not df1.empty: stores = \ self.store_sos_policies[(self.store_sos_policies['store_policy'].str.encode( 'utf-8') == row.store_policy.encode('utf-8')) & (self.store_sos_policies['target_validity_start_date'] <= datetime.date( self.current_date))] if stores.empty: relevant_stores = stores else: relevant_stores = relevant_stores.append(stores, ignore_index=True) relevant_stores = relevant_stores.drop_duplicates(subset=['kpi', 'sku_name', 'target', 'sos_policy'], keep='last') results_df = pd.DataFrame(columns=['sub_category', 'sub_category_fk', 'score']) sos_sub_category_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.SOS_SUB_CATEGORY) for row in relevant_stores.itertuples(): sos_policy = json.loads(row.sos_policy) numerator_key = sos_policy[self.NUMERATOR].keys()[0] denominator_key = sos_policy[self.DENOMINATOR].keys()[0] numerator_val = sos_policy[self.NUMERATOR][numerator_key] denominator_val = sos_policy[self.DENOMINATOR][denominator_key] json_policy = json.loads(row.store_policy) kpi_fk = row.kpi # This is to assign the KPI to SOS_manufacturer_category_GLOBAL if json_policy.get('store_type') and len(json_policy.get('store_type')) > 1: kpi_fk = 8 if numerator_key == 'manufacturer': numerator_key = numerator_key + '_name' # we need to include 'Philadelphia' as a manufacturer for all countries EXCEPT Chile if self.country == 'Chile': numerator_values = [numerator_val] else: numerator_values = [numerator_val, 'Philadelphia'] else: # if the numerator isn't 'manufacturer', we just need to convert the value to a list numerator_values = [numerator_val] if denominator_key == 'sub_category': include_stacking_list = ['Nuts', 'DRY CHEESE', 'IWSN', 'Shredded', 'SNACK'] if denominator_val in include_stacking_list: facings_field = 'facings' else: facings_field = 'facings_ign_stack' else: facings_field = 'facings_ign_stack' if denominator_key == 'sub_category' and denominator_val.lower() == 'all': # Here we are talkin on a KPI when the target have no denominator, # the calculation should be done on Numerator only numerator = self.scif[(self.scif[numerator_key] == numerator_val) & (self.scif['location_type'] == 'Primary Shelf') ][facings_field].sum() kpi_fk = 9 denominator = None denominator_id = None else: numerator = self.scif[(self.scif[numerator_key].isin(numerator_values)) & (self.scif[denominator_key] == denominator_val) & (self.scif['location_type'] == 'Primary Shelf')][facings_field].sum() denominator = self.scif[(self.scif[denominator_key] == denominator_val) & (self.scif['location_type'] == 'Primary Shelf')][facings_field].sum() try: if denominator is not None: denominator_id = self.all_products[self.all_products[denominator_key] == denominator_val][ denominator_key + '_fk'].values[0] if numerator is not None: numerator_id = self.all_products[self.all_products[numerator_key] == numerator_val][ numerator_key.split('_')[0] + '_fk'].values[0] sos = 0 if numerator and denominator: sos = np.divide(float(numerator), float(denominator)) * 100 score = 0 target = row.target * 100 if sos >= target: score = 100 identifier_parent = None should_enter = False if denominator_key == 'sub_category' and kpi_fk == row.kpi: # if this a sub_category result, save it to the results_df for 'Perfect Store' store results_df.loc[len(results_df)] = [denominator_val, denominator_id, score / 100] identifier_parent = self.common_v2.get_dictionary(kpi_fk=sos_sub_category_kpi_fk, sub_category_fk=denominator_id) should_enter = True manufacturer = None self.common_v2.write_to_db_result(kpi_fk, numerator_id=numerator_id, numerator_result=numerator, denominator_id=denominator_id, denominator_result=denominator, result=target, score=sos, target=target, score_after_actions=manufacturer, identifier_parent=identifier_parent, should_enter=should_enter) except Exception as e: Log.warning(denominator_key + ' - - ' + denominator_val) # if there are no sub_category sos results, there's no perfect store information to be saved if len(results_df) == 0: return 0 # save aggregated results for each sub category kpi_weight = self.get_kpi_weight('SOS') for row in results_df.itertuples(): identifier_result = \ self.common_v2.get_dictionary(kpi_fk=sos_sub_category_kpi_fk, sub_category_fk=row.sub_category_fk) # sub_cat_weight = self.get_weight(row.sub_category_fk) result = row.score score = result * kpi_weight self.powersku_sos[row.sub_category_fk] = score # limit results so that aggregated results can only add up to 3 self.common_v2.write_to_db_result(sos_sub_category_kpi_fk, numerator_id=row.sub_category_fk, denominator_id=self.store_id, result=row.score, score=score, identifier_parent=row.sub_category_fk, identifier_result=identifier_result, weight=kpi_weight, target=kpi_weight, should_enter=True) def calculate_powersku_price_adherence(self): adherence_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_PRICE_ADHERENCE) adherence_sub_category_kpi_fk = \ self.common_v2.get_kpi_fk_by_kpi_type(Const.POWER_SKU_PRICE_ADHERENCE_SUB_CATEGORY) if self.sub_category_assortment.empty: return False results = pd.merge(self.sub_category_assortment, self.adherence_results, how='left', on='product_fk') results['into_interval'].fillna(0, inplace=True) for row in results.itertuples(): parent_dict = self.common_v2.get_dictionary(kpi_fk=adherence_sub_category_kpi_fk, sub_category_fk=row.sub_category_fk) score_value = 'Not Present' in_session = row.in_session if in_session: if not pd.isna(row.trax_average) and row.suggested_price: price_in_interval = 1 if row.into_interval == 1 else 0 if price_in_interval == 1: score_value = 'Pass' else: score_value = 'Fail' else: score_value = 'No Price' score = Const.PRESENCE_PRICE_VALUES[score_value] self.common_v2.write_to_db_result(adherence_kpi_fk, numerator_id=row.product_fk, denominator_id=row.sub_category_fk, result=row.trax_average, score=score, target=row.suggested_price, numerator_result=row.min_target, denominator_result=row.max_target, weight=row.percent_range, identifier_parent=parent_dict, should_enter=True) aggregated_results = results.groupby('sub_category_fk').agg( {'into_interval': 'sum', 'product_fk': 'count'}).reset_index().rename( columns={'product_fk': 'product_count'}) aggregated_results['percent_complete'] = \ aggregated_results.loc[:, 'into_interval'] / aggregated_results.loc[:, 'product_count'] for row in aggregated_results.itertuples(): identifier_result = self.common_v2.get_dictionary(kpi_fk=adherence_sub_category_kpi_fk, sub_category_fk=row.sub_category_fk) kpi_weight = self.get_kpi_weight('PRICE') result = row.percent_complete score = result * kpi_weight self.powersku_price[row.sub_category_fk] = score self.common_v2.write_to_db_result(adherence_sub_category_kpi_fk, numerator_id=row.sub_category_fk, denominator_id=self.store_id, result=result, score=score, numerator_result=row.into_interval, denominator_result=row.product_count, identifier_parent=row.sub_category_fk, identifier_result=identifier_result, weight=kpi_weight, target=kpi_weight, should_enter=True) def heinz_global_price_adherence(self, config_df): config_df = config_df.sort_values(by=["received_time"], ascending=False).drop_duplicates( subset=['start_date', 'end_date', 'ean_code', 'store_type'], keep="first") if config_df.empty: Log.warning("No external_targets data found - Price Adherence will not be calculated") return self.adherence_results self.match_product_in_scene.loc[self.match_product_in_scene['price'].isna(), 'price'] = \ self.match_product_in_scene.loc[self.match_product_in_scene['price'].isna(), 'promotion_price'] # =============== remove after updating logic to support promotional pricing =============== results_df = self.adherence_results my_config_df = \ config_df[config_df['store_type'].str.encode('utf-8') == self.store_info.store_type[0].encode('utf-8')] products_in_session = self.scif['product_ean_code'].unique().tolist() products_in_session = [ean for ean in products_in_session if ean is not pd.np.nan and ean is not None] my_config_df = my_config_df[my_config_df['ean_code'].isin(products_in_session)] for row in my_config_df.itertuples(): product_pk = \ self.all_products[self.all_products['product_ean_code'] == row.ean_code]['product_fk'].iloc[0] mpisc_df_price = \ self.match_product_in_scene[(self.match_product_in_scene['product_fk'] == product_pk) | (self.match_product_in_scene[ 'substitution_product_fk'] == product_pk)]['price'] try: suggested_price = float(row.suggested_price) except Exception as e: Log.error("Product with ean_code {} is not in the configuration file for customer type {}" .format(row.ean_code, self.store_info.store_type[0].encode('utf-8'))) break percentage_weight = int(row.percentage_weight) upper_percentage = (100 + percentage_weight) / float(100) lower_percentage = (100 - percentage_weight) / float(100) min_price = suggested_price * lower_percentage max_price = suggested_price * upper_percentage percentage_sku = percentage_weight into_interval = 0 prices_sum = 0 count = 0 trax_average = None for price in mpisc_df_price: if price and pd.notna(price): prices_sum += price count += 1 if prices_sum > 0: trax_average = prices_sum / count into_interval = 0 if not np.isnan(suggested_price): if min_price <= trax_average <= max_price: into_interval = 100 results_df.loc[len(results_df)] = [product_pk, trax_average, suggested_price, into_interval / 100, min_price, max_price, percentage_sku] self.common_v2.write_to_db_result(10, numerator_id=product_pk, numerator_result=suggested_price, denominator_id=product_pk, denominator_result=trax_average, result=row.percentage_weight, score=into_interval) if trax_average: mark_up = (np.divide(np.divide(float(trax_average), float(1.13)), float(suggested_price)) - 1) * 100 self.common_v2.write_to_db_result(11, numerator_id=product_pk, numerator_result=suggested_price, denominator_id=product_pk, denominator_result=trax_average, score=mark_up, result=mark_up) return results_df def calculate_perfect_store_extra_spaces(self): extra_spaces_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type( Const.PERFECT_STORE_EXTRA_SPACES_SUB_CATEGORY) sub_cats_for_store = self.relevant_sub_categories if self.extra_spaces_results.empty: pass try: relevant_sub_categories = [x.strip() for x in self.extra_spaces_template[ self.extra_spaces_template['country'].str.encode('utf-8') == self.country.encode('utf-8')][ 'sub_category'].iloc[0].split(',')] except IndexError: Log.warning( 'No relevant sub_categories for the Extra Spaces KPI found for the following country: {}'.format( self.country)) self.extra_spaces_results = pd.merge(self.extra_spaces_results, self.all_products.loc[:, [ 'sub_category_fk', 'sub_category']].dropna().drop_duplicates(), how='left', on='sub_category_fk') relevant_extra_spaces = \ self.extra_spaces_results[self.extra_spaces_results['sub_category'].isin( relevant_sub_categories)] kpi_weight = self.get_kpi_weight('EXTRA') for row in relevant_extra_spaces.itertuples(): self.powersku_empty[row.sub_category_fk] = 1 * kpi_weight score = result = 1 if row.sub_category_fk in sub_cats_for_store: sub_cats_for_store.remove(row.sub_category_fk) self.common_v2.write_to_db_result(extra_spaces_kpi_fk, numerator_id=row.sub_category_fk, denominator_id=row.template_fk, result=result, score=score, identifier_parent=row.sub_category_fk, target=1, should_enter=True) for sub_cat_fk in sub_cats_for_store: result = score = 0 self.powersku_empty[sub_cat_fk] = 0 self.common_v2.write_to_db_result(extra_spaces_kpi_fk, numerator_id=sub_cat_fk, denominator_id=0, result=result, score=score, identifier_parent=sub_cat_fk, target=1, should_enter=True) def heinz_global_extra_spaces(self): try: supervisor = self.store_info['additional_attribute_3'][0] store_target = -1 # for row in self.store_sos_policies.itertuples(): # policies = json.loads(row.store_policy) # for key, value in policies.items(): # try: # if key == 'additional_attribute_3' and value[0] == supervisor: # store_target = row.target # break # except KeyError: # continue for row in self.supervisor_target.itertuples(): try: if row.supervisor == supervisor: store_target = row.target break except: continue except Exception as e: Log.error("Supervisor target is not configured for the extra spaces report ") raise e results_df = self.extra_spaces_results # limit to only secondary scenes relevant_scif = self.scif[(self.scif['location_type_fk'] == float(2)) & (self.scif['facings'] > 0)] if relevant_scif.empty: return results_df # aggregate facings for every scene/sub_category combination in the visit relevant_scif = \ relevant_scif.groupby(['scene_fk', 'template_fk', 'sub_category_fk'], as_index=False)['facings'].sum() # sort sub_categories by number of facings, largest first relevant_scif = relevant_scif.sort_values(['facings'], ascending=False) # drop all but the sub_category with the largest number of facings for each scene relevant_scif = relevant_scif.drop_duplicates(subset=['scene_fk'], keep='first') for row in relevant_scif.itertuples(): results_df.loc[len(results_df)] = [row.sub_category_fk, row.template_fk, row.facings] self.common_v2.write_to_db_result(13, numerator_id=row.template_fk, numerator_result=row.facings, denominator_id=row.sub_category_fk, denominator_result=row.facings, context_id=row.scene_fk, result=store_target) return results_df def check_bonus_question(self): bonus_kpi_fk = self.common_v2.get_kpi_fk_by_kpi_type(Const.BONUS_QUESTION_SUB_CATEGORY) bonus_weight = self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS['Bonus']].iloc[0] sub_category_fks = self.sub_category_weight.sub_category_fk.unique().tolist() sub_category_fks = [x for x in sub_category_fks if str(x) != 'nan'] if self.survey.check_survey_answer(('question_fk', Const.BONUS_QUESTION_FK), 'Yes,yes,si,Si'): result = 1 else: result = 0 for sub_cat_fk in sub_category_fks: sub_cat_weight = self.get_weight(sub_cat_fk) score = result * sub_cat_weight target_weight = bonus_weight * sub_cat_weight self.powersku_bonus[sub_cat_fk] = score self.common_v2.write_to_db_result(bonus_kpi_fk, numerator_id=sub_cat_fk, denominator_id=self.store_id, result=result, score=score, identifier_parent=sub_cat_fk, weight=target_weight, target=target_weight, should_enter=True) def commit_results_data(self): self.common_v2.commit_results_data() def update_score_sub_category_weights(self): all_sub_category_fks = self.all_products[['sub_category', 'sub_category_fk']].drop_duplicates() self.sub_category_weight = pd.merge(self.sub_category_weight, all_sub_category_fks, left_on='Category', right_on='sub_category', how='left') def get_weight(self, sub_category_fk): weight_value = 0 if self.country in self.sub_category_weight.columns.to_list(): weight_df = self.sub_category_weight[self.country][ (self.sub_category_weight.sub_category_fk == sub_category_fk)] if weight_df.empty: return 0 weight_value = weight_df.iloc[0] if pd.isna(weight_value): weight_value = 0 weight = weight_value * 0.01 return weight def get_kpi_weight(self, kpi_name): weight = self.kpi_weights['Score'][self.kpi_weights['KPIs'] == Const.KPI_WEIGHTS[kpi_name]].iloc[0] return weight def get_supervisor_target(self): supervisor_target = self.targets[self.targets['kpi_type'] == 'Extra Spaces'] return supervisor_target def calculate_sub_category_sum(self, dict_list, sub_cat_fk): total_score = 0 for item in dict_list: try: total_score += item[sub_cat_fk] except: pass return total_score def set_relevant_sub_categories(self): if self.country in self.sub_category_weight.columns.to_list(): df = self.sub_category_weight[['Category', 'sub_category_fk', self.country]].dropna() self.relevant_sub_categories = df.sub_category_fk.to_list() else: self.relevant_sub_categories = []
class LIBERTYToolBox: def __init__(self, data_provider, output, common_db): 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.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.store_info = self.ps_data_provider.get_ps_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.result_values = self.ps_data_provider.get_result_values() self.templates = self.read_templates() self.common_db = common_db self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider, common=self.common_db) self.manufacturer_fk = Const.MANUFACTURER_FK self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.retailer = self.store_info['retailer_name'].iloc[0] self.branch = self.store_info['branch_name'].iloc[0] self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0] self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0] self.body_armor_delivered = self.get_body_armor_delivery_status() self.convert_base_size_and_multi_pack() def read_templates(self): templates = {} for sheet in Const.SHEETS: converters = None if sheet == Const.MINIMUM_FACINGS: converters = {Const.BASE_SIZE_MIN: self.convert_base_size_values, Const.BASE_SIZE_MAX: self.convert_base_size_values} templates[sheet] = \ pd.read_excel(Const.TEMPLATE_PATH, sheet_name=sheet, converters=converters).fillna('') return templates # 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. """ if self.region != 'Liberty': return red_score = 0 main_template = self.templates[Const.KPIS] for i, main_line in main_template.iterrows(): relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7) if relevant_store_types and self.additional_attribute_7 not in relevant_store_types: continue result = self.calculate_main_kpi(main_line) if result: red_score += main_line[Const.WEIGHT] * result if len(self.common_db.kpi_results) > 0: kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT) self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score, identifier_result=Const.RED_SCORE_PARENT, should_enter=True) return 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. """ relevant_scif = self.scif scene_types = self.does_exist(main_line, Const.SCENE_TYPE) if scene_types: relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)] excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE) if excluded_scene_types: relevant_scif = relevant_scif[~relevant_scif['template_name'].isin( excluded_scene_types)] template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP) if template_groups: relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)] result = self.calculate_kpi_by_type(main_line, relevant_scif) return result def calculate_kpi_by_type(self, main_line, relevant_scif): """ the function calculates all the kpis :param main_line: one kpi line from the main template :param relevant_scif: :return: boolean, but it can be None if we want not to write it in DB """ kpi_type = main_line[Const.KPI_TYPE] relevant_template = self.templates[kpi_type] kpi_line = relevant_template[relevant_template[Const.KPI_NAME] == main_line[Const.KPI_NAME]].iloc[0] kpi_function = self.get_kpi_function(kpi_type) weight = main_line[Const.WEIGHT] if relevant_scif.empty: result = 0 else: result = kpi_function(kpi_line, relevant_scif, weight) result_type_fk = self.ps_data_provider.get_pks_of_result( Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL) if self.does_exist(main_line, Const.PARENT_KPI_NAME): # if this is a child KPI, we do not need to return a value to the Total Score KPI return 0 else: # normal behavior for when this isn't a child KPI kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT, identifier_result=kpi_name, should_enter=True) return result # SOS functions def calculate_sos(self, kpi_line, relevant_scif, weight): market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET) if market_share_required: market_share_target = self.get_market_share_target() else: market_share_target = 0 if not market_share_target: market_share_target = 0 denominator_facings = relevant_scif['facings'].sum() filtered_scif = relevant_scif.copy() manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER) if liberty_truck: liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin( liberty_truck)] filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates() if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered: body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK] filtered_scif = filtered_scif.append(body_armor_scif, sort=False) numerator_facings = filtered_scif['facings'].sum() sos_value = numerator_facings / float(denominator_facings) result = 1 if sos_value > market_share_target else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_facings, denominator_id=self.store_id, denominator_result=denominator_facings, weight=weight, score=result * weight, result=sos_value * 100, target=market_share_target * 100, identifier_parent=parent_kpi_name, should_enter=True) return result # Availability functions def calculate_availability(self, kpi_line, relevant_scif, weight): survey_question_skus_required = self.does_exist( kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED) if survey_question_skus_required: survey_question_skus, secondary_survey_question_skus = \ self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME]) unique_skus = \ relevant_scif[relevant_scif['product_fk'].isin( survey_question_skus)]['product_fk'].unique().tolist() if secondary_survey_question_skus: secondary_unique_skus = \ relevant_scif[relevant_scif['product_fk'].isin(secondary_survey_question_skus)][ 'product_fk'].unique().tolist() else: secondary_unique_skus = None else: secondary_unique_skus = None manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] brand = self.does_exist(kpi_line, Const.BRAND) if brand: relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)] category = self.does_exist(kpi_line, Const.CATEGORY) if category: relevant_scif = relevant_scif[relevant_scif['category'].isin(category)] excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND) if excluded_brand: relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)] excluded_sku = self.does_exist(kpi_line, Const.EXCLUDED_SKU) if excluded_sku: relevant_scif = relevant_scif[~relevant_scif['product_name'].isin(excluded_sku)] unique_skus = relevant_scif['product_fk'].unique().tolist() length_of_unique_skus = len(unique_skus) minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS] if length_of_unique_skus >= minimum_number_of_skus: if secondary_unique_skus: length_of_unique_skus = len(secondary_unique_skus) minimum_number_of_skus = kpi_line[Const.SECONDARY_MINIMUM_NUMBER_OF_SKUS] result = 1 if length_of_unique_skus > minimum_number_of_skus else 0 else: result = 1 else: result = 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=length_of_unique_skus, target=minimum_number_of_skus, score=result * weight, identifier_parent=parent_kpi_name, should_enter=True) return result def get_relevant_product_assortment_by_kpi_name(self, kpi_name): template = self.templates[Const.SURVEY_QUESTION_SKUS] relevant_template = template[template[Const.KPI_NAME] == kpi_name] # we need this to fix dumb template relevant_template[Const.EAN_CODE] = \ relevant_template[Const.EAN_CODE].apply(lambda x: str(int(x)) if x != '' else None) primary_ean_codes = \ relevant_template[relevant_template[Const.SECONDARY_GROUP] != 'Y'][Const.EAN_CODE].unique().tolist() primary_ean_codes = [code for code in primary_ean_codes if code is not None] primary_products = self.all_products[self.all_products['product_ean_code'].isin( primary_ean_codes)] primary_product_pks = primary_products['product_fk'].unique().tolist() secondary_ean_codes = \ relevant_template[relevant_template[Const.SECONDARY_GROUP] == 'Y'][Const.EAN_CODE].unique().tolist() if secondary_ean_codes: secondary_products = self.all_products[self.all_products['product_ean_code'].isin( secondary_ean_codes)] secondary_product_pks = secondary_products['product_fk'].unique().tolist() else: secondary_product_pks = None return primary_product_pks, secondary_product_pks # Count of Display functions def calculate_count_of_display(self, kpi_line, relevant_scif, weight): filtered_scif = relevant_scif.copy() manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER) if liberty_truck: liberty_truck_scif = relevant_scif[relevant_scif[Const.LIBERTY_KEY_MANUFACTURER].isin( liberty_truck)] filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates() brand = self.does_exist(kpi_line, Const.BRAND) if brand: filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)] category = self.does_exist(kpi_line, Const.CATEGORY) if category: filtered_scif = filtered_scif[filtered_scif['category'].isin(category)] excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND) if excluded_brand: filtered_scif = filtered_scif[~filtered_scif['brand_name'].isin(excluded_brand)] excluded_category = self.does_exist(kpi_line, Const.EXCLUDED_CATEGORY) if excluded_category: filtered_scif = filtered_scif[~filtered_scif['category'].isin(excluded_category)] ssd_still = self.does_exist(kpi_line, Const.ATT4) if ssd_still: filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)] if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered: body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK] filtered_scif = filtered_scif.append(body_armor_scif, sort=False) size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM) if size_subpackages: # convert all pairings of size and number of subpackages to tuples # size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages] size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in size_subpackages] filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['Base Size'], filtered_scif['Multi-Pack Size'])), index=filtered_scif.index).isin(size_subpackages_tuples)] excluded_size_subpackages = self.does_exist(kpi_line, Const.EXCLUDED_SIZE_SUBPACKAGES_NUM) if excluded_size_subpackages: # convert all pairings of size and number of subpackages to tuples # size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages] size_subpackages_tuples = [tuple([self.convert_base_size_values(i) for i in x.split(';')]) for x in excluded_size_subpackages] filtered_scif = filtered_scif[~pd.Series(list(zip(filtered_scif['Base Size'], filtered_scif['Multi-Pack Size'])), index=filtered_scif.index).isin(size_subpackages_tuples)] sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM) if sub_packages: if sub_packages == [Const.NOT_NULL]: filtered_scif = filtered_scif[~filtered_scif['Multi-Pack Size'].isnull()] elif sub_packages == [Const.GREATER_THAN_ONE]: filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'] > 1] else: filtered_scif = filtered_scif[filtered_scif['Multi-Pack Size'].isin( [int(i) for i in sub_packages])] if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED): number_of_passing_displays, _ = self.get_number_of_passing_displays(filtered_scif) if self.does_exist(kpi_line, Const.PARENT_KPI_NAME): parent_kpi_name = kpi_line[Const.PARENT_KPI_NAME] + Const.LIBERTY + Const.DRILLDOWN kpi_fk = self.common_db.get_kpi_fk_by_kpi_type( kpi_line[Const.KPI_NAME] + Const.LIBERTY) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=number_of_passing_displays, score=number_of_passing_displays, identifier_parent=parent_kpi_name, should_enter=True) return 0 else: parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY identifier_result = parent_kpi_name + Const.DRILLDOWN kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=number_of_passing_displays, score=number_of_passing_displays * weight, identifier_parent=parent_kpi_name, identifier_result=identifier_result, should_enter=True) return number_of_passing_displays else: return 0 # Share of Display functions def calculate_share_of_display(self, kpi_line, relevant_scif, weight): base_scif = relevant_scif.copy() ssd_still = self.does_exist(kpi_line, Const.ATT4) if ssd_still: ssd_still_scif = base_scif[base_scif['att4'].isin(ssd_still)] else: ssd_still_scif = base_scif denominator_passing_displays, _ = \ self.get_number_of_passing_displays(ssd_still_scif) manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = ssd_still_scif[ssd_still_scif['manufacturer_name'].isin(manufacturer)] else: filtered_scif = ssd_still_scif liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER) if liberty_truck: liberty_truck_scif = ssd_still_scif[ssd_still_scif[Const.LIBERTY_KEY_MANUFACTURER].isin( liberty_truck)] filtered_scif = filtered_scif.append(liberty_truck_scif, sort=False).drop_duplicates() if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET): market_share_target = self.get_market_share_target(ssd_still=ssd_still) else: market_share_target = 0 if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered: body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK] filtered_scif = filtered_scif.append(body_armor_scif, sort=False) if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED): numerator_passing_displays, _ = \ self.get_number_of_passing_displays(filtered_scif) if denominator_passing_displays != 0: share_of_displays = \ numerator_passing_displays / float(denominator_passing_displays) else: share_of_displays = 0 result = 1 if share_of_displays > market_share_target else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=numerator_passing_displays, denominator_id=self.store_id, denominator_result=denominator_passing_displays, weight=weight, result=share_of_displays * 100, target=market_share_target * 100, score=result * weight, identifier_parent=parent_kpi_name, should_enter=True) return result else: return 0 def get_number_of_passing_displays(self, filtered_scif): if filtered_scif.empty: return 0, 0 filtered_scif = \ filtered_scif.groupby(['Base Size', 'Multi-Pack Size', 'scene_id'], as_index=False)['facings'].sum() filtered_scif['passed_displays'] = \ filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1) number_of_displays = filtered_scif['passed_displays'].sum() facings_of_displays = filtered_scif[filtered_scif['passed_displays'] == 1]['facings'].sum() return number_of_displays, facings_of_displays def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets template = self.templates[Const.MINIMUM_FACINGS] relevant_template = template[(template[Const.BASE_SIZE_MIN] <= row['Base Size']) & (template[Const.BASE_SIZE_MAX] >= row['Base Size']) & (template[Const.MULTI_PACK_SIZE] == row['Multi-Pack Size'])] if relevant_template.empty: return 0 minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min() return 1 if row['facings'] >= minimum_facings else 0 # Share of Cooler functions def calculate_share_of_coolers(self, kpi_line, relevant_scif, weight): scene_ids = relevant_scif['scene_id'].unique().tolist() total_coolers = len(scene_ids) if total_coolers == 0: return 0 passing_coolers = 0 if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET): market_share_target = self.get_market_share_target() else: market_share_target = 0 for scene_id in scene_ids: cooler_scif = relevant_scif[relevant_scif['scene_id'] == scene_id] filtered_scif = cooler_scif.copy() manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = cooler_scif[cooler_scif['manufacturer_name'].isin(manufacturer)] liberty_truck = self.does_exist(kpi_line, Const.LIBERTY_KEY_MANUFACTURER) if liberty_truck: liberty_truck_scif = cooler_scif[cooler_scif[Const.LIBERTY_KEY_MANUFACTURER].isin( liberty_truck)] filtered_scif = filtered_scif.append( liberty_truck_scif, sort=False).drop_duplicates() if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered: body_armor_scif = cooler_scif[cooler_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK] filtered_scif = filtered_scif.append(body_armor_scif, sort=False).drop_duplicates() coke_facings_threshold = self.does_exist(kpi_line, Const.COKE_FACINGS_THRESHOLD) cooler_sos = filtered_scif['facings'].sum() / cooler_scif['facings'].sum() cooler_result = 1 if cooler_sos >= coke_facings_threshold else 0 passing_coolers += cooler_result coke_market_share = passing_coolers / float(total_coolers) result = 1 if coke_market_share > market_share_target else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=passing_coolers, denominator_id=self.store_id, denominator_result=total_coolers, weight=weight, result=coke_market_share * 100, target=market_share_target * 100, score=result * weight, identifier_parent=parent_kpi_name, should_enter=True) return result # Survey functions def calculate_survey(self, kpi_line, relevant_scif, weight): return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0 # helper functions def convert_base_size_and_multi_pack(self): self.scif.loc[:, 'Base Size'] = self.scif['Base Size'].apply(self.convert_base_size_values) self.scif.loc[:, 'Multi-Pack Size'] = \ self.scif['Multi-Pack Size'].apply(lambda x: int(x) if x is not None else None) @staticmethod def convert_base_size_values(value): try: new_value = float(value.split()[0]) if value not in [None, ''] else None except IndexError: Log.error('Could not convert base size value for {}'.format(value)) new_value = None return new_value def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets template = self.templates[Const.MARKET_SHARE] relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) & (template[Const.RETAILER] == self.retailer) & (template[Const.BRANCH] == self.branch)] if relevant_template.empty: if ssd_still: if ssd_still[0].lower() == Const.SSD.lower(): return 49 elif ssd_still[0].lower() == Const.STILL.lower(): return 16 else: return 0 else: return 26 if ssd_still: if ssd_still[0].lower() == Const.SSD.lower(): return relevant_template[Const.SSD].iloc[0] elif ssd_still[0].lower() == Const.STILL.lower(): return relevant_template[Const.STILL].iloc[0] # total 26, ssd only 49, still only 16 return relevant_template[Const.SSD_AND_STILL].iloc[0] def get_body_armor_delivery_status(self): if self.store_info['additional_attribute_8'].iloc[0] == 'Y': return True else: return False 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.SOS: return self.calculate_sos elif kpi_type == Const.AVAILABILITY: return self.calculate_availability elif kpi_type == Const.COUNT_OF_DISPLAY: return self.calculate_count_of_display elif kpi_type == Const.SHARE_OF_DISPLAY: return self.calculate_share_of_display elif kpi_type == Const.SHARE_OF_COOLERS: return self.calculate_share_of_coolers elif kpi_type == Const.SURVEY: return self.calculate_survey else: Log.warning( "The value '{}' in column sheet in the template is not recognized".format(kpi_type)) return None @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, np.float64]: return [cell] elif type(cell) in [unicode, str]: return [x.strip() for x in cell.split(",")] return None
class JNJToolBox: NUMERATOR = 'numerator' DENOMINATOR = 'denominator' SP_LOCATION_KPI = 'secondary placement location quality' SP_LOCATION_QUALITY_KPI = 'secondary placement location visibility quality' LVL3_HEADERS = [ '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' ] LVL2_HEADERS = [ 'assortment_super_group_fk', 'assortment_group_fk', 'assortment_fk', 'target', 'passes', 'total', 'kpi_fk_lvl1', 'kpi_fk_lvl2', 'group_target_date', 'super_group_target' ] LVL1_HEADERS = [ 'assortment_super_group_fk', 'assortment_group_fk', 'super_group_target', 'passes', 'total', 'kpi_fk_lvl1' ] ASSORTMENT_FK = 'assortment_fk' ASSORTMENT_GROUP_FK = 'assortment_group_fk' ASSORTMENT_SUPER_GROUP_FK = 'assortment_super_group_fk' # local_msl availability # LOCAL_MSL_AVAILABILITY = 'local_msl' # LOCAL_MSL_AVAILABILITY_SKU = 'local_msl - SKU' # TODO: change this # local_msl availability LOCAL_MSL_AVAILABILITY = 'Distribution' LOCAL_MSL_AVAILABILITY_SKU = 'Distribution - SKU' # jnjanz local msl/oos KPIs OOS_BY_LOCAL_ASSORT_STORE_KPI = 'OOS_BY_LOCAL_ASSORT_STORE' OOS_BY_LOCAL_ASSORT_PRODUCT = 'OOS_BY_LOCAL_ASSORT_PRODUCT' OOS_BY_LOCAL_ASSORT_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY' OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY' OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT' MSL_BY_LOCAL_ASSORT = 'MSL_BY_LOCAL_ASSORT' MSL_BY_LOCAL_ASSORT_PRODUCT = 'MSL_BY_LOCAL_ASSORT_PRODUCT' MSL_BY_LOCAL_ASSORT_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY' MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY' MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT = 'MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT' # msl availability MSL_AVAILABILITY = 'MSL' MSL_AVAILABILITY_SKU = 'MSL - SKU' JNJ = 'JOHNSON & JOHNSON' TYPE_SKU = 'SKU' TYPE_OTHER = 'Other' SUCCESSFUL = [1, 4] OTHER = 'Other' YES = 'Yes' NO = 'No' OOS = 'OOS' DISTRIBUTED = 'DISTRIBUTED' EXTRA = 'EXTRA' def __init__(self, data_provider, output, common, exclusive_template): 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.session_id = self.data_provider.session_id self.products = self.data_provider[Data.PRODUCTS] self.all_products_i_d = self.data_provider[ Data.ALL_PRODUCTS_INCLUDING_DELETED] 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.templates = self.data_provider[Data.ALL_TEMPLATES] self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.tools = JNJGENERALToolBox(self.data_provider, self.output, rds_conn=self.rds_conn) self.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.common = common self.New_kpi_static_data = common.get_new_kpi_static_data() self.kpi_results_new_tables_queries = [] self.all_products = self.ps_data_provider.get_sub_category( self.all_products, 'sub_category_local_name') self.store_info = self.data_provider[Data.STORE_INFO] self.store_info = self.ps_data_provider.get_ps_store_info( self.store_info) self.current_date = datetime.now() self.labels = self.ps_data_provider.get_labels() self.products_in_ass = [] self.products_to_ass = pd.DataFrame( columns=assTemplate.COLUMNS_ASSORTMENT_DEFINITION_SHEET) self.assortment_policy = pd.DataFrame( columns=assTemplate.COLUMNS_STORE_ATTRIBUTES_TO_ASSORT) self.ass_deleted_prod = pd.DataFrame(columns=[ assTemplate.COLUMN_GRANULAR_GROUP, assTemplate.COLUMN_EAN_CODE ]) self.session_category_info = pd.DataFrame() self.session_products = pd.DataFrame() self.assortment = Assortment(self.data_provider, self.output, self.ps_data_provider) self.products_to_remove = [] self.ignore_from_top = 1 self.start_shelf = 3 self.products_for_ass_new = pd.DataFrame( columns=['session_id', 'product_fk']) self.prev_session_products_new_ass = pd.DataFrame() self.session_category_new_ass = pd.DataFrame() self.own_manuf_fk = int( self.data_provider.own_manufacturer.param_value.values[0]) self.kpi_result_values = self.get_kpi_result_values_df() self.parser = Parser self.exclusive_template = exclusive_template self.result_values = self.ps_data_provider.get_result_values() def get_kpi_result_values_df(self): query = JNJQueries.get_kpi_result_values() query_result = pd.read_sql_query(query, self.rds_conn.db) return query_result def get_session_products(self, session): return self.session_products[self.session_products['session_id'] == session] def result_value_pk(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 split_and_strip(value): return map(lambda x: x.strip(), value.split(';')) def reset_scif_and_matches(self): self.scif = self.data_provider[Data.SCENE_ITEM_FACTS].copy() self.match_product_in_scene = self.data_provider[Data.MATCHES].copy() def filter_scif_matches_for_kpi(self, kpi_name): if not self.exclusive_template.empty: template_filters = {} kpi_filters_df = self.exclusive_template[ self.exclusive_template['KPI'] == kpi_name] if kpi_filters_df.empty: return if not kpi_filters_df.empty: if kpi_filters_df['Exclude1'].values[0]: template_filters.update({ kpi_filters_df['Exclude1'].values[0]: (self.split_and_strip( kpi_filters_df['Value1'].values[0]), 0) }) if kpi_filters_df['Exclude2'].values[0]: template_filters.update({ kpi_filters_df['Exclude2'].values[0]: (self.split_and_strip( kpi_filters_df['Value2'].values[0]), 0) }) if 'Exclude3' in kpi_filters_df.columns.values: if kpi_filters_df['Exclude3'].values[0]: template_filters.update({ kpi_filters_df['Exclude3'].values[0]: (self.split_and_strip( kpi_filters_df['Value3'].values[0]), 0) }) if 'Exclude4' in kpi_filters_df.columns.values: if kpi_filters_df['Exclude4'].values[0]: template_filters.update({ template_filters['Exclude4'].values[0]: (self.split_and_strip( template_filters['Value4'].values[0]), 0) }) filters = self.get_filters_for_scif_and_matches( template_filters) self.scif = self.scif[self.tools.get_filter_condition( self.scif, **filters)] self.match_product_in_scene = self.match_product_in_scene[ self.tools.get_filter_condition( self.match_product_in_scene, **filters)] def get_filters_for_scif_and_matches(self, template_filters): product_keys = filter( lambda x: x in self.data_provider[Data.ALL_PRODUCTS].columns.values .tolist(), template_filters.keys()) scene_keys = filter( lambda x: x in self.data_provider[Data.ALL_TEMPLATES].columns. values.tolist(), template_filters.keys()) product_filters = {} scene_filters = {} filters_all = {} for key in product_keys: product_filters.update({key: template_filters[key]}) for key in scene_keys: scene_filters.update({key: template_filters[key]}) if product_filters: product_fks = self.get_fk_from_filters(product_filters) filters_all.update({'product_fk': product_fks}) if scene_filters: scene_fks = self.get_scene_fk_from_filters(scene_filters) filters_all.update({'scene_fk': scene_fks}) return filters_all def get_fk_from_filters(self, filters): all_products = self.data_provider.all_products product_fk_list = all_products[self.tools.get_filter_condition( all_products, **filters)] product_fk_list = product_fk_list['product_fk'].unique().tolist() return product_fk_list def get_scene_fk_from_filters(self, filters): scif_data = self.data_provider[Data.SCENE_ITEM_FACTS] scene_fk_list = scif_data[self.tools.get_filter_condition( scif_data, **filters)] scene_fk_list = scene_fk_list['scene_fk'].unique().tolist() return scene_fk_list def get_own_manufacturer_skus_in_scif(self): # Filter scif by own_manufacturer & product_type = 'SKU' return self.scif[(self.scif.manufacturer_fk == self.own_manuf_fk) & (self.scif.product_type == "SKU") & (self.scif["facings"] > 0)]['item_id'].unique().tolist() def fetch_local_assortment_products(self): # TODO Fix with real assortment lvl3_assortment = self.assortment.get_lvl3_relevant_ass() local_msl_ass_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.LOCAL_MSL_AVAILABILITY]['pk'].drop_duplicates().values[0] local_msl_ass_sku_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.LOCAL_MSL_AVAILABILITY_SKU]['pk'].drop_duplicates().values[0] if lvl3_assortment.empty: return [], pd.DataFrame() lvl3_assortment = lvl3_assortment[lvl3_assortment['kpi_fk_lvl3'] == local_msl_ass_sku_fk] if lvl3_assortment.empty: return [], pd.DataFrame() assortments = lvl3_assortment['assortment_group_fk'].unique() products_in_ass = [] for assortment in assortments: current_assortment = lvl3_assortment[ lvl3_assortment['assortment_group_fk'] == assortment] current_assortment_product_fks = list( current_assortment[~current_assortment['product_fk'].isna()] ['product_fk'].unique()) products_in_ass.extend(current_assortment_product_fks) #ignore None if anty products_in_ass = [ p for p in products_in_ass if not ((p == None) or p == 'None') ] return products_in_ass, lvl3_assortment @kpi_runtime() def local_assortment_hierarchy_per_store_calc(self): Log.debug("starting local_assortment calc") self.products_in_ass, lvl3_assortment = self.fetch_local_assortment_products( ) self.products_in_ass = np.unique(self.products_in_ass) if lvl3_assortment.empty or len(self.products_in_ass) == 0: Log.warning( "Assortment list is empty for store_fk {} in the requested session : {} - visit_date: {}" .format(self.store_id, self.session_id, self.session_info.get('visit_date').iloc[0])) return self.local_assortment_hierarchy_per_category_and_subcategory() oos_per_product_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.OOS_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0] msl_per_product_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.MSL_BY_LOCAL_ASSORT_PRODUCT]['pk'].values[0] products_in_session = self.scif['item_id'].drop_duplicates().values for sku in self.products_in_ass: if sku in products_in_session: result = self.result_value_pk(self.DISTRIBUTED) result_num = 1 else: result = self.result_value_pk(self.OOS) result_num = 0 # Saving OOS self.common.write_to_db_result( fk=oos_per_product_kpi_fk, numerator_id=sku, numerator_result=result, result=result, denominator_id=self.own_manuf_fk, denominator_result=1, score=result, identifier_parent="OOS_Local_store", should_enter=True) # Saving MSL self.common.write_to_db_result(fk=msl_per_product_kpi_fk, numerator_id=sku, numerator_result=result_num, result=result, denominator_id=self.own_manuf_fk, denominator_result=1, score=result, identifier_parent="MSL_Local_store", should_enter=True) # Saving MSL - Extra # Add the Extra Products found in Session from same manufacturer into MSL own_manufacturer_skus = self.get_own_manufacturer_skus_in_scif() extra_products_in_scene = set(products_in_session) - set( self.products_in_ass) for sku in extra_products_in_scene: if sku in own_manufacturer_skus: result = self.result_value_pk(self.EXTRA) # Extra result_num = 1 self.common.write_to_db_result( fk=msl_per_product_kpi_fk, numerator_id=sku, numerator_result=result_num, result=result, denominator_id=self.own_manuf_fk, denominator_result=1, score=result, identifier_parent="MSL_Local_store", should_enter=True) oos_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.OOS_BY_LOCAL_ASSORT_STORE_KPI]['pk'].values[0] msl_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.MSL_BY_LOCAL_ASSORT]['pk'].values[0] denominator = len(self.products_in_ass) # Saving OOS oos_numerator = len( list(set(self.products_in_ass) - set(products_in_session))) oos_res = round( (oos_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result(fk=oos_kpi_fk, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, numerator_result=oos_numerator, result=oos_res, denominator_result=denominator, score=oos_res, identifier_result="OOS_Local_store") # Saving MSL msl_numerator = len( list(set(self.products_in_ass) & set(products_in_session))) msl_res = round( (msl_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result(fk=msl_kpi_fk, numerator_id=self.own_manuf_fk, denominator_id=self.store_id, numerator_result=msl_numerator, result=msl_res, denominator_result=denominator, score=msl_res, identifier_result="MSL_Local_store") Log.debug("finishing oos_per_store_calc") return def local_assortment_hierarchy_per_category_and_subcategory(self): Log.debug("starting oos_per_category_per_sub_category_per_product") products_in_session = self.scif['product_fk'].drop_duplicates().values # OOS KPIs oos_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self. OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0] oos_cat_subcat_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.OOS_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0] oos_cat_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.OOS_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0] # MSL KPIs msl_cat_subcat_sku_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self. MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY_PRODUCT]['pk'].values[0] msl_cat_subcat_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.MSL_BY_LOCAL_ASSORT_CATEGORY_SUB_CATEGORY]['pk'].values[0] msl_cat_kpi_fk = self.New_kpi_static_data[ self.New_kpi_static_data['client_name'] == self.MSL_BY_LOCAL_ASSORT_CATEGORY]['pk'].values[0] categories = self.all_products[self.all_products['product_fk'].isin(self.products_in_ass)] \ ['category_fk'].drop_duplicates().values for category in categories: products_in_cat = self.all_products[ self.all_products['category_fk'] == category]['product_fk'].drop_duplicates().values relevant_for_ass = list( set(self.products_in_ass) & set(products_in_cat)) denominator = len(relevant_for_ass) # Saving OOS oos_numerator = len( list(set(relevant_for_ass) - set(products_in_session))) oos_res = round((oos_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result(fk=oos_cat_kpi_fk, numerator_id=self.own_manuf_fk, numerator_result=oos_numerator, result=oos_res, denominator_id=category, denominator_result=denominator, score=oos_res, identifier_result="OOS_Local_cat_" + str(int(category))) # Saving MSL msl_numerator = len( list(set(relevant_for_ass) & set(products_in_session))) msl_res = round((msl_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result(fk=msl_cat_kpi_fk, numerator_id=self.own_manuf_fk, numerator_result=msl_numerator, result=msl_res, denominator_id=category, denominator_result=denominator, score=msl_res, identifier_result="MSL_Local_cat_" + str(int(category))) sub_categories = self.all_products[( self.all_products['product_fk'].isin(self.products_in_ass) & (self.all_products['category_fk'] == category))]['sub_category_fk'].drop_duplicates().values for sub_category in sub_categories: products_in_sub_cat = self.all_products[ (self.all_products['sub_category_fk'] == sub_category) & (self.all_products['category_fk'] == category )]['product_fk'].drop_duplicates().values relevant_for_ass = list( set(self.products_in_ass) & set(products_in_sub_cat)) denominator = len(relevant_for_ass) # Saving OOS oos_numerator = len( list(set(relevant_for_ass) - set(products_in_session))) oos_res = round( (oos_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result( fk=oos_cat_subcat_kpi_fk, numerator_id=self.own_manuf_fk, numerator_result=oos_numerator, result=oos_res, denominator_id=sub_category, denominator_result=denominator, score=oos_res, identifier_result="OOS_Local_subcat_" + str(int(sub_category)), identifier_parent="OOS_Local_cat_" + str(int(category)), should_enter=True) # Saving MSL msl_numerator = len( list(set(relevant_for_ass) & set(products_in_session))) msl_res = round( (msl_numerator / float(denominator)), 4) if denominator != 0 else 0 self.common.write_to_db_result( fk=msl_cat_subcat_kpi_fk, numerator_id=self.own_manuf_fk, numerator_result=msl_numerator, result=msl_res, denominator_id=sub_category, denominator_result=denominator, score=msl_res, identifier_result="MSL_Local_subcat_" + str(int(sub_category)), identifier_parent="MSL_Local_cat_" + str(int(category)), should_enter=True) for sku in relevant_for_ass: if sku in products_in_session: result = self.result_value_pk(self.DISTRIBUTED) result_num = 1 else: result = self.result_value_pk(self.OOS) result_num = 0 # Saving OOS self.common.write_to_db_result( fk=oos_cat_subcat_sku_kpi_fk, result=result, score=result, numerator_id=sku, numerator_result=result, denominator_id=sub_category, denominator_result=1, identifier_parent="OOS_Local_subcat_" + str(int(sub_category)), should_enter=True) # Saving MSL self.common.write_to_db_result( fk=msl_cat_subcat_sku_kpi_fk, result=result, score=result, numerator_id=sku, numerator_result=result_num, denominator_id=sub_category, denominator_result=1, identifier_parent="MSL_Local_subcat_" + str(int(sub_category)), should_enter=True) # Saving MSL # Add the New Products found in Session for the subcat,cat from same manufacturer into MSL # Filter products in session based on sub_cat and category # extra_products_in_scene = set(products_in_session) - set(self.products_in_ass) # for sku in extra_products_in_scene: relevant_products_in_session = list( set(products_in_session) & set(products_in_sub_cat)) extra_products_in_scene = set( relevant_products_in_session) - set(relevant_for_ass) for sku in extra_products_in_scene: # Filter scif by own_manufacturer & product_type = 'SKU' own_manufacturer_skus = self.scif[ (self.scif.manufacturer_fk == self.own_manuf_fk) & (self.scif.product_type == "SKU") & (self.scif["facings"] > 0)]['item_id'].tolist() if sku in own_manufacturer_skus: result = self.result_value_pk(self.EXTRA) # Extra result_num = 1 self.common.write_to_db_result( fk=msl_cat_subcat_sku_kpi_fk, result=result, score=result, numerator_id=sku, numerator_result=result_num, denominator_id=sub_category, denominator_result=1, identifier_parent="MSL_Local_subcat_" + str(int(sub_category)), should_enter=True) Log.debug("finishing assortment_per_category") return def main_calculation(self): try: if self.scif.empty: Log.warning('Scene item facts is empty for this session') Log.warning( 'Unable to calculate local_msl assortment KPIs: SCIF is empty' ) return 0 self.reset_scif_and_matches() self.filter_scif_matches_for_kpi( "Distribution") #changed from local_msl to Distribution self.local_assortment_hierarchy_per_store_calc() except Exception as e: Log.error("Error: {}".format(e)) return 0
class LIBERTYToolBox: def __init__(self, data_provider, output, common_db): 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.ps_data_provider = PsDataProvider(self.data_provider, self.output) self.store_info = self.ps_data_provider.get_ps_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.templates = {} self.result_values = self.ps_data_provider.get_result_values() for sheet in Const.SHEETS: self.templates[sheet] = pd.read_excel(Const.TEMPLATE_PATH, sheetname=sheet).fillna('') self.common_db = common_db self.survey = Survey(self.data_provider, output=self.output, ps_data_provider=self.ps_data_provider, common=self.common_db) self.manufacturer_fk = Const.MANUFACTURER_FK self.region = self.store_info['region_name'].iloc[0] self.store_type = self.store_info['store_type'].iloc[0] self.retailer = self.store_info['retailer_name'].iloc[0] self.branch = self.store_info['branch_name'].iloc[0] self.additional_attribute_4 = self.store_info['additional_attribute_4'].iloc[0] self.additional_attribute_7 = self.store_info['additional_attribute_7'].iloc[0] self.body_armor_delivered = self.get_body_armor_delivery_status() # 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. """ red_score = 0 main_template = self.templates[Const.KPIS] for i, main_line in main_template.iterrows(): relevant_store_types = self.does_exist(main_line, Const.ADDITIONAL_ATTRIBUTE_7) if relevant_store_types and self.additional_attribute_7 not in relevant_store_types: continue result = self.calculate_main_kpi(main_line) if result: red_score += main_line[Const.WEIGHT] if len(self.common_db.kpi_results) > 0: kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(Const.RED_SCORE_PARENT) self.common_db.write_to_db_result(kpi_fk, numerator_id=1, denominator_id=self.store_id, result=red_score, identifier_result=Const.RED_SCORE_PARENT, should_enter=True) return 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. """ relevant_scif = self.scif scene_types = self.does_exist(main_line, Const.SCENE_TYPE) if scene_types: relevant_scif = relevant_scif[relevant_scif['template_name'].isin(scene_types)] excluded_scene_types = self.does_exist(main_line, Const.EXCLUDED_SCENE_TYPE) if excluded_scene_types: relevant_scif = relevant_scif[~relevant_scif['template_name'].isin(excluded_scene_types)] template_groups = self.does_exist(main_line, Const.TEMPLATE_GROUP) if template_groups: relevant_scif = relevant_scif[relevant_scif['template_group'].isin(template_groups)] result = self.calculate_kpi_by_type(main_line, relevant_scif) return result def calculate_kpi_by_type(self, main_line, relevant_scif): """ the function calculates all the kpis :param main_line: one kpi line from the main template :param relevant_scif: :return: boolean, but it can be None if we want not to write it in DB """ kpi_type = main_line[Const.KPI_TYPE] relevant_template = self.templates[kpi_type] kpi_line = relevant_template[relevant_template[Const.KPI_NAME] == main_line[Const.KPI_NAME]].iloc[0] kpi_function = self.get_kpi_function(kpi_type) weight = main_line[Const.WEIGHT] if relevant_scif.empty: result = 0 else: result = kpi_function(kpi_line, relevant_scif, weight) result_type_fk = self.ps_data_provider.get_pks_of_result( Const.PASS) if result > 0 else self.ps_data_provider.get_pks_of_result(Const.FAIL) kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(kpi_name) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=result_type_fk, identifier_parent=Const.RED_SCORE_PARENT, identifier_result=kpi_name, should_enter=True) return result # SOS functions def calculate_sos(self, kpi_line, relevant_scif, weight): market_share_required = self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET) if market_share_required: market_share_target = self.get_market_share_target() else: market_share_target = 0 if not market_share_target: market_share_target = 0 manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] number_of_facings = relevant_scif['facings'].sum() result = 1 if number_of_facings > market_share_target else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=number_of_facings, target=market_share_target, identifier_parent=parent_kpi_name, should_enter=True) return result # Availability functions def calculate_availability(self, kpi_line, relevant_scif, weight): survey_question_skus_required = self.does_exist(kpi_line, Const.SURVEY_QUESTION_SKUS_REQUIRED) if survey_question_skus_required: survey_question_skus = self.get_relevant_product_assortment_by_kpi_name(kpi_line[Const.KPI_NAME]) unique_skus = \ relevant_scif[relevant_scif['product_fk'].isin(survey_question_skus)]['product_fk'].unique().tolist() else: manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: relevant_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] brand = self.does_exist(kpi_line, Const.BRAND) if brand: relevant_scif = relevant_scif[relevant_scif['brand_name'].isin(brand)] category = self.does_exist(kpi_line, Const.CATEGORY) if category: relevant_scif = relevant_scif[relevant_scif['category'].isin(category)] excluded_brand = self.does_exist(kpi_line, Const.EXCLUDED_BRAND) if excluded_brand: relevant_scif = relevant_scif[~relevant_scif['brand_name'].isin(excluded_brand)] unique_skus = relevant_scif['product_fk'].unique().tolist() length_of_unique_skus = len(unique_skus) minimum_number_of_skus = kpi_line[Const.MINIMUM_NUMBER_OF_SKUS] result = 1 if length_of_unique_skus >= minimum_number_of_skus else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=length_of_unique_skus, target=minimum_number_of_skus, identifier_parent=parent_kpi_name, should_enter=True) return result def get_relevant_product_assortment_by_kpi_name(self, kpi_name): template = self.templates[Const.SURVEY_QUESTION_SKUS] relevant_template = template[template[Const.KPI_NAME] == kpi_name] relevant_ean_codes = relevant_template[Const.EAN_CODE].unique().tolist() relevant_ean_codes = [str(int(x)) for x in relevant_ean_codes if x != ''] # we need this to fix dumb template relevant_products = self.all_products[self.all_products['product_ean_code'].isin(relevant_ean_codes)] return relevant_products['product_fk'].unique().tolist() # Count of Display functions def calculate_count_of_display(self, kpi_line, relevant_scif, weight): filtered_scif = relevant_scif manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] brand = self.does_exist(kpi_line, Const.BRAND) if brand: filtered_scif = filtered_scif[filtered_scif['brand_name'].isin(brand)] ssd_still = self.does_exist(kpi_line, Const.ATT4) if ssd_still: filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)] size_subpackages = self.does_exist(kpi_line, Const.SIZE_SUBPACKAGES_NUM) if size_subpackages: # convert all pairings of size and number of subpackages to tuples size_subpackages_tuples = [tuple([float(i) for i in x.split(';')]) for x in size_subpackages] filtered_scif = filtered_scif[pd.Series(list(zip(filtered_scif['size'], filtered_scif['number_of_sub_packages'])), index=filtered_scif.index).isin(size_subpackages_tuples)] sub_packages = self.does_exist(kpi_line, Const.SUBPACKAGES_NUM) if sub_packages: if sub_packages == [Const.NOT_NULL]: filtered_scif = filtered_scif[~filtered_scif['number_of_sub_packages'].isnull()] else: filtered_scif = filtered_scif[filtered_scif['number_of_sub_packages'].isin([int(i) for i in sub_packages])] if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED): number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif) parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=number_of_passing_displays, identifier_parent=parent_kpi_name, should_enter=True) return 1 if number_of_passing_displays > 0 else 0 else: return 0 # Share of Display functions def calculate_share_of_display(self, kpi_line, relevant_scif, weight): filtered_scif = relevant_scif manufacturer = self.does_exist(kpi_line, Const.MANUFACTURER) if manufacturer: filtered_scif = relevant_scif[relevant_scif['manufacturer_name'].isin(manufacturer)] ssd_still = self.does_exist(kpi_line, Const.ATT4) if ssd_still: filtered_scif = filtered_scif[filtered_scif['att4'].isin(ssd_still)] if self.does_exist(kpi_line, Const.MARKET_SHARE_TARGET): market_share_target = self.get_market_share_target(ssd_still=ssd_still) else: market_share_target = 0 if self.does_exist(kpi_line, Const.INCLUDE_BODY_ARMOR) and self.body_armor_delivered: body_armor_scif = relevant_scif[relevant_scif['brand_fk'] == Const.BODY_ARMOR_BRAND_FK] filtered_scif = filtered_scif.append(body_armor_scif, sort=False) if self.does_exist(kpi_line, Const.MINIMUM_FACINGS_REQUIRED): number_of_passing_displays = self.get_number_of_passing_displays(filtered_scif) result = 1 if number_of_passing_displays > market_share_target else 0 parent_kpi_name = kpi_line[Const.KPI_NAME] + Const.LIBERTY kpi_fk = self.common_db.get_kpi_fk_by_kpi_type(parent_kpi_name + Const.DRILLDOWN) self.common_db.write_to_db_result(kpi_fk, numerator_id=self.manufacturer_fk, numerator_result=0, denominator_id=self.store_id, denominator_result=0, weight=weight, result=number_of_passing_displays, target=market_share_target, identifier_parent=parent_kpi_name, should_enter=True) return result else: return 0 def get_number_of_passing_displays(self, filtered_scif): if filtered_scif.empty: return 0 filtered_scif['passed_displays'] = \ filtered_scif.apply(lambda row: self._calculate_pass_status_of_display(row), axis=1) return filtered_scif['passed_displays'].sum() def _calculate_pass_status_of_display(self, row): # need to move to external KPI targets template = self.templates[Const.MINIMUM_FACINGS] package_category = (row['size'], row['number_of_sub_packages'], row['size_unit']) relevant_template = template[pd.Series(zip(template['size'], template['subpackages_num'], template['unit_of_measure'])) == package_category] minimum_facings = relevant_template[Const.MINIMUM_FACINGS_REQUIRED_FOR_DISPLAY].min() return 1 if row['facings'] > minimum_facings else 0 # Survey functions def calculate_survey(self, kpi_line, relevant_scif, weight): return 1 if self.survey.check_survey_answer(kpi_line[Const.QUESTION_TEXT], 'Yes') else 0 # helper functions def get_market_share_target(self, ssd_still=None): # need to move to external KPI targets template = self.templates[Const.MARKET_SHARE] relevant_template = template[(template[Const.ADDITIONAL_ATTRIBUTE_4] == self.additional_attribute_4) & (template[Const.RETAILER] == self.retailer) & (template[Const.BRANCH] == self.branch)] if relevant_template.empty: if ssd_still: if ssd_still[0].lower() == Const.SSD.lower(): return 49 elif ssd_still[0].lower() == Const.STILL.lower(): return 16 else: return 0 else: return 26 if ssd_still: if ssd_still[0].lower() == Const.SSD.lower(): return relevant_template[Const.SSD].iloc[0] elif ssd_still[0].lower() == Const.STILL.lower(): return relevant_template[Const.STILL].iloc[0] # total 26, ssd only 49, still only 16 return relevant_template[Const.SSD_AND_STILL].iloc[0] def get_body_armor_delivery_status(self): if self.store_info['additional_attribute_8'].iloc[0] == 'Y': return True else: return False 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.SOS: return self.calculate_sos elif kpi_type == Const.AVAILABILITY: return self.calculate_availability elif kpi_type == Const.COUNT_OF_DISPLAY: return self.calculate_count_of_display elif kpi_type == Const.SHARE_OF_DISPLAY: return self.calculate_share_of_display elif kpi_type == Const.SURVEY: return self.calculate_survey else: Log.warning( "The value '{}' in column sheet in the template is not recognized".format(kpi_type)) return None @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 [x.strip() for x in cell.split(",")] return None