def test_set_filtered_scif_and_matches_for_specific_kpi_additionally_filter_scif_and_matches(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
scif, matches = tool_box.set_filtered_scif_and_matches_for_specific_kpi(
tool_box.filtered_scif, tool_box.filtered_matches, 'Brand SOS')
matches_excluded_kpi_specific = set(tool_box.filtered_matches['probe_match_fk'].values.tolist()) - \
set(matches['probe_match_fk'].values.tolist())
self.assertEquals(len(matches), 30)
self.assertNotEqual(len(matches), len(tool_box.filtered_matches))
included_matches_expected = [
3, 4, 5, 6, 7, 8, 14, 15, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27,
28, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43
]
excluded_matches_expected = [
11, 12, 13, 20, 29, 30, 31, 32, 44, 45, 46, 47, 48, 49, 50, 51
]
self.assertItemsEqual(matches_excluded_kpi_specific,
excluded_matches_expected)
matches_positive_check = tool_box.filtered_matches[tool_box.filtered_matches['probe_match_fk']. \
isin(included_matches_expected)]
self.assertItemsEqual(
included_matches_expected,
matches_positive_check['probe_match_fk'].values.tolist())
self.assertEquals(len(scif), 5)
self.assertNotEqual(len(scif), len(tool_box.filtered_scif))
def test_filters_for_scif_and_matches_return_empty_dict_if_template_empty(
self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
expected_result = {}
template_filters = tool_box.get_filters_dictionary(
DataTestUnitPEPSICOUK.empty_exclusion_template)
self.assertDictEqual(expected_result, template_filters)
self.assertIsInstance(template_filters, dict)
def test_filters_for_scif_and_matches_return_empty_dict_with_ommitted_action_field(
self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
expected_result = {
'category': (['Cat 1', 'Cat 2'], 0),
'location_type': ['Primary Shelf']
}
template_filters = tool_box.get_filters_dictionary(
DataTestUnitPEPSICOUK.exclusion_template_missing_action)
self.assertDictEqual(expected_result, template_filters)
def test_do_exclusion_rules_apply_to_store_returns_false_if_kpi_in_store_policy_tab_and_store_attr_do_not_match_policy(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
result = tool_box.do_exclusion_rules_apply_to_store('Some KPI')
self.assertFalse(result)
def test_do_exclusion_rules_apply_to_store_returns_true_if_kpi_in_store_policy_tab_and_store_attributes_comply_and_values_with_comma(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
result = tool_box.do_exclusion_rules_apply_to_store('Hero SKU SOS')
self.assertTrue(result)
def test_unpack_external_targets_json_fields_to_df_returns_empty_df_if_input_empty(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
result_df = tool_box.unpack_external_targets_json_fields_to_df(
DataTestUnitPEPSICOUK.data_json_empty, 'json_field')
self.assertTrue(result_df.empty)
def test_filters_for_scif_and_matches_are_retrieved_in_the_right_format(
self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
expected_result = {
'smart_attribute_state': (['additional display'], 0),
'product_name': (['General Empty'], 0)
}
excl_template_all_kpis = tool_box.exclusion_template[
tool_box.exclusion_template['KPI'].str.upper() == 'ALL']
template_filters = tool_box.get_filters_dictionary(
excl_template_all_kpis)
self.assertDictEqual(expected_result, template_filters)
def test_unpack_external_targets_json_fields_to_df_returns_df_with_pk_field_only(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
result_df = tool_box.unpack_external_targets_json_fields_to_df(
DataTestUnitPEPSICOUK.data_json_empty_with_pks, 'json_field')
self.assertItemsEqual(result_df.columns.values.tolist(), ['pk'])
def test_get_filters_for_scif_and_matches_returns_values_with_product_and_scene_pks(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
excl_template_all_kpis = tool_box.exclusion_template[
tool_box.exclusion_template['KPI'].str.upper() == 'ALL']
template_filters = tool_box.get_filters_dictionary(
excl_template_all_kpis)
filters = tool_box.get_filters_for_scif_and_matches(template_filters)
expected_result = {'product_fk': [1, 2, 3, 4, 5]}
self.assertDictEqual(filters, expected_result)
def test_set_filtered_scif_and_matches_for_specific_kpi_does_not_change_filtered_scif_and_matches_if_no_policy_applies(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
scif, matches = tool_box.set_filtered_scif_and_matches_for_specific_kpi(
tool_box.filtered_scif, tool_box.filtered_matches,
'PepsiCo Segment Space to Sales Index')
self.assertEquals(len(matches), len(tool_box.filtered_matches))
self.assertEquals(len(scif), len(tool_box.filtered_scif))
assert_frame_equal(scif, tool_box.filtered_scif)
assert_frame_equal(matches, tool_box.filtered_matches)
def test_unpack_all_external_targets_forms_data_frame_with_all_relevant_columns_and_all_records(
self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
columns = tool_box.all_targets_unpacked.columns.values.tolist()
expected_columns_in_output_df = DataTestUnitPEPSICOUK.external_targets_columns
validation_list = [
col in columns for col in expected_columns_in_output_df
]
self.assertTrue(all(validation_list))
self.assertEquals(len(tool_box.all_targets_unpacked), 32)
def test_unpack_all_external_targets_kpi_relevant_columns_are_filled(self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
targets = tool_box.all_targets_unpacked
shelf_placement_targets = targets[targets['operation_type'] ==
tool_box.SHELF_PLACEMENT]
data_with_null_values = shelf_placement_targets[shelf_placement_targets \
['Shelves From Bottom To Include (data)'].isnull()]
self.assertEquals(len(data_with_null_values), 0)
keys_with_null_values = shelf_placement_targets[shelf_placement_targets \
['No of Shelves in Fixture (per bay) (key)'].isnull()]
self.assertEquals(len(keys_with_null_values), 0)
def test_empty_scene_details(self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_2, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_2,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
self.assertTrue(tool_box.scif.empty)
self.assertTrue(tool_box.match_product_in_scene.empty)
self.assertTrue(tool_box.filtered_matches.empty)
self.assertTrue(tool_box.filtered_scif.empty)
def test_unpack_external_targets_json_fields_to_df_gets_all_fields_from_json_and_matches_correct_pks(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1, sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_1,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
expected_result = pd.DataFrame.from_records([
{
'pk': 2,
u'store_type': u'CORE',
u'additional_attribute_2': np.nan,
u'additional_attribute_1': u'ALL'
},
{
'pk': 3,
u'store_type': u'ALL',
u'additional_attribute_2': np.nan,
u'additional_attribute_1': u'OT'
},
{
'pk': 10,
u'store_type': u'CORE',
u'additional_attribute_2': u'SAINSBURY',
u'additional_attribute_1': np.nan
},
])
result_df = tool_box.unpack_external_targets_json_fields_to_df(
DataTestUnitPEPSICOUK.data_json_1, 'json_field')
self.assertItemsEqual([
'pk', 'store_type', 'additional_attribute_1',
'additional_attribute_2'
], result_df.columns.values.tolist())
assert_frame_equal(expected_result, result_df)
def test_filtered_scif_facings_and_gross_length_reduced_following_matches(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
for i, row in tool_box.filtered_scif.iterrows():
expected_facings = len(tool_box.filtered_matches[
(tool_box.filtered_matches['scene_fk'] == row['scene_fk'])
& (tool_box.filtered_matches['product_fk'] == row['product_fk']
)])
expected_len = tool_box.filtered_matches[(tool_box.filtered_matches['scene_fk'] == row['scene_fk']) &
(tool_box.filtered_matches['product_fk'] == row['product_fk'])] \
['width_mm_advance'].sum()
self.assertEquals(row['facings'], expected_facings)
self.assertEquals(row['gross_len_add_stack'], expected_len)
def test_excluded_matches_are_not_present_in_filtered_matches_and_included_are_present(
self):
self.mock_scene_item_facts(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='scif'))
self.mock_match_product_in_scene(
pd.read_excel(DataTestUnitPEPSICOUK.test_case_rollout,
sheetname='matches'))
self.mock_store_area(DataTestUnitPEPSICOUK.store_area_map_neutral)
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
excluded_matches = tool_box.match_product_in_scene[~(
tool_box.match_product_in_scene['Out'].isnull()
)]['probe_match_fk'].values.tolist()
matches_negative_check = tool_box.filtered_matches[tool_box.filtered_matches['probe_match_fk'].\
isin(excluded_matches)]
self.assertTrue(matches_negative_check.empty)
included_matches = tool_box.match_product_in_scene[
tool_box.match_product_in_scene['Out'].isnull(
)]['probe_match_fk'].values.tolist()
matches_positive_check = tool_box.filtered_matches[tool_box.filtered_matches['probe_match_fk'].\
isin(included_matches)]
self.assertItemsEqual(
included_matches,
matches_positive_check['probe_match_fk'].values.tolist())
class PepsicoUtil(UnifiedKPISingleton):
LEVEL1 = 1
LEVEL2 = 2
LEVEL3 = 3
EXCLUSION_TEMPLATE_PATH = os.path.join(
os.path.dirname(os.path.realpath(__file__)), '..', 'Data',
'Inclusion_Exclusion_Template_Rollout.xlsx')
ADDITIONAL_DISPLAY = 'additional display'
INCLUDE_EMPTY = True
EXCLUDE_EMPTY = False
OPERATION_TYPES = []
SOS_VS_TARGET = 'SOS vs Target'
HERO_SKU_SPACE_TO_SALES_INDEX = 'Hero SKU Space to Sales Index'
HERO_SKU_SOS_VS_TARGET = 'Hero SKU SOS vs Target'
LINEAR_SOS_INDEX = 'Linear SOS Index'
PEPSICO = 'PEPSICO'
SHELF_PLACEMENT = 'Shelf Placement'
HERO_SKU_PLACEMENT_TOP = 'Hero SKU Placement by shelf numbers_Top'
HERO_PLACEMENT = 'Hero Placement'
HERO_SKU_STACKING = 'Hero SKU Stacking'
HERO_SKU_PRICE = 'Hero SKU Price'
HERO_SKU_PROMO_PRICE = 'Hero SKU Promo Price'
BRAND_FULL_BAY_KPIS = ['Brand Full Bay_90', 'Brand Full Bay']
BRAND_FULL_BAY = 'Brand Full Bay'
HERO_PREFIX = 'Hero SKU'
ALL = 'ALL'
HERO_SKU_OOS_SKU = 'Hero SKU OOS - SKU'
HERO_SKU_OOS = 'Hero SKU OOS'
HERO_SKU_AVAILABILITY = 'Hero SKU Availability'
BRAND_SPACE_TO_SALES_INDEX = 'Brand Space to Sales Index'
BRAND_SPACE_SOS_VS_TARGET = 'Brand Space SOS vs Target'
SUB_BRAND_SPACE_TO_SALES_INDEX = 'Sub Brand Space to Sales Index'
SUB_BRAND_SPACE_SOS_VS_TARGET = 'Sub Brand Space SOS vs Target'
PEPSICO_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Segment Space to Sales Index'
PEPSICO_SEGMENT_SOS_VS_TARGET = 'PepsiCo Segment SOS vs Target'
PEPSICO_SUB_SEGMENT_SPACE_TO_SALES_INDEX = 'PepsiCo Sub Segment Space to Sales Index'
PEPSICO_SUB_SEGMENT_SOS_VS_TARGET = 'PepsiCo Sub Segment SOS vs Target'
PLACEMENT_BY_SHELF_NUMBERS_TOP = 'Placement by shelf numbers_Top'
TOTAL_LINEAR_SPACE = 'Total Linear Space'
NUMBER_OF_FACINGS = 'Number of Facings'
NUMBER_OF_BAYS = 'Number of bays'
NUMBER_OF_SHELVES = 'Number of shelves'
PRODUCT_BLOCKING = 'Product Blocking'
PRODUCT_BLOCKING_ADJACENCY = 'Product Blocking Adjacency'
SHELF_PLACEMENT_VERTICAL_LEFT = 'Shelf Placement Vertical_Left'
SHELF_PLACEMENT_VERTICAL_CENTER = 'Shelf Placement Vertical_Center'
SHELF_PLACEMENT_VERTICAL_RIGHT = 'Shelf Placement Vertical_Right'
NUMBER_OF_SHELVES_TEMPL_COLUMN = 'No of Shelves in Fixture (per bay) (key)'
RELEVANT_SHELVES_TEMPL_COLUMN = 'Shelves From Bottom To Include (data)'
SHELF_PLC_TARGETS_COLUMNS = [
'kpi_operation_type_fk', 'operation_type', 'kpi_level_2_fk', 'type',
NUMBER_OF_SHELVES_TEMPL_COLUMN, RELEVANT_SHELVES_TEMPL_COLUMN,
'KPI Parent'
]
SHELF_PLC_TARGET_COL_RENAME = {
'kpi_operation_type_fk_x': 'kpi_operation_type_fk',
'operation_type_x': 'operation_type',
'kpi_level_2_fk_x': 'kpi_level_2_fk',
'type_x': 'type',
NUMBER_OF_SHELVES_TEMPL_COLUMN + '_x': NUMBER_OF_SHELVES_TEMPL_COLUMN,
RELEVANT_SHELVES_TEMPL_COLUMN + '_x': RELEVANT_SHELVES_TEMPL_COLUMN,
'KPI Parent_x': 'KPI Parent'
}
HERO_SKU_AVAILABILITY_SKU = 'Hero SKU Availability - SKU'
HERO_SKU_PLACEMENT_BY_SHELF_NUMBERS = 'Hero SKU Placement by shelf numbers'
HERO_SKU_AVAILABILITY_BY_HERO_TYPE = 'Hero SKU Availability by Hero Type'
SHARE_OF_ASSORTMENT_BY_HERO_TYPE = 'Share of Assortment by Hero Type'
HERO_SKU_LABEL = 'Hero SKU'
HERO_TYPE = 'hero_type'
HERO_SKU_SOS_OF_CAT_BY_HERO_TYPE = 'Hero SKU SOS of Category by Hero Type'
CATEGORY_FULL_BAY = 'Category Full Bay'
CSN = 'CSN'
PRICE = 'Price'
PROMO_PRICE = 'Promo Price'
LINEAR_SPACE_PER_PRODUCT = 'Linear Space Per Product'
FACINGS_PER_PRODUCT = 'Facings per Product'
PRICE_SCENE = 'Price Scene'
PROMO_PRICE_SCENE = 'Promo Price Scene'
HERO_SKU_SOS = 'Hero SKU SOS'
BRAND_SOS = 'Brand SOS'
SUB_BRAND_SOS = 'Sub Brand SOS'
PEPSICO_SEGMENT_SOS = 'PepsiCo Segment SOS'
BRAND_SOS_OF_SEGMENT = 'Brand SOS of Segment'
BINS_NOT_RECOGNIZED = 'Bins_not_recognized'
def __init__(self, output, data_provider):
super(PepsicoUtil, self).__init__(data_provider)
self.output = output
self.common = Common(self.data_provider)
# self.common_v1 = CommonV1(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] if self.data_provider[Data.STORE_FK] is not None \
else self.session_info['store_fk'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.display_scene = self.get_match_display_in_scene()
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.probe_groups = self.get_probe_group()
self.match_product_in_scene = self.match_product_in_scene.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.toolbox = GENERALToolBox(self.data_provider)
self.commontools = PEPSICOUKCommonToolBox(self.data_provider,
self.rds_conn)
self.all_templates = self.commontools.all_templates
self.custom_entities = self.commontools.custom_entities
self.on_display_products = self.commontools.on_display_products
self.exclusion_template = self.commontools.exclusion_template
self.filtered_scif = self.commontools.filtered_scif.copy()
self.filtered_matches = self.commontools.filtered_matches.copy()
self.filtered_matches = self.filtered_matches.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy(
)
self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy(
)
self.scene_bay_shelf_product = self.commontools.scene_bay_shelf_product
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.full_store_info = self.commontools.full_store_info.copy()
self.external_targets = self.commontools.external_targets
self.assortment = Assortment(self.commontools.data_provider,
self.output)
self.lvl3_ass_result = self.get_lvl3_relevant_assortment_result()
self.own_manuf_fk = self.all_products[
self.all_products['manufacturer_name'] ==
self.PEPSICO]['manufacturer_fk'].values[0]
self.scene_kpi_results = self.get_results_of_scene_level_kpis()
self.kpi_results_check = pd.DataFrame(columns=[
'kpi_fk', 'numerator', 'denominator', 'result', 'score', 'context'
])
self.sos_vs_target_targets = self.construct_sos_vs_target_base_df()
self.all_targets_unpacked = self.commontools.all_targets_unpacked.copy(
)
self.block_results = pd.DataFrame(columns=['Group Name', 'Score'])
self.hero_type_custom_entity_df = self.get_hero_type_custom_entity_df()
def get_match_display_in_scene(self):
query = PEPSICOUK_Queries.get_match_display(self.session_uid)
match_display = pd.read_sql_query(query, self.rds_conn.db)
return match_display
def get_probe_group(self):
query = PEPSICOUK_Queries.get_probe_group(self.session_uid)
probe_group = pd.read_sql_query(query, self.rds_conn.db)
return probe_group
@staticmethod
def get_full_bay_and_positional_filters(parameters):
filters = {parameters['Parameter 1']: parameters['Value 1']}
if parameters['Parameter 2']:
filters.update({parameters['Parameter 2']: parameters['Value 2']})
if parameters['Parameter 3']:
filters.update({parameters['Parameter 3']: parameters['Value 3']})
return filters
# @staticmethod
# def get_stack_data(row):
# is_stack = False
# sequences_list = row['all_sequences'][0:-1].split(',')
# count_sequences = collections.Counter(sequences_list)
# repeating_items = [c > 1 for c in count_sequences.values()]
# if repeating_items:
# if any(repeating_items):
# is_stack = True
# return is_stack
@staticmethod
def split_and_strip(value):
return map(lambda x: x.strip(), str(value).split(','))
def construct_sos_vs_target_base_df(self):
sos_targets = self.get_relevant_sos_vs_target_kpi_targets()
sos_targets = sos_targets.drop_duplicates(subset=[
'kpi_operation_type_fk', 'kpi_level_2_fk', 'numerator_value',
'denominator_value', 'type'
],
keep='first')
sos_targets = sos_targets.drop(
['key_json', 'data_json', 'start_date', 'end_date'], axis=1)
if not sos_targets.empty:
sos_targets['numerator_id'] = sos_targets.apply(
self.retrieve_relevant_item_pks,
axis=1,
args=('numerator_type', 'numerator_value'))
sos_targets['denominator_id'] = sos_targets.apply(
self.retrieve_relevant_item_pks,
axis=1,
args=('denominator_type', 'denominator_value'))
sos_targets['identifier_parent'] = sos_targets['KPI Parent'].apply(
lambda x: self.common.get_dictionary(kpi_fk=int(float(x))))
return sos_targets
def get_relevant_sos_vs_target_kpi_targets(self, brand_vs_brand=False):
sos_vs_target_kpis = self.external_targets[
self.external_targets['operation_type'] == self.SOS_VS_TARGET]
sos_vs_target_kpis = sos_vs_target_kpis.drop_duplicates(subset=[
'operation_type', 'kpi_level_2_fk', 'key_json', 'data_json'
])
relevant_targets_df = pd.DataFrame(
columns=sos_vs_target_kpis.columns.values.tolist())
if not sos_vs_target_kpis.empty:
policies_df = self.commontools.unpack_external_targets_json_fields_to_df(
sos_vs_target_kpis, field_name='key_json')
policy_columns = policies_df.columns.values.tolist()
del policy_columns[policy_columns.index('pk')]
store_dict = self.full_store_info.to_dict('records')[0]
for column in policy_columns:
store_att_value = store_dict.get(column)
policies_df = policies_df[policies_df[column].isin(
[store_att_value, self.ALL])]
kpi_targets_pks = policies_df['pk'].values.tolist()
relevant_targets_df = sos_vs_target_kpis[
sos_vs_target_kpis['pk'].isin(kpi_targets_pks)]
# relevant_targets_df = relevant_targets_df.merge(policies_df, on='pk', how='left')
data_json_df = self.commontools.unpack_external_targets_json_fields_to_df(
relevant_targets_df, 'data_json')
relevant_targets_df = relevant_targets_df.merge(data_json_df,
on='pk',
how='left')
kpi_data = self.kpi_static_data[['pk', 'type']].drop_duplicates()
kpi_data.rename(columns={'pk': 'kpi_level_2_fk'}, inplace=True)
relevant_targets_df = relevant_targets_df.merge(
kpi_data,
left_on='kpi_level_2_fk',
right_on='kpi_level_2_fk',
how='left')
linear_sos_fk = self.common.get_kpi_fk_by_kpi_type(
self.LINEAR_SOS_INDEX)
if brand_vs_brand:
relevant_targets_df = relevant_targets_df[
relevant_targets_df['KPI Parent'] == linear_sos_fk]
else:
relevant_targets_df = relevant_targets_df[~(
relevant_targets_df['KPI Parent'] == linear_sos_fk)]
return relevant_targets_df
def retrieve_relevant_item_pks(self, row, type_field_name,
value_field_name):
try:
if row[type_field_name].endswith("_fk"):
item_id = row[value_field_name]
else:
# print row[type_field_name], ' :', row[value_field_name]
item_id = self.custom_entities[
self.custom_entities['name'] ==
row[value_field_name]]['pk'].values[0]
except KeyError as e:
Log.error('No id found for field {}. Error: {}'.format(
row[type_field_name], e))
item_id = None
return item_id
def calculate_sos(self, sos_filters, **general_filters):
numerator_linear = self.calculate_share_space(
**dict(sos_filters, **general_filters))
denominator_linear = self.calculate_share_space(**general_filters)
return float(numerator_linear), float(denominator_linear)
def calculate_share_space(self, **filters):
filtered_scif = self.filtered_scif[self.toolbox.get_filter_condition(
self.filtered_scif, **filters)]
space_length = filtered_scif['updated_gross_length'].sum()
return space_length
def add_kpi_result_to_kpi_results_df(self, result_list):
self.kpi_results_check.loc[len(self.kpi_results_check)] = result_list
def get_results_of_scene_level_kpis(self):
scene_kpi_results = pd.DataFrame()
if not self.scene_info.empty:
scene_kpi_results = self.ps_data.get_scene_results(
self.scene_info['scene_fk'].drop_duplicates().values)
return scene_kpi_results
def get_store_data_by_store_id(self):
store_id = self.store_id if self.store_id else self.session_info[
'store_fk'].values[0]
query = PEPSICOUK_Queries.get_store_data_by_store_id(store_id)
query_result = pd.read_sql_query(query, self.rds_conn.db)
return query_result
def get_facings_scene_bay_shelf_product(self):
self.filtered_matches['count'] = 1
aggregate_df = self.filtered_matches.groupby(
['scene_fk', 'bay_number', 'shelf_number', 'product_fk'],
as_index=False).agg({'count': np.sum})
return aggregate_df
def get_lvl3_relevant_assortment_result(self):
assortment_result = self.assortment.get_lvl3_relevant_ass()
# if assortment_result.empty:
# return assortment_result
# products_in_session = self.filtered_scif.loc[self.filtered_scif['facings'] > 0]['product_fk'].values
# assortment_result.loc[assortment_result['product_fk'].isin(products_in_session), 'in_store'] = 1
return assortment_result
@staticmethod
def get_block_and_adjacency_filters(target_series):
filters = {target_series['Parameter 1']: target_series['Value 1']}
if target_series['Parameter 2']:
filters.update(
{target_series['Parameter 2']: target_series['Value 2']})
if target_series['Parameter 3']:
filters.update(
{target_series['Parameter 3']: target_series['Value 3']})
return filters
@staticmethod
def get_block_filters(target_series):
if isinstance(target_series['Value 1'], list):
filters = {target_series['Parameter 1']: target_series['Value 1']}
else:
filters = {
target_series['Parameter 1']: [target_series['Value 1']]
}
if target_series['Parameter 2']:
if isinstance(target_series['Value 2'], list):
filters.update(
{target_series['Parameter 2']: target_series['Value 2']})
else:
filters.update(
{target_series['Parameter 2']: [target_series['Value 2']]})
if target_series['Parameter 3']:
if isinstance(target_series['Value 2'], list):
filters.update(
{target_series['Parameter 3']: target_series['Value 3']})
else:
filters.update(
{target_series['Parameter 3']: [target_series['Value 3']]})
return filters
def reset_filtered_scif_and_matches_to_exclusion_all_state(self):
self.filtered_scif = self.commontools.filtered_scif.copy()
self.filtered_matches = self.commontools.filtered_matches.copy()
def reset_secondary_filtered_scif_and_matches_to_exclusion_all_state(self):
self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy(
)
self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy(
)
def get_available_hero_sku_list(self, dependencies_df):
hero_list = dependencies_df[
(dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU)
& (dependencies_df['numerator_result'] == 1
)]['numerator_id'].unique().tolist()
return hero_list
def get_unavailable_hero_sku_list(self, dependencies_df):
hero_list = dependencies_df[
(dependencies_df['kpi_type'] == self.HERO_SKU_AVAILABILITY_SKU)
& (dependencies_df['numerator_result'] == 0
)]['numerator_id'].unique().tolist()
return hero_list
def get_hero_type_custom_entity_df(self):
hero_type_df = self.custom_entities[self.custom_entities['entity_type']
== self.HERO_TYPE]
hero_type_df.rename(columns={'pk': 'entity_fk'}, inplace=True)
return hero_type_df
def __init__(self, output, data_provider):
super(PepsicoUtil, self).__init__(data_provider)
self.output = output
self.common = Common(self.data_provider)
# self.common_v1 = CommonV1(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] if self.data_provider[Data.STORE_FK] is not None \
else self.session_info['store_fk'].values[0]
self.scif = self.data_provider[Data.SCENE_ITEM_FACTS]
self.rds_conn = PSProjectConnector(self.project_name,
DbUsers.CalculationEng)
self.display_scene = self.get_match_display_in_scene()
self.kpi_static_data = self.common.get_kpi_static_data()
self.kpi_results_queries = []
self.probe_groups = self.get_probe_group()
self.match_product_in_scene = self.match_product_in_scene.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.toolbox = GENERALToolBox(self.data_provider)
self.commontools = PEPSICOUKCommonToolBox(self.data_provider,
self.rds_conn)
self.all_templates = self.commontools.all_templates
self.custom_entities = self.commontools.custom_entities
self.on_display_products = self.commontools.on_display_products
self.exclusion_template = self.commontools.exclusion_template
self.filtered_scif = self.commontools.filtered_scif.copy()
self.filtered_matches = self.commontools.filtered_matches.copy()
self.filtered_matches = self.filtered_matches.merge(
self.probe_groups, on='probe_match_fk', how='left')
self.filtered_scif_secondary = self.commontools.filtered_scif_secondary.copy(
)
self.filtered_matches_secondary = self.commontools.filtered_matches_secondary.copy(
)
self.scene_bay_shelf_product = self.commontools.scene_bay_shelf_product
self.ps_data = PsDataProvider(self.data_provider, self.output)
self.full_store_info = self.commontools.full_store_info.copy()
self.external_targets = self.commontools.external_targets
self.assortment = Assortment(self.commontools.data_provider,
self.output)
self.lvl3_ass_result = self.get_lvl3_relevant_assortment_result()
self.own_manuf_fk = self.all_products[
self.all_products['manufacturer_name'] ==
self.PEPSICO]['manufacturer_fk'].values[0]
self.scene_kpi_results = self.get_results_of_scene_level_kpis()
self.kpi_results_check = pd.DataFrame(columns=[
'kpi_fk', 'numerator', 'denominator', 'result', 'score', 'context'
])
self.sos_vs_target_targets = self.construct_sos_vs_target_base_df()
self.all_targets_unpacked = self.commontools.all_targets_unpacked.copy(
)
self.block_results = pd.DataFrame(columns=['Group Name', 'Score'])
self.hero_type_custom_entity_df = self.get_hero_type_custom_entity_df()
def test_get_yes_no_score_type_fk_if_score_value_zero(self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
expected_res = 5
result_fk = tool_box.get_yes_no_score(0)
self.assertEquals(result_fk, expected_res)
def test_get_kpi_score_value_pk_by_value_returns_none_if_value_does_not_exist(
self):
tool_box = PEPSICOUKCommonToolBox(self.data_provider_mock, self.output)
result_fk = tool_box.get_kpi_score_value_pk_by_value(
'non_existing_value')
self.assertIsNone(result_fk)
