notebooks/CIK_to_Graph.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SEC CIK files to graph\n",
    "\n",
    "(C) 2023-2024 by [Damir Cavar](http://damir.cavar.me/)\n",
    "\n",
    "This code generates a graph or triple representation from the Central Index Key (CIK) JSON data files for corporations that describe the coporate filings on [SEC](https://www.sec.gov/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Prerequisites:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install -U rdflib"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to import various modules for RDF, JSON, and regular expression processing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from rdflib.namespace import RDF, RDFS, SKOS, OWL, DC, DCTERMS, XSD, TIME, NamespaceManager\n",
    "from rdflib import Graph, URIRef, Literal, Namespace\n",
    "from datetime import datetime\n",
    "import os\n",
    "import glob\n",
    "import json\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data and files are located in a specific subfolder, and all files end in `json`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_folder = os.path.join(\".\", \"data\", \"CIKs\")\n",
    "data_files = tuple(glob.glob(os.path.join(data_folder, '*.json')))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following regular expression is used to clean corporation names and to generate the URI of the instance:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "re_name_clean = re.compile(r\"[ &,.-]+\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need a couple of URIs to define the graph relations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "default_prefix = r\"http://www.iu.edu/kg/sec/\"\n",
    "has_cik_uri = URIRef(default_prefix + r\"hasCIK\")\n",
    "entityType_uri = URIRef(default_prefix + r\"entityType\")\n",
    "has_sic_uri = URIRef(default_prefix + r\"hasSIC\")\n",
    "sicDescription_uri = URIRef(default_prefix + r\"hasSICDescription\")\n",
    "exchange_uri = URIRef(default_prefix + r\"exchange\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a graph instance for all the class specifications and corporation instances:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "g = Graph()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A couple of namespace declarations are necessary, too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "vaem_ns = Namespace(r\"http://www.linkedmodel.org/schema/vaem#\")\n",
    "iu_ns = Namespace(r\"http://www.iu.edu/kg/sec/\")\n",
    "namespace_manager = NamespaceManager(g)\n",
    "namespace_manager.bind(\"vaem\", vaem_ns, override=True)\n",
    "namespace_manager.bind(\"iusec\", iu_ns, override=True)\n",
    "g.namespace_manager = namespace_manager"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We add the specification and meta-information to the graph:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Graph identifier=N0af729135dcd425b8aac7c1bebc79b24 (<class 'rdflib.graph.Graph'>)>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ontology_uri = URIRef(default_prefix + r\"SEC\")\n",
    "g.add( (ontology_uri, RDF.type, OWL.Ontology) )\n",
    "g.add( (ontology_uri, DC.creator, Literal(\"Damir Cavar\")) )\n",
    "g.add( (ontology_uri, OWL.versionInfo, Literal(\"Created using automatic conversion scripts by Damir Cavar\")) )\n",
    "g.add( (ontology_uri, vaem_ns.hasOwner,   Literal(\"Indiana University, NLP-Lab, Course L715\")) )\n",
    "g.add( (ontology_uri, vaem_ns.hasSteward, Literal(\"Damir Cavar\")) )\n",
    "dt = datetime.now()\n",
    "g.add( (ontology_uri, vaem_ns.publishedDate, Literal(dt.strftime(\"%Y-%m-%dT%H:%M:%SZ\"))) )\n",
    "g.add( (ontology_uri, vaem_ns.releaseDate,   Literal(dt.strftime(\"%Y-%m-%dT%H:%M:%SZ\"))) )\n",
    "g.add( (ontology_uri, OWL.imports, URIRef(r\"http://purl.org/dc/elements/1.1/\")) )\n",
    "g.add( (ontology_uri, OWL.imports, URIRef(r\"http://purl.org/dc/terms/\")) )\n",
    "corporation_uri = URIRef(default_prefix + r\"Corporation\")\n",
    "g.add( (corporation_uri, RDF.type,        OWL.Class) )\n",
    "g.add( (corporation_uri, RDFS.subClassOf, OWL.Thing) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following function converts CIK data from the corporate SEC JSON data to triples and stores them in the graph:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def cik_to_graph(filename, g):\n",
    "    with open(filename, mode='r', encoding='utf-8') as f:\n",
    "        data = json.load(f)\n",
    "    if not \"name\" in data: return\n",
    "    name = re_name_clean.sub(\"_\", data[\"name\"].strip())\n",
    "    name = re.sub(r\"__+\", \"_\", name.lower())\n",
    "    while name[-1] == \"_\": name = name[:-1]\n",
    "    corp_id_uri = URIRef(default_prefix + name)\n",
    "    g.add( (corp_id_uri, RDFS.label, Literal(data[\"name\"].strip())) )\n",
    "    g.add( (corp_id_uri, RDFS.subClassOf, corporation_uri) )\n",
    "    for k in data:\n",
    "        if k == \"cik\":\n",
    "            g.add( (corp_id_uri, has_cik_uri, Literal(data[\"cik\"])) )\n",
    "        elif k == \"sic\":\n",
    "            g.add( (corp_id_uri, has_sic_uri, Literal(data[\"sic\"])) )\n",
    "        elif k == \"sicDescription\":\n",
    "            g.add( (corp_id_uri, sicDescription_uri, Literal(data[\"sicDescription\"])) )\n",
    "        elif k == \"insiderTransactionForOwnerExists\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"insiderTransactionForOwnerExists\"), Literal(data[\"insiderTransactionForOwnerExists\"])) )\n",
    "        elif k == \"insiderTransactionForIssuerExists\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"insiderTransactionForIssuerExists\"), Literal(data[\"insiderTransactionForIssuerExists\"])) )\n",
    "        elif k == \"exchanges\":\n",
    "            for e in data[\"exchanges\"]:\n",
    "                g.add( (corp_id_uri, exchange_uri, Literal(e)) )\n",
    "        elif k == \"ein\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"hasEIN\"), Literal(data[\"ein\"])) )\n",
    "        elif k == \"description\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"description\"), Literal(data[\"description\"])) )\n",
    "        elif k == \"website\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"website\"), Literal(data[\"website\"])) )\n",
    "        elif k == \"investorWebsite\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"investorWebsite\"), Literal(data[\"investorWebsite\"])) )\n",
    "        elif k == \"category\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"category\"), Literal(data[\"category\"])) )\n",
    "        elif k == \"fiscalYearEnd\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"fiscalYearEnd\"), Literal(data[\"fiscalYearEnd\"])) )\n",
    "        elif k == \"stateOfIncorporation\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"stateOfIncorporation\"), Literal(data[\"stateOfIncorporation\"])) )\n",
    "        elif k == \"stateOfIncorporationDescription\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"stateOfIncorporationDescription\"), Literal(data[\"stateOfIncorporationDescription\"])) )\n",
    "        #     \"addresses\": {\n",
    "        # \"mailing\": {\n",
    "        #     \"street1\": \"ROUTE 206 AND PROVINCE LINE ROAD\",\n",
    "        #     \"street2\": null,\n",
    "        #     \"city\": \"PRINCETON\",\n",
    "        #     \"stateOrCountry\": \"NJ\",\n",
    "        #     \"zipCode\": \"08543\",\n",
    "        #     \"stateOrCountryDescription\": \"NJ\"\n",
    "        # },\n",
    "        # \"business\": {\n",
    "        #     \"street1\": \"ROUTE 206 AND PROVINCE LINE ROAD\",\n",
    "        #     \"street2\": null,\n",
    "        #     \"city\": \"PRINCETON\",\n",
    "        #     \"stateOrCountry\": \"NJ\",\n",
    "        #     \"zipCode\": \"08543\",\n",
    "        #     \"stateOrCountryDescription\": \"NJ\"\n",
    "        # }\n",
    "        # },\n",
    "        elif k == \"phone\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"phone\"), Literal(data[\"phone\"])) )\n",
    "        elif k == \"flags\":\n",
    "            g.add( (corp_id_uri, URIRef(default_prefix + \"flags\"), Literal(data[\"flags\"])) )\n",
    "        elif k == \"entityType\":\n",
    "            g.add( (corp_id_uri, entityType_uri, Literal(data[\"entityType\"])) )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We generate the corporate SEC CIK triple data for all corporations that we have JSON data for:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "for f in data_files:\n",
    "    cik_to_graph(f, g)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We serialize the graph as a Turtle graph in the same location where the JSON data files are stored:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Graph identifier=N0af729135dcd425b8aac7c1bebc79b24 (<class 'rdflib.graph.Graph'>)>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.serialize(destination=os.path.join(\".\", \"data\", \"CIKs\", \"cik_graph.ttl\"), format=\"ttl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also serialize the graph in JSON-LD format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Graph identifier=N0af729135dcd425b8aac7c1bebc79b24 (<class 'rdflib.graph.Graph'>)>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.serialize(destination=os.path.join(\".\", \"data\", \"CIKs\", \"cik_graph.jsonld\"), format=\"json-ld\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(C) 2023-2024 by [Damir Cavar](http://damir.cavar.me/)"
   ]
  }
 ],
 "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.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}