<jupyter_start><jupyter_text>Still diff of two, will fix special character<jupyter_code>round2.company_permalink = round2.company_permalink.str.encode('latin-1').str.decode('ascii', 'ignore')
companies.permalink = companies.permalink.str.encode('latin-1').str.decode('ascii', 'ignore')
#unique companies in companies.csv
companies['permalink'].nunique()
#unique companies in rounds2.csv
round2['company_permalink'].nunique()<jupyter_output><empty_output><jupyter_text>Unique companies in both datasets's are 66368.<jupyter_code>#merging the two dataframes
master_frame = pd.merge(round2, companies, how = 'left', left_on = 'company_permalink', right_on = 'permalink')
master_frame.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 114949 entries, 0 to 114948
Data columns (total 16 columns):
 #   Column                   Non-Null Count   Dtype  
---  ------                   --------------   -----  
 0   company_permalink        114949 non-null  object 
 1   funding_round_permalink  114949 non-null  object 
 2   funding_round_type       114949 non-null  object 
 3   funding_round_code       31140 non-null   object 
 4   funded_at                114949 non-null  object 
 5   raised_amount_usd        94959 non-null   float64
 6   permalink                114949 non-null  object 
 7   name                     114948 non-null  object 
 8   homepage_url             108815 non-null  object 
 9   category_list            111539 non-null  object 
 10  status                   114949 non-null  object 
 11  country_code             106271 non-null  object 
 12  state_code               104003 non-null  object 
 13  region                   104782 non-null  object 
 14  city[...]<jupyter_text>Total observations are 114949.  Funding Type Analysis<jupyter_code>#count null values in master_frame
master_frame.isnull().sum()<jupyter_output><empty_output><jupyter_text>remove null rows from relevent columns<jupyter_code>master_frame = master_frame[~master_frame['raised_amount_usd'].isnull()]
master_frame.isnull().sum()
#Removing row with null values
master_frame = master_frame[~master_frame['name'].isnull()]
master_frame = master_frame[~master_frame['country_code'].isnull()]
master_frame = master_frame[~master_frame['category_list'].isnull()]
master_frame.isnull().sum()
master_frame.tail()
#removing rows with companies status as closed
master_frame = master_frame[~(master_frame['status'] == "closed")]
master_frame.tail()
master_frame.reset_index(inplace=True)
master_frame.head()
#Retaining the rows with required investment types
master_frame = master_frame[(master_frame['funding_round_type'] == 'venture') | (master_frame['funding_round_type'] == 'seed')
                            | (master_frame['funding_round_type'] == 'angel') | (master_frame['funding_round_type'] == 'private_equity')]
master_frame.head()
#average investment amount for each of the four funding types in millions
round(master_frame.groupby('funding_round_type').raised_amount_usd.mean()/1000000,2)<jupyter_output><empty_output><jupyter_text>Plot showing the fraction of total investments (globally) in angel, venture, seed, and private equity, and the average amount of investment in each funding type.<jupyter_code>#for plotting graph
plot1 = pd.DataFrame({'total': round(master_frame.groupby('funding_round_type').raised_amount_usd.sum(),2),
                   'average': round(master_frame.groupby('funding_round_type').raised_amount_usd.mean()/1000000,2)})

plot1.head()
#plot 1 showing the fraction of total investments (globally) in angel, venture, seed, and private equity, and the average amount of investment in each funding type. 
axes = plot1.plot.bar(rot=0, subplots=True, figsize=(20,12))
axes[1].legend(loc=2) 
fig1 = plt.gcf()
plt.show()
fig1.savefig('plotOne.png', dpi=100)<jupyter_output><empty_output><jupyter_text>Venture funding is good for Spark fund<jupyter_code>#removing other funding round types
master_frame = master_frame[master_frame['funding_round_type'] == 'venture']
master_frame.head()
#Total venture investment in all countries
master_frame['total_funding'] = master_frame['raised_amount_usd'].groupby(master_frame['country_code']).transform('sum')
master_frame.head()
#Top 9 countries
#sorting by decending order
fundsum = master_frame.sort_values(by=['total_funding'], ascending=False)
total_fund_each_country = fundsum.groupby('total_funding').head(1)
top9 = total_fund_each_country.head(9)
top9
#Plot two showing the top 9 countries against the total amount of investments of funding type Venture
top9.plot(kind='bar',x='country_code',y='total_funding', figsize=(16,8))
fig1 = plt.gcf()
plt.show()
fig1.savefig('plotTwo.png', dpi=100)<jupyter_output><empty_output><jupyter_text>Top 3 english speaking countries with highest total funding - USA, GBR, IND<jupyter_code>#retaining only required countries
master_frame = master_frame[(master_frame['country_code'] == 'USA') | (master_frame['country_code'] == 'GBR')
                                  | (master_frame['country_code'] == 'IND')]
