{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "xslF345X02\n",
      "xsl1-Z_X01\n",
      "xslSBSE-C_X01\n",
      "xsl144X01\n",
      "xsl1-A_X01\n",
      "xslF345X03\n",
      "xslFormDX01\n",
      "xslQUALIFX01\n",
      "xslSCHEDULE_13D_X01\n",
      "xslFormNPORT-P_X01\n",
      "xslEFFECTX01\n",
      "xslFormMA-I_X01\n",
      "xslForm13F_X02\n",
      "xslC_X01\n",
      "xslSBSE-A_X01\n",
      "xslN-PX_X01\n",
      "xslSCHEDULE_13G_X01\n",
      "xsl1-K_X01\n",
      "xslF345X01\n",
      "xslF25X02\n",
      "xslF345X05\n",
      "xslFormN-CEN_X01\n",
      "xslF345X04\n"
     ]
    }
   ],
   "source": [
    "# Exploring what documents the SEC has both human readable and machine readable versions of\n",
    "# Human:   https://www.sec.gov/Archives/edgar/data/1318605/000128825718000026/xslFormDX01/primary_doc.xml\n",
    "# Machine: https://www.sec.gov/Archives/edgar/data/1318605/000128825718000026/primary_doc.xml\n",
    "\n",
    "import polars as pl\n",
    "\n",
    "# Read the CSV file\n",
    "df = pl.read_csv('data/submissions_index.csv')\n",
    "\n",
    "# Clean up the data\n",
    "df = df.with_columns(\n",
    "    pl.col('accession_number').str.replace_all('-', '').alias('accession_number')\n",
    ")\n",
    "\n",
    "# Filter rows where primary_doc_url contains a slash and create item_before_slash column\n",
    "df_filtered = df.filter(pl.col('primary_doc_url').str.contains('/')).with_columns(\n",
    "    pl.col('primary_doc_url').str.split('/').list.first().alias('item_before_slash')\n",
    ")\n",
    "\n",
    "# Get unique items\n",
    "unique_items = df_filtered.select('item_before_slash').unique()\n",
    "for item in unique_items['item_before_slash'].to_list():\n",
    "    print(item)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://www.sec.gov/Archives/edgar/data/1651721/000116169721000148/xsl1-K_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/000095017024034150/xslF345X02/ownership.xml\n",
      "https://www.sec.gov/Archives/edgar/data/58411/000139390523000189/xsl1-Z_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/000180280622000010/xslFormDX01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1729427/000168316820001280/xslC_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1737995/000149315224021891/xsl1-A_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/000089924323007030/xslF345X03/doc4.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/000095017024070843/xslF345X05/ownership.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1604174/000110465924092878/xslFormNPORT-P_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/720005/000072000523000058/xslFormMA-I_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827821/000196794024000071/xsl144X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1604174/000160417424000002/xslFormN-CEN_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/313807/000087666121001337/xslF25X02/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/313807/000031380724000026/xslForm13F_X02/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/999999999523000761/xslEFFECTX01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1114446/000111444624000948/xslSBSE-A_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/891478/000089147822000050/xslSBSE-C_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1604174/000110465924094942/xslN-PX_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1737995/999999999424000071/xslQUALIFX01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1008586/000109544924000051/xslSCHEDULE_13D_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1827087/000089924323015174/xslF345X04/doc4.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1971543/000163658724000029/xslSCHEDULE_13G_X01/primary_doc.xml\n",
      "https://www.sec.gov/Archives/edgar/data/1004980/000100498003000162/xslF345X01/edgar.xml\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jgfri\\AppData\\Local\\Temp\\ipykernel_23288\\314493069.py:21: DeprecationWarning: `GroupBy.count` is deprecated. It has been renamed to `len`.\n",
      "  item_counts = df_filtered.group_by('item_before_slash').count().sort('count', descending=True)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Item Counts:\n",
      "shape: (23, 2)\n",
      "┌─────────────────────┬─────────┐\n",
      "│ item_before_slash   ┆ count   │\n",
      "│ ---                 ┆ ---     │\n",
      "│ str                 ┆ u32     │\n",
      "╞═════════════════════╪═════════╡\n",
      "│ xslF345X03          ┆ 2443015 │\n",
      "│ xslF345X02          ┆ 891785  │\n",
      "│ xslF345X05          ┆ 245072  │\n",
      "│ xslF345X04          ┆ 74968   │\n",
      "│ xsl144X01           ┆ 59564   │\n",
      "│ …                   ┆ …       │\n",
      "│ xslC_X01            ┆ 222     │\n",
      "│ xslSBSE-C_X01       ┆ 72      │\n",
      "│ xsl1-Z_X01          ┆ 68      │\n",
      "│ xslSCHEDULE_13D_X01 ┆ 47      │\n",
      "│ xslFormMA-I_X01     ┆ 4       │\n",
      "└─────────────────────┴─────────┘\n"
     ]
    }
   ],
   "source": [
    "\n",
    "example_urls = df_filtered.group_by('item_before_slash').agg(\n",
    "    pl.col('cik').first().alias('cik'),\n",
    "    pl.col('accession_number').first().alias('accession_number'),\n",
    "    pl.col('primary_doc_url').first().alias('primary_doc_url')\n",
    ").with_columns(\n",
    "    pl.concat_str(\n",
    "        pl.lit(\"https://www.sec.gov/Archives/edgar/data/\"),\n",
    "        pl.col('cik'),\n",
    "        pl.lit(\"/\"),\n",
    "        pl.col('accession_number'),\n",
    "        pl.lit(\"/\"),\n",
    "        pl.col('primary_doc_url')\n",
    "    ).alias('full_url')\n",
    ")\n",
    "\n",
    "# Print the results\n",
    "for row in example_urls.iter_rows(named=True):\n",
    "    print(row['full_url'])\n",
    "\n",
    "# If you want to see the count of each item\n",
    "item_counts = df_filtered.group_by('item_before_slash').count().sort('count', descending=True)\n",
    "print(\"Item Counts:\")\n",
    "print(item_counts)"
   ]
  }
 ],
 "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
