app.py

##### Imports #####

import graphviz

import streamlit as st
import pandas as pd
# import numpy as np

# custom component
from st_keyup import st_keyup

##### Data Functions #####

# What does persist = disk do ?
# @st.cache_data(persist="disk")

### For competitor analysis

@st.cache_data
def load_pandas_xlsx(path):
	data = pd.read_excel(path)
	return data

@st.cache_data
def build_company_df(input_df):
	# build company df
	output_df = input_df[['companyLabel', 'companyLabelJA', 'company']].drop_duplicates()
	return output_df

### For industry analysis

@st.cache_data
def build_industry_df(input_df):
	# Pre compute unique number of companies per industry
	output_df = input_df[['company', 'companyLabel', 'companyLabelJA', 'industry', 'industryLabel', 'industryLabelJA']].drop_duplicates().groupby(['industry', 'industryLabel', 'industryLabelJA'])['company'].count().sort_values(ascending=False).reset_index().copy()
	output_df = output_df.rename(columns={'company': 'n_competitors'})
	return output_df

### For product analysis

@st.cache_data
def build_product_df(input_df):
	# Pre compute unique number of companies per product
	output_df = input_df[['company', 'companyLabel', 'companyLabelJA', 'product', 'productLabel', 'productLabelJA']].drop_duplicates().groupby(['product', 'productLabel', 'productLabelJA'])['company'].count().sort_values(ascending=False).reset_index().copy()
	output_df = output_df.rename(columns={'company': 'n_competitors'})
	return output_df

### For customer analysis

@st.cache_data
def build_company_product_kg(company_product_path, product_manufacturer_path):
    company_product_df = pd.read_csv(company_product_path)
    product_manufacturer_df = pd.read_csv(product_manufacturer_path)
    
    output_df = pd.concat([company_product_df, product_manufacturer_df])
    return output_df

@st.cache_data
def build_company_df_2(input_df):
    # build company df
    output_df = input_df[['companyLabel', 'companyLabelJA', 'company']].drop_duplicates()
    return output_df

property_mapping = {
    'http://www.wikidata.org/prop/direct/P186': 'made_from_material',
    'http://www.wikidata.org/prop/direct/P527': 'has_part',
    'http://www.wikidata.org/prop/direct/P2283': 'uses',
    'http://www.wikidata.org/prop/direct/P31': 'instance_of',
    'http://www.wikidata.org/prop/direct/P366': 'has_use',
    'http://www.wikidata.org/prop/direct/P361': 'part_of'
}

@st.cache_data
def build_product_kg_df():

    product_df_1 = pd.read_csv('data/product_manufacturer_relations_haspart_uses_madefrom_out.csv')
    product_df_2 = pd.read_csv('data/product_relations_haspart_uses_madefrom_out.csv')

    product_df_3 = pd.read_csv('data/product_relations_partof_hasuse_out.csv')
    product_df_4 = pd.read_csv('data/product_manufacturer_relations_hasuse_partof_out.csv')

    product_kg_df = pd.concat([product_df_1, product_df_2, product_df_3, product_df_4]).drop_duplicates()
    product_kg_df['propertyLabel'] = product_kg_df.propertyLabel.apply(lambda x: property_mapping[x])
    
    return product_kg_df

@st.cache_data
def build_product_instance_df():

    product_instance_df_1 = pd.read_csv('data/product_relations_instance_out.csv')
    product_instance_df_2 = pd.read_csv('data/product_manufacturer_relations_instance_out.csv')

    product_instance_df = pd.concat([product_instance_df_1, product_instance_df_2]).drop_duplicates()
    product_instance_df['propertyLabel'] = product_instance_df.propertyLabel.apply(lambda x: property_mapping[x])
    
    return product_instance_df

### For searching

def search_df(inp, df, col):
	mask = df[col].str.contains(inp, case=False, regex=False)
	select_df = df[mask]
	return select_df

##### Data Logic #####

# For competitor and industry analysis
COMPETITOR_PATH = 'data/merged_competitors_all_20241122.xlsx'
INDUSTRY_PATH = 'data/industry_hierarchy_20241125.xlsx'

# For customer analysis
COMPANY_PRODUCT_PATH = 'data/company_product_pairs.csv'
PRODUCT_MANUFACTURER_PATH = 'data/product_manufacturer_pair.csv'

