<jupyter_start><jupyter_text>SPARKS FUND DATA ANALYSIS  Objective : You work for Spark Funds, an asset management company. Spark Funds wants to makeinvestments in a few companies. The CEO of Spark Funds wants to understand the global trendsin investments so that she can take the investment decisions effectively.  Business and Data UnderstandingSpark Funds has two minor constraints for investments:  1. It wants to invest between **5 to 15 million USD** per round of investment.  2. It wants to invest only in **English-speaking countries** because of the ease of communication with the companies it would invest in.  * For the analysis, consider a country to be English speaking only if **English** is one of the **official languages** in that country.  Business objective: **The objective is to identify the best sectors, countries, and a suitable investment type for making investments. The overall strategy is to invest where others are investing, implying that the 'best' sectors and countries are the ones 'where most investors are investing'.**<jupyter_code>#import numpy and pandas packages
import numpy as np
import pandas as pd
import warnings  #hides warnings
warnings.filterwarnings('ignore')
#import matplotlib packages
import matplotlib.pyplot as plt
#import seaborn packages
import seaborn as sns<jupyter_output><empty_output><jupyter_text>DATA CLEANING<jupyter_code>#Reading rounds2 csv file
rounds2 = pd.read_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\rounds2.csv",
                encoding = 'ISO-8859-1')
#showing top 5 rows
rounds2.head()
#removing junk characters from the string
rounds2['company_permalink'] = rounds2['company_permalink'].apply(lambda x:x.encode('utf-8').decode('ascii','ignore'))
#dropping columns that doesn't hold meaning for analysis
rounds2.drop(['funding_round_code','funding_round_permalink','funded_at'],axis = 1,inplace = True)
#splitting company name
rounds2['company_permalink'] = rounds2['company_permalink'].apply(lambda x:x.split('/')[2])
#equally casing company name
rounds2['company_permalink'] = rounds2['company_permalink'].str.lower()
#saving file for future purpose
rounds2.to_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\cleaned_rounds2.csv")
rounds2.head()
#reading companies csv file
companies = pd.read_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\companies.txt",
                delimiter = '\t',encoding = 'ISO-8859-1')
#showing top 5 rows
companies.head()
#removing junk characters from the string
companies['permalink'] = companies['permalink'].apply(lambda x:x.encode('utf-8').decode('ascii','ignore'))
#dropping columns that doesn't hold meaning for analysis
companies.drop(['name','homepage_url','state_code','region','city','founded_at'],axis = 1,inplace = True)
#splitting company name
companies['permalink'] = companies['permalink'].apply(lambda x:x.split('/')[2])
#equally casing company name
companies['permalink'] = companies['permalink'].str.lower()
#saving file for future purpose
companies.to_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\cleaned_companies_data.csv")
companies.head()<jupyter_output><empty_output><jupyter_text>DATA UNDERSTANDING  Q) How many unique companies are present in rounds2?<jupyter_code>print("Their are",rounds2['company_permalink'].nunique(),"unique values in rounds2 dataset.")<jupyter_output>Their are 66368 unique values in rounds2 dataset.<jupyter_text>Q) How many unique companies are present in the companies file?<jupyter_code>print("Their are",companies['permalink'].nunique(),"unique values in companies dataset.")<jupyter_output>Their are 66368 unique values in companies dataset.<jupyter_text>Q) Are there any companies in the rounds2 file which are not  present in companies ? Answer Y/N.<jupyter_code>#converting dataframe into set
seta  = set(companies['permalink'].unique())
setb  = set(rounds2['company_permalink'].unique())
seta.issubset(setb)<jupyter_output><empty_output><jupyter_text>Q) Merge the two data frames so that all  variables (columns)  in the companies frame are added to the rounds2 data frame. Name the merged frame master_frame. How many observations are present in master_frame ?<jupyter_code>#merging both dataframes
master_frame = rounds2.merge(companies,how = 'inner',right_on = 'permalink',left_on = 'company_permalink')
#showing top 5
master_frame.head()
#looking for number of rows in master dataframe
master_frame.shape
#storing those rows which have value
master_frame = master_frame[~(master_frame['raised_amount_usd'].isnull() | master_frame['country_code'].isnull() |
                             master_frame['category_list'].isnull())]
