Data_preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9fe89e34",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import liberaries and functions\n",
    "import pandas as pd\n",
    "import glob\n",
    "import time\n",
    "from bs4 import BeautifulSoup\n",
    "import re\n",
    "import requests\n",
    "from random import random\n",
    "from tqdm.auto import tqdm\n",
    "from Data.data import load_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d063cb71",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get firm category per CIK\n",
    "# def make_request(url, params=None, headers=None, max_retries = 4):\n",
    "\n",
    "#     current_tries = 1\n",
    "#     while current_tries < max_retries:\n",
    "#         response = requests.get(url=url, params=params, headers=headers)\n",
    "        \n",
    "#         if response.status_code == 200:\n",
    "#             # time.sleep(random())\n",
    "#             return response\n",
    "#         else:\n",
    "#             time.sleep(random()+0.1)\n",
    "#             current_tries += 1\n",
    "            \n",
    "#     return requests.get(url=url, params=params, headers=headers)\n",
    "\n",
    "# headers = {'User-Agent': 'kuleuven'}\n",
    "\n",
    "# def get_category(CIK):\n",
    "#     res = make_request(f\"https://data.sec.gov/submissions/CIK{CIK:010}.json\", headers=headers)\n",
    "    \n",
    "#     if res.status_code == 200:\n",
    "#         temp = res.json()['category']\n",
    "#     else:\n",
    "#         temp = res.status_code\n",
    "\n",
    "#     return temp\n",
    "\n",
    "# CIKs = files[\"CIK\"].astype(int).unique()\n",
    "# cat_list = [get_category(cik) for cik in tqdm(CIKs)]\n",
    "# cat_df = pd.DataFrame({\"CIK\": CIKs, \"category\": cat_list})\n",
    "# cat_df.to_csv(\"category.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "65ec8229",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all raw html/text file paths\n",
    "all_files = glob.glob('Data\\RiskData\\*')\n",
    "\n",
    "all_files_df = pd.DataFrame(all_files, columns=[\"path\"])\n",
    "all_files_df[\"name\"] = all_files_df[\"path\"].apply(lambda x: x.split(\"\\\\\")[-1])\n",
    "all_files_df[\"extension\"] = all_files_df[\"name\"].apply(lambda x: x.split(\".\")[-1])\n",
    "all_files_df[\"CIK\"] = all_files_df[\"name\"].apply(lambda x: x.split(\"-\")[0]).astype(int)\n",
    "\n",
    "all_files_df[\"report_dt\"] = pd.to_datetime(\n",
    "    all_files_df[\"name\"].apply(lambda x: x.split(\"-\")[2][1:])\n",
    ")\n",
    "all_files_df[\"filing_dt\"] = pd.to_datetime(\n",
    "    all_files_df[\"name\"].apply(lambda x: x.split(\"-\")[1][1:])\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e431070e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter files for the time period and drop duplicates\n",
    "files = all_files_df.copy()[\n",
    "    (all_files_df[\"report_dt\"].dt.year > 2005) & \n",
    "    (all_files_df[\"report_dt\"].dt.year < 2022)\n",
    "    ]\n",
    "    \n",
    "files.sort_values(by=[\"CIK\", \"report_dt\", \"filing_dt\"], inplace=True)\n",
    "files.drop_duplicates(subset=[\"CIK\", \"report_dt\"], keep='last', inplace=True)\n",
    "files.loc[:, \"CIK\"] = files[\"CIK\"].astype(int)\n",
    "\n",
    "# # Save all files to train the W2V model\n",
    "# files.to_csv(\"Data\\\\all_files.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "76467fdb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\Users\\u0147656\\Anaconda3\\envs\\ETM_env\\lib\\site-packages\\numpy\\lib\\arraysetops.py:580: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    }
   ],
   "source": [
    "# Generate the ETM training data\n",
    "RF_df = pd.read_csv('Data\\W2V_train_3.csv', index_col=0).dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cb8b6154",
   "metadata": {},
   "outputs": [],
   "source": [
    "cat_df = pd.read_csv(\"Data/category.csv\", index_col=0)\n",
    "\n",
    "# Find smaller reporting companies\n",
    "regex = r\"(Smaller reporting company)\"\n",
    "cat_df[\"category\"] = cat_df[\"category\"].str.extract(regex)[0].str.replace(regex, 'SRC', regex=True)\n",
    "\n",
    "# Firms industry info\n",
    "all_co = pd.read_excel(\"Data/all_companies.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "621921da",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add firm categories to the files df\n",
    "RF_df_extend = pd.merge(RF_df, cat_df, on=\"CIK\", how=\"left\")\n",
    "\n",
    "# Add firm industry to the Risk Factors df\n",
    "RF_df_extend = pd.merge(RF_df_extend, all_co[[\"CIK\", \"SIC\", \"Industry\"]], on=\"CIK\", how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "81bb2e5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(RF_df_extend[[\"CIK\", \"report_dt\", \"filing_dt\"]].drop_duplicates().shape)\n",
    "print(RF_df_extend[\"CIK\"].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "05cb3bfb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop SRC firms and finance industry\n",
    "RF_no_Fin = RF_df_extend.copy()[\n",
    "    # (RF_df_extend[\"category\"] != \"SRC\") &\n",
    "    (RF_df_extend[\"Industry\"] != \"Office of Finance\") & \n",
    "    (RF_df_extend[\"Industry\"] != \"Office of Structured Finance\")\n",
    "]\n",
    "RF_no_Fin.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d906e36d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1.394277e+06\n",
       "mean     1.158323e+02\n",
       "std      1.081632e+02\n",
       "min      1.000000e+00\n",
       "25%      5.600000e+01\n",
       "50%      9.400000e+01\n",
       "75%      1.490000e+02\n",
       "max      1.475800e+04\n",
       "Name: cleaned_txt, dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "word_cnt = RF_no_Fin['cleaned_txt'].astype('str').map(lambda x: len(x.split()))\n",
    "word_cnt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a5cdd033",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin = RF_no_Fin[word_cnt >= word_cnt.quantile(0.1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8efb73f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1.255026e+06\n",
       "mean     4.541601e+01\n",
       "std      2.460240e+01\n",
       "min      1.000000e+00\n",
       "25%      2.800000e+01\n",
       "50%      4.000000e+01\n",
       "75%      5.800000e+01\n",
       "max      2.400000e+02\n",
       "Name: cleaned_txt, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rf_count = RF_no_Fin.groupby(['CIK', 'report_dt'])['cleaned_txt'].transform('count')\n",
    "rf_count.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e372cd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin = RF_no_Fin[\n",
    "    (rf_count>rf_count.quantile(0.005))&\n",
    "    (rf_count<rf_count.quantile(0.999))\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "05d1e064",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(RF_no_Fin[[\"CIK\", \"report_dt\", \"filing_dt\"]].drop_duplicates().shape)\n",
    "print(RF_no_Fin[\"CIK\"].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6898949",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin.rename(columns={'Unnamed: 0.1': 'rf_seq'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25753017",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d93fdaa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin.to_csv(\"Data\\clean_docs_3.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e8a858a",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_Fin = pd.read_csv(\"Data\\clean_docs_3.csv\")\n",
    "Item1A = pd.read_csv(\"Data\\Item1As.csv\", index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c88daf5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Item1A = Item1A[Item1A.CIK.isin(RF_no_Fin.CIK.unique())]\n",
    "Item1A.to_csv(\"Data\\Item1As.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d26e6900",
   "metadata": {},
   "source": [
    "Code try"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a936fa46",
   "metadata": {},
   "outputs": [],
   "source": [
    "get_CIKs = pd.read_csv('Data\\get_CIKs.csv', index_col=0)\n",
    "missing_CIKs = [CIK for CIK in get_CIKs[\"CIK\"].unique() if CIK not in all_files_df[\"CIK\"].unique()]\n",
    "missing_CIKs_df = get_CIKs.set_index(\"CIK\").loc[missing_CIKs].reset_index()\n",
    "missing_CIKs_df.to_csv(\"missing_CIKs_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57375826",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_no_SRC = pd.read_csv(\"Data\\RF_df.csv\", index_col=0).reset_index(drop=True)\n",
    "RF_SRC = pd.read_csv(\"Data\\RF_SRC.csv\", index_col=0).reset_index(drop=True)\n",
    "RF_df = pd.concat([RF_no_SRC, RF_SRC]).reset_index(drop=True)\n",
    "RF_df.to_csv(\"Data\\W2V_train.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90a7824d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_data = pd.read_csv(\"Data\\clean_docs.csv\")\n",
    "word_cnt = cleaned_data['cleaned_txt'].astype('str').map(lambda x: len(x.split()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6c498ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "word_cnt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8dd0301",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(int(word_cnt.quantile(q=0.999)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad0d3393",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(word_cnt.quantile(q=0.001))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4d41d0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from bs4 import BeautifulSoup\n",
    "import re\n",
    "import spacy\n",
    "# from spacy.attrs import SENT_START\n",
    "from time import strftime, gmtime\n",
    "import pandas as pd\n",
    "import glob\n",
    "nlp = spacy.load('en_core_web_sm')\n",
    "from file_preprocess import file_preprocess"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0a7c1cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "test_files = [\n",
    "    'Data\\\\RiskData\\\\1377888-R20130415-C20121231-F13-0.htm',\n",
    "    'Data\\\\RiskData\\\\20-R20090313-C20090103-F58-0.htm',\n",
    "    'Data\\\\RiskData\\\\1750-R20190718-C20190531-F66-0.htm',\n",
    "    'Data\\\\RiskData\\\\2488-R20180227-C20171230-F42-0.htm',\n",
    "    'Data\\\\RiskData\\\\1505732-R20200228-C20191231-F40-0.htm',\n",
    "    'Data\\\\RiskData\\\\39899-R20080228-C20071230-F03-0.htm',\n",
    "    'Data\\\\RiskData\\\\3153-R20210218-C20201231-F06-0.htm',\n",
    "    'Data\\\\RiskData\\\\3453-R20200228-C20191231-F57-0.htm',\n",
    "    'Data\\\\RiskData\\\\6494-R20170324-C20161231-F58-0.htm',\n",
    "    'Data\\\\RiskData\\\\1000234-R20070316-C20061231-F01-0.htm',\n",
    "    'Data\\\\RiskData\\\\1004980-R20200218-C20191231-F09-0.htm',\n",
    "    'Data\\\\RiskData\\\\100517-R20200225-C20191231-F10-0.htm',\n",
    "    'Data\\\\RiskData\\\\1007019-R20070129-C20061031-F01-0.htm',\n",
    "    'Data\\\\RiskData\\\\1003078-R20211020-C20210828-F99-0.htm',\n",
    "    'Data\\\\RiskData\\\\1005409-R20140313-C20131231-F18-0.htm',\n",
    "    'Data\\\\RiskData\\\\1005757-R20070301-C20061231-F01-0.htm',\n",
    "    'Data\\\\RiskData\\\\1536089-R20200115-C20190930-F42-0.htm',\n",
    "    'Data\\\\RiskData\\\\1423746-R20130424-C20121231-F30-0.htm',\n",
    "    'Data\\\\RiskData\\\\20212-R20100302-C20091231-F63-0.htm',\n",
    "    'Data\\\\RiskData\\\\1789029-R20210602-C20201231-F86-0.htm',\n",
    "    'Data\\\\RiskData\\\\30140-R20070612-C20070331-F01-0.htm',\n",
    "    'Data\\\\RiskData\\\\1727196-R20210309-C20201231-F41-0.htm'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "345b3e1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(test_files[-1], \"rb\") as f:\n",
    "    html_text = f.read()\n",
    "    \n",
    "RFs = file_preprocess(html_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a6aae78",
   "metadata": {},
   "outputs": [],
   "source": [
    "RFs = []\n",
    "for file in test_files:\n",
    "    with open(file, \"rb\") as f:\n",
    "        html_text = f.read()\n",
    "\n",
    "        RFs.append(file_preprocess(html_text))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19c9e9af",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_df = pd.read_csv('Data\\RFs_all.csv').dropna()\n",
    "RF_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "931c621e",
   "metadata": {},
   "outputs": [],
   "source": [
    "rf_count = RF_df.groupby(['CIK', 'report_dt'])['Item 1A'].count()\n",
    "rf_count.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df627c05",
   "metadata": {},
   "outputs": [],
   "source": [
    "word_cnt = RF_df['Item 1A'].astype('str').map(lambda x: len(x.split()))\n",
    "word_cnt.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10dfd8c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pattern_1 = r\"table\\s+of\\s+content[s]?\"\n",
    "pattern_2 = r\"(item\\W*)?1a\"\n",
    "pattern_3 = r\"risk\\s+factor[s]?\"\n",
    "\n",
    "pattern = r\"(table\\s+of\\s+content[s]?)|((item\\W*)?1a)|(risk\\s+factor[s]?)\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3049fd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "text = u\"Our Series D Preferred Stock u.s. or us or US entitles the holder c to cumulative dividends, payable quarterly, at an annual rate of (i) 8% of the Stated Value of $1.00 during the three year period commencing on the date of issue, and (ii) 12% of the Stated table of content Value commencing three years after the date risk factor of issue item 1A.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "200efd31",
   "metadata": {},
   "outputs": [],
   "source": [
    "from Data.data import nlp_clean, bigram"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4ca9071",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample = RF_df[\"Item 1A\"].sample(1000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd2720d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "for t in nlp(text):\n",
    "    print(t, t.is_alpha, t.pos_, t.shape_.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ceddc2c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "list(nlp_clean(nlp(text)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25a66ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample.str.replace(pattern, \" \", case=False, regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e44209f3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace some unwanted patterns in the RFs\n",
    "pattern = r\"(table\\s+of\\s+content[s]?)|((item\\W*)?1a)|(risk\\s+factor[s]?)\"\n",
    "RF_df[\"Item 1A\"] = RF_df[\"Item 1A\"].str.replace(pattern, \" \", case=False, regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40ae8d19",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample = RF_df[\"Item 1A\"][word_cnt<=2]\n",
    "corpus = nlp.pipe(sample.to_list())\n",
    "clean_corpus = [list(d) for d in (nlp_clean(doc, lemma=True) for doc in corpus)]\n",
    "\n",
    "def token(text):\n",
    "    return text\n",
    "\n",
    "transformed_sents = bigram(\n",
    "    raw_data = clean_corpus, \n",
    "    tokenizer=token,\n",
    "    min_cnt = 0.00001\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8f2a406",
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_data = sample.to_frame()\n",
    "cleaned_data.loc[:, 'cleaned_txt'] = [\" \".join(d) for d in transformed_sents]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64b18c25",
   "metadata": {},
   "outputs": [],
   "source": [
    "RF_df[\"Item 1A\"][word_cnt<=3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "91ad3c86",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"Data/filings_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "9f3e4c33",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[(df[\"reportDate\"]>=\"2006-01-01\")&(df[\"reportDate\"]<=\"2022-06-30\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "41ef166a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sample = pd.read_excel(\"Sample.xlsx\")\n",
    "data = agg_tops[[\n",
    "    'CIK', 'filing_dt', 'report_dt', 'rprt_length'\n",
    "]].copy()\n",
    "\n",
    "data[\"reported\"] = agg_tops['Topic'].map(len)\n",
    "SP500 = data[data['CIK'].isin(sample['CIK'])]\n",
    "SP500.to_excel(\"Sample_Olivia.xlsx\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.13 ('ETM_env')",
   "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.7.13"
  },
  "vscode": {
   "interpreter": {
    "hash": "2e814d5f901f2e2f1812217256e7a2386c6b3dac449e52472436b21a420d1617"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}