# Load data
with st.spinner(text="Loading competitor data ..."):
	competitor_df = load_pandas_xlsx(COMPETITOR_PATH)
st.success("Competitor Data Loaded!")

# Load data
with st.spinner(text="Build company df ..."):
	company_df = build_company_df(competitor_df)
st.success("Company Data Loaded!")

industry_hierarchy = load_pandas_xlsx(INDUSTRY_PATH)

# industry_data = pd.read_excel('data/industry_hierarchy_20241125.xlsx')

### Pre computation Steps ### 

# Pre compute unique number of companies per industry
industry_to_counts = build_industry_df(competitor_df)

# Pre compute unique number of companies per industry
product_to_counts = build_product_df(competitor_df)

### end ###	

# Title
st.title('3C Competitor / Customer Analysis Demo')

option = st.selectbox(
    "Analysis Mode",
    ("Customer Analysis", "Competitor Analysis", "Industry Analysis", "Product Analysis")
)

st.write("You selected:", option)

##### App Logic #####

if option == "Competitor Analysis":

	st.title("Searching by Company")

	# Get input
	inp = st_keyup("Enter a company name", value="amazon", key="0", debounce=500)

	# Perform search
	select_df = search_df(inp, company_df, 'companyLabel')

	# def show_data():
	# 	select_value = st.session_state.value
	#	row_id=select_value['selection']['rows'][0]
	#	st.write(company_df.iloc[row_id])
		
	# Show search results
	with st.status("Searching ...", state="running", expanded=False) as status:
		status.update(label=f"{len(select_df)} results found", state="complete", expanded=True)

		### Selection for Company ###
		st.dataframe(select_df, on_select="rerun", key="value", selection_mode="single-row")

	# Expand if company is selected
	select_value = st.session_state.value
	if len(select_value['selection']['rows']) > 0:

		st.title("Company Data")

		row_id = select_value['selection']['rows'][0]
		
		row = select_df.iloc[row_id]
		
		entries = competitor_df[competitor_df.company == row.company]

		st.write(f"Company Name: {row.companyLabel}")
		st.write(f"Japanese Name: {row.companyLabelJA}")
		st.write(f"Wikidata URL: {row.company}")


		st.write(f"Cities where Offices Located In: {list(entries.headquartersLabel.unique())}")

		st.write(f"Countries Operated in: {list(entries.countryLabel.unique())}")

		st.write(f"Industries belongs to: {list(set(list(entries.industryLabel.unique()) + list(entries.industryLabelJA.unique())))}")

		st.write(f"Products or Services Provided: {list(set(list(entries.productLabel.unique()) + list(entries.productLabelJA.unique())))}")
		

		st.title("Competitive Analysis by Industry")

		# new: select from pre-computed list
		associated_industries = set(list(entries.industry.unique()))
		wikidata_industries = industry_to_counts[industry_to_counts.industry.apply(lambda x: x in associated_industries)]

		# old: direct computation
		# wikidata_industries = entries[['industry', 'industryLabel', 'industryLabelJA']].drop_duplicates().copy()

		### selection for industry ###
		st.dataframe(wikidata_industries, on_select="rerun", key="industry", selection_mode="single-row")
		select_industry = st.session_state.industry

		# expand if industry if selected
		if len(select_industry['selection']['rows']) > 0:

			industry_id = select_industry['selection']['rows'][0]
			
			industry = wikidata_industries.iloc[industry_id]

			st.title(f"All Competitors for {industry.industryLabel}")

			competitors = competitor_df[competitor_df.industry == industry.industry][['companyLabel', 'companyLabelJA', 'company', 'country', 'countryLabel']].drop_duplicates().copy()
			st.dataframe(competitors)
			# print("------")

			st.title("Analysis by country")

			competitors['countryLabel'] = competitors.countryLabel.fillna('undefined')
			competitors['country'] = competitors.country.fillna('undefined')

			# country_counts = competitors.groupby(['country', 'countryLabel'])['companyLabel'].count().sort_values(ascending=False).reset_index()[:50]

			country_counts = competitors.groupby(['countryLabel', 'country'])['companyLabel'].count().sort_values(ascending=False).reset_index()[:50]
			country_counts = country_counts.rename(columns={'countryLabel': 'countryName', 'country': 'countryWikidata', 'companyLabel': 'countryCounts'})


			### selection for country ###
			st.dataframe(country_counts, on_select="rerun", key="country", selection_mode="single-row")
			select_country = st.session_state.country

			# expand if country is selected
			if len(select_country['selection']['rows']) > 0:

				select_country_id = select_country['selection']['rows'][0]

				select_country = country_counts.iloc[select_country_id]

				competitors_by_country = competitors[competitors.country == select_country.countryWikidata][['companyLabel', 'companyLabelJA', 'company']].copy()

				st.title(f"Companies with industry {industry.industryLabel} in country {select_country.countryName}")
				st.dataframe(competitors_by_country)