master_frame.head()
#looking for NaN value
master_frame.isna().sum()
#Converting $ to million $.
master_frame['raised_amount_usd'] = master_frame['raised_amount_usd']/1000000
#showing top 5
master_frame.head()<jupyter_output><empty_output><jupyter_text>Funding Type AnalysisSpark Funds wants to choose one of these four investment types **(venture,angel,seed and private equity)** for each potentialinvestment they will make.<jupyter_code>#looking for unique values
master_frame['funding_round_type'].unique()
#looking for the count of unique value 
master_frame['funding_round_type'].value_counts()
#retaining the rows with only specified funding type
master_frame = master_frame[master_frame['funding_round_type'].isin(['venture','seed','angel','private_equity'])]
plt.figure(figsize=(8,7))
sns.countplot(x = 'funding_round_type',data = master_frame,hue = 'funding_round_type',dodge=False)
#dropping extra column
master_frame = master_frame.drop('permalink',axis = 1)
#showing top 5
master_frame.head()<jupyter_output><empty_output><jupyter_text>Q) Average funding amount of venture type<jupyter_code>#extracting venture type investment dataframe
venture_df = master_frame[master_frame['funding_round_type'].isin(['venture'])]
venture_df.head()
#venture average
venture_mean = round(venture_df['raised_amount_usd'].mean(),2)
venture_mean<jupyter_output><empty_output><jupyter_text>Q)Average funding amount of angel type<jupyter_code>#extracting angel investment type dataframe
angel_df = master_frame[master_frame['funding_round_type'].isin(['angel'])]
angel_df.head()
#angel mean
angel_mean = round(angel_df['raised_amount_usd'].mean(),2)
angel_mean<jupyter_output><empty_output><jupyter_text>Q) Average funding amount of seed type<jupyter_code>#extracting seed investment type dataframe
seed_df = master_frame[master_frame['funding_round_type'].isin(['seed'])]
seed_df.head()
#seed mean
seed_mean = round(seed_df['raised_amount_usd'].mean(),2)
seed_mean<jupyter_output><empty_output><jupyter_text>Q) Average funding amount of private equity type<jupyter_code>#private equity investment type dataframe
equity_df = master_frame[master_frame['funding_round_type'].isin(['private_equity'])]
equity_df.head()
#private equity mean
equity_mean = round(equity_df['raised_amount_usd'].mean(),2)
equity_mean<jupyter_output><empty_output><jupyter_text>Q) Considering that Spark Funds wants to invest between 5 to 15 million USD per  investment round, which investment type is the most suitable for them?<jupyter_code>#creating a dictionary of means of specified investment type
mean_list = {'venture' : venture_mean,'seed' : seed_mean,'angel' : angel_mean,'equity' : equity_mean}
#looking for the investment type which is in between 5 million to 15 million $
investment_type = {mean for mean in mean_list if mean_list[mean] > 5 and mean_list[mean] < 15}
investment_type
visual_df = master_frame.pivot_table(values = 'raised_amount_usd',index = 'funding_round_type',
                                    aggfunc="mean").reset_index()
plt.figure(figsize = (8,5))
sns.barplot(data = visual_df,x = 'funding_round_type', y = 'raised_amount_usd')
plt.yscale('log')<jupyter_output><empty_output><jupyter_text>COUNTRY ANALYSISSpark Funds wants to invest in countries with the highest amount of funding for the chosen investment type. This is a part of its broader strategy to invest where most investments are occurring.* Spark Funds wants to see the top nine countries which have received the highest total funding (across ALL sectors for the chosen investment type)* For the chosen investment type, make a data frame named top9 with the top nine countries (based on the total investment amount each country has received)<jupyter_code>#web scrapping for knowing the primary language of country
language_df = pd.read_html("https://en.wikipedia.org/wiki/List_of_territorial_entities_where_English_is_an_official_language")
table1 = language_df[0]
table1
table2 = language_df[1]
table2.head()
#appending both tables to each other
language_master_df = table1.append(table2)
#dropping extra column (Nr)
language_master_df = language_master_df.drop('No.',axis = 1)
#reseting index
language_master_df.reset_index(drop = True).head()
#extracting necessary columns
language_master_df = language_master_df[['Alpha-3 code','Primary language?']]
language_master_df.head()
#splitting primary column behalf on space
language_master_df['Primary language?']  = language_master_df['Primary language?'].apply(lambda x:x.split(' ')[0])
language_master_df.head()
#renaming columns
language_master_df = language_master_df.rename(columns={'Alpha-3 code':'country_code','Primary language?':'English Langauge'})
language_master_df.head()
#merging scrapped dataframe with venture type investment dataframe
master_venture_df = venture_df.merge(language_master_df,on = 'country_code')
master_venture_df.head()
#saving dataframe to csv for future purpose
master_venture_df.to_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\Countries With their primary language as English.csv")<jupyter_output><empty_output><jupyter_text>Q) Top English speaking country<jupyter_code>#creating a pivot table to represent data,sorted behalf on raised amount usd
top9 = master_venture_df.pivot_table(values = 'raised_amount_usd',index = 'country_code',
                                     aggfunc = 'sum').sort_values(by = 'raised_amount_usd',ascending = False)
top9 = top9.reset_index()
top9.head(9)
plt.figure(figsize=(15,6))
plt.yscale('log')
sns.barplot(data = top9.head(9), x = 'country_code', y = 'raised_amount_usd' )<jupyter_output><empty_output><jupyter_text>SECTOR ANALYSIS 1The __business rule__ says that the __first string__ before the vertical bar will be __considered the primary sector__.Extract the primary sector of each category list from the category_list column.<jupyter_code>#retaining rows of top 3 english speaking countries
master_venture_df = master_venture_df[(master_venture_df['country_code'] == 'USA')|
                                     (master_venture_df['country_code'] == 'GBR')|
                                     (master_venture_df['country_code'] == 'IND')]
