2_Extract_10_K_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8kusXEoDSXZD"
   },
   "source": [
    "# 2. Extract the company names and details\n",
    "-------------------\n",
    "Group 3 , September 24, 2022\n",
    "1. Gezhi Cheng, \n",
    "2. Haowei Lee, \n",
    "3. Ziyi Liu, \n",
    "4. VS Chaitanya Madduri\n",
    "\n",
    "> <i>Description: This notebook focuses on analysing the 10-k/10-q files present in the gogole drive and tries to extract the list of companies along with their details </i>\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>Tip:</b> # Please run this notebook in the colab . \n",
    "</div> \n",
    "\n",
    "### Pre requisites: \n",
    "1. And add the shortcut of the drive link : https://drive.google.com/drive/folders/1tZP9A0hrAj8ptNP3VE9weYZ3WDn9jHic to your personal drive.\n",
    "\n",
    "The above drive contains the 10-k fillings from the author \n",
    "Bill McDonald\n",
    "\n",
    "Do to the huge files we have used our personal google drive folders to save the files.\n",
    "\n",
    "Files:\n",
    "Company_Details_analysis.csv - to store the 10-k files of the companies selected\n",
    "\n",
    "### Output files:\n",
    "1. And add the shortcut of the drive link :https://drive.google.com/drive/folders/1X4UdGsQiHVWSr63FRiz8rwOuWW5Ua8uI?usp=sharing to your personal drive.\n",
    "\n",
    "Files:\n",
    "Selected_10k_v2.csv - to store the 10-k files of the companies selected\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Zs_LVrgPd7re"
   },
   "source": [
    "## 1. Import Required Packages "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "ahCQGD-axaqM"
   },
   "outputs": [],
   "source": [
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import zipfile\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "V-N3MloQxICY",
    "outputId": "c72900c0-be8b-4058-b4d6-381749ecab43"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mounted at /content/drive\n"
     ]
    }
   ],
   "source": [
    "# Load the Drive helper and mount \n",
    "# Please allow the access to the google drive\n",
    "from google.colab import drive\n",
    "drive.mount('/content/drive')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7vxhK1ageBFX"
   },
   "source": [
    "## 2.Loading the files to the notebook"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aaT7tvcD0zsn"
   },
   "source": [
    "### 2.1 Extract the list of the zip files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Vhkn2dJL0JXv",
    "outputId": "3e82468b-d356-4402-9d84-e67e8e6c6a96"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6\n"
     ]
    }
   ],
   "source": [
    "# getting the list of the zip files in the main folder zip .  \n",
    "files = [] # empty list to caputure the zip files\n",
    "dir_path = \"/content/drive/MyDrive/10-X_C_Zip\" # Main path directory \n",
    "  # Iterate directory\n",
    "for path in os.listdir(dir_path):\n",
    "    # check if current path is a file\n",
    "    if os.path.isfile(os.path.join(dir_path, path)):\n",
    "        files.append(path) # appending the file names to the list\n",
    "\n",
    "# Printing the number of the files\n",
    "print(len(files)) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Rvjh_eYhmrU_",
    "outputId": "0c091708-eb7f-4619-a881-646a42312cf4"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['10-X_C_1993-2000.zip',\n",
       " '10-X_C_2001-2005.zip',\n",
       " '10-X_C_2006-2010.zip',\n",
       " '10-X_C_2011-2015.zip',\n",
       " '10-X_C_2016-2020.zip',\n",
       " '10-X_C_2021.zip']"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# printing the zip file names \n",
    "files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "id": "cm6GALxoBZ94"
   },
   "outputs": [],
   "source": [
    "# Based on our analysis we have decided to collect all the 10-k fillings between\n",
    "# 2011-2021\n",
    "\n",
    "files = [\n",
    "  '10-X_C_2011-2015.zip',\n",
    " '10-X_C_2016-2020.zip',\n",
    " '10-X_C_2021.zip']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UXMxabJpPP19",
    "outputId": "a4ac16c3-5df5-41e9-ad86-7d8fa8a669ea"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(files)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "6SQljKe2qETu"
   },
   "outputs": [],
   "source": [
    "\n",
    "DIR_PATH = \"/content/drive/MyDrive/SPM_files/\" # defining the path in the Google drive\n",
    "\n",
    "# Profitability Analysis\n",
    "company_list = pd.read_csv(DIR_PATH + \"Company_Details_analysis.csv\")\n",
    "\n",
    "# if you are loading the file from local files\n",
    "# company_list = pd.read_csv(\"Company_Details_analysis.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 250
    },
    "id": "tm-uRsv6qK2H",
    "outputId": "5a7b5a62-69f9-4dee-d37e-430933765801"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-87e74aef-132d-40a1-931d-190312bd31fa\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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>Unnamed: 0</th>\n",
       "      <th>Company_Name</th>\n",
       "      <th>Std_Indust_Class</th>\n",
       "      <th>Index_Key</th>\n",
       "      <th>Class_number</th>\n",
       "      <th>Class_number_first</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>206</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>ELECTRIC &amp; OTHER SERVICES COMBINED [4931]</td>\n",
       "      <td>755001</td>\n",
       "      <td>4931</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>303</td>\n",
       "      <td>RGC RESOURCES INC</td>\n",
       "      <td>NATURAL GAS TRANSMISSION &amp; DISTRIBUTION [4923]</td>\n",
       "      <td>1069533</td>\n",
       "      <td>4923</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>322</td>\n",
       "      <td>SPIRE INC</td>\n",
       "      <td>NATURAL GAS DISTRIBUTION [4924]</td>\n",
       "      <td>1126956</td>\n",
       "      <td>4924</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>382</td>\n",
       "      <td>UGI CORP /PA/</td>\n",
       "      <td>GAS &amp; OTHER SERVICES COMBINED [4932]</td>\n",
       "      <td>884614</td>\n",
       "      <td>4932</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>437</td>\n",
       "      <td>FIRSTENERGY CORP</td>\n",
       "      <td>ELECTRIC SERVICES [4911]</td>\n",
       "      <td>1031296</td>\n",
       "      <td>4911</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-87e74aef-132d-40a1-931d-190312bd31fa')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-87e74aef-132d-40a1-931d-190312bd31fa button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-87e74aef-132d-40a1-931d-190312bd31fa');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   Unnamed: 0       Company_Name  \\\n",
       "0         206        UNITIL CORP   \n",
       "1         303  RGC RESOURCES INC   \n",
       "2         322          SPIRE INC   \n",
       "3         382      UGI CORP /PA/   \n",
       "4         437   FIRSTENERGY CORP   \n",
       "\n",
       "                                 Std_Indust_Class  Index_Key  Class_number  \\\n",
       "0       ELECTRIC & OTHER SERVICES COMBINED [4931]     755001          4931   \n",
       "1  NATURAL GAS TRANSMISSION & DISTRIBUTION [4923]    1069533          4923   \n",
       "2                 NATURAL GAS DISTRIBUTION [4924]    1126956          4924   \n",
       "3            GAS & OTHER SERVICES COMBINED [4932]     884614          4932   \n",
       "4                        ELECTRIC SERVICES [4911]    1031296          4911   \n",
       "\n",
       "   Class_number_first  \n",
       "0                  49  \n",
       "1                  49  \n",
       "2                  49  \n",
       "3                  49  \n",
       "4                  49  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "company_list.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5K1s8p3Sd0ng"
   },
   "source": [
    "## 3. Creating nexted dictionaries for file location\n",
    "- we wil use this dictionary to get location of all the 10-k filling we requre from the company list.\n",
    "- Level 1 will store the main zip files eg : '10-X_C_2011-2015.zip'\n",
    "- Level 2 wil store the company index numbers eg: 755001\n",
    "- Level 3 will store the folder paths eg: '2011/QTR1/20110203_10-K_edgar_data_755001_0001193125-11-022690.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "M669Skq4rxMo"
   },
   "outputs": [],
   "source": [
    "final_dic = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "id": "naKmipqMpjQm"
   },
   "outputs": [],
   "source": [
    "# Extract the file names and fodler details for the selected companies\n",
    "# please create the company folders in the destination folder.\n",
    "drive_path = \"/content/drive/MyDrive/10-X_C_Zip/\"\n",
    "\n",
    "\n",
    "for file_name in files:\n",
    "  final_dic[file_name] = {} \n",
    "  for index in company_list['Index_Key'].unique():\n",
    "    final_dic[file_name][index]= {}\n",
    "  zip_filepath = drive_path + file_name\n",
    "  zip = zipfile.ZipFile(drive_path + file_name)\n",
    "  for index in company_list['Index_Key'].unique():\n",
    "    final_dic[file_name][index]= [s for s in zip.namelist() if ('_' + str(index)+ '_' in s) & ('10-K_edgar' in s)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "LhiP51XwwVcW",
    "outputId": "3ebb2d93-7dd1-4469-e24a-a0abf030c02d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['10-X_C_2011-2015.zip', '10-X_C_2016-2020.zip', '10-X_C_2021.zip'])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dic.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SmEb_IQyswte",
    "outputId": "0654b431-838f-4279-e2f1-66b7b303a6b0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys([755001, 1069533, 1126956, 884614, 1031296, 1610418, 1109357, 1336047, 1623925, 100122, 1317630, 1318220, 73088, 823768, 1582244, 1060391, 1130464, 922224, 753308, 1428389, 1410636, 1522727, 1591763, 1047862, 92122, 1326160, 1379661, 4904, 352541, 1123852, 65984, 764622, 72903, 76063, 81018, 1085392, 92521, 1081316, 1585790, 1693317, 788784, 822818, 1013871, 1021635, 1032208, 1035201, 91928, 1130310, 107833, 1193311, 1423902, 1419242, 1540947, 31978, 1161728, 66004, 72741, 827052, 1002910, 1061219, 715957, 787250, 354707, 78128, 861878, 874761, 91882, 1108426, 1296445, 1561660, 1567683, 1634997, 1637757, 1711269, 1783400, 1733998, 1612720, 1422841, 1549922, 1378140, 1368265, 1061069, 727273, 1043186, 1528356, 1563922, 1599947, 1690384, 891532, 1730346, 1338065, 1004980])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dictionary contains teh location of all the files that need to be extrated\n",
    "final_dic['10-X_C_2011-2015.zip'].keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "LBLUdJtxnzzB",
    "outputId": "a14dc588-fa7e-427f-bc31-64278539d59e"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['10-X_C_2011-2015.zip', '10-X_C_2016-2020.zip', '10-X_C_2021.zip'])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dic.keys() # printing main dictionary keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "vuVfhL3BuO63",
    "outputId": "97b281a6-5c6d-4154-c71c-7fea30887bb5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2016/QTR4/20161122_10-K_edgar_data_884614_0000884614-16-000067.txt',\n",
       " '2017/QTR4/20171121_10-K_edgar_data_884614_0000884614-17-000029.txt',\n",
       " '2018/QTR4/20181120_10-K_edgar_data_884614_0000884614-18-000033.txt',\n",
       " '2019/QTR4/20191126_10-K_edgar_data_884614_0000884614-19-000059.txt',\n",
       " '2020/QTR4/20201120_10-K_edgar_data_884614_0000884614-20-000052.txt']"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Printing an example company key\n",
    "final_dic['10-X_C_2016-2020.zip'][884614]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LnzTEM9Cplhp"
   },
   "source": [
    "## 3. Extract the text data , company name  and details form the zip files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Z7hyUUNB1cxQ"
   },
   "source": [
    "3.1 Extract text content to Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0WBTVDPFplws"
   },
   "outputs": [],
   "source": [
    "# A master dictionary to save the company details\n",
    "# which details of the company are specified in th elist\n",
    "columns = ['Company_Key', 'Text_data', 'Quarter_details',\"Year\"] \n",
    "# creating the empty dataframe with columns\n",
    "master_dataframe = pd.DataFrame(columns= columns) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "v0ZtQg_FxOKf"
   },
   "outputs": [],
   "source": [
    "\n",
    "# please create the company folders in the destination folder.\n",
    "drive_path = \"/content/drive/MyDrive/10-X_C_Zip/\" # the input folder\n",
    "\n",
    "#looping around the zip files \n",
    "for file_name in files: \n",
    "  # zip file handler  \n",
    "  zip = zipfile.ZipFile(drive_path + file_name)\n",
    "  # looping around the company index codes that we selected\n",
    "  for company in company_list['Index_Key'].unique():\n",
    "    # Checking if the path exists\n",
    "    if final_dic[file_name][company]:\n",
    "      # looping around all the file paths \n",
    "      for text_file_name in final_dic[file_name][company]: \n",
    "         # getting latest index of file to create the next index for entry\n",
    "        index_val = master_dataframe.shape[0]\n",
    "        try:\n",
    "          # capturing the 10-k filling data and filepath\n",
    "          master_dataframe.loc[index_val,\"Text_data\"] =  zip.read(text_file_name)\n",
    "          master_dataframe.loc[index_val,\"filename\"] =  text_file_name\n",
    "        except:\n",
    "          # except block in case if there are errors in the code\n",
    "          print(\"Name of the file :: {}\".format(text_file_name))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "vnrUMq9owBFF",
    "outputId": "5dce3dbd-4069-4b61-ca1e-954d7b89d2c5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['10-X_C_2011-2015.zip', '10-X_C_2016-2020.zip', '10-X_C_2021.zip'])"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_dic.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "w8_I5vhUfp-E"
   },
   "source": [
    "## 4.Extracting company details "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "g3rCY75qJ1og"
   },
   "outputs": [],
   "source": [
    "def extract_compnay_name (text_input):\n",
    "   '''\n",
    "   This function will extarct the name of the company from text\n",
    "   input: \n",
    "    text_input: string which contains all the 10-k info\n",
    "   return:\n",
    "    name of the company\n",
    "   '''\n",
    "   file_data = str(text_input).split('\\\\n')\n",
    "   for line in file_data:\n",
    "     stripped_line = line.strip()\n",
    "     if \"COMPANY CONFORMED NAME:\" in stripped_line :                \n",
    "       splitted_str = stripped_line.split(\":\")  # split COMPANY CONFORMED NAME and real name\n",
    "       company_name = splitted_str[1] \n",
    "       return company_name.strip() \n",
    "\n",
    "def extract_company_index (text_input):\n",
    "   '''\n",
    "    This function will extarct the name of the company from text\n",
    "   input: \n",
    "    text_input: string which contains all the 10-k info\n",
    "   return:\n",
    "    company Index\n",
    "   '''   \n",
    "   file_data = str(text_input).split('\\\\n')\n",
    "   for line in file_data:\n",
    "     stripped_line = line.strip()\n",
    "     if \"CENTRAL INDEX KEY:\" in stripped_line:\n",
    "            \n",
    "      splitted_str = stripped_line.split(\":\")  # split COMPANY CONFORMED NAME and real name\n",
    "      company_index = splitted_str[1]\n",
    "      return company_index.strip() \n",
    "      "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "eNehzSWH1j5r"
   },
   "source": [
    "### 4.1 Extract the Company name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Z2hdsajOJ4j_"
   },
   "outputs": [],
   "source": [
    "# Extract the company name\n",
    "master_dataframe['company_name'] = master_dataframe['Text_data'].apply(lambda x: extract_compnay_name(x)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "DIgV3n4qJ4hU"
   },
   "outputs": [],
   "source": [
    "# code to remove special charaters from the name \n",
    "master_dataframe['company_name'] = master_dataframe['company_name'].str[6:-2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZTM7SYZK1xQG"
   },
   "source": [
    "### 4.2 Extract the 10-k filling year and quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "jfz8MQHhLQr3"
   },
   "outputs": [],
   "source": [
    "# Extract the Year details from the file path \n",
    "master_dataframe['Year'] = master_dataframe['filename'].str.split(\"/\").str[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "v2ROGjuVLQpI"
   },
   "outputs": [],
   "source": [
    "# Extract the Quarter details from the file path \n",
    "master_dataframe['Quarter_details'] =  master_dataframe['filename'].str[5:9]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LaO58op-1t4v"
   },
   "source": [
    "### 4.2 Extract the Company key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "StzoA9beLQmd"
   },
   "outputs": [],
   "source": [
    "# Extract the company index code.\n",
    "master_dataframe['Company_Key'] = master_dataframe['Text_data'].apply(lambda x: extract_company_index(x)) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KNtF_cScNaDd"
   },
   "outputs": [],
   "source": [
    "# Removing the special charaters from the code\n",
    "master_dataframe['Company_Key'] = master_dataframe['Company_Key'].str[6:-2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "X57GfOY2INAf"
   },
   "outputs": [],
   "source": [
    "# type casting the key to integer\n",
    "master_dataframe['Company_Key'] = master_dataframe['Company_Key'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "GkEHwtgtNSB2",
    "outputId": "2141aebc-fb0a-44a4-8050-8c74b860048c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-8df6220b-e19e-45f7-ae1f-53b2803abf25\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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_Key</th>\n",
       "      <th>Text_data</th>\n",
       "      <th>Quarter_details</th>\n",
       "      <th>Year</th>\n",
       "      <th>filename</th>\n",
       "      <th>company_name</th>\n",
       "      <th>cik_filename</th>\n",
       "      <th>actual_list</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2011</td>\n",
       "      <td>2011/QTR1/20110203_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2012</td>\n",
       "      <td>2012/QTR1/20120201_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2013</td>\n",
       "      <td>2013/QTR1/20130130_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2014</td>\n",
       "      <td>2014/QTR1/20140129_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2015</td>\n",
       "      <td>2015/QTR1/20150128_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8df6220b-e19e-45f7-ae1f-53b2803abf25')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-8df6220b-e19e-45f7-ae1f-53b2803abf25 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-8df6220b-e19e-45f7-ae1f-53b2803abf25');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   Company_Key                                          Text_data  \\\n",
       "0       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "1       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "2       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "3       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "4       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "\n",
       "  Quarter_details  Year                                           filename  \\\n",
       "0            QTR1  2011  2011/QTR1/20110203_10-K_edgar_data_755001_0001...   \n",
       "1            QTR1  2012  2012/QTR1/20120201_10-K_edgar_data_755001_0001...   \n",
       "2            QTR1  2013  2013/QTR1/20130130_10-K_edgar_data_755001_0001...   \n",
       "3            QTR1  2014  2014/QTR1/20140129_10-K_edgar_data_755001_0001...   \n",
       "4            QTR1  2015  2015/QTR1/20150128_10-K_edgar_data_755001_0001...   \n",
       "\n",
       "  company_name  cik_filename  actual_list  \n",
       "0  UNITIL CORP        755001            1  \n",
       "1  UNITIL CORP        755001            1  \n",
       "2  UNITIL CORP        755001            1  \n",
       "3  UNITIL CORP        755001            1  \n",
       "4  UNITIL CORP        755001            1  "
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# showing the top 5 records\n",
    "master_dataframe.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uPeW9eSugg_l"
   },
   "source": [
    "\n",
    "#### Note:\n",
    "For most of the 10-k fillings has the company code in the filename but it is not the case for all the files. SO in the below step we am comparing the company code from the filename to inside the file.\n",
    "\n",
    "\n",
    "<div class=\"alert alert-block alert-info\">\n",
    "    <b>FYI:</b> Since there are many file in the zip files it is time consuming and colab is crashing sometimes to process all the files. So we are extracting the the files based on the file name. \n",
    "</div> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Fz1LmFrrNqxH"
   },
   "outputs": [],
   "source": [
    "# extracting the company code from the file \n",
    "master_dataframe['cik_filename'] = master_dataframe['filename'].str.split('_').str[4]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "XSY6N3okNqt6"
   },
   "outputs": [],
   "source": [
    "# showing the top 5 records\n",
    "master_dataframe['cik_filename'] = master_dataframe['cik_filename'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 112
    },
    "id": "aXEGJ9oFOCJQ",
    "outputId": "dd195312-8e2f-41af-f81e-c4b555019d1d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-374b74ea-c317-40eb-a9c7-5ceabaaee5ac\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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_Key</th>\n",
       "      <th>Text_data</th>\n",
       "      <th>Quarter_details</th>\n",
       "      <th>Year</th>\n",
       "      <th>filename</th>\n",
       "      <th>company_name</th>\n",
       "      <th>cik_filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2011</td>\n",
       "      <td>2011/QTR1/20110203_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>755001</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>QTR1</td>\n",
       "      <td>2012</td>\n",
       "      <td>2012/QTR1/20120201_10-K_edgar_data_755001_0001...</td>\n",
       "      <td>UNITIL CORP</td>\n",
       "      <td>755001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-374b74ea-c317-40eb-a9c7-5ceabaaee5ac')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-374b74ea-c317-40eb-a9c7-5ceabaaee5ac button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-374b74ea-c317-40eb-a9c7-5ceabaaee5ac');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   Company_Key                                          Text_data  \\\n",
       "0       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "1       755001  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "\n",
       "  Quarter_details  Year                                           filename  \\\n",
       "0            QTR1  2011  2011/QTR1/20110203_10-K_edgar_data_755001_0001...   \n",
       "1            QTR1  2012  2012/QTR1/20120201_10-K_edgar_data_755001_0001...   \n",
       "\n",
       "  company_name  cik_filename  \n",
       "0  UNITIL CORP        755001  \n",
       "1  UNITIL CORP        755001  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_dataframe.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "1u8qjUXPOCGo",
    "outputId": "ef3fe919-02c1-4d10-c449-ef7a959fbcc6"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-01a2d932-bfb6-4bf0-aeea-afd09791e331\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <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_Key</th>\n",
       "      <th>Text_data</th>\n",
       "      <th>Quarter_details</th>\n",
       "      <th>Year</th>\n",
       "      <th>filename</th>\n",
       "      <th>company_name</th>\n",
       "      <th>cik_filename</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>57183</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015/QTR4/20151124_10-K_edgar_data_1126956_000...</td>\n",
       "      <td>LACLEDE GAS CO</td>\n",
       "      <td>1126956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20947</td>\n",
       "      <td>b\"&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2011/QTR1/20110216_10-K_edgar_data_1031296_000...</td>\n",
       "      <td>CLEVELAND ELECTRIC ILLUMINATING CO</td>\n",
       "      <td>1031296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20947</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2012/QTR1/20120228_10-K_edgar_data_1031296_000...</td>\n",
       "      <td>CLEVELAND ELECTRIC ILLUMINATING CO</td>\n",
       "      <td>1031296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>53456</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013/QTR1/20130225_10-K_edgar_data_1031296_000...</td>\n",
       "      <td>JERSEY CENTRAL POWER &amp; LIGHT CO</td>\n",
       "      <td>1031296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1407703</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015/QTR1/20150217_10-K_edgar_data_1031296_000...</td>\n",
       "      <td>FirstEnergy Solutions Corp.</td>\n",
       "      <td>1031296</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>81100</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016/QTR1/20160229_10-K_edgar_data_1085392_000...</td>\n",
       "      <td>PUGET SOUND ENERGY INC</td>\n",
       "      <td>1085392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>641</th>\n",
       "      <td>13372</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017/QTR1/20170223_10-K_edgar_data_72741_00000...</td>\n",
       "      <td>NSTAR ELECTRIC CO</td>\n",
       "      <td>72741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>690</th>\n",
       "      <td>754737</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016/QTR1/20160226_10-K_edgar_data_91882_00007...</td>\n",
       "      <td>SCANA CORP</td>\n",
       "      <td>91882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>692</th>\n",
       "      <td>754737</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018/QTR1/20180223_10-K_edgar_data_91882_00007...</td>\n",
       "      <td>SCANA CORP</td>\n",
       "      <td>91882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>754737</td>\n",
       "      <td>b'&lt;Header&gt;\\r\\n&lt;FileStats&gt;\\r\\n    &lt;FileName&gt;201...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019/QTR1/20190228_10-K_edgar_data_91882_00007...</td>\n",
       "      <td>SCANA CORP</td>\n",
       "      <td>91882</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79 rows × 7 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-01a2d932-bfb6-4bf0-aeea-afd09791e331')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-01a2d932-bfb6-4bf0-aeea-afd09791e331 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-01a2d932-bfb6-4bf0-aeea-afd09791e331');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "     Company_Key                                          Text_data  \\\n",
       "14         57183  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "20         20947  b\"<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "21         20947  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "22         53456  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "24       1407703  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "..           ...                                                ...   \n",
       "537        81100  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "641        13372  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "690       754737  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "692       754737  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "693       754737  b'<Header>\\r\\n<FileStats>\\r\\n    <FileName>201...   \n",
       "\n",
       "    Quarter_details Year                                           filename  \\\n",
       "14              NaN  NaN  2015/QTR4/20151124_10-K_edgar_data_1126956_000...   \n",
       "20              NaN  NaN  2011/QTR1/20110216_10-K_edgar_data_1031296_000...   \n",
       "21              NaN  NaN  2012/QTR1/20120228_10-K_edgar_data_1031296_000...   \n",
       "22              NaN  NaN  2013/QTR1/20130225_10-K_edgar_data_1031296_000...   \n",
       "24              NaN  NaN  2015/QTR1/20150217_10-K_edgar_data_1031296_000...   \n",
       "..              ...  ...                                                ...   \n",
       "537             NaN  NaN  2016/QTR1/20160229_10-K_edgar_data_1085392_000...   \n",
       "641             NaN  NaN  2017/QTR1/20170223_10-K_edgar_data_72741_00000...   \n",
       "690             NaN  NaN  2016/QTR1/20160226_10-K_edgar_data_91882_00007...   \n",
       "692             NaN  NaN  2018/QTR1/20180223_10-K_edgar_data_91882_00007...   \n",
       "693             NaN  NaN  2019/QTR1/20190228_10-K_edgar_data_91882_00007...   \n",
       "\n",
       "                           company_name  cik_filename  \n",
       "14                       LACLEDE GAS CO       1126956  \n",
       "20   CLEVELAND ELECTRIC ILLUMINATING CO       1031296  \n",
       "21   CLEVELAND ELECTRIC ILLUMINATING CO       1031296  \n",
       "22      JERSEY CENTRAL POWER & LIGHT CO       1031296  \n",
       "24          FirstEnergy Solutions Corp.       1031296  \n",
       "..                                  ...           ...  \n",
       "537              PUGET SOUND ENERGY INC       1085392  \n",
       "641                   NSTAR ELECTRIC CO         72741  \n",
       "690                          SCANA CORP         91882  \n",
       "692                          SCANA CORP         91882  \n",
       "693                          SCANA CORP         91882  \n",
       "\n",
       "[79 rows x 7 columns]"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# comparing of the company key from the file is same as the company key from text \n",
    "master_dataframe[master_dataframe['Company_Key'] != master_dataframe['cik_filename']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MWwQE8_UhkoX"
   },
   "source": [
    "- 79 cases there is an mismatch between the company key from the file and company key from text "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CV2XQrkrOCBI",
    "outputId": "b970e986-e398-460e-bf42-64b18ed92904"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(888, 7)"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_dataframe.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NCpP9nfI2Ead"
   },
   "source": [
    "### 4.3 Creating an flag column to highlight the mismatch cases "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Ib9kVjaiNR_O"
   },
   "outputs": [],
   "source": [
    "#Creating an flag column to highlioght the mismatch cases \n",
    "master_dataframe['actual_list'] = np.where(master_dataframe['Company_Key'].isin(company_list['Index_Key'].unique()), 1,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "SPpC4XhfQ3_c",
    "outputId": "06669c27-2803-4910-830c-53c5ee7d9b06"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    809\n",
       "0     79\n",
       "Name: actual_list, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "master_dataframe['actual_list'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "01AWeSxMZ3no",
    "outputId": "cc1917a5-c06d-4124-ed97-26fab1cbe2a2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 809 entries, 0 to 887\n",
      "Data columns (total 8 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   Company_Key      809 non-null    int64 \n",
      " 1   Text_data        809 non-null    object\n",
      " 2   Quarter_details  809 non-null    object\n",
      " 3   Year             809 non-null    int64 \n",
      " 4   filename         809 non-null    object\n",
      " 5   company_name     809 non-null    object\n",
      " 6   cik_filename     809 non-null    int64 \n",
      " 7   actual_list      809 non-null    int64 \n",
      "dtypes: int64(4), object(4)\n",
      "memory usage: 56.9+ KB\n"
     ]
    }
   ],
   "source": [
    "master_dataframe.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "umiBl6bbaVlk",
    "outputId": "646f5ce5-9414-45ff-fd95-6333d5d3082a"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py:4913: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\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",
      "  errors=errors,\n"
     ]
    }
   ],
   "source": [
    "master_dataframe.drop(['actual_list'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "OOS6SLuW2Klu"
   },
   "source": [
    "## 5. Exporting the files to the drive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "a7QVTmff0DO9"
   },
   "outputs": [],
   "source": [
    "# exporting the fikes to the storage system\n",
    "master_dataframe.to_csv(\"/content/drive/MyDrive/SPM_files/Selected_10k_v2.csv\", index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UqsdlTeRTDRd",
    "outputId": "80457f2f-85ad-4418-dc6c-4603a163e7ef"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Size of the dataframe : 609765017 bytes\n"
     ]
    }
   ],
   "source": [
    "# code to understan the size of the file \n",
    "from sys import getsizeof\n",
    "print(\"Size of the dataframe : {} bytes\".format(getsizeof(master_dataframe)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Inf2UR6BIqrS"
   },
   "outputs": [],
   "source": [
    "## End of the Notebook\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "provenance": [],
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}