master_frame.head()
#Extracting  primary sector from column_list
master_frame['category_list'] = master_frame['category_list'].apply(lambda x: x.split('|')[0])
master_frame.head()
#Reading mapping.csv file
mappings = pd.read_csv('mapping.csv')
mappings.head()
#mapping primary sector and main sector
mapping = pd.melt(mappings, id_vars =['category_list'], value_vars =['Automotive & Sports','Cleantech / Semiconductors','Entertainment',
                                                             'Health','Manufacturing','News, Search and Messaging','Others',
                                                             'Social, Finance, Analytics, Advertising']) 
mapping = mapping.rename(columns = {"variable":"main_sector"})
mapping = mapping[~(mapping.value == 0)]
mapping = mapping.drop('value', axis = 1)
mapping.head()
master_frame = master_frame.merge(mapping, how = 'left', on ='category_list')
master_frame.head()
#removing NaN values for main_sector
master_frame = master_frame[~(master_frame.main_sector.isnull())]
master_frame.head()<jupyter_output><empty_output><jupyter_text>Sector Analysis 2<jupyter_code>#Filtered on USA.
D1 = master_frame[(master_frame['country_code'] == 'USA') & (master_frame['raised_amount_usd'] >= 5000000) & 
                 (master_frame['raised_amount_usd'] <= 15000000)]
D1_group = D1[['raised_amount_usd','main_sector']].groupby('main_sector').agg(['sum', 'count']).rename(
    columns={'sum':'total_raised','count' : 'total_count'})
D1 = D1.merge(D1_group, how='left', on ='main_sector')
D1.head()
#Filtered by GBR
D2 = master_frame[(master_frame['country_code'] == 'GBR') & (master_frame['raised_amount_usd'] >= 500000) & 
             (master_frame['raised_amount_usd'] <= 15000000)]
D2_group = D2[['raised_amount_usd','main_sector']].groupby('main_sector').agg(['sum', 'count']).rename(
    columns={'sum':'total_raised','count' : 'total_count'})
D2 = D2.merge(D2_group, how='left', on ='main_sector')
D2.head()
#Filtered by IND
D3 = master_frame[(master_frame['country_code'] == 'IND') & (master_frame['raised_amount_usd'] >= 500000) & 
             (master_frame['raised_amount_usd'] <= 15000000)]
D3_group = D3[['raised_amount_usd','main_sector']].groupby('main_sector').agg(['sum', 'count']).rename(
    columns={'sum':'total_raised','count' : 'total_count'})
D3 = D3.merge(D3_group, how='left', on ='main_sector')
D3.head()
#Count of investment in D1
D1['raised_amount_usd'].count()
#Count of investment in D2, D3
print(D2['raised_amount_usd'].count())
print(D3['raised_amount_usd'].count())
#Total amount of investment in D1,D2,D3
print(D1['raised_amount_usd'].sum())
print(D2['raised_amount_usd'].sum())
print(D3['raised_amount_usd'].sum())
#top sectors and count of investments in D1
D1_group
#top sectors and count of investments in D2
D2_group
#top sectors and count of investments in D3
D3_group
#D1, company with highest investment in Others sector
comp = D1[D1['main_sector']=='Others']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())
#D1, company with highest investment in 2nd best sector
comp = D1[D1['main_sector']=='Cleantech / Semiconductors']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())
#D2, company with highest investment in Others sector
comp = D2[D2['main_sector']=='Others']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())
#D2, company with highest investment in 2nd Best sector
comp = D2[D2['main_sector']=='Cleantech / Semiconductors']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())
#D3, company with highest investment in Others sector
comp = D3[D3['main_sector']=='Others']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())
#D3, company with highest investment in 2nd best sector
comp = D3[D3['main_sector']=='News, Search and Messaging']
company = comp.pivot_table(values = 'raised_amount_usd', index = 'name', aggfunc = 'sum')
company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()
print(company.head())<jupyter_output>raised_amount_usd
name                          
GupShup             33000000.0
IMImobile           26000000.0
SMS GupShup         22000000.0
Roadrunnr           21000000.0
hike                21000000.0
{'fraction_non_alphanumeric': 0.13558808682483595, 'fraction_numerical': 0.034326097930338216, 'mean_word_length': 5.018226002430134, 'pattern_counts': {'":': 1, '<': 35, '<?xml version=': 0, '>': 35, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '124497', 'license': 'MIT', 'n_tokens_mistral': 3456, 'n_tokens_neox': 3150, 'n_words': 692, 'path': 'final_assignment.ipynb', 'repo_name': 'Chiragchhillar1/ML-SparkFund', 'size': '9905'}