08_Analyzing_Startup_Fundraising_Deals_from_Crunchbase.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.2"
    },
    "colab": {
      "name": "08-Analyzing-Startup-Fundraising-Deals-from-Crunchbase.ipynb",
      "provenance": [],
      "include_colab_link": true
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/lis-r-barreto/Data-Engineer-Career-Path/blob/main/08_Analyzing_Startup_Fundraising_Deals_from_Crunchbase.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R8EjyGjEztE1"
      },
      "source": [
        "## Introduction"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "xvLL155mztE6"
      },
      "source": [
        "import pandas as pd\n",
        "pd.options.display.max_columns = 99\n",
        "chunk_iter = pd.read_csv('crunchbase-investments.csv', chunksize=5000, encoding='ISO-8859-1')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1WA_7X7fztE_"
      },
      "source": [
        "Compute each column's missing value counts"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7DPfhF_zztFB"
      },
      "source": [
        "mv_list = []\n",
        "for chunk in chunk_iter:\n",
        "    mv_list.append(chunk.isnull().sum())\n",
        "    \n",
        "combined_mv_vc = pd.concat(mv_list)\n",
        "unique_combined_mv_vc = combined_mv_vc.groupby(combined_mv_vc.index).sum()\n",
        "unique_combined_mv_vc.sort_values()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "i7TUwOs-ztFF"
      },
      "source": [
        "Total memory footprint for each column"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "89_mPPYLztFG"
      },
      "source": [
        "chunk_iter = pd.read_csv('crunchbase-investments.csv', chunksize=5000, encoding='ISO-8859-1')\n",
        "counter = 0\n",
        "series_memory_fp = pd.Series(dtype='float64')\n",
        "for chunk in chunk_iter:\n",
        "    if counter == 0:\n",
        "        series_memory_fp = chunk.memory_usage(deep=True)\n",
        "    else:\n",
        "        series_memory_fp += chunk.memory_usage(deep=True)\n",
        "    counter += 1\n",
        "\n",
        "# Drop memory footprint calculation for the index.\n",
        "series_memory_fp = series_memory_fp.drop('Index')\n",
        "series_memory_fp"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5gcr7VaGztFJ"
      },
      "source": [
        "Total memory footprint of the data (in megabytes)"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4Fh3IkBjztFN"
      },
      "source": [
        "series_memory_fp.sum() / (1024 * 1024)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oJfS-6qdztFP"
      },
      "source": [
        "unique_combined_mv_vc.sort_values()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1a0BHj7JztFR"
      },
      "source": [
        "# Drop columns representing URL's or containing way too many missing values (>90% missing)\n",
        "drop_cols = ['investor_permalink', 'company_permalink', 'investor_category_code']\n",
        "keep_cols = chunk.columns.drop(drop_cols)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bl2XiRO8ztFT"
      },
      "source": [
        "keep_cols.tolist"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3zL8nwaxztFV"
      },
      "source": [
        "## Selecting Data Types\n",
        "\n",
        "Let's first determine which columns shift types across chunks. Note that we only lay the groundwork for this step."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "cI_8wj67ztFX"
      },
      "source": [
        "# Key: Column name, Value: List of types\n",
        "col_types = {}\n",
        "chunk_iter = pd.read_csv('crunchbase-investments.csv', chunksize=5000, encoding='ISO-8859-1', usecols=keep_cols)\n",
        "\n",
        "for chunk in chunk_iter:\n",
        "    for col in chunk.columns:\n",
        "        if col not in col_types:\n",
        "            col_types[col] = [str(chunk.dtypes[col])]\n",
        "        else:\n",
        "            col_types[col].append(str(chunk.dtypes[col]))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kbgkmBgTztFY"
      },
      "source": [
        "uniq_col_types = {}\n",
        "for k,v in col_types.items():\n",
        "    uniq_col_types[k] = set(col_types[k])\n",
        "uniq_col_types"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "178nij8-ztFZ"
      },
      "source": [
        "chunk"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1TTlh9fnztFc"
      },
      "source": [
        "## Loading Chunks Into SQLite"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3AaHmm22ztFd"
      },
      "source": [
        "import sqlite3\n",
        "conn = sqlite3.connect('crunchbase.db')\n",
        "chunk_iter = pd.read_csv('crunchbase-investments.csv', chunksize=5000, encoding='ISO-8859-1')\n",
        "\n",
        "for chunk in chunk_iter:\n",
        "    chunk.to_sql(\"investments\", conn, if_exists='append', index=False)"
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}