<jupyter_start><jupyter_text>CrunchBase exploratory analysisThis notebook explores CrunchBase data to analyse the distribution of AI companies by sector. It was initially created for our collaborative MSc supervision with the Bank of England but is also used in our analysis of UK creative industries**Tasks**1. Load data from Nesta DAPS2. Identify a suitable sectoral category3. Flag AI companies  Preamble<jupyter_code>#Basic imports
import pandas as pd
import numpy as np
from data_getters.core import get_engine
from data_getters.inspector import get_schemas
from random import sample


import matplotlib.pyplot as plt

today_str = str(datetime.date.today())
#We create this type to deal with some Nones later
NoneType = type(None)<jupyter_output><empty_output><jupyter_text>Load data<jupyter_code>#Connection to the database

my_config_here ="../mysqldb_team.config"

con = get_engine(my_config_here)
#Read organisations
comps_chunks = pd.read_sql_table('crunchbase_organizations', con, chunksize=1000)

#Read categories
cats_chunks = pd.read_sql_table('crunchbase_organizations_categories', con, chunksize=1000)

#Concatenate the chunks into dfs
comps, cats = [pd.concat(x).reset_index(drop=True) for x in [comps_chunks,cats_chunks]]
#descr_short, descr_long = [comps[v].apply(lambda x: x.lower() if type(x)==str else np.nan) for v in ['short_description','long_description']]
print(len(comps))

print(len(cats))

print(len(set(cats['organization_id'])))<jupyter_output><empty_output><jupyter_text>There are around 600,000K organisations without categories<jupyter_code>comps.head()
cats.head()<jupyter_output><empty_output><jupyter_text>Slight data processing<jupyter_code>#Merge organisations and categories

#We reset the index because you can't merge series
cats_grouped = cats.groupby('organization_id')['category_name'].apply(lambda x: list(x)).reset_index(drop=False)

