CC.1 DataScientist.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Practical Project Crowberry Data Scientist\n",
    "21.01.2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pd.set_option('display.max_columns',None)\n",
    "df = pd.read_excel(\"Data Scientist - Project.xlsx\",header=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is based on the table with 8894 startup records and 45 property columns. Final startups should be by the following criteria: (There are probably more, depending on analytical results WIP) <br>\n",
    "1. HQ Country are the Nordics (Denmark, Sweden, Norway, Finland, Iceland) with the direct consequence of HQ Region Europe\n",
    "2. Growth stage: Pre-seed stage, Seed stage and A-Round (could be measured/obtained by the columns GROWTH STAGE and LAST ROUND)\n",
    "3. Funding: If the company was already funded, the last funding (LAST FUNDING DATE) should not be older than 4 years. Either the startups is already independent or not active/ early in tech\n",
    "\n",
    "There are three more conventions Crowberry focusses on:\n",
    "- early in tech\n",
    "- company first\n",
    "- Opportunity in Inclusivity\n",
    "\n",
    "These perspectives will be considered in the later and more detailed stage of this project."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. HQ Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = df['HQ COUNTRY'].unique()\n",
    "nordics = ['Sweden', 'Norway', 'Iceland', 'Denmark', 'Finland']\n",
    "df_filtered = df[df['HQ COUNTRY'].isin(nordics)]\n",
    "\n",
    "df_filtered.reset_index();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Orginal table is reduced to a size if 8698 records by setting HQ COUNTRY to the nordics. Recheck of the new table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Sweden', 'Finland', 'Denmark', 'Iceland', 'Norway'], dtype=object)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered['HQ COUNTRY'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### CHECK :-)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Transform column formats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 VALUATION and VALUATION (EUR)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Function to set average value from valuation interval (min + max)/2\n",
    "\n",
    "def avg_valuation(cellValue):\n",
    "    val = cellValue.split('-')\n",
    "    return (float(val[1])+ float(val[0]))/2\n",
    "\n",
    "\n",
    "#Parse both valuation columns and create maximum valuation column\n",
    "\n",
    "valuationColumns = ['VALUATION', 'VALUATION (EUR)']\n",
    "\n",
    "for column in valuationColumns:\n",
    "    df_filtered.loc[:,column + ' AVG'] = df_filtered[column]\n",
    "    df_filtered.loc[:,column + ' AVG'] = [avg_valuation(cell) if isinstance(cell, str) \n",
    "                                else cell for cell in df_filtered['VALUATION AVG']]\n",
    "\n",
    "#df_filtered['VALUATION MAX']   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 LAST FUNDING DATE, FIRST FUNDING DATE and VALUATION DATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Parsing and transforming date columns LAST FUNDING DATE, FIRST FUNDING DATE and VALUATION DATE to real date format\n",
    "\n",
    "def parse_year_to_date(year):\n",
    "    return '1/'+ year\n",
    "\n",
    "list_of_datecolumns = ['LAST FUNDING DATE', 'FIRST FUNDING DATE', 'VALUATION DATE']\n",
    "\n",
    "for column in list_of_datecolumns:\n",
    "    df_filtered.loc[:,column] = df_filtered[column].astype(str)\n",
    "    df_filtered.loc[:,column] = [parse_year_to_date(x) if len(x)==4 else x for x in df_filtered[column]]\n",
    "    df_filtered.loc[:,column] = pd.to_datetime(df_filtered[column])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3  REVENUE as list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_238123/1660686734.py:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_filtered['REVENUE AS LIST'] = [convert_to_list_of_numbers(cell) if isinstance(cell, str) else [0] for cell in df_filtered['EACH ROUND AMOUNT']]\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0                                  [0, 0, 0]\n",
       "1                              [0, 1.7, 0.6]\n",
       "2       [11.5, 0, 0.9, 2.3, 0.05, 2.5, 0.05]\n",
       "3                                     [0, 0]\n",
       "4                                        [0]\n",
       "                        ...                 \n",
       "8889                                     [0]\n",
       "8890                                     [0]\n",
       "8891                                     [0]\n",
       "8892                                     [0]\n",
       "8893                                     [0]\n",
       "Name: REVENUE AS LIST, Length: 8698, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#function that takes in a value from a cell (string) and converts it to a list of numbers\n",
    "def convert_to_list_of_numbers(cellValue):\n",
    "    cellValueAsList = cellValue.split(';')\n",
    "    return [float(x) if x != 'n/a' else 0 for x in cellValueAsList]\n",
    "\n",
    "# Convert all cells of the \"each round amount\" column to a list with the function above\n",
    "# if there is something to convert, otherwise create a list with just a 0 in it␣ ↪for that cell\n",
    "\n",
    "df_filtered['REVENUE AS LIST'] = [convert_to_list_of_numbers(cell) if isinstance(cell, str) else [0] for cell in df_filtered['EACH ROUND AMOUNT']]\n",
    "\n",
    "df_filtered['REVENUE AS LIST']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_238123/281430619.py:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_filtered['REVENUE AS LIST'] = df_filtered['REVENUE AS LIST'].apply(lambda cell: None if len(cell)==1 else check_for_nulllist(cell))\n"
     ]
    }
   ],
   "source": [
    "#Function to check if Revenue List is 0. Maybe just add to the step before, but good for now.\n",
    "\n",
    "def check_for_nulllist(cellValue):\n",
    "    return (None if all(v == 0 for v in cellValue) is True else cellValue)\n",
    "\n",
    "# To do: Get rid of apply and lambda, do it the easy way\n",
    "df_filtered['REVENUE AS LIST'] = df_filtered['REVENUE AS LIST'].apply(lambda cell: None if len(cell)==1 else check_for_nulllist(cell))\n",
    "\n",
    "#df_filtered['REVENUE AS LIST'].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 Make categories for VALUATION AVG value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: I thought this might help when we do evaluations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_238123/4174041880.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  df_filtered['VALUATION CAT'] = np.nan\n",
      "/tmp/ipykernel_238123/4174041880.py:3: FutureWarning: Boolean inputs to the `inclusive` argument are deprecated infavour of `both` or `neither`.\n",
      "  df_filtered.loc[df_filtered['VALUATION AVG'].between(0,2*10**6, inclusive = True), 'VALUATION CAT'] = '< 2 Mio.'\n",
      "/tmp/ipykernel_238123/4174041880.py:4: FutureWarning: Boolean inputs to the `inclusive` argument are deprecated infavour of `both` or `neither`.\n",
      "  df_filtered.loc[df_filtered['VALUATION AVG'].between(2*10**6,10*10**6, inclusive = True), 'VALUATION CAT'] = '2-10 Mio.'\n",
      "/tmp/ipykernel_238123/4174041880.py:5: FutureWarning: Boolean inputs to the `inclusive` argument are deprecated infavour of `both` or `neither`.\n",
      "  df_filtered.loc[df_filtered['VALUATION AVG'].between(10*10**6,50*10**6, inclusive = True), 'VALUATION CAT'] = '10-50 Mio.'\n",
      "/tmp/ipykernel_238123/4174041880.py:6: FutureWarning: Boolean inputs to the `inclusive` argument are deprecated infavour of `both` or `neither`.\n",
      "  df_filtered.loc[df_filtered['VALUATION AVG'].between(50*10**6,100*10**6, inclusive = True), 'VALUATION CAT'] = '50-100 Mio.'\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "array([nan, '2-10 Mio.', '10-50 Mio.', '50-100 Mio.', '< 2 Mio.'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered['VALUATION CAT'] = np.nan\n",
    "\n",
    "df_filtered.loc[df_filtered['VALUATION AVG'].between(0,2*10**6, inclusive = True), 'VALUATION CAT'] = '< 2 Mio.'\n",
    "df_filtered.loc[df_filtered['VALUATION AVG'].between(2*10**6,10*10**6, inclusive = True), 'VALUATION CAT'] = '2-10 Mio.'\n",
    "df_filtered.loc[df_filtered['VALUATION AVG'].between(10*10**6,50*10**6, inclusive = True), 'VALUATION CAT'] = '10-50 Mio.'\n",
    "df_filtered.loc[df_filtered['VALUATION AVG'].between(50*10**6,100*10**6, inclusive = True), 'VALUATION CAT'] = '50-100 Mio.'\n",
    "\n",
    "df_filtered['VALUATION CAT'].unique()\n",
    "# There is one odd value of -92000000 in VALUATION column- need to check"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.5 Make catageories for TOTAL FUNDING"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Test Correlations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.08978403439298711"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Correlation between total funding and valuation. This is just a cheap test - \n",
    "#should be more accurate to choose total funding during seed stage for startups\n",
    "#that are valuated in growth stage now\n",
    "\n",
    "df_filtered['TOTAL FUNDING (USD M)'].corr(df_filtered['VALUATION AVG'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (1993949834.py, line 1)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  Input \u001b[0;32mIn [22]\u001b[0;36m\u001b[0m\n\u001b[0;31m    Result is 0.19 - so.. nope\u001b[0m\n\u001b[0m                        ^\u001b[0m\n\u001b[0;31mSyntaxError\u001b[0m\u001b[0;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "Result is 0.19 - so.. nope"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Reducing the records to goal-records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take care Svenja: Makes no sense for analytics to have limited records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.1 GROWTH STAGE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['late growth stage', 'early growth stage', 'seed stage', nan,\n",
       "       'not meaningful'], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered['GROWTH STAGE'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We want to consider 'early growth stage', 'seed stage' and records without a value, since they still could be meaningful for us. There is only one record with a GROWTH STAGE of 'not meaningful', which can be neglected after manual review (too old). We delete all rows with neglected growth stage but keep records with unknown growth stage:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7098, 49)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neglected_growth_stages = ['late growth stage', 'not meaningful']\n",
    "\n",
    "indexrows = df_filtered[df_filtered['GROWTH STAGE'].isin(neglected_growth_stages)].index\n",
    "\n",
    "df_filtered = df_filtered.drop(indexrows)\n",
    "df_filtered.reset_index\n",
    "df_filtered.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reduced to 7098 records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 LAST ROUND"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reduces records which were last funded in the goal-growth stage or not funded at all. If last funding was later than A-series, the record is out."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['ACQUISITION', 'SERIES A', nan, 'BUYOUT', 'ANGEL', 'SEED',\n",
       "       'LATE VC', 'EARLY VC', 'GRANT', 'IPO', 'DEBT', 'MERGER',\n",
       "       'PRIVATE PLACEMENT VC', 'LENDING CAPITAL', 'POST IPO DEBT',\n",
       "       'SECONDARY', 'CONVERTIBLE', 'POST IPO EQUITY',\n",
       "       'GROWTH EQUITY NON VC', 'GROWTH EQUITY VC', 'ICO',\n",
       "       'POST IPO CONVERTIBLE', 'SERIES C', 'SERIES B', 'SERIES D',\n",
       "       'SPINOUT', 'MEDIA FOR EQUITY', '-'], dtype=object)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered['LAST ROUND'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5877, 49)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neglected_last_rounds = ['ACQUISITION',\n",
    "       'LATE VC', 'IPO', 'MERGER', 'POST IPO DEBT', 'POST IPO EQUITY',\n",
    "       'GROWTH EQUITY NON VC', 'GROWTH EQUITY VC',\n",
    "       'POST IPO CONVERTIBLE', 'SERIES C', 'SERIES B', 'SERIES D']\n",
    "\n",
    "indexrowsLR = df_filtered[df_filtered['LAST ROUND'].isin(neglected_last_rounds)].index\n",
    "\n",
    "df_filtered = df_filtered.drop(indexrowsLR)\n",
    "df_filtered.reset_index\n",
    "df_filtered.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reduced to 5877."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.3 Last Funding Year (< 01/2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4086, 49)"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort out for last funding dates older than jan 2018\n",
    "indexLFD = df_filtered[df_filtered['LAST FUNDING DATE'] < '2018-01-01'].index\n",
    "\n",
    "df_filtered = df_filtered.drop(indexLFD)\n",
    "df_filtered.reset_index\n",
    "df_filtered.shape\n",
    "\n",
    "#df_filtered['LAST FUNDING DATE'].unique()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reduced to 4086.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_filtered['FOUNDERS GENDERS']= df_filtered['FOUNDERS GENDERS'].fillna('')\n",
    "#df_filtered['FOUNDERS GENDERS'] = df_filtered['FOUNDERS GENDERS'].astype(str)\n",
    "\n",
    "#df_filtered['FOUNDERS GENDERS'].unique()\n",
    "\n",
    "#df_filtered['FOUNDERS GENDERS'].str.contains('female').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_filtered.notnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.4 Neglect records with None Values in most important columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Im Folgenden löschen wir records, die garkeine Jahresangaben haben und null revenue und keine Geschlechtsangabe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2354, 49)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Check if LAST FUNDING DATE, FIRST FUNDING DATE, SEED YEAR, REVENUE AS LIST, VALUATION DATE IS ZERO, FOUNDERS GENDER\n",
    "\n",
    "index_of_null_records = df_filtered[(df_filtered['LAST FUNDING DATE'].isnull() & df_filtered['FIRST FUNDING DATE'].isnull() & df_filtered['SEED YEAR'].isnull()& df_filtered['VALUATION DATE'].isnull() & df_filtered['REVENUE AS LIST'].isnull() & df_filtered['FOUNDERS GENDERS'].isnull())].index\n",
    "\n",
    "df_filtered = df_filtered.drop(index_of_null_records)\n",
    "df_filtered.reset_index\n",
    "df_filtered.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reduced to 2192 records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Parse and sort by industry"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.3 Convert industries to list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2              [robotics]\n",
       "6                    None\n",
       "8                    None\n",
       "9                [travel]\n",
       "14            [education]\n",
       "              ...        \n",
       "8692             [health]\n",
       "8696    [wellness beauty]\n",
       "8700               [kids]\n",
       "8742              [media]\n",
       "8747                 None\n",
       "Name: INDUSTRIES AS LIST, Length: 2354, dtype: object"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert_industry_to_list(cellValue):\n",
    "    industryAsList = cellValue.split(';')\n",
    "    return None if pd.isnull(cellValue) else industryAsList\n",
    "\n",
    "df_filtered['INDUSTRIES AS LIST'] = [None if pd.isnull(cell) else convert_industry_to_list(cell) for cell in df_filtered['INDUSTRIES']]\n",
    "\n",
    "df_filtered['INDUSTRIES AS LIST']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 Set main industry as first entry of industry list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MAIN INDUSTRY\n",
       "dating                   5\n",
       "education               74\n",
       "energy                 201\n",
       "enterprise software    143\n",
       "event tech              10\n",
       "fashion                 59\n",
       "fintech                178\n",
       "food                   155\n",
       "gaming                  84\n",
       "health                 445\n",
       "home living             35\n",
       "hosting                  7\n",
       "jobs recruitment        46\n",
       "kids                     6\n",
       "legal                   39\n",
       "marketing               82\n",
       "media                  118\n",
       "music                   28\n",
       "real estate             80\n",
       "robotics                65\n",
       "security                58\n",
       "semiconductors          35\n",
       "sports                  42\n",
       "telecom                 24\n",
       "transportation          85\n",
       "travel                  52\n",
       "wellness beauty         37\n",
       "dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_filtered['MAIN INDUSTRY'] = [None if cell == None else cell[0] for cell in df_filtered['INDUSTRIES AS LIST']]\n",
    "\n",
    "#df_filtered['MAIN INDUSTRY']\n",
    "\n",
    "pd.set_option('display.max_rows',28)\n",
    "df_filtered.groupby('MAIN INDUSTRY').size()\n",
    "\n",
    "#df_filtered['MAIN INDUSTRY'].isnull().sum() = 161 Nicht kategorisierte Startups\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save df_filtered dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'df_filtered' (DataFrame)\n"
     ]
    }
   ],
   "source": [
    "%store df_filtered"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "## Just blödsinn checking here for Education industry  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NAME                   74\n",
       "PROFILE URL            74\n",
       "WEBSITE                74\n",
       "HQ REGION              74\n",
       "HQ COUNTRY             74\n",
       "                       ..\n",
       "VALUATION (EUR) AVG    42\n",
       "REVENUE AS LIST        39\n",
       "VALUATION CAT          42\n",
       "INDUSTRIES AS LIST     74\n",
       "MAIN INDUSTRY          74\n",
       "Length: 51, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ed = df_filtered[df_filtered['MAIN INDUSTRY'] == 'education']\n",
    "\n",
    "pd.set_option('display.max_rows',48)\n",
    "df_ed.count()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df_ed.groupby('HQ CITY').size()\n",
    "\n",
    "df_ed['FOUNDERS GENDERS'].str.contains('female').sum()\n",
    "df_ed['FOUNDERS BACKGROUNDS'].unique()\n",
    "\n",
    "df_ed['VALUATION'].unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}