class Generator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.tool_box = HEINEKENTWToolBox(self.data_provider, self.output) self.common = Common(data_provider) @log_runtime('Total Calculations', log_start=True) def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for this session') self.tool_box.main_calculation() self.common.commit_results_data()
class Generator: def __init__(self, data_provider, output): self.data_provider = data_provider self.output = output self.project_name = data_provider.project_name self.session_uid = self.data_provider.session_uid self.tool_box = CCAAUToolBox(self.data_provider, self.output) self.common = Common(data_provider) @log_runtime('Total Calculations', log_start=True) def main_function(self): """ This is the main KPI calculation function. It calculates the score for every KPI set and saves it to the DB. """ if self.tool_box.scif.empty: Log.warning('Scene item facts is empty for this session') # for kpi_set_fk in self.tool_box.kpi_static_data['kpi_set_fk'].unique().tolist(): # score = self.tool_box.main_calculation(kpi_set_fk=kpi_set_fk) # self.common.write_to_db_result(kpi_set_fk, self.tool_box.LEVEL1, score) self.tool_box.main_calculation() self.common.commit_results_data()
class INBEVTRADMXToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.common2 = Common2(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.templates = self.data_provider.all_templates self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'inbevtradmx_template_11_v3.xlsx') self.availability = Availability(self.data_provider) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.geo = GeoLocation.INBEVTRADMXGeo(self.rds_conn, self.session_uid, self.data_provider, self.kpi_static_data, self.common, self.common2) self.new_static_data = self.common2.kpi_static_data self.manufacturer_fk = 1 self.match_displays_in_scene = self.data_provider.match_display_in_scene self.mpis = self.mpis_merger() self.all_data = pd.merge(self.scif, self.match_product_in_scene[[ 'product_fk', 'shelf_number', 'scene_fk', 'facing_sequence_number' ]], how="inner", left_on=['item_id', 'scene_id'], right_on=['product_fk', 'scene_fk']).drop_duplicates() self.ignore_stacking = False self.facings_field = 'facings' if not self.ignore_stacking else 'facings_ign_stack' self.INCLUDE_FILTER = 1 self.EXCLUDE_FILTER = 0 self.CONTAIN_FILTER = 2 self.EXCLUDE_EMPTY = False self.INCLUDE_EMPTY = True # init functions: def parse_template(self): """ convert excel file to data frame :return: data frame """ template_df = pd.read_excel(self.excel_file_path, sheetname='template', encoding='utf-8') template_df['Store Additional Attribute 4'] = template_df[ 'Store Additional Attribute 4'].fillna('') template_df['Store Additional Attribute 13'] = template_df[ 'Store Additional Attribute 13'].fillna('') return template_df def check_store_type(self, row, relevant_columns): """ this method checks if the session store type is valid :type relevant_columns: list :param row: current KPI row :return: boolean """ # make sure that we need to check this field if relevant_columns.__contains__('store_type'): # get the valid stores from the template stores_df = pd.read_excel(self.excel_file_path, sheetname='store types') # create a list of the valid stores stores = stores_df.values return row['store_type'] in stores else: return True # calculation: def main_calculation(self): # calculate geo geo_result = self.geo.calculate_geo_location() self.geo.write_geo_to_db(float(geo_result)) # calculate from template self.calculate_kpi_set_from_template() self.calculate_posm_displays() self.common.commit_results_data() self.common2.commit_results_data() def calculate_kpi_set_from_template(self): """ this method chooses the correct set to calculate there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from template :return: None """ # get the template parsed_template = self.parse_template() # get all the unique sets # sets = parsed_template['KPI Level 1 Name'].unique() # get the session additional_attribute_4 & 13 additional_attribute_4 = self.store_info.additional_attribute_4.values[ 0] additional_attribute_13 = self.store_info.additional_attribute_13.values[ 0] set_name = self.choose_correct_sets_to_calculate( additional_attribute_4, additional_attribute_13, parsed_template) # for set_name in set_names: # wrong value in additional attribute 4 - shouldn't calculate if set_name == '': Log.warning( 'Wrong value in additional attribute 4 - shouldnt calculate') return -1 # get only the part of the template that is related to this set set_template_df = parsed_template[parsed_template['KPI Level 1 Name'] == set_name] # start calculating ! self.calculate_set_score(set_template_df, set_name) @staticmethod def choose_correct_sets_to_calculate(additional_attribute_4, additional_attribute_13, template): """ choose what is the appropriate set to calculate :param additional_attribute_4: session additional_attribute_4. if None, will ignore the kpi. :param additional_attribute_13: session additional_attribute_13. if None, will ignore this attribute :param template: parsed template :return: set name to calculate - assuming each additional attribute 4 matches only 1 set name. """ template = template.dropna(subset=['Store Additional Attribute 4'], axis=0) # Makes sure attribute 4 exist if not additional_attribute_4: return '' if additional_attribute_13: sets = template[ (template['Store Additional Attribute 4'].str.contains( additional_attribute_4)) & ((template['Store Additional Attribute 13'].str.contains( additional_attribute_13)) | (template['Store Additional Attribute 13'] == ''))] else: sets = template[ (template['Store Additional Attribute 4'].str.contains( additional_attribute_4)) & (template['Store Additional Attribute 13'] == '')] if sets.empty: return '' else: sets = sets['KPI Level 1 Name'].unique().tolist() if len(sets) == 1: set_name = sets[0] elif additional_attribute_4 == 'BC': set_name = sets[0] elif additional_attribute_4 == 'BA': set_name = sets[1] elif additional_attribute_4 == 'MODELORAMA': set_name = sets[2] else: Log.error('KPI should only run on one KPI set.') return set_name def calculate_set_score(self, set_df, set_name): """ this method iterates kpi set and calculates it's score :param set_df: the set df to calculate score for :param set_name: the kpi set name :return: None """ set_score = 0 # get array of all kpi level 2 names kpi_names = set_df['KPI Level 2 Name'].unique() # iterate all kpi level 2 for kpi_name in kpi_names: # calculate kpi level 2 score kpi_score = self.calculate_kpi_level_2_score( kpi_name, set_df, set_name) # accumulate set score set_score += kpi_score # round set_score if the set is 'Set Urban' if set_name == 'Set Urban': set_score = round(set_score) # finally, write level 1 kpi set score to DB self.write_kpi_set_score_to_db(set_name, set_score) def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name): """ this method gets kpi level 2 name, and iterates it's related atomic kpis :param set_name: kpi set name :param kpi_name: kpi level 2 name :param set_df: kpi set df :return: kpi level 2 score """ kpi_df = set_df[set_df['KPI Level 2 Name'].str.encode('utf8') == kpi_name.encode('utf-8')] kpi_score = 0 # iterate the all related atomic kpis for i, row in kpi_df.iterrows(): # get atomic kpi name kpi_level_3_name = row['KPI Level 3 Name'] # calculate atomic kpi score atomic_kpi_score = self.calculate_atomic_kpi_score( row, kpi_level_3_name, kpi_name, set_name) # accumulate kpi level 2 score kpi_score += atomic_kpi_score write_to_all_levels = False if len(kpi_df ) > 1: # if there is just one atomic we don't need two levels write_to_all_levels = True elif kpi_df['KPI Level 3 Name'].iloc[0] != kpi_df[ 'KPI Level 2 Name'].iloc[0]: write_to_all_levels = True self.write_kpi_score_to_db(kpi_name, set_name, kpi_score, write_to_all_levels) return kpi_score def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name, set_name): """ this method calculates score for specific atomic kpi :param set_name: kpi set name :param kpi_name: kpi name :param kpi_level_3_name: the atomic kpi name :param row: atomic kpi details :return: atomic kpi score """ atomic_kpi_score = 0 # get column name to consider in calculation relevant_columns = map(str.strip, str(row['column names']).split(',')) optional_columns = map(str.strip, str(row['optional column']).split(',')) is_kpi_passed = 0 if self.check_store_type(row, relevant_columns): # get weight of current atomic kpi curr_weight = row['weights'] # figure out what type of calculation need to be done if row['KPI type'] == 'Product Availability': if kpi_level_3_name == 'URBAN': score = self.calculate_weigthed_availability_score( row, relevant_columns) if score: atomic_kpi_score = score elif kpi_level_3_name == 'Hay o no hay # frentes': if self.calculate_lead_availability_score( row, relevant_columns): is_kpi_passed = 1 elif kpi_level_3_name == 'Adherencia de materiales': if self.calculate_or_availability(row, relevant_columns, optional_columns): is_kpi_passed = 1 else: if self.calculate_availability_score( row, relevant_columns): is_kpi_passed = 1 if kpi_level_3_name == '100% Marcas HE': curr_weight = self.unique_brand_count elif row['KPI type'] == 'SOS': ratio = self.calculate_sos_score(row, relevant_columns) if (row['product_type'] == 'Empty') & (ratio <= 0.2): if self.scif[self.scif['template_name'] == row['template_name']].empty: is_kpi_passed = 0 else: is_kpi_passed = 1 elif ratio == 1: is_kpi_passed = 1 elif row['KPI type'] == 'Survey': if self.calculate_survey_score(row): is_kpi_passed = 1 if is_kpi_passed == 1: atomic_kpi_score += curr_weight # write result to DB # the customer asked for this specific KPI will write 100 in DB if it passed even if the weight is 0 if kpi_level_3_name == 'Sin Espacios Vacios' and curr_weight == 0 and is_kpi_passed == 1: # atomic_kpi_score = 100 self.write_atomic_to_db(kpi_level_3_name, 100, kpi_name, set_name, is_kpi_passed, curr_weight) else: self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score, kpi_name, set_name, is_kpi_passed, curr_weight) return atomic_kpi_score # sos def calculate_sos_score(self, row, relevant_columns): """ this method calculates share of shelf score according to columns from the data frame :param relevant_columns: columns to check in the excel file :param row: data frame to calculate from :return: share of shelf score """ if 'shelf_number' in relevant_columns: scif_index_list = [] df = self.all_data[ (self.all_data.template_name == row['template_name']) & (self.all_data.shelf_number == row['shelf_number']) & (self.all_data.facing_sequence_number > 0)] df.shelf_number = df.shelf_number.astype(float) df.shelf_number = df.shelf_number.astype(str) df = df[df['product_type'] != 'Empty'] else: # get df only with the correct template name df = self.scif[self.scif.template_name == row['template_name']] if not pd.isna(row['exclude product_type']): for excl in [ e.strip() for e in row['exclude product_type'].split(',') ]: df = df[df['product_type'] != excl] # sum of all the facings in df facings = df.facings.values.sum() if facings == 0: return 0 # create dictionary for calculating filters_dict = self.create_sos_filtered_dictionary( relevant_columns, row) # reduce the df only to relevant columns df = df[filters_dict.keys()] # check if it's invasion KPI for the special case inv = row['KPI Level 3 Name'][:3] == 'Inv' ratio = self.calculate_sos_ratio(df, filters_dict, inv) return ratio / facings def calculate_sos_ratio(self, df, filters_dict, inv): ratio = 0 if 'manufacturer_name' in df.columns.tolist(): filter_out = df[(df['product_type'] == ('Empty')) & (df['manufacturer_name'] == 'General')].index df.drop(filter_out, inplace=True) for key in filters_dict: delete = [key for value in filters_dict[key] if value in ['nan']] for key in delete: del filters_dict[key] # iterate the data frame for i, df_row in df.iterrows(): # initialize the boolean variable bol = True # special case that the customer asked # if inv and df_row.product_type == 'Empty': if df_row.product_type == 'Empty': ratio = ratio + self.scif.facings.loc[i] continue # iterate the filtered dictionary keys else: for key in filters_dict.keys(): # check if the value in df row in in "key" column is in the filtered dictionary we created before bol &= df_row[key] in filters_dict[key] # that means success of the inner loop, that all the values matching for this data frame row if bol: if 'shelf_number' in filters_dict.keys(): ratio = ratio + self.all_data.facings.loc[i] else: # accumulate ratio ratio = ratio + self.scif.facings.loc[i] return ratio def create_sos_filtered_dictionary(self, relevant_columns, row): """ this method filters out relevant columns from the template fso we can calculate SOS easily :param relevant_columns: relevant columns from temolates :param row: specific row from template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value in [ 'Store Additional Attribute 4', 'Store Additional Attribute 13' ] or column_value == 'store_type': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict # availability: def calculate_weigthed_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ passed = 0 # Gets the scene types scene_types = row['template_name'].split(', ') scenes_num = len(scene_types) # man = row['manufacturer_name'] if 'scene_type' in relevant_columns: relevant_columns.remove('scene_type') # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for scene in scene_types: filters_dict.update({'template_name': scene}) # call the generic method from KPIUtils_v2 if self.availability.calculate_availability(**filters_dict): passed += 1 return (passed / float(scenes_num)) * 100 if scenes_num else 0 def calculate_lead_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ # Gets the brand names brand_names = row['brand_name'].split(', ') if 'scene_type' in relevant_columns: relevant_columns.remove('scene_type') # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for brand in brand_names: filters_dict.update({'brand_name': brand}) # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) if self.decide_availability_score(row, availability_score): return True return False def calculate_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) for key in filters_dict: delete = [key for value in filters_dict[key] if value in ['nan']] for key in delete: del filters_dict[key] # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) filtered_df = self.scif[self.get_filter_condition( self.scif, **filters_dict)] self.unique_brand_count = len(filtered_df['brand_name'].unique()) # check if this score should pass or fail return self.decide_availability_score(row, availability_score) def create_availability_filtered_dictionary(self, relevant_columns, row): """ this method creates a dictionary with keys according to the specific row in the template :param relevant_columns: columns to create keys by :param row: the specific row in the template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value == 'Store Additional Attribute 4' or column_value == 'store_type' or \ column_value == 'Store Additional Attribute 13': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict def calculate_or_availability(self, row, relevant_columns, optional_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ for optional_column in optional_columns: # create filtered dictionary temp_relevant_columns = relevant_columns[:] temp_relevant_columns.append(optional_column) filters_dict = self.create_availability_filtered_dictionary( temp_relevant_columns, row) for key in filters_dict: delete = [ key for value in filters_dict[key] if value in ['nan'] ] for key in delete: del filters_dict[key] # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) # check if this score should pass or fail if self.decide_availability_score(row, availability_score): return True return False def filter_product_names(self, exclude_skus): """ this method filters list of SKUs from self.scif :param exclude_skus: list of SKUs to exclude from template :return: filtered list """ return filter(lambda sku: sku not in exclude_skus, self.scif.product_name.values) @staticmethod def decide_availability_score(row, availability_score): """ this method decides if the score should pass or fail according to the template :param row: scpecific row from template :param availability_score: score :return: Boolean """ if availability_score == 0: return False else: if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[ 'KPI Level 3 Name'] == 'Hay o no hay Pop?': if row['KPI Level 2 Name'] == 'Pop Exterior': return availability_score > 1 elif row['KPI Level 2 Name'] == 'Pop Interior': return availability_score > 1 elif row['KPI Level 1 Name'] == 'Set Self Execution' and row[ 'KPI Level 3 Name'] == 'Hay o no hay # frentes': return availability_score > 24 else: return True # surveys: def calculate_survey_score(self, row): """ this method calculates survey score according to columns from the data frame :param row: data frame to calculate from :return: boolean """ question_code = str(int(row['Survey Question Code'])) if not self.survey_response.empty and \ not self.survey_response[self.survey_response.code == question_code].empty: answer = self.survey_response.selected_option_text[ self.survey_response.code == question_code].values[0] if answer == 'Si': return True else: if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador': return True return False else: return False # help functions: def calculate_posm_displays(self): new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(DISPLAY_KPI) for display_fk in self.match_displays_in_scene['display_fk'].to_list(): self.common2.write_to_db_result(fk=new_kpi_set_fk, result=0, numerator_id=self.manufacturer_fk, denominator_id=display_fk) new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(POSM_KPI) for product_fk in self.scif['product_fk'][self.scif['location_type'] == 'POSM'].to_list(): self.common2.write_to_db_result(fk=new_kpi_set_fk, result=0, numerator_id=self.manufacturer_fk, denominator_id=product_fk) def handle_exclude_skus(self, filters_dict, relevant_columns, row): """ this method checks if there is value in 'exclude skus' column in the template. if exists, it filters out the relevant skus from the calculation :param filters_dict: filtered dictionary :param relevant_columns: columns to create keys by :param row: specific row to calculate :return: None """ try: exclude_skus = row['exclude skus'].split(',') except AttributeError: exclude_skus = [] if exclude_skus: # filter out some product names according to template product_names = self.filter_product_names(exclude_skus) filters_dict['product_name'] = product_names if 'exclude skus' in row.to_dict().keys( ) and 'exclude skus' in relevant_columns: relevant_columns.remove('exclude skus') # db functions: def write_kpi_set_score_to_db(self, set_name, set_score): """ this method writes set kpi score to static.kps_results DB :param set_name: set name :param set_score: set score :return: None """ # kpi_set_fk = self.kpi_static_data.kpi_set_fk[self.kpi_static_data.kpi_set_name == set_name].unique()[0] # self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score) new_kpi_set_fk = self.common2.get_kpi_fk_by_kpi_name(set_name) self.common2.write_to_db_result( fk=new_kpi_set_fk, result=set_score, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, identifier_result=self.common2.get_dictionary(name=set_name)) def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score, write_to_all_levels): """ this method writes kpi score to static.kpk_results DB :param kpi_name: name of level 2 kpi :param set_name: name of related set :param kpi_score: the score :return: None """ # kpi_fk = \ # self.kpi_static_data.kpi_fk[ # (self.kpi_static_data.kpi_name.str.encode('utf-8') == kpi_name.encode('utf-8')) & # (self.kpi_static_data.kpi_set_name == set_name)].values[0] # self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score) if write_to_all_levels: new_kpi_fk = self.common2.get_kpi_fk_by_kpi_name(kpi_name) self.common2.write_to_db_result( fk=new_kpi_fk, result=kpi_score, should_enter=True, identifier_parent=self.common2.get_dictionary(name=set_name), identifier_result=self.common2.get_dictionary(name=kpi_name)) def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name, set_name, is_kpi_passed, curr_weight): """ this method writes atomic kpi score to static.kpi_results DB :param curr_weight: current weight of atomic kpi :param is_kpi_passed: is this kpi passed :param atomic_name: atomic kpi name :param atomic_score: the score :param kpi_name: name of related kpi :param set_name: name of related set :return: """ # atomic_kpi_fk = \ # self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) & # (self.kpi_static_data.kpi_name == kpi_name) & # (self.kpi_static_data.kpi_set_name == set_name)].values[0] # attrs = self.common.create_attributes_dict(fk=atomic_kpi_fk, score=is_kpi_passed, level=self.LEVEL3) # attrs['result'] = {0: atomic_score} # attrs['kpi_weight'] = {0: curr_weight} # query = insert(attrs, self.common.KPI_RESULT) # self.common.kpi_results_queries.append(query) identifier_parent = self.common2.get_dictionary(name=kpi_name) if atomic_name == kpi_name: identifier_parent = self.common2.get_dictionary(name=set_name) new_atomic_fk = self.common2.get_kpi_fk_by_kpi_name(atomic_name) # kpi_fk = \ # self.new_static_data[self.new_static_data['client_name'].str.encode('utf-8') == kpi_name.encode('utf-8')][ # 'pk'].values[0] self.common2.write_to_db_result(fk=new_atomic_fk, result=atomic_score, numerator_id=self.manufacturer_fk, denominator_id=self.store_id, weight=curr_weight, should_enter=True, score=is_kpi_passed, identifier_parent=identifier_parent) def mpis_merger(self): try: mpis = self.match_product_in_scene.merge(self.products, on='product_fk', suffixes=['', '_p']) \ .merge(self.scene_info, on='scene_fk', suffixes=['', '_s']) \ .merge(self.templates, on='template_fk', suffixes=['', '_t']) return mpis except: pass def get_filter_condition(self, df, **filters): """ :param df: The data frame to be filters. :param filters: These are the parameters which the data frame is filtered by. Every parameter would be a tuple of the value and an include/exclude flag. INPUT EXAMPLE (1): manufacturer_name = ('Diageo', DIAGEOAUGENERALToolBox.INCLUDE_FILTER) INPUT EXAMPLE (2): manufacturer_name = 'Diageo' :return: a filtered Scene Item Facts data frame. """ if not filters: return df['pk'].apply(bool) if self.facings_field in df.keys(): filter_condition = (df[self.facings_field] > 0) else: filter_condition = None for field in filters.keys(): if field in df.keys(): if isinstance(filters[field], tuple): value, exclude_or_include = filters[field] else: value, exclude_or_include = filters[ field], self.INCLUDE_FILTER if not value: continue if not isinstance(value, list): value = [value] if exclude_or_include == self.INCLUDE_FILTER: condition = (df[field].isin(value)) elif exclude_or_include == self.EXCLUDE_FILTER: condition = (~df[field].isin(value)) elif exclude_or_include == self.CONTAIN_FILTER: condition = (df[field].str.contains(value[0], regex=False)) for v in value[1:]: condition |= df[field].str.contains(v, regex=False) else: continue if filter_condition is None: filter_condition = condition else: filter_condition &= condition else: pass # Log.warning('field {} is not in the Data Frame'.format(field)) return filter_condition
class GSKNZToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.set_up_template = pd.read_excel(os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data', 'gsk_set_up.xlsx'), sheet_name='Functional KPIs', keep_default_na=False) self.gsk_generator = GSKGenerator(self.data_provider, self.output, self.common, self.set_up_template) def main_calculation(self, *args, **kwargs): """ This function calculates the KPI results. """ assortment_store_dict = self.gsk_generator.availability_store_function( ) self.common.save_json_to_new_tables(assortment_store_dict) assortment_category_dict = self.gsk_generator.availability_category_function( ) self.common.save_json_to_new_tables(assortment_category_dict) assortment_subcategory_dict = self.gsk_generator.availability_subcategory_function( ) self.common.save_json_to_new_tables(assortment_subcategory_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_whole_store_function( ) self.common.save_json_to_new_tables(facings_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_whole_store_function( ) self.common.save_json_to_new_tables(linear_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_sub_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_by_sub_category_function( ) self.common.save_json_to_new_tables(facings_sos_dict) facings_sos_dict = self.gsk_generator.gsk_global_facings_sos_by_category_function( ) self.common.save_json_to_new_tables(facings_sos_dict) linear_sos_dict = self.gsk_generator.gsk_global_linear_sos_by_category_function( ) self.common.save_json_to_new_tables(linear_sos_dict) self.common.commit_results_data() return
class INBEVTRADMXToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.store_info = self.data_provider[Data.STORE_INFO] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] self.templates_path = os.path.join( os.path.dirname(os.path.realpath(__file__)), '..', 'Data') self.excel_file_path = os.path.join(self.templates_path, 'inbevtradmx_template.xlsx') self.availability = Availability(self.data_provider) self.survey_response = self.data_provider[Data.SURVEY_RESPONSES] self.geo = GeoLocation.Geo(self.rds_conn, self.session_uid, self.data_provider, self.kpi_static_data, self.common) def parse_template(self): """ convert excel file to data frame :return: data frame """ template_df = pd.read_excel(self.excel_file_path, sheetname='template') return template_df def filter_product_names(self, exclude_skus): """ this method filters list of SKUs from self.scif :param exclude_skus: list of SKUs to exclude from template :return: filtered list """ return filter(lambda sku: sku not in exclude_skus, self.scif.product_name.values) def calculate_availability_score(self, row, relevant_columns): """ this method calculates availability score according to columns from the data frame :param row: data frame to calculate from :param relevant_columns: columns to check in the excel file :return: boolean """ # create filtered dictionary filters_dict = self.create_availability_filtered_dictionary( relevant_columns, row) # call the generic method from KPIUtils_v2 availability_score = self.availability.calculate_availability( **filters_dict) # check if this score should pass or fail return self.decide_availability_score(row, availability_score) def create_availability_filtered_dictionary(self, relevant_columns, row): """ this method creates a dictionary with keys according to the specific row in the template :param relevant_columns: columns to create keys by :param row: the specific row in the template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value == 'store Additional Attribute 4' or column_value == 'store_type': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict def handle_exclude_skus(self, filters_dict, relevant_columns, row): """ this method checks if there is value in 'exclude skus' column in the template. if exists, it filters out the relevant skus from the calculation :param filters_dict: filtered dictionary :param relevant_columns: columns to create keys by :param row: specific row to calculate :return: None """ try: exclude_skus = row['exclude skus'].split(',') except AttributeError: exclude_skus = [] if exclude_skus: # filter out some product names according to template product_names = self.filter_product_names(exclude_skus) filters_dict['product_name'] = product_names relevant_columns.remove('exclude skus') @staticmethod def decide_availability_score(row, availability_score): """ this method decides if the score should pass or fail according to the template :param row: scpecific row from template :param availability_score: score :return: Boolean """ if availability_score == 0: return False else: if row['KPI Level 1 Name'] == 'Set Modeloramas' and row[ 'KPI Level 3 Name'] == 'Hay o no hay Pop?': if row['KPI Level 2 Name'] == 'Pop Exterior': return availability_score > 1 elif row['KPI Level 2 Name'] == 'Pop Interior': return availability_score > 2 else: return True def calculate_sos_score(self, row, relevant_columns): """ this method calculates share of shelf score according to columns from the data frame :param relevant_columns: columns to check in the excel file :param row: data frame to calculate from :return: share of shelf score """ # get df only with the correct template name df = self.scif[self.scif.template_name == row['template_name']] # sum of all the facings in df facings = df.facings.values.sum() if facings == 0: return 0 # create dictionary for calculating filters_dict = self.create_sos_filtered_dictionary( relevant_columns, row) # reduce the df only to relevant columns df = df[filters_dict.keys()] ratio = self.calculate_sos_ratio(df, filters_dict) return ratio / facings def calculate_sos_ratio(self, df, filters_dict): ratio = 0 # iterate the data frame for i, df_row in df.iterrows(): # initialize the boolean variable bol = True # iterate the filtered dictionary keys for key in filters_dict.keys(): # check if the value in df row in in "key" column is in the filtered dictionary we created before bol &= df_row[key] in filters_dict[key] # that means success of the inner loop, that all the values matching for this data frame row if bol: # accumulate ratio ratio = ratio + self.scif.facings.loc[i] return ratio def create_sos_filtered_dictionary(self, relevant_columns, row): """ this method filters out relevant columns from the template fso we can calculate SOS easily :param relevant_columns: relevant columns from temolates :param row: specific row from template :return: dictionary """ # dictionary to send to the generic method filters_dict = {} self.handle_exclude_skus(filters_dict, relevant_columns, row) # fill the dictionary for column_value in relevant_columns: if column_value == 'Store Additional Attribute 4' or column_value == 'store_type': continue filters_dict[column_value] = map( str.strip, str(row.loc[column_value]).split(',')) return filters_dict def calculate_survey_score(self, row): """ this method calculates survey score according to columns from the data frame :param row: data frame to calculate from :return: boolean """ question_code = str(int(row['Survey Question Code'])) if not self.survey_response.empty and \ not self.survey_response[self.survey_response.code == question_code].empty: answer = self.survey_response.selected_option_text[ self.survey_response.code == question_code].values[0] if answer == 'Si': return True else: if row['KPI Level 2 Name'] == 'Primer Impacto' and answer == 'No tiene Enfirador': return True return False else: return False def check_store_type(self, row, relevant_columns): """ this method checks if the session store type is valid :type relevant_columns: list :param row: current KPI row :return: boolean """ # make sure that we need to check this field if relevant_columns.__contains__('store_type'): # get the valid stores from the template stores_df = pd.read_excel(self.excel_file_path, sheetname='store types') # create a list of the valid stores stores = stores_df.values return row['store_type'] in stores else: return True def calculate_set_score(self, set_df, set_name): """ this method iterates kpi set and calculates it's score :param set_df: the set df to calculate score for :param set_name: the kpi set name :return: None """ set_score = 0 # get array of all kpi level 2 names kpi_names = set_df['KPI Level 2 Name'].unique() # iterate all kpi level 2 for kpi_name in kpi_names: # calculate kpi level 2 score kpi_score = self.calculate_kpi_level_2_score( kpi_name, set_df, set_name) # write kpi level 2 score to DB self.write_kpi_score_to_db(kpi_name, set_name, kpi_score) # accumulate set score set_score += kpi_score # finally, write level 1 kpi set score to DB self.write_kpi_set_score_to_db(set_name, set_score) def calculate_kpi_level_2_score(self, kpi_name, set_df, set_name): """ this method gets kpi level 2 name, and iterates it's related atomic kpis :param set_name: kpi set name :param kpi_name: kpi level 2 name :param set_df: kpi set df :return: kpi level 2 score """ kpi_df = set_df[set_df['KPI Level 2 Name'] == kpi_name] kpi_score = 0 # iterate the all related atomic kpis for i, row in kpi_df.iterrows(): # get atomic kpi name kpi_level_3_name = row['KPI Level 3 Name'] # calculate atomic kpi score atomic_kpi_score = self.calculate_atomic_kpi_score( row, kpi_level_3_name, kpi_name, set_name) # accumulate kpi level 2 score kpi_score += atomic_kpi_score return kpi_score def calculate_atomic_kpi_score(self, row, kpi_level_3_name, kpi_name, set_name): """ this method calculates score for specific atomic kpi :param set_name: kpi set name :param kpi_name: kpi name :param kpi_level_3_name: the atomic kpi name :param row: atomic kpi details :return: atomic kpi score """ atomic_kpi_score = 0 # get column name to consider in calculation relevant_columns = map(str.strip, str(row['column names']).split(',')) if self.check_store_type(row, relevant_columns): # get weight of current atomic kpi curr_weight = row['weights'] # figure out what type of calculation need to be done if row['KPI type'] == 'Product Availability': if self.calculate_availability_score(row, relevant_columns): atomic_kpi_score += curr_weight elif row['KPI type'] == 'SOS': ratio = self.calculate_sos_score(row, relevant_columns) if (row['product_type'] == 'Empty') & (ratio <= 0.2): atomic_kpi_score += curr_weight elif ratio == 1: atomic_kpi_score += curr_weight elif row['KPI type'] == 'Survey': if self.calculate_survey_score(row): atomic_kpi_score += curr_weight # write result to DB self.write_atomic_to_db(kpi_level_3_name, atomic_kpi_score, kpi_name, set_name) return atomic_kpi_score def write_kpi_set_score_to_db(self, set_name, set_score): """ this method writes set kpi score to static.kps_results DB :param set_name: set name :param set_score: set score :return: None """ kpi_set_fk = self.kpi_static_data.kpi_set_fk[ self.kpi_static_data.kpi_set_name == set_name].unique()[0] self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, set_score) def write_kpi_score_to_db(self, kpi_name, set_name, kpi_score): """ this method writes kpi score to static.kpk_results DB :param kpi_name: name of level 2 kpi :param set_name: name of related set :param kpi_score: the score :return: None """ kpi_fk = \ self.kpi_static_data.kpi_fk[(self.kpi_static_data.kpi_name == kpi_name) & (self.kpi_static_data.kpi_set_name == set_name)].values[0] self.common.write_to_db_result(kpi_fk, self.LEVEL2, kpi_score) def write_atomic_to_db(self, atomic_name, atomic_score, kpi_name, set_name): """ this method writes atomic kpi score to static.kpi_results DB :param atomic_name: atomic kpi name :param atomic_score: the score :param kpi_name: name of related kpi :param set_name: name of related set :return: """ atomic_kpi_fk = \ self.kpi_static_data.atomic_kpi_fk[(self.kpi_static_data.atomic_kpi_name == atomic_name) & (self.kpi_static_data.kpi_name == kpi_name) & (self.kpi_static_data.kpi_set_name == set_name)].values[0] self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3, atomic_score) def calculate_kpi_set_from_template(self): """ this method chooses the correct set to calculate there will always be only on set to calculate, depending on the field 'Store additional attribute 4' from template :return: None """ # get the template parsed_template = self.parse_template() # get all the unique sets sets = parsed_template['KPI Level 1 Name'].unique() # get the session additional_attribute_4 additional_attribute_4 = self.store_info.additional_attribute_4.values[ 0] set_name = self.choose_correct_set_to_calculate( additional_attribute_4, sets) # wrong value in additional attribute 4 - shouldn't calculate if set_name == '': Log.warning( 'Wrong value in additional attribute 4 - shouldnt calculate') return -1 # get only the part of the template that is related to this set set_template_df = parsed_template[parsed_template['KPI Level 1 Name'] == set_name] # start calculating ! self.calculate_set_score(set_template_df, set_name) @staticmethod def choose_correct_set_to_calculate(additional_attribute_4, sets): """ choose what is the appropriate set to calculate :param additional_attribute_4: session additional_attribute_4 :param sets: list of optional sets :return: set name to calculate """ if additional_attribute_4 == 'BC': set_name = sets[0] elif additional_attribute_4 == 'BA': set_name = sets[1] elif additional_attribute_4 == 'MODELORAMA': set_name = sets[2] else: return '' return set_name def main_calculation(self): # calculate geo geo_result = self.geo.calculate_geo_location() self.geo.write_geo_to_db(float(geo_result)) # calculate from template self.calculate_kpi_set_from_template() self.common.commit_results_data()
class AMERICASToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 FIRST_KPI = 'First KPI' SECOND_KPI = 'Second KPI' def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.kpi_results_queries = [] def get_product_fk_from_product_name(self, product_name): return self.all_products.product_fk[self.all_products.product_name == product_name].values[0] def calculate_first_kpi(self): """ this method if in shelf number 2 all products are the product name below :return: True/False """ product_name = 'CORONA LIGHT 355ML BOTE TALLCAN' mpis = self.match_product_in_scene second_shelf = mpis[mpis.shelf_number == 2] unique_products_in_second_self = second_shelf.product_fk.unique() if len(unique_products_in_second_self) != 1: return False else: product_fk = self.get_product_fk_from_product_name(product_name) if product_fk in unique_products_in_second_self: return True else: return False def calculate_second_kpi(self): product_name = 'MODELO ESPECIAL 355ML BOTE SINGLE' facing_counter = 0 try: product_fk = self.get_product_fk_from_product_name(product_name) products_in_scene_df = self.match_product_in_scene[ self.match_product_in_scene.product_fk == product_fk] for i, row in products_in_scene_df.iterrows(): if str(row.face_count) == 'nan': facing_counter += 1 else: facing_counter += row.face_count except IndexError: pass return facing_counter >= 5 def write_result_to_db(self, kpi_name, kpi_result): if kpi_result: score = 100 else: score = 0 atomic_kpi_fk = self.kpi_static_data.atomic_kpi_fk[ self.kpi_static_data.atomic_kpi_name == kpi_name].values[0] self.common.write_to_db_result(atomic_kpi_fk, self.LEVEL3, score) kpi_fk = self.kpi_static_data.kpi_fk[self.kpi_static_data.kpi_name == kpi_name].values[0] self.common.write_to_db_result(kpi_fk, self.LEVEL2, score) kpi_set_fk = self.kpi_static_data.kpi_set_fk[ self.kpi_static_data.kpi_name == kpi_name].values[0] self.common.write_to_db_result(kpi_set_fk, self.LEVEL1, score) def main_calculation(self): self.write_result_to_db(self.FIRST_KPI, self.calculate_first_kpi()) self.write_result_to_db(self.SECOND_KPI, self.calculate_second_kpi()) self.common.commit_results_data()
class PURINAToolBox: LEVEL1 = 1 LEVEL2 = 2 LEVEL3 = 3 def __init__(self, data_provider, output): self.output = output self.data_provider = data_provider self.common = Common(self.data_provider) self.project_name = self.data_provider.project_name self.session_uid = self.data_provider.session_uid self.products = self.data_provider[Data.PRODUCTS] self.all_products = self.data_provider[Data.ALL_PRODUCTS] self.match_product_in_scene = self.data_provider[Data.MATCHES] self.visit_date = self.data_provider[Data.VISIT_DATE] self.session_info = self.data_provider[Data.SESSION_INFO] self.scene_info = self.data_provider[Data.SCENES_INFO] self.store_id = self.data_provider[Data.STORE_FK] self.scif = self.data_provider[Data.SCENE_ITEM_FACTS] self.rds_conn = PSProjectConnector(self.project_name, DbUsers.CalculationEng) self.kpi_static_data = self.common.get_kpi_static_data() self.session_info = self.data_provider[Data.SESSION_INFO] self.session_fk = self.session_info['pk'].values[0] self.kpi_results_queries = [] self.kpi_static_queries = [] self.purina_scif = self.scif.loc[self.scif['category_fk'] == PET_FOOD_CATEGORY] def calculate_purina(self, *args, **kwargs): """ This function calculates the KPI results. """ if not self.is_session_purina(): return # Update all new static KPIs self.create_new_static_kpi() self.kpi_static_data = self.common.get_kpi_static_data(refresh=True) self.update_kpi_score() self.run_data_collecting() self.common.commit_results_data() def update_kpi_score(self): # Only to see results in join :( for kpi in PURINA_KPI: kpi_fk = self.get_kpi_fk_by_kpi_name(kpi, self.LEVEL2, set_name=PURINA_SET) self.common.write_to_db_result(kpi_fk, self.LEVEL2, 1) def run_data_collecting(self): """ This function run the man calculation of linear sos with sub category out of subsegment or price out of subsegment :param price_kpi: :return: """ data = self.purina_scif.dropna(subset=[LINEAR_SIZE]) if data.empty: Log.info("No relevant purina's products were found in session.") return # subseg_name_list = data[SCIF_SUBSEGMENT].unique() # for subseg in subseg_name_list: # if not subseg: # subseg = NO_SUBSEG # by_subseg = data.loc[pd.isnull(data[SCIF_SUBSEGMENT])] # subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE])) # else: # by_subseg = data.loc[data[SCIF_SUBSEGMENT] == subseg] # subseg_ft = self.cm_to_ft(sum(by_subseg[LINEAR_SIZE])) # atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=SUBSEGMENT_SET) # self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft) # atomic_fk = self.get_kpi_fk_by_kpi_name(subseg, self.LEVEL3, father=SUBSEGMENT_KPI, set_name=PRICE_SET) # self.common.old_write_to_db_result(fk=atomic_fk, level=self.LEVEL3, score=subseg_ft) # gets all category linear size category_ft = self.cm_to_ft(sum(data[LINEAR_SIZE])) fk = self.get_kpi_fk_by_kpi_name(PURINA_SET, self.LEVEL1) self.common.write_to_db_result(fk, self.LEVEL1, category_ft) man = data['manufacturer_name'].unique() for mf in man: by_mf = data.loc[data['manufacturer_name'] == mf] manufacturer_ft = self.cm_to_ft(sum(by_mf[LINEAR_SIZE])) relevant_kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == MANUFACTUR) & (self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0] atomic_fk = self.get_kpi_fk_by_kpi_name(mf, self.LEVEL3, father=MANUFACTUR, set_name=PURINA_SET) if atomic_fk: self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3, score=manufacturer_ft, score_2=manufacturer_ft, session_uid=self.session_uid, store_fk=self.store_id, display_text=mf.replace("'","''"), visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=PURINA_SET, kpi_fk=relevant_kpi_fk) else: print 'atomic cannot be saved for manufacturer {}'.format(mf) brands = by_mf['brand_name'].unique() for brand in brands: by_brand = by_mf.loc[data['brand_name'] == brand] brand_ft = self.cm_to_ft(sum(by_brand[LINEAR_SIZE])) kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == BRAND) & (self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0] atomic_fk = self.get_kpi_fk_by_kpi_name(brand, self.LEVEL3, father=BRAND, set_name=PURINA_SET) if atomic_fk: self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3, score=brand_ft, score_2=brand_ft, style=mf.replace("'","''"), session_uid=self.session_uid, store_fk=self.store_id, display_text=brand.replace("'","''"), visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=PURINA_SET, kpi_fk=kpi_fk) else: print 'atomic cannot be saved for brand {}'.format(brand) categories = by_brand[SCIF_CATEOGRY].unique() for cat in categories: if not cat: cat = OTHER by_cat = by_brand.loc[pd.isnull(by_brand[SCIF_PRICE])] cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE])) else: by_cat = by_brand.loc[data[SCIF_SUB_CATEOGRY] == cat] cat_ft = self.cm_to_ft(sum(by_cat[LINEAR_SIZE])) kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == CATEGORY) & (self.kpi_static_data['kpi_set_name'] == PURINA_SET)]['kpi_fk'].values[0] atomic_fk = self.get_kpi_fk_by_kpi_name(cat, self.LEVEL3, father=CATEGORY, set_name=PURINA_SET) if atomic_fk: self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3, score=cat_ft, score_2=cat_ft, style=mf.replace("'","''"), result=brand.replace("'","''"), session_uid=self.session_uid, store_fk=self.store_id, display_text=cat.replace("'","''"), visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=PURINA_SET, kpi_fk=kpi_fk) else: print 'atomic cannot be saved for category {}'.format(cat) sub_cats = by_cat[SCIF_SUB_CATEOGRY].unique() for sub_cat in sub_cats: if not sub_cat: sub_cat = OTHER by_sub_cat = by_cat.loc[pd.isnull(by_cat[SCIF_PRICE])] sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE])) else: by_sub_cat = by_cat.loc[data[SCIF_SUB_CATEOGRY] == sub_cat] sub_cat_ft = self.cm_to_ft(sum(by_sub_cat[LINEAR_SIZE])) # write to db under sub category atomic kpi score with brand name in results kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == SUB_CATEGORY) & (self.kpi_static_data['kpi_set_name'] == PURINA_SET)][ 'kpi_fk'].values[0] atomic_fk = self.get_kpi_fk_by_kpi_name(sub_cat, self.LEVEL3, father=SUB_CATEGORY, set_name=PURINA_SET) if atomic_fk: self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3, score=sub_cat_ft, score_2=sub_cat_ft, style=mf.replace("'","''"), result=brand.replace("'","''"), result_2=cat.replace("'","''"), session_uid=self.session_uid, store_fk=self.store_id, display_text=sub_cat.replace("'","''"), visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=PURINA_SET, kpi_fk=kpi_fk) else: print 'atomic cannot be saved for sub category {}'.format(sub_cat) prices = by_sub_cat[SCIF_PRICE].unique() for price_class in prices: if not price_class: price_class = OTHER by_prices = by_sub_cat.loc[pd.isnull(by_sub_cat[SCIF_PRICE])] price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE])) else: by_prices = by_sub_cat.loc[by_sub_cat[SCIF_PRICE] == price_class] price_ft = self.cm_to_ft(sum(by_prices[LINEAR_SIZE])) kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == PRICE_KPI) & (self.kpi_static_data['kpi_set_name'] == PURINA_SET)][ 'kpi_fk'].values[0] atomic_fk = self.get_kpi_fk_by_kpi_name(price_class, self.LEVEL3, father=PRICE_KPI, set_name=PURINA_SET) if atomic_fk: self.common.write_to_db_result(fk=atomic_fk, atomic_kpi_fk=atomic_fk, level=self.LEVEL3, score=price_ft, score_2=price_ft, style=mf.replace("'","''"), result=brand.replace("'","''"), result_2=cat.replace("'","''"), result_3=sub_cat.replace("'","''"), session_uid=self.session_uid, store_fk=self.store_id, display_text=price_class.replace("'", "''"), visit_date=self.visit_date.isoformat(), calculation_time=datetime.utcnow().isoformat(), kps_name=PURINA_SET, kpi_fk=kpi_fk ) else: print 'atomic cannot be saved for price class {}'.format(price_class) @staticmethod def cm_to_ft(cm): return cm / 30.48 def get_labels(self): query = """select pk, labels, ean_code from static_new.product """ labels = pd.read_sql_query(query, self.rds_conn.db) return labels def get_kpi_fk_by_kpi_name(self, kpi_name, kpi_level, father=None, logic_father=None, set_name=None): if kpi_level == self.LEVEL1: column_key = 'kpi_set_fk' column_value = 'kpi_set_name' father_value = 'kpi_set_name' elif kpi_level == self.LEVEL2: column_key = 'kpi_fk' column_value = 'kpi_name' father_value = 'kpi_set_name' elif kpi_level == self.LEVEL3: column_key = 'atomic_kpi_fk' column_value = 'atomic_kpi_name' father_value = 'kpi_name' else: raise ValueError('invalid level') try: relevant = self.kpi_static_data[self.kpi_static_data[column_value] == kpi_name] if father: relevant = relevant[relevant[father_value] == father] if set_name: relevant = relevant[relevant['kpi_set_name'] == set_name] return relevant[column_key].values[0] except IndexError: Log.info('Kpi name: {}, isn\'t equal to any kpi name in static table'.format(kpi_name)) return None def create_new_static_kpi(self): # This functions takes all brands, sub categories, categories and manufacturers in session. # The function adds them to database in case they are new. brands = self.get_all_brands() sub_cats = self.get_all_sub_categories() manufacturer = self.get_all_manufacturers() cats = self.get_all_categories() prices = self.get_all_price_classes() new_brands = self.purina_scif.loc[~self.purina_scif['brand_name'].isin(brands)]['brand_name'].unique() new_manufacturer = self.purina_scif.loc[~self.purina_scif['manufacturer_name'].isin(manufacturer)][ 'manufacturer_name'].unique() new_sub_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_SUB_CATEOGRY].isin(sub_cats)) & (~pd.isnull(self.purina_scif[SCIF_SUB_CATEOGRY]))][SCIF_SUB_CATEOGRY].unique() new_cat = self.purina_scif.loc[(~self.purina_scif[SCIF_CATEOGRY].isin(cats)) & (~pd.isnull(self.purina_scif[SCIF_CATEOGRY]))][SCIF_CATEOGRY].unique() new_prices = self.purina_scif.loc[(~self.purina_scif[SCIF_PRICE].isin(prices)) & (~pd.isnull(self.purina_scif[SCIF_PRICE]))][SCIF_PRICE].unique() self.save_static_atomics(BRAND, new_brands, PURINA_SET) self.save_static_atomics(MANUFACTUR, new_manufacturer, PURINA_SET) self.save_static_atomics(CATEGORY, new_cat, PURINA_SET) self.save_static_atomics(SUB_CATEGORY, new_sub_cat, PURINA_SET) self.save_static_atomics(PRICE_KPI, new_prices, PURINA_SET) self.commit_static_data() def get_all_brands(self): return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == BRAND]['atomic_kpi_name'] def get_all_sub_categories(self): return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == SUB_CATEGORY]['atomic_kpi_name'] def get_all_manufacturers(self): return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == MANUFACTUR]['atomic_kpi_name'] def get_all_categories(self): return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == CATEGORY]['atomic_kpi_name'] def get_all_price_classes(self): return self.kpi_static_data.loc[self.kpi_static_data['kpi_name'] == PRICE_KPI]['atomic_kpi_name'] def save_static_atomics(self, kpi_name, atomics, set_name): kpi_fk = self.kpi_static_data.loc[(self.kpi_static_data['kpi_name'] == kpi_name) & (self.kpi_static_data['kpi_set_name'] == set_name)]['kpi_fk'].values[0] for current in atomics: current = current.replace("'", "''") query = """ INSERT INTO {0} (`kpi_fk`, `name`, `description`, `display_text`,`presentation_order`, `display`) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}');""".format(STATIC_ATOMIC, kpi_fk, current, current, current, 1, 'Y') self.kpi_static_queries.append(query) def commit_static_data(self): """ This function writes all KPI results to the DB, and commits the changes. """ self.rds_conn.disconnect_rds() self.rds_conn.connect_rds() # ProjectConnector(self.project_name, DbUsers.CalculationEng) cur = self.rds_conn.db.cursor() for query in self.kpi_static_queries: try: cur.execute(query) except Exception as e: Log.info('query {} could not be executed.'.format(query)) self.rds_conn.db.commit() self.rds_conn.disconnect_rds() def is_session_purina(self): # This function checks is the session is of Purina project by its category and that it is a successful visit. session_data = self.get_session_category_data() session_data = session_data.loc[(session_data['category_fk'] == 13) & (session_data['resolution_fk'] == 1) & (session_data['exclude_status_fk'] == 1)] if not session_data.empty: return True return False def get_session_category_data(self): local_con = PSProjectConnector(self.project_name, DbUsers.CalculationEng) query = """select category_fk, resolution_fk, exclude_status_fk from probedata.session_category where session_fk = {}""".format(self.session_fk) data = pd.read_sql_query(query, local_con.db) return data