extract_tables.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled3.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "source": [
        "!pip install sec-api"
      ],
      "metadata": {
        "id": "A8rz9XHnBuM-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from sec_api import ExtractorApi\n",
        "import pandas as pd"
      ],
      "metadata": {
        "id": "Kec7laejknyS"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def get_section(filing_url, section = '1'):\n",
        "    res = filing_url[:filing_url.index(\"htm\") + len(\"htm\")]\n",
        "    section_text = extractorApi.get_section(str(res), section, \"html\")\n",
        "    return section_text\n",
        "\n",
        "def read_and_clean_table(text):\n",
        "    tables = pd.read_html(text)\n",
        "    cleaned_tables = []\n",
        "    for table in tables:\n",
        "        table = table.dropna(how='all')\n",
        "        drop_cols = []\n",
        "        for col in table.columns:\n",
        "            nan_cnt = table[col].isnull().sum()\n",
        "            if nan_cnt >= table.shape[0]/2:\n",
        "                drop_cols.append(col)\n",
        "        table = table.drop(labels=drop_cols, axis=1)\n",
        "        cleaned_tables.append(table)\n",
        "    return cleaned_tables\n",
        "\n",
        "def table_to_str(table):\n",
        "    table = table.values.tolist()\n",
        "    table_str = []\n",
        "    for row in table:\n",
        "        row_cl = [str(data).replace('\\xa0', ' ').replace('\\\\x', ' ').replace('(',' ').replace(')',' ') for data in row]\n",
        "        table_str.append(' | '.join(row_cl))\n",
        "    table_str = ' \\n '.join(table_str)\n",
        "    return table_str"
      ],
      "metadata": {
        "id": "AbrJRx5YBuil"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "api_key = '8aa664eee7896b98038d05ca58f3779d3d9dfa9ca28ad1394702477226556894'\n",
        "extractorApi = ExtractorApi(api_key)"
      ],
      "metadata": {
        "id": "MBHVIT5vlEC3"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "url = \"https://www.sec.gov/Archives/edgar/data/0001408710/000119312517265630/d396247d10k.htm\"\n",
        "section = '6'\n",
        "tables = []\n",
        "for table in read_and_clean_table('<html><body>' + get_section(url, section) + '</body></html>'):\n",
        "    table_data = table_to_str(table)\n",
        "    tables.append(table_data)\n",
        "print(tables)"
      ],
      "metadata": {
        "id": "fMCMx37CChe_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "-pkKoh2YjWEg"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}