#This gives us a dataframe with a new field with the list of categories for the organisation
comps_cats = pd.merge(comps,cats_grouped,left_on='id',right_on='organization_id')
comps_cats.shape<jupyter_output><empty_output><jupyter_text>This is as expected given the length of dfs above (there seems to be a small number of organisations in the `cat` df not included in the `comps` df but we can worry about that a bit later.<jupyter_code>#Subset to focus on company entities.
#There are some missing values in roles, which we would also drop (Note the False in the control flow)

comps_cats = comps_cats.loc[['company' in x if type(x)!=NoneType else False for x in comps_cats['roles']]]

comps_cats.shape<jupyter_output><empty_output><jupyter_text>Identify 'AI' companies<jupyter_code>#Find AI companies based on text description or the AI category

#These are the terms we use for now. TODO: expand these using semantic similarities
ai_terms = ['data science','machine learning', 'deep learning','artificial intelligence','neural network', ' ai ','natural language processing','text mining']

#Lowercase the text
comps_cats['long_description'] = comps_cats['long_description'].apply(lambda x: x.lower() if type(x)!=NoneType else np.nan)

#Count the number of times that a company mentions AI 
comps_cats['ai_text_n'] = [sum([term in x for term in ai_terms]) if pd.isnull(x)==False else np.nan for x in comps_cats['long_description']]
comps_cats['ai_text_n'].value_counts()<jupyter_output><empty_output><jupyter_text>Most companies that mention AI do this once. Others mention it more often<jupyter_code>#Check a few companies with more than 3 AI mentions to see what they do
for x in sample(list(comps_cats.loc[comps_cats['ai_text_n']>3]['long_description']),5):
    print(x)
    print('\n')
#Now we check AI in categories. Note there might be other relevant categories in the data but we will not do this for now
comps_cats['ai_cats'] = ['artificial intelligence' in c for c in comps_cats['category_name']]

comps_cats['ai_cats'].sum()
#What is the overlap between companies with AI categories and AI relateed text in the description?
comp_cats_frequences = pd.crosstab(comps_cats['ai_text_n'],comps_cats['ai_cats'])

#What is the distribution of companies that mention AI various times over the share of companies with AI in their category?
comp_cats_frequences['text_share'] = 100*comp_cats_frequences[True]/comp_cats_frequences.sum(axis=1)

comp_cats_frequences<jupyter_output><empty_output><jupyter_text>Around a third of companies with AI in their categories don't mention AI related terms in their descriptions.There are quite a few companies that mention AI repeatedly but don't have an AI category.<jupyter_code># Quick check of company descriptions for companies that have AI cats but no AI Terms

#Check a few companies with more than 3 AI mentions to see what they do
for x in sample(list(comps_cats.loc[(comps_cats['ai_text_n']==0)&(comps_cats['ai_cats']==True)]['long_description']),5):
    print(x)
    print('\n')<jupyter_output><empty_output><jupyter_text>The companies that have ai categories but no ai related terms in their description look quite noisy. Let's exclude them from the analysis for now<jupyter_code>#Flag as AI companies with at least one AI term in their description. Later we could change this threshold
comps_cats['ai_flag'] = comps_cats['ai_text_n']>0<jupyter_output><empty_output><jupyter_text>A couple of descriptive analyses  Evolution<jupyter_code>#We need to create a year variable (founded on is currently a date)
 
comps_cats['founded_year'] = [x.year if type(x)!=NoneType else np.nan for x in comps_cats['founded_on']]
fig,ax = plt.subplots()

(100*pd.crosstab(comps_cats['founded_year'],comps_cats['ai_flag'],normalize=1)).plot(ax=ax,title='Year share of activity')

ax.set_xlim(2000,2018)<jupyter_output><empty_output><jupyter_text>Very interesting: explosion of AI startup activity while startup activity in general slows-up. What else could be explaining this? China's entry?  Geography<jupyter_code>#Calculate country distribution
country_distr = pd.crosstab(comps_cats['country'],comps_cats['ai_flag']).sort_values(True,ascending=False)

country_distr[:20]<jupyter_output><empty_output><jupyter_text>Some of the coverage issues are apparent - relatively limited activity in Japan. And where is China?<jupyter_code>#Calculate index of comparative advantage
country_distr['ai_rca']= (country_distr[True]/country_distr[True].sum())/(country_distr.sum(axis=1)/country_distr.sum(axis=1).sum())
#Plot RCAs for top 20 countries by level of activity
(country_distr[:20]['ai_rca'].sort_values(ascending=False)-1).plot.bar(title='Relative specialisation in AI for top 20 countries')<jupyter_output><empty_output><jupyter_text>Some results are expected (Israel, Singapore). Others (Canada), not so much.<jupyter_code>#That gnarly pivot gives us the number of ai companies per year and country.
ai_country_counts = pd.pivot_table(comps_cats.groupby(['founded_year','country'])['ai_flag'].sum().reset_index(drop=False),index='country',columns='founded_year',values='ai_flag').fillna(0)

#We want to focus our visualisation on the top 10 countries by overall activity
bigger_countries = ai_country_counts.sum(axis=1).sort_values(ascending=False).index[:15]

#Consider share of activity in a given year

ai_country_shares = ai_country_counts.apply(lambda x: x/x.sum(),axis=1).fillna(0)
fig,ax = plt.subplots()

ai_country_shares.loc[bigger_countries].T.rolling(window=3).mean().plot(ax=ax,title='Share of year in Country',figsize=(10,5),cmap='tab20',linewidth=2)

ax.set_xlim(2000,2018)
ax.legend()<jupyter_output><empty_output><jupyter_text>Everyone seems to be following a similar patterns perhaps with the exception of Singapore and Switzerland, which seem to be growing faster  Consider country sizes (TODO)  Cluster sectors<jupyter_code>from itertools import combinations, product, chain
import networkx as nx
import community

def flatten_list(a_list):
    '''
    Flattens a list
    '''
    
    return([x for el in a_list for x in el])
#Here the idea is to create a proximity matrix based on co-occurrences

#Turn co-occurrences into combinations of pairs we can use to construct a similarity matrix
sector_combs = flatten_list([sorted(list(combinations(x,2))) for x in comps_cats['category_name']])
sector_combs = [x for x in sector_combs if len(x)>0]

#Turn the sector combs into an edgelist
edge_list = pd.DataFrame(sector_combs,columns=['source','target'])

edge_list['weight']=1

#Group over edge pairs to aggregate weights
edge_list_weighted = edge_list.groupby(['source','target'])['weight'].sum().reset_index(drop=False)

edge_list_weighted.sort_values('weight',ascending=False).head(n=10)
#Create network and extract communities
net = nx.from_pandas_edgelist(edge_list_weighted,edge_attr=True)

#We choose a high level of resulution (lower == more finely grained)
comms = community.best_partition(net,resolution=0.3)
#What does this look like?
comm_strings = pd.DataFrame(comms,index=['comm']).T.groupby('comm')

#This is just to visualise the participation in communities
for n,x in enumerate(comm_strings.groups.keys()):
    print(n)
    print('====')
    print('\t'.join(list(comm_strings.groups[x])))
    #print(', '.join(list(x.index())))
#Create sector lookup

sector_labels = ['industrial','ads','aerospace','food_agriculture','recruitment',
'data_analytics','apps','finance','content','construction_real_state',
'non_profit','immersive','transport',
'b2b','energy','retail_fashion','security','health','marketing','innovation','education',
'ict','marketplace_sharing_economy','telecommunications','computing','legal_professional',
'consumer_services','software_development','sales','sports_games','events','travelling','information',
'internet','smart_sensors','search','social_networks','adult']

#They are in reverse order because I labelled them from below
industry_lookup = {n:y for n,y in enumerate(sector_labels[::-1])}
# Note that these categories may be too aggregate - eg sports games contain both video games (creative) and sports (non creative).

# One way to deal with this is by increasing the granularity of the community detection.
#Lookup every category
comps_cats['sector_list']= [[industry_lookup[comms[lab]] for lab in cats] for cats in comps_cats['category_name']]
comps_cats['sector_list'].head(n=20)<jupyter_output><empty_output><jupyter_text>There is a lot of overlap between categories - hard to delineate companies into a single category. For now we will perform an analysis that considers all sectors for a company<jupyter_code>ai_counts = []

for sector in set(industry_lookup.values()):
    
    #Subset the df to find all companies that mention sector
    has_sector = comps_cats.loc[[sector in indust for indust in comps_cats['sector_list']]]
    
    #How many are there?
    sector_tot = len(has_sector)
    
    #How many companies mentioning AI in the category?
    ai_tot = has_sector['ai_flag'].sum()
    
    #Create a series
    out = pd.Series([sector_tot,ai_tot],name=sector)
    
    ai_counts.append(out)
    
#Create df and label
ai_sector_df = pd.concat(ai_counts,axis=1).T
ai_sector_df.columns = ['total','ai']

#Create a 'share' variable showing the proportion of ai companies in a vertical
ai_sector_df['shares'] = 100*(ai_sector_df['ai']/ai_sector_df['total'])
ax = ai_sector_df.sort_values('shares',ascending=False)['shares'].plot.bar(figsize=(9,5),color='coral',edgecolor='black')
ax.set_ylabel('AI companies as \n % of total',size=14)

ax.xaxis.set_tick_params(labelsize=14)
ax.set_title('AI representation in CrunchBase verticals',size=14)

plt.tight_layout()

plt.savefig('/Users/jmateosgarcia/Desktop/company_focus.pdf')<jupyter_output><empty_output><jupyter_text>How many AI companies in creative sectors in the UK?<jupyter_code>creative_sector = ['ads','immersive','marketing','content',]
uk_finance = comps_cats.loc[(comps_cats['country']=='United Kingdom')&(['finance' in x for x in comps_cats['sector_list']])]
print(len(uk_finance)) 
print(uk_finance['ai_flag'].sum())<jupyter_output><empty_output><jupyter_text>Final tidy up<jupyter_code>vars_to_drop = ['permalink','domain','email','phone','facebook_url','linkedin_url','logo_url','is_health','mesh_terms','organization',
               'parent_id','organization_id']
uk_comps = comps_cats.loc[(comps_cats['country']=='United Kingdom'),[x for x in comps_cats.columns if x not in vars_to_drop]]
uk_comps.to_csv(f'{today_str}_cb_uk.csv',compression='gzip')<jupyter_output><empty_output><jupyter_text>Create data dictionary<jupyter_code>print('|name|type|observations|')
print('|----|----|----|')

for c in uk_comps.columns:
    
    print(f'|{c}|{type(uk_comps[c].iloc[0])}|   |')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11655085281111813, 'fraction_numerical': 0.00773847125710676, 'mean_word_length': 7.234720416124837, 'pattern_counts': {'":': 0, '<': 73, '<?xml version=': 0, '>': 76, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '266137', 'license': 'MIT', 'n_tokens_mistral': 4222, 'n_tokens_neox': 3991, 'n_words': 1112, 'path': 'notebooks/04_crunchbase-Copy1.ipynb', 'repo_name': 'nestauk/ai_ci', 'size': '12664'}