elif option == "Industry Analysis":

	st.title("Searching by Industry")

	# Get input
	industry_input = st_keyup("Enter an industry name", value="retail", key="1", debounce=500)

	# Perform search
	industry_select_df = search_df(industry_input, industry_to_counts, 'industryLabel')

	# Show search results
	with st.status("Searching ...", state="running", expanded=False) as status:
		status.update(label=f"{len(industry_select_df)} results found", state="complete", expanded=True)

		### Selection for Industry ###
		st.dataframe(industry_select_df, on_select="rerun", key="industry", selection_mode="single-row")
		select_industry = st.session_state.industry
		
		# expand if industry if selected
		if len(select_industry['selection']['rows']) > 0:

			industry_id = select_industry['selection']['rows'][0]
			
			industry = industry_select_df.iloc[industry_id]

			st.title(f"All Competitors for {industry.industryLabel}")

			competitors = competitor_df[competitor_df.industry == industry.industry][['companyLabel', 'companyLabelJA', 'company', 'country', 'countryLabel']].drop_duplicates().copy()
			st.dataframe(competitors)

			st.title(f'Industry Superclasses of {industry.industryLabel}')

			superclasses = industry_hierarchy[industry_hierarchy.subject == industry.industry][['object', 'objectLabel', 'objectLabelJa']]

			st.dataframe(superclasses)

			st.title(f'Industry Subclasses of {industry.industryLabel}')

			subclasses = industry_hierarchy[industry_hierarchy.object == industry.industry][['subject', 'subjectLabel', 'subjectLabelJa']]

			st.dataframe(subclasses)

			# st.title(f'Hierarchy Graph')

			# Create a graphlib graph object
			# graph = graphviz.Digraph()

			# for sup in superclasses.itertuples():
			# 	graph.edge(sup.industryLabel, industry.industryLabel)

			# for sub in subclasses.itertuples():
			# 	graph.edge(industry.industryLabel, sub.industryLabel)

			# st.graphviz_chart(graph)

			st.title(f'Test Network Graph')

			# Create a graphlib graph object
			graph = graphviz.Digraph()

			graph.edge("run", "intr")
			graph.edge("intr", "runbl")
			graph.edge("runbl", "run")
			graph.edge("run", "kernel")
			graph.edge("kernel", "zombie")
			graph.edge("kernel", "sleep")
			graph.edge("kernel", "runmem")
			graph.edge("sleep", "swap")
			graph.edge("swap", "runswap")
			graph.edge("runswap", "new")
			graph.edge("runswap", "runmem")
			graph.edge("new", "runmem")
			graph.edge("sleep", "runmem")

			st.graphviz_chart(graph)


elif option == "Product Analysis":

	st.title("Searching by Product")

	# Get input
	product_input = st_keyup("Enter an product name", value="computer", key="2", debounce=500)

	# Perform search
	product_select_df = search_df(product_input, product_to_counts, 'productLabel')

	# Show search results
	with st.status("Searching ...", state="running", expanded=False) as status:
		status.update(label=f"{len(product_select_df)} results found", state="complete", expanded=True)

		### Selection for Product ###
		st.dataframe(product_select_df, on_select="rerun", key="product", selection_mode="single-row")
		select_product = st.session_state.product
		
		# expand if product if selected
		if len(select_product['selection']['rows']) > 0:

			product_id = select_product['selection']['rows'][0]
			
			product = product_select_df.iloc[product_id]

			st.title(f"All Competitors for {product.productLabel}")

			competitors = competitor_df[competitor_df['product'] == product['product']][['companyLabel', 'companyLabelJA', 'company', 'country', 'countryLabel']].drop_duplicates().copy()
			st.dataframe(competitors)

