import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

df = pd.read_csv("master_df.csv", sep=",", encoding="ISO-8859-1")
df.head()

# first, let's filter the df so it only contains the four specified funding types
df = df[(df.funding_round_type == "venture") | 
        (df.funding_round_type == "angel") | 
        (df.funding_round_type == "seed") | 
        (df.funding_round_type == "private_equity") ]



"""
Explanation: Part 3: Analysis
In this section, we'll conduct the three types of analyses - funding type, country analysis, and sector analysis.
Funding Type Analysis
Let's compare the funding amounts across the funding types. Also, we need to impose the constraint that the investment amount should be between 5 and 15 million USD. We will choose the funding type such that the average investment amount falls in this range.
End of explanation
"""


# distribution of raised_amount_usd
sns.boxplot(y=df['raised_amount_usd'])
plt.yscale('log')
plt.show()

"""
Explanation: Now, we have to compute a representative value of the funding amount for each type of invesstment. We can either choose the mean or the median - let's have a look at the distribution of raised_amount_usd to get a sense of the distribution of data.
End of explanation
"""


# summary metrics
df['raised_amount_usd'].describe()

"""
Explanation: Let's also look at the summary metrics.
End of explanation
"""


# comparing summary stats across four categories
sns.boxplot(x='funding_round_type', y='raised_amount_usd', data=df)
plt.yscale('log')
plt.show()

# compare the mean and median values across categories
df.pivot_table(values='raised_amount_usd', columns='funding_round_type', aggfunc=[np.median, np.mean])

"""
Explanation: Note that there's a significant difference between the mean and the median - USD 9.5m and USD 2m. Let's also compare the summary stats across the four categories.
End of explanation
"""


# compare the median investment amount across the types
df.groupby('funding_round_type')['raised_amount_usd'].median().sort_values(ascending=False)

"""
Explanation: Note that there's a large difference between the mean and the median values for all four types. For type venture, for e.g. the median is about 20m while the mean is about 70m. 
Thus, the choice of the summary statistic will drastically affect the decision (of the investment type). Let's choose median, since there are quite a few extreme values pulling the mean up towards them - but they are not the most 'representative' values.
End of explanation
"""


# filter the df for private equity type investments
df = df[df.funding_round_type=="venture"]

# group by country codes and compare the total funding amounts
country_wise_total = df.groupby('country_code')['raised_amount_usd'].sum().sort_values(ascending=False)
print(country_wise_total)

"""
Explanation: The median investment amount for type 'private_equity' is approx. USD 20m, which is beyond Spark Funds' range of 5-15m. The median of 'venture' type is about USD 5m, which is suitable for them. The average amounts of angel and seed types are lower than their range.
Thus, 'venture' type investment will be most suited to them.
Country Analysis
Let's now compare the total investment amounts across countries. Note that we'll filter the data for only the 'venture' type investments and then compare the 'total investment' across countries.
End of explanation
"""


# top 9 countries
top_9_countries = country_wise_total[:9]
top_9_countries

"""
Explanation: Let's now extract the top 9 countries from country_wise_total.
End of explanation
"""


# filtering for the top three countries
df = df[(df.country_code=='USA') | (df.country_code=='GBR') | (df.country_code=='IND')]
df.head()

"""
Explanation: Among the top 9 countries, USA, GBR and IND are the top three English speaking countries. Let's filter the dataframe so it contains only the top 3 countries.
End of explanation
"""


# filtered df has about 38800 observations
df.info()

"""
Explanation: After filtering for 'venture' investments and the three countries USA, Great Britain and India, the filtered df looks like this.
End of explanation
"""


# boxplot to see distributions of funding amount across countries
plt.figure(figsize=(10, 10))
sns.boxplot(x='country_code', y='raised_amount_usd', data=df)
plt.yscale('log')
plt.show()

"""
Explanation: One can visually analyse the distribution and the total values of funding amount.
End of explanation
"""


# extracting the main category
df.loc[:, 'main_category'] = df['category_list'].apply(lambda x: x.split("|")[0])
df.head()

"""
Explanation: Now, we have shortlisted the investment type (venture) and the three countries. Let's now choose the sectors.
Sector Analysis
First, we need to extract the main sector using the column category_list. The category_list column contains values such as 'Biotechnology|Health Care' - in this, 'Biotechnology' is the 'main category' of the company, which we need to use.
Let's extract the main categories in a new column.
End of explanation
"""


# drop the category_list column
df = df.drop('category_list', axis=1)
df.head()

"""
Explanation: We can now drop the category_list column.
End of explanation
"""


# read mapping file
mapping = pd.read_csv("mapping.csv", sep=",")
mapping.head()

"""
Explanation: Now, we'll read the mapping.csv file and merge the main categories with its corresponding column.
End of explanation
"""


# missing values in mapping file
mapping.isnull().sum()

# remove the row with missing values
mapping = mapping[~pd.isnull(mapping['category_list'])]
mapping.isnull().sum()

"""
Explanation: Firstly, let's get rid of the missing values since we'll not be able to merge those rows anyway.
End of explanation
"""


# converting common columns to lowercase
mapping['category_list'] = mapping['category_list'].str.lower()
df['main_category'] = df['main_category'].str.lower()

# look at heads
print(mapping.head())

print(df.head())

"""
Explanation: Now, since we need to merge the mapping file with the main dataframe (df), let's convert the common column to lowercase in both.
End of explanation
"""


mapping['category_list']

"""
Explanation: Let's have a look at the category_list column of the mapping file. These values will be used to merge with the main df.
End of explanation
"""


# values in main_category column in df which are not in the category_list column in mapping file
df[~df['main_category'].isin(mapping['category_list'])]

"""
Explanation: To be able to merge all the main_category values with the mapping file's category_list column, all the values in the  main_category column should be present in the category_list column of the mapping file.
Let's see if this is true.
End of explanation
"""


# values in the category_list column which are not in main_category column 
mapping[~mapping['category_list'].isin(df['main_category'])]

"""
Explanation: Notice that values such as 'analytics', 'business analytics', 'finance', 'nanatechnology' etc. are not present in the mapping file.
Let's have a look at the values which are present in the mapping file but not in the main dataframe df.
End of explanation
"""


# replacing '0' with 'na'
mapping['category_list'] = mapping['category_list'].apply(lambda x: x.replace('0', 'na'))
print(mapping['category_list'])

"""
Explanation: If you see carefully, you'll notice something fishy - there are sectors named alter0tive medicine, a0lytics, waste ma0gement, veteri0ry, etc. This is not a random quality issue, but rather a pattern. In some strings, the 'na' has been replaced by '0'. This is weird - maybe someone was trying to replace the 'NA' values with '0', and ended up doing this. 
Let's treat this problem by replacing '0' with 'na' in the category_list column.
End of explanation
"""


# merge the dfs
df = pd.merge(df, mapping, how='inner', left_on='main_category', right_on='category_list')
df.head()

# let's drop the category_list column since it is the same as main_category
df = df.drop('category_list', axis=1)
df.head()

# look at the column types and names
df.info()

"""
Explanation: This looks fine now. Let's now merge the two dataframes.
End of explanation
"""


help(pd.melt)

# store the value and id variables in two separate arrays

# store the value variables in one Series
value_vars = df.columns[9:18]

# take the setdiff() to get the rest of the variables
id_vars = np.setdiff1d(df.columns, value_vars)

print(value_vars, "\n")
print(id_vars)

# convert into long
long_df = pd.melt(df, 
        id_vars=list(id_vars), 
        value_vars=list(value_vars))

long_df.head()

"""
Explanation: Converting the 'wide' dataframe to 'long'
You'll notice that the columns representing the main category in the mapping file are originally in the 'wide' format - Automotive & Sports, Cleantech / Semiconductors etc.
They contain the value '1' if the company belongs to that category, else 0. This is quite redundant. We can as well have a column named 'sub-category' having these values. 
Let's convert the df into the long format from the current wide format. First, we'll store the 'value variables' (those which are to be melted) in an array. The rest will then be the 'index variables'.
End of explanation
"""


# remove rows having value=0
long_df = long_df[long_df['value']==1]
long_df = long_df.drop('value', axis=1)

# look at the new df
long_df.head()
len(long_df)

# renaming the 'variable' column
long_df = long_df.rename(columns={'variable': 'sector'})

# info
long_df.info()

"""
Explanation: We can now get rid of the rows where the column 'value' is 0 and then remove that column altogether.
End of explanation
"""


# summarising the sector-wise number and sum of venture investments across three countries

# first, let's also filter for investment range between 5 and 15m
df = long_df[(long_df['raised_amount_usd'] >= 5000000) & (long_df['raised_amount_usd'] <= 15000000)]


# groupby country, sector and compute the count and sum
df.groupby(['country_code', 'sector']).raised_amount_usd.agg(['count', 'sum'])

"""
Explanation: The dataframe now contains only venture type investments in countries USA, IND and GBR, and we have mapped each company to one of the eight main sectors (named 'sector' in the dataframe). 
We can now compute the sector-wise number and the amount of investment in the three countries.
End of explanation
"""


# plotting sector-wise count and sum of investments in the three countries
plt.figure(figsize=(16, 14))

plt.subplot(2, 1, 1)
p = sns.barplot(x='sector', y='raised_amount_usd', hue='country_code', data=df, estimator=np.sum)
p.set_xticklabels(p.get_xticklabels(),rotation=30)
plt.title('Total Invested Amount (USD)')

plt.subplot(2, 1, 2)
q = sns.countplot(x='sector', hue='country_code', data=df)
q.set_xticklabels(q.get_xticklabels(),rotation=30)
plt.title('Number of Investments')


plt.show()

"""
Explanation: This will be much more easy to understand using a plot.
End of explanation
"""


