Analysis  for SparksFunds.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importing packages which going to be used\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to ignore Warnings\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Data Cleaning "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>permalink</th>\n",
       "      <th>name</th>\n",
       "      <th>homepage_url</th>\n",
       "      <th>category_list</th>\n",
       "      <th>status</th>\n",
       "      <th>country_code</th>\n",
       "      <th>state_code</th>\n",
       "      <th>region</th>\n",
       "      <th>city</th>\n",
       "      <th>founded_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/Organization/-Fame</td>\n",
       "      <td>#fame</td>\n",
       "      <td>http://livfame.com</td>\n",
       "      <td>Media</td>\n",
       "      <td>operating</td>\n",
       "      <td>IND</td>\n",
       "      <td>16</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/Organization/-Qounter</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>http://www.qounter.com</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE - Other</td>\n",
       "      <td>Delaware City</td>\n",
       "      <td>04-09-2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/Organization/-The-One-Of-Them-Inc-</td>\n",
       "      <td>(THE) ONE of THEM,Inc.</td>\n",
       "      <td>http://oneofthem.jp</td>\n",
       "      <td>Apps|Games|Mobile</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/Organization/0-6-Com</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>http://www.0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>operating</td>\n",
       "      <td>CHN</td>\n",
       "      <td>22</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>01-01-2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/Organization/004-Technologies</td>\n",
       "      <td>004 Technologies</td>\n",
       "      <td>http://004gmbh.de/en/004-interact</td>\n",
       "      <td>Software</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>IL</td>\n",
       "      <td>Springfield, Illinois</td>\n",
       "      <td>Champaign</td>\n",
       "      <td>01-01-2010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             permalink                    name  \\\n",
       "0                  /Organization/-Fame                   #fame   \n",
       "1               /Organization/-Qounter                :Qounter   \n",
       "2  /Organization/-The-One-Of-Them-Inc-  (THE) ONE of THEM,Inc.   \n",
       "3                /Organization/0-6-Com                 0-6.com   \n",
       "4       /Organization/004-Technologies        004 Technologies   \n",
       "\n",
       "                        homepage_url  \\\n",
       "0                 http://livfame.com   \n",
       "1             http://www.qounter.com   \n",
       "2                http://oneofthem.jp   \n",
       "3                 http://www.0-6.com   \n",
       "4  http://004gmbh.de/en/004-interact   \n",
       "\n",
       "                                       category_list     status country_code  \\\n",
       "0                                              Media  operating          IND   \n",
       "1  Application Platforms|Real Time|Social Network...  operating          USA   \n",
       "2                                  Apps|Games|Mobile  operating          NaN   \n",
       "3                                        Curated Web  operating          CHN   \n",
       "4                                           Software  operating          USA   \n",
       "\n",
       "  state_code                 region           city  founded_at  \n",
       "0         16                 Mumbai         Mumbai         NaN  \n",
       "1         DE             DE - Other  Delaware City  04-09-2014  \n",
       "2        NaN                    NaN            NaN         NaN  \n",
       "3         22                Beijing        Beijing  01-01-2007  \n",
       "4         IL  Springfield, Illinois      Champaign  01-01-2010  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Decoding the given 'companies.csv' file to load data in system.\n",
    "companies = pd.read_csv('companies.csv',encoding='ISO-8859-1')\n",
    "companies.permalink = companies.permalink.str.encode('ISO-8859-1').str.decode('ascii', 'ignore')\n",
    "companies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>funding_round_code</th>\n",
       "      <th>funded_at</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>/funding-round/9a01d05418af9f794eebff7ace91f638</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>05-01-2015</td>\n",
       "      <td>10000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/ORGANIZATION/-QOUNTER</td>\n",
       "      <td>/funding-round/22dacff496eb7acb2b901dec1dfe5633</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>14-10-2014</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>/funding-round/b44fbb94153f6cdef13083530bb48030</td>\n",
       "      <td>seed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-03-2014</td>\n",
       "      <td>700000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/ORGANIZATION/-THE-ONE-OF-THEM-INC-</td>\n",
       "      <td>/funding-round/650b8f704416801069bb178a1418776b</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>30-01-2014</td>\n",
       "      <td>3406878.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>/funding-round/5727accaeaa57461bd22a9bdd945382d</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>19-03-2008</td>\n",
       "      <td>2000000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     company_permalink  \\\n",
       "0                  /organization/-fame   \n",
       "1               /ORGANIZATION/-QOUNTER   \n",
       "2               /organization/-qounter   \n",
       "3  /ORGANIZATION/-THE-ONE-OF-THEM-INC-   \n",
       "4                /organization/0-6-com   \n",
       "\n",
       "                           funding_round_permalink funding_round_type  \\\n",
       "0  /funding-round/9a01d05418af9f794eebff7ace91f638            venture   \n",
       "1  /funding-round/22dacff496eb7acb2b901dec1dfe5633            venture   \n",
       "2  /funding-round/b44fbb94153f6cdef13083530bb48030               seed   \n",
       "3  /funding-round/650b8f704416801069bb178a1418776b            venture   \n",
       "4  /funding-round/5727accaeaa57461bd22a9bdd945382d            venture   \n",
       "\n",
       "  funding_round_code   funded_at  raised_amount_usd  \n",
       "0                  B  05-01-2015         10000000.0  \n",
       "1                  A  14-10-2014                NaN  \n",
       "2                NaN  01-03-2014           700000.0  \n",
       "3                  B  30-01-2014          3406878.0  \n",
       "4                  A  19-03-2008          2000000.0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# decoding the given 'rounds2.csv' file to load data in system.\n",
    "rounds2 = pd.read_csv('rounds2.csv',encoding='ISO-8859-1')\n",
    "rounds2.company_permalink = rounds2.company_permalink.str.encode('ISO-8859-1').str.decode('ascii', 'ignore')\n",
    "rounds2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Understand the Data Set \n",
    "\n",
    "    1.)How many unique companies are present in rounds2?\t\n",
    "\n",
    "    2.)How many unique companies are present in companies?\t                       \n",
    "\n",
    "    3.)In the companies data frame, which column can be used as the unique key for each company? Write the name of the column. Are there any companies in the rounds2 file which are not present in companies? Answer yes or no: Y/N\t \n",
    "\n",
    "    4.)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?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "66368\n",
      "Unique companies are present in rounds2 are: 66368\n"
     ]
    }
   ],
   "source": [
    "# 1.) How many unique companies are present in rounds2?\n",
    "rounds2['company_permalink'] = rounds2['company_permalink'].str.lower()\n",
    "rounds2['company_name'] = rounds2['company_permalink'].apply(lambda x:0 if x==\"0\" else str(x[14:]))\n",
    "print(len(rounds2['company_name'].unique()))\n",
    "\n",
    "# For confirming -\n",
    "print(\"Unique companies are present in rounds2 are:\",len(rounds2['company_name'].value_counts()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "66368\n",
      "Unique companies are present in rounds2 are: 66368\n"
     ]
    }
   ],
   "source": [
    "# 2.) How many unique companies are present in companies?\n",
    "companies['permalink'] = companies['permalink'].str.lower()\n",
    "companies['company_name'] = companies['permalink'].apply(lambda x:0 if x==\"0\" else str(x[14:]))\n",
    "print(len(companies['company_name'].unique()))\n",
    "\n",
    "# For confirming -\n",
    "companies['company_name'].describe()\n",
    "print(\"Unique companies are present in rounds2 are:\",len(companies['company_name'].value_counts()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "companies in the rounds2 file which are present in companies: 66368\n"
     ]
    }
   ],
   "source": [
    "# 3.) Are there any companies in the rounds2 file which are not present in companies?\n",
    "test1 = pd.DataFrame(companies.company_name.unique())\n",
    "test2 = pd.DataFrame(rounds2.company_name.unique())\n",
    "print(test1.equals(test2))\n",
    "\n",
    "# For  Confirming-\n",
    "print(\"companies in the rounds2 file which are present in companies:\",len(test1.isin(test2)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "114949\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>funding_round_code</th>\n",
       "      <th>funded_at</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>company_name</th>\n",
       "      <th>permalink</th>\n",
       "      <th>name</th>\n",
       "      <th>homepage_url</th>\n",
       "      <th>category_list</th>\n",
       "      <th>status</th>\n",
       "      <th>country_code</th>\n",
       "      <th>state_code</th>\n",
       "      <th>region</th>\n",
       "      <th>city</th>\n",
       "      <th>founded_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>/funding-round/9a01d05418af9f794eebff7ace91f638</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>05-01-2015</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>-fame</td>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>#fame</td>\n",
       "      <td>http://livfame.com</td>\n",
       "      <td>Media</td>\n",
       "      <td>operating</td>\n",
       "      <td>IND</td>\n",
       "      <td>16</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>/funding-round/22dacff496eb7acb2b901dec1dfe5633</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>14-10-2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-qounter</td>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>http://www.qounter.com</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE - Other</td>\n",
       "      <td>Delaware City</td>\n",
       "      <td>04-09-2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>/funding-round/b44fbb94153f6cdef13083530bb48030</td>\n",
       "      <td>seed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-03-2014</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>-qounter</td>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>http://www.qounter.com</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE - Other</td>\n",
       "      <td>Delaware City</td>\n",
       "      <td>04-09-2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/-the-one-of-them-inc-</td>\n",
       "      <td>/funding-round/650b8f704416801069bb178a1418776b</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>30-01-2014</td>\n",
       "      <td>3406878.0</td>\n",
       "      <td>-the-one-of-them-inc-</td>\n",
       "      <td>/organization/-the-one-of-them-inc-</td>\n",
       "      <td>(THE) ONE of THEM,Inc.</td>\n",
       "      <td>http://oneofthem.jp</td>\n",
       "      <td>Apps|Games|Mobile</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>/funding-round/5727accaeaa57461bd22a9bdd945382d</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>19-03-2008</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6-com</td>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>http://www.0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>operating</td>\n",
       "      <td>CHN</td>\n",
       "      <td>22</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>01-01-2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     company_permalink  \\\n",
       "0                  /organization/-fame   \n",
       "1               /organization/-qounter   \n",
       "2               /organization/-qounter   \n",
       "3  /organization/-the-one-of-them-inc-   \n",
       "4                /organization/0-6-com   \n",
       "\n",
       "                           funding_round_permalink funding_round_type  \\\n",
       "0  /funding-round/9a01d05418af9f794eebff7ace91f638            venture   \n",
       "1  /funding-round/22dacff496eb7acb2b901dec1dfe5633            venture   \n",
       "2  /funding-round/b44fbb94153f6cdef13083530bb48030               seed   \n",
       "3  /funding-round/650b8f704416801069bb178a1418776b            venture   \n",
       "4  /funding-round/5727accaeaa57461bd22a9bdd945382d            venture   \n",
       "\n",
       "  funding_round_code   funded_at  raised_amount_usd           company_name  \\\n",
       "0                  B  05-01-2015         10000000.0                  -fame   \n",
       "1                  A  14-10-2014                NaN               -qounter   \n",
       "2                NaN  01-03-2014           700000.0               -qounter   \n",
       "3                  B  30-01-2014          3406878.0  -the-one-of-them-inc-   \n",
       "4                  A  19-03-2008          2000000.0                0-6-com   \n",
       "\n",
       "                             permalink                    name  \\\n",
       "0                  /organization/-fame                   #fame   \n",
       "1               /organization/-qounter                :Qounter   \n",
       "2               /organization/-qounter                :Qounter   \n",
       "3  /organization/-the-one-of-them-inc-  (THE) ONE of THEM,Inc.   \n",
       "4                /organization/0-6-com                 0-6.com   \n",
       "\n",
       "             homepage_url                                      category_list  \\\n",
       "0      http://livfame.com                                              Media   \n",
       "1  http://www.qounter.com  Application Platforms|Real Time|Social Network...   \n",
       "2  http://www.qounter.com  Application Platforms|Real Time|Social Network...   \n",
       "3     http://oneofthem.jp                                  Apps|Games|Mobile   \n",
       "4      http://www.0-6.com                                        Curated Web   \n",
       "\n",
       "      status country_code state_code      region           city  founded_at  \n",
       "0  operating          IND         16      Mumbai         Mumbai         NaN  \n",
       "1  operating          USA         DE  DE - Other  Delaware City  04-09-2014  \n",
       "2  operating          USA         DE  DE - Other  Delaware City  04-09-2014  \n",
       "3  operating          NaN        NaN         NaN            NaN         NaN  \n",
       "4  operating          CHN         22     Beijing        Beijing  01-01-2007  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4.) 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.\n",
    "master_frame = pd.merge(rounds2, companies, on = 'company_name') # Naming the merged frame as master_frame\n",
    "print(len(master_frame.company_name)) # checking its length\n",
    "master_frame.head() # getting some visuals of dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning the Data\n",
    "\n",
    "    1.) Inspecting Null Values\n",
    "    2.) Dropping unnecessary columns\n",
    "    3.) Dropping unnecessary rows"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Inspecting Null Values\n",
    "Checking the Null values present in the whole dataframe so that we can identify which column we should drop and which column we keep to work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "company_permalink              0\n",
       "funding_round_permalink        0\n",
       "funding_round_type             0\n",
       "funding_round_code         83809\n",
       "funded_at                      0\n",
       "raised_amount_usd          19990\n",
       "company_name                   0\n",
       "permalink                      0\n",
       "name                           1\n",
       "homepage_url                6134\n",
       "category_list               3410\n",
       "status                         0\n",
       "country_code                8678\n",
       "state_code                 10946\n",
       "region                     10167\n",
       "city                       10164\n",
       "founded_at                 20521\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Inspecting the Null values :-\n",
    "master_frame.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "company_permalink           0.00\n",
      "funding_round_permalink     0.00\n",
      "funding_round_type          0.00\n",
      "funding_round_code         72.91\n",
      "funded_at                   0.00\n",
      "raised_amount_usd          17.39\n",
      "company_name                0.00\n",
      "permalink                   0.00\n",
      "name                        0.00\n",
      "homepage_url                5.34\n",
      "category_list               2.97\n",
      "status                      0.00\n",
      "country_code                7.55\n",
      "state_code                  9.52\n",
      "region                      8.84\n",
      "city                        8.84\n",
      "founded_at                 17.85\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "#  Inspecting the Null values percentage :- \n",
    "print(round(100*(master_frame.isnull().sum()/len(master_frame.index)), 2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Dropping unnecessary columns\n",
    "For **Sparks Funds**, we are mostly driving our analysis based on *funding round type, category, country etc.* Hence, so many columns present in the ***master_frame*** which are not necessary, we will drop those columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>funding_round_code</th>\n",
       "      <th>funded_at</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>company_name</th>\n",
       "      <th>permalink</th>\n",
       "      <th>name</th>\n",
       "      <th>homepage_url</th>\n",
       "      <th>category_list</th>\n",
       "      <th>status</th>\n",
       "      <th>country_code</th>\n",
       "      <th>state_code</th>\n",
       "      <th>region</th>\n",
       "      <th>city</th>\n",
       "      <th>founded_at</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>/funding-round/9a01d05418af9f794eebff7ace91f638</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>05-01-2015</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>-fame</td>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>#fame</td>\n",
       "      <td>http://livfame.com</td>\n",
       "      <td>Media</td>\n",
       "      <td>operating</td>\n",
       "      <td>IND</td>\n",
       "      <td>16</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>/funding-round/22dacff496eb7acb2b901dec1dfe5633</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>14-10-2014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-qounter</td>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>http://www.qounter.com</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE - Other</td>\n",
       "      <td>Delaware City</td>\n",
       "      <td>04-09-2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>/funding-round/b44fbb94153f6cdef13083530bb48030</td>\n",
       "      <td>seed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01-03-2014</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>-qounter</td>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>http://www.qounter.com</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>operating</td>\n",
       "      <td>USA</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE - Other</td>\n",
       "      <td>Delaware City</td>\n",
       "      <td>04-09-2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/-the-one-of-them-inc-</td>\n",
       "      <td>/funding-round/650b8f704416801069bb178a1418776b</td>\n",
       "      <td>venture</td>\n",
       "      <td>B</td>\n",
       "      <td>30-01-2014</td>\n",
       "      <td>3406878.0</td>\n",
       "      <td>-the-one-of-them-inc-</td>\n",
       "      <td>/organization/-the-one-of-them-inc-</td>\n",
       "      <td>(THE) ONE of THEM,Inc.</td>\n",
       "      <td>http://oneofthem.jp</td>\n",
       "      <td>Apps|Games|Mobile</td>\n",
       "      <td>operating</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>/funding-round/5727accaeaa57461bd22a9bdd945382d</td>\n",
       "      <td>venture</td>\n",
       "      <td>A</td>\n",
       "      <td>19-03-2008</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6-com</td>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>http://www.0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>operating</td>\n",
       "      <td>CHN</td>\n",
       "      <td>22</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>Beijing</td>\n",
       "      <td>01-01-2007</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     company_permalink  \\\n",
       "0                  /organization/-fame   \n",
       "1               /organization/-qounter   \n",
       "2               /organization/-qounter   \n",
       "3  /organization/-the-one-of-them-inc-   \n",
       "4                /organization/0-6-com   \n",
       "\n",
       "                           funding_round_permalink funding_round_type  \\\n",
       "0  /funding-round/9a01d05418af9f794eebff7ace91f638            venture   \n",
       "1  /funding-round/22dacff496eb7acb2b901dec1dfe5633            venture   \n",
       "2  /funding-round/b44fbb94153f6cdef13083530bb48030               seed   \n",
       "3  /funding-round/650b8f704416801069bb178a1418776b            venture   \n",
       "4  /funding-round/5727accaeaa57461bd22a9bdd945382d            venture   \n",
       "\n",
       "  funding_round_code   funded_at  raised_amount_usd           company_name  \\\n",
       "0                  B  05-01-2015         10000000.0                  -fame   \n",
       "1                  A  14-10-2014                NaN               -qounter   \n",
       "2                NaN  01-03-2014           700000.0               -qounter   \n",
       "3                  B  30-01-2014          3406878.0  -the-one-of-them-inc-   \n",
       "4                  A  19-03-2008          2000000.0                0-6-com   \n",
       "\n",
       "                             permalink                    name  \\\n",
       "0                  /organization/-fame                   #fame   \n",
       "1               /organization/-qounter                :Qounter   \n",
       "2               /organization/-qounter                :Qounter   \n",
       "3  /organization/-the-one-of-them-inc-  (THE) ONE of THEM,Inc.   \n",
       "4                /organization/0-6-com                 0-6.com   \n",
       "\n",
       "             homepage_url                                      category_list  \\\n",
       "0      http://livfame.com                                              Media   \n",
       "1  http://www.qounter.com  Application Platforms|Real Time|Social Network...   \n",
       "2  http://www.qounter.com  Application Platforms|Real Time|Social Network...   \n",
       "3     http://oneofthem.jp                                  Apps|Games|Mobile   \n",
       "4      http://www.0-6.com                                        Curated Web   \n",
       "\n",
       "      status country_code state_code      region           city  founded_at  \n",
       "0  operating          IND         16      Mumbai         Mumbai         NaN  \n",
       "1  operating          USA         DE  DE - Other  Delaware City  04-09-2014  \n",
       "2  operating          USA         DE  DE - Other  Delaware City  04-09-2014  \n",
       "3  operating          NaN        NaN         NaN            NaN         NaN  \n",
       "4  operating          CHN         22     Beijing        Beijing  01-01-2007  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_frame.head() #checking before droppping rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>venture</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>venture</td>\n",
       "      <td>NaN</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>seed</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/-the-one-of-them-inc-</td>\n",
       "      <td>venture</td>\n",
       "      <td>3406878.0</td>\n",
       "      <td>(THE) ONE of THEM,Inc.</td>\n",
       "      <td>Apps|Games|Mobile</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>venture</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     company_permalink funding_round_type  raised_amount_usd  \\\n",
       "0                  /organization/-fame            venture         10000000.0   \n",
       "1               /organization/-qounter            venture                NaN   \n",
       "2               /organization/-qounter               seed           700000.0   \n",
       "3  /organization/-the-one-of-them-inc-            venture          3406878.0   \n",
       "4                /organization/0-6-com            venture          2000000.0   \n",
       "\n",
       "                     name                                      category_list  \\\n",
       "0                   #fame                                              Media   \n",
       "1                :Qounter  Application Platforms|Real Time|Social Network...   \n",
       "2                :Qounter  Application Platforms|Real Time|Social Network...   \n",
       "3  (THE) ONE of THEM,Inc.                                  Apps|Games|Mobile   \n",
       "4                 0-6.com                                        Curated Web   \n",
       "\n",
       "  country_code  \n",
       "0          IND  \n",
       "1          USA  \n",
       "2          USA  \n",
       "3          NaN  \n",
       "4          CHN  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  Dropping unnecessary columns\n",
    "master_frame = master_frame.drop(['funding_round_code', 'funding_round_permalink', 'funded_at','permalink', 'homepage_url','state_code', 'region', 'city', 'founded_at','status','company_name'], axis = 1)\n",
    "master_frame.head() # checking again after dropping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "company_permalink      0.00\n",
      "funding_round_type     0.00\n",
      "raised_amount_usd     17.39\n",
      "name                   0.00\n",
      "category_list          2.97\n",
      "country_code           7.55\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Inspecting the Null values percentage after dropping columns :-\n",
    "print(round(100*(master_frame.isnull().sum()/len(master_frame.index)), 2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping unnecessary rows\n",
    "For the remaining columns from ***master_frame*** dataframe, I saw that there are columns which still have null counts.\n",
    "So, I have dropped those rows and inspect the dataframe again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>venture</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>seed</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>venture</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>/organization/01games-technology</td>\n",
       "      <td>undisclosed</td>\n",
       "      <td>41250.0</td>\n",
       "      <td>01Games Technology</td>\n",
       "      <td>Games</td>\n",
       "      <td>HKG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>/organization/0ndine-biomedical-inc</td>\n",
       "      <td>seed</td>\n",
       "      <td>43360.0</td>\n",
       "      <td>Ondine Biomedical Inc.</td>\n",
       "      <td>Biotechnology</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114942</th>\n",
       "      <td>/organization/zzzzapp-com</td>\n",
       "      <td>seed</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>Zzzzapp Wireless ltd.</td>\n",
       "      <td>Advertising|Mobile|Web Development|Wireless</td>\n",
       "      <td>HRV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114943</th>\n",
       "      <td>/organization/zzzzapp-com</td>\n",
       "      <td>seed</td>\n",
       "      <td>33149.0</td>\n",
       "      <td>Zzzzapp Wireless ltd.</td>\n",
       "      <td>Advertising|Mobile|Web Development|Wireless</td>\n",
       "      <td>HRV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114944</th>\n",
       "      <td>/organization/zzzzapp-com</td>\n",
       "      <td>convertible_note</td>\n",
       "      <td>41313.0</td>\n",
       "      <td>Zzzzapp Wireless ltd.</td>\n",
       "      <td>Advertising|Mobile|Web Development|Wireless</td>\n",
       "      <td>HRV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114945</th>\n",
       "      <td>/organization/zzzzapp-com</td>\n",
       "      <td>seed</td>\n",
       "      <td>32842.0</td>\n",
       "      <td>Zzzzapp Wireless ltd.</td>\n",
       "      <td>Advertising|Mobile|Web Development|Wireless</td>\n",
       "      <td>HRV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114947</th>\n",
       "      <td>/organization/asys-2</td>\n",
       "      <td>seed</td>\n",
       "      <td>18192.0</td>\n",
       "      <td>Ãasys</td>\n",
       "      <td>Consumer Electronics|Internet of Things|Teleco...</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>88529 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          company_permalink funding_round_type  \\\n",
       "0                       /organization/-fame            venture   \n",
       "2                    /organization/-qounter               seed   \n",
       "4                     /organization/0-6-com            venture   \n",
       "6          /organization/01games-technology        undisclosed   \n",
       "7       /organization/0ndine-biomedical-inc               seed   \n",
       "...                                     ...                ...   \n",
       "114942            /organization/zzzzapp-com               seed   \n",
       "114943            /organization/zzzzapp-com               seed   \n",
       "114944            /organization/zzzzapp-com   convertible_note   \n",
       "114945            /organization/zzzzapp-com               seed   \n",
       "114947                 /organization/asys-2               seed   \n",
       "\n",
       "        raised_amount_usd                    name  \\\n",
       "0              10000000.0                   #fame   \n",
       "2                700000.0                :Qounter   \n",
       "4               2000000.0                 0-6.com   \n",
       "6                 41250.0      01Games Technology   \n",
       "7                 43360.0  Ondine Biomedical Inc.   \n",
       "...                   ...                     ...   \n",
       "114942             7000.0   Zzzzapp Wireless ltd.   \n",
       "114943            33149.0   Zzzzapp Wireless ltd.   \n",
       "114944            41313.0   Zzzzapp Wireless ltd.   \n",
       "114945            32842.0   Zzzzapp Wireless ltd.   \n",
       "114947            18192.0                  Ãasys   \n",
       "\n",
       "                                            category_list country_code  \n",
       "0                                                   Media          IND  \n",
       "2       Application Platforms|Real Time|Social Network...          USA  \n",
       "4                                             Curated Web          CHN  \n",
       "6                                                   Games          HKG  \n",
       "7                                           Biotechnology          CAN  \n",
       "...                                                   ...          ...  \n",
       "114942        Advertising|Mobile|Web Development|Wireless          HRV  \n",
       "114943        Advertising|Mobile|Web Development|Wireless          HRV  \n",
       "114944        Advertising|Mobile|Web Development|Wireless          HRV  \n",
       "114945        Advertising|Mobile|Web Development|Wireless          HRV  \n",
       "114947  Consumer Electronics|Internet of Things|Teleco...          USA  \n",
       "\n",
       "[88529 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# a.) Dropping rows based on null columns\n",
    "master_frame = master_frame[~(master_frame['raised_amount_usd'].isnull() | master_frame['country_code'].isnull() | master_frame['category_list'].isnull())]\n",
    "master_frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "77.01589400516751\n"
     ]
    }
   ],
   "source": [
    "# b.) Percentage of remaining rows :-\n",
    "print(100*(len(master_frame.index)/114949)) # we devided the length of master_frame (after cleaning the data) with its \n",
    "# original length (initial length of rows of data) to get the idea of value of retained data(remaining data)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(88529, 6)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# c.) To check the shape of dataframe after cleaning the data :-\n",
    "master_frame.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Funding Type Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retaining the rows with only four investment types.\n",
    "**Spark Funds** wants to choose one of these four investment *types(venture, angel, seed, and private equity)* for each potential investment they will make. So let's observe how many funding types are present in ***master_frame*** and then retain the rows with above-mentioned investment types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "venture                  47809\n",
       "seed                     21095\n",
       "debt_financing            6506\n",
       "angel                     4400\n",
       "grant                     1939\n",
       "private_equity            1820\n",
       "undisclosed               1345\n",
       "convertible_note          1320\n",
       "equity_crowdfunding       1128\n",
       "post_ipo_equity            598\n",
       "product_crowdfunding       330\n",
       "post_ipo_debt              151\n",
       "non_equity_assistance       60\n",
       "secondary_market            28\n",
       "Name: funding_round_type, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Observing all the unique funding_round_type :-\n",
    "master_frame.funding_round_type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>venture</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/-qounter</td>\n",
       "      <td>seed</td>\n",
       "      <td>700000.0</td>\n",
       "      <td>:Qounter</td>\n",
       "      <td>Application Platforms|Real Time|Social Network...</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>venture</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>/organization/0ndine-biomedical-inc</td>\n",
       "      <td>seed</td>\n",
       "      <td>43360.0</td>\n",
       "      <td>Ondine Biomedical Inc.</td>\n",
       "      <td>Biotechnology</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>/organization/0ndine-biomedical-inc</td>\n",
       "      <td>venture</td>\n",
       "      <td>719491.0</td>\n",
       "      <td>Ondine Biomedical Inc.</td>\n",
       "      <td>Biotechnology</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     company_permalink funding_round_type  raised_amount_usd  \\\n",
       "0                  /organization/-fame            venture         10000000.0   \n",
       "2               /organization/-qounter               seed           700000.0   \n",
       "4                /organization/0-6-com            venture          2000000.0   \n",
       "7  /organization/0ndine-biomedical-inc               seed            43360.0   \n",
       "8  /organization/0ndine-biomedical-inc            venture           719491.0   \n",
       "\n",
       "                     name                                      category_list  \\\n",
       "0                   #fame                                              Media   \n",
       "2                :Qounter  Application Platforms|Real Time|Social Network...   \n",
       "4                 0-6.com                                        Curated Web   \n",
       "7  Ondine Biomedical Inc.                                      Biotechnology   \n",
       "8  Ondine Biomedical Inc.                                      Biotechnology   \n",
       "\n",
       "  country_code  \n",
       "0          IND  \n",
       "2          USA  \n",
       "4          CHN  \n",
       "7          CAN  \n",
       "8          CAN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retaining the rows with only those four investment types which we want to consider :-\n",
    "master_frame = master_frame[(master_frame['funding_round_type'] == 'venture') \n",
    "                            | (master_frame['funding_round_type'] == 'seed') \n",
    "                            | (master_frame['funding_round_type'] == 'angel') \n",
    "                            | (master_frame['funding_round_type'] == 'private_equity')]\n",
    "master_frame.head() # to check the data in dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "funding_round_type\n",
       "angel               971573.89\n",
       "private_equity    73938486.28\n",
       "seed                747793.68\n",
       "venture           11724222.69\n",
       "Name: raised_amount_usd, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculating average investment amount for each of the four funding types.\n",
    "round(master_frame.groupby('funding_round_type').raised_amount_usd.mean(), 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>funding_round_type</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>venture</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>venture</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>/organization/0ndine-biomedical-inc</td>\n",
       "      <td>venture</td>\n",
       "      <td>719491.0</td>\n",
       "      <td>Ondine Biomedical Inc.</td>\n",
       "      <td>Biotechnology</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>venture</td>\n",
       "      <td>20000000.0</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>venture</td>\n",
       "      <td>1700000.0</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      company_permalink funding_round_type  raised_amount_usd  \\\n",
       "0                   /organization/-fame            venture         10000000.0   \n",
       "4                 /organization/0-6-com            venture          2000000.0   \n",
       "8   /organization/0ndine-biomedical-inc            venture           719491.0   \n",
       "10                 /organization/0xdata            venture         20000000.0   \n",
       "11                 /organization/0xdata            venture          1700000.0   \n",
       "\n",
       "                      name  category_list country_code  \n",
       "0                    #fame          Media          IND  \n",
       "4                  0-6.com    Curated Web          CHN  \n",
       "8   Ondine Biomedical Inc.  Biotechnology          CAN  \n",
       "10                  H2O.ai      Analytics          USA  \n",
       "11                  H2O.ai      Analytics          USA  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retaining rows with only venture type. \n",
    "# As, Venture have the highest count in funding round.\n",
    "master_frame = master_frame[master_frame['funding_round_type'] == 'venture'] #choosing only venture types. \n",
    "master_frame.head() # checking dataframe after alterations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>10000000.0</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/0-6-com</td>\n",
       "      <td>2000000.0</td>\n",
       "      <td>0-6.com</td>\n",
       "      <td>Curated Web</td>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>/organization/0ndine-biomedical-inc</td>\n",
       "      <td>719491.0</td>\n",
       "      <td>Ondine Biomedical Inc.</td>\n",
       "      <td>Biotechnology</td>\n",
       "      <td>CAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>20000000.0</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>1700000.0</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      company_permalink  raised_amount_usd  \\\n",
       "0                   /organization/-fame         10000000.0   \n",
       "4                 /organization/0-6-com          2000000.0   \n",
       "8   /organization/0ndine-biomedical-inc           719491.0   \n",
       "10                 /organization/0xdata         20000000.0   \n",
       "11                 /organization/0xdata          1700000.0   \n",
       "\n",
       "                      name  category_list country_code  \n",
       "0                    #fame          Media          IND  \n",
       "4                  0-6.com    Curated Web          CHN  \n",
       "8   Ondine Biomedical Inc.  Biotechnology          CAN  \n",
       "10                  H2O.ai      Analytics          USA  \n",
       "11                  H2O.ai      Analytics          USA  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Dropping the column 'funding_round_type' as we are going to work with venture type only, from this point forward.\n",
    "master_frame = master_frame.drop(['funding_round_type'], axis = 1)\n",
    "master_frame.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Country Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  Analysing the countries based on investment amount\n",
    "\n",
    "Spark Funds wants to see the top nine countries which have received the highest total funding (across ALL sectors for the chosen investment type)\n",
    "\n",
    "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country_code</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>420068029342.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHN</th>\n",
       "      <td>39338918773.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBR</th>\n",
       "      <td>20072813004.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IND</th>\n",
       "      <td>14261508718.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAN</th>\n",
       "      <td>9482217668.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              raised_amount_usd\n",
       "country_code                   \n",
       "USA             420068029342.00\n",
       "CHN              39338918773.00\n",
       "GBR              20072813004.00\n",
       "IND              14261508718.00\n",
       "CAN               9482217668.00"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Identify the top three English-speaking countries in the data frame top9.\n",
    "pd.options.display.float_format = '{:.2f}'.format # to see the values till end.\n",
    "top9 =round(master_frame.pivot_table(values = 'raised_amount_usd', index = 'country_code', aggfunc = 'sum'),2) # \n",
    "# rounding decimal values to 2 digits. \n",
    "top9 = top9.sort_values(by = 'raised_amount_usd', ascending = False) \n",
    "# to sort the resulted data into descending order by values\n",
    "top9 = top9.iloc[:9, ] # to get the data of top 9 countries \n",
    "top9.head() # looking the dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>10000000.00</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>20000000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>1700000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>8900000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>/organization/1-mainstream</td>\n",
       "      <td>5000000.00</td>\n",
       "      <td>1 Mainstream</td>\n",
       "      <td>Apps|Cable|Distribution|Software</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             company_permalink  raised_amount_usd          name  \\\n",
       "0          /organization/-fame        10000000.00         #fame   \n",
       "10        /organization/0xdata        20000000.00        H2O.ai   \n",
       "11        /organization/0xdata         1700000.00        H2O.ai   \n",
       "12        /organization/0xdata         8900000.00        H2O.ai   \n",
       "22  /organization/1-mainstream         5000000.00  1 Mainstream   \n",
       "\n",
       "                       category_list country_code  \n",
       "0                              Media          IND  \n",
       "10                         Analytics          USA  \n",
       "11                         Analytics          USA  \n",
       "12                         Analytics          USA  \n",
       "22  Apps|Cable|Distribution|Software          USA  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filtering rows on USA, GBR and IND as country_codes.\n",
    "#As SparksFunds wants to invest in only top three English speaking countries.\n",
    "master_frame = master_frame[(master_frame['country_code'] == 'USA') \n",
    "                            | (master_frame['country_code'] == 'GBR') \n",
    "                            | (master_frame['country_code'] == 'IND')]\n",
    "\n",
    "master_frame.head() # to look into dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Sector Analysis 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract the primary sector of each category\n",
    " Extract the primary sector value into category_list column. According to the business rule the first string before the vertical bar will be considered the primary sector."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>10000000.00</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>20000000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>1700000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>8900000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>/organization/1-mainstream</td>\n",
       "      <td>5000000.00</td>\n",
       "      <td>1 Mainstream</td>\n",
       "      <td>Apps</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             company_permalink  raised_amount_usd          name category_list  \\\n",
       "0          /organization/-fame        10000000.00         #fame         Media   \n",
       "10        /organization/0xdata        20000000.00        H2O.ai     Analytics   \n",
       "11        /organization/0xdata         1700000.00        H2O.ai     Analytics   \n",
       "12        /organization/0xdata         8900000.00        H2O.ai     Analytics   \n",
       "22  /organization/1-mainstream         5000000.00  1 Mainstream          Apps   \n",
       "\n",
       "   country_code  \n",
       "0           IND  \n",
       "10          USA  \n",
       "11          USA  \n",
       "12          USA  \n",
       "22          USA  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Extracting the primary sector value from category list column\n",
    "master_frame['category_list'] = master_frame['category_list'].apply(lambda x: x.split('|')[0]) \n",
    "master_frame.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Map each primary sector to one of the eight main sectors\n",
    "Use the mapping file 'mapping.csv' to map each primary sector to one of the eight main sectors (Note that ‘Others’ is also considered one of the main sectors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_list</th>\n",
       "      <th>Automotive &amp; Sports</th>\n",
       "      <th>Blanks</th>\n",
       "      <th>Cleantech / Semiconductors</th>\n",
       "      <th>Entertainment</th>\n",
       "      <th>Health</th>\n",
       "      <th>Manufacturing</th>\n",
       "      <th>News, Search and Messaging</th>\n",
       "      <th>Others</th>\n",
       "      <th>Social, Finance, Analytics, Advertising</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3D</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3D Printing</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3D Technology</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Accounting</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>683</th>\n",
       "      <td>Wholesale</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>Wine And Spirits</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>685</th>\n",
       "      <td>Wireless</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>Women</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>687</th>\n",
       "      <td>Young Adults</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>688 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        category_list  Automotive & Sports  Blanks  \\\n",
       "0                 NaN                    0       1   \n",
       "1                  3D                    0       0   \n",
       "2         3D Printing                    0       0   \n",
       "3       3D Technology                    0       0   \n",
       "4          Accounting                    0       0   \n",
       "..                ...                  ...     ...   \n",
       "683         Wholesale                    0       0   \n",
       "684  Wine And Spirits                    0       0   \n",
       "685          Wireless                    0       0   \n",
       "686             Women                    0       0   \n",
       "687      Young Adults                    0       0   \n",
       "\n",
       "     Cleantech / Semiconductors  Entertainment  Health  Manufacturing  \\\n",
       "0                             0              0       0              0   \n",
       "1                             0              0       0              1   \n",
       "2                             0              0       0              1   \n",
       "3                             0              0       0              1   \n",
       "4                             0              0       0              0   \n",
       "..                          ...            ...     ...            ...   \n",
       "683                           0              0       0              0   \n",
       "684                           0              1       0              0   \n",
       "685                           0              1       0              0   \n",
       "686                           0              0       0              0   \n",
       "687                           0              0       0              0   \n",
       "\n",
       "     News, Search and Messaging  Others  \\\n",
       "0                             0       0   \n",
       "1                             0       0   \n",
       "2                             0       0   \n",
       "3                             0       0   \n",
       "4                             0       0   \n",
       "..                          ...     ...   \n",
       "683                           0       1   \n",
       "684                           0       0   \n",
       "685                           0       0   \n",
       "686                           0       1   \n",
       "687                           0       1   \n",
       "\n",
       "     Social, Finance, Analytics, Advertising  \n",
       "0                                          0  \n",
       "1                                          0  \n",
       "2                                          0  \n",
       "3                                          0  \n",
       "4                                          1  \n",
       "..                                       ...  \n",
       "683                                        0  \n",
       "684                                        0  \n",
       "685                                        0  \n",
       "686                                        0  \n",
       "687                                        0  \n",
       "\n",
       "[688 rows x 10 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading mapping.csv file \n",
    "mapping = pd.read_csv('mapping.csv')\n",
    "mapping.category_list = mapping.category_list.replace({'0':'na', '2.na' :'2.0'}, regex=True) #replacing values to make it useful.\n",
    "mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Reshaping the mapping dataframe to merge with the master_frame dataframe. Using melt() function to unpivot the table.\n",
    "mapping = pd.melt(mapping, id_vars =['category_list'], \n",
    "                  value_vars =['Manufacturing','Automotive & Sports', 'Cleantech / Semiconductors',\n",
    "                               'Entertainment','Health','News, Search and Messaging','Others',\n",
    "                               'Social, Finance, Analytics, Advertising'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing entites with null values\n",
    "mapping = mapping[~(mapping.value == 0)] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_list</th>\n",
       "      <th>variable</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3D</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3D Printing</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3D Technology</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Advanced Materials</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Agriculture</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5433</th>\n",
       "      <td>Social Recruiting</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5434</th>\n",
       "      <td>Social Television</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5435</th>\n",
       "      <td>Social Travel</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5446</th>\n",
       "      <td>Stock Exchanges</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5479</th>\n",
       "      <td>Venture Capital</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>687 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           category_list                                 variable\n",
       "1                     3D                            Manufacturing\n",
       "2            3D Printing                            Manufacturing\n",
       "3          3D Technology                            Manufacturing\n",
       "7     Advanced Materials                            Manufacturing\n",
       "15           Agriculture                            Manufacturing\n",
       "...                  ...                                      ...\n",
       "5433   Social Recruiting  Social, Finance, Analytics, Advertising\n",
       "5434   Social Television  Social, Finance, Analytics, Advertising\n",
       "5435       Social Travel  Social, Finance, Analytics, Advertising\n",
       "5446     Stock Exchanges  Social, Finance, Analytics, Advertising\n",
       "5479     Venture Capital  Social, Finance, Analytics, Advertising\n",
       "\n",
       "[687 rows x 2 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dropping value column as all the rows have same (1) as their column. so, we dont need it anymore. \n",
    "mapping = mapping.drop('value', axis = 1)\n",
    "mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>category_list</th>\n",
       "      <th>main_sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3D</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3D Printing</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3D Technology</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Advanced Materials</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Agriculture</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5433</th>\n",
       "      <td>Social Recruiting</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5434</th>\n",
       "      <td>Social Television</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5435</th>\n",
       "      <td>Social Travel</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5446</th>\n",
       "      <td>Stock Exchanges</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5479</th>\n",
       "      <td>Venture Capital</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>687 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           category_list                              main_sector\n",
       "1                     3D                            Manufacturing\n",
       "2            3D Printing                            Manufacturing\n",
       "3          3D Technology                            Manufacturing\n",
       "7     Advanced Materials                            Manufacturing\n",
       "15           Agriculture                            Manufacturing\n",
       "...                  ...                                      ...\n",
       "5433   Social Recruiting  Social, Finance, Analytics, Advertising\n",
       "5434   Social Television  Social, Finance, Analytics, Advertising\n",
       "5435       Social Travel  Social, Finance, Analytics, Advertising\n",
       "5446     Stock Exchanges  Social, Finance, Analytics, Advertising\n",
       "5479     Venture Capital  Social, Finance, Analytics, Advertising\n",
       "\n",
       "[687 rows x 2 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# naming column with recognisable name as main sector.\n",
    "mapping = mapping.rename(columns = {\"variable\":\"main_sector\"})\n",
    "mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "      <th>main_sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>10000000.00</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "      <td>Entertainment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>20000000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>1700000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>8900000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/1-mainstream</td>\n",
       "      <td>5000000.00</td>\n",
       "      <td>1 Mainstream</td>\n",
       "      <td>Apps</td>\n",
       "      <td>USA</td>\n",
       "      <td>News, Search and Messaging</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            company_permalink  raised_amount_usd          name category_list  \\\n",
       "0         /organization/-fame        10000000.00         #fame         Media   \n",
       "1        /organization/0xdata        20000000.00        H2O.ai     Analytics   \n",
       "2        /organization/0xdata         1700000.00        H2O.ai     Analytics   \n",
       "3        /organization/0xdata         8900000.00        H2O.ai     Analytics   \n",
       "4  /organization/1-mainstream         5000000.00  1 Mainstream          Apps   \n",
       "\n",
       "  country_code                              main_sector  \n",
       "0          IND                            Entertainment  \n",
       "1          USA  Social, Finance, Analytics, Advertising  \n",
       "2          USA  Social, Finance, Analytics, Advertising  \n",
       "3          USA  Social, Finance, Analytics, Advertising  \n",
       "4          USA               News, Search and Messaging  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merging mapping dataframe with master_frame to add data to it. and to use them as whole.\n",
    "master_frame = master_frame.merge(mapping, how = 'left', on ='category_list')\n",
    "master_frame.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Nanotechnology' 'Natural Gas Uses' 'Natural Language Processing'\n",
      " 'Enterprise 2.0' 'Adaptive Equipment' 'Racing' 'Specialty Retail'\n",
      " 'Biotechnology and Semiconductor' 'Rapidly Expanding' 'Navigation'\n",
      " 'Product Search' 'GreenTech' 'Retirement']\n"
     ]
    }
   ],
   "source": [
    "#Listing out those rows which have no main sectors in the master_frame.\n",
    "print(master_frame[master_frame.main_sector.isnull()].category_list.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "165"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Identifying number of rows with NaN value in main_sector.\n",
    "len(master_frame[master_frame.main_sector.isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38638"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filtering the rows which have main_sector from NaN or null values.\n",
    "master_frame = master_frame[~(master_frame.main_sector.isnull())]\n",
    "len(master_frame.index) # checking length of data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Others                                     9110\n",
      "Cleantech / Semiconductors                 8348\n",
      "Social, Finance, Analytics, Advertising    7814\n",
      "News, Search and Messaging                 4721\n",
      "Health                                     3429\n",
      "Manufacturing                              2630\n",
      "Entertainment                              2006\n",
      "Automotive & Sports                         580\n",
      "Name: main_sector, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# checking all 8 main sectors and their number of entites in main_frame.\n",
    "print(master_frame.main_sector.value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sector Analysis 2\n",
    "### Create Data-Frames D1, D2, D3  for three different countries\n",
    "\n",
    "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:\n",
    "\n",
    "All the columns of the master_frame along with the primary sector and the main sector\n",
    "\n",
    "The total number (or count) of investments for each main sector in a separate column\n",
    "\n",
    "The total amount invested in each main sector in a separate column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>12012.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8934256.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3075071.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6012337.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8304890.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>11000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>15000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       raised_amount_usd\n",
       "count           12012.00\n",
       "mean          8934256.97\n",
       "std           3075071.25\n",
       "min           5000000.00\n",
       "25%           6012337.25\n",
       "50%           8304890.00\n",
       "75%          11000000.00\n",
       "max          15000000.00"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# making D1 dataframe containing data specifically regarding USA for the ranged \"raised amount\".\n",
    "D1 = master_frame[(master_frame['country_code'] == 'USA') \n",
    "                  & (master_frame['raised_amount_usd'] >= 5000000) \n",
    "                  & (master_frame['raised_amount_usd'] <= 15000000)]\n",
    "D1.describe() # to get the basic value check on the varibles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEECAYAAAA4Qc+SAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAPM0lEQVR4nO3df6zddX3H8efLttsYIE6rNwqOMsJEN8HhVZw6vaLTon+QbWpgbE6naUzQ6RITumUbGpMFY/ZTxa5zXXVxJXM4hxaLTjxiVDZaLdCCYgNVG5Z0jAEWiVp474/77Xa4vb3n3Pace9pPn4/k5H5/fM7n8z7tua/7uZ/7PeekqpAkHfseN+kCJEmjYaBLUiMMdElqhIEuSY0w0CWpEQa6JDViooGeZEOSvUl2DNH2L5Js7253Jrl/CUqUpGNGJnkdepKXAPuAj1XVLy7ifm8HfqmqfndsxUnSMWaiM/SquhG4r/9YkjOTbEmyLcmXk5w9z10vATYtSZGSdIxYPukC5rEeeGtVfTvJ+cBVwAUHTiY5HTgDuGFC9UnSUemoCvQkJwEvBD6R5MDhn5zT7GLgn6vqkaWsTZKOdkdVoDO7BHR/VT1ngTYXA5ctTTmSdOw4qi5brKoHgbuTvA4gs849cD7JM4CfAb42oRIl6ag16csWNzEbzs9IsifJm4FLgTcnuQXYCVzUd5dLgKvLt4iUpINM9LJFSdLoHFVLLpKkwzexP4quXLmyVq1aNanhpQU99NBDnHjiiZMuQzrItm3b7q2qJ893bmKBvmrVKrZu3Tqp4aUF9Xo9ZmZmJl2GdJAk3znUOZdcJKkRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY042t4+Vxq5vvfWHyvfF0mT5gxdzauqRd9Ov/wzi76PNGkGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0YGOhJNiTZm2THgHbPS/JIkteOrjxJ0rCGmaFvBFYv1CDJMuB9wPUjqEmSdBgGBnpV3QjcN6DZ24FrgL2jKEqStHhH/JmiSU4Ffg24AHjegLZrgDUAU1NT9Hq9Ix1eGhufnzrWjOJDov8SuLyqHhn0YbxVtR5YDzA9PV0zMzMjGF4agy2b8fmpY80oAn0auLoL85XAq5Psr6pPjaBvSdKQjjjQq+qMA9tJNgKfMcwlaekNDPQkm4AZYGWSPcAVwAqAqlo31uokSUMbGOhVdcmwnVXVG4+oGknSYfOVopLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRAwM9yYYke5PsOMT5S5Pc2t2+muTc0ZcpSRpkmBn6RmD1AufvBl5aVecA7wXWj6AuSdIiLR/UoKpuTLJqgfNf7du9CThtBHVJkhZp1GvobwY+O+I+JUlDGDhDH1aSlzEb6C9eoM0aYA3A1NQUvV5vVMNLI+fzU8eakQR6knOAjwAXVtV/H6pdVa2nW2Ofnp6umZmZUQwvjd6Wzfj81LHmiJdckvws8Engt6vqziMvSZJ0OAbO0JNsAmaAlUn2AFcAKwCqah3wJ8CTgKuSAOyvqulxFSxJmt8wV7lcMuD8W4C3jKwiSdJh8ZWiktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRI/sIOmkpnPuez/HAwz9ekrFWrd081v5POWEFt1zxyrGOoeOLga5jygMP/5jdV75m7OP0er2xfwTduH9g6PjjkoskNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNGBjoSTYk2ZtkxyHOJ8lfJ9mV5NYk542+TEnSIMPM0DcCqxc4fyFwVndbA3z4yMuSJC3WwECvqhuB+xZochHwsZp1E/CEJE8dVYGSpOGM4iPoTgW+17e/pzv2n3MbJlnD7Cyeqakper3eCIbX8WYpnjf79u1bknH8HtAojSLQM8+xmq9hVa0H1gNMT0/XuD+zUQ3asnnsn/UJS/OZokv1WHT8GMVVLnuAp/ftnwbcM4J+JUmLMIpAvxZ4Q3e1ywuAB6rqoOUWSdJ4DVxySbIJmAFWJtkDXAGsAKiqdcB1wKuBXcAPgDeNq1hJ0qENDPSqumTA+QIuG1lFkqTD4itFJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1Ijlky5AWoyTn7mWZ3907dIM9tHxdn/yMwFeM95BdFwx0HVM+f4dV7L7yvGHYK/XY2ZmZqxjrFq7eaz96/gz1JJLktVJvpVkV5KDpkdJTkny6SS3JNmZ5E2jL1WStJCBgZ5kGfAh4ELgWcAlSZ41p9llwO1VdS4wA/xZkp8Yca2SpAUMM0N/PrCrqu6qqh8BVwMXzWlTwMlJApwE3AfsH2mlkqQFDRPopwLf69vf0x3r90HgmcA9wG3AO6rq0ZFUKEkayjB/FM08x2rO/quA7cAFwJnA55N8uaoefExHyRpgDcDU1BS9Xm+x9UpL8rzZt2/fkozj94BGaZhA3wM8vW//NGZn4v3eBFxZVQXsSnI3cDbwH/2Nqmo9sB5genq6xn0VgRq0ZfPYrz6BpbnKZakei44fwyy53AycleSM7g+dFwPXzmnzXeDlAEmmgGcAd42yUEnSwgbO0Ktqf5K3AdcDy4ANVbUzyVu78+uA9wIbk9zG7BLN5VV17xjrliTNMdQLi6rqOuC6OcfW9W3fA7xytKVJkhbD93KRpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRQwV6ktVJvpVkV5K1h2gzk2R7kp1JvjTaMiVJgywf1CDJMuBDwK8Ce4Cbk1xbVbf3tXkCcBWwuqq+m+QpY6pXknQIw8zQnw/sqqq7qupHwNXARXPa/Cbwyar6LkBV7R1tmZKkQQbO0IFTge/17e8Bzp/T5ueBFUl6wMnAX1XVx+Z2lGQNsAZgamqKXq93GCXreLdq7ealGWjLeMc5cQV+D2ikhgn0zHOs5unnucDLgROAryW5qarufMydqtYD6wGmp6drZmZm0QXr+LZ7ZmnGWbV2M7uvfM3SDCaNyDCBvgd4et/+acA987S5t6oeAh5KciNwLnAnkqQlMcwa+s3AWUnOSPITwMXAtXPa/CvwK0mWJ/lpZpdk7hhtqZKkhQycoVfV/iRvA64HlgEbqmpnkrd259dV1R1JtgC3Ao8CH6mqHeMsXJL0WMMsuVBV1wHXzTm2bs7++4H3j640SdJi+EpRSWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktSIod4+VzqWJfN9iuIQ93vf4tpXzf1kRmlpOUNX86pq0bcvfvGLi76PNGkGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRmdQLIpL8F/CdiQwuDbYSuHfSRUjzOL2qnjzfiYkFunQ0S7K1qqYnXYe0GC65SFIjDHRJaoSBLs1v/aQLkBbLNXRJaoQzdElqhIEuSY0w0CWpEQa6JirJR5I8awT97BtFPeOW5I1JnjbG/jcmee24+tfRzUDX2GXWvM+1qnpLVd2+1DVN0BuBsQW6jm8GusYiyaokdyS5Cvg68HdJtibZmeQ9fe16SaaTLOtmlzuS3Jbk97vzZybZkmRbki8nObs7fkaSryW5Ocl7B9RyUpIvJPl61/dFfTV+s/stYUeSjyd5RZKvJPl2kud37Z6Y5FNJbk1yU5JzuuPvTvKuvnF2dH0eeOx/2z3ezyU5oZs5TwMfT7I9yQmHqHd3kpXd9nSSXrf90u5+25N8I8nJ3Q/LDya5Pclm4CmH9z+mJhzOB+h68zboBqwCHgVe0O0/sfu6DOgB53T7PWZD7rnA5/vu/4Tu6xeAs7rt84Ebuu1rgTd025cB+xaoZTnw+G57JbALSFfjfuDZzE5utgEbunMXAZ/q7vMB4Ipu+wJge7f9buBdfePs6Po80O9zuuP/BPxW/+Md8G+3G1jZbU8DvW7708CLuu2Tusf168Dnu3/XpwH3A6+d9P+/t8ncnKFrnL5TVTd1269P8nXgG8AvAHPXze8Cfi7JB5KsBh5MchLwQuATSbYDfwM8tWv/ImBTt/0PA+oI8KdJbgX+DTgVmOrO3V1Vt1XVo8BO4AtVVcBtzAYzwIsPjFFVNwBPSnLKgDHvrqrt3fa2vr6OxFeAP0/ye8z+wNsPvATYVFWPVNU9wA0jGEfHqOWTLkBNewhml0eAdwHPq6r/SbIR+Kn+ht3xc4FXMTvjfj3wTuD+qnrOIfof9lVxlwJPBp5bVT9Osrtv/B/2tXu0b/9R/v/7I4cYez+PXbbsf0z9/T4CzLu8cgj9/f5fn1V1Zbes8mrgpiSv6KtFcoauJfF4ZsP9gSRTwIVzG3Rrxo+rqmuAPwbOq6oHgbuTvK5rky70YXa2enG3femA8U8B9nZh/jLg9EXWf+OBMZLMAPd2te0GzuuOnwecMURf3wdOHtBmN7NLUAC/ceBgkjO73ybeB2wFzu5qu7j7G8RTgZcN9YjUJGfoGruquiXJN5hd0riL2TCe61Tg7/uuhvmD7uulwIeT/BGwArgauAV4B/CPSd4BXDOghI8Dn06yFdgOfHORD+HdXW23Aj8Afqc7fg3whm456GbgziH62gisS/Iw8MtV9fA8bd7D7B+R/xD4977j7+x+ID0C3A58FvgRs+v6t3Xjf2lRj0xN8b1cJKkRLrlIUiNcclEzkjybg694+WFVnT+JegZJ8i8cvO5+eVVdP4l6dOxzyUWSGuGSiyQ1wkCXpEYY6JLUCANdkhrxvwKXf5Xt+hCtAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# To get known about the outliers.\n",
    "D1.boxplot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>main_sector</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8900000.00</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5000000.00</td>\n",
       "      <td>News, Search and Messaging</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>11999347.00</td>\n",
       "      <td>Others</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>15000000.00</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5000000.00</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38791</th>\n",
       "      <td>15000000.00</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38794</th>\n",
       "      <td>5026000.00</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38798</th>\n",
       "      <td>12000000.00</td>\n",
       "      <td>Cleantech / Semiconductors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38799</th>\n",
       "      <td>14219999.00</td>\n",
       "      <td>Cleantech / Semiconductors</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38802</th>\n",
       "      <td>8500000.00</td>\n",
       "      <td>Cleantech / Semiconductors</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12012 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       raised_amount_usd                              main_sector\n",
       "3             8900000.00  Social, Finance, Analytics, Advertising\n",
       "4             5000000.00               News, Search and Messaging\n",
       "17           11999347.00                                   Others\n",
       "20           15000000.00                            Manufacturing\n",
       "21            5000000.00                            Manufacturing\n",
       "...                  ...                                      ...\n",
       "38791        15000000.00  Social, Finance, Analytics, Advertising\n",
       "38794         5026000.00  Social, Finance, Analytics, Advertising\n",
       "38798        12000000.00               Cleantech / Semiconductors\n",
       "38799        14219999.00               Cleantech / Semiconductors\n",
       "38802         8500000.00               Cleantech / Semiconductors\n",
       "\n",
       "[12012 rows x 2 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# making a saprate dataframe for main_sector to get known the invested money in each sector and the total counts.\n",
    "D1_FT = D1[['raised_amount_usd','main_sector']] # taking 2 variable for data frame\n",
    "D1_FT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grouping rows by unique main sectors\n",
    "D1_FT = D1_FT.groupby('main_sector')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>Total_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Automotive &amp; Sports</th>\n",
       "      <td>1454104361.00</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cleantech / Semiconductors</th>\n",
       "      <td>21206628192.00</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Entertainment</th>\n",
       "      <td>5099197982.00</td>\n",
       "      <td>591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health</th>\n",
       "      <td>8211859357.00</td>\n",
       "      <td>909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>7258553378.00</td>\n",
       "      <td>799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>News, Search and Messaging</th>\n",
       "      <td>13959567428.00</td>\n",
       "      <td>1582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>26321007002.00</td>\n",
       "      <td>2950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Social, Finance, Analytics, Advertising</th>\n",
       "      <td>23807376964.00</td>\n",
       "      <td>2714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        raised_amount_usd            \n",
       "                                             Total_amount Total_count\n",
       "main_sector                                                          \n",
       "Automotive & Sports                         1454104361.00         167\n",
       "Cleantech / Semiconductors                 21206628192.00        2300\n",
       "Entertainment                               5099197982.00         591\n",
       "Health                                      8211859357.00         909\n",
       "Manufacturing                               7258553378.00         799\n",
       "News, Search and Messaging                 13959567428.00        1582\n",
       "Others                                     26321007002.00        2950\n",
       "Social, Finance, Analytics, Advertising    23807376964.00        2714"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting values of total amount and total count on raised amount in each sector.\n",
    "D1_FT = D1_FT.agg(['sum', 'count']).rename(columns={'sum':'Total_amount','count' : 'Total_count'})\n",
    "D1_FT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "      <th>main_sector</th>\n",
       "      <th>(raised_amount_usd, Total_amount)</th>\n",
       "      <th>(raised_amount_usd, Total_count)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/0xdata</td>\n",
       "      <td>8900000.00</td>\n",
       "      <td>H2O.ai</td>\n",
       "      <td>Analytics</td>\n",
       "      <td>USA</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "      <td>23807376964.00</td>\n",
       "      <td>2714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/1-mainstream</td>\n",
       "      <td>5000000.00</td>\n",
       "      <td>1 Mainstream</td>\n",
       "      <td>Apps</td>\n",
       "      <td>USA</td>\n",
       "      <td>News, Search and Messaging</td>\n",
       "      <td>13959567428.00</td>\n",
       "      <td>1582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/128-technology</td>\n",
       "      <td>11999347.00</td>\n",
       "      <td>128 Technology</td>\n",
       "      <td>Service Providers</td>\n",
       "      <td>USA</td>\n",
       "      <td>Others</td>\n",
       "      <td>26321007002.00</td>\n",
       "      <td>2950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/1366-technologies</td>\n",
       "      <td>15000000.00</td>\n",
       "      <td>1366 Technologies</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>USA</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>7258553378.00</td>\n",
       "      <td>799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/1366-technologies</td>\n",
       "      <td>5000000.00</td>\n",
       "      <td>1366 Technologies</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>USA</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>7258553378.00</td>\n",
       "      <td>799</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 company_permalink  raised_amount_usd               name  \\\n",
       "0             /organization/0xdata         8900000.00             H2O.ai   \n",
       "1       /organization/1-mainstream         5000000.00       1 Mainstream   \n",
       "2     /organization/128-technology        11999347.00     128 Technology   \n",
       "3  /organization/1366-technologies        15000000.00  1366 Technologies   \n",
       "4  /organization/1366-technologies         5000000.00  1366 Technologies   \n",
       "\n",
       "       category_list country_code                              main_sector  \\\n",
       "0          Analytics          USA  Social, Finance, Analytics, Advertising   \n",
       "1               Apps          USA               News, Search and Messaging   \n",
       "2  Service Providers          USA                                   Others   \n",
       "3      Manufacturing          USA                            Manufacturing   \n",
       "4      Manufacturing          USA                            Manufacturing   \n",
       "\n",
       "   (raised_amount_usd, Total_amount)  (raised_amount_usd, Total_count)  \n",
       "0                     23807376964.00                              2714  \n",
       "1                     13959567428.00                              1582  \n",
       "2                     26321007002.00                              2950  \n",
       "3                      7258553378.00                               799  \n",
       "4                      7258553378.00                               799  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merging \"D1_FT\" with \"D1\" to add extra information to our main database based on main sector. \n",
    "D1 = D1.merge(D1_FT, how='left', on ='main_sector')\n",
    "D1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>619.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8667573.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2870090.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6082165.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>10653380.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>15000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       raised_amount_usd\n",
       "count             619.00\n",
       "mean          8667573.99\n",
       "std           2870090.64\n",
       "min           5000000.00\n",
       "25%           6082165.00\n",
       "50%           8000000.00\n",
       "75%          10653380.00\n",
       "max          15000000.00"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# similarly for D2 as we did for D1.\n",
    "# making dataframe containing data specifically about GBR, for the defined raised amount range.\n",
    "D2 = master_frame[(master_frame['country_code'] == 'GBR') & \n",
    "             (master_frame['raised_amount_usd'] >= 5000000) & \n",
    "             (master_frame['raised_amount_usd'] <= 15000000)]\n",
    "\n",
    "D2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEECAYAAAA4Qc+SAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAPJElEQVR4nO3df6zddX3H8efLtm4MEKfVGwVHGWGim+DwKk6dXtBp0T/INjUwNsVpGhN0usSEbtmGxmTBmP1UsetcV11cyRzOocWiE48YlY1WCxRUbKBqw5KOMUAqUQvv/XG/3Q63t/ec255zT/vp85Gc3O+Pz/l83qc993U/93O/55xUFZKko9/jJl2AJGk0DHRJaoSBLkmNMNAlqREGuiQ1wkCXpEZMNNCTbEiyJ8mOIdr+RZLt3e3OJPcvQYmSdNTIJK9DT/JS4CHgY1X1S4u439uBX66q3x1bcZJ0lJnoDL2qbgTu6z+W5PQkW5JsS/LlJGfOc9eLgU1LUqQkHSWWT7qAeawH3lpV30lyLnAVcP7+k0lOBU4DbphQfZJ0RDqiAj3JCcCLgE8k2X/4p+Y0uwj456p6ZClrk6Qj3REV6MwuAd1fVc9doM1FwGVLU44kHT2OqMsWq+pB4O4krwPIrLP3n0/yTOBnga9NqERJOmJN+rLFTcyG8zOT7E7yZuAS4M1JbgFuBy7su8vFwNXlW0RK0gEmetmiJGl0jqglF0nSoZvYH0VXrlxZq1atmtTw0oL27t3L8ccfP+kypANs27bt3qp6ynznJhboq1atYuvWrZMaXlpQr9djZmZm0mVIB0jy3YOdc8lFkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1Igj7e1zpZHre2/9sfJ9kTRpztDVvKpa9O3Uyz+z6PtIk2agS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1IiBgZ5kQ5I9SXYMaPf8JI8kee3oypMkDWuYGfpGYPVCDZIsA94HXD+CmiRJh2BgoFfVjcB9A5q9HbgG2DOKoiRJi3fYnyma5GTg14HzgecPaLsGWAMwNTVFr9c73OGlsfH5qaPNKD4k+i+By6vqkUEfxltV64H1ANPT0zUzMzOC4aUx2LIZn5862owi0KeBq7swXwm8Osm+qvrUCPqWJA3psAO9qk7bv51kI/AZw1ySlt7AQE+yCZgBVibZDVwBrACoqnVjrU6SNLSBgV5VFw/bWVVdeljVSJIOma8UlaRGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0YGOhJNiTZk2THQc5fkuTW7vbVJGePvkxJ0iDDzNA3AqsXOH838LKqOgt4L7B+BHVJkhZp+aAGVXVjklULnP9q3+5NwCkjqEuStEijXkN/M/DZEfcpSRrCwBn6sJKcx2ygv2SBNmuANQBTU1P0er1RDS+NnM9PHW1GEuhJzgI+AlxQVf99sHZVtZ5ujX16erpmZmZGMbw0els24/NTR5vDXnJJ8nPAJ4Hfqao7D78kSdKhGDhDT7IJmAFWJtkNXAGsAKiqdcCfAE8GrkoCsK+qpsdVsCRpfsNc5XLxgPNvAd4ysookSYfEV4pKUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNWL5pAuQFuPs93yOBx7+yZKMtWrt5rH2f9JxK7jlileOdQwdWwx0HVUeePgn7LryNWMfp9frMTMzM9Yxxv0DQ8cel1wkqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjRgY6Ek2JNmTZMdBzifJXyfZmeTWJOeMvkxJ0iDDzNA3AqsXOH8BcEZ3WwN8+PDLkiQt1sBAr6obgfsWaHIh8LGadRPwxCRPG1WBkqThjOK9XE4Gvt+3v7s79p9zGyZZw+wsnqmpKXq93giG17FmKZ43Dz300JKM4/eARmkUgZ55jtV8DatqPbAeYHp6usb95kdq0JbNY3/TLFiaN+daqseiY8cornLZDTyjb/8U4J4R9CtJWoRRBPq1wBu6q11eCDxQVQcst0iSxmvgkkuSTcAMsDLJbuAKYAVAVa0DrgNeDewEfgi8aVzFSpIObmCgV9XFA84XcNnIKpIkHRJfKSpJjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaMVSgJ1md5NtJdiZZO8/5k5J8OsktSW5P8qbRlypJWsjAQE+yDPgQcAHwbODiJM+e0+wy4I6qOhuYAf4syeNHXKskaQHDzNBfAOysqruq6sfA1cCFc9oUcGKSACcA9wH7RlqpJGlBwwT6ycD3+/Z3d8f6fRB4FnAPcBvwjqp6dCQVSpKGsnyINpnnWM3ZfxWwHTgfOB34fJIvV9WDj+koWQOsAZiamqLX6y22Xh3jTnzWWp7z0QP+jDMeHx1v9yc+C3q948c7iI4pwwT6buAZffunMDsT7/cm4MqqKmBnkruBM4H/6G9UVeuB9QDT09M1MzNziGXrWPWDtVey68rXjH2cXq/HuJ+fq9ZuZuaN4x1Dx5ZhllxuBs5Iclr3h86LgGvntPke8HKAJFPAM4G7RlmoJGlhA2foVbUvyduA64FlwIaquj3JW7vz64D3AhuT3MbsEs3lVXXvGOuWJM0xzJILVXUdcN2cY+v6tu8BXjna0iRJi+ErRSWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJasRQgZ5kdZJvJ9mZZO1B2swk2Z7k9iRfGm2ZkqRBlg9qkGQZ8CHg14DdwM1Jrq2qO/raPBG4ClhdVd9L8tQx1StJOoiBgQ68ANhZVXcBJLkauBC4o6/NbwGfrKrvAVTVnlEXKu23au3mpRloy3jHOem4FWPtX8eeYQL9ZOD7ffu7gXPntPkFYEWSHnAi8FdV9bG5HSVZA6wBmJqaotfrHULJOpZtXH38koxz6Za9SzKW3wMapWECPfMcq3n6eR7wcuA44GtJbqqqOx9zp6r1wHqA6enpmpmZWXTB0pLYshmfnzraDBPou4Fn9O2fAtwzT5t7q2ovsDfJjcDZwJ1IkpbEMFe53AyckeS0JI8HLgKundPmX4FfTbI8yc8wuyTzzdGWKklayMAZelXtS/I24HpgGbChqm5P8tbu/Lqq+maSLcCtwKPAR6pqxzgLlyQ91jBLLlTVdcB1c46tm7P/fuD9oytNkrQYvlJUkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDViqLfPlY5myXyfojjE/d63uPZVcz+ZUVpaztDVvKpa9O2LX/ziou8jTZqBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWpEJvWCiCT/BXx3IoNLg60E7p10EdI8Tq2qp8x3YmKBLh3JkmytqulJ1yEthksuktQIA12SGmGgS/NbP+kCpMVyDV2SGuEMXZIaYaBLUiMMdElqhIGuiUrykSTPHkE/D42innFLcmmSp4+x/41JXjuu/nVkM9A1dpk173Otqt5SVXcsdU0TdCkwtkDXsc1A11gkWZXkm0muAr4O/F2SrUluT/Kevna9JNNJlnWzyx1Jbkvy+93505NsSbItyZeTnNkdPy3J15LcnOS9A2o5IckXkny96/vCvhq/1f2WsCPJx5O8IslXknwnyQu6dk9K8qkktya5KclZ3fF3J3lX3zg7uj73P/a/7R7v55Ic182cp4GPJ9me5LiD1LsrycpuezpJr9t+WXe/7Um+keTE7oflB5PckWQz8NRD+x9TEw7lA3S9eRt0A1YBjwIv7Paf1H1dBvSAs7r9HrMh9zzg8333f2L39QvAGd32ucAN3fa1wBu67cuAhxaoZTnwhG57JbATSFfjPuA5zE5utgEbunMXAp/q7vMB4Ipu+3xge7f9buBdfePs6Prc3+9zu+P/BPx2/+Md8G+3C1jZbU8DvW7708CLu+0Tusf1G8Dnu3/XpwP3A6+d9P+/t8ncnKFrnL5bVTd1269P8nXgG8AvAnPXze8Cfj7JB5KsBh5McgLwIuATSbYDfwM8rWv/YmBTt/0PA+oI8KdJbgX+DTgZmOrO3V1Vt1XVo8DtwBeqqoDbmA1mgJfsH6OqbgCenOSkAWPeXVXbu+1tfX0djq8Af57k95j9gbcPeCmwqaoeqap7gBtGMI6OUssnXYCathdml0eAdwHPr6r/SbIR+On+ht3xs4FXMTvjfj3wTuD+qnruQfof9lVxlwBPAZ5XVT9Jsqtv/B/1tXu0b/9R/v/7IwcZex+PXbbsf0z9/T4CzLu8chD9/f5fn1V1Zbes8mrgpiSv6KtFcoauJfEEZsP9gSRTwAVzG3Rrxo+rqmuAPwbOqaoHgbuTvK5rky70YXa2elG3fcmA8U8C9nRhfh5w6iLrv3H/GElmgHu72nYB53THzwFOG6KvHwAnDmizi9klKIDf3H8wyendbxPvA7YCZ3a1XdT9DeJpwHlDPSI1yRm6xq6qbknyDWaXNO5iNoznOhn4+76rYf6g+3oJ8OEkfwSsAK4GbgHeAfxjkncA1wwo4ePAp5NsBbYD31rkQ3h3V9utwA+BN3bHrwHe0C0H3QzcOURfG4F1SR4GfqWqHp6nzXuY/SPyHwL/3nf8nd0PpEeAO4DPAj9mdl3/tm78Ly3qkakpvpeLJDXCJRdJaoRLLmpGkudw4BUvP6qqcydRzyBJ/oUD190vr6rrJ1GPjn4uuUhSI1xykaRGGOiS1AgDXZIaYaBLUiP+F9HWb0w7ajbQAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# To get known about the outliers.\n",
    "D2.boxplot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "      <th>main_sector</th>\n",
       "      <th>(raised_amount_usd, Total_amount)</th>\n",
       "      <th>(raised_amount_usd, Total_count)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/365scores</td>\n",
       "      <td>5500000.00</td>\n",
       "      <td>365Scores</td>\n",
       "      <td>Android</td>\n",
       "      <td>GBR</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "      <td>1089404014.00</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/7digital</td>\n",
       "      <td>8468328.00</td>\n",
       "      <td>7digital</td>\n",
       "      <td>Content Creators</td>\n",
       "      <td>GBR</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>482784687.00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/7digital</td>\n",
       "      <td>10000000.00</td>\n",
       "      <td>7digital</td>\n",
       "      <td>Content Creators</td>\n",
       "      <td>GBR</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>482784687.00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/90min</td>\n",
       "      <td>15000000.00</td>\n",
       "      <td>90min</td>\n",
       "      <td>Media</td>\n",
       "      <td>GBR</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>482784687.00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/90min</td>\n",
       "      <td>5800000.00</td>\n",
       "      <td>90min</td>\n",
       "      <td>Media</td>\n",
       "      <td>GBR</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>482784687.00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         company_permalink  raised_amount_usd       name     category_list  \\\n",
       "0  /organization/365scores         5500000.00  365Scores           Android   \n",
       "1   /organization/7digital         8468328.00   7digital  Content Creators   \n",
       "2   /organization/7digital        10000000.00   7digital  Content Creators   \n",
       "3      /organization/90min        15000000.00      90min             Media   \n",
       "4      /organization/90min         5800000.00      90min             Media   \n",
       "\n",
       "  country_code                              main_sector  \\\n",
       "0          GBR  Social, Finance, Analytics, Advertising   \n",
       "1          GBR                            Entertainment   \n",
       "2          GBR                            Entertainment   \n",
       "3          GBR                            Entertainment   \n",
       "4          GBR                            Entertainment   \n",
       "\n",
       "   (raised_amount_usd, Total_amount)  (raised_amount_usd, Total_count)  \n",
       "0                      1089404014.00                               133  \n",
       "1                       482784687.00                                56  \n",
       "2                       482784687.00                                56  \n",
       "3                       482784687.00                                56  \n",
       "4                       482784687.00                                56  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "D2_FT = D2[['raised_amount_usd','main_sector']].groupby('main_sector').agg(['sum', 'count']).rename(\n",
    "    columns={'sum':'Total_amount','count' : 'Total_count'})\n",
    "D2 = D2.merge(D2_FT, how='left', on ='main_sector')\n",
    "D2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>328.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>8992510.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3219811.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>5000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6000000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8700000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>10555000.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>15000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       raised_amount_usd\n",
       "count             328.00\n",
       "mean          8992510.98\n",
       "std           3219811.35\n",
       "min           5000000.00\n",
       "25%           6000000.00\n",
       "50%           8700000.00\n",
       "75%          10555000.00\n",
       "max          15000000.00"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# same as for D3\n",
    "# making dataframe containing data specifically about IND, for the defined raised amount range.\n",
    "D3 = master_frame[(master_frame['country_code'] == 'IND') & \n",
    "             (master_frame['raised_amount_usd'] >= 5000000) & \n",
    "             (master_frame['raised_amount_usd'] <= 15000000)]\n",
    "\n",
    "D3.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEECAYAAAA4Qc+SAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAAPNklEQVR4nO3df6zddX3H8efLttsYIE6rNwqOMsJEN8HhVZw6vaLTon+QbWpgbE6naUzQ6RITumUbGpMFY/ZTxa5zXXVxJXM4hxaLTjxiVDZaLdCCYgNVG5Z0jAEWiVp474/77Xa4vb3n3Pace9pPn4/k5H5/fM7n8z7tua/7uZ97vuekqpAkHfseN+kCJEmjYaBLUiMMdElqhIEuSY0w0CWpEQa6JDViooGeZEOSvUl2DNH2L5Js7253Jrl/CUqUpGNGJvk69CQvAfYBH6uqX1zE/d4O/FJV/e7YipOkY8xEZ+hVdSNwX/+xJGcm2ZJkW5IvJzl7nrteAmxakiIl6RixfNIFzGM98Naq+naS84GrgAsOnExyOnAGcMOE6pOko9JRFehJTgJeCHwiyYHDPzmn2cXAP1fVI0tZmyQd7Y6qQGd2Cej+qnrOAm0uBi5bmnIk6dhxVL1ssaoeBO5O8jqAzDr3wPkkzwB+BvjahEqUpKPWpF+2uInZcH5Gkj1J3gxcCrw5yS3ATuCivrtcAlxdvkWkJB1koi9blCSNzlG15CJJOnwT+6PoypUra9WqVZMaXlrQQw89xIknnjjpMqSDbNu27d6qevJ85yYW6KtWrWLr1q2TGl5aUK/XY2ZmZtJlSAdJ8p1DnXPJRZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktSIo+3tc6WR63tv/bHyfZE0ac7Q1byqWvTt9Ms/s+j7SJNmoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktSIgYGeZEOSvUl2DGj3vCSPJHnt6MqTJA1rmBn6RmD1Qg2SLAPeB1w/gpokSYdhYKBX1Y3AfQOavR24Btg7iqIkSYt3xJ8pmuRU4NeAC4DnDWi7BlgDMDU1Ra/XO9LhpbHx+aljzSg+JPovgcur6pFBH8ZbVeuB9QDT09M1MzMzguGlMdiyGZ+fOtaMItCngau7MF8JvDrJ/qr61Aj6liQN6YgDvarOOLCdZCPwGcNckpbewEBPsgmYAVYm2QNcAawAqKp1Y61OkjS0gYFeVZcM21lVvfGIqpEkHTavFJWkRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNGBjoSTYk2ZtkxyHOX5rk1u721STnjr5MSdIgw8zQNwKrFzh/N/DSqjoHeC+wfgR1SZIWafmgBlV1Y5JVC5z/at/uTcBpI6hLkrRIo15DfzPw2RH3KUkawsAZ+rCSvIzZQH/xAm3WAGsApqam6PV6oxpeGjmfnzrWjCTQk5wDfAS4sKr++1Dtqmo93Rr79PR0zczMjGJ4afS2bMbnp441R7zkkuRngU8Cv11Vdx55SZKkwzFwhp5kEzADrEyyB7gCWAFQVeuAPwGeBFyVBGB/VU2Pq2BJ0vyGeZXLJQPOvwV4y8gqkiQdFq8UlaRGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRyyddgLQY577nczzw8I+XZKxVazePtf9TTljBLVe8cqxj6PhioOuY8sDDP2b3la8Z+zi9Xo+ZmZmxjjHuHxg6/rjkIkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhoxMNCTbEiyN8mOQ5xPkr9OsivJrUnOG32ZkqRBhpmhbwRWL3D+QuCs7rYG+PCRlyVJWqyBgV5VNwL3LdDkIuBjNesm4AlJnjqqAiVJwxnFlaKnAt/r29/THfvPuQ2TrGF2Fs/U1BS9Xm8Ew+t4sxTPm3379i3JOH4PaJRGEeiZ51jN17Cq1gPrAaanp2vcl1arQVs2j/2SfFiaS/+X6rHo+DGKV7nsAZ7et38acM8I+pUkLcIoAv1a4A3dq11eADxQVQctt0iSxmvgkkuSTcAMsDLJHuAKYAVAVa0DrgNeDewCfgC8aVzFSpIObWCgV9UlA84XcNnIKpIkHRavFJWkRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMGfki0dDQ5+ZlrefZH1y7NYB8db/cnPxPgNeMdRMcVA13HlO/fcSW7rxx/CPZ6PWZmZsY6xqq1m8fav44/LrlIUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGjFUoCdZneRbSXYlOegyvSSnJPl0kluS7EzyptGXKklayMBAT7IM+BBwIfAs4JIkz5rT7DLg9qo6F5gB/izJT4y4VknSAoaZoT8f2FVVd1XVj4CrgYvmtCng5CQBTgLuA/aPtFJJ0oKGCfRTge/17e/pjvX7IPBM4B7gNuAdVfXoSCqUJA1lmDfnyjzHas7+q4DtwAXAmcDnk3y5qh58TEfJGmANwNTUFL1eb7H1SkvyvNm3b9+SjOP3gEZpmEDfAzy9b/80Zmfi/d4EXFlVBexKcjdwNvAf/Y2qaj2wHmB6errG/W52atCWzWN/F0RYmndbXKrHouPHMEsuNwNnJTmj+0PnxcC1c9p8F3g5QJIp4BnAXaMsVJK0sIEz9Kran+RtwPXAMmBDVe1M8tbu/DrgvcDGJLcxu0RzeVXdO8a6JUlzDPUBF1V1HXDdnGPr+rbvAV452tIkSYvhlaKS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDXCQJekRhjoktQIA12SGmGgS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0w0CWpEQa6JDViqEBPsjrJt5LsSrL2EG1mkmxPsjPJl0ZbpiRpkOWDGiRZBnwI+FVgD3Bzkmur6va+Nk8ArgJWV9V3kzxlTPVKkg5hmBn684FdVXVXVf0IuBq4aE6b3wQ+WVXfBaiqvaMtU5I0yMAZOnAq8L2+/T3A+XPa/DywIkkPOBn4q6r62NyOkqwB1gBMTU3R6/UOo2Qd71at3bw0A20Z7zgnrsDvAY3UMIGeeY7VPP08F3g5cALwtSQ3VdWdj7lT1XpgPcD09HTNzMwsumAd33bPLM04q9ZuZveVr1mawaQRGSbQ9wBP79s/Dbhnnjb3VtVDwENJbgTOBe5EkrQkhllDvxk4K8kZSX4CuBi4dk6bfwV+JcnyJD/N7JLMHaMtVZK0kIEz9Kran+RtwPXAMmBDVe1M8tbu/LqquiPJFuBW4FHgI1W1Y5yFS5Iea5glF6rqOuC6OcfWzdl/P/D+0ZUmSVoMrxSVpEYY6JLUCANdkhphoEtSIwx0SWqEgS5JjTDQJakRBrokNcJAl6RGGOiS1AgDXZIaYaBLUiMMdElqhIEuSY0Y6u1zpWNZMt+nKA5xv/ctrn3V3E9mlJaWM3Q1r6oWffviF7+46PtIk2agS1IjDHRJaoSBLkmNMNAlqREGuiQ1wkCXpEYY6JLUCANdkhqRSV0QkeS/gO9MZHBpsJXAvZMuQprH6VX15PlOTCzQpaNZkq1VNT3pOqTFcMlFkhphoEtSIwx0aX7rJ12AtFiuoUtSI5yhS1IjDHRJaoSBLkmNMNA1UUk+kuRZI+hn3yjqGbckb0zytDH2vzHJa8fVv45uBrrGLrPmfa5V1Vuq6valrmmC3giMLdB1fDPQNRZJViW5I8lVwNeBv0uyNcnOJO/pa9dLMp1kWTe73JHktiS/350/M8mWJNuSfDnJ2d3xM5J8LcnNSd47oJaTknwhyde7vi/qq/Gb3W8JO5J8PMkrknwlybeTPL9r98Qkn0pya5KbkpzTHX93knf1jbOj6/PAY//b7vF+LskJ3cx5Gvh4ku1JTjhEvbuTrOy2p5P0uu2XdvfbnuQbSU7uflh+MMntSTYDTzm8/zE14XA+QNebt0E3YBXwKPCCbv+J3ddlQA84p9vvMRtyzwU+33f/J3RfvwCc1W2fD9zQbV8LvKHbvgzYt0Aty4HHd9srgV1Auhr3A89mdnKzDdjQnbsI+FR3nw8AV3TbFwDbu+13A+/qG2dH1+eBfp/THf8n4Lf6H++Af7vdwMpuexroddufBl7UbZ/UPa5fBz7f/bs+DbgfeO2k//+9TebmDF3j9J2quqnbfn2SrwPfAH4BmLtufhfwc0k+kGQ18GCSk4AXAp9Ish34G+CpXfsXAZu67X8YUEeAP01yK/BvwKnAVHfu7qq6raoeBXYCX6iqAm5jNpgBXnxgjKq6AXhSklMGjHl3VW3vtrf19XUkvgL8eZLfY/YH3n7gJcCmqnqkqu4BbhjBODpGLZ90AWraQzC7PAK8C3heVf1Pko3AT/U37I6fC7yK2Rn364F3AvdX1XMO0f+wV8VdCjwZeG5V/TjJ7r7xf9jX7tG+/Uf5/++PHGLs/Tx22bL/MfX3+wgw7/LKIfT3+399VtWV3bLKq4GbkryirxbJGbqWxOOZDfcHkkwBF85t0K0ZP66qrgH+GDivqh4E7k7yuq5NutCH2dnqxd32pQPGPwXY24X5y4DTF1n/jQfGSDID3NvVths4rzt+HnDGEH19Hzh5QJvdzC5BAfzGgYNJzux+m3gfsBU4u6vt4u5vEE8FXjbUI1KTnKFr7KrqliTfYHZJ4y5mw3iuU4G/73s1zB90Xy8FPpzkj4AVwNXALcA7gH9M8g7gmgElfBz4dJKtwHbgm4t8CO/uarsV+AHwO93xa4A3dMtBNwN3DtHXRmBdkoeBX66qh+dp8x5m/4j8h8C/9x1/Z/cD6RHgduCzwI+YXde/rRv/S4t6ZGqK7+UiSY1wyUWSGuGSi5qR5Nkc/IqXH1bV+ZOoZ5Ak/8LB6+6XV9X1k6hHxz6XXCSpES65SFIjDHRJaoSBLkmNMNAlqRH/CzdQf5XX3oKLAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# To get known about the outliers.\n",
    "D3.boxplot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>company_permalink</th>\n",
       "      <th>raised_amount_usd</th>\n",
       "      <th>name</th>\n",
       "      <th>category_list</th>\n",
       "      <th>country_code</th>\n",
       "      <th>main_sector</th>\n",
       "      <th>(raised_amount_usd, Total_amount)</th>\n",
       "      <th>(raised_amount_usd, Total_count)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/organization/-fame</td>\n",
       "      <td>10000000.00</td>\n",
       "      <td>#fame</td>\n",
       "      <td>Media</td>\n",
       "      <td>IND</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>280830000.00</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/organization/21diamonds-india</td>\n",
       "      <td>6369507.00</td>\n",
       "      <td>21Diamonds</td>\n",
       "      <td>E-Commerce</td>\n",
       "      <td>IND</td>\n",
       "      <td>Others</td>\n",
       "      <td>1013409507.00</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/organization/a-little-world</td>\n",
       "      <td>6410000.00</td>\n",
       "      <td>A LITTLE WORLD</td>\n",
       "      <td>Finance</td>\n",
       "      <td>IND</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "      <td>550549550.00</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/organization/adlabs-imagica</td>\n",
       "      <td>8180000.00</td>\n",
       "      <td>Adlabs Imagica</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>IND</td>\n",
       "      <td>Entertainment</td>\n",
       "      <td>280830000.00</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/organization/agile</td>\n",
       "      <td>5740000.00</td>\n",
       "      <td>Agile</td>\n",
       "      <td>Finance</td>\n",
       "      <td>IND</td>\n",
       "      <td>Social, Finance, Analytics, Advertising</td>\n",
       "      <td>550549550.00</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                company_permalink  raised_amount_usd            name  \\\n",
       "0             /organization/-fame        10000000.00           #fame   \n",
       "1  /organization/21diamonds-india         6369507.00      21Diamonds   \n",
       "2    /organization/a-little-world         6410000.00  A LITTLE WORLD   \n",
       "3    /organization/adlabs-imagica         8180000.00  Adlabs Imagica   \n",
       "4             /organization/agile         5740000.00           Agile   \n",
       "\n",
       "   category_list country_code                              main_sector  \\\n",
       "0          Media          IND                            Entertainment   \n",
       "1     E-Commerce          IND                                   Others   \n",
       "2        Finance          IND  Social, Finance, Analytics, Advertising   \n",
       "3  Entertainment          IND                            Entertainment   \n",
       "4        Finance          IND  Social, Finance, Analytics, Advertising   \n",
       "\n",
       "   (raised_amount_usd, Total_amount)  (raised_amount_usd, Total_count)  \n",
       "0                       280830000.00                                33  \n",
       "1                      1013409507.00                               110  \n",
       "2                       550549550.00                                60  \n",
       "3                       280830000.00                                33  \n",
       "4                       550549550.00                                60  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "D3_FT = D3[['raised_amount_usd','main_sector']].groupby('main_sector').agg(['sum', 'count']).rename(\n",
    "    columns={'sum':'Total_amount','count' : 'Total_count'})\n",
    "\n",
    "D3 = D3.merge(D3_FT, how='left', on ='main_sector')\n",
    "D3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sector-wise Investment Analysis For D1, D2, D3 \n",
    "Analyse the below points :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of investments(count) in USA: 12012\n",
      "Total number of investments(count) in GBR: 619\n",
      "Total number of investments(count) in IND: 328\n"
     ]
    }
   ],
   "source": [
    "#Total number of investments (total count)\n",
    "print(\"Total number of investments(count) in USA:\",D1.raised_amount_usd.count())\n",
    "print(\"Total number of investments(count) in GBR:\",D2.raised_amount_usd.count())\n",
    "print(\"Total number of investments(count) in IND:\",D3.raised_amount_usd.count())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total amount of investment in USA: $ 107318294664.0\n",
      "Total amount of investment in GBR: $ 5365228300.0\n",
      "Total amount of investment in IND: $ 2949543602.0\n"
     ]
    }
   ],
   "source": [
    "#Total amount of investment (total amount) \n",
    "print(\"Total amount of investment in USA: $\", round(D1.raised_amount_usd.sum(), 2))\n",
    "print(\"Total amount of investment in GBR: $\",round(D2.raised_amount_usd.sum(), 2))\n",
    "print(\"Total amount of investment in IND: $\",round(D3.raised_amount_usd.sum(), 2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>Total_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Automotive &amp; Sports</th>\n",
       "      <td>1454104361.00</td>\n",
       "      <td>167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cleantech / Semiconductors</th>\n",
       "      <td>21206628192.00</td>\n",
       "      <td>2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Entertainment</th>\n",
       "      <td>5099197982.00</td>\n",
       "      <td>591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health</th>\n",
       "      <td>8211859357.00</td>\n",
       "      <td>909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>7258553378.00</td>\n",
       "      <td>799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>News, Search and Messaging</th>\n",
       "      <td>13959567428.00</td>\n",
       "      <td>1582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>26321007002.00</td>\n",
       "      <td>2950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Social, Finance, Analytics, Advertising</th>\n",
       "      <td>23807376964.00</td>\n",
       "      <td>2714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        raised_amount_usd            \n",
       "                                             Total_amount Total_count\n",
       "main_sector                                                          \n",
       "Automotive & Sports                         1454104361.00         167\n",
       "Cleantech / Semiconductors                 21206628192.00        2300\n",
       "Entertainment                               5099197982.00         591\n",
       "Health                                      8211859357.00         909\n",
       "Manufacturing                               7258553378.00         799\n",
       "News, Search and Messaging                 13959567428.00        1582\n",
       "Others                                     26321007002.00        2950\n",
       "Social, Finance, Analytics, Advertising    23807376964.00        2714"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking into D1_FT dataframe\n",
    "#to get known about Top sector, second-top, third-top sector for D1 dataframe (based on count of investments)\n",
    "#and Number of investments in the top, second-top, third-top sector in D1 dataframe.\n",
    "D1_FT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>Total_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Automotive &amp; Sports</th>\n",
       "      <td>167051565.00</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cleantech / Semiconductors</th>\n",
       "      <td>1150139665.00</td>\n",
       "      <td>128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Entertainment</th>\n",
       "      <td>482784687.00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health</th>\n",
       "      <td>214537510.00</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>361940335.00</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>News, Search and Messaging</th>\n",
       "      <td>615746235.00</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>1283624289.00</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Social, Finance, Analytics, Advertising</th>\n",
       "      <td>1089404014.00</td>\n",
       "      <td>133</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        raised_amount_usd            \n",
       "                                             Total_amount Total_count\n",
       "main_sector                                                          \n",
       "Automotive & Sports                          167051565.00          16\n",
       "Cleantech / Semiconductors                  1150139665.00         128\n",
       "Entertainment                                482784687.00          56\n",
       "Health                                       214537510.00          24\n",
       "Manufacturing                                361940335.00          42\n",
       "News, Search and Messaging                   615746235.00          73\n",
       "Others                                      1283624289.00         147\n",
       "Social, Finance, Analytics, Advertising     1089404014.00         133"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking into D2_FT dataframe\n",
    "#to get known about Top sector, second-top, third-top for D2 dataframe (based on count of investments)\n",
    "#Number of investments in the top, second-top, third-top sector in D2 dataframe.\n",
    "D2_FT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Total_amount</th>\n",
       "      <th>Total_count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_sector</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Automotive &amp; Sports</th>\n",
       "      <td>136900000.00</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cleantech / Semiconductors</th>\n",
       "      <td>165380000.00</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Entertainment</th>\n",
       "      <td>280830000.00</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health</th>\n",
       "      <td>167740000.00</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>200900000.00</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>News, Search and Messaging</th>\n",
       "      <td>433834545.00</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>1013409507.00</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Social, Finance, Analytics, Advertising</th>\n",
       "      <td>550549550.00</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        raised_amount_usd            \n",
       "                                             Total_amount Total_count\n",
       "main_sector                                                          \n",
       "Automotive & Sports                          136900000.00          13\n",
       "Cleantech / Semiconductors                   165380000.00          20\n",
       "Entertainment                                280830000.00          33\n",
       "Health                                       167740000.00          19\n",
       "Manufacturing                                200900000.00          21\n",
       "News, Search and Messaging                   433834545.00          52\n",
       "Others                                      1013409507.00         110\n",
       "Social, Finance, Analytics, Advertising      550549550.00          60"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Looking into D3_FT dataframe\n",
    "#to get known about Top sector, second-top, third-top for D2 dataframe (based on count of investments)\n",
    "#Number of investments in the top, second-top, third-top sector in D3 dataframe.\n",
    "D3_FT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/virtustream</th>\n",
       "      <td>64300000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           raised_amount_usd\n",
       "company_permalink                           \n",
       "/organization/virtustream        64300000.00"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the top sector in USA , which company received the highest investment?\n",
    "company = D1[D1['main_sector']=='Others']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1) # to see only the top one entity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/shotspotter</th>\n",
       "      <td>67933006.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           raised_amount_usd\n",
       "company_permalink                           \n",
       "/organization/shotspotter        67933006.00"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the second top sector in USA , which company received the highest investment?\n",
    "company = D1[D1['main_sector']=='Social, Finance, Analytics, Advertising']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/electric-cloud</th>\n",
       "      <td>37000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              raised_amount_usd\n",
       "company_permalink                              \n",
       "/organization/electric-cloud        37000000.00"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the top sector in GBR , which company received the highest investment?\n",
    "company = D2[D2['main_sector']=='Others']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/celltick-technologies</th>\n",
       "      <td>37500000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     raised_amount_usd\n",
       "company_permalink                                     \n",
       "/organization/celltick-technologies        37500000.00"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the second top sector in GBR , which company received the highest investment?\n",
    "company = D2[D2['main_sector']=='Social, Finance, Analytics, Advertising']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/firstcry-com</th>\n",
       "      <td>39000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            raised_amount_usd\n",
       "company_permalink                            \n",
       "/organization/firstcry-com        39000000.00"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the top sector in IND , which company received the highest investment?\n",
    "company = D3[D3['main_sector']=='Others']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>raised_amount_usd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>company_permalink</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>/organization/gupshup-technology-india-pvt-ltd</th>\n",
       "      <td>33000000.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                raised_amount_usd\n",
       "company_permalink                                                \n",
       "/organization/gupshup-technology-india-pvt-ltd        33000000.00"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#For the second top sector in IND , which company received the highest investment?\n",
    "company = D3[D3['main_sector']=='News, Search and Messaging']\n",
    "company = company.pivot_table(values = 'raised_amount_usd', index = 'company_permalink', aggfunc = 'sum')\n",
    "company = company.sort_values(by = 'raised_amount_usd', ascending = False).head()\n",
    "company.head(1) # to see only the top entity. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysis Result :\n",
    "#### Based on the data analysis performed, SparksFunds should invest in -\n",
    "\n",
    "Funding type - **Venture**.\n",
    "\n",
    "Countries - **USA**, **Britain** & **India** respectively.\n",
    "\n",
    "Top two sectors to invest, are - **Others** & **Social, Finance, Analytics, Advertising**."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}