elif option == "Customer Analysis":

	# Load data
	with st.spinner(text="Build company product knowledge graph ..."):
		company_product_kg_df = build_company_product_kg(COMPANY_PRODUCT_PATH, PRODUCT_MANUFACTURER_PATH)
		company_df_2 = build_company_df_2(company_product_kg_df)
	st.success("Company Product Knowledge Graph Loaded!")

	with st.spinner(text="Build product relationship knowledge graph ..."):
		product_kg_df = build_product_kg_df()
	st.success("Product Relationship Knowledge Graph Loaded!")

	with st.spinner(text="Build product instance knowledge graph ..."):
		product_instance_df = build_product_instance_df()
	st.success("Product Instance Knowledge Graph Loaded!")

	### Search Start

	st.title("Searching by Company")

	# Get input
	inp = st_keyup("Enter a company name", value="toshiba", key="0", debounce=500)

	# Perform search
	select_df = search_df(inp, company_df_2, 'companyLabel')

	# def show_data():
	# 	select_value = st.session_state.value
	#	row_id=select_value['selection']['rows'][0]
	#	st.write(company_df.iloc[row_id])
		
	# Show search results
	with st.status("Searching ...", state="running", expanded=False) as status:
		status.update(label=f"{len(select_df)} results found", state="complete", expanded=True)

		### Selection for Company ###
		st.dataframe(select_df, on_select="rerun", key="value", selection_mode="single-row")


		# Expand if company is selected
		select_value = st.session_state.value
		if len(select_value['selection']['rows']) > 0:

			st.title("Company Data")

			row_id = select_value['selection']['rows'][0]
			
			row = select_df.iloc[row_id]
			
			entries = company_product_kg_df[company_product_kg_df.company == row.company]

			st.write(f"Company Name: {row.companyLabel}")
			st.write(f"Japanese Name: {row.companyLabelJA}")
			st.write(f"Wikidata URL: {row.company}")

			# st.write(f"Products or Services Provided: {list(set(list(entries.productLabel.unique()) + list(entries.productLabelJA.unique())))}")

			st.write(f"Products and services provided by {row.companyLabel}")

			product_select_df = company_product_kg_df[(company_product_kg_df.company == row.company) & (company_product_kg_df.propertyLabel == 'product_or_service_provided')][['productLabel', 'productLabelJA', 'product', 'company', 'companyLabel', 'companyLabelJA']]

			### Selection for Product ###
			st.dataframe(product_select_df, on_select="rerun", key="product", selection_mode="single-row")
			select_product = st.session_state.product


			# expand if product if selected
			if len(select_product['selection']['rows']) > 0:

				product_id = select_product['selection']['rows'][0]
				
				target_product = product_select_df.iloc[product_id]

				# st.title(f"All Product Categories produced by {row.companyLabel}")
				# st.dataframe(competitors)

				# Hypothesis
				# for incoming relations: 'uses' of 'has_part' is useful, since it lists services that have selected product as a component
				# for outgoing relations: 'has_use' and 'part_of' is useful, since it lists services that have selected product as a component


				####### Build kg paths

				### Step 1 ###

				start_df = pd.DataFrame()
				start_df[['company_start', 'companyLabel_start', 'companyLabelJA_start', 'product_start', 'productLabel_start', 'productLabelJA_start']] = [[target_product.company, target_product.companyLabel, target_product.companyLabelJA, target_product['product'], target_product.productLabel, target_product.productLabelJA]]

				### Step 2 ###

				related_out_df = product_kg_df[(product_kg_df['product'] == target_product['product']) & (product_kg_df['propertyLabel'].apply(lambda x: x in ['has_use', 'part_of']))]
				related_in_df = product_kg_df[(product_kg_df['object'] == target_product['product']) & (product_kg_df['propertyLabel'].apply(lambda x: x in ['uses', 'has_part']))]

				path_df = pd.concat(
					[
						start_df.merge(related_out_df[['product', 'object', 'objectLabel', 'objectLabelJa']], left_on='product_start', right_on='product').drop(columns=['product']).rename(columns={'object': 'product_second', 'objectLabel': 'productLabel_second', 'objectLabelJa': 'productLabelJa_second'}),
						start_df.merge(related_in_df[['object', 'product', 'productLabel', 'productLabelJa']], left_on='product_start', right_on='object').drop(columns=['object']).rename(columns={'product': 'product_second', 'productLabel': 'productLabel_second', 'productLabelJa': 'productLabelJa_second'}),
					]
				)

				# merge 1

				### Step 3a ###

				path_df_1 = path_df.merge(company_product_kg_df[['company', 'companyLabel', 'companyLabelJA', 'product']], left_on='product_second', right_on='product').drop(columns=['product'])

				### Step 3b ###

				path_df_2 = path_df.merge(product_instance_df[['object', 'objectLabel', 'objectLabelJa', 'product']], left_on='product_second', right_on='product').drop(columns=['product']).rename(columns={'object': 'product_third', 'objectLabel': 'productLabel_third', 'objectLabelJa': 'productLabelJa_third'})
				path_df_2 = path_df_2.merge(company_product_kg_df[['company', 'companyLabel', 'companyLabelJA', 'product']], left_on='product_third', right_on='product').drop(columns=['product'])

				### Step 3c ###

				path_df_3 = path_df.merge(product_instance_df[['object', 'objectLabel', 'objectLabelJa', 'product']], left_on='product_second', right_on='product').drop(columns=['product']).rename(columns={'object': 'product_third', 'objectLabel': 'productLabel_third', 'objectLabelJa': 'productLabelJa_third'})
				path_df_3 = path_df_3.merge(product_instance_df[['product', 'productLabel', 'productLabelJa', 'object']], left_on='product_third', right_on='object').drop(columns=['object']).rename(columns={'product': 'product_fourth', 'productLabel': 'productLabel_fourth', 'productLabelJa': 'productLabelJa_fourth'})
				path_df_3 = path_df_3.merge(company_product_kg_df[['company', 'companyLabel', 'companyLabelJA', 'product']], left_on='product_fourth', right_on='product').drop(columns=['product'])

				### Step 5 ###

				path_df_1['length'] = 4
				path_df_2['length'] = 5
				path_df_3['length'] = 6

				final_path_df = pd.concat([path_df_1, path_df_2, path_df_3])

				final_path_df = final_path_df.reset_index(drop=True)
				final_path_df['path_id'] = final_path_df.index
				#final_path_df = final_path_df.set_index('path_id', drop=False)

				final_company_df = final_path_df[['path_id', 'company', 'companyLabel', 'companyLabelJA']].copy()

				### Step 6 ###

				st.title(f"Potential Customers for {target_product.companyLabel} for product {target_product.productLabel}")

				# st.dataframe(final_company_df)

				st.dataframe(final_company_df, on_select="rerun", key="customer", selection_mode="single-row")
				select_customer = st.session_state.customer

				if len(select_customer['selection']['rows']) > 0:

					customer_id = select_customer['selection']['rows'][0]
					target_customer = final_company_df.iloc[customer_id]

					customer_df = final_path_df[final_path_df.path_id == target_customer.path_id].iloc[0]		

					# import graphviz

					# Create a graphlib graph object
					graph = graphviz.Digraph()

					graph.edge(customer_df.companyLabel_start, customer_df.productLabel_start, label='  produces')
					graph.edge(customer_df.productLabel_start, customer_df.productLabel_second, label='  part of')

					if customer_df.length == 4:
						graph.edge(customer_df.productLabel_second, customer_df.companyLabel, label= '  produced by')

					elif customer_df.length == 5:
						graph.edge(customer_df.productLabel_second, customer_df.productLabel_third, label='  instance of')
						graph.edge(customer_df.productLabel_third, customer_df.companyLabel, label= '  produced by')

					if customer_df.length == 6:
						graph.edge(customer_df.productLabel_second, customer_df.productLabel_third, label='  instance of')
						graph.edge(customer_df.productLabel_fourth, customer_df.productLabel_third, '  instance of')
						graph.edge(customer_df.productLabel_fourth, customer_df.companyLabel, label= '  produced by')

					st.graphviz_chart(graph)





else:

	st.write("no option selected")