Python — Competitor Exclusivity Analysis

A continuation from the Competitor Exclusivity Analysis, SQL version here … This is a transpilation of the SQL version to python, given Python’s popularity hahahaha.

***

Concept Refresh:

***

Codes:

# import the necessary packages
import os
import pandas as pd
import numpy as np
from datetime import datetime

# read csv. 
input_file_path = "C:/Users/XXXXX/folder/"
table_dim_competitor_universe = pd.read_csv(input_file_path+'csv_table_dim_competitor_universe.csv')
table_monthly_aggregated_stock_count_by_outlet_and_state = pd.read_csv(input_file_path+'csv_table_monthly_aggregated_stock_count_by_outlet_and_state.csv')
# this step on date formating is not needed if it's read from a database instead of txt file
table_monthly_aggregated_stock_count_by_outlet_and_state["report_month"] = pd.to_datetime(table_monthly_aggregated_stock_count_by_outlet_and_state["report_month"])

# stage one
table_dim_competitor_universe_of_interest=table_dim_competitor_universe[
    table_dim_competitor_universe['flag_competitor_universe_for_exclusive_reporting'].isin(['Y'])]
table_compute_stage_one = pd.merge(table_monthly_aggregated_stock_count_by_outlet_and_state
                                   , table_dim_competitor_universe_of_interest
                                   , on=['state', 'competitor'], how='inner')
table_compute_stage_one=table_compute_stage_one.drop(['stock_count'], axis = 1)
table_compute_stage_one['flag_competitor_exists'] = 1
table_compute_stage_one

# stage two
table_compute_stage_two=table_compute_stage_one.\
            set_index(['report_month','state','outlet_name']).\
            groupby(['report_month','state','outlet_name']).\
            agg({'flag_competitor_exists':['sum']})
table_compute_stage_two.columns = ['count_number_of_competitor']
table_compute_stage_two = table_compute_stage_two.reset_index()
table_compute_stage_two

# stage three
table_compute_stage_two['group_competitor_exclusivity'] = np.where(table_compute_stage_two['count_number_of_competitor']==1, 'Competitor Exclusive',
                                                       np.where(table_compute_stage_two['count_number_of_competitor']>1, 'Competitor Non-Exclusive(Shared)'
                                                        , 'Recheck'))

table_compute_stage_three=table_compute_stage_two
table_compute_stage_three

# final
table_final = pd.merge(pd.merge(table_monthly_aggregated_stock_count_by_outlet_and_state
                                   , table_dim_competitor_universe
                                   , on=['state', 'competitor'], how='left')
    ,table_compute_stage_three , on=['report_month','state', 'outlet_name'], how='left')

table_final.loc[((table_final['flag_competitor_universe_for_exclusive_reporting']=='N')), 'group_competitor_exclusivity'] = 'Not in Competitor Universe'
table_final

Till the next time! Tschüss!

Not a fan of animal-based products but here’s a Hermes Birkin Python 30, considering it’s exclusivity. Image credits at the end.

References:
https://www.fast-world-leather.com/product-page/hermes-birkin-python-30
https://eelianx.medium.com/sql-competitor-exclusivity-analysis-314c54c8572a