master_venture_df.head()
#extracting first string from category list by splitting behalf on this '|' character
master_venture_df['category_list'] = master_venture_df['category_list'].apply(lambda x:x.split('|')[0])
master_venture_df.head()
#reading mapping csv file
mapping = pd.read_csv(r"D:\Industrail Project\Spark Funds Industrial IP-1\mapping.csv")
#removing 1 NaN value of mapping csv file
mapping[1:]
#reshaping the mapping dataframe into a new dataframe using melt() method
mapping_melt_frame = pd.melt(mapping , id_vars = ['category_list'] , var_name='main_sector')
mapping_melt_frame = mapping_melt_frame[mapping_melt_frame['value'] == 1]
mapping_melt_frame
#merging the venture dataframe to melted mapping dataframe behlaf on category list
master_venture_df = master_venture_df.merge(mapping_melt_frame,on = 'category_list')
#dropping unnecessary columns
master_venture_df = master_venture_df.drop(['English Langauge','value'],axis = 1)
master_venture_df.head()<jupyter_output><empty_output><jupyter_text>Sector Analysis 21. Create three separate data frames D1, D2 and D3 for each of the three countries containing the observations of funding type FT falling within the 5-15 million USD range. The three data frames should contain: - All the columns of the master_frame along with the primary sector and the main sector - The total number (or count) of investments for each main sector in a separate column - The total amount invested in each main sector in a separate column<jupyter_code>#creating d1 dataframe for USA 
d1 = master_venture_df[(master_venture_df['country_code']==top9['country_code'][0]) & (master_venture_df['raised_amount_usd']>=5.0) & (master_venture_df['raised_amount_usd']<=15.0) ]
#creating d2 dataframe for GBR
d2 = master_venture_df[(master_venture_df['country_code']==top9['country_code'][1]) & (master_venture_df['raised_amount_usd']>=5.0) & (master_venture_df['raised_amount_usd']<=15.0) ]
#creating d3 dataframe for IND
d3 = master_venture_df[(master_venture_df['country_code']==top9['country_code'][2]) & (master_venture_df['raised_amount_usd']>=5.0) & (master_venture_df['raised_amount_usd']<=15.0) ]<jupyter_output><empty_output><jupyter_text>Total number of investments (count)<jupyter_code>plt.figure(figsize=(8,5))
sns.countplot(data = d1,y = 'main_sector')
#for USA
d1['main_sector'].value_counts()[0]
#for GBR
d2['main_sector'].value_counts()[0]
plt.figure(figsize=(8,5))
sns.countplot(data = d2,y = 'main_sector')
#for IND
d3['main_sector'].value_counts()[0]
plt.figure(figsize=(8,5))
sns.countplot(data = d3,y = 'main_sector')<jupyter_output><empty_output><jupyter_text>Total amount of investment (USD)<jupyter_code>#for USA
round(d1['raised_amount_usd'].sum(),2)
#for GBR
round(d2['raised_amount_usd'].sum(),2)
#for IND
round(d3['raised_amount_usd'].sum(),2)<jupyter_output><empty_output><jupyter_text>Top sector (based on count of investments)<jupyter_code>#for USA
d1['main_sector'].value_counts()
#for GBR
d2['main_sector'].value_counts()
#for IND
d3['main_sector'].value_counts()<jupyter_output><empty_output><jupyter_text>Top second sector<jupyter_code>#for USA
d1['main_sector'].value_counts().index[1]
#for GBR
d2['main_sector'].value_counts().index[1]
#for IND
d3['main_sector'].value_counts().index[1]<jupyter_output><empty_output><jupyter_text>Top third sector<jupyter_code>#for USA
d1['main_sector'].value_counts().index[2]
#for GBR
d2['main_sector'].value_counts().index[2]
#for IND
d3['main_sector'].value_counts().index[2]<jupyter_output><empty_output><jupyter_text>Number of investments in top sector<jupyter_code>#for USA
d1.groupby(by = 'main_sector')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)
#for GBR
d2.groupby(by = 'main_sector')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)

#for IND
d3.groupby(by = 'main_sector')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)<jupyter_output><empty_output><jupyter_text>For the top sector count-wise (point 3),which company received the highest<jupyter_code>#for USA
d1 = d1[d1['main_sector'] == 'Others']
d1.groupby(by = 'company_permalink')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)
#for GBR
d2 = d2[d2['main_sector'] == 'Others']
d2.groupby(by = 'company_permalink')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)
#for IND
d3 = d3[d3['main_sector'] == 'Others']
d3.groupby(by = 'company_permalink')['raised_amount_usd'].sum().sort_values(ascending = False).head(1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11524382272192443, 'fraction_numerical': 0.01385802065629494, 'mean_word_length': 7.36468015308912, 'pattern_counts': {'":': 0, '<': 95, '<?xml version=': 0, '>': 95, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '414998', 'license': 'Apache-2.0', 'n_tokens_mistral': 5087, 'n_tokens_neox': 4836, 'n_words': 1231, 'path': 'Sparks Funds Investment Data Analysis.ipynb', 'repo_name': 'ayush-1306/Sparks-Funds-Investment-Data-Analysis', 'size